数据库 笔记

数据库

一.数据库基本概念

1.数据库DataBase

2.定义:保存一组数据的仓库就是数据库

#### 例

BirdBoot项目中，我们为了保存一组用户信息，创建了一个目录users。里面用若干个文件保存每一个用户信息

此时users目录就可以称为是一个数据库

只不过对于这些数据的维护操作，要么手动，要么通过自己编码来维护。 

手动操作效率低

编码来维护:维护成本高，可扩展性差

二.数据库管理系统(DBMS)

1. 定义:一套独立可运行的软件，用于维护磁盘上的数据。维护性好，性能好，可扩展性强。

2.常见的DBMS

- MySQL

- MariaDB

- Oracle   

- DB2

- SQLServer

三.数据库管理系统中常见的概念

1.库:是表的集合，一个库中可以存放若干张表。通常库是服务于项目的。

2.表:是数据的集合，具有一组相同属性的数据存放在同一张表中。

3.行:称为是记录，表中每一条数据

4.列:称为是字段，每一条记录的属性

##### 例

BirdBoot项目中，涉及到的数据有用户信息，文章信息。用户信息就可以设计一张用户表保存。文章信息可以设计一张文章表保存

四.如何操作数据库

1.数据库的角色

数据库是一个独立运行的软件，并且是以**服务端**的形式体现的，我们要操作数据库则需要以客户端的角度与服务端建立连接并进行相关的操作

2. 数据库的交互

1)我们连接上数据库后，向其发送SQL语句，数据库会理解该SQL语句的含义并进行对应的操作。

2)SQL语句的标准:SQL92标准。

-所有的DBMS都支持SQL92标准，该标准就是操作数据库的"普通话"。

-不是所有的数据库操作都在SQL92标准中，不在该标准中的操作不同的数据库提供的语法可能完全不同，此时这类SQL称为是"方言"

#### 学习重点

1.学会SQL语句

2.SQL:全称Structured Query Language

五.数据库的连接方式

1.常用的客户端

- 命令行

- 图形化界面

- JDBC

- 集成开发环境(IDE)

2. SQL语言

1)Structured Query Language，结构化查询语言

2)操作数据库的语言

3)SQL语句不区分大小写，但是好的书写习惯:关键字大写，非关键字小写

3.SQL分类

-  DDL数据定义语言，操作数据库对象的语言。数据库对象(库，表，视图，索引，序列)

- DML数据操作语言，是操作表中数据的语言。对表中数据操作的语言(增INSERT，删DELETE，改UPDATE)

-  DQL数据查询语言，是查询表中数据的语言。SELECT语句(重要)

-  DCL 数据控制语言，管理数据库的语言(权限分配等，DBA关心的)

-  TCL 事务控制语言，事务控制语言。

DDL语言-数据定义语言

DDL:.对数据库对象进行操作的语言, 涉及到的关键字CREATE,ALTER,DROP

一.对数据库的操作

1.新建一个数据库

语法:

CREATE DATABASE 数据库名 [charset=字符集]

##### 例

新建一个数据库:mydb

CREATE DATABASE mydb;

2.指定字符集

##### 例

创建数据库mydb1,指定字符集为UTF-8

CREATE DATABASE mydb1 CHARSET=UTF8;

创建数据库mydb2,指定字符集为GBK

CREATE DATABASE mydb2 CHARSET=GBK;

3.查看已创建的数据库

语法:

SHOW DATABASES;

4.查看创建数据库时的信息

语法:

       SHOW CREATE DATABASE 数据库名

##### 例

SHOW CREATE DATABASE mydb;

5.删除数据库

语法:

DROP DATABASE 数据库名

##### 例

DROP DATABASE mydb;

6.切换数据库

语法:

USE 数据库名

##### 例

USE mydb1; //切换数据库到mydb1

USE mydb2;      //切换数据库到mydb2

二.表操作

1.创建表

语法:

CREATE TABLE 表名(

  字段名名1 类型[(长度)] [DEFALUT 默认值] [约束],

         字段名名2 类型,

          ...

)[CHARSET=字符集]

##### 例

准备一个数据库mydb并使用

CREATE DATABASE mydb; 创建数据库mydb

USE mydb; 切换到mydb，那么后面创建表都是创建到这个库中

创建一张表user，保存用户信息(用户名，密码，昵称，年龄)

CREATE TABLE user(

//类型是方言，不同数据库不同，mysql中整数为INT，oraclet为Number

id INT,              

//字符串类型是VARCHAR，长度为字节量，如果是UTF-8编码32字节可以存10个汉字。

username VARCHAR(32),      

    password VARCHAR(32),   

nickname VARCHAR(32),

//对于整数而言，长度表示保存的数字位数。

    age INT(3)              

)

2.查看表结构

语法:

DESC 表名

##### 例

查看user表的结构

DESC user

3. 查看表创建时的信息

语法:

SHOW CREATE TABLE 表名

##### 例

SHOW CREATE TABLE user

4.查看当前数据库中创建的所有表

语法:

SHOW TABLES

5. 修改表名

语法:

RENAME TABLE 原表名 TO 新表名

##### 例

将user表改名为userinfo

RENAME TABLE user TO userinfo

6.删除表

 语法:

DROP TABLE 表名

##### 例

删除表userinfo

DROP TABLE userinfo

7.修改表结构

1)准备一张表

CREATE TABLE hero(

name VARCHAR(32),

age INT(3)

)

2)添加一个字段

-在表末尾追加新字段

   语法:

     ALTER TABLE 表名 ADD 字段名 类型[(长度) 默认值 约束]

###### 例

向表hero的末尾添加一个新字段gender，它的类型是字符串，长度占10字节

ALTER TABLE hero ADD gender VARCHAR(10)

-在表最开始添加字段

  语法:

    ALTER TABLE 表名 ADD 字段名 类型 FIRST

###### 例

在hero表最开始添加id字段，类型为int

ALTER TABLE hero ADD id INT FIRST

-在表中插入新的字段

  语法:在表中现有的字段后面添加新字段

    ALTER TABLE 表名 ADD 字段名 类型 AFTER 表中现有字段名

###### 例

在name字段后面添加密码pwd字段

ALTER TABLE hero ADD pwd VARCHAR(32) AFTER name

3)删除字段

 语法:

   ALTER TABLE 表名 DROP 字段名

##### 例

将hero中的pwd字段删除

ALTER TABLE hero DROP pwd

4)修改表字段

-语法:

  ALTER TABLE 表名 CHANGE 原字段名 新字段名 类型[长度 默认值 约束]

-修改表结构的注意事项

- 修改表结构最好是在表中没有数据的情况下进行

