MySQL基础教程

1 基本操作

根据操作对象的不同，可将 基本操作分为三类，分别为：库操作、表（字段）操作和数据操作。

1.1 库操作

-- 创建一个数据库
create database db_test;

-- 创建一个数据库
create database if not exists db_test;

-- 列出服务器上所有的数据库
show databases;

-- 删除数据库
drop database db_test;

-- 显示db_test数据库中的表
show tables from db_test;

1.2 表操作

-- 查看表信息
desc tb_dept;

-- 创建表
use db_test;
create table tb_dept(
	id int primary key auto_increment,
    name varchar(18),
    description varchar(100)
)；

-- 复制表
create table tb_copy like tb_dept;

-- 删除表，再重新建表；可以还原自增长属性
truncate tb_copy;

-- 删除表
drop table tb_dept;

-- 更改表名
alter table tb_dept rename dept;
rename table dept to tb_dept;

-- 修改列类型，只有当字段只包含空值时才可以修改
alter table tb_dept modify name varchar(30); 

-- 增加列
alter table tb_dept add phone varchar(12);

-- 删除列
alter table tb_dept drop tel;
alter table tb_dept drop column tel;

1.3 数据操作，基本CURD

-- 插入 C
insert into tb_dept(id,name,description) values ('','','');

-- 更新 U
update + 表名 + set + 字段=值 + [where 条件];

-- 删除 R
delete + from + 表名 + [where 条件];

-- 查询 D
select + 字段列表|* + from + 表名 + [where 条件];

1.4 数据操作，高级CUD

(1) 高级插入，蠕虫复制

-- 基本语法
insert into + 表名 + [(*,*)] + select + 字段列表\* + from + 表名;

-- 1）从已有的数据表中拷贝数据到新的数据表
insert into tb_copy select * from tb_dept;

-- 2）可以迅速的让表中的数据膨胀到一定的数量级，多用于测试表的压力及效率
insert into tb_copy select * from tb_copy;

(2) 高级更新

update + 表名 + set + 字段 = 值 + [where 条件] + [limit 更新数量];

(3) 高级删除

delete + from + 表名 + [where 条件] + [limit 删除数量];

1.5 数据操作，高级查询 R

完整表达

select + [select 选项] + 字段列表[(字段别名)]|* 
	   + from + 数据源 
	   + [where 条件] 
	   + [group by 子句] 
	   + [order by 子句] 
	   + [limit 子句];

select 选项

select * from tb_dept;			-- 默认是all
select all * from tb_dept;		-- 保留所有的查询结果
select distinct * from tb_dept;	-- 去重

(1) 字段别名

• 取一个简便的名字
• 解决多表查询的同名字段问题

字段名 + [as] + 别名

(2) 数据源

• 单表数据源
• 多表数据源(笛卡尔积)
• 查询语句(子查询)

-- 多表数据源
select * from + 表名1,表名2,...;

-- 查询语句
select * from + (select * from + 表名) + [as] + 别名;

(3) where 子句

• 比较运算符：>、<、>=、<=、<>、=、
• 比较运算符：like、between ... and ...（闭区间）、in 和 not in
• 逻辑运算符：&&、||、和 !

(4) group by 子句

• 目的是为了（按分组字段）统计数据，并不是为了单纯的进行分组而分组

• 统计函数

  • cout()：统计分组后，每组的总记录数
  • max()：统计每组中的最大值
  • min()：统计每组中的最小值
  • avg()：统计每组中的平均值
  • sum()：统计每组中的数据总和

  -- 将表 student 中的数据按字段 sex 进行分组，并进行统计
  -- count(*) 表示统计组内全部记录的数量
  -- count(字段名) 统计对应字段的非null记录的总数
  select sex,count(*),max(age),min(age),avg(age),sum(age) 
  from student group by sex;
  

• group 排序

  -- group by 进行分组之后，展示的记录会根据分组的字段值进行排序
  -- 默认为升序。当然，也可以人为的设置升序和降序
  group by + 字段名 + [asc/desc];
  

