MySQL数据库学习笔记（三） 约束与索引、MySQL中函数、关联查询、Select语句的6大字句以及子查询

前面我们了解到了关系型数据库设计规则：

• 遵循ER模型和三范式（E entity 表示实体的意思，对应到数据库当中的一张表；R relationship 代表关系的意思。）
• 三范式：1.列不能拆 2.唯一标识 3.关系引用主键。

具体表现：

• 将数据放在表中，再将表放入库中。
• 一个一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有唯一性。
• 表具有一些特性，这些特性定义了数据在表中如何存储，类似java中 “类”的设计。
• 表由列组成，我们也称为字段。每个字段描述了它所含有的数据的意义，数据表的设计实际上就是对字段的设计。
• 表中的数据是按行进行存储的，一行即为一条记录。每一行类似于java或者python中的"对象"。

本文将继续对MySQL数据库知识进行学习。

---

1.约束与索引

数据完整性是指数据的精确性和可靠性。

数据完整性的考量方面
1.实体完整性：例如，同一张表中，不能存在两条完全相同无法区分的记录。
2.域完整性：例如，年龄范围0-120，性别范围"男/女"。
3.引用完整性：例如，员工所在部门，在部门表中要能够找到这个部门。
4.用户自定义完整性：例如用户名唯一、密码不能为空等。

约束是用来对数据业务规则和数据完整性进行实施与维护。约束的作用范围仅限在当前数据库，约束可以被当作数据库对象来进行处理，它们具有名称和关联模式。注意：约束是逻辑约束，不会因为设置约束而额外占用空间。

根据约束的特点，可将约束分为下面这几种约束：

根据约束的特点，对约束进行分类
1.键约束：主键约束、外键约束、唯一键约束
2.Not NULL约束：非空约束
3.Check约束：检查约束
4.Default约束：默认值约束
5.自增约束

索引是一个单独的、物理的存储在数据页上的数据库结构，它是表中一列或若干列值的集合和相应的指向表中数据值的物理标识数据页的逻辑指针清单(类似于新华字典的目录索引页)。

索引的存在会增加数据库的存储空间，同时插入、修改数据的时间开销变多(因为插入和修改数据时，索引也会发生变化)，但是可以大大加快查询速度。所以应该在键列、或其他经常需要查询、排序、按范围查找的列上建立索引，而对于在查询中很少使用和参考的列、修改非常频繁的列等列上不应该创建索引。

注意事项：

• MySQL会在主键、唯一键、外键列上自动创建索引，其他列需要创建索引的话，需要手动创建。
• 删除主键时，对应的索引也会删除。
• 删除唯一键的方式也是通过对应的索引来实现的。
• 删除外键，外键列上的索引还存在，如果需要删除，需要单独删除索引。

---

2.DDL(约束)

2.1 查看某个表的约束

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
或
SHOW CREATE TABLE 表名;

2.2 查看某个表的索引

SHOW INDEX FROM 表名称;

2.3 主键约束：primary key

主键分为单列主键和复合主键。

主键的特点
1.唯一并且非空
2.一个表中只能只有一个主键约束
3.主键约束名称为PRIMARY KEY
4.创建主键会自动对应的索引，同样删除主键对应的索引也会删除

（1）在建表时指定主键约束

create table 【数据名.】表名(
	字段名1 数据类型  primary key ,
	....
);
或
create table 【数据名.】表名(
	字段名1 数据类型,
	....,
    primary key(字段名1)
);
或
create table 【数据名.】表名(
    字段名1 数据类型,
    字段名2 数据类型,
	....,
    primary key(复合主键字段列表)#如果是复合主键，那么就需要在所有字段列表后面使用这种形式指定，不能在字段后面直接加primary key
);

（2）在建表后指定主键约束

alter table 表名称 add primary key (主键字段列表);

（3）如何删除主键约束

alter table 表名称 drop primary key;

---

2.4 唯一键约束：unique key

唯一键约束特点
1.同一个表可以有多个唯一约束。
2.唯一约束可以是某一个列的值唯一，也可以多个列组合值的唯一。
3.MySQL会给唯一约束的列上默认创建一个唯一约束。
4.删除唯一键只能通过删除对应索引的方式删除，删除时需要指定唯一键索引名。