- 当表中含有数据时

  - 尽量不修改表中某字段的类型，否则可能因为现有数据不满足新修改的类型导致修改失败

  - 尽量不缩短字段长度

  - 若为字段新添加约束，该字段现有的数据不能违反该约束

##### 例

修改hero表中的年龄字段长度为5

ALTER TABLE hero CHANGE age age INT(5)

修改hero表中的年龄字段为字符串，长度为20字节

ALTER TABLE hero CHANGE age age VARCHAR(20)

DML语言-数据操作语言

• 定义

DML是对表中数据进行操作的语言，涉及的关键字:INSERT UPDATE DELETE

- INSERT:向表中插入新数据的语句

- UPDATE:修改表中数据的语句

- DELETE:删除表中数据的语句

二. INSERT语句

1.-向表中插入数据

语法

INSERT INTO 表名 (字段1,字段2,字段3,...) VALUES (值1,值2,值3,...)

向表中插入一条记录，将值1插入的到字段1中，将值2插入到字段2中

#### 例

向person表中插入记录

INSERT INTO person (name,age) VALUES ('张三',22);

INSERT INTO person (age,name) VALUES (33,'李四');

INSERT INTO person (name,age) VALUES (36,'王五');   不可以，值的顺序与前面指定的字段不匹配

INSERT INTO person (name,age) VALUES ('王五')       不可以，值的个数与前面指定的字段不匹配

注：

- 在数据库中，字符串的字面量是使用单引号括起来的

- 在INSERT语句中指定的字段与VALUES后面指定的值的顺序，个数，类型要完全一致

- 在INSERT语句中字段指定时的顺序和个数可以与表结构定义时不一致

2.查询表中数据的语法

SELECT * FROM 表名

###### 例

SELECT * FROM person

3.插入默认值

1)INSERT语句中指定字段时，忽略的字段会被插入默认值。当表中字段没有被明确指定默认值时，默认值一律为NULL

##### 例

INSERT INTO person (name) VALUES ('王五')

此时该条记录插入后，age字段使用默认值NULL

2)默认值的指定

注:指定默认值是属于DDL语句的范畴

使用DEFAULT关键字来为字段指定默认值，可以在创建表时，也可以在修改表时指定

-在创建表时为字段指定默认值

CREATE TABLE person(

name VARCHAR(30) DEFAULT '无名氏',

age INT(3) DEFAULT 30

)

-修改表时为字段指定默认值

ALTER TABLE person CHANGE name name VARCHAR(30) DEFAULT '无名氏'

可以使用DESC 表名 在查看表结构时看到字段的默认值

此时向person表中插入数据，若不指定name字段，字段默认值插入'无名氏'

INSERT INTO person (age) VALUES (55)

4.全列插入

1)在INSERT语句中不指定字段时，为全列插入，此时要求VALUES子句后面指定的值的个数，顺序，类型必须与表结构中字段完全一致

语法

INSERT INTO 表名 VALUES(值1,值2,...)

此时就要求VALUES子句中指定的值必须与表结构一致

##### 例

INSERT INTO person VALUES('赵六',15)   

2)补充说明

子句

在SQL语句中一个关键字可以组成一个子句

###### 例

INSERT INTO person(name,age) INSERT子句

VALUES('张三',22)    VALUES子句

这两个子句整体构成了INSERT语句

3)插入默认值

INSERT INTO person VALUES(DEFAULT,15) 使用DEFAULT关键字插入默认值

4)插入NULL值

INSERT INTO person VALUES(NULL,78)  使用NULL关键字插入NULL值

下面是错误示范:

INSERT INTO person VALUES(15,'赵六')  不可以，与person表结构字段顺序不一致

INSERT INTO person VALUES('赵六')      不可以，与person表结构字段顺序不一致

5 批量插入

语法:

INSERT INTO 表名(字段...) VALUES(第一组值...),(第二组值...),....

##### 例

一次插入三条记录

INSERT INTO person(name,age) VALUES('阿猫',11),('阿狗',22),('阿三',33)

三.UPDATE语句-修改表数据

1.语法:

UPDATE 表名

SET 字段1=新值,字段2=新值,...

[WHERE 过滤条件]

#### 例

UPDATE person

SET age=40

此时该SQL执行后，person表中每条记录的age字段都会被改为40

注:当UPDATE语句中不添加WHERE子句时，会将表中所有记录进行修改，这种操作实际开发中很少见

2.WHERE子句在UPDATE中的作用

1)当UPDATE语句添加了WHERE子句时，只有满足WHERE子句要求的记录才会被修改

##### 例

修改张三的年龄为22

UPDATE person

SET age=22

WHERE name='张三'

2)数据库在修改记录时对表是逐行进行的，每一行是否进行修改取决于该行记录是否满足WHERE子句的条件要求

-WHERE子句基础条件:>,>=,<,<=,=,<>(首先"="在数据库中表达等于

"<>"在数据库中表达不等于。"!="也可以表达不等于，但不是SQL92标准)

##### 例

将年龄高于30岁的人改为29

UPDATE person

SET age=29

WHERE age>30

#### 使用表达式修改

将每个人的年龄涨一岁

UPDATE person

SET age=age+1

#### 修改多字段

将李四的名字改为'李老四',年龄改为66

UPDATE person

SET name='李老四',age=66

WHERE name='李四'

四.DELETE语句-删除表中数据

1.语法:

DELETE FROM 表名

[WHERE 过滤条件]

注:DELETE语句通常不会忽略WHERE子句，否则是清空表操作!

#### 例

删除person表中'李老四'

DELETE FROM person

WHERE name='李老四' //仅删除person表中name字段为'李老四'的记录

2.清空表

DELETE FROM person

### 总结

- INSERT语句，向表中插入数据的语言

  - INSERT语句指定的字段顺序，个数可以与表结构不一致，但是VALUES子句后指定的值必须与指定的字段顺序，类型，个数完全一致

  - INSERT语句在指定字段时，可以忽略某些字段，此时插入数据时被忽略的字段会插入默认值

  - INSERT语句中可以使用关键字DEFAULT显示声明插入默认值

  - INSERT语句中可以使用关键字NULL显示声明插入NULL值

  - INSERT语句中不指定字段时为全列插入，此时VALUES后指定的值的顺序，个数，类型要与表结构完全一致。实际开发中不推荐这样的写法。

- UPDATE语句，修改表中记录的语言

  - UPDATE语句通常要指定WHERE子句，如果不指定则为全表修改

- DELETE语句，删除表中记录的语言

  - DELETE语句通常要指定WHERE子句，否则是清空表操作

数据类型

设计一张表:为表中每一个字段指定类型以确保该字段可以保存正确类型的数据

注:类型本身属于方言，不同的数据库类型不完全一样。

一.数字类型

1.整数类型

1)INT类型:占用4个字节，保存范围+-21亿

