SQL 语法 MySQL 数据库

SQL 语法 MySQL 数据库

SQL语句是对所有关系数据库都通用的命令语法，而JDBC API只是执行SQL语句的工具，JDBC允许对不同的平台、不同的数据库采用相同的编程接口来执行SQL语句

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

数据库仅仅是存放用户数据的地方。当用户访问、操作数据库中的数据时，就需要数据库管理系统的帮助。数据库管理系统的全称是Database Management System，简称DBMS。把数据库和数据库管理系统笼统地称为数据库，通常所说的数据库既包括存储用户数据的部分，也包括管理数据库的管理系统

DBMS是所有数据的知识库，它负责管理数据的存储、安全、一致性、并发、恢复和访问等操作。DBMS有一个数据字典（也称为系统表），用于存储它拥有的每个事务的相关信息，例如名字、结构、位置和类型，这种关于数据的数据也称为元数据

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

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

MySQL数据库的一个实例可以同时包含多个数据库，MySQL使用如下命令来查看当前实例下包含多少数据库

show databases;

创建新的数据库

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

删除指定数据库

delete database 数据库名;

进入指定数据库

use 数据库名;

查询该数据库下包含多少个数据表

show tables;

查看指定数据表的表结构

desc 表明;

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

• MyISAM：这是MySQL早期默认的存储机制，对事务支持不够好
• InnoDB：InnoDB提供事务安全的存储机制。InnoDB存储机制，如果不想使用InnoDB表，则可以使用skip-innodb选项
• ENGINE = MyISAM —— 强制使用MyISAM
• ENGINE = InnoDB —— 强制使用InnoDB

SQL语句基础

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

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

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

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

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

标识符的命名规则：

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

DDL语句

DDL语句是操作数据库对象的语句，包括创建（create）、删除（delete）、修改（alter）数据库对象

数据库里的几种常见的数据库对象

 

创建表的语法

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

每个列定义之间以英文逗号(,)隔开，最后一个列定义不需要使用英文逗号，而是直接以括号结束

列名放在前面，列类型放在后面。如果要指定列的默认值，则使用default关键字，而不是使用等号(=)

 

使用子查询建表语句，可以在建表的同时插入数据。子查询建表的语法

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

#创建premium_info数据表，该数据表和user_info完全相同，数据也完全相同
create table premium_info
as
select * from user_info;

修改表结构语法

修改表结构使用alter table，修改表结构包括增加列定义、修改列定义、删除列、重命名列等操作。SQL语句中的字符串值不少用双引号引起，而是用单引号引起的

alter table 表名
add
(
    #可以有多个列定义
    column_name1 datatype [default expr] ,
    ...
)

如果只是新增一列，则可以省略圆括号，仅在add后紧跟一个列定义即可

# 为premium_info数据表增加一个pre_id字段，该字段的类型为int;
alter table premium_info
add pre_id int;
# 为premium_info数据表增加增加name、duration字段，两个字段的类型都为varchar(255)
alter table premium_info
add
(
name varchar(255) default 'Jimmy',
duration varchar(255)
)

修改列定义的语法

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

first或者after col_name指定需要将目标修改到指定位置，该修改语句每次只能修改一个列定义

#将premium_info数据表的pre_id列修改成varchar(255)类型
alter table premium_info
modify pre_id varchar(255);
#将premium_info的duration列修改成int类型
alter table premium_info
modify duration int;

如果需要让MySQL支持一次修改多个列定义，则可以在alter table后使用多个modify命令

删除列的语法

alter table 表名
drop column_name  

# 删除premium_info表中的name字段
alter talbe premium_info
drop name;

从数据库中删除列定义通常总是可以成功，删除列定义时将从每行中删除该列的数据，并释放该列在数据块中占用的空间。所以删除大表中的字段时需要比较长的时间，因为还需要回收空间

MySQL的两种特殊语法：重命名数据表和完全改变列定义

重命名数据表的语法

alter table 表名
rename to 新表名

alter table premium_info
rename to premium;

change选项的语法

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

alter table premium_info
change duration time int;

删除表的语法

drop table 表名;

# 删除数据表
drop table premium_info;

删除数据表的效果如下

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

truncate表的语法

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

truncate 表名

数据库约束

约束是表上强制执行的数据校验规则，约束主要用于保证数据库里数据的完整性。除此之外，当表中数据存在相互依赖性时，可以保护相关的数据不被删除

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

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

