1. MySQL 基础

• 数据库：用于存储和管理数据的仓库。
• 数据库的特点：
  • 持久化存储数据，就是一个文件系统。
  • 方便存储和管理数据。
  • 使用了统一的方式操作数据库 -- SQL。
• 登录 MySQL 的命令： mysql [-hIP地址] -u账号 -p密码 或 mysql --host=ip --user=账号 --password=密码
• 退出 MySQL 的命令：exit、quit
• MySQL 的卸载：
  • 在安装目录下找到 my.ini 文件，打开并记录 datadir 的值
  • 卸载 MySQL
  • 删除 datadir 所对应路径中 MySQL 文件夹
• MySQL 服务启动
  • 通过界面手动操作
  • 使用管理员打开 cmd
    • net start MySQL：启动 MySQL 服务
    • net stop MySQL ：关闭 MySQL 服务
• MySQL 安装目录结构：
  • bin：二进制可执行文件
  • data：数据目录，放日志文件、数据文件等
  • include：放 C 语言的头文件
  • lib：放软件运行所需要的库文件
  • share：放置 MySQL 的错误信息
  • my.ini：数据库的配置文件
• MySQL 数据目录：
  • 每个数据库对应数据目录的一个文件夹，.frm 文件就是对应的表
• MySQL 默认数据库：
  • information_schema ：描述 MySQL 信息，里面存的视图，没有对应的本地文件夹。
  • MySQL：核心数据库。
  • performance_schema：对性能提升做操作的数据库。
  • test：用于用户测试的数据库。
• SQL(Structured Query Language 结构化查询语言)：定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方。
• SQL 语句可以多行书写，以分号结尾。
• SQL 语句不区分大小写，关键字建议用大写。
• 注释：
  • -- 内容 ：单行注释，必须加空格
  • # 内容 ：单行注释
  • /* 内容 */ ：多行注释
• SQL 分类：
  • DDL (Data Definition Language) ：数据定义语言。用来定义数据库对象，数据库，表，列等。关键字：create, drop,alter 等。
  • DML (Data Manipulation Language)：数据操作语言。用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等。
  • DQL (Data Query Language) 数据查询语言。用来查询数据库中表的记录(数据)。关键字：select, where 等
  • DCL (Data Control Language) 数据控制语言。用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等
• 数据表中数据类型：
  • int、double[(m,n)]，m 为数字总宽度，n 为小数点后几位。
  • date：日期类型，只包含年月日，yyyy-MM-dd，计算 date 日期差可以使用 datediff(date1,date2);
  • datetime：日期类型，包含年月日时分秒，yyyy-MM-dd HH:mm:ss
  • timestamp：时间戳类型，包含年月日时分秒，与 datatime 区别的，如果不给这个字段赋值，或者赋值为 null ，则默认使用当前系统时间自动赋值。
  • varchar：字符串，需要定义字符长度，如 varchar(20)

2. DDL

• 操作数据库：CRUD
  • C(Create 创建)：create database if not exists databasename character set utf8; ，注意不能是 utf-8 。
  • R(Retrieve 查询)：
    • 查询所有数据库： show databases;
    • 利用查询某个数据库的创建语句查询数据库的字符集 show create database databasename;
  • U(Update)：修改字符集 alter database 数据库名称 character set 字符集名称;
  • D(Delete)：drop database if exists 数据库名称;
  • 使用数据库：
    • 使用数据库：use 数据库名称;
    • 查询当前使用的数据库：select database();
• 操作表 CRUD

  • C(Creat)

    creat table 表名(
        列名1 数据类型1，
        列名2 数据类型2
    )
    

    复制表：create table 表名 like 被复制的表名;

  • R(Retrieve 查询)

    • 查询库中所有表：show tables;
    • 查询表结构：desc 表名;

  • U(Update)

    • 修改表名：alter table 表名 rename to 新的表名;
    • 修改表的字符集：alter table 表名 character set 字符集名称;
    • 添加一列：alter table 表名 add 列名 数据类型;
    • 修改列名称和类型：alter table change 列名 新列名 新数据类型; 和 alter table 表名 modify 列名 新数据类型;
    • 删除列：alter table 表名 drop 列名;

  • D(Delete)

    • drop table [if exists] 表名;