• 函数 group_concat(字段名)

  -- 对分组的结果中的某个字段值进行字符串连接，即保留该组某个字段的所有值
  -- 将表 student 中的数据按字段 sex进行分组，并保留字段 name 的值
  select sex,count(*),group_concat(name) from student group by sex;
  

• 多个字段分组

  -- 将表 student 先按字段 grade 进行分组，再按字段 sex 进行分组
  select *,count(*) from student group by grade,sex;
  

(5) having 子句

having 字句，与where子句一样，都是进行条件判断的，但是 where 是针对磁盘数据进行判断。数据进入内存之后，会进行分组操作，分组结果就需要 having 来处理。思考可知，having 能做 where 能做的几乎所有事情，但是 where 却不能做 having 能做的很多事情。

• 分组统计的结果或者说统计函数只有 having 能够使用

  -- having子句可以对统计函数得到的结果进行筛选，但是where却不能
  select grade,count(*) from student group by grade having count(*) >= 2;
  
  -- 报错：Invalid use of group function
  select grade,count(*) from student where count(*) >= 2 group by grade;
  

• having 能够使用字段别名，where 则不能

  -- 究其原因，where是从磁盘读取数据，而磁盘中数据的名字只能是字段名，别名是数据（字段）进入到内存后才产生的。
  -- 值得注意的是，使用了字段别名，这在无意中就优化了 SQL 并提高了效率，因为少了一次统计函数的计算。
  select grade,count(*) as total from student group by grade having total>=2;
  
  -- 报错：Unknown column 'total' in where clause
  select grade,count(*) as total from student where total >= 2 group by grade;
  

(6) order by 子句

• 可升序和降序，默认是升序

  -- 基本语法，默认是升序
  order by + [asc/desc]；
  

• 多字段排序

  -- 将表 student 中的数据先按年龄 age 升序排序
  -- 对于年龄相同的那些记录，再按班级 grade 降序排序
  select * from student order by age,grade desc;
  select * from student order by age asc,grade desc;
  

(7) limit 子句

• 只用来限制数据量

• 限制起始页、限制数据量

  -- 从0开始，查2个数据
  select * from student limit 0,2;
  

• 实现分页

(8) 连接查询（join）

将多张表（大于等于 2 张表）按照某个指定的条件进行数据的拼接，其最终结果记录数可能有变化，但字段数一定会增加。

-- 基本语法
左表 join 右表

• 交叉连接 = 多表查询（笛卡尔积形式，保留全部数据）

  -- 将表 student 与 class 进行交叉连接
  select * from student cross join class;
  
  -- 将表 student 与 class 进行多表查询
  select * from student,class;
  

• 内连接（需要满足一定的匹配条件）

  -- 基本语法
  左表 + [inner] + join + 右表 + on + 左表.字段 = 右表.字段;
  
  -- 将表 student 与 class 进行内连接
  select * from student inner join class on student.grade = class.grade;
  select * from student join class on student.grade = class.grade;
  
  -- 1）如果两表中有某个表的条件字段名唯一，那么在书写连接条件的时候，可以省略表名，直接书写字段名，MySQL 会自动识别唯一字段名，但不建议这么做
  
  -- 2）将表 student 与 class 进行内连接，起别名，避免结果中有同名字段
  select s.*,c.id as c_id,c.grade as c_grade,room 
  from student as s inner join class as c on s.grade = c.grade;
  
  -- 3）内连接可以没有连接条件，即可以没有on及之后的内容，这时内连接的结果全部保留，与交叉连接的结果完全相同。
  
  -- 4）而且在内连接的时候可以使用where关键字代替on，但不建议这么做，因为where没有on的效率高。
  

• 外连接：以某张表为主表，取出里面的所有记录，然后让主表中的每条记录都与另外一张表进行连接，不管能否匹配成功，其最终结果都会保留，匹配成功，则正确保留；匹配失败，则将另外一张表的字段都置为 NULL.

  -- 基本语法
  左表 + left\right + join + 右表 + on + 左表.字段 = 右表.字段;
  
  -- 左右连接
  left join：左外连接（左连接），以左表为主表；
  right join：右外连接（右连接），以右表为主表。
  
  -- 无论以那张表为主表，其外连接的结果（记录数量）都不会少于主表的记录总数。
  -- 左连接与右连接有主表差异，但显示的结果都是：左表的数据在左边，右表的数据在右边。
  
  -- 将表 student 与 class 进行左连接
  select s.*,c.id as c_id,c.grade as c_grade,room 
  from student as s left join class as c on s.grade = c.grade;
  
  -- 将表 student 与 class 进行右连接
  select s.*,c.id as c_id,c.grade as c_grade,room 
  from student as s right join class as c on s.grade = c.grade;
  

