Mysql基本语句
1. 数据库(datebase)
保存有组织的数据的容器，简单理解为存放数据的仓库
2. 数据库管理系统(database manage system)
我们平常不是直接从数据库里面获取数据的，而是通过使用数据库管理系统来访问数据库从而获取数据的，这些软件称为DBMS（由于习惯我们平时所说的数据库指的就是数据库管理系统，容易被误导）
3. 表(table)
类似于文件夹，把数据分类放在同一个文件内，即放在同一个表中
4. 列和行(column and row)
表的结构类似于excel的表格，表列对应excel列，表行对应excel行
5. 数据类型(data type)
表中的每列(字段)都有自己的数据类型
# 常见数据类型
int
float
double
char
varchar
datetime
timestamp
TEXT
6. 三大范式
第一范式：每个字段都是最小的单元，不可再分
第二范式：满足第一范式，表中的字段必须完全依赖于全部主键而非部分主键
第三范式：满足第二范式，非主键外的所有字段必须互不依赖
7. 约束
NOT NULL: 非空
UNIQUE: 唯一性
PRIMARY KEY: 主键
FOREIGN KEY: 外键
CHECK: 控制字段的值范围
8. 超键、候选键、主键、外键
主键：唯一标识数据的单个或多个字段
外键：存在他表中的主键
候选键：可以作为主键但没有设为主键的单个或多个字段
超键：候选键的集合
9. 表操作
# 建表
CREATE TABLE [IF NOT EXISTS] `runoob_tbl`(
`runoob_id` INT UNSIGNED AUTO_INCREMENT,
`runoob_title` VARCHAR(100) NOT NULL,
`runoob_author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY ( `runoob_id` )
)ENGINE = InnoDB DEFAULT CHARSET = utf8;
# 删表
DROP TABLE [IF EXISTS] <表名>
# 清空
TRUNCATE TABLE <表名>
10. SELECT语句
基本查询
SELECT (字段1, 字段2, ...) FROM <表名>
条件查询
SELECT (字段1, 字段2, ...) FROM <表名> WHERE <条件表达式>
排序查询
SELECT (字段1, 字段2, ...) FROM <表名> ORDER BY 字段1 DESC, 字段2 ASC, ...
# 特定记录的排序
SELECT * FROM <表名> ORDER BY id = 9 DESC,字段1 DESC
子查询
# FROM 后子查询一定要取别名
SELECT * FROM (SELECT * FROM <表名>) AS <另取的表名> WHERE <条件表达式>
分页查询
SELECT (字段1, 字段2, ...) FROM <表名> LIMIT <M> OFFSET <N>
# 分页优化，查询第十万条后的100条数据
SELECT * FROM <表名> LIMIT 100 OFFSET 100000;
SELECT * FROM <表名> LIMIT 100000,100; (不包括第100000)
SELECT * FROM <表名> WHERE id >= (SELECT id FROM <表名> LIMIT 100000,1) limit 100;
分组查询
SELECT (字段1，聚合函数) FROM <表名>
WHERE <条件表达式>
GROUP BY 字段1
HAVING <条件表达式>
ORDER BY <条件表达式>
# WHERE，用于分组前，WHERE过滤的是行
# SELECT，字段只能加 GROUP BY 后面的字段和聚合函数
# HAVING，用于分组后，过滤组，且条件字段必须在前面查询存在
# 分组后的OrderBy只排序每组的第一个，不一定是正确的，应该排序后才分组
# 画重点，分组需要列出非GROUP 之后的字段
SELECT * FROM <表名> WHERE id IN （
SELECT max(id) FROM <表名> GROUP BY id,time
）
并 / 差 / 交集查询
SELECT (字段1, 字段2, ...) FROM <表名1>
UNION [ALL] / EXCEPT / INTERSECT
SELECT (字段1, 字段2, ...) FROM <表名2>
# 二表字段需一样
聚合查询
COUNT(字段)，SUM(),AVG(),MAX(),MIN()
# 若查询无结果，COUNT()返回0，而SUM()、AVG()、MAX()、MIN()返回NULL
连接查询
SELECT (表1.字段, 表2.字段2, ...) FROM <表1> XXX JOIN <表2> ON <表1>.column = <表2>.column
XXX: INNER / RIGHT OUTER / LEFT OUTER / FULL OUTER（Mysql不支持，所以可以用左连接 UNION 右连接）
-
内连接，只返回同时存在于两张表的行数据
-
外连接
-
左连接，返回右表都存在的行，左边不存在填充NULL
-
右连接，返回左表都存在的行，右边不存在填充NULL
-
全连接，把两张表的所有记录全部选择出来，自动把对方不存在的列填充为NULL
-
11. INSERT语句
# 插入或替换（根据主键来执行）
# 若存在该主键，删除原记录，插入一条新的、否则直接插入记录
REPLACE INTO <表名> （字段） VALUES （值）
# 插入或更新（根据主键来执行）
# 若存在该主键，更新记录、否则直接插入记录
# 返回0，1，2
INSERT INTO <表名> (字段1) VALUES (值) ON DUPLICATE KEY UPDATE `字段2` = "更新值"
# 插入或忽略
INSERT IGNORE INTO <表名> （字段） VALUES （值）
12. UPDATE
# 返回更新条数
UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE <条件表达式>
13. DELETE
# 返回条数
DELETE FROM <表名> WHERE <条件表达式>
# 跨表更新（两个表都可更新）
UPDATE <表名1> XXX JOIN <表名2> on <表名1>.a = <表名2>.b SET <表名1>.x = 0,<表名2>.x=0 WHERE <条件表达式>
14. 视图
# 视图是一种虚表，建立在原来的表上，其本质是查询语句，不会增加查询效率
# 简化查询
# 权限限制，安全性
CREATE VIEW <视图名> SELECT (字段1, 字段2, ...) FROM <表名> WHERE <条件表达式>
## 15. 名词
- DML（data manipulation language）数据操作语言：select、update
- DDL（data definition language）数据定义语言：create、alter、drop
- DCL（data control language）数据控制语言：grant、revoke
16. 其他
- 判断null：ISNULL
- 替换：IFNULL(
字段，替换的值)，若为null则替换成给定值 - delete使用别名的时候，要在delete和from间加上删除表的别名，这样才是正确的写法
17. 最近的计算
TO_DAY：返回格林威治至今的天数
DATE_SUB：日期减去指定时间
DATE_FORMAT(date,format)：格式化显示日期
PERIOD_DIFF("YYMM","YYMM")：返回两个年月之间的月份差
QUARTER(date)：返回给定日期对应的季度，春夏秋冬1234
YEAR(date)：返回年份
YEARWEEK：第几周
# 今天
SELECT * FROM `表名` WHERE TO_DAYS(create_time) = TO_DAYS(now());
# 昨天
SELECT * FROM `表名` WHERE TO_DAYS(now()) - TO_DAYS(create_time) = 1;
# 近7天
# 本质是大于7天前的包括未来，应再限定今天以内
SELECT * FROM `表名` WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(create_time);
# 本月
# DATE_SUB这个函数只能显示近30天，而28，29，31这种无法具体显示
SELECT * FROM `表名` WHERE DATE_FORMAT(create_time,'%Y%m') = DATE_FORMAT(CURDATE( ),'%Y%m');
# 上一月
SELECT * FROM `表名` WHERE
PERIOD_DIFF(date_format(now( ),'%Y%m'),date_format(create_time, '%Y%m')) = 1;
# 本季度
SELECT * FROM `表名` WHERE QUARTER(create_date) = QUARTER(now());
# 上季度
SELECT * FROM `表名` WHERE QUARTER(create_date) = QUARTER(DATE_SUB(now(),interval 1 QUARTER));
# 本年
SELECT * FROM `表名` WHERE YEAR(create_date) = YEAR(now());
# 去年
SELECT * FROM `表名` WHERE YEAR(create_date) = YEAR(date_sub(now(),interval 1 year));
# 本周
SELECT * FROM `表名` WHERE YEARWEEK(date_format(create_time,'%Y-%m-%d')) = YEARWEEK(now());
# 上周
SELECT * FROM `表名` WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now()) - 1;
