MySQL 的 SQL 笔记

主键

选取主键的一个基本原则是:不使用任何业务相关的字段(id)作为主键。修改了主键,会造成一系列的影响

类型:

  1. 自增整数类型(id):BIGINT NOT NULL AUTO_INCREMENT
  2. 全局唯一 GUID 类型

注意:如果使用 INT 自增类型,那么当一张表的记录数超过 2147483647(约21亿)时,会达到上限而出错。使用 BIGINT自增类型则可以最多约 922 亿亿条记录。

索引

主键默认含有索引。

即该列的值如果越互不相同,那么索引效率越高。

ALTER TABLE students
ADD INDEX idx_score (score);

多个索引

ALTER TABLE students
ADD INDEX idx_name_score (name, score);

唯一索引保证唯一约束,又是索引,如身份证号、邮箱等字段

ALTER TABLE students
ADD UNIQUE INDEX uni_email (email)

只建唯一约束,不建索引

ALTER TABLE students
ADD CONSTRAINT uni_email UNIQUE (email)

查询数据

条件查询

SELECT * FROM <表名> WHERE <条件表达式> (AND / OR)
条件 表达式举例1 表达式举例2 说明
使用 <> 判断不相等 score <> 80 name <> ‘abc’ 常用 <> 代替 Not
使用 LIKE 判断相似 name LIKE ‘ab%’ name LIKE ‘%bc%’ %表示任意字符,例如’ab%’将匹配’ab’,’abc’,’abcd’

投影查询

SELECT id, score points, name FROM students; // points 为别名

排序

SELECT id, name, gender, score FROM students ORDER BY score DESC, gender; // 表示先按 score 列倒序,如果有相同分数的,再按 gender 列排序。
  • ORDER BY 默认正序,从小到大,DESC descend 倒序

分页查询

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0; // 第 1 页,每页 3 条

LIMIT 3 OFFSET 3; // 第 2 页,每页 3 条
  • LIMIT <M> OFFSET <N> 显示从 N+1 行开始,后 M 条记录。

聚合查询

SELECT COUNT(*) boys FROM students WHERE gender = 'M';
  • COUNT(*)、COUNT(id)、可用 WHERE 条件
  • 其他函数:SUM、AVG、MAX、MIN
  • 如果是字符类型,MAX() 和 MIN() 会返回排序最后和排序最前的字符
  • 结合 GROUP BY 分组:SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;
  • 多个列分组:SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;

多表查询

SELECT * FROM students, classes; // 同时查询 students 表和 classes 表的“乘积”

连接查询

  • 先确定一个主表作为结果集,然后,把其他表的行有选择性地「连接」在主表结果集上。

    SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
    FROM students s
    INNER JOIN classes c
    ON s.class_id = c.id;
  • 内连接:只查询符合两个表的结果,取交集

  • 右外连接 RIGHT OUTER JOIN:查询符合右边表所有结果,空余用 NULL 填充

  • 左外连接 LEFT OUTER JOIN:查询符合主表所有结果,空余用 NULL 填充

  • FULL OUTER JOIN,并集

修改数据

INSERT

INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);
INSERT INTO students (class_id, name, gender, score) VALUES
(1, '大宝', 'M', 87),
(2, '二宝', 'M', 81);

UPDATE

UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;
  • WHERE 跟 SELECT 的 WHERE 一样使用
  • UPDATE students SET score=60; 修改表的所有数据,先用 SELECT 语句测试

DELETE

DELETE FROM <表名> WHERE ...;
  • DELETE FROM students; 删除表所有数据

库/表/列 SQL

也可以只安装 MySQL Client,然后连接到远程 MySQL Server。假设远程 MySQL Server 的 IP 地址是 10.0.1.99,那么就使用 -h 指定 IP 或域名:

mysql -h 10.0.1.99 -u root -p

SHOW DATABASES;
CREATE DATABASE test;
use test;
DROP DATABASE test;

SHOW TABLES; 