3. DML

• DML：增删改表中的数据
  • 添加数据
    • insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
    • 如果表名后不定义列名，则默认给所有列添加数据 insert into 表名 values(值1,值2,...值n);，必须是所有列，可以为 NULL ，但不能省略。
    • 除了数字类型外，其他类型都需要用引号(单双都行)引起来。
    • 同时向表中添加多条数据 insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n),(值1,值2,...值n);
  • 删除数据
    • delete from 表名 [where 条件];
    • delete 的多表使用方法：
      delete t1 from t1,t2 where t1.id=t2.id; 将 t1 表中在 t2 表中有匹配记录的值全部删掉。
    • 如果要删除所有记录
      • delete from 表名; 不推荐使用，一条一条删除，效率慢。
      • truncate table 表名 推荐使用，先删除表，再创建表，效率高。
  • 修改数据
    • update 表名 set 列名1=值1,列名2=值2,... [where 条件];
    • 不加任何条件，会修改全部记录。

4. DQL

• DQL：查询表中的数据

  select
      字段列表
  from
      表名列表
  where
      条件列表
  group by
      分组字段
  having
      分组之后的条件
  order by
      排序
  limit
      分页限定
  

  • 去除重复 select distinct address from student; 多个字段的话结果集完全一样才能去重。
  • 计算列：
    • 可以对数据进行四则运算和取模运算。(一般只会对数值型进行计算)
      select name,math,english,math+english from student;
    • 有 null 参与的运算结果为 null ，可以通过 ifnull() 函数解决。
      select name,math,english,math+ifnull(english,0) from student;
  • 起别名：select name,math,english,math+ifnull(english,0) as sum from student; ，as 关键字也可以省略。
  • 运算符：
    • <、>、<=、>=、=、!=、<>
    • between...and，前后都包含
    • in(集合)，中间加不加空格都可以
    • like：模糊查找，_ 单个任意字符，% 多个任意字符。
    • is null 和 is not null，注意 null 值不能用 = 或者 != 或者 <> 号来判断
    • and 或 && ，推荐 and
    • or 或 ||
    • not 或 !
  • 排序查询
    • order by 排序字段1 排序方式1, 排序字段2 排序方式2;
    • ASC，升序(默认)，DESC，降序
  • 聚合函数：将一列数据作为一个整体，进行纵向计算
    • count：计算个数，一般选择非空的列，参数只能为一个
      • count(*)：当此行数据有不为 null 的列，就算 1 个数据
    • max 、min、sum、avg
    • 聚合函数的计算，会排除 null 值，可以用 ifnull 函数解决
  • 分组查询：
    • group by 分组字段;
    • 分组之后查询的字段必须为分组字段或者聚合函数
      select sex,AVG(math) from student group by sex;
    • where 和 having 的区别：
      where 后不可以跟聚合函数，having 可以进行聚合函数的判断。
      where 在分组之前进行限定，如果不满足条件，则不参与分组。having 在分组之后进行限定，如果不满足结果，则不会被查询出来。例如：
      按照性别分组。分别查询男、女同学的平均分，人数 要求：分数低于 70 分的人，不参与分组，分组之后。人数要大于 2 个人。
      select sex, avg(math), count(id) from student where math>70 group by sex having count(id)>2;
  • 分页查询：
    • limit 开始的索引，每页查询的条数，limit 是 MySQL 特有的
    • 开始的索引 = (当前页码 - 1) * 每页显示的条数

    • -- 每页显示3条记录
      select * from student limit 0,3; -- 第1页
      select * from student limit 3,3; -- 第2页
      select * from student limit 6,3; -- 第3页
      

5. MySQL 约束

• 主键约束：primary key
  • 添加主键：
    • alter table stu modify id int primary key;
    • alter table stu add primary key(id);
  • 删除主键：alter table stu drop primary key;
