MySQL的学习与使用

admin管理员组

文章数量:1547971

MySQL的学习与使用

本文是基于Windows 10系统环境，学习和使用MySQL：

• Windows 10
• mysql-5.5.40-win64.msi for Windows

---

一、MySQL相关命令

（1）登入MySQL

• 打开cmd
  
• 在命令行终端，键入以下命令

mysql -uroot -p

（2）查看MySQL中的数据库

• 在命令行终端，键入以下命令

show databases; # 一定要加“;”号

（3）创建一个新的数据库

• 在命令行终端，键入以下命令

create database day15; # 创建一个名为day15的数据库
create database day16 default character set utf8; # 创建并设置字符集

• 提示以下内容，创建成功
  

（4）删除一个数据库

• 在命令行终端，键入以下命令

drop database day15; # 删除一个名为day15的数据库

（5）查看一个数据库的默认字符集

• 在命令行终端，键入以下命令

show create database day15; # 查看一个名为day15数据库的默认字符集

（6）修改一个数据库的默认字符集

• 在命令行终端，键入以下命令

alter database day15 default character set gbk; # 修改day15数据库的默认字符集

（7）进入一个数据库

• 在命令行终端，键入以下命令

use day15; # 只有先进入数据库，才能操作该数据库下的表

（8）显示数据表

• 在命令行终端，键入以下命令

show tables; # 显示数据表

（9）创建数据表

• 在命令行终端，键入以下命令

create table student(sid int, sname varchar(20), sage int); # 创建数据表

（10）删除数据表

• 在命令行终端，键入以下命令

drop table student; # 删除数据表

（11）修改数据表的名字

• 在命令行终端，键入以下命令

alter table student rename teacher; # 修改数据表的名字

（12）查询数据表的信息

• 在命令行终端，键入以下命令

desc student; # 查询数据表的信息

（13）增加数据表的一个新字段

• 在命令行终端，键入以下命令

alter table student add column sgender varchar(2); # 增加数据表的一个新字段

（14）删除数据表的一个字段

• 在命令行终端，键入以下命令

alter table student drop column sgender; # 删除数据表的一个字段

（15）修改数据表的一个字段的类型和约束

• 在命令行终端，键入以下命令

alter table student modify sname varchar(50) unique; # 修改数据表的一个字段的类型

（16）修改数据表的一个字段的名字

• 在命令行终端，键入以下命令

alter table student change sname name varchar(50); # 修改数据表的一个字段的名字

（17）增加数据

• 在命令行终端，键入以下命令

insert into student values(1,'xuzheng',10); # 增加一条数据，需要全部填写
insert into student(sid, sname) values(3,'wang'); # 增加一条数据，可以部分填写

（18）删除数据

• 在命令行终端，键入以下命令

delete from student where sid=3; # 删除表中sid=3的数据
delete from student; # 删除表的所有内容，不能重置删除表的约束，可以回滚（事务）
# 删除表的所有内容，会重置删除表的约束，如自增长约束，彻底删除，不可以回滚
truncate table student; 

（19）修改数据

• 在命令行终端，键入以下命令

update student set sage=30; # 修改数据表所有age字段的值为30，此处为全部修改
update student set sage=30 where sid=3; # 修改数据表中sid=3的age字段的值为30
# 修改数据表中sid=3的多个字段
update student set sname='li', sage=30 where sid=3; 

（20）查询数据

• 查询所有列

select * from student; # 查询表的所有列

• 查询指定列

select sid,sname from student; # 查询表的所有列
select sid as '编号',sname as '姓名' from student; # 查询表的指定列

• 查询时增加一个常量列

select sid,sname，sage,'China' as country from student; # 查询表时增加一个常量列

• 查询时合并列

select sid,sname,(servlet+jsp) as total from student; # 查询时合并列
# 注意事项，只能合并数值类型的字段

• 查询时去除重复记录

select distinct(gender) from student; # 查询时合并列
# 注意事项，只能合并数值类型的字段

（21）条件查询

• 逻辑条件
• and(与) or(或)

 # 查询表中gender为female与country为China的列
select * from student where gender='female' and country='China';

• 比较条件
• < > >= <= = <>(不等于) between and

 # 查询表中sage>20的列
select * from student where sage>20;
select * from student where sage between 20 and 30; # sage>=20 and sage<=30

• 判空条件
• is null is not null =’’ <>’'

 # 查询表中sname为空的列
