一、创建表语句
CREATE TABLE `dept` (
`deptno` varchar(11) DEFAULT NULL,
`dname` varchar(255) DEFAULT NULL,
`loc` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `test01`.`emp`(`empno`, `ename`, `job`, `mgr`, `sal`, `deptno`) VALUES ('7369', 'smith', 'clerk', '7902', 800, 20);
INSERT INTO `test01`.`emp`(`empno`, `ename`, `job`, `mgr`, `sal`, `deptno`) VALUES ('7499', 'allen', 'salesman', '7698', 1600, 30);
INSERT INTO `test01`.`emp`(`empno`, `ename`, `job`, `mgr`, `sal`, `deptno`) VALUES ('7521', 'ward', 'manage', '7698', 1250, 30);
INSERT INTO `test01`.`emp`(`empno`, `ename`, `job`, `mgr`, `sal`, `deptno`) VALUES ('7566', 'martin', 'salesman', '7839', 2975, 20);
INSERT INTO `test01`.`emp`(`empno`, `ename`, `job`, `mgr`, `sal`, `deptno`) VALUES ('7698', 'blake', 'manage', '7839', 2850, 30);
INSERT INTO `test01`.`emp`(`empno`, `ename`, `job`, `mgr`, `sal`, `deptno`) VALUES ('7788', 'scott', 'analyst', NULL, NULL, 20);
INSERT INTO `test01`.`emp`(`empno`, `ename`, `job`, `mgr`, `sal`, `deptno`) VALUES ('7839', 'king', 'president', NULL, 5000, 10);
INSERT INTO `test01`.`emp`(`empno`, `ename`, `job`, `mgr`, `sal`, `deptno`) VALUES ('7844', 'turner', 'salesman', NULL, NULL, 20);
INSERT INTO `test01`.`emp`(`empno`, `ename`, `job`, `mgr`, `sal`, `deptno`) VALUES ('7876', 'adams', 'clerk', NULL, NULL, 30);
INSERT INTO `test01`.`emp`(`empno`, `ename`, `job`, `mgr`, `sal`, `deptno`) VALUES ('7900', 'james', 'analyst', NULL, NULL, 20);
INSERT INTO `test01`.`emp`(`empno`, `ename`, `job`, `mgr`, `sal`, `deptno`) VALUES ('7902', 'frod', 'analyst', NULL, NULL, 10);
CREATE TABLE `dept` (
`deptno` varchar(11) DEFAULT NULL,
`dname` varchar(255) DEFAULT NULL,
`loc` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `dept` VALUES ('10 ', 'accounting ', 'new york ');
INSERT INTO `dept` VALUES ('20', 'research', 'dallas');
INSERT INTO `dept` VALUES ('30', 'sales ', 'chicago ');
INSERT INTO `dept` VALUES ('40 ', 'operat ', 'boston ');
二、表的基础查询
1、 表的基础查询
select * from emp;
select empno"员工编号",ename"员工姓名",job"员工编号",mgr"上级编号",hiredate"入职日期",sal"员工工资",comm"奖金",deptno"部门编号"from emp;
2、 distinct
select distinct deptno from emp;消除重复行,统计有几个部门编号
3、 count
1.1计算有多少个员工
select count(*) deptno from emp;
1.2按job消除重复行:
select distinct job from emp;
1.3按job,deptno消除重复行:
select distinct job,deptno from emp;
1.3怎么统计每个部门的工种各有几种。
select count(temp.job),temp.deptno from(select distinct job,deptno from emp) temp group by temp.deptno;
4、where、 like、in、is 、
select * from emp where sal>=2000 and sal<=2500
select ename,sal from emp where ename like 's%'；
select ename,sal from emp where ename like '__O%'；_表示两个字符。
select * from emp where empno in (7369,7844);
select * from emp where mgr is null ;
select * from emp where (sal>500 or job='manager') and ename like 'J%';
5、order by
select * from emp order by sal asc;也可以显示一行。asc默认升序，desc降序。
select ename,sal12"年薪" from emp order by "年薪";使用列的别名排序(别名需要使用""号圈中)
select deptno,sal from emp order by deptno,sal desc ;先按depto升序，再按sal降序。
6、聚类函数max(最大),min(最小),avg(平均),sum(和),count(统计)
显示最低工资并显示出雇员名字：
select ename"姓名",sal"工资" from emp where sal=(select min(sal) from emp)
显示所有员工的平均工资和工资总和:
select avg(sal), sum(sal) from emp;
把高于平均工资的雇员的名字和他的工资显示出来:
select ename,sal from emp where sal>(select avg(sal) from emp);
请显示工资最高的员工的名字、工作岗位和工资：
select ename,job,sal from emp where sal=(select max(sal)from emp);
请显示工资高于平均工资的员工信息,并显示平均工资(效率不高):
select ename,job,sal,(select avg(sal) from emp)"平均工资"from emp where sal>(select avg(sal) from emp);
二、表的复杂查询
- 1group by用于对查询的结果分组统计
- 2having子句用于限制分组显示结果
- 3--group by和having子句使用(having与group by结合使用，可以对分组后的查询结果进行筛选)
- 4、分组函数只能出现在选择列表，having、order by,顺序是group by，having，order by
- 5、在选择列中如果有列、表达式、和分组函数，那么这些列和表达式必需有一个出现在group by子句中，否则就会出错
如：select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)<2000
这里deptno就一定要出现在group by中
1. 如何显示每个部门的平均工资和最高工资:
select deptno"部门号",avg(sal)"平均工资",max(sal)"最高工资",min(sal)"最低工资" from emp group by deptno;
+--------+-------------+----------+----------+
| 部门号 | 平均工资 | 最高工资 | 最低工资 |
+--------+-------------+----------+----------+
| 10 | 2916.666667 | 5000.00 | 1300.00 |
| 20 | 2175.000000 | 3000.00 | 800.00 |
| 30 | 1566.666667 | 2850.00 | 950.00 |
+--------+-------------+----------+----------+
2. 如何显示每个部门的平均工资和最高工资并显示部门名称(多表查询)：
select emp.deptno"部门号",avg(sal)"平均工资",max(sal)"最高工资",dept.dname"部门名称" from emp,dept where emp.deptno=dept.deptno group by emp.deptno,dept.dname;
+--------+-------------+----------+------------+
| 部门号 | 平均工资 | 最高工资 | 部门名称 |
+--------+-------------+----------+------------+
| 10 | 2916.666667 | 5000.00 | accounting |
| 20 | 2175.000000 | 3000.00 | research |
| 30 | 1566.666667 | 2850.00 | sales |
+--------+-------------+----------+------------+
select emp.deptno"部门号",avg(sal)"平均工资",max(sal)"最高工资",dept.dname"部门名称" from emp,dept where emp.deptno=dept.deptno group by emp.deptno;
+--------+-------------+----------+------------+
| 部门号 | 平均工资 | 最高工资 | 部门名称 |
+--------+-------------+----------+------------+
| 10 | 2916.666667 | 5000.00 | accounting |
| 20 | 2175.000000 | 3000.00 | research |
| 30 | 1566.666667 | 2850.00 | sales |
+--------+-------------+----------+------------+
3.显示每个部门的每种岗位的平均工资和最低工资：顺序group by，having，order by
select deptno"部门号",job"岗位",avg(sal)"平均工资",min(sal)"最低工资" from emp group by deptno,job order by deptno
+--------+-----------+-------------+----------+
| 部门号 | 岗位 | 平均工资 | 最低工资 |
+--------+-----------+-------------+----------+
| 10 | clerk | 1300.000000 | 1300.00 |
| 10 | manager | 2450.000000 | 2450.00 |
| 10 | president | 5000.000000 | 5000.00 |
| 20 | analyst | 3000.000000 | 3000.00 |
| 20 | clerk | 950.000000 | 800.00 |
| 20 | manager | 2975.000000 | 2975.00 |
| 30 | clerk | 950.000000 | 950.00 |
| 30 | manager | 2850.000000 | 2850.00 |
| 30 | salesman | 1400.000000 | 1250.00 |
+--------+-----------+-------------+----------+
select deptno"部门号",job"岗位",avg(sal)"平均工资",min(sal)"最低工资" from emp group by deptno order by deptno;
有问题，必须要按部门号和工作分。这里的后面的group by后面必须要有，除了聚类函数之外的字段。
4. 显示平均工资低于2000的部门号和它的平均工资：顺序group by，having，order by
select deptno"部门号",avg(sal)"平均工资" from emp group by deptno having avg(sal)<2000;
+--------+-------------+
| 部门号 | 平均工资 |
+--------+-------------+
| 30 | 1566.666667 |
+--------+-------------+
select deptno"部门号",avg(sal)"平均工资" from emp group by deptno ;
+--------+-------------+
| 部门号 | 平均工资 |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
+--------+-------------+
5. 显示平均工资大于2000的部门号和它的平均工资并按升序排列：
select deptno"部门号",avg(sal)"平均工资" from emp group by deptno having avg(sal)<2000
+--------+-------------+----------+----------+
| 部门号 | 平均工资 | 最高工资 | 最低工资 |
+--------+-------------+----------+----------+
| 10 | 2916.666667 | 5000.00 | 1300.00 |
| 20 | 2175.000000 | 3000.00 | 800.00 |
| 30 | 1566.666667 | 2850.00 | 950.00 |
+--------+-------------+----------+----------+
6.显示部门号为 20部门的平均工资
select deptno"部门号",avg(sal)"平均工资" from emp group by deptno having deptno=20;
+--------+-------------+
| 部门号 | 平均工资 |
+--------+-------------+
| 20 | 2175.000000 |
+--------+-------------+
7.显示平均工资大于2000的部门号和它的平均工资并按升序排列
select deptno"部门号",avg(sal)"平均工资" from emp group by deptno having avg(sal)>2000 order by "平均工资"；
+--------+-------------+
| 部门号 | 平均工资 |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
+--------+-------------+
多表查询:注意，如果多张表都有相同名字的字段，则需要带表名(别名)
8.显示sales部门位置和其员工的姓名：
select emp.ename"员工姓名",dept.dname"部门名称",dept.loc"部门所在地"from emp,dept where dept.dname='sales' and emp.deptno=dept.deptno;
+----------+----------+------------+
| 员工姓名 | 部门名称 | 部门所在地 |
+----------+----------+------------+
| allen | sales | chicago |
| ward | sales | chicago |
| martin | sales | chicago |
| blake | sales | chicago |
| turner | sales | chicago |
| james | sales | chicago |
+----------+----------+------------+
9.显示雇员名，雇员工资及所在部门的名字:
select emp.ename,dept.dname,dept.loc from emp,dept
where dept.dname='sales' and emp.deptno=dept.deptno;
+--------+-------+---------+
| ename | dname | loc |
+--------+-------+---------+
| allen | sales | chicago |
| ward | sales | chicago |
| martin | sales | chicago |
| blake | sales | chicago |
| turner | sales | chicago |
| james | sales | chicago |
+--------+-------+---------+
10.如何显示部门号为10的部门名、员工名和工资:
select dept.dname,emp.ename,emp.sal from dept,emp where dept.deptno=10 and dept.deptno=emp.deptno;
+------------+--------+---------+
| dname | ename | sal |
+------------+--------+---------+
| accounting | clark | 2450.00 |
| accounting | king | 5000.00 |
| accounting | miller | 1300.00 |
+------------+--------+---------+
select dept.dname,emp.ename,emp.sal from dept,emp where emp.deptno=10 and dept.deptno=emp.deptno;显示结果是一样的。
11.显示雇员名、雇员工资及所在部门的名字并按部门排序:
select e.ename"雇员名字",e.sal"工资",d.dname"部门名称" from emp e,dept d where e.deptno=d.deptno order by d.dname;
+----------+---------+------------+
| 雇员名字 | 工资 | 部门名称 |
+----------+---------+------------+
| king | 5000.00 | accounting |
| clark | 2450.00 | accounting |
| miller | 1300.00 | accounting |
| smith | 800.00 | research |
| ford | 3000.00 | research |
| jones | 2975.00 | research |
| scott | 3000.00 | research |
| adams | 1100.00 | research |
| martin | 1250.00 | sales |
| james | 950.00 | sales |
| ward | 1250.00 | sales |
| allen | 1600.00 | sales |
| blake | 2850.00 | sales |
| turner | 1500.00 | sales |
+----------+---------+------------+
字段别名更多的意义是解决字段名的重复，如一个表字段被查询两次或更多次时：
SELECT username AS name,username,email FROM user
或者两个及更多表进行查询，有相同的返回字段时。别名的使用。
select emp.ename"雇员名字",emp.sal"工资",dept.dname"部门名称" from emp ,dept where emp.deptno=dept.deptno order by dept.dname;
//没用别名查询结果一样
+----------+---------+------------+
| 雇员名字 | 工资 | 部门名称 |
+----------+---------+------------+
| king | 5000.00 | accounting |
| clark | 2450.00 | accounting |
| miller | 1300.00 | accounting |
| smith | 800.00 | research |
| ford | 3000.00 | research |
| jones | 2975.00 | research |
| scott | 3000.00 | research |
| adams | 1100.00 | research |
| martin | 1250.00 | sales |
| james | 950.00 | sales |
| ward | 1250.00 | sales |
| allen | 1600.00 | sales |
| blake | 2850.00 | sales |
| turner | 1500.00 | sales |
+----------+---------+------------+
12 显示某个员工的上级领导的姓名，比如显示"ford"的上级：
select (select ename from emp where ename='ford')"员工姓名",ename"上级领导" from emp
where empno=(select mgr from emp where ename='ford')；
+----------+----------+
| 员工姓名 | 上级领导 |
+----------+----------+
| ford | jones |
+----------+----------+
三、表的连接查询
1显示公司每个员工姓名和他的上级的名字--分析，把emp表看成两张表分别是worker/boss--外连接(左外连接、右外连接)
select worker.ename"员工名字",boss.ename"领导名字" from emp worker,emp boss where worker.mgr=boss.empno;
+----------+----------+
| 员工名字 | 领导名字 |
+----------+----------+
| smith | ford |
| allen | blake |
| ward | blake |
| jones | king |
| martin | blake |
| blake | king |
| clark | king |
| scott | jones |
| turner | blake |
| adams | scott |
| james | blake |
| ford | jones |
| miller | clark |
+----------+----------+
四、 表的复杂查询
1、--子查询,如何显示与smith同一部门的所有员工:
select deptno"部门号",ename"员工名字" from emp where deptno=(select deptno from emp where ename='smith');
+--------+----------+
| 部门号 | 员工名字 |
+--------+----------+
| 20 | smith |
| 20 | jones |
| 20 | scott |
| 20 | adams |
| 20 | ford |
+--------+----------+
2、如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号
第一步：select distinct job from emp where deptno=10;把部门号为10的工作消除重复。
+-----------+
| job |
+-----------+
| manager |
| president |
| clerk |
+-----------+
第二步：select * from emp where job in (select distinct job from emp where deptno=10);
+-------+--------+-----------+------+---------------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+---------------------+---------+------+--------+
| 7369 | smith | clerk | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 7566 | jones | manager | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 7698 | blake | manager | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 7782 | clark | manager | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 7839 | king | president | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 7876 | adams | clerk | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
| 7900 | james | clerk | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
| 7934 | miller | clerk | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+---------------------+---------+------+--------+
3、如何显示高于部门平均工资的员工名字、薪水、部门的平均工资:
- 1首先要知道各个部门的平均工资;select avg(sal)"部门平均工资",deptno from emp group by deptno
- 2把上面的查询结果当作一个临时表对待,这里必须字段别名。字段别名的使用：SELECT username name FROM
user"username"是原字段名称, 这个"name"就是别名 - 3在from子句中使用子查询这里需要说明的当在from子句中使用子查询时，该子查询会被作为一个临时表来对待，当在from子句中使用子查询时，必需给子查询指定别名。
select e.ename"员工名字",e.sal"薪水",temp.myavg"部门平均工资",e.deptno from emp e,(select avg(sal) myavg,deptno from emp group by deptno) temp where e.deptno=temp.deptno and e.sal>temp.myavg
+----------+---------+--------------+--------+
| 员工名字 | 薪水 | 部门平均工资 | deptno |
+----------+---------+--------------+--------+
| allen | 1600.00 | 1566.666667 | 30 |
| jones | 2975.00 | 2175.000000 | 20 |
| blake | 2850.00 | 1566.666667 | 30 |
| scott | 3000.00 | 2175.000000 | 20 |
| king | 5000.00 | 2916.666667 | 10 |
| ford | 3000.00 | 2175.000000 | 20 |
+----------+---------+--------------+--------+
按雇员的id号升序取出
- 1select * from tablename order by orderfield desc/asc limit position， counter；
- 2SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15 ,注意，10为偏移量
- 3为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：
SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last. - 4如果只给定一个参数，它表示返回最大的记录行数目：
mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行 //也就是说，LIMIT n 等价于 LIMIT 0,n。
如果你想得到最后几条数据可以多加个 order by id desc
4、请显示第5个到第10个入职的雇员信息(按照入职的时间先后顺序查找)
1、显示第1个到第4个入职的雇员（top后的数表示要取出几条记录）：
select * from emp order by hiredate ;
+-------+--------+-----------+------+---------------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
| 7369 | smith | clerk | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 7499 | allen | salesman | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 |
| 7521 | ward | salesman | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |
| 7566 | jones | manager | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 7698 | blake | manager | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 7782 | clark | manager | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 7844 | turner | salesman | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
| 7654 | martin | salesman | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
| 7839 | king | president | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 7900 | james | clerk | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
| 7902 | ford | analyst | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 7934 | miller | clerk | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
| 7788 | scott | analyst | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 7876 | adams | clerk | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
select * from emp order by hiredate limit 0,4;
+-------+--------+---------+------+---------------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+---------+------+---------------------+---------+------+--------+
| 7902 | ford | analyst | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 7934 | miller | clerk | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
| 7788 | scott | analyst | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
+-------+--------+---------+------+---------------------+---------+------+--------+
5、select top 6 * from emp where empno not in(select top 4 empno from emp order by hiredate) order by hiredate
--请显示第11个到13个入职的雇员信息：10为起始位置，3为偏移量。
select * from emp order by hiredate limit 10,3;
--请显示第5个到9个入职的雇员信息(按薪水高低排序)
select * from emp order by sal desc limit 4,4;
+-------+--------+----------+------+---------------------+---------+--------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+----------+------+---------------------+---------+--------+--------+
| 7698 | blake | manager | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 7782 | clark | manager | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 7499 | allen | salesman | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 |
| 7844 | turner | salesman | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
+-------+--------+----------+------+---------------------+---------+--------+--------+
6、显示公司每个员工和他的上级领导的名字，没有上级领导的也要显示出来
--左外连接：指如果左边的表记录全部显示，如果没有匹配的记录，就用null填写
select worker.ename"员工名字",boss.ename"领导名字" from emp worker left join emp boss on worker.mgr=boss.empno;
+----------+----------+
| 员工名字 | 领导名字 |
+----------+----------+
| smith | ford |
| allen | blake |
| ward | blake |
| jones | king |
| martin | blake |
| blake | king |
| clark | king |
| scott | jones |
| king | NULL |
| turner | blake |
| adams | scott |
| james | blake |
| ford | jones |
| miller | clark |
+----------+----------+
7、--右外连接：指如果右边的表记录全部显示，如果没有匹配的记录，就用null填写
select worker.ename"员工名字",boss.ename"领导名字" from emp worker right join emp boss on worker.mgr=boss.empno;
+----------+----------+
| 员工名字 | 领导名字 |
+----------+----------+
| smith | ford |
| allen | blake |
| ward | blake |
| jones | king |
| martin | blake |
| blake | king |
| clark | king |
| scott | jones |
| turner | blake |
| adams | scott |
| james | blake |
| ford | jones |
| miller | clark |
| NULL | smith |
| NULL | allen |
| NULL | ward |
| NULL | martin |
| NULL | turner |
| NULL | adams |
| NULL | james |
| NULL | miller |
+----------+----------+