MySQL

序言

• 实现数据持久化
• 使用完整的管理系统统一管理，易于查询

数据库的相关概念

DB：数据库（database），存储数据的“仓库”。保存了一些列有组织的数据

DBMS：数据库管理系统（Database Management System），数据库是通过DBMS创建和操作的容器

SQL：结构化查询语言（Structure Query Language），专门用来与数据库通讯的语言

SQL的优点

• 不是某个特定数据库供应商专有的语言，几乎所有DBMS都支持SQL
• 简单易学
• 虽然简单，但实际上是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作

数据库存储数据的特点

• 将数据放到表中，表放在库中
• 一个数据库中可以有多个表，每个表都有一个名字，用来标识自己的名字。表名具有唯一性
• 表具有某些特征，这些特征定义了数据库在表中如何存储
• 表由列组成，我们称之为字段。所有表都是由一个或多个列组成的
• 表中的数据是按行存储的

MySQL优点

• 成本低：开放源代码，一般可以免费试用
• 性能高：执行很快
• 简单：容易安装使用

DBMS分为两类

• 基于共享文件系统的DBMS
• 基于客户机—服务器的DBMS

---

MySQL产品使用

MySQl服务的启动和停止

• 方式一：计算机—右击管理—服务
• 方式二：通过管理员身份运行cmd
  • net start mysql（启动服务）
  • net stop mysql（停止服务）

MySQL服务的登录和退出

• 方式一：通过MySQL自带客户端，只限于root用户
• 方式二：通过windows自带客户单

  • 登录：mysql [ -h 主机名 -P 端口号] -u root -p

    mysql -h localhost -P 3306 -u root -p

    mysql -u root -p本机

  • 退出：exit或ctrl + c

MySQL常见的命令

• 查看当前所有的数据库：show databases;

• 打开指定的库：use 库名

• 查看当前库的所有表：show tables;

• 查看其他库的所有表：show tables from 库名;

• 创建表：create table 表名(列名 列类型,

  列名 列类型,

  ...);

• 查看表结构：desc 表名;

• 查看服务器版本

  • 方式一：登录到MySQL服务端：select version();
  • 方式二：没有登录到MySQL服务端：mysql –version或mysql –V

MySQL的语法规范

• 不区分大小写，但建议关键字大写，表名、列名小写
• 每条命令最好用分号结尾
• 每条命令根据需要，可以进行缩进或换行
• MySQL中所有的索引下标从1开始，与基本语言有区别
• 注释
  • 单行注释：#注释文字
  • 单行注释：– 注释文字
  • 多行注释：/* 注释文字 */

---

DQL语言学习（Data Query Language）

基础查询

语法：

SELECT 查询列表
FROM 表名;

查询列表：表中字段、常量值、表达式、函数

查询的结果：一个虚拟的表格

查询表中单个字段：

SELECT 列名
FROM 表名;

查询表中多个字段：

SELECT 列名1, 列名2, ..., 列名N
FROM 表名;

查询表中所有字段：

#方法一
#查询表内所有列表
SELECT * 
FROM 表名;

#方法二
SELECT 所有列名
FROM 表名;

查询常量值：

#字符型与日期型的常量值必须使用单引号
SELECT 数字;
SELECT '字符';

查询表达式：

SELECT 表达式;

查询函数：

SELECT 函数();

起别名：

#方法一
#建议别名加双引号
SELECT 列名 AS 别名
FROM 表名;

#方法二
SELECT 列名 别名
FROM 表名;

如果查询的字段有重名的情况，使用别名可以进行区分

去重：

SELECT DISTINCT 列名
FROM 表名;

+号的作用：只有运算符一个功能

• 如果两个操作数都为数值型，则做加法运算
• 如果一方为字符型，试图将字符型转换为数值型
  • 如果转换成功，则继续做加法运算
  • 如果转换失败，则将字符型转换为零
• 如果其中一方为null，则结果一定为null
• 如果希望使用字符相加，则使用CONCAT(str1,str2...)函数（详见后面章节）
• 如果列存在为null数值和不为null的数值则可以使用IFNULL(列名, 为null时自定义数值)

条件查询

语法：

SELECT 列名
FROM 表名
WHERE 筛选条件;

筛选条件分类：

• 按条件表达式筛选
  • 条件运算符：=、>、<、>=、<=、!=（<>）
• 按逻辑表达式筛选
  • 逻辑运算符：&&（and）、||（or）、!（not）
• 模糊查询：
  • like、between、in、is null

按条件表达式筛选：

SELECT 列名
FROM 表名
WHERE 条件表达式;

