sql语句的学习之mysql（史上最全，没有之一）

SQL语句

l SQL分类：

n 数据定义语言：简称DDL(Data Definition Language)，用来定义数据库对象：数据库，表，列等。关键字：create，alter，drop等

n 数据操作语言：简称DML(Data Manipulation Language)，用来对数据库中表的记录进行更新。关键字：insert，delete，update等

n 数据控制语言：简称DCL(Data Control Language)，用来定义数据库的访问权限和安全级别，及创建用户。

n 数据查询语言：简称DQL(Data Query Language)，用来查询数据库中表的记录。关键字：select，from，where等

SQL通用语法

l SQL语句可以单行或多行书写，以分号结尾

l 可使用空格和缩进来增强语句的可读性

l MySQL数据库的SQL语句不区分大小写，建议使用大写，例如：SELECT * FROM user。

l 同样可以使用/**/的方式完成注释

l MySQL中的我们常使用的数据类型如下

 

 

常用类型：

l int：整型

l double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；

l decimal：泛型型，在表单钱方面使用该类型，因为不会出现精度缺失问题；

l char：固定长度字符串类型；

l varchar：可变长度字符串类型；

l text：字符串类型；

l blob：字节类型；

l date：日期类型，格式为：yyyy-MM-dd；

l time：时间类型，格式为：hh:mm:ss

l timestamp：时间戳类型；

 

插入表记录：★★★

l 语法：

insert into 表 (列名1,列名2,列名3..) values  (值1,值2,值3..); -- 向表中插入某些列

insert into 表 values (值1,值2,值3..); --向表中插入所有列

l 注意：

插入的数据应与字段的数据类型相同

所有的字符串数据必须使用单引号

数据的大小应该在列的长度范围内

在values中列出的数据位置必须与被加入列的排列位置相对应。

除了数值类型外，其它的字段类型的值必须使用引号引起。

如果要插入空值，可以不写字段，或者插入 null。

对于自动增长的列在操作时，直接插入null值即可。

 

l 例如：

INSERT INTO sort(sid,sname) VALUES('s001', '电器');

更新表记录：★★★

用来修改指定条件的数据，将满足条件的记录指定列修改为指定值

l 语法：

update 表名 set 字段名=值,字段名=值;

update 表名 set 字段名=值,字段名=值 where 条件;

l 注意：

u 列名的类型与修改的值要一致.

u 修改值得时候不能超过最大长度.

u 值如果是字符串或者日期需要加’’.

 

UPDATE stu SET sname=’zhangSanSan’, age=’32’, gender=’female’ WHERE sid=’s_1001’;

UPDATE stu SET sname=’liSi’, age=’20’ WHERE age>50 AND gender=’male’;

UPDATE stu SET sname=’wangWu’, age=’30’ WHERE age>60 OR gender=’female’;

UPDATE stu SET gender=’female’ WHERE gender IS NULL UPDATE stu SET age=age+1 WHERE sname=’zhaoLiu’;

删除记录：delete★★★

l 语法：

delete from 表名 [where 条件];

或者

truncate table 表名;

 

l 面试题：

删除表中所有记录使用delete from 表名; 还是用truncate table 表名;

删除方式：delete 一条一条删除，不清空auto_increment记录数。

truncate 直接将表删除，重新建表，auto_increment将置为零，从新开始。

虽然TRUNCATE和DELETE都可以删除表的所有记录，但有原理不同。DELETE的效率没有TRUNCATE高！

TRUNCATE其实属性DDL语句，因为它是先DROP TABLE，再CREATE TABLE。而且TRUNCATE删除的记录是无法回滚的，但DELETE删除的记录是可以回滚的（回滚是事务的知识！）。

DELETE FROM stu WHERE sid=’s_1001’003B

DELETE FROM stu WHERE sname=’chenQi’ OR age > 30;

DELETE FROM stu;-------删除表中的所有数据

查询的基本语法：

