数据库笔记

## 数据库基本概念

### **数据库DataBase(简称DB)**

存储数据的仓库就称为数据库

在我们的项目中我们使用users目录保存若干的obj文件,每个文件保存一个用户信息,

这个users就可以称为是一个数据库.只是我们维护数据要么手动,要么写java代码读写操作

来进行维护.

低效,且不通用.

### **数据库管理系统Database management system(简称DBMS)**

它们都是独立可运行的软件,我们用java写的程序可以连接这些软件让其负责对数据进行维护

常见的DBMS:

- oracle

- mysql

- DB2

- SQLServer

### **如何操作DBMS?**

SQL语言:Structured Query Language

SQL有执行标准:SQL92

SQL语言是操作DBMS的语言,所有的DBMS都支持标准的SQL语言以及自身提供的可扩展SQL

### **连接数据库的方式:**

1:命令行(控制台)的客户端

show databases;

2:图形化界面的客户端

3:IDEA终极版提供了连接数据库的功能

4:在JAVA中要是用JDBC连接数据库

### SQL语句的分类:

- DDL:数据定义语言,用来操作数据库对象(数据库对象:表,索引,序列等都属于数据库对象)

包含的语法:CREATE,DROP,ALTER

- DML:数据操作语言,用来操作表中的数据(包含的操作:对数据的增删改)

包含的语法:INSERT,UPDATE,DELETE

- DQL:数据查询语言,用来查询表中的数据

包含的语法:SELECT

- TCL:事务控制语言

包含的语法:COMMIT,ROLLBACK

- DCL:数据控制语言,通常用来让DBA管理数据库使用

## DDL数据定义语言

### 查看DBMS里有多少个数据库?

`SHOW DATABASES;`

### 新建一个数据库:

##### 语法:

`CREATE DATABASE 数据库名`

例如:

`CREATE DATABASE mydb;`

##### 创建一个数据库时可以指定该数据库使用的字符集

`CREATE DATABASE 数据库名 CHARSET=UTF8/GBK`

例如:

`CREATE DATABASE mydb2 CHARSET=UTF8`

### 练习:

创建数据库:db1(字符集用gbk) db2(字符集用utf8)

`CREATE DATABASE db1 CHARSET=GBK`

`CREATE DATABASE db2 CHARSET=UTF8`

### 查看数据库信息

**SHOW CREATE DATABASE 数据库名**

例如:

`SHOW CREATE DATABASE mydb`

### 删除数据库

**DROP DATABASE 数据库名**

例如:

`DROP DATABASE db1`

`DROP DATABASE db2`

想要保存数据,那么数据是保存在表中的,而表是归属于某一个数据库的.因此我们在创建

表之前必须要先使用某一个已创建的数据库.

### 使用某个指定的数据库:

**USE 数据库名**

例如:

使用mydb这个数据库:

`USE mydb;`

```

练习:

1. 创建 mydb1和mydb2 数据库 字符集分别为utf8和gbk

CREATE DATABASE mydb1 CHARSET=utf8;

CREATE DATABASE mydb2 CHARSET=gbk;

2. 查询所有数据库检查是否创建成功

SHOW DATABASES;

3. 检查两个数据库的字符集是否正确

SHOW CREATE DATABASE mydb1;

SHOW CREATE DATABASE mydb2;

4. 先使用mydb2 再使用 mydb1

USE mydb2;

USE mydb1;

5. 删除两个数据库

DROP DATABASE mydb1;

DROP DATABASE mydb2;

```

### 创建表

**CREATE TABLE 表名(**

**字段名1 数据类型,**

**字段名2 数据类型,**

**...**

**)**

例如:创建一张表userinfo用来保存用户信息.

每个用户保存内容有:用户名,密码,昵称,年龄.其中年龄为整数,其余为字符串

`CREATE TABLE userinfo(`

`id INT,`

`username VARCHAR(30),`

`password VARCHAR(30),`

`nickname VARCHAR(30),`

`age INT`

`)`

查看当前数据库中已创建的表

**SHOW TABLES**

### 查看某一张表的详细信息

**SHOW CREATE TABLE 表名**

`SHOW CREATE TABLE userinfo`

### 查看表结构

**DESC 表名**

例如:查看userinfo表的结构

`DESC userinfo`

### 删除表

DROP TABLE 表名

例如:删除user表

DROP TABLE user

### 修改表

- 修改表名

- 修改表结构

### 修改表名:

**RENAME TABLE 原表名 TO 新表名**

例如:

将userinfo表改名为user

`RENAME TABLE userinfo TO user`

```

练习:

1. 创建数据库mydb3 字符集gbk 并使用

CREATE DATABASE mydb3 CHARSET=GBK

USE mydb3

2. 创建t_hero英雄表, 有名字和年龄字段

CREATE TABLE t_hero(

id INT,

name VARCHAR(30),

age INT

)

3. 修改表名为hero

RENAME TABLE t_hero TO hero

4. 查看表hero的信息

SHOW CREATE TABLE hero

5. 查询表hero结构

DESC hero

6. 删除表hero

DROP TABLE hero

7. 删除数据库mydb3

DROP DATABASE mydb3

```

### 修改表结构:

**ALTER TABLE**

#### 添加列:

**ALTER TABLE 表名 ADD 列名 类型[长度]**

例如:向user表中追加性别

`ALTER TABLE user ADD gender VARCHAR(10)`

`CREATE TABLE hero(`

`username VARCHAR(30),`

`age INT(3)`

`)`

##### 在hero第一列上追加新列

**ALTER TABLE 表名 ADD 字段名(列名) 类型 FIRST**

`ALTER TABLE hero ADD id INT FIRST`

##### 在表中插入一个新字段

**ALTER TABLE 表名 ADD 字段名 类型 AFTER 字段**

例如:

在hero表的姓名和年龄之间加入gender字段

`ALTER TABLE hero ADD gender VARCHAR(10) AFTER username`

#### 删除字段

**ALTER TABLE 表名 DROP 字段名**

删除hero表中的字段gender

`ALTER TABLE hero DROP gender`

#### 修改表中的字段

*注意:可以修改表中字段的类型,长度信息.但是尽量不要在表中包含数据后再进行修改,*

*否则可能因为表中现有数据违背该字段修改后的要求导致修改失败.*

**ALTER TABLE 表名 CHANGE 原字段名 新字段名 新类型**

将hero表中age字段类型从INT更换为VARCHAR

`ALTER TABLE hero CHANGE age age VARCHAR(10)`

将字段age类型的长度改为100

`ALTER TABLE hero CHANGE age age VARCHAR(100)`

将字段age改为gender,长度改为10,类型不变还是VARCHAR

`ALTER TABLE hero CHANGE age gender VARCHAR(10)`

```

练习:

1. 创建数据库mydb4 字符集utf8并使用

CREATE DATABASE mydb4 CHARSET=UTF8

USE mydb4

2. 创建teacher表 有名字(name)字段

CREATE TABLE teacher(

name VARCHAR(30)

)

3. 添加表字段: 最后添加age 最前面添加id(int型) , age前面添加salary工资(int型)

ALTER TABLE teacher ADD age INT(3)

ALTER TABLE teacher ADD id INT FIRST

ALTER TABLE teacher ADD salary INT AFTER name

4. 删除age字段

ALTER TABLE teacher DROP age

5. 修改表名为t

RENAME TABLE teacher TO t

6. 删除表t

DROP TABLE t

7. 删除数据库mydb4

DROP DATABASE mydb4

```

#### 总结

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

- CREATE,ALTER,DROP

- 创建表:CREATE TABLE

- 修改表:ALTER TABLE

- 删除表:DROP TABLE

## DML语言

**DML:数据操作语言,它是对表中的数据进行操作的语言.它包含:增,删,改**

创建一张表用于测试DML:

```

CREATE TABLE person(

name VARCHAR(30),

age INT(3)

)

```

### 插入数据

#### 语法:

**INSERT INTO 表名 [(字段1,字段2,...)] VALUES (字段1的值,字段2的值,...)**

```

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

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

```

- 在数据库中,字符串是使用单引号的.

- 指定的值要与前面指定的类的顺序,类型,个数完全一致

- 查看person表中所有数据:

```

SELECT * FROM person

```

##### 在INSERT语句中未被指定的列在插入数据时，默认插入NULL值。如果未指定的列指定了默认值，则插入指定的默认值

