【MySQL数据库】使用DQL命令查询数据（二）

创建员工表empp

#创建表empp
CREATE TABLE `empp`(
`empno` INT,
`ename` VARCHAR(50),
`job` VARCHAR(50),
`mgr` INT,
`hiredate` DATE,
`sal` DECIMAL(7,2),
`comm` DECIMAL(7,2),
`deptno` INT);
#插入数据
INSERT INTO empp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO empp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO empp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO empp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO empp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO empp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO empp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO empp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO empp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO empp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO empp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO empp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO empp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO empp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

1 分组查询

分组查询关键字：group by，对查询出来的数据按照某个字段进行分组。 

#查询每个部门的部门编号和每个部门的工资和
SELECT deptno AS '部门编号',SUM(sal) AS '每个部门工资和' FROM empp GROUP BY deptno;
#查询每个部门的部门编号以及每个部门的人数
SELECT deptno AS '部门编号',COUNT(deptno) AS '每个部门的人数'FROM empp GROUP BY deptno;
#查询每个部门的部门编号以及每个部门工资大于1500的人数
SELECT deptno AS'部门编号',COUNT(sal) AS'工资大于1500的人数' FROM empp WHERE sal>1500 GROUP BY deptno;

 having子句：对分组后的数据再次进行过滤。

#查询工资总和大于9000的部门编号以及工资和
SELECT deptno AS'工资总和大于9000的部门编号',SUM(sal) AS'工资和'FROM empp GROUP BY deptno HAVING SUM(sal)>9000;

2 LIMIT子句

 LIMIT关键字：对查询出来的结果进行起始行和行数显示的限制。

语法：LIMIT [m],[n]；#m表示起始行，不写则默认从0开始，0表示第一行，n表示显示的行数

#查询5行记录，起始行从0开始
SELECT * FROM empp LIMIT 0,5;

3 数据完整性  

3.1 主键约束

PRIMARY KEY:设置主键，保证指定字段的数据具有唯一性且不为NULL。

#方式一：
CREATE TABLE `tb1`(
`id` INT PRIMARY KEY,#将id字段设为主键
`name` VARCHAR(50)
);
#方式二：
CREATE TABLE `tb2`(
`id` INT,
`name` VARCHAR(50),
PRIMARY KEY(id)#设置id为主键，这种设置方式可以设置联合主键
);
CREATE TABLE `tb3`(
`classid` INT,
`stuid` INT,
`name` VARCHAR(50),
PRIMARY KEY(classid,stuid)
);
#方式三：创建完表后，再添加主键
CREATE TABLE `tb4`(
`id` INT,
`name` VARCHAR(50)
);
#向tb4表添加主键
ALTER TABLE tb4 ADD PRIMARY KEY(id);

3.2 唯一性约束

UNIQUE:设置字段数据唯一性。在指定字段后面写上unique后，该字段的数据具有唯一性。

CREATE TABLE `tb5`(
`id` INT,
`name` VARCHAR(50) UNIQUE
);

3.3 域完整性 

非空约束：在指定字段后面写上NOT NULL。

CREATE TABLE `tb6`(
`id` INT PRIMARY KEY,
`name` VARCHAR(50) NOT NULL,
`Sex` VARCHAR(10)
);

3.4 引用完整性

 引用完整性：一个表中某字段的数据来源于另一个表中某字段的数据。

#外键实现：[constraint 字段名(见名知意)] foreign key(字段名1) references 表名(字段名2)
#字段名1和字段名2的数据类型要一致，且字段名2得是主键
CREATE TABLE `score`(
`id` INT,
`score` INT,
`sid` INT,
CONSTRAINT fk_score_sid FOREIGN KEY(sid) REFERENCES student(sid)
);
#创建好表之后给表添加外键
CREATE TABLE `score2`(
`id` INT,
`score` INT,
`sid` INT
);
ALTER TABLE score2 ADD FOREIGN KEY(sid) REFERENCES student(sid);

4 多表查询 

4.1 合并结果集

作用：把两个select语句的查询结果合并到一起。

前提条件：被合并的两个查询结果，它们的列数和列类型必须相同。

union：合并的结果集是去除掉重复记录的。union all：不去除重复记录。

#union:去除重复记录
SELECT * FROM t1 UNION SELECT * FROM t2;
#union all:不去除重复记录
SELECT * FROM t1 UNION ALL SELECT * FROM t2;

4.2 连接查询

创建dept表，举例说明。

#创建表dept
CREATE TABLE `dept`(
`deptno` INT,
`dname` VARCHAR(14),
`loc` VARCHAR(13)
);
#向dept表添加数据
INSERT INTO dept VALUES
(10, 'ACCOUNTING', 'NEW YORK'),(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),(40, 'OPERATIONS', 'BOSTON');

连接查询就是求出多个表的乘积，如表t1连接t2，那么查询出来的结果就是t1*t2 。连接查询的结果是笛卡尔积，此种查询会产生一些不正确的记录，需要进行筛除。

