下载MySQL

下载时版本的选择:

下载时有两种版本可以选mysql-installer-web-community和mysql-installer-communityl的版本的区别

我们到底该选择哪种呢?

如果你在运行MySQL安装程序时可以上网（网络连接），选择mysql-installer-web-community；

如果你在运行MySQL安装程序时不可以上网（没有网络连接），选择mysql-installer-community 所以我们选择mysql-installer-community

用通俗的说法就是一个是在线安装，一个是离线安装。看下这两个文件的大小就知道了。

需要下载两个文件:

Windows下载

不用下载, 使用D盘下的安装包在D:\Java\MySQL\MySQL安装包\MySQL5.7.30-winx64压缩版安装包.zip

Linux下载

MySQL服务端: MySQL-client-5.5.48-1.linux2.6.i386.rpm

MySQL客户端: MySQL-server-5.5.48-1.linux2.6.i386.rpm

MySQL5.7.30的安装(window版)

我的MySQL的用户名和密码都是root

将MySQL注入为Windows的一个服务,每次开机自启

启动MySQL服务:

net start 命令名字:开启一个服务，如：net start MySQL

net stop 命令名字:关闭一个服务器，如：net stop MySQL

连接MySQL数据库:

cmd中输入命令,格式:mysql -u账户 -p密码 -h数据库服务器安装的主机 -P数据库端口mysql -uroot -padmin -h127.0.0.1 -P3306

若连接的数据库服务器在本机上，并且端口是3306。则可以简写： mysql -uroot -padmin

MySQL的卸载(window版)

参见梦学谷mysql安装教程

MySQL的安装(linux版)

第一步: 将mysql服务器和mysql客户端上传到linux中

第二步: 安装mysql服务端使用rpm -ivh rpm软件包名

rpm -ivh MySQL-client-5.5.48-1.linux2.6.i386.rpm

如果安装的过程中发现冲突(比如之前安装过) 则需要先卸载掉原来的mysql再重新执行第二步的安装命令

卸载命令

yum -y remove mysql*

第三步: 为安装好后的MySQL的root用户设置密码(首次安装root用户是没有密码的, 这里给root用户设置的密码也为root)

/usr/bin/mysqladmin -u root password root

第四步: 安装MySQL客户端使用rpm -ivh rpm软件包名

rpm -ivh MySQL-server-5.5.48-1.linux2.6.i386.rpm

*注意: 如果安装的过程中报错: “GPG keys…” 安装失败, 解决方案

rpm -ivh rpm软件包名 –force –nodoeps

第五步: 验证安装是否成功

mysqladmin –version

第六步: 修改MySQL配置文件, 将编码统一为utf8

(*注意: MySQL5.5默认配置文件叫/etc/my.cnf MySQL5.6后默认配置文件改名为/etc/mysql-default-cnf)\

找到红色的这四个地方, 分别在其末尾加上编码

vim /etc/my.cnf

[mysql]
default-character-set=utf8
[client]
default-character-set=utf8
[mysqld]
character_set_server=utf8
character_set_client=utf8
collation_server=utf_general_ci

第七步: 启动MySQL应用

service mysql start

第八步: 登录MySQL

mysql -u root -p

第九步: 查看MySQL修改后的字符集

show variables like '%char%'

第十步: 创建数据库/创建表

create database test01;
use test01;
create table user(
  id int(4) auto_increment,
  name varchar(4),
primary key(id)
);

第十一步: 向表中插入数据并查询插入的结果

insert into user(name) values("张三");
select * from user;

第十二步: mysql清屏ctrl+L (推荐使用, 这个命令在linux系统下都能实现清屏的效果)

第十三步: 退出mysql控制台 exit

其他命令

停止MySQL应用

service mysql stop

重启MySQL应用

service mysql restart

注意事项 将计算机reboot(重启)后, 再次登录MySQL 可能会报错 /var/lib/mysql/mysql.sock不存在原因: 是mysql服务没有启动

设置mysql开机自启

打开开机自启

chkconfig mysql on

关闭开机自启

chkconfig mysql off

检查某个服务是否开机自启(前面标有星号*的代表开启自启的服务)

ntsysv

mysql的目录结构

数据库目录datadir = /var/lib/mysql

安装目录/var/lib/mysql

配置文件存放目录/etc/my.cnf

命令目录/usr/bin (包括mysqladmin mysqldump等)

启停脚本目录/etc/init.d/

MySQL的卸载(linux版)

MySQL常用数据类型对应Java中的数据类型

MySQL	Java
INT	int
BIGINT	long
FLOAT(s,p)	float
DOUBLE(s,p)	double
DECIMAL(多用于货币)	BigDecimal
CHAR(size)	char
VARCHAR(size)	String
TEXT存储大量字符串
DATE年月日
DATETIME年月日时分秒
BIT存储0或1,0代表false 1代表true	Boolean

注意:

DOUBLE(s,p) p代表精度s代表范围, num DOUBLE(5,2)表示定义了一个数num,该数整数和小数一共占5为.其中小数占2位
在MySQL中，日期时间值使用单引号引起来。相当于Java中Date，Calender

MySQL端口3306

MySQL中的存储引擎

InnoDB ：支持事务，支持外键，行锁

MyISAM：不支持事务，不支持外键，表锁

查看MySQL的存储引擎

show engines;

default标注的就是当前默认使用的存储引擎

MySQL查询函数

注意:函数可以相互嵌套使用

统计函数

count总记录数

select count(主键) from 表 where 条件
-- (注意: 查询结果在Java中需使用Long类型来接收)	

-- 统计分数大于85分的有多少个同学
select count(id) from student where score>85

max求最大值

select max(字段) from 表 where 条件	

-- 查询学生中最高的分数
select max(score) from student

min求最小值

select min(字段) from 表 where 条件	

-- 查询学生中最低的分数
select min(score) from student

sum求和

select sum(字段) from 表 where 条件	

-- 查询学生表中所有学生的总分数
select sum(score) from student

avg求平均值

select avg(字段) from 表 where 条件	

-- 查询学生表中所有学生的平均分
select avg(score) from student

greatest求同行记录中不同字段中的最大值

-- 查询学号为10 的学生数学,英语,语文三科中获得分数最高的学科的分数是多少
select greatest(mathScore,englishScore,chineseScore) from student
where studentid=10

字符函数

lower/upper大小写转换

-- 	将学生表中的姓名全部小写并返回
select lower(name) from student

concat字符连接

select name from student where name like concat('%',keyword,'%')
-- 此函数在MyBatis模糊匹配的时候要用到,keyword代表一个字符串	模糊查询学生表中姓名包含'张'的学生姓名
-- 此时传入的keyword='张'

char_length字符个数

select char_length('Hello哈')	
-- 返回6, 即' hello哈'所占字符个数

