文章目录

• 1、安装数据库
• 2、关系数据库基本概念和 MySQL 基本命令
• 3、SQL 语句基础
• 4、DDL 语句

• 4.1 . 创建表的语法
• 4.2、修改表结构的语法
• 4.3、删除表的语法
• 4.4、truncate 表

• 5、数据库约束

• 5.1、NOT NULL 约束
• 5.2、 UNIQUE 约束
• 5.3、 PRIMARY KEY 约束
• 5.4、 FOREIGN KEY约束
• 5.5、 CHECK 约束

• 6、索引
• 7、视图
• 8、DML 语句语法

• 8.1、insert 语句
• 8.2、update 语句
• 8.3、delete语句

• 9、单表查询
• 10、数据库函数
• 11、分组和组函数
• 12、多表连接查询

• 12.1、SQL 92 的连接查询
• 12.1、SQL 99 的连接查询

• 13、子查询
• 14、集合运算

• 14.1、union运算
• 14.2、minus 运算
• 14.3、intersect 运算

1、安装数据库

Win10配置免安装版MySQL5.7

2、关系数据库基本概念和 MySQL 基本命令

严格来说， 数据库（Database)仅仅是存放用户数据的地方。 当用户访问、 操作数据库中的数据时，就需要数据库管理系统的帮助。 数据库管理系统的全称是 Database Management System, 简称 DBMS。

习惯上常常把数据库和数据库管理系统笼统地称为数据库， 通常所说的数据库既包括存储用户数据的部分， 也包括管理数据库的管理系统。

DBMS 是所有数据的知识库， 它负责管理数据的存储、 安全、 一致性、 并发、 恢复和访问等操作。

DBMS 有一个数据字典（ 有时也被称为系统表）， 用于存储它拥有的每个事务的相关信息， 例如名字、结构、 位置和类型， 这种关于数据的数据也被称为元数据（ metadata )。

在数据库发展历史中， 按时间顺序主要出现了如下几种类型的数据库系统。

• 网状型数据库
• 层次型数据库
• 关系数据库
• 面向对象数据库

关系数据库是其中理论最成熟、 应用最广泛的数据库。 从 20 世纪 70 年代末开始， 关系数据库理论逐渐成熟，  随之涌现出大量商用的关系数据库。 关系数据库理论经过 30 多年的发展己经相当完善， 在大量数据的查找、 排序操作上非常成熟且快速，  并对数据库系统的并发、 隔离有非常完善的解决方案。

面向对象数据库则是由面向对象编程语言催生的新型数据库， 目前有些数据库系统如 Oracle llg 等开始增加面向对象特性， 但面向对象数据库还没有大规模地商业应用。

对于关系数据库而言， 最基本的数据存储单元就是数据表， 因此可以简单地把数据库想象成大量数据表的集合（ 当然， 数据库绝不仅由数据表组成)。

数据表是存储数据的逻辑单元， 可以把数据表想象成由行和列组成的表格， 其中每一行也被称为一条记录， 每一列也被称为一个字段。  为数据库建表时， 通常需要指定该表包含多少列， 每列的数据类型信息， 无须指定该数据表包含多少行一一因为数据库表的行是动态改变的，  每行用于保存一条用户数据。

一般还应该为每个数据表指定一个特殊列， 该特殊列的值可以唯一地标识此行的记录， 则该特殊列被称为主键列。

MySQL 数据库的一个实例 (Server Instance) 可以同时包含多个数据库，

• 查看当前实例下包含多少个数据库：

show databases;

MySQL 默认以分号作为每条命令的结束符， 所以在每条 MySQL 命令结束后都应该输一个英文分号（ ; ）。

• 创建数据库

create database [IF NOT EXISTS] 数据库名;

• 删除数据库

drop database 数据库名;

建立了数据库之后， 如果想操作该数据库（ 例如为该数据库建表， 在该数据库中执行查询等操作），则需要进入该数据库。

• 进入数据库：

use 数据库名;

进入指定数据库后，

• 查询该数据库下的数据表：

show tables;

• 查看数据表的表结构:

desc 表名

MySQL 数据库安装成功后， 在其安装目录下有一个 bin 路径，该路径下包含一个 mysql 命令， 该命令用于启动 MySQL 命令行客户端。 执行 mysql 命令的语法如下：

mysql  -u 用户名 - p 密码 - h 主机名 default-character-set=utf8

执行上面命令可以连接远程主机的 MySQL 服务。 为了保证有较好的安全性， 执行上面命令时可以省略-p 后面的密码， 执行该命令后系统会提示输入密码。

MySQL 数据库通常支持如下两种存储机制。

• MyISAM： 这是 MySQL 早期默认的存储机制， 对事务支持不够好。
• InnoDB： InnoDB  提供事务安全的存储机制。 InnoDB 通过建立行级锁来保证事务完整性， 并以Oracle 风格的共享锁来处理 Select 语句。  系统默认启动 InnoDB 存储机制， 如果不想使用 InnoDB存储机制， 则可以使用 skip-innodb 选项。

对比两种存储机制， InnoDB 比 MylSAM 多了事务支持的功能。 如果使用 5.0 以上版本的 MySQL 数据库系统，  通常无须指定数据表的存储机制， 因为系统默认使用 InnoDB 存储机制。 如果需要在建表时显式指定存储机制，  则可在标准建表语法的后面添加下面任意一句。

 ENGINE=MyISAM   --强制使用 MylSAM 存储机制。
 ENGINE=InnoDB   --强制使用 InnoDB 存储机制。

3、SQL 语句基础

SQL 的全称是 Structured Query Language, 也就是结构化查询语言。 SQL 是操作和检索关系数据库的标准语言， 标准的 SQL 语句可用于操作任何关系数据库。

使用 SQL 语句， 程序员和数据库管理员 (DBA ) 可以完成如下任务：

• 在数据库中检索信息。
• 对数据库的信息进行更新。
• 改变数据库的结构。
• 更改系统的安全设置。
• 增加或回收用户对数据库、 表的许可权限。

在上面 5 个任务中， 一般程序员管理前 3 个任务， 后面 2 个任务通常由 DBA 来完成。

标准的 SQL 语句通常可分为如下几种类型。