按逻辑表达式筛选：

SELECT 列名
FROM 表名
WHERE 逻辑表达式;

模糊查询：

• LIKE关键字：

  SELECT 列名
  FROM 表名
  WHERE 列名 LIKE '%可能包含的字符%';

  • 一般和通配符搭配使用

    • % 任意多个字符，包含0个字符
    • _ 任意单个字符

    转义使用反斜杠\，或使用自定义转移符号ESCAPE '自定义转移符号'

• BETWEEN AND关键字：

  SELECT 列名
  FROM 表名
  WHERE 列名 BETWEEN 数值 AND 数值;

  • 使用BETWEEN AND可以调高代码简洁度
  • 包含临界值，且两个临界值不可以颠倒

• IN关键字：

  SELECT 列名
  FROM 表名
  WHERE 列名 IN(数值1, 数值2...);

  • 使用IN可以提高代码简洁度
  • IN列表的值类型必须一致或兼容（’123’ -> 123）
  • 不可以使用通配符

• IS NULL关键字：

  SELECT 列名
  FROM 表名
  WHERE 列名 IS NULL;

  • = NULL等于号不能判断NULL值
  • 可读性较高

• 安全等于：<=>

  • 判断的为是否等于的值，可以判断NULL值和标准值
  • 可读性较差，一般不使用

排序查询

语法：

SELECT 列名
FROM 表名
ORDER BY 列名 ASC(升序)|DESC(降序);

如果不进行升降序约束，默认条件下为升序排序

ORDER BY子句一般是放在查询语句的最后面，LIMIT子句除外

排序查询的常用情况：

• 排序查询与条件查询进行结合：

  SELECT 列名
  FROM 表名
  WHERE 筛选条件
  ORDER BY 列名 ASC(升序)|DESC(降序);

• ORDER BY后可以为表达式：

  SELECT 列名
  FROM 表名
  ORDER BY 表达式 ASC(升序)|DESC(降序);

• ORDER BY后可以为别名：

  SELECT 列名
  FROM 表名
  ORDER BY 别名 ASC(升序)|DESC(降序);

• ORDER BY后可以为函数：

  SELECT 列名
  FROM 表名
  ORDER BY 函数 ASC(升序)|DESC(降序);
  
  #例
  SELECT 列名
  FROM 表名
  ORDER BY LENGTH() ASC(升序)|DESC(降序);

• 按多个字段排序：

  SELECT 列名
  FROM 表名
  ORDER BY 字段1 ASC(升序)|DESC(降序), 字段2 ASC(升序)|DESC(降序)...;

常见函数

将一组逻辑语句封装在方法体中，对外提供方法接口。隐藏了实现细节、提高了代码的复用性

函数调用：

SELECT 函数名(实参列表)
FROM 表名;

分类：单行函数（concat、length、ifnull等）、分组函数（统计使用，又称为统计函数、聚合函数、组函数）

单行函数

字符函数：

• LENGTH(str)获取参数值的字节个数
• CONCAT(str)拼接字符串
• UPPER(str) LOWER(str)大写小写转换
• SUBSTR(str1, pos[, len])截取字符串
• INSTR(str1, str2)返回子串第一次在主串中出现的索引位置
• TRIM(str1 FROM str2)将str2中开头和结尾的str1去除
• LPAD(str, len, padstr)总字符个数为len个，在str左侧使用padstr填充
• RPAD(str, len, padstr)总字符个数为len个，在str右侧使用padstr填充
• REPLACE(str, from_str, to_str)使用to_str替换str中的from_str

数学函数：

• round(x[, d])四舍五入，d为小数点后保留位数
• CEIL(x)向上取整
• FLOOR()向下取整
• TRUNCATE(x, d)截断，x小数点后保留d位
• MOD(n, m)取余运算，n % m
• RAND()返回随机数，0-1之间

日期函数：

• NOW()返回当前系统日期+时间、

• CURDATA()返回当前系统日期，不包含时间

• CURTIME()返回当前系统时间，不包含日期

• YEAR(date)返回date的年份

• MONTH(date)返回date的月份、MONTHNAME(date)返回date的月份（英文）

  除年（year）、月（month）外，还包含日（day）、小时（hour）、分钟（minute）、秒（second）

• STR_TO_DATE(str, '%m-%d-%Y')将日期格式的字符转换为日期类型

  格式符	功能
  %Y	四位的年份
  %y	两位的年份
  %m	月份（01， 02，…）
  %c	月份（1，2，…）
  %d	日（01，02，…）
  %H	小时（24小时制）
  %h	小时（12小时制）
  %i	分钟（00，01，…59）
  %s	秒（00，01，…59）

