Mysql - 教程笔记

主键

原则：

1. 不可重复（唯一）
2. 最好不要修改
3. 和业务不相关
   常见主键：
4. 自增整数类型（整数）（INT自增最多21亿）（BIGINT）
5. 全局唯一GUID类型（字符串。GUID算法）
6. 联合主键（多个字段一起设置为主键，只要不是所有主键重复即可）

外键

与其他表格关联起来的字段，称为外键。

• 添加外键

  ALTER TABLE students
  ADD CONSTRAINT fk_class_id  # CONSTRAINT 定义一个 外键约束的名称
  FOREIGN KEY (class_id)   # 指定外键
  REFERENCES classes (id);  # 关联其他表
  

• 删除外键约束(并没有删除外键列)

  ALTER TABLE students
  DROP FOREIGN KEY fk_class_id;
  

外键约束会降低性能。所以有的时候为了提高性能，只是添加一个作为外键的列，却没有外键约束。

• 一对多关系：
• 多对多关系：通过两个一对多关系（一个中间表）实现。
• 一对一关系：一个表的记录对应到另一个表的唯一一个记录。（把一个大表拆分为两个一对一的表，为了把经常读取和不经常读取的字段分开）

索引

经常根据某一列进行查询，就可以对列创建索引。加快查询速度

# 添加索引
ALTER TABLE students
ADD INDEX idx_score (score);  # 也可以多列 idx_score (name, score);

• 作为索引列，每条记录的值越不相同，索引效率越高。
• 索引越多，插入、更新和删除记录的速度就越慢。（虽然提高了查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引）
• 会自动对其创建主键索引
• 唯一索引：一些根据业务要求，需要具有唯一性约束的列（例如身份证）

  # 添加唯一索引
  ALTER TABLE students
  ADD UNIQUE INDEX uni_name  (name);
  
  # or 添加带有唯一约束的索引
  ALTER TABLE students
  ADD CONSTRAINT uni_name UNIQUE (name);
  

查询

基本查询，条件查询，投影查询，分页查询，聚合查询，多表查询，连接查询。

• in, like, between(5,10) 包含
  
• 起别名（返回的列名改变）
  
• 排序：order by [DESC/ASC],
  
• 分页：LIMIT OFFSET (offset默认为0，第五页，那 N=M*(5-1))
  
• count(): select count(*) from students; 没匹配到会返回0
  
• SUM、AVG、MAX、MIN 一般记录是数值型的。 没匹配到返回 NULL
  
• 多表查询：列数之和，行数之积；注意字段名可能重合，最好使用别名。
  

  SELECT
      students.id sid,
      students.name,
      students.gender,
      students.score,
      classes.id cid,
      classes.name cname
  FROM students, classes;  # 还可以给表设置别名
  

• 连接查询：主表、需要连接的表、连接条件、（where 、order by）
  • 内连接(inner join 两个字段相等，交集)

  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;
  

  • 外连接 (不存在的字段用 NULL)
    • LEFT OUTER JOIN
    • RIGHT OUTER JOIN

修改数据

• 增加：INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...),(第二条记录),...; 有默认值的字段可以不出现
• 更新：UPDATE <表名> SET 字段1=值1,字段2=值2,... WHERE ...；更新字段可以使用表达式；没有匹配到结果也不会报错；最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再用UPDATE更新。（就是自己先看下查的是不是要改的数据）
• 删除：DELETE FROM <表名> WHERE ...;也是没匹配到也不会报错，也是最好先SELECT

其他

• MySql Client 是为了连接 mysql(server端)，所以只下载 Client ，连接远程mysql mysql -h 10.0.1.99 -u root -p
• MySQL Workbench: 管理 MySQL 的可视化图形界面。（其实和MySQL Client 是一个作用，只是有界面）
• 命令行程序mysql实际上是MySQL客户端，真正的MySQL服务器程序是mysqld，在后台运行。
• 端口 3306
• 字段应该避免允许 NULL（简化查询条件，加快查询速度）
• 数据库

  # 数据库
  SHOW DATABASE;  # 列出所有数据库
  CREATE DATABASE test; # 创建
  DROP DATABASE test;  # 删除
  USE test;   # 切换
  
  # 表
  SHOW TABLES;  # 查看当前数据库的所有表
  DESC test;   # 查看表的结构
  SHOW CREATE TABLE test;  # 查看创建表的SQL语句
  DROP TABLE test;  # 删除表
  ALTER TABLE test ADD COLUMN birth VIRCHAR(10) NOT NULL; # 表格新增列
  ALTER TBALE test CHANGE COLUMN birth birthday VIRCHAR(10) NOT NULL;  # 修改列名
  ALTER TABLE test DROP COLUMN birthday; # 删除列
  

SQL 语句

• 有则替换，无则添加（与更新相比，原纪录被删除，新增一条纪录）

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

• 有则更新，无则添加

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

• 有则忽略，无则添加

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