• 查询语句： 主要由 select 关键字完成， 查询语句是 SQL 语句中最复杂、 功能最丰富的语句。
• DML ( Data Manipulation Language, 数据操作语言） 语句： 主要由 insert、 update 和 delete三个关键字完成。
• DDL ( Data Definition Language, 数据定义语言） 语句： 主要由 create、 alter、 drop 和 truncate四个关键字完成。
• DCL (Data Control Language, 数据控制语言） 语句： 主要由 grant 和 revoke 两个关键字完成。
• 事务控制语句： 主要由 commit、 rollback 和 savepoint 三个关键字完成。

SQL 语句的关键字不区分大小写， 例如： create 和 CREATE 的作用完全一样。 在上面 5 种 SQL语句中， DCL 语句用于为数据库用户授权， 或者回收指定用户的权限， 通常无须程序员操作。

在 SQL 命令中也可能需要使用标识符， 标识符可用于定义表名、 列名， 也可用于定义变量等。 这些标识符的命名规则如下：

• 标识符通常必须以字母开头。
• 标识符包括字母、 数字和三个特殊字符（#_ $)。
• 不要使用当前数据库系统的关键字、 保留字， 通常建议使用多个单词连缀而成， 单词之间以_分隔。
• 同一个模式下的对象不应该同名， 这里的模式指的是外模式。

4、DDL 语句

DDL 语句是操作数据库对象的语句， 包括创建（ create )、 删除 (drop) 和修改（ alter ) 数据库对象。

最基本的数据库对象是数据表， 数据表是存储数据的逻辑单元。 但数据库里绝不仅包括数据表， 数据库里可包含如表1所示的几种常见的数据库对象。

表 1:几种常见的数据库对象

4.1 . 创建表的语法

标准的建表语句的语法如下:

create table [ 模式名.] 表名
(
    # 可以有多个列定义
    ……
 columnNamel datatype [default expr] ,
) ;

建立数据表是建立表结构， 就是指定该数据表有多少列， 每列的数据类型， 所以建表语句的重点就是圆括号里的列定义， 列定义由列名、 列类型和可选的默认值组成。

例如：

create table test
(
  # 整型通常用 int
  test id int,
  # 小数点数
  test_price decimal,
  # 普通长度文本， 使用 default 指定默认值
  test name varchar (255) default XXX 1 f
  # 大文本类型
  test desc text,
  # 图片
  test_img blob,
  test date datetime
);  

建表时需要指定每列的数据类型， 不同数据库所支持的列类型不同， 这需要查阅不同数据库的相关文档。 MySQL 支持如列类型如下：

• 数值类型

• 日期和时间类型

• 字符串类型

上面是比较常见的建表语句， 这种建表语句只是创建一个空表， 该表里没有任何数据。 如果使用子查询建表语句， 则可以在建表的同时插入数据。 子查询建表语句的语法如下：

create table [模式名.]表名 [column[, column...]]
as subquery;

上面语法中新表的字段列表必须与子查询中的字段列表数量匹配， 创建新表时的字段列表可以省略，如果省略了该字段列表， 则新表的列名与选择结果完全相同。

下面语句使用子查询来建表：

# 创建 hehe 数据表， 该数据表和 user_inf 完全相同， 数据也完全相同
create table hehe
as
select * from user inf;

因为上面语句是利用子查询来建立数据表， 所以执行该 SQL 语句要求数据库中己存在 inf 数据表。

4.2、修改表结构的语法

修改表结构使用 alter table, 修改表结构包括增加列定义、 修改列定义、 删除列、 重命名列等操作。

• 增加列定义的语法如下：

alter table 表名
add
(
  # 可以有多个列定义
  column namel datatype [default expr] ,
  ……
);  

上面的语法格式中圆括号部分与建表语法的圆括号部分完全相同， 只是此时圆括号里的列定义是追加到己有表的列定义后面。 还有一点需要指出， 如果只是新增一列， 则可以省略圆括号， 仅在 add 后紧跟一个列定义即可。

为数据表增加字段的 SQL 语句如下：

# 为 hehe 数据表增加一个 hehe_id 字段， 该字段的类型为 int
alter table hehe
add hehe_id int;
# 为hehei据表增加 aaa、 bbb 字段， 两个字段的类型都为 varchar(255)
alter table hehe
(
  add
  aaa varchar(255) default 'XXX',
  bbb varchar(255)
) ;

增加字段时需要注意： 如果数据表中己有数据记录， 除非给新增的列指定了默认值， 否则新增的数据列不可指定非空约束， 因为那些己有的记录在新增列上肯定是空（ 实际上， 修改表结构很容易失败，只要新增的约束与己有数据冲突， 修改就会失败）。

• 修改列定义的语法如下：

alter table 表名
modify column_name datatype [default expr] [first|after col_name];

上面语法中 first 或者 after col

从上面修改语法中可以看出/ 该修改语句每次只能修改一个列定义， 如下代码所示：

 # 将 hehe 表的 hehe_id 列修改成 varchar (255 > 类型
 alter table hehe
 modify hehe_id varchar(255);
 # 将 hehe 表的'bbb 列修改成 int 类型
 alter table hehe
 modify bbb int;

显然， add 新增的列名必须是原表中不存在的， 而 modify 修改的列名必须是原表中己存在的。

• 从数据表中删除列的语法：

alter table 表名
drop column name

例如:

# 删除 hehe 表中的 aaa 字段
alter table hehe
drop aaa;

• 重命名数据表的语法格式如下：

alter table 表名
rename to 新表名

如下 SQL 语句用于将 hehe 表命名为 wawa：

# 将 hehe 数据表重命名为 wawa
alter table hehe
rename to wawa;

MySQL 为 alter table 提供了 change 选项， 该选项可以改变列名。 change 选项的语法如下:

alter table 表名
change old_column_name new_column_name type [default expr] [first|after col_name]

如下语句所示：

# 将 wawa 数据表的 bbb 字段重命名为 ddd
alter table wawa
change bbb ddd int;

4.3、删除表的语法

• 删除表的语法格式如下:

drop table 表名；

如下 SQL 语句将会把数据库中己有的 wawa 数据表删除:

# 删除数据表
drop table wawa;

删除数据表的效果如下：

* 表结构被删除， 表对象不再存在。

* 表里的所有数据也被删除。

* 该表所有相关的索引、 约束也被删除。

4.4、truncate 表

对于大部分数据库而言， truncate 都被当成 DDL 处理， truncate 被称为“ 截断”  某个表一一它的作用是删除该表里的全部数据， 但保留表结构。 相对于 DML 里的 delete 命令而言， truncate 的速度要快得多，  而且 truncate 不像 delete 可以删除指定的记录， truncate 只能一次性删除整个表的全部记录。

• truncate命令的语法如下：

truncate 表名

5、数据库约束

所有的关系数据库都支持对数据表使用约束， 通过约束可以更好地保证数据表里数据的完整性。 约束是在表上强制执行的数据校验规则， 约束主要用于保证数据库里数据的完整性。 除此之外， 当表中数据存在相互依赖性时， 可以保护相关的数据不被删除。

大部分数据库支持下面 5 种完整性约束：

• NOT NULL： 非空约束， 指定某列不能为空。
• UNIQUE： 唯一约束， 指定某列或者几列组合不能重复。
• PRIMARY KEY： 主键， 指定该列的值可以唯一地标识该条记录。
• FOREIGN KEY： 外键， 指定该行记录从属于主表中的一条记录， 主要用于保证参照完整性。
• CHECK： 检查， 指定一个布尔表达式， 用于指定对应列的值必须满足该表达式。

虽然大部分数据库都支持上面 5 种约束， 但 MySQL 不支持 CHECK 约束， 虽然 MySQL 的 SQL 语句也可以使用  CHECK 约束， 但这个 CHECK 约束不会有任何作用。虽然约束的作用只是用于保证数据表里数据的完整性， 但约束也是数据库对象，  并被存储在系统表中， 也拥有自己的名字。 根据约束对数据列的限制。

约束分为如下两类：

• 单列约束： 每个约束只约束一列。
• 多列约束： 每个约束可以约束多个数据列。

为数据表指定约束有如下两个时机：

• 建表的同时为相应的数据列指定约束。
• 建表后创建， 以修改表的方式来增加约束。

大部分约束都可以采用列级约束语法或者表级约束语法。

5.1、NOT NULL 约束

非空约束用于确保指定列不允许为空， 非空约束是比较特殊的约束， 它只能作为列级约束使用， 只能使用列级约束语法定义。

SQL 中的 null 不区分大小写。 SQL 中的null 具有如下特征：

• 所有数据类型的值都可以是 null， 包括 int、 float、 boolean 等数据类型。
• 与 Java 类似的是， 空字符串不等于 null, 0 也不等于 null。

如果需要在建表时为指定列指定非空约束， 只要在列定义后增加 not null 即可。

建表语句如下：

create table hehe
(
  # 建立了非空约束， 这意味着 hehe_id 不可以为 null
  hehe id int not null,
  # MySQL 的非空约束不能指定名字
  hehe_name varchar(255) default 'xyz' not null,
  # 下面列可以为空， 默认就是可以为空
  hehe_gender varchar(2) null
) 

除此之外， 也可以在使用 alter table 修改表时增加或者删除非空约束， SQL 命令如下:

# 增加非空约束
alter table hehe
modify hehe_gender varchar(2) not null
# 取消非空约束
alter table hehe
modify hehe_name varchar(2) null;
# 取消非空约束， 并指定默认值
alter table hehe
modify hehe_name varchar(255) default 'abc' null;

5.2、 UNIQUE 约束

唯一约束用于保证指定列或指定列组合不允许出现重复值。 虽然唯一约束的列不可以出现重复值，但可以出现多个 null 值（ 因为在数据库中 null 不等于 null )。

同一个表内可建多个唯一约束， 唯一约束也可由多列组合而成。 当为某列创建唯一约束时， MySQL会为该列相应地创建唯一索引。 如果不给唯一约束起名， 该唯一约束默认与列名相同。

唯一约束既可以使用列级约束语法建立， 也可以使用表级约束语法建立。 如果需要为多列建组合约束， 或者需要为唯一约束指定约束名， 则只能用表级约束语法。

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

使用列级约束语法建立唯一约束非常简单， 只要简单地在列定义后增加 unique 关键字即可。

SQL语句如下：

# 建表时创建唯一约束， 使用列级约束语法建立约束
create table unique_test
(
  # 建立了非空约束， 这意味着 test_id 不可以为 null
  test id int not null,
  # unique 就是唯一约束， 使用列级约束语法建立唯一约束
  test_name varchar(255) unique
 ) 

如果需要为多列组合建立唯一约束， 或者想自行指定约束名， 则需要使用表级约束语法。

表级约束语法格式如下：

[constraint 约束名] 约束定义

上面的表级约束语法格式既可放在 create table 语句中与列定义并列， 也可放在 alter table 语句中使用 add 关键字来添加约束。 SQL 语句如下：

# 建表时创建唯一约束， 使用表级约束语法建立约束
create table unique_test2
(
  # 建立了非空约束， 这意味着 test_id 不可以为 null
  test_id int not null,
  test name varchar(255) ,
  test_pass varchar(255),
  # 使用表级约束语法建立唯一约束
  unique (test_name),
  # 使用表级约束语法建立唯一约束， 而且指定约束名
  constraint test2_uk unique(test_pass)
);  