• DATE_FORMAT('年-月-日', '%Y年%m月%d日')将日期格式类型转换为日期字符串

• datediff(str1, str2)返回两个日期相差天数

其他函数

• VERSION();查看当前版本号
• DATABASE();查看当前使用数据库
• USER();当前用户
• PASSWORD()返回该字符的密码形式
• MD5()返回该字符的MD5加密形式

流程控制函数

• if函数：IF(expr1, expr2, expr3)如果条件1成立返回表达式2，如果条件不成立返回表达式3

• case函数：

  • 类似于switch case的效果

      CASE
      	需要判断的表达式
      WHEN 常量1 THEN 语句或值1;
      WHEN 常量2 THEN 语句或值2;
      ...
      ELSE
      	语句或值n;
    END

    then后的语句为值时不需要分号

  • 多重if

    CASE
    WHEN 条件1 THEN 语句或值1;
    WHEN 条件2 THEN 语句或值2;
    ...
    ELSE
    	语句或值n;
    END

分组函数

用作统计使用，又称为聚合函数或统计函数或组函数

• SUM([DISTINCT] expr)求和
• AVG([DISTINCT] expr)平均值
• MIN([DISTINCT] expr)最小值
• MAX([DISTINCT] expr)最大值
• COUNT([DISTINCT] expr, [expr...])计算个数（非空）

参数支持类型：

sum、avg一般用于处理数值型计算

min、max、count支持处理所有类型

所有分组函数使用时忽略null值

与distinct搭配使用，在运算时将所有重复值进行去除

效率：COUNT(*)一般用于统计表的行数

• MyISAM存储引擎下，COUNT(*)效率最高
• InnoDB存储引擎下，COUNT(*)与COUNT(1)效率相同

和分组函数一同查询的字段要求是group by后的字段

分组查询

语法：

SELECT
	查询列表
FROM
	表名
GROUP BY
	列名;

一般要求查询列表中的元素必须出现在group by中，否则将会导致分组出错

根据筛选数据源，对筛选条件分为两类：

• 分组前的筛选：

  #数据源为原始表，使用WHERE分组前筛选
  SELECT
  	查询列表
  FROM
  	表名
  WHERE
  	条件表达式
  GROUP BY
  	列名;

• 分组后的筛选：

  #数据源为分组后的结果集，使用HAVING分组后筛选
  SELECT
  	查询列表
  FROM
  	表名
  GROUP BY
  	列名
  HAVING
  	筛选条件;

能使用分组前筛选的，优先考虑分组钱筛选

按多个字段进行分组：

SELECT
	查询列表
FROM
	表名
GROUP BY
	列名1, 
	列名2...;

对分组结果进行排序：

SELECT
	查询列表
FROM
	表名
GROUP BY
	列名
[ORDER BY 列名];

连接查询

又称为多表查询，当查询的字段来自于多个表时，会使用连接查询

笛卡尔乘积现象：表1有m行，表2有n行，结果有m*n行

一般使用sql99标准（不支持全外连接）

sql99的语法：

SELECT
	查询列表
FROM
	表1 别名
[连接类型] JOIN 表2 别名 ON 连接条件
[WHERE 筛选条件]
[GROUP BY 分组]
[HAVING 筛选条件]
[ORDER BY 排序列表];

连接类型：内连接（inner）、左外连接（left[outer]）、右外连接（right[outer]）、全外连接（full[outer]）、交叉连接（cross）

• 内连接：

  • 等值连接（sql92）：

    #连接条件为=
    SELECT
    	查询列表
    FROM
    	表名1, 表名2...
    WHERE
    	等值连接条件;

    SELECT
    	查询列表
    FROM
    	表名1
    INNER JOIN 表名2 ON 连接条件

    • 为解决多表中的字段重名问题，往往为表起别名，提高语义性
    • 表的顺序可以无要求
    • 多表连接的结果为多表的交集部分，n表连接至少需要n-1个连接条件

  • 非等值连接：

    #连接条件不为=
    SELECT
    	查询列表
    FROM
    	表名1,
    	表名2
    	...
    WHERE
    	等值连接条件;

  • 自连接：

    SELECT
    	查询列表
    FROM
    	表 别名1,
    	表 别名2,
    	...
    WHERE
    	等值连接条件;

    自己与自己连接（一定要取别名）

