存储引擎
MySQL 可以将数据以不同的技术存储在文件（内存）中，这种技术就称为存储引擎。
每种存储引擎使用不同的存储机制/索引技巧/锁定水平。
索引 是对数据表中一列或多列的值进行排序的一种结构。
常见引擎
- MyISAM
- InnoDB
- Memory
- CSV
- Archive
- BlackHole
各种存储引擎的特点
| 特点 | MyISAM | InnoDB | Memory | Archive |
|---|---|---|---|---|
| 存储限制 | 256TB | 64TB | 有 | 无 |
| 事务 | - | 支持 | - | - |
| 索引 | 支持 | 支持 | 支持 | - |
| 锁颗粒 | 表锁 | 行锁 | 表锁 | 行锁 |
| 压缩 | 支持 | - | - | 支持 |
| 外键 | - | 支持 | - | - |
锁
- 共享锁（读锁）
- 排他锁（写锁）
锁颗粒
- 表锁，一种开销最小的锁策略
- 行锁，一种开销最大的锁策略
配置
修改存储引擎
- 修改mysql.ini，
default-storage-engine = engine - 创建数据表时通过
ENGINE = engine指定，或ALTER TABLE tp1 ENGINE = engine
事务
原子性/ 一致性/ 隔离性/ 持久性 简称ACID
用于保证数据库的完整性 （常见的银行转账逻辑）
查看表创建语句
SHOW CREATE TBALE xxx
函数
- 字符函数
-- CONCAT/CONCAT_WS 字符串连接
-- FORMAT 格式化
-- LOWER/UPPER 大小写
-- LEFT/RIGHT(str,len) 字符串左右截取
-- SUBSTRING(str, start, [len]) 字符串截取，索引从1开始
-- LENGTH 字符串长度
-- LTRIM/RTRIM 删除左/右前/后导空格 LEADING 前导，TRAILING 后序，BOTH 前后空格
SELECT TRIM(LEADING '?' FROM '??MySQL???'); -- MySQL???
-- LIKE 模糊查询 匹配带%字段时候 1后面表示不在是通配符
SELECT * FROM username WHERE name LIKE '%1%%' ESCAPE '1';
SELECT * FROM username WHERE name LIKE '%\%%';
-- REPLACE 替换
SELECT REPLACE('??My??SQL???', '??', '!'); -- !My!SQL!?
- 数值运算符与函数
-- CEIL 向上取整
-- DIV 除法取整
-- FLOOR 向下取整
-- MOD 取余
-- POWER 幂运算
-- ROUND 四舍五入
-- TRUNCATE 数字截取
SELECT TRUNCATE(123.89, -1) -- 120
- 比较运算符与函数
[NOT] BETWEEN ... AND ... 在范围内
[NOT] IN() 在给出的范围内
IS [NOT] NULL 为空
- 日期时间函数
-- NOW() 当前日期和时间
-- CURDATE/CURTIME 当前日期/当前时间
-- DATE_ADD() 日期变化
SELECT DATE_ADD('2017-4-1', INTERVAL -365 DAY);
-- DATEDIFF() 日期差值
SELECT DATE_ADD('2017-4-1', '2017-5-1');
-- DATE_FORMAT() 日期格式化
SELECT DATE_FORMAT('2017-4-1', '%m/%d/%Y');
-- UNIX_TIMESTAMP() date类型转换为timestamp形式整数
-- FROM_UNIXTIME() 跟上面相反
- 信息函数
-- CONNECTION_ID() 连接ID
-- DATEBASE() 当前数据库
-- LAST_INSERT_ID 最后插入的数据的id，多条插入只返回第一条最后操作id
-- USER() 当前登陆用户
-- VERSION() mysql版本号
- 聚合函数
-- AVG 平均值
-- COUNT 计数
-- MIN/MAX 最小/大值
-- SUM 求和
- 加密函数
-- MD5() 信息摘要算法, 常用于保存密码
-- PASSWORD() 修改用户密码
SET PASSWORD=PASSWORD('xxxxx');
自定义函数
-- 不带参数
CREATE FUNCTION fun1() RETURNS VARCHAR(30) RETURN DATE_FORMAT(NOW(), '%Y年%m月%d日 %H点%i分%s秒');
SELECT fun1();
-- 带参数函数
CREATE FUNCTION fun2(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED) RETURNS FLOAT(10, 2) UNSIGNED RETURN (num1 + num2)/2;
SELECT fun2(5, 6);
-- 符合结构, 多个参数
CREATE FUNCTION adduser(username VARCHAR(20)) RETURNS INT UNSIGNED RETURN INSERT test(username) VALUES(username) RETURN LAST_INSERT_ID();END;
-- 删除函数
DROP FUNCTION xxx;
EXPLAIN 性能分析语句
- type = const 表示通过索引一次就找到了；
- key = primary 的话，表示使用了主键；
- key = null 表示没用到索引。
- type = all 表示为全表扫描；
- type = ref 因为这时认为是多个匹配行，在联合查询中，一般为REF
比较查询
SELECT round(AVG(goods_price),2) FROM `tdb_goods`
SELECT goods_id,goods_name,goods_price from tdb_goods where goods_price >= 5391.30;
-- 精简为
SELECT goods_id,goods_name,goods_price from `tdb_goods` WHERE goods_price >= (SELECT ROUND(AVG(goods_price), 2) FROM `tdb_goods`);
ANY SOME 满足其中一个就行
ALL 满足所有结果
比如 goods_price 子查询结果最低 2899
SELECT goods_id,goods_name,goods_price from tdb_goods where goods_price >= ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate='台式机');
其他 [NOT] IN，[NOT] EXISTS
-- 查询分类个数
SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
-- 将查询的结果存储到表中
INSERT INTO tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
多表查询
更新
UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id;
查询并新建表，一步到位
CREATE TABLE IF NOT EXISTS tdb_goods_brands(
brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
brand_name VARCHAR(40) NOT NULL
) SELECT brand_name FROM tdb_goods GROUP BY brand_name;
UPDATE tdb_goods INNER JOIN tdb_goods_brands ON tdb_goods.brand_name=tdb_goods_brands.brand_name SET tdb_goods.brand_name=tdb_goods_brands.brand_id;
-- 修改表字段名和类型
AlTER TABLE tdb_goods CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;
连接
ON后接条件，而 WHERE 一般用于结果集的过滤
- 内连接 INNER JOIN （JOIN / CROSS JOIN）
仅显示左表及右表符合连接条件的记录
SELECT
goods_id,
goods_name,
cate_name
FROM
tdb_goods
INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;
- 外连接
左外连接 LEFT JOIN
左表全部，右表符合条件的部分
右外连接 LEFT JOIN
右表全部，左表符合条件的部分
- 多表连接
SELECT
goods_id,
goods_name,
cate_name,
brand_name,
goods_price
FROM
tdb_goods AS g
INNER JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id
INNER JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id
无限分类逻辑
一般是这样的结构： 分类id 分类名字 父类id
CREATE TABLE tdb_goods_types(
type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
type_name VARCHAR(20) NOT NULL,
parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
);
-- 插入数据
INSERT tdb_goods_types(type_name,parent_id) VALUES('家用电器',DEFAULT);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑、办公',DEFAULT);
INSERT tdb_goods_types(type_name,parent_id) VALUES('大家电',1);
INSERT tdb_goods_types(type_name,parent_id) VALUES('生活电器',1);
INSERT tdb_goods_types(type_name,parent_id) VALUES('平板电视',3);
INSERT tdb_goods_types(type_name,parent_id) VALUES('空调',3);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电风扇',4);
INSERT tdb_goods_types(type_name,parent_id) VALUES('饮水机',4);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑整机',2);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑配件',2);
INSERT tdb_goods_types(type_name,parent_id) VALUES('笔记本',9);
INSERT tdb_goods_types(type_name,parent_id) VALUES('超级本',9);
INSERT tdb_goods_types(type_name,parent_id) VALUES('游戏本',9);
INSERT tdb_goods_types(type_name,parent_id) VALUES('CPU',10);
INSERT tdb_goods_types(type_name,parent_id) VALUES('主机',10);
-- 自连接，数据表自己连接自己
-- 查找所有分类及其父类（以子类为基础找父类）
SELECT s.type_id,s.type_name,p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p ON s.parent_id = p.type_id;
-- 查找所有分类及其子类（以父类为基础找子类）
SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id;
-- 查找所有分类及其子类的数目
SELECT p.type_id,p.type_name,count(s.type_name) AS children_count FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id;
面试题：
CREATE TABLE IF NOT EXISTS province(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
province VARCHAR(32) NOT NULL
);
INSERT province(id, province) VALUES (NULL,"广东"),(NULL,"湖南"),(NULL,"湖北");
CREATE TABLE IF NOT EXISTS city(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
city VARCHAR(32) NOT NULL,
province_id SMALLINT UNSIGNED NOT NULL
);
INSERT city(id, city, province_id) VALUES (NULL,"广州",1),(NULL,"深圳",1),(NULL,"惠州",1),(NULL,"长沙",2),(NULL,"武汉",3),(NULL,"黄冈",3);
-- 1. 写一条sql语句关联两个表，实现：显示城市基本信息，显示字段：城市id，城市名，所属省份
SELECT
c.id AS '城市Id',
c.city AS '城市名',
p.province AS '所属省份'
FROM
city AS c
LEFT JOIN province AS p ON c.province_id = p.id;
-- 2. 统计每个省会有多少个城市，从多到少。
SELECT
p.province AS '省会名',
count(c.city) AS '城市数'
FROM
province AS p
LEFT JOIN city AS c ON c.province_id = p.id
GROUP BY
p.province
ORDER BY
count(c.city) DESC;
多表删除
-- 分组查看可以看到重复的数据
SELECT goods_id, goods_name FROM tdb_goods GROUP BY goods_name HAVING COUNT(goods_name) > 1 ORDER BY goods_id;
-- 查询重复的数据
SELECT t1.goods_id, t1.goods_name
FROM tdb_goods AS t1
LEFT JOIN (
SELECT goods_id, goods_name ROM tdb_goods
GROUP BY goods_name
HAVING COUNT(goods_name) > 1
) AS t2 ON t1.goods_name = t2.goods_name
WHERE t1.goods_id > t2.goods_id;
存储过程
存储过程 是SQL语句和控制语句的预编译集合，以一个名称存储并最为一个单元处理。
创建存储过程
CREATE ... PROCEDURE 过程名() 过程体;
调用存储过程
CALL 过程名
查看状态
SHOW STATUS
查看存储过程内容
SHOW CREATE {PROCEDURE|FUNCTION} sp_name
- 增强了SQL语句功能和灵活性
- 实现较快的执行速度
- 减少网络流量
注意: 在命令行创建存储过程的是hi需要通过DELIMITER 语句修改定界符。
参数
- IN 表示该参数的值必须在调用存储过程时指定
- OUT 表示该参数的值可以被存储过程改变，且可以返回，类似php方法的引用参数
- INOUT 表示该参数的调用时制定，且可以被改变和返回
过程体
过程提由合法的SQL语句构成
可以是任意SQL语句（不包括创建表）
如果是复合机构，需使用BEGIN ... END语句
复合结构可以包含声明，循环，控制结构
-- 数据准备
CREATE TABLE users(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(32) NOT NULL,
password VARCHAR(32) NOT NULL,
age SMALLINT UNSIGNED DEFAULT 0,
sex SMALLINT UNSIGNED DEFAULT 0
);
insert users(username,password,age,sex) values('A',md5('A'),20,0);
insert users(username,password,age,sex) values('B',md5('B'),23,1);
insert users(username,password,age,sex) values('C',md5('C'),23,1);
insert users(username,password,age,sex) values('D',md5('D'),24,1);
insert users(username,password,age,sex) values('E',md5('E'),24,0);
insert users(username,password,age,sex) values('F',md5('F'),23,0);
insert users(username,password,age,sex) values('G',md5('G'),22,0);
insert users(username,password,age,sex) values('H',md5('H'),23,0);
insert users(username,password,age,sex) values('I',md5('I'),23,0);
insert users(username,password,age,sex) values('J',md5('J'),22,1);
insert users(username,password,age,sex) values('K',md5('K'),22,1);
insert users(username,password,age,sex) values('L',md5('L'),22,0);
insert users(username,password,age,sex) values('M',md5('M'),24,1);
insert users(username,password,age,sex) values('N',md5('N'),21,0);
insert users(username,password,age,sex) values('O',md5('O'),20,0);
insert users(username,password,age,sex) values('P',md5('P'),20,1);
insert users(username,password,age,sex) values('Q',md5('Q'),24,1);
insert users(username,password,age,sex) values('R',md5('R'),24,1);
-- 不带参数
CREATE PROCEDURE p1() SELECT VERSION();
CALL p1;
-- 带参数 根据id删除数据，注意参数避免与字段名重复！
CREATE PROCEDURE removeUser(IN removeId INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = removeId;
END
CALL removeUser(10); -- 成功
-- 删除并返回影响的行数和当前数据条数
DROP PROCEDURE IF EXISTS removeUser2;
CREATE PROCEDURE removeUser2(IN removeId SMALLINT UNSIGNED, OUT affected SMALLINT UNSIGNED, OUT num SMALLINT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = removeId;
SELECT ROW_COUNT() INTO affected;
SELECT COUNT(id) FROM users INTO num;
END
CALL removeUser2(12, @affected, @num);
SELECT @affected, @num;
存储过程和自定义函数的区别
- 存储过程适合更复杂的功能，而函数的针对性更强。
- 存储过程可返回多个值，而函数只有一个
- 存储过程一般独立执行，而函数可以作为其他SQL语句调用
