【MySQL】# MySQL基础知识

1. 关系数据库概述

1.1 数据模型

• 层次模型（树状）
• 网状模型
• 关系模型（二维表格，重点）

1.2 数据类型

1. INT：整型 4
2. BIGINT: 长整型 8 常用
3. REAL: 浮点型 4
4. DOUBLE: 浮点型 8
5. DECIMAL(M, N): 高精度小数 共20位，其中小数10位
6. CHAR: 定长字符串
7. VARCHAR: 变长字符串
8. BOOLEAN
9. DATE
10. TIME
11. DATETIME

1.3 SQL (Structured Query Language)

1. Oracle把自己扩展的SQL称为 PL/SQL
2. DDL(Data Definition Language)：创建、删除、修改表结构 数据库管理员
3. DML(Data Manipulation Language): 添加、删除、更新数据的能力 应用程序对数据库的操作
4. DQL(Data Query Language): 用户查询数据 用户

CRUD：Create、Retrieve、Update、Delete

良好习惯：SQL关键字总是大写，表名和列名均使用小写。

---

2. 关系模型

1. 记录：表的每一行
2. 字段：表的每一列（字段应该避免允许为NULL）

2.1 主键

1. 定义：能够唯一标识一条记录的某个字段

2. 基本原则：不使用任何业务相关的字段作为主键，一般使用id

   INT NOT NULL AUTO_INCREMENT
   

2.2 联合主键（不常用）

通过多个字段唯一标识记录，允许一列有重复，只要不是所有主键列都重复即可

2.3 外键（也可以不设置约束，仅依靠应用程序的逻辑来保证）

1. 一对多关系（一个班级对应多名学生）

   在 多 的表里首先添加一个字段

   创建外键约束：
       ALTER TABLE 多的表名
       ADD CONSTRAINT 外键约束名
       FOREIGN KEY (作为外键的字段)
       REFERENCES 一的表名 (主键);
   删除外键约束：
   	ALTER TABLE students
   	DROP FOREIGN KEY fk_class_id;
   

2. 多对多关系（一个老师对应多个班级，一个班级对应多个老师）

   创建一个中间表（建立两个一对多的关系）

   teachers表：
   	id   name
   classes表：
   	id   name
   teacher_classes表：
   	id   teacher_id    class_id
   

3. 一对一关系（一个表的记录对应到另一个表的唯一一个记录，一个学生对应一个电话号码）

   用于将常读取和不常读取字段分开，提高查询速度

2.4 索引

关系数据库中对某一列或多个列的值进行预排序的数据结构

值越不相同，索引效率越高

创建索引：
	ALTER TABLE students
	ADD INDEX 索引名称 (需要创建索引的列);

唯一索引：不宜作为主键，但具有唯一性约束

唯一索引：
	ALTER TABLE students
	ADD UNIQUE INDEX 唯一索引名称 (建立唯一索引的列);
唯一约束：
	ALTER TABLE students
	ADD CONSTRAINT 唯一约束的名称 UNIQUE (建立唯一约束的列);

---

3. 查询数据(DQL)

3.1 准备数据