• 自然连接（natural join）

  自然连接是自动匹配连接条件，以两表中同名字段作为匹配条件，如果两表有多个同名字段，那就都作为匹配条件。自然连接与普通连接的区别：1）自动匹配同名字段；2）显示的时候合并同名字段。

  • 自然内连接

    -- 基本语法
    左表 + nature join + 右表;
    
    -- 将表 student 与 class 进行自然内连接
    select * from student natural join class;
    

  • 自然外连接

    -- 基本语法
    左表 + nature + left/right + join + 右表;
    
    -- 将表 student 与 class 进行自然左外连接
    select * from student natural left join class;
    
    -- 将表 student 与 class 进行自然右外连接
    select * from student natural right join class;
    

  • 用内连接和外连接模拟自然连接，关键就在于使用同名字段作为连接条件及合并同名字段。

    -- 基本语法
    -- using内部的字段名就是作为连接条件的字段，也是需要合并的同名字段。
    左表 + inner/left/right + join + 右表 + using(字段1, 字段2, ...);
    
    -- 将表 student 与 class 进行自然左外连接
    select * from student natural left join class;
    
    -- 用左外连接模拟自然左外连接
    select * from student left join class using(id,grade);
    

(9) 联合查询（union）

将多次查询（多条 select 语句）的结果，在字段数相同的情况下，在记录的层次上进行拼接；每条select 语句获取的字段数相同，但与字段类型无关。

连接查询是水平拼接，联合查询是垂直拼接。

-- 基本语法
select 语句1 + union + [union选项] + select 语句2 + ...;

-- union 选项
all：无论重复与否，保留所有记录；
distinct：表示去重，为默认选项。

-- 联合查询，默认去重
select * from class union distinct select * from class;

-- 联合查询，保留所有记录
select * from class union all select * from class;

-- 在 student 表中，按年龄，男升女降
-- 注意1：想在联合查询中使用order by，必须将select语句用括号括起来
-- 注意2：使order by生效，必须将其与limit搭配使用，而limit的限定数设置为一个非常大的数即可
(select * from student where gender = "boy" order by age asc)
union
(select * from student where gender = "girl" order by age desc);

(10) 子查询

子查询是指，查询在某个查询结果之上进行的，一条 select 语句内部包含了另外一条 select 语句。

按按结果分类，

• 标量子查询：子查询得到的结果是一行一列，出现的位置在 where 之后

  select * from student where c_id = (select id from class where grade = "PM3.1");
  

• 列子查询：子查询得到的结果是一列多行，出现的位置在 where 之后

  select * from student where c_id in (select id from class);
  
  select * from student where c_id = any (select id from class);
  select * from student where c_id = some (select id from class);
  select * from student where c_id = all (select id from class);
  
  select * from student where c_id != any (select id from class);
  select * from student where c_id != some (select id from class);
  select * from student where c_id != all (select id from class);
  

• 行子查询：子查询得到的结果是多行一列（多行多列），出现的位置在 where 之后

  -- 列子查询
  select * from student 
  where (age, height) = (select max(age), max(height) from student);
  

• 表子查询：子查询得到的结果是多行多列，出现的位置在 from 之后

  -- 表子查询
  select * from 
  (select * from student order by height desc) as student 
  group by c_id;
  

按位置分类，

• from子查询：子查询出现在 from 之后

• where 子查询：子查询出现在 where 条件之中

• exists 子查询：子查询出现在 exists 里面\

  -- exists 子查询，看括号内查询记录是否为空，即是否存在满足条件的记录
  select * from student where
  exists(select * from class where id = 3);
  

2 主键（primary key）

(1) 主键分类