- INT(m):m是一个数字，可以指定保存的位数

2)BIGINT类型:占用8个字节

2.浮点数类型

DOUBLE(M,N)

- M是一个数字，表示数字的位数

- N是一个数字，表示小数的位数

- M是包含N的位数

- DOUBLE(7,2):可以保存一个7位数，其中2为是小数，整数5位

##### 例

CREATE TABLE person1(

age INT(3), 最大999

    salary DOUBLE(7,2) 最大99999.99   )

- 插入小数

  INSERT INTO person1(salary) VALUES(99999.99)  

- 如果小数位的精度超过了允许精度时，会进行四舍五入

  INSERT INTO person1(salary) VALUES(1234.867)

                                                                             ^7会进行四舍五入

  实际插入表中后，该记录salary字段的值位1234.87                                          

- 如果四舍五入后得到的值超过了该字段可以保存的最大值时会报错

  INSERT INTO person1(salary) VALUES(99999.997)

3)字符类型

#### 定长字符串:CHAR类型是定长字符串

- CHAR(m):m是一个数字，表示长度，单位是字符。m最大可取255

- CHAR类型在磁盘中开辟的长度是固定的。例如:CHAR(3),若字符集为UTF-8，则固定开辟12字节。如果实际保存的数据不足则会补充若干个空格来填充长度

- 优点:在磁盘中保存每条记录的长度是固定的，因此读取速度快

- 缺点:在磁盘中占用空间固定，无论实际保存数据多少，都要占够空间，因此对磁盘空间有浪费

#### 变长字符串:VARCHAR类型

- VARCHAR(m):m是一个数字，表示字符串的长度，单位是字节.最大65535

- 实际占用磁盘空间按照实际保存的数据而定，数据转换出来的字节是多少就保存多少。

  - name VARCHAR(30):name字段保存的字符串转换的字节最多30字节。以UTF-8为例，保存"张三"实际需要6个字节，那么该记录实际开辟的磁盘空间也是6个字节

- 优点:由于磁盘空间占用取决于实际保存的数据，因此不造成磁盘空间浪费

- 缺点:由于数据长度不一致，会导致读取效率低

4)TEXT类型

- TEXT(m):m是一个数字，单位是**字符**，m最大值为65535

5)日期类型

- DATE用于保存年月日

- TIME用于保存时分秒

- DATETIME用于保存年月日时分秒

- TIMESTAMP(时间戳)，保存UTC时间，记录自1970年元旦至表示的时间所经过的毫秒

#### 例

CREATE TABLE userinfo(

id INT, id整数类型

    name VARCHAR(30), name字符串类型，最多30字节

    gender CHAR(1), gender字符串类型，最多1个字符

    birth DATETIME, birth日期类型，保存年月日时分秒

    salary DOUBLE(7,2) salary浮点数类型，最多保存99999.99

)

- 插入一个日期类型的数据时，DATETIME类型为例(可以保存年月日时分秒)，可以使用字符串来表示，但是格式必须为"yyyy-MM-dd hh:mm:ss"(M表示月，m表示分)

  INSERT INTO userinfo(id,name,gender,birth,salary)

  VALUES(1,'张三','男','1998-08-03 10:22:35',5000.65)

- DATETIME类型可以忽略时分秒。时分秒默认为0。

  INSERT INTO userinfo(id,name,gender,birth,salary)

  VALUES(2,'李四','女','2002-03-15',10000)

- DATETIME类型不可以忽略年月日!

  INSERT INTO userinfo(id,name,gender,birth,salary)

  VALUES(3,'王五','男','10:45:16',9995)

- DOUBLE类型精度超出范围时会进行四舍五入，但整数部分超出范围会报错

  INSERT INTO userinfo(id,name,gender,birth,salary)

  VALUES(3,'王五','男','1996-06-27',100000)

约束条件

我们可以为表添加约束条件，可以限制对标的操作必须符合我们施加的约束条件，否则数据库会拒绝该操作

一.主键约束PrimaryKey

- 主键约束要求该字段的值必须非空且唯一

- 主键约束在一张标中只能为一个字段施加

- 主键约束会为表中第一个字段添加，并且通常该字段取名为"id"

#### 例

CREATE TABLE user1(

id INT PRIMARY KEY, 为id字段添加主键约束

name VARCHAR(30),

age INT(3)

)

INSERT INTO user1 (id,name,age) VALUES(1,'张三',22),(2,'李四',33)

- 主键不能插入重复的值

- 主键不能插入NULL值

- 插入数据时不能忽略主键字段(会使用默认值NULL)，如果主键字段有对应默认值生成机制可以忽略

- 修改表记录时，不可以将重复的值修改到主键字段

- 修改表记录时也不可以将NULL值修改到主键字段

**永远不应当更新主键字段!**

二. 自增

具有主键约束的字段通常主键的生成方式由系统完成，而自增就是其中一种方式

##### 例

- 创建表时为主键字段添加自增

  CREATE TABLE user2(

   id INT PRIMARY KEY AUTO_INCREMENT,

      name VARCHAR(30),

      age INT(3)

  )

- 修改表时为主键字段添加自增

##### 例

 修改user1表时为id字段添加主键约束以及自增模式

  ALTER TABLE user1 CHANGE id id INT PRIMARY KEY AUTO_INCREMENT

  修改表时仅添加自增

  ALTER TABLE user1 CHANGE id id INT AUTO_INCREMENT

- 当主键字段具有自增时，插入数据则可以忽略主键字段

  INSERT INTO user2(name,age) VALUES('张三',33)

- 当主键字段具有自增时，可以显示的向主键中插入NULL值，但此时主键字段仍然使用自增(不推荐这样写)

  INSERT INTO user2(id,name,age) VALUES(NULL,'王五',45)

三.非空约束(NOT NULL)

被非空约束施加的字段，该字段的值不能为NULL

#### 例

- 创建表时为字段添加非空约束

  CREATE TABLE user3(

   id INT PRIMARY KEY AUTO_INCREMENT,

      name VARCHAR(30) NOT NULL, name字段施加非空约束

      age INT(3)

  )

  

- 修改表时为字段添加非空约束

##### 例

  为user2表的name字段添加非空约束

  ALTER TABLE user2 CHANGE name name VARCHAR(30) NOT NULL

- 插入数据时，不能向具有非空约束的字段中插入NULL值

- 也不可以修改NULL值到具有非空约束的字段中

四.唯一性约束(UNIQUE)

具有唯一性约束的字段值不允许重复，但是可以为NULL。

#### 例

- 创建表时为字段添加唯一性约束

  CREATE TABLE user4(

   id INT PRIMARY KEY AUTO_INCREMENT,

   name VARCHAR(30) UNIQUE,

      age INT(3)      )

