MySQL小知识点整理

绪论

本篇文章主要整理MySQL相关的小知识点，不定时更新。

正文

// 查看数据库版本
select version();
// 查看当前时间
select now();
// 查看当前用户
select user();
// 创建数据库
create database my_time;
// 查看数据库创建语句
show create database my_time;
// 修改数据库编码
alter database my_time character set = gbk;
// 删除数据库
drop database my_time;
// 查看当前使用的数据库
select database();
// 查询符合条件的所有表名
show tables like 'z_%';
// 查表结构
show columns from my_time;
desc my_time;
// 查看表创建语句
show create table my_time;
// 查看索引
show indexes from my_time\G

// 新增一个非空，默认值为10的列
ALTER TABLE my_time ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10 COMMENT '年龄';
// 删除列，多个用逗号分隔
ALTER TABLE my_time DROP age;
// 新增主键
ALTER TABLE my_time ADD CONSTRAINT PK_TIME_ID PRIMARY KEY (id);
// 删除主键
ALTER TABLE my_time DROP PRIMARY KEY;
// 新增唯一性约束
ALTER TABLE my_time ADD UNIQUE (start_time);
// 删除唯一性约束
ALTER TABLE my_time DROP INDEX start_time;
// 修改列默认值
ALTER TABLE my_time ALTER if_work SET DEFAULT 10;
// 修改列定义，当大数据类型修改为小数据类型，可能造成数据丢失
ALTER TABLE my_time MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;
// 修改列名称和类型
ALTER TABLE my_time CHANGE id p_id TINYINT UNSIGNED NOT NULL FIRST;
// 修改表名
ALTER TABLE my_time RENAME TO mytime;
RENAME TABLE mytime TO my_time;

插入语句

INSERT INTO my_time VALUES (1, '123', 1, 8, 1);
INSERT INTO my_time SET p_id = 3, start_time = 'xxx';
INSERT INTO my_time (p_id, start_time) SELECT 5, 'xxxx' FROM DUAL;

更新语句

UPDATE my_time SET if_work = 5 WHERE p_id = 5;
UPDATE my_time t1 INNER JOIN ( SELECT p_id, 30 AS 'if_work' FROM my_time WHERE p_id > 1 ) t2 ON t1.p_id = t2.p_id SET t1.if_work = t2.if_work;

删除语句

DELETE FROM my_time WHERE p_id = 5;

子查询与连接

// 内连接
在MySQL中join、cross join和inner join是一样的，仅展示符合条件的数据。
// 左外连接
left join：左表全部数据，右表缺失部分用null补充。
// 右外连接
right join：右表全部数据，左表缺失部分用null补充。

字符函数（中间有null，结果为null）

// 字符串拼接
SELECT CONCAT('A', '-', 'B', 'C');		A-BC
SELECT CONCAT_WS('-', 'A', 'B', 'C');		A-B-C
// 大写转小写
SELECT LOWER('mySQL');		mysql
// 小写转大写
SELECT UPPER('mySQL');		MYSQL
// 获取左侧两个字符
SELECT LEFT ('MYSQL', 2);		MY
// 获取右侧两个字符
SELECT RIGHT ('MYSQL', 2);		QL
// 获取字符串长度
SELECT LENGTH('MYSQL');		5
// 删除左侧空格
SELECT LTRIM('  MYSQL');		MYSQL
// 删除右侧空格
SELECT LTRIM('MYSQL  ');		MYSQL
// 删除两侧空格
SELECT TRIM('  MYSQL  ');		MYSQL
SELECT TRIM(LEADING '?' FROM '??MySQL???');		MySQL???
SELECT TRIM(TRAILING '?' FROM '??MySQL???' );	??MySQL
SELECT TRIM(BOTH '?' FROM '??MySQL???' );		MySQL
// 字符串截取，从1开始，后面是长度，不写则获取剩余全部。负数从后面截取
SELECT SUBSTRING('MYSQL', 1, 3);		MYS
SELECT SUBSTRING('MYSQL', 2);		YSQL
SELECT SUBSTRING('MYSQL', -2);		QL
// 字符串替换
SELECT REPLACE('??MYSQL???', '?', '');		MYSQL

数值运算函数

// 向上取整
SELECT CEIL(-3.01);		-3
向下取整
SELECT FLOOR(-3.01);	-4
// 除法
SELECT 3 DIV 4;		0
SELECT 3/4;			0.75
// 取余
SELECT 5 MOD 3;		2
SELECT MOD(5, 3);	2
SELECT 5 % 3;		2
// 幂运算
SELECT POWER(3, 2);		9
// 四舍五入
SELECT ROUND(3.55, 1);		3.6
SELECT FORMAT(3.55, 1);		3.6
// 数字截取，不做四舍五入
SELECT TRUNCATE(3.55, 1);	3.5

日期时间函数

// 当前日期
SELECT CURDATE();		2019-08-08
// 当前时间
SELECT CURTIME();		19:28:49
// 时间累加
SELECT DATE_ADD( '2019-08-08', INTERVAL 365 DAY );		2020-08-07
SELECT DATE_ADD( '2019-08-08', INTERVAL -365 DAY );		2018-08-08
SELECT DATE_ADD('2019-08-08', INTERVAL 1 YEAR);			2020-08-08
SELECT DATE_ADD('2019-08-08', INTERVAL 1 WEEK);			2019-08-15
// 日期差值
SELECT DATEDIFF('2019-08-08', '2019-08-01');		7
// 日期格式化
SELECT DATE_FORMAT('2019-08-08', '%Y_%m_%d %H:%i:%S');		2019_08_08 00:00:00

聚合函数

平均值：AVG()
计数：COUNT()
最大值：MAX()
最小值：MIN()
求和：SUM()

加密函数

// 信息摘要算法
SELECT MD5('123456');		e10adc3949ba59abbe56e057f20f883e
// 密码算法
SELECT PASSWORD('123456');		*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9

定义

• 并发控制：当多个连接对记录进行修改时保证数据的一致性和完整性。
• 共享锁(读锁)：在同一时间段内，多个用户可以读取同一个资源，读取过程中数据不会发生任何变化。
• 排它锁(写锁)：在任何时候只能有一个用户写入资源，当进行写锁时会阻塞其他的读锁或写锁操作。
• 锁颗粒：表锁：是一种开销最小的锁策略；行锁：是一种开销最大的锁策略（每行都可能有）。
• 事务：保证数据库的完整性特性：原子性、一致性、隔离性、持久性。
• 外键：保证数据的一致性。
• 索引： 是对数据表中一列或多列的值进行排序的一种结构（普通索引、唯一索引、全文索引、btree索引、hash索引）。

（若有什么错误，请留言指正，3Q）