一.前番回顾
1.MySQL常见 数据类型
我们在实际开发中最常用的数据类型：
整型： tinyint int bigint
小数型: decimal double
字符串型：char varchar
日期时间：date datetime timestamp
文本与大对象： text longtext blob
2.基本操作之DML操作
1) 添加(主要关键字：insert)
create table mktest.mkt select * from mktest.emp where empno>20; --前提是两表的字段及字段类型以及约束等保持一致，当然也可以使用子查询过滤部分字段，不需要字段名称一致
insert into mktest.mkt(sid,sname,age) values(12,'Tom',34); -- 选择部分字段添加
insert into mktest.mkt values(12,'Tom',34，3200.00); -- 全字段添加
insert into mktest.mkt select * from mkt2; -- 结果集作为数据源进行记录的插入
2) 更新(主要关键字：update.... set ...)
update mktest.mkt set sname='Sam' where sid=12; -- 针对编号为12的客户进行英文名称的修改
3) 删除(主要关键字：delete)
delete from mktest.mkt [where 条件]; -- 中括号中条件可选，没有where条件的话即删除表所有数据，否则是按照条件删除。
4) 清空表(truncate table)
truncate table mkt; -- 清空表的全部记录，属于DDL操作，因此效率比较高，对于自增的属性，会进行重置
-- 注意truncate table 与 drop table 以及delete from区别
drop table 会删除表结构
delete from 会删除记录，但是因为属于DML操作，所以性能相对于truncate table来说比较慢，而且不会对自增主键不会进行重置。
truncate table 真正的清空表，会对自增主键的值进行重置，效率比较高。
二.DQL 查询操作
1.基础查询SQL
select 字段列表 from mktest.mkt [where 条件]；-- where 条件不要即查询该表所有记录，否则按照条件删选满足条件的记录。
-- 例如：
select * from mktest.mkt; -- 查询所有记录的所有字段，注：*通配符表示所有字段(因为它会全字段扫描，当数据库表记录比较大的时候性能比较低，因此生产环境不会使用*，而宁可写全字段)
select sname,age from mktest.mkt where sid >20; -- 查询编号大于20的客户的名字和年龄
select sname,age from mktest.mkt where sname is null; -- 查询名称为null的客户的名字和年龄
2.条件查询(模糊)
条件查询:即上面提到的 + where 条件的查询，模糊查询查询则是值使用 like 关键字 ，虽然like模糊查询会全表查询牺牲一定的性能，但是有时候某些应用场景需要用到，因此必须会使用。
通配符：
% -- 匹配任意多个字符
_ -- 下划线，匹配一个任意字符
%s% -- 匹配包含s的
s% -- 匹配以s开头的
%s -- 匹配以s结尾的
_L% -- 匹配第一个字符任意，第二个字符必须为L的
select * from mktest.mkt where sname like '%y'; -- 查询名称以y结尾的客户信息
select * from mktest.mkt where sname like '_y'; --查询名字第一个字符任意，第二个字符为y的客户信息
3.分组查询
1) 聚合函数
谈到分组就要谈到聚合函数了，所谓聚合函数指的是MySQL提供的，对于分组字段进行聚合，例如求某一组数的最大值，最小值，总个数，求和操作等。
常用聚合函数：
count(1) 统计记录数，count(字段名)则是对字段的统计
sum(字段名) 对某一字段聚合求和
max(字段名) 求最大值 -> 例如一个部门最高的工资
avg(字段名) 求平均值 -> 求某一个部门的员工的平均工资
min(字段名) 求最小值
2) 分组关键字 (group by )
对分组后的结果集进行条件删选的话，使用having关键字，使用方法同where
select age,max(invest) from mktest.mkt group by age; -- 即求不同年龄段的客户的最大投资额，展示分组年龄和该年龄最大投资额
select age,max(invest) from mktest.mkt group by age having age>24;-- 针对分组后的结果去除age<=24的部分。
having 和where的区别：
顺序：having是在分组之后使用的
能否使用聚合函数：having可以使用聚合函数
4.结果排序(order by)
select * from mktest.mkt order by invest desc; -- 对查询后得到的结果集进行降序排序返回
select * from mktest.mkt order by invest; -- 默认是升序
select * from mktest.mkt order by invest asc; -- 升序
即：
降序 -> order by xxx desc
升序 -> order by xxx [asc]; asc -- 可以省略
5.对查询结果集进行限制(limit)
limit 是该查询结果集中最后执行的限制结果集的记录条数的关键字。
select age,max(invest) from mktest.mkt group by age having age>24 limit 2;
-- 此时最终结果只有两条，而不是每一个分组两条哦(当然也不能)。
使用：
limit 起始查询索引,查询的条数
参数1：起始查询条数，如果从0开始查询，则可以直接省略
select * from mktest.mkt where age>20 limit 2,5; -- 即从结果集的第三条记录开始取5条记录
注意：
limit关键字限制查询结果只能在MySQL中使用，在其他数据库管理系统中可不能使用哦，hive 本省也不是标准SQL，姑且不算。
提示:关于子查询，内外连接等高级查询将在后续章节涉及到，此处不做详解。
6.练习：
1) 数据准备：
emp员工表
| Field | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| empno | int(11) | No | PRI | null | 员工编号 |
| ename | varchar(30) | YES | null | 员工名称 | |
| job | varchar(30) | YES | null | 工作 | |
| mgr | int(11) | YES | null | 上司编号 | |
| hiredate | datetime | YES | null | 雇用日期 | |
| sal | decimal(10,2) | YES | null | 薪水 | |
| comm | decimal(10,2) | YES | null | 年终奖金 | |
| deptno | int(11) | YES | MUL外键 | null | 部门ID |
INSERT INTO mktest.emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17 00:00:00', 800.00, null, 20);
INSERT INTO mktest.emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20 00:00:00', 1600.00, 300.00, 30);
INSERT INTO mktest.emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22 00:00:00', 1250.00, 500.00, 30);
INSERT INTO mktest.emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02 00:00:00', 2975.00, null, 20);
INSERT INTO mktest.emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-29 00:00:00', 1250.00, 1400.00, 30);
INSERT INTO mktest.emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01 00:00:00', 2850.00, null, 30);
INSERT INTO mktest.emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09 00:00:00', 2450.00, null, 10);
INSERT INTO mktest.emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19 00:00:00', 3000.00, null, 20);
INSERT INTO mktest.emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7802, 'Jpppp', 'Programine', 7788, '2019-01-21 00:00:00', 5000.00, 100.00, 20);
INSERT INTO mktest.emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7839, 'KING', 'PRESIDENT', null, '1981-11-17 00:00:00', 5000.00, null, 10);
INSERT INTO mktest.emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08 00:00:00', 1500.00, 0.00, 30);
INSERT INTO mktest.emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23 00:00:00', 1100.00, null, 20);
INSERT INTO mktest.emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03 00:00:00', 950.00, null, 30);
INSERT INTO mktest.emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03 00:00:00', 3000.00, null, 20);
INSERT INTO mktest.emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23 00:00:00', 1300.00, null, 10);
注意：emp表使用empno作为主键，另外deptno作为外键，引用自dept表的主键deptno字段
dept部门表
| Field | Type | Null | Key | Default | Description |
|---|---|---|---|---|---|
| deptno | int(11) | NO | PRI | null | 部门ID |
| dname | varchar(30) | YES | null | 部门名称 | |
| loc | varchar(30) | YES | null | 部门地址 |
部门表dept表deptno作为主键
INSERT INTO mktest.dept (deptno, dname, loc) VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO mktest.dept (deptno, dname, loc) VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO mktest.dept (deptno, dname, loc) VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO mktest.dept (deptno, dname, loc) VALUES (40, 'OPERATIONS', 'BOSTON');
2) 练习
-- 查询姓名中包含了s的员工信息
select * from mktest.emp where ename like '%s%';
-- 查询30号部门工资最高的前三条记录
select * from mktest.emp where deptno=30 order by sal desc limit 3;
-- 查询20号部门工资最高的员工信息
select * from mktest.emp where deptno=20 and sal=(select max(sal) from mktest.emp where deptno=20);
-- 查询入职时间超过35年的员工的信息
select *,year(now())-year(hiredate) hireAge from mktest.emp where year(now())-year(hiredate)>35;
-- 查询在当月倒数第二天入职的员工信息 (DATA_SUB求日期的前一天，LAST_DAY求月底最后一天日期)
select * from mktest.emp where date(hiredate)=DATE_SUB(LAST_DAY(hiredate),interval 1 day);
-- 查询每个部门在当月倒数第二天入职的员工信息
select * from mktest.emp where date(hiredate)=DATE_SUB(LAST_DAY(hiredate),interval 1 day) group by deptno;
-- 统计每个部门中工资超过1000的员工的各个部门的平均工资
select *,avg(sal) from mktest.emp where sal>1000 group by deptno;
-- 查询平均工资超过2000的部门编号和平均工资
select deptno,avg(sal) from mktest.emp group by deptno having avg(sal)>2000;
-- 查询员工编号为7788的员工所在部门的名称 (此题是外连接，属于高级查询，下节将提到)
select ep.empno,dp.dname from mktest.emp ep left join mktest.dept dp on ep.deptno = dp.deptno where ep.empno=7788;
提示：下一章节：基本函数，之后第4章才会涉及到高级查询。