```

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

```

- age字段会插入默认值NULL

- 在数据库中，无论表中某个字段的类型是什么，只要没有单独指定过默认值时，默认值都是NULL

##### 在INSERT语句中字段名可以忽略，若忽略则为全列插入，此时VALUES后指定的值的顺序，类型，个数必须与表中字段一致

```

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

INSERT INTO person VALUES('钱七',NULL)

```

- 所有字段都必须给值，如果想插入NULL值，则直接指定NULL即可

- 以下都是错误的操作

```

INSERT INTO person VALUES('赵六');列的值个数不匹配

INSERT INTO person VALUES(22,'赵六');列的值类型不匹配

```

### 修改表中数据

#### 语法:

**UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2,....] [WHERE 过滤条件]**

- **通常情况下修改表中记录时都要指定WHERE条件，如果不添加WHERE条件则是对表中每一条记录都进行修改!**

```

UPDATE person

SET age=15

执行后，person表中每条记录的age字段的值都会变为15!

```

#### 修改指定记录

```

将张三的年龄改为22岁:

UPDATE person

SET age=22

WHERE name='张三'; WHERE子句在这里的作用:仅选定表中name字段值为'张三'的记录

将年龄等于15岁的人改为年龄36岁

UPDATE person

SET age=36

WHERE age=15; 满足WHERE条件的记录都会进行修改

```

#### WHERE子句中常用的条件

**=，<，>，<=，>=，<>(不等于，!=不是所有数据库都支持)**

```

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

UPDATE person

SET age=29

WHERE age>30

```

#### 将一个计算表达式的结果作为修改的值使用

```

将person表中每个人的年龄涨一岁

UPDATE person

SET age=age+1

```

#### 同时修改多个字段

```

将"李四"的名字改为"李老四"并且年龄改为55

UPDATE person

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

WHERE name='李四'

```

- SQL语句中的每个子句之间可以有任意个空格或换行。且SQL语句关键字是不区分大小写的。

- 字符串的内容是严格区分空格，大小写的。

### 删除表中数据

#### 语法

**DELETE [FROM] 表名 [WHERE 过滤条件]**

- **WHERE子句不写，则是清空表操作！**

#### 案例

```

删除'李老四'这条记录

DELETE FROM person WHERE name='李老四'

删除表中年龄在30岁以上的(含30)

DELETE FROM person WHERE age>=30

```

#### 清空表操作

```

DELETE FROM person

```

### 练习

```

练习:

1. 创建数据库day1db 字符集utf8并使用

create database day1db charset=utf8;

use day1db;

2. 创建t_hero表, 有name字段 字符集utf8

create table t_hero(name varchar(20))charset=utf8;

3. 修改表名为hero

rename table t_hero to hero;

4. 最后面添加价格字段money, 最前面添加id字段, name后面添加age字段

alter table hero add money int;

alter table hero add id int first;

alter table hero add age int after name;

5. 表中添加以下数据: 1,李白,50,6888 2,赵云,30,13888 3,刘备,25,6888

insert into hero values(1,'李白',50,6888);

insert into hero values(2,'赵云',30,13888);

insert into hero values(3,'刘备',25,6888);

6. 查询价格为6888的英雄名

select name from hero where money=6888;

7. 修改刘备年龄为52岁

update hero set age=52 where name='刘备';

8. 修改年龄小于等于50岁的价格为5000

update hero set money=5000 where age<=50;

9. 删除价格为5000的信息

delete from hero where money=5000;

10. 删除表, 删除数据库

drop table hero;

drop database day1db;

```

### 总结

- DML：数据操作语言，对表中数据进行操作的语言。

- INSERT语句用于向表中插入数据

1. INSERT时指定的字段顺序可以不与表中一致，但是VALUES中的值必须与指定的字段一致

2. 不指定的字段插入默认值，字段没有指定默认值时，默认值为NULL

3. 当完全不指定字段时，则为全列插入，此时VALUES中指定的值顺序，类型，个数必须与表完全一致

- UPDATE语句用于修改表中数据

- DELETE语句用于删除表中数据

- UPDATE语句和DELETE语句实际应用中要添加WHERE子句，否则都是对表中全部记录进行操作

## 数据库中的数据类型

### 不同的数据库管理系统，数据类型不完全一致

### 数字类型

- 整数类型:INT(m)和BIGINT(m)

m表示数字的长度(位数)

INT(5):该字段保存一个5位整数。此时该字段存的为数字18，实际存储:00018

- 浮点类型:DOUBLE(m,n)

m表示整体数字的位数

n表示小数的位数

DOUBLE(5,3):该字段可以保存一个5位数，其中3位是小数。最大可以保存到:99.999

实际插入数据时如果精度超过了可保存的范围时，会自动进行四舍五入:

XXX表有一个DOUBLE(5,3)的字段:

INSERT INTO XXX VALUES(12.8576)此时该字段实际保存的值为:12.858

### 字符类型

- CHAR(n):定长字符串。

n:长度，单位是字符。CHAR(10),该字段可以存放最多10个字符

最大长度:255个字符

表中每条记录该字段在磁盘上占据的空间是固定的，如果实际保存的字符不足指定的长度时，会在后面补充若干个空格来达到长度。

name CHAR(10)

INSERT INTO person (name) values('张三'):实际该记录中name的值:'张三 '(后面有8个空格)

优点:由于长度固定，因此查询速度快

缺点:磁盘占用存在浪费的情况

- VARCHAR(n):变长字符串

n:长度，单位是字节。VARCHAR(10),该字段保存的字符转换的字节最多10个。

最大长度为:65535个字节

优点:实际字段值占用多少字节就在磁盘上开辟多少字节。

缺点:长度不固定，因此查询性能慢

- TEXT(n):可变长字符

n:长度，单位是字符。最大值为65535

### 日期类型

- DATE：可以保存年月日

- TIME：保存时分秒

- DATETIME：保存年月日时分秒

- TIMESTAMP: 时间戳，记录UTC时间。从1970-01-01 00:00:00到其表示的时间之间经过的毫秒

### 例

```

CREATE TABLE userinfo(

id INT,

name VARCHAR(30),

birth DATETIME,

salary DOUBLE(7,2)

)

```

#### 插入日期时，可以使用字符串格式，格式应当为:'yyyy-MM-dd hh:mm:ss'

- MM表示两个数字的"月"，mm表示两位数字的"分"

```

INSERT INTO userinfo VALUES(1,'张三','1992-08-02 11:23:56',5000.98)

```

#### 如果日期类型使用的是DATETIME，那么在不指定时分秒时，默认为00:00:00

```

INSERT INTO userinfo VALUES(2,'李四','1989-06-30',9000)

```

#### DATETIME不能忽略年月日

```

INSERT INTO userinfo VALUES(3,'王五','13:25:33',12000)

```

#### 插入DOUBLE类型数字时，超过精度部分则会四舍五入

```

INSERT INTO userinfo VALUES(3,'王五','1986-03-15',6000.857)

```

#### 整数部分超过时会报错

```

INSERT INTO userinfo VALUES(4,'赵六','1986-03-15',100000)

```

## 约束

约束就是为表中某个字段添加一些限制条件，只有符合条件时才可以对表中记录进行操作

主键约束，外键约束，唯一性约束，非空约束

### 主键约束

#### 什么是主键(PRIMARY KEY)

该字段的值仅表达该表中唯一的一条记录

一张表只能有一个字段添加主键约束。该约束要求该字段:**非空且唯一**

### 非空约束(NOT NULL)

添加了非空约束的字段:每条记录该字段的值都不能为NULL

### 例

```

CREATE TABLE student(

id INT PRIMARY KEY,

name VARCHAR(30) NOT NULL,

age INT(3),

gender CHAR(1)

)

```

#### 使用DESC查看student表结构时可以体现出主键以及非空约束

```

DESC student

```

#### 不能将NULL值插入到主键或者具有非空约束的字段上

```

当不指定id时，数据库会将NULL默认插入到id字段，这违背主键约束:

INSERT INTO student (name,age,gender) VALUES ('张三',22,'男');

不能将NULL值显示的插入到具有非空约束的name字段上:

INSERT INTO student VALUES(1,NULL,22,'男');

```

#### 不能将重复的值插入到主键字段中

```

INSERT INTO student VALUES(1,'张三',22,'男');

不能再将1作为主键的值插入另一条记录。主键字段的值不可重复。

INSERT INTO student VALUES(1,'李四',33,'男');

```