classes表：
    CREATE TABLE classes (
    id BIGINT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
students表：
	CREATE TABLE students(
    id INT NOT NULL AUTO_INCREMENT,
    class_id INT NOT NULL,
    name VARCHAR(10) NOT NULL, 
    gender VARCHAR(1) NOT NULL,
    scoure INT NOT NULL,
    PRIMARY KEY(id)
    ) ENGINE=INNODB DEFAULT CHARSET=UTF8;

3.2 基本查询

1. 查询所有

   SELECT * FROM students;
   

2. 测试数据库连接

     	SELECT 1;
   

3.3 条件查询

1. 获取成绩在80分以上的

   SELECT * FROM students WHERE score >= 80;
   

2. 获取成绩在80分以上且是“男生”

   SELECT * FROM students WHERE score >= 80 AND gender = 'M'
   

3. 获取成绩在80分以上或者是“男生”

   SELECT * FROM students WHERE score >= 80 OR gender = 'M'
   

4. 获取班级不为1的

   SELECT * FROM students WHERE class_id <> 1;
   

5. 获取分数在80以下或者90以上，并且是男生

   SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';
   

6. 获取名字带“王”的（LIKE ‘%王’）

     	SELECT * FROM students WHERE name LIKE "%王%";
   

7. 获取分数在80到90之间的

   SELECT * FROM students WHERE score Between 80 AND 90;
   

3.4 投影查询

1. 获取指定列并将name列改为stuName

   SELECT id, name stuName, score FROM students;
   

2. 获取指定列，且是男生

     	SELECT id, name stuName, score FROM students WHERE gender = 'M';
   

3.5 排序（默认按照主键排序）

1. 根据成绩排序（默认从低到高 ORDER BY）

   SELECT * FROM students ORDER BY score;
   

2. 根据成绩排序（从高到低）

   SELECT id, name, score FROM students ORDER BY score DESC;
   

3. 先根据成绩升序排列，如果成绩有相同的，再按性别降序排列排序

   SELECT id, name, gender, score FROM students ORDER BY score ASC,  gender DESC;
   

4. 查询一班的学生成绩，并按照倒序排列

   SELECT id, name, gender, score
   FROM students
   WHERE class_id = 1
   ORDER BY score DESC;
   

3.6 分页查询

1. 查询学生的成绩，分页显示，从第1条显示，每页最多显示3条(LIMIT 3 OFFSET 0;)

   SELECT id, name, gender, score
   FROM students
   ORDER BY score DESC
   LIMIT 3 OFFSET 0;
   

2. 每页的开始OFFSET = 每页要显示的条数 （当前页码 - 1）

从 OFFSET 条开始显示，每页有 LIMIT 条

3.7 聚合查询

1. 查询表中共有多少行数据，以num显示

   SELECT COUNT(*) num FROM students;
   SELECT COUNT(1) num FROM students;     蜜汁快一点
   
   SELECT COUNT(列名) num FROM students;      查询该列存在的行数（若该列为 null，则不算一条）
   

2. 查询有多少男生

   SELECT COUNT(id) boys FROM students WHERE gender = 'M';
   

3. 其他聚合函数

4. 使用AVG查询男生的平均成绩（聚合查询使用WHERE条件）

   SELECT AVG(score) average FROM students WHERE gender = 'M';
   

5. 每页3条记录，通过聚合查询获得总页数

     	SELECT CEILING(COUNT(*) / 3) FROM students;
   

3.8 分组查询

1. 查询每个班级的人数

   SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;
   

2. 每个班级的平均分

   SELECT class_id, AVG(score) ave FROM students GROUP BY class_id;
   

3. 每个班级男生女生的平均分，降序排列

   SELECT class_id, gender, AVG(score) ave 
   FROM students 
   GROUP BY class_id, gender
   ORDER BY ave DESC;
   

3.9 分组过滤查询

select fieldName
from tbName
where condition_
group by 分组要求
having 过滤规则;         例如，班级为1， 学号为1， 2， 3之中的人的成绩

3.10 多表查询(慎用)

查询两个表中的属性（笛卡尔积）

SELECT
	s.id sid,
	s.name sname,
	s.score,
	c.name cname
FROM students s, classes c
WHERE s.gender = 'M' AND c.id = 1;

3.11 连接查询

先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上

1.内连接（INNER JOIN） —— 只返回同时存在于两张表的行数据

1> 先确定主表                    FROM 表1
2> 再确定需要连接的表             INNER JOIN <表2>
3> 确定连接条件                   ON <条件。。。>
4> 根据情况再加WHERE 或者 ORDER BY
	SELECT ... 
	FROM <表1> 
	INNER JOIN <表2> 
	INNER JOIN <表3> 
	ON <条件...>

查询所有学生，同时返回班级名称

SELECT s.id, s.name, c.name class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;

2.外连接（OUTER JOIN）

右外连接： ---  返回右表都存在的行，若一行仅在右表有，那么以 NULL 填充剩余字段
	SELECT s.id, s.name, c.name class_name, s.gender, s.score
    FROM students s
    RIGHT OUTER JOIN classes c
    ON s.class_id = c.id;
左外连接： ---  返回左表都存在的行

全外连接： ---  把两张表的所有记录全部选择出来，并且，自动把对方不存在的列填充为NULL

3.12 分支结构查询

从case开始，到end结束。满足条件对应一个结果，类似于Java中的switch case

查询姓名，成绩以及成绩对应的等级

SELECT name, score,
CASE
	WHEN score >= 90 THEN 'A'
	WHEN score >= 80 AND score < 90 THEN 'B'
	WHEN score >= 70 AND score < 80 THEN 'C'
	WHEN score < 70 THEN 'D'
END AS 'level'
from students;

3.13 时间查询

select sysdate();                          当前系统时间(年，月，日, 时，分，秒)
select curdate();                          当前日期
select curtime();                          当前时间
select week();                             指定日期是这一年的第几周

3.14 子查询

1. 查询成绩高于小明的学生id和姓名

   SELECT score
   FROM students
   WHERE name = '小明';
   
   SELECT id, name
   FROM students
   WHERE score > 90;
   
   整合一下：
   	SELECT id, name
       FROM students
       WHERE score > (SELECT score
           FROM students
           WHERE name = '小明');
   

2. 查询学生成绩前5名的信息（子查询结果作为一张表，从表内查询指定数据）

   SELECT id, name, score         与下面的select语句一样
   FROM (SELECT id, name, score
   			FROM students
   			ORDER BY score DESC) as tmp
   LIMIT 5;
   

4. 修改数据（DML）

4.1 INSERT

INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);