select * from student where sname is null;
select * from student where sname is null or sname='';
select * from student where sname is not null and sname<>'';

• 模糊条件
• like
• % 匹配任意多个字符
• _ 匹配任意一个字符

select * from student where sname like '张%'; # 张三  张大大
select * from student where sname like '张_'; # 张三

• 聚合查询
• sum() avg() max() min() count()

 # 查询表中所有成员的年龄之和
select sum(sage) from student;

 # 查询表中所有成员的平均年龄
select avg(sage) from student;

 # 查询表中所有成员的最大年龄
select max(sage) from student;

 # 查询表中所有成员的最小年龄
select min(sage) from student;

 # 查询表中所有成员的数量
select count(*) from student;

• 分页查询
• 起始行从0开始

 # 查询表中从标号为0的记录，查询两行
select * from student limit 0,2;
 # 查询表中从标号为3的记录，查询两行
select * from student limit 2,2;

• 排序查询
• asc：数值递增 、字母(a-z)
• desc：数值递减、字母(z-a)

 # 年龄正序查询
select * from student order by sage asc;
 # 年龄逆序查询
select * from student order by sage desc;
 # 多条件排序
select * from student order by servlet asc, jsp desc;

• 分组查询

 # 查询男女各多少人
select gender, count(*) as '人数' from student group by gender;

• 分组之前的条件使用where关键字，分组之后的条件使用having关键字

 # 查询人数大于2个的性别
select gender, count(*) as total from student group by gender having total>2;

• 关联查询

# 查询员工姓名和部门名
# 多表查询规则：1)确定查询哪些表	2)确定查询哪些字段	3)确定表和表之间的连接条件
# 内连接查询(只有满足条件的结果才可以显示)
select empName, deptName ---- 2)确定查询哪些字段
	from employee, dept  ---- 1)确定查询哪些表
	where employee.deptId=dept.id ---- 3)确定表和表之间的连接条件
# 内连接查询另一种写法
select empName, deptName ---- 2)确定查询哪些字段
	from employee   	 ---- 1)确定查询哪些表 
	inner join dept		 ---- 3)确定表和表之间的连接条件
	on employee.deptId=dept.id  
# 使用别名
select e.empName, d.deptName ---- 2)确定查询哪些字段
	from employee as e   	 ---- 1)确定查询哪些表 
	inner join dept	as d	 ---- 3)确定表和表之间的连接条件
	on e.deptId=d.id  
# 左外连接查询(使用左表的数据去匹配右表的数据，如果符合连接条件的结果则显示，如果不符合连接条件的结果则显示null)
select d.deptName, e.empName 		---- 2)确定查询哪些字段
	from dept as d   	 			---- 1)确定查询哪些表 
	left outer join employee as e	---- 3)确定表和表之间的连接条件
	on d.id=e.deptId  
# 自连接查询(使用左表的数据去匹配右表的数据，如果符合连接条件的结果则显示，如果不符合连接条件的结果则显示null)
select e.empName, d.deptName
	from employee as e
	left outer join employee as b
	where e.bossId=b.id

二、数据库sql文件的导出和导入

（1）数据库导出sql文件

• 打开cmd，并键入以下的命令
• 然后再输入密码

# 导出数据库day15，并输出为day15.sql文件
mysqldump -uroot -p day15 > day15.sql

# 使用dir命令查看当前目录下，是否存在day15.sql文件
dir

（2）将sql文件导入mysql，生成数据库

• 首先，在mysql中创建一个空的数据库，命名为day15

# 创建一个空的数据库，并且注意其字符集与sql文件中的数据库保持一致
create database day15;

# 进入刚创建好的空数据库day15
use day15;
# 将sql文件导入，生成数据库里的表
# 一定要注意路径名中的斜线‘/’
source C:/Users/bailang/day15.sql

• 通过以下命令可以看出，sql文件导入成功

show tables;
select * from student;

三、数据约束

（1）默认值

• 建一张数据表
• 如果不插入值，系统自动给address分配默认值‘beijing’
• 如果插入NULL，那么就是NULL，默认值不起作用

create table test(id int, address varchar(20) default 'beijing');

（2）非空

• 建一张数据表
• gender字段必须有值，非空约束，也不能赋空值

create table test(id int, gender varchar(2) not null);

（3）唯一

• 建一张数据表
• id字段是唯一约束，不能重复，但是可以是空值NULL

create table test(id int unique, gender varchar(2));

（4）主键

• 非空+唯一
• 建一张数据表