上面的建表语句为 test_name、 test_pass 分别建立了唯一约束， 这意味着这两列都不能出现重复值。

除此之外， 还可以为这两列组合建立唯一约束， SQL 语句如下:

# 建表时创建唯一约束， 使用表级约束语法建立约束
create table unique_test3
(
  # 建立了非空约束， 这意味着 test_id 不可以为 null
  test_id int not null,
  test name varchar(255),
  test_pass varchar(255),
  # 使用表级约束语法建立唯一约束， 指定两列组合不允许重复
  constraint test3_uk unique(test_name,test_pass)
);  

对于上面的 unique_test2 和 unique_test3 两个表， 都是对 test_name、test_pass  建立唯一约束， 其中unique_test2 要求 test_name、 test_pass 都不能出现重复值， 而 unique_test3 只  要 求 test_name、test_pass两列值的组合不能重复。

也可以在修改表结构时使用 add 关键字来增加唯一约束， SQL 语句如下：

# 增加唯一约束
alter table unique_test3
add unique(test_name, test_pass);

还可以在修改表时使用 modify 关键字， 为单列采用列级约束语法来增加唯一约束， 代码如下:

# 列增加唯一约束
alter table unique_test3
modify test_name varchar(255) unique;

对于大部分数据库而言， 删除约束都是在 alter table 语句后使用“ drop constraint 约束名” 语法来完成的， 但  MySQL 并不使用这种方式， 而是使用 “drop index 约束名” 的方式来删除约束。 例如如下 SQL语句:

# 删除 unique_test3 表上的 test3_uk 唯一约束
alter table unique_test3
drop index test3_uk;

5.3、 PRIMARY KEY 约束

主键约束相当于非空约束和唯一约束， 即主键约束的列既不允许出现重复值， 也不允许出现 null值；如果对多列组合建立主键约束,则多列里包含的每一列都不能为空， 但只要求这些列组合不能重复。

主键列的值可用于唯一地标识表中的一条记录。

每一个表中最多允许有一个主键， 但这个主键约束可由多个数据列组合而成， 主键是表中能唯一确定一行记录的字段或字段组合。

建立主键约束时既可使用列级约束语法， 也可使用表级约束语法。 如果需要对多个字段建立组合主键约束， 则只能使用表级约束语法。  使用表级约束语法来建立约束时， 可以为该约束指定约束名。 但不管用户是否为该主键约束指定约束名， MySQL 总是将所有的主键约束命名为  PRIMARY。

当创建主键约束时， MySQL 在主键约束所在列或列组合上建立对应的唯一索引。

创建主键约束的语法和创建唯一约束的语法非常像， 一样允许使用列级约束语法为单独的数据列创建主键，  如果需要为多列组合建立主键约束或者需要为主键约束命名， 则应该使用表级约束语法来建立主键约束。 与建立唯一约束不同的是， 建立主键约束使用  primary key。

• 建表时创建主键约束， 使用列级约束语法：

create table primary_test
(
 # 建立了主键约束
 test_id int primary key,
 test_name varchar(255)
); 

建表时创建主键约束， 使用表级约束语法:

create table primary_test2
( 
  test_id int not null,
  test_name varchar(255),
  test_pass varchar(255),
  # 指定主键约束名为 test2_pk， 对大部分数据库有效， 但对 MySQL 无效
  # MySQL 数据库中该主键约秦名依然是 PRIMARY
  constraint test2_pk primary key(test_id)
);  

建表时创建主键约束， 以多列建立组合主键， 只能使用表级约束语法:

create table primary_test3
(
  test name varchar(255),
  test_pass varchar(255),
  # 建立多列组合的主键约束
  primary key(test_name, test_jpass)
);  

如果需要删除指定表的主键约束， 则在 alter table 语句后使用 drop primary key 子句即可。 SQL 语句如下:

 # 删除主键约束
alter table primary_test3
drop primary key;

如果需要为指定表增加主键约束， 既可通过 modify 修改列定义来增加主键约束， 这将采用列级约束语法来增加主键约束； 也可通过 add 来增加主键约束， 这将采用表级约束语法来增加主键约束。 SQL语句如下：

# 使用表级约束语法增加主键约束
alter table primary_test3
add primary key(test_name,test_pass);

如果只是为单独的数据列增加主键约束， 则可使用 modify 修改列定义来实现， 如下 SQL 语句所示:

# 使用列级约束语法增加主键约束
alter table primary_test3
modify test_name varchar(255) primary key;

很多数据库对主键列都支持一种自增长的特性一一如果某个数据列的类型是整型 主键约束 ， 则可指定该列具有自增长功能 ，MySQL 指定自增长功能通常用于设置逻辑主键列一一该列的值没有任何物理意义， 仅仅用于标识每行记录。 SQL 语句 如下:

create table primary_test4
(
  increment primary key,
  test_name varchar(255),
  test_pass varchar(255)
);  

5.4、 FOREIGN KEY约束

外键约束主要用于保证一个或两个数据表之间的参照完整性， 外键是构建于一个表的两个字段或者两个表的两个字段之间的参照关系。  外键确保了相关的两个字段的参照关系： 子（ 从） 表外键列的值必须在主表被参照列的值范围之内， 或者为空（  也可以通过非空约束来约束外键列不允许为空）。

当主表的记录被从表记录参照时， 主表记录不允许被删除， 必须先把从表里参照该记录的所有记录全部删除后， 才可以删除主表的该记录。 还有一种方式， 删除主表记录时级联删除从表中所有参照该记录的从表记录。

从表外键参照的只能是主表主键列或者唯一键列， 这样才可保证从表记录可以准确定位到被参照的主表记录。 同一个表内可以拥有多个外键。

建立外键约束时， MySQL 也会为该列建立索引。

外键约束通常用于定义两个实体之间的一对多、 一对一的关联关系。 对于一对多的关联关系， 通常在多的一端增加外键列， 例如老师一学生（  假设一个老师对应多个学生， 但每个学生只有一个老师， 这是典型的一对多的关联关系）。 为了建立他们之间的关联关系，  可以在学生表中增加一个外键列， 该列中保存此条学生记录对应老师的主键。 对于一对一的关联关系， 则可选择任意一方来增加外键列，  增加外键列的表被称为从表， 只要为外键列增加唯一约束就可表示一对一的关联关系了。 对于多对多的关联关系，  则需要额外增加一个连接表来记录它们的关联关系。

建立外键约束同样可以采用列级约束语法和表级约束语法。 如果仅对单独的数据列建立外键约束，则使用列级约束语法即可； 如果需要对多列组合创建外键约束， 或者需要为外键约束指定名字， 则必须使用表级约束语法。

采用列级约束语法建立外键约束直接使用 references 关键字， references 指定该列参照哪个主表， 以及参照主表的哪一列。

如下 SQL 语句所示：

# 为了保证从表参照的主表存在， 通常应该先建主表
create table teacher_table
(
  # auto increment： 代表数据库的自动编号策略， 通常用作数据表的逻辑主键
  teacher_id int auto_increment,
  teacher_name varchar(255),
  primary key(teacher_id)
);  
create table student table3
(
  # 为本表建立主键约束
  student_id int auto_increment primary key,
  student_name varchar(255),
  java_teacher_name varchar(255),
  java_teacher_pass varchar(255),
  # 使用表级约束语法建立外键约束， 指定两列的联合外键
  foreign key(java_teacher_name , java_teacher_pass)
    references teacher_table3(teacher_name , teacher_pass)
);

删除外键约束的语法很简单， 在 alter table 后增加 “ drop foreign key 约束名 ” 子句即可。 如下代码所示:

# 删除 student_table3 表上名为 student_table3_ibfk_l 的外键约束
alter table student_table3
drop foreign key student_table3_ibfk_l;

增加外键约束通常使用 add foreign key 命令。 如下 SQL 语句所示:

# 修改 student_table3 数据表， 增加外键约束
alter table student_table3
add foreign key(java_teacher_name,java_teacher_pass)
  references teacher_table3(teacher_name,teachter_pass);

值得指出的是， 外键约束不仅可以参照其他表， 而且可以参照自身， 这种参照自身的情况通常被称为自关联。

例如， 使用一个表保存某个公司的所有员工记录， 员工之间有部门经理和普通员工之分， 部门经理和普通员工之间存在一对多的关联关系， 但他们都是保存在同一个数据表里的记录， 这就是典型的自关联。 下面的 SQL 语句用于建立自关联的外键约束。

# 使用表级约束语法建立外约束键， 且直接参照自身
create table foreign_test
(
  foreign_id int auto_increment primary key,
  foreign_name varchar(255),
  # 使用该袠的 refer_id 参照到本表的 foreign_id 列
  refer_id int,
  foreign key(refer_id) references foreign_test(foreign_id)
);  