（1）在建表时指定唯一键约束

create table 【数据名.】表名(
	字段名1 数据类型  primary key ,
	字段名2 数据类型 unique key,
	....
);

create table 【数据名.】表名(
    字段名1 数据类型  primary key ,
    字段名2 数据类型,
    字段名3 数据类型,
	....,
    unique key(复合唯一字段列表)#如果是复合唯一键，那么就需要在所有字段列表后面使用这种形式指定，不能在字段后面直接加unique key
);

（2）在建表后增加唯一键约束

alter table表名称 add 【constraint 约束名】 unique 【key】 (字段名列表);
#如果没有指定约束名，(字段名列表)中只有一个字段的，默认是该字段名，如果是多个字段的默认是字段名列表的第1个字段名。也可以通过show index from 表名;来查看

（3）如何删除唯一键约束

ALTER TABLE 表名称 DROP INDEX 唯一性约束名;
#注意：如果忘记名称，可以通过“show index from 表名称;”查看

---

2.5 外键约束：foreign key

外键特点
1.外键约束是保证一个或者两个表之间参照完整性，外键是构建一个表的两个字段或者两个表的两个字段之间的参照关系。
2.在创建外键约束时，如果不给外键约束名称，默认名不是列名，而是产生一个外键名，也可以指定外键约束名。
3.当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但索引名是列名，不是外键的约束名。
4.删除外键时，关于外键列上的普通索引需要单独删除。

建立外键约束的要求：

• 1.在从表中建立外键，而且主表要先存在。
• 2.一个表可以建立多个外约束。
• 3.从表的外键列，在主表中引用的只能是键列（主键、唯一键、外键）。
• 4.从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样。

外键约束具有5个约束等级：

• Cascade方式：在父表上update/delete记录时，同步update/delete掉子表的匹配记录。
• Set null方式：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null。
• No action方式：如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作。
• Restrict方式：同no action, 都是立即检查外键约束。注意：如果没有指定等级，就相当于Restrict方式。
• Set default方式：父表有变更时,子表将外键列设置成一个默认的值，但Innodb不能识别。

（1）在建表时指定外键约束

create table 【数据名.】表名(
	字段名1 数据类型  primary key ,
	字段名2 数据类型 【unique key】,
	....,
    foreign key (从表字段) references 主表名(主表字段) 【on update 外键约束等级】【on delete 外键约束等级【
    #外键只能在所有字段列表后面单独指定
);

create table 【数据名.】表名(
    字段名1 数据类型,
    字段名2 数据类型,
	....,
    primary key(复合主键字段列表),#如果是复合主键，那么就需要在所有字段列表后面使用这种形式指定，不能在字段后面直接加primary key
    unique key(复合唯一字段列表),#如果是复合唯一键，那么就需要在所有字段列表后面使用这种形式指定，不能在字段后面直接加unique key
    foreign key (从表字段) references 主表名(主表字段) 【on update 外键约束等级】【on delete 外键约束等级【
    #外键只能在所有字段列表后面单独指定
);

（2）建表后指定外键约束

alter table表名称 add 【constraint 约束名】 foreign key (从表字段名) references 主表名(主表被参照字段名) 【on update xx】[on delete xx];

（3）删除外键约束

ALTER TABLE 表名称 DROP FOREIGN KEY 外键约束名;
#查看约束名 SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
#删除外键约束不会删除对应的索引，如果需要删除索引，需要用ALTER TABLE 表名称 DROP INDEX 索引名;
#查看索引名 show index from 表名称;

---

2.6 非空约束：Not NULL

Not NULL非空约束，规定某个字段不能为空。

（1）建表时给某个字段指定非空约束

create table 【数据名.】表名(
	字段名1 数据类型  primary key ,
	字段名2 数据类型 【unique key】 【not null】,
	....,
    foreign key (从表字段) references 主表名(主表字段) 【on update 外键约束等级】【on delete 外键约束等级【
    #外键只能在所有字段列表后面单独指定
);