MySQL不支持CHECK约束

根据约束对数据列的限制，可以分为如下两类：

• 单行约束：每个约束只约束一列
• 多行约束：每个约束约束多个数据列

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

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

NOT NULL约束

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

SQL中的NULL值特征：

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

建表示指定非空约束：

create table null_test
(
    # 建立了非空约束，这意味着user_id不可以为null
    user_id int not null,
    # MySQL的非空约束不能指定名字
    user_name varchar(255) default 'lin' not null,
    # 下面列可以为空，默认就是可以为空
    user_location varchar(255) null
)

也可以在使用alter table修改表时增加或者删除非空约束：

# 增加非空约束
alter table null_test
modify user_location varchar(255) not null;
# 取消非空约束
alter table null_test
modify user_name varchar(255) null;
# 取消非空约束，并指定默认值
alter table null_test
modify user_location varchar(255) default 'Nantes' null;

UNIQUE约束

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

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

当建立唯一约束时，MySQL在唯一约束所在列或列组合上建立对应的唯一索引。如果不给唯一约束起码，该唯一约束默认与列名相同

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

test_name varchar(255) unique

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

[constraint 约束名] 约束定义

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

上面建表语句为test_name、test_pass分别建立唯一约束，这意味着两列不能为空，除此之外还可以为这两列组合建立唯一约束

# 建表时创建唯一约束，使用表级约束语法建约束
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要求test_name、test_pass都不能出现重复值，而unique_test3只要求test_name、test_pass两列值的组合不能重复

可以修改表结构增加唯一约束

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

可以在修改表时使用modify关键字，为单列采用列级约束语来增加唯一约束

# 为null_test表的user_name列增加唯一约束
alter table null_test
modify user_name varchar(255) unique;

对于大多数数据库而言，删除约束都是在alter table语句后使用drop constraint约束名的语法删除约束，但MySQL并不使用这种方式，而是使用drop index、约束名的方式来删除约束

# 删除unique_test3表上的test3_uk
alter table unique_test3
drop index test3_uk;

PRIMARY KEY约束

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

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

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

建立主键约束使用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_pass)
);

如果需要删除指定表的主键约束，则在alter table语句后使用drop primary_key字句即可

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

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

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

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

MySQL只用auto_increment来设置自增长

create table primary_test4
(
    # 建立主键约束，使用自增长
    test_id int auto_increment primary key,
    test_name varchar(255),
    test_pass varchar(255)
);

FOREIGN KEY约束

外键约束主要保证一个或两个数据表之间的参照完整性，外键是构建于一个表的两个字段或者两个表的两个字段之间的参照关系。外键确保了相关的两个字段的参照关系：子(从)表外键列的值必须在主表被参照列的值范围之内，或者为空。

当主表的记录被从表的记录参照时，主表记录不允许被删除，必须先把从表里参照该记录的所有记录全部删除后，才能可以删除主表

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

外键约束通常用于定义两个实体之间的一对多，一对一的关联关系。对于一对多的关联关系，通常在多的一端增加外键列。对于一对一的关联关系，则可以选择任意一方增加外键列，增加外键列的表被称为从表。对于多对多的关联关系，则需要额外增加一个连接表来记录他们的关联关系。

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

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

# 为了保证从表参照的主表存在，通常应该先创建主表
create table teacher_table1
(
    #auto_increment：代表数据库的自动编号策略，通常用作数据库的逻辑主键
    teacher_id int auto_increment,
    teacher_name varchar(255),
    primary key(teacher_id)
);

create table student_table1
(
student_id int auto_increment primary key,
student_name varchar(255),
# 指定java_teacher参照到teacher_table的teacher_id列
java_teacher int references teacher_table1(teacher_id)
);

但值得指出的是，虽然mysql支持使用列级约束语法来建立外键约束，但这种列级约束语法建立的外键约束不会生效，mysql提供这种列级约束语法仅仅是为了和标准的SQL保持良好的兼容性，如果要使用mysql中的外键约束生效，则应该使用表级约束语法

#为了保证从表参照的的主表存在，通常应该先建主表
create table teacher_table
(
    #auto_increment：代表数据库的自动编码策略，通常用作数据表的逻辑主键
    teacher_id int auto_increment,
    teacher_name varchar(255),
    primary key(teacher_id)
);

