数据库(笔记)

查看每个班级名以及对应的班主任名字数据库

数据库基本概念

数据库与数据库管理系统

数据库DataBase

保存一组数据的地方就称为一个数据库.比如我们创建一个目录,在目录中创建若干的文件,每个文件都是一组数据,那么这个目录就可以称为一个数据库.

只是针对这些文件上的数据维护要么手动,要么我们需要编写java代码进行维护,此时维护的通用性差,效率差,维护的复杂度高.

数据库管理系统DBMS

它是一个独立的软件,用户维护磁盘上的数据.

操作它便捷,且通用性高.

常见的DBMS:

• Mysql
• Oracle
• DB2
• SQLServer

在java项目中与数据库管理系统的关系

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

库与表的概念

表:表的结构由行和列构成,用于保存一组数据.

表中的行称为记录,列称为字段

列:用于表示一条记录中有多少个属性

行:保存着一组属性的一条记录

库

可以叫数据库,每个数据库中可以包含若干张表.

实际应用中每一个项目在数据库管理系统中都可以创建一个数据库,然后在这个数据库中创建若干张表来保存不同的数据.

如何操作数据库

所有的数据库DBMS都支持使用SQL语句进行操作.

所有DBMS都支持SQL92标准.

该标准中不是所有的数据库操作都有语法级定义

很多操作不同数据库就会各自定义对应的语法

SQL92标准相当于是"普通话",而各自定义的语法相当于"方言"

这也是数据库学习的主要目的:学会SQL语言

SQL的全称:Structured Query Language 翻译:结构化查询语言

连接数据库的方式

• 命令行形式
• 图形化界面(第一方或第三方提供的独立软件)
• IDEA中操作
• JDBC(java数据库连接),直接用java程序进行数据库连接与交互

SQL分类

• DDL 数据定义语言
• DML 数据操作语言
• DQL 数据查询语言
• DCL 数据控制语言
• TCL 事务控制语言

DDL语言

数据定义语言，是针对数据库对象进行操作的语言

关键字:

CREATE,DROP,ALTER

常见的数据库对象:数据库，表，视图，索引，序列等

SHOW DATABASES
查看当前DBMS中已经创建的数据库

数据库操作

新建一个数据库

语法:

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

注:SQL语句中是不区分大小写的，但是行业规范中建议:关键字全大写，其他全小写。

例:

创建一个名为mydb
CREATE DATABASE mydb

创建数据库时指定字符集

CREATE DATABASE mydb1 CHARSET=UTF8;
CREATE DATABASE mydb2 CHARSET=GBK;

查看数据库创建信息

SHOW CREATE DATABASE 数据库名

例如:

SHOW CREATE DATABSE mydb;

删除数据库

DROP DATABASE 数据库名

例如:

DROP DATABASE mydb;

切换数据库

将来可以针对某个数据库进行相关的操作，因此就会存在切换数据库操作

USE 数据库名

例如:

USE mydb1;
USE mydb2;

练习:

1. 创建 db1和db2 数据库 字符集分别为utf8和gbk
   CREATE DATABASE db1 CHARSET=UTF8;
   CREATE DATABASE db2 CHARSET=GBK;
2. 查询所有数据库检查是否创建成功
   SHOW DATABASES;
3. 检查两个数据库的字符集是否正确(查看创建时的SQL)
   SHOW CREATE DATABASE db1;
   SHOW CREATE DATABASE db2;
4. 先使用db2 再使用 db1
   USE db2;
   USE db1;
5. 删除两个数据库
   DROP DATABASE db1;
   DROP DATABASE db2;

表的操作

创建表

语法:

CREATE TABLE 表名(
    字段1名 类型 [默认值，约束条件],
    字段2名 类型 [默认值，约束条件],
    ....
)

例如

CREATE DATABASE mydb;     准备工作，先创建一个数据库mydb
USE mydb;                 切换到mydb数据库
下面创建表时，都是创建到mydb这个库中了
CREATE TABLE user(
	id INT,					在MySQL中整数类型为int
    username VARCHAR(30),   VARCHAR为变长字符串，指定的长度为字节,30表示该字符
    password VARCHAR(30),   串转换为字节后最多存30字节(UTF8编码中文10个字) 
    age INT(3)              int若指定长度为整数的位数
)

查看表结构

语法:

DESC 表名

例如

DESC user;

查看创建表的详细信息

语法

SHOW CREATE TABLE 表名

例如

SHOW CREATE TABLE user;

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

语法:

SHOW TABLES;

修改表名

语法:

RENAME TABLE 原表名 TO 新表名

例如

将user表改名为userinfo
REANAME TABLE user TO userinfo;

删除表

语法

DROP TABLE 表名

例如:

DROP TABLE userinfo;

练习:

1.创建数据库mydb3 字符集gbk 并使用
2.创建t_hero英雄表, 有名字和年龄字段
3.修改表名为hero
4.查看表hero的信息
5.查询表hero结构
6.删除表hero
7.删除数据库mydb3

修改表

准备一张表

CREATE TABLE hero(
	name VARCHAR(30),
	age INT(3)
)

添加字段

• 在表的末尾追加新的字段

  ALTER TABLE 表名 ADD 字段名 类型
  

  例如:

  向表中追加性别字段
  ALTER TABLE hero ADD gender VARCHAR(10)
  

• 在表的最开始插入新字段

  ALTER TABLE 表名 ADD 字段名 类型 FIRST
  

  例如

  ALTER TABLE hero ADD id INT FIRST
  

• 在表中插入字段

  将指定的字段放在表中某字段的后面
  ALTER TABLE 表名 ADD 字段名 类型 AFTER 表中某字段
  

  例如:

  在名字后面追加密码字段pwd
  ALTER TABLE hero ADD pwd VARCHAR(30) AFTER name
  

删除字段

ALTER TABLE 表名 DROP 字段名

例如:

删除hero表中的pwd字段
ALTER TABLE hero DROP pwd

修改字段

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

例如

将hero表中年龄字段的长度改为5
ALTER TABLE hero CHANGE age age INT(5)

将age改为VARCHAR(10)
ALTER TABLE hero CHANGE age age VARCHAR(10)

将hero表中gender字段改名为pwd并且类型为VARCHAR(30)
ALTER TABLE hero CHANGE gender pwd VARCHAR(30)

注意事项

修改字段时，如果表中已经包含数据

• 当修改字段类型的长度时，应当只增不减，由于原字段某记录的值长度不允许缩小了会导致修改失败
• 当修改字段类型时，尽量不进行。例如将name字段改为int值。"张三"怎么改成int？？？

练习:

1.创建数据库mydb4 字符集utf8并使用
2.创建teacher表 有名字(name)字段
3.添加表字段: 最后添加age 最前面添加id(int型) , age前面添加salary工资(int型)
4.删除age字段
5.修改表名为t
6.删除表t
7.删除数据库mydb4

DML 数据操作语言

对表中记录进行操作的语言

关键字:

INSERT,UPDATE,DELETE

准备一张表

CREATE TABLE person(
	name VARCHAR(30),
	age INT(3)
)

插入数据(INSERT语句)

语法:

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

例如:

INSERT INTO pesron (name,age) VALUES ('张三',18)
INSERT INTO pesron (age,name) VALUES (22,'李四')

注：

• 数据库当中，字符串的字面量使用单引号括上，并且字符串的内容是区分大小写的

• 插入数据时指定的字段的顺序可以与表不同，但是VALUES后面指定的值必须与指定的字段顺序，类型，个数一致

• 查询表中数据:

SELECT * FROM 表名
SELECT * FROM person

插入默认值

当插入数据时，某个字段不指定时，则会插入默认值，当表中该字段没有明确指定默认值时，字段默认值为NULL

例如:

INSERT INTO person (name) VALUES ('王五')
没有指定age,则该条记录插入后age字段的值为null

默认值的指定

注：这属于DDL语句的范畴，使用DEFAULT指定默认值

创建表时
CREATE TABLE person(
	name VARCHAR(30) DEFAULT '无名氏',
	age INT(3) DEFAULT 18
)

修改该时
ALTER TABLE person CHANGE age age INT(3) DEFAULT 18;
修改后，age字段的默认值为18

INSERT INTO person (name) VALUES ('赵六')
插入数据后，赵六的年龄为18(默认值)