- 修改表时也可以为字段添加唯一性约束

  注意:user3的name本身具有非空约束，但是如果此时添加唯一性约束时会取消非空约束

  ALTER TABLE user3 CHANGE name name VARCHAR(30) UNIQUE

  

  如果不希望取消非空约束则需要一并声明

  ALTER TABLE user3 CHANGE name name VARCHAR(30) NOT NULL UNIQUE

- 插入数据时，不可以将重复的值插入到具有唯一性约束的字段中

  INSERT INTO user4(name,age) VALUES('张三',22) 可以

  INSERT INTO user4(name,age) VALUES('张三',45) 不可以再次插入'张三'到name字段

- 可以插入NULL值到具有唯一性约束的字段中，且可以多次

  INSERT INTO user4(name,age) VALUE(NULL,36) 成功

  INSERT INTO user4(name,age) VALUE(NULL,47) 成功

- 修改记录，也不能将重复的值修改到具有唯一性约束的字段中

  

  

五.检查约束(CHECK)

检查约束允许我们自定义约束条件，仅当满足我们指定的条件才可以进行操作

#### 例

- 创建表时添加CHECK约束

  CREATE TABLE user5(

   id INT PRIMARY KEY AUTO_INCREMENT,

   name VARCHAR(30),

   age INT(3) CHECK(age>0 AND age<120) 要求age字段的值>0且<120

  )

- 插入数据时，age字段的取值必须符合CHECK约束

  INSERT INTO user5(name,age) VALUES('张三',22) 成功

  INSERT INTO user5(name,age) VALUES('李四',160) 失败

- 更新表数据时，也不能违反CHECK约束

六.外键约束

外键约束在实际开发中几乎不会用

DQL语言-数据查询语言

一.DQL语言用来检索表中数据的语言，涉及到的关键字SELECT

执行顺序

SELECT 子句 6

FROM 子句 1

JOIN... ON...子句 2

WHERE 子句 3

GROUP BY 子句 4

HAVING 子句 5

ORDER BY 子句 7

LIMIT 子句       8

二.基础查询

语法:  SELECT 字段1,字段2,... FROM 表名

- SELECT 子句用于指定查询表中的字段

- FROM子句用于指定数据来自那张表

#### 例

- 使用SELECT * FROM 表名，可以从指定的表中查询所有字段

  USE tedu

  检索teacher表中的所有字段的所有记录

  SELECT * FROM teacher

  注:

 - 实际开发中，java代码例若执行DQL语句，不要用"*"

 -因为当使用SELECT * 时数据库首先要查询数据字典了解待查询表的表结构，了解全字段后才能进行查询。因此会给数据库带来额外的开销，这不划算。因为java代码仅需要编写一次，因此我们应当在SELECT后将所有字段列出。

- 查看表中指定的某些字段

  查看老师的名字，工资，性别，职位

  SELECT name,salary,gender,title

  FROM teacher

三.WHERE子句

1)在DQL语句中，WHERE用于指定过滤条件，仅查询满足该过滤条件的记录

#### 例

- 查看"一级讲师"的信息，列出:名字，职位，工资，年龄

  1:查看老师的相关信息     FROM子句，查看teacher表

  2:查看"一级讲师"信息      WHERE子句，添加对应的过滤条件

  3:查看名字，职位，工资，年龄  SELECT子句，查看对应的字段

  SELECT name,title,salary,age

  FROM teacher

  WHERE title='一级讲师'

2)连接多个条件

- AND:"与"，都为真时才为真

- OR:"或"，都为假时才为假

##### 例

- 查看7岁的"大队长"都有谁?列出这些学生的名字,年龄,性别和职位

  SELECT name,age,gender,job

  FROM student

  WHERE age=7 AND job='大队长';

3)AND的优先级高于OR

为了提高OR的优先级，可以使用"()"。

##### 例

- 查看班级编号在6(含)以下的所有大队长和中队长的名字，年龄，性别，班级编号和职位       

  SELECT name,age,gender,class_id,job

  FROM student

  WHERE class_id<=6 AND (job='大队长' OR job='中队长')      

  使用()来提高OR的优先级达到查询需求

  

四. IN(列表):等于列表其中之一

##### 例

- 查看所有大队长，中队长和小队长的名字，性别，年龄和职位?

  SELECT name,gender,age,job

  FROM student

  WHERE job IN('大队长','中队长','小队长')

五. NOT IN(列表):不在列表中

##### 例

- 查看除一级讲师和二级讲师之外的所有老师的名字，职称，工资

  SELECT name,title,salary

  FROM teacher

  WHERE title NOT IN('一级讲师','二级讲师')

六. BETWEEN... AND ...:在一个范围内

##### 例

- 查看工资在2000到5000之间的老师的名字,性别,年龄,工资

  SELECT name,gender,age,salary

  FROM teacher

  WHERE salary BETWEEN 2000 AND 5000

                          下限               上限

七. DISTINCT:可以将结果集按照指定的字段去除重复行

DISTINCT必须紧跟在SELECT关键字之后

##### 例

- 查看老师的职称都有哪些?

  SELECT title       查看teacher表中每个老师的职称

  FROM teacher

  SELECT DISTINCT title(去重)

  FROM teacher

 - 查看各年龄段的学生都有哪些职位?

  SELECT DISTINCT age,job 当age与job值相同的记录会被去除。例8岁\的大队长可能查出4个，但仅保留 一行

  FROM student       

八.LIKE 模糊查询

LIKE有两个可用的通配符:

- _:表示任意一个字符。确切的数量表示1个字符

- %:表示任意个字符。不确切的数量表示0-任意个字符

##### 格式示例

LIKE '%X%' 表示字符串中包含字符X

LIKE '_X%' 表示字符串中第二个字符是X

LIKE 'X%'   表示字符串以X开始

LIKE '%X'   表示字符串以X结束

LIKE '%X_Y' 表示字符串倒数第三个字符数X并且最后一个字符是Y    

九. NULL值判断:判断一个字段值是否为NULL

- IS NULL:判断字段的值是否为NULL

- IS NOT NULL:判断字段的值是否不是NULL

- 不可以用=和<>判断NULL值

##### 例

- 查看哪些老师的奖金为空?

  SELECT name,salary,comm

  FROM teacher

  WHERE comm IS NULL

- 查看有奖金的老师?

  SELECT name,salary,comm

  FROM teacher

  WHERE comm IS NOT NULL

十.ORDER BY子句

查询结果集进行排序，按照ORDER BY后指定的字段值进行升序或降序排序

- 升序:ORDER BY 字段 [ASC]。将当前结果集按照指定的字段值从小到大排序。

- 降序:ORDER BY 字段 DESC。将当前结果集按照指定的字段值从大到小排序。