create table student_table
(
    student_id int auto_increment primary key,
    studnet_name varchar(255),
    # 指定java_teacher参照到teacher_table的teacher_id列
    java_teacher int,
    foreign key(java_teacher) references teacher_table(teacher_id)
);

如果使用表级约束语，则需要使用foreign key来指定本表的外键列，并使用references来指定参照到那个表，以及参照到主表的哪个数据列。使用表级约束语法可以为外键指定约束名，如果创建外键约束时没有指定约束名，则MySQL会为该外键约束名为table_name_ibfk_n，其中table_name是从表的表名，而n是从1开始的整数

如果需要显式指定外键约束的名字，则可以使用constraint来指定名字

create table teacher_table2
(
    #auto_increment：代表数据库的自动编码策略，通常用作数据表的逻辑主键
    teacher_id int auto_increment,
    teacher_name varchar(255),
    primary key(teacher_id)
);

create table student_table2
(
    student_id int auto_increment primary key,
    studnet_name varchar(255),
    # 指定java_teacher参照到teacher_table的teacher_id列
    java_teacher int,
    # 使用表级约束语法建立外键约束，指定外键约束的约束名为student_teacher_fk
    constraint student_teacher_fk foreign key(java_teacher)
    references teacher_table(teacher_id)
);

如果需要建立多列组合的外键约束，则必须使用表级约束语法

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),
    # 使用表级约束语法建立外键约束，指定两列的联合外键
    foreign key(java_teacher_name, java_teacher_pass)
    references teacher_table(teacher_name, teacher_pass)
);

删除外键约束的语法也很简单，在alter table后增加"drop foreign key 约束名"字句即可

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

增加外键约束通常使用add foreign key命令，如下

# 修改student_table的数据表，增加外键约束
alter table student_table
add foreign key(java_teacher_name, java_teacher_pass)
references teacher_table(teacher_name, teacher_pass);

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

# 使用表级约束语法建立外约束键，且直接参照自身
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

create table teacher_table
(
    teacher_id int auto_increment,
    teacher_name varchar(255),
    primary key(teacher_id)
);

create table studnet_table
(
    # 为本表建立主键约束
    student_id int auto_increment primary key,
    studnet_name varchar(255),
    java_teacher int,
    # 使用表级约束语法建立外键约束，定义级联删除
    foreign key(java_teacher) references teacher_table(teacher_id)
    on delete cascade
    # 也可以使用on delete set null
);

CHECK约束

建立CHECK约束的语法很简单，只要在建表的列定义后增加check即可

create table check_test
(
    emp_id int auto_increment,
    emp_name varchar(255),
    emp_salary decimal,
    # 创建CHECK约束
    check(emp_salary>0)
);

索引

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

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

创建索引有两种方式

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

删除索引也有两种方式

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

创建索引的语法格式如下：

create index index_name
on table_name (column[,column]...);

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

create index emp_last_name_index
on employees(last_name);

同时对多列建立索引如

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

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

drop index 索引名 on 表名

如下SQL语句删除了employees表中的emp_last_name_idx的索引

drop index emp_last_index on employees;

索引的好处是加速查询，但索引也有如下坏处：

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

视图

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

使用试图的优点：

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

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

create or replace view 视图名
as
subquery

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

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

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 form teacher_table
# 指定不允许修改视图的数据
with check option;

删除视图使用如下语句：

drop view 视图名

如下SQL语句删除了前面刚刚创建的视图名

drop view view_test;

DML语句的语法

与DDL操作数据库对象不同，DML主要操作数据表里的数据，使用DML可以完成以下3中任务：

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

DML语句由insert into、update、和delete from 3个命令组成。

insert into语句

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

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

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

insert into teacher_table2 value ('Vincent');

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

insert into teacher_table2
# 使用null代替主键列的值
values(null, 'Pigeau');

然而此时，Pigeau记录的主键列的值是2，而不是SQL语句插入的null，因为该主键列是自增长，系统会自动为该列分配值

根据外键约束规则：外键列里的值必须是被参照列里已有的值，所以向从表中插入记录之前，通常应该先向主表中插入记录，否则从表记录的外键列只能为null。现向从表student_table2中插入记录

insert into student_table2
# 当向外键列里插值时，外键列的值必须是被参照列里已有的值
values (null, 'Mars', 2)；

在一些特殊的情况下，我们可以使用带子查询的插入语句，带子查询的插入语句可以一次插入多条记录

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