全列插入

当不指定字段名时，则为全列插入，此时VALUES后面指定的值的顺序，类型，个数必须与表结构一致

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

年龄插入默认值:使用DEFAULT关键字代替值
INSERT INTO person VALUES ('孙八',DEFAULT)

显示的插入NULL值:使用NULL关键字代替值
INSERT INTO person VALUES ('周九',NULL)

下列是错误的:
INSERT INTO person VALUES ('老十')		列对应值的个数不匹配
INSERT INTO person VALUES (22,'老十')		列对应的值类型不匹配(第一列应是名字VARCHAR类型)

修改数据(UPDATE语句)

语法:

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

例:

UPDATE person SET age=40
上述SQL执行后person表中每条记录的age字段值都会被改为40!

WHERE子句在UPDATE中的使用

通常修改表中数据时，要在UPDATE语句中添加WHERE子句，WHERE子句的作用是添加过滤条件用于筛选要修改的记录

子句:在SQL中一个关键字后面会搭配一些内容，这个就是一个子句

例如:

将张三的年龄改为20岁
UPDATE person				子句
SET age=20					子句
WHERE name='张三'			   子句
在上述SQL中，WHERE条件的作用是筛选出修改的记录

数据库在修改记录时是逐条进行修改的，记录是否被修改取决于这条记录是否满足WHERE指定的条件。
上述SQL中只有张三的记录符合WHERE条件，因此只有该条记录的年龄会被修改为20，而其他记录则不会被修改。




将person表中年龄为40岁的改为36岁
UPDATE person
SET age=36
WHERE age=40			person表中凡是年龄为40的记录都会被改为36

WHERE中还可以使用如:>,>=,<,<=,<>(不等于使用"<>",而"!="不是所有数据库都支持)

将person表中年龄大于30岁的人改为年龄29岁
UPDATE person
SET age=29
WHERE age>30

使用表达式进行修改

为person表中每个人的年龄涨一岁
UPDATE person
SET age=age+1

修改多个字段

将person表中的"李四"改名为"李老四"，年龄改为55岁
UPDATE person
SET name='李老四',age=55
WHERE name='李四'

删除数据(DELETE语句)

语法:

DELETE FROM 表名 [WHERE 过滤条件]

注：在DELETE语句中不添加WHERE子句就是清空表操作

例:

删除名字为"李老四"的记录
DELETE FROM person
WHERE name='李老四'			删除表中满足WHERE条件的记录

删除年龄小于25岁的人
DELETE FROM person
WHERE age<25

清空表操作

DELETE FROM person

练习

1.创建数据库day1db 字符集utf8并使用
CREATE DATABASE day1db CHARSET=UTF8
USE day1db
2.创建t_hero表, 有name字段,字符集utf8
CREATE TABLE t_hero(
	name VARCHAR(30)
)CHARSET=UTF8
3.修改表名为hero
RENAME TABLE t_hero TO hero
4.最后面添加价格字段money(整数类型，长度6), 最前面添加id字段(整数类型), name后面添加age字段(整数类  型，长度3)
ALTER TABLE hero ADD money INT(6)
ALTER TABLE hero ADD id INT FIRST
ALTER TABLE hero ADD age INT(3) AFTER name
5.表中添加以下数据: 1,李白,50,6888 2,赵云,30,13888 3,刘备,25,6888
INSERT INTO hero (id,name,age,money) VALUES(1,'李白',50,6888)
INSERT INTO hero (id,name,age,money) VALUES(2,'赵云',30,13888)
INSERT INTO hero (id,name,age,money) VALUES(3,'刘备',25,6888)
6.修改刘备年龄为52岁
UPDATE hero
SET age=52
WHERE name='刘备'
7.修改年龄小于等于50岁的价格为5000
UPDATE hero
SET money=5000
WHERE age<=50
8.删除价格为5000的信息
DELETE FROM hero
WHERE money=5000
9.删除表, 删除数据库
DROP TABLE hero
DROP DATABASE day1db

总结

• DML：数据操作语言，它是对表中数据进行操作的语言，涵盖的操作:增(INSERT)，删(DELETE)，改(UPDATE)

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

  • 插入数据时，指定的字段名的顺序可以与表不相同，但是VALUES子句中指定的值要与指定的字段顺序，个数，类型都相同
  • 插入数据时可以忽略某些字段，此时插入的记录中没有指定的字段会插入该字段的默认值
  • 可以使用DEFAULT关键字显示的为某个字段插入默认值
  • 可以使用NULL显示的为某个字段插入NULL值
  • 插入数据时可以不指定任何字段，此时为全列插入，VALUES子句要求指定的值的顺序，个数，类型必须与表一致

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

  修改数据时通常会指定WHERE子句，否则为全表所有记录进行修改

• DELETE语句用于删除表中的数据

  删除数据时通常也要指定WHERE子句，否则是清空表操作

数据类型

不同的数据库，数据类型不完全一致

数字类型

整数类型:

• INT(m)
• BIGINT(m)

m表示整数的位数。

例如

INT(5):整数最大位数为5.如果插入一条记录该字段值为整数18，实际保存时:00018

浮点类型 DOUBLE(m,n)

• m:数字的位数

• n:为其中小数的位数

• m包含n

  • DOUBLE(5,2):数字总共5位，其中包含2位小数，可保存的最大值:999.99

  • 如果保存数字时小数的精度超过允许范围时，会进行四舍五入

    例如

    Person表中有一个字段为salary是一个DOUBLE类型(7,4)
    INSERT INTO person (salary) VALUES(555.125687)
    插入数据后，该记录中salary字段最终保存的值:555.1257
    

• 如果插入最大值还需要四舍五入时会报错

  INSERT INTO person (salary) VALUES(999.999987)
  

字符类型

定长字符:CHAR(n)

• n表示长度，单位是字符
• name CHAR(10):name可以保存10个字符
• 最大值为255，最多可以保存255个字符
• 定长字符串，无论表中该字段实际保存的字符个数,在磁盘空间中开辟的一定是最大字符所占用的空间。当达不到指定长度时会在后面补充若干个空格。
• 优点:由于每条记录占用的磁盘空间是固定的，因此查询速度更快。
• 缺点:犹豫每条记录该字段占用的磁盘空间是固定的，那么这会导致磁盘空间的浪费

变长字符:VARCHAR(n)

• n表示长度，但是为字节
• 最大值为65535
• 实际占用磁盘空间为用多少占多少
• 优点:磁盘空间没有浪费
• 缺点:由于每条记录的占用空间不固定，因此查询速度相对较慢

变长字符:TEXT(n)

• n表示长度，单位是字符
• 最大值为65535

日期类型

• DATE类型:保存年月日
• TIME类型:保存时分秒
• DATETIME:保存年月日时分秒
• TIMESTAMP:时间戳类型，记录UTC时间。自1970年元旦到该时间之间经过的毫秒

例

准备一张表

CREATE TABLE userinfo(
	id INT,
	username VARCHAR(30),
	gender CHAR(1),
	birth DATETIME,
	salary DOUBLE(7,2)
)

• 插入日期类型是，如果日期类型为DATETIME型，可以用字符串各式表示:"yyyy-MM-dd hh:mm:ss"

  MM表示2位数字的月，mm表示2位数字的分

  INSERT INTO userinfo(id,username,gender,birth,salary)
  VALUES(1,'张三','男','1998-12-20 10:05:22',5000.50)
  

• 插入日期时，如果类型为DATETIME，是可以忽略时分秒的

  INSERT INTO userinfo(id,username,gender,birth,salary)
  VALUES(2,'李四','男','2002-05-16',8000.96)
  

• 插入日期时，如果类型为DATETIME，不可以忽略年月日

  INSERT INTO userinfo(id,username,gender,birth,salary)
  VALUES(3,'王五','女','19:23:56',9500.15)
  

• 插入DOUBLE类型值是，小数部分超过精度会四舍五入，整数部分超过会直接报错

  INSERT INTO userinfo(id,username,gender,birth,salary)
  VALUES(3,'王五','女','2002-05-16 19:23:56',100000)
  

约束条件

可以对表添加约束条件，这样一来只有满足约束的操作才会被允许，否则会被拒绝

主键约束(Primary Key)

