SQL 知识概述 (continual updating)

发表于 更新于 分类于 ，

数据库

安装 MySQL

  1. 下载 MySQL
    打开 MySQL 官网https://dev.mysql.com/downloads/，选择 MySQL Installer for Windows，如图：
    MySQL安装1
    选择文件体积较大的文件安装
    如图：
    MySQL安装2
    弹出的注册界面直接点击 No thanks, just start my download.，如图：
    MySQL安装3
  2. 安装 MySQL
    双击打开刚刚下载的文件开始安装
    选择 server only，如图：
    MySQL安装4
    在此界面设置 root 用户密码，如图：
    MySQL安装5
    最后单击 finish 完成安装即可
  3. 设置环境变量
    右键单击此电脑，单击属性，选择系统，单击高级系统设置，单击环境变量，选择 Path，单击编辑，单击新建，复制安装 MySQL 的 bin 目录，点击确定保存即可。
  4. MySQL 常用命令行操作
    点击前往查看

语法基础

SQL 是什么

SQL 简介

SQL，即 "Structured Query Language (结构化查询语言)"，是数据库的标准语言。SQL 有 6 个常用动词: insert (插入)、delete (删除)、select (选择)、update (更新)、create (创建) 和 grant (授权)。

SQL 分类：

  1. 数据定义语言 (DDL)
  2. 数据操纵语言 (DML)
  3. 数据控制语言 (DCL)

关键字

关键字 描述
ADD 在现有表中添加一列
ADD CONSTRAINT 在创建表后添加约束
ALTER 添加，删除或修改表中的列，或更改表中列的数据类型
ALTER COLUMN 更改表中列的数据类型
ALTER TABLE 添加，删除或修改表中的列
ALL 如果所有子查询值都满足条件，则返回 true
AND 仅包含两个条件都为真的行
ANY 如果任何子查询值满足条件，则返回 true
AS 用别名重命名列或表
ASC 将结果集按升序排序
BACKUP DATABASE 创建现有数据库的备份
BETWEEN 选择给定范围内的值
CASE 根据条件创建不同的输出
CHECK 限制可以放置在列中的值的约束
COLUMN 更改列的数据类型或删除表中的列
CONSTRAINT 添加或删除约束
CREATE 创建数据库，索引，视图，表或过程
CREATE DATABASE 创建一个新的 SQL 数据库
CREATE INDEX 在表上创建索引（允许重复值）
CREATE OR REPLACE VIEW 更新视图
CREATE TABLE 在数据库中创建一个新表
CREATE PROCEDURE 创建一个存储过程
CREATE UNIQUE INDEX 在表上创建唯一索引（无重复值）
CREATE VIEW 根据 SELECT 语句的结果集创建视图
DATABASE 创建或删除 SQL 数据库
DEFAULT 为列提供默认值的约束
DELETE 从表中删除行
DESC 对结果集按降序排序
DISTINCT 仅选择不同的（不同的）值
DROP 删除列，约束，数据库，索引，表或视图
DROP COLUMN 删除表中的列
DROP CONSTRAINT 删除 UNIQUE，PRIMARY KEY，FOREIGN KEY 或 CHECK 约束
DROP DATABASE 删除现有的 SQL 数据库
DROP DEFAULT 删除默认约束
DROP INDEX 删除表中的索引
DROP TABLE 删除数据库中的现有表
DROP VIEW 删除视图
EXEC 执行存储过程
EXISTS 测试子查询中是否存在任何记录
FOREIGN KEY 约束是用于将两个表链接在一起的键
FROM 指定要从中选择或删除数据的表
FULL OUTER JOIN 当左表或右表中存在匹配项时，返回所有行
GROUP BY 对结果集进行分组（与汇总函数一起使用：COUNT，MAX，MIN，SUM，AVG）
HAVING 使用汇总功能代替 WHERE
IN 允许您在 WHERE 子句中指定多个值
INDEX 创建或删除表中的索引
INNER JOIN 返回两个表中具有匹配值的行
INSERT INTO 在表格中插入新行
INSERT INTO SELECT 将数据从一个表复制到另一个表
IS NULL 测试空值
IS NOT NULL 测试非空值
JOIN 联接表
LEFT JOIN 返回左表中的所有行，以及右表中的匹配行
LIKE 在列中搜索指定的模式
LIMIT 指定要在结果集中返回的记录数
NOT 仅包含条件不成立的行
NOT NULL 强制列不接受 NULL 值的约束
OR 包括其中任一条件为真的行
ORDER BY 对结果集按升序或降序排序
OUTER JOIN 当左表或右表中存在匹配项时，返回所有行
PRIMARY KEY 唯一标识数据库表中每个记录的约束
PROCEDURE 存储过程
RIGHT JOIN 返回右表中的所有行，以及左表中的匹配行
ROWNUM 指定要在结果集中返回的记录数
SELECT 从数据库中选择数据
SELECT DISTINCT 仅选择不同的（不同的）值
SELECT INTO 将数据从一个表复制到新表中
SELECT TOP 指定要在结果集中返回的记录数
SET 指定表中应更新的列和值
TABLE 创建表，或添加，删除或修改表中的列，或删除表或表中的数据
TOP 指定要在结果集中返回的记录数
TRUNCATE TABLE 删除表中的数据，但不删除表本身
UNION 合并两个或多个 SELECT 语句的结果集（仅不同的值）
UNION ALL 合并两个或多个 SELECT 语句的结果集（允许重复值）
UNIQUE 确保列中所有值唯一的约束
UPDATE 更新表中的现有行
VALUES 指定 INSERT INTO 语句的值
VIEW 创建，更新或删除视图
WHERE 筛选结果集以仅包含满足指定条件的记录