• 自动增长：auto_increment
  • 添加自动增长：alter table stu modify id int auto_increment;
  • 删除自动增长：alter table stu modify id int;
  • 传入值为 null 的话，会自动增长，也可以手动指定值，会以新值为基准继续增长。
• 非空约束：not null
  • 添加约束：alter table stu modify name varchar(20) not null;
  • 删除约束：alter table stu modify name varchar(20);
• 唯一约束：unique，唯一约束限定的列的值可以有多个 null 。
  • 添加约束：alter table stu modify name varchar(20) unique;
  • 删除约束：alter table stu drop index colname;
• 外键约束：foreign key，添加级联操作以后，主键所在的表中的数据发生更改，外键所在表中的数据也发生相应更改。
  • 添加约束 ：alter table 表名 add [constraint 外键名称] foreign key(外键字段名称) references 主表名称(主表列名称); 不写外键名的话系统会自动分配一个唯一名称的外键。
  • 删除外键：alter table 表名 drop foreign key 外键名称;
  • 外键值可以为 null ，但不能为主表的主列中不存在的值。
  • 级联更新：ON UPDATE CASCADE(添加到添加外键约束的后面)
  • 级联删除：ON DELETE CASCADE
• default：设置默认值。
• 创建表的时候，primary key(col1,col2) 代表联合主键。

6. 数据库设计

• 多表之间的关系：

  • 一对一：如人和身份证，可以在任意一方添加唯一外键(unique)指向另一方的主键。
  • 一对多：部门和员工，在多的一方建立外键，指向另一方的主键。
  • 多对多：学生和课程，一个学生可以选择很多门课程，一个课程也可以被很多学生选择。实现多对多关系需要借助第三张中间表，中间表至少包含两个字段，两个字段作为第三张表的外键，分别指向两张表的主键，例如，这时候可以添加一个有学号和课程号的表，两个键作为联合主键。

• 对于一张表：

  学号	姓名	系名	系主任	课程名称	分数
  100	张三	经济系	吴红	高等数学	99

  1. 函数依赖：A-->B，如果通过 A 属性(属性组)的值，可以确定唯一 B 属性的值。则称 B 依赖于 A，例如：学号--> 姓名、(学号，课程名称) --> 分数。
  2. 完全函数依赖：A-->B，如果 A 是一个属性组，则 B 属性值的确定需要依赖于 A 属性组中所有的属性值。例如：(学号，课程名称) --> 分数。
  3. 部分函数依赖：A-->B，如果 A 是一个属性组，则 B 属性值的确定只需要依赖于 A 属性组中某一些值即可。例如：（学号，课程名称） -- > 姓名
  4. 传递函数依赖：A-->B, B -- >C ，如果通过 A 属性(属性组)的值，可以确定唯一 B 属性的值，在通过 B 属性(属性组)的值可以确定唯一 C 属性的值，则称 C 传递函数依赖于 A。例如：学号--> 系名，系名--> 系主任。
  5. 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码，例如：该表中码为：(学号，课程名称)。
     主属性：码属性组中的所有属性。
     非主属性：除过码属性组的属性。

• 范式：

  • 第一范式（1NF）：每一列都是不可分割的原子数据项。

  • 第二范式（2NF）：在 1NF 的基础上，非码属性必须完全依赖于码（在 1NF 基础上消除非主属性对主码的部分函数依赖）

  • 第三范式（3NF）：在 2NF 基础上，任何非主属性不依赖于其它非主属性（在 2NF 基础上消除传递依赖）

  • 上面经过第二范式后变为

    学号	课程名称	分数
    100	高等数学	99

    学号	姓名	系名	系主任
    100	张三	经济系	吴红

  • 经第三范式后变为：

    学号	课程名称	分数
    100	高等数学	99

    系名	系主任
    经济系	吴红

    学号	姓名	系名
    100	张三	经济系

7. 数据库的备份和还原