• 主键约束在一张表中只能被施加在一个字段上
• 主键约束的特点:该字段的值在整张表中要求每条记录都必须是非空且唯一的
• 通常情况下一张表中第一个字段为主键字段，名字一般使用"id"

例

CREATE TABLE user1(
	id INT PRIMARY KEY,
	name VARCHAR(30),
	age INT(3)
)

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

• 主键字段不能插入重复的值

  INSERT INTO user1(id,name,age) VALUES(1,'王五',36)
  

• 不能将NULL值插入主键字段

  INSERT INTO user1(id,name,age) VALUES(NULL,'王五',36)
  INSERT INTO user1(name,age) VALUES('王五',36)
  

• 修改表中现有记录时，也不能修改重复的值或NULL给主键字段

  UPDATE user1
  SET id=1
  WHERE name='李四'
  

具体主键约束的字段通常可以搭配自增使用AUTO_INCREMENT

• 创建表时，可以在添加主键约束的同时添加自增

  CREATE TABLE user2(
  	id INT PRIMARY KEY AUTO_INCREMENT,
  	name VARCHAR(30),
  	age INT(3)
  )
  

• 修改表时，也可以为主键字段添加自增

  ALTER TABLE user1 CHANGE id id INT PRIMARY KEY AUTO_INCREMENT
  
  如果id字段已经具有了主键约束，那么可以单独修改添加自增
  ALTER TABLE user1 CHANGE id id INT AUTO_INCREMENT
  

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

  INSERT INTO user2(name,age) VALUES('张三',22);
  INSERT INTO user2(name,age) VALUES('李四',16);
  

• 当主键字段具有自增时，可以显示的为主键插入NULL值，此时主键仍然自增(不推荐)

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

非空约束(NOT NULL)

被非空约束修饰的字段，在任何时候该字段的值都不允许为空

例

• 创建表时可以为某字段指定非空约束

  CREATE TABLE user3(
  	id INT PRIMARY KEY AUTO_INCREMENT,
  	name VARCHAR(30) NOT NULL,					name字段被施加非空约束
  	age INT(3)
  )
  

• 修改表时为某字段施加非空约束

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

• 不能将NULL值插入具有非空约束的字段上

  INSERT INTO user3(name,age) VALUES(NULL,16)
  

• 也不能将具有非空约束的字段修改为NULL值

  INSERT INTO user3(name,age) VALUES('张三',22);
  数据插入后进行修改操作:
  UPDATE user3
  SET name=NULL
  WHERE name='张三'
  

• 插入数据时，不能忽略具有非空约束的字段

  INSERT INTO user3(age) VALUES(22)
  

唯一性约束(UNIQUE)

具有唯一性约束的字段，该字段的值整张表中不允许有重复的

例

• 创建一张表时可以为某个字段添加唯一性约束

  CREATE TABLE user4(
  	id INT PRIMARY KEY AUTO_INCREMENT,
  	name VARCHAR(30) UNIQUE,
  	age INT(3)
  )
  

• 可以在修改表时为某个字段添加唯一性约束

  ALTER TABLE user3 CHANGE name name VARCHAR(30) UNIQUE
  注意:如果user3中name字段有非空约束时，上述SQL添加唯一性约束后会取消非空约束
  
  ALTER TABLE user3 CHANGE name name VARCHAR(30) NOT NULL UNIQUE
  
  

• 插入数据时，不能将重复的值插入具有唯一性约束的字段

  INSERT INTO user4(name,age) VALUES('张三',22)		成功
  INSERT INTO user4(name,age) VALUES('张三',55)     失败
  

• 具有唯一性约束的字段，一张表中可以存在NULL值且可以重复

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

• 修改记录时，不能将重复的值修改到具有唯一性约束的字段，NULL除外。

  UPDATE user4
  SET name='张三'
  WHERE age=36
  

外键约束

外键约束在实际开发中几乎不会被采用，因为它的约束要求不能满足很多业务场景对表中数据关联关系的要求。

DQL语言(数据查询语言)

DQL是用来检索表中数据使用的语言(学习的重点内容)

语法

									执行顺序
SELECT 子句                           6
FROM 子句								1
JOIN...ON...子句                      2
WHERE 子句                            3
GROUP BY 子句                         4
HAVING 子句                           5
ORDER BY 子句                         7
LIMIT 子句(方言)                       8

基础查询

一条DQL中至少会包含的两个子句:SELECT子句和FROM 子句

• SELECT 子句用于指定查询表中那些列的内容
• FROM 子句用于指定从那些表中查询数据

语法

SELECT 字段1[,字段2,字段3...(也可以是函数或表达式)]
FROM 表1[,表2,表3...(子查询或视图)]

例

SELECT * FROM teacher
上述SQL用于检索teacher表中所有数据，并且在查询的结果集中列出该表中所有字段(*的作用就是列出所有字段)
但是，数据库执行该SQL时首先会查询数据字典了解teacher表共有多少个字段后才能开始执行真实的查询，这是比较浪费性能的操作，一旦查询频繁，问题会凸显。
由于将来我们都是在JAVA中链接数据库并执行SQL语句，因此我们执行SQL时应当明确指出要查询的字段，哪怕是全列查询也不要使用"*"！！！！！

临时手动查询，可以使用*。

查看teacher表中所有记录，并且每条记录要列出所有字段
SELECT * FROM teacher

查看teacher表中所有记录，仅查看老师的工号，名字，性别，年龄，工资
SELECT id,name,gender,age,salary
FROM teacher

WHERE子句

在DQL中，WHERE子句是用于过滤需要查询的记录。只有满足过滤条件的记录才会被列在查询结果集中。

例

• 查看"一级讲师"的名字，性别，年龄和工资?

  1:查询的是老师的信息，因此可以确定FROM子句应当从teacher表中查询数据
  2:查看名字，性别，年龄和工资，因此可以确定SELECT子句
  3:查看"一级讲师"的信息，因此可以确定WHERE子句的过滤条件:title='一级讲师'
  
  SELECT name,gender,age,salary,title
  FROM teacher
  WHERE title='一级讲师'
  
  

• 查看除"刘苍松"以外的老师的名字，年龄，工资?

  SELECT name,age,salary
  FROM teacher
  WHERE name<>'刘苍松'
  

• 查看职位是"大队长"的学生的名字，年龄和性别?

  SELECT name,age,gender
  FROM student
  WHERE job='大队长'
  

• 查看年龄在30岁(含)以上的老师都有谁?列出名字，年龄，工资，性别

  SELECT name,age,salary,gender
  FROM teacher
  WHERE age>=30
  

使用AND和OR链接多个条件

• AND:都为真是才为真
• OR:都为假时才为假

例

• 查看7岁的大队长都有谁?列出名字，年龄，性别，职位

  SELECT name,age,gender,job
  FROM student
  WHERE job='大队长' AND age=7
  

• 查看班级编号小于6的所有中队长都有谁?列明名字，年龄，性别，班级编号(class_id)，职位

  SELECT name,age,gender,class_id,job
  FROM student
  WHERE class_id<6 AND job='中队长'
  

• 查看所有一级讲师和三级讲师的名字，职称，工资?

  SELECT name,job,salary
  FROM teacher
  WHERE title='一级讲师' OR title='三级讲师'
  

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

  SELECT name,gender,age,job
  FROM student
  WHERE job='大队长' OR job='中队长' OR job='小队长'
  

AND的优先级是高于OR的，可以使用()去提高OR的优先级

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

  SELECT name,age,gender,job,class_id
  FROM student
  WHERE class_id<=6 AND job='大队长' OR job='中队长'
  上述SQL表达的含义是:查看班级编号在6(含)一下的大队长和所有班级的中队长
  
  
  SELECT name,age,gender,job,class_id
  FROM student
  WHERE class_id<=6 AND (job='大队长' OR job='中队长')
  提高OR的优先级满足查询要求
  

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

• 查看所有大队长，中队长和小队长的名字，职位，年龄

  SELECT name,job,age
  FROM student
  WHERE job='大队长' OR job='中队长' OR job='小队长'
  等价
  SELECT name,job,age
  FROM student
  WHERE job IN ('大队长','中队长','小队长')
  
  

• 查看所有一级讲师，二级讲师，三级讲师的名字，职称，工资和性别

  SELECT name,title,salary,gender
  FROM teacher
  WHERE title IN ('一级讲师','二级讲师','三级讲师')
  