语法规则

  • 不区分大小写
  • 以分号结尾

命名规则

  • 不能是 SQL 关键字
  • 只能使用英文字母、数字、下划线

数据类型

  • 数字
  • 字符串
  • 日期时间
  • 二进制

数字

整数

  • tinyint
  • smallint
  • mediumint
  • int / integer
  • bigint

浮点数

  • float
  • double

定点数

  • decimal (m,d) m 表示最多包含的有效数字个数（默认 10），d 表示有多少位小数（默认 0）

字符串

  • char
  • varchar
  • tinytext
  • text
  • mediumtext
  • longtext
  • enum

日期时间

  • date YYYY-MM-DD
  • time HH:MM:SS
  • datetime YYYY-MM-DD HH:MM:SS
  • year YYYY
  • timestamp YYYYMMDD HHMMSS

二进制

  • bit
  • tinyblob
  • blob
  • mediumblob
  • longblob

注释

  • – 注释内容
  • /*
    注释内容
    注释内容
    */

查询操作

select

select 语句

语法

1
select 列名 from 列名

select 语句子句

  • select
  • from
  • where
  • group by
  • having
  • order by
  • limit

例：

1
select name from product;
1
select name, type, price from product;
1
select * from product; -- 查询速度慢，不推荐

特殊列名

包含空格或者关键字，应该用反引号括起来，而不能用单引号或者双引号

1
select `product name` from product;

换行说明

1
2
select name, type, price
from product;

如果 SQL 语句比较短，可以只写一行；如果 SQL 语句比较长，以 “子句” 为单位进行换行

使用别名：as

as 关键字

语法

1
2
select 列名 as 别名
from 表名;

使用

  • 列表名比较长或可读性差
  • 使用内置函数
  • 用于多表查询
  • 需要把两个或更多的列放在一起

例：

1
2
select name as product_name
from product;
1
2
select name as 名称
from product;
1
2
3
4
select name as 名称,
       type as 类型,
       price as 售价
from product;

如果包含空格则应用英文双引号括起来，而不应用单引号或反引号

1
2
select name as "商品 名称"
from product;
1
2
select name as "product-name"
from product;

条件子句：where

语法

1
2
3
select 列名
from 表名
where 条件;

一般结合以下运算符使用：

  • 比较运算符
  • 逻辑运算符
  • 其他运算符

比较运算符

  • >
  • <
  • =
  • >=
  • <=
  • !>
  • !<
  • != / <>