• 快照（复制当前表 test 到新表 test_of_1）

  CREATE TABLE test_of_1 SELECT FROM test where clas_id=1;
  

• 写入结果集（查询结果写入表中，可以创建新表(CREATE)，或者 将结果插入到指定表（INSERT））

  # 创建新表
  CREATE TABLE statistics (
    id BIGINT NOT NULL AUTO_INCREMENT,
    class_id BIGINT NOT NULL,
    average DOUBLE NOT NULL,
    PRIMARY KEY(id)
  );
  # 添加查询结果
  INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id; 
  

事务

一系列操作必须全部执行，若失败，则撤销。这种把多条语句作为一个整体进行操作的功能，被称为数据库事务。

特性 ACID

• Atomic: 原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行；
• Consistent：一致性，事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100；
• Isolation：隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；
• Duration：持久性，即事务完成后，对数据库数据的修改被持久化存储。

使用

• 隐式事务：自动将单条SQL语句作为一个事务执行，这种事务被称为隐式事务；
• 显式事务：要手动把多条SQL语句作为一个事务执行，使用BEGIN开启一个事务，使用COMMIT提交一个事务，这种事务被称为显式事务。

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  # ROLLABCK => 如果希望主动让事务失败，可以不提交，直接回滚事务

隔离级别（并发执行事务时需要设定）

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

---

• Read Uncommitted （脏读 = 读取 未提交的）
  • 隔离级别最低的一种事务级别。在这种隔离级别下，一个事务会读到另一个事务更新后但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是脏数据，这就是脏读（Dirty Read）。
• Read Committed （不可重复读）(UPDATE 操作)
  • 在Read Committed隔离级别下，一个事务可能会遇到不可重复读（Non Repeatable Read）的问题
  • 不可重复读是指，在一个事务内，多次读同一数据，在这个事务还没有结束时，如果另一个事务恰好修改了这个数据，那么，在第一个事务中，两次读取的数据就可能不一致。(就是没提交就读不到)
• Repeatable Read （默认级别，幻读）(INSERT INTO 操作)
  • 事务B在第3步第一次读取id=99的记录时，读到的记录为空，说明不存在id=99的记录。随后，事务A在第4步插入了一条id=99的记录并提交。事务B在第6步再次读取id=99的记录时，读到的记录仍然为空，但是，事务B在第7步试图更新这条不存在的记录时，竟然成功了，并且，事务B在第8步再次读取id=99的记录时，记录出现了。
    
  • (A插入后未提交，B 在当前隔离级别，update 会卡在那。
  • A Insert 后提交，B不能查到A刚insert的，需要update那条记录后，才能查到
  • 如果是默认的，B 不用update 也能直接查到，所以 还是和 Reaptable Read 有区别？（InnoDB）
• Serializable
  
  • 最严格的级别，脏读、不可重复度、幻读 都不会存在。
    
  • 如果没有指定隔离级别，数据库就会使用默认的隔离级别。在MySQL中，如果使用InnoDB，默认的隔离级别是Repeatable Read。

命令的颗粒度(隔离)：

    # 脏读
    # B 会立马获取 A 语句执行结果
    A: begin => SQL1,SQL2      B:begin => SQL1,SQL2    
    
    # 不可重复读
    # A 更改记录后，一提交，B 就获取到了，虽然 B 还没执行完命令，
    # 所以可能 B 两次获取的数据不一样（在 A 提交数据前后）
    A: begin => [SQL1,SQL2] => commit     B:begin => SQL2,SQL2  
    
    # 幻读
    # A 更改记录并提交，B 不能立马获取到。但是，如果 A 进行 Insert(添加) 操作提交后，
    # B 不能查看到那条记录，却可以对那条记录进行更新，并且更新后可以查看到那条记录
    A: begin => [SQL1,SQL2] => commit     B:begin => 'SQL2,SQL2'
    
    # Serializable 杜绝了那些行为的影响，但由于事务是串行执行，所以效率会大大下降
    A: begin => [SQL1,SQL2] => commit     B:begin => [SQL2,SQL2]

参考网址<-------

远程连接

例如，你想myuser使用mypassword从任何主机连接到mysql服务器的话。

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
FLUSH   PRIVILEGES;

如果你想允许用户myuser从ip为192.168.1.6的主机连接到mysql服务器，并使用mypassword作为密码

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
FLUSH   PRIVILEGES;

如果你想允许用户myuser从ip为192.168.1.6的主机连接到mysql服务器的dk数据库，并使用mypassword作为密码

GRANT ALL PRIVILEGES ON dk.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
FLUSH   PRIVILEGES;

mysql远程连接-参考

服务器控制台 入站规则 打开mysql端口3306

忘记密码

1. my.cnf => [mysqld] => skip-grant-tables
2. systemctl restart mysqld.service
3. mysql -u root -p 无密码登录
4. use mysql
5. update user set password=password('****') where user='root'
6. flush privileges;
7. 重启 mysql