NOT IN(列表):不在列表中，不能等于列表中的任何一项

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

  SELECT name,title,salary
  FROM teacher
  WHERE title<>'一级讲师' AND title<>'二级讲师'
  等价于
  SELECT name,title,salary
  FROM teacher
  WHERE title NOT IN ('一级讲师','二级讲师')
  
  

• 查看除大队长，中队长，小队长的其他学生的名字，职位，性别，年龄

  SELECT name,job,gender,age
  FROM student
  WHERE job NOT IN('大队长','中队长','小队长')
  

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

• 查看工资在2000到5000之间的老师的名字，工资，职称和性别

  SELECT name,salary,title,gender
  FROM teacher
  WHERE salary>=2000 AND salary<=5000
  等价于
  SELECT name,salary,title,gender
  FROM teacher
  WHERE salary BETWEEN 2000 AND 5000
                       下限      上限    
  

• 查看年龄在7到10岁的学生的名字，性别，年龄

  SELECT name,gender,age
  FROM student
  WHERE age BETWEEN 7 AND 10
  

• 查看年龄在20到35之间的男老师都有谁?列出名字，性别，年龄，职称

  SELECT name,gender,age,title
  FROM teacher
  WHERE age BETWEEN 20 AND 35 
  AND gender='男'
  

• 查看所有在3-5层的班级都有哪些?列出班级名称和所在楼层

  SELECT name,floor
  FROM class
  WHERE floor BETWEEN 3 AND 5
  

DISTINCT去重，去除结果集中指定字段值重复的记录

语法

SELECT DISTINCT 字段1[,字段2,...]
FROM 表名
...

• DISTINCT关键字必须紧跟在SELECT关键字之后
• DISTINCT后面可以指定多个字段，当指定了多个字段时效果为这些字段值的组合相同的记录会被去重

例

• 查看老师都有哪些职位?

  SELECT title
  FROM teacher
  上述SQL表达的含义时查看每个老师的职位
  
  SELECT DISTINCT title
  FROM teacher
  重复的title仅保留一行
  

• 查看学生的职位都有哪些?

  SELECT DISTINCT job
  FROM student
  

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

  SELECT DISTINCT age,job    结果集中age与job字段值相同的记录仅保留一行
  FROM student
  

练习

1.查看负责课程编号(subject_id)为1的男老师都有谁?
2.查看工资高于5000的女老师都有谁?
3.查看工资高于5000的男老师或所有女老师的工资？
4.查看所有9岁学生的学习委员和语文课代表都是谁?
5.查看工资在6000到10000之间的老师以及具体工资?
6.查看工资在4000到8000以外的老师及具体工资?
7.查看老师负责的课程编号都有什么?
8.查看所有女老师的职称都是什么?
9.查看7-10岁的男同学的职位都有哪些?
10.查看一级讲师和二级讲师的奖金(comm)是多少?
11.查看除老板和总监的其他老师的工资和奖金是多少?
12.查看'3年级2班'和'5年级3班'在那层楼?

LIKE模糊查询

LIKE中有两个通配符:

• %:百分号表达有任意个字符(0-多个)
• _:下划线表示1个字符

组合示意:

LIKE '%X%':字符串中含有X
LIKE '_X%':字符串中第二个字符是X
LIKE 'X%': 字符串中第一个字符是X
LIKE '%X': 字符串中最后一个字符是X
LIKE '%X_Y':字符串中倒数第三个字符是X并且最后一个字符是Y

例

• 查看名字中含有"苍"的老师都有谁?

  SELECT name,age,salary
  FROM teacher
  WHERE name LIKE '%苍%'
  

• 查看姓张的学生都有谁?

  SELECT name,age,job,gender
  FROM student
  WHERE name LIKE '张%'
  

• 查看名字最后一个字是"晶"的老师?

  SELECT name,age,gender
  FROM teacher
  WHERE name LIKE '%晶'
  

• 查看三个字名字中，第二个字是"平"的学生都有谁?

  SELECT name,age,gender
  FROM student
  WHERE name LIKE '_平_'
  

• 查看哪些学生是课代表?

  SELECT name,job
  FROM student
  WHERE job LIKE '%课代表'
  

• 查看所有2班都在几层?

  SELECT name,floor
  FROM class
  WHERE name LIKE '%2班'
  

练习

1.查询名字姓"李"的学生姓名
2.查询名字中包含"江"的学生姓名
3.查询名字以"郭"结尾的学生姓名
4.查询9-12岁里是"课代表"的学生信息
  SELECT name,job,age
  FROM student
  WHERE age BETWEEN 9 AND 12
  AND job LIKE '%课代表'
5.查询名字第二个字是"苗"的学生信息
6.查询姓"邱"的课代表都是谁?
  SELECT name,job
  FROM student
  WHERE name LIKE '邱%'
  AND job LIKE '%课代表'

NULL值判断

• 判断NULL值要使用:IS NULL
• 判断不为NULL值要使用:IS NOT 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 字段1 ASC|DESC,字段2 ASC|DESC ,...

  • 按照多字段排序时，排序存在优先级，首先按照第一个字段排序，再按照第二个字段排序
  • 排序的规则:当按照第一个字段排序后，第一个字段值相同的记录再按照第二个字段的值排序，以此类推

• ORDER BY 只能是DQL语句中的最后一个子句(LIMIT(由于是方言，并且算是ORDER BY的附属))

例

• 查看老师的工资排名

  SELECT name,salary
  FROM teacher
  ORDER BY salary DESC		按照工资降序排序结果集
  

• 查看所有老师的奖金情况，从少到多

  SELECT name,comm
  FROM teacher
  ORDER BY comm
  

• 查看学生的生日，并且生日由早到晚

  日期类型是可以比较大小的，规则:越早的日期越小，越晚的日期越大
  SELECT name,birth
  FROM student
  ORDER BY birth
  

• 查看7-10岁的学生信息，学生排序按照从小到大排序

  SELECT name,age,birth
  FROM student
  WHERE age BETWEEN 7 AND 10
  ORDER BY birth DESC
  

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

  SELECT name,comm,salary
  FROM teacher
  ORDER BY comm ,salary DESC
  
  注意:如果排序的第一个字段值没有重复值时，第二个字段的排序是无效的
  

分页查询

当一条SQL语句查询出的结果集中记录数过多时，会消耗大量的系统资源，因此通常都会采取分段分批的方式

查询结果。

分页查询在SQL92标准中没有语法级别的定义，因此它是方言，不同数据库采取的分页语句可能完全不同。

在Mysql中使用LIMIT来完成的，在ORACLE中使用伪列ROWNUM实现

语法:

ORDER BY ...
LIMIT N,M

• N:表示跳过结果集中N条记录

• M:表示从跳过的N条记录后查询M条记录

• 在分页中常用的参数:

  • 页数:查看第几页数据
  • 每页显示的条目数

• 计算公式:

  • N的公式(页数-1)*每页显示的条目数

  • M就是每页显示的条目数

  • 例如:一页显示15条，显示第三页

    LIMIT (3-1)*15,15===>LIMIT 30,15  跳过30条然后显示15条
    

例

• 查看老师工资排名的前5名?

  分页:每页显示5条，显示第一页
  SELECT name,salary,comm
  FROM teacher
  ORDER BY salary DESC
  LIMIT 0,5
  

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

  - LIMIT N,M
  - N的公式:(页数-1)*每页显示的条数
  - M:每页显示的条数
  SELECT name,comm
  FROM teacher
  ORDER BY comm DESC 
  LIMIT 12,3
  
  

DQL中可以使用函数或表达式进行查询

在SELECT子句中使用表达式

例

• 查看每个老师的工资和年薪是多少?

  SELECT name,salary,salary*12
  FROM teacher
  

在SELECT子句中使用函数

IFNULL函数:IFNULL(arg1,arg2)

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

• 当arg1为NULL时函数范围arg2的值

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

• 内部逻辑示意

• IF(taskScore.get_exp is null,0,taskScore.get_exp)