#### 主键的值可以由数据库自行维护

```

ALTER TABLE student CHANGE id id INT AUTO_INCREMENT;

```

#### 当主键字段具有自增效果时，我们可以忽略主键值的插入

```

INSERT INTO student (name,age,gender) VALUES('李四',22,'男')

```

#### 当主键字段具有自增效果时，我们可以显示的向ID字段插入NULL值.此时主键仍然自增(不推荐操作)

```

此时并不会将NULL值作为主键字段的值，而是使用自增的主键值。

INSERT INTO student VALUES(NULL,'王五',25,'女')

```

## DQL语言:数据查询语言

**DQL用于检索表中数据的语言。**

### 基本语法：

```

SELECT 子句

FROM 子句

JOIN... ON... 子句

WHERE 子句

GROUP BY 子句

HAVING 子句

ORDER BY 子句

```

### 一条DQL语句必须包含的两个子句分别为:SELECT子句和FROM子句

- SELECT 子句用于选定表中的字段，选定的字段会被包含在查询的结果集中

- FROM 子句用于指定查询的表

#### 查看student表中所有记录，每条记录所有字段都要展示在结果集中。

```

SELECT * FROM student

"*"在SELECT子句中表达的意思时查看表中所有字段。

```

#### SELECT子句中可以仅指定个别字段进行查询

```

查看每个学生的名字，年龄，性别

SELECT name,age,gender

FROM student

```

### WHERE子句

**WHERE子句可以添加过滤条件，那么此时仅查询出满足该子句过滤条件的记录**

#### 比较运算符:=,>,>=,<,<=,<>

##### 例

- 查看所有大队长

```

SELECT name,job

FROM student

WHERE job='大队长'

```

- 查看除了'刘苍松'以外的其他老师的名字和年龄

```

SELECT name,age

FROM teacher

WHERE name<>'刘苍松'

```

- 查看年龄在30岁以上的老师的名字和年龄(含30岁)

```

SELECT name,age

FROM teacher

WHERE age>=30

```

#### 在WHERE子句中可以使用OR,AND来连接多个过滤条件

- AND:与。都为真时才为真

- OR:或。都为假时才为假

##### 例如

- 查看学生中7岁的大队长都有谁?

```

SELECT name,age,job

FROM student

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

```

- 查看班级号小于6的所有中队长都有谁?(班级号:class_id)

```

SELECT name,job,class_id

FROM student

WHERE class_id<6 AND job='中队长'

```

- 查看7岁的大队长和中队长都有谁？

**AND的优先级高于OR**

```

SELECT name,age,job

FROM student

WHERE age=7 AND job='大队长' OR job='中队长'

上述SQL查询的语义是:查看7岁的大队长和所有部分年龄的中队长

通过添加"()"来提高OR的优先级

实际写法应当是:

SELECT name,age,job

FROM student

WHERE age=7 AND (job='大队长' OR job='中队长')

```

- IN(列表) 等于列表之一

查看职位是大队长,中队长或小队长的学生?

```

SELECT name,job

FROM student

WHERE job='大队长' OR job='中队长' OR job='小队长'

等价

SELECT name,job

FROM student

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

```

- NOT IN(列表) 不等于列表中的内容

查看职位除了大队长，中队长，小队长的其他学生?

```

SELECT name,job

FROM student

WHERE job<>'大队长' AND job<>'中队长' AND job<>'小队长'

等价

SELECT name,job

FROM student

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

```

- BETWEEN...AND... 在两者之间

查看年龄在9到11岁之间的学生都有谁?

```

SELECT name,age

FROM student

WHERE age>=9 AND age<=11

等价

SELECT name,age

FROM student

WHERE age BETWEEN 9 AND 11

```

- DISTINCT 去重操作。去除结果集中指定字段值重复的行。

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

查看学校的学生有多少种职位?

```

SELECT DISTINCT job

FROM student

```

可以多字段去重，去除指定的字段值的组合重复的行

查看学校同年龄同职位都有多少种?(去除年龄与职位组合相同重复的行)

```

SELECT DISTINCT age,job

FROM student

```

#### 练习:

1. 查看负责课程编号(subject_id)为1的男老师都有谁?

```

SELECT name,subject_id,gender

FROM teacher

WHERE subject_id=1 AND gender='男'

```

2. 查看工资高于5000的女老师都有谁?

```

SELECT name,gender,salary

FROM teacher

WHERE salary>5000 AND gender='女'

```

3. 查看工资高于5000的男老师或所有女老师的工资？

```

SELECT name,salary,gender

FROM teacher

WHERE salary>5000 AND gender='男' OR gender='女'

```

4. 查看所有9岁学生的学习委员和语文课代表都是谁?

```

SELECT name,age,job

FROM student

WHERE age=9 AND (job='学习委员' OR job='语文课代表')

```

5. 查看工资在6000到10000之间的老师以及具体工资?

```

SELECT name,salary

FROM teacher

WHERE salary BETWEEN 6000 AND 10000

```

6. 查看工资在4000到8000以外的老师及具体工资?

```

SELECT name,salary

FROM teacher

WHERE salary<4000 OR salary>8000

SELECT name,salary

FROM teacher

WHERE salary NOT BETWEEN 6000 AND 10000

```

7. 查看老师负责的课程编号都有什么?

```

SELECT DISTINCT subject_id

FROM teacher

```

- LIKE：模糊查询

LIKE中有两个通配符

- _下划线表示1个字符

- % 百分号表示任意个字符(0---多次)

组合示意

- %X% 字符中含有X的

- %X 字符以X结束

- X% 字符以X开始

- _X _ 在三个字符中第二个字符为X

- %X_ 字符中倒数第二个为X

- X_Y 三个字符中第一个为X，第三个为Y

- X%Y 字符中第一个是X，最后一个是Y

##### 例

- 查看所有姓张的同学都是谁? 理解为:名字是以"张"开头的。匹配字符应写为:'张%'

```

SELECT name

FROM student

WHERE name LIKE '张%'

```

- 查看名字中含有王的同学都是谁? 匹配字符:'%王%'

```

SELECT name

FROM student

WHERE name LIKE '%王%'

```

- 查看三个字名字且最有一个字是"平"的同学都有谁? 理解:前两个字是任意字符 匹配字符:'__平'

```

SELECT name

FROM student

WHERE name LIKE '__平'

```

- 查看"李"姓的三个字的名字的同学都有谁? 匹配字符:'李__'

```

SELECT name

FROM student

WHERE name LIKE '李__'

```

- 查看职位是"语文什么表"的同学都有谁? 匹配字符:'语文%表'

```

SELECT name,job

FROM student

WHERE job LIKE '语文%表'

```

- 练习:

1. 查询名字姓"李"的学生姓名

```

SELECT name

FROM student

WHERE name LIKE '李%'

```

2. 查询名字中包含"江"的学生姓名

```

SELECT name

FROM student

WHERE name LIKE '%江%'

```

3. 查询名字以"郭"结尾的学生姓名

```

SELECT name

FROM student

WHERE name LIKE '%郭'

```

4. 查询9-12岁里是"课代表"的学生信息

```

SELECT name,age,job

FROM student

WHERE age BETWEEN 9 AND 12

AND job LIKE '%课代表'

```

5. 查询名字第二个字是"苗"的学生信息

```

SELECT name

FROM student

WHERE name LIKE '_苗%'

```

6. 查询姓"邱"的课代表都是谁?

```

SELECT name,job

FROM student

WHERE name LIKE '邱%'

AND job LIKE '%课代表'

```

- 判断NULL值。

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

- IS NOT NULL 判断某个字段的是不为NULL

- **不能直接搭配"="或"<>"来判断NULL值！！！**

查看奖金(comm)为空的老师都有谁?

```

SELECT name,salary,comm

FROM teacher

WHERE comm=NULL

上述SQL是查询不到任何数据的!!

正确写法:

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 子句只能是DQL中的最后一个子句(原因是该子句是最后执行的子句)。

作用:对结果集按照指定字段值的升序或降序进行排序

#### 排序方式:

- 升序:ASC 默认不写就是升序

- 降序:DESC

- **多字段排序时:会首先按照第一个字段排序，然后当第一个字段值相同的记录中再按照第二个字段排序以此类推*

##### 例:

- 查看老师的工资排名? 按照工资进行降序(从大到小)

```