逻辑运算符

  • and
  • or
  • not

其他运算符

  • is null / isnull
  • is not null
  • in
  • not in
  • between A and B
  • not between A and B

运算符优先级

  • */ 高于 ±
  • not > and > or

排序子句：order by

order by 子句

语法

1
2
3
select 列名
from 表名
order by 列名 asc 或 desc;

asc 表示升序，desc 表示降序

中文字符串字段排序

语法

1
order by convert(列名 using gbk);

限制行数：limit

limit 子句

语法

1
2
3
select 列名
from 表名
limit n;

获取前 n 行，必须放在最后

语法

1
limit start, n

start 表示开始位置

去重处理：distinct

语法

1
2
select distinct 字段列表
from 表名;

数据统计

算术运算

  • +
  • -
  • *
  • /

聚合函数

  • sum()
  • avg()
  • max()
  • min()
  • count()

聚合函数一般用于 select 子句，而不能用于 where 子句

求和：sum ()

语法

1
2
select sum(列名)
from 表名;

求平均值：avg ()

语法

1
2
select avg(列名)
from 表名;

求最值：max () 和 min ()

语法

1
2
select max(列名)
from 表名;

获取行数：count ()

语法

1
2
select count(列名)
from 表名;

深入了解

语法

1
函数名(类型 列名)

类型取值有两种：all 和 distinct，默认值是 all

特别注意

  • 聚合函数只能用于 select、order by、having 这 3 种子句，而不能用于 where、group by 等其他子句
  • sum (),avg (),max (),min () 这 4 个聚合函数，只适用于统计数字类型的列。如果制定列的类型不是数字类型，就可能会报错

分组子句：group by

语法

1
2
3
select 列名
from 表名
group by 列名;

指定条件：having

语法

1
2
3
4
5
select 列名
from 表名
where 条件
group by 列名
having 条件;

having 子句必须结合 group by 子句一起用，且必须写在 group by 子句后面

子句顺序

1
2
3
4
5
6
7
select 列名
from 表名
where 条件
group by 列名
having 条件
order by 列名
limit n;

高级查询

模糊查询：like

  • %
  • _

通配符：%

  • where 列名 like ‘string%’
  • where 列名 like ‘% string’
  • where 列名 like ‘% string%’

通配符：_

  • where 列名 like ‘string_’
  • where 列名 like ‘_string’
  • where 列名 like ‘_string_’

随机查询：rand ()

语法

1
2
3
4
select 列名
from 表名
order by rand()
limit n;

子查询

在一条 select 语句中使用另一个 select 语句

  • 单值子查询
  • 多值子查询
  • 关键子查询

单值子查询

作为子查询的 select 返回单个值

多值子查询

作为子查询的 select 返回多个值

  • in
  • all
  • any / some

关联子查询

例：

1
2
3
4
5
6
7
8
select name,type,price
from product as e1
where price > (
  select avg(price)
  from product as e2
  where e1.type = e2.type
  group by type
);

内置函数

内置函数简介

  • 聚合函数
  • 数学函数
  • 字符串函数
  • 时间函数
  • 排名函数
  • 加密函数
  • 系统函数
  • 其他函数

数学函数

  • abs (列名)
  • mod (被除数，除数)
  • round (列名，n)
  • truncate (列名，n)
  • sign (列名)
  • pi()
  • rand()
  • ceil (列名)
  • floor (列名)

字符串函数

  • length (列名)
  • trim (列名)
  • reverse (列名)
  • repeat (列名，n)
  • replace (列名，A, B) – 将 A 替换成 B
  • substring (列名，start, length)
  • left (列名，n)
  • right (列名，n)
  • concat (列名 1, 列名 2, …, 列名 n)
  • lower (列名)
  • upper (列名)
  • lpad (列名，length, str)
  • rpad (列名，length, str)

时间函数

  • curdate()
  • curtime()
  • now()
  • year (列名)
  • month (列名)
  • monthname (列名)
  • dayofweek (列名)
  • dayname (列名)
  • dayofmonth (列名)
  • dayofyear (列名)
  • quarter (列名)