• IF(ctd.task_delivery_score is not null and taskScore.task_delivery_id is null

  用java语法理解逻辑
  IFNULL(arg1,arg2){
      if(arg1!=null){
          return arg1;
      }else{
          return arg2;
      }
  }
  

  用java语法理解逻辑
  if(taskScore.get_exp==null){
    return 0;
  }else{
    return get_exp;
  }
  

例:

• 查看每个老师工资+奖金一共多少?

  SELECT name,salary,comm,salary+comm
  FROM teacher
  在数据库中，任何数字与NULL计算结果都是NULL
  
  
  SELECT name,salary,comm,salary+IFNULL(comm,0)
  FROM teacher
  
  

在WHERE子句中使用表达式

• 查看年薪小于60000的讲师都有谁?

  SELECT name,salary,salary*12
  FROM teacher
  WHERE salary*12<60000
  

在WHERE子句中使用函数

• 查看奖金小于3000的讲师都有谁?

  SELECT name,comm
  FROM teacher
  WHERE comm<3000
  上述查询中是不显示奖金为NULL值的
  
  
  SELECT name,comm
  FROM teacher
  WHERE IFNULL(comm,0)<3000
  
  

别名

• 在SELECT子句中为函数或表达式取别名，增强可读性。
• 别名也可以在FROM子句中为表取别名，便于关联查询使用。

在SELECT子句中使用别名

语法

• 字段名 别名

  SELECT salary*12 salary
  FROM teacher
  

• 字段名 AS 别名

  SELECT salary*12 AS salary
  FROM teacher
  

• 字段名 [AS] '别名'

  SELECT salary*12 'salary'
  FROM teacher
  
  SELECT salary*12 AS 'salary'
  FROM teacher
  

• 字段名 [AS] "别名"

  SELECT salary*12 "salary"
  FROM teacher
  
  SELECT salary*12 AS "salary"
  FROM teacher
  

• 别名如果是SQL关键字或者包含空格时，要使用引号

  SELECT salary*12 from      语法错误，因为会将from当成关键字使用
  FROM teacher
  
  正确写法:
  SELECT salary*12 'from'    
  FROM teacher
  
  
  
  SELECT salary*12 annu sal     数据库会理解为annu为别名，sal就不能被识别了
  FROM teacher
  
  正确写法
  SELECT salary*12 'annu 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 age=8
  AND name LIKE '%苗%'

3.查询负责课程编号1和2号且工资高于6000的老师信息
  SELECT name,salary,subject_id
  FROM teacher
  WHERE salary>6000
  AND subject_id IN (1,2)

4.查询10岁以上的语文课代表和数学课代表
  SELECT name,age,job
  FROM student
  WHERE age>10
  AND job IN ('语文课代表','数学课代表')

5.查询不教课程编号1的老师信息,按照工资降序排序
  SELECT name,salary,subject_id
  FROM teacher
  WHERE subject_id<>1
  ORDER BY salary DESC

6.查询没有奖金的老师信息
  SELECT name,comm
  FROM teacher
  WHERE IFNULL(comm,0)=0

7.查询所有老师的奖金，并按照奖金降序排序
  SELECT name,comm
  FROM teacher
  ORDER BY comm 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

练习题9中，不添加LIMIT是查看的顺序与添加LIMIT后查询的顺序不一致

MySQL官方引用:

If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.

One factor that affects the execution plan is LIMIT, so an ORDER BY query with and without LIMIT may return rows in different orders.
  
翻译:  
如果多行在 ORDER BY 列中具有相同的值，则服务器可以自由地以任意顺序返回这些行，并且可能会根据整体执行计划以不同的方式返回这些行。换句话说，这些行的排序顺序对于无序列是不确定的。

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

意思是说，当ORDER BY子句指定的字段值多行记录相同时，结果集中这些记录的顺序可能是不同的。因此LIMIT分页会出现重复的数据或丢失数据的现象。

解决办法，添加其他字段(该字段值最好不重复，例如id字段)进行排序确保顺序

9.查看全校年龄最小学生的第6-10名
  SELECT name,age,birth
  FROM student
  ORDER BY birth DESC,id
  LIMIT 5,5

聚合函数

聚合函数又成为:分组函数，多行函数。对结果集中记录进行统计的

聚合函数可以将多条记录中同一个字段的值进行统计并最终得到一条结果。

聚合函数:

• MIN：求指定字段的最小值
• MAX：求最大值
• AVG：求平均值
• SUM：求和
• COUNT：统计记录的行数
• 聚合函数是忽略NULL值的，在AVG,COUNT中最明显
• MIN,MAX,AVG,SUM都是对值的统计，COUNT是对记录数的统计

数据库会先将满足要求的记录查询出结果集，并利用聚合函数对结果集中的记录进行统计

例

• 查看所有老师的平均工资是多少?

  1:准备用于统计的所有记录----查询出所有老师的工资
  SELECT salary
  FROM teacher
  
  2:对工资求平均值
  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:在上述SQL基础上添加聚合函数对工资进行统计
  SELECT AVG(salary)
  FROM teacher
  WHERE subject_id=1
  
  

• 查看一共有多少位老师?

  SELECT COUNT(name)				统计name字段值不为NULL的记录数
  FROM teacher;
  
  SELECT COUNT(comm)				由于comm有NULL值，记录数会减少
  FROM teacher;
  
  SELECT COUNT(*)					数据库对COUNT(*)进行了优化，统计记录数通常使用它
  FROM teacher;
  

• 查看所有老师的平均奖金是多少?

  SELECT AVG(comm),SUM(comm)
  FROM teacher
  由于聚合函数忽略NULL值，因此这里的平均工资统计时忽略了NULL记录，不准确。
  
  解决办法:可以将comm字段值为NULL的记录改为0，使其参与聚合函数的统计。
  SELECT AVG(IFNULL(comm,0)),SUM(comm)
  FROM teacher;
  

• 查看负责课程编号2的老师共多少人?

  SELECT COUNT(*)
  FROM teacher
  WHERE subject_id=2
  

• 查看班级编号(class_id)为1的学生有多少人?

  SELECT COUNT(*)
  FROM student
  WHERE class_id=1
  

• 查看全校学生生日最大的是哪天?

  SELECT MIN(birth)
  FROM student
  

• 查看11岁的课代表总共多少人?

  SELECT COUNT(*)
  FROM student
  WHERE age=11
  AND job LIKE '%课代表'
  

GROUP BY子句

GROUP BY子句是仅用于配合聚合函数的，它可以对结果集中指定字段值相同的记录进行分组，在组内使用聚合函数统计结果。

• 如果SELECT 子句中没有聚合函数，就不会使用GROUP BY
• 在SELECT子句中出现了聚合函数，那么不在聚合函数的中的其他字段都应当出现在GROUP BY子句中

按照单字段分组

例

• 查看教每门课程老师的平均工资是多少?

  SELECT AVG(salary),subject_id
  FROM teacher
  GROUP BY subject_id		在查询出的结果集中，subject_id字段值相同的记录会被分为一组
  

• 查看每个班级各多少人?

  SELECT COUNT(*),class_id	每一组数据统计记录数
  FROM student
  GROUP BY class_id			将student表中class_id字段值相同的记录划分为一组
  

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

  SELECT COUNT(*) '人数',MAX(birth) '最小生日',MIN(birth) '最大生日',job
  FROM student
  GROUP BY job
  

按照多字段分组

GROUP BY子句后可以指定多个字段，将结果集中这些字段值组合相同的记录看作一组

例

• 查看每个班每种职位各多少人?

  SELECT COUNT(*),class_id,job
  FROM student
  GROUP BY class_id,job     在结果集中只有class_id和job字段值都相同的记录会被划分为一组
  

按照聚合函数排序

• 查看每个科目老师的平均工资，并由少到多展示

  SELECT AVG(salary),subject_id
  FROM teacher
  GROUP BY subject_id
  ORDER BY AVG(salary)
  
  好的书写习惯:SELECT 子句中若出现了函数或表达式应当取别名来增加结果集的可读性
  并且在ORDER BY中也可以使用别名对结果集排序
  
  SELECT AVG(salary) avg,subject_id
  FROM teacher
  GROUP BY subject_id
  ORDER BY avg
  

HAVING子句

HAVING子句紧跟在GROUP BY子句之后，用于过滤分组。

HAVING子句中可以使用聚合函数作为过滤条件。

查看各科目老师的平均工资，但是仅查看高于6000的那些科目老师的平均工资

SELECT AVG(salary),subject_id
FROM teacher
WHERE AVG(salary)>6000
GROUP BY subject_id

将聚合函数放在WHERE中进行过滤会出现上述错误:WHERE子句中不能使用聚合函数

原因:

WHERE与HAVING的过滤时机不同

• WHERE是在检索表中数据时进行过滤的，从而将满足该过滤条件的记录产生查询结果集
• HAVING是在产生结果集后并根据GROUP BY子句按照指定字段对结果集分组后， 添加过滤条件来筛选需要的分组
• WHERE先进行过滤，用于确定结果集。HAVING后过滤，用于确定GOURP BY分组

例

• 查看各科目老师的平均工资，但是仅查看高于6000的那些科目老师的平均工资

  											子句执行顺序
  SELECT AVG(salary),subject_id				4：对最终包流的两组数据进行统计
  FROM teacher								1：确定数据来源的表
  GROUP BY subject_id							2：确定数据的分组(按照subject_id将数据分6组)
  HAVING AVG(salary)>6000						3：需要保留的分组(仅有2组满足要求)
  

• 查看每个科目老师的平均工资，前提是该科目老师最高工资要超过9000

  SELECT AVG(salary),subject_id
  FROM teacher
  GROUP BY subject_id
  HAVING MAX(salary)>9000
  

• 查看科目老师的工资总和是多少?前提是该科老师的平均奖金要高于4000.

  SELECT SUM(salary),subject_id
  FROM teacher
  GROUP BY subject_id
  HAVING AVG(IFNULL(comm,0))>4000
  

子查询

嵌套在其他SQL语句中的一个DQL语句被称为子查询

子查询使用场景

• DQL中:基于一个查询结果集再进行二次查询
• DML中:基于一个查询结果集进行数据操作
• DDL中:基于一个查询结果集操作数据库对象(表，视图)

子查询分类

• 单行单列子查询:该子查询的查询结果集仅有一行且这一行仅有一列。该查询结果就只有一个值
• 多行单列子查询:该查询结果集仅有一列，但是存在多行。
• 多行多列子查询:该结果集就是一个表格
• 单列子查询通常用于DQL语句，多列子查询通常用于DDL语句

在DQL中使用子查询

例:

• 查看哪个老师的工资高于王克晶的工资?

  1：查看王克晶的工资是多少?
  SELECT salary FROM teacher WHERE name='王克晶'
  
  2：查看哪个老师的工资高于8000(王克晶的工资)
  SELECT name,salary FROM teacher WHERE salary>8000
  
  
  用java的思想:
  int sal = SELECT salary FROM teacher WHERE name='王克晶';   //sal=8000
  SELECT name,salary FROM teacher WHERE salary>sal
  
  
  实际上在SQL中的写法:子查询要使用()括起来
  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.查看李费水的班级编号
  SELECT class_id FROM student WHERE name='李费水'
  
  2.查看谁的班级编号与李费水相同
  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)
  