length字节个数

select length('Hello哈')	
-- 返回8, 即' hello哈'所占字节个数, 注意,中文占3个字节

lpad

-- rpad原理同lpad,从右边填充	
-- lpad(str,len,padstr)返回字符串str,其左边由字符串padstr填充到len字符长度,
-- 假如str的长度大于len则返回值被缩短至len字符	
-- select lpad("a哈",3,"X") 返回'Xa哈' (可以看出是字符不是字节)
select lpad('hi',1,'??')  返回'h'

ltrim消除左边空格 rtrim消除右边空格 trim消除两端空格

select ltrim(' abc ') -- 返回'abc '
select rtrim(' abc ') -- 返回' abc'
select trim(' abc ')  -- 返回'abc'

replace字符串替换

select replace('Hello','l','H') 
-- 会将找到的匹配字符全部替换	返回'HeHHo'

substring截取字符串 (注意:索引从1开始)

select substring('abcde',2)   -- 从索引为2的位置截取,返回'bcde'  
select substring('abcde',2,2) -- 从索引为2的位置截取2个字符,返回'bc'

数字函数

abs绝对值

select abs(-1) 
-- 该函数对字符串同样有用select abs("-2")+1 返回3	返回1

mod取余

select mod(11,3)	
-- 返回2

ceil向上取整

-- ceil(X)返回不小于X的最小整数值 (ceil天花板以上不低于天花板)	
select ceil(0.5),ceil(-1.5),ceil(1.5)  -- 返回1  -1  2

floor向下取整

-- floor(X)返回不大于X的最大整数值 (floor地板以下不高于地板)	
select floor(0.5),floor(-1.5),floor(1.5) -- 返回0  -2  1

round四舍五入

select round(13.495)    -- 对13.495进行四舍五入,不保留小数,返回13
select round(13.495,1)  -- 对13.495进行四舍五入,保留一位小数,返回13.5,因为要保留一位小数,所以从小数点后第二位进行四舍五
select round(13.495,-1) -- 对13.495进行四舍五入,保留十位数,返回10,因为要保留十位数,所以从个位数进行四舍五

truncate直接舍去多少位小数

-- truncate(X,D)返回舍去D位小数后的数字X	
truncate(13.495,2) -- 返回13.4

日期函数

now求当前系统日期和时间,包含年月日时分秒

select now()	
-- 返回当前系统日期和时间,譬如2019-04-16 02:24:22

current_date计算当前日期

select current_date()	
-- 返回当前系统日期,譬如2019-04-16

current_time计算当前时间

select current_time()	
-- 返回当前系统时间,譬如02:24:22

year获取字符串中的年

SELECT YEAR(NOW())	
-- 返回当前系统年份2019

month获取字符串中的月

SELECT MONTH(NOW())	
-- 返回当前系统月份4

day获取字符串中的日

SELECT DAY(NOW())	
-- 返回当前系统某月的第几天16

hour获取字符串中的时

SELECT HOUR(NOW())	
-- 返回当前系统今日几点2

minute获取字符串中的分

SELECT MINUTE(NOW())	
-- 返回当前系统今日当前时间处在几分50

second获取字符串中的秒

SELECT SECOND(NOW())	
-- 返回当前系统今日当前时间处在几秒32

转换函数

字符串转数字

SELECT '3.14'+0 -- 返回3.14
SELECT '3.14'+3 from dual -- 注意浮点数计算可能会丢失精度,返回 6.140000000000001

数字转字符串

SELECT CONCAT(123,'')	
-- 返回'123'

date_format日期转字符串

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') 
-- %代表的意思见附表	返回' 2019-04-16'

str_to_date字符串转日期

SELECT STR_TO_DATE('2016-01-02', '%Y-%m-%d %H') 
-- 注意Y大写	返回'2016-01-02 00:00:00

附表: MySQL日期格式化（format）取值范围

值	含义
%S、%s	两位数字形式的秒（ 00,01, …, 59）
%I、%i	两位数字形式的分（ 00,01, …, 59）
%H	24小时制，两位数形式小时（00,01, …,23）
%h	12小时制，两位数形式小时（00,01, …,12）
%k	24小时制，数形式小时（0,1, …,23）
%l	12小时制，数形式小时（0,1, …,12）
%T	24小时制，时间形式（HH:mm:ss）
%r	12小时制，时间形式（hh:mm:ss AM 或 PM）
%p	AM上午或PM下午
%W	一周中每一天的名称（Sunday,Monday, …,Saturday）
%a	一周中每一天名称的缩写（Sun,Mon, …,Sat）
%w	以数字形式标识周（0=Sunday,1=Monday, …,6=Saturday）
%U	数字表示周数，星期天为周中第一天
%u	数字表示周数，星期一为周中第一天
%d	两位数字表示月中天数（01,02, …,31）
%e	数字表示月中天数（1,2, …,31）
%D	英文后缀表示月中天数（1st,2nd,3rd …）
%j	以三位数字表示年中天数（001,002, …,366）
%M	英文月名（January,February, …,December）
%b	英文缩写月名（Jan,Feb, …,Dec）
%m	两位数字表示月份（01,02, …,12）
%c	数字表示月份（1,2, …,12）
%Y	四位数字表示的年份（2015,2016…）
%y	两位数字表示的年份（15,16…）
%文字	直接输出文字内容

管理数据库

创建数据库

-- 创建数据库并制定字符集 utf8
CREATE DATABASE IF NOT EXISTS database_name DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

-- 创建数据库并制定字符集 utf8mb4
CREATE DATABASE IF NOT EXISTS database_name DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

删除数据库

drop database database_name

显示所有数据库

show databases

查询数据库使用的编码

show create database database_name 
-- 显示database_name数据库的默认编码信息

查询当前数据库有哪些表

show tables

选择数据库

use database_name

管理表

使用哪个数据库

use database_name

创建表

create table student(name varchar(20),age int(3)) 
-- 创建一张student表,包含姓名和年龄两个字段

删除表

drop table 表名;

向表中添加字段

-- alter table 表名add 字段名 字段类型(长度)	
alter table student add score int(3)

删除表中的字段

-- alter table 表名 drop 字段名	
alter table student drop score
-- 删除student表中的score字段

修改表中字段类型

-- alter table 表名 modify 字段名 新的字段类型(长度)	
alter table student modify score varchar(3)
-- 修改student表中score字段的类型为varchar,长度为3

修改表中字段名称

-- alter table 表名 change 旧字段名 新字段名 新字段类型(长度) 	
alter table student change score class varchar(20)
-- 修改student表中score字段名为class,新字段类型为varchar长度为20

修改表名

-- alter table 表名rename 新表名	
alter table student rename person
-- 修改student表名为person

查看表的结构

