2023_10_11_MYSQL_DAY_03_笔记_上

 10章 作业题01答案
INSERT INTO class(classid,cname) VALUES(1,'Java1班');
INSERT INTO class(cname,classid) VALUES('Java2班',2);
INSERT INTO class VALUES(3,'Java3班',NULL);
 
10章 作业题02 03 04答案
INSERT INTO student VALUES('A001','张三','男','2005-5-1',100,1);
INSERT INTO student VALUES('A002','MIKE','男','1905-05-06',10,NULL);
INSERT INTO student(xh,xm,sex)  VALUES('A003','JOHN','女' );
 
10章 作业题05答案
UPDATE student
SET sex='女'
WHERE xh='A002';
 
10章 作业题06答案
UPDATE student
SET sex='男',birthday='1980-04-01'
WHERE xh='A003';
 
10章 作业题07答案
做法1
UPDATE student
SET studentcid=3
WHERE studentcid IS NULL;
 
做法2
UPDATE student
SET studentcid=(SELECT classid FROM class WHERE cname='Java3班')
WHERE studentcid IS NULL;
 
 
 
 
 
 
#课堂练习50
#删除语句 多列子查询  ***
DELETE FROM emp_back
WHERE (deptno,sal) IN  (SELECT deptno,AVG(sal) FROM emp GROUP BY deptno);
 
#课堂练习49
#删除语句 使用了子查询
DELETE FROM emp_back
WHERE deptno = (SELECT deptno FROM dept WHERE loc='NEW YORK');
 
#课堂练习48
DELETE FROM emp_back
WHERE mgr=7566;
 
#删除语句 
DELETE FROM emp_back
WHERE job='CLERK';
#课堂练习48
#更新语句 使用了子查询
UPDATE manager
SET sal=sal+500
WHERE deptno IN (SELECT deptno FROM dept WHERE loc='NEW YORK' OR loc='CHICAGO');
 
#课堂练习47
UPDATE manager
SET comm=0.0
WHERE comm IS NULL;
 
#课堂练习46
UPDATE manager
SET job='CLERK'
WHERE deptno=20;
 
 
#更新语句
UPDATE emp
SET deptno=20,comm=1200
WHERE empno=8888;
 
#课堂练习45
CREATE TABLE emp_back AS 
SELECT * FROM emp WHERE 1=0;
 
INSERT INTO emp_back 
SELECT * FROM emp WHERE hiredate > '1982-1-1'
 
#通过子查询插入多行数据
INSERT INTO manager
SELECT * FROM emp WHERE job='MANAGER';
#利用查询语句建立表
CREATE TABLE manager
SELECT * FROM emp WHERE 1=0;
 
#课堂练习44
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
VALUES (8888,'BOB','CLEAK',7788,SYSDATE(),3000,NULL,NULL);
 
#使用日期函数插入数据
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
VALUES (7196,'GREEN','SALESMAN',7782,SYSDATE(),2000,NULL,10);
 
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
VALUES (7197,'GREEN2','SALESMAN',7782,'2023-9-1',2000,NULL,10);
 
#获取当前时间的系统函数
SELECT SYSDATE();
 
#课堂练习43
INSERT INTO dept(deptno,dname) VALUES(90,'MARKET');
#课堂练习42
INSERT INTO dept(loc,deptno,dname) VALUES('SY',80,'HR');
 
#插入语句 方式2
INSERT INTO dept VALUES(201,'宣传部','上海');
#空值的插入
INSERT INTO dept VALUES(203,'后勤部',NULL);
 
 
#插入语句 方式1
INSERT INTO dept(loc,deptno,dname) VALUES('河南新乡',200,'研发部');
#空值的插入
INSERT INTO dept(loc,deptno,dname) VALUES(NULL,202,'服务部');
INSERT INTO dept(deptno,dname) VALUES(205,'小麦部');
 
06章04题作业答案
SELECT e.`ename` ,d.`dname`,e.`sal`,s.`grade`
FROM emp e LEFT OUTER JOIN dept d ON(e.`deptno`=d.`deptno`), salgrade s
WHERE e.`sal` BETWEEN s.`losal` AND s.`hisal` AND s.`grade` > 4
 
 
#在 FROM 子句中使用子查询
SELECT  a.ename, a.sal, a.deptno, b.salavg
FROM    emp a, (SELECT   deptno, AVG(sal) salavg
                FROM     emp
                GROUP BY deptno) b
WHERE   a.deptno = b.deptno  AND    a.sal > b.salavg;
 
#子查询里的空值处理
SELECT    ename
FROM     emp
WHERE  empno NOT IN (SELECT IFNULL (mgr,0) FROM   emp);
 
#多列子查询
SELECT deptno,ename,hiredate 
FROM emp 
WHERE (deptno,hiredate) IN (SELECT deptno ,MIN(hiredate) 
                            FROM emp 
                            GROUP BY deptno);
 
 
#课堂练习41
SELECT ename,job
FROM emp
WHERE job = ANY (SELECT job FROM emp WHERE deptno=10) 
      AND deptno <> 10;
	  
	  
 
#课堂练习40
SELECT ename,hiredate
FROM emp
WHERE hiredate > ALL (SELECT hiredate FROM emp WHERE deptno=10) 
      AND deptno <> 10;
 
#课堂练习39
SELECT ename,hiredate
FROM emp
WHERE hiredate > ANY (SELECT hiredate FROM emp WHERE deptno=10) 
      AND deptno <> 10;
 
#多行子查询 all操作符
SELECT empno, ename, job, sal
FROM     emp
WHERE     sal > ALL (SELECT sal FROM emp WHERE  deptno= 20)
AND    deptno <> 20;
 
SELECT empno, ename, job, sal
FROM     emp
WHERE     sal < ALL (SELECT sal FROM emp WHERE  deptno= 20)
AND    deptno <> 20;
 
#多行子查询 any操作符
SELECT ename,sal,empno
FROM emp
WHERE empno < ANY (SELECT mgr FROM emp ORDER BY mgr DESC);
 
SELECT ename,sal,empno
FROM emp
WHERE empno > ANY (SELECT mgr FROM emp ORDER BY mgr DESC);
 