向学生表中插入数据

INSERT INTO students (class_id, name, gender, score) 
VALUES
  (1, '大宝', 'M', 87),
  (2, '二宝', 'M', 81);

4.2 UPDATE

UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;

1. 更新id为1的学生的信息

   UPDATE students 
   SET name='大牛', score=66 
   WHERE id=1;
   

2. 把所有60分以下的同学的成绩加10分

   UPDATE students 
   SET score=score+10 
   WHERE score<80;
   

   可以根据返回受影响行数判断是否更新成功

4.3 DELETE

DELETE FROM <表名> WHERE ...;

1. 删除id=5，6，7的记录

   DELETE FROM students 
   WHERE id>=5 AND id<=7;
   

2. 删除id=1，4，9的记录

   DELETE FROM students 
   WHERE id IN (1, 4, 9);
   

3. 清空整表数据

   truncate table students;
   

5. MySQL

命令行程序 mysql 实际上是MySQL客户端，真正的MySQL服务器程序是 mysqld ，在后台运行。

命令行的方式运行，一般用于SSH操作远程服务器，注意语句后面加 ；

默认编码集 Latin1

5.1 数据库管理

1. 创建数据库

   CREATE DATABASE test character set utf8;
   

2. 删除数据库

   DROP DATABASE test;
   

3. 切换到某一个数据库

   USE test;
   

4. 查看所有数据库

   SHOW DATABASES;
   

5. 数据库连接

   cmd> mysql -u root -p
   

5.2 数据表管理

1. 列出数据库的所有表

   SHOW TABLES;
   

2. 查看一个表的结构

   DESC students;
   

3. 查看创建表的SQL语句

   SHOW CREATE TABLE students;
   

4. 删除表

   DROP TABLE students;
   

5. 给students表新增一列birth

   ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;
   

6. 修改birth列名为birthday

   ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;
   

7. 删除列

   ALTER TABLE students DROP COLUMN birthday;
   

5.3 数据约束

1. 默认值 default

   create table person1(
       id int,
       name varchar(50),
       country varchar(50) default 'PRC' -- 默认值字段
   );
   

2. 非空 not null

   create table person1(
       id int,
       name varchar(50) not null,   -- 非空
       country varchar(50) default 'PRC' -- 默认值字段
   );
   

3. 唯一 unique（当前字段中保存的数据在当前表内不可以重复）

   create table person1(
       id int unique,
       name varchar(50) not null,   -- 非空
       country varchar(50) default 'PRC' -- 默认值字段
   );
   

4. 主键 primary key

   create table person1(
       id int primary key,
       name varchar(50) not null,   -- 非空
       country varchar(50) default 'PRC' -- 默认值字段
   );
   

5. 自增长 auto_increment（从1开始）

   自增长可以指定数值，而且存在影响自增长计数情况

   create table person1(
       id int primary key auto_increment,
       name varchar(50) not null,   -- 非空
       country varchar(50) default 'PRC' -- 默认值字段
   );
   

6. 外键约束（一般不用）

   -- 外键声明约束
       -- constraint 声明使用关键字
       -- fk_emp_dept 外键名称 fk ==> foreign key emp ==> employee dept ==> 部门表
   	-- foreign key 外键声明关键字(从表外键字段)  deptId   employee表内的外键是deptId
   	-- references 选择当前外键关联的主表字段关键字 主表(字段) dept(id)
   
   -- 部门表 【主表】
   create table dept(
       id int primary key auto_increment,
       deptName varchar(50) not null
   );
   
   -- 员工表，员工表中存在字段和部门表有直接联系，deptId ==> dept表内的id
   -- 【从表】
   create table employee(
       id int primary key auto_increment,
       name varchar(50) not null,
       deptId int,
       
   	constraint fk_emp_dept foreign key (deptId)
   	references nzgp2001.dept(id)
   );
   