SELECT name,salary,comm

FROM teacher

ORDER BY salary DESC

```

​

- 查看老师的奖金?按照升序(从小到大)排序

```

SELECT name,salary,comm

FROM teacher

ORDER BY comm ASC

或者

SELECT name,salary,comm

FROM teacher

ORDER BY comm

```

- 按照日期字段排序时，升序为从早到晚

按照生日从远到近排序学生

```

SELECT name,birth

FROM student

ORDER BY birth

```

- 按照老师的工资降序后再按照奖金的升序排

```

SELECT name,salary,comm

FROM teacher

ORDER BY salary DESC,comm

如果第一个字段的值没有重复值，则第二个字段排序无效

```

- 按照学生的年龄的升序再按照生日的降序排序

```

SELECT name,age,birth

FROM student

ORDER BY age,birth DESC

```

### 分页查询

将一个DQL语句执行后的查询结果集分段查询出来。

当一个查询结果记录数非常多时，通常都采取分页查询的方式来分段分批的查询减少不必要的系统开销。

分页查询在SQL92标准中没有定义。意味着不同的数据库有完全不同的分页写法。

**在MySQL中是通过在ORDER BY子句后面追加LIMIT来完成分页的。**

#### 语法

​ORDER BY 字段 LIMIT 跳过的记录数,每页显示的条目数

​LIMIT中两个数字的换算公式:

- LIMIT (页数-1)*每页显示的条目数 , 每页显示的条目数

例如:每页显示5条，显示第三页?

LIMIT (3-1)*5,5 ==> LIMIT 10,5

每页显示8条，显示第9页?

LIMIT (9-1)*8,8 ==> LIMIT 64,8

#### 例

- 查看老师工资排名的前5名? 分析:按工资降序，分页查询，每页5条，显示第一页

```

SELECT name,salary

FROM teacher

ORDER BY salary DESC

LIMIT 0,5

```

- 按照老师奖金的降序排序后，每页显示3条，显示第5页

```

SELECT name,salary,comm

FROM teacher

ORDER BY comm DESC

LIMIT 12,3

```

### 在DQL中可以使用表达式或者函数的结果进行查询

#### 在SELECT子句中使用函数或表达式

- 查看每个老师的年薪是多少?月薪*12

```

SELECT name,salary,salary*12

FROM teacher

```

- 查看每个老师的工资+奖金的总和是多少? salary+comm

**任何数字与NULL进行运算结果都是NULL**

```

SELECT name,salary,comm,salary+comm

FROM teacher

```

IFNULL(arg1,arg2):如果arg1不为NULL函数直接返回arg1,若为NULL函数返回arg2

```

IFNULL的逻辑示意:

IFNULL(arg1,arg2){

if(arg1==null){

return arg2;

}else{

return arg1;

}

}

```

```

SELECT name,comm,IFNULL(comm,0)

FROM teacher

查看老师的工资+奖金

SELECT name,salary,comm,salary+IFNULL(comm,0)

FROM teacher

```

#### 在WHERE子句中使用函数或表达式作为过滤条件

- 查看年薪小于5万的老师都有谁?

```

SELECT name,salary,salary*12

FROM teacher

WHERE salary*12<50000

```

- 查看奖金小于3000的老师都有谁?

**比较运算中也忽略NULL值**

```

SELECT name,salary,comm

FROM teacher

WHERE comm<3000

上述DQL查询的结果集中不含有奖金字段为NULL的记录

SELECT name,salary,comm

FROM teacher

WHERE IFNULL(comm,0)<3000

```

### 别名

别名在SELECT子句中可以被应用于字段上，函数上或表达式上

别名也可以被应用于其他子句中，比如在FROM子句中为表取别名(后面学习关联查询详细介绍)

- 查看老师的年薪是多少?

```

SELECT name,salary*12

FROM teacher

上述DQL查询结果集中，第二个字段的字段名直接使用是该表达式，可读性差。

SELECT name,salary*12 sal

FROM teacher

可以为表达式取别名，此时查询结果集第二个字段名会使用该别名。可读性好。

```

- 定义别名支持的语法

1. 字段名 别名

```

SELECT name,salary*12 sal FROM teacher

```

2. 字段名 AS 别名

```

SELECT name,salary*12 AS sal FROM teacher

```

3. 字段名 AS '别名' AS可以省略

```

SELECT name,salary*12 AS 'sal' FROM teacher

```

4. 字段名 AS "别名" AS可以省略

```

SELECT name,salary*12 AS "sal" FROM teacher

```

#### 练习:

1. 查询所有10岁学生的生日,按生日对应的年纪从大到小.

```

SELECT name,age,birth

FROM student

WHERE age=10

ORDER BY birth

```

2. 查询8岁同学中名字含有"苗"的学生信息

```

SELECT name,age

FROM student

WHERE name LIKE '%苗%'

AND age=8

```

3. 查询负责课程编号1和2号且工资高于6000的老师信息

```

SELECT name,subject_id,salary

FROM teacher

WHERE subject_id IN(1,2)

AND salary>6000

```

4. 查询10岁以上的语文课代表和数学课代表中

```

SELECT name,age,job

FROM student

WHERE age>10

AND job IN ('语文课代表','数学课代表')

```

5. 查询不教课程编号1的老师信息,按照工资降序排序

```

SELECT name,subject_id,salary

FROM teacher

WHERE subject_id<>1

ORDER BY salary DESC

```

6. 查询没有奖金的老师信息

```

SELECT name,salary,comm

FROM teacher

WHERE IFNULL(comm,0)=0

```

7. 查询所有老师的奖金，并按照奖金降序排序

```

SELECT name,comm

FROM teacher

ORDER BY comm DESC

SELECT name,IFNULL(comm,0)

FROM teacher

ORDER BY IFNULL(comm,0) DESC

```

8. 查看工资高于8000的老师负责的课程编号都有那些?

```

SELECT DISTINCT subject_id

FROM teacher

WHERE salary>8000

```

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

```

SELECT name,age,birth

FROM student

ORDER BY birth DESC

LIMIT 5,5

```

### 聚合函数

聚合函数**用来统计**的。可以将一个查询结果集中的多条记录统计为一条。

聚合函数:

- MIN() 求指定字段在结果集中所有记录里的最小值

- MAX() 求指定字段在结果集中所有记录里的最大值

- AVG() 求指定字段在结果集中所有记录里的平均值

- SUM() 求指定字段在结果集中所有记录里值的总和

- COUNT() 不是对字段值的统计，而是对结果集中记录数的统计。统计结果集的记录数

**聚合函数的使用时，首要任务是先将需要统计的所有记录查询出来，之后再加以统计。**

#### 例:

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

1. 先列出所有老师的工资

```

SELECT salary FROM teacher

```

2. 对salary加上聚合函数，将结果集中该字段值的所有记录统计平均数

```

SELECT AVG(salary) FROM teacher

```

- 查看学校老师的最高工资，最低工资，平均工资和工资总和分别是多少?

```

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

FROM teacher

```

- 查看负责课程编号1的老师的平均工资是多少?

1. 查看负责课程编号1的老师的工资?

```

SELECT salary FROM teacher WHERE subject_id=1

```

2. 对上述结果集应用聚合函数统计结果

```

SELECT AVG(salary) FROM teacher WHERE subject_id=1

```

- 查看所有老师的奖金总和和平均奖金是多少?

**聚合函数忽略NULL值，这个在AVG和COUNT中表现最为明显**。

```

SELECT SUM(comm),AVG(comm)

FROM teacher

上述DQL查询后实在奖金总和基础上除以15得到的平均值，而非18.因为有3个老师奖金为NULL

可以通过搭配IFNULL来解决:

SELECT SUM(comm),AVG(IFNULL(comm,0))

FROM teacher

```

- 查看学校一共多少个老师?

很多数据库都对COUNT(*)进行过优化，因此统计记录数通常就用它。

```

SELECT COUNT(*) FROM teacher

```

- 查看负责课程编号1的老师一共几人?

```

SELECT COUNT(*) FROM teacher WHERE subject_id=1

```

- 查看班级编号(class_id)为1的班里有多少个学生?

```

SELECT COUNT(*) FROM student WHERE class_id=1

```

- 查看学校年龄最大的大队长生日是哪天?

寻找student表中所有大队长的生日中距离现在最远的

```

