1. 关系数据库基本概念和 MySQL 基本命令
show databases; —— 查看当前实例下包含多少个数据库
create datebase 数据库名; ——创建新的数据库
drop database 数据库名; —— 删除数据库
use 数据库名; —— 进入指定数据库
show tables; —— 查看数据表
desc 表名; —— 查看表结构
mysql -p 密码 -u 用户名 -h 主机名 –default-character-set-utf8
连接指定数据库并设置默认字符集为 utf8
2. SQL 语句基础
标准 SQL 语句通常分为如下几种类型:
- 查询语句:用于查询数据表中的数据,主要由 select 完成
- DDL(Data Definition Language,数据定义语言)语句:主要用于操作数据表,主要由 create 创建、alter 修改、drop 删除、truncate 重建四个关键字完成
- DML(Data Manipulation Language,数据操作语言)语句:主要用于操作数据表数据,主要由 insert 添加、update 更新、delete 删除三个关键字完成
- DCL(Data Control Language,数据控制语言)语句:主要由 grant 和 revoke 两个关键字完成
- 事务控制语句:主要由 commit、rollback 和 savepoint 三个关键字完成
SQL 语句的关键字不区分大小写
3. DDL 语句
DDL 语句用于操作数据库对象,最基本的数据库对象是 数据表,但数据库绝不仅包括数据表
1. 创建表的语法
创建空表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
create table [模式名.]表名 ( # 可以有多个列定义 column_name1 dataType [default expr] ... ); # 示例 create table test ( # 整数型 test_id int, # 小数型 test_price decimal, # 普通长度文本,使用 default 指定默认值 test_name varchar(255) default 'xxx', # 大文本类型 test_desc text, # 图片类型 test_img blob, # 日期类型 test_date datetime ); |
子查询建表语句
1 2 3 4 5 6 7 8 |
create teble [模式名.]表名 [column[, column...]] as subquery; # 示例 # 创建 awsl 数据表,该数据表和 user_inf 完全相同,数据也完全相同 create table awsl as select * from user_inf; |
2. 修改表结构
修改表结构包括 增加列定义、修改列定义、删除列、重命名列等操作
增加列定义
1 2 3 4 5 6 7 |
alter table 表名 add ( # 可以有多个列定义 column_name1 datatype [default expr], ... ); |
如果新增的列只有一行,可以省略圆括号
如果数据表中已有数据记录(即不是刚创建的数据表),除非给新增的列指定了默认值,否则不可指定非空约束,因为该列数据必定为空
修改列定义
1 2 3 4 5 6 7 8 9 10 |
alter table 表名 modify column_name datatype [default expr] [first|after col_name]; # 示例 # 将 test 表的 name 列改为 int 类型 alter table test modify name int; # 将 test 表的 id 列改为 varchar(255) 类型 alter table test modify id varchar(255); |
修改列定义会清除原有的定义,相当于重定义列
MySQL 数据库的 modify 命令不支持一次修改多个列定义
删除列
1 2 3 4 5 6 7 |
alter table 表名 drop column_name; # 示例 # 删除 test 表中 id 列 alter table test drop id; |
重命名数据表
1 2 3 4 5 6 7 |
alter table 表名 rename to 新表名; # 示例 # 将 test 表重命名为 haha alter table test rename to haha; |
完全改变列定义
change 关键字比 modify 多一个列名
1 2 3 4 5 6 7 |
alter table 表名 change 旧列名 新列名 type [default expr] [first|after col_name]; # 示例 # 将 test 表的 id 列重命名成 uuid alter table test change id uuid int; |
3. 删除表
1 |
drop table 表名; |
删除表的效果如下
- 表结构被删除,表对象也不再存在
- 表里的所有数据也被删除
- 该表所有相关的索引、约束也被删除
4. truncate 表
truncate 被称为“截断”某个表——它的作用是删除该表里的全部数据,但保留表结构
1 |
truncate 表名 |
4. 数据库约束
约束是在表上强制执行的数据校验规则,主要用于保证数据库里数据的完整性。除此之外,当表中数据存在相互依赖性时,可以保护相关的数据不被删除
大部分数据库支持下面 5 种完整性约束
- NOT NULL —— 非空约束,指定某列不能为空
- UNIQUE —— 唯一约束,指定某列或者几列组合不能重复
- PRIMARY KEY —— 主键,指定该列的值可以唯一地标志该条记录
- FOREINGN KEY —— 外键,指定该行记录从属于主表的一条记录,主要用于保证参照完整性
- CHECK —— 检查,指定一个布尔表达式,用于指定对应列的值必须满足该表达式
MySQL 可以指定 CHECK 约束,但没有任何效果
约束也是数据库对象,被存储在系统表中,也有自己的名字
根据约束对列的限制,约束分为如下两类
- 单列约束:每个约束值约束一列
- 多列约束:每个约束可以约束多个数据列
为数据表指定约束有如下两个时机
- 建表的同时为相应的数据列指定约束
- 建表后创建,以修改表的方式增加约束
大部分约束都可以采用列级约束语法或表级约束语法
约束通常只能建立和删除,无法修改
提示:MySQL 使用 information_schema 数据库里的 TABLE_CONSTRAINTS 表来保存该数据库实例中所有的约束信息
1. NOT NULL 非空约束
SQL 中的 null 不区分大小写,具有如下特征
- 所有数据类型的值都可以是 null,包括 int、float、boolean 等数据类型
- 空字符串不等于 null,0 也不等于 null
- null 不等于 null
非空约束用于确保指定列不允许为空,作为列级约束使用,只能使用列级约束语法
在建表时使用 NOT NULL 约束
1 2 3 4 5 6 7 8 |
create table test ( # 建立非空约束 test_id int NOT NULL, test_name varchar(255) default 'xyz' not null, # 显式指定可空,默认就是可空 test_gender varchar(2) null ); |
在修改表时增加或删除非空约束
1 2 3 4 5 6 7 8 9 |
# 增加非空约束 alter table test modify test_gender varchar(2) not null; # 取消非空约束 alter table test modify test_name varchar(255) null; # 取消非空约束并指定默认值 alter table test modify test_name vatchar(255) default 'abc' null; |
2. UNIQUE 唯一约束
唯一约束用于保证表内所有指定列或指定列组合不允许出现重复值
当建立唯一约束时,MySQL 在唯一约束所在列或列组合上建立对应的唯一索引
列级约束语法
使用列级约束只需要在类定义后增加 unique 关键字即可,约束名即为列名
1 2 3 4 5 6 7 |
# 建表时创建唯一约束,使用列级约束语法建立约束 create table unique_test ( test_id int not null, # 建立唯一约束 test_name varchar(255) unique ); |
表级约束语法
如果需要为多列组合建立唯一约束,或想自行制定约束名,可以使用表级约束语法
1 2 3 4 5 6 7 8 9 10 11 12 13 |
[constraint 约束名] unique(列名) # 示例 create table unique_test_a ( test_id int not null, test_name varchar(255), test_pass varchar(255), # 使用表级约束语法建立唯一约束 unique(test_name), # 使用表级约束语法建立唯一约束并指定约束名 constraint test_a_uk unique(test_pass) ); |
建立多列组合约束语法如下
1 2 3 4 5 6 7 8 |
create table unique_test_b ( test_id int not null, test_name varchar(255), test_pass varchar(255), # 使用表级约束语法建立唯一约束,指定两列组合不能重复 constraint test_b_uk unique(test_name,test_pass) ); |
对于 test_a 表,要求 test_name 和 test_pass 都不能出现重复值 (a.test_name != b.test_name) (a.test_pass != b.test_pass)
对于 test_b 表,要求 test_name 和 test_pass 的组合不能出现重复值 (a.test_name != b.test_name && a.test_pass != b.test_pass)
在修改表结构时增加唯一约束的方式如下
1 2 3 4 5 6 7 8 9 10 |
alter table 表名 add unique(列1,列2,...); # 示例 alter table unique_test add ( unique(test_id), constraint test_uk unique(test_name, test_pass) ); |
删除唯一约束
大部分数据库使用 “drop constraint 约束名” 来删除约束,但 MySQL 使用 “drop index 约束名” 来删除约束
1 2 |
alter table unique_test drop index test_uk; |
3. PRIMARY KEY 主键约束
主键约束相当于非空约束和唯一约束,即主键约束列既不允许出现重复值,也不允许出现 null
每个表中最多允许有一个主键,但这个可以建立多列组合主键
MySQL 允许在建立主键约束时为约束命名,但该名字没有任何作用,默认都为 PRIMARY
列级约束语法
1 2 3 4 5 6 |
create table primary_test ( # 建立主键约束 test_id int primary key, test_name varchar(255) ); |
表级约束语法
1 2 3 4 5 6 7 8 |
create table primary_test ( test_id int not null, test_name varchar(255), # 指定 test_id 为主键,命名为 test_pk,但命名对 MySQL 无效 # MySQL 中该主键的约束名依然是 PRIMARY constraint test_pk primary key(test_id) ); |
多列组合约束
1 2 3 4 5 6 |
create table primary_test ( test_account varchar(255), test_pass varchar(255), primary key(test_name, test_pass) ); |
删除主键约束
1 2 |
alter table 表名 drop primary key; |
增加主键约束
1 2 3 4 5 6 7 |
# 使用表级约束语法 add 增加主键约束 alter table primary_test add primary key(test_name,test_pass); # 使用列级约束语法 modify 增加主键约束 alter table primary_test modify test_name varchar(255) primary key; |
自增长特性
自增长列的值会随着记录数量自动设置,通常用于设置逻辑主键列——该列的值没有任何物理意义,仅用于标识每行记录
1 2 3 4 5 6 7 |
create table primary_test ( # 建立主键约束,使用自增长 test_id int auto_increment primary key, test_name varchar(255), test_pass varchar(255) ); |
一旦指定了某列具有自增长特性,则向该表插入记录时可不为该列指定值(指定 null 代表跳过该值),该列的值由数据库系统自动生成
4. FOREIGN KEY 外键约束
外键约束用于在两个数据表之间建立关联。
从表中外键列的值必须为空或是主表中被参照列的值,被参照列必须是主表中的主键或其唯一键列
同一个表可以拥有多个外键
列级约束语法(无效)
列级约束语法使用 references 关键字
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
# 定义 column_name 列并指定其参照到 table_name 表的 column_name 列 column_name type references table_name(column_name) # 示例 # 为保证从表参照的主表存在,通常应该先建主表 create table teacher_table ( # 定义为主键,可被参照 teacher_id int auto_increment primary key, teacher_name varchar(255) ); create table student_table ( # 为本表建立主键约束 student_id int auto_increment primary key, student_name varchar(255), # 指定 java_teacher 参照到 teacher_table 的 teacher_id 列 java_teacher int references teacher_table(teacher_id) ); |
虽然 MySQL 支持使用列级约束语法建立外键约束,但不会生效,需要使用表级约束语法
表级约束语法
如果在定义外键约束时没有指定约束名,则默认为 table_name_ibfk_n,n 是从 1 开始的整数
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
# [定义外键约束名称] 指定 column_name 列参照到 table_name 表中的 column_name 列 [constraint key_name] foreign key(column_name) references table_name(column_name) create table teacher_table ( teacher_id int auto_increment primary key, teacher_name varchar(255) ); create table student_table ( student_id int auto_increment primary key, student_name varchar(255), # 定义 java_teacher 列 java_teacher int, # 指定 java_teacher 参照到 teacher_table 的 teacher_id 列 foreign key(java_teacher) references teacher_table(teacher_id) ); |
组合外键约束
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
# [定义外键约束名称] 指定 column_name 列参照到 table_name 表中的 column_name 列 [constraint key_name] foreign key(column_name1, column_name2, ...) references table_name(column_name1, column_name2, ...) create table teacher_table ( teacher_name varchar(255), teacher_pass varchar(255), # 定义组合主键约束 primary key(teacher_name,teacher_pass) ); create table student_table ( student_id int auto_increment primary key, student_name varchar(255), java_teacher_name varchar(255), java_teacher_pass varchar(255), # 分别指定 java_teacher_name 和 java_teacher_pass 列参照到 teacher_table 表的 teacher_name 和 teacher_pass foreign key(java_teacher_name, java_teacher_pass) references teacher_table(teacher_name, teacher_pass) ); |
删除外键约束
1 2 3 4 5 6 |
alter table 表名 drop foreign key 约束名; # 示例 alter table student_table drop foreign key student_table_ibfk_1; |
增加外键约束
1 2 3 |
alter table 表名 add [constrains 约束名] foreign key(col1, col2, ...) references 主表名(col1, col2, ...); |
如果希望在删除主表记录时,从表记录也随之删除,可以在建立外键约束后添加
- on delete cascade —— 删除主表记录时,把参照该主表记录的从表记录全部级联删除
- on delete set null —— 删除主表记录时,把参照该主表记录的从表记录的外键设为 null
5. CHECK 约束
MySQL 支持建表时指定 CHECK 约束,但不会生效,只需要在列定义后增加 check 逻辑表达式即可
1 2 3 4 5 6 7 |
create table check_test ( emp_id int auto_increment primary key, emp_name varchar(255), emp_salary decimal, check(emp_salary>0) ); |
5. 索引
索引是存放在模式(schema)中的一个数据库对象,用于加速对表的查询
创建索引有两种方式:
- 自动:当在表上定义主键约束、唯一约束和外键约束时,系统会为该数据列自动创建对应的索引
- 手动:通过 create index 关键字创建索引
删除索引有两种方式:
- 自动:数据表被删除时,该表上的所有索引都将被删除
- 手动:通过 drop index 语句删除指定数据表上的指定数据
手动创建索引
1 2 |
create index index_name on table_name (column[, column]...); |
手动删除索引
1 |
drop index 索引名 on 表名 |
有些数据库删除索引时无需指定表名,因为它们要求建立索引时每个索引都有唯一的名字,如 Oracle 数据库
索引也有如下两个坏处
- 当数据库中的记录被添加、删除、修改时,数据库系统需要维护索引,因此回有一定的系统开销
- 存储索引信息需要一定的磁盘空间
6. 视图
视图是一个或多个数据表中数据的逻辑显示,相当于创建一个引用原数据表部分数据的新数据表
创建视图
创建视图需要跟随一个查询语句作为视图内容
1 2 3 4 5 6 7 8 9 10 |
create or replace view 视图名 as subquery # 示例 // 创建 user_info 视图 create or replace view user_info as // 选择 user 表的 name, age, desc 列 select name, age, desc from user; |
视图的使用与数据表没有什么区别,但通常只是查询视图数据,不会修改视图数据
如果希望强制使视图只读,可以在创建视图时使用 with check option 子句
大部分数据库都是用 with check option 子句,Oracle 数据库采用 with read only 子句
删除视图
1 2 3 4 |
drop view 视图名 # 示例 drop view view_test; |
7. DML 语句
DML 主要操作数据表里的数据,可以完成如下三个任务
- 插入新数据
- 修改已有数据
- 删除不需要的数据
1. 添加记录
insert into 语句用于向数据表中插入数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
insert into table_name[(col1, col2, ...)] values(val1, val2, ...); # 示例 # 列出列名并插入数据 insert into teacher(name, age, gender) values('Yonghao Luo', 48, 'Male'); # 不列出列名,按顺序插入数据 insert into teacher // 使用 null 忽略自增长主键 values(null, 'Yonghao Luo', 'Male', 48); # 同时插入多个数据 values('aaa', 111), ('bbb', 222), ...; |
insert into 语句允许使用查询语句插入数据,例如
1 2 |
insert into test(col) select col from source; |
2. 修改记录
update 语句用于修改数据表的记录,可以同时修改多条记录,通过使用 where 子句 可以限定修改哪些记录,没有 where 子句代表 where 表达式的值总为 true,这意味着会修改所有记录
1 2 3 4 5 6 7 8 9 10 11 12 |
update table_name set column1 = value1, column2 = value2, ... [where 语句]; # 示例 update test_table set name = '罗玉龙' where name = '罗永浩'; update test_table set name = '老头子' where age > 200; |
3. 删除记录
delete from 语句用于删除指定数据表的整行记录,使用 where 子句 来限定需要删除的行
没有 where 子句代表删除该表的所有记录
1 2 3 4 5 6 7 8 9 |
delete from table_name [WHERE condition]; # 示例 # 删除 test_table 表的所有记录 delete from test_table; # 删除 test_table 表中 age > 200 的所有记录 delete from test_table where age > 200; |
8. 单表查询
select 语句用于查询数据,可以从一个或多个数据表中选出特定行、特定列的交集
select 语句语法
1 2 3 |
select column1, column2 from 数据源 [where condition] |
该语句的含义是:将数据源中的 column1 和 column2 按顺序选择出来
columnN 可以被当作指向该行该列数据的变量
数据源可以是表或视图,select 后的列表用于确定选择哪些列,where 语句用于确定选择哪些行,只有满足 where 条件的记录才会被选择出来,没有 where 语句则选择所有行
如果想选择所有列,可以使用 * 代表所有列
算术表达式
由于 columnN 可以被当作变量处理,因此 select 语句中可以使用算术表达式以改变其输出的数据(不改变其在表中的值)
支持以下算术运算符:(+,-,*,/)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
# 将 users 表中的 user_id 列以该列 + 5 后的结果选择出来 select user_id + 5 from users; 输出 --> +-------------+ | user_id + 5 | +-------------+ | 6 | +-------------+ # where 语句中也可以使用算术表达式 select user_id + 5 from users where user_id * 3 > 4; # 使用 concat 函数连接字符串 select concat(user_name, ' nmsl') from users where user_name = 'cxk' |
注意:如果在算数运算符中使用 null,计算结果也会变成 null!
别名
如果不希望直接使用列名作为列标题,可以为数据列或表达式起别名
只需要在数据列或表达式后紧跟一个别名,用 空格 或 “as” 关键字隔开即可
1 2 3 |
# 使用别名 select user_name 'New Name' from users; select user_id + 5 as 'New User ID' from users; |
select 语句中的常量
如果 select 语句只有一个常量表达式,例如:2 + 3,并且此时 where 语句总为 true,那么,该表中有多少行数据,MySQL 就将输出多少次该常量
对于这种选择常量的情况,指定表没有任何意义
因此 MySQL 提供了一种扩展语法,允许 select 语句后没有 from 子句,例如
1 |
select 5 + 1; |
但该语句并不是标准 SQL 语句,为了使该语句符合 SQL 规范,可以使用 dual 作为一个占位符,代表一个虚表
1 |
select 5 + 1 from dual; |
去除重复行
在 select 关键字后紧跟 distinct 关键字以去除重复行
1 2 |
# 选出 student_table 中所有 age 列并去重 select distinct age from student_table; |
运算符
比较运算符
大于:>
小于:<
大于等于:>=
小于等于:<=
相等:=
不相等:<>
逻辑运算符
非:not
与:and
或:or
赋值运算符
与编程语言不同,SQL 中赋值运算符是 ” := ”
特殊运算符
除此之外,SQL 还支持下列特殊运算符
- A between B and C —— 要求 A∈[B, C],即 B <= A <= C
- A in(B, C, D, …) —— 要求 A 等于括号中的任意一个表达式的值
- like —— 字符串匹配
- is null —— 要求指定值等于 null
上列中的字母都可以是表达式
like 关键字支持两个通配符:
- _ —— 代表任意一个字符
- % —— 代表任意多个字符
在 MySQL 中如果需要查询通配符应使用 “\” 进行转义
1 2 3 |
# 查询所有名字以下划线开头的学生 select * from student_table where student_name like '\_%'; |
但标准 SQL 语句应使用 escape 关键字显式进行转义
1 2 3 |
# 查询所有名字以下划线开头的学生 select * from student_table where student_name like '\_%' escape '\'; |
运算符优先级
运算符 | 优先级(小的优先) |
---|---|
所有比较运算符 | 1 |
not | 2 |
and | 3 |
or | 4 |
与 java 相似,使用括号可以强制改变优先级顺序
1 2 |
select * from student_table where (student_id > 3 or student_name like '张%') and java_teacher > 1; |
排序
查询的结果默认按插入顺序排序,使用 order by 子句可以使查询结果按某列值大小进行排序
1 2 |
select ... order by column1 [desc/asc], column2, ...; |
desc 代表升序排列,asc 代表降序排列,默认为升序
如果指定多个排序列,则每个排序列要单独设定 desc/asc,第一个排序列是首要排序列,只有当该列存在相同值时,后续排序列才会起作用
9. 数据库函数
函数用于进行数据处理或复杂计算,通过一个或多个输入计算出一个返回值
函数分为单行函数和多行函数
单行函数对每行输入值单独计算,每行得到一个计算结果返回给用户;
多行函数对多行输入值整体计算,最后只会得到一个结果
执行函数的语法如下
1 |
function_name(arg1, arg2, ...) |
MySQL 单行函数
以下函数可能在其他数据库中不可用
char_length(str) : 计算 str 的字符串长度
sin(number) : 计算 number 的 sin 值
date_add(date, inertval 2 MONTH) : 向 date 添加指定长度的时间, interval 是关键字, 2 是数值, MONTH 是单位
adddate(date, days) : 向 date 添加指定天数
curdate() : 当前日期
curtime() : 当前时间
md5(str) : 获取 str 的 md5
MySQL提供了 以下处理 null 的函数
ifnull(p1, p1) : 如果 p1 为 null, 则返回 p2, 否则返回 p1
nullif(p1, p2) : 如果 p1 和 p2 相等, 则返回 null, 否则返回 p1
if(p1, p2, p3) : 如果 p1 为 true, 不等于 0, 且不等于 null, 则返回 p2, 否则返回 p3
isnull(p1) : 判断 p1 是否为 null
MySQL 流程控制函数
用法 1
case 函数使用 value 和后面的 compare_value1 compare_value2 …进行比较
如果 value 和指定的 compare_value 相等, 则返回对应的 result, 否则返回 else 后的 result
使用 end 结束 case 函数
1 2 3 4 5 6 |
select student_name, case java_teacher when 1 then 'Java 老师' when 2 then 'Ruby 老师' else '其他老师' end from student_table |
用法 2
不指定 value, 将 comopare_value 换成 condition
例如
1 2 3 4 5 6 |
select student_name, case when student_id <= 3 then '初级' when student_id <= 6 then '中级' else '高级' end from student_table |
condition1, condition2 都是一个 Boolean 表达式
10. 分组和组函数
组函数 即 多行函数, 组函数将一组记录作为整体计算, 返回一个结果
常用组函数
avg([distinct|all]expr) : 计算多行 expr 的平均值, expr 可以是变量, 常量或数据列, 但其数据类型必须是数值型, distinct 关键字表明不计算重复值, all 表示计算所有值
count({*|[distinct|all]expr}) 计算多行 expr 的总条数, * 表示统计该表内的所有记录行数
max(expr) 计算多行 expr 的最大值, 数据可以是任何类型
min(expr) 计算多行 expr 的最小值, 数据可以是任何类型
分组
默认情况下, 组函数会把所有记录当成一组, 为了对数据进行显示分组可以在 select 语句后使用 group by 子句
当对 单列数据 进行分组时, 会将该列所有值相同的数据分为一组
例如: 对于 1, 1, 3, 5, 1 会将 [1, 1, 1] 分为一组; [3] 分为一组; [5] 分为一组
当对 多列数据 进行分组时, 要求多列的值完全相同才会被当成一组
例如: 对于 [1, 1] [1, 2] [1, 2] [2, 2] [2, 2] 会将 {[1,1]} 分为一组; {[1, 2], [1, 2]} 分为一组; {[2, 2], [2, 2]} 分为一组
对分组进行过滤
使用 having 子句对分组进行过滤
例如 having count(*) > 2 用于过滤数据行大于 2 条的分组
与 where 不同的是, where 子句仅用于过滤行, having 用于过滤多个分组
11. 多表连接查询
多表连接查询有两种规范
SQL 92:
等值连接
非等值连接
外连接
广义笛卡儿积
SQL 99:
交叉连接
自然连接
使用using子句的连接
使用on子句的连接
全外连接或者左、右外连接
SQL 92
SQL 92 语法直接将表名放在 from 子句内即可
1 2 3 |
select column1, column2 ... from table1, table2 ... [where join_condition] |
如果不指定 where 条件, 则选出广义笛卡儿积, 表的每一行都与其他表所有行进行连接
例如:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
# student_table +--------------+ | student_name | +--------------+ | 李小二 | | 朱小瑞 | | 陈小远 | | 王小帅 | +--------------+ # teacher_table +--------------+ | teacher_name | +--------------+ | 张三 | | 李四 | | 王五 | +--------------+ |
上面两个表, 使用 select * from student_table, teacher_table 选择出的广义笛卡儿积结果为
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
+--------------+--------------+ | student_name | teacher_name | +--------------+--------------+ | 李小二 | 张三 | | 李小二 | 李四 | | 李小二 | 王五 | | 朱小瑞 | 张三 | | 朱小瑞 | 李四 | | 朱小瑞 | 王五 | | 陈小远 | 张三 | | 陈小远 | 李四 | | 陈小远 | 王五 | | 王小帅 | 张三 | | 王小帅 | 李四 | | 王小帅 | 王五 | +--------------+--------------+ |
使用 where 子句可以指定连接条件, 结果相当于对广义笛卡儿积进行过滤, 还可以使用 and 添加其他过滤条件
例如 where student_teacher_id = teacher_id
外连接
外连接就是在连接条件的列名后添加外连接符(视数据库类型而定), 代表向该表添加一行通配记录, 该行记录的所有数据都是 null, 且可以与另一个表中所有不满足条件的记录进行匹配
MySQL 不支持外连接
SQL 语句如下
1 2 3 4 |
select * from student_table s, teacher_table t # 右外连接 where s.student_teacher_id = t.teacher_id(*) |
自连接
如果同一个表中的不同记录之间存在主、外键关联, 即自关联, 例如把员工、经理保存在同一个表里, 则需要使用自连接查询
自连接只是连接的一种用法, 本质上就是把一个表当成两个表来使用, 语法如下
1 2 3 4 5 6 7 8 9 10 11 12 13 |
# 添加一个自关联的数据表 create table emp_table ( emp_id int auto_increment primary key, emp_name varchar(255), manager_id int, foreign key(manager_id) references emp_table(emp_id) ); insert into emp_table values (null, '罗永浩', null), (null, '库克', 1), (null, '林斌', 1); |
如果需要查询该数据表的所有员工名, 以及每个员工对应的经理名, 则必须使用自连接查询, 语法如下
1 2 3 |
select emp.emp_id, emp.emp_name as 员工名, mgr.emp_name as 经理名 from emp_table emp, emp_table mgr where emp.manager_id = mgr.emp_id; |
SQL 99
SQL 99 显式使用 xxx join 进行多表连接, 使用专门的连接子句指定连接条件
交叉连接 (cross join)
交叉连接就是 SQL 92 中的广义笛卡儿积
1 2 3 |
select * from table1 as t1 cross join table2 as t2; |
自然连接 (natural join)
自然连接会以两个表中同名列作为等值连接条件, 如果两个表中没有同名列则与交叉连接效果完全相同
using 子句连接
使用 using 子句可以显示指定使用哪些同名列作为等值连接条件, 此时可以只使用 join 作为连接关键字, SQL 语句如下
1 2 3 4 |
select s.student_name t.teacher_name from student_table s join teacher_table t using(teacher_id); |
on 子句连接
on 子句用于手动指定连接条件, SQL 语句如下
1 2 3 4 |
select s.student_name, t.teacher_name from student_table s join teacher_table t on s.student_teacher_id > t.teacher_id; |
外连接
使用 left join 进行左外连接
使用 right join 进行右外连接
与 SQL 92 相反, SQL 99 的左外连接会向连接表添加万能行, 选择原表的所有数据
右外连接会向原表添加万能行, 选择连接表的所有数据
12. 子查询
子查询是指在查询语句中嵌套另一个查询语句, 子查询支持多层嵌套
对于普通的查询语句而言, 子查询可以出现在两个位置
1. 出现在 from 语句后当作数据表, 该用法实质上是一个临时视图
2. 出现在 where 条件后作为过滤条件的值
使用子查询需要注意以下几点
- 子查询要用括号括起来
- 把子查询当作数据表时, 可以为子查询起别名, 尤其是作为前缀来限定数据列时, 必须给子查询起别名
- 把子查询当作过滤条件时, 将子查询放在比较运算符的右边可以增强查询的可读性
- 把子查询当作过滤条件时, 单行子查询使用单行运算符, 多行子查询使用多行运算符
如果子查询返回单行单列值, 则可以被当成一个标量使用, 即可以使用单行记录比较运算符
1 2 3 |
select * from (select * from student_table) t where t.teacher_id > 1; |
如果子查询返回多个值, 则需要使用 in, any, all 等关键字
1 2 3 4 |
select * from student table where student_id in (select teacher_id from teacher_table); |
上面语句的子查询将返回单列多行值, 被当作一个值列表, 只要 student_id 与该值列表中的任意一个值相等, 就可以选出这条记录
any 和 all 可以与运算符结合使用
例如 = any(columns) 代表等于该列的任意一个值
= all(columns) 代表等于该列的所有值
<any(col) 代表小于该列的任意一个值, 相当于小于该列的最大值
>any(col) 代表大于该列的任意一个值, 相当于大于该列的最小值
1 2 3 4 5 |
select * from student_table where student_age < any( select teacher_age from teacher_table ); |
13. 集合运算
select 语句返回的结果是一个包含多条数据的数据集, 类似于数学中的集合, 因此可以进行 并(union), 差(minus), 交(intersect) 运算
为了对两个结果集进行集合运算, 这两个结果集必须满足如下条件
- 两个结果集所包含的数据列数量必须相等
- 两个结果集所包含的数据列数据类型必须一一对应
1. union 并运算
union 用于合并两个结果集, 语法格式如下
1 |
select ... union select ... |
2. minus 差运算
minus 用于从结果集中删去另一个结果集的数据, 数据必须一一对应
1 |
select ... minus select ... |
假设想从所有学生记录中删去与老师记录 ID 相同, 姓名相同的记录, 可以进行如下的 minus 运算
1 2 3 |
select student_id, student_name from student_table minus select teacher_id, teacher_name from teacher_table |
MySQL 不支持 minus 运算, 可以通过指定过滤条件手动实现
1 2 3 4 5 |
select student_id, student_name from student_table where (student_id, student_name) not in (select teacher_id, teacher_name from teacher_table); |
3. intersect 交运算
intersect 运算用于找出两个结果集中相同的数据
1 |
select ... intersect select ... |
假设想找出学生记录中与老师记录 ID 相同, 姓名相同的记录, 可以进行如下的 intersect 运算
1 2 3 |
select student_id, student_name from student_table where student_id < 4 intersect select teacher_id, teacher_name from teacher_table where teacher_name like '李%'; |
MySQL 不支持 intersect 运算, 可以通过多表连接查询实现上述运算
需要指出的是, 如果进行 intersect 运算的两个 select 子句都使用了 where 子句进行过滤, 那么将 intersect 运算改写成多表连接查询后还需要将两个 where 条件放在后面
1 2 3 4 |
select student_id, student_name from student_table join teacher_table on(student_id = teacher_id and student_name = teacher_name) where student_id < 4 and teacher_name like '李%'; |
发表评论