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. 创建表的语法

创建空表

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
);

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

);

子查询建表语句

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

# 示例
# 创建 awsl 数据表，该数据表和 user_inf 完全相同，数据也完全相同
create table awsl
as
select * from user_inf;

create teble [模式名.]表名 [column[, column...]]

as subquery;

# 示例

# 创建 awsl 数据表，该数据表和 user_inf 完全相同，数据也完全相同

create table awsl

select * from user_inf;

2. 修改表结构

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

增加列定义

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

alter table 表名

add

(

# 可以有多个列定义

column_name1 datatype [default expr],

...

);

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

修改列定义

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);

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 命令不支持一次修改多个列定义

删除列

alter table 表名
drop column_name;

# 示例
# 删除 test 表中 id 列
alter table test
drop id;

alter table 表名

drop column_name;

# 示例

# 删除 test 表中 id 列

alter table test

drop id;

重命名数据表

alter table 表名
rename to 新表名;

# 示例 
# 将 test 表重命名为 haha
alter table test
rename to haha;

alter table 表名

rename to 新表名;

# 示例

# 将 test 表重命名为 haha

alter table test

rename to haha;

完全改变列定义

change 关键字比 modify 多一个列名

alter table 表名
change 旧列名 新列名 type [default expr] [first|after col_name];

# 示例
# 将 test 表的 id 列重命名成 uuid
alter table test
change id uuid int;

alter table 表名

change 旧列名新列名 type [default expr] [first|after col_name];

# 示例

# 将 test 表的 id 列重命名成 uuid

alter table test

change id uuid int;

3. 删除表

drop table 表名;

1	drop table 表名;

删除表的效果如下

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

4. truncate 表

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

create table test
(
    # 建立非空约束
    test_id int NOT NULL,
    test_name varchar(255) default 'xyz' not null,
    # 显式指定可空，默认就是可空
    test_gender varchar(2) null
);

create table test

(

# 建立非空约束

test_id int NOT NULL,

test_name varchar(255) default 'xyz' not null,

# 显式指定可空，默认就是可空

test_gender varchar(2) null

);

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

# 增加非空约束
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;

# 增加非空约束

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 关键字即可，约束名即为列名

# 建表时创建唯一约束，使用列级约束语法建立约束
create table unique_test
(
    test_id int not null,
    # 建立唯一约束
    test_name varchar(255) unique
);

# 建表时创建唯一约束，使用列级约束语法建立约束

create table unique_test

(

test_id int not null,

# 建立唯一约束

test_name varchar(255) unique

);

表级约束语法

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

[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)
);

[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)

);

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

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)
);

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)

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

alter table 表名
add unique(列1,列2,...);

# 示例
alter table unique_test
add
(
    unique(test_id),
    constraint test_uk unique(test_name, test_pass)
);

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 约束名” 来删除约束

alter table unique_test
drop index test_uk;

1 2	alter table unique_test drop index test_uk;

3. PRIMARY KEY 主键约束

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

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

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

列级约束语法

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

create table primary_test

(

# 建立主键约束

test_id int primary key,

test_name varchar(255)

);

表级约束语法

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)
);

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)

);

多列组合约束

create table primary_test
(
    test_account varchar(255),
    test_pass varchar(255),
    primary key(test_name, test_pass)
);

create table primary_test

(

test_account varchar(255),

test_pass varchar(255),

primary key(test_name, test_pass)

);

删除主键约束

alter table 表名
drop primary key;

1 2	alter table 表名 drop primary key;

增加主键约束

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

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

# 使用表级约束语法 add 增加主键约束

alter table primary_test

add primary key(test_name,test_pass);

# 使用列级约束语法 modify 增加主键约束

alter table primary_test

modify test_name varchar(255) primary key;

自增长特性

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

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

create table primary_test

(

# 建立主键约束，使用自增长

test_id int auto_increment primary key,

test_name varchar(255),

test_pass varchar(255)

);

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

4. FOREIGN KEY 外键约束

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

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

列级约束语法（无效）

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

# 定义 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)
);

# 定义 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 开始的整数

# [定义外键约束名称] 指定 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)
);

# [定义外键约束名称] 指定 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)

);

组合外键约束

# [定义外键约束名称] 指定 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)
);

# [定义外键约束名称] 指定 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)

);

删除外键约束

alter table 表名
drop foreign key 约束名;