如果想定义当删除主表记录时， 从表记录也会随之删除， 则需要在建立外键约束后添加 on delete cascade 或添加 on  delete set null, 第一种是删除主表记录时， 把参照该主表记录的从表记录全部级联删除； 第二种是指定当删除主表记录时，  把参照该主表记录的从表记录的外键设为 null。 如下 SQL 语句所示：

#为了保证从表参照的主表存在， 通常应该先建主表
create table teacher table4
(
  # auto_increment： 代表数据厍的自动编号策略， 通常用作数据表的逻辑主键
  teacher_id int auto_increment,
  teacher_name varchar(255) ,
  primary key(teacher_id)
);
create table student table4
(
 # 为本表建立主键约束
 student_id int auto_increment primary key,
 student name varchar(255),
 java_teacher int,
 # 使用表级约束语法建立外键约束， 定义级联删除
 foreign key(java_teacher) references teacher_table4(teacher_id)
 on delete cascade          # 也可用 on delete set null
);

5.5、 CHECK 约束

当前版本的 MySQL支持建表时指定CHECK约束， 但这个 CHECK约束不会有任何作用。 建立CHECK约束的语法很简单， 只要在建表的列定义后增加 check (逻辑表达式） 即可。 如下 SQL 语句所示：

create table check test
(
  emp_id int auto_increment,
  emp_name varchar(255) ,
  emp_salary decimal,
  primary key(emp_id),
  # 建立 CHECK 约束
  check(emp_salary>0)
);  

6、索引

索引是存放在模式（ schema) 中的一个数据库对象， 虽然索引总是从属于数据表， 但它也和数据表一样属于数据库对象。 创建索引的唯一作用就是加速对表的查询， 索引通过使用快速路径访问方法来快速定位数据， 从而减少了磁盘的 I/O。

索引作为数据库对象， 在数据字典中独立存放， 但不能独立存在， 必须属于某个表。

创建索引有两种方式：

• 自动： 当在表上定义主键约束、 唯一约束和外键约束时， 系统会为该数据列自动创建对应的索引。
• 手动： 用户可以通过 create index…语句来创建索引。

删除索引也有两种方式：

• 自动： 数据表被删除时， 该表上的索引自动被删除。
• 手动： 用户可以通过 drop index…语句来删除指定数据表 h的指定索引。

索引的作用类似于书的目录， 几乎没有一本书没有目录， 因此几乎没有一个表没有索引。 一个表中可以有多个索引列，  每个索引都可用于加速该列的查询速度。正如书的目录总是根据书的知识点来建立一样——因为读者经常要根据知识点来查阅一本书。  类似的,通常为经常需要查询的数据列建立索引， 可以在一列或者多列上创建索引。 创建索引的语法格式如下：

create index index_name
on table name (column[, column ] ...)；

下面的索引将会提高对 employees 表基于 last name 字段的查询速度。

 create index emp_last_name_idx
 on employees(last_name);

也可同时对多列建立索引， 如下 SQL 语句所示:

# 下面语句为 employees 的 first_name 和 last_name 两列同时建立索引
create index emp_last_name_idx2
on employees(first_name, last_name);

MySQL 中删除索引需要指定表， 采用如下语法格式:

drop index 索引名
on 表名;

如下 SQL 语句删除了 employees 表上的 emp_last_name idx2索引:

drop index enqp_last_name_idx2
on employees;

索引的好处是可以加速查询。 但索引也有如下两个坏处。

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

7、视图

视图看上去非常像一个数据表， 但它不是数据表， 因为它并不能存储数据。 视图只是一个或多个数据表中数据的逻辑显示。

使用视图有如下几个好处：

• 可以限制对数据的访问。
• 可以使复杂的查询变得简单。
• 提供了数据的独立性。
• 提供了对相同数据的不同显示。

因为视图只是数据表中数据的逻辑显示一一也就是一个查询结果， 所以创建视图就是建立视图名和查询语句的关联。

创建视图的语法如下：

create or replace view 视图名
as
subquery

从上面的语法可以看出， 创建、 修改视图都可使用上面语法。 上面语法的含义是， 如果该视图不存在， 则创建视图； 如果指定视图名的视图己经存在， 则使用新视图替换原有视图。 后面的 subquery 就是一个查询语句， 这个查询可以非常复杂。

通过建立视图的语法规则不难看出， 所谓视图的本质， 其实就是一条被命名的 SQL 查询语句。

一旦建立了视图以后， 使用该视图与使用数据表就没有什么区别了， 但通常只是查询视图数据， 不会修改视图里的数据， 因为视图本身没有存储数据。

如下 SQL 语句就创建了一个简单的视图：

create or replace view view_test
as
select teacher_name ,teacher_pass from teacher_table;

为了强制不允许改变视图的数据， MySQL 允许在创建视图时使用 with check option 子句， 使用该子句创建的视图不允许修改， 如下所示：

create or replace view view_test
as
select teacher name from teacher table
# 指定不允许修改该视图的数据
with check option;

删除视图使用如下语句:

drop view 视图名

8、DML 语句语法

与 DDL 操作数据库对象不同,DML 主要操作数据表里的数据， 使用 DML 可以完成如下三个任务。

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

DML 语句由 insert 、 update 和 delete 三个命令组成。

8.1、insert 语句

insert 用于向指定数据表中插入记录。 对于标准的 SQL 语句而言， 每次只能插入一条记录。 insert语句的语法格式如下：

insert into table_name [(column [, column...])]
values(value [, value...]);

执行插入操作时， 表名后可以用括号列出所有需要插入值的列名， 而 values 后用括号列出对应需要插入的值。

如果省略了表名后面的括号及括号里的列名列表， 默认将为所有列都插入值， 则需要为每一列都指定一个值。 如果既不想在表名后列出列名， 又不想为所有列都指定值， 则可以为那些无法确定值的列分配 null。

下面的 SQL 语句示范了如何向数据表中插入记录。

在表名后使用括号列出所有需要插入值的列:

insert into teacher_table2(teacher_name)
values('xyz');

如果不想在表后用括号列出所有列， 则需要为所有列指定值； 如果某列的值不能确定， 则为该列分配一个 null 值。

insert into teacher table2
# 使用 null 代替主键列的值
values(null , 'abc'); 

在一些特别的情况下， 可以使用带子查询的插入语句， 带子查询的插入语句可以一次插入多条记录，如下 SQL 语句所示：

insert into student_table2(student name)
# 使用子查询的值来插入
select teacher name from teacher_table2;

正如上面的 SQL 语句所示， 带子查询的插入语句甚至不要求查询数据的源表和插入数据的目的表是同一个表， 它只要求选择出来的数据列和插入目的表的数据列个数相等、 数据类型匹配即可。

MySQL 甚至提供了一种扩展的语法， 通过这种扩展的语法也可以一次插入多条记录。 MySQL 允许在 values 后使用多个括号包含多条记录， 表示多条记录的多个括号之间以英文逗号（，） 隔开。 如下SQL 语句所示：

insert into teacher table2
# 同时插入多个值
values(null , "Yeeku"),
(null , "Sharfly");

8.2、update 语句

update 语句用于修改数据表的记录， 每次可以修改多条记录， 通过使用 where 子句限定修改哪些记录。 where  子句是一个条件表达式， 该条件表达式类似于 Java 语言的 if, 只有符合该条件的记录才会被修改。 没有 where 子句则意味着  where 表达式的值总是 true， 即该表的所有记录都会被修改。 update 语句的语法格式如下：

update table_name
set columnl= valuel[, column2 = value2] …
[WHERE condition];

使用 update 语句不仅可以一次修改多条记录， 也可以一次修改多列。 修改多列都是通过在 set 关键字后使用 columnl=valuel,column2=value2…来实现的， 修改多列的值之间以英文逗号（ ,） 隔幵。

下面的 SQL 语句将会把 teacher_table2 表中所有记录的 teacher name 列的值都改为’孙悟空’。

update teacher_table2
set teacher_name = 孙悟空 ’；

也可以通过添加 where 条件来指定只修改特定记录， 如下 SQL 语句所示:

# 只修改 teacher_id 大于 1 的记录
update teacher_table2
set teacher_name =
where teacher id > 1;

8.3、delete语句

delete from 语句用于删除指定数据表的记录。 使用 delete from 语句删除时不需要指定列名， 因为总是整行地删除。

使用 delete from 语句可以一次删除多行， 删除哪些行采用 where 子句限定， 只删除满足 where 条件的记录。 没有 where 子句限定将会把表里的全部记录删除。

delete from 语句的语法格式如下:

delete from table_name
[WHERE condition];