SELECT ename,sal,empno
FROM emp
WHERE empno = ANY (SELECT mgr FROM emp ORDER BY mgr DESC);
 
 
#多行子查询 in操作符
SELECT ename,sal
FROM emp
WHERE empno IN (SELECT mgr FROM emp);
 
#课堂练习38
SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno
HAVING AVG(sal) > (SELECT AVG(sal) FROM emp WHERE deptno=10);
 
#课堂练习37
SELECT ename,sal,dname
FROM emp e,dept d
WHERE e.`deptno`=d.`deptno`
      AND sal > (SELECT sal FROM emp WHERE ename='SMITH')
      AND d.`loc`='CHICAGO';
 
#课堂练习36
SELECT ename,hiredate
FROM emp
WHERE hiredate = (SELECT MIN(hiredate) FROM emp);
 
 
# HAVING子句中使用子查询
SELECT  deptno, MIN(sal)
FROM    emp
GROUP BY  deptno
HAVING    MIN(sal) > (SELECT MIN(sal) FROM  emp WHERE deptno = 20);
 
 
#子查询中使用组函数
SELECT ename,job,sal
FROM emp
WHERE sal = (SELECT MIN(sal) FROM emp);
 
#单行子查询语句
SELECT ename,job
FROM emp
WHERE job = (SELECT job FROM emp WHERE empno=7369)
     AND sal > (SELECT sal FROM emp WHERE empno=7876);
 
#子查询 单行子查询
SELECT ename
FROM emp
WHERE sal > (SELECT sal FROM emp WHERE ename='JONES');
 
 
#课堂练习35
SELECT ename,hiredate,deptno
FROM emp
LIMIT 0,5; #第1页
 
SELECT ename,hiredate,deptno
FROM emp
LIMIT 5,5; #第2页
 
SELECT ename,hiredate,deptno
FROM emp
LIMIT 10,5; #第3页
 
 
#课堂练习34
SELECT e.deptno,dname,COUNT(*),AVG(sal)
FROM emp e,dept d
WHERE e.`deptno`=d.`deptno`
GROUP BY e.deptno,dname
HAVING COUNT(*) > 2 AND AVG(sal) > 2000
ORDER BY COUNT(*);
 
#七个子句的查询
SELECT job,SUM(sal)
FROM emp
WHERE job NOT LIKE 'SALES%'
GROUP BY job
HAVING SUM(sal) > 5000
ORDER BY SUM(sal)
LIMIT 0,2;
 
#分页查询
SELECT empno,ename,deptno,sal
FROM emp
ORDER BY empno
LIMIT 5,5; #第2页
 
SELECT empno,ename,deptno,sal
FROM emp
ORDER BY empno
LIMIT 0,5; #第1页
 
#课堂练习33
SELECT e.deptno,dname,COUNT(*)
FROM emp e,dept d
WHERE e.`deptno`=d.`deptno`
GROUP BY e.deptno,dname
HAVING COUNT(*) > 2;
 
总结:
1、WHERE子句用来过滤分组之前的记录，不能使用组函数
2、HAVING子句用来过滤分组之后的记录，可以使用组函数
 
#6个子句的查询
SELECT job,SUM(sal)
FROM emp
WHERE job NOT LIKE 'SALES%'
GROUP BY job
HAVING SUM(sal) > 5000
ORDER BY SUM(sal);
 
查询语句执行过程：
1、通过FROM子句中找到需要查询的表；
2、通过WHERE子句进行非分组函数筛选判断；
3、通过GROUP BY子句完成分组操作；
4、通过HAVING子句完成组函数筛选判断；
5、通过SELECT子句选择显示的列或表达式及组函数；
6、通过ORDER BY子句进行排序操作。
#HAVING 子句
SELECT deptno,COUNT(empno) 
FROM emp 
GROUP BY deptno
HAVING COUNT(empno) > 3; 
 
SELECT deptno,MAX(sal)
FROM emp
GROUP BY deptno
HAVING MAX(sal) > 2900;
 
 
#课堂练习32
SELECT COUNT(*),MAX(sal),MIN(sal),d.`loc`
FROM emp e,dept d
WHERE e.`deptno`=d.`deptno` AND d.`loc`='CHICAGO'
GROUP BY d.`loc`;
 
 
 
 
#课堂练习31 自连接
SELECT e2.`empno` 经理编号,e2.`ename` 经理姓名,COUNT(*)
FROM emp e,emp e2
WHERE e.`mgr`=e2.`empno`
GROUP BY e2.`empno`,e2.`ename`;
 
#课堂练习30
SELECT d.`deptno`,d.`dname`,e.`job`,COUNT(empno),MAX(sal),MIN(sal)
FROM emp e,dept d
WHERE e.`deptno`=d.`deptno`
GROUP BY d.`deptno`,d.`dname`,e.`job`;
#多表查询分组查询
SELECT d.`deptno`,d.`dname`,COUNT(empno),MAX(sal)
FROM emp e,dept d
WHERE e.`deptno`=d.`deptno`
GROUP BY d.`deptno`,d.`dname`;
 
 
#按多列分组查询
SELECT deptno,job,SUM(sal)
FROM emp
GROUP BY deptno,job;
#分组子句 
SELECT job,COUNT(empno)
FROM emp
GROUP BY job;
SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno;
 
#课堂练习29
SELECT COUNT(*),MAX(sal),MIN(sal)
FROM emp 
WHERE deptno=30;
 
#课堂练习28
SELECT (sal+IFNULL(comm,0))*12 年收入
FROM emp 
 
#课堂练习27
SELECT COUNT(job), COUNT(DISTINCT job)
FROM emp 
 
 
#课堂练习26
SELECT SUM(sal),AVG(sal)
FROM emp
WHERE deptno=20;
 
#除COUNT(*)之外，其它所有分组函数都会忽略列中的空值，然后再进行运算; IFNULL==空值处理函数
SELECT AVG(comm),AVG(IFNULL(comm,0))
FROM emp;
 
#空值处理函数 IFNULL(comm,0)
SELECT ename 员工姓名,sal 工资收入,comm 奖金收入,sal+IFNULL(comm,0) 总收入 
FROM emp;
 
SELECT COUNT(deptno),COUNT(DISTINCT deptno)
FROM emp
 
#五个组函数--聚合函数
SELECT MIN(hiredate),MAX(hiredate),MIN(sal),MAX(sal),AVG(sal),SUM(sal),COUNT(*)
FROM emp;
 