SELECT MIN(birth) FROM student WHERE job='大队长'

```

### GROUP BY子句,分组

GROUP BY子句可以将其所在的DQL语句中的查询结果集上按照指定的字段值相同的记录进行分组，搭配聚合函数可以进行组内统计。

**分组是配合统计的，如果SELECT子句中没有聚合函数时，无需使用GROUP BY子句**

#### 例

1. 查看每个科目的老师的平均工资分别是多少?

```

SELECT name,salary,subject_id

FROM teacher

```

上述SQL会得到结果集


2. 在DQL上添加GROUP BY子句

```

SELECT salary,subject_id

FROM teacher

GROUP BY subject_id

意思是将:

SELECT salary,subject_id

FROM teacher

查询出的结果集按照subject_id字段值相同的记录分组

```

等同于将结果集分为了五组:


3. 最终配合聚合函数，可以对结果集中五组数据产生五个统计结果

```

SELECT AVG(salary),subject_id

FROM teacher

GROUP BY subject_id

```

**在SELECT子句中如果包含聚合函数,那么凡是不在聚合函数中的字段都应当出现在GROUP BY子句中**

- 查看每个班各有多少学生?提示:student表的class_id为班级编号,该字段值相同的为同一个班

```

SELECT COUNT(*),class_id

FROM student

GROUP BY class_id

```

- 查看学校每种职位的学生各多少人?以及每个职位最大的生日和最小生日?

```

SELECT COUNT(*) 总人数,MAX(birth) 最小生日,MIN(birth) 最大生日,job

FROM student

GROUP BY job

```

##### GROUP BY可以按照多列分组。结果集中指定的这些列值的组合相同的记录看作一组

- 查看同班同职位的学生各多少人?

```

SELECT COUNT(*)

FROM student

GROUP BY job,class_id

```

##### 为聚合函数取别名，可便于按该字段的结果排序

- 查看每个科目老师的平均工资，平均工资从少到多排序

```

SELECT AVG(salary) avg,subject_id

FROM teacher

GROUP BY subject_id

ORDER BY avg

```

#### 在分组统计中使用过滤条件

##### 例

-

```

SELECT AVG(salary),subject_id

FROM teacher

WHERE AVG(salary)>6000

GROUP BY subject_id

```

**聚合函数不能被应用于WHERE子句中**

本质原因是过滤时机不同，WHERE子句中添加的过滤条件是在第一次从表中逐条检索数据时生效的，从而产生查询结果集。

**实际的过滤实际应当是在从表中查询出结果集并针对该结果集分组统计得出统计结果后再进行过滤**

##### HAVING子句

HAVING子句是紧跟在GROUP BY子句之后的子句，它的作用是添加过滤条件针对分组统计结果后的数据进行筛选

HAVING子句可以利用聚合函数的统计的结果进行过滤

- 查看每个科目老师的平均工资，前提是之查看平均工资高于6000的?

```

SELECT AVG(salary),subject_id

FROM teacher

GROUP BY subject_id

HAVING AVG(salary)>6000

```

- 查看每个科目老师的平均工资，前提是该科目老师的最高工资高于5000?

```

SELECT AVG(salary),subject_id

FROM teacher

GROUP BY subject_id

HAVING MAX(salary)>5000

```

- 查看科目老师的工资总和,前提是该科目老师的平均奖金高于4000?

```

SELECT SUM(salary),subject_id

FROM teacher

GROUP BY subject_id

HAVING AVG(IFNULL(comm,0))>4000

```

### 子查询

嵌套在其他SQL语句中的一条DQL语句，那个这个DQL就称为是子查询。

子查询常被应用于:

- DQL中：可以基于一个查询结果集进行查询(最常被用于DQL语句)

- DML中 : 可以基于一个查询结果集进行增删改操作

- DDL中 ： 可以基于一个查询结果集进行数据对象操作(创建表，视图等)

#### 在DQL语句中使用子查询

**子查询在DQL中使用时必须使用"()"括起来**

##### 例

- 哪个老师的工资高于"王克晶"的工资?

1. 查询王克晶的工资?

```

SELECT salary FROM teacher WHERE name='王克晶' ==> 8000

```

2. 查看谁的工资高于她(工资>8000)?

```

SELECT name,salary

FROM teacher

WHERE salary>(SELECT salary FROM teacher WHERE name='王克晶')

```

- 查看高于老师平均工资的那些老师的工资分别是多少?

```

1:确定条件=>老师的平均工资

SELECT AVG(salary) FROM teacher

2:基于条件查询

SELECT name,salary

FROM teacher

WHERE salary>(SELECT AVG(salary) FROM teacher)

```

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

```

1:"李费水"在哪个班(class_id)?

SELECT class_id FROM student WHERE name='李费水'

2:查看谁的班级ID和李费水的相同

SELECT name,class_id

FROM student

WHERE class_id=(SELECT class_id FROM student WHERE name='李费水')

```

- 查看工资最高的那个老师的工资及奖金是多少?

```

1:查看老师的最高工资是多少?

SELECT MAX(salary) FROM teacher

2:查看工资最高的那个老师的工资及奖金是多少?

SELECT name,salary,comm

FROM teacher

WHERE salary=(SELECT MAX(salary) FROM teacher)

```

#### 子查询分类

- 单行单列子查询:查询结果集为一行一列，即:只有一个值

通常用于过滤条件，可以搭配:=,>,>=,<,<=,<>使用

- 多行单列子查询:查询结果集为多行一列，即:有多个值

通常用于过滤条件，但是要搭配:IN,ALL,ANY使用

IN等效于"="做等于判断，只不过是判断等于列表其中之一

ALL和ANY用于">,>=,<,<="使用:

1. \>ANY：大于列表之一(大于列表最小的即可)

2. \>ALL: 大于列表所有(大于列表最大的)

3. <ANY：小于列表之一(小于列表最大的即可)

4. <ALL: 小于列表所有(小于列表最小的)

- 多行多列子查询:查询结果集为一个表.

1. 常被应用于DQL语句中的FROM子句中当作一张表看待

2. 被用作DDL语句中将一个结果集当作一张表创建出来

####

##### 多行单列子查询

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

```

1:查看"祝雷"和"乐华"的班级号是多少?

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

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

SELECT name,class_id

FROM student

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

上述代码会出现错误:子查询返回了多余1行记录，原因不可能用"="来同时满足等于多个值的判断

实际需求:只要班级编号等于子查询结果的其中之一即可

当判断等于某个子查询结果时，如果该子查询为一个多行单列子查询时，要搭配IN来判断等于其中之一

SELECT name,class_id

FROM student

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

```

- 查看比科目2和科目3老师工资都高的老师都有谁?

```

1:知道科目2和科目4老师的工资分别是多少?

SELECT salary FROM teacher WHERE subject_id IN (2,4)

2:大于上述查询所有工资即可

SELECT name,salary

FROM teacher

WHERE salary>ALL(SELECT salary FROM teacher WHERE subject_id IN (2,4))

```

##### 多行多列子查询

- 在DDL语句中使用

创建一张表，表明:teacher_salary_info。该表字段:max_sal,min_sal,avg_sal,sum_sal,subject_id

表中记录了每个科目老师的最高工资，最低工资，平均工资和工资总和以及对应的科目

```

表中数据对应的查询语句:

SELECT

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

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

FROM teacher

GROUP BY subject_id

上述DQL查询结果集就是我们目标要创建的表teacher_salary_info希望的样子与数据

CREATE TABLE teacher_salary_info

AS

SELECT

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

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

FROM teacher

GROUP BY subject_id

```

### 关联查询

查询结果集中的数据来自于多张表，而表与表中的数据之间存在的对应关系被称为关联关系。

两张表中的数据就可以产生关联关系，常见的关联关系有三种

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

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

- 多对多:A表与B表双向都是一对多关系就称为多对多关系

关联查询就是指联合多张表查询数据形成一个查询结果集的过程。

**在关联查询中的重中之重就是指定关联条件，也叫连接条件**

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

例如:3张表关联查询至少要有2个连接条件

##### 笛卡尔积

**缺少连接条件时会产生笛卡尔积，会将A表每条记录与B表每条记录都建立一次连接，产生巨量数据，这通常是一个无意义的结果集，要尽量避免。**

数据量计算:

以两张表为例:若A表10条记录，B表10条记录，笛卡尔积会产生两张表数据量乘积的条数。

即:10*10等于100条记录

#### 关联查询语法:

```