• 备份数据库：mysqldump -u用户名 -p密码 要备份的数据库名称 > 要保存的路径
• 还原数据库：登录数据库 → 创建数据库 →使用数据库 → 执行文件 source 文件路径。

8. 多表查询

• 隐式内连接：使用 where 条件消除无用数据，例如 select * from emp,demp where emp.dept_id=dept.id;
• 显示内连接：select 字段列表 from 表名1 [inner] join 表名2 on 条件;
  例如：select * from emp [inner] join dept on emp.dept_id = dept.id;
• 左外连接：select 字段列表 from 表1 left join 表2 on 条件;，查询的是左表全部数据以及其交集部分。
• 右外连接：select 字段列表 from 表1 right join 表2 on 条件;
• 当子查询结果是单行单列的，可以作为条件判断。
• 当子查询结果是多行单列的，子查询可以作为条件，用运算符 in 来判断。
• 当子查询结果是多行多列的，子查询可以作为一张虚拟表参与查询。
• 表起了别名以后，以前的表名无法使用。
• MySQL 子查询中有 ALL、ANY(SOME)、IN、NOT IN、EXISTS、NOT EXISTS 等关键字。
  • ANY 和 SOME 等效。
  • ALL 代表所有，ANY 代表任意一个。
    • SELECT * FROM emp t1 WHERE t1.salary >= ALL(SELECT salary FROM emp); -- 查询薪资最高的人
    • SELECT * FROM emp t1 WHERE t1.salary > ANY(SELECT salary FROM emp); -- 查询结果中去除工资最低的人
  • EXISTS 相当于一个判断条件，将前面查询的结果逐条进行判断，当 EXISTS 条件语句有返回记录行的时候为 true ，没有返回记录行的时候为 false。
    • SELECT id,ename,mgr FROM emp t1 WHERE EXISTS(SELECT * FROM emp t2 WHERE t2.id = t1.mgr); -- 查询所有有上级的人
  • EXISTS 比 IN 要快。

9. 事务

• 事务：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。
• 操作：
  • 开启事务：start transaction;
  • 回滚：rollback;
  • 提交：commit；
  • 当开启事务但未提交的时候，在当前窗口查询可以查询到已经改变的数据(但实际未改变，换一个窗口查询或者重开窗口查询都是之前的，除非提交事务)。
• MySQL 默认自动提交事务，一条 DML (增删改) 语句会自动提交一次事务。手动提交需要先开启事务再提交。
• 查看事务的默认提交方法：select @@autocommit; 1 为自动提交，0 为手动提交。
• 修改事务的默认提交方式：set @@autocommit = 值;
• 事务的四大特征：
  • 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
  • 持久性：当事务提交或回滚后，数据库会持久化的保存数据。
  • 隔离性：多个事务之间，相互隔离。
  • 一致性：事务操作前后，数据总量不变。
• 事务存在的问题：
  • 概念：多个事务之间是隔离的，相互独立的，但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别可以解决这些问题。
  • 脏读：一个事务，读取到另一个事务中没有提及的数据。
  • 不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。
  • 幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条记录，则第一个事务查询不到自己的修改。
• 事务的隔离级别：
  • read uncommitted：读未提交。产生问题：脏读，不可重复读，幻读。
  • read committed：读已提交(Oracle 默认)。产生问题：不可重复读、幻读。
  • repeatable read：可重复读(MySQL 默认)。产生的问题：幻读。
  • serializable：串行化。可以解决所有问题，类似于锁机制，同时只能一个事务访问数据。
  • 隔离级别从上到下，安全越来越高，效率越来越低。
  • 数据库查询隔离级别：select @@tx_isolation;
  • 数据库设置隔离级别：set global transaction isolation level 隔离级别字符串; (设置完以后重新打开窗口才会生效)

10. 用户管理和权限管理

• DCL：管理用户、授权
• 管理用户：
  • 添加用户：create user '用户名'@'主机名' identified by '密码';
  • 删除用户：drop user '用户名'@'主机名';
  • 修改用户密码：
    • update user set password = password('新密码') where user='用户名';
    • set password for '用户名'@'主机名' = password('新密码');
  • 查询用户：
    • 切换到 MySQL 数据库 。use mysql;
    • 查询 user 表。 select * from user;
  • 通配符 % 代表在任意主机使用用户登录数据库。