#课堂练习25
SELECT e.empno,e.ename,e.`job`,d.deptno,d.`loc`
FROM emp e,dept d 
WHERE e.deptno = d.deptno AND (d.`loc`='CHICAGO' OR job='MANAGER');
 
#联合查询 UNION 去除重复数据
SELECT e.empno,e.ename,d.deptno,d.dname 
FROM emp e LEFT OUTER JOIN dept d ON(e.deptno = d.deptno)
UNION 
SELECT e.empno,e.ename,d.deptno,d.dname 
FROM emp e RIGHT OUTER JOIN dept d ON(e.deptno = d.deptno);
 
#联合查询  UNION ALL 保留重复数据
SELECT e.empno,e.ename,d.deptno,d.dname 
FROM emp e LEFT OUTER JOIN dept d ON(e.deptno = d.deptno)
UNION ALL
SELECT e.empno,e.ename,d.deptno,d.dname 
FROM emp e RIGHT OUTER JOIN dept d ON(e.deptno = d.deptno);
 
 
#课堂练习24 右外连接  题目?   没有下属的也要查出来
SELECT e.`ename` ,e2.`ename`
FROM emp e RIGHT OUTER JOIN emp e2 ON(e.`mgr`=e2.`empno`);
 
 
#课堂练习23 左外连接  没有领导的也要查出来
SELECT e.`ename` ,e2.`ename`
FROM emp e LEFT OUTER JOIN emp e2 ON(e.`mgr`=e2.`empno`);
 
 
#课堂练习22
SELECT ename,dname,hiredate
FROM emp e,dept d
WHERE e.`deptno`=d.`deptno` AND e.`hiredate` > '1980-5-1'
 
#课堂练习21 笛卡尔积数据
SELECT ename,dname
FROM emp,dept
 
 
#右外连接
SELECT e.`ename` ,e.`deptno`,d.`loc`
FROM emp e RIGHT OUTER JOIN dept d ON(e.`deptno`=d.`deptno`);
 
 
#左外连接
SELECT e.`ename` ,e.`deptno`,d.`loc`
FROM emp e LEFT OUTER JOIN dept d ON(e.`deptno`=d.`deptno`);
 
#等值连接 自然连接
SELECT e.`ename` ,e.`deptno`,d.`loc`
FROM emp e,dept d
WHERE e.`deptno`=d.`deptno`
 
 
#课堂练习20 自连接
SELECT e.`ename` 员工姓名,e.`empno` 员工编号,e2.`ename` 经理姓名,e2.`empno` 经理编号
FROM emp e,emp e2,dept d
WHERE e.`mgr`=e2.`empno` AND e.`deptno`=d.`deptno` AND (d.`loc`='NEW YORK' OR  d.`loc`='CHICAGO');
 
#自连接
SELECT e.`ename` 员工姓名,e2.`ename` 上级姓名
FROM emp e,emp e2
WHERE e.`mgr`=e2.`empno`
 
#课堂练习19
SELECT e.`empno`,e.`ename`,e.`sal`,s.`grade`,d.`loc`
FROM emp e,dept d,salgrade s
WHERE e.`deptno`=d.`deptno` AND e.`sal` BETWEEN s.`losal` AND s.`hisal`
ORDER BY s.`grade` DESC;
 
#多于两个表的连接
SELECT e.`ename`,e.`sal`,d.`dname`,s.`grade`
FROM emp e,dept d,salgrade s
WHERE e.`deptno`=d.`deptno` AND e.`sal` BETWEEN s.`losal` AND s.`hisal`; 
 
#非等值连接
SELECT ename,sal,grade
FROM emp e,salgrade s
WHERE e.`sal` BETWEEN s.`losal` AND s.`hisal`;
 
#综合练习4
SELECT ename,e.deptno,dname,sal
FROM emp e,dept d
WHERE e.`deptno`=d.`deptno` AND dname = 'RESEARCH' AND sal < 1500;
 
#综合练习3
SELECT ename,loc
FROM emp e,dept d
WHERE e.`deptno`=d.`deptno` AND ename LIKE '%A%';
 
#综合练习2
SELECT ename,comm,loc
FROM emp e,dept d
WHERE e.`deptno`=d.`deptno` AND loc='CHICAGO' AND comm IS NOT NULL;
 
#综合练习1
SELECT ename,e.deptno,dname
FROM emp e,dept d
WHERE e.`deptno`=d.`deptno`
 
 
#多表等值连接查询
SELECT empno,ename,dept.deptno,dname
FROM emp,dept
WHERE emp.`deptno`=dept.`deptno`
#使用表的别名
SELECT empno,ename,d.deptno,dname
FROM emp e,dept d
WHERE e.`deptno`=d.`deptno`
 
#课堂练习18
SELECT ename,hiredate,job
FROM emp
WHERE hiredate BETWEEN '1982-1-1' AND '1983-12-31' AND (job LIKE 'SALES%' OR job LIKE 'MAN%')
ORDER BY hiredate DESC
 
#课堂练习17
SELECT ename,deptno,sal
FROM emp
WHERE deptno NOT IN (10) AND sal BETWEEN 2000 AND 3000
ORDER BY deptno ASC,sal DESC
 
 
#课堂练习16
SELECT ename,deptno,sal
FROM emp
WHERE deptno IN (20,30)
ORDER BY sal
 
SELECT ename,deptno
FROM emp
WHERE deptno IN (20,30)
ORDER BY sal
 
 
 
#同时按多列排序
SELECT ename,deptno,sal
FROM emp
ORDER BY deptno ASC,sal DESC;
 
#DESC 降序  从大到小  == 空值小
SELECT ename,job,deptno,hiredate
FROM emp
ORDER BY hiredate DESC
 
#按三种方式排序
SELECT ename,job,deptno,hiredate
FROM emp
ORDER BY hiredate
 
SELECT ename,job,deptno,hiredate 入职日期
FROM emp
ORDER BY 入职日期
 
SELECT ename,job,deptno,hiredate 入职日期
FROM emp
ORDER BY 4
 
 
可以按照3种方式进行排序：分别是按列名排序、按列别名排序、按列序号排序。
ASC表示按升序排序(默认值), DESC表示按降序排序。
可以同时按照多个列名进行排序
空值在升序排列中排在最前面，在降序排列中排在最后 == 空值小
 
 
4种特殊比较运算符	BETWEEN..AND.. ,  IN,  LIKE,  IS NULL
 