- ORDER BY可以按照多个字段排序，此时排序存在优先级。

    - 首先按照ORDER BY后指定的第一个字段的值将结果集按照该字段排序方式(升序或降序)排序

    - 排序后再按照第二个字段指定的值排序，仅会对第一个字段值相同的记录按照第二个字段排序

    - 若有第三个字段以此类推

- 不指定排序方式时，默认为升序

- ORDER BY子句是DQL中最后一个子句(像MySQL和MariaDB还会在ORDER BY后使用LIMIT子句，但这属于方言)

##### 例

- 查看老师的工资和奖金，首先按照奖金的升序，再按照工资的降序

  SELECT name,comm,salary

  FROM teacher

  ORDER BY comm ASC,salary DESC  每个字段都要单独指定排序方式

  等同于

  SELECT name,comm,salary

  FROM teacher

  ORDER BY comm ,salary DESC    升序ASC可以忽略

  -多字段排序时，首先将结果集按照comm的排序方式排序，之后将comm字段值相同的记录再按照salary的方式排序

  -当comm字段值不同的记录，则不会再按照salary字段排序

十一. 分页查询

将一个DQL语句的查询结果分段查询出来

1.使用场景

当一条DQL语句可以查询出非常大量的数据时，我们可以分批将数据查询，例如淘宝检索一件商品时，分出现第一页，第二页等信息，每页展示30条记录。一次仅查询30条记录，避免因为数据庞大导致系统过度的资源开销，已经更慢的传输速度。

2.方言

-分页查询是方言，并没有在SQL92标准中有语法定义。意味着不同的数据库分页方式的SQL可能完全不同。

-在ORACLE中使用伪列ROWNUM完成，而MySQL和MariaDB则使用LIMIT子句完成。

3.语法

SELECT...

FROM ...

...

ORDER BY ...

LIMIT M,N

- M:表示跳过结果集M条记录

- N:表示从跳过的M条记录后连续检索N条记录

- 在分页查询中有两个常见的参数

  - 页数:当前显示第几页内容

  - 条目数:每页显示的条目数

4.分页公式

  - M:(页数-1)*条目数

  - N:条目数

#### 例

-查看老师奖金信息，按照降序排序后，每页显示3条，显示第5页?

  页数:5   条目数:3

  - M:(页数-1)*条目数   (5-1)*3->M:12

  - N:条目数

  SELECT name,comm

  FROM teacher

  ORDER BY comm DESC

  LIMIT 12,3

-查看全校年龄最小学生的第6-10名

  SELECT name,birth,age

  FROM student

  ORDER BY birth DESC

  LIMIT 5,5

注:不添加LIMIT看到结果集中6-10和添加了LIMIT获取6-10记录不完全一样

原因:  MySQL官方文档给出了解释

如果多行在 ORDER BY 列中具有相同的值，则服务器可以自由地以任意顺序返回这些行，并且可能会根据整体执行计划以不同的方式返回这些行。换句话说，这些行的排序顺序对于无序列是不确定的。

影响执行计划的一个因素是 LIMIT，因此带有和不带 LIMIT 的 ORDER BY 查询可能会返回不同顺序的行。

解决方式:

排序时指定字段值相同的记录无论是否添加LIMIT子句,要保证这些记录的顺序始终一致即可.我们要额外的追加一个辅助字段进行排序,确保顺序一定一致,通常辅助字段值不要用重复的,赋值字段首选ID

  SELECT name,birth,age

  FROM student

  ORDER BY birth DESC,id

  LIMIT 5,5

十二.在DQL中使用函数或表达式

1) 在SELECT子句中使用表达式

##### 例

- 查看每个老师的工资和年薪

  SELECT name,salary,salary*12

  FROM teacher

2)在SELECT子句中使用函数

2.IFNULL函数

1)定义:   IFNULL(arg1,arg2)

2)作用

- 当arg1的值不为NULL时，函数返回arg1的值

- 当arg1的值为NULL时，函数返回arg2的值

- IFNULL函数的作用就是将一个NULL值替换为非NULL值

###### 例

- 查看每个老师的工资，奖金，工资+奖金分别是多少?

  在数据库中任何数字与NULL运算结果都是NULL

  SELECT name,salary,comm,salary+IFNULL(comm,0) 使用IFNULL函数将NULL替换为非NULL值

  FROM teacher

3. 1)在WHERE中使用表达式

##### 例

- 查看年薪高于60000的老师都有谁?

  SELECT name,salary

  FROM teacher

  WHERE salary*12>60000

2)在WHERE中使用函数

##### 例

- 查看哪些老师的奖金少于3000?

  SELECT name,comm

  FROM teacher

  WHERE comm<3000

  查询不出comm为NULL的记录。因为NUL是状态，不具可比性。因此:=,<>,>,>=,<,<=都不可以

  SELECT name,comm

  FROM teacher

  WHERE IFNULL(comm,0)<3000

十三.别名:通常使用在SELECT子句和FROM子句中

- 在SELECT子句中我们可以为字段取别名。

  - 当字段为函数或表达式时，我们通常给字段添加别名，为了增加可读性

  - 为SELECT中的子查询取别名

- 在FROM 子句中可以为表添加别名

语法

- 字段名<空格>别名

  SELECT name,salary,salary*12

  FROM teacher

  当SELECT中含有表达式时，结果集该字段使用这个表达式作为字段名

  SELECT name,salary,salary*12 annusal

  FROM teacher

- 字段名 AS 别名

  SELECT name,salary,salary*12 AS annusal

  FROM teacher

- 字段名 [AS] '别名'

  SELECT name,salary,salary*12 AS 'annusal'

  FROM teacher

  SELECT name,salary,salary*12 'annusal'

  FROM teacher

- 字段名 [AS] "别名"

  SELECT name,salary,salary*12 AS "annusal"

  FROM teacher

  SELECT name,salary,salary*12 "annusal"

  FROM teacher

- 如果别名希望使用关键字或者别名含有空格时，要使用引号将别名括起来

  SELECT name,salary,salary*12 FROM 别名FROM,但是FROM是关键字

  FROM teacher                                              会引发SQL语义错误

  SELECT name,salary,salary*12 'FROM'     

  FROM teacher

 SELECT name,salary,salary*12 annu sal       当含有空格时，数据库认为annu是salary*12的别名，后面的sal会语法错误。

 FROM teacher

十四.聚合函数

1.概念:聚合函数又称为多行函数，分组函数。对记录按照指定字段统计的，可以将多行记录统计出一行记录

2.聚合函数分类

- MIN:统计指定字段的最小值

- MAX:统计指定字段的最大值

- SUM:对指定字段求和

- AVG:对指定字段求平均值