# 示例
alter table student_table
drop foreign key student_table_ibfk_1;

alter table 表名

drop foreign key 约束名;

# 示例

alter table student_table

drop foreign key student_table_ibfk_1;

增加外键约束

alter table 表名
add [constrains 约束名] foreign key(col1, col2, ...)
    references 主表名(col1, col2, ...);

alter table 表名

add [constrains 约束名] foreign key(col1, col2, ...)

references 主表名(col1, col2, ...);

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

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

5. CHECK 约束

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

create table check_test
(
    emp_id int auto_increment primary key,
    emp_name varchar(255),
    emp_salary decimal,
    check(emp_salary>0)
);

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 语句删除指定数据表上的指定数据

手动创建索引

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

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

手动删除索引

drop index 索引名 on 表名

1	drop index 索引名 on 表名

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

索引也有如下两个坏处

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

6. 视图

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

创建视图

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

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;

create or replace view 视图名

subquery

# 示例

// 创建 user_info 视图

create or replace view user_info

// 选择 user 表的 name, age, desc 列

select name, age, desc from user;

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

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

删除视图

drop view 视图名

# 示例
drop view view_test;

drop view 视图名

# 示例

drop view view_test;

7. DML 语句

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

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

1. 添加记录

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

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 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 语句允许使用查询语句插入数据，例如

insert into test(col)
select col from source;

1 2	insert into test(col) select col from source;

2. 修改记录

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

update table_name
set column1 = value1, column2 = value2, ...
[where 语句];

# 示例
update test_table
set name = '罗玉龙'
where name = '罗永浩';

update test_table
set name = '老头子'
where age > 200;

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 子句代表删除该表的所有记录

delete from table_name [WHERE condition];

# 示例
# 删除 test_table 表的所有记录
delete from test_table;

# 删除 test_table 表中 age > 200 的所有记录
delete from test_table
where age > 200;

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 语句语法

select column1, column2
from 数据源
[where condition]

select column1, column2

from 数据源

[where condition]

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

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

算术表达式

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

支持以下算术运算符：(+，-，*，/)

# 将 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'

# 将 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” 关键字隔开即可

# 使用别名
select user_name 'New Name' from users;
select user_id + 5 as 'New User ID' from users;

# 使用别名

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 子句，例如

select 5 + 1;

1	select 5 + 1;

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

select 5 + 1 from dual;

1	select 5 + 1 from dual;

去除重复行

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

# 选出 student_table 中所有 age 列并去重
select distinct age from student_table;

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

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

# 查询所有名字以下划线开头的学生

select * from student_table

where student_name like '\_%';

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

# 查询所有名字以下划线开头的学生
select * from student_table
where student_name like '\_%' escape '\';

# 查询所有名字以下划线开头的学生

select * from student_table

where student_name like '\_%' escape '\';

运算符优先级

运算符	优先级（小的优先）
所有比较运算符	1
not	2
and	3
or	4

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

select * from student_table
where (student_id > 3 or student_name like '张%') and java_teacher > 1;

1 2	select * from student_table where (student_id > 3 or student_name like '张%') and java_teacher > 1;

排序

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

select ...
order by column1 [desc/asc], column2, ...;

1 2	select ... order by column1 [desc/asc], column2, ...;

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

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

9. 数据库函数

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

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

执行函数的语法如下

function_name(arg1, arg2, ...)

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

select student_name, case java_teacher
when 1 then 'Java 老师'
when 2 then 'Ruby 老师'
else '其他老师'
end
from student_table

select student_name, case java_teacher

when 1 then 'Java 老师'

when 2 then 'Ruby 老师'

else '其他老师'

end

from student_table

用法 2

不指定 value, 将 comopare_value 换成 condition

例如

select student_name, case
when student_id <= 3 then '初级'
when student_id <= 6 then '中级'
else '高级'
end
from student_table

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

select column1, column2 ...
from table1, table2 ...
[where join_condition]

select column1, column2 ...

from table1, table2 ...

[where join_condition]

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

例如:

# student_table
+--------------+
| student_name |
+--------------+
| 李小二       |
| 朱小瑞       |
| 陈小远       |
| 王小帅       |
+--------------+

# teacher_table
+--------------+
| teacher_name |
+--------------+
| 张三         |
| 李四         |
| 王五         |
+--------------+

# student_table

+--------------+

| student_name |

+--------------+

| 李小二 |

| 朱小瑞 |