MySQL允许在values后使用多个括号包含多条记录，表示多条记录的多个括号之间以英文逗号(,)隔开

insert into teacher_table2
# 同时插入多个值
values (null, 'Paris'),
(null, 'Nantes');

update语句

update语句用于修改数据表的记录，每次可以修改多条记录，通过使用where子句限定修改哪些记录。没有where子句则意味着where表达式的值总是true，即该表的所有记录都会被修改，update语句的语法格式如下:

update teacher_table
set column1 = value1[,column=value2]...
[WHERE condition];

使用update不仅可以一次修改多条记录，也可以一次修改多列。修改多列都是通过在set关键字后使用column1=value1,column2=value2...来实现的，修改多列的值之间以英文逗号(,)隔开

update teacher_table2
set teacher_name = '王';

也可以通过添加where条件来指定只修改特定记录，如下

update teacher_table
set teacher_name = '林'
where teacher_id > 1;

delete from语句

delete from语句用于删除指定数据表的记录。使用delete from语句删除时不需要指定列名，因为总是整行地删除。使用delete from语句可以一次删除多行，删除哪些行采用where字句限定，只删除满足where条件的记录。没有where字句限定将会把表里的全部记录删除

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

delete from table_name
[WHERE condition];

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

delete from studnet_table2;

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

delete form teacher_table2
where teacher_id > 2;

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

单表查询

select语句的功能就是查询数据。select语句也是SQL语句中功能最丰富的语句，select语句不仅可以执行单表查询，而且可以执行多表连接查询，还可以进行子查询，select语句用于从一个或多个数据表中选出特定行、特定列的交集

select语句最简单的功能如图所示

 

单表查询的select语句的语法如下:

select colimn1 colimn2 ...
form 数据源
[WHERE condition]

上面的语法格式中的数据源可以是表、视图等。从上面的语法格式中可以看出，select后的列表用于选择哪些列，where条件用于确定选择哪些行，只有满足where条件的记录才会被选择出来；如果没有where条件，则默认选出所有行。如果想选择所有列，则可使用星号(*)代表所有列

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

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使用concat函数来进行字符串连接运算。

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

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

select concat(teacher_name, null)
from teacher_table;

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

select teacher_id + 5 as MY_ID
from teacher_table;

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

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

如果需要选择多列，并为多列起别名，则多列与列之间以逗号隔开，但列和列名之间以空格隔开

select teacher_id + 5 MY_ID, teacher_name 老师名
from teacher_table;

不仅可以为列或表达式起别名，也可以为表起别名，为表起别名的语法和为列或表达式起别名的语法完全一样

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

列名可以当成变量处理，所以运算符也可以在多列之间进行运算

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

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

# 选出所有记录，包括重复行
select student_name, java_teacher
from student_table;

# 去除重复行
select distinct student_name, java_teacher
from student_table;

注：使用distinct去除重复行时，distinct紧跟select关键字，它的作用是去除后面字段组合的重复值，而不管对应对应记录在数据库是否重复

前面已经看到了where字句的作用：可以控制只选择指定的行。因为where字句里包含的是一个条件表达式，所以可以使用>、>=、<、<=、=和<>等基本的比较运算符。SQL中的比较运算符不仅可以比较数值之间的大小，也可以比较字符串、日期之间的大小

SQL判断两个值是否相等的比较运算符是单等号=，判断不等的运算符是<>；SQL中的赋值运算符不是等号，而是冒号等号(:=)

SQL支持的特殊比较运算符

运算符	含义
expr1 between expr2 and expr3	要求expr1 >= expr2 并且 expr2 <= expr3
expr1 in(expr2,expr3,expr4,...)	要求expr1等于后面括号里任意一个表达式的值
like	字符串匹配，like后的字符串支持通配符
is null	要求指定值等于null

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

select *
from student_table
where student_id between 2 and 4;

# 选出java_teacher小于等于2，student_id大于等于2的所有记录
select *
from student_table
where 2 between java_teacher and student_id;

使用in比较运算时，必须在in后的括号里列出一个或多个值，它要求指定列必须与in括号里任意一个值相等

# 选出student_id、java_teacher列的值为2或4的所有记录
select *
from student_table
where student_id in(2,4);

与之类似的是，in括号里的值既可以是常量，也可以是变量或者列名

