MySQL数据库

1 常用数据类型

1.1 整数类型

1.2 小数类型

如果用户指定的精度超出精度范围，会四舍五入进行处理。
定点数以字符串形式存储，在对精度要求比较高的时候，使用 DECIMAL
尽量避免做浮点数比较，因为两个浮点数进行减法和比较运算时容易出问题

1.3 日期/时间类型

1.4 字符串类型

varchar 和 text类型是变长类型，其存储需求取决于列值的实际长度。

2 MySQL管理

2.1 数据库启动及关闭

（1）Win

• 首先打开终端，进入/mysql/bin目录: cd xxx/mysql/bin
• 启动：mysqld
• 退出：mysqladmin -uroot shutdown

（2）Linux

• 查看mysql服务是否已启动：ps -ef | grep mysqld，若已启动会输出mysql进程列表
• 打开终端，进入/usr/bin目录：cd /usr/bin
• 启动：./mysqld_safe &
• 关闭：./mysqladmin -u root -p shutdown

(3) Mac

• 启动：mysqld
• 关闭：./mysqladmin -u root -p shutdown

2.2 用户管理

2.2.1 用户登陆mysql

mysql -h mysql服务器地址 -u 用户名 -p,若使用本机服务器，-h mysql服务器地址 可省略,管理员登陆本机：mysql -u root -p

2.2.2 添加用户并给用户授权

添加用户：create user 用户名@'登陆主机' identified by '密码';
为用户授权：grant 权限 on 数据库名.表名 to 用户名@'登陆主机' with grant option;

参数说明：
（1）登陆主机：用户连接 MySQL 时所用主机的名字。若在创建的过程中，未指定主机名，主机名默认为“%”，表示一组主机，即对所有主机开放权限。@‘localhost’: 代表仅本机登陆； @‘%’ ：代表可远程登陆
（2）identified by ‘密码’：用于指定用户密码，密码需用单引号括起来
（3）with grant option：该用户是否能把权限分配给其他用户
（4）权限：all privileges代表所有权限，有select，delete等权限

踩坑记录：
使用grant all privileges on 数据库名.表名 to 用户名@‘登陆主机’ identified by '密码' with grant option添加用户，报错信息如下：

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use near 'privaleges 
*.* to zhangsan@'localhost' identified by '123456' with grant option' at line 1

解决方案：这种添加用户的方式只适用于mysql8.0之前的版本。更换上述添加用户的方式

2.2.2 修改用户密码

alter user 用户名@'登陆主机' identified by '密码';

踩坑记录： 新版本不再支持password函数，使用set password for 用户名@'主机地址'=password('用户名')，会报语法错误

2.2.3 查看用户权限

show grants for 用户名@‘登陆主机’;

2.2.4 删除用户

drop user 用户名@'登陆主机',删除一个或多个用户，并撤销其权限。

2.2.5 回收用户权限

revoke 权限 on 数据库名.数据表名 from 用户名@'登陆主机'

2.3 常用命令行操作数据库命令

（1）登陆数据库：mysql -h mysql服务器地址 -u 用户名 -p,若使用本机服务器，-h可省略

（2）显示系统所有数据库：show databases

（3）使用指定数据库：use 数据库名

（4）查看指定数据库表：show tables

（5）查看表的字段结构：desc 表名

（6）创建数据库：create database 数据库名 default charset utf8

3 SQL语言

3.1 注释

• 单行注释：-- 注释内容
• 多行注释：/* 注释内容 */
• 快捷键：Ctrl+/ ， 取消注释：Ctrl+Shift+/

3.2 分类

• DDL：数据定义语言，进行数据库表的管理等（create、drop、alter、truncate）
• DQL: 数据查询语言（如select）
• DML：数据操作语言，对数据进行增删改（insert、delete、update）
• DCL：数据控制语言 （grant、revoke）
• TPL：事务处理语言（commit、rollback、savepoint）

3.3 字段的约束

3.3.1 主键约束（primary key）

唯一标识表中的每条信息，一个表中只能有一个主键，不能重复，不能为空。

auto_increment: 主键自增长