-- desc 表名	
desc student 
-- 显示student表各个字段的详细信息
-- (包括字段名,字段的类型是否允许为空,是否是主键,是否自增等等)

截断表

-- truncate table 表名	
truncate table student
-- 删除student表中保存的所有记录

修改表的存储引擎

-- alter table 表名 engine='InnoDB'	
alter student engine='InnoDB'

管理数据

插入一条数据

-- insert into 表名(字段名1,字段名2) values(值1,值2)	
insert into student(name,age) values("张三",20)

插入多条数据

-- insert into 表名(字段名1,字段名2) values(值1,值2),(值1,值2)	
insert into student(name,age) values("李四",21),("王五",22)

修改

-- update 表名 set 字段1=值1,字段2=值2 where 条件	
update student set name="李四",age=24 where name="张三"
-- 查找姓名为张三的学生并将其姓名改为李四,年龄改为24岁

删除

-- delete from 表名 where 条件	
delete from student where name="张三"

查询数据

asc升序

-- select * from 表名order by 字段asc	
select * from student order by age asc
-- 根据年龄升序查询学生的信息

desc降序

-- select * from 表名 order by 字段 desc	
select * from student order by age desc
-- 根据年龄降序查询学生的信息

分组查询

group by

select * from 表名 group by 字段
-- (注意:当group by单独使用时,只显示出每组的第一条记录)	
select * from student group by gender
-- 按照gender字段来分组,gender字段只有两个值('男'或'女'),所以分为两组

(1) group by的含义:将查询结果按照1个或多个字段进行分组，字段值相同的为一组

(2) group by可用于单个字段分组，也可用于多个字段分组

select * from employee;

+------+------+--------+------+------+-------------+
| num  | d_id | name   | age  | sex  | homeaddr    |
+------+------+--------+------+------+-------------+
|    1 | 1001 | 张三   |   26 | 男   | beijinghdq  |
|    2 | 1002 | 李四   |   24 | 女   | beijingcpq  |
|    3 | 1003 | 王五   |   25 | 男   | changshaylq |
|    4 | 1004 | Aric   |   15 | 男   | England     |
+------+------+--------+------+------+-------------+

select * from employee group by d_id,sex;
select * from employee group by sex;

+------+------+--------+------+------+------------+
| num  | d_id | name   | age  | sex  | homeaddr   |
+------+------+--------+------+------+------------+
|    2 | 1002 | 李四   |   24 | 女   | beijingcpq |
|    1 | 1001 | 张三   |   26 | 男   | beijinghdq |
+------+------+--------+------+------+------------+
根据sex字段来分组，sex字段的全部值只有两个('男'和'女')，所以分为了两组
当group by单独使用时，只显示出每组的第一条记录
所以group by单独使用时的实际意义不大
group by + group_concat()
(1) group_concat(字段名)可以作为一个输出字段来使用，
(2) 表示分组之后，根据分组结果，使用group_concat()来放置每一组的某字段的值的集合

select sex from employee group by sex;

+------+
| sex  |
+------+
| 女   |
| 男   |
+------+

group_concat()

select sex,group_concat(name) from employee group by sex;

+------+--------------------+
| sex  | group_concat(name) |
+------+--------------------+
| 女   | 李四               |
| 男   | 张三,王五,Aric     |
+------+--------------------+

select sex,group_concat(d_id) from employee group by sex;

+------+--------------------+
| sex  | group_concat(d_id) |
+------+--------------------+
| 女   | 1002               |
| 男   | 1001,1003,1004     |
+------+--------------------+

group by + 集合函数

(1)通过group_concat()的启发，我们既然可以统计出每个分组的某字段的值的集合，那么我们也可以通过集合函数来对这个”值的集合”做一些操作

select sex,group_concat(age) from employee group by sex;

+——+——————-+
| sex | group_concat(age) |
+——+——————-+
| 女 | 24 |
| 男 | 26,25,15 |
+——+——————-+

分别统计性别为男/女的人年龄平均值

select sex,avg(age) from employee group by sex;

+——+———-+
| sex | avg(age) |
+——+———-+
| 女 | 24.0000 |
| 男 | 22.0000 |
+——+———-+

分别统计性别为男/女的人的个数

select sex,count(sex) from employee group by sex;

+——+————+
| sex | count(sex) |
+——+————+
| 女 | 1 |
| 男 | 3 |
+——+————+

group by + having

(1) having 条件表达式：用来分组查询后指定一些条件来输出查询结果

(2) having作用和where一样，但having只能用于group by

select sex,count(sex) from employee group by sex having count(sex)>2;

+——+————+
| sex | count(sex) |
+——+————+
| 男 | 3 |
+——+————+

group by + with rollup

(1)with rollup的作用是：在最后新增一行，来记录当前列里所有记录的总和

select sex,count(age) from employee group by sex with rollup;

+——+————+
| sex | count(age) |
+——+————+
| 女 | 1 |
| 男 | 3 |
| NULL | 4 |
+——+————+

select sex,group_concat(age) from employee group by sex with rollup;

+——+——————-+
| sex | group_concat(age) |
+——+——————-+
| 女 | 24 |
| 男 | 26,25,15 |
| NULL | 24,26,25,15 |
+——+——————-+

数据约束

CREATE TABLE employee(
  id  BIGINT  PRIMARY KEY AUTO_INCREMENT,
  name  VARCHAR(20)  UNIQUE,
  deptId  INT  NOT NULL,
  country VARCHAR(10)  DEFAULT '中国',
  CONSTRAINT emp_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)
              -- 外键名     外键约束   外键           参考表(参考字段)
);

PRIMARY KEY主键
AUTO_INCREMENT自增
UNIQUE唯一
NOT NULL非空
DEFAULT默认
FOREIGN KEY外键约束

外键约束注意事项：

约束别人的表称为主表，被约束的表称为副表, 外键设置在副表上, 参考字段为主表的主键
添加数据：先添加主表，再添加副表
修改数据：先修改副表，再修改主表
删除数据：先删除副表，再删除主表

级联操作

可以使用级联操作实现直接修改或删除主表数据，从而影响副表数据

注意：级联操作必须在外键基础上使用

级联修改： ON UPDATE CASCADE

级联删除： ON DELETE CASCADE

CREATE TABLE employee(
	id INT PRIMARY KEY,
	empName VARCHAR(20),
	deptId INT,
	-- 声明一个外键约束  -- ON CASCADE UPDATE ：级联修改
	CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE
	    --           外键名称                外键           参考表(参考字段)
)

级联修改

– 直接修改部门,员工表中的数据会跟着改

UPDATE dept SET id=5 WHERE id=4;

级联删除

– 直接删除部门,员工表中的数据会跟着改

DELETE FROM dept WHERE id=1;

多表查询

内连接查询

