阿特我自己
[email protected]
Hello WvT
13.2 SQL 语法
13.2 SQL 语法

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,数据控制语言)语句:主要由 grantrevoke 两个关键字完成
  • 事务控制语句:主要由 commitrollbacksavepoint 三个关键字完成

SQL 语句的关键字不区分大小写

3. DDL 语句

DDL 语句用于操作数据库对象,最基本的数据库对象是 数据表,但数据库绝不仅包括数据表

1. 创建表的语法

创建空表

子查询建表语句

2. 修改表结构

修改表结构包括 增加列定义、修改列定义、删除列、重命名列等操作

增加列定义

如果新增的列只有一行,可以省略圆括号
如果数据表中已有数据记录(即不是刚创建的数据表),除非给新增的列指定了默认值,否则不可指定非空约束,因为该列数据必定为空

修改列定义

修改列定义会清除原有的定义,相当于重定义列

MySQL 数据库的 modify 命令不支持一次修改多个列定义

删除列

重命名数据表

完全改变列定义

change 关键字比 modify 多一个列名

3. 删除表

删除表的效果如下

  • 表结构被删除,表对象也不再存在
  • 表里的所有数据也被删除
  • 该表所有相关的索引、约束也被删除

4. truncate 表

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 约束

在修改表时增加或删除非空约束

2. UNIQUE 唯一约束

唯一约束用于保证表内所有指定列或指定列组合不允许出现重复值

当建立唯一约束时,MySQL 在唯一约束所在列或列组合上建立对应的唯一索引

列级约束语法

使用列级约束只需要在类定义后增加 unique 关键字即可,约束名即为列名

表级约束语法

如果需要为多列组合建立唯一约束,或想自行制定约束名,可以使用表级约束语法

建立多列组合约束语法如下

对于 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)

在修改表结构时增加唯一约束的方式如下

删除唯一约束

大部分数据库使用 “drop constraint 约束名” 来删除约束,但 MySQL 使用 “drop index 约束名” 来删除约束

3. PRIMARY KEY 主键约束

主键约束相当于非空约束和唯一约束,即主键约束列既不允许出现重复值,也不允许出现 null

每个表中最多允许有一个主键,但这个可以建立多列组合主键

MySQL 允许在建立主键约束时为约束命名,但该名字没有任何作用,默认都为 PRIMARY

列级约束语法

表级约束语法

多列组合约束

删除主键约束

增加主键约束

自增长特性

自增长列的值会随着记录数量自动设置,通常用于设置逻辑主键列——该列的值没有任何物理意义,仅用于标识每行记录

一旦指定了某列具有自增长特性,则向该表插入记录时可不为该列指定值(指定 null 代表跳过该值),该列的值由数据库系统自动生成

4. FOREIGN KEY 外键约束

外键约束用于在两个数据表之间建立关联。

从表中外键列的值必须为或是主表中被参照列的值,被参照列必须是主表中的主键或其唯一键列
同一个表可以拥有多个外键

列级约束语法(无效)

列级约束语法使用 references 关键字

虽然 MySQL 支持使用列级约束语法建立外键约束,但不会生效,需要使用表级约束语法

表级约束语法

如果在定义外键约束时没有指定约束名,则默认为 table_name_ibfk_n,n 是从 1 开始的整数

组合外键约束

删除外键约束

增加外键约束

 

如果希望在删除主表记录时,从表记录也随之删除,可以在建立外键约束后添加

  • on delete cascade —— 删除主表记录时,把参照该主表记录的从表记录全部级联删除
  • on delete set null —— 删除主表记录时,把参照该主表记录的从表记录的外键设为 null

5. CHECK 约束

MySQL 支持建表时指定 CHECK 约束,但不会生效,只需要在列定义后增加 check 逻辑表达式即可

5. 索引

索引是存放在模式(schema)中的一个数据库对象,用于加速对表的查询

创建索引有两种方式:

  • 自动:当在表上定义主键约束、唯一约束和外键约束时,系统会为该数据列自动创建对应的索引
  • 手动:通过 create index 关键字创建索引

删除索引有两种方式:

  • 自动:数据表被删除时,该表上的所有索引都将被删除
  • 手动:通过 drop index 语句删除指定数据表上的指定数据

手动创建索引

手动删除索引

有些数据库删除索引时无需指定表名,因为它们要求建立索引时每个索引都有唯一的名字,如 Oracle 数据库