1.1.1 查询指定字段信息

select 字段1,字段2,...from 表名;

查询表中所有字段

select * from 表名;

注意:使用"*"在练习、学习过程中可以使用，在实际开发中，不推荐使用。原因，要查询的字段信息不明确，若字段数量很多，会导致查询速度很慢。

 

1.1.2 distinct用于去除重复记录

select distinct 字段 from 表名;

去除重复记录（两行或两行以上记录中系列的上的数据都相同），例如emp表中sal字段就存在相同的记录。当只查询emp表的sal字段时，那么会出现重复记录，那么想去除重复记录，需要使用DISTINCT：

SELECT DISTINCT sal FROM emp;

1.1.3 查询时进行列计算

　　因为sal和comm两列的类型都是数值类型，所以可以做加运算。如果sal或comm中有一个字段不是数值类型，那么会出错。

SELECT *,sal+comm FROM emp;

 

comm列有很多记录的值为NULL，因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL。下面使用了把NULL转换成数值0的函数IFNULL：

SELECT *,sal+IFNULL(comm,0) FROM emp;

1.1.4 给列名添加别名

在上面查询中出现列名为sal+IFNULL(comm,0)，这很不美观，现在我们给这一列给出一个别名，为total：

SELECT *, sal+IFNULL(comm,0) AS total FROM emp;

给列起别名时，是可以省略AS关键字的：

SELECT *,sal+IFNULL(comm,0) total FROM emp;

 

使用的as关键字，as可以省略的.

别名可以给表中的字段，表设置别名。 当查询语句复杂时，使用别名可以极大的简便操作。

表别名格式:  

select * from 表名 as 别名;

或

select * from 表名 别名;

列别名格式：

select 字段名 as 别名 from 表名;

或

select 字段名 别名 from 表名;

 

1.1.5 多个关键字的排序

select *

from  emp

where

group by

having

order by

limit

(注意having的作用相当于where,group by和having可以一起使用,也可以和where一起使用,where位于group by的前面,用于分组前的操作,having位于group by的后面,用于分组后的操作)

条件查询

where语句表条件过滤。满足条件操作，不满足不操作，多用于数据的查询与修改。

格式 :select 字段  from 表名  where 条件;

while条件的种类如下：

比较运算符	>  <  <=   >=   =  <> 或者!=	大于、小于、大于(小于)等于、不等于
	BETWEEN  ...AND...	显示在某一区间的值(含头含尾)
	IN(set)	字段下列的值和set比较,如果相同则被查询出来
	LIKE 通配符	模糊查询，Like语句中有两个通配符： % 用来匹配多个字符；例first_name like ‘a%’; _ 用来匹配一个字符。例first_name like ‘a_’;
	IS NULL	判断是否为空 is null; 判断为空 is not null; 判断不为空
逻辑运算符	and	多个条件同时成立
	or	多个条件任一成立
	not	不成立，例：where not(salary>100);

 

查询时排序

顺序是按照从上往下而言的

1.1.6 查询所有学生记录，按年龄升序排序

SELECT *

FROM stu

ORDER BY sage ASC;

或者

SELECT *

FROM stu

ORDER BY sage;

 

1.1.7 查询所有学生记录，按年龄降序排序

SELECT *

FROM stu

ORDER BY age DESC;

 

1.1.8 查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序

SELECT * FROM emp

ORDER BY sal DESC,empno ASC;

 

聚合函数

如果sql语句中有group by语句,group by后的列为指定列

Count(*)是查询每组总的记录数,该组是否算有记录,不是根据group by 后的列是不是null,而是根据改行有无数据(只要改行有一列(该列可以是主键,也可以是普通列)有数据,则该组算有数据),而且聚合函数通常位于select和from之间

聚合函数是用来做纵向运算的函数：

l COUNT()：统计指定列不为NULL的记录行数；

 

l MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

l MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

l SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

l AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

 

1.1.9 COUNT