create table 【数据名.】表名(
    字段名1 数据类型 【not null】,
    字段名2 数据类型 【not null】,
	....,
    primary key(复合主键字段列表),#如果是复合主键，那么就需要在所有字段列表后面使用这种形式指定，不能在字段后面直接加primary key
    unique key(复合唯一字段列表),#如果是复合唯一键，那么就需要在所有字段列表后面使用这种形式指定，不能在字段后面直接加unique key
    foreign key (从表字段) references 主表名(主表字段) 【on update 外键约束等级】【on delete 外键约束等级【
    #外键只能在所有字段列表后面单独指定
);

（2）建表后指定某个字段非空

ALTER TABLE 表名称 MODIFY 字段名 数据类型 NOT NULL 【default 默认值】;
#如果该字段原来设置了默认值约束，要跟着一起再写一遍，否则默认值约束会丢失

（3）在建表后取消某个字段非空

ALTER TABLE 表名称 MODIFY 字段名 数据类型 【default 默认值】;
#如果该字段原来设置了默认值约束，要跟着一起再写一遍，否则默认值约束会丢失

---

2.7 默认值约束：Default

（1）建表时给某个字段指定默认约束

create table 【数据名.】表名(
	字段名1 数据类型  primary key ,
	字段名2 数据类型 【unique key】 【not null】 【default 默认值】,
	....,
    foreign key (从表字段) references 主表名(主表字段) 【on update 外键约束等级】【on delete 外键约束等级【
    #外键只能在所有字段列表后面单独指定
);

create table 【数据名.】表名(
    字段名1 数据类型 【not null】 【default 默认值】,
    字段名2 数据类型 【not null】 【default 默认值】,
	....,
    primary key(复合主键字段列表),#如果是复合主键，那么就需要在所有字段列表后面使用这种形式指定，不能在字段后面直接加primary key
    unique key(复合唯一字段列表),#如果是复合唯一键，那么就需要在所有字段列表后面使用这种形式指定，不能在字段后面直接加unique key
    foreign key (从表字段) references 主表名(主表字段) 【on update 外键约束等级】【on delete 外键约束等级【
    #外键只能在所有字段列表后面单独指定
);

（2）建表后指定某个字段的默认值约束

ALTER TABLE 表名称 MODIFY 字段名 数据类型  【default 默认值】 【NOT NULL】;
#如果该字段原来设置了非空约束，要跟着一起再写一遍，否则非空约束会丢失

（3）建表后取消某个字段的默认值约束

ALTER TABLE 表名称 MODIFY 字段名 数据类型 【NOT NULL】;
#如果该字段原来设置了非空约束，要跟着一起再写一遍，否则非空约束会丢失

---

2.8 自增约束：auto_increment

关于自增长auto_increment
1.一个表最多只能有一个自增长列
2.自增长列必须是键列（主键列，唯一键列，外键列），并且要求非空
3.自增列必须是整数类型
4.InnoDB表的自动增长列可以手动插入，但是插入的值如果是空或者0，则实际插入的将是自动增长后的值

（1）建表时指定自增长列

create table 【数据名.】表名(
	字段名1 数据类型  primary key auto_increment,
	字段名2 数据类型 【unique key】 【not null】 【default 默认值】,
	....
);

或
create table 【数据名.】表名(
	字段名1 数据类型  primary key ,
	字段名2 数据类型 【unique key  not null】 auto_increment,
	....
);

（2）建表后指定自增长列

alter table 【数据名.】表名 modify 自增字段名 数据类型 auto_increment;

（3）如何删除自增约束

alter table 【数据名.】表名 modify 自增字段名 数据类型;

---

3.DML（约束）

• 1.如果某列有自增约束，如何添加字段的值

添加数据时，对于自增列：

insert into 【数据库名.]表名称 values(值列表)；#在值列表中，对应自增列可以赋值为null和0

insert into 【数据库名.]表名称(部分字段列表) values(值列表)；#自增列在(部分字段列表)中不写就可以

• 2.如果某列有默认值约束，如何添加、修改该字段的值

对于有默认值，添加数据时：

insert into 【数据库名.]表名称 values(值列表)；#在值列表中，对应默认值列，如果想用默认值，用default

insert into 【数据库名.]表名称(部分字段列表) values(值列表)；#对应默认值列，如果想用默认值，在(部分字段列表)中不写就可以