• 业务主键，即使用真实的业务数据作为主键，例如学号、课程编号等等，很少使用；
• 逻辑主键，即使用逻辑性的字段作为主键，字段没有业务含义，经常使用。

(2) 主键必须满足：唯一性，不能为空

(3) 添加主键（3种方式）

-- 1. 在创建表的时候，直接在字段之后，添加primary key关键字，不能添加复合主键
create table my_table(
    number char(10) primary key comment '学号',
    name varchar(20) not null comment '姓名'
)charset utf8;

-- 2. 在创建表的时候，在所有的字段之后，使用primary key(主键字段列表)来创建主键
create table my_table(
    number char(10) not null comment '学号',
    course char(10) not null comment '课程编号',
    primary key(number,course)
);

-- 3. 当表创建完之后，额外追加主键
--    可以直接追加主键，也可以通过修改表字段的属性追加主键
--    注意：追加的字段必须保证唯一性
create table my_table(
    course char(10) not null comment '课程编号',
    name varchar(10) not null comment '课程名称'
);
alter table my_table modify course char(10) primary key comment '课程编号';
alter table my_table add primary key(course); -- 推荐使用

(4) 删除主键（主键无法更新，只能先删除，再添加）

alter table my_table drop primary key;

(5) 主键冲突（duplicate key）

-- 假设表 my_class 有两个字段,room 和 班级名，其中班级名是主键
-- 进行插入操作时，班级名发生主键冲突

-- 1. 主键冲突，进行更新操作
insert into my_class values ('B315','PM3527')
on duplicate key update room = 'B315'; -- 冲突处理，更新指定的值

-- 2. 主键冲突，选择替换操作
replace into my_class values ('B315','PM3528');

3 外键（foreign key）

(1) 定义：外面的键。如果一张表中有一个非主键的字段指向另外一张表的主键，那么将该字段称之为外键。每张表中，可以有多个外键。

(2) 注意：外键要求字段本身是一个索引（普通索引）如果字段本身没有索引，外键就会先创建一个索引，然后才创建外键。

(3) 新增外键

-- 1）在创建表的时候，增加外键
create table tb_foreign(
    id int primary key auto_increment,
    name varchar(20) not null comment '学生姓名',
    c_id int comment '班级表ID',
    foreign key(c_id) references class(id)   -- 增加外键
);

-- 2）在创建表之后，增加外键
-- 基本语法
alter table + 表名 
			+ add[constraint + 外键名字] 
			+ foreign key(外键字段) 
			+ references + 外部表名(主键字段);
			
-- 创建外键
create table tb_foreign(
    id int primary key auto_increment,
    name varchar(20) not null comment '学生姓名',
    c_id int comment '班级表ID'
);

-- 增加外键
alter table tb_foreign 
add constraint test_foreign		-- 指定外键名
foreign key(c_id)				-- 指定外键字段
references class(id);			-- 引用外部表主键

(4) 删除外键

-- 基本语法
alter table + 表名 + drop foreign key + 外键名字;

-- 删除外键
alter table tb_foreign drop foreign key test_foreign;

(5) 外键条件

• 外键要存在，首先必须保证表的引擎是 InnoDB（默认的存储引擎），如果不是 InnoDB 存储引擎，那么外键可以创建成功，但没有约束作用
• 外键字段的字段类型，必须与父表的主键类型完全一致
• 每张表中的外键名称不能重复
• 新增外键的字段，如果数据已经存在，那么要保证数据与父表中的主键对应

(6) 外键约束（外键的作用，默认作用）

• 父表和子表的定义
  • 父表：指外键所指向的表
  • 子表：拥有外键的表
• 约束子表，在子表进行数据的写操作（增和改）的时候，如果对应的外键字段新值在父表找不到对应的匹配，那么操作就会失败。
• 约束父表，在父表进行数据的写操作（删和改，且涉及主键）的时候，如果对应的主键字段旧值在子表已经被数据引用，那么操作就会失败。

(7) 外键约束（定制操作）