当需要纵向统计时可以使用COUNT()。

l 查询emp表中记录数：

SELECT COUNT(*) AS cnt FROM emp;

 

l 查询emp表中有佣金的人数：

SELECT COUNT(comm) cnt FROM emp;

注意，因为count()函数中给出的是comm列，那么只统计comm列非NULL的行数。

 

l 查询emp表中月薪大于2500的人数：

SELECT COUNT(*) FROM emp

WHERE sal > 2500;

 

l 统计月薪与佣金之和大于2500元的人数：

SELECT COUNT(*) AS cnt FROM emp WHERE sal+IFNULL(comm,0) > 2500;

 

l 查询有佣金的人数，以及有领导的人数：

SELECT COUNT(comm), COUNT(mgr) FROM emp;

 

1.1.10 SUM和AVG

当需要纵向求和时使用sum()函数。

l 查询所有雇员月薪和：

SELECT SUM(sal) FROM emp;

 

l 查询所有雇员月薪和，以及所有雇员佣金和：

SELECT SUM(sal), SUM(comm) FROM emp;

 

l 查询所有雇员月薪+佣金和：

SELECT SUM(sal+IFNULL(comm,0)) FROM emp;

 

l 统计所有员工平均工资：

SELECT SUM(sal), COUNT(sal) FROM emp;

或者

SELECT AVG(sal) FROM emp;

 

1.1.11 MAX和MIN

l 查询最高工资和最低工资：

SELECT MAX(sal), MIN(sal) FROM emp;

分组查询

分组查询是把记录使用某一列进行分组,然后查询组信息,组信息里面含有那些列,取决于select和from中间具有的列;如果是一方的主键作为多方的外键,那么根据一张表中的主键分组和根据另一张表中的外键分组,效果是一样的

l 查询每个部门的部门编号和每个部门的工资和：

SELECT deptno, SUM(sal)

FROM emp

GROUP BY deptno;

 

l 查询每个部门的部门编号以及每个部门的人数：

SELECT deptno,COUNT(*)

FROM emp

GROUP BY deptno;

 

l 查询每个部门的部门编号以及每个部门工资大于1500的人数：

SELECT deptno,COUNT(*)

FROM emp

WHERE sal>1500

GROUP BY deptno;

子句

l 查询工资总和大于9000的部门编号以及工资和：

SELECT deptno, SUM(sal)

FROM emp

GROUP BY deptno

HAVING SUM(sal) > 9000;

　　

注意，WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。

因为having是对分组后数据的约束,所以,having后的列名必须来自select和from之间的列名

表与表之间的关系

1.1.12 一对一：

例如t_person表和t_card表，即人和身份证。这种情况需要找出主从关系，即谁是主表，谁是从表。人可以没有身份证，但身份证必须要有人才行，所以人是主表，而身份证是从表。设计从表可以有两种方案：

Ø 在t_card表中添加外键列（相对t_user表），并且给外键添加唯一约束；

Ø 给t_card表的主键添加外键约束（相对t_user表），即t_card表的主键也是外键。即从表的主键就是外键

方式1:基于主键的一对一(一般采用的方式)

 

方式2:基于外键的一对一(了解,一般不采用)

 

1.1.13 一对多（多对一）：

最为常见的就是一对多！一对多和多对一，这是从哪个角度去看得出来的。t_user和t_section的关系，从t_user来看就是一对多，而从t_section的角度来看就是多对一！这种情况都是在多方创建外键！

一方的主键作为多方的外键

 

两者名称相同,数据也相同

 

1.1.14 多对多：

l 例如t_stu和t_teacher表，即一个学生可以有多个老师，而一个老师也可以有多个学生。这种情况通常需要创建中间表来处理多对多关系。例如再创建一张表t_stu_tea表，给出两个外键，一个相对t_stu表的外键，另一个相对t_teacher表的外键。

 

 

合并结果集

