MySQL数据库操作教程

共计 11113 个字符，预计需要花费 28 分钟才能阅读完成。

MySQL 数据库操作系列教程，希望对大家有所帮助。

约束是为了保证数据的完整性和一致性

-- 对一个数据列建立的约束，就是列级约束
-- 对多个数据列建立的约束，就是表级约束
-- 列级约束既可以在列定义时声明，也可以在列定义后声明，
-- 表级约束只能在列定义后声明

-- 注意！
--NOT NUll 和 DEFAULT 没有表级约束

约束类型 包括：

NOT NULL（非空约束）
PRIMARY KEY（主键约束）
UNIQUE KEY（唯一约束）
DEFAULT（默认约束）
FOREIGN KEY（外键约束）

CREATE TABLE users(id SMALLINT UNSIGNED PRIMARY KEY);
-- 主键：PRIMARY KEY
-- 主键约束，每张数据表只可以有一个主键，
-- 主键保证记录的唯一性，且其自动为 NOT NULL

CREATE TABLE users(name VARCHAR(20) NOT NULL UNIQUE KEY);
-- 唯一约束：UNIQUE KEY
-- 唯一约束可以保证记录的唯一性，且唯一约束字段可以为空
-- 每张数据库表可以存在多个唯一约束

CREATE TABLE users(sex ENUM('1','2','3') DEFAULT '3');
-- 默认约束：DEFAULT
-- 默认值，当插入记录时，如果没有明确为字段赋值，则自动赋予默认值
--ENUM 是枚举，表示用户只能从 3 个选项中选一个字段赋值，详细内容可自行搜索

CREATE TABLE provinces(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(20) NOT NULL
);
-- 父表，省份信息
CREATE TABLE users(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
uname VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED,
FOREIGN KEY (pid) REFERENCES provinces (id)
);
-- 子表，用户信息，用户 pid 对应 省份 id

-- 外键约束：FOREIGN KEY

-- 外键约束要求：
--1. 父表和子表必须使用相同的存储引擎，而且禁止使用临时表
--2. 数据表的存储引擎只能为 InnoDB
/*
3. 外键列和参照列必须具有相似的数据类型。其中数字的长度或者是否有符号位都必须相同
  但是，若是字符的长度，则可以不同
*/
--4. 外键列和参照列必须创建索引，如果外键列不存在索引。！MySQL 将自动创建索引！

外键约束 的参照操作及功能:

1.CASCADE：从父表删除或更新且自动删除或更新子表中匹配的行
2.SET NULL：从父表删除或更新行，并设置子表中的外键列为 NULL
(ps.如果使用该选项，必须保证子表列没有指定 NOT NULL)
3.RESTRICT：拒绝对父表的删除或更新操作
4.NO ACTION：标准 SQL 的关键字，在 MySQL 中与 RESTRICT 相同

-- 例子
CREATE TABLE users(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
uname VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED,
FOREIGN KEY (pid) REFERENCES provinces (id) ON DELETE CASCADE
);
--ON DELETE 意思为删除操作发生时

更多详情见请继续阅读下一页的精彩内容：http://www.linuxidc.com/Linux/2017-02/141092p2.htm

[]中括号内为可省略字符

-- 添加主键约束
ALTER TABLE 表名 ADD [CONSTRAINT [symbol(约束名)]] PRIMARY KEY [index_type](index_col_name);
-- 约束名为这个约束的名称，可略
--index_col_name，即为添加约束的列名，指定其进行添加操作，注意有小括号。

-- 删除主键约束(一张表主键只有一个，不需指定)
ALTER TABLE 表名 DROP PRIMARY KEY;

-- 添加主键约束 例(假设前置条件都已定义)：
ALTER TABLE users ADD CONSTRAINT PRIKEY_users_id PRIMARY KEY (id);

-- 添加唯一约束
ALTER TABLE 表名 ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,..);

-- 删除唯一约束
ALTER TABLE 表名 DROP {INDEX|KEY} index_name;
-- 为什么指定的是索引名，是因为一张表会有多个唯一约束，且需要注意的是，删除的是唯一约束这个属性，而不是整个字段！

-- 添加唯一约束 例(假设前置条件都已定义)：
ALTER TABLE 表名 ADD UNIQUE (name);

-- 删除唯一约束 例(假设前置条件都已定义)：
SHOW INDEXES FROM 表名 \G;
-- 找到要删除唯一约束属性的列，找到 key_name, 即索引名
ALTER TABLE 表名 DROP INDEX 索引名;

-- 添加外键约束
ALTER TABLE 表名 ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,..) reference_definition;

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

