此帖子为试牛刀，做笔记练习的，至于文章用到的数据库是mysql，表emp是针对视频中摘出的进行学习的数据库表；
上表emp：
071201.png
现针对上面给出的表进行单表的查询操作，现将类似的操作作如下的详细整理：
- 查询出部门编号为30的所有员工
select ename from emp where deptno=30;
mysql> select ename from emp where deptno=30;
+-----------+
| ename |
+-----------+
| 黛绮丝 |
| 殷天正 |
| 谢逊 |
| 关羽 |
| 韦一笑 |
| 陈普 |
+-----------+
6 rows in set (0.00 sec)
- 所有销售员的姓名、编号和部门编号。
select ename,empno,deptno from emp where job='销售员';
mysql> select ename,empno,deptno from emp where job='销售员';
+-----------+-------+--------+
| ename | empno | deptno |
+-----------+-------+--------+
| 黛绮丝 | 1002 | 30 |
| 殷天正 | 1003 | 30 |
| 谢逊 | 1005 | 30 |
| 韦一笑 | 1010 | 30 |
+-----------+-------+--------+
4 rows in set (0.00 sec)
- 找出奖金高于工资的员工。
select ename from emp where COMM>sal;
mysql> select ename from emp where COMM>sal;
+--------+
| ename |
+--------+
| 谢逊 |
| 张三 |
+--------+
2 rows in set (0.00 sec)
- 找出奖金高于工资60%的员工。//关系运算的使用 > < = !=
select ename from emp where comm>sal*0.6;
select ename from emp where (COMM/sal)>0.6;
mysql> select ename from emp where (COMM/sal)>0.6;
+--------+
| ename |
+--------+
| 谢逊 |
| 张三 |
+--------+
2 rows in set (0.00 sec)
mysql> select ename from emp where comm>sal*0.6;
+--------+
| ename |
+--------+
| 谢逊 |
| 张三 |
+--------+
2 rows in set (0.00 sec)
- 找出部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料。//与连接符and
select * from emp where deptno=10 and job="经理";
select * from emp where deptno=20 and job='销售员';
select * from emp where (deptno=10 and job="经理") or (deptno=20 and job='销售员');
mysql> select * from emp where deptno=10 and job="经理";
+-------+--------+--------+------+------------+-------+------+--------+
| empno | ename | job | mgr | hiredate | sal | COMM | deptno |
+-------+--------+--------+------+------------+-------+------+--------+
| 1007 | 张飞 | 经理 | 1009 | 2001-09-01 | 24500 | NULL | 10 |
+-------+--------+--------+------+------------+-------+------+--------+
1 row in set (0.00 sec)
mysql> select * from emp where deptno=20 and job='销售员';
Empty set (0.00 sec)
mysql> select * from emp where (deptno=10 and job="经理") or (deptno=20 and job='销售员');
+-------+--------+--------+------+------------+-------+------+--------+
| empno | ename | job | mgr | hiredate | sal | COMM | deptno |
+-------+--------+--------+------+------------+-------+------+--------+
| 1007 | 张飞 | 经理 | 1009 | 2001-09-01 | 24500 | NULL | 10 |
+-------+--------+--------+------+------------+-------+------+--------+
1 row in set (0.00 sec)
- 找出部门编号为10中所有经理，部门编号为20中所有销售员，还有即不是经理又不是销售员但其工资大或等于20000的所有员工详细资料。//或连接符or
select * from emp where (deptno=10 and job='经理') or (deptno = 20 and job='销售员') or (job not in ('经理','销售员') and sal >= 20000);
mysql> select * from emp where (deptno=10 and job='经理') or (deptno = 20 and job='销售员') or (job not in ('经理','销售员') and sal >= 20000);
+-------+-----------+-----------+------+------------+-------+------+--------+
| empno | ename | job | mgr | hiredate | sal | COMM | deptno |
+-------+-----------+-----------+------+------------+-------+------+--------+
| 1007 | 张飞 | 经理 | 1009 | 2001-09-01 | 24500 | NULL | 10 |
| 1008 | 诸葛亮 | 分析师 | 1004 | 2007-04-19 | 30000 | NULL | 20 |
| 1009 | 曾阿牛 | 董事长 | NULL | 2001-11-17 | 50000 | NULL | 10 |
| 1013 | 庞统 | 分析师 | 1004 | 2001-12-03 | 30000 | NULL | 20 |
+-------+-----------+-----------+------+------------+-------+------+--------+
4 rows in set (0.00 sec)
- 无奖金或奖金低于1000的员工。
select * from emp where comm<1000 or comm is null;//判断为空关键字is null
mysql> select * from emp where comm<1000 or comm is null;
+-------+-----------+-----------+------+------------+-------+------+--------+
| empno | ename | job | mgr | hiredate | sal | COMM | deptno |
+-------+-----------+-----------+------+------------+-------+------+--------+
| 1001 | 甘宁 | 文员 | 1013 | 2000-12-17 | 8000 | NULL | 20 |
| 1004 | 刘备 | 经理 | 1009 | 2001-04-21 | 29750 | NULL | 20 |
| 1006 | 关羽 | 经理 | 1009 | 2001-05-01 | 28500 | NULL | 30 |
| 1007 | 张飞 | 经理 | 1009 | 2001-09-01 | 24500 | NULL | 10 |
| 1008 | 诸葛亮 | 分析师 | 1004 | 2007-04-19 | 30000 | NULL | 20 |
| 1009 | 曾阿牛 | 董事长 | NULL | 2001-11-17 | 50000 | NULL | 10 |
| 1010 | 韦一笑 | 销售员 | 1006 | 2001-09-08 | 15000 | 0 | 30 |
| 1011 | 周泰 | 文员 | 1008 | 2007-05-23 | 11000 | NULL | 20 |
| 1012 | 陈普 | 文员 | 1006 | 2001-12-03 | 9500 | NULL | 30 |
| 1013 | 庞统 | 分析师 | 1004 | 2001-12-03 | 30000 | NULL | 20 |
| 1014 | 黄盖 | 文员 | 1007 | 2002-01-23 | 13000 | NULL | 10 |
+-------+-----------+-----------+------+------------+-------+------+--------+
11 rows in set (0.00 sec)
- 查询名字由三个字组成的员工。
select * from emp where ename like '___';
mysql> select * from emp where ename like '___';
+-------+-----------+-----------+------+------------+-------+------+--------+
| empno | ename | job | mgr | hiredate | sal | COMM | deptno |
+-------+-----------+-----------+------+------------+-------+------+--------+
| 1002 | 黛绮丝 | 销售员 | 1006 | 2001-02-20 | 16000 | 3000 | 30 |
| 1003 | 殷天正 | 销售员 | 1006 | 2001-02-22 | 12500 | 5000 | 30 |
| 1008 | 诸葛亮 | 分析师 | 1004 | 2007-04-19 | 30000 | NULL | 20 |
| 1009 | 曾阿牛 | 董事长 | NULL | 2001-11-17 | 50000 | NULL | 10 |
| 1010 | 韦一笑 | 销售员 | 1006 | 2001-09-08 | 15000 | 0 | 30 |
+-------+-----------+-----------+------+------------+-------+------+--------+
5 rows in set (0.00 sec)
10.查询2000年入职的员工。
select * from emp where hiredate like '2000-%';
mysql> select * from emp where hiredate like '2000-%'
-> ;
+-------+--------+--------+------+------------+------+------+--------+
| empno | ename | job | mgr | hiredate | sal | COMM | deptno |
+-------+--------+--------+------+------------+------+------+--------+
| 1001 | 甘宁 | 文员 | 1013 | 2000-12-17 | 8000 | NULL | 20 |
+-------+--------+--------+------+------------+------+------+--------+
1 row in set (0.00 sec)
- 查询所有员工详细信息，用编号升序排序
select * from emp order by empno asc;
mysql> select * from emp order by empno asc;
+-------+-----------+-----------+------+------------+-------+-------+--------+
| empno | ename | job | mgr | hiredate | sal | COMM | deptno |
+-------+-----------+-----------+------+------------+-------+-------+--------+
| 1001 | 甘宁 | 文员 | 1013 | 2000-12-17 | 8000 | NULL | 20 |
| 1002 | 黛绮丝 | 销售员 | 1006 | 2001-02-20 | 16000 | 3000 | 30 |
| 1003 | 殷天正 | 销售员 | 1006 | 2001-02-22 | 12500 | 5000 | 30 |
| 1004 | 刘备 | 经理 | 1009 | 2001-04-21 | 29750 | NULL | 20 |
| 1005 | 谢逊 | 销售员 | 1006 | 2001-09-28 | 12500 | 14000 | 30 |
| 1006 | 关羽 | 经理 | 1009 | 2001-05-01 | 28500 | NULL | 30 |
| 1007 | 张飞 | 经理 | 1009 | 2001-09-01 | 24500 | NULL | 10 |
| 1008 | 诸葛亮 | 分析师 | 1004 | 2007-04-19 | 30000 | NULL | 20 |
| 1009 | 曾阿牛 | 董事长 | NULL | 2001-11-17 | 50000 | NULL | 10 |
| 1010 | 韦一笑 | 销售员 | 1006 | 2001-09-08 | 15000 | 0 | 30 |
| 1011 | 周泰 | 文员 | 1008 | 2007-05-23 | 11000 | NULL | 20 |
| 1012 | 陈普 | 文员 | 1006 | 2001-12-03 | 9500 | NULL | 30 |
| 1013 | 庞统 | 分析师 | 1004 | 2001-12-03 | 30000 | NULL | 20 |
| 1014 | 黄盖 | 文员 | 1007 | 2002-01-23 | 13000 | NULL | 10 |
| 1015 | 张三 | 保洁员 | 1001 | 2013-05-01 | 8500 | 50000 | 50 |
+-------+-----------+-----------+------+------------+-------+-------+--------+
15 rows in set (0.00 sec)
- 查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序
select * from emp order by sal desc,hiredate asc;
mysql> select * from emp order by sal desc,hiredate asc;
+-------+-----------+-----------+------+------------+-------+-------+--------+
| empno | ename | job | mgr | hiredate | sal | COMM | deptno |
+-------+-----------+-----------+------+------------+-------+-------+--------+
| 1009 | 曾阿牛 | 董事长 | NULL | 2001-11-17 | 50000 | NULL | 10 |
| 1013 | 庞统 | 分析师 | 1004 | 2001-12-03 | 30000 | NULL | 20 |
| 1008 | 诸葛亮 | 分析师 | 1004 | 2007-04-19 | 30000 | NULL | 20 |
| 1004 | 刘备 | 经理 | 1009 | 2001-04-21 | 29750 | NULL | 20 |
| 1006 | 关羽 | 经理 | 1009 | 2001-05-01 | 28500 | NULL | 30 |
| 1007 | 张飞 | 经理 | 1009 | 2001-09-01 | 24500 | NULL | 10 |
| 1002 | 黛绮丝 | 销售员 | 1006 | 2001-02-20 | 16000 | 3000 | 30 |
| 1010 | 韦一笑 | 销售员 | 1006 | 2001-09-08 | 15000 | 0 | 30 |
| 1014 | 黄盖 | 文员 | 1007 | 2002-01-23 | 13000 | NULL | 10 |
| 1003 | 殷天正 | 销售员 | 1006 | 2001-02-22 | 12500 | 5000 | 30 |
| 1005 | 谢逊 | 销售员 | 1006 | 2001-09-28 | 12500 | 14000 | 30 |
| 1011 | 周泰 | 文员 | 1008 | 2007-05-23 | 11000 | NULL | 20 |
| 1012 | 陈普 | 文员 | 1006 | 2001-12-03 | 9500 | NULL | 30 |
| 1015 | 张三 | 保洁员 | 1001 | 2013-05-01 | 8500 | 50000 | 50 |
| 1001 | 甘宁 | 文员 | 1013 | 2000-12-17 | 8000 | NULL | 20 |
+-------+-----------+-----------+------+------------+-------+-------+--------+
15 rows in set (0.00 sec)
- 查询每个部门的平均工资
select deptno ,avg(sal+ ifnull('comm',0)) '部门平均工资' from emp group by deptno;
mysql> select deptno ,avg(sal+ ifnull('comm',0)) '部门平均工资' from emp group by deptno;
+--------+--------------------+
| deptno | 部门平均工资 |
+--------+--------------------+
| 20 | 21750 |
| 30 | 15666.666666666666 |
| 10 | 29166.666666666668 |
| 50 | 8500 |
+--------+--------------------+
4 rows in set (0.00 sec)
- 查询每个部门的雇员数量。
select deptno,count(*) '人数' from emp group by deptno;
mysql> select deptno,count(*) '人数' from emp group by deptno;
+--------+--------+
| deptno | 人数 |
+--------+--------+
| 20 | 5 |
| 30 | 6 |
| 10 | 3 |
| 50 | 1 |
+--------+--------+
4 rows in set (0.00 sec)
- 查询每种工作的最高工资、最低工资、人数
select job,min(sal) 最低工资,max(sal) 最高工资,count(*) 人数 from emp group by job;
mysql> select job,min(sal) 最低工资,max(sal) 最高工资,count(*) 人数 from emp group by job;
+-----------+--------------+--------------+--------+
| job | 最低工资 | 最高工资 | 人数 |
+-----------+--------------+--------------+--------+
| 文员 | 8000 | 13000 | 4 |
| 销售员 | 12500 | 16000 | 4 |
| 经理 | 24500 | 29750 | 3 |
| 分析师 | 30000 | 30000 | 2 |
| 董事长 | 50000 | 50000 | 1 |
| 保洁员 | 8500 | 8500 | 1 |
+-----------+--------------+--------------+--------+
6 rows in set (0.00 sec)
至此单表查询的练习就算告一段落，准备挑战多表查询！
网友评论