– 需求：查询员工及其所在部门(显示员工姓名，部门名称)

– 2.2 内连接查询：

SELECT empName,deptName         -- 2）确定哪些哪些字段
	FROM employee,dept           -- 1）确定查询哪些表
	WHERE employee.deptId=dept.id  -- 3）表与表之间连接条件

– 内连接的另一种语法

SELECT empName,deptName
	FROM employee
	INNER JOIN dept
	ON employee.deptId=dept.id;

– 内连接使用别名

SELECT e.empName,d.deptName
	FROM employee e
	INNER JOIN dept d
	ON e.deptId=d.id;

左[外]连接查询

需求：查询每个部门的员工

预期结果：

软件开发部张三

软件开发部李四

应用维护部王五

秘书部陈六

总经办 null –没有符合条件的结果,显示null

2.2 左[外]连接查询：使用左边表的数据去匹配右边表的数据，如果有符合连接条件的结果则显示，如果没有符合连接条件的结果则显示null

（注意：左外连接：左表的数据一定会完成显示！）

SELECT d.deptName,e.empName
	FROM dept d
	LEFT OUTER JOIN employee e
	ON d.id=e.deptId;

右[外]连接查询

2.3 右[外]连接查询: 使用右边表的数据去匹配左边表的数据，如果有符合连接条件的结果则显示，如果没有符合连接条件的结果则显示null

（注意：右外连接：右表的数据一定会完成显示！）

SELECT d.deptName,e.empName
	FROM employee e
	RIGHT OUTER JOIN dept d
	ON d.id=e.deptId;

自连接查询

2.4 自连接查询

需求：查询员工及其上司

预期结果：

张三 null

李四张三

王五李四

陈六王五

SELECT e.empName,b.empName
	FROM employee e 
	LEFT OUTER JOIN employee b
	ON e.bossId=b.id;
```    
	
# 存储过程

存储过程就是带有逻辑的sql语句, 存储过程的优点是执行效率非常快,因为存储过程是在数据库的服务器端执行的

存储过程的缺点是移植性很差,不同数据库的存储过程不能相互移植。
    
## 存储过程的创建执行与删除
### 创建存储过程
```sql
DELIMITER $                    -- 声明存储过程的结束符
CREATE PROCEDURE pro_test()     --存储过程名称(参数列表)
BEGIN                         -- 开始
	-- 可以写多个sql语句;      -- sql语句+流程控制
	SELECT * FROM employee;
END $                         -- 结束 结束符

执行存储过程

CALL pro_test();              -- CALL 存储过程名称(参数);

删除存储过程

DROP PROCEDURE pro_test;      -- DROP PROCEDURE 存储过程名;

参数：

IN：表示输入参数，可以携带数据到存储过程中
OUT：表示输出参数，可以从存储过程中返回结果
INOUT：表示输入输出参数，既可以输入功能，也可以输出功能

带有输入参数的存储过程

-- 需求：传入一个员工的id，查询员工信息
DELIMITER $
CREATE PROCEDURE pro_findById(IN eid INT)  -- IN: 输入参数
BEGIN
	SELECT * FROM employee WHERE id=eid;
END $ 

CALL pro_findById(4);

带有输出参数的存储过程

DELIMITER $
CREATE PROCEDURE pro_testOut(OUT str VARCHAR(20))  -- OUT：输出参数
BEGIN
	SET str='helljava';    -- 给参数赋值
END $

call pro_testOut (@msg);  -- 定义一个会话变量msg接收存储过程的返回值
select @msg;           -- 查看msg变量的值

带有输入输出参数的存储过程

DELIMITER $
CREATE PROCEDURE pro_testInOut(INOUT n INT)  -- INOUT： 输入输出参数
BEGIN
   SELECT n;      -- 查看变量
   SET n =500;    -- 设置变量的值
END $

-- 调用
SET @n=10;
CALL pro_testInOut(@n);   -- 输出n=500
SELECT @n;              -- 输出n=10

带有条件判断的存储过程

-- 需求：输入一个整数，如果1，则返回"星期一",如果2，返回"星期二",如果3，返回"星期三"。其他数字，返回"错误输入";
DELIMITER $
CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(20))
BEGIN
	IF num=1 THEN
		SET str='星期一';
	ELSEIF num=2 THEN
		SET str='星期二';
	ELSEIF num=3 THEN
		SET str='星期三';
	ELSE
		SET str='输入错误';
	END IF;
END $

CALL pro_testIf(4,@str);
SELECT @str;

带有循环功能的存储过程

-- 需求： 输入一个整数，求和。例如，输入100，统计1-100的和
DELIMITER $
CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)
BEGIN
	-- 定义一个局部变量
	DECLARE i INT DEFAULT 1;
	DECLARE vsum INT DEFAULT 0;
	WHILE i<=num DO
	      SET vsum = vsum+i;
	      SET i=i+1;
	END WHILE;
	SET result=vsum;
END $

CALL pro_testWhile(100,@result);
SELECT @result;

使用查询的结果赋值给变量（INTO）

USE day16;

DELIMITER $
CREATE PROCEDURE pro_findById2(IN eid INT,OUT vname VARCHAR(20) )
BEGIN
	SELECT empName INTO vname FROM employee WHERE id=eid;
END $

CALL pro_findById2(1,@NAME);
SELECT @NAME;

触发器

触发器作用:当操作了某张表时，希望同时触发一些动作/行为，可以使用触发器完成

例如：当向员工表插入一条记录时，希望同时往日志表插入数据

-- 需求： 当向员工表插入一条记录时，希望MySQL同时自动往日志表插入数据
-- 创建触发器(添加)
CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROW    -- 当往员工表插入一条记录时
     INSERT INTO test_log(content) VALUES('员工表插入了一条记录');
     
-- 插入
INSERT INTO employee(id,empName,deptId) VALUES(1,'张三',1);

-- 创建触发器(修改)
CREATE TRIGGER tri_empUpd AFTER UPDATE ON employee FOR EACH ROW    -- 当往员工表修改一条记录时
     INSERT INTO test_log(content) VALUES('员工表修改了一条记录');
     
-- 修改
UPDATE employee SET empName='李四' WHERE id=1;
 
-- 创建触发器(删除)
CREATE TRIGGER tri_empDel AFTER DELETE ON employee FOR EACH ROW     -- 当往员工表删除一条记录时
     INSERT INTO test_log(content) VALUES('员工表删除了一条记录');
  
-- 删除
DELETE FROM employee WHERE id=1;

MySQL用户与权限管理

MySQL数据库所有的用户信息和权限都存储在mysql数据库下的user表中,以下是user表的数据结构

