mysql常用SQL命令总结笔记二（超详细）

这一份笔记重点是对表中数据的操作（DML），即我们所熟知的增删查改的详细命令，以及一些常见的系统函数总结。对数据库和数据表相关操作见mysql常用SQL命令总结笔记一

增删改

一．插入数据
1.INSERT 表名 [字段名] VALUES （和字段名对应的数值）；
2. 一次插入多条记录时，VALUES后面多个括号并列即可；
3. INSERT 表名 SET 字段名=值；
4. 将表1查询结果插入到表2中：INSERT 表2名 SELECT 字段名 FROM 表1名；
二.更新数据
UPDATE 表名 SET 字段名=值，… [WHERE 条件] [ORDER BY 字段名][LIMIT 限制条数]；
三.删除数据
DELETE FROM 表名 [WHERE 条件] [ORDER BY 字段名][LIMIT 限制条数]；
彻底清空数据表：TRUNCATE TABLE 表名；（自增长同时从1开始）

查

完整语句：
SELECT 字段名
[
FROM 表名
[WHERE 条件]
[GROUP BY {col_name|position}][ASC|DESC],…
[HAVING 条件 对分组结果二次筛选]
[ORDER BY ][ORDER BY{ col_name|position}][ASC|DESC],…
[LIMIT 限制显示条数]
]
带条件语句查询：

• WHWERE 条件：
  1.比较 = != <=> < > >=等
  2.指定范围：BETWEEN AND 、NOT BETWEEN AND
  例：Id取3~10：WHERE id BETWEEN 3 AND 10
  3.指定集合：IN、NOT IN
  4.匹配字符（模糊查询）：LIKE、NOT LIKE
  其中
  %：代表0个1个或多个任意字符 例：姓张的用户WHERE user_name LIKE ’张%’
  下划线_：代表1个任意字符 例：用户名为3为 WHERE user_name LIKE ’___’
  5.是否为空值：IS NULL、IS NOT NULL
  6.多个查询条件：AND、OR

• 分组查询GROUP BY 配合聚合函数：
  聚合函数：CONCAT、MAX、MIN、AVG、SUM
  (最好AS为另一个组名)
  1.得到组中某个字段的详情：GROUP_CONCAT
  例：查询id,sex,用户名详情，并按照性别分组
  SELECT id,sex GROUP_CONCAT(username) FROM 表名 GROUP BY sex；
  2.对表中某组计数
  SELECT COUNT (*) AS 新表名 FROM 表名
  3.最后配合WITH ROLLUP，会发现运行结果多了一行NULL，是对分组以后的结果进行汇总

• HAVING 子句
  对分组后结果进行二次筛选

• ORDER BY
  默认升序ASC，降序为DESC
  (ORDER BY 字段名 ASC/DESC)
  随机提取记录：ORDER BY RAND()

• LIMIT
  LIMIT 偏移量（起始点），显示条数
  更新和删除时，只能用单参数，表示前几条

连接查询：
将两个或两个以上的表连接起来，从中查询需要的数据，即找到表和表之间的关系。

1. 内连接查询：
   找到两个表之间符合连接条件的记录
   例：查询user表中的id,name,sex,emil以及对应省份，其中省份id对应的省份名通过查询province表的proname来得到
   SELECT u.id ,u.name ,u.sex ,u.emil
   FROM user AS U
   JOIN provinces AS P
   ON u.proId=p.id;（连接条件）
2. 外连接查询：
   左外连接（显示左表中全部记录加右表中部分记录，LEFT前面的是左表）
   右外连接（显示左表中全部记录加右表中部分记录，以右表为准）

外键
目的：保证数据的一致性和完整性，被参照的是主表，外键所在字段为子表，父表和子表都为InnoDB，外键列和参照列必须创建索引。
打个比方：若想删除主表（部门表），必须先把子表（部门里的员工）删掉

外键约束
目的：无法插入垃圾信息 会互相约束
参照条件：FOREIGN KEY (子表信息) REFERENCES (主表信息)

CASCADE：从父表中删除或更新，且自动删除或更新与子表关联的行
ON DELETE CASCADE/ON UPDATE CASCADE

指定外键名称：
CONSTRAINT 外键名称（取名规则：从表_fk_主表）
删除外键：
ALTER TABLE 表名 DROP FOREIGN KEY外键名称 ;
添加外键：
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY () REFERENCES ()
RESTRICT：拒绝对父表的删除或更新操作

联合查询：
两个表中相同项结合到一起
UNION:合并同时去掉重复项
UNION ALL:简单合并
例：SELECT user FROM 表1 UNION SELECT user FROM 表2；

子查询：
将一个查询语句嵌套在另一个查询语句中，内层查询语句的查询结果可以为外层查询语句提供条件。

1. 由IN/NOT IN 引发的子查询
   SELECT id FROM department
   SELECT id，username FROM 表1 WHERE depid IN (1,2,3,4)
   结合后：SELECT id，username FROM 表1 WHERE depid IN (SELECT id FROM department);
2. 比较运算符的子查询 > < >= <=
3. 使用EXISTS/NOT EXISTS的子查询
4. 使用ANY/SOME或者ALL的子查询
   SELECT * FROM * WHERE score<ANY/ALL(另一条件);
   
   将查询结果同时写入数据表中：
   INSERT 表（字段名）SELECT 字段名 FROM 表；----运行时先执行后面的SELECT语句
   创建表时同时将查询结果写入数据表中：
   CREATE TABLE 表名 (…) SELECT 字段名 FROM 表；

正则表达式的查询：REGEXP

常见系统函数

• 数学函数
  
  SELECT FLOOR(num1) FROM …
• 字符串函数库
  
  
• 日期时间函数
  
• 条件判断函数
  
• 加密函数
  
• 其他函数
  

---