排名函数

  • rank()
  • row_number()
  • dense_rank()

rank()

语法

1
2
3
4
rank() over(
  partition by 列名
  order by 列名 asc 或 desc
)

partition by 是对某一列进行分组

row_number()

语法

1
2
3
4
row_number() over(
  partition by 列名
  order by 列名 asc 或 desc
)

dense_rank()

语法

1
2
3
4
dense_rank() over(
  partition by 列名
  order by 列名 asc 或 desc
)

加密函数

md5()

语法

1
md5(列名)

sha1()

语法

1
sha1(列名)

系统函数

  • database()
  • version()
  • user()
  • connection_id()

其他函数

  • cast()
  • if()
  • ifnull()

cast()

语法

1
cast(列名 as type)

if()

语法

1
if(条件, 值1, 值2)

ifnull()

语法

1
ifnull(列名, 新值)

数据修改

数据修改简介

  • insert
  • delete
  • update

插入数据：insert

insert 语句

语法

1
2
insert into 表名(列名1, 列名2, ..., 列名n)
values (值1, 值2, ..., 值n);

特殊情况

  • 顺序不一致
  • 插入部分字段

replace 语句

只有 MySQL 可以使用

更新数据：update

语法

1
2
update 表名
set 列名 = 值;

删除数据：delete

delete 语句

语法

1
2
delete from 表名
where 条件;

深入了解

一次性删除所有：

1
delete from employee;
1
truncate table employee;

几点区别：

  • delete 语句属于 DML 语句，而 truncate table 语句属于 DDL 语句
  • delete 语句后面可以使用 where 子句来指定条件，从而实现删除部分数据。而 truncate table 语句只能删除所有数据
  • delete 语句是逐行进行删除的，并且每删除一行就在日志里记录一次。而 truncate table 语句则是一次性删除所有行，它不记录日志，只记录整个数据页的释放操作。所以 truncate
    table 语句的速度更快，性能更好，并且使用的系统和事务日志资源更少
  • 使用 delete 语句删除数据之后，再次往表中添加记录时，自增字段的值为删除时该字段的最大值加 1。使用 truncate table 语句删除数据之后，再次往表中添加记录时，自增字段的默认值被重置为 1

表的操作

表的操作简介

  • create table
  • drop table
  • alter table

库操作

  • 创建库
  • 查看库
  • 修改库
  • 删除库

创建库

语法

1
create database 库名;

查看库

语法

1
show databases;

修改库

语法

1
2
3
alter database 库名
default character set = 字符集名
default collate = 校对规则名;

删除库

语法

1
drop database 库名;

创建表

语法

1
2
3
4
5
6
7
create table 表名
(
  列名1 数据类型 列属性,
  列名2 数据类型 列属性,
  ...,
  列名n 数据类型 列属性,
);

列的属性：

  • default
  • not null
  • auto-increment
  • check
  • unique
  • primary key
  • foreign key
  • comment

查看表

  • show tables
  • show create table
  • describe

show tables 语句

语法

1
show tables;

show create table 语句

语法

1
show create table 表名;

describe 语句

语法

1
describe 表名;

修改表名

修改表名

语法

1
2
alter table 旧表名
rename to 新表名;

修改字段

  • 添加列
    语法 
1
2
alter table 表名
add 列名 数据类型;
  • 删除列
    语法 
1
2
alter table 表名
drop 列名;
  • 修改列名
    语法 
1
2
alter table 表名
change 原列名 新列名 新数据类型;
  • 修改数据类型
    语法 
1
2
alter table 表名
modify 列名 新数据类型;

复制表

只复制结构

语法

1
2
create table 新表名
like 旧表名;

同时复制结构和数据

语法

1
2
create table 新表名
as (select * from product);

删除表

语法

1
drop table 表名;

列的属性

列的属性简介

  • default
  • not null
  • auto-increment
  • check
  • unique
  • primary key
  • foreign key
  • comment

语法

1
2
3
4
5
6
7
create table 表名
(
  列名1 数据类型 列属性,
  列名2 数据类型 列属性,
  ...,
  列名n 数据类型 列属性,
);