修改数据：

update 【数据库名.]表名称 set 字段名1 = 值1, 字段名2 = 值2 。。。 【where 条件】; #对应默认值列，如果想用默认值，写字段名 = default就可以

---

4.MySQL中函数

两种SQL函数：单行函数与多行函数。

4.1 单行函数

单行函数的特点：

• 只对一行进行变换，每行返回一个结果
• 可以嵌套
• 参数可以是一字段或一个值或者一个表达式

---

4.1.1 字符串函数

函数	用法
CONCAT(S1,S2,......,Sn)	连接S1,S2,......,Sn为一个字符串
CONCAT_WS(s, S1,S2,......,Sn)	同CONCAT(s1,s2,...)函数，但是每个字符串之间要加上s
CHAR_LENGTH(s)	返回字符串s的字符数
LENGTH(s)	返回字符串s的字节数，和字符集有关
INSERT(str, index , len, instr)	将字符串str从第index位置开始，len个字符长的子串替换为字符串instr
UPPER(s) 或 UCASE(s)	将字符串s的所有字母转成大写字母
LOWER(s) 或LCASE(s)	将字符串s的所有字母转成小写字母
LEFT(s,n)	返回字符串s最左边的n个字符
RIGHT(s,n)	返回字符串s最右边的n个字符
LPAD(str, len, pad)	用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str ,len, pad)	用字符串pad对str最右边进行填充，直到str的长度为len个字符
LTRIM(s)	去掉字符串s左侧的空格
RTRIM(s)	去掉字符串s右侧的空格
TRIM(s)	去掉字符串s开始与结尾的空格
TRIM(【BOTH 】s1 FROM s)	去掉字符串s开始与结尾的s1
TRIM(【LEADING】s1 FROM s)	去掉字符串s开始处的s1
TRIM(【TRAILING】s1 FROM s)	去掉字符串s结尾处的s1
REPEAT(str, n)	返回str重复n次的结果
REPLACE（str, a, b）	用字符串b替换字符串str中所有出现的字符串a
STRCMP(s1,s2)	比较字符串s1,s2
SUBSTRING(s,index,len)	返回从字符串s的index位置其len个字符

 

• 举例1：大小写控制函数

函数	结果
LOWER('SQL Course')	sql course
UPPER('SQL Course')	SQL COURSE

这类函数改变字符的大小写。

• 举例2：字符控制函数

函数	结果
CONCAT('Hello','World')	HelloWorld
SUBSTR('HelloWorld',1,5)	Hello
LENGTH('HelloWorld')	10
INSTR('HelloWorld','W')	6
LPAD(salary,10,'*')	*****24000
RPAD(salary,10, '*')	24000*****
TRIM('H' FROM 'HelloWorld')	elloWorld
REPLACE('abcd','b','m')	amcd

---

4.1.2 数值函数

函数	用法
ABS(x)	返回x的绝对值
CEIL(x)	返回大于x的最小整数值
FLOOR(x)	返回小于x的最大整数值
MOD(x,y)	返回x/y的模
RAND()	返回0~1的随机值
ROUND(x,y)	返回参数x的四舍五入的有y位的小数的值
TRUNCATE(x,y)	返回数字x截断为y位小数的结果
SQRT(x)	返回x的平方根
POW(x,y)	返回x的y次方

举例1：ROUND:四舍五入

ROUND(45.926, 2)     -->  45.93

• 举例2：TRUNCATE:截断

TRUNCATE(45.926)      --> 45

• 举例3：MOD:求余

MOD(1600, 300)	 --> 100

---

4.1.3 日期函数