• 外连接：

  查询一个表中有，另外一张表中没有的记录（两个表的差集）

  外连接的查询结果为主表中所有记录

  若从表中有与主表匹配的值则显示其匹配值

  若从表中没有与主表匹配的值则显示null

  • 左外连接：left join左边的是主表
  • 右外连接：right join右边的是主表
  • 全外连接：并集结果

• 交叉连接：笛卡尔乘积

子查询（内查询）

出现在其他语句中的select语句，称为子查询也称为内查询

外部的查询语句，称为主查询或外查询

按子查询出现的位置：

• select后面：仅支持标量子查询
• from后面：支持表子查询
• where或having后面：标量子查询、列子查询、行子查询
• exists（相关子查询）后面：表子查询

按结果集的行列数不同：

• 标量子查询：结果集只有一行一列
• 列子查询：结果集只有一列多行
• 行子查询：结果集有一行多列
• 表子查询：结果集一般为多行多列

where或having

标量子查询：单行子查询

#WHERE
SELECT
	查询列表
FROM
	表名
WHERE
	列名 单行操作符 (
        #标量查询内部的子查询结果为一行一列
        SELECT 查询列表
        FROM 表名
        WHERE 条件
);

#HAVING
SELECT
	查询列表
FROM
	表名
GROUP BY
	列名	#分组
HAVING
	列名 单行操作符 (
        #标量查询内部的子查询结果为一行一列
        SELECT 查询列表
        FROM 表名
        WHERE 条件
);

列子查询：多行子查询

SELECT
	查询列表
FROM
	表名
WHERE
	列名 多行操作符 (
    	#列查询内部的子查询结果为多行一列
		SELECT DISTINCT	查询列表
   		FROM 表名
    	WHERE 条件
);

行子查询：一列多行（有局限性，不建议使用）

特点：

• 子查询放在小括号内
• 子查询一般放在条件右侧
• 标量子查询一般搭配单行操作符使用
  • 单行操作符< > >= <= = <>
• 列子查询一般搭配多行操作符使用
  • 多行操作符：IN（等于列表中任意一个）、ANY/SOME（返回其中任意一个值进行比较）、ALL（返回所有值进行比较）
• 子查询执行优先于主查询执行

select

SELECT
	查询列表, (
        SELECT 查询列表
        FROM 表名
        WHERE 筛选条件
)
FROM
	表名;

仅支持标量子查询

####from

SELECT
	查询列表
FROM (
	SELECT 查询列表
    FROM 表名
    [WHERE]
    [GROUP BY]
    [HAVING]
) 别名
INNER JOIN
	表名
ON
	查询条件
[WHERE]
[GROUP BY]
[HAVING];

将子查询结果充当一张表，要求必须起别名

exists（相关子查询）

SELECT EXISTS(
    SELECT 查询列表
    FROM 表名
    [WHERE]
    [GROUP BY]
    [HAVING]
);

查询exists()中是否有值

分页查询

应用场景：在服务器提供大量数据时，进行多次输出

语法：

SELECT								#⑥
	查询列表
FROM								#①
	表名
[JOIN TYPE JOIN 表名2 ON 连接条件]	#②
[WHERE 筛选条件]					 #③
[GROUP BY 分组字段]				     #④
[HAVING 分组后筛选]					#⑤
[ORDER BY 排序字段]					 #⑦
LIMIT OFFSET, SIZE;					#⑧

offset要显示条目的其实索引（起始索引从0开始），如果索引值从0开始，则可以省略

size要显示的条目个数

limit语句放在查询语句的最后

联合查询

union 联合 合并：将多条查询语句的结果合并成一个结果

#查询内容1
SELECT
	查询列表
FROM
	表名

UNION
#查询内容2
SELECT
	查询列表
FROM
	表名
...;

注意事项：

• 联合查询涉及的查询列表的列数必须相同
• 要求多条查询语句的查询的每一列的类型和顺序最好一致
• union会自动去重，UNION ALL会保留重复元素

---

DML语言学习（Data Manipulation Language）

插入语句

#方法一
INSERT INTO 表名(
	列名1,
    ...
)
VALUE(
	值1,
    ...
);

#方法二
INSERT INTO 表名
SET 列名1 = 值1,
	列名2 = 值2,
	...;

mysql中数据常用类型：

• 数值类型：int、float、double
• 字符类型：varchar、blob、text
• 日期和时间类型：datetime、date、time

特点：

• 插入值的类型要与列的类型一致

• 不可以为null的列必须插入值，可以为null的列可以写null

• 列与值的个数必须一致

• 可以省略列名，默认所有列，列的顺序与表中列的顺序一致

方式一与方式二区别：