• MySQL 中忘记了 root 用户的密码。
  • 停止 MySQL 服务：cmd(管理员) → net stop mysql
  • 使用无验证方式启动 MySQL 服务：mysql --skip-grant-tables
  • 打开新的 cmd 窗口，输入 mysql ，敲回车登录。
  • use mysql;
  • update user set password = password('新密码') where user='root';
  • 关闭两个窗口。
  • 打开任务管理器，手动结束 mysql.exe 进程。
  • 启动 MySQL 服务。
  • 使用新密码登录。
• 权限管理：

  • 查询权限：show grants for '用户名'@'主机名';

  • 授予权限：grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

    例如：给张三用户授予所有权限，在任意数据库的任意表上
    GRANT ALL ON *.* TO 'zhangsan'@'localhost';

  • 撤销权限：revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

11. 主从复制

mysql 读写分离

mysql 主从复制

12. 索引

mysql 索引

13. explain

mysql explain

14. 扩展语法

14.1 select

• 随机抽取一条数据

  SELECT * from `user` ORDER BY RAND() limit 1;
  

14.2 insert

添加元组，主键或唯一键冲突时，有以下几种解决方案：

14.2.1 replate into

replace into tb_name(col_name, …) values(…)

• 无重复数据时直接插入，Affected rows: 1
• 有重复数据时，先删除后插入，Affected rows: 2
• 在有外键的情况下，对主表进行这样操作时，因为如果主表存在一条记录，被从表所用时，直接使用 replace into 是会报错的，这和 replace into 的内部原理是相关（先删除然后再插入）。
• 表中有一个自增的主键,带来的问题
  replace 操作在自增主键的情况下，遇到唯一键冲突时执行的是 delete+insert ,但是在记录 binlog 时,却记录成了 update 操作, update 操作不会涉及到 auto_increment 的修改。备库应用了 binlog 之后，备库的表的 auto_increment 属性不变。如果主备库发生主从切换，备库变为原来的主库，写新的主库则有风险发生主键冲突

14.2.2 ignore

若有冲突，则忽略，不添加

insert ignore into tb_name values(...)

14.2.3 on duplicate key update

若冲突，则进行 update 操作。

insert into user(name, age) values('111', 18) on duplicate key update name='222',age=19;

• 无重复数据时直接插入，Affected rows: 1
• 有重复数据时，，Affected rows: 2 (官方定义)
• 若 update 后面的值也会引起冲突，则报错

15.知识补充

• IF 语句的用法：

  /* 性别取反 */
  update user 
  set sex=if(sex='m','f','m');
  

• CASE 的用法：

  update user
  set var = case grade when 'A' then 'a' else  'B' then 'b' else 'c' end;
  

• SQL 语句的执行顺序：

  (8) SELECT (9)DISTINCT<select_list>
  (1) FROM <left_table>
  (3) <join_type> JOIN <right_table>
  (2)         ON <join_condition>
  (4) WHERE <where_condition>
  (5) GROUP BY <group_by_list>
  (6) WITH {CUBE|ROLLUP}
  (7) HAVING <having_condition>
  (10) ORDER BY <order_by_list>
  (11) LIMIT <limit_number>
  

• MySQL 报错：MySQL Illegal mix of collations for operation 'like'
  在 MySQL 5.5 以上, 若字段类型是 time,date,datetime
  在 select 时如果使用 like '%中文%' 会出现这个错误
  在旧版的 MySQL 是不会出现错误的.
  升到 MySQL 5.5 以上, 必需改成 like binary '%中文%' 即可避免出现这个错误。

• sql hints

  有些数据库支持 sql hints, 可以通过 sql hints 指定走哪个数据库哪个索引 ，如果 sql hints 有误，不会报错，类似于注释。比如强制从主库读取

  select /*master*/ * from com_info where id = 1