使用了多行单列子查询

使用了多行单列子查询时，那么在WHERE中做为过滤条件判断时需要搭配使用:

如:>,>=,<,<=,=,<>此时需要使用ANY,ALL,IN等

ANY和ALL是搭配>,>=,<,<=:

• >ANY:大于列表中的其中之一，大于最小的即可
• <ANYL:小于列表中其中之一，小于最大的即可
• >ALL:大于列表中所有的，大于最大的
• <ALL:小于列表中所有的，小于最小的

IN和NOT IN是代替=和<>

例

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

  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('祝雷','李费水'))
  

搭配使用IN

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

• 查看比教科目2和科目4老师工资都高的老师都有谁?

  1:查询科目2和科目4的老师工资都是多少
  SELECT salary FROM teacher WHERE subject_id IN (2,4)
  
  2:查看高于他们所有工资的老师
  SELECT name,salary,subject_id
  FROM teahcer
  WHERE salary>ALL(SELECT salary FROM teacher WHERE subject_id IN (2,4))
  

在DML语句中使用子查询

例

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

  1.查看范传奇负责的科目ID
  SELECT subject_id FROM teacher WHERE name='范传奇'
  
  2.修改数据
  UPDATE teacher
  SET salary = salary+500
  WHERE subject_id=(SELECT subject_id FROM teacher WHERE name='范传奇')
  
  

在DDL语句中使用子查询

可以将子查询的结果集当作表创建出来

例

• 创建一张表用于记录老师工资的统计情况(按照科目)。要记录每门课老师的最高工资，最低工资，平均工资和工资总和以及科目编号。表名:teacher_salary_info

  1:创建表
  CREATE TABLE teacher_salary_info(
  	max_salary INT,
  	min_salary INT,
  	avg_salary INT,
  	sum_salary INT,
  	subject_id INT
  )
  
  2:写DQL查询表中应有的统计数据
  SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),subject_id
  FROM teacher
  GROUP BY subject_id
  
  3:笨重且重复的劳动。。。大可不必
  INSERT INTO teacher_salary_info(...) VALUES (...)
  INSERT INTO teacher_salary_info(...) VALUES (...)
  ...
  
  
  该查询结果集就是我们希望创建出来的表的样子:
  SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),subject_id
  FROM teacher
  GROUP BY subject_id
  
  此时可以基于该查询创建表
  CREATE TABLE teacher_salary_info
  AS
  SELECT MAX(salary) max_salary,MIN(salary) min_salary,
         AVG(salary) avg_salary,SUM(salary) sum_salary,
         subject_id
  FROM teacher
  GROUP BY subject_id
  注:如果子查询的SELECT子句中出现了函数或表达式时，必须指定别名(别名会组为表的字段名)
  
  
  

关联查询(重点知识)

查询结果集中的数据来自于多张表，联合多张表进行查询。

表与表中的数据会有一定的对应关系，被称为关联关系。

关联关系有三种:

• 一对一:A表中的一条记录仅唯一对应B表中的一条记录
• 一对多:A表中的一条记录可以对应B表中的多条记录
• 多对多:A表与B表中的记录双向看待都是一对多时就是多对多

在关联查询中，非常重要的一个点就是指定链接条件，用于让数据库知道两张表中的数据是如何对应的。延伸出多张表之间也需要分别之中其中两张表的关系。总结:N张表关联，至少要有N-1个链接条件

笛卡尔积

如果连接条件不指定，或者连接条件无效时会产生笛卡尔积，这通常是一个无意义的结果集，性能和时间消耗量惊人，要尽量避免，严重时可能导致服务器宕机。

关联查询的语法

SELECT 表中对应的字段
FROM 表A,表B,...
WHERE 连接条件                 连接条件必须与过滤条件同时成立
AND 过滤条件                   

例

• 查看每个老师以及其任课的课程名称

  老师信息来自teacher表，课程名称来自subject表
  
  SELECT teacher.name,teacher.salary,teacher.subject_id,subject.name
  FROM teacher,subject
  WHERE teacher.subject_id=subject.id
  
  为了解决表名过长问题，可以在FROM中为表取别名，这样WHERE子句，SELECT子句等地方都可以用该别名来定位表中的字段了:
  SELECT t.name,t.salary,t.subject_id,s.name
  FROM teacher t,subject s
  WHERE t.subject_id=s.id
  
  

• 笛卡尔积的产生

  SELECT t.name,t.salary,t.subject_id,s.name
  FROM teacher t,subject s
  
  不指定连接条件时，会产生笛卡尔积
  数据库会将teacher表中的每条数据与subject表中每条数据都连接一次并产生一条记录
  产生的结果集中的记录数为teacher表记录数与subject表记录数的乘机
  

• 查看班级的名称和对应的班主任(老师)是谁?

  1:明确数据都来自哪些表
    班级信息来自class表
    班主任信息(老师信息)来自teacher表
    明确FROM子句:FROM class c,teacher t
    
  2:当表明确了，就要确定这些表中数据的对应关系(连接条件)  
    班级表的teacher_id记录了老师表中老师的id
    明确WHERE中的连接条件:t.id=c.teacher_id
    
  SELECT c.name,t.name  
  FROM class c,teacher t
  WHERE c.teacher_id=t.id
  

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

  1:数据来自哪些表?
    student,class
  2:student表与class表的连接条件?
    s.class_id=c.id
  
  SELECT s.name,s.age,c.name,c.floor
  FROM student s,class c
  WHERE s.class_id=c.id
  

关联查询中使用过滤条件

连接条件必须与过滤条件同时满足