• 方式一支持批量插入，方式二不支持
• 方式一支持子查询，方式二不支持

修改语句

#修改单表记录
UPDATE 表名
SET 列 = 新值,
	列 = 新值,
	...
WHERE
	筛选条件;

#修改多表记录(拓展)
#sql92
UPDATE 表名1 别名1,
	表名2 别名2
SET 列 = 新值,
	列 = 新值,
	...
WHERE
	连接条件
AND 筛选条件;
	
#sql99
UPDATE 表名1 别名1,
	连接类型
JOIN IN 表名2 别名2 ON 连接条件
SET 列 = 新值,
	列 = 新值,
	...
WHERE
	筛选条件;

删除语句

#整行删除
#单表删除
DELETE FROM
	表名
WHERE
	筛选条件;

#方式2,直接删除表内所有数据
TRUNCATE TABLE 表名;

#多表删除(拓展)
#sql92
DELETE 别名(要删谁写谁的别名) FROM
	表名1 别名1,
	表名2 别名2...
WHERE
	连接条件
AND 筛选条件;
	
#sql99
DELETE 别名 (要删谁写谁的别名) FROM
	表名1 别名1 连接类型
JOIN IN 表名2 别名2 ON 连接条件
WHERE
	筛选条件;

---

DDL语言学习（Data Define Language）

数据定义语言：库和表的管理

库的管理、表的管理：创建（creat）、修改（alter）、删除（drop）

库的管理

库的创建

CREATE DATABASE 库名;

库的修改

rename命令在使用过程中会出现数据丢失，所以库一般不修改库名

#修改库的字符集
ALTER DATABASE 库名 CHARACTER 
SET gbk;

库的删除

DROP DATABASE [IF EXISTS] 库名;

表的管理

表的创建

CREATE TABLE [IF NOT EXISTS] 表名(
	列名 列的类型[(长度) 约束],
    ...
);

表的修改

#修改列名
ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 类型;

#修改列的类型或约束
ALTER TABLE 表名 MODIFY COLUMN 列名 新类型;

#添加新列
ALTER TABLE 表名 ADD COLUMN 列名 类型[FIRST|AFTER 列名];

#删除列
ALTER TABLE 表名 DROP COLUMN 列名;

#修改表名
ALTER TABLE 表名 RENAME TO 新表名;

表的删除

DROP TABLE [IF EXISTS] 表名;

表的复制

#仅仅复制表的结构
CREATE TABLE 新表名 LIKE 被复制表名;

#复制表的结构和数据
CREATE TABLE 新表名 SELECT
	*
FROM
	被复制表名;

#只复制部分数据
CREATE TABLE 新表名 SELECT
	*
FROM
	被复制表名
WHERE
	限制条件;

数据类型

常见的数据类型：

• 数值型：整型、小数（定点数、浮点数）
• 字符型：短文本（char、varchar）、长文本（text、blob）
• 日期型

整型

整数类型	字节	范围
Tinyint	1	有符号：-128 ~ 127、无符号：0 ~ 255
Smallint	2	有符号：-2^15^ ~ 2^15^-1、无符号：0 ~ 2^16^-1
Mediumint	3	有符号：-2^23^ ~ 2^23^-1、无符号：0 ~ 2^24^-1
Bigint	8	有符号：-2^63^ ~ 2^63^-1、无符号：0 ~ 2^64^-1
Int、integer	4	有符号：-2^31^ ~ 2^31^-1、无符号：0 ~ 2^32^-1

特点：

• 如果不设置无符号还是有符号，默认就是有符号；如果需要设置无符号，则在整数类型后追加UNSIGNED

• 如果插入的数值超出了整型的范围，则默认插入临界值（不同mysql版本可能不会插入数值直接报错）

• 如果不设置长度，则有默认长度

  长度代表了显示的最大宽度，如果不够会在0左边填充，必须搭配zerofull使用，符号类型失效

小数

浮点数类型	字节	范围
float	4	-2^128^ ~ 2^128^
double	8	-2^1024^ ~ 2^1024^
定点数类型	字节	范围
DEC(M, D)、DECIMAL(M, D)	M+2	最大取值范围与double相同，给定decimal的有效范围有M、D决定

特点：

• M整数部位+小数部位长度、D小数部位长度，不能超出范围，M、D均可以省略（定点数时M默认为10，D默认为0）
• 定点数的精度较高，要求插入数值的精度较高时应用

字符型

较短文本

字符串类型	最多字符数	描述及存储需求
char(M)	M	M为0 ~ 255之间的整数
varchar(M)	M	M为0 ~ 65535之间的整数