1. 作用：合并结果集就是把两个select语句的查询结果合并到一起！

2. 合并结果集有两种方式：

l UNION：去除重复记录，例如：SELECT * FROM t1 UNION SELECT * FROM t2；

l UNION ALL：不去除重复记录，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2。

 

 

3. 要求：被合并的两个结果：列数、列类型必须相同。

连接查询

1.1.15 笛卡尔积的问题

连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1*t2。

 

 

连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。

那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

你能想像到emp和dept表连接查询的结果么？emp一共14行记录，dept表一共4行记录，那么连接后查询出的结果是56行记录。

也就你只是想在查询emp表的同时，把每个员工的所在部门信息显示出来，那么就需要使用主外键来去除无用信息了。

 

 

　　使用主外键关系做为条件来去除无用信息

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;

 

 

 

上面查询结果会把两张表的所有列都查询出来，也许你不需要那么多列，这时就可以指定要查询的列了。

SELECT emp.ename,emp.sal,emp.comm,dept.dname  FROM emp,dept WHERE emp.deptno=dept.deptno;

 

 

 

还可以为表指定别名，然后在引用列时使用别名即可。

SELECT e.ename,e.sal,e.comm,d.dname FROM emp AS e,dept AS d WHERE e.deptno=d.deptno;

 

1.1.16 　内  

上面的连接语句就是内连接，但它不是SQL标准中的查询方式，可以理解为方言！SQL标准的内连接为：(语句中的*可以换成emp.*,dept.*)

SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;

 

内连接的特点：查询结果必须满足条件。例如我们向emp表中插入一条记录：

 

　　其中deptno为50，而在dept表中只有10、20、30、40部门，那么上面的查询结果中就不会出现“张三”这条记录，因为它不能满足e.deptno=d.deptno这个条件。

1.1.17 　外连接（左外连接、右外连接）

外连接的特点：查询出的结果存在不满足条件的可能。

 

左外连接：

SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;

 

左表记录无论是否满足条件都会查询出来，而右表只有满足条件才能出来。左表中不满足条件的记录，右表部分都为NULL

这么说你可能不太明白，我们还是用上面的例子来说明。其中emp表中“张三”这条记录中，部门编号为50，而dept表中不存在部门编号为50的记录，所以“张三”这条记录，不能满足e.deptno=d.deptno这条件。但在左连接中，因为emp表是左表，所以左表中的记录都会查询出来，即“张三”这条记录也会查出，但相应的右表部分显示NULL。

 

 

右外连接

 

右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。例如在dept表中的40部门并不存在员工，但在右连接中，如果dept表为右表，那么还是会查出40部门，但相应的员工信息为NULL。

SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;

 

 

 

 

连接查询心得：

连接不限与两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。

 

子查询(查询的嵌套)

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

l 子查询出现的位置：

Ø where后，作为条件的一部分；(而不能作为被删除和更改的表格)

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

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

Ø any

Ø All

Ø IN

l 子查询结果集的形式：

Ø 单行单列（用于条件）

Ø 单行多列（用于条件）

Ø 多行单列（用于条件）

Ø 多行多列（用于表）

 

 * (***)单行单列：SELECT * FROM 表1 别名1 WHERE 列1 [=、>、<、>=、<=、!=] (SELECT 列 FROM 表2 别名2 WHERE 条件)

    * (**)多行单列：SELECT * FROM 表1 别名1 WHERE 列1 [IN, ALL, ANY] (SELECT 列 FROM 表2 别名2 WHERE 条件)

    * (*)单行多列：SELECT * FROM 表1 别名1 WHERE (列1,列2) IN (SELECT 列1, 列2 FROM 表2 别名2 WHERE 条件)

    * (***)多行多列：SELECT * FROM 表1 别名1 , (SELECT ....) 别名2 WHERE 条件

 

练习：

1. 工资高于甘宁的员工。

分析：

查询条件：工资>甘宁工资，其中甘宁工资需要一条子查询。

 