7. 级联操作

   -- 在外键约束的情况下，操作主表和从表都有很多约束
   -- SQL中引入的级联操作
       -- 级联删除 on delete cascade
       -- 级联修改 on update cascade
       
   -- 部门表 【主表】
   create table dept(
       id int primary key auto_increment,
       deptName varchar(50) not null
   );
   
   -- 员工表，员工表中存在字段和部门表有直接联系，deptId ==> dept表内的id
   -- 【从表】
   create table employee(
       id int primary key auto_increment,
       name varchar(50) not null,
       deptId int,
       
   	constraint fk_emp_dept foreign key (deptId)
   	references nzgp2001.dept(id)
       on delete cascade -- 级联删除
   	on update cascade -- 级联修改
   );
   
   -- 主表中有外键约束的字段数据发生修改，从表中对于字段同时发生修改，级联修改
   update dept set id = 10 where id = 3;
   

5.4 实用SQL语句

1. 插入或替换（REPLACE） — 不存在则插入，存在则删除再插入

   REPLACE INTO students 
   VALUES (1, 1, '小明', 'F', 99);
   

2. 插入或更新（INSERT INTO … ON DUPLICATE KEY UPDATE …） — 不存在则插入，存在则更新

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

3. 插入或忽略（INSERT IGNORE INTO…）

   INSERT IGNORE INTO students 
   VALUES (1, 1, '小明', 'F', 99);
   

4. 快照

   CREATE TABLE students_of_class1 
   SELECT * FROM students 
   WHERE class_id=1;
   

5. 将查询结果集写入表中

   创建结果集表：
   	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;
   

6. 强制使用索引（索引必须存在）

    SELECT * FROM students 
    FORCE INDEX (idx_class_id) 
    WHERE class_id = 1 
    ORDER BY id DESC;
   

---

6. 事务管理

6.1 事务

1. 数据库事务：把多条语句作为一个整体进行操作的功能

2. ACID特性：

   1. A：Atomic，原子性，要么全部执行，要么全部不执行；
   2. C：Consistent，一致性，事务完成后，所有数据的状态都是一致的
   3. I：Isolation，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离
   4. D：Duration，持久性，即事务完成后，对数据库数据的修改被持久化存储

3. 隐式事务：单条SQL语句，数据库系统自动将其作为一个事务执行

4. 显式事务：

   1. 开启事务： BEGIN；

   2. 提交数据： COMMIT；

   3. 回滚事务： ROLLBACK；

   4. 设置事务的隔离级别：SET TRANSACTION ISOLATION LEVEL 级别

      BEGIN;
      UPDATE accounts SET balance = balance - 100 WHERE id = 1;
      SELECT * FROM accounts;
      
      UPDATE accounts SET balance = balance + 100 WHERE id = 2;
      SELECT * FROM accounts;
      
      -- 没有问题
      COMMIT;
      
      -- 发现问题
      ROLLBACK;
      

6.2 隔离级别（从低到高）

1. Read Uncommitted【级别最低，什么都避免不了】

   脏读：事务A 读到 事务B 更新后但未提交的数据

   事务B 回滚，那么 事务A 读到的数据就是脏数据（和上一次读到的数据不一致）

   TRANSACTION_SERIALIZABLE       // 对应常量
   

2. Read Committed【可避免脏读】

   不可重复读（Non Repeatable Read）：在事务A内，多次读同一数据，若在这个事务没结束前，事务B恰好修改了这个数据，那么事务A中，两次读取的数据可能就不一致，也就是说一个事务可以看到其他事务所做的修改

   TRANSACTIONREPEATABLEREAD
   

3. Repeatable Read【可避免脏读，不可重复读】

   幻读（Phantom Read）：在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

   • 在一个事务中，第一次查询某条记录，没有
   • 但是，当试图更新这条不存在的记录时，竟然能成功
   • 并且，再次读取同一条记录，它就神奇地出现了

   TRANSACTIONREADCOMMITTED
   

4. Serializable【可避免脏读，不可重复读，幻读】

   最严格的隔离级别，所有事务按照次序依次执行

   TRANSACTIONREADUNCOMMITTED
   

5. MySQL中，如果使用InnoDB，默认的隔离级别是Repeatable Read

---

7. 权限管理

1. 创建用户

   -- 用户名 saolei 密码 123456
   create user 'lwclick' identified by '123456';
   

2. 授权用户

   -- 授权操作所有nzgp2001库权限
   grant all on nzgp2001.* to 'lwclick';
   

3. 撤销授权

   -- 撤销saolei用户所有nzgp2001数据库权限
   revoke all on nzgp2001.* from 'lwclick';
   

4. 删除用户

   -- 删除用户
   drop user 'lwclick';
   

---

8. 索引

索引分单列索引和组合索引

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)

索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