特点：

• char表示固定长度的字符、varchar表示可变长度的字符
• char效率较高、varchar效率较低
• char

ENUM枚举型

仅可以插入枚举中定义的元素，枚举中未定义的元素插入为空

其他

binary、varbinary用于保存较短的二进制

set用于保存集合

日期类型

日期和时间类型	字节	最小值	最大值
date	4	1000-01-01	9999-12-31
datetime	8	1000-01-01 00:00:00	9999-12-31 23:59:59
timestamp	4	19700101 080001	2038年某个时刻
time	3	-838:59:59	838:59:59
year	1	1901	2155

timestamp和实际时区有关，更能反映实际的日期，受 mysql版本和sqlmode影响较大

常见约束

一种限制，用于限制表中的数据，为了保障添表中数据的准确性和可靠性

CREATE TABLE 表名(
	字段名 字段类型 约束
)

分类：

• NOT NULL非空约束，用于保障该字段的值不能为空

• DEFAULT默认约束，用于保障该字段有默认值

• PRIMARY KEY主键约束，用于保障该字段的值具有唯一性，并且非空

• UNIQUE唯一约束，用于保障该字段的值具有唯一性，可以为空

• CHACK检查约束（mysql不支持）

• FOREIGN KEY外键约束，用于限制两个表的关系，用于保障该字段的值必须来自于主表的关联列的值

  在从表添加外键约束，用于引用主表中某列的值

添加约束的时机：

• 创建表时
• 修改表时

约束的添加分类：

• 列级约束：不支持外键约束
• 表级约束：脱离所有字段，在所有字段之后，不支持非空约束和默认约束

创建表时添加约束

#添加列级约束
CREATE DATABASE 库名;
USE 库名;
CREATE TABLE 表名(
	字段名 字段类型 约束类型,
    ...
);

直接在字段名和类型后面追加约束类型即可，不支持外键约束

#添加表级约束
CREATE DATABASE 库名;
USE 库名;
CREATE TABLE 表名(
	字段名 字段类型,
    ...,
    [CONSTRAINT 约束名] 约束类型(字段名),
    ...
);

一般情况下，非空约束、默认约束、主键约束、唯一约束使用列级约束，外键约束使用表级约束（外键约束一定要起约束名）

主键与唯一

	主键	唯一
保证唯一性	√	√
是否允许为空	×	√
一个表中有多少个	至多一个	可以有多个
是否允许组合	√	√

外键

要在从表中设置外键关系

从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求

主表中的关联列必须是一个key（主键、唯一、外键）

插入数据时，先插入主表，再插入从表

删除数据时，先删除从表，再删除主表

修改表时添加约束

#列级约束
ALTER TABLE 表名 MODIFY COLUMN 列名 新类型 约束;

#表级约束,外键
ALTER TABLE 表名 ADD [CONSTRAINT 约束名] FOREIGN KEY(列名) REFERENCES 主表的列;

修改表时删除约束

#删除默认约束,默认约束
ALTER TABLE 表名 MODIFY COLUMN 列名 新类型;

#删除主键约束
ALTER TABLE 表名 DROP PRIMARY KEY;

#删除唯一约束
ALTER TABLE 表名 DROP INDEX 约束名;

#删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 约束名;

标识列

又称为自增长列，可以不用手动插入值，系统提供默认的序列值

CREATE TABLE 表名(
	字段名 字段类型 AUTO_INCREMENT,
    ...
);

#修改自增长步长
SET auto_increment_increment = 步长;

特点：

• 标识列必须和key搭配

• 一个表中至多有一个标识列

• 标识列的类型只能是数值型，一般为int类型

• 标识列可以自定义步长，也可以自定义起始值（一般不推荐）

---

TCL语言学习（Transaction Contral Language）

事物的介绍

事务：一个或一组sql语句组成一个执行单元，每个sql语句是相互依赖的，这个执行单元要么全部执行，要不全部不执

存储引擎：

概念：在mysql中的数据用各种不同的技术存储在文件（或内存）中

通过show engines;来查看mysql支持的存储引擎

在mysql中用的最多的存储引擎有：innodb、myisam、memory等。其中innodb支持事务，而myisam、memory等不支持事务

事务的ACID属性：

• 原子性（Atomicity）

  原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生

• 一致性（Consistency）

  事务必须使数据库从一个一致性状态变换到另一个一致性状态

• 隔离性（Isolation）

  事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰

• 持久性（Durability）

  持久性是一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响

事物的创建

隐式事务：事务没有明显的开启和结束的标记，例如：insert、update、delete