#课堂练习15
SELECT ename,job,deptno
FROM emp
WHERE job IN ('SALESMAN','MANAGER') AND deptno IN (10,20) AND ename LIKE '%A%';
 
SELECT ename,job,deptno
FROM emp
WHERE (job ='SALESMAN' OR job='MANAGER') AND (deptno=10 OR deptno=20) AND ename LIKE '%A%';
 
 
#课堂练习14
SELECT ename,hiredate,job
FROM emp
WHERE hiredate BETWEEN '1981-1-1' AND '1981-12-31' AND job NOT LIKE 'SALES%';
 
SELECT ename,hiredate,job
FROM emp
WHERE hiredate >= '1981-1-1' AND hiredate <='1981-12-31' AND job NOT LIKE 'SALES%';
 
#课堂练习13 写法1 使用特殊比较运算符
SELECT ename,deptno,sal
FROM emp
WHERE deptno IN (10,20) AND sal BETWEEN 3000 AND 5000;
#写法2 使用逻辑运算符
SELECT ename,deptno,sal
FROM emp
WHERE (deptno = 10 OR deptno=20) AND (sal >= 3000 AND sal <= 5000);
 
 
#课堂练习12
SELECT ename,job,sal
FROM emp
WHERE sal > 2000 AND (job='MANAGER' OR job='SALESMAN');
#运算符的优先级
SELECT ename, job, sal 
FROM   emp 
WHERE ( job='SALESMAN'  
OR  job='PRESIDENT') 
AND  sal>1500;
 
SELECT ename, job, sal 
FROM   emp 
WHERE  job='SALESMAN'  
OR  job='PRESIDENT'
AND  sal>1500;
 
 
SELECT ename,comm
FROM emp
WHERE comm IS NOT NULL;
 
SELECT ename,sal
FROM emp
WHERE sal NOT BETWEEN 3000 AND 5000;
 