列名	说明
Host	“Host列指定了允许用户登录所使用的IP，比如user=root Host=192.168.1.1。这里的意思就是说root用户只能通过192.168.1.1的客户端去访问。而%是个通配符，如果Host=192.168.1.%，那么就表示只要IP地址前缀为“192.168.1.”的客户端都可以连接。如果Host=%，表示所有IP都有连接权限”
User	用户名
Password	密码
select_priv	用户可以通过SELECT命令选择数据
insert_priv	用户可以通过INSERT命令插入数据
update_priv	用户可以通过UPDATE命令修改现有数据
delete_priv	用户可以通过DELETE命令删除现有数据
create_priv	用户可以创建新的数据库和表
drop_priv	用户可以删除现有数据库和表
reload_priv	用户可以执行刷新和重新加载MySQL所用各种内部缓存的特定命令,包括日志、权限、主机、查询和表;重新加载权限表
shutdown_priv	用户可以关闭MySQL服务器;在将此权限提供给root账户之外的任何用户时,都应当非常谨慎
process_priv	用户可以通过SHOW PROCESSLIST命令查看其他用户的进程;服务器管理
file_priv	用户可以执行SELECT INTO OUTFILE和LOAD DATA INFILE命令;加载服务器上的文件
grant_priv	用户可以将已经授予给该用户自己的权限再授予其他用户(任何用户赋予全部已有权限)
references_priv	目前只是某些未来功能的占位符；现在没有作用;
index_priv	用户可以创建和删除表索引;用索引查询表;
alter_priv	用户可以重命名和修改表结构
show_db_priv	用户可以查看服务器上所有数据库的名字,包括用户拥有足够访问权限的数据库;可以考虑对所有用户禁用这个权限,除非有特别不可抗拒的原因
super_priv	用户可以执行某些强大的管理功能,例如通过KILL命令删除用户进程,使用SET GLOBAL修改全局MySQL变量,执行关于复制和日志的各种命令;超级权限;
create_tmp_table_priv	用户可以创建临时表
lock_tables_priv	用户可以使用LOCK TABLES命令阻止对表的访问/修改
execute_priv	用户可以执行存储过程;此权限只在MySQL 5.0及更高版本中有意义
repl_slave_priv	用户可以读取用于维护复制数据库环境的二进制日志文件;此用户位于主系统中,有利于主机和客户机之间的通信;主服务器管理
repl_client_priv	用户可以确定复制从服务器和主服务器的位置;从服务器管理
create_view_priv	用户可以创建视图;此权限只在MySQL 5.0及更高版本中有意义
show_view_priv	用户可以查看视图或了解视图如何执行;此权限只在MySQL 5.0及更高版本中有意义
create_routine_priv	用户可以更改或放弃存储过程和函数;此权限是在MySQL 5.0中引入的
alter_routine_priv	用户可以修改或删除存储函数及函数;此权限是在MySQL 5.0中引入的
create_user_priv	用户可以执行CREATE USER命令,这个命令用于创建新的MySQL账户
event_priv	用户能否创建、修改和删除事件;这个权限是MySQL 5.1.6新增的
trigger_priv	用户能否创建和删除触发器,这个权限是MySQL 5.1.6新增的
create_tablespace_priv	用户可以创建表空间
ssl_type	支持ssl标准加密安全字段
ssl_cipher	支持ssl标准加密安全字段
x509_issuer	支持x509标准字段
x509_subject	支持x509标准字段
max_questions	每小时允许执行多少次查询0表示无限制
max_updates	每小时可以执行多少次更新 0表示无限制
max_connections	每小时可以建立的多少次连接0表示无限制
max_user_connections	单用户可以同时具有的连接数
plugin	5.5.7开始,mysql引入plugins以进行用户连接时的密码验证,plugin创建外部/代理用户
authentication_string	通过authentication_string可以控制两者的映射关系,(PAM plugin等,PAM可以支持多个服务名)尤其是在使用代理用户时，并须声明这一点

MySQL下新增删除用户给用户赋权限

如何修改MySQL的用户密码

MySQL的密码都使用了MD5加密算法进行加密,如果需要修改密码可以借助PASSWORD(“新密码”)函数