显示事务：事务具有明显的开启和结束标记，必须先设置自动提交功能为禁止set autocommit = 0;

显示事务的开启步骤：

• 开启事务

  SET AUTOCOMMIT = 0;
  START TRANSACTION; #可选

• 编写事物的sql语句（select、insert、update、delete）

  语句1；

  语句2；

  或设置回滚点（savepoint 回滚点名）

• 结束事务

  commit;提交事务

  rollback;回滚事务，数据只保留到内存并不会写入数据库

  rollback to 回滚点名;

对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致一下问题：

• 脏读：对于两个事务T1、T2，T1读取了已经被T2更新但还没有被提交的字段之后，若 T2 回滚，T1读取的内容就是临时且无效的
• 不可重复读：对于两个事务T1、T2，T1读取了一个字段，然后T2 更新了该字段。之后，T1再次读取同一个字段，值就不同了
• 幻读：对于两个事务T1、T2，T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行。之后，如果 T1再次读取同一个表，就会多出几行

数据库事务的隔离性：数据库系统个必须具有隔离并发运行各事务的能力，使它们不会相互影响，避免各种并发问题

回滚点

savepoint 节点名;设置保存点

SET autocommit = 0;
START TRANSACTION;
DELECT FROM 表名 WHERE 条件;
SAVEPOINT a;
DELECT FROM 表名 WHERE 条件;
ROLLBACK TO a;

---

视图

视图的介绍

虚拟的表，和普通表一样使用

mysql5.0.1版本出现的新特性，是通过表动态生成的数据，行和列的数据来自定义视图的查询中使用的表。只保存sql逻辑，不保存查询结果

CREATE VIEW v1
AS
SELECT 列名
FROM 表名1
INNER JOIN 表名2 ON 连接条件;

SELECT * FROM v1 WHERE 条件;

应用场景：

• 多个地方用到同样的查询结果
• 该查询结果使用的sql语句比较复杂

视图的创建

SELECT VIEW 视图名
AS
查询语句

• 重用sql语句
• 简化复杂的sql操作，不必知道它的查询细节
• 保护数据提高安全性

视图的修改

# 方式一
CREATE OR REPLACE VIEW 视图名
AS
查询语句

# 方式二
ALTER VIEW 视图名
AS
查询语句

视图的删除

DROP VIEW 视图名,...

视图的查看

# 方式一
DESC 视图名
# 方式二
SHOW CREATE VIEW 视图名

视图的更新

视图支持插入、更新、删除，且操作时会直接操作原始表中，所以我们在创建视图时会对其设置权限

以下视图是不允许进行更新：

• 包含一下关键字的sql语句：分组函数、destinct、group by、having、union或union all
• select中包含子查询
• join
• from一个不能更新的视图
• where子句的子查询引用了from子句中的表

视图与表

	视图	表
创建语法的关键字	create view	create table
是否占用实际空间	只保存sql逻辑	保存数据
使用	一般只用于查询	增删改查

delete和truncate

delete支持回滚，truncate不支持回滚

---

变量

系统变量：全局变量、会话变量

自定义变量：用户变量、局部变量

系统变量

变量由系统提供，不是用户定义，属于服务器层面

使用语法：

• 查看所有的系统变量：SHOW GLOBAL|SESSION VARIABLES;
• 查看满足条件的部分系统变量：SHOW GLOBAL|SESSION VARIABLES LIKE '%char%;'
• 查看指定的某个系统变量的值SELECT @@GLOBAL|SESSION.系统变量名
• 为某个系统变量赋值SET GLOBAL|SESSION 系统变量名=值;

全局变量作用域：服务器每次启动将为所有的全局变量赋初始值，针对于所有的会话有效，但是不能跨重启

会话变量作用域：仅仅针对于当前会话（连接）有效

自定义变量

变量是用户自定义的，不是由系统提供的。使用步骤：声明、赋值、使用（查看、比较、运算等）

用户变量：

• 声明并初始化：SET @用户变量名=值;（可以在=前加:）
• 赋值（更新用户变量的值）：SET @用户变量名=值;
• 查看用户变量的值：SELECT @用户变量名

用户变量的作用域：仅仅针对于当前会话（连接）有效

局部变量：

• 声明：DECLARE 变量名 类型;
• 赋值（更新局部变量的值）：SET 局部变量名=值;
• 查看局部变量的值：SELECT 局部变量名

局部变量的作用域：仅仅在定义它的begin end中有效，且必须为begin end的第一句

---

存储过程

存储过程介绍

含义：一组预先编译好的SQL语句的集合，可以理解为批处理语句