-- 添加外键约束 例(假设前置条件都已定义)：
ALTER TABLE users ADD FOREIGN KEY (vid) REFERENCE VIP (id);
--VIP 是父表，users 是子表

-- 删除外键约束 例(假设前置条件都已定义)：
SHOW CREATE TABLE 表名;
-- 查看外键约束名称(若没有设置则系统自动设置)，在 CONSTRAINT 后，有个名称即为外键约束名
ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;
SHOW CREATE TABLE 表名;
-- 发现外键约束已经删除，但是还会存在索引，若想删除索引，则：
ALTER TABLE 表名 DROP INDEX 索引名;

-- 添加 / 删除默认约束
ALTER TABLE 表名 ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT};

-- 添加默认约束 例(假设前置条件都已定义)：
ALTER TABLE users ALTER age SET DEFAULT 16;
--DROP 则无需指定默认值，直接后接 DEFAULT

MySQL 数据库操作系列教程，希望对大家有所帮助。

约束是为了保证数据的完整性和一致性

-- 对一个数据列建立的约束，就是列级约束
-- 对多个数据列建立的约束，就是表级约束
-- 列级约束既可以在列定义时声明，也可以在列定义后声明，
-- 表级约束只能在列定义后声明

-- 注意！
--NOT NUll 和 DEFAULT 没有表级约束

约束类型 包括：

NOT NULL（非空约束）
PRIMARY KEY（主键约束）
UNIQUE KEY（唯一约束）
DEFAULT（默认约束）
FOREIGN KEY（外键约束）

CREATE TABLE users(id SMALLINT UNSIGNED PRIMARY KEY);
-- 主键：PRIMARY KEY
-- 主键约束，每张数据表只可以有一个主键，
-- 主键保证记录的唯一性，且其自动为 NOT NULL

CREATE TABLE users(name VARCHAR(20) NOT NULL UNIQUE KEY);
-- 唯一约束：UNIQUE KEY
-- 唯一约束可以保证记录的唯一性，且唯一约束字段可以为空
-- 每张数据库表可以存在多个唯一约束

CREATE TABLE users(sex ENUM('1','2','3') DEFAULT '3');
-- 默认约束：DEFAULT
-- 默认值，当插入记录时，如果没有明确为字段赋值，则自动赋予默认值
--ENUM 是枚举，表示用户只能从 3 个选项中选一个字段赋值，详细内容可自行搜索

CREATE TABLE provinces(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(20) NOT NULL
);
-- 父表，省份信息
CREATE TABLE users(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
uname VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED,
FOREIGN KEY (pid) REFERENCES provinces (id)
);
-- 子表，用户信息，用户 pid 对应 省份 id

-- 外键约束：FOREIGN KEY

-- 外键约束要求：
--1. 父表和子表必须使用相同的存储引擎，而且禁止使用临时表
--2. 数据表的存储引擎只能为 InnoDB
/*
3. 外键列和参照列必须具有相似的数据类型。其中数字的长度或者是否有符号位都必须相同
  但是，若是字符的长度，则可以不同
*/
--4. 外键列和参照列必须创建索引，如果外键列不存在索引。！MySQL 将自动创建索引！

外键约束 的参照操作及功能:

1.CASCADE：从父表删除或更新且自动删除或更新子表中匹配的行
2.SET NULL：从父表删除或更新行，并设置子表中的外键列为 NULL
(ps.如果使用该选项，必须保证子表列没有指定 NOT NULL)
3.RESTRICT：拒绝对父表的删除或更新操作
4.NO ACTION：标准 SQL 的关键字，在 MySQL 中与 RESTRICT 相同

-- 例子
CREATE TABLE users(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
uname VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED,
FOREIGN KEY (pid) REFERENCES provinces (id) ON DELETE CASCADE
);
--ON DELETE 意思为删除操作发生时

更多详情见请继续阅读下一页的精彩内容：http://www.linuxidc.com/Linux/2017-02/141092p2.htm

[]中括号内为可省略字符

1. 添加 / 删除列

-- 添加列
ALTER TABLE 表名 ADD [COLUMN] 添加列名 添加列类型 [FIRST|AFTER 列名称];
--FIRST 即为添加至最前，AFTER 即为在指定列的后面
-- 若不填写位置，则默认添加至最后方

-- 例(假设前置条件都已定义)：
ALTER TABLE users ADD name VARCHAR(20) NOT NULL AFTER id;

-- 添加多列与添加单列有两个不同：
--1. 要在添加列名与列类型时候，加上小括号 () 隔开
--2. 添加多列的时候无法指定位置