函数	用法
CURDATE() 或 CURRENT_DATE()	返回当前日期
CURTIME() 或 CURRENT_TIME()	返回当前时间
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()	返回当前系统日期时间
YEAR(date) / MONTH(date) / DAY(date) / HOUR(time) / MINUTE(time) / SECOND(time)	返回具体的时间值
WEEK(date) / WEEKOFYEAR(date)	返回一年中的第几周
DAYOFWEEK()	返回周几，注意：周日是1，周一是2，。。。周六是7
WEEKDAY(date)	返回周几，注意，周1是0，周2是1，。。。周日是6
DAYNAME(date)	返回星期：MONDAY,TUESDAY.....SUNDAY
MONTHNAME(date)	返回月份：January，。。。。。
DATEDIFF(date1,date2) / TIMEDIFF(time1, time2)	返回date1 - date2的日期间隔 / 返回time1 - time2的时间间隔
DATE_ADD(datetime, INTERVAL expr type)	返回与给定日期时间相差INTERVAL时间段的日期时间
DATE_FORMAT(datetime ,fmt)	按照字符串fmt格式化日期datetime值
STR_TO_DATE(str, fmt)	按照字符串fmt对str进行解析，解析为一个日期

其中：

（1）DATE_ADD(datetime,INTERVAL expr type)

表达式类型：

参数类型	参数类型
YEAR	YEAR_MONTH
MONTH	DAY_HOUR
DAY	DAY_MINUTE
HOUR	DAY_SECOND
MINUTE	HOUR_MINUTE
SECOND	HOUR_SECOND
	MINUTE_SECOND

举例：

SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);
SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR);   #可以是负数
SELECT DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH);   #需要单引号

 

（2）DATE_FORMAT(datetime,fmt) 和 STR_TO_DATE(str, fmt)

格式符	说明	格式符	说明
%Y	4位数字表示年份	%y	表示两位数字表示年份
%M	月名表示月份（January,....）	%m	两位数字表示月份（01,02,03。。。）
%b	缩写的月名（Jan.，Feb.，....）	%c	数字表示月份（1,2,3,...）
%D	英文后缀表示月中的天数（1st,2nd,3rd,...）	%d	两位数字表示月中的天数(01,02...)
%e	数字形式表示月中的天数（1,2,3,4,5.....）
%H	两位数字表示小数，24小时制（01,02..）	%h和%I	两位数字表示小时，12小时制（01,02..）
%k	数字形式的小时，24小时制(1,2,3)	%l	数字形式表示小时，12小时制（1,2,3,4....）
%i	两位数字表示分钟（00,01,02）	%S和%s	两位数字表示秒(00,01,02...)
%W	一周中的星期名称（Sunday...）	%a	一周中的星期缩写（Sun.，Mon.,Tues.，..）
%w	以数字表示周中的天数(0=Sunday,1=Monday....)
%j	以3位数字表示年中的天数(001,002...)	%U	以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第一天
%u	以数字表示年中的第几周，（1,2,3。。）其中Monday为周中第一天
%T	24小时制	%r	12小时制
%p	AM或PM	%%	表示%

---

4.1.4 流程函数

函数	用法
IF(value,t ,f)	如果value是真，返回t，否则返回f
IFNULL(value1, value2)	如果value1不为空，返回value1，否则返回value2
CASE WHEN 条件1 THEN result1 WHEN 条件2 THEN result2 .... [ELSE resultn] END	相当于Java的if...else if...else...
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 .... [ELSE 值n] END	相当于Java的switch...case...

举例1：

SELECT employee_id,12 * salary * (1 + IFNULL(commission_pct,0)) as "年薪"
FROM employees;

举例2：

SELECT last_name, job_id, salary,
       CASE job_id WHEN 'IT_PROG'  THEN  1.10*salary
                   WHEN 'ST_CLERK' THEN  1.15*salary
                   WHEN 'SA_REP'   THEN  1.20*salary
       ELSE      salary END     "实发工资"
FROM   employees;

---

4.1.5 其他函数

函数	用法
database()	返回当前数据库名
version()	返回当前数据库版本
user()	返回当前登录用户名
password(str)	返回字符串str的加密版本，41位长的字符串
md5(str)	返回字符串str的md5值，也是一种加密方式

---

4.2 分组函数

• 分组函数的含义：
  分组函数作用于一组数据，并对一组数据返回一个值。
  

组函数类型
avg()：平均函数
sum()：求和函数
max()：最大值函数
min()：最小值函数
count)()：计数函数

