MYSQL数据库增删改查操作

SQL语言分类：DML DCL DDL
注释：# /…/

DDL

创建数据表：

CREATE TABLE 数据表名(
	列名 数据类型[约束][Comment 注释],
	...
)[Comment=注释]

修改表结构：

#添加字段
ALTER TABLE 表名
ADD 列名 数据类型[约束][Comment 注释],
...
;

#修改字段类型和约束
ALTER TABLE 表名
MODIFY 列名 数据类型[约束][Comment 注释],
...
;

#删除字段
ALTER TABLE 表名
DROP 列名,
...
;

字段约束：
PRIMARY KEY
NOT NULL
UNIQUE
FOREIGN KEY

数据库范式：
1、原子性（每一列都不可再拆分）
2、唯一性（每一列必须唯一，无重复数据）
3、关联性（每一列与主键有直接关系，无传递依赖）

索引：数据排序（二叉树）

#创建索引
CREATE TABLE 表名(
 index [索引名] (字段),
 ...
);

#添加索引
CREATE INDEX 索引名 on 表名(字段);
或
ALTER TABLE 表名 ADD INDEX [索引名](字段);

#查看索引
SHOW INDEX FROM 表名;

#删除索引
DROP INDEX 索引名 on 表名;

索引使用原则：
数据量大，经常被查询的表；
经常被用作检索条件的字段；
不要再大字段上创建索引。

简单查询：

#查询全部数据
SELECT * FROM 表名;
#使用列别名
AS
#分页查询
LIMIT 起始位置，偏移量
#结果集排序
ORDER BY 列名[ASC|DESC]
#多个字段排序
ORDER BY 列名1,列名2...
#去除重复记录
DISTINCT
#DISTINCT只能查询一列数据，多列去除重复失效
#DISTINCT只能在SELECT子句中使用一次，写在第一个字段前

条件查询：

WHERE 条件 [AND|OR] 条件...

IN()
IS NULL
IS NOT NULL
BETWEEN AND
LIKE “_A%”
REGEXP

ROUND()、FLOOR()、CEIL()
IFNULL(NULL,0)
DATEDIFF(NOW(),day)
LENGTH()

AND OR NOT XOR

WHERE子句：应该把索引条件或筛选掉记录最多的条件写在左侧
执行顺序：FROM -> WHERE->SELECT->ORDER BY->LIMIT

聚合函数：
AVG() 非数字数据统计结果为0
COUNT(*) 获取所有记录数
COUNT(列名) 获取包含非空值的记录数
MAX()
MIN()

聚合函数不能出现在WHERE子句中；
查询语句包含GROUP BY子句时，SELECT子句中可以包含聚合函数和GROUP BY子句的分组列，不能包含其他的。

WITH ROLLUP写在GROUP BY子句中对分组结果集再次做汇总计算
GROUP_CONCAT()把分组查询中的某个字段拼成一个字符串

HAVING用在GROUP BY后，和WHERE相似，用作查询条件，HAVING中可以有聚合函数，但聚合函数的比较条件只能和具体的值比较，不能和字段比较
GROUP BY 1 （依据SELECT子句中的第一列进行分组）

连接查询

表连接分为内连接和外连接
内连接只保留符合条件的记录
外连接保留全部记录

#内连接
SELECT XX FROM 表1 JOIN 表2 ON 连接条件；
#可以用WHERE子句
SELECT XX FROM 表1，表2 WHERE 连接条件；
#外连接
LEFT JOIN
RIGHT JOIN
#UNION关键字可以将多个查询语句的结果集合并
#外连接里，条件写在WHERE子句中，不符合条件的记录会被过滤掉

子查询：

可以写在WHERE子句、FROM子句、SELECT子句
分为单行和多行，多行只能出现在WHERE和FROM子句中
WHERE子句中处理多行子查询：IN、ALL、ANY、EXISTS

数据添加

INSERT INTO 表名(字段1,字段2,...)
VALUES(值1,值2,...),
...
;

#INSERT方言
INSERT [INTO] 表民 SET 字段1=值1,字段2=值2,...;

#IGNORE关键字
#只插入数据库中不存在的记录
INSERT [IGNORE] INTO 表名...;

数据更新

UPDATE [IGNORE] 表名
SET 字段1=值1,字段2=值2,...
[WHERE 条件1...]
[ORDER BY...]
[LIMIT...] 这里只能跟单个值

#UPDATE表连接
UPDATE 表1 JOIN 表2 ON 条件
SET 字段1=值1,字段2=值2,...;
或
UPDATE 表1,表2
SET 字段1=值1,字段2=值2,...
WHERE 连接条件;
#外连接
UPDATE 表1 [LEFT|RIGHT] JOIN 表2 ON 条件
SET 字段1=值1,字段2=值2,...;

数据删除

DELETE [IGNORE] FROM 表名
[WHERE 条件1,条件2,...]
[ORDER BY ...]
[LIMIT ...];
#DELETE表连接：可以删除多张表的数据
DELETE 表1,...
FROM 表1 JOIN 表2 ON 条件
[WHERE 条件1,条件2,...]
[ORDER BY ...]
[LIMIT ...];
#外连接
DELETE 表1,...
FROM 表1[LEFT|RIGHT]JOIN 表2 ON 条件...;

DELETE语句是在事务机制下删除记录，删除记录前先把要删除的记录保存在日志文件中，再删除记录。

#在事务机制之外删除记录，速度比DELETE删除快很多
TRUNCATE TABLE 表名；