默认值

语法

1
列名 数据类型 default 默认值

非空

语法

1
列名 数据类型 not null

自动递增

语法

1
列名 数据类型 auto_increment

条件检查

语法

1
列名 数据类型 check(表达式)

唯一键

语法

1
列名 数据类型 unique

主键

语法

1
列名 数据类型 primary key
  • 具有唯一性
  • 不允许为空

主键和唯一键的区别

  • 主键的值不能为 NULL，而唯一键的值可以为 NULL
  • 一个表只能有一个主键，但可以有多个唯一键
  • 主键可以作为外键，但是唯一键不可以

外键

语法

1
constraint 外键名 foreign key(子表的列名) references 父表名(父表的列名)
  • 一般用父表的主键作为子表的外键
  • 插入数据时，必须先插入父表，然后才能插入子表
  • 删除表时，先删除子表，然后才能删除父表

注释

语法

1
列名 数据类型 comment '注释内容'

注释是一个字符串，需要用单引号括起来

操作已有表

  • 约束型属性
  • 其他属性

约束型属性

  • 条件检查
  • 唯一键
  • 主键
  • 外键 
1
2
3
4
5
6
7
-- 添加属性
alter table 表名
add constraint 标识名
属性;
-- 删除属性
alter table 表名
drop constraint 标识名;

其他属性

  • 默认值
  • 非空
  • 自动递增
  • 注释

语法

1
2
alter table 表名
modify 列名 数据类型 属性;

多表查询

多表查询简介

表与表的关系

  • 一对一
  • 一对多
  • 多对多

多表查询的方式

  • 联合查询
  • 内连接
  • 外连接
  • 笛卡尔积连接

集合运算

  • 并集
  • 交集
  • 差集

语法

1
2
3
4
-- 并集
select 列名 from 表A
union
select 列名 from 表B

内连接

基本语法

语法

1
2
3
4
select 列名
from 表A
inner join 表B
on 表A.列名  = 表B.列名；

在多表连接中，不管在什么子句中，所有列名前面都应该加上一个表名

深入了解

  • 单表查询
    对于单表查询来说，列名前面的表名前缀是可以省略的

  • using (列名)
    on staff.sid = market.sid 等价于 using (sid)

  • 连接多个表
    语法

1
2
3
4
5
6
select 列名
from 表A
inner join 表B on 连接条件
inner join 表C on 连接条件
...
;
  • 查询条件
    内连接的查询条件不一定要使用 "="，还可以使用非等值连接

外连接

外连接是什么

  • 左外连接：根据左表提取结果
  • 右外连接：根据右表提取结果
  • 完全外连接：同时对左表和右表提取结果

左外连接

语法

1
2
3
4
select 列名
from 表A
left outer join 表B
on 表A.列名  = 表B.列名；

右外连接

语法

1
2
3
4
select 列名
from 表A
right outer join 表B
on 表A.列名  = 表B.列名；

完全外连接

首先获取左外连接的结果，然后获取右外连接的结果，最后使用 union 求并集即可

笛卡尔积连接

语法

1
2
select 列名
from 表名1, 表名2;

视图

创建视图

视图简介

表保存的是实际的数据，而视图保存的是一条 select 语句，本身并不存储数据
语法

1
2
create view 视图名
as select 语句;

修改数据

  • 更新数据
    语法 
1
2
update 视图名
set 列表 = 新值;

视图和原表共享一份数据

  • 插入数据
    往视图中插入数据时，即使不符合 where 条件，数据也会被直接插入原表中

  • 删除数据

以下几种情况的视图不允许修改数据

  • 包含聚合函数的视图
  • 包含子查询的视图
  • 包含 distinct, group by, having, union 等的视图
  • 由不可更新的视图所创建的视图

查看视图

语法

1
2
3
4
5
6
7
8
-- 方式1
describe 视图名;

-- 方式2
show table status like '视图名';

-- 方式3
show create view 视图名;

修改视图

alter view

语法

1
2
alter view 视图名
as select 语句;

create or replace view

