Tracy785的博客

1.1 导入外部数据 1.2 创建数据表 #创建数据表字段 CREATE TABLE data1( id INT(10), Record_date DATE, city VARCHAR(255), age VARCHAR(255), category VARCHAR(255), price FLOAT(10) ); #插入数据 INSERT INTO data1 VALUES (10...

2.1 数据维度(行列) #查看数据条数 SELECT COUNT(*) FROM data1; #查看数据表2的字段数量 SELECT COUNT(*) as column_num FROM information_schema.`COLUMNS` WHERE TABLE_NAME='data2'; 2.2 数据表信息 #查看数据表信息 DESC data1; 2.3 查看空值 #查看pr...

3.1 处理缺失值(填充) #计算price列的均值 SELECT ROUND(AVG(price),2) AS avg_price FROM data1; #使用均值填充0值 UPDATE data1 SET price=2199.67 WHERE price=0; 3.2 清理空格 #清理字符中的空格 UPDATE data1 SET city=TRIM(city); select ci...

4.1 数据表匹配合并 #INNER JOIN匹配查询 SELECT record_date,city,age,category,price,gender,pay,mp FROM data1 INNER JOIN data2 ON data1.id=data2.id; #LEFT JOIN匹配查询 SELECT record_date,city,age,category,price,gender...

5.1　按列提取数据 #按列提取 SELECT city FROM data1; 5.2　按行提取数据 #按行提取 SELECT * FROM data1 WHERE city='beijing'; 5.3　按位置提取数据 #按位置提取 SELECT * FROM data1 LIMIT 2,5; 5.4　按条件提取数据 #按条件提取并计算 SELECT AVG(price) FROM data1...

6.1按条件筛选（与，或，非） #数据筛选AND SELECT * FROM data1 WHERE city='shanghai' AND age>30; #数据筛选IN SELECT * FROM data1 WHERE city IN ('shanghai','beijing'); #数据筛选OR SELECT * FROM data1 WHERE city='shanghai' ...

7.1　分类汇总 #单列分类汇总 SELECT city,COUNT(id) AS id_count FROM data1 GROUP BY city ORDER BY id_count; #多列分类汇总 SELECT city,colour,ROUND(SUM(price),2) AS id_count FROM data1 GROUP BY city,colour; 7.2　数据透视 #查看...