-- 删除列
ALTER TABLE 表名 DROP [COLUMN] 列名;

-- 删除单列与多列例(假设前置条件都已定义)：
ALTER TABLE users DROP name;
ALTER TABLE users DROP name,DROP age;

-- 注意！删除同时也可以添加，只要使用逗号 "," 隔开就好了

2. 修改数据表

-- 修改列定义
ALTER TABLE 表名 MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name];

-- 修改列位置例，也可以更换类型(假设前置条件都已定义)：
ALTER TABLE 表名 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;

-- 修改列名称(CHANGE 既可以修改列定义，也可以修改列名称)
ALTER TABLE 表名 CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name];

-- 修改列名称例(假设前置条件都已定义)：
ALTER TABLE 表名 CHANGE pw password TINYINT UNSIGNED NOT NULL;

-- 修改数据表名称
-- 方法 1.
ALTER TABLE 表名 RENAME[TO|AS] new_tb_name
-- 方法2.
RENAME TABLE 表名 TO new_tb_name [,tb_name2 TO new tb_name2]...

[]中括号内为可省略字符

/* 第一种插入方式 */

-- 语法结构
INSERT [INTO] 表名 [(列名,..)] {VALUES|VALUE} ({expr | DEFAULT},..),(..),..;
/*
1. 列名可以省略，当列名省略时，即所有字段都需要赋值；2. 插入值时，可以用表达式、函数等；3. 同时插入多条记录，记得逗号；*/

-- 实例(假设前提条件已满足)
INSERT users VALUES(DEFAULT,'lucy',6*6),(NULL,'jack',md5('36'));
/*
1. 有默认值时，可以使用 DEFAULT;
2. 可以书写表达式、函数等，来写入值
3. 如有自动编号等属性时，可以使用 NULL；4. 插入多条语句，记得逗号
*/

/* 第二种插入方式 */

-- 语法结构
INSERT [INTO] tb_name SET col_name={expr|DEFAULT},..;
/*
与第一种方式的区别在于:
1. 此方法可以使用子查询(SubQuery);
2. 一次只可以插入一条记录;
*/

-- 实例(假设前提条件已满足)
INSERT users SET name='Tom',age=13;

/* 第三种插入方式 */

-- 语法结构
INSERT [INTO] tb_name [(col_name,..)] SELECT ...;
/* 此方法可以将查询的结果插入到指定数据表 */

-- 实例(假设前提条件已满足)
INSERT list SELECT name FROM users WHERE age >= 18;

-- 将查询结果写入数据表，例(假设前提条件已满足)：
INSERT new_tb(new_name) SELECT old_name FROM old_tb GROUP BY old_name;

[]中括号内为可省略字符

1. 单表更新

-- 更新记录(单表更新)

-- 语法格式
UPDATE [LOW_PRIORITY] [IGNORE] table_referrence SET col_name1={expr1|DEFAULT}[,col_name2={expr2|DEFAULT}].. [WHERE where_condition];
-- 若是省略 where 条件，那么所有记录全部更新


-- 实例(假设前提条件已满足)：

UPDATE users SET mon = mon + 1;
-- 单列，表中记录全部更新

UPDATE users SET mon = mon + age,sex = 0 WHERE id>3;
-- 多列，并且有 WHERE 条件

2. 单表删除

-- 删除记录(单表删除)

-- 语法格式
DELETE FROM tb_name [WHERE where_condition];
-- 若是省略 where 条件，那么所有记录全部删除


-- 实例(假设前提条件已满足)：
DELETE FROM users WHERE id = 1;

3.WHERE

WHERE 条件表达式
对记录进行过滤，如果没有指定 WHERE 子句，则显示所有记录。
在 WHERE 表达式中，可以使用 MySQL 支持的函数或运算符。

[]中括号内为可省略字符

查找 就属于非常常见的操作了。
要注意的是，SELECT 本身是在大部分语句执行完之后执行，
所以在有取别名、WHERE 等条件时，注意命令执行顺序

-- 语法格式
SELECT select_expr [,select_expr ..]
[FROM table_referrences
 [WHERE where_condition]
 [GROUP BY {col-name|position} [ASC|DESC],..]
 [HAVING where_condition]
 [ORDER BY {col_name|expr|position} [ASC|DESC],..]
 [LIMIT {[offset,] row_count|row_count OFFSET offset}]
]

查询表达式，select_expr

1. 每一个表达式表示想要的一列，必须有至少一个。
2. 多个列之间可以用英文逗号分隔。
3. 星号 (*) 表示所有列。tb_name.* 可以表示命名表的所有列。
4. 查询表达式可以使用[AS] alias_name 为其赋予别名。
5. 别名可以用于 GROUP BY，ORDER BY 或 HAVING 子句。