8.1 普通索引

• 创建索引

  CREATE INDEX indexName ON mytable(username(length)); 
  

• 添加索引

  ALTER table tableName ADD INDEX indexName(columnName)
  

• 删除索引

  DROP INDEX [indexName] ON mytable; 
  

• 创建表的时候直接指定

  CREATE TABLE mytable(
      ID INT NOT NULL,
      username VARCHAR(16) NOT NULL,  
  
      INDEX [indexName] (username(length))  
  );  
  

• 显示索引

  SHOW INDEX FROM table_name; \G
  

8.2 唯一索引

索引列的值必须唯一，但允许有空值。

• 创建索引

  CREATE UNIQUE INDEX indexName ON mytable(username(length)) 
  

• 添加索引

  ALTER TABLE mytable ADD UNIQUE [indexName] (username(length))
  

• 创建表的时候直接指定

  CREATE TABLE mytable(
      ID INT NOT NULL,
      username VARCHAR(16) NOT NULL,  
  
      UNIQUE [indexName] (username(length))  
  );  
  

---

9. 视图

为了保障数据安全性，提高查询效率。数据独立（源表和视图）

9.1 视图是什么？

视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个select语句保存在数据字典中的。

基表：用来创建视图的表叫做基表base table

通过视图，可以展现基表的部分数据；视图数据来自定义视图的查询中使用的表，使用视图动态生成。

9.2 视图的操作

9.2.1 创建视图

CREATE [OR REPLACE]
	[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select语句
   [WITH [CASCADED | LOCAL] CHECK OPTION]

• OR REPLACE：表示替换已有视图
• ALGORITHM：表示视图选择算法。
  • UNDEFINED 默认，MySQL将自动选择所要使用的算法
  • merge 合并，将视图的语句与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分
  • temptable 临时表，将视图的结果存入临时表，然后使用临时表执行语句
• [WITH [CASCADED | LOCAL] CHECK OPTION] ：视图在更新时保证在视图的权限范围之内
  • cascade默认值：更新视图的时候，检查所有的视图(嵌套视图及其底层的视图)
  • local：更新视图的时候，只检查将要更新的视图本身

单表创建视图

create view v_F_players(编号,名字,性别,电话)    -- 指定显示列的姓名
              -- 视图名后面的列的数量必须匹配select子句中的列的数量。
as
select id, name, sex, phone from players
where sex='F'
with check option;

select * from  v_F_players;

多表创建视图

create view v_match
as 
select a.id, a.name, sex, c.teamId, c.div
from player a, mat b, teams c
where a.id = b.id and b.teamId = c.teamId;

9.2.2 查看视图

show create view v_F_players\G;

9.2.3 更改视图

1. CREATE OR REPLACE VIEW语句修改视图

   -- 在视图存在的情况下可对视图进行修改，视图不在的情况下可创建视图
   create or replace view view_name as select语句;
   

2. ALTER语句修改视图

   -- 修改数据库中已存在的表的定义，当基表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间一致
   ALTER
       [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
       [DEFINER = { user | CURRENT_USER }]
       [SQL SECURITY { DEFINER | INVOKER }]
   VIEW view_name [(column_list)]
   AS select_statement
       [WITH [CASCADED | LOCAL] CHECK OPTION]
   

3. DML操作更新视图

   -- 因为视图本身没有数据，因此对视图进行的dml操作最终都体现在基表中
   create view v_student as select * from student;
   
   update v_student set name='钱六' where 学号='1';
   

   不能做DML操作

   • select子句中包含distinct
   • select子句中包含组函数
   • select语句中包含group by子句
   • select语句中包含order by子句
   • select语句中包含union 、union all等集合运算符
   • where子句中包含相关子查询
   • from子句中包含多个表
   • 如果视图中有计算列，则不能更新
   • 如果基表中有某个具有非空约束的列未出现在视图定义中，则不能做insert操作

4. 删除视图

   drop view v_student;
   

9.2.4 使用WITH CHECK OPTION约束

可以执行DML操作的视图，定义时可以带上WITH CHECK OPTION约束

作用：对视图所做的DML操作的结果，不能违反视图的WHERE条件的限制。

create view v_veterans
as
select * from players
where birth_date < '1960-01-01'
with check option;

-- 使用update对视图进行修改，抛出异常，违反了视图中的where条件
update v_veterans
set BIRTH_DATE='1970-09-01'
where PLAYERNO=39;

9.2.5 嵌套视图

定义在另一个视图的上面的视图

mysql> create view v_ear_veterans
    -> as
    -> select * from v_veterans
　　 -> where JOINED < 1980;