NOT BETWEEN .. AND .. :不在某个区间
NOT IN (集合）：不在某个集合内
NOT LIKE    ：不像.....
IS NOT NULL:  不是空
 
 
#课堂练习11
SELECT ename,comm
FROM emp
WHERE comm IS NULL;
 
#课堂练习10
SELECT *
FROM emp
WHERE ename LIKE '%T_';
 
#课堂练习9
SELECT *
FROM emp
WHERE ename LIKE 'W%';
 
# IS NULL
SELECT ename,mgr
FROM emp
WHERE mgr IS NULL;
 
 
SELECT ename,comm
FROM emp
WHERE comm IS NULL;
 
#Like运算符
SELECT ename
FROM emp
WHERE ename LIKE 'S%';
 
SELECT ename
FROM emp
WHERE ename LIKE 'S_';
 
SELECT ename
FROM emp
WHERE ename LIKE '%A%';
 
SELECT ename
FROM emp
WHERE ename LIKE '__A%';
 
#课堂练习8
SELECT ename,sal
FROM emp
WHERE sal BETWEEN 3000 AND 5000;
 
#课堂练习7
SELECT ename,hiredate
FROM emp
WHERE hiredate BETWEEN '1982-1-1' AND '1985-12-31';
 
#IN运算符
select empno,ename,deptno
from emp
where deptno in (10,20);
 
#使用BETWEEN .. AND.. 可以查询出某列的值在某个范围内(包括边界值)的数据行
SELECT empno,ename,sal
FROM emp
WHERE sal BETWEEN 1250 AND 1600;
 
#课堂练习6
SELECT empno,ename,deptno
FROM emp
WHERE deptno <> 10;
 
SELECT empno,ename,deptno
FROM emp
WHERE deptno != 10;
 
#课堂练习5
SELECT empno,ename,hiredate
FROM emp
WHERE hiredate < '1985-12-31';
 
#课堂练习4
SELECT empno,ename,job
FROM emp
WHERE job='SALESMAN';
#带条件查询2 非等值情况
SELECT empno,ename,hiredate
FROM emp
WHERE hiredate > '1985-01-01';
 
 
#带条件查询2
SELECT empno,ename,job
FROM emp
WHERE job='CLERK';
SELECT empno,ename,job
FROM emp
WHERE job="CLERK";
#带条件查询1
SELECT empno,ename,deptno
FROM emp
WHERE deptno=20;
 
#课堂练习3
SELECT DISTINCT job FROM emp
 
#排除重复数据的查询
SELECT DISTINCT deptno FROM emp
 
#课堂练习2  ==?
SELECT ename 员工姓名,sal 工资收入,comm 奖金收入,sal+comm 总收入 FROM emp;
 
#课堂练习1
SELECT ename,sal 涨薪前,sal*1.2 涨薪后 FROM emp;
 
#空值参与算术运算，运算后的结果仍为NULL ==待解决?
SELECT ename,sal,comm,sal+comm 月总收入 FROM emp
 
#数值类型的字段可以做数学运算
SELECT empno,ename,sal,sal*12 年薪 FROM emp
 
#按字段别名查询  单双引号的使用
SELECT empno 编号,ename "姓 名",sal '工 资',comm "奖,金" FROM emp
 
#按字段别名查询
SELECT empno 编号,ename 姓名,sal 工资,comm 奖金 FROM emp
 
#查询指定字段
SELECT empno,ename,sal,comm FROM emp
 
#查询所有字段
SELECT * FROM emp

posted @ 2023-10-11 11:32 Kbaor 阅读(27) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· 2023_10_10_MYSQL_DAY_02_笔记

· 2023_10_12_MYSQL_DAY_04_笔记

· Java学习-第一部分-第三阶段-第三节：MySQL基础

· 数据库基础

· SQL相关知识学习

阅读排行：
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
· 上周热点回顾（2.24-3.2）

公告

昵称： Kbaor
园龄： 3年6个月
粉丝： 1
关注： 0

+加关注

2025年3月

日

一

二

三

四

五

六

随笔分类

随笔档案

文章分类

文章档案

阅读排行榜

评论排行榜

1. 2023_10_10_MYSQL_DAY_02_课后题(2)

Kbaor

🌊与👻与🐟

2023_10_11_MYSQL_DAY_03_笔记_上

2023_10_11_MYSQL_DAY_03_笔记_上

公告

搜索

常用链接

我的标签

随笔分类

随笔档案

文章分类

文章档案

阅读排行榜

评论排行榜

最新评论

	10章作业题01答案
	INSERT INTO class(classid,cname) VALUES(1,'Java1班');
	INSERT INTO class(cname,classid) VALUES('Java2班',2);
	INSERT INTO class VALUES(3,'Java3班',NULL);

	10章作业题02 03 04答案
	INSERT INTO student VALUES('A001','张三','男','2005-5-1',100,1);
	INSERT INTO student VALUES('A002','MIKE','男','1905-05-06',10,NULL);
	INSERT INTO student(xh,xm,sex) VALUES('A003','JOHN','女' );

	10章作业题05答案
	UPDATE student
	SET sex='女'
	WHERE xh='A002';

	10章作业题06答案
	UPDATE student
	SET sex='男',birthday='1980-04-01'
	WHERE xh='A003';

	10章作业题07答案
	做法1
	UPDATE student
	SET studentcid=3
	WHERE studentcid IS NULL;

	做法2
	UPDATE student
	SET studentcid=(SELECT classid FROM class WHERE cname='Java3班')
	WHERE studentcid IS NULL;






	#课堂练习50
	#删除语句多列子查询 ***
	DELETE FROM emp_back
	WHERE (deptno,sal) IN (SELECT deptno,AVG(sal) FROM emp GROUP BY deptno);

	#课堂练习49
	#删除语句使用了子查询
	DELETE FROM emp_back
	WHERE deptno = (SELECT deptno FROM dept WHERE loc='NEW YORK');

	#课堂练习48
	DELETE FROM emp_back
	WHERE mgr=7566;

	#删除语句
	DELETE FROM emp_back
	WHERE job='CLERK';

	#课堂练习48
	#更新语句使用了子查询
	UPDATE manager
	SET sal=sal+500
	WHERE deptno IN (SELECT deptno FROM dept WHERE loc='NEW YORK' OR loc='CHICAGO');

	#课堂练习47
	UPDATE manager
	SET comm=0.0
	WHERE comm IS NULL;

	#课堂练习46
	UPDATE manager
	SET job='CLERK'
	WHERE deptno=20;


	#更新语句
	UPDATE emp
	SET deptno=20,comm=1200
	WHERE empno=8888;

	#课堂练习45
	CREATE TABLE emp_back AS
	SELECT * FROM emp WHERE 1=0;

	INSERT INTO emp_back
	SELECT * FROM emp WHERE hiredate > '1982-1-1'

	#通过子查询插入多行数据
	INSERT INTO manager
	SELECT * FROM emp WHERE job='MANAGER';

	#利用查询语句建立表
	CREATE TABLE manager
	SELECT * FROM emp WHERE 1=0;

	#课堂练习44
	INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	VALUES (8888,'BOB','CLEAK',7788,SYSDATE(),3000,NULL,NULL);

	#使用日期函数插入数据
	INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	VALUES (7196,'GREEN','SALESMAN',7782,SYSDATE(),2000,NULL,10);

	INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	VALUES (7197,'GREEN2','SALESMAN',7782,'2023-9-1',2000,NULL,10);

	#获取当前时间的系统函数
	SELECT SYSDATE();

	#课堂练习43
	INSERT INTO dept(deptno,dname) VALUES(90,'MARKET');
	#课堂练习42
	INSERT INTO dept(loc,deptno,dname) VALUES('SY',80,'HR');

	#插入语句方式2
	INSERT INTO dept VALUES(201,'宣传部','上海');
	#空值的插入
	INSERT INTO dept VALUES(203,'后勤部',NULL);


	#插入语句方式1
	INSERT INTO dept(loc,deptno,dname) VALUES('河南新乡',200,'研发部');
	#空值的插入
	INSERT INTO dept(loc,deptno,dname) VALUES(NULL,202,'服务部');
	INSERT INTO dept(deptno,dname) VALUES(205,'小麦部');

	06章04题作业答案
	SELECT e.`ename` ,d.`dname`,e.`sal`,s.`grade`
	FROM emp e LEFT OUTER JOIN dept d ON(e.`deptno`=d.`deptno`), salgrade s
	WHERE e.`sal` BETWEEN s.`losal` AND s.`hisal` AND s.`grade` > 4


	#在 FROM 子句中使用子查询
	SELECT a.ename, a.sal, a.deptno, b.salavg
	FROM emp a, (SELECT deptno, AVG(sal) salavg
	FROM emp
	GROUP BY deptno) b
	WHERE a.deptno = b.deptno AND a.sal > b.salavg;


	#子查询里的空值处理
	SELECT ename
	FROM emp
	WHERE empno NOT IN (SELECT IFNULL (mgr,0) FROM emp);

	#多列子查询
	SELECT deptno,ename,hiredate
	FROM emp
	WHERE (deptno,hiredate) IN (SELECT deptno ,MIN(hiredate)
	FROM emp
	GROUP BY deptno);


	#课堂练习41
	SELECT ename,job
	FROM emp
	WHERE job = ANY (SELECT job FROM emp WHERE deptno=10)
	AND deptno <> 10;



	#课堂练习40
	SELECT ename,hiredate
	FROM emp
	WHERE hiredate > ALL (SELECT hiredate FROM emp WHERE deptno=10)
	AND deptno <> 10;

	#课堂练习39
	SELECT ename,hiredate
	FROM emp
	WHERE hiredate > ANY (SELECT hiredate FROM emp WHERE deptno=10)
	AND deptno <> 10;

	#多行子查询 all操作符
	SELECT empno, ename, job, sal
	FROM emp
	WHERE sal > ALL (SELECT sal FROM emp WHERE deptno= 20)
	AND deptno <> 20;

	SELECT empno, ename, job, sal
	FROM emp
	WHERE sal < ALL (SELECT sal FROM emp WHERE deptno= 20)
	AND deptno <> 20;

	#多行子查询 any操作符
	SELECT ename,sal,empno
	FROM emp
	WHERE empno < ANY (SELECT mgr FROM emp ORDER BY mgr DESC);

	SELECT ename,sal,empno
	FROM emp
	WHERE empno > ANY (SELECT mgr FROM emp ORDER BY mgr DESC);

	SELECT ename,sal,empno
	FROM emp
	WHERE empno = ANY (SELECT mgr FROM emp ORDER BY mgr DESC);


	#多行子查询 in操作符
	SELECT ename,sal
	FROM emp
	WHERE empno IN (SELECT mgr FROM emp);

	#课堂练习38
	SELECT deptno,AVG(sal)
	FROM emp
	GROUP BY deptno
	HAVING AVG(sal) > (SELECT AVG(sal) FROM emp WHERE deptno=10);

	#课堂练习37
	SELECT ename,sal,dname
	FROM emp e,dept d
	WHERE e.`deptno`=d.`deptno`
	AND sal > (SELECT sal FROM emp WHERE ename='SMITH')
	AND d.`loc`='CHICAGO';

	#课堂练习36
	SELECT ename,hiredate
	FROM emp
	WHERE hiredate = (SELECT MIN(hiredate) FROM emp);


	# HAVING子句中使用子查询
	SELECT deptno, MIN(sal)
	FROM emp
	GROUP BY deptno
	HAVING MIN(sal) > (SELECT MIN(sal) FROM emp WHERE deptno = 20);


	#子查询中使用组函数
	SELECT ename,job,sal
	FROM emp
	WHERE sal = (SELECT MIN(sal) FROM emp);

	#单行子查询语句
	SELECT ename,job
	FROM emp
	WHERE job = (SELECT job FROM emp WHERE empno=7369)
	AND sal > (SELECT sal FROM emp WHERE empno=7876);

	#子查询单行子查询
	SELECT ename
	FROM emp
	WHERE sal > (SELECT sal FROM emp WHERE ename='JONES');


	#课堂练习35
	SELECT ename,hiredate,deptno
	FROM emp
	LIMIT 0,5; #第1页

	SELECT ename,hiredate,deptno
	FROM emp
	LIMIT 5,5; #第2页

	SELECT ename,hiredate,deptno
	FROM emp
	LIMIT 10,5; #第3页


	#课堂练习34
	SELECT e.deptno,dname,COUNT(*),AVG(sal)
	FROM emp e,dept d
	WHERE e.`deptno`=d.`deptno`
	GROUP BY e.deptno,dname
	HAVING COUNT(*) > 2 AND AVG(sal) > 2000
	ORDER BY COUNT(*);

	#七个子句的查询
	SELECT job,SUM(sal)
	FROM emp
	WHERE job NOT LIKE 'SALES%'
	GROUP BY job
	HAVING SUM(sal) > 5000
	ORDER BY SUM(sal)
	LIMIT 0,2;

	#分页查询
	SELECT empno,ename,deptno,sal
	FROM emp
	ORDER BY empno
	LIMIT 5,5; #第2页

	SELECT empno,ename,deptno,sal
	FROM emp
	ORDER BY empno
	LIMIT 0,5; #第1页

	#课堂练习33
	SELECT e.deptno,dname,COUNT(*)
	FROM emp e,dept d
	WHERE e.`deptno`=d.`deptno`
	GROUP BY e.deptno,dname
	HAVING COUNT(*) > 2;

	总结:
	1、WHERE子句用来过滤分组之前的记录，不能使用组函数
	2、HAVING子句用来过滤分组之后的记录，可以使用组函数

	#6个子句的查询
	SELECT job,SUM(sal)
	FROM emp
	WHERE job NOT LIKE 'SALES%'
	GROUP BY job
	HAVING SUM(sal) > 5000
	ORDER BY SUM(sal);

	查询语句执行过程：
	1、通过FROM子句中找到需要查询的表；
	2、通过WHERE子句进行非分组函数筛选判断；
	3、通过GROUP BY子句完成分组操作；
	4、通过HAVING子句完成组函数筛选判断；
	5、通过SELECT子句选择显示的列或表达式及组函数；
	6、通过ORDER BY子句进行排序操作。

	#HAVING 子句
	SELECT deptno,COUNT(empno)
	FROM emp
	GROUP BY deptno
	HAVING COUNT(empno) > 3;

	SELECT deptno,MAX(sal)
	FROM emp
	GROUP BY deptno
	HAVING MAX(sal) > 2900;


	#课堂练习32
	SELECT COUNT(*),MAX(sal),MIN(sal),d.`loc`
	FROM emp e,dept d
	WHERE e.`deptno`=d.`deptno` AND d.`loc`='CHICAGO'
	GROUP BY d.`loc`;




	#课堂练习31 自连接
	SELECT e2.`empno` 经理编号,e2.`ename` 经理姓名,COUNT(*)
	FROM emp e,emp e2
	WHERE e.`mgr`=e2.`empno`
	GROUP BY e2.`empno`,e2.`ename`;

	#课堂练习30
	SELECT d.`deptno`,d.`dname`,e.`job`,COUNT(empno),MAX(sal),MIN(sal)
	FROM emp e,dept d
	WHERE e.`deptno`=d.`deptno`
	GROUP BY d.`deptno`,d.`dname`,e.`job`;

	#多表查询分组查询
	SELECT d.`deptno`,d.`dname`,COUNT(empno),MAX(sal)
	FROM emp e,dept d
	WHERE e.`deptno`=d.`deptno`
	GROUP BY d.`deptno`,d.`dname`;


	#按多列分组查询
	SELECT deptno,job,SUM(sal)
	FROM emp
	GROUP BY deptno,job;

	#分组子句
	SELECT job,COUNT(empno)
	FROM emp
	GROUP BY job;

	SELECT deptno,AVG(sal)
	FROM emp
	GROUP BY deptno;

	#课堂练习29
	SELECT COUNT(*),MAX(sal),MIN(sal)
	FROM emp
	WHERE deptno=30;

	#课堂练习28
	SELECT (sal+IFNULL(comm,0))*12 年收入
	FROM emp

	#课堂练习27
	SELECT COUNT(job), COUNT(DISTINCT job)
	FROM emp


	#课堂练习26
	SELECT SUM(sal),AVG(sal)
	FROM emp
	WHERE deptno=20;

	#除COUNT(*)之外，其它所有分组函数都会忽略列中的空值，然后再进行运算; IFNULL==空值处理函数
	SELECT AVG(comm),AVG(IFNULL(comm,0))
	FROM emp;

	#空值处理函数 IFNULL(comm,0)
	SELECT ename 员工姓名,sal 工资收入,comm 奖金收入,sal+IFNULL(comm,0) 总收入
	FROM emp;

	SELECT COUNT(deptno),COUNT(DISTINCT deptno)
	FROM emp

	#五个组函数--聚合函数
	SELECT MIN(hiredate),MAX(hiredate),MIN(sal),MAX(sal),AVG(sal),SUM(sal),COUNT(*)
	FROM emp;

	#课堂练习25
	SELECT e.empno,e.ename,e.`job`,d.deptno,d.`loc`
	FROM emp e,dept d
	WHERE e.deptno = d.deptno AND (d.`loc`='CHICAGO' OR job='MANAGER');


	#联合查询 UNION 去除重复数据
	SELECT e.empno,e.ename,d.deptno,d.dname
	FROM emp e LEFT OUTER JOIN dept d ON(e.deptno = d.deptno)
	UNION
	SELECT e.empno,e.ename,d.deptno,d.dname
	FROM emp e RIGHT OUTER JOIN dept d ON(e.deptno = d.deptno);

	#联合查询 UNION ALL 保留重复数据
	SELECT e.empno,e.ename,d.deptno,d.dname
	FROM emp e LEFT OUTER JOIN dept d ON(e.deptno = d.deptno)
	UNION ALL
	SELECT e.empno,e.ename,d.deptno,d.dname
	FROM emp e RIGHT OUTER JOIN dept d ON(e.deptno = d.deptno);


	#课堂练习24 右外连接题目? 没有下属的也要查出来
	SELECT e.`ename` ,e2.`ename`
	FROM emp e RIGHT OUTER JOIN emp e2 ON(e.`mgr`=e2.`empno`);


	#课堂练习23 左外连接没有领导的也要查出来
	SELECT e.`ename` ,e2.`ename`
	FROM emp e LEFT OUTER JOIN emp e2 ON(e.`mgr`=e2.`empno`);


	#课堂练习22
	SELECT ename,dname,hiredate
	FROM emp e,dept d
	WHERE e.`deptno`=d.`deptno` AND e.`hiredate` > '1980-5-1'

	#课堂练习21 笛卡尔积数据
	SELECT ename,dname
	FROM emp,dept


	#右外连接
	SELECT e.`ename` ,e.`deptno`,d.`loc`
	FROM emp e RIGHT OUTER JOIN dept d ON(e.`deptno`=d.`deptno`);


	#左外连接
	SELECT e.`ename` ,e.`deptno`,d.`loc`
	FROM emp e LEFT OUTER JOIN dept d ON(e.`deptno`=d.`deptno`);

	#等值连接自然连接
	SELECT e.`ename` ,e.`deptno`,d.`loc`
	FROM emp e,dept d
	WHERE e.`deptno`=d.`deptno`


	#课堂练习20 自连接
	SELECT e.`ename` 员工姓名,e.`empno` 员工编号,e2.`ename` 经理姓名,e2.`empno` 经理编号
	FROM emp e,emp e2,dept d
	WHERE e.`mgr`=e2.`empno` AND e.`deptno`=d.`deptno` AND (d.`loc`='NEW YORK' OR d.`loc`='CHICAGO');

	#自连接
	SELECT e.`ename` 员工姓名,e2.`ename` 上级姓名
	FROM emp e,emp e2
	WHERE e.`mgr`=e2.`empno`

	#课堂练习19
	SELECT e.`empno`,e.`ename`,e.`sal`,s.`grade`,d.`loc`
	FROM emp e,dept d,salgrade s
	WHERE e.`deptno`=d.`deptno` AND e.`sal` BETWEEN s.`losal` AND s.`hisal`
	ORDER BY s.`grade` DESC;

	#多于两个表的连接
	SELECT e.`ename`,e.`sal`,d.`dname`,s.`grade`
	FROM emp e,dept d,salgrade s
	WHERE e.`deptno`=d.`deptno` AND e.`sal` BETWEEN s.`losal` AND s.`hisal`;

	#非等值连接
	SELECT ename,sal,grade
	FROM emp e,salgrade s
	WHERE e.`sal` BETWEEN s.`losal` AND s.`hisal`;

	#综合练习4
	SELECT ename,e.deptno,dname,sal
	FROM emp e,dept d
	WHERE e.`deptno`=d.`deptno` AND dname = 'RESEARCH' AND sal < 1500;

	#综合练习3
	SELECT ename,loc
	FROM emp e,dept d
	WHERE e.`deptno`=d.`deptno` AND ename LIKE '%A%';

	#综合练习2
	SELECT ename,comm,loc
	FROM emp e,dept d
	WHERE e.`deptno`=d.`deptno` AND loc='CHICAGO' AND comm IS NOT NULL;

	#综合练习1
	SELECT ename,e.deptno,dname
	FROM emp e,dept d
	WHERE e.`deptno`=d.`deptno`


	#多表等值连接查询
	SELECT empno,ename,dept.deptno,dname
	FROM emp,dept
	WHERE emp.`deptno`=dept.`deptno`
	#使用表的别名
	SELECT empno,ename,d.deptno,dname
	FROM emp e,dept d
	WHERE e.`deptno`=d.`deptno`

	#课堂练习18
	SELECT ename,hiredate,job
	FROM emp
	WHERE hiredate BETWEEN '1982-1-1' AND '1983-12-31' AND (job LIKE 'SALES%' OR job LIKE 'MAN%')
	ORDER BY hiredate DESC

	#课堂练习17
	SELECT ename,deptno,sal
	FROM emp
	WHERE deptno NOT IN (10) AND sal BETWEEN 2000 AND 3000
	ORDER BY deptno ASC,sal DESC


	#课堂练习16
	SELECT ename,deptno,sal
	FROM emp
	WHERE deptno IN (20,30)
	ORDER BY sal

	SELECT ename,deptno
	FROM emp
	WHERE deptno IN (20,30)
	ORDER BY sal



	#同时按多列排序
	SELECT ename,deptno,sal
	FROM emp
	ORDER BY deptno ASC,sal DESC;

	#DESC 降序从大到小 == 空值小
	SELECT ename,job,deptno,hiredate
	FROM emp
	ORDER BY hiredate DESC

	#按三种方式排序
	SELECT ename,job,deptno,hiredate
	FROM emp
	ORDER BY hiredate

	SELECT ename,job,deptno,hiredate 入职日期
	FROM emp
	ORDER BY 入职日期

	SELECT ename,job,deptno,hiredate 入职日期
	FROM emp
	ORDER BY 4


	可以按照3种方式进行排序：分别是按列名排序、按列别名排序、按列序号排序。
	ASC表示按升序排序(默认值), DESC表示按降序排序。
	可以同时按照多个列名进行排序
	空值在升序排列中排在最前面，在降序排列中排在最后 == 空值小


	4种特殊比较运算符 BETWEEN..AND.. , IN, LIKE, IS NULL

	#课堂练习15
	SELECT ename,job,deptno
	FROM emp
	WHERE job IN ('SALESMAN','MANAGER') AND deptno IN (10,20) AND ename LIKE '%A%';

	SELECT ename,job,deptno
	FROM emp
	WHERE (job ='SALESMAN' OR job='MANAGER') AND (deptno=10 OR deptno=20) AND ename LIKE '%A%';


	#课堂练习14
	SELECT ename,hiredate,job
	FROM emp
	WHERE hiredate BETWEEN '1981-1-1' AND '1981-12-31' AND job NOT LIKE 'SALES%';

	SELECT ename,hiredate,job
	FROM emp
	WHERE hiredate >= '1981-1-1' AND hiredate <='1981-12-31' AND job NOT LIKE 'SALES%';

	#课堂练习13 写法1 使用特殊比较运算符
	SELECT ename,deptno,sal
	FROM emp
	WHERE deptno IN (10,20) AND sal BETWEEN 3000 AND 5000;
	#写法2 使用逻辑运算符
	SELECT ename,deptno,sal
	FROM emp
	WHERE (deptno = 10 OR deptno=20) AND (sal >= 3000 AND sal <= 5000);


	#课堂练习12
	SELECT ename,job,sal
	FROM emp
	WHERE sal > 2000 AND (job='MANAGER' OR job='SALESMAN');

	#运算符的优先级
	SELECT ename, job, sal
	FROM emp
	WHERE ( job='SALESMAN'
	OR job='PRESIDENT')
	AND sal>1500;

	SELECT ename, job, sal
	FROM emp
	WHERE job='SALESMAN'
	OR job='PRESIDENT'
	AND sal>1500;


	SELECT ename,comm
	FROM emp
	WHERE comm IS NOT NULL;

	SELECT ename,sal
	FROM emp
	WHERE sal NOT BETWEEN 3000 AND 5000;

	NOT BETWEEN .. AND .. :不在某个区间
	NOT IN (集合）：不在某个集合内
	NOT LIKE ：不像.....
	IS NOT NULL: 不是空


	#课堂练习11
	SELECT ename,comm
	FROM emp
	WHERE comm IS NULL;

	#课堂练习10
	SELECT *
	FROM emp
	WHERE ename LIKE '%T_';

	#课堂练习9
	SELECT *
	FROM emp
	WHERE ename LIKE 'W%';

	# IS NULL
	SELECT ename,mgr
	FROM emp
	WHERE mgr IS NULL;


	SELECT ename,comm
	FROM emp
	WHERE comm IS NULL;

	#Like运算符
	SELECT ename
	FROM emp
	WHERE ename LIKE 'S%';

	SELECT ename
	FROM emp
	WHERE ename LIKE 'S_';

	SELECT ename
	FROM emp
	WHERE ename LIKE '%A%';

	SELECT ename
	FROM emp
	WHERE ename LIKE '__A%';

	#课堂练习8
	SELECT ename,sal
	FROM emp
	WHERE sal BETWEEN 3000 AND 5000;

	#课堂练习7
	SELECT ename,hiredate
	FROM emp
	WHERE hiredate BETWEEN '1982-1-1' AND '1985-12-31';

	#IN运算符
	select empno,ename,deptno
	from emp
	where deptno in (10,20);

	#使用BETWEEN .. AND.. 可以查询出某列的值在某个范围内(包括边界值)的数据行
	SELECT empno,ename,sal
	FROM emp
	WHERE sal BETWEEN 1250 AND 1600;

	#课堂练习6
	SELECT empno,ename,deptno
	FROM emp
	WHERE deptno <> 10;

	SELECT empno,ename,deptno
	FROM emp
	WHERE deptno != 10;

	#课堂练习5
	SELECT empno,ename,hiredate
	FROM emp
	WHERE hiredate < '1985-12-31';

	#课堂练习4
	SELECT empno,ename,job
	FROM emp
	WHERE job='SALESMAN';

	#带条件查询2 非等值情况
	SELECT empno,ename,hiredate
	FROM emp
	WHERE hiredate > '1985-01-01';


	#带条件查询2
	SELECT empno,ename,job
	FROM emp
	WHERE job='CLERK';

	SELECT empno,ename,job
	FROM emp
	WHERE job="CLERK";

	#带条件查询1
	SELECT empno,ename,deptno
	FROM emp
	WHERE deptno=20;

	#课堂练习3
	SELECT DISTINCT job FROM emp

	#排除重复数据的查询
	SELECT DISTINCT deptno FROM emp

	#课堂练习2 ==?
	SELECT ename 员工姓名,sal 工资收入,comm 奖金收入,sal+comm 总收入 FROM emp;

	#课堂练习1
	SELECT ename,sal 涨薪前,sal*1.2 涨薪后 FROM emp;

	#空值参与算术运算，运算后的结果仍为NULL ==待解决?
	SELECT ename,sal,comm,sal+comm 月总收入 FROM emp

	#数值类型的字段可以做数学运算
	SELECT empno,ename,sal,sal*12 年薪 FROM emp

	#按字段别名查询单双引号的使用
	SELECT empno 编号,ename "姓名",sal '工资',comm "奖,金" FROM emp

	#按字段别名查询
	SELECT empno 编号,ename 姓名,sal 工资,comm 奖金 FROM emp

	#查询指定字段
	SELECT empno,ename,sal,comm FROM emp

	#查询所有字段
	SELECT * FROM emp