• auto_increment的默认初始值是 1，每新增一条记录，字段值自动加 1
• 必须具备 not null 属性
• 最大值受该字段的数据类型约束,超过最大值，自增长失效
• auto_increment约束的字段只能是整数类
• 一个表中只能有一个字段使用，且该字段必须有唯一索引

主键设置自增长： 字段名 字段类型 primary key auto_increment not null
在插入数据时，自增长字段的值可使用占位符‘0’或‘null’。

3.3.2 外键约束（foreign key）

经常和主键约束一起使用，用来处理表之间关系。表与表之间记录存在3种关系：一对一、一对多、多对多

（1）在创建表时设置外键约束：
字段名 字段类型 foreign key
foreign key(从表列名) references 主表名(列名)

（2）在修改表时设置外键约束
alter table 表名 add constraint 外键名 foreign key(列名) references 主表名(列名)

（3）删除外键约束
alter table 表名 drop foreign key 外键名

3.3.3 唯一约束（unique）

所有记录中字段的值不允许重复。字段名 数据类型 unique

3.3.4 非空约束（not null）

字段值不允许为空。字段名 数据类型 not null

3.3.5 默认值约束（defaut）

用来指定某列的默认值。字段名 数据类型 defaut 默认值

3.4 创建数据库表

create table 表名(
	字段名 数据类型 约束，
	字段名 数据类型 约束，
	......
)

3.5 数据的基本操作：增删改查

3.5.1 插入数据

(1) 插入一条数据
insert into 表名 values(值1,值2....值n)
insert into 表名(字段名1,字段名2....字段名n) values(值1,值2....值n)

(2) 插入多条数据
insert into 表名 values(值1,值2....值n),(值1,值2....值n)......
insert into 表名 (字段名1,字段名2....字段名n) values(值1,值2....值n),(值1,值2....值n)......；

3.5.2 删除数据或表

3.5.2.1 删除数据

(1) 删除满足指定条件的数据（delete）
delete from 表名 where 条件

(2) 删除所有数据(truncate)
truncate table 表名

truncate处理速度 > delete处理速度

3.5.2.2 删除表

drop table 表名或
drop table if exists 表名

3.5.3 修改数据

update 表名 set 字段1=值1,字段2=值2......字段n=值n where 条件

3.5.4 查询数据

select 字段1,字段2...字段n from 表名 where 条件

过滤重复数据(distinct)：select distinct * from 表名 where 条件

基本规律：
（1）select * 或 select 字段名：控制查询返回的字段
（2）where 条件：控制返回的数据

3.5.4.1 模糊查询

select * from 表名 where 字段名 like ...

• % 代表任意多个字符
• _ 代表任意一个字符

3.5.4.2 范围查找

select * from 表名 where 字段名 <in(值1,值2....值n)>或<between 开始值 and 结束值>
非连续范围查找:in(值1,值2....值n)
连续范围查找: between 开始值 and 结束值,包含开始值和结束值

3.5.4.3 排序

order by 字段1，字段2 desc/asc

• asc : 升序，默认升序
• desc：降序

3.5.4.4 分组查询

（1）聚合函数

• avg()，返回一组数据的平均值
• count()，返回表中的行数
• sum()，返回一组值的总和
• max()，返回一组数据的最大值
• min()，返回一组数据的最小值

（2）分组：group by 字段名

select 聚合函数 from 表名 where 条件 group by 字段名

（3）分组聚合之后的数据筛选: group by 字段名 having 条件

select 聚合函数 from 表名 group by 字段名 having 条件

3.5.4.5 显示指定记录数

limit 开始行号，显示多少行，省略开始行号默认从0开始，在select语句最后

3.5.4.6 分页

limit (n - 1) * m, m

• m，每页显示多少条记录
• n, 第n页

3.5.4.7 连接查询

连接查询是另一种类型的多表查询。连接查询对多个表进行JOIN运算，简单地说，就是先确定一个主表作为结果集，然后把其他表的行有选择性地“连接”在主表结果集上。如果一条select要用到多个表,表中有同名字段.就需要 表名.字段名加以区分。

（1）内连接（inner join）

查询出两张表都存在的记录

select 表名1.字段名1,表名2.字段名2......表名n.字段名n from 表1 inner join 表2 on 表1.字段名 = 表2.字段名