create table test(id int primary key, gender varchar(2));

（5）自增长

• 自动地递增
• 建一张数据表

create table test(id int primary key auto_increment, gender varchar(2));
# 四位，零填充，0001 xu
create table test(id int(4) zerofill primary key auto_increment, gender varchar(2));

（6）外键

• 解决数据冗余问题
• 约束employee表的deptId不可以随意插入，应该遵循dept表的Id
• 建一张数据表（副表/从表）

create table employee(id int primary key, empName varchar(20), deptId int,
constraint employee_dept_fk foreign key(deptId) references dept(id));

• 建另一张数据表（主表）
• 主表的参考字段必须为主键

create table dept(id int primary key, deptName varchar(20));

• 1.有了外键约束，添加数据的顺序：先添加主表，再添加副表数据
• 2.有了外键约束，修改数据的顺序：先修改副表，再添加主表数据
• 3.有了外键约束，删除数据的顺序：先删除副表，再删除主表数据

（7）级联操作

• 当有了外键约束的时候，必须先修改或者删除副表中所有的关联数据，才能修改或删除主表，但是我们希望通过直接修改或删除主表数据，从而影响副表数据
• 级联修改：on update cascade
• 级联删除：on delete cascade
• 建一张数据表（副表/从表），然后设置级联

create table employee(id int primary key, empName varchar(20), deptId int,
constraint employee_dept_fk foreign key(deptId) references dept(id) on update cascade on delete cascade);

• 建另一张数据表（主表）
• 主表的参考字段必须为主键

create table dept(id int primary key, deptName varchar(20));

四、数据库中表的设计原则——三大范式

（1）完全函数依赖

• 定义
  通过AB能够推出C，但是A或者B单独不能推出C，那么C完全依赖于AB
• 实例
  通过（学号、课程）能够推出分数，但是单独用学号或者课程不能推断出分数，那么分数完全依赖于（学号、课程）

（2）部分函数依赖

• 定义
  通过AB能够推出C，A能够推出C或者B能够推出C，那么C部分依赖于AB
• 实例
  通过（学号、课程）能够推出姓名，但是单独用学号能推断出姓名，用课程不能推断出姓名，那么姓名部分依赖于（学号、课程）

（3）传递函数依赖

• 定义
  A能够推出B，B能够推出C，那么C传递依赖于A
• 实例
  通过学号能够推出系名，通过系名能推断出系主任，那么学号传递依赖于系主任

（4）第一范式

属性不可切割

• 要求表的每一个字段必须是不可分割的独立单元

student :   id    name
	       001	  张三|狗剩    #  违反了第一范式
# 修改后
student :   id    name   old_name
	       001	  张三     狗剩

（5）第二范式

不存在部分函数依赖

• 要求每张表只表达一个意思，表的每个字段都和表的主键有依赖

employee :   id    name   deptId  deptName  # deptName符合第二范式

（6）第三范式

不存在传递函数依赖

• 要求每张表的主键之外的其他字段都和表的主键有直接决定性的依赖

employee :   id    name   deptId  deptName  # deptName违反了第三范式

五、存储过程

（1）定义

• 带有逻辑的sql语句，条件或者循环

（2）创建一个存储过程

delimiter $  # 声明存储过程的结束符
create procedure pro_test()
begin
	select * from student; # 可以写多个sql语句
end $

• 执行一个存储过程

call pro_test();

（3）创建一个带有输入参数的存储过程

delimiter $  # 声明存储过程的结束符
create procedure pro_findById(in eid int)  # in: 输入参数
begin
	select * from student where id=eid; # 可以写多个sql语句
end $

• 执行一个存储过程

call pro_findById(4);

（4）创建一个带有输出参数的存储过程

delimiter $  # 声明存储过程的结束符
create procedure pro_testOut(out str varchar(20))  # out: 输出参数
begin
	set str='this is a procedure';
end $

• 执行一个存储过程

call pro_testOut(@name);
select @name;

（5）删除一个存储过程

drop procedure pro_testOut;

（6）创建一个带有输入输出参数的存储过程

delimiter $  # 声明存储过程的结束符
create procedure pro_testInOut(inout n int)  # inout: 输入输出参数
begin
	select n;
	set n=500;
end $

• 执行一个存储过程

set @n=10;
call pro_testInOut(@n);
select @n;

（7）创建一个带有条件判断的存储过程

