洪晨的MySQL学习笔记

-- 查看当前所有存在的数据库
SHOW DATABASES

-- 创建数据库
CREATE DATABASE `honksunstudy` CHARSET utf8 COLLATE utf8_general_ci

-- 选择当前数据库
USE `honksunstudy`

-- 查看已经创建好的数据库定义
SHOW CREATE DATABASE `honksunstudy`

-- 删除数据库
/*
DROP DATABASE 声明删除数据库后,数据库中存储的所有数据表和数据也将一同被删除,而且不能恢复
*/
DROP DATABASE `honksunstudy`

-- 查看MySQL所支持的引擎类型
SHOW ENGINES

# 方法二: 查看默认存储引擎
SHOW VARIABLES LIKE '%storage_engine%'

经典习题 :

1. 查看当前系统中的数据库.

   SHOW DATABASES
   

2. 创建数据库Book , 使用 SHOW CREATE DATABASE 语句查看数据库定义信息.

   -- 创建Book数据库
   CREATE DATABASE `Book` CHARSET utf8 COLLATE utf8_general_ci
   
   -- 查看数据库定义信息
   SHOW CREATE DATABASE `book`
   

3. 删除数据库Book.

   # 删除数据库
   DROP DATABASE `book`
   

-- 创建外键语法 CONSTRAINT `外键名称` FOREIGN KEY (`字段名1`) REFERENCES `主表名`(`主键列1`)
-- 创建主表
CREATE TABLE tb_dept1(
id INT(11),
`name` VARCHAR(50) NOT NULL,
`location` VARCHAR(255),
PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 创建从表
CREATE TABLE tb_emp5(
id INT(11),
`name` VARCHAR(50),
`deptId` INT(11),
`salary` FLOAT,
PRIMARY KEY(id),
CONSTRAINT fk_emp_dept1 FOREIGN KEY (`deptId`) REFERENCES `tb_dept1`(`id`) -- 添加外键约束
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 查看数据表
SHOW TABLES

-- 查询数据库版本
SELECT VERSION()

-- 使用非空约束 NOT NULL
CREATE TABLE tb_emp6(
`id` INT(11),
`name` VARCHAR(50) NOT NULL,
`deptId` INT(11),
`salary` FLOAT,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

/* 使用唯一性约束: 要求该列唯一,允许为空,但只能出现一个空值. 唯一约束可以确保一列或者几列不出现重复值 */
-- 方法一 : 在定义完列之后直接指定唯一约束
CREATE TABLE `tb_dept2`(
`id` INT(11) PRIMARY KEY,
`name` VARCHAR(50) UNIQUE,
`location` VARCHAR(50)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 方法二 : 在定义完所有列之后指定唯一约束 CONSTRAINT 约束名 UNIQUE(字段名)
CREATE TABLE `tb_dept3`(
`id` INT(11) PRIMARY KEY,
`name` VARCHAR(50),
`location` VARCHAR(50),
CONSTRAINT STH UNIQUE(`name`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

/*使用默认约束  DEFAULT */
CREATE TABLE `tb_emp7`(
`id` INT(11) PRIMARY KEY,
`name` VARCHAR(50) NOT NULL,
`deptId` INT(11) DEFAULT '1111',
`salary` FLOAT
)ENGINE=INNODB DEFAULT CHARSET=utf8;

/* 使用表的属性值自动增加 AUTO_INCREMENT */
CREATE TABLE `tb_emp8`(
`id` INT(11) PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(25) NOT NULL,
`deptId` INT(11),
`salary` FLOAT
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 插入数据
-- 方式一
INSERT INTO `tb_emp8`(`name`,`salary`) VALUES('Lucy','1500'),('MySQL',2500),('Java','3000');
-- 方式二
INSERT INTO `weather` SET `Id`=5, `RecordDate`=DATE(NOW()), `Temperature`=29;

/* 查看数据表结构 */
DESCRIBE `tb_emp8`
DESC `tb_dept3`
SHOW CREATE TABLE `tb_emp5`

-- 修改表名
ALTER TABLE `tb_dept3` RENAME `tb_dept5`


-- 修改字段数据类型
ALTER TABLE `tb_dept1` MODIFY `name` VARCHAR(100)

-- 修改字段名
ALTER TABLE `tb_dept1` CHANGE `location` `loc` VARCHAR(255)

-- 添加有完整性约束条件字段
ALTER TABLE `tb_dept1` ADD `column_1` INT(11) NOT NULL

-- 在表的第一列添加一个字段
ALTER TABLE `tb_dept1` ADD `colum_2` INT(11) FIRST;

-- 在表的指定列之后添加一个字段
ALTER TABLE `tb_dept1` ADD `colum_3` INT(11) AFTER `loc`

-- 删除字段
ALTER TABLE `tb_dept1` DROP `colum_2`

-- 修改字段为表的第一个字段
ALTER TABLE `tb_dept1` MODIFY `column_1` INT(11) FIRST

-- 修改字段到表的指定列之后
ALTER TABLE `tb_dept1` MODIFY `column_1` INT(11) AFTER `loc`

-- 更改表的存储引擎
ALTER TABLE `tb_dept2` ENGINE=MRG_MYISAM

-- 删除外键约束
ALTER TABLE `tb_emp5` DROP FOREIGN KEY `fk_emp_dept1`

-- 删除没有被关联的表
DROP TABLE `tb_dept2`


-- 删除被其它关联的主表
-- 1.解除外键约束
ALTER TABLE `tb_emp` DROP FOREIGN KEY fk_emp_dept

-- 2.删除主表
DROP TABLE `tb_dept2`

外键级联操作

ON DELETE CASCADE -- 级联删除
ON UPDATE CASCADE -- 级联更新
ON UPDATE SET NULL -- 级联置空
ALTER TABLE person ADD CONSTRAINT fk_person_nativePlace FOREIGN
KEY(nativePlaceId) REFERENCES nativePlace(id) ON UPDATE SET NULL;

经典习题:

customers 表结构

字段名	数据类型	主键	外键	非空	唯一	自增
c_num	INT(11)	是	否	是	是	是
c_name	VARCHAR(50)	否	否	否	否	否
c_contact	VARCHAR(50)	否	否	否	否	否
c_city	VARCHAR(50)	否	否	否	否	否
c_birth	DATETIME	否	否	是	否	否

1. 创建数据库Market

2. 创建数据表 customers, 在 c_num字段上添加主键约束和自增约束, 在c_birth字段上添加非空约束.

   CREATE TABLE customers(
   c_num INT(11) PRIMARY KEY AUTO_INCREMENT,
   c_name VARCHAR(50),
   c_contact VARCHAR(50),
   c_city VARCHAR(50),
   c_birth DATETIME NOT NULL
   )ENGINE=INNODB DEFAULT CHARSET=utf8;`customers`
   

3. 将c_contact 字段插入到 c_birth 字段后面.

   ALTER TABLE `customers` MODIFY `c_contact` VARCHAR(50) AFTER `c_birth`
   

4. 将 c_name 字段数据类型改为 VARCHAR(70).

   ALTER TABLE `customers` MODIFY `c_name` VARCHAR(70)
   

5. 将 c_contact 字段改为 c_phone.

   ALTER TABLE `customers` CHANGE `c_contact` `c_phone` VARCHAR(50) 
   

6. 增加 c_gender 字段,数据类型为CHAR(1).

   ALTER TABLE `customers` ADD `c_gender` CHAR(1)
   

7. 将表名修改为 customers_info.

   ALTER TABLE `customers` RENAME `customers_info`
   

8. 删除字段 c_city.

   ALTER TABLE `customers_info` DROP `c_city`
   

9. 修改数据表的存储引擎为 MyISAM.

   ALTER TABLE `customers_info` ENGINE=MRG_MYISAM
   

orders 表结构

字段名	数据类型	主键	外键	非空	唯一	自增
o_num	INT(11)	是	否	是	是	是
0_date	DATE	否	否	否	否	否
c_id	VARCHAR(50)	否	是	否	否	否

1. 创建数据表 orders , 在 o_num 字段上添加主键约束和自增约束, 在 c_id 字段上添加外键约束, 关联customers 表中的主键 c_num.

   /* 注意: 外键一定要和主表主键数据类型相同才可以 */
   CREATE TABLE orders(
   o_num INT(11) PRIMARY KEY AUTO_INCREMENT,
   o_date DATE,
   c_id INT(11),
   CONSTRAINT fk_order_customer FOREIGN KEY (c_id) REFERENCES `customers_info`(`c_num`)
   )ENGINE=INNODB DEFAULT CHARSET=utf8;
   

2. 删除orders 表的外键约束, 然后删除表 customers.

   ALTER TABLE `orders` DROP FOREIGN KEY fk_order_customer
   
   DROP TABLE `customers_info`
   

-- 查看错误信息
SHOW WARNINGS
-- 删除表中数据
DELETE FROM tmp3
CREATE TABLE tmp12(
    b BIT(4)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO tmp12(`b`) VALUES (2),(9),(15)
/* b+0 表示将二进制的结果转换为对应的数字的值,BIN()函数将数字装换为二进制 */
SELECT BIN(b+0) FROM tmp12

MySQL中的比较运算符

运算符	作用
=	等于
<=>	安全的等于
<>, !=	不等于
<=	小于等于
>=	大于等于
>	大于
IS NULL	判断一个值是否为 NULL
IS NOT NULL	判断一个值是否不为 NULL
LEAST	在有两个或多个参数时, 返回最小值
GREATEST	当有两个或多个参数时, 返回最大值
BETWEEN AND	判断一个值是否落在两个值之间
ISNULL	与 IS NULL 作用相同
IN	判断一个值是 IN 列表中的任意一个值
NOT IN	判断一个值不是 IN 列表中的任意一个值
LIKE	通配符匹配
REGEXP	正则表达式匹配

/*% : 匹配任何数目的字符, 甚至包括零字符_ : 只能匹配一个字符*/
SELECT 'stud' LIKE 'stud', 'stud' LIKE 'stu_', 'stud' LIKE '%d', 'stud' LIKE 't_ _ _','s' LIKE NULL
/*^ : 匹配以该字符后面的字符开头的字符串$ : 匹配以该字符后面的字符结尾的字符串. : 匹配任何一个单字符[] : 匹配在方括号内的任何字符*/
SELECT 'ssky' REGEXP '^s','ssky' REGEXP 'y$', 'ssky' REGEXP '.y', 'ssky' REGEXP '[a,b]'

综合案例:

CREATE TABLE tmp15(
    note VARCHAR(100),price INT
)ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO tmp15 VALUES('Thisisgood',50);
-- 对整型数值字段 price 进行算术运算
SELECT price, price +10, price -10, price *2, price /2, price %3 FROM tmp15
-- 对字段price 进行比较运算
SELECT price, price>10,price<10,price !=10,price<>10, price = 10, price<=> 10 FROM tmp15
-- 判断price值是否落在30`80区间;返回与70,30相比的最大值;判断price是否为IN列表(10,20,50,35)中的某个值
SELECT price, price BETWEEN 30 AND 80, GREATEST(price,70,30), price IN (10,20,50,35) FROM tmp15
-- 判断字段note是否为空;使用like判断是否以字母 t 开头; 使用 REGEXP 判断是否以字母 y 结尾; 判断是否包含字母 g 或者 m
SELECT note, note IS NULL, note LIKE 't%', note REGEXP '$y', note REGEXP '[g,m]' FROM tmp15
-- 将price字段值 与 null,0 进行逻辑运算
SELECT price, price && 1, price && NULL, price || 0, price AND 0, 0 AND NULL ,price OR NULL FROM tmp15
-- 将price字段值与 2, 4 进行 按位与,按位或,操作,并对price进行按位操作
SELECT price, price & 2, price | 4, ~price FROM tmp15
-- 将price 字段值费别左移和右移两位
SELECT price, price << 2, price >> 2 FROM tmp15

MySQL函数

数学函数

-- 绝对值函数
SELECT ABS(2),ABS(-3.3),ABS(-33)
-- 返回圆周率函数
SELECT PI()
-- 平方根函数
SELECT SQRT(9),SQRT(40),SQRT(-49)
-- 求余函数
SELECT MOD(31,8), MOD(234,10), MOD(45,5.6)
-- 获取整数函数
/* 返回一个不小于 x 的最小整数值 */
SELECT CEIL(-3.35), CEILING(3.35)
/* 返回一个不大于 x 的最大整数值 */
SELECT FLOOR(-3.35), FLOOR(3.35)
-- 获取随机数的函数
/* 不带参数的 RAND() 每次产生的随机数是不同的 */
SELECT RAND()
/* 当 RAND(x) 的参数相同时, 将产生相同的随机数, 不同的 x 产生的随机数值不同 */
SELECT RAND(10), RAND(10), RAND(11)
-- ROUND(x) 返回最接近于参数 x 的整数, 对 x 值进行四舍五入
SELECT ROUND(-1.14), ROUND(-1.67), ROUND(1.14), ROUND(1.66)
-- ROUND(x,y) 对操作数 x 进行四舍五入操作,结果保留小数点后面指定 y 位. -1 表示保留小数点左边1位
SELECT ROUND(1.38,1), ROUND(1.38,0), ROUND(232.38, -1)
/* TRUNCATE(x,y)函数对 x 进行截取操作,结果保留小数点后面指定 y 位,不会进行四舍五入 */
SELECT TRUNCATE(1.31,1), TRUNCATE(1.99,1),TRUNCATE(1.99,0),TRUNCATE(19.99,-1)
/* SIGN(x) 返回参数的符号, x 的值为负,零或正 时返回结果依次为 -1, 0, 1 */
SELECT SIGN(-21), SIGN(0), SIGN(21)
-- 乘方运算
SELECT POW(2,2), POWER(2,2)
/* e = 2.718281828459045EXP(x) 返回 e 的 x 乘方后的值*/
SELECT EXP(3), EXP(-3), EXP(1), EXP(0)
-- LOG(x) 返回 x 的自然对数,x 相对于基数 e 的对数
SELECT LOG(3), LOG(-3)
-- LOG10(x) 返回 x 的基数为10的对数
SELECT LOG10(2), LOG10(100), LOG10(-100)
-- RADIANS(x) 将参数 x 由角度转化为弧度
SELECT RADIANS(90), RADIANS(180)
-- DEGREES(x) 将弧度转化为角度
SELECT DEGREES(PI()), DEGREES(PI()/2)
-- SIN(x) 返回正弦, 其中x 为弧度值
SELECT SIN(1), SIN(PI())
-- ASIN(x) 返回x 的反正弦, 即正弦为 x 的值
SELECT ASIN(0.8414709848078965), ASIN(3)
-- COS(x) 返回x的余弦,其中x为弧度值
SELECT COS(0), COS(PI()), COS(1)
-- ACOS(x) 返回x的反余弦, 即余弦是x的值
SELECT ACOS(1), ACOS(-1), ROUND(ACOS(0.5403023058681398))
-- TAN(x) 返回x的正切,其中x为给定的弧度值
SELECT TAN(0.3), ROUND(TAN(PI()/4))
-- ATAN(x) 计算反正切值
SELECT ATAN(0.30933624960962325), ATAN(1)
-- COT() 函数计算余切值
SELECT COT(0.3), 1/TAN(0.3), COT(PI()/4)

字符串函数

/*
CHAR_LENGTH : 返回字符串字符个数
LENGTH : 返回字符串的字节长度,使用utf8编码字符集时,一个汉字是3个字节,一个数字或字母算一个字节
*/
SELECT CHAR_LENGTH('洪晨'), LENGTH('洪晨'),CHAR_LENGTH('date'),LENGTH('date')



-- CONCAT : 拼接字符串.(如果有一个参数为null,返回结果则为NULL)
SELECT CONCAT('myslq','5.7'), CONCAT('mysql',NULL,'5.7')

-- CONCAT_WS : 第一个参数时其它参数的分隔符,同时忽略null值
SELECT CONCAT_WS('-','mysql','5.7'), CONCAT_WS('/','2021','0813'),CONCAT_WS('*','hello',NULL,'world')

/*
INSERT(s1,x,y,s2) : 替换字符串,从第x个字符开始长度为y的字符串替换为ksun
起始位置-1超出了字符串长度,直接返回原字符
替换长度超出了原字符串长度,则从第x个字符开始,截取后面的所有字符,并替换成指定字符s2
*/
SELECT INSERT('hongchen',4,4,'ksun') AS col1
SELECT INSERT('hongchen',-1,7,'honksun') AS col2
SELECT INSERT('honchen',4,100,'ksun') AS col3


-- 全部转换为小写
SELECT LOWER('HONKSUN'),LCASE('HONKSUN')

-- 全部转换为大写
SELECT UPPER('honksun'),UCASE('honksun')

-- 返回字符串开始的最左边n个字符
SELECT LEFT('honksun',3)

-- 返回字符串开始的最右边n个字符
SELECT RIGHT('honksun',3)


-- LPAD : 左侧填充字符串
SELECT LPAD('hello',4,'??') -- hello 长度大于 4, 因此返回 hell
SELECT LPAD('hello',10,'?') -- hello 长度小于10, 左侧填充 ? , 长度为10,因此返回 ?????hello

-- RPAD : 右侧填充字符串
SELECT RPAD('hello',4,'??') -- hello 长度大于 4, 因此返回 hell
SELECT RPAD('hello',10,'??') -- hello 长度小于10, 右侧填充 ? , 长度为10,因此返回 hello?????


-- LTRIM : 只删除字符串左边的空格
SELECT '( book )', CONCAT('(',LTRIM(' book '),')')

-- RTRIM : 只删除字符串右边的空格
SELECT '( book )', CONCAT('(',RTRIM(' book '),')')

-- TRIM : 删除字符串两侧的空格
SELECT '( book )', CONCAT('(',TRIM(' book '),')')

-- 删除字符串两端的重复字符串,而中间的字符串并不会被删除
SELECT TRIM('ab' FROM 'ab88889999ab88889999ab')

-- REPEAT : 重复生成字符串
SELECT REPEAT('mysql',2)

-- SPACE : 返回一个由 n 个空格组成的字符串
SELECT CONCAT('(',SPACE(6),')')

-- REPLACE : 使用字符串 w 替代字符串 x
SELECT REPLACE('xxx.baidu.com','x','w')
UPDATE titles_test SET emp_no=REPLACE(`emp_no`,10001,10005) WHERE id=5; -- 10005 替换掉 emp_no 列中的 10001
/*
STRCMP : 比较字符串大小
s1 小于 s2 , 返回 -1
s1 大于 s2 , 返回 1 
s1 等于 s2 , 返回 0
*/
SELECT STRCMP('text','text2'), STRCMP('text2','text'), STRCMP('text','text')


-- SUBSTRING : 获取字符串的子字符串

SELECT SUBSTRING('breakfast',5); -- 返回从第5个开始到字符串结尾
SELECT SUBSTRING('breakfast',5,2); -- 返回从第5个位置开始长度为3的子字符串
SELECT SUBSTRING('breakfast',-3); -- 返回从结尾开始第3个位置到字符串结尾
SELECT SUBSTRING('breakfast',-5,3) -- 返回从结尾开始第5个位置长度为3的字符串

-- MID : 和 SUBSTRING 结果一样
SELECT MID('breakfast',5)
SELECT MID('breakfast',6,3)
SELECT MID('lunch',-3)
SELECT MID('lunch',-3,2)

-- 三个函数作用相同,返回子字符串 str1 在字符串 str 中的开始位置, INSTR 参数位置是相反的
SELECT LOCATE('ball','basketball'), POSITION('ball' IN 'basketball'),INSTR('basketball','ball')


-- REVERSE : 字符串逆序, 反转函数
SELECT REVERSE('abcdefg')

-- ELT(N,str1,str2,....) : 返回指定位置的字符串, 若N=1, 则返回值为字符串1,若N=2,则返回值为字符串2,以此类推. 若N小于1或大于参数的数目,则返回值为null
SELECT ELT(2,'hongchen','honksun'),ELT(5,'honksun')

-- FIELD(s,s1,s2,......) : 返回指定字符串位置. 返回字符串s在列表s1,s2,...中第一次出现的位置,在找不到s的情况下,返回值为0,如果s为null,则返回值为0,原因是null不能同任何值进行同等比较
SELECT FIELD('honk','hongchen','honksun','honk')

-- FIND_IN_SET : 返回子串位置,多用于字符串列表
SELECT FIND_IN_SET('honksun','hongchen,honksun,shenyang')


-- MAKE_SET : 选取字符串函数.返回由 x 的二进制指定的相应位的字符串组成的字符串.s1对应 1, s2 对应01,.......

/*
1 : 0001
4 : 0100
*/
SELECT MAKE_SET(1,'a','b','c') -- 返回第一个字符串
SELECT MAKE_SET(1 | 4,'hello','nice','world') -- 从左到右第1个和第3个
SELECT MAKE_SET(1 | 4,'hello','nice',NULL,'world') -- null值不会添加到结果中
SELECT MAKE_SET(0,'a','b','c','d') -- 0 返回空字符串

日期和时间函数

-- 获取系统当前日期,'CURDATE() + 0' 将当前日期值转换为数值型
SELECT CURDATE(),CURRENT_DATE(),CURDATE() + 0

-- 获取系统当前时间, 'CURTIME()+0' 将当前时间值转换为数值型
SELECT CURTIME(),CURRENT_TIME(),CURTIME()+0

-- 获取当前系统日期和时间
SELECT CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE()

-- 时间戳
SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP(NOW()),NOW()

-- 将时间戳转换为普通格式
SELECT FROM_UNIXTIME('1629084565')

-- 返回当前UTC日期值
SELECT UTC_DATE(),UTC_DATE()+0

-- 返回当前UTC时间值
SELECT UTC_TIME(),UTC_TIME()+0

-- 返回指定日期中的月份
SELECT MONTH('2021-08-16')

-- 返回指定日期对应的月份的英文全名
SELECT MONTHNAME('2021-08-16')

-- 获取星期的函数
SELECT DAYNAME('2021-8-16')

-- 获取日期对应的周索引
SELECT DAYOFWEEK('2021-8-16')

-- 获取日期对应的工作日索引, 0 表示周一, 1 表示周二,......
SELECT WEEKDAY('2021-08-16 11:44:31'), WEEKDAY('2021-08-16')

-- 查询指定日期是一年中的第几周
SELECT WEEK('2021-08-16')
SELECT WEEK('2021-08-16',0)
SELECT WEEK('2021-08-16',1)
SELECT WEEKOFYEAR('2021-08-16')

-- 返回指定日期在一年中的位置
SELECT DAYOFYEAR('2021-08-16')

-- 返回指定日期在一个月中的位置
SELECT DAYOFMONTH('2021-08-16')

-- 返回指定日期对应的年份
SELECT YEAR('21-02-16'),YEAR('96-10-28')

-- 返回指定日期对应的季度
SELECT QUARTER('2021-08-16')

-- 返回指定时间的分钟值
SELECT MINUTE('2021-08-16 11:56:31')

-- 返回指定时间的秒值
SELECT SECOND('2021-08-16 11:56:31'),SECOND('11:56:31')

-- 提取日期或者时间值
SELECT EXTRACT(YEAR FROM '2021-08-16 11:56:31'),EXTRACT(YEAR_MONTH FROM '2021-08-16 11:56:31'),EXTRACT(DAY_MINUTE FROM '2021-08-16 11:56:31')

-- 将时间转换为秒值
SELECT TIME_TO_SEC('11:56:31')

-- 将秒值转换为时间格式
SELECT SEC_TO_TIME(42991),SEC_TO_TIME(42991)+0,TIME_TO_SEC('12:04:00'),SEC_TO_TIME(42991)

-- 加日期操作
SELECT DATE_ADD('2021-08-16 23:59:59', INTERVAL 1 SECOND)
SELECT ADDDATE('2021-08-16 23:59:59', INTERVAL 1 SECOND)
SELECT DATE_ADD('2021-08-16 23:59:59', INTERVAL '1:1' MINUTE_SECOND) -- 增加1分1秒
-- mysql里查找某一天的后一天的用法是:DATE_ADD(yyyy-mm-dd,INTERVAL 1 DAY)，四舍五入的函数为round)

-- 减日期操作
SELECT DATE_SUB('2021-08-16', INTERVAL 5 DAY)
SELECT SUBDATE('2021-08-16', INTERVAL 3 DAY)
SELECT DATE_SUB('2021-08-16 23:59:59', INTERVAL '0 0:1:1' DAY_SECOND) -- 减少1分1秒

-- 加时间操作
SELECT ADDTIME('2021-08-16 23:59:59','1:1:1'),ADDTIME('14:33:16','2:2:2')

-- 减时间操作
SELECT SUBTIME('2021-08-16 14:36:20','1:1:1'),SUBTIME('14:36:48','0:5:0')

-- 计算两个日期之间的间隔天数
SELECT DATEDIFF('2021-10-1','2021-08-16 14:38:37')

-- 日期对应星期几
SELECT DAYNAME(NOW());

-- 格式化日期和时间值
SELECT DATE_FORMAT('2021-08-16 14:38:37', '%W %M %Y')
SELECT DATE_FORMAT('2021-08-16 14:38:37', '%D %y %a %d %m %b %j')
SELECT DATE_FORMAT('2021-08-16 14:38:37','%H:%i:%s')
SELECT DATE_FORMAT('2021-08-16 14:38:37','%X %V')

-- 格式化时间值
SELECT TIME_FORMAT('14:45:30','%H %k %h %I %l')

-- 查看显示不同格式化类型下的格式字符串
SELECT GET_FORMAT(DATE,'EUR'),GET_FORMAT(DATE,'USA')
-- 返回显示格式字符串来显示指定的日期值
SELECT DATE_FORMAT('2021-08-16 15:15:33', GET_FORMAT(DATE,'USA'))

条件判断函数

SELECT IF(1>2,2,3),IF(1<2,'yes','no'),IF(STRCMP('test','test1'),'no','yes')
SELECT IFNULL(1,2),IFNULL(NULL,10),IFNULL(1/0,'wrong')
SELECT CASE 2 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END
SELECT CASE WHEN 1<0 THEN 'true' ELSE 'false' END

系统信息函数

-- 查看当前MySQL版本号
SELECT VERSION()
-- 查看当前用户的连接数
SELECT CONNECTION_ID()
-- 查看当前用户的连接信息
SELECT PROCESSLIST
-- 查看当前使用的数据库
SELECT DATABASE(),SCHEMA()
-- 获取当前登录用户名称
SELECT USER(),CURRENT_USER(),SYSTEM_USER()
-- 获取字符串使用的字符集
SELECT CHARSET('abd'),CHARSET(CONVERT('abc' USING latin1)),CHARSET(VERSION())
-- 获取字符串排列方式
SELECT COLLATION('abc'),COLLATION(CONVERT('abc' USING utf8))
-- 获取最后一个自动生成的ID值的函数
SELECT LAST_INSERT_ID()

加/解密函数

-- 加密密码
SELECT PASSWORD('123456')
-- MD5 加密
SELECT MD5('123456')
-- ENCODE(str,pwd_str) : 加密字符串, pwd_str 作为密码, 加密 str.
SELECT ENCODE('secret','cry'), LENGTH(ENCODE('secret','cry'))
-- 解密被 ENCODE() 加密的字符串
-- DECODE(str,pwd_str) : 使用pwd_str作为密码,解密加密字符串 str
SELECT DECODE(ENCODE('secret','cry'),'cry')

格式化函数

SELECT FORMAT(1235.23658,4) -- 保留4位小数,并进行四舍五入
SELECT FORMAT(1256.2,4)	 -- 保留4位小数,位数不够用0补齐
SELECT FORMAT(12356.925,0)  -- 不保留小数位,四舍五入返回整数

数字转换不同进制函数

SELECT CONV('a',16,2) -- 字母a, 16进制转2进制
SELECT CONV(15,10,2) -- 数字15, 10进制转2进制
SELECT CONV(15,10,8) -- 数字15, 10进制转8进制
SELECT CONV(15,10,16) -- 数字15, 10进制转16进制

其它函数

-- IP地址与数字相互转换
SELECT INET_ATON('192.168.1.12')
SELECT INET_NTOA(3232235788)
-- 加锁/解锁函数
SELECT GET_LOCK('lock1',18000) 
-- 返回结果为1,说明成功得到一个名称为'lock1'的锁,持续时间为18000秒
SELECT IS_USED_LOCK('lock1') 
-- 返回结果为当前连接ID,表示lock1的锁正在被使用
SELECT IS_FREE_LOCK('lock1') 
-- 返回结果为0,说明 lock1 锁正在被使用
SELECT RELEASE_LOCK('lock1') 
-- 返回值为1,说明解锁成功
-- 重复执行指定函数
SELECT BENCHMARK(500000, PASSWORD('123456'))
-- 改变字符集函数
SELECT CHARSET('string'),CHARSET(CONVERT('string' USING latin1))
-- 改变数据类型函数
SELECT CAST(100 AS CHAR(2)), CONVERT('2021-08-18 09:05:53',TIME)

经典习题

-- 计算18除以5的商和余数
SELECT TRUNCATE(-18/5,0) -- 求商
SELECT MOD(18,5) -- 求余
-- 将弧度值 PI()/4 转换为角度值
SELECT DEGREES(PI()/4)
-- 计算 9 的 4 次方
SELECT POWER(9,4)
-- 保留浮点数值 3.14159 小数点后面2位
SELECT ROUND(3.14159,2)
-- 分别计算字符串"hello,world" 和 "university" 的长度
SELECT LENGTH('hello,world'),LENGTH('university')
-- 从字符串"nice to meet you" 中获取字符串"meet"
SELECT SUBSTRING('nice to meet you',9,4)
-- 重复输出3次字符串"cheer"
SELECT REPEAT('cheer,',3)
-- 将字符串'voodoo' 逆序输出
SELECT REVERSE('voodoo')
-- 4个字符串'MySQL','not','is','great',按顺序排序,从中选择1,3,4位置处的字符串组成新的字符串
SELECT MAKE_SET(1|12,'mysql','not','is','great') 
-- 参考1011=11
-- 计算当前日期是一年的第几周
SELECT WEEK(CURDATE())
-- 计算当前日期是一周中的第几个工作日
SELECT WEEKDAY(CURDATE())
-- 计算'1929-02-14' 与当前日期之间相差的年份
SELECT TIMESTAMPDIFF(YEAR,'1929-02-14',DATE_FORMAT(NOW(),'%Y-%m-%d'));
-- 按 "97 Oct 4th Saturday" 格式输出当前日期
SELECT DATE_FORMAT(CURDATE(),'%y,%b,%D,%W')
-- 从当前日期时间中获取时间值,并将其转换为秒值
SELECT TIME_TO_SEC(CURTIME())
-- 使用加密函数对"MySQL"加密,并解密
SELECT ENCODE('MySQL','123')
SELECT DECODE(ENCODE('MySQL','123'),'123')
-- 将十进制的值100转换为十六进制
SELECT CONV(100,10,16)
-- 格式化数值5.1584, 四舍五入保留到小数点后面3位
SELECT FORMAT(5.1584,3)
-- 将字符串"new string" 的字符集改为 gb2312
SELECT CHARSET(CONVERT('new string' USING gb2312))

查询数据

-- 查询指定范围内的条件记录
SELECT `s_id`,`f_name`,`f_price` FROM `fruits` WHERE `s_id` IN(101,102) ORDER BY `f_name`SELECT `s_id`,`f_name`,`f_price` FROM `fruits` WHERE `s_id` NOT IN(101,102) ORDER BY `f_name`
-- 查询价格在2.00元到10.2元之间的水果名称和价格
SELECT `f_name`,`f_price` FROM `fruits` WHERE `f_price` BETWEEN 2.00 AND 10.2
-- 查询价格在2.00元到10.2元之外的水果名称和价格
SELECT `f_name`,`f_price` FROM `fruits` WHERE `f_price` NOT BETWEEN 2.00 AND 10.2
-- 查找所有以'b'字母开头的水果
SELECT `f_name` FROM `fruits` WHERE `f_name` LIKE 'b%'
-- 查找所有包含'b'字母的水果
SELECT `f_name` FROM `fruits` WHERE `f_name` LIKE '%b%'
-- 查找所有以'b'字母开头,并以'y'字母结尾的水果
SELECT `f_name` FROM `fruits` WHERE `f_name` LIKE 'b%y'
-- 查找以字母 y 结尾,并且 y 前面只有4个字母的记录
SELECT `f_name` FROM `fruits` WHERE `f_name` LIKE '____y'
-- 查询`s_id` =101 并且 `f_price`大于等于5 的值
SELECT * FROM `fruits` WHERE `s_id`=101 AND `f_price` >= 5
-- 查询 `s_id`=101或者102,并且`f_price`>5,`f_name`=apple 的值
SELECT * FROM `fruits` WHERE `s_id` IN(101,102) AND `f_price`>5 AND `f_name`='apple'
-- 查询`s_id`=101或者102 的值
SELECT * FROM `fruits` WHERE `s_id`=101 OR `s_id`=102SELECT * FROM `fruits` WHERE `s_id` IN (101,102)
-- 去重复
SELECT DISTINCT `s_id` FROM `fruits`
-- 单列排序
SELECT `f_name` FROM `fruits` ORDER BY `f_name`
-- 多列排序
/* 多列排序时,首先排序的第一列必须有相同的列值,才会对第二列进行排序.如果第一列数据中所有值都是唯一的,将不再对第二列进行排序    */
SELECT `f_name`,`f_price` FROM `fruits` ORDER BY `f_name`,`f_price`
-- 指定排序
SELECT * FROM `fruits` ORDER BY `f_price` DESC -- 降序排序
SELECT * FROM `fruits` ORDER BY `f_price` ASC -- 升序排序
-- 分组
SELECT `s_id`,COUNT(*) AS total FROM `fruits` GROUP BY `s_id`-- 根据`s_id`对表进行分组,将每个供应商名字显示出来
-- GROUP_CONCAT() 函数,将每个分组中的名称显示出来
SELECT `s_id`,GROUP_CONCAT(`f_name`) AS `names` FROM `fruits` GROUP BY `s_id`-- 根据`s_id`对表进行分组,并显示水果种类大于1的分组
-- HAVING : 过滤分组
SELECT `s_id`,GROUP_CONCAT(`f_name`) AS `names` FROM `fruits` GROUP BY `s_id` HAVING COUNT(`f_name`)>1-- 分组后之后增加一条记录,统计记录数量
-- WITH ROLLUP : 分组之后进行统计
SELECT `s_id`,COUNT(*) AS `total` FROM `fruits` GROUP BY `s_id` WITH ROLLUP-- 多字段分组,先按`s_id`进行分组,再对`f_name`按不同的取值进行分组
SELECT `s_id`,`f_name` FROM `fruits` GROUP BY `s_id`,`f_name`
-- 分页
SELECT * FROM `fruits` LIMIT 0,5
-- 查询 `c_email` 为空的值
SELECT `c_id`,`c_name`,`c_email` FROM `customers` WHERE `c_email` IS NULL
-- 查询 `c_email` 不为空的值
SELECT `c_id`,`c_name`,`c_email` FROM `customers` WHERE `c_email` IS NOT NULL

使用聚合函数查询

-- COUNT(*): 计算表中总的行数,不管某列有数值或者为空值
SELECT COUNT(*) FROM `customers`
-- COUNT(字段名) : 计算指定列下总的行数,计算式将忽略空值的行
SELECT COUNT(`c_email`) FROM `customers`SELECT `s_id`,COUNT(`f_name`) FROM `fruits` GROUP BY `s_id`
-- 求和,SUM()在计算时,忽略列值为null的行
SELECT SUM(`f_price`) FROM `fruits`
-- 平均值,忽略列值为null的行
SELECT AVG(`f_price`) FROM `fruits` WHERE `s_id`=101
-- 最大值
SELECT MAX(`f_price`) FROM `fruits`
-- 最小值
SELECT MIN(`f_price`) FROM `fruits`

连接查询

-- 内连接
inner join    on
-- 外连接
left join	on
right join	on
-- 复合条件查询
inner/left/right join	on 	where ....

子查询

-- ANY和SOME  关键字在一个 比较操作符 的后面,表示若与子查询返回的任何值比较为TRUE,则返回TRUE
SELECT num1 FROM tab1 WHERE num1 > ANY (SELECT num2 FROM tab2)
-- ALL : 需要同时满足所有内层查询的条件.
SELECT num1 FROM tab1 WHERE num1 > ALL (SELECT num2 FROM tab2)
/*EXISTS : 后面的参数是一个任意的子查询,系统对子查询进行运算以判断它是否返回行,如果至少返回一行,那么 EXISTS 结果为TRUE,此时外层查询语句将进行查询.	 如果子查询没有返回任何一行,那么 EXISTS 返回的结果是FALSE,此时外层语句将不进行查询.NOT EXISTS : 后面的参数是一个任意的子查询,系统对子查询进行运算以判断它是否返回行,如果至少返回一行,那么 EXISTS 结果为FALSE,此时外层查询语句将不进行查询.	     如果子查询没有返回任何一行,那么 EXISTS 返回的结果是TRUE,此时外层语句将进行查询.*/	   
SELECT num1 FROM tab1 WHERE EXISTS (SELECT num2 FROM tab2 WHERE num2=27)SELECT num1 FROM tab1 WHERE NOT EXISTS (SELECT num2 FROM tab2 WHERE num2=27)
-- IN : 内层查询语句仅仅返回一个数据列,这个数据列里的值将提供给外层查询语句进行比较
SELECT num1 FROM tab1 WHERE num1 IN(SELECT num2 FROM tab2)SELECT num1 FROM tab1 WHERE num1 NOT IN(SELECT num2 FROM tab2)
-- 合并查询
-- UNION : 将结果组合成单个结果集,执行的时候删除重复的记录,所有返回的行都是唯一的.
SELECT num1 FROM tab1 UNION SELECT num2 FROM tab2 
-- UNION ALL : 将结果组合成单个结果集,执行的时候不删除重复行也不对结果进行自动排序.
SELECT num1 FROM tab1 UNION ALL SELECT num2 FROM tab2 
-- REGEXP : 正则表达式
SELECT * FROM `fruits` WHERE `f_name` REGEXP '^b'

正则表达式常用字符匹配列表

选项	说明	例子
^	匹配文本的开始字符	'^b'匹配以字母b开头的字符串
$	匹配文本的结束字符	'st$'匹配以st结尾的字符串
.	匹配任何单个字符	'b.t'匹配任何b和t之间有一个字符
*	匹配零个或多个在它前面的字符	'f*n'匹配字符n前面有任意个字符f
<字符串>	匹配包含指定的字符串的文本	'fa'
[字符集合]	匹配字符集合中的任何一个字符	'[xz]'匹配x或者z
[^]	匹配不在括号中的任何字符	'[^ abc]'匹配任何不包含 a,b,c的字符串
字符串	匹配前面的字符串至少n次	b{2}匹配2个或更多的b
字符串	匹配前面的字符串至少n次,至多m次.如果n为0,此参数为可选参数	b{2,4}匹配最少2个,最多4个b

经典习题

-- 查询名字以字母N或者S结尾的记录
SELECT * FROM `employee` WHERE `e_name` LIKE '%N' OR `e_name` LIKE '%S'

-- 查询所有2001~2005年入职的员工信息,查询部门编号为20和30的员工信息并使用 UNION 合并两个查询结果
SELECT * FROM `employee` WHERE `hireDate` BETWEEN '2001-01-01' AND '2005-12-31'
UNION
SELECT * FROM `employee` WHERE `dept_no`='20' OR `dept_no`='30'

-- 使用 LIKE 查询员工姓名中包含a的记录
SELECT * FROM `employee` WHERE `e_name` LIKE '%a%'

-- 使用REGEXP 查询员工姓名中包含T,C,M 3个字母中任意1个记录
SELECT * FROM `employee` WHERE `e_name` REGEXP '[TCM]'

索引

-- 创建普通索引
CREATE TABLE book(
bookid INT NOT NULL,
bookname VARCHAR(255) NOT NULL,
`authors` VARCHAR(255) NOT NULL,
info VARCHAR(255) NULL,
`comment` VARCHAR(255) NULL,
year_publication YEAR NOT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 使用 EXPLAIN 语句查看索引是否正在使用
EXPLAIN SELECT * FROM book WHERE year_publication=1990


-- 创建唯一索引
CREATE TABLE t1(
id INT NOT NULL,
`name` CHAR(30) NOT NULL,
UNIQUE INDEX UniqIdx(id)  -- 在 id 字段上使用 UNIQUE 关键字创建一个名为 UniqIdx 的唯一索引
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 创建单列索引
CREATE TABLE t2(
id INT NOT NULL,
`name` CHAR(50) NULL,
INDEX SingleIdx(`name`(20))  -- 在 id 字段上使用 INDEX 关键字创建一个名为 SingleIdx 的单列索引
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 创建组合索引
CREATE TABLE t3(
id INT NOT NULL,
`name` CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
INDEX MultiIdx(id,`name`,age) -- 在 id,name,age字段上创建一个名为 MultiIdx 的组合索引
)ENGINE=INNODB DEFAULT CHARSET=utf8;

/*
全文索引: 可用于全文搜索. 只有MyISAM 存储引擎支持 FULLTEXT 索引.
并且只为 CHAR,VARCHAR,TEXT 列创建索引.
*/
CREATE TABLE t4(
id INT NOT NULL,
`name` CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
FULLTEXT INDEX FullTxtIdx(info) -- 在info 字段上建立全文索引
)ENGINE=INNODB DEFAULT CHARSET=utf8;

mysql强制索引和禁止某个索引
1、mysql强制使用索引:force index(索引名或者主键PRI)
例如:
select * from table force index(PRI) limit 2;(强制使用主键)
select * from table force index(ziduan1_index) limit 2;(强制使用索引"ziduan1_index")
select * from table force index(PRI,ziduan1_index) limit 2;(强制使用索引"PRI和ziduan1_index")

2、mysql禁止某个索引：ignore index(索引名或者主键PRI)
例如:
select * from table ignore index(PRI) limit 2;(禁止使用主键)
select * from table ignore index(ziduan1_index) limit 2;(禁止使用索引"ziduan1_index")
select * from table ignore index(PRI,ziduan1_index) limit 2;(禁止使用索引"PRI,ziduan1_index")

-- 查看指定表中创建的索引
SHOW INDEX FROM book

-- 在 bookname 字段上添加索引
ALTER TABLE `book` ADD INDEX BkNameIdx(bookname(30))

-- 在 bookId 字段上添加唯一索引
ALTER TABLE book ADD UNIQUE INDEX UniqidIdx(bookId)

-- 在 bookname 字段上创建普通索引
CREATE INDEX BkName ON `book`(`bookname`)

-- 在 bookid 字段上创建唯一索引
CREATE UNIQUE INDEX UniqidIdx ON `book`(`bookid`)

-- 删除索引
ALTER TABLE `book` DROP INDEX UniqidIdx

DROP INDEX BkName ON `book`

存储过程和函数

创建存储过程和函数

DELIMITER $$
CREATE    PROCEDURE `honksunstudy`.`Proc`(IN | OUT | INOUT ) -- IN 表示输入参数,OUT 表示输出参数,INOUT表示既可以输入也可以输出	
BEGIN		
-- 定义变量   
DECLARE myparam INT DEFAULT 100;      
-- 为变量赋值   
SET myparam=10;      
-- 声明光标.光标的名称为: cursor_fruit, select 语句从fruits表中查询出f_name,f_price   
DECLARE cursor_fruit CURSOR FOR SELECT `f_name`,`f_price` FROM `fruits`;      
-- 打开光标   
OPEN cursor_fruit;      
-- 使用光标.将查询出来的数据存入 fruit_name,fruit_price 这两个变量中   
FETCH cursor_fruit INTO fruit_name, fruit_price;      
-- 关闭光标   
CLOSE cursor_fruit		
SELECT * FROM `fruits`	
END$$
DELIMITER ;
-- 定义条件. 定义"ERROR 1148(42000)"错误,名称为 command_not_allowed
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';
DECLARE command_not_allowed CONDITION FOR 1148;
-- 定义处理程序-- 捕获SQLstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='NO_SUCH_TBALE' -- 如果遇到sqlstate_value值为"42S02",执行 CONTINUE 操作,并且输出"NO_SUCH_TBALE"信息.
-- 捕获MySQL_error_code
DECLARE CONTINUE HANDLER FOR 1146 SET @info='NO_SUCH_TBALE' 
-- 如果遇到MySQL_error_code值为1146,执行 CONTINUE 操作,并且输出"NO_SUCH_TBALE"信息.
-- 先定义条件,然后调用DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info='NO_SUCH_TBALE'; -- 这里先定义no_such_table条件,遇到1146错误就执行CONTINUE操作.
-- 使用SQLwarning
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='NO_SUCH_TBALE'; -- SQLwarning捕获所有以01开头的sqlstate_value值,然后执行exit操作,并且输出"error"信息.
-- 使用not found
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='NO_SUCH_TBALE'; -- NOT FOUND 捕获所有以02开头的sqlstate_value值,然后执行exit操作,并且输出"NO_SUCH_TBALE"信息.
-- 使用SQLexception
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='NO_SUCH_TBALE'; -- sqlexception捕获所有没有被SQLwarning或not found 捕获的sqlstate_value值,然后执行exit操作,并且输入"ERROR"信息.
/*该示例判断val值是否为空,如果val值为空,输出字符串"val is NULL",否则输出字符串"val is not NULL".IF 语句都需要使用 END IF 来结束.*/
IF val IS NULL  THEN SELECT 'val is NULL';  ELSE SELECT 'val is not NULL';END IF;  
-- CASE 语句
CASE val WHEN 1 THEN SELECT 'val is 1'; WHEN 2 THEN SELECT 'val is 2'; ELSE SELECT 'val is not 1 or 2';END CASE;
CASE WHEN val IS NULL THEN SELECT 'val is NULL'; WHEN val <0 THEN SELECT 'val is less than 0'; WHEN val >0 THEN SELECT 'val is greater than 0'; ELSE SELECT 'val is 0';END CASE;
-- LOOP 循环语句
DECLARE id INT DEFAULT 0;add_loop: LOOPSET id = id+1;  IF id >= 10 THEN LEAVE add_loop; -- LEAVE 用来退出任何被标注的流程控制构造  
END IF;END LOOP add_loop;
-- ITERATE : 将执行顺序转到语句段开头处
/*p1=0,如果p1的值小于10时,重复执行p1加1操作;当p1大于等于10并且小于20时,打印消息"p1 is between 10 and 20"; 当p1大于20时,退出循环*/
CREATE PROCEDURE doiterate()BEGINDECLARE p1 INT DEFAULT 0;
my_loop:LOOP  
SET p1=p1+1;  
IF p1 < 10 THEN ITERATE my_loop;  
ELSEIF p1 >20 THEN LEAVE my_loop; 
END IF;  
SELECT 'p1 is between 10 and 20';END LOOP my_loop;
END  
-- REPEAT : 创建一个带条件判断的循环过程,每次语句执行完毕之后,会对条件表达式进行判断,如果表达式为真,则循环结束;否则重复执行循环中的语句.
/*执行循环id加1操作.当id值小于10时,循环重复执行;当id值大于或等于10时,退出循环.REPEAT循环都以 END REPEAT 结束*/
DECLARE id INT DEFAULT 0;
REPEATSET id = id+1;UNTIL id >=10
END REPEAT;
-- WHILE : 创建一个带条件判断的循环过程,与 REPEAT 不同, WHILE 在执行语句时,先对指定的表达式进行判断,如果为真,则进行循环内的语句,否则退出循环
/*i值小于10时,将重复执行循环过程*/
DECLARE i INT DEFAULT 0;
WHILE i< 10 DOSET i=i+1;
END WHILE;

调用储存过程和函数

-- 调用储存过程
CALL `Proc`
-- 调用储存函数
SELECT `CountProc2`(101)

创建动态储存过程

DELIMITER $$

USE `test01`$$

DROP PROCEDURE IF EXISTS `gbks`$$

CREATE DEFINER=`root`@`%` PROCEDURE `gbks`(IN chars VARCHAR(255)) -- 定义储存过程名字和参数
BEGIN
	  DECLARE ss VARCHAR(255); -- 定义变量
	  SET @ss=chars;	-- 将参数的值 赋值 给变量
	  
	  DROP TABLE IF EXISTS test;
	  CREATE TABLE test(
	    content VARCHAR(255)
	  )ENGINE=INNODB DEFAULT CHARSET=gbk;
	  
	  INSERT INTO test VALUES (@ss);	-- 使用变量
	  
	  SELECT LENGTH(content) AS `GBK编码字符长度` FROM test;	
	
	END$$

DELIMITER ;

创建OUT模式的存储过程和使用

-- out 模式的存储过程
DELIMITER $$

CREATE
    PROCEDURE `shop`.`test_into`(OUT accounts INT)
   
	BEGIN
	
	SELECT COUNT(*) INTO accounts	-- 将结果赋值给参数accounts
	FROM account;

	END$$

DELIMITER ;

-- 调用out模式的存储过程
CALL test_into(@into);
-- 查看传出的参数
SELECT @into;

查看存储过程和函数

-- 获取数据库中所有以字母'P'开头的存储过程信息
SHOW PROCEDURE STATUS LIKE 'P%'SHOW FUNCTION STATUS LIKE 'C%'
-- 查看存储过程和函数定义
SHOW CREATE FUNCTION `CountProc2`SHOW CREATE PROCEDURE `Proc`
-- 从 information_schema.`ROUTINES` 表中查看存储过程和函数过程信息
SELECT * FROM information_schema.`ROUTINES` WHERE `ROUTINE_NAME`='CountProc2' AND `ROUTINE_TYPE`='FUNCTION'

修改存储过程和函数

-- 修改存储过程定义
ALTER PROCEDURE `Proc` MODIFIES SQL DATA SQL SECURITY INVOKER
-- 查看修改后信息
SELECT SPECIFIC_NAME,SQL_DATA_ACCESS,SECURITY_TYPE FROM information_schema.`ROUTINES` WHERE `ROUTINE_NAME`='Proc' AND `ROUTINE_TYPE`='PROCEDURE'

-- 修改函数定义
ALTER FUNCTION `CountProc2` READS SQL DATA COMMENT 'FIND NAME'
-- 查看修改后信息
SELECT SPECIFIC_NAME,SQL_DATA_ACCESS,SECURITY_TYPE FROM information_schema.`ROUTINES` WHERE `ROUTINE_NAME`='CountProc2' AND `ROUTINE_TYPE`='FUNCTION'

删除存储过程和函数

-- 删除储存过程
DROP PROCEDURE `Proc`

-- 删除存储函数
DROP FUNCTION `CountProc2`

练习题

1. 写个hello world 的存储过程和函数

   存储过程

   DELIMITER $$
   
   CREATE
       /*[DEFINER = { user | CURRENT_USER }]*/
       PROCEDURE `honksunstudy`.`HelloWorld`()
   	BEGIN
   	
   	SELECT 'Hello,World';
   
   	END$$
   
   DELIMITER ;
   
   -- 调用储存过程
   CALL `HelloWorld`
   

   函数

   DELIMITER $$
   
   CREATE
       /*[DEFINER = { user | CURRENT_USER }]*/
       FUNCTION `honksunstudy`.`HelloWorld2`()
       RETURNS VARCHAR(255)
       BEGIN
       
       RETURN (SELECT 'Hello,World2');
   
       END$$
   
   DELIMITER ;
   
   -- 调用函数
   SELECT `HelloWorld2`()
   

2. 创建一个执行动态SQL的储存过程.

   DELIMITER $$
   
   CREATE
       /*[DEFINER = { user | CURRENT_USER }]*/
       PROCEDURE `test01`.`test2`(IN a INT)
   	BEGIN
   	
   	SELECT * FROM `employee` WHERE `Id`=a;
   
   	END$$
   
   DELIMITER ;
   -- 调用储存过程
   CALL `test2`(2);
   

视图

创建视图

CREATE    VIEW `honksunstudy`.`view_t`     AS(SELECT * FROM `fruits`);

使用视图

SELECT * FROM `view_t`

-- 查看视图基本信息
DESCRIBE `view_t`
-- 查看数据库中所有视图的信息
SELECT * FROM `information_schema`.views

修改视图

-- 修改视图
CREATE OR REPLACE VIEW `view_t2` AS SELECT * FROM `tab1`ALTER VIEW `view_t2` AS SELECT * FROM `tb_emp8`
-- 更新视图
UPDATE `view_t2` SET `salary`=5000 WHERE id=1
-- 插入视图
INSERT INTO `view_t2` VALUES (4,'VUE',NULL,6000)
-- 删除视图记录
DELETE FROM `view_t2` WHERE id=4
-- 删除视图
DROP VIEW `view_t2`

触发器

创建触发器

DELIMITER $$CREATE    /*[DEFINER = { user | CURRENT_USER }]*/   
TRIGGER `honksunstudy`.`ins_sum` 
BEFORE INSERT    ON `honksunstudy`.`t2`    
FOR EACH ROW BEGIN        
INSERT INTO `t1` VALUES (101,'honksun');        
END$$
DELIMITER ;

-- 查看触发器
SHOW TRIGGERS
-- 查看系统所有的触发器
SELECT * FROM `information_schema`.`TRIGGERS`
-- 删除触发器
DROP TRIGGER `ins_sum`

用户操作

-- 创建用户
CREATE USER 'honksun'@'localhost' IDENTIFIED BY '123456'
-- 授权用户操作权限
GRANT SELECT,UPDATE ON *.* TO 'honksun'@'localhost' IDENTIFIED BY '123456'
-- 删除用户
DROP USER 'hongchen'@'localhost'
-- 修改root密码在dos窗口下输入:
mysqladmin -u root -h localhost -p PASSWORD "654321"SELECT PASSWORD('123456') 
-- user表中的密码可以用password函数获取也可以修改MySQL数据库中的user表,用update更新密码
UPDATE `user` SET authentication_string=PASSWORD('654321') WHERE USER='honksun' AND HOST='localhost'
-- 修改登录的用户自身的密码
SET PASSWORD=PASSWORD('123456')
-- root用户修改普通用户密码
SET PASSWORD FOR 'honksun'@'localhost'=PASSWORD("654321")GRANT USAGE ON *.* TO 'honksun'@'localhost' IDENTIFIED BY '123456'
-- 修改完密码后,一定要重新加载权限
FLUSH PRIVILEGES -- 重新加载权限

权限控制

-- 授权
GRANT SELECT ON *.* TO 'honksun'@'localhost'
-- 收权
REVOKE SELECT ON *.* FROM 'honksun'@'localhost'
-- 查看权限
SHOW GRANTS FOR 'honksun'@'localhost'

练习题

创建联系数据库

CREATE TABLE player(
    playid INT PRIMARY KEY,
    playname VARCHAR(30),
    teamnum INT UNIQUE,
    info VARCHAR(50)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

1. 创建一个新账号,用户名为account1, 该用户通过本地主机连接数据库,密码为'oldpwd1', 授权该用户对player表的select和insert 权限,并且授权该用户对player表的info字段的update权限.

   GRANT SELECT,INSERT,UPDATE(`info`) ON `player` TO 'account1'@'localhost' IDENTIFIED BY 'oldpwd1'
   

2. 更改account1用户的密码为 789654

   SET PASSWORD FOR 'account1'@'localhost'='789654'
   

3. 使用flush privileges 重新加载权限表

   FLUSH PRIVILEGES
   

4. 查看授权给 account1 用户的权限

   SHOW GRANTS FOR 'account1'@'localhost'
   

5. 收回 account1 用户的权限

   REVOKE UPDATE ON `player` FROM 'account1'@'localhost'
   

6. 将 account1 用户的账号信息从系统中删除.

   DROP USER 'account1'@'localhost'
   

数据备份与恢复

为了保证数据的一致性,在备份文件前,执行刷新缓存操作,将内存中的数据刷新到磁盘中,同时锁定数据表. 备份文件时,最好关闭服务器.

FLUSH TABLES WITH READ LOCK

以管理员方式运行DOS窗口

-- 备份数据库
mysqldump -u root -p honksunstudy > D:\backup\honksunstudy_20210826.sql

-- 备份数据表
mysqldump -u root -p honksunstudy fruits > D:\backup\fruits20210826_backup.sql

-- 备份多个数据库
mysqldump -u root -p --databases honksunstudy draft > D:\backup\honksunstudy_draft_20210826_backup.sql

-- 备份所有数据库
mysqldump -u root -p --all-databases > D:\backup\Alldatabases_backup.sql

-- 恢复数据库
1. 执行恢复数据库语句前,必须先创建数据库.
2. mysql -u root -p honksunstudy < D:\backup\honksunstudy_20210826.sql

在登陆数据库的情况下: 使用 source 命令 可以恢复数据库.
mysql> use honksunstudy
Database changed
mysql> source D:\backup\fruits20210826_backup.sql

-- 数据库迁移
mysqldump -h www.abc.com -uroot -ppassword dbname | mysql -h www.def.com -uroot -ppassword

表的导出

1. 如果出现报错 :

   mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'
   

   1. 在 my.ini文件下面添加一行

      [mysqld]
      basedir=D:\mysql-5.7.31\
      datadir=D:\mysql-5.7.31\data\
      port=3306
      character-set-server=utf8
      
      -- 添加语句
      secure_file_priv =
      

   2. 执行导出命令即可.

      -T 表示文件类型为txt文件
      mysqldump -T D:\backup -u root -p honksunstudy fruits
      

-- 导出文件类型为 txt
mysql -u root -p --execute="select * from fruits;" honksunstudy > D:\backup\fruits.txt

-- 导出文件类型为 html
mysql -u root -p --html --execute="select * from fruits;" honksunstudy > D:\backup\fruits.html

-- 导出文件类型为 xml
mysql -u root -p --xml --execute="select * from fruits;" honksunstudy > D:\backup\fruits.xml

表的导入

如果出现报错:

mysqlimport: Error: 1406, Data too long for column 'f_id' at row 1, when using table: fruits

1. 在my.ini文件下面添加一行

[mysqld]
basedir=D:\mysql-5.7.31\
datadir=D:\mysql-5.7.31\data\
port=3306
character-set-server=utf8
secure_file_priv =
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

2. 然后执行导入命令.

mysqlimport -u root -p honksunstudy D:\backup/fruits.txt

直接备份表

-- 创建一个和原表一样的备份表
CREATE TABLE customers_backup LIKE customers

-- 拷贝数据到备份表
INSERT INTO customers_backup SELECT * FROM customers

-- 直接复制表
CREATE TABLE customprice_backup SELECT * FROM customprice;

性能优化

-- 分析查询语句
EXPLAIN SELECT * FROM `fruits`

-- 查询MySQL服务器的连接次数
SHOW  STATUS LIKE 'CONNECTIONS'

-- 查询服务器上线时间
SHOW STATUS LIKE 'Uptime'

-- 查询MySQL 服务器的慢查询次数
SHOW STATUS LIKE 'slow_queries'

-- 查询操作的次数
SHOW STATUS LIKE 'Com_select'

-- 查询插入操作的次数
SHOW STATUS LIKE 'Com_insert'

-- 查询更新操作的次数
SHOW STATUS LIKE 'Com_update'

-- 查询删除操作的次数
SHOW STATUS LIKE 'Com_delete'

-- 分析查询语句
EXPLAIN SELECT * FROM fruits
DESCRIBE SELECT * FROM fruits WHERE fruits.s_id='101'

-- 给fruits表的s_id字段加上索引
CREATE INDEX Sid ON fruits(s_id)

-- 优化插入记录速度
-- 1.禁用索引
ALTER TABLE fruits DISABLE KEYS
-- 重新开启索引
ALTER TABLE fruits ENABLE KEYS

-- 2.禁用唯一性检查
SET UNIQUE_CHECKS=0
-- 开启唯一性检查
SET UNIQUE_CHECKS=1

-- 3.禁用外键检查
set FOREIGN_key_checks=0
-- 开启外键检查
SET FOREIGN_key_checks=1

-- 4.禁止自动提交
SET autocommit=0
-- 开启自动提交
SET autocommit=1

/*
ANALYZE TABLE 分析表的过程中,数据库系统会自动对表加一个只读锁. 在分析期间,只能读取表中的记录,不能更新和插入记录.
*/
ANALYZE TABLE fruits;

-- 检查表
CHECK TABLE fruits

-- 优化表
OPTIMIZE TABLE fruits

-- 在DOS窗口下登录MySQL, 执行
show variables like 'datadir';  -- 显示真正的MySQL配置文件路径

事务

-- 打开事务的方式1
BEGIN;

UPDATE t1 SET `name`='honksun' WHERE id=2;

end;

-- 打开事务方式2
SET autocommit=0; -- 关闭事务自动提交
-- 开启事务
START TRANSACTION;

UPDATE t1 SET `name`='hongchen' WHERE id=2

ROLLBACK

COMMIT

SET autocommit=1;	-- 开启事务自动提交

事务回滚点

-- 1、查看mysql事务是否开启
要查看当前是否已开启事件调度器，可执行如下SQL：  
SHOW VARIABLES LIKE 'event_scheduler';  
或  
SELECT @@event_scheduler;  
或  
SHOW PROCESSLIST;

-- 2、开启mysql事务可执行
SET GLOBAL event_scheduler = 1;  
或  
SET GLOBAL event_scheduler = ON;

-- 3、默认的是commit自动提交到数据库，查看命令是如下：
show variables like "autocommit";

-- 4、直接用 SET 来改变 MySQL 的自动提交模式:
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

-- 5. 设置回滚点
SET autocommit=0; -- 关闭自动提交
START TRANSACTION; -- 手动开启事务

UPDATE logss SET Num =2 WHERE Id=2;

SAVEPOINT sp1; -- 保存回滚点名称

UPDATE logss SET Num=3 WHERE Id=3;

ROLLBACK TO sp1; -- 回滚到回滚点

COMMIT; -- 提交事务

更新表 update,replace into

/* 
mysql中常用的三种插入数据的语句:
insert into表示插入数据，数据库会检查主键，如果出现重复会报错；
replace into表示插入替换数据，需求表中有PrimaryKey，或者unique索引，
如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样；
insert ignore表示，如果中已经存在相同的记录，则忽略当前新数据；
*/

replace into 跟 insert 功能类似，不同点在于：
replace into 首先尝试插入数据到表中，
1. 如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。
2. 否则，直接插入新数据。

要注意的是：插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。

MySQL replace into 有三种形式：

1. replace into tbl_name(col_name, ...) values(...)

2. replace into tbl_name(col_name, ...) select ...

3. replace into tbl_name set col_name=value, ...

REPLACE INTO test02 SELECT * FROM test01;


修改 update

 -- 修改学员名字 带了指定条件 where
 UPDATE `student` SET `name`='honksun' WHERE id=1;
 
 -- 不指定条件的情况下，会修改所有的表
 UPDATE `student` SET `pwd`='111111'
 
 -- 修改多个属性，用逗号隔开 ,
 UPDATE `student` SET `name`='honksun',`address`='沈阳' WHERE id=1;
 
 -- 通过多个条件定位数据
 UPDATE `student` SET `sex`='帅哥' WHERE pwd='123456' AND sex='男'

删除表

1、delete

 -- 删除数据(避免这样操作，会清空表的所有内容)
 DELETE FROM `student`
 
 -- 删除指定数据
 DELETE FROM `student` WHERE `id`=1;

语法：DELETE FROM 表名 [where 条件]

2、truncate

 -- 清空表
 TRUNCATE `grade`

delete 和 truncate 的区别：

相同点：都可以删除表数据，不会删除表结构。

不同点：

• truncate 重新设置自增列，计数器归零。
• truncate 不会影响事务

创建定时事件任务

DELIMITER $$

SET GLOBAL event_scheduler = ON$$     -- required for event to execute but not create    

CREATE EVENT `test01`.`test`

ON SCHEDULE
	 EVERY '1' DAY STARTS '2021-10-18 11:35:00' -- 每天的11:35分执行
DO
	BEGIN
	    TRUNCATE employee01; -- 执行的SQL语句
	END$$

DELIMITER ;

查找一张表中连续出现的数据

-- 查找所有至少连续出现三次的数字。

-- 方式一
SELECT s1.* FROM logss AS s1
INNER JOIN logss AS s2 ON s1.Id+1=s2.Id
INNER JOIN logss AS s3 ON s1.Id+2=s3.Id 
WHERE s1.Num=s2.Num AND s1.Num=s3.Num

-- 方式二
SELECT s1.*
FROM
logss AS s1, logss AS s2, logss AS s3
WHERE
s1.Id+1=s2.Id
AND s1.Id+2=s3.Id
AND s1.Num=s2.Num
AND s1.Num=s3.Num;

Windows 环境下 MySQL 主从复制

1. 查看datadir的具体路径

   SHOW VARIABLES LIKE '%datadir%';
   

2. 打开 my.ini 文件, 添加如下代码

   [mysqld]
   log_bin="D:/MySQL Server 5.7/MySQLlog/binlog"
   expire_logs_days=10		# 表示二进制日志文件删除的天数
   max_binlog_size=100M	# 表示二进制日志文件最大的大小
   

   注意,此时我们需要在 D:\MySQL Server 5.7\ 文件夹下新建 MySQLlog 文件夹,在 MySQLlog 文件夹下新建binlog文件夹,日志记录在该文件夹里面.

   配置好日志文件夹后,需要重启MySQL服务.

   MySQL安装位置 D:\MySQL Server 5.7\Data 文件夹下的 .err 文件为MySQL的启动报错日志文件.

3. 测试查看 log_bin 是否成功开启

   SHOW VARIABLES LIKE '%log_bin%';
   /*===========================================================*/
   log_bin	OFF	-- 参数为 ON,表示二进制日志文件开启;为OFF,表示二进制日志文件开启失败
   log_bin_basename	
   log_bin_index	
   log_bin_trust_function_creators	OFF
   log_bin_use_v1_row_events	OFF
   sql_log_bin	ON
   

4. 在 master 主机上创建一个复制所需要的账号.

   # repl 为用户名, % 表示任何远程地地址的 repl 用户都可以连接 master 主机.
   mysql> grant replication slave on *.* to repl@'%' identified by '123456';
   Query OK, 0 rows affected, 1 warning (0.01 sec)
   

5. 在 my.ini 配置文件中配置 master 主机相关的信息.

   [mysqld]
   log_bin="D:/MySQL Server 5.7/MySQLlog/binlog"
   expire_logs_days=10
   max_binlog_size=100M
   
   server-id=1		# 表示服务器标识 id 号,master 和 salve 主机的 server-id 不能一样
   binlog-do-db=test01		# 表示需要复制的数据库
   binlog-ignore-db=mysql 	# 表示不需要复制的数据库
   

6. 重启MySQL 服务, 然后输入 SHOW MASTER STATUS; 命令查询 master 主机信息.

7. 将 master 主机的数据备份出来

   D:\MySQL Server 5.7\bin>mysqldump -u root -p -h localhost test01 >D:\test01.txt
   

8. 将 test01.txt 复制到 salve 上面去,在 salve 上面创建 test01 数据库, 然后执行以下操作

   D:\MySQL Server 5.7\bin>mysqldump -u root -p -h localhost test01 <d:\test01.txt
   Enter password: ******
   -- MySQL dump 10.13  Distrib 5.7.35, for Win64 (x86_64)
   --
   -- Host: localhost    Database: test01
   -- ------------------------------------------------------
   -- Server version       5.7.35-log
   
   /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
   /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
   /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
   /*!40101 SET NAMES utf8 */;
   /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
   /*!40103 SET TIME_ZONE='+00:00' */;
   /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
   /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0
   */;
   /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
   /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
   /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
   
   /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
   /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
   /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
   /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
   /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
   /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
   /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
   
   -- Dump completed on 2021-10-26 14:13:10
   

9. 配置 salve 从机上的 my.ini 配置文件.

   [mysqld]
   server-id=2		# 从机配置 server-id 一定不能与主机 master 相同
   
   # The next three options are mutually exclusive to SERVER_PORT below.
   # skip-networking
   # enable-named-pipe
   # shared-memory
   
   		找到这个模块
   # ***** Group Replication Related *****
   # Specifies the server ID. For servers that are used in a replication topology,
   # you must specify a unique server ID for each replication server, in the
   # range from 1 to 2^32 ? 1. “Unique” means that each ID must be different
   # from every other ID in use by any other replication source or replica.
   # server-id=1	-- 注释掉这条语句
   # ***** Group Replication Related *****
   # The host name or IP address of the replica to be reported to the source
   # during replica registration. This value appears in the output of SHOW SLAVE HOSTS
   # on the source server. Leave the value unset if you do not want the replica to
   # register itself with the source.
   # report_host=0.0
   
   # 另外, 如果配置中还有 log_bin 的配置,可以将它注释掉
   

10. 重启 salve 从机上面的 MySQL 服务, 然后关闭 salve 服务.

    # 查看主机状态
    SHOW MASTER STATUS;
    
    # 停止从机 slave 服务
    mysql> stop slave;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> change master to
        -> master_host='192.168.172.150',
        -> master_user='repl',
        -> master_password='123456',
        -> master_log_file='binlog.000002',
        -> master_log_pos=154;
    Query OK, 0 rows affected, 1 warning (0.03 sec)
    

11. 开启 slave 从机的 slave 服务

    # 开启从机 slave 服务
    mysql> start slave;
    Query OK, 0 rows affected (0.02 sec)
    
    # 查看 从机 slave 状态
    mysql> show slave status \G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.172.150
                      Master_User: repl
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: binlog.000002
              Read_Master_Log_Pos: 4116
                   Relay_Log_File: 1-PC-relay-bin.000002
                    Relay_Log_Pos: 317
            Relay_Master_Log_File: binlog.000002
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB:
              Replicate_Ignore_DB:
               Replicate_Do_Table:
           Replicate_Ignore_Table:
          Replicate_Wild_Do_Table:
      Replicate_Wild_Ignore_Table:
                       Last_Errno: 0
                       Last_Error:
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 4116
                  Relay_Log_Space: 523
                  Until_Condition: None
                   Until_Log_File:
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File:
               Master_SSL_CA_Path:
                  Master_SSL_Cert:
                Master_SSL_Cipher:
                   Master_SSL_Key:
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error:
                   Last_SQL_Errno: 0
                   Last_SQL_Error:
      Replicate_Ignore_Server_Ids:
                 Master_Server_Id: 1
                      Master_UUID: cdd324a1-1d9d-11ec-8e13-b42e99b0c24d
                 Master_Info_File: D:\MySQL Server 5.7\Data\master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more up
    dates
               Master_Retry_Count: 86400
                      Master_Bind:
          Last_IO_Error_Timestamp:
         Last_SQL_Error_Timestamp:
                   Master_SSL_Crl:
               Master_SSL_Crlpath:
               Retrieved_Gtid_Set:
                Executed_Gtid_Set:
                    Auto_Position: 0
             Replicate_Rewrite_DB:
                     Channel_Name:
               Master_TLS_Version:
    1 row in set (0.00 sec)
    

12. 测试环境是否搭建成功.新建一张表,然后插入数据,看是否同步成功.

问题解决

1. 从机 slave 出现 server-id 与主机 master 重复问题

   2021-10-26T07:01:05.001866Z 3 [ERROR] Slave I/O for channel '': Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). Error_code: 1593
   

   解决办法:

   1. 首先查看 从机 slave 的server-id

      mysql> show variables like 'server_id';
      +---------------+-------+
      | Variable_name | Value |
      +---------------+-------+
      | server_id     | 2     |
      +---------------+-------+
      1 row in set, 1 warning (0.00 sec)
      

   2. 关闭 从机 slave 服务.

      mysql> stop slave;
      

   3. 然后更改server_id 的值.

      mysql> set global server_id=2;
      Query OK, 0 rows affected (0.00 sec)
      

   4. 重启 slave 服务

   5. 如果不可以, 再试试重启 MySQL 服务.

参考解决方案: https://www.sohu.com/a/337010600_100109711

2. 从机 slave 出现 找不到表错误.

   Last_SQL_Errno: 1051
   Last_SQL_Error: Error 'Unknown table 'test01.test03'' on query. D
   efault database: 'test01'. Query: 'DROP TABLE `test03` /* generated by server */'
   

   1. 查看主机 master 状态.

      mysql> show master status \G
      *************************** 1. row ***************************
                   File: binlog.000002 # 记下 File 的日志代号
               Position: 4116		# 记下 Position 的编号
           Binlog_Do_DB: test01
       Binlog_Ignore_DB: mysql
      Executed_Gtid_Set:
      1 row in set (0.00 sec)
      

   2. 修改从机 slave 的 master 配置

      mysql> change master to
          -> master_host='192.168.172.150',
          -> master_user='repl',
          -> master_password='123456',
          -> master_log_file='binlog.000002',
          -> master_log_pos=4116;
      Query OK, 0 rows affected, 1 warning (0.00 sec)
      

   3. 重启从机 slave 服务

   4. 重启MySQL 服务.

查看 MySQL 报错日志的位置是 D:\MySQL Server 5.7\Data 文件夹下, .err文件.

FOR UPDATE 手动锁表

select … for update 语句是我们经常使用手工加锁语句。在数据库中执行select … for update ,大家会发现会对数据库中的表或某些行数据进行锁表，在mysql中，如果查询条件带有主键，会锁行数据，如果没有，会锁表。

在事务中进行锁表操作

BEGIN;
SELECT * FROM test04 WHERE `code`=1 FOR UPDATE; -- 可以选择不加where条件

COMMIT; -- 一定要提交之后,才会解锁.
END

SQL 行转列, 用 case when

mysql> select * from numbers;
+------+------+------+
| n1   | n2   | n3   |
+------+------+------+
|    1 |    1 |    1 |
|    2 |    2 |    2 |
|    3 |    3 |    3 |
+------+------+------+
3 rows in set (0.00 sec)

mysql> SELECT 
    -> CASE n1 WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 1 END AS n1 ,
    -> CASE n2 WHEN 1 THEN 2 WHEN 2 THEN 2 WHEN 3 THEN 2 END AS n2,
    -> CASE n3 WHEN 1 THEN 3 WHEN 2 THEN 3 WHEN 3 THEN 3 END AS n3
    -> FROM numbers;
+------+------+------+
| n1   | n2   | n3   |
+------+------+------+
|    1 |    2 |    3 |
|    1 |    2 |    3 |
|    1 |    2 |    3 |
+------+------+------+
3 rows in set (0.00 sec)

SQL 列转行, 用 union / union all

本质就是将每一列数据分散成一条数据显示出来

mysql> select * from numbers;
+------+------+------+
| n1   | n2   | n3   |
+------+------+------+
|    1 |    2 |    3 |
|    1 |    2 |    3 |
|    1 |    2 |    3 |
+------+------+------+
3 rows in set (0.00 sec)


mysql> SELECT n1, n1 AS n2, n1 AS n3 FROM numbers
    -> UNION 
    -> SELECT n2 AS n1, n2, n2 AS n3 FROM numbers
    -> UNION 
    -> SELECT n3 AS n1, n3 AS n2, n3 FROM numbers;
+------+------+------+
| n1   | n2   | n3   |
+------+------+------+
|    1 |    1 |    1 |
|    2 |    2 |    2 |
|    3 |    3 |    3 |
+------+------+------+
3 rows in set (0.00 sec)

删除emp_no重复的记录，只保留最小的id对应的记录。

DELETE FROM titles_test WHERE id NOT IN (
SELECT * FROM (
SELECT MIN(id) FROM titles_test GROUP BY emp_no) AS aa); -- 把得出的表重命名那就不是原表了

-- MySQL中不允许在子查询的同时删除表数据（不能一边查一边把查的表删了)

聚合函数 group_concat（）

题目描述：按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees。

知识点总结：group_concat（）函数将group by产生的同一个分组中的值连接起来，返回一个字符串结果。

语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )
通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。

注意：当数据太大，group_concat超出了默认值1024，超过就会截断，group_concat查询出来的数据就会不全。

使用子查询的方法实现rank排序

SELECT a.id,a.number,
(SELECT COUNT(DISTINCT b.number) FROM passing_number b WHERE b.number>=a.number ) `rank`
 FROM passing_number a 

group by 累计相加结果

SELECT s1.emp_no,s1.salary,
(SELECT SUM(s2.salary) FROM salaries AS s2 
 WHERE s1.emp_no>=s2.emp_no 
 AND to_date='9999-01-01') AS running_total
FROM salaries AS s1
WHERE s1.to_date='9999-01-01'
GROUP BY s1.salary
ORDER BY s1.emp_no ASC;

分组排名取前几位,解决办法

-- 找出每个岗位分数排名前2名的用户，得到的结果先按照language的name升序排序，再按照积分降序排序，最后按照grade的id升序排序
SELECT g1.`id`,l.`name`,g1.`score` FROM grade AS g1
INNER JOIN LANGUAGE AS l ON g1.`language_id`=l.`id`
WHERE  (
  SELECT COUNT(DISTINCT g2.score) FROM grade AS g2 WHERE g1.`score`<=g2.`score` AND g1.`language_id`=g2.`language_id`
) <=2
ORDER BY l.`name`,g1.`score` DESC,g1.`id`;

Excel列值生成SQL语句

=CONCATENATE("REPLACE INTO goods_backup20211113 VALUES ('"&A2&"','"&B2&"',1,'"&D2&"','"&E2&"','"&F2&"','/static/nophoto.png','"&G2&"',1,0,0,now(),now());")