（2）左连接（left join）

查询出左表存在的记录

select 表名1.字段名1,表名2.字段名2......表名n.字段名n from 表1 left join 表2 on 表1.字段名 = 表2.字段名

（3）右连接（right join）

查询出右表存在的记录

select 表名1.字段名1,表名2.字段名2......表名n.字段名n from 表1 right join 表2 on 表1.字段名 = 表2.字段名

（4）全连接（full join）

查询出左表和右表存在的全部记录

select 表名1.字段名1,表名2.字段名2......表名n.字段名n from 表1 full join 表2 on 表1.字段名 = 表2.字段名

3.5.4.8 多表查询

select * from 表1,表2；

这种一次查询两个表的数据，查询的结果也是一个二维表，它是表1和表2的“乘积”，即表1的每一行与表2的每一行都两两拼在一起返回。结果集的列数是表1和表2的列数之和，行数是表1和表2的行数之积。

3.5.4.9 自关联

自关联是同一张表做连接查询，自关联下一定找到同一张表可关联的同一字段。

3.5.4.10 子查询

子查询是嵌套到主查询里面的，做为主查询的数据源或者条件，是独立可以单独运行的查询语句。主查询不能立独立运行,依赖子查询的结果。

（1）表级子查询

子查询的结果是多行多列（一个表）
select * from (select * from students where sex='女') stu INNER JOIN scores sc ON stu.studentNo = sc.studentNo;

（2）列子查询

子查询的返回结果是一列（一列多行）
select score from scores where studentNo in (select studentNo from students where age=30);

（3）标量子查询

子查询的返回结果只有一个值（一行一列）
select * from students where age > (select avg(age) from students);

3.5.4.11 条件筛选

having 与 where 筛选的区别：

• having能用于聚合函数，而where不能
• where 是对原始数据进行筛选
• having 是对标已经分过组的数据筛选

3.6 给表和字段起别名

表名/字段名 as 别名，as可省略

3.7 空判断

是否为空：is null
是否不为空：is not null
！null不能用于比较运算

3.8 MySQL内置函数

3.8.1 拼接字符串

concact(参数1，参数2......参数n)

3.8.2 返回字符串长度

length（字符串）
如：select * from student where length（name） = 5

3.8.3 字符串截取

（1）从左侧截取指定数量字符: left（字符串，截取数量）
（2）从右侧截取指定数量字符：right（字符串，截取数量）
（3）从指定位置截取指定数量字符：substring(字符串，指定位置，截取数量), 指定位置从1开始

3.8.4 去除字符串空格

（1）去除字符串左侧空格：ltrim('字符串')
（2）去除字符串右侧空格: rtrim('字符串')
（3）trim去除字符串两侧空格: trim('字符串')

3.8.5 四舍五入

round（数字，保留小数位数),保留位数默认为0

3.8.6 随机数

rand()

随机抽取：select * from student order by rand() limit 1

3.8.7 返回系统时间

（1）返回系统日期:current_date()
（2）返回系统时间: current_time()
（3）返回系统日期与时间: now()

3.9 存储过程

3.9.1 什么是存储过程？

存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。
一个存储过程是一个可编程的函数，它在数据库中创建并保存。它可以由SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。

3.9.2 存储过程优缺点

（1）优点

• 增强SQL语言的功能和灵活性：存储过程可以用控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算
• 标准组件式编程：存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。
• 较快的执行速度：如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。
• 减少网络流量：针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织进存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少网络流量并降低了网络负载。
• 作为一种安全机制来充分利用：通过对执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

（2）缺点

• 存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
• 存储过程的性能调校与撰写，受限于各种数据库系统。

3.9.2 创建存储过程

存储过程就是具有名字的一段代码，用来完成一个特定的功能。
创建的存储过程保存在数据库的数据字典中。

create procedure 存储过程名称(参数列表) 
	begin 
     sql 语句;
     sql 语句;
     ......
	end
call 过程名()

3.9.3 存储过程中的关键语法

（1） 声明语句结束符: delimiter $$或delimiter //

（2）声明存储过程：create procedure 存储过程名称(参数类型 参数名 数据类型)

（3）过程开始和结束: begin ...... end