- COUNT:统计记录数的

#### 注意事项

- MIN,MAX,SUM,AVG是对值的统计。而COUNT是对记录数的统计

- 聚合函数忽略NULL值。这个在AVG，COUNT上尤为明显

#### 例

- 查看老师的平均工资是多少?

  SELECT AVG(salary)

  FROM teacher

  - 查看老师的最高工资,最低工资,平均工资和工资总和都是多少?

  SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)

  FROM teacher

  3:添加聚合函数

  SELECT COUNT(name) FROM teacher    

统计name字段值不为NULL的记录共多少条

  SELECT COUNT(comm) FROM teacher  

  因为comm字段有NULL值，聚合函数忽略对NULL的统计

  

  SELECT COUNT(1) FROM teacher       可以，不推荐

  SELECT COUNT(*) FROM teacher       DBMS基本都对COUNT(*)采取了优化

十五. GROUP BY 分组

GROUP BY子句可以将结果集按照指定字段值相同的记录进行分组，配合聚合函数可以实现组内统计。

- 在SELECT子句中出现聚合函数时，那么不在聚合函数中的字段都要出现在GROUP BY子句中。

- GROUP BY子句是配合聚合函数使用的，如果SELECT子句中没有聚合函数，通常不写GROUP BY。

1.按单字段分组

##### 例

- 查看每种职位的老师平均工资是多少?

  SELECT AVG(salary),title

  FROM teacher

  GROUP BY title

2.按多字段分组

GROUP BY后指定多个字段时，分组方式为:这些字段值都一样的记录看作一组

##### 例

- 查看同班级同性别的学生分别多少人?

  SELECT COUNT(*),class_id,gender

  FROM student

  GROUP BY class_id,gender   班级号相同且性别相同的记录分为一组

3.按照聚合函数的结果排序

##### 例

- 查看每个科目老师的平均工资排名?

  SELECT AVG(salary) avg_sal,subject_id

  FROM teacher

  GROUP BY subject_id

  ORDER BY avg_sal DESC

-好的书写习惯:当SELECT子句中出现了函数或表达式时，通常应当为该字段添加别名。ORDER BY子句也可以根据别名对该结果集按字段排序

十六.HAVING子句

HAVING子句用于分组中的过滤条件

#### 问题

- 查看每个科目老师的平均工资?但是仅查看平局工资高于6000的那些.

  SELECT AVG(salary),subject_id

  FROM teacher

  WHERE AVG(salary)>6000

  GROUP BY subject_id

#### 错误

聚合函数不能在WHERE子句中使用

#### 原因

WHERE子句中使用聚合函数错误的原因是:过滤时机不对。

- WHERE的过滤时机是在第一次检索表中每一条记录时进行过滤的，用于确定结果集的记录

- 聚合函数的统计结果进行过滤，前提:

  - 先将表中数据检索出结果集

  - 才能将结果集分组

  - 分组后才能根据聚合函数统计结果

  - 最后再根据统计结果过滤

- 聚合函数的过滤是在WHERE之后进行的

1.HAVING子句的应用

HAVING子句是紧跟在GOURP BY子句之后，用于对分组进行过滤的子句。

2.HAVING和WHERE的区别

- WHERE是在第一次检索表数据时用于添加过滤条件。确定结果集

- HAVING是在GROUP BY之后(将结果集分组之后)添加过滤条件的，用于确定分组。

- 过滤时机不同，作用不同

#### 例

- 查看每个科目老师的平均工资?但是仅查看平局工资高于6000的那些.

   子句执行顺序

  SELECT AVG(salary),subject_id 4 符合要求的分组统计对应信息

  FROM teacher 1 数据来源，数据从teacher表查询

  GROUP BY subject_id 2    确定分组，按科目分组(比如6组)

  HAVING AVG(salary)>6000 3    过滤分组，比如只有3组符合要求

十七. 子查询 (SubQuery)

1. 概念: 嵌套在一个SQL语句中的DQL语句，该DQL被称为子查询

2.应用场景

- DQL中使用子查询

  - 在SELECT子句中，将当前子查询结果作为一个字段展示

  - 在WHERE子句中，将当前子查询结果作为过滤条件使用(最常用的场景)

- DML中使用:将一个查询结果集用于增删改操作

- DDL中使用

  - 在创建表时可以将一个子查询结果集当作表创建

  - 在创建视图时使用一个子查询结果集

3.子查询分类

- 单行单列子查询，该子查询的结果集只有一个值

- 多行单列子查询，该子查询结果集是多个值

- 多列子查询(多行多列子查询|多行多列子查询),将该子查询当作一张表使用

4. 在DQL中使用子查询

1)单行单列子查询

###### 例

- 查看比范传奇工资高的老师都有谁?

  SELECT name,salary

  FROM teacher

  WHERE salary>(SELECT salary FROM teacher WHERE name='范传奇')

  

2) 多行单列子查询

多行单列子查询是可以检索出若干个值。因此作为过滤条件使用时

- 判断等值，要配合:IN,NOT IN使用。等于是不能同时等于好几个值的，只能同于其中之一

- 判断>,>=,<,<=时，要搭配ANY和ALL使用

  - \>ANY(列表)：大于列表其中之一，判断标准:大于列表最小值即可

  - \>ALL(列表)：大于列表所有，判断标准:大于列表最大值

  - <ANY(列表)：小于最大的即可

  - <ALL(列表)：小于最小的即可

###### 例

- 查看与"祝雷"和"李费水"在同一个班的学生都有谁?

  SELECT name,class_id

  FROM student

  WHERE class_id=(SELECT class_id FROM student WHERE name IN('祝雷','李费水'))

  错误原因，两个学生的班级号不同，而没有任何一个学生的班级号可以同时等于两个不同的值

SELECT name,class_id

  FROM student

  WHERE class_id IN (SELECT class_id

                     FROM student

                     WHERE name IN('祝雷','李费水'))

5. 在DML语句中使用子查询

##### 例

- 给与'范传奇'负责同一科目的所有老师工资涨500

  UPDATE teacher

  SET salary=salary+500

  WHERE subject_id=(SELECT subject_id

                    FROM teacher

                    WHERE name='范传奇')

6. 在DDL语句中使用子查询

可以将一个查询结果集当作一张表创建出来

##### 例

- 创建一张表,该表中记录了每个科目老师的工资情况,要求展示:最高,最低,总和和平均工资以及该科目id

  

CREATE TABLE teacher_salary_info

  AS

  SELECT MAX(salary) max_sal,MIN(salary) min_sal,

         SUM(salary) sum_sal,AVG(salary) avg_sal,subject_id

  FROM teacher

  GROUP BY subject_id

  

  当子查询的SELECT子句包含函数或表达式时，应当为其取别名，此时创建的表中该字段名会使用指定的别名