第一步：查询甘宁的工资

SELECT sal FROM emp WHERE ename='甘宁'

 

第二步：查询高于甘宁工资的员工

SELECT * FROM emp WHERE sal > (${第一步})

 

结果：

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='甘宁')

 

l 子查询作为条件

l 子查询形式为单行单列

 

2. 工资高于30部门所有人的员工信息

分析：

查询条件：工资高于30部门所有人工资，其中30部门所有人工资是子查询。高于所有需要使用all关键字。

 

第一步：查询30部门所有人工资

SELECT sal FROM emp WHERE deptno=30;

 

第二步：查询高于30部门所有人工资的员工信息

SELECT * FROM emp WHERE sal > ALL (${第一步})

 

结果：

SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30)

 

l 子查询作为条件

l 子查询形式为多行单列（当子查询结果集形式为多行单列时可以使用ALL或ANY关键字）

 

3. 查询工作和工资与殷天正完全相同的员工信息

分析：

查询条件：工作和工资与殷天正完全相同，这是子查询

 

第一步：查询出殷天正的工作和工资

SELECT job,sal FROM emp WHERE ename='殷天正'

 

第二步：查询出与殷天正工作和工资相同的人

SELECT * FROM emp WHERE (job,sal) IN (${第一步})

 

结果：

SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename='殷天正')

 

l 子查询作为条件

l 子查询形式为单行多列

 

4. 查询员工编号为1006的员工名称、员工工资、部门名称、部门地址

分析：

查询列：员工名称、员工工资、部门名称、部门地址

查询表：emp和dept，分析得出，不需要外连接（外连接的特性：某一行（或某些行）记录上会出现一半有值，一半为NULL值）

条件：员工编号为1006

 

第一步：去除多表，只查一张表，这里去除部门表，只查员工表

SELECT ename, sal FROM emp e WHERE empno=1006

 

第二步：让第一步与dept做内连接查询，添加主外键条件去除无用笛卡尔积

SELECT e.ename, e.sal, d.dname, d.loc  FROM emp e, dept d WHERE e.deptno=d.deptno AND empno=1006

 

第二步中的dept表表示所有行所有列的一张完整的表，这里可以把dept替换成所有行，但只有dname和loc列的表，这需要子查询。

第三步：查询dept表中dname和loc两列，因为deptno会被作为条件，用来去除无用笛卡尔积，所以需要查询它。

SELECT dname,loc,deptno FROM dept;

 

第四步：替换第二步中的dept

SELECT e.ename, e.sal, d.dname, d.loc FROM emp e, (SELECT dname,loc,deptno FROM dept) d WHERE e.deptno=d.deptno AND e.empno=1006

 

l 子查询作为表

l 子查询形式为多行多列

 

练习(使用exam库)

1.1.18 单表练习

/*

1. 查询出部门编号为30的所有员工

2. 所有销售员的姓名、编号和部门编号。

3. 找出奖金高于工资的员工。

4. 找出奖金高于工资60%的员工。

5. 找出部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料。

6. 找出部门编号为10中所有经理，部门编号为20中所有销售员，还有即不是经理又不是销售员但其工资大或等于20000的所有员工详细资料。

8. 无奖金或奖金低于1000的员工。

9. 查询名字由三个字组成的员工。

10.查询2000年入职的员工。

11. 查询所有员工详细信息，用编号升序排序

12. 查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序

13. 查询每个部门的平均工资

14. 查询每个部门的雇员数量。

15. 查询每种工作的最高工资、最低工资、人数

16. 显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于50000，输出结果按月工资的合计升序排列

*/

 

/*1. 查询出部门编号为30的所有员工*/

SELECT *

FROM emp

WHERE deptno=30;

 

/*2. 查询所有销售员的姓名、编号和部门编号。*/

SELECT ename, empno, deptno

FROM emp

WHERE job='销售员';

 