注意:修改完密码之后必须重启MySQL服务才能生效(Window打开任务管理器找到服务重启或者使用命令service mysqld restart

UPDATE USER SET PASSWORD=PASSWORD('123456') WHERE USER='root';

给用户分配权限

命令: GRANT 权限ON 数据库.表 TO ‘用户名‘@’localhost字段值’ IDENTIFIED BY ‘密码’;

GRANT SELECT ON test.student TO 'parkour'@'localhost' IDENTIFIED BY '123456';  #分配给parkour用户test数据库下student表的查询权限
GRANT DELETE ON test.student TO 'parkour'@'localhost' IDENTIFIED BY '123456';  #分配给parkour用户test数据库下student表的删除权限

MySQL的备份和还原

在企业修改数据库之前请先备份!!!!

备份命令:mysqldump -u用户名 -p 数据库名 > 备份路径/文件名.sql

注意:需要先退出mysql才能备份,之后在cmd命令窗口中执行下面命即可

C:\Users\parko>mysqldump -u root -p pinyougoudb > C:/Users/parko/Desktop/pinyougoudb.sql
Enter password: ****

C:\Users\parko>

还原命令:mysqldump -u用户名 -p 数据库名 > 备份路径/文件名.sql

注意:不需要登录mysql在cmd命令窗口下执行下面命令即可

C:\Users\parko>mysql -u root -p pinyougoudb < C:/Users/parko/Desktop/pinyougoudb.sql
Enter password: ****

C:\Users\parko>

MySQL中的其他细节内容

SQL语句的分类

DDL：数据定义语言create / drop / alter

DML：数据操作语句insert / delete /update / truncate

DQL：数据查询语言select / show

DCL：数据控制语言grant/ revoke

MySQL中的变量

全局变量

mysql数据库内置的变量（对所有连接都起作用）

查看所有全局变量： show variables
查看某个全局变量： select @@变量名
修改全局变量： set 变量名=新值
character_set_client: MySQL接收数据的编码
character_set_results： MySQL输出数据的编码

会话变量

只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开，那么会话变量全部丢失

定义会话变量: set @变量=值
查看会话变量： select @变量

局部变量

在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕，局部变量就会丢失

MySQL用很多常用的内置变量，掌握这些内置变量后对于我们快速获取当前MySQL的配置有很大帮助，下面就来列举几个常用的变量。

操作	命令
查看当前MySQL版本号	show variables like ‘version’
查看MySQL监听的端口号	show variables like ‘port’
查看MySQL数据的默认存储路径	show variables like ‘datadir’
查看MySQL使用的引擎	show variables like ‘storage_engine’
查看MySQL的最大连接数	show variables like ‘max_connections’
修改MySQL最大连接数	set global max_connections=65535

MySQL中的注释

单行注释(2种写法)

#注释的内容
-- 注释的内容 注意”–” 与注释之间是有空格的

多行注释
/*注释的内容*/

MySQL中字符使用单引号,关键字大写

MySQL自带的三个数据库内表的作用介绍

information_schema数据库

information_schema数据库是MySQL自带的，它提供了访问数据库元数据的方式。

什么是元数据呢？元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。

在MySQL中，把 information_schema 看作是一个数据库，确切说是信息数据库。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。在information_schema数据库中，有数个只读表。它们实际上是视图，而不是基本表，因此，你将无法看到与之相关的任何文件。

表	说明
character_sets字符集表	提供了mysql实例可用字符集的信息。执行SHOW CHARACTER SET命令的结果就是取至此表
collation_character_set_applicability	指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段
collations校对表	提供了关于各字符集的对照信息
column_privileges列的权限表	给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表
columns	提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息
engines存储引擎表
events
files
global_status全局状态表
global_variables全局变量表
innodb_buffer_page
innodb_buffer_page_lru
innodb_buffer_pool_stats
innodb_cmp
innodb_cmp_reset
innodb_cmpmem
innodb_cmpmem_reset
innodb_lock_waits
innodb_locks
innodb_trx
key_column_usage	描述了具有约束的键列
parameters
partitions
plugins插件表
processlist
profiling
referential_constraints
routines	提供了关于存储子程序（存储程序和函数）的信息。此时，ROUTINES表不包含自定义函数（UDF）。名为”mysql.proc name”的列指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。
schema_privileges方案权限表	给出了关于方案（数据库）权限的信息。该信息来自mysql.db授权表。是非标准表
schemata纲要表	提供了当前mysql实例中所有数据库的信息。show databases的结果就取至此表
session_status会话状态表
session_variables会话变量表
statistics统计表	提供了关于表索引的信息。是show index from schemaname.tablename的结果取之此表
table_constraints表的约束表	描述了存在约束的表。以及表的约束类型
table_privileges表的权限表	给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表
tables表	存储了MySQL所有数据库下的所有表的信息, 详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息
tablespaces
triggers触发器表	提供了关于触发程序的信息。必须有super权限才能查看该表
user_privileges用户权限表	给出了关于全程权限的信息。该信息源自mysql.user授权表。是非标准表
views视图表	给出了关于数据库中的视图的信息。需要有show views权限，否则无法查看视图信息

mysql数据库

该数据库是MySQL的核心数据库，主要负责存储数据库的用户、权限设置、关键字等MySQL自己需要使用的控制和管理信息。不可以删除，如果对MySQL不是很了解，也不要轻易修改这个数据库里面表的信息。

表	说明
colums_priv
db
event
func
general_log
help_category
help_relation
help_topic
host
ndb_binlog_index
plugin
proc
procs_priv
proxies_priv
servers
slow_log
tables_priv
time_zone
time_zone_leap_second
time_zone_name
time_zone_transition
time_zone_transition_type
user用户表	包含创建用户和管理用户权限的信息

performance_schema数据库

MySQL 5.5版本新增了一个性能优化的引擎： PERFORMANCE_SCHEMA这个功能默认是关闭的：

需要设置参数： performance_schema 才可以启动该功能，这个参数是静态参数，只能写在my.ini 中不能动态修改。

表	说明
cond_instances
events_waits_current
events_waits_history
events_waits_history_long
events_waits_summary_by_instance
events_waits_summary_by_thread_by_event_name
events_waits_summary_global_by_event_name
file_instances
file_summary_by_event_name
file_summary_by_instance
mutex_instances
performance_timers
rwlock_instances
setup_consumers	描述各种事件的表
setup_instruments	描述这个数据库下的表名以及是否开启监控
setup_timers设置定时器表	监控选项已经采样频率的时间间隔
threads线程表

数据库表的设计

设计数据库表要遵循的三大范式

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

student表 : name –违反第一范式
张三|狗娃 student表： name nickName –符合第一范式
张三狗娃

第二范式：在第一范式的基础上，要求每张表只表达一个意思。表的每个字段都和表的主键有依赖。

employee表: 员工编号员工姓名订单名称 –违反第二范式
001 张三手机订单 employee表: 员工编号员工姓名订单名称 –符合第二范式
001 张三手机订单
oder表 : 订单编号订单名称
001 手机订单

第三范式：在第二范式的基础上，要求每张表的主键之外的其他字段都只能和主键有直接决定依赖关系。

employee表: 员工编号员工姓名部门编号部门名 –违反第三范式
001 张三 100 开发部 employee表: 员工编号员工姓名部门编号 –符合第三范式
001 张三 100
department表: 部门编号部门名
001 开发部

SQL优化

MySQL常见报错

使用MySQL8以上版本, 使用客户端工具连接数据库报plugin caching_sha2_password could not be loaded

索引

创建索引

1.创建单值索引

create index idx_user_name on user(username);;  --对user表中的name字段创建索引

2.创建复合索引

create index idx_user_namepwd on user (username,password);;  --对user表中的name字段和password字段创建复合索引

3.创建唯一索引

create unique index idx_user_idcard on user (idcard);  -- 对user表中的idcard创建唯一索引

4.查看索引

show index from user;  --查看user表创建了哪些索引

5.删除索引

drop index idx_user_idcard on user;  --删除user表中的idx_user_idcard这个索引

索引失效的情况

首先创建一张staff员工表来说明下面所有失效的情况

CREATE TABLE `staff` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `pos` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `add_time` date NOT NULL COMMENT '入职时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工表';	INSERT INTO `staff` VALUES (1, '张三', 18, '经理', '2020-05-01');
INSERT INTO `staff` VALUES (2, '李四', 19, '助理', '2020-05-02');
INSERT INTO `staff` VALUES (3, '王五', 20, '码农', '2020-05-03');

1.复合索引不满足最佳左侧字段优先的法则, 会导致索引失效

先针对name, age, pos三个字段创建复合索引

create index idx_nameAgePos on staff(name,age,pos);

接下来分别执行下述的三个SQL语句, 查看执行计划

从左侧的执行计划可以看出, 要使用到复合索引必须要满足的条件是

复合索引最左侧的字段优先, 由于创建的复合索引的字段顺序是name, age, pos

所以在查询的时候也需要从name开始查询

这就像你对房子创建楼梯一样, 你创建好了三层的楼梯, 你要上楼, 首先得踏过第一层楼梯, 才能到二楼. 复合索引也是一样, 首先匹配最左侧的字段, 然后再依次匹配后面的字段

使用到了索引的情况

explain select * from staff where name='张三';  /*使用到了复合索引中的name字段*/

explain select * from staff where name='张三' and age = 18; /*使用到了复合索引的name, age字段*/

explain select * from staff where name='张三' and age = 18 and pos = '经理'; /*使用到了复合索引的name,age,pos字段*/

explain select * from staff where name='张三' and pos = '经理'; /*只使用到了复合索引的name*/

未使用到索引的情况

explain select * from staff where age = 18;

explain select * from staff where pos = '经理';

explain select * from staff where age = 18 and pos = '经理';

explain select * from staff where pos = '经理' and age = 18;

2.在索引列上进行了 (计算函数 (自动or手动)类型转换) 等操作 , 会导致索引失效

首先对age列创建一个单独的索引

create index idx_age on staff(age);

在索引列上进行计算导致的索引失效的情况

explain select * from staff where age+1 = 19;

在索引列上使用函数导致的索引失效的情况

explain select * from staff where left(name,1)='张';

首先对add_time列创建一个单独的索引

create index idx_addtime on staff(add_time);

在索引列上自动进行类型转化导致的索引失效的情况

(数字类型的字符串转数字是自动转的, 不需要加函数)

explain select * from staff where age+'1' = 19;

在索引列上手动进行类型转化导致的索引失效的情况

日期转字符串需要手动使用转换函数DATE_FORMAT进行转化

explain select * from staff where date_format (add_time, '%Y-%m-%d') = '2020-05-01';

3.使用复合索引进行查询时, 如果要根据复合索引中的某个字段的范围进行查找, 则该字段后的字段索引失效

/*只使用到了复合索引的name, age字段, 因为age是按照范围(>或<)查找所以之后的pos字段索引失效*/
explain select * from staff where name='张三' and age > 19 and pos = '经理';

4.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)), 减少select *