（1）可以对数值型数据使用avg()和sum(）函数

SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM   employees
WHERE  job_id LIKE '%REP%';

（2）可以对任意数据类型的数据使用min和max函数

SELECT MIN(hire_date), MAX(hire_date)
FROM	  employees;

（3）COUNT(*)返回表中记录总数,适用于任意数据类型。

SELECT COUNT(*)
FROM	  employees
WHERE  department_id = 50;

（4）COUNT(expr) 返回expr不为空的记录总数

SELECT COUNT(commission_pct)
FROM   employees
WHERE  department_id = 50;

---

5.关联查询

关联查询七种结果
（1）A∩B
（2）A
（3）A - A∩B
（4）B
（5）B - A∩B
（6）A ∪ B
（7）A ∪B- A∩B 或者 (A - A∩B) ∪ （B - A∩B）

---

如何实现？

• （1）内连接
• （2）外连接：左外连接、右外连接、全外连接（mysql使用union代替全外连接）

1、内连接：实现A∩B

select 字段列表
from A表 inner join B表
on 关联条件
where 等其他子句;

或

select 字段列表
from A表 , B表
where 关联条件 and 等其他子句;

代码示例：

#查询员工的姓名和他所在的部门的名称
#员工的姓名在t_employee
#部门的名称在t_department
SELECT ename "员工的姓名",dname "部门名称"
FROM t_employee INNER JOIN t_department
ON t_employee.did = t_department.did

SELECT ename "员工的姓名",dname "部门名称"
FROM t_employee , t_department
WHERE t_employee.did = t_department.did


#查询薪资高于20000的男员工的姓名和他所在的部门的名称
SELECT ename "员工的姓名",dname "部门名称"
FROM t_employee INNER JOIN t_department
ON t_employee.did = t_department.did
WHERE salary>20000 AND gender = '男'

2、左外连接

#实现查询结果是A
select 字段列表
from A表 left join B表
on 关联条件
where 等其他子句;

#实现A -  A∩B
select 字段列表
from A表 left join B表
on 关联条件
where 关联字段 is null and 等其他子句;

代码示例：

#查询所有员工的姓名和他所在的部门的名称
SELECT ename "员工的姓名",dname "部门名称"
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did


#查询所有没有部门的员工
SELECT ename "员工的姓名",dname "部门名称"
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL

3、右外连接

#实现查询结果是B
select 字段列表
from A表 right join B表
on 关联条件
where 等其他子句;

#实现B -  A∩B
select 字段列表
from A表 right join B表
on 关联条件
where 关联字段 is null and 等其他子句;

代码示例：

#查询所有部门，以及所有部门下的员工信息
SELECT * 
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.did

#查询那些没有员工属于它的部门
SELECT * 
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL

4、用union代替全外连接

#实现查询结果是A∪B
#用左外的A，union 右外的B
select 字段列表
from A表 left join B表
on 关联条件
where 等其他子句

union 

select 字段列表
from A表 right join B表
on 关联条件
where 等其他子句;



#实现A∪B -  A∩B  或   (A -  A∩B) ∪ （B - A∩B）
#使用左外的 (A -  A∩B)  union 右外的（B - A∩B）
select 字段列表
from A表 left join B表
on 关联条件
where 关联字段 is null and 等其他子句

union

select 字段列表
from A表 right join B表
on 关联条件
where 关联字段 is null and 等其他子句

代码示例：

#查询所有员工，所有部门，包括没有员工的部门，和没有部门的员工
SELECT *
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did

UNION

SELECT *
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.did


#查询那些没有部门的员工和所有没有员工的部门

#没有部门的员工
SELECT *
FROM t_employee LEFT JOIN t_department
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL

UNION 

#所有没有员工的部门
SELECT *
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.did
WHERE t_employee.did IS NULL

5、自连接

两个关联查询的表是同一张表，通过取别名的方式来虚拟成两张表

select 字段列表
from 表名 别名1 inner/left/right join 表名 别名2
on 别名1.关联字段 = 别名2的关联字段
where 其他条件

代码示例：

#查询员工的编号，姓名，薪资和他领导的编号，姓名，薪资
#这些数据全部在员工表中
#把t_employee表，即当做员工表，又当做领导表
#领导表是虚拟的概念，我们可以通过取别名的方式虚拟
SELECT emp.eid "员工的编号",emp.ename "员工的姓名" ,emp.salary "员工的薪资",
	mgr.eid "领导的编号" ,mgr.ename "领导的姓名",mgr.salary "领导的薪资"
FROM t_employee emp INNER JOIN t_employee mgr
#t_employee emp：如果用emp.，表示的是员工表的
#t_employee mgr：如果用mgr.，表示的是领导表的
ON emp.mid = mgr.eid

#表的别名不要加""，给列取别名，可以用""，列的别名不使用""也可以，但是要避免包含空格等特殊符号。

---

6. select语句的6大子句

select语句的6大顺序：

select语句的6大顺序
（1）from：从哪些表中筛选
（2）where：从表中筛选的条件
（3）group by：分组依据
（4）having：在统计结果中再次筛选
（5）order by：排序，降序：desc，升序：asc
（6）limit：分页；limit m,n m=（第几页 - 1）*每页的数量，n = 每页的数量

代码示例：

#查询每个部门的男生的人数，并且显示人数超过5人的，按照人数降序排列，
#每页只能显示10条，我要第2页
SELECT did,COUNT(*) "人数"
FROM t_employee
WHERE gender = '男'
GROUP BY did
HAVING COUNT(*)>5
ORDER BY 人数 DESC
LIMIT 10,10

---

6.1 group by与分组函数

可以使用GROUP BY子句将表中的数据分成若干组**

SELECT column, group_function(column)
FROM table
[WHERE	condition]
[GROUP BY	group_by_expression];

明确：WHERE一定放在FROM后面GROUP BY 前面

在SELECT列表中所有未包含在分组函数中的列都应该包含在 GROUP BY子句中

SELECT   department_id, AVG(salary)
FROM     employees
GROUP BY department_id ;

包含在 GROUP BY 子句中的列不必包含在SELECT 列表中

SELECT   AVG(salary)
FROM     employees
GROUP BY department_id ;

使用多个列分组

SELECT   department_id dept_id, job_id, SUM(salary)
FROM     employees
GROUP BY department_id, job_id ;

6.2 having与分组函数

SELECT   department_id, MAX(salary)
FROM     employees
GROUP BY department_id
HAVING   MAX(salary)>8000 ;

having与where的区别？

• （1）where是从表中筛选的条件，而having是统计结果中再次筛选
• （2）where后面不能加“分组/聚合函数”，而having后面可以跟

#统计部门平均工资高于8000的部门和平均工资
SELECT   department_id, AVG(salary)
FROM     employees
WHERE    AVG(salary) > 8000 #错误
GROUP BY department_id;;

#统计部门平均工资高于8000的部门和平均工资
SELECT   department_id, AVG(salary)
FROM     employees
GROUP BY department_id
HAVING   AVG(salary)>8000 ;;

#查询每个部门的男生的人数，并且显示人数超过5人的，按照人数降序排列，
#每页只能显示10条，我要第2页
SELECT did,COUNT(*) "人数"
FROM t_employee
WHERE gender = '男'
GROUP BY did
HAVING COUNT(*)>5
ORDER BY 人数 DESC
LIMIT 10,10

---

7. 子查询

子查询：表示在一个查询嵌在另外一个查询之中。

根据子查询所在的位置不同进行分类
1.where型：(1)子查询是单值结果，可以使用(=,>等比较运算符)；（2）子查询是多值结果，那么可以使用(in,all,any等运算符)
2.from型：必须给子查询取别名
3.exists型
注意：不管子查询在哪里，子查询必须使用()括起来

查询全公司最高工资的员工信息
select * from 员工表 where 薪资 = (select max(薪资) from 员工表);

查询每个部门的编号，名称，平均工资
select 部门编号, 部门名称， 平均工资
from 部门表 inner join (select 部门编号，avg(薪资) from 员工表  group by 部门编号) temp
on 部门表.部门编号 = temp.部门编号

查询那些有员工的部门
select 部门编号, 部门名称 from 部门表
where exists (select * from 员工表  where 部门表.部门编号 = 员工表.部门编号);

---

总结

本节对约束与索引、MySQL中函数、关联查询以及Select语句的6大字句进行了解，其中最重要的，需要重点掌握的是Select语句的6大字句。这些是数据库中最基本的知识，认真掌握了对后面学习会有很大的帮助。