/*3. 找出奖金高于工资的员工。*/

SELECT *

FROM emp

WHERE comm > sal

 

/*4. 找出奖金高于工资60%的员工。*/

SELECT *

FROM emp

WHERE comm > sal*0.6;

 

/*5. 找出部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料。*/

SELECT *

FROM emp

WHERE (deptno=10 AND job='经理') OR (deptno=20 AND job='销售员')

 

/*6. 找出部门编号为10中所有经理，部门编号为20中所有销售员，还有即不是经理又不是销售员但其工资大或等于20000的所有员工详细资料。*/

SELECT *

FROM emp

WHERE (deptno=10 AND job='经理') OR (deptno=20 AND job='销售员') OR (job NOT IN ('经理', '销售员') AND sal >= 20000)

 

/*8. 无奖金或奖金低于1000的员工。*/

SELECT *

FROM emp

WHERE comm IS NULL OR comm < 1000

 

/*9. 查询名字由三个字组成的员工。*/

SELECT *

FROM emp

WHERE ename LIKE '___'

 

/*10.查询2000年入职的员工。*/

SELECT *

FROM emp

WHERE hiredate LIKE '2000-%'

 

/*11. 查询所有员工详细信息，用编号升序排序*/

SELECT *

FROM emp

ORDER BY empno

 

/*12. 查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序*/

SELECT *

FROM emp

ORDER BY sal DESC, hiredate ASC

 

/*13. 查询每个部门的平均工资*/

SELECT deptno, AVG(sal)

FROM emp

GROUP BY deptno

 

/*14. 查询每个部门的雇员数量。*/

SELECT deptno, COUNT(*)

FROM emp

GROUP BY deptno

 

/*15. 查询每种工作的最高工资、最低工资、人数*/

SELECT job, MAX(sal), MIN(sal), COUNT(*)

FROM emp

GROUP BY job

/*16. 显示非销售人员工作名称以及非销售人员中从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于50000，输出结果按月工资的合计升序排列*/

/*

列：工作名称、工资和(分组信息)

表：emp

条件：无

分组：从事同一工作的工资和，即使用job分组

分组条件：工资合计>50000，这是分组条件，而不是where条件

排序：工资合计排序，即sum(sal) asc

*/

SELECT job,SUM(sal) FROM emp WHERE job!='销售员' GROUP BY job HAVING SUM(sal)>50000 ORDER BY SUM(sal) ASC;

1.1.19 多表练习

1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数

2. 

(1)列出所有员工的姓名及其直接上级的姓名,没有领导的员工的信息不查询

(2)列出所有员工的姓名及其直接上级的姓名,没有领导的员工的信息也查询

(3)列出所有员工的姓名及其直接上级的姓名,没有领导的员工的信息也查询,并将该员工的领导的姓名设置为’提莫大魔王’

3. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称

4. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

5. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。

6. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。

7.  列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。

8. 列出与庞统从事相同工作的所有员工及部门名称

9. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。

10.列出薪金比关羽高的所有员工。

11.列出所有文员的姓名及其部门名称，部门的人数。

 

 

2.列出所有具有领导员工的姓名及其直接领导的姓名

这种情况如果没有领导的员工的信息就没有办法查询出来

SELECT e.ename,p.ename FROM emp e,emp p WHERE e.mgr=p.empno;

除了满足题目要求外,将没有领导的员工姓名也查询出来

SELECT e.ename,p.ename FROM emp e LEFT OUTER JOIN emp p ON e.mgr=p.empno;

除了满足题目要求外,将没有领导的员工姓名也查询出来,并且将该员工的领导设置成'提莫大魔王'

SELECT e.ename ,IFNULL(p.ename,'提莫大魔王')'领导' FROM emp e LEFT OUTER JOIN emp p ON e.mgr=p.empno;

 

1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。

列：d.deptno, d.dname, d.loc, 部门人数

表：dept d, emp e