例:

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

  SELECT c.name,c.floor,t.name,t.salary
  FROM class c,teacher t
  WHERE c.teacher_id=t.id			连接条件
  AND t.name='王克晶'			  过滤条件
  
  

• 查看三年级的班级班主任都是谁?要列出班级名称，所在楼层，班主任名字和工资

  SELECT c.name,c.floor,t.name,t.salary
  FROM class c,teacher t
  WHERE c.teacher_id=t.id	
  AND c.name LIKE '3年级%'
  

• 查看来自南京的学生都有谁?要列出城市名字，学生名字，年龄，性别

  SELECT l.name,s.name,s.age,s.gender
  FROM student s,location l
  WHERE s.location_id=l.id
  AND l.name='南京'
  

• 查看5年级的中队长都有谁?要列出学生名字，年龄，性别，职位和所在班级的名字以及楼层

  SELECT s.name,s.age,s.gender,s.job,c.name,c.floor
  FROM student s,class c
  WHERE s.class_id=c.id
  AND c.name LIKE '5年级%'
  AND s.job='中队长'
  

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

N张表关联时，对应的N-1个连接条件要同时成立

例

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

  1：数据来自哪些表?
    teacher表，class表，student表
  
  2:连接条件
    student表与class表连接条件:s.class_id=c.id
    class表与teacher表连接条件:c.teacher_id=t.id
    
  3:过滤条件
    老师的名字:t.name='范传奇'   
  
  SELECT s.name,s.age,c.name,t.name
  FROM teacher t,class c,student s
  WHERE s.class_id=c.id				连接条件
  AND c.teacher_id=t.id				连接条件
  AND t.name='范传奇'
  
  

• 查看1年级1班的同学的名字和来自的城市

  1:数据来自哪些表?
    student s,location l,class c
  2:连接条件
    student与location的连接条件:s.location_id=l.id
    student与class表的连接条件:s.class_id=c.id
    
  3:过滤条件
    班级为1年级1班:c.name='1年级1班'
  
  SELECT s.name,l.name,c.name
  FROM student s,location l,class c
  WHERE s.location_id=l.id
  AND s.class_id=c.id
  AND c.name='1年级1班'
  
  
  

练习

1.查看来自北京的学生都是谁?
SELECT s.name,l.name
FROM location l,student s
WHERE l.id=s.location_id
AND l.name='北京'

2.教"英语"的老师都是谁?
SELECT t.name,s.name
FROM teacher t,subject s
WHERE t.subject_id=s.id
AND s.name='英语'

3.刘苍松所带班级的学生都有谁?
SELECT t.name,c.name,s.name
FROM teacher t,class c,student s
WHERE t.id=c.teacher_id
AND s.class_id=c.id
AND t.name='刘苍松'

4.教语文的老师所带的班级有哪些?
SELECT s.name,t.name,c.name
FROM subject s,teacher t,class c
WHERE s.id=t.subject_id
AND t.id=c.teacher_id
AND s.name='语文'

5.王克晶所带的班级学生都来自哪些城市(去重)?
SELECT DISTINCT l.name
FROM teacher t,class c,student s,location l
WHERE t.id=c.teacher_id
AND c.id=s.class_id
AND l.id=s.location_id
AND t.name='王克晶'

6.3年级的几个班主任都教哪些课程?
SELECT c.name,t.name,s.name
FROM class c,teacher t,subject s
WHERE c.teacher_id=t.id
AND t.subject_id=s.id
AND c.name LIKE '3年级%'

7.工资高于10000的老师所带班里的大队长都是谁?
SELECT t.name,c.name,s.name,s.job
FROM teacher t,class c,student s
WHERE t.id=c.teacher_id
AND c.id=s.class_id
AND t.salary>10000
AND s.job='大队长'

8."李费水"的班主任教哪门课?
SELECT st.name,c.name,t.name,su.name
FROM student st,class c,teacher t,subject su
WHERE st.class_id=c.id
AND c.teacher_id=t.id
AND t.subject_id=su.id
AND st.name='李费水'

9.所在4楼的班里的大队长和中队长以及班主任都是谁?
SELECT s.name,s.job,c.name,c.floor,t.name
FROM student s,class c,teacher t
WHERE s.class_id=c.id
AND c.teacher_id=t.id
AND c.floor=4
AND s.job IN('大队长','中队长')

如果用OR连接过滤条件，要注意OR的优先级低的问题。仍然要保证连接条件与过滤条件同时满足
SELECT s.name,s.job,c.name,c.floor,t.name
FROM student s,class c,teacher t
WHERE s.class_id=c.id
AND c.teacher_id=t.id
AND c.floor=4
AND (s.job='大队长' OR  s.job='中队长')

10.全校最小的同学的班主任是谁?
SELECT s.name,s.birth,c.name,t.name
FROM student s,class c,teacher t
WHERE s.class_id=c.id
AND c.teacher_id=t.id
AND s.birth=(SELECT MAX(birth) FROM student)

去掉重复值
SELECT DISTINCT t.name
FROM student s,class c,teacher t
WHERE s.class_id=c.id
AND c.teacher_id=t.id
AND s.birth=(SELECT MAX(birth) FROM student)

关联查询中使用聚合函数

例

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

  1:范传奇所带班级都有哪些学生(查询出参与统计的所有记录)
  1.1:数据来自哪些表？
      teacher t,class c,student s
  1.2:连接条件?
      teacher与class:t.id=c.teacher_id
      class与student:c.id=s.class_id
  1.3:过滤条件
      老师的名字:范传奇   t.name='范传奇'
      
  SELECT s.name,....    
  FROM teacher t,class c,student s
  WHERE t.id=c.teacher_id
  AND c.id=s.class_id
  AND t.name='范传奇'
  
  在上述SQL中添加聚合函数即可:
  SELECT COUNT(*)
  FROM teacher t,class c,student s
  WHERE t.id=c.teacher_id
  AND c.id=s.class_id
  AND t.name='范传奇'
  
  

• 查看教语文的老师平均工资是多少?

  SELECT AVG(t.salary)
  FROM teacher t,subject s
  WHERER t.subject_id=s.id
  AND s.name='语文'
  

• 查看教每门课老师的平均工资是多少(GROUP BY)?列出平均工资和科目名称

  1：准备数据(列出所有老师的工资，以及其所教的科目)
  SELECT t.salary,s.name
  FROM teacher t,subject s
  WHERE t.subject_id=s.id
  
  2：按照科目名称相同的记录分组，然后统计工资
  SELECT AVG(t.salary),s.name
  FROM teacher t,subject s
  WHERE t.subject_id=s.id
  GROUP BY s.name
  

• 仅查看平均工资高于6000的那些科目的老师平均工资是多少?

  SELECT AVG(t.salary),s.name
  FROM teacher t,subject s
  WHERE t.subject_id=s.id
  GROUP BY s.name
  HAVING AVG(t.salary)>6000
  
  在上述SQL基础上再查看平均工资排名?
  SELECT AVG(t.salary) avg,s.name
  FROM teacher t,subject s
  WHERE t.subject_id=s.id
  GROUP BY s.name
  HAVING AVG(t.salary)>6000
  ORDER BY avg DESC
  

• 查看工资最低的老师班里的学生共多少人?

  1:未知条件: 老师最低工资
  SELECT MIN(salary) FROM teacher
  
  2:查看最低工资老师班里的学生都有谁
  SELECT s.name
  FROM teacher t,class c,student s
  WHERE t.id=c.teacher_id
  AND c.id=s.class_id
  AND t.salary=(SELECT MIN(salary) FROM teacher)
  
  3:添加聚合函数统计
  SELECT COUNT(*)
  FROM teacher t,class c,student s
  WHERE t.id=c.teacher_id
  AND c.id=s.class_id
  AND t.salary=(SELECT MIN(salary) FROM teacher)
  
  

主键与外键

在多表关联查询中，连接条件通常就是两张表中某个字段值做等值判断

通常这两个字段就分别是两张表中的主键与外键

例:

学生表student与班级表class关联查询时

对于班级表(class表)而言，id字段就是该表的主键(Primary Key):主键字段的特点:非空且唯一

对于学生表(student表)，每个学生都要记录他所在的班级信息，对此我们在student表中设计了一个字段class_id,