如下 SQL 语句将会把 student_table2 表中的记录全部删除:

delete from student table2;

也可以使用 where 条件来限定只删除指定记录， 如下 SQL 语句所示:

delete from teacher_table2
where teacher id > 2;

当主表记录被从表记录参照时 ： 主表记录不能被删除， 只有先将从表中参照主表记录的所有记录全部删除后， 才可删除主表记录。  还有一种情况， 定义外键约束时定义了主表记录和从表记录之间的级联删除 on delete cascade， 或者使用 on delete set  null 用于指定当主表记录被删除时， 从表中参照该记录的从表记录把外键列的值设为 null。

9、单表查询

select 语句的功能就是查询数据。 select 语句也是SQL 语句中功能最丰富的语句， select 语句不仅可以执行单表查询， 而且可以执行多表连接查询， 还可以

进行子查询， select 语句用于从一个或多个数据表中选出特定行、 特定列的交集。

单表查询的 select 语句的语法格式如下：

select columnl, column2  ……
from 数据源
[where condition]

下面的 SQL 语句将会选择出 teacherjable 表中的所有行、 所有列的数据:

select *
from teacher table;

如果增加 where 条件， 则只选择出符合 where 条件的记录。 如下 SQL 语句将选择出 student_table表中 java teacher 值大于 3 的记录的 student name 列的值。

select student_name
from student_table
where java_teacher > 3;

当使用 select 语句进行查询时， 还可以在 select 语句中使用算术运算符（+、 一、 *、 /)， 从而形成算术表达式。 使用算术表达式的规则如下:

• 对数值型数据列、 变量、 常量可以使用算术运算符（+、 -、 *、 /) 创建表达式；
• 对日期型数据列、 变量、 常量可以使用部分算术运算符（+、 -） 创建表达式， 两个日期之间可以进行减法运算， 日期和数值之间可以进行加、 减运算；
• 运算符不仅可以在列和常量、 变量之间进行运算， 也可以在两列之间进行运算。

不论从哪个角度来看， 数据列都很像一个变量， 只是这个变量的值具有指定的范围一一逐行计算表中的每条记录时， 数据列的值依次变化。 因此能使用变量的地方， 基本上都可以使用数据列。

下面的 select 语句中使用了算术运算符。

# 数据列实际上可当成一个变量
select teacher_id + 5
from teacher_table;
# 查询出 teacher_table 表中 teacher_id * 3 大于 4 的记录
select *
from teacher_table
where teacher id * 3 > 4;

select 后的不仅可以是数据列， 也可以是表达式， 还可以是变量、 常量等。 例如，如下语句也是正确的:

# 数据列实际上可当成一个变量
select 3*5, 20
from teacher table;

SQL 语句中算术运算符的优先级与 Java 语言中的运算符优先级完全相同， 乘法和除法的优先级高于加法和减法， 同级运算的顺序是从左到右， 表达式中使用括号可强行改变优先级的运算顺序。

MySQL 中没有提供字符串连接运算符， 即无法使用加号（+) 将字符串常量、 字符串变量或字符串列连接起来。 MySQL 使用 concat 函数来进行字符串连接运算。

如下 SQL 语句所示：

# 选择出teacher_name 和 'XX '字符串连接后的结果
select concat(teacher_name ,'xx')
from teacher_table;

对于 MySQL 而言， 如果在算术表达式中使用 null , 将会导致整个算术表达式的返回值为 null; 如果在字符串连接运算中出现 null, 将会导致连接后的结果也是 null。 如下 SQL 语句将会返回 null:

select concat(teacher_name, null)
from teacher_table;

如果不希望直接使用列名作为列标题， 则可以为数据列或表达式起一个别名， 为数据列或表达式起别名时， 别名紧跟数据列， 中间以空格隔开， 或者使用 as 关键字隔开。 如下 SQL 语句所示：

select teacher_id + 5 as MY_ID
from teacher table;

如果列别名中使用特殊字符（ 例如空格）， 或者需要强制大小写敏感， 都可以通过为别名添加双引号来实现。 如下 SQL 语句所示:

# 可以为选出的列起别名， 别名中包括单引号字符， 所以把别名用双引号引起来
select teacher_id + 5 "MY'id"
from teacher table;

如果需要选择多列， 并为多列起别名， 则列与列之间以逗号隔开， 但列和列别名之间以空格隔开。如下 SQL 语句所示：

select teacher_id + 5 MY_ID , teacher name 老师名
from teacher table;

不仅可以为列或表达式起别名， 也可以为表起别名， 为表起别名的语法和为列或表达式起别名的语法完全一样， 如下 SQL 语句所示：

select teacher_id + 5 MY_ID , teacher_name 老师名
# 为 teacher_table 起另lj名 t
from teacher_table t

前面己经提到， 列名可以当成变量处理， 所以运算符也可以在多列之间进行运算， 如下 SQL 语句所示:

select teacher_id + 5 MY_ID, concat(teacher_name , teacher_id) teacher_name
from teacher_table
where teacher id * 2 > 3;

甚至可以在 select、 where 子句中都不出现列名， 如下 SQL 语句所示·:

select 5 + 4
from teacher_table
where 2 < 9;

对于选择常量的情形， 指定数据表可能没有太大的意义， 所以 MySQL 提供了一种扩展语法， 允许 select 语句后没有 from 子句， 即可写成如下形式:

select 5 + 4;

select 默认会把所有符合条件的记录全部选出来， 即使两行记录完全一样。 如果想去除重复行， 则可以使用 distinct 关键字从查询结果中清除重复行。 比较下面两条 SQL 语句的执行结果：

# 选出所有记录， 包括重复行
select student_name,java_teacher
from student table;
# 去除重复行
select distinct student_name,java_teacher
from student table;

前面己经看到了 where 子句的作用一一可以控制只选择指定的行。 因为 where 子句里包含的是一个条件表达式， 所以可以使用>、 =和<>等基本的比较运算符。

SQL 中的比较运算符不仅可以比较数值之间的大小， 也可以比较字符串、 日期之间的大小。

SQL 中判断两个值是否相等的比较运算符是单等号， 判断不相等的运算符是◊; SQL中的赋值运算符不是等号， 而是冒号等号（ := )。

除此之外， SQL 还支持如表 2所示的特殊的比较运算符。

表2

下面的 SQL 语句选出 student_id 大于等于 2， 且小于等于 4 的所有记录。

select * from student_table
where student id between 2 and 4;

使用 between val1 and val2 必须保证 val1 小于 val2,否则将选不出任何记录。 除此之外， between  val1 and val2 中的两个值不仅可以是常量， 也可以是变量， 或者是列名也行。 如下 SQL 语句选出  java_teacher小于等于 2, student id 大于等于 2 的所有记录。

select from student_table
where 2 between java_teacher and student_id;

使用 in 比较运算符时， 必须在 in 后的括号里列出一个或多个值， 它要求指定列必须与 in 括号里任意一个值相等。 如下 SQL 语句所示：

# 选出 student_id 为 2 或 4 的所有记录
select * from student_table
where student id in(2, 4);

与之类似的是， in 括号里的值既可以是常量， 也可以是变量或者列名， 如下 SQL 语句所示:

# 选出 student_id 为 2 或 4 的所有记录
select * from student_table
where student id in(2, 4);

like 运算符主要用于进行模糊查询， 例如， 若要查询名字以“ 孙” 开头的所有记录， 这就需要用到模糊查询， 在模糊查询中需要使用  like 关键字。 SQL 语句中可以使用两个通配符： 下画线（_) 和百分号（％ ), 其中下画线可以代表一个任意的字符，  百分号可以代表任意多个字符。 如下 SQL 语句将查询出所有学生中名字以“ ?小” 开头的学生。

select * from student table
where student name like '孙％';

下面的 SQL 语句将查询出名字为两个字符的所有学生。

select * from student table
# 下面使用两个下画线代表两个字符
where student name like '__';

在某些特殊的情况下， 查询的条件里需要使用下画线或百分号， 不希望 SQL 把下画线和百分号当成通配符使用， 这就需要使用转义字符， MySQL 使用反斜线（\ ) 作为转义字符， 如下 SQL 语句所示：

# 选出所有名字以下画线开头的学生
select * from student_table
where student name like '\_%';

标准 SQL 语句并没有提供反斜线（\ ) 的转义字符， 而是使用 escape 关键字显式进行转义。 例如，为了实现上面功能需要使用如下 SQL 语句：

#在标准的 SQL 中选出所有名字以下画线开头的学生
select from student_table
where student name like '\_%' escape '\';

is null 用于判断某些值是否为空， 判断是否为空不要用=111111 来判断， 因为 SQL 中 null=null 返回 null。

如下 SQL 语句将选择出 student_table 表中 student_name 为 null 的所有记录:

select from student_table
where student_name is null;

如果 where 子句后有多个条件需要组合， SQL 提供了 and 和 or 逻辑运算符来组合两个条件， 并提供了 not 来对逻辑表达式求否。 如下 SQL 语句将选出学生名字为 2 个字符， 且 student_id 大于 3 的所有记录。

select * from student_table
# 使 用 and 来 组 合 多 个 条 件
where student name like '__' and student_id > 3;

下面的 SQL 语句将选出 student_table 表中姓名不以下画线开头的所有记录。

select * from student_table
# 使用not对where条件取否
where not student name like '\_%';

当使用比较运算符、 逻辑运算符来连接表达式时， 必须注意这些运算符的优先级。 SQL 中比较运算符、 逻辑运算符的优先级如表 3 所示:

表2:MySQL逻辑运算符优先级

如果 SQL 代码需要改变优先级的默认顺序， 则可以使用括号， 括号的优先级比所有的运算符高。

如下 SQL 语句使用括号来改变逻辑运算符的优先级：

select * from student table
# 使用括号强制先计算 or 运算
where (student_id > 3 or student_name > '张 '）
and java_teacher > 1;

执行查询后的查询结果默认按插入顺序排列； 如果需要查询结果按某列值的大小进行排序， 则可以使用 order by 子句。 order by 子句的语法格式如下：

order by column_name1 [desc] , column_name2 ...

进行排序时默认按升序排列， 如果强制按降序排列， 则需要在列后使用 desc 关键字（ 与之对应的是 asc 关键字， 用不用该关键字的效果完全一样， 因为默认是按升序排列）。

上面语法中设定排序列时可采用列名、 列序号和列别名。 如下 SQL 语句选出 student_table 表中的所有记录， 选出后按 java_teacher 列的升序排列。

select * from student_table
order by java_teacher;

如果需要按多列排序， 则每列的asc > desc 必须单独设定。 如果指定了多个排序列， 则第一个排序列是首要排序列，  只有当第一列中存在多个相同的值时，第二个排序列才会起作用。 如下 SQL 语句先按 java_teacher 列的降序排列， 当 java  teacher 列的值相同时按 student name 列的升序排列。

select * from student_table
order by java_teacher desc , student_name;

10、数据库函数

正如前面看到的连接字符串使用的 concat 函数， 每个数据库都会在标准的 SQL 基础上扩展一些函数， 这些函数用于进行数据处理或复杂计算， 它们通过对一组数据进行计算,得到最终需要的输出结果。

函数一般都会有一个或者多个输入， 这些输入被称为函数的参数， 函数内部会对这些参数进行判断和计算， 最终只有一个值作为返回值。 函数可以出现在 SQL 语句的各个位置， 比较常用的位置是 select 之后和 where 子句中。

根据函数对多行数据的处理方式， 函数被分为单行函数和多行函数， 单行函数对每行输入值单独计算， 每行得到一个计算结果返回给用户； 多行函数对多行输入值整体计算， 最后只会得到一个结果。

执行函数的语法如下：

function_name(argl, arg2 ...)

多行函数也称为聚集函数、 分组函数， 主要用于完成一些统计功能， 在大部分数据库中基本相同。 但不同数据库中的单行函数差别非常大， MySQL 中的单行函数具有如下特征。

• 单行函数的参数可以是变量、 常量或数据列。单行函数可以接收多个参数， 但只返回一个值。
• 单行函数会对每行单独起作用， 每行（ 可能包含多个参数） 返回一个结果。
• 使用单行函数可以改变参数的数据类型。 单行函数支持嵌套使用， 即内层函数的返回值是外层函数的参数。

MySQL 的单行函数分类如图所示:

MySQL 数据库的数据类型大致分为数值型、 字符型和日期时间型， 所以 MySQL 分别提供了对应的函数。转换函数主要负责完成类型转换,其他函数又大致分为如下几类。

• 位函数
• 流程控制函数
• 加密解密函数
• 信息函数

每个数据库都包含了大量的单行函数， 这些函数的用法也存在一些差异， 但有一点是相同的一一每个数据库都会为一些常用的计算功能提供相应的函数，  这些函数的函数名可能不同， 用法可能有差异，但所有数据库提供的函数库所能完成的功能大致相似，  读者可以参考各数据库系统的参考文档来学习这些函数的用法。

下面通过一些例子来介绍 MySQL 单行函数的用法:

# 选出 teacher_table 表中 teacher_name 列的字符长度
select char_length(teacher_name) from teacher_table;
# 计算 teacher_name 列的字符长度的 sin 值
select sin(char_length(teacher_name)) from teacher_table;
# 计算 1.57 的lin 值， 约等于 1
select sin(1.57);
# 为指定日期添加一定的时间
# 在这种用法下 interval 是关键字， 需要一个数值， 还需要一个单位
SELECT DATE_ADD('1998-01-02' ，interval 2 MONTH);
# 这种用法更简单
select ADDDATE('1998-01-02',3);
# 获取当前日期
select CURDATE();
# 获取当前时间
select curtime();
# 下面的 MD5 是 MD5 加密函数
select MD5('testing');

MySQL 提供了如下几个处理 null 的函数。

• ifnull(expr1,expr2): 如果 exprl 为 null， 则返回 expr2， 否则返回 exprl。
• nullif(expr1,expr2): 如果 erprl 和 expr2 相等， 则返回 null, 否 则 返 回 exprl。
• if(exprl，expr2，expr3): 有点类似于？:三目运算符， 如果 exprl 为 true， 不等于 0， 且不等于 null，则返回 expr2, 否 则 返 回 expr3。
• isnull(expr1)： 判断 exprl 是否为 null, 如 果 为 null 则 返 回 true, 否 则 返 回 false。

# 如果 student_name 列为 null, 则返回 '没有名字
select ifnull(student_name,'没有名字 '）from student_ table;
# student 列等于 '张三 ’ ， 则返回 null
select nullif(student_name, 1 张三 '）from student_table;
# 如果student_name 列为null, 则返回 '没有名字'， 否则返回 '有名字
select if(isnull(student_name),'没有名字', '有名字' from student table;

MySQL 还提供了一个 case 函数， 该函数是一个流程控制函数:

一个用法的语法格式如下：

case value
when compare_valuel then resultl
when compare_value2 then result2
……
else result
end

case函数用 value 和后面的 compare_valuel 、 compare_value2、 …依次进行比较， 如果 value  和指定的 compare valuel 相等， 则返回对应的 resultl , 否 则 返 回 else 后的 result。 例如如下  SQL 语句：

# 如果 java_teacher 为 1， 则返回'Java 老师、 为 2 返回'Ruby 老师、 否则返回 '其他老师 ’
select student_name , case java_teacher
when 1 then 'Java 老师'，
when 2 then 'Ruby 老师 '，
else '其他老师 '
end
from student table;

case 函数第二个用法的语法格式如下:

case
when conditionl then resultl
when condition2 then result2
……
else result
end

在第二个用法中， condition1、 condition2 都是一个返回 boolean 值的条件表达式， 因此这种用法更加灵活。 例如如下 SQL 语句：

# id 小于 3 的为初级班， 3 6 的为中级班， 其他的为高级班
select student
when student_id<=3 then '初级班'
when student_id<=6 then '中级班'
else  '高级班'，
……
end
from student table;

虽然此处介绍了一些 MySQL 常用函数的简单用法， 但通常不推荐在 Java 程序中使用特定数据库的函数， 因为这将导致程序代码与特定数据库耦合； 如果需要把该程序移植到其他数据库系统上时， 可能需要打开源程序， 重新修改 SQL 语句。

MySQL中提供了三种循环：

• while

WHILE expression DO
   statements
END WHILE

• loop

loop_name:loop
          if 条件 THEN -- 满足条件时离开循环
               leave loop_name;  -- 和 break 差不多都是结束训话
       end if；
  end loop;

• repeat

REPEAT
 statements;
UNTIL expression
END REPEAT

11、分组和组函数

组函数也就是前面提到的多行函数， 组函数将一组记录作为整体计算， 每组记录返回一个结果， 而不是每条记录返回一个结果。 常用的组函数有如下 5 个。

• avg([distinct |all]expr): 计算多行expr 的平均值， 其中， expr 可以是变量、 常量或数据列，  但其数据类型必须是数值型。 还可以在变量、 列前使用 distinct 或 all 关键字， 如果使用 distinct,  则表明不计算重复值； all 用和不用的效果完全一样， 表明需要计算重复值。
• count({ *|[distinct |all]expr})： 计算多行 expr 的总条数， 其中， expr 可以是变量、 常量或数据列，其数据类型可以是任意类型； 用星号（ *)表示统计该表内的记录行数； distinct 表示不计算重复值。
• max(expr)： 计算多行 expr 的最大值， 其中 expr 可以是变量、 常量或数据列， 其数据类型可以是任意类型。
• min(expr)： 计算多行 expr 的最小值， 其中 expr 可以是变量、 常量或数据列， 其数据类型可以是任意类型。
• sum([distinct|all]expr)： 计算多行 expr 的总和， 其中， expr 可以是变量、 常量或数据列， 但其数据类型必须是数值型； distinct 表示不计算重复值。

# 计算 student_table 表中的记录条数
select count(*) from student_table;
# 计算 java_teacher 列总共有多少个值
select count(distinct java_teacher) from student_table;
# 统计所有 student_id 的总和
select sum(student_id) from student_table;
# 计 算 的 结 果 是 * 记 录 的 行 数
select sum(20) from student_table;
# 选出 student_table 表中 student_id 最大的值
select max(student_id) from student_table;
# 选出 teacher_table 表中 teacher_id 最小的值
select min(teacher_id) from teacher table;
# 因为 sum 里的 expr 是常量 34, 所以每行的值都相同
# 使用 distinct 强制不计算重复值， 所以下面计算结果为 34
select sum(distinct 34) from student_table;
# 使用 count 續计记录行数时， null 不会被计算在
select count(student_name) from student table;

在默认情况下， 组函数会把所有记录当成一组， 为了对记录进行显式分组， 可以在 select 语句后使用 group by 子句，  group by 子句后通常跟一个或多个列名， 表明查询结果根据一列或多列进行分组当一列或多列组合的值完全相同时， 系统会把这些记录当成一组。  如下 SQL 语句所示：

# count(*)将会对每组得到一个结果
select count(*)
from student_table
# 将 java_teacher 列值相同的记录当成一组
group by java_teacher;
• 1
• 2
• 3
• 4
• 5

如果对多列进行分组， 则要求多列的值完全相同才会被当成一组。 如下 SQL 语句所示:

# count(*)将会对每组得到一个结果
select count(*)
from student_table
# 将 java_teacher 列值相同的记录当成一组
group by java_teacher;

对于很多数据库而言， 分组计算时有严格的规则一一如果查询列表中使用了组函数， 或者 select 语句中使用了 group by  分组子句， 则要求出现在 select 列表中的字段， 要么使用组函数包起来， 要么必须出现在 group by 子句中。 这条规则很容易理解，  因为一旦使用了组函数或使用了 group by 子句， 都将导致多条记录只有一条输出， 系统无法确定输出多条记录中的哪一条记录。

对于 MySQL 来说， 并没有上面的规则要求，如果某个数据列既没有出现在 group by 之后， 也没有使用组函数包起来， 则 MySQL 会输出该列的第一条记录的值。

如果需要对分组进行过滤， 则应该使用 having子句， having 子句后面也是一个条件表达式， 只有满足该条件表达式的分组才会被选出来。 having子句和 where 子句非常容易混淆， 它们都有过滤功能， 但它们有如下区别：

• 不 能 在 where 子句中过滤组， where 子句仅用于过滤行。 过滤组必须使用 having 子句。
• 不能在where 子句中使用组函数， having 子句才可使用组函数。

如下 SQL 语句所示：

select * from student_table
group by java_teacher
# 对组进行过滤 ^
having count(*) > 2;

12、多表连接查询

很多时候， 需要选择的数据并不是来自一个表， 而是来自多个数据表， 这就需要使用多表连接查询。

例如， 对于上面的 student_table 和 teacher_table 两个数据表， 如果希望查询出所有学生以及他的老师名字， 这就需要从两个表中取数据。

多表连接查询有两种规范， 较早的 SQL 92 规范支持如下几种多表连接查询。

• 等值连接。
• 非等值连接。
• 外连接。
• 广义笛卡儿积。

SQL 99 规范提供了可读性更好的多表连接语法， 并提供了更多类型的连接查询。 SQL 99 支持如下几种多表连接查询。

• 交叉连 接。
• 自然连接。
• 使用using子句的连接。
• 使用on子句的连接。
• 全外连接或者左、 右外连接

12.1、SQL 92 的连接查询

SQL 92 的多表连接语法比较简洁， 这种语法把多个数据表都放在 from 之后， 多个表之间以逗号隔开； 连接条件放在 where  之后， 与查询条件之间用 and 逻辑运算符连接。 如果连接条件要求两列值相等，则称为等值连接， 否则称为非等值连接； 如果没有任何连接条件，  则称为广义笛卡儿积。 SQL 92 中 多表连接查询的语法格式如下：

select columnl , column2 ...
from tablel, table2 ...
[where join_condition]

多表连接查询中可能出现两个或多个数据列具有相同的列名， 则需要在这些同名列之间使用表名前缀或表别名前缀作为限制， 避免系统混淆。

所有的列都可以增加表名前缀或表别名前缀。 只是进行单表查询时， 绝不可能出现同名列，所以系统不可能混淆， 因此通常省略表名前缀。

如下 SQL 语句查询出所有学生的资料以及对应的老师姓名。

select s.* , teacher_name
# 指定多个数据表， 并指定表别名
from student_table s , teacher_table t
# 使用 where 指定连接条件
where s.java_teacher = t.teacher_id;

只要把 where 后的连接条件去掉， 就可以得到广义笛卡儿积， 如下 SQL 语句所示：

# 不使用连接条件， 得到广义笛卡儿积
select s.* , teacher_name
# 指定多个数据表， 并指定表别名
from student_table s , teacher_table t;

与此类似的是， 非等值连接的执行结果可以使用上面的嵌套循环来计算， 如下 SQL 语句所示 :

select s.* , teacher name
# 指定多个数据表， 并指定表别名
from student_table s , teacher_table t
# 使用 where 指定连接条件， 非等值连接
where s.java_teacher > t.teacher_id;

如果还需要对记录进行过滤， 则将过滤条件和连接条存使用 and 连 接 起 如 下 SQL 语句所示:

select s.* , teacher name
# 指定多个数据表， 并指定表别名
from student_table s , teacher_table t
# 使用 where 定连接条件， 并指定 student_name 列不能为 null
where s.java_teacher = t.teacher_id and student_name is not null;

有一种自连接， 和自关联类似， 如果同一个表中的不同记录之间存在主、 外键约束关联， 例如把员工、 经理保存在同一个表里， 则需要使用自连接查询。

自连接只是连接的一种用法， 并不是一种连接类型 ， 不管是 SQL 92 还是 SQL 99 都可以使用自连接查询。 自连接的本质就是把一个表当成两个表来用。

下面的 SQL 语句建立了一个自关联的数据表， 并向表中插入了 4 条数据。

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),
(null , ’ 沙僧’ ， 1);

如果需要查询该数据表中的所有员工名， 以及每个员工对应的经理名， 则必须使用自连接查询。 所谓自连接就是把一个表当成两个表来用，  这就需要为一个表起两个别名， 而且查询中用的所有数据列都要加表别名前缀， 因为两个表的数据列完全一样。 下面的自连接查询可以查询出所有的员工名，  以及对应的经理名。

select emp.emp_id,emp.emp 员工名， mgr.emp 经理名
from emp_table emp,emp_table mgr
where emp.manager_id = mgr.emp_id;

12.1、SQL 99 的连接查询

SQL 99 的连接查询与 SQL 92 的连接查询原理基本相似， 不同的是 SQL 99  连接查询的可读性更强，查询用的多个数据表显式使用 xxx join 连接， 而不是直接依次排列在 from 之后， from  后只需要放一个数据表； 连接条件不再放在where 之后， 而是提供了专门的连接条件子句。

• 交叉连接（ cross join ): 交叉连接效果就是 SQL 92 中的广义笛卡儿积， 所以交叉连接无须任何连接条件， 如下 SQL 语句所示：

select s.* , teacher_name
# SQL 99 多表连接查询&from 后只有一个表名
from student_table s
# cross join交叉连接， 相当于广义笛卡儿积
cross join teacher_table t;

• 自然连接（ naturaljoin ): 自然连接表面上看起来也无须指定连接条件， 但自然连接是有连接条件的，  自然连接会以两个表中的同名列作为连接条件； 如果两个表中没有同名列， 则自然连接与交叉连接效果完全一样一一因为没有连接条件。 如下 SQL  语句所示：