CREATE TABLE `students` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT "ID",
`class_id` bigint(20) NOT NULL DEFAULT 0 COMMENT "班级 ID",
`name` varchar(100) NOT NULL DEFAULT '' COMMENT "姓名",
`gender` varchar(1) NOT NULL DEFAULT '男' COMMENT "性别",
`score` int(11) NOT NULL DEFAULT 100 COMMENT "分数",
`created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT "学生表";

DESC students; // describe,查看表结构

SHOW CREATE TABLE students; // 查看创建表的语句

DROP TABLE students; // 删除表
  • bigint(20) 后面的 20 指的是字段最长长度,只是起提示作用。bigint 8 字节,最长不超过 20 位(922亿亿)
  • NOT NULL:插入数据时,必须有值
  • CHARSET( character set ) utf8 与 utf8mb4 的区别,char utf8 默认为 3 个字节,utf8mb4 默认为 4 个字节
  • COLLATE=utf8mb4_0900_ai_ci
  • ENGINE=InnoDB DEFAULT:默认引擎 InnoDB

ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL; // 给 students 表新增一列 birth
ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL; // 把列名改为 birthday,类型改为 VARCHAR(20)
ALTER TABLE students MODIFY COLUMN birth VARCHAR(20) DEFAULT '' NOT NULL; // 设置默认值为 ''
ALTER TABLE studens DROP COLUMN birthday; // 删除 birthday 列
EXIT // 退出

实用 SQL

插入或替换

REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

插入或更新

INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;

插入或忽略:存在就忽略

INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

快照:复制当前表的数据到一个新表

-- 对 class_id=1 的记录进行快照,并存储为新表 students_of_class1:
CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;

写入查询结果集

INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;

处理数据表被锁

show open tables where in_use>0; // 查看表是否被锁
show processlist // 查看所有进程
kill id // 杀进程

用户

SELECT DISTINCT concat('User:''',USER,'''@''',HOST,''';') AS QUERY FROM mysql. USER; // 查看数据库的所有用户
show grants for 'label'@'%' // 查看用户所有表的权限
CREATE USER 'label'@'%' IDENTIFIED BY 'label123'; // 创建用户
CREATE USER 'label'@'localhost' IDENTIFIED BY 'label123'; // 创建用户

事务

这种把多条语句作为一个整体进行操作的功能,被称为数据库 「事务」。

对于单条SQL语句,数据库系统自动将其作为一个事务执行,这种事务被称为隐式事务

使用BEGIN开启一个事务,使用COMMIT提交一个事务,这种事务被称为 显式事务

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; // 如果 COMMIT 语句执行失败了,整个事务也会失败。
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK; // 回滚前面执行的 sql

事务有四大特性:

  • 原子性(Atomicity):不可分割,要么成功,要么失败;
  • 一致性(Consistency):事务完成后,所有数据的状态是一致的;
  • 隔离性(Isolation):并发执行的事务,对数据的操作要具有隔离性;
  • 持久性(Durability):事务完成后,数据就持久化到数据库中

事务有隔离性有级别,共 4 种,隔离级别由低到高

Isolation Level 脏读(Dirty Read) 不可重复读(Non Repeatable Read) 幻读(Phantom Read)
Read Uncommitted Yes Yes Yes
Read Committed - Yes Yes
Repeatable Read - - Yes
Serializable - - -

默认测试数据库

mysql> select * from students;
+----+-------+
| id | name |
+----+-------+
| 1 | Alice |
+----+-------+
1 row in set (0.00 sec)


INSERT INTO students(id, name) VALUES (1, 'Alice');
select @@session.transaction_isolation # 查看隔离级别
SET @@session.transaction_isolation = 'READ-UNCOMMITTED'; # 切换隔离级别读为提交

Read Uncommitted(读未提交)

一个事务可以读到另一个事务更新,但未提交的数据。如果另一个事务回滚,当前读取的值就是脏数据,称为脏读。

时刻 事务 A 事务 B
1 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
2 BEGIN; BEGIN;
3 UPDATE students SET name = ‘Bob’ WHERE id = 1;
4 SELECT * FROM students WHERE id = 1; // ‘Bob’
5 ROLLBACK;
6 SELECT * FROM students WHERE id = 1; // ‘Alice’
7 COMMIT;

Read Commited(读已提交)

存在不可重复读的问题,事务重复读时,可能数据不一致。

时刻 事务A 事务B
1 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
2 BEGIN; BEGIN;
3 SELECT * FROM students WHERE id = 1; // ‘Alice’
4 UPDATE students SET name = ‘Bob’ WHERE id = 1;
5 COMMIT;
6 SELECT * FROM students WHERE id = 1; // ‘Bob’
7 COMMIT;
  • 读已提交隔离级别使用读未提交的测试用例的结果:两次读取都是 Alice

Repeatable Commited(可重复读)

存在幻读的问题,幻读就是没有读取到的记录,以为不存在,但插入时将失败,再次读取时又能读取到数据。

时刻 事务A 事务B
1 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
2 BEGIN; BEGIN;
3 SELECT * FROM students WHERE id = 99; // Empty
4 INSERT INTO students (id, name) VALUES (99, ‘Bob’);
5 COMMIT;
6 SELECT * FROM students WHERE id = 99; // Empty
7 INSERT INTO students(id, name) values(99, Alice) // 失败
8 SELECT * FROM students WHERE id = 99; // Alice
9 COMMIT;

其实幻读影响不大,没有脏读和不可重复读的问题,Mysql 默认隔离级别就是 Repeatable Commited。

  • 可重复读隔离级别使用读未提交的测试用例的结果:两次读取都是 Alice
  • 可重复读隔离级别使用读已提交的测试用例的结果:两次读取都是 Alice

Serializable(串行化)

串行操作,没有并发。

时刻 事务A 事务B
1 SET TRANSACTION ISOLATION LEVEL Serializable; SET TRANSACTION ISOLATION LEVEL Serializable;

上面三种情况均会报错。

设置隔离级别




数据库语句顺序

  • 写的顺序:select … from… where…. group by… having… order by.. limit [offset,] (rows)
  • 执行顺序:from… where…group by… having…. select … order by… limit
mysql> select host,user from user;

image-20191126122815557

SELECT host,user,password FROM user;

延伸阅读

Depp Wang wechat
个人公众号