delimiter $  # 声明存储过程的结束符
create procedure pro_testIf(in n int,out str varchar(20)) 
begin
	if n=1 then
		set str='one';
	elseif n=2 then
		set str='two';
	else
		set str='other';
	end if;
end $

• 执行一个存储过程

call pro_testInOut(1, @str);
select @str;

（8）创建一个带有循环功能的存储过程

delimiter $  # 声明存储过程的结束符
create procedure pro_testWhile(in n int,out result int) 
begin
	declare i default 1;
	declare vsum default 0;
	while i<=n do
		set vsum = vsum + i;
		set i = i + 1;
	end while;
	set result = vsum;
end $

• 执行一个存储过程

call pro_testWhile(100, @result);
select @result;

（9）存储过程练习

• 如果学生的英语成绩平均分小于等于70，则输出一般
• 如果学生的英语成绩平均分大于70，且小于等于90，则输出良好
• 如果学生的英语成绩平均分大于90，则输出优秀

六、触发器

（1）创建一个触发器

create trigger tri_empAdd after insert on employee for each row
	insert into test_log(content) values('员工表插入一条记录');

# 也可以执行一个存储过程
create trigger tri_empAdd after insert on employee for each row
	call pro_test();

create trigger tri_empAdd after update on employee for each row
	insert into test_log(content) values('员工表修改一条记录');

create trigger tri_empAdd after delete on employee for each row
	insert into test_log(content) values('员工表删除一条记录');

七、数据库权限

（1）root权限

• root：拥有所有权限

（2）修改mysql密码

use mysql;
update user set password=password('123456') where user='root';

（3）分配权限

• 权限：select insert delete update drop create all

# 授予172.30.12.59的eric用户对于表day16.employee的select权限
grant select on day16.employee to 'eric'@'172.30.12.59' identified by 'password';

# 只有localhost的eric用户对于表day16.employee具有insert权限
grant insert on day16.employee to 'eric'@'localhost' identified by 'password';

# 任何地址的eric用户对于表day16.employee都有delete权限
grant delete on day16.employee to 'eric'@'%' identified by 'password';

八、数据库备份和恢复

（1）数据库备份

mysqldump -uroot -p day15 > d:/day15.sql;

（2）数据库恢复

drop database day15;  # 删除原来的数据库
create database day15; # 新建一个空的数据库
mysql -uroot -p day15 < d:/day15.sql; # 恢复数据库

九、数据库索引

（1）数据库索引的理解

MySQL中存在多种类型的索引，索引在底层的数据结构是通过B+树或者hash索引表生成的。对于不同类型的索引是和存储引擎相关的。如果是使用InnoDB、MyISAM存储引擎，索引的底层数据结构就是B+树，如果是memory存储引擎，索引的底层数据结构就是hash索引表。

不同的存储引擎表示数据在磁盘上的组织形式
MySQL的索引存储的是key-vlaue键值对，key存储着索引的值，value存储着某一行的值
InnoDB、MyISAM存储引擎为什么要用B+树，如果用红黑树、avl树等二叉树会使整个树变高，会增大整体IO次数，从而影响数据访问的效率。B+树可以地在一个叶子节点中尽可能多的存储数据，使这棵树变低，减少IO次数，提高数据访问效率
MySQL中有主键索引、唯一索引、普通索引、组合索引、全文索引等，在我们实际用的过程中，主键索引和组合索引使用频率最高，在使用主键索引和组合索引中会存在回表、索引覆盖、最左匹配、索引下推等一系列情况。
同时，在执行SQL语句时，可以使用explain查看SQL语句的执行计划，查看该语句使用用上索引。

（2）存储引擎

数据在磁盘上的组织形式
MyISAM存储引擎：索引文件和数据文件分开存储，不支持事务，只支持表锁
InnoDB存储引擎：索引和数据在一个文件中存储，支持事务，支持表锁和行锁

（3）数据结构

• B树
  B树的每一层都存储数据记录，而B+树只在叶子节点记录数据，非叶子节点存储索引key值

• B+树
  B树的每一层都存储数据记录，而B+树只在叶子节点记录数据，非叶子节点存储索引key值，叶子节点之间有双向链表

  千万级的数据表B+树只需要3-4层的树就能够存储下，而且主键索引的数据类型越短越好，大于4个字节用int，小于4个用varchar。如果主键索引是int类型，最好是自增，能够避免页分裂现象

• hash表
  1.需要大量的内存资源，memory存储引擎是基于内存的
  2.hash表不支持范围查询，需要逐个匹配