十八. 关联查询(重点)

1. 定义: 联合多张表查询数据，查询结果集中的字段来自与多张表。

2. 关联关系: 表与表中的记录会产生对应关系，用于我们联合查询。

3.关联关系分类

- 一对一:A表的一条记录仅唯一对应B表中的一条记录

- 一对多:A表中的一条记录对应B表中的多条记录

- 多对多:当A与B表双向看到都是一对多时就是多对多关系

4. 连接条件

1)在DQL中我们会指定连接条件，用来让数据库在查询中知道两张表中记录与记录的对应关系，从而查询出对应的记录。

2)关联查询中连接条件通常不可以忽略或缺失，否则会产生笛卡尔积

3)连接条件的个数: N张表联合查询就要有至少N-1个连接条件

5.语法

SELECT 字段

FROM 表A,表B[,表C,...]

WHERE A表与B表的连接条件

AND   [其他表的连接条件]

AND   过滤条件

注意:连接条件要同时满足，且如果还有过滤条件时，也要与过滤条件同时满足

### 例

- 查看每个老师以及其负责课程科目名?

  SELECT teacher.name,teacher.age,subject.name

  FROM teacher,subject

  WHERE teacher.subject_id=subject.id

  上述代码缺点:为了区分表中的字段，写法:表名.字段名。

             如果表名较长，写起来十分累赘

  解决办法:在FROM子句中指定表名时为表取别名。此时区分表字段时可写作:表别名.字段名

  SELECT t.name,t.age,s.name

  FROM teacher t,subject s

  WHERE t.subject_id=s.id

- 不指定连接条件，会产生笛卡尔积

   当不指定连接条件时,数据库在进行关联查询时,仍然会用A表一条记录与B表每条记录连接一次,并产生结果集中的一条记录.此时的数据量为A表记录数与B表记录数的乘积.

当表中数据量大时,这样的结果集开销巨大,甚至可能导致服务器宕机.因此要尽量避免.

6.连接条件与过滤条件同时满足

#### 例

- 王克晶是哪个班的班主任?列出:班级名称，楼层，老师名称，工资

    SELECT c.name,c.floor,t.name,t.salary

    FROM class c,teacher t

    WHERE c.teacher_id=t.id 连接条件

    AND t.name='王克晶'   过滤条件

7. N张表关联

N张表关联查询，至少要有N-1个连接条件

#### 例

- 查看"范传奇"所带班级的学生都有谁?要列出:学生名字，年龄，班级名称，老师名字

    SELECT s.name,s.age,c.name,t.name

    FROM student s,class c,teacher t

    WHERE s.class_id=c.id

    AND c.teacher_id=t.id

AND t.name='范传奇';

8.关联查询中使用聚合函数

将关联查询的结果集进行统计

#### 例

- 查看范传奇所带班级的学生共多少人?

    SELECT COUNT(*)

    FROM teacher t,class c,student s

    WHERE t.id=c.teacher_id

    AND s.class_id=c.id

    AND t.name='范传奇';

十八.主键与外键

1.定义:

1)主键:一张表中通常第一个字段为主键字段,用来唯一标识表中的一条记录.主键要求的条件是非空且唯一.

2)外键:一张表中一个字段保存了另一张表中主键字段的值,那么这个字段就是外键字段.

3)在关联关系中,两张表通常就是使用主外键进行关联的.并且在关联查询中总是用等值连接主键与外键来建立两张表中记录的对应关系.

-通常:定义外键字段的表在关联关系中处于"多"的一方.

例如:学生与班级表,学生表中有一个字段class_id保存着class表主键id字段的值.此时class_id字段就是外键字段.因此班级表与学生表存在一对多的关系,即:班级表的一条记录可以对应学生表的多条记录.

2.外键约束

1)如果给某张表的某个字段施加外键约束，此时外键约束要求必须指明该外键对应的是哪张表的主键字段。

2)外键约束要求:

- 外键字段保存的值必须是主键字段存在的值或NULL

3)外键约束引发的问题:

- 外键字段不能保存主键字段没有的值

- 不能轻易删除主键字段表中记录，因为如果外键中存在着要删除的主键记录，此时相当于删除掉主键自己则外键字段保存了主键字段没有的值。

  - 如果想删除主键字段记录，则需要现将该主键的值对应的所有表中外键是该值的记录更新为NULL，此时才可以将其删除，因此会带来大量的DML操作。

-结论:关联关系中通常不适用外键约束。

3.多对多关系

1)多对多关系:A表与B表双向看待都是一对多，就是多对多关系

2)多对多关系需要提供一张关联关系表来维系多对多关系.该表保存着两张产生关系表的外键字段.

3)多对多关联查询

##### 例

- 查看学习语文的学生都有谁?

  SELECT s.name,su.name,tss.score

  FROM subject su,student s,t_stu_subject_score tss

  WHERE su.id=tss.subject_id

  AND s.id=tss.stu_id

  AND su.name='语文';

 十九. 内连接

内连接是关联查询的另一种写法

#### 语法

SELECT 子句

FROM 表A

JOIN 表B ON A与B的连接条件

[JOIN 表C ON A与C或B与C的连接条件…]

WHERE 过滤条件

#### 例

- 查看1年级1班的学生信息?列出学生名字,年龄,所在班级

  SELECT s.name,s.age,c.name

  FROM class c

  JOIN student s ON c.id=s.class_id ON子句中书写连接条件

  WHERE c.name='1年级1班'   WHERE子句中写过滤条件

二十.外连接

外连接也用于关联查询，特点:可以将不满足连接条件的记录也查询出来

- 左外连接:以JOIN左侧表为驱动表，该表中所有记录都要体现在结果集中，右侧表不满足连接条件的记录对应的字段全部为NULL。前提是如果有过滤条件则仅列出符合过滤条件的记录

- 右外连接:以JOIN右侧表作为驱动表，该表中的记录都要体现在结果集中，左侧表不满足连接条件的字段都补NULL

#### 例

- 查看所有班级信息和对应的班主任信息,如果该班没有班主任也要将班级信息列出来.

  SELECT c.name,t.name

  FROM class c

  JOIN teacher t ON c.teacher_id=t.id

因为class表中有三个班级的teacher_id为99,而teacher表中没有id为99的记录.因此这三条班级记录由于不满足连接条件,所以在结果集中没有被查询出来.

  

  需求是要将class表中记录完整展现(不满足连接条件的记录也要展现)

  SELECT c.name,c.floor,t.name,t.salary

  FROM class c LEFT JOIN teacher t ON c.teacher_id=t.id

  