（4）变量赋值： set 变量名 = 值, 用户变量名一般以@开头

（5）定义变量：declare 变量名 数据类型 [defalut 默认值],[]中为可选

（6）存储过程体：create function 存储函数名(参数)

（7）调用存储过程：call 存储过程名(参数)

（8）删除存储过程：drop procedure 过程名或 drop procedure if exists 过程名

3.9.4 存储过程的参数

存储过程的参数用在存储过程的定义，共有三种参数类型：in、out、 inout
（1）in: 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
（2）out：输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
（3） inout：输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

3.9.5 存储过程体

begin
	begin
		...
		在过程调用时必须执行的语句，例如：SQL语句，if-then-else和while-do语句、声明变量的declare语句等
		...
	end
end

为语句块贴标签:

标签1：begin
	标签2：begin
	......
	end 标签2
end 标签1

3.9.6 变量

（1）变量申明：declare 变量名 数据类型 [defalut 默认值],[]中为可选
（2）变量赋值： set 变量名 = 值, 用户变量名一般以@开头

3.9.7 举个

删除指定学号的学生信息：

mysql> delimiter $$ # 将语句的结束符号从分号;临时改为两个$$
mysql> create procedure delete_sudents(in p_no int)
    	-> begin
	    ->     delete from students where no = p_no;
	    -> end$$

mysql> delimiter; #将语句的结束符号恢复为分号

mysql> call delete_sudents(1001) #调用存储过程，删除学号为1001的学生信息

3.10 视图

3.10.1 什么是视图？

视图是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个select语句保存在数据字典中。
视图为select语句的封装，视图可以理解为一张只读的表，针对视图只能用select，不能update和delete。

3.10.2 视图的创建、使用和删除

（1）创建视图：create view 视图名 as 查询语句
（2）使用视图：select * from 视图名 where 条件
（3）删除视图：drop view 视图名或drop view if exists 视图名

3.11 事务

3.11.1 什么是事务？

事务主要用于处理操作量大，复杂度高的数据。

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
• 事务用来管理 insert,update,delete 语句

3.11.2 事务必须满足的4个条件

（1）原子性

一个事务中的所有操作，要么全部完成，要么全部不完成，在执行过程中发生错误，会被回滚到事务开始前的状态。

（2）一致性

在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

（3）隔离性

数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

（4）持久性

事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失

3.11.3 事务控制语句

（1）显式地开启一个事务：begin或begin transanction

（2）提交事务：commit或commit work

（3）事务回滚：rollback或rollback work，回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

（4）在事务中创建一个保存点：savepoint identifier, 允许在事务中创建一个保存点，一个事务中可以有多个savepoint；

（5）删除一个事务的保存点:release savepoint identifier，当没有指定的保存点时，执行该语句会抛出一个异常；

（6）把事务回滚到标记点：rollbanck to identifier

（7）设置事务的隔离级别：set transaction，InnoDB 存储引擎提供事务的隔离级别有read uncommitted、read committed、repeatable read和 serializable。

3.11.4 事务处理的方法

（1）用begin，rollback，commit实现

• 开始一个事务：begin
• 事务回滚：rollback
• 事务提交：commit

（2）用set改变 mysql 的自动提交模式

• 禁止自动提交：set autocommit=0
• 开启自动提交：set autocommit=1

3.12 索引

3.12.1 什么是索引？

索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
创建索引的目的：增快select的查询速率。

3.12.2 使用索引的优缺点

（1）优点

• 提高select的查询速度
• 降低update,delete和insert语句的执行速度
• 项目中80%以上是select,所以index必须的

（2）缺点

• 会降低更新表的速度，如对表进行insert、update、delete。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
• 建立索引会占用磁盘空间的索引文件。

解决办法： 在实际工作中如果涉及到大量的数据修改操作,修改之前可以把索引删除,修改完成后再把索引建立起来。

3.12.3 索引的创建、使用和删除

（1）创建索引：create index from on 表名(字段名)

• 若字段为字符串，需要指明长度，如：表名<字段（长度）>
• 对于主键，系统会自动创建索引

（2）调用索引：where后面的字段，系统会自动查找是否有索引

（3）查看索引：show index from 表名

（4）删除索引：drop index 索引名 from on 表名