两小时学会MySQL查询语句（下篇）

学生表

#创建表
CREATE TABLE LX_student( 
    studentId INT(8) PRIMARY KEY NOT NULL, 
    studentName VARCHAR(8) NOT NULL, 
    studentSex VARCHAR(2) NOT NULL, 
    Birthday DATE NOT NULL, 
    Nation VARCHAR(6) NOT NULL, 
    Address VARCHAR(20) NOT NULL, 
    majorId INT(4), stuResume TEXT, 
    stuCertificate BLOB, 
FOREIGN KEY (majorId) REFERENCES LX_major(majorId) 
); 
​
#插入数据
INSERT INTO LX_student(studentId,studentName,studentSex,Birthday,Nation,Address,majorId)
VALUES (06053113,'唐李生','男','1987-4-19','汉','湖北省麻城',0501),
(07042219,'黄耀','男','1989-1-2','汉','黑龙江省牡丹江市',0403),
(06041138,'华美','女','1987-9-11','汉','河北省保定市',0403),
(07045120,'刘权利','男','1989-10-20','回','湖北省武汉市',0403),
(08055117,'王燕','女','1990-10-20','回','河南省安阳市',0501),
(08045142,'郝明星','女','1989-11-29','满','辽宁省大连市',0403),
(08053101,'高猛','男','1990-2-3','汉','湖北生黄石市',0501),
(08053124,'多桑','男','1988-10-26','藏','西藏',0501),
(08053160,'郭政强','男','1986-11-24','土家','湖南省吉首',0501),
(08053116,'陆敏','女','1990-8-3','汉','广东省东莞',0501),
(08053131,'林惠萍','女','1989-4-12','壮','广西省柳州市',0501),
(07093305,'郑家谋','男','1988-9-12','汉','上海市',0904),
(07093342,'罗家艳','女','1988-5-16','满','北京市',0904),
(07093325,'史玉磊','男','1988-9-11','汉','湖北省孝感市',0904),
(07093317,'凌晨','女','1988-6-28','汉','浙江省温州',0904),
(08041136,'徐栋梁','男','1989-12-20','回','陕西咸阳',0403),
(08041127,'巴朗','男','1989-12-25','蒙古','内蒙古',0403);

员工表

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

部门表

#创建表
CREATE TABLE dept (
  deptno INT,
  dname VARCHAR (14),
  loc VARCHAR (13)
) ;
​
#插入数据
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, 'OPERATIONS', 'BOSTON'); 

连接查询

自然连接

#自然连接，两表之间无条件连接，生成n（第一张表行数）*m（第二张表行数）行数据的新表（笛卡尔积）（但是大部分数据是没有任何意义的）
语法：SELECT 查询列 FROM 表一 NATURAL JOIN 表二；（自然连接不需要连接条件）
例：SELECT * FROM grede NATURAL JOIN dept;

内连接

语法：SELECT 查询列 FROM 表一 JOIN 表二 ON 连接条件；
例：SELECT e.*,d.dname,d.loc FROM emp e JOIN dept d ON e.deptno = d.deptno;

外连接

分为左连接和右连接
#语法：（左连接）SELECT 查询返回列表 FROM 表一 别名 LEFT [INNER] JOIN 表二 别名 ON 连接条件
​
#查询emp表的所有数据，和对应部门信息（左连接）
SELECT e.*,d.dname,d.loc FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno;
​
​
#语法：（右连接）SELECT 查询返回列表 FROM 表一 别名 RIGHT [INNER] JOIN 表二 别名 ON 连接条件
​
#查询dept表的所有数据，和对应员工信息信息（右连接）
SELECT d.*,e.* FROM emp e RIGHT JOIN dept d ON e.deptno = d.deptno;

自连接

顾名思义就是表自己和自己在某种条件下进行连接

#查询员工及其所属领导的名字
SELECT e.ename,a.ename FROM emp e,emp a WHERE e.empno = a.mgr;
​
​
#查询员工及其所属领导的名字，如果没有领导也要展示出来（外连接）
SELECT d.ename,e.ename FROM emp e RIGHT JOIN emp d ON e.empno = d.mgr;

联合查询

目的：使得两张不相干的表合并一起，以便查询（注意：两次查询结果返回的列数，和数据类型必须一致）