explain select * from staff where name='张三' and age =18 and pos = '经理';

explain select name,age,pos from staff where name='张三' and age =18 and pos = '经理'; /*extra列还会用到index*/

5.mysql在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

explain select * from staff where age != 18;
explain select * from staff where age <> 18;

6.is null , is not null也无法使用索引

explain select * from staff where age is not null;

7.like以通配符开头(‘%abc…’) 会导致索引失效

explain select * from staff where name like '%张';

只要通配符不打头还是能用到索引的

explain select * from staff where name like '张%';

8.字符串不加单引号会导致索引失效

9.少用or 用or连接时会导致索引失效

explain select * from staff where name = '张三' or age = 20;

10.SQL语句的执行顺序

SELECT DISTINCT 
   <select_list> 
FROM 
   <left_table><join_type> JOIN <right_table> 
ON <join_condition> 
WHERE
	<where_condition> 
GROUP BY
	<group_by_list> 
HAVING
	<having_condition> 
ORDER BY
	<order_by_condition> 
LIMIT 
  <limit number>	
/*
先执行from 找到要查询的表
再执行on查找join的条件
再执行where条件
再对查询出来的结果进行分组执行group by
再对分组后的结果进行筛选执行having条件
再对上述结果进行去重执行distinct
再对查询出来的所有结果进行排序执行order by
最后显示要获取查询总结果的条数(分页查询) 执行 limit
*/

七种join查询

内连接

select <select_list> from TableA A 
inner join TableB B
on A.key = B.key

左连接

select <select_list> from TableA A 
left join TableB B
on A.key = B.key

右连接

select <select_list> from TableA A 
right join TableB B
on A.key = B.key

左连接(只包含左边独有的部分)

select <select_list> from TableA A 
left join TableB B
on A.key = B.key where B.key is null

右连接(只包含右边独有的部分)

select <select_list> from TableA A 
right join TableB B
on A.key = B.key where A.key is null

全连接(包含左右两边全部的部分) 注意MySQL不支持全连接, Oracle支持

select <select_list> from TableA A 
full outer join TableB B
on A.key = B.key

全连接(包含左右两边各自独立的部分) 注意MySQL不支持全连接, Oracle支持

select <select_list> from TableA A 
full outer join TableB B
on A.key = B.key 
where A.key is null or B.key is null

现在以员工表和部门表为例说明上述7中join

执行计划explain

使用explain关键字 + 要执行的SQL 可以查看MySQL优化器是如果执行这条SQL的(能查看SQL的执行顺序)

explain select * from user;

对上述字段进行说明

id展示表的执行优先级

id值越大的表越先被执行,有三种情况

id相同, 执行顺序由上至下
id不同, 如果是子查询, id的序号会递增, id值越大优先级越高, 越先被执行
id值既有相同的也有不同的

select_type查询类型

SIMPLE

简单的select查询, 查询中不包含任何子查询或者union

PRIMARY

查询中若包含任何复杂的子查询, 则最外层的查询会被标记为PRIMARY

SUBQUERY

在select或where后面包含的子查询会被标记为SUBQUERY

DERIVED(衍生)

在from后面包含的子查询会被标记为DERIVED, MySQL会递归执行这些子查询, 把结果放在临时表中

UNION

若第二个select出现在union之后, 则被标记为union

若union包含在from后面的子查询中, 则外层的select将被标记为DERIVED

UNION RESULT

从union表获取结果的select

explain
select * from emp e left join dept d on e.deptId = d.deptId
union
select * from emp e right join dept d on e.deptId = d.deptId

type访问类型排序

显示查询使用了何种类型

从最好到最差依次是: (一般来说,得保证查询至少达到range级别,最好能达到ref)

system>const>eq_ref>ref>range>index>ALL

system表中只有一行记录(系统表中才会出现) 可以不用关注, 一般不可能优化到这个程度

const通过索引一次就找到了对应的数据, 比如where 条件后面根据主键(或唯一索引)查找

explain select * from user where id=1;

eq_ref唯一性索引扫描, 对于每个索引键, 表中只有一条记录与之匹配, 常见于主键或唯一索引扫描

eq_ref一般存在于多张表联表查询的情况, 比如,员工表与部门表之间根据deptId进行查询

ref 就是对某个表的其中几列创建了复合索引, 但查询的使用并没有使用到全部索引列, 只使用了其中部分索引列进行查询的情况

比如, 我们对user表的Idcard和bankcode创建了一个复合索引, 现在只根据idcard来查询

create index idx_user_idcard_bankcode on user(idcard,bankcode);   -- 创建复合索引
show index from user; -- 查询user表中的所有索引

根据idcard查询某条记录

explain select * from user where idcard='511521199312095258';

range 只检索给定范围的行, 使用一个索引来选择行 key列显示使用了哪个索引, 一般就是在你的where语句中出现了between < > in等的查询. 这种范围扫描索引比全表扫描要好

下面以emp表来做说明, emp表的数据如下, 其中empId是主键, 拥有唯一性索引

select * from emp;

现根据empId使用4种方式查询员工信息

explain select * from emp where empId between 1 and 2;

explain select * from emp where empId >2;