# 选出student_id、java_teacher列的值为2的所有记录
select *
from student_table
where 2 in(student_id,java_teacher);

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

select *
from student_table
where student_name like '孙%';

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

select *
from student_table
# 下面使用两个下划线代表来个字符
where student_name like '__';

在某些特殊情况下，查询的条件里需要使用下划线或百分号，不希望SQL把下划线和百分号当成通配符使用，这就需要使用转义字符，MySQL使用反斜线(/)作为转义字符

# 选出所有名字以下划线开头的学生
select 8
from student_table
where student_name like '\_%';

is null 用于判断某些值是否为空，判断是否为空不能用=null来判断，因为SQL中null=null返回null。如下SQL语句将选择出student_table表中student_name为null的所有记录

select *
from student_table
where student_name is null;

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

select *
from student_table
where student_name like '__' and studnent_id > 3;

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

select *
from student_table
# 使用not对where条件取否
where not student_name like '/_%';

SQL中比较运算符、逻辑运算符的优先级

 

order by语句

执行查询后的结果默认按插入顺序排序；如果需要在查询结果按某列值的大小进行排序，则可以使用order by字句

ORDER BY 语句用于根据指定的列对结果集进行排序。ORDER BY 语句默认按照升序对记录进行排序。如果您希望按照降序对记录进行排序，可以使用 DESC 关键字

order by字句的语法如下：

order by column_name1 [desc], column_name...

进行排序时默认按升序排序排列，如果强制按降序排序，则需要在列后使用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;

数据库函数

每个数据库都会在标准的SQL基础上扩展一些函数，这些函数用于进行数据处理或复杂计算，他们通常对一组数据进行计算，得到最终需要的输出结果。函数一般都会有一个或者多个输入，这些输入被称为函数的参数，函数内部会对这些参数进行判断和计算，最终只有一个值作为返回值。函数可以出现在SQL语句中的各个位置，比较常用的位置是select之后的where子句中

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

 

SQL中的函数和java语言中的方法有点相似，但SQL中的函数是独立的程序单元，也就是说，调用函数时无需使用任何类、对象作为调用者，而是直接执行函数。如下：

function_name(arg1,arg2...)

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

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

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

 

MySQ数据库的数据类型大致分为数值型、字符型、和日期时间型。所以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;

# 为指定日期添加一定的时间，在这种用法下interval是关键字，需要一个数值还有一个单位
select DATE_ADD('1998-01-02', interval 2 MONTH);

# 获取当前日期
select CURDATE();

# 获取当前时间
select curtime();

# 下面的MD5是MD5加密函数
select MD5('testing');

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

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

# 如果student_name列为null，则返回'没有名字'
select ifnull(student_name, '没有名字')
from student_table;

# 如果CTO_name列为'吴局'，则返回null
select nullif(CTO_name, '吴局')
from CTO_table;

# 如果student_name列为null，则返回'没有名字'，否则返回'有名字'
select if(isnull(student_name), '没有名字', '有名字')
from student_table;

case函数

case函数，流程控制函数。case函数有两个用法

case函数第一个用法的语法

case value
when compare_value1 then result1
when compare_value2 then result2
...
else result
end

case函数用value和后面的compare_value1、compare_value2、...依次进行比较，如果value和指定的compare_value1相等，则返回对应的result1，否则返回else后的result

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

case函数第二个用法的语法

case
when condition1 then result1
when condition2 then result2
...
else result
end  

condition返回boolean值的条件表达式

# id小于3的为初级工程师，3~6为中级工程师，其他为高级工程师
select employees_name, case
when employees_id <= 3 then '初级工程师'
when employees_id <= 6 then '中级工程师'
else '高级工程师'
end
from employees_table;

分组和组函数

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

• avg([distinct|all]expr)：计算多行expr平均值，其中expr可以是变量、常量或者数据列，但其数据类型必须是数值型。使用distinct表明不计算重复值；all表明需要计算重复值
• count({*|[distinct|all] expr})：计算多行expr的总条数，其中expr可以是变量、常量或者数据列，但其数据类型必须是数值型。用星号（*）表示统计该表内的记录行数
• max(expr)：计算多行expr的最大值
• min(expr)：计算多行expr的最小值
• sum([distanct|all]expr)：计算多行expr的总和

# 计算student_table表中的记录条数
select count(*)

# 计算java_teacher列总共有多少个值
select count(distinct java_teacher)