#将薪资低于5000的员工，和年龄大于50岁的员工全部查询出来
​
SELECT * FROM emp WHERE sal > 2000
UNION             #UNION ALL 不去重    UNION 去重
SELECT * FROM emp WHERE job = 'manager';

(去重)

（不去重）

子查询

子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。

语法:SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );

子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。

分类:

子查询出现的位置：

• where后，作为条件的一部分；

• from后，作为被查询的一条表；

• SELECT之后 ,作为被查询的一列;

当子查询出现在where后作为条件时，还可以使用如下关键字：

• any

• all

根据子查询结果不同，分为：

A. 标量子查询（子查询结果为单个值）

B. 列子查询(子查询结果为一列)

C. 行子查询(子查询结果为一行)

D. 表子查询(子查询结果为多行多列)

标量子查询

#查询“销售部”的所有员工信息
​
1.查询“销售部门”ID
SELECT deptno FROM dept WHERE dname = 'SALES';
​
2.根据部门ID查询员工信息
SELECT * FROM emp WHERE deptno = 30;
​
3.连接
SELECT * FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE dname = 'SALES');
​
​
#查询在‘king’入职之后的员工信息
1.获取“king”入职日期
SELECT hiredate FROM emp WHERE ename = 'king';
​
2.查询比‘1981-11-17’更后入职员工信息
SELECT * FROM emp WHERE hiredate > '1981-11-17';
​
3.连接
SELECT * FROM emp WHERE hiredate > (SELECT hiredate FROM emp WHERE ename = 'king');
​

列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。

常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL

操作符	说明
IN	在指定的集合范围之内，多选一
NOT IN	不在指定的集合范围之内
ANY	子查询返回列表中，有任意一个满足即可
SOME	与ANY等同，使用SOME的地方都可以使用ANY
ALL	子查询返回列表的所有值都必须满足

#查询‘SALES’和‘RESEARCH’的所有员工信息
1.查询‘SALES’和‘RESEARCH’的部门编号
SELECT deptno FROM dept WHERE dname = 'SALES' OR dname = 'RESEARCH';
​
2.通过部门编号查询所有员工信息
SELECT * FROM emp WHERE deptno IN (20,30);
​
3.连接
SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE dname = 'SALES' OR dname = 'RESEARCH');
​
​
#查询比‘SALES’所有人工资都高的员工信息
1.查询所有‘SALES’的员工工资
SELECT e.sal FROM emp e JOIN dept d ON e.deptno = d.deptno WHERE d.dname = 'SALES';
​
2.比财务部所有人工资都都高的员工信息
SELECT * FROM emp WHERE sal > ALL (SELECT e.sal FROM emp e JOIN dept d ON e.deptno = d.deptno WHERE d.dname = 'SALES');

行子查询

#查询与‘WARD’的薪资及直属领导相同的员工信息
1.查询‘WARD’的薪资及直属领导
SELECT sal,mgr FROM emp WHERE ename = 'WARD';
​
2.查询与‘WARD’的薪资及直属领导相同的员工信息
SELECT * FROM emp WHERE (sal,mgr) = (SELECT sal,mgr FROM emp WHERE ename = 'WARD');

表子查询

#表子查询
#查询与‘WARD’，‘SCOTT’的职位和薪资相同的员工信息
1.查询与‘WARD’，‘SCOTT’的职位和薪资
SELECT job,sal FROM emp WHERE ename = 'WARD' OR ename = 'SCOTT';
​
2.查询与‘WARD’，‘SCOTT’的职位和薪资相同的员工信息
SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename = 'WARD' OR ename = 'SCOTT');
​
​
#查询入职时间是'1981-11-17'之后的员工信息，及其部门信息
1.入职时间是'1981-11-17'之后的员工信息
SELECT * FROM emp WHERE hiredate > '1981-11-17';
​
2.查询入职时间是'1981-11-17'之后的员工信息，及其部门信息
SELECT e.*,d.* FROM (SELECT * FROM emp WHERE hiredate > '1981-11-17') e LEFT JOIN dept d ON e.deptno = d.deptno;

两小时学会MySQL查询语句（上篇）http://t.csdn.cn/VvHoT