语法

1
2
create or replace view 视图名
as select 语句;

删除视图

语法

1
drop view 视图;

多表视图

语法

1
2
create view 视图名
as select 语句;

索引

索引简介

索引是建立在数据表中列上的一个数据库对象，在一个表中可以给一列或者多列设置索引
使用索引查询的时间复杂度为 O(log2n)O(log_2^n)

创建索引

语法

1
2
create index 索引名
on 表名(列名);

查看索引

语法

1
show index from 表名;

删除索引

语法

1
2
drop index 索引
on 表名;

存储程序

存储程序简介

  • 存储例程
    • 存储过程
    • 存储函数
  • 触发器
  • 事件

存储过程

  • 创建存储过程
  • 查看存储过程
  • 修改存储过程
  • 删除存储过程

创建存储过程

  • 不带参数的存储过程
    语法 
1
2
3
4
create procedure 存储过程名()
begin
    ...
end;

调用
语法

1
call 存储过程名;
  • 带参数的存储过程
    语法 
1
2
3
4
create procedure 存储过程名(参数1 类型1, 参数2 类型2, ..., 参数n 类型 n)
begin
    ...
end;
  • 参数前缀
    in(default)
    out
    inout

查看存储过程

  • show procedure status like
    语法 
1
show procedure status like '存储过程名';
  • show create procedure
    语法 
1
show create procedure 存储过程名();

修改存储过程

语法

1
2
3
4
alter procedure 存储过程名()
begin
    ...
end;

删除存储过程

语法

1
drop procedure 存储过程名;

存储函数

  • 创建存储函数
  • 查看存储函数
  • 修改存储函数
  • 删除存储函数
  • 变量的定义
  • 常用的语句

创建存储函数

语法

1
2
3
4
5
create function 存储函数名(参数1 类型1, 参数2 类型2, ..., 参数n 类型n) returns 返回值类型
begin
    ...
    return 返回值;
end;

查看存储函数

  • show function status like
    语法 
1
show function status like '存储函数名';
  • show create function
    语法 
1
show create function 存储函数名();

修改存储函数

语法

1
2
3
4
alter function 存储函数名()
begin
    ...
end;

删除存储函数

语法

1
drop function 存储函数名;

变量的定义

  • 全局变量
    语法 
1
set @变量名 = 值;
  • 局部变量
    语法 
1
2
3
4
-- 声明变量
declare 变量名 类型;
-- 初始化值
set 变量名 = 值;

常用的语句

  • 判断语句
    语法 
1
2
3
4
5
6
7
if 判断条件 then
    ...
elseif 判断条件 then
    ...
else
    语句列表
end if;
  • 循环语句
    语法 
1
2
3
4
5
6
7
8
9
10
11
12
-- while语句
while 判断条件 do
    ...
end while;
-- repeat语句
repeat
    ...
until 表达式 end repeat;
-- loop语句
loop
    ...
end loop;

触发器

创建触发器

语法

1
2
3
4
5
create trigger 触发器名 before/after 操作名
on 表名 for each row
begin
    ...
end;

查看触发器

语法

1
show triggers;

删除触发器

语法

1
drop trigger 触发器名;

事件

创建事件

  • 在某个时间点执行
    语法 
1
2
3
4
5
6
create event 事件名
on schedule at 某个时间点
do
begin
    ...
end;
  • 每隔一段时间执行
    语法 
1
2
3
4
5
6
create event 事件名
on schedule every 事件间隔
do
begin
    ...
end;

查看事件

  • show events
    语法 
1
show events
  • show create event
    语法 
1
show create event 事件名;

修改事件

语法

1
2
alter event 事件名
...;

删除事件

语法

1
drop event 事件名;

游标

创建游标

  • 创建游标
  • 打开游标
  • 获取数据
  • 关闭游标

语法

1
2
3
4
5
6
7
8
-- 创建游标
declare 游标名 cursor for 查询语句;
-- 打开游标
open 游标名
-- 获取数据
fetch 游标名 into 变量1, 变量2, ..., 变量n;
-- 关闭游标
close 游标名;