explain select * from emp where empId <2;

explain select * from emp where empId in(1,2);

index想full index scan 使用索引进行全表扫描, index类型与ALL类型的区别在于, index类型只遍历索引树, 这通常比ALL快, 因为index是从索引中读取的, 而all是从硬盘中读取的

比如, 还是以上面emp员工表的数据为例, 其中empId是主键, 具有唯一索引, 如果我要查询emp整张表的empId, 则使用的就是index类型

explain select empId from emp;

ALL直接全表扫描, 未使用到索引, 这个效率是最低的

首先查看emp中的所有索引, 发现该表只有一个主键索引, empName列是没有创建索引的

show index from emp;

此时, 我如果直接查询emp表中的所有empName, 则进行的就是ALL类型的全表扫描

explain select empName from emp;

MySQL开发规范

查询语句规约

1.不要使用select ※

使用select * 语句在相关表增加字段时可能造成insert into select和select into等语句字段不匹配,从而报错

2.当个查询条件既可以放在where子句也可以放到having子句中时, 请放到where子句中. 尽可能早筛选不必要数据以避免额外的开销

先执行where子句,后执行having子句, having子句一般配合group子句使用. where子句是筛选出数据结果集. having子句则是对筛选出的数据结果进行过滤

3.从where子句中移除where 1=1无意义的筛选条件, 使用动态SQL标签替代

4.查询条件中的常量类型要与表中定义的类型一致,避免出现隐式类型转化而无法用到索引

-- 正例:
select id,name from user where id=10;
-- 反例:
select id,name from user where id='10';

5.避免在where子句中查询条件左侧字段上使用函数, 而是在查询条件右侧常量是哪个使用函数或者其他方式实现

-- where子句中查询条件左侧字段上使用函数会造成索引失效
-- 正例:
select id,name from user where data1>date_sub(now(),interval 1 day);
-- 反例:
select id,name from user where date_add(data1,interval 1 day)>now();

6.业务系统应对大表的复杂查询进行并发控制

大表的复杂查询会消耗较多系统资源并且耗时较长, 并发用户可能会重复点击按钮发起查询请求.

陷入越慢越点,越点越慢的误区. 甚至耗尽系统资源导致系统崩溃. 因此业务系统设计上应予以考虑.

例如: 在点击”搜索” 按钮后一定时间内禁用该按钮. (非极速的搜索就是这样做的)

7.where子句中对同一字段的查询,使用in替换or

-- 正例:
select id,name from user where id in(10,20);
-- 反例:
select id,name from user where id=10 or id=20;

8.where子句中对不同字段的查询,使用union替换or

-- 正例:
select id,name from user where id = 10 
union
select id,name from user where name='张三';

-- 反例:
select id,name from user where id = 10 or name='张三';

DML语句规约

9.当要往数据库中插入多条数据时, 避免使用单条单条的SQL一条一条的插入,应使用批量插入的方式, 减少与数据库服务器多次交互数据

-- 正例:
insert into user(id,name) values(10,'张三'),(20,'李四');
-- 反例:
insert into user(id,name) values(10,'张三');
insert into user(id,name) values(20,'李四');

10.insert语句必须显示的列出要插入的字段名

-- 正例:
insert into user(id,name) values(10,'张三');
-- 反例:
insert into user values(10,'张三');

DDL语句规约

11.要删除表的数据使用delete语句, 尽量避免使用truncate来清除表的数据, 因为truncate没有事务

-- 正例:
delete table user;
-- 反例:
truncate table user;

12.对于有归档数据需求的表,建议在表中都增加两个字段create_time,update_time分别表示该条记录的创建时间和最后修改时间

增加创建时间和最后修改时间便于对数据进行生命周期管理, 通过这两个时间可以定期对旧数据进行归档, 做数据做备份

知道是什么时候添加修改的数据, 便于排查由于数据问题导致的bug

13.对于数据是否为null的判断只能使用is null或is not null

null表示未知值, 与任何值直接比较都是null值

1)null<>null 返回null而不是false

2)null=null返回null而不是true

3)null<>1返回null而不是true

14.not in包含的子查询(或in包含的子查询)返回的结果集严禁null值, 如果有返回null值的可能,需要加上is not null过滤条件, 筛选掉null值

如果not in的集合中包含null值, 即使还存在其他满足条件的记录, 返回的结果集都是为null

-- 正例:
select id,name from user where name not in(selec name from nameTable where name is not null);
-- 反例:
select id,name from user where name not in(selec name from nameTable);

15.在关联表更新时,更新目标表需要加上where限制条件限制目标表更新范围

1)避免目标表由于在源数据集中没有匹配到记录从而将原来要更新字段的值置为null的问题

2)如果比加上where条件限制,更新时会更新所有记录

-- 正例:
update test1
set test1.name=(select test2.name from test2 where test1.id=test2.id)
where test1.id=test2.id;
-- 反例:
update test1
set test1.name=(select test2.name from test2 where test1.id=test2.id);

16.使用sum函数和avg函数时要注意

在某一列全为null时, count(列名)返回结果为0, 但是sum(列名), avg(列名) 返回结果为null. 因此使用sum(), avg()函数时需要注意NPE问题

可以使用以下方式避免使用sum, avg函数有可能导致的空指针问题

select ifnull(sum(字段),0) from 表;
select ifnull(avg(字段),0) from 表;

17.count(distinct column)计算该列除null外的不重复行的个数

注意, 在使用count(distinct column1,column2)时, 如果一条记录其中一列为null时, 即使另一列有不同的值,也返回0

18.MySQL不支持全外连接, 该怎么办?

TODO

19.在不需要去除重复记录时,使用union all而不是union

union会把两个结果集的所有数据存放到一个临时表中进行去重操作, union alll不会对结果集进行去重,不会创建临时表, 在不需要去重时效率更高

20.SQL语句中in包含的值不要超过200个

对于索引列, in中包含的值个数过多时, 可能导致MySQL的执行计划错误.

在update,delete,where条件中的in值过多时, 容易造成锁等待与死锁

21.不要创建冗余索引, 索引a_b_c就能囊括完索引a, a_b, a_b_c了

-- 正例:
index(a,b,c);
-- 反例:
index(a), index(a,b), index(a,b,c)

空值处理规约

函数使用规约

表连接与集合处理规约

子查询使用规约

锁及事务处理规约

排序和分页处理规约

group by分组和distinct使用规约

索引使用规约

其他

ORM框架和JDBC使用建议

ORM映射

JDBC使用

count(*)/count(1)/count(列名)的区别

count(*)和count(1)两者没啥区别: 都是统计表中有多少条记录, 如果其中一条记录的某个字段为null也会被统计

count(列名)统计列名那一列字段的数量, 如果其中一条记录的某个字段为null则会被忽略