# 统计所有student_id 的总和
select sum(student_id)

# 计算的结果是20 * 记录的行数
select sum(20)

# 选出student_table表中student_id最大的值
select max(student_id)

# 选出student_table表中student_id最小的值
select min(student_id)

# 因为sum里的expr是常量23，所以每行的值都相同
# 使用distinct强制不计算重复值，所以下面计算结果为23
select sum(distinct 23)

# 使用count统计记录行数，null不会被计算在内
select count(student_name)

# 对于可能出现null的列，可以使用ifnull函数来处理该列
# 计算java_teacher列所有记录的平均值
select avg(ifnull(java_teacher, 0))

from student_table;

# distinct和*不可同时使用

group by语句

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

SQL GROUP BY 语法

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

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

如果对多列进行分组，则要求多列的值完全相同才会被当成一组

# count(*)将会对每组得到一个结果
select count(*)
from student_table
# 将java_teacher、student_name两列的值完全相同时才会被当成一组
group by java_teacher, student_name;

having语句

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

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

在SQL中增加HAVING子句原因是，WHERE关键字无法与合计函数一起使用

SQL HAVING 语法

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

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

多表连接查询

以下book与student数据表：

 

 

 

交叉连接（cross join）

交叉连接无须任何连接条件。返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积

select *
from book as a
# cross join交叉连接，相当于广义笛卡尔积
cross join stu as b
order by a.id

 

自然连接

自然连接会以两个表中的同名列作为连接条件；如果两个表没有同名列，则自然连接与交叉连接效果完全一样——因为没有连接条件。

select s.*, teacher_name
from student_table s
# natural join 自然连接使用两个表中的同名列作为连接条件
natural join teacher_table t;

在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列

使用using子句的连接

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

select s.*, teacher_name
from student_table s
# join连接另一个表
join teacher_table t
using(teacher id);

使用on子句的连接

最常用的的连接方式，而且每个on子句只指定一个连接条件。这意味着：如果需要进行N表连接，则需要有N-1个join...on对

select s.*, teacher_name
from student_table s
# join连接另一个表
join teacher_table t
# 使用on来指定连接条件
on s.java_teacher = t.teacher_id;

on子句的连接条件除了等值条件之外，也可以是非等值条件

select s.*, teacher_name
from student_table s
# join连接另一个表
join teacher_table t
# 使用on来指定连接条件
on s.java_teacher > t.teacher_id;

• 等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列
• 不等值连接：在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>

全外连接或者左、右外链接

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

左联接

是以左表为基准，将a.stuid = b.stuid的数据进行连接，然后将左表没有的对应项显示，右表的列为null

select *
from book as a
left join stu as b
on a.sutid = b.stuid

 

右连接

是以右表为基准，将a.stuid = b.stuid的数据进行连接，然以将右表没有的对应项显示，左表的列为null

select *
from book as a
right join stu as b
on a.sutid = b.stuid 

 

全连接

完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值

 

子查询

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

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

使用子查询时的注意点

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

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

• 把子查询当成where条件中的值，如果子查询返回单行、单列值，则被当成一个标量值使用，也就可以使用单行记录比较运算符

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

• 如果子查询返回多个值，则需要使用in、any和all等关键字
• in可以单独使用，此时可以把子查询返回的多个值当成一个值列表

select *
from student_table
where student_id in
(select teacher_id
from teacher_table);

• any、all可与>、>=、<、<=、<>、=等运算符结合使用。与any结合表示大于、大于等于、小于、小于等于、不等于、等于其中任意一个值；与all结合表示大于、大于等于、小于、小于等于、不等于、等于全部值
• =any与in的作用相同

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

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

# 选出student_table表中student_id大于teacher_table表中所有teacher_id的记录
select *
from student_table
where student_id >
all(select teacher_id
from teacher_table);

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

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

集合运算

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

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

union运算

union运算的语法格式

select 语句 union select 语句

查询所有教师的信息和主键小于4的学生信息

# 查询结果包含两列，第一列为int类型，第二列为varchar类型
select * from teacher_table
union
select student_id , student_name from student_table;

minus运算

minus运算的语法格式,MySQL实则不支持这种运算

select 语句 minus select 语句

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

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

intersect运算

intersect运算的语法格式

select 语句 intersect select 语句

找出学生记录中与老师记录中的ID相同、姓名相同的记录

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