索引也有如下两个坏处

  • 当数据库中的记录被添加、删除、修改时,数据库系统需要维护索引,因此回有一定的系统开销
  • 存储索引信息需要一定的磁盘空间

6. 视图

视图是一个或多个数据表中数据的逻辑显示,相当于创建一个引用原数据表部分数据的新数据表

创建视图

创建视图需要跟随一个查询语句作为视图内容

视图的使用与数据表没有什么区别,但通常只是查询视图数据,不会修改视图数据
如果希望强制使视图只读,可以在创建视图时使用 with check option 子句

大部分数据库都是用 with check option 子句,Oracle 数据库采用 with read only 子句

删除视图

7. DML 语句

DML 主要操作数据表里的数据,可以完成如下三个任务

  • 插入新数据
  • 修改已有数据
  • 删除不需要的数据

1. 添加记录

insert into 语句用于向数据表中插入数据

insert into 语句允许使用查询语句插入数据,例如

2. 修改记录

update 语句用于修改数据表的记录,可以同时修改多条记录,通过使用 where 子句 可以限定修改哪些记录,没有 where 子句代表 where 表达式的值总为 true,这意味着会修改所有记录

3. 删除记录

delete from 语句用于删除指定数据表的整行记录,使用 where 子句 来限定需要删除的
没有 where 子句代表删除该表的所有记录

8. 单表查询

select 语句用于查询数据,可以从一个或多个数据表中选出特定行、特定列的交集

select 语句语法

该语句的含义是:将数据源中的 column1 和 column2 按顺序选择出来
columnN 可以被当作指向该行该列数据的变量

数据源可以是表或视图,select 后的列表用于确定选择哪些列,where 语句用于确定选择哪些行,只有满足 where 条件的记录才会被选择出来,没有 where 语句则选择所有行
如果想选择所有列,可以使用 * 代表所有列

算术表达式

由于 columnN 可以被当作变量处理,因此 select 语句中可以使用算术表达式以改变其输出的数据(不改变其在表中的值)

支持以下算术运算符:(+,-,*,/)

注意:如果在算数运算符中使用 null,计算结果也会变成 null!

别名

如果不希望直接使用列名作为列标题,可以为数据列或表达式起别名
只需要在数据列或表达式后紧跟一个别名,用 空格 或 “as” 关键字隔开即可

select 语句中的常量

如果 select 语句只有一个常量表达式,例如:2 + 3,并且此时 where 语句总为 true,那么,该表中有多少行数据,MySQL 就将输出多少次该常量

对于这种选择常量的情况,指定表没有任何意义
因此 MySQL 提供了一种扩展语法,允许 select 语句后没有 from 子句,例如

但该语句并不是标准 SQL 语句,为了使该语句符合 SQL 规范,可以使用 dual 作为一个占位符,代表一个虚表

去除重复行

在 select 关键字后紧跟 distinct 关键字以去除重复行

运算符

比较运算符

大于:>
小于:<
大于等于:>=
小于等于:<=
相等:=
不相等:<>

逻辑运算符

非: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 中如果需要查询通配符应使用 “\” 进行转义

但标准 SQL 语句应使用 escape 关键字显式进行转义

运算符优先级

运算符优先级(小的优先)
所有比较运算符1
not2
and3
or4

与 java 相似,使用括号可以强制改变优先级顺序

排序

查询的结果默认按插入顺序排序,使用 order by 子句可以使查询结果按某列值大小进行排序

desc 代表升序排列,asc 代表降序排列,默认为升序

如果指定多个排序列,则每个排序列要单独设定 desc/asc,第一个排序列是首要排序列,只有当该列存在相同值时,后续排序列才会起作用

9. 数据库函数

函数用于进行数据处理或复杂计算,通过一个或多个输入计算出一个返回值
函数分为单行函数多行函数

单行函数对每行输入值单独计算,每行得到一个计算结果返回给用户;
多行函数对多行输入值整体计算,最后只会得到一个结果

执行函数的语法如下

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 函数

用法 2

不指定 value, 将 comopare_value 换成 condition

例如

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 子句内即可

如果不指定 where 条件, 则选出广义笛卡儿积, 表的每一行都与其他表所有行进行连接

例如:

上面两个表, 使用 select * from student_table, teacher_table  选择出的广义笛卡儿积结果为

使用 where 子句可以指定连接条件, 结果相当于对广义笛卡儿积进行过滤, 还可以使用 and 添加其他过滤条件

例如 where student_teacher_id = teacher_id

外连接