• 三种模式（针对父表）

  • district：严格模式（默认），父表不能删除或更新一个已经被子表数据引用的记录
  • cascade：级联模式，父表的操作，对应子表关联的数据也跟着被删除
  • set null：置空模式，父表的操作之后，子表对应的数据（外键字段）被置空。进行删除置空操作的时候，有一个前提，那就是，子表的外键字段必须允许为空，否则的话，操作是无法成功的。

  -- 基本语法
  foreign key(外键字段) + references + 父表(主键字段) 
  					 + [on delete + 模式 + on update + 模式];
  					 
  -- 通常一个合理的做法（约束模式）是：删除的时候，子表被置空；更新的时候，子表进行级联操作。
  -- 创建外键，指定模式：删除置空，更新级联
  create table my_foreign3(
      id int primary key auto_increment,
      name varchar(20) not null,
      c_id int,
      foreign key(c_id)		-- 增加外键
      references class(id)	-- 引用父表
      on delete set null		-- 指定删除模式
      on update cascade		-- 指定更新模式
  );
  

4 数据库优化

4.1 MySQL索引实现

(1) MyISAM存储引擎与非聚集索引

MyISAM使用B+ 树作为索引结构。叶节点的data域存放的是数据记录的地址。MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。

主索引（Primary key）和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

(2) InnoDB存储引擎与聚集索引

InnoDB也使用B+ 树作为索引结构。InnoDB的表数据文件本身就是索引文件，是按B+ 树组织的一个索引结构。

这棵树的叶节点data域保存了完整的数据记录，这种索引叫做聚集索引，这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

InnoDB的辅助索引data域存储相应记录的主键值而不是地址。所以，不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+ 树，非单调的主键会造成在插入新记录时数据文件为了维持B+ 树的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

4.2 范式

4.3 事务

Mysql数据库，当且仅当引擎是InnoDB，才支持事务。

(1) 事务的四大特性

• 原子性（Atomicity）
• 一致性（Consistency）
• 隔离性（Isolation，又称独立性）
• 持久性（Durability）

(2) 事务的操作

start commit;  -- 开启事务，同begin
rollback;  -- 结束事务，并撤销所有未提交的修改
commit;  -- 提交事务，并永久化对数据库的所有修改
end commit;  -- 结束事务

savepoint {custom_name};  -- 在事务中创建保存点，后面可以回滚到保存点
release {custom_name};  -- 删除保存点
rollback to {custom_name}; -- 回滚到保存点

set autocommit = 0;  -- 禁止自动提交事务
set autocommit = 1;  -- 开启自动提交事务

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

(2) 事务的隔离级别

事务隔离级别	脏读	不可重复读	幻读
read-uncommitted	是	是	是
read-commit(Oracle默认隔离级别)	否	是	是
repeatable-read (mysql默认级别)	否	否	是
serializable	否	否	否

• 读不提交(Read Uncommited，RU) ：这种隔离级别下，事务间完全不隔离，会产生脏读，可以读取未提交的记录，实际情况下不会使用
• 读提交(Read commited，RC)： 本事务读取到的是最新的数据（其他事务提交后的）。问题是，在同一个事务里，前后两次相同的SELECT会读到不同的结果（不重复读）
• 可重复读（Repeatable Read，RR）： 在同一个事务里，SELECT的结果是事务开始时时间点的状态，因此，同一个事务同样的SELECT操作读到的结果会是一致的。但是，会有幻读现象
• 串行化（SERIALIZABLE）。读操作会隐式获取共享锁，可以保证不同事务间的互斥。事务A在操作数据库时，事务B只能排队等待。这种隔离级别很少使用，吞吐量太低，用户体验差。

(3) 查看和设置事务的隔离级别

-- 查看当前会话隔离级别
select @@tx_isolation;

-- 查看系统当前隔离级别 
select @@global.tx_isolation;

-- 设置当前会话的事务隔离级别
set session transaction isolation level read uncommitted;
set session transaction isolation level read committed;
set session transaction isolation level repeatable read;
set session transaction isolation level serializable;

-- 设置系统的事务隔离级别
set global transaction isolation level read uncommitted;
set global transaction isolation level read committed;
set global transaction isolation level repeatable read;
set global transaction isolation level serializable;

4.4 视图

4.5 存储过程

此文为个人学习MySQL时结合网络资源所作总结，记于此处，以便日后查看，如有版权问题请联系本人。未完待续…