条件：e.deptno=d.deptno

*/

SELECT d.*, z1.cnt

FROM dept d, (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno) z1

WHERE d.deptno = z1.deptno(这行语句说明了该部门下至少有一个员工)

/*

 

3. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。

列：e.empno, e.ename, d.dname

表：emp e, emp m, dept d

条件：e.hiredate<m.hiredate

思路：

1. 先不查部门名称，只查部门编号!

列：e.empno, e.ename, e.deptno

表：emp e, emp m

条件：e.mgr=m.empno, e.hiredate<m.hireadate

*/

SELECT e.empno, e.ename, e.deptno

FROM emp e, emp m

WHERE e.mgr=m.empno AND e.hiredate<m.hiredate

 

 

SELECT e.empno, e.ename, d.dname

FROM emp e, emp m, dept d

WHERE e.mgr=m.empno AND e.hiredate<m.hiredate AND e.deptno=d.deptno

 

 

 

/*

4. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

列：*

表：emp e, dept d

条件：e.deptno=d.deptno

*/

SELECT *

FROM emp e RIGHT OUTER JOIN dept d

ON e.deptno=d.deptno

 

/*

5. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。

列：job, count(*)

表：emp e

条件：min(sal) > 15000

分组：job

*/

SELECT job, COUNT(*)

FROM emp e

GROUP BY job

HAVING MIN(sal) > 15000

 

 

/*

6. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。

列：e.ename

表：emp

条件：e.deptno=(select deptno from dept where dname='销售部')

*/

 

SELECT *

FROM emp e

WHERE e.deptno=(SELECT deptno FROM dept WHERE dname='销售部')

 

 

 

/*

7. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。

列：*

表：emp e

条件：sal>(查询出公司的平均工资)

*/

SELECT e.*, d.dname, m.ename, s.grade

FROM emp e, dept d, emp m, salgrade s

WHERE e.sal>(SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno AND e.mgr=m.empno AND e.sal BETWEEN s.losal AND s.hisal

 

---------------

 

SELECT e.*, d.dname, m.ename, s.grade

FROM

  emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno

        LEFT OUTER JOIN emp m ON e.mgr=m.empno

        LEFT OUTER JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal

WHERE e.sal>(SELECT AVG(sal) FROM emp)

 

 

SELECT * FROM emp;

SELECT * FROM dept;

SELECT * FROM salgrade;

 

 

 

/*

8.列出与庞统从事相同工作的所有员工及部门名称。

列：e.*, d.dname

表：emp e, dept d

条件：job=(查询出庞统的工作)

*/

 

SELECT e.*, d.dname

FROM emp e, dept d

WHERE e.deptno=d.deptno AND job=(SELECT job FROM emp WHERE ename='庞统')

 

 

 

/*

9.列出薪金高于在部门30工作的所有员工的薪金　的员工姓名和薪金、部门名称。

列：e.ename, e.sal, d.dname

表：emp e, dept d

条件；sal>all (30部门薪金)

*/

SELECT e.ename, e.sal, d.dname

FROM emp e, dept d

WHERE e.deptno=d.deptno AND sal > ALL (SELECT sal FROM emp WHERE deptno=30)

 

/*10. 列出薪金比关羽高的所有员工。*/

/*

列：所有

表：emp

条件：sal>关羽的sal，其中关羽的sal需要子查询

*/

SELECT *

FROM emp e

WHERE e.sal > (SELECT sal FROM emp WHERE ename='关羽')

 

/**************************************************/

 

/*11. 列出所有文员的姓名及其部门名称，部门的人数。*/

/*

列：姓名、部门名称、部门人数

表：emp emp dept

条件：job=文员

分组：emp以deptno得到部门人数

连接：emp连接emp分组，再连接dept

*/

SELECT e.ename, d.dname, z.cnt

FROM emp e, (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno) z, dept d

WHERE e.deptno=d.deptno AND z.deptno=d.deptno;