select s.* , teacher_name
# SQL 99 多表连接查询&
from 后只有一个表名
from student_table s
# natural join 自然连接使用两个表中的同名列作为连接条件
natural join teacher_table t;

• using 子句连接： using 子句可以指定一列或多列， 用于显式指定两个表中的同名列作为连接条件。  假设两个表中有超过一列的同名列， 如果使用 natural join, 则会把所有的同名列当成连接条件； 使用 using 子句，  就可显式指定使用哪些同名列作为连接条件。 如下 SQL 语句所示：

select s.* , teacher_name
# SQL 99 多表连接查询&
# from 后只有一个表名
from student_table s
# join 连接另一个表
join teacher_table t
using(teacher_id);

运行上面语句将出现一个错误， 因为 student_table 表中并不存在名为 teacher _id 的列。 也就是说，如果使用 using 子句来指定连接条件， 则两个表中必须有同名列， 否则就会出现错误。

• on 子句连接： 这是最常用的连接方式， SQL 99 语法的连接条件放在 on 子句中指定， 而且每个on子句只指定一个连接条件。 这意味着： 如果需要进行N表连接， 则需要有 N-1个 join…on 对。
  如下 SQL 语句所示：

select s.* , teacher_name
# SQL 99 多表连接查询的
""
from 后只有一个表名
from student_table s
# join 连接另一个表
join teacher_table t
# 使用 on 来指运连接条件
on s.java_teacher = t.teacher_id;