• 二叉树（红黑树、AVL数）
  1.都是二叉树，有且仅有两个叶子节点，如果扩展需要增加层数，增加IO次数，降低数据访问的效率

（4）局部性原理和磁盘预读

内存和磁盘在进行数据交互时，读取数据的大小由一个最基本的逻辑单元，页或者datapage来决定，不同的操作系统，页的大小会不同，一般是4KB或者8KB，每次读取是逻辑单元的整数倍
InnoDB存储引擎默认每次读取16KB的数据

（5）聚簇索引和非聚簇索引

数据和索引放在一起的是聚簇索引，数据和索引没有放在一起的是非聚簇索引
innodb中既有聚簇索引，又有非聚簇索引
myisam中只有非聚簇索引

在插入数据时，innodb将数据和索引绑定在一起进行存储，索引可以使主键，也可以是唯一键，或者是6字节的rowid
如果创建索引的是其他字段，那么在叶子节点存储的是该记录的主键，然后通过主键索引找到该记录，这个过程也就是回表

（6）索引名词

• 回表
  从某一个索引的叶子节点中获取聚簇索引的id值，再根据id值再去聚簇索引中获取全量数据
• 索引覆盖
  从某一个索引的叶子节点中能够获得到全量查询列的过程，叫做索引覆盖
• 最左匹配
  对于组合索引，只要查询条件满足最左前缀，即组合索引的最左N个字段或者字符串索引的最左M个字符，就可以利用索引来加速检索
• 索引下推
  在不使用索引下推的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 。
  在使用索引下推的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。
  索引条件下推可以减少存储引擎与mysql服务器之间的IO，提高数据访问效率。

（7）事务

能够保证原子性、隔离性、一致性和持久性的一个或者一组数据库操作称为一个事务

• 原子性
  事务是指一系列的操作，所有操作要么成功完成，否则失败回滚

• 隔离性
  两个事务是独立的，相互不影响的

• 一致性
  事务必须从一个一致性状态转移到另一个一致性状态

• 持久性
  事务一旦提交，数据的更改就是永久性的

（8）事务的隔离性

• 读未提交
  一个事务可以读取到其他事务未提交的数据，会出现脏读
• 读已提交
  一个事务只能读取到其他事务已提交的数据，会出现不可重复读、幻读（相同查询条件，两次读到不同数量的记录行）
• 可重复读
  一个事务第一次读取某条记录后，即使其他事务修改了该记录的值并且提交，该事务再次读取该条记录时，读取到的值仍然为第一次读取到的值，而不是每次都读取到不同的值。这就是可重复读，但是仍然会出现幻读的现象，但是MySQL解决了幻读的现象，在可重复读的隔离级别下，不会出现幻读
• 串行化
  这种隔离级别，对同一条记录不允许出现读-写、写-读的并发操作，只允许读-读的并发操作。当一个事务正在执行写操作的事务，另一个事务的读操作会被阻塞，直到写事务提交

（9）mysql的锁

• 共享锁
  将读取到的数据加一把共享锁，允许其他事务也加共享锁，但是不允许加排他锁
• 排他锁
  将读取到的数据加一把排他锁，不允许其他事务也加共享锁和排他锁
• 行锁
  单个行记录上的锁
• 间隙锁1
  锁定一个范围，不包括记录本身，记录上下的间隙加锁
• 间隙锁2
  锁定一个范围，包括记录本身，记录上下的间隙和记录本身加锁

（10）创建数据库时的字符集

最好选择utf8mb4

（11）索引优化

• 利用覆盖索引来进行查询操作，避免回表
• SQL性能优化的目标
  至少要达到 range 级别，要求是ref级别，如果可以是consts最好。 说明
• 如果有order by的场景，请注意利用索引的有序性
  order by 后面的字段是组合索引的一部分，并且放在索引组合顺序的最后。
  正例：where a=? and b=? order by c; 索引：a_b_c
• 建组合索引的时候，区分度最高的在最左边
  存在非等号和等号混合时，在建索引时，请把等号条件的列前置。如：where c>? and d=? 那么即使c的区分度更高，也必须把d放在索引的最前列，即索引idx_d_c

（12）创建索引

create index idx_tbl_a_b on tbl (a,b); 

十、分库分表技术

shardingsphere

十一、MySQL系统命令

查看配置文件

mysql --help | grep myf

查看MySQL数据存储位置

ps -ef | grep mysqld

---

本文标签： mysql