-查看所有班级信息和对应的班主任信息,无论是班级还是老师不满足连接条件也要展示

  SELECT c.name,c.floor,t.name,t.salary

  FROM class c LEFT JOIN teacher t ON c.teacher_id=t.id

  UNION

  SELECT c.name,c.floor,t.name,t.salary

  FROM class c RIGHT JOIN teacher t ON c.teacher_id=t.id

  

  -UNION可以将多个SQL语句的结果集取并集。重复的记录仅保留一次。

  -需要注意，这些SQL查询时SELECT子句应当是一致的。字段的个数，顺序，类型。

  注:ORACLE中有全外连接的语法:FULL OUTER JOIN

二十一.自连接

1. 定义:自连接是指当前表中的一条记录可以对应自己的多条记录

2. 场景:具有相同属性的一组数据之间又存在上下级的树状结构数据。

例如:

- 一个公司的人员组织结构。大家都是员工，员工又存在上下级。

- 电商中常见的分类树

3. 形式:当前表中有一个字段为外键而其保存的是本表主键字段的值

#### 例

- 查看'刘苍松'的下属都有谁?

  teacher表中记录了所有老师的信息,而manager字记录了该老师的上级的id

  manager字段记录了teacher表主键字段id的值.

  

  SELECT t.name,m.name

  FROM teacher t,teacher m     将teacher当做两张表看待,t表示保存老师  m表示保存领导

  WHERE t.manager=m.id

  AND m.name='刘苍松'

  内连接写法

  SELECT t.name,m.name

  FROM teacher t

  JOIN teacher m ON t.manager=m.id

  WHERE m.name='刘苍松'

十八.主键与外键

1.定义:

1)主键:一张表中通常第一个字段为主键字段,用来唯一标识表中的一条记录.主键要求的条件是非空且唯一.

2)外键:一张表中一个字段保存了另一张表中主键字段的值,那么这个字段就是外键字段.

3)在关联关系中,两张表通常就是使用主外键进行关联的.并且在关联查询中总是用等值连接主键与外键来建立两张表中记录的对应关系.

-通常:定义外键字段的表在关联关系中处于"多"的一方.

例如:学生与班级表,学生表中有一个字段class_id保存着class表主键id字段的值.此时class_id字段就是外键字段.因此班级表与学生表存在一对多的关系,即:班级表的一条记录可以对应学生表的多条记录.

2.外键约束

1)如果给某张表的某个字段施加外键约束，此时外键约束要求必须指明该外键对应的是哪张表的主键字段。

2)外键约束要求:

- 外键字段保存的值必须是主键字段存在的值或NULL

3)外键约束引发的问题:

- 外键字段不能保存主键字段没有的值

- 不能轻易删除主键字段表中记录，因为如果外键中存在着要删除的主键记录，此时相当于删除掉主键自己则外键字段保存了主键字段没有的值。

  - 如果想删除主键字段记录，则需要现将该主键的值对应的所有表中外键是该值的记录更新为NULL，此时才可以将其删除，因此会带来大量的DML操作。

-结论:关联关系中通常不适用外键约束。

3.多对多关系

1)多对多关系:A表与B表双向看待都是一对多，就是多对多关系

2)多对多关系需要提供一张关联关系表来维系多对多关系.该表保存着两张产生关系表的外键字段.

3)多对多关联查询

##### 例

- 查看学习语文的学生都有谁?

  SELECT s.name,su.name,tss.score

  FROM subject su,student s,t_stu_subject_score tss

  WHERE su.id=tss.subject_id

  AND s.id=tss.stu_id

  AND su.name='语文';

 十九. 内连接

内连接是关联查询的另一种写法

#### 语法

SELECT 子句

FROM 表A

JOIN 表B ON A与B的连接条件

[JOIN 表C ON A与C或B与C的连接条件

 ...

]

WHERE 过滤条件

####

- 查看1年级1班的学生信息?列出学生名字,年龄,所在班级

  SELECT s.name,s.age,c.name

  FROM class c

  JOIN student s ON c.id=s.class_id ON子句中书写连接条件

  WHERE c.name='1年级1班'   WHERE子句中写过滤条件

二十.外连接

外连接也用于关联查询，特点:可以将不满足连接条件的记录也查询出来

- 左外连接:以JOIN左侧表为驱动表，该表中所有记录都要体现在结果集中，右侧表不满足连接条件的记录对应的字段全部为NULL。前提是如果有过滤条件则仅列出符合过滤条件的记录

- 右外连接:以JOIN右侧表作为驱动表，该表中的记录都要体现在结果集中，左侧表不满足连接条件的字段都补NULL

#### 例

- 查看所有班级信息和对应的班主任信息,如果该班没有班主任也要将班级信息列出来.

  SELECT c.name,t.name

  FROM class c

  JOIN teacher t ON c.teacher_id=t.id

  因为class表中有三个班级的teacher_id为99,而teacher表中没有id为99的记录.因此这三条班级记录由于不满足连接条件,所以在结果集中没有被查询出来.

  

  需求是要将class表中记录完整展现(不满足连接条件的记录也要展现)

  SELECT c.name,c.floor,t.name,t.salary

  FROM class c LEFT JOIN teacher t ON c.teacher_id=t.id

  

-查看所有班级信息和对应的班主任信息,无论是班级还是老师不满足连接条件也要展示

  SELECT c.name,c.floor,t.name,t.salary

  FROM class c LEFT JOIN teacher t ON c.teacher_id=t.id

  UNION

  SELECT c.name,c.floor,t.name,t.salary

  FROM class c RIGHT JOIN teacher t ON c.teacher_id=t.id

  

  -UNION可以将多个SQL语句的结果集取并集。重复的记录仅保留一次。

  -需要注意，这些SQL查询时SELECT子句应当是一致的。字段的个数，顺序，类型。

  注:ORACLE中有全外连接的语法:FULL OUTER JOIN

二十一.自连接

1. 定义:自连接是指当前表中的一条记录可以对应自己的多条记录

2. 场景:具有相同属性的一组数据之间又存在上下级的树状结构数据。

例如:

- 一个公司的人员组织结构。大家都是员工，员工又存在上下级。

- 电商中常见的分类树

3. 形式:当前表中有一个字段为外键而其保存的是本表主键字段的值

#### 例

- 查看'刘苍松'的下属都有谁?

  teacher表中记录了所有老师的信息,而manager字段记录了该老师的上级老师的id

  manager字段记录了teacher表主键字段id的值.

  

  SELECT t.name,m.name

  FROM teacher t,teacher m     将teacher当做两张表看待,t表示保存老师  m表示保存领导

  WHERE t.manager=m.id

  AND m.name='刘苍松'

  

  内连接写法

  SELECT t.name,m.name

  FROM teacher t

  JOIN teacher m ON t.manager=m.id

  WHERE m.name='刘苍松'