使用 on 子句的连接完全可以代替 SQL 92 中的等值连接、 非等值连接， 因为 on 子句的连接条件除等值条件之外， 也可以是非等值条件。 如下 SQL 语句就是 SQL 99 中的非等值连接。

select s.* , teacher_name
# SQL 99 多表连接查询的from 后只有一个表名
from student_table s
# join 连接另一个表
join teacher_table t
# 使用 on 来指定连接条件： 非等值连接
on s. id>t.teachter_id;

• 左、 右、 全外连接： 这三种外连接分别使用 left [outer] join、 right [outer]join 和  fbll [outer] join，这三种外连接的连接条件一样通过 on 子句来指定， 既可以是等值连接条件， 也可以是非等值连接条件。

下面使用右外连接， 连接条件是非等值连接。

select s.* , teacher_name
# SQL 99 多表连接查询 from 后只有一个表名
from student table s
# right join 右外连接另一个表
right join teacher_table t
# 使用 on 来指定连接条屏， 使用非等值连接
on s.java teacher < t.teacher id;

下面使用左外连接， 连接条件是非等值连接。

select s.* , teacher_name
# SQL 99 多表连接查询的
from 后只有一个表名
from student_table s
# left join 左外连接另一个表
left join teacher_table t
# 使用 on 来指定连接f件， 使用非等值连接
on s.java_teacher > t.teacher_id;

运行上面两条外连接语句并查看它们的运行结果， 不难发现 SQL 99 外连接与 SQL 92 外连接恰好相反， SQL99 左外连接将会把左边表中所有不满足连接条件的记录全部列出； SQL 99 右外连接将会把右边表中所有不满足连接条件的记录全部列出。

13、子查询

子查询就是指在查询语句中嵌套另一个查询，子查询可以支持多层嵌套。对于一个普通的查询语句而言，子查询可以出现在两个位置。

• 出现在form语句后当成数据表，这种用法也被称为行内视图，因为该子查询的实质就是一个临时视图。
• 出现在where 条件后作为过滤条件的值。

使用子查询时要注意如下几点。

• 子查询要用括号括起来。
• 把子查询当成数据表时（ 出现在 from 之后）， 可以为该子查询起别名， 尤其是作为前缀来限定数据列时， 必须给子查询起别名。
• 把子查询当成过滤条件时， 将子查询放在比较运算符的右边， 这样可以增强查询的可读性。
• 把子查询当成过滤条件时， 单行子查询使用单行运算符， 多行子查询使用多行运算符。

对于把子查询当成数据表来用， 只是把之前的表名变成子查询（ 也可为子查询起别名）， 其他部分与普通查询没有任何区别。 下面的 SQL 语句示范了把子查询当成数据表的用法：

select *
# 把子查询当成数据表
from (select * from student_table) t
where t.java_teacher > 1;

把子查询当成数据表的用法更准确地说是当成视图， 可以把上面的 SQL 语句理解成在执行查询时创建了一个临时视图， 该视图名为 t，  所以这种临时创建的视图也被称为行内视图。 理解了这种子查询的实质后， 不难知道这种子查询可以完全代替查询语句中的数据表，  包括在多表连接查询中使用这种子查询。

还有一种情形： 把子查询当成 where 条件中的值， 如果子查询返回单行、 单列值， 则被当成一个标量值使用， 也就可以使用单行记录比较运算符。 例如如下 SQL 语句：

select *
from student table
where java_teacher >
# 返回单行、 单列的子查询可以当成标量值使用
(select teacher_id
from teacher_table
where teacher name='Yeeku');

上面查询语句中的子查询（ 粗体字部分） 将返回一个单行、 单列值（ 该值就是 1 )——实际上， 这就是这种子查询的实质， 单行、单列子查询的返回值被当成标量值处理。

如果子查询返回多个值， 则需要使用 in、 any 和 all 等关键字， in 可以单独使用， 与前面介绍比较运算符时所讲的 in 完全一样， 此时可以把子查询返回的多个值当成一个值列表。 如下 SQL 语句所示:

select *
from student_table
where student_id in
(select teacher_id
from teacher table);

上面查询语句中的子查询（ 粗体字部分） 将返回多个值， 这多个值将被当成一个值列表， 只要studentjd 与该值列表中的任意一个值相等， 就可以选出这条记录。

any 和 all 可以与>、 >=、 <、<=、 <>、 =等运算符结合使用， 与 any  结合使用分别表示大于、 大于等于、 小于、 小于等于、 不等于、 等于其中任意一个值； 与 all 结合使用分别表示大于、 大于等于、 小于、  小于等于、 不等于、 等于全部值。 从上面介绍中可以看出， =any 的作用与 in 的作用相同。 如下 SQL

语句使S=any 来代替上面的 in。

select *
from student_table
where student_id =
any(select teacher_id
from teacher table);

<ANY 只要小于值列表中的最大值即可， >ANY 只要大于值列表中的最小值即可。 <ALL要求小于值列表中的最小值， >ALL要求大于值列表中的最大值。

下面的 SQL 语句选出 student_table 表中 student id 大于 teacher table 表中所有 teacher_id 的记录:

select *
from student_table
where student_id >
all(select teacher_id
from teacher_table);

还有一种子查询可以返回多行、 多列， 此时 where 子句中应该有对应的数据列， 并使用圆括号将多个数据列组合起来。 如下 SQL 语句所示：

select *
from student_table
where (student_id,student_name)
=any(select teacher_id, teacher_name
from teacher table);

14、集合运算

select 语句查询的结果是一个包含多条数据的结果集,类似于数学里的集合,可以进行交( intersect)、并（union) 和 差（minus) 运 算， select 查询得到的结果集也可能需要进行这三种运算。

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

• 两个结果集所包含的数据列的数量必须相等。
• 两个结果集所包含的数据列的数据类型也必须一一对应。

14.1、union运算

union 运算的语法格式如下：

select 语句 union select 语句

下面的 SQL 语句查询出所有教师的信息和主键小于 4 的学生信息。

# 查询结果集包含两列， 第一列为 int 类型， 第二列为 varchar 类型
select * from teacher table
union
# 这个结果集的数据列必须与前一个结果集的数据列一一对应
select student_id , student name from student table;

14.2、minus 运算

minus 运算的语法格式如下:

select 语句 minus select 语句

MySQL 并不支持使用 minus 运算符， 因此只能借助于子查询来“ 曲线” 实现上面的 minus 运算。

例如想从所有学生记录中“ 减去” 与老师记录的 ID 相同、 姓名相同的记录， 则可进行如下的 minus运算:

select student_id , student_name from student_table
minus
# 两个结果集的数据列的数量相等， 数据类型一一对应， 可以进行 minus 运算
select teacher id , teacher name from teacher_table;

使用MySQL则 可以通过如下子查询来实现上面运算。

select student_id , student_name from student_table
where(student_id,student_name)
not in
(select teacher_id , teacher_name from teacher_table);

14.3、intersect 运算

intersect 运算的语法格式如下:

select 语句 intersect select 语句

MySQL 不支持使用 intersect 运算符， 同样只能借助于多表连接查询来“ 曲线” 实现上面的 intersect 运算

例如想找出学生记录中与老师记录中的 ID 相同、 姓名相同的记录， 则可进行如下的 intersect 运算：

select student_id , student_name from student_table
intersect
# 两个结果集的数据列的数量相等， 数据类型一一对应， 可以进行 intersect 运算
select teacher id , teacher name from teacher table;

使用 MySQL 可以通过如下多表连接查询来实现上面运算：

select student_id , student_name from student_table
join
teacher_table
on(student id=teacher id and student name=teacher name);

参考：

【1】：《Java疯狂讲义》

【2】：《MySQL必知必会》

【3】：MySQL 数据类型|菜鸟教程

【4】：发光驴子：mysql 循环三种循环用法