-- 实例(假设前提条件已满足)

-- 指定属性查询
SELECT name,age FROM users;
SELECT age,name FROM users;
-- 上面两个例子的属性顺序颠倒，同时也会影响输出的顺序不同

-- 有时出现 FROM 多张表的情况时，使用以下格式，可以不容易出错
SELECT users.name,users.age FROM users;

-- 取别名
SELECT id AS userId, name AS userName FROM users;
-- 注意以下情况

SELECT id name FROM users;
-- 这种情况不会打印两列数据，而是给 id 取别名为 name，输出一列

[]中括号内为可省略字符

1.GROUP BY(对查询结果分组)

-- 语句格式
[GROUP BY {col_name|position} [ASC|DESC],..]
/*
ASC: 升序(默认)
DESC: 降序
*/

-- 实例(假设前提条件已满足):
SELECT age FROM users GROUP BY age;
-- 指定名称
SELECT age FROM users GROUP BY 1;
-- 指定位置

2.HAVING(设置分组条件)

-- 语句格式
[HAVING where_condition]
/*
HAVING 条件要么为一个聚合函数(如最大、小值，平均值等)，要么保证 HAVING 后的判断属性，必须出现在 SELECT 后的条件中
*/

-- 实例(假设前提条件已满足)
SELECT sex FROM users GROUP BY sex HAVING age > 18;
-- 错误示例
SELECT sex,age FROM users GROUP BY sex HAVING age > 18;
-- 在 SELECT 后加入 age 即可
SELECT sex,age FROM users GROUP BY sex HAVING count(id) > 2;
-- 在后面加入聚合函数，也可以

3.ORDER BY(对查询结果排序)

-- 语句格式
[ORDER BY {col_name|expr|position} [ASC|DESC],..]

-- 实例(假设前提条件已满足)
SELECT * FROM users ORDER BY id DESC;
-- 单个属性

SELECT * FROM users ORDER BY age,id DESC;
-- 多个属性(若是第一个条件可排列好，则忽略后面条件，以此往后类推)

4.LIMIT(限制查询结果返回的数量)

-- 语句格式
[LIMIT {[offset,] row_count|row_count OFFSET offset}]


-- 实例(假设前提条件已满足)

SELECT * FROM users LIMIT 2;
-- 限制返回数量

SELECT * FROM users ORDER BY id DESC LIMIT 3,2;
-- 从 3 开始，返回 2 行结果
-- 注意，SELECT 编号是从 0 开始的(即第一行编号为 0)，这里要特别注意

1. 子查询定义

子查询 (SubQuery) 是指出现在其他 SQL 语句内的 SELECT 子句。

-- 例
SELECT * FROM t1 WHERE col1 = (SELECT col2 FROM t2);
/*
1.SELECT * FROM t1，称为 Outer Query/Outer Statement(外层查询 / 外层声明)
2.SELECT col2 FROM t2，称为 SubQuery
*/

注意：

1. 子查询指嵌套在查询内部，且必须始终出现在圆括号内。
2. 子查询可以包含多个关键字或条件，如：DISTINCT、GROUP BY、ORDER BY、LIMIT、函数等等。
3. 子查询的外层查询可以是：SELECT，INSERT，UPDATE，SET 或 DO。
4. 子查询返回值，可以为标量、一行、一列或子查询。

2. 比较运算符的子查询

-- 语法结构
operand comparison_operator subquery;

-- 示例(假设所有前提条件都已满足)

SELECT ROUND(AVG(price),2) FROM Store;
-- 求出价格平均数，且保留小数点后两位
SELECT id,name,price FROM Store WHERE price >= (SELECT ROUND(AVG(price),2) FROM Store);
-- 使用子查询，求出大于等于平均价格的商品

SELECT * FROM Store WHERE categ = '彩电'\G;
-- 注意，查询可以使用中文

注意，在 比较 的时候，可能会出现返回多条结果
若是使用返回多条结果的子查询去 比较 则会报错
因为系统不知道，你是要和哪条返回结果去 比较
所以，就有了‘修饰比较修饰符’

--ANY,SOME,ALL
operand comparison_operator ANY (subquery);
operand comparison_operator SOME (subquery);
operand comparison_operator ALL (subquery);

ANY	SOME	ALL
>、>=	最小值	最小值
<、<=	最大值	最大值
=	任意值	任意值
<>、!=

-- 示例(假设子查询有多个返回值)
SELECT name,price FROM Store WHERE price > ANY (SELECT price FROM Store WHERE categ = '彩电');
-- 对照上表，可以看出彩电返回的是返回值中的最小值

