【sql数据分析流程】

基本概念

主键——必须包含唯一值，非空，唯一非空约束；

表

创建表

CREATE TABLE bookpub (
  书号 int(10) NOT NULL,
  书名 varchar(50) DEFAULT NULL,
  作者 varchar(50) DEFAULT NULL,
  售价 float DEFAULT NULL,
  出版日期  timestamp NULL DEFAULT NULL,
  下一次出版日期 timestamp NULL DEFAULT NULL,
  PRIMARY KEY (书号) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入值

insert  into bookpub (书号,书名,作者,售价,出版日期,下一次出版日期) values 
(1,'ASP.NET项目开发全程实录','张领等',59,'2017-12-01 00:00:00','2018-03-18 00:00:00'),
(2,'ASP.NET程序开发范例宝典第版','贯伟红等',89,'2017-11-12 00:00:00','2018-05-01 00:00:00'),
(3,'ASP.NET网络开发实例自学手册','房大伟等',86,'2017-01-25 00:00:00','2018-04-10 00:00:00'),
(4,'ASP.NET2.0网络编程自学手册','苏宇等',78,'2017-06-05 00:00:00','2018-10-01 00:00:00'),
(5,'ASP.NET技术方案宝典','王小科等',75,'2017-03-05 00:00:00','2018-08-07 00:00:00'),
(6,'ASP.NET网络编程标准教程','丁红等',88,'2017-04-06 00:00:00','2018-08-08 00:00:00'),
(7,'SQL应用与开发范例宝典','房大伟等',89,'2017-11-03 00:00:00','2018-11-11 00:00:00');

导入数据

查询SELECT

SELECT * FROM brand ;
SELECT * FROM brand limit 10;
SELECT * FROM brand WHERE cat_name='运动户外';

LIKE & NOT LIKE

SELECT * FROM brand WHERE cat_name like '手机%';
SELECT * FROM brand WHERE cat_name not like '手机%';

IN

select * from brand where cat_name in ('图书杂志','海购商品','运动户外'); 

连表查询

select * from 城市划分;
select a.*,b.* from 招聘信息 as a left join 城市划分 as b on a.工作地点 = b.城市名称;

select a.招聘单位,a.薪资区间,a.招聘岗位,a.工作经验, a.类别, a.工作地点,b.城市名称,b.城市划分 from 招聘信息 as a left join 城市划分 as b on a.工作地点 = b.城市名称;

select a.招聘单位,a.薪资区间,a.招聘岗位,a.工作经验, a.类别, a.工作地点,b.城市名称,b.城市划分 from 招聘信息 as a inner  join 城市划分 as b on a.工作地点 = b.城市名称;

select a.招聘单位,a.薪资区间,a.招聘岗位,a.工作经验, a.类别, a.工作地点,b.城市名称,b.城市划分 from 招聘信息 as a  right  join 城市划分 as b on a.工作地点 = b.城市名称;

更新UPDATE

UPDATE brand set url = '100'  WHERE cat_name='运动户外';

插入INSERT INTO

INSERT INTO brand 
select * from brand_new where cat_name='运动户外';

删除DELETE/DROP

DELETE FROM brand  where cat_name='运动户外';
alter table brand DROP COLUMN is_hot;

排序ORDER BY

select * from free_data order by 数据量;
select * from free_data order by 数据量 desc;
select * from free_data order by 数据量,创建时间;

统计量MAX/MIN/AVG/SUM/STEDDEV等

select sum(售价) from bookpub;
select avg(售价) from bookpub;
select STDDEV(售价) from bookpub;

select min(售价) from bookpub;
select max(售价) from bookpub;

select count(售价) from bookpub;

分类汇总GROUP BY

select 部门名称, sum(数据量) as total from test01.free_data group by 部门名称;

筛选WHERE/HAVING

select 部门名称, sum(数据量) as total from test01.free_data where 数据量 > 0 group by 部门名称;
select 部门名称, sum(数据量) as total  from test01.free_data  group by 部门名称 having total>50 order by total;