外连接就是在连接条件的列名后添加外连接符(视数据库类型而定), 代表向该表添加一行通配记录, 该行记录的所有数据都是 null, 且可以与另一个表中所有不满足条件的记录进行匹配

MySQL 不支持外连接

SQL 语句如下

自连接

如果同一个表中的不同记录之间存在主、外键关联, 即自关联, 例如把员工、经理保存在同一个表里, 则需要使用自连接查询

自连接只是连接的一种用法, 本质上就是把一个表当成两个表来使用, 语法如下

如果需要查询该数据表的所有员工名, 以及每个员工对应的经理名, 则必须使用自连接查询, 语法如下

SQL 99

SQL 99 显式使用 xxx join 进行多表连接, 使用专门的连接子句指定连接条件

交叉连接 (cross join)

交叉连接就是 SQL 92 中的广义笛卡儿积

自然连接 (natural join)

自然连接会以两个表中同名列作为等值连接条件, 如果两个表中没有同名列则与交叉连接效果完全相同

using 子句连接

使用 using 子句可以显示指定使用哪些同名列作为等值连接条件, 此时可以只使用 join 作为连接关键字, SQL 语句如下

on 子句连接

on 子句用于手动指定连接条件, SQL 语句如下

外连接

使用 left join 进行左外连接
使用 right join 进行右外连接

与 SQL 92 相反, SQL 99 的左外连接会向连接表添加万能行, 选择原表的所有数据

右外连接会向原表添加万能行, 选择连接表的所有数据

12. 子查询

子查询是指在查询语句中嵌套另一个查询语句, 子查询支持多层嵌套

对于普通的查询语句而言, 子查询可以出现在两个位置
1. 出现在 from 语句后当作数据表, 该用法实质上是一个临时视图
2. 出现在 where 条件后作为过滤条件的值

使用子查询需要注意以下几点

  1. 子查询要用括号括起来
  2. 把子查询当作数据表时, 可以为子查询起别名, 尤其是作为前缀来限定数据列时, 必须给子查询起别名
  3. 把子查询当作过滤条件时, 将子查询放在比较运算符的右边可以增强查询的可读性
  4. 把子查询当作过滤条件时, 单行子查询使用单行运算符, 多行子查询使用多行运算符

如果子查询返回单行单列值, 则可以被当成一个标量使用, 即可以使用单行记录比较运算符

如果子查询返回多个值, 则需要使用 in, any, all 等关键字

上面语句的子查询将返回单列多行值, 被当作一个值列表, 只要 student_id 与该值列表中的任意一个值相等, 就可以选出这条记录

anyall 可以与运算符结合使用

例如 = any(columns) 代表等于该列的任意一个值
= all(columns) 代表等于该列的所有值

<any(col) 代表小于该列的任意一个值, 相当于小于该列的最大值
>any(col) 代表大于该列的任意一个值, 相当于大于该列的最小值

13. 集合运算

select 语句返回的结果是一个包含多条数据的数据集, 类似于数学中的集合, 因此可以进行 并(union), 差(minus), 交(intersect) 运算

为了对两个结果集进行集合运算, 这两个结果集必须满足如下条件

  1. 两个结果集所包含的数据列数量必须相等
  2. 两个结果集所包含的数据列数据类型必须一一对应

1. union 并运算

union 用于合并两个结果集, 语法格式如下

2. minus 差运算

minus 用于从结果集中删去另一个结果集的数据, 数据必须一一对应

假设想从所有学生记录中删去与老师记录 ID 相同, 姓名相同的记录, 可以进行如下的 minus 运算

MySQL 不支持 minus 运算, 可以通过指定过滤条件手动实现

3. intersect 交运算

intersect 运算用于找出两个结果集中相同的数据

假设想找出学生记录中与老师记录 ID 相同, 姓名相同的记录, 可以进行如下的 intersect 运算

MySQL 不支持 intersect 运算, 可以通过多表连接查询实现上述运算

需要指出的是, 如果进行 intersect 运算的两个 select 子句都使用了 where 子句进行过滤, 那么将 intersect 运算改写成多表连接查询后还需要将两个 where 条件放在后面

 

赞赏

发表评论

textsms
account_circle
email

Hello WvT

13.2 SQL 语法
1. 关系数据库基本概念和 MySQL 基本命令 show databases; —— 查看当前实例下包含多少个数据库 create datebase 数据库名; ——创建新的数据库 drop database 数据库名; —— 删除数据库 …
扫描二维码继续阅读
2019-08-16


没有激活的小工具