3.[NOT] IN 的子查询

-- 语法结构
operand comparison_operator [NOT] IN (subquery);
--=ANY 运算符与 IN 等效
--!=ALL 或 <>ALL 运算符与 NOT IN 等效

4.[NOT] EXISTS 的子查询(不常用)

如果 子查询返回任何行，EXISTS 将返回 TRUE；
否则 为 FALSE

1. 多表更新

-- 基本格式
UPDATE table_references SET col_name1 = {expr1|DEFAULT} [,col_name2 = {expr2|DEFAULT}]... [WHERE where_condition];

-- 语法结构
table_reference {[INNER|CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN} table_reference ON conditional_expr;

-- 连接类型
/*
INNER JOIN, 内连接
  在 MySQL 中，JOIN，CROSS JOIN 和 INNER JOIN 是等价的
LEFT [OUTER] JOIN，左外连接
RIGHT [OUTER] JOIN，右外连接
*/

-- 实例(假设前提条件已满足)
UPDATE tb1 INNER JOIN tb2 ON tb1_att = tb2_att SET tb1_att = tb2_id;
--tb1 内连接 tb2，若 tb1_att=tb2_att，那么设置 tb1_att 为 tb2_id

2. 多表创建写入

若是没有创建表，那么直接在创建的时候写入数据，一步到位

-- 创建数据表同时将查询结果写入到数据表
CREATE TABLE [IF NOT EXISTS] tb_name [(create_definition,..)] select_statement;

-- 实例(假设前提条件已满足)
CREATE TABLE new_tb
(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(40) NOT NULL
)
SELECT name FROM old_tb GROUP BY old_name;

3. 多表删除

-- 语法结构
DELETE tb_name[.*][,tb_name[.*]]...
FROM table_references
[WHERE where_condition];

-- 删除时，有时会用到查找重复的数据
-- 查找重复至少一次的数据(假设前提条件已满足)
SELECT goods_id,goods_name FROM tb_goods GROUP BY goods_name HAVING count(goods_name) > 1;

-- 连接语法结构
table_reference {[INNER|CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN} table_reference ON conditional_expr;
/*
ps. 在 MySQL 中，JOIN，CROSS JOIN 和 INNER JOIN 是等价的
LEFT [OUTER] JOIN，左外连接
RIGHT [OUTER] JOIN，右外连接
ON 关键字后面设定连接条件，也可以使用 WHERE 来替代
(通常使用 ON 关键字来设定连接条件，使用 WHERE 关键字来进行结果集记录的过滤)
*/

-- 数据表参照
table_reference tb_name [[AS] alias] | table_subquery [AS] alias;
-- 数据库可以使用 tb_name AS alias_name
-- 或者 tb_name alias_name 赋予别名
--table_subquery 可以作为子查询使用在 FROM 子句中，这样的子查询必须为其赋予别名

-- 内连接，显示左表及右表符合连接条件的记录

-- 示例操作(假设前提条件已满足)
SELECT book_id,book_name,cate_name FROM tb_book INNER JOIN tb_book_cates ON tb_book.cate_id = tb_book_cates.cate_id;

-- 外连接
/*
A LEFT JOIN B join_condition(拿左外连接作说明)
数据表 B 的结果集依赖数据表 A
数据表 A 的结果集根据左连接条件依赖所有数据表(B 表除外)
左外连接条件决定如何检索数据表 B(在没有指定 WHERE 条件的情况下)
如果数据表 A 的某条记录符合 WHERE 条件，但是在数据表 B 不存在符合连接条件的记录，将生成一个所有列为空的额外的 B 行
*/
-- 左外连接，显示左表的全部记录及右表符合连接条件的记录
-- 示例操作(假设前提条件已满足)
SELECT book_id,book_name,cate_name FROM tb_book LEFT JOIN tb_book_cates ON tb_book.cate_id = tb_book_cates.cate_id;

-- 右外连接，显示右表的全部记录及左表符合连接条件的记录
-- 示例操作(假设前提条件已满足)
SELECT book_id,book_name,cate_name FROM tb_book RIGHT JOIN tb_book_cates ON tb_book.cate_id = tb_book_cates.cate_id;

-- 多表连接(两张表以上)

-- 示例操作(假设前提条件已满足)
SELECT stu_id,stu_name,stu_sex,stu_cid,stu_pid FROM tb_stu AS s
INNER JOIN tb_stu_class AS c ON s.stu_cid = c.class_id
INNER JOIN tb_stu_province AS p ON s.stu_pid = p.pro_id;