| 陈小远 |

| 王小帅 |

+--------------+

# teacher_table

+--------------+

| teacher_name |

+--------------+

| 张三 |

| 李四 |

| 王五 |

+--------------+

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

+--------------+--------------+
| student_name | teacher_name |
+--------------+--------------+
| 李小二       | 张三         |
| 李小二       | 李四         |
| 李小二       | 王五         |
| 朱小瑞       | 张三         |
| 朱小瑞       | 李四         |
| 朱小瑞       | 王五         |
| 陈小远       | 张三         |
| 陈小远       | 李四         |
| 陈小远       | 王五         |
| 王小帅       | 张三         |
| 王小帅       | 李四         |
| 王小帅       | 王五         |
+--------------+--------------+

+--------------+--------------+

| student_name | teacher_name |

+--------------+--------------+

| 李小二 | 张三 |

| 李小二 | 李四 |

| 李小二 | 王五 |

| 朱小瑞 | 张三 |

| 朱小瑞 | 李四 |

| 朱小瑞 | 王五 |

| 陈小远 | 张三 |

| 陈小远 | 李四 |

| 陈小远 | 王五 |

| 王小帅 | 张三 |

| 王小帅 | 李四 |

| 王小帅 | 王五 |

+--------------+--------------+

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

例如 where student_teacher_id = teacher_id

外连接

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

MySQL 不支持外连接

SQL 语句如下

select *
from student_table s, teacher_table t
# 右外连接
where s.student_teacher_id = t.teacher_id(*)

select *

from student_table s, teacher_table t

# 右外连接

where s.student_teacher_id = t.teacher_id(*)

自连接

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

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

# 添加一个自关联的数据表
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);

# 添加一个自关联的数据表

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);

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

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;

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 中的广义笛卡儿积

select *
from table1 as t1
cross join table2 as t2;

select *

from table1 as t1

cross join table2 as t2;

自然连接 (natural join)

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

using 子句连接

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

select s.student_name t.teacher_name
from student_table s
join teacher_table t
using(teacher_id);

select s.student_name t.teacher_name

from student_table s

join teacher_table t

using(teacher_id);

on 子句连接

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

select s.student_name, t.teacher_name
from student_table s
join teacher_table t
on s.student_teacher_id > t.teacher_id;

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 条件后作为过滤条件的值

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

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

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

select *
from (select * from student_table) t
where t.teacher_id > 1;

select *

from (select * from student_table) t

where t.teacher_id > 1;

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

select *
from student table
where student_id in
(select teacher_id from teacher_table);

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) 代表大于该列的任意一个值, 相当于大于该列的最小值

select *
from student_table
where student_age < any(
select teacher_age from teacher_table
);

select *

from student_table

where student_age < any(

select teacher_age from teacher_table

);

13. 集合运算

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

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

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

1. union 并运算

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

select ... union select ...

1	select ... union select ...

2. minus 差运算

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

select ... minus select ...

1	select ... minus select ...

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

select student_id, student_name from student_table
minus
select teacher_id, teacher_name from teacher_table

select student_id, student_name from student_table

minus

select teacher_id, teacher_name from teacher_table

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

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

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 运算用于找出两个结果集中相同的数据

select ... intersect select ...

1	select ... intersect select ...

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

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 '李%';

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 条件放在后面

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 '李%';

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 '李%';

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

2. SQL 语句基础

3. DDL 语句

1. 创建表的语法

2. 修改表结构

3. 删除表

4. truncate 表

4. 数据库约束

1. NOT NULL 非空约束

2. UNIQUE 唯一约束

3. PRIMARY KEY 主键约束

4. FOREIGN KEY 外键约束

5. CHECK 约束

5. 索引

6. 视图

7. DML 语句

1. 添加记录

2. 修改记录

3. 删除记录

8. 单表查询

select 语句语法

算术表达式

别名

select 语句中的常量

运算符

排序

9. 数据库函数

MySQL 单行函数

MySQL 流程控制函数

10. 分组和组函数

常用组函数

分组

对分组进行过滤

11. 多表连接查询

SQL 92

外连接

自连接

SQL 99

交叉连接 (cross join)

自然连接 (natural join)

using 子句连接

on 子句连接

外连接

12. 子查询

13. 集合运算

1. union 并运算

2. minus 差运算

3. intersect 交运算

发表评论 取消回复

发表评论取消回复