#如果两个表中有相同的字段，可以用来做为筛除条件
SELECT * FROM empp,dept WHERE empp.`deptno`=dept.`deptno`;
#上面的查询语句是将两个表中的所有字段全部查询出来，也可以查询指定的列
SELECT empno,ename,sal,empp.deptno,dname FROM empp,dept WHERE empp.`deptno`=dept.`deptno`;
SELECT empp.`empno`,empp.`ename`,empp.`sal`,empp.`deptno`,dept.`dname` FROM empp,dept WHERE empp.`deptno`=dept.`deptno`;
#可以给表名取别名
SELECT e.empno,e.ename,e.sal,e.deptno,d.dname FROM empp e,dept AS d WHERE e.`deptno`=d.`deptno`;

4.3 内连接 

上述讲的连接查询就是内连接。内连接的标准语法是：

表1 INNER JOIN 表2 ON 条件

SELECT * FROM empp INNER JOIN dept ON empp.`deptno`=dept.`deptno`;

内连接查询结果的特点是：查询出来的结果都是必须满足条件的。

4.4 外连接 

外连接分为左外连接和右外连接。外连接查询的特点是：查询出的结果存在不满足条件的可能。

4.4.1 左外连接 

左外连接是先查询出左表的所有数据，然后查询右表，右表满足条件的显示出来，不满足条件的结果显示NULL。

语法： 表1 LEFT OUTER JOIN 表2 ON 条件 

SELECT * FROM empp LEFT OUTER JOIN dept ON empp.`deptno`=dept.`deptno`;

4.4.2 右外连接

右外连接是先将右边表的所有数据都查询出来，然后去查询左边的表，满足条件的显示出来，如果左表存在不满足条件的数据，则结果显示NULL。

语法：表1 RIGHT OUTER JOIN 表2 ON 条件 

SELECT * FROM empp RIGHT OUTER JOIN dept ON empp.deptno=dept.deptno;

4.5 自然连接 

 自然连接：系统自动的筛除笛卡尔积中无用的记录。前提条件是多表中存在相同名称相同数据类型的字段。

#自然内连接：表名1 natural join 表名2
SELECT * FROM empp NATURAL JOIN dept;
#自然左外连接：表名1 natural left join 表名2
SELECT * FROM empp NATURAL LEFT JOIN dept;
#自然右外连接：表名1 natural right join 表名2
SELECT * FROM empp NATURAL RIGHT JOIN dept;

5 子查询 

子查询：一个select语句中包含另一个完整的select语句。子查询就是嵌套查询。嵌套的select语句可以写在where子句后面作为条件，也可以写在from后面作为表。

#查询工资高于JONES的员工
SELECT * FROM empp WHERE sal>(SELECT sal FROM empp WHERE ename='jones');

#查询与SCOTT同一个部门的员工
SELECT * FROM empp WHERE deptno=(SELECT deptno FROM empp WHERE ename='scott');

#工资高于30号部门所有人的员工信息
SELECT * FROM empp WHERE sal>(SELECT MAX(sal) FROM empp WHERE deptno=30);

#查询工作和工资与MARTIN（马丁）完全相同的员工信息
SELECT * FROM empp WHERE job=(SELECT job FROM empp WHERE ename='martin') AND sal=(SELECT sal FROM empp WHERE ename='martin');
#也可以使用IN写
SELECT * FROM empp WHERE (job,sal) IN (SELECT job,sal FROM empp WHERE ename='martin');

#查询有2个以上直接下属的员工信息
#第一步：先查询出现2次及以上的mrg
SELECT mgr FROM empp GROUP BY mgr HAVING COUNT(mgr)>=2;
#第二步：查询下属员工信息
SELECT * FROM empp WHERE mgr IN(SELECT mgr FROM empp GROUP BY mgr HAVING COUNT(mgr)>=2);

#查询员工编号为7788的员工名称、员工工资、部门名称、部门地址
SELECT e.ename,e.sal,d.dname,d.loc FROM empp e,dept d WHERE e.`deptno`=d.`deptno` AND e.`empno`=7788;
#求7369员工编号、姓名、经理编号和经理姓名(自己连接自己，给表名起别名)
SELECT e1.empno,e1.ename,e1.mgr,e2.ename FROM empp e1,empp e2 WHERE e1.mgr = e2.empno AND e1.empno = 7369;
SELECT e1.empno,e1.ename,e1.mgr,e2.ename FROM empp e1,empp e2 WHERE e1.mgr=e2.`empno`;
#求各个部门薪水最高的员工所有信息
SELECT MAX(sal)FROM empp GROUP BY deptno;
SELECT * FROM empp WHERE sal IN(SELECT MAX(sal) FROM empp GROUP BY deptno);
#第二种写法：
SELECT e.* FROM empp e,(SELECT MAX(sal) maxsal,deptno FROM empp GROUP BY deptno) a
WHERE e.deptno = a.deptno AND e.sal =a.maxsal;