SELECT 字段

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

WHERE 连接条件

AND 过滤条件

注意:表与表之间的连接条件必须与过滤条件同时满足！否则会产生笛卡尔积

```

#### 例：

- 查看每个学生的名字，年龄以及其所在的班级名称和所在楼层

```

1:先确定数据来自于哪些表?

学生信息来自于student表，班级信息来自于class表

明确FROM子句:

FROM student,class

2:确定两张表之间的数据关联关系

学生表中某条记录的class_id的值与班级表中某条记录的id值相同的建立连接关系

明确WHERE子句中的连接条件:

WHERE student.class_id=class.id

3:查询相应的字段并指定对应的过滤条件...

查询数据时要指定表名.列名来明确数据来自哪张表的字段，此时由于表名可能较长，导致SQL编写可读性差:

SELECT student.name,student.age,class.name,class.floor

FROM student,class

WHERE student.class_id=class.id

解决办法，为表取别名:

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

FROM student s,class c

WHERE s.class_id=c.id

```

- 查看每个班级的名字，所在楼层，以及班主任的名字和工资

```

1:先确定数据来自于哪些表?

班级表class和老师表teacher

明确FROM子句:

FROM class c,teacher t

2:确定两张表之间的数据关联关系

班级表中的teacher_id字段的值与teacher表id字段值相同的记录关联在一起

明确WHERE子句中的连接条件:

WHERE c.teacher_id=t.id

3:查询相应的字段并指定对应的过滤条件...

查询数据时要指定表名.列名来明确数据来自哪张表的字段，此时由于表名可能较长，导致SQL

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

FROM class c,teacher t

WHERE c.teacher_id=t.id

```

- 在关联查询同时添加过滤条件

**关联条件要与过滤条件同时满足。条件之间要用AND连接**

查看"王克晶"是哪个班的班主任?

```

1:确定数据来自哪些表?

查看的是班级信息，因此数据仅来自于班级表class

2:过滤条件来自哪张表? 老师的名字=王克晶

过滤条件来自于老师表teacher：teacher.name='王克晶'

3:班级表与老师表的关联关系?

class.teacher_id=teacher.id

SELECT c.name,t.name

FROM class c,teacher t

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

AND t.name='王克晶' 过滤条件要与连接条件同时满足!!!

```

- N表联合查询至少要有N-1个连接条件，并且这些连接条件都要同时满足

查看老师"范传奇"所带的班的学生都有谁?

```

1:数据来自哪些表?

学生信息来自于student,老师信息来自于teacher

2:过滤条件来自哪张表?

老师的名字是"范传奇"，条件来自于teacher表。

3:student表于teacher表的关联关系?

问题:student表与teacher表本身之间没有关系。

解决办法:找到其他与这两张表之间都有关系的表，作为中间建立联系的表

班级表:class表.该表与student表关联条件:student.class_id=class.id

该表与teacher表关联条件:class.teacher_id=teacher.id

SELECT s.name,s.age,s.class_id,c.id,c.name,c.teacher_id,t.id,t.name

FROM student s,class c,teacher t

WHERE s.class_id=c.id 该条件确定学生表记录与班级表记录的对应关系

AND c.teacher_id=t.id 该条件确定班级表记录与老师表记录的对应关系

AND t.name='范传奇'

```

- 查看来自北京的学生都是谁?

```

SELECT s.name,s.age,l.name

FROM student s,location l

WHERE s.location_id=l.id

AND l.name='北京'

```

- 教"英语"的老师都是谁?

```

SELECT t.name,su.name

FROM teacher t,subject su

WHERE t.subject_id=su.id

AND su.name='英语'

```

- 范传奇代班的学生共多少人?

```

1:准备要统计的数据

SELECT s.name,t.name

FROM teacher t,class c,student s

WHERE t.id=c.teacher_id

AND c.id=s.class_id

AND t.name='范传奇'

上述DQL语句可以查询出范传奇带的所有学生信息

2:添加聚合函数进行统计条目数即可

SELECT COUNT(*)

FROM teacher t,class c,student s

WHERE t.id=c.teacher_id

AND c.id=s.class_id

AND t.name='范传奇'

```

- 查询教语文的老师的平均工资是多少?

```

1:统计前先准备数据->查询所有语文老师的工资

1.1:查询的数据来自哪些表? 查工资salary来自teacher表

1.2:过滤条件:科目名为"语文" 科目来自于科目表subject的name字段

1.3:teacher表与subject表关联条件:teacher.subject_id=subject.id

2:在SELECT子句中对salary字段添加AVG()聚合函数

SELECT AVG(t.salary)

FROM teacher t,subject su

WHERE t.subject_id=su.id

AND su.name='语文'

```

- 查看每门课老师的平均工资分别是多少?结果集中要体现:平均工资及科目名称

```

SELECT AVG(t.salary),su.name

FROM teacher t,subject su

WHERE t.subject_id=su.id

GROUP BY su.name

仅看平均工资高于6000的?

SELECT AVG(t.salary),su.name

FROM teacher t,subject su

WHERE t.subject_id=su.id

GROUP BY su.name

HAVING AVG(t.salary)>6000

平均工资从多到少展示

SELECT AVG(t.salary),su.name

FROM teacher t,subject su

WHERE t.subject_id=su.id

GROUP BY su.name

HAVING AVG(t.salary)>6000

ORDER BY AVG(salary) DESC

```

- 查看所有教**英语**的老师带的来自**上海**或**南京**的学生都有谁?

结果集中要体现:学生的名字，年龄，所在班级名称，老师名称以及来自的城市

```

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

FROM student s,class c,teacher t,location l,subject su

WHERE s.class_id=c.id 学生与班级建立关联关系

AND c.teacher_id=t.id 班级与老师建立关联关系

AND t.subject_id=su.id 老师与科目建立关联关系

AND s.location_id=l.id 学生与所在地建立关联关系

AND su.name='英语' 科目为英语的记录

AND l.name IN ('上海','南京')

```

- 查询工资最低的老师带的学生共多少人?

```

1:未知条件->谁的工资最低? 查询出最低工资是多少，之后谁的工资等于它

求出最低工资是多少:

SELECT MIN(salary) FROM teacher

2:数据来自那些表? student表

3:过滤条件来自哪些表? 查询最低工资的老师，过滤条件来自于teacher表

4:student表与teacher表没有直接关联关系，因此还要借助:class表

准备所有要统计的数据，可以先将最低工资的老师和他的学生查询出来

SELECT s.name,t.name

FROM student s,class c,teacher t

WHERE s.class_id=c.id 连接条件

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

AND t.salary=(SELECT MIN(salary) FROM teacher) 该老师的工资等于最低工资

添加聚合函数

SELECT COUNT(*)

FROM student s,class c,teacher t

WHERE s.class_id=c.id 连接条件

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

AND t.salary=(SELECT MIN(salary) FROM teacher)

```

##### 关联关系通常是建立在主外键等值连接的基础上的

以学生表Student和班级表Class为例:

班级表中id字段作为班级表的**主键**:primary key(pk). 主键:非空且唯一

学生表中的学生为了与班级表的记录产生对应关系，因此在学生表中以class_id字段记录了其所在的班级的主键字段的值。那么class_id就称为是class表主键所对应的**外键**:foreign key(fk)

保存外键字段值的表在关联关系(一对多)中处于"多"的一方


#### 多对多关系

两张表之间双向都是一对多就是多对多关系。多对多关系需要依靠一张关联关系表维系。

关联关系表中有两个字段分别记录了这两张表的主键字段。

t_stu_subject_score表，该表有两个字段stu_id，subject_id分别记录了student表的id和subject表的id。因此依靠这张表就可以实现student与subject表的多对多关系。

实际查询时，写法就是三张表关联查询即可

- 查看'李费水'每门课程的成绩是多少?

结果集中需要体现:学生名字，科目名称，考试成绩

```

1:数据来自哪些表?

学生名字来自student表，科目名称来自subject表，考试成绩来自t_stu_subject_scroe

2:student表与subject表本身没有关联关系，它们都与t_stu_subject_score有关系

关联条件:student别名s t_stu_subject_score别名sss subject别名su

s.id=sss.stu_id

sss.subject_id=su.id

SELECT s.name,su.name,sss.score