优点：

• 提高代码的重用性

• 简化操作

• 减少了编译次数并减少了和数据库服务器的连接次数

存储过程语法

# 创建语法
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	存储过程体(一组合法有效的SQL语句)
END

注意：

• 参数列表包含三部分：参数模式、参数名、参数类型
• 参数模式：
  • IN：该参数可以作为输入，调用改参数需要传入值
  • OUT：该参数可以作为输出，调用改参数需要返回值
  • INOUT：该参数可以作为输入、输出
• 如果存储过程体仅有一句，BEGIN END可以省略，存储过程体中每条SQL语句都需要分号。存储过程结尾可以使用DELIMITER重新设置DELIMITER 结束标记

# 调用方法
CALL 存储过程名(实参列表);

IN模式

CREATE PROCEDURE p(IN name VARCHAR(20))
BEGIN
	存储过程体(一组合法有效的SQL语句)
END $

CALL p('Bob')$

OUT模式

CREATE PROCEDURE p(OUT name VARCHAR(20))
BEGIN
	SELECT 列名 INTO name
	FROM 表名
	WHERE 条件
END $

CALL p(@name)$

INOUT模式

CREATE PROCEDURE p(INOUT a INT)
BEGIN
	SET a=a*2;
END $
# 初始化变量
SET @a=10$
# 调用
CALL p(@a)$
# 查看
SELECT @a$

存储过程删除

DROP PROCEDURE 存储过程名

一次仅能删除一个存储过程

存储过程查看

SHOW CREATE PROCEDURE 存储过程名

---

函数

函数的介绍

含义：一组预先编译好的SQL语句的集合，可以理解为批处理语句

优点：

• 提高代码的重用性

• 简化操作

• 减少了编译次数并减少了和数据库服务器的连接次数

存储过程与函数的区别：

• 存储过程可以有0个返回，也可以有多个返回，适合做批量的操作
• 函数只能有1个返回，适合处理后返回一个值

函数的创建

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型

注意：

• 参数列表包含两部分：参数名、参数类型
• 函数体：肯定会有return语句
• 函数体中只有一句话可以省略BEGIN END
• 使用delimiter语句设置结束标记

函数的调用

SELECT 函数名(参数列表)

函数的查看

SHOW CREATE FUNCTION 函数名

函数的删除

DROP FUNCTION 函数名

---

流程控制结构

流程控制的介绍

顺序结构：程序从上往下依次执行

分支结构：程序从两条或多条路径中选择一条去执行

循环机构：程序在满足一定的条件基础上，重复执行一段代码

分支结构-if函数

功能：实现简单的双分支

语法：IF(表达式1,表达式2,表达式3)

执行顺序：如果表达式1成立，则IF函数返回表达式2的值，否则返回表达式3的值

分支结构-case结构

类似于switch-case语句，一般用于实现等值判断或区间判断

语法：

# 作为表达式
# 等值
CASE 变量|表达式|字段
WHEN 要判断的值 THEN 返回的值1
WHEN 要判断的值 THEN 返回的值2
...
ELSE 返回的值n
END;
# 区间
CASE
WHEN 要判断的条件1 THEN 返回的值1
WHEN 要判断的条件2 THEN 返回的值2
...
ELSE 返回的值n
END;

# 作为独立语句
# 等值
CASE 变量|表达式|字段
WHEN 要判断的值 THEN 返回的语句1;
WHEN 要判断的值 THEN 返回的语句2;
...
ELSE 返回的语句n;
END CASE;
# 区间
CASE
WHEN 要判断的条件1 THEN 返回的语句1;
WHEN 要判断的条件2 THEN 返回的语句2;
...
ELSE 返回的语句n;
END CASE;

特点：

• 可以作为表达式，嵌套在其他语句中使用，可以放在任何地方，BEGIN END中或BEGIN END的外面
• 可以作为独立的语句去使用，只能放在BEGIN END中使用

分支结构-if结构

功能：实现多重分支

IF 条件1 THEN 语句1;
ELSEIF 条件2 THEN 语句2;
...
ELSE 语句n;
END IF;

只能应用于BEGIN END中

循环结构

分类：while、loop、repeat

循环控制：iterate类似于continue、leave类似于break

# while先判断后执行
[标签:] WHILE 循环条件 DO
	循环体
END WHILE [标签];

# loop可以模拟死循环
[标签:] LOOP
	循环体;
END LOOP [标签];

# repeat先执行后判断
[标签:] REPEAT
	循环体;
UNTIL 结束循环的条件
END REPEAT[标签];