SQL全名 Structured Query Language。
基本操作
CRUD增删改查
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
INSERT INTO user
VALUES(10, 'root', 'xxx@123.com');
-- 删除表中满足特定条件的行
DELETE FROM user
WHERE username='root';
-- 从表中删除满足特定条件的行，不指定表时会保留表的结构索引约束之类的，同时记录事务日志。
DROP TABLE IF EXISTS table_name;
-- 直接释放表所占用的数据页，且不能用WHERE子句指定删除条件。同样保留表的结构索引约束，但不会记录每一行的删除操作。
TRUNCATE TABLE table_name;
UPDATE user
SET username='root'
WHERE username='robot';
SELECT prod_name
FROM products;
子查询
子查询和连接的区别
子查询也可以成为内部查询，属于嵌套在较大查询中的SQL查询。
1
2
3
4
5
6
SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
FROM orders
WHERE prod_id='RGAN01'
)
IN操作符在WHERE子句中使用，作用是几个特定值中任选一个值。
BETWEEN操作符WHERE子句中使用，作用是选取介于某个范围内的值。
AND，OR，NOT
LIKE 正则表达式
语法
连接：可以从多个表中获取相关的数据。
子查询：嵌套在其它SQL语句中的查询。
连接可以替换子查询，并且比子查询的效率一般更快。
原因：
- 减少查询次数。子查询会
- 减少中间数据传输。
JOIN
内连接 （inner join/join）
外连接（左外连接left join，右外连接right join，全外连接full join）
交叉连接（cross join）
内连接（innner join，或称等值连接）：返回两张表中匹配的记录
左连接（left join）：返回两张表匹配的记录，以及左表中多余的记录
右连接（right join）：返回两张表匹配的记录，以及右表中多余的记录
全连接（full join）：返回两张表匹配的记录，以及左右两表中各自多余的记录
交叉连接（CROSS JOIN）：将一个表中的每一行和另一个表中的每一行进行结合，最终得到的结果集行数是两个表行数的乘积。
自然连接（natural join）：自动连接所有同名列
1
2
3
4
5
6
7
8
9
10
SELECT
up.university, qd.difficult_level,
COUNT(qpd.question_id)/COUNT(distinct qpd.device_id) AS avg_answer_cnt
FROM
question_detail AS qd
inner join question_practice_detail AS qpd
inner join user_profile AS up
ON
up.device_id=qpd.device_id AND qpd.question_id=qd.question_id
GROUP BY university, difficult_level
UNION
组合查询：列数和列顺序相同；列数据类型相同或兼容。
合并多个select语句的结果，会去除结果集中的重复行
union all，只是简单的合并结果
1
2
3
4
5
6
7
8
9
10
-- 查找山东大学或者性别为男性的用户
SELECT device_id, gender, age, gpa
FROM user_profile
WHERE university='山东大学'
union all
SELECT device_id, gender, age, gpa
FROM user_profile
WHERE gender='male'
函数
AVG，COUNT，MAX，MIN，SUM
1
2
3
SELECT SUBSTRING_INDEX(profile, ',', -1)gender, COUNT(*)number
FROM user_submit
GROUP BY gender
排序和分组
ORDER BY（DESC，ASC）
GROUP BY（先分组后排序）
HAVING （对汇总的GROUP BY结果进行过滤，要求存在一个GROUPBY子句）
HAVING适用于汇总的组记录；而 WHERE 适用于单个记录。
1
2
3
4
-- 多重排序
SELECT device_id, gpa, age
FROM user_profile
ORDER BY gpa ASC, age ASC
1
2
3
4
5
6
7
SELECT
university,
AVG(question_cnt) AS avg_question_cnt,
AVG(answer_cnt) AS avg_answer_cnt
FROM user_profile
GROUP BY university
HAVING avg_question_cnt<5 OR avg_answer_cnt<20
limit
limit 5 offset 10
limit 10，5
从第10条开始，返回之后的五条
case函数
SELECT case when age>12 then ‘>12age’ end age12
1
2
3
4
5
SELECT CASE WHEN age<25 OR age IS NULL THEN '25岁以下'
WHEN age>=25 THEN '25岁及以上'
END age_cnt, COUNT(*) as number
FROM user_profile
GROUP BY age_cnt
约束
NOT NULL
UNIQUE
PRIMARY KEY（NOT NULL和UNIQUE的结合）
FOREIGN KEY
CHECK
DEFAULT
索引
一个表可以没有索引。
索引（Index）是帮助MySQL高效获取数据的数据结构，索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
基础分类：主键索引，唯一索引，普通索引，全文索引，组合索引
-
普通索引：找到满足条件的第一个记录之后，会查找下一个记录，直到不满足要求。
-
唯一索引：查找到第一个满足条件的记录后，就会停止继续检索。
- （普通索引和唯一索引性能差距微乎其微）
- 主键索引（unique，notnull）
- 全文索引：全文索引是一种专门用于文本搜索的索引类型，它可以在文本列中快速查找包含特定关键词的记录。
- 组合索引：组合索引是基于多个列创建的索引，它将多个列的值组合在一起作为索引键。
索引存储的数据结构：B+树，哈希表，有序数组
日志
- binlog：数据备份和主从复制
- redo log：持久性。用于崩溃掉电等事务恢复
- undo log：原子性。用于事务回滚和MVCC