而该字段保存的就是class表中的id字段的值。用于使每个学生都能与对应的班级记录关联在一起。那么在学生表中这个class_id(用于记录class表主键字段的值)字段就称为是class表对应的外键。

并且在关联关系中，包含外键字段的表永远多"多"的那一方。例如:class表与student表就是"一对多"

外键在数据库中也存在外键约束

外键约束的要求:

外键字段保存的值必须是其对应的主键字段中已有的值。

• 例如:student表与class表。 student表的class_id为外键，对应的是class表id这个主键
  • class表有3条记录，id分别是1,2,3。此时student表任何记录中class_id可选值仅为1,2,3,NULL。若class_id值为4则违反外键约束。

删除主键表中的某条记录时，必须保证该记录主键所对应的外键表中没有一条记录的外键保存该主键字段值。

• 例如:student表与class表。 student表的class_id为外键，对应的是class表id这个主键
  • class表有1条id为1的班级记录，同时student表中有三个学生记录的class_id为1.此时若删除class表中id为1的这条记录是不被允许的(原因是3个学生的外键记录着该该主键值)。

结论:表设计中通常不使用外键约束，我们都是在程序中以主外键作为关联查询的条件而已

多对多关系

多对多关系在表设计时，通常要添加一张关联关系表来维持两张表的多对多关系。

关联关系表通常不需要主键字段，并且一定有两个字段分别是多对多关系的两张表中各自主键所对应的外键字段。

例

学生表Student与科目表Subject就是多对多的。

从学生表角度出发:一个学生可以对应多个科目

从科目表角度出发:一个科目可以对应多个学生

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

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

实际查询就是一个三张表的关联查询

例：

• 查看'李费水'都学了哪门课程以及成绩?

  SELECT s.name,su.name,tss.score
  FROM student s,t_stu_subject_score tss,subject su
  WHERE s.id=tss.stu_id
  AND su.id=tss.subject_id
  AND s.name='李费水'
  

• 查看学语文的学生都有谁?

  SELECT s.name,su.name,tss.score
  FROM student s,t_stu_subject_score tss,subject su
  WHERE s.id=tss.stu_id
  AND su.id=tss.subject_id
  AND su.name='语文'
  

练习

1.查看1年级1班所有同学的语文成绩是多少?
SELECT s.name,c.name,su.name,tss.score
FROM student s,class c,t_stu_subject_score tss,subject su
WHERE s.class_id=c.id
AND s.id=tss.stu_id
AND su.id=tss.subject_id
AND c.name='1年级1班'
AND su.name='语文'


2.统计1年级1班数学成绩的平均值?
SELECT AVG(tss.score)
FROM student s,class c,t_stu_subject_score tss,subject su
WHERE s.class_id=c.id
AND s.id=tss.stu_id
AND su.id=tss.subject_id
AND c.name='1年级1班'
AND su.name='数学'

3.统计6年级的英语成绩的平均值?
SELECT AVG(tss.score)
FROM student s,class c,t_stu_subject_score tss,subject su
WHERE s.class_id=c.id
AND s.id=tss.stu_id
AND su.id=tss.subject_id
AND c.name LIKE '6年级%'
AND su.name='英语'

4.查看"刘苍松"所带班级的英语平均分?
SELECT AVG(tss.score)
FROM student s,class c,teacher t,t_stu_subject_score tss,subject su
WHERE s.class_id=c.id
AND c.teacher_id=t.id
AND s.id=tss.stu_id
AND su.id=tss.subject_id
AND t.name='刘苍松'
AND su.name='英语'


5.查看工资最高的老师所带班级的各科成绩的平均分，最高分和最低分分别是多少?
SELECT AVG(tss.score),MAX(tss.score),MIN(tss.score),su.name
FROM student s,class c,teacher t,t_stu_subject_score tss,subject su
WHERE s.class_id=c.id
AND c.teacher_id=t.id
AND s.id=tss.stu_id
AND su.id=tss.subject_id
AND t.salary=(SELECT MAX(salary) FROM teacher)
GROUP BY su.name


6.查看所有大队长的5门成绩平均分是多少？
SELECT AVG(tss.score),s.name
FROM student s,t_stu_subject_score tss,subject su
WHERE s.id=tss.stu_id
AND su.id=tss.subject_id
AND s.job='大队长'
GROUP BY s.name


7:查看刘苍松负责的科目最高成绩是谁?

内连接

语法:

SELECT 子句
FROM 表1
JOIN 表2 ON 连接条件(表2与表1的连接条件)
[JOIN 表3 ON 连接条件(表3与表2或表1的连接条件)]  
...

内连接与关联查询效果一致，特点:将连接条件单独定义在ON子句中，不在WHERE中与过滤条件混淆。结构看起来会更清晰。

例:

• 查看1年级1班所有同学的信息?

  关联查询:
  SELECT s.name,s.age,c.name
  FROM class c,student s
  WHERE c.id=s.class_id		连接条件
  AND c.name='1年级1班'		  过滤条件
  
  内连接:
  SELECT s.name,s.age,c.name
  FROM class c
  JOIN student s ON c.id=s.class_id
  WHERE c.name='1年级1班'
  
  
  
  

• 查看教英语的老师都有谁?

  SELECT t.name,s.name
  FROM teacher t
  JOIN subject s ON t.subject_id=s.id
  WHERE s.name='英语'
  

• 查看每个班级名以及对应的班主任名字?

  SELECT c.name,t.name
  FROM class c
  JOIN teacher t ON c.teacher_id=t.id
  

• 查看王克晶所带班级的女同学都有谁?(列出:老师名字，班级名字，学生名字，学生性别)

  SELECT t.name,c.name,s.name,s.gender
  FROM teacher t
  JOIN class ON t.id=c.teacher_id
  JOIN student s ON s.class_id=s.id
  WHERE t.name='王克晶'
  AND s.gender='女'
  

• 查看刘苍松所带班级的学生的英语成绩分别是多少，分数降序?

  SELECT s.name,c.name,t.name,su.name,sss.score
  FROM teacher t
  JOIN class c ON t.id=c.teacher_id
  JOIN student s ON c.id=s.student_id
  JOIN t_stu_subject_score sss ON s.id=sss.stu_id
  JOIN subject su ON su.id=sss.subject_id
  WHERE t.name='刘苍松'
  AND su.name='英语'
  ORDER BY sss.score DESC
  

• 查看全校来自每个城市的学生各多少人?

  SELECT COUNT(*),l.name
  FROM student s
  JOIN location l ON l.id=s.location_id
  GROUP BY l.name
  

外连接

外连接可以将关联查询中不满足连接条件的记录也查询出来

外连接分为:

• 左外连接:以JOIN左侧的表作为驱动表，该表中满足连接条件的记录会被显示出来，当该表中存在不满足连接条件的记录时，在结果集中该记录来自JOIN右侧表中的字段值全部为NULL
• 右外连接:与左外连接相反

例:

• 查看所有的班级，以及该班级对应的班主任，没有班主任的也要将班级列出来

  SELECT c.name,t.name
  FROM class c
  JOIN teacher t ON c.teacher_id=t.id
  班级表中有3个班级的teacher_id为99，但是teacher表中没有id为99的老师，因此这三条记录是不满足连接条件的，因此结果集中没有体现这三个班级
  
  左外连接，以class表为主，该表中不满足连接条件时，结果集中该记录中来此teacher表的字段为NULL
  SELECT c.name,t.name
  FROM class c LEFT JOIN teacher t ON c.teacher_id=t.id
  
  
  

• 查看所有的班级，以及该班级对应的班主任，如果该讲师不带班也要将该讲师列出来

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

• 查看所有的班级，以及该班级对应的班主任，不满足连接条件的班级和老师也都要体现

  在数据库中UNION关键字可以对结果集取并集(两个结果集的字段数，类型，顺序要完全一致)

  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,m.name
  FROM teacher t 
  JOIN teacher m ON t.manager=m.id
  WHERE m.name='刘苍松'
  

• 查看3年级2班的班长是谁?(student表中team_leader记录班长的学生id)

  SELECT s.id,s.name,s.team_leader,c.name
  FROM student s JOIN class c ON s.class_id=c.id
  WHERE c.name='3年级2班'
  AND s.id=s.team_leader
  

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

  SELECT s.name '学生',s.birth '生日',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)