FROM student s,subject su,t_stu_subject_score sss

WHERE s.id=sss.stu_id

AND sss.subject_id=su.id

AND s.name='李费水'

```

#### 练习

1. 查看1年级1班所有同学的语文成绩是多少?

```

1:数据来自那些表?查看成绩，学生姓名，科目,班级名称。表:t_stu_subject_score,student,subject,class

2:过滤条件来自哪些表?1年级1班：class表，语文:subject表

3:4张表要有3个连接条件

SELECT s.name,su.name,sss.score,c.name

FROM student s,class c,subject su,t_stu_subject_score sss

WHERE s.class_id=c.id

ANDs.id=sss.stu_id

AND su.id=sss.subject_id

AND c.name='1年级1班'

AND su.name='语文'

```

2. 统计1年级1班数学成绩的平均值?

```

SELECT AVG(sss.score)

FROM student s,class c,subject su,t_stu_subject_score sss

WHERE s.class_id=c.id

ANDs.id=sss.stu_id

AND su.id=sss.subject_id

AND c.name='1年级1班'

AND su.name='数学'

```

3. 统计6年级的英语成绩的平均值?

```

SELECT AVG(sss.score)

FROM student s,class c,subject su,t_stu_subject_score sss

WHERE s.class_id=c.id

ANDs.id=sss.stu_id

AND su.id=sss.subject_id

AND c.name LIKE '6年级%'

AND su.name='英语'

```

4. 查看4年级数学成绩最高的同学名字?

```

1:4年级数学最高的分数是多少?(子查询)

2:找到4年级同学中数学分数=该最高值的

1:

SELECT MAX(sss.score)

FROM student s,class c,subject su,t_stu_subject_score sss

WHERE s.class_id=c.id

ANDs.id=sss.stu_id

AND su.id=sss.subject_id

AND c.name LIKE '4年级%'

AND su.name='数学'

2:

SELECT s.name,c.name,sss.score

FROM student s,class c,subject su,t_stu_subject_score sss

WHERE s.class_id=c.id

ANDs.id=sss.stu_id

AND su.id=sss.subject_id

AND c.name LIKE '4年级%'

AND su.name='数学'

AND sss.score=(SELECT MAX(sss.score)

FROM student s,class c,subject su,t_stu_subject_score sss

WHERE s.class_id=c.id

ANDs.id=sss.stu_id

AND su.id=sss.subject_id

AND c.name LIKE '4年级%'

AND su.name='数学')

```

5. 查看"刘苍松"所带班级的英语平均分?

```

1:数据来自哪些表

t_stu_subject_score,teacher,class,subject

2:过滤条件来自哪些表?

teacher,subject

SELECT AVG(sss.score)

FROM student s,class c,teacher t,subject su,t_stu_subject_score sss

WHER s.class_id=c.id

AND s.id=sss.stu_id

AND sss.subject_id=su.id

AND c.teacher_id=t.id

AND t.name='刘苍松'

AND su.name='英语'

```

6. 查看工资最高的老师所带班级的各科成绩的平均分，最高分和最低分分别是多少?

```

1:组织统计的数据

科目 最高分 最低分 平均分

语文 MAX(sss.score) MIN(sss.score) AVG(sss.score)

需统计的数据展示的内容有科目，分数。按照科目分组统计分数即可

2:查看工资最高的老师所带班级所有同学每门课的分数是多少(要统计的数据)

2.1:工资最高的老师是谁?

SELECT name

FROM teacher

WHERE salary=(SELECT MAX(salary) FROM teacher)

2.2:该老师所在的班级

2.2:关联查询

数据来自哪些表?

成绩:t_stu_subject_score 科目:subject

过滤条件?

工资最高(2.1的子查询完成)的老师的名字 teacher表

SELECT MAX(sss.score),MIN(sss.score),AVG(sss.score),su.name

FROM student s,class c,t_stu_subject_score sss,subject su,teacher t

WHERE s.class_id=c.id

AND s.id=sss.stu_id

AND sss.subject_id=su.id

AND c.teacher_id=t.id

AND t.salary=(SELECT MAX(salary) FROM teacher)

GROUP BY su.name

```

7. 查看所有大队长的5门成绩平均分是多少？

```

SELECT s.name,AVG(sss.score) avg_score

FROM student s,t_stu_subject_score sss,subject su

WHERE s.id=sss.stu_id

AND sss.subject_id=su.id

AND s.job='大队长'

GROUP BY s.name

ORDER BY avg_score DESC

```

8. 教"数学"的老师所带班的英语平均分分别是多少?

```

1:查看假定1年级1班和1年级2班所有学生英语成绩

SELECT s.name,su.name,sss.score,c.name

FROM student s,class c,teacher t,t_stu_subject_score sss,subject su

WHERE s.class_id=c.id

AND c.teacher_id=t.id

AND s.id=sss.stu_id

AND sss.subject_id=su.id

AND su.name='英语'

AND c.name IN ('1年级1班','1年级2班')

2:统计两个班英语的平均值

SELECT AVG(sss.score),c.name

FROM student s,class c,teacher t,t_stu_subject_score sss,subject su

WHERE s.class_id=c.id

AND c.teacher_id=t.id

AND s.id=sss.stu_id

AND sss.subject_id=su.id

AND su.name='英语'

AND c.name IN ('1年级1班','1年级2班')

GROUP BY c.name

3:查看教数学的老师所带的班级名称?

SELECT c.name

FROM class c,teacher t,subject su

WHERE c.teacher_id=t.id

AND t.subject_id=su.id

AND su.name='数学'

4:将步骤三内容作为自查徐替换步骤2中班级名称的比较即可

SELECT AVG(sss.score),c.name

FROM student s,class c,teacher t,t_stu_subject_score sss,subject su

WHERE s.class_id=c.id

AND c.teacher_id=t.id

AND s.id=sss.stu_id

AND sss.subject_id=su.id

AND su.name='英语'

AND c.name IN (SELECT c.name

FROM class c,teacher t,subject su

WHERE c.teacher_id=t.id

AND t.subject_id=su.id

AND su.name='数学')

GROUP BY c.name

```

9. 2年级语文最高分学生所在班里的女同学都来自哪个城市?

```

1:查询需求

学生来自哪个城市?(去重)

2:过滤条件:

2.1女同学

2.2班级号与2年级语文最高分的同学相同

3:从哪查?

student,location

先解决:2年级语文最高分的同学的班级号

1:找到2年级语文最高分是多少

class,student,subject,t_stu_subject_score

先列出2年级所有学生的语文成绩

SELECT s.name,su.name,sss.score,c.name

FROM student s,class c,t_stu_subject_score sss,subject su

WHERE s.class_id=c.id

AND s.id=sss.stu_id

AND sss.subject_id=su.id

AND c.name LIKE '2年级%'

AND su.name='语文'

找到2年级语文最高分?

SELECT MAX(sss.score)

FROM student s,class c,t_stu_subject_score sss,subject su

WHERE s.class_id=c.id

AND s.id=sss.stu_id

AND sss.subject_id=su.id

AND c.name LIKE '2年级%'

AND su.name='语文'

找谁的成绩是最高分，且她所在的班级

SELECT c.name

FROM student s,class c,t_stu_subject_score sss,subject su

WHERE s.class_id=c.id

AND s.id=sss.stu_id

AND sss.subject_id=su.id

AND su.name='语文'

AND c.name LIKE '2年级%'

AND sss.score=(SELECT MAX(sss.score)

FROM student s,class c,t_stu_subject_score sss,subject su

WHERE s.class_id=c.id

AND s.id=sss.stu_id

AND sss.subject_id=su.id

AND c.name LIKE '2年级%'

AND su.name='语文')

查看2年级1班和3班所有的女同学所在的城市

SELECT s.name,s.gender,l.name,c.name

FROM student s,location l,class c

WHERE s.location_id=l.id

AND s.class_id=c.id

AND s.gender='男'

AND c.name IN ('2年级1班','2年级3班')

最终将IN的部分换成上面的查询

SELECT s.name,s.gender,l.name,c.name

FROM student s,location l,class c

WHERE s.location_id=l.id

AND s.class_id=c.id

AND s.gender='男'

AND c.name IN (SELECT c.name

FROM student s,class c,t_stu_subject_score sss,subject su

WHERE s.class_id=c.id

AND s.id=sss.stu_id

AND sss.subject_id=su.id

AND su.name='语文'

AND c.name LIKE '2年级%'

AND sss.score=(SELECT MAX(sss.score)

FROM student s,class c,t_stu_subject_score sss,subject su

WHERE s.class_id=c.id

AND s.id=sss.stu_id

AND sss.subject_id=su.id

AND c.name LIKE '2年级%'

AND su.name='语文'))

```

#### 内连接

##### 语法

```

SELECT t1.xxx,t2.xxx,...

FROM 表1 t1

JOIN 表2 t2 ON 与表1的连接条件

[JOIN 表3 t3 ON 与表1或表2的连接条件]

```

内连接的查询与关联查询一致，却别在于我们将关联关系(连接条件)单独定义在JOIN后面跟的ON子句上。

优点:关联查询时结构清晰，连接条件与过滤条件分开在不同的子句定义。ON子句定义连接条件，WHERE子句定义过滤条件

##### 例

- 查看每个班的班主任是谁?

1. 原来的写法

```

SELECT c.name,t.name

FROM class c,teacher t

WHERE c.teacher_id=t.id

```

2. 内连接的写法

```

SELECT c.name,t.name

FROM class c

JOIN teacher t ON c.teacher_id=t.id

```

- 查看范传奇和刘苍松带的班级是哪个?

1. 原来的写法

```

SELECT c.name,t.name

FROM class c,teacher t

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

AND t.name IN ('范传奇','刘苍松')

```

2. 内连接的写法

```

SELECT c.name,t.name

FROM class c

JOIN teacher t ON c.teacher_id=t.id

WHERE t.name IN ('范传奇','刘苍松')

```

- 查看1年级1班的学生的名字和其所来自的城市?

1. 原来的写法

```

SELECT s.name,c.name,l.name

FROM student s,class c,location l

WHERE s.class_id=c.id

AND s.location_id=l.id

AND c.name='1年级1班'

```

2. 内连接写法

```

SELECT s.name,c.name,l.name

FROM student s

JOIN class c ON s.class_id=c.id

JOIN location l ON s.location_id=l.id

WHERE c.name='1年级1班'

```

- 查看2年级每个班的学生的名字以及其班主任的名字?

```

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

FROM student s

JOIN class c ON s.class_id=c.id

JOIN teacher t ON c.teacher_id=t.id

WHERE c.name LIKE '2年级%'

```

- 查看3年级2班每个同学的数学成绩，并按成绩的降序展示

```

SELECT s.name,su.name,sss.score,c.name

FROM class c

JOIN student s ON s.class_id=c.id

JOIN t_stu_subject_score sss ON s.id=sss.stu_id

JOIN subject su ON sss.subject_id=su.id

WHERE c.name='3年级2班'

AND su.name='数学'

ORDER BY sss.score DESC

```

- 查看4年级每个班的数学平均分是多少?

```

SELECT AVG(sss.score),su.name,c.name

FROM class c

JOIN student s ON s.class_id=c.id

JOIN t_stu_subject_score sss ON s.id=sss.stu_id

JOIN subject su ON sss.subject_id=su.id

WHERE c.name LIKE '4年级%'

AND su.name='数学'

GROUP BY c.name,su.name 班级名相同且科目相同的记录看作一组

```

GROUP BY子句中如果出现了多个字段，则是将结果集中指定的这些字段值的组合相同的记录看作一组。

#### 外连接

外连接的作用是将关联查询中不满足关联条件的记录显示在结果集中。

外连接分类:

- 左外连接 LEFT JOIN

左外连接是将LEFT JOIN左侧的表做为主表，结果集中会包含该表所有满足过滤条件的记录，当来自于LEFT JOIN右侧表的字段时，不满足连接条件的记录值全为NULL

- 右外连接 RIGHT JOIN

右外连接是将RIGHT JOIN右侧的表做为主表，结果集中会包含该表所有满足过滤条件的记录，当来自于RIGHT JOIN左侧表的字段时，不满足连接条件的记录值全为NULL

##### 例

- 查看每个班级名字和班主任的名字

```

SELECT c.name,t.name

FROM class c

JOIN teacher t ON c.teacher_id=t.id

```

class表中如:1年级4班没有被列出在结果集中

teacher表中如:韩少云没有被列出在结果集中

原因:这些记录不满足连接条件 c.teacher_id=t.id

结论:不满足连接条件的记录不会在关联查询和内连接中被查询出来

- 查看每个班级名字和班主任的名字,如果该老师不带班也要把老师显示出来

需求:当teacher表中有不满足连接条件的记录时，也要将其显示出来

```

SELECT c.name,t.name

FROM class c

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

```

- 查看每个班级名字和班主任的名字,如果该班没有班主任，也要将该班级查询出来

```

SELECT c.name,t.name

FROM class c

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

```

- UNION取并集，将左连接与右连接并集，可以达到全连接效果

union可以连接两个DQL语句的结果集，并将它们并为一个结果集，其中重复的记录仅显示一次。

```

SELECT c.name,t.name

FROM class c

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

UNION

SELECT c.name,t.name

FROM class c

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

```

#### 自连接

同一张表中的一条数据可以对应多条数据。

自连接通常用于保存具有相同属性且存在上下级关系的树状结构数据使用

例如

- 公司中的组织架构

- 电商中的类别树

例

- 查看每个老师和他的领导是谁?

```

关联查询写法:

SELECT t.name,m.name

FROM teacher t,teacher m

WHERE t.manager=m.id

内连接

SELECT t.name,m.name

FROM teacher t

JOIN teacher m ON t.manager=m.id

```

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

```

SELECT t.name

FROM teacher t

JOIN teacher m ON t.manager=m.id

WHERE m.name='刘苍松'

```

- 把所有老师和他的上司列出来，没有上司的也要把该老师列出来

```

SELECT t.name,m.name

FROM teacher t

LEFT JOIN teacher m ON t.manager=m.id

```

- 查看刘苍松的下属所带班级的名称分别是什么?

```

SELECT t.name,c,name,m.name

FROM teacher t

JOIN teacher m ON t.manager=m.id

JOIN class c ON c.teacher_id=t.id

WHERE m.name='刘苍松'

```

- 查看3年级2班的班长是谁?

```

SELECT s.name

FROM student s

JOIN class c ON c.id=s.class_id

WHERE c.name='3年级2班'

AND s.id=s.team_leader

```

- 刘苍松的下属所带班级的班长都是谁?

```

SELECT s.name,c.name,t.name,m.name

FROM teacher t

JOIN teacher m ON t.manager=m.id

JOIN class c ON c.teacher_id=t.id

JOIN student s ON s.class_id=c.id

WHERE m.name='刘苍松'

AND s.id=s.team_leader

```

- 年龄最大的学生所在班的班主任的上司是谁?

```

SELECT

s.name '学生的名字',c.name '所在班级',

t.name '班主任',m.name '班主任的上司'

FROM student s

JOIN class c ON s.class_id=c.id

JOIN teacher t ON c.teacher_id=t.id

JOIN teacher m ON t.manager=m.id

WHERE s.birth=(SELECT MIN(birth) FROM student)

```

- 刘苍松的下属所带的班级各多少人?

```

1:统计的是哪张表的记录数?

学生的数量。数据来自学生表student

2:过滤条件

上司是刘苍松，来自表:teacher(上司表)

3:学生表与上司表没有直接联系，因此要找到关联关系表

student->class->teacher(老师)->teacher(上司)

准备要统计的数据

列出的数据:

学生名,所在班级,班主任，班主任的上司

SELECT s.name,c.name,t.name,m.name

FROM student s

JOIN class c ON s.class_id=c.id

JOIN teacher t ON c.teacher_id=t.id

JOIN teacher m ON t.manager=m.id

WHERE m.name='刘苍松'

最终:

SELECT COUNT(*),s.gender,c.name,t.name,m.name

FROM student s

JOIN class c ON s.class_id=c.id

JOIN teacher t ON c.teacher_id=t.id

JOIN teacher m ON t.manager=m.id

WHERE m.name='刘苍松'

GROUP BY s.gender,c.name,t.name,m.name

ORDER BY c.name

```

#### 关联查询中未指定连接条件，或指定的连接条件无效时会产生笛卡尔积

结果集的数据量是参数查询表中记录数的乘积,除非应用需要，否则**应尽量避免**

```

SELECT COUNT(*)

FROM class c,teacher t

```