学习笔记 -- MySQL常用语句(二)

MySQL系列文章目录

1. MySQL的介绍和基本使用(一)
2. MySQL常用语句(二)
3. MySQL常用函数(三)
4. MySQL事务机制(一)

---

---

前言

• 上一章讲解了MySQL的基本使用和配置文件详情

一、MySQL语言分类

• DDL：逻辑库、数据表、视图、索引
• DML：添加、修改、删除、查询
• DCL：用户、事务、权限

二、MySQL语句注意事项

• SQL语句不区分大小写，但是字符串区分大小写；建议SQL语言使用大写
• SQL语句必须以分号结尾
• SQL语句对于换行和空格没有限制；推荐增加适当的换行美化语句
• 当行注释# ，多行注释/*···*/

三、数据库范式

数据库建表须满足第三范式 参考
第一范式：原子性，数据库表每一列都是不可分割的数据项，同列不存在多个值，也不能出现重复值。
第二范式：唯一性，数据表每条记录唯一的，为了区分加入唯一标识列，主键列。
第三范式：关联性，每一列都与主键有直接关联，列与列之间不存在直接依赖。

四、常用字段约束

约束名称	关键字	描述
主键约束	PRIMARY KEY	字段唯一，且不能未NULL，自增auto_increment
非空约束	NOT NULL	字段不能为NULL
唯一约束	UNIQUE	字段值唯一，且可以为NULL，如：电话
外键约束	FOREIGN KEY	保持数据的关联性，不建议用，容易形成闭环

五、常用数据类型

数字型

类型	大小	说明
TINYINT	1字节	小整数
SMALLINT	2字节	普通整数
MEDIUMINT	3字节	普通整数
INT	4字节	较大整数
BIGINT	8字节	大整数
FLOAT	4字节	单精度浮点数
DOUBLE	8字节	双精度浮点数
DECIMAL	--------	DECIMAL(10,2)

字符串

类型	大小	说明
CHAR	1-255	固定长度字符串
VARCHAR	1-65535	不固定长度字符串
TEXT	1-65535	不确定长度字符串
MEDIUMTEXT	1-1千6百万字符	不确定长度字符串
LONGTEXT	1-42亿字符	不确定长度字符串

时间类型

类型	大小	说明
DATE	3字节	日期
TIME	3字节	时间
YEAR	1字节	年份
DATETIME	8字节	日期时间
TIMESTAMP	4字节	时间戳(1970年后)

六、DDL语句

逻辑库操作

CREATE DATABASE 逻辑库名称;  # 创建逻辑空间
DROP DATABASE 逻辑库名称;  # 删除逻辑空间
USE 逻辑库名称;  # 进入逻辑空间
SHOW DATABASES;  # 查看现有的逻辑空间

数据表操作

# 创建数据表
CREATE TABLE 数据表名 (
	列名1 数据类型[约束] [COMMENT 注释],
	列名2 数据类型[约束] [COMMENT 注释],
	....
)[COMMENT 注释];

SHOW TABLES ;  # 查看所有数据表
DESC 数据表名;  # 查询表结构
SHOW CREATE TABLE 数据表名;  # 查询创建数据表的SQL语句
DROP TABLE 数据表名;  # 删除数据表

修改数据表结构

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

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

# 修改字段名称
ALTER TABLE 表名称
CHANGE 列1 新列名 数据类型 [约束] [COMMENT 注释],
CHANGE 列2 新列名 数据类型 [约束] [COMMENT 注释],
....;

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

# 根据结果集创建数据表
CREATE TABLE 表名称 AS (查询结果集)  #带数据
CREATE TABLE 表名称1 LIKE 表名称2  #不带数据

索引

索引的使用原则

• 数据量很大，而且经常被查询的数据表可以设置索引，如：淘宝搜索
• 索引只添加在经常被用作检索条件的字段上面
• 不要在超长的字段上创建索引

# 建表创建索引
CREATE TABLE 表名称(
.....
INDEX [索引名称] (字段),
.....
);
# 添加索引
CREATE INDEX 索引名称 ON 表名(字段);
ALTER TABLE 表名称 ADD INDEX [索引名称](字段);
# 查看索引
SHOW INDEX FROM 表名称;
# 删除索引
DROP INDEX 索引名称 ON 表名;

七、DML语句

查询语句

常用运算符

表达式	意义	例子	描述
IS NULL	为空	name IS NULL
IS NOT NULL	不为空	name IS NOT NULL
BETWEEN AND	范围	sal BETWEEN 2000 AND 3000	左闭右闭
LIKE	模糊查询	name LIKE “A%”	匹配A开头
REGEXP	正则匹配	name REGEXP “[a-zA-Z]{4}”	匹配前四个为英文字母
IN	包含	num IN (10,20,30)	符合的括号的内容
其它	常用比较运算符	常用的四则运算符

# 查询
SELECT * FROM 表名;
SELECT 字段1 AS 列别名1,字段2 AS 列别名2 FROM 表名;
# 查询数据分页,偏移量为往后数据量
SELECT ... FROM .... LIMIT (起始位置,)偏移量;
# 排序 DESC降序 ASC升序(默认)
SELECT 字段1, 字段2 FROM 表名 ORDER BY 字段1 DESC,字段2 ASC;
# 去重 DISTINCT,只能查询一列;多列失效, 且要写在最前面和出现一次
SELECT DISTINCT 字段1 FROM 表名;
# 条件查询
SELECT 字段1	FROM 表名 WHERE 条件1 [AND|OR] 条件2;
SELECT 10*IFNULL(NULL, 0);  # 判断null返回0
SELECT DATEIFF(NOW(), 日期1);  # 求两日期的天数 

高级查询

常用函数

函数名	意义
AVG()	平均值
SUM()	求和
MAX()	最大值
MIN()	最小值
COUNT()	获取记录数
LENGTH()	求字符串长度
IFNULL(NULL, 0)	判断null返回0
DATEIFF(NOW(), 日期1)	求两日期的天数
FLOOT	向下取整
CEIL	向上取整

分组查询
GROUP BY 子句的作用是通过一定规则将一个数据划分成若干个小区域，在对每个区域做处理

# 普通分组 and 逐级分组
SELECT 字段1,字段2,聚合函数(字段3）FROM 表名 GROUP BY 字段1,字段2;

# 汇总计算，对分组后的每一列在求取对应的方法结果
SELECT 字段1,聚合函数(字段2）FROM 表名 GROUP BY 字段1 WITH ROLLUP;

# GROUP_CONCAT函数可以把分组查询中的某个字段拼接成一个字符串
SELECT 字段1,聚合函数(字段2),GROUP_CONCAT(字段3) FROM 表名 GROUP BY 字段1;

# HAVING 对分组后的结果条件筛选
SELECT 字段1,聚合函数(字段2）FROM 表名 GROUP BY 字段1 HAVING 条件;

内连接

• 必须关联条件，否则会两张表交叉连接，产生笛卡尔积
• 内连接是结果集中只保留符合连接条件的纪律
• 相同数据表也可以连接

SELECT ... FROM 表1 [INNER] JOIN 表2 ON 连接条件 [INNER] JOIN 表3 ON 连接条件;
SELECT ... FROM 表1 JOIN 表2 WHERE 连接条件;
SELECT ... FROM 表1,表2 WHERE 连接条件;

外连接

• 外连接是不管符不符合连接条件，记录都保存在结果集中
• 分为左外连接和右外连接
• 条件写在where子句中，不符合条件的记录会被筛选掉，而不是保留

左外连接就是保留左表的全部记录，与右表连接。如果右表有符合条件的记录就与左表连接，如果没有就用NULL值与左表连接，反之右外连接也如此。

# 外连接
SELECT ... FROM 左表1 LEFT|[RIGHT] JOIN 右表 ON 条件;

# UNION 合并结果集
(查询结果) UNION (查询结果) UNION ....;

子查询
只建议from连接子查询。不推荐where和selec子查询。因为每一次查询都是逐条数据查询，性能堪忧。

SELECT ... FROM (子查询结果集);

增加语句

INSER语句可以向数据表写入一条或多条记录，Oracle是不支持多个values

# 支持插入单条或多条数据
INSERT INTO 表名(字段1,字段2,...) VALUES (值1,值2,...),(值1,值2,...);
# Mysql方言，独有插入
INSERT INTO 表名 SET 字段1=值1,字段2=值2,...;
# IGNORE 关键字会让INSERT直插入数据库不存在的数据，冲突则忽略
INSERT [IGNORE] INTO 表名(字段1,字段2,...) VALUES (值1,值2,...);

更新语句

用于修改表存在的记录,如果您使用IGNORE关键词，则即使在更新过程中出现错误，更新语句也不会中断。如果出现了重复关键字冲突，则这些行不会被更新

# IGNOGE, LIMIT只能写入限制的前n行的数据
UPDATE [IGNORE] 表名 SET 字段1=值1,字段2=值2,...[WHERE 条件1] [ORDER BY...] [LIMIT ...];

如果通过相关查询(子查询)来更新特定的数据，效率非常底下，所以推荐使用表连接还更新数据，并且表连接可以修改多张表的数据
内连接：

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语句是在事务机制下删除记录，删除之前，先要将删除的记录保存到日志文件，在删除。
• TRUNCATE语句跳过事务机制删除，速度远超普通删除

普通删除：

# IGNORE 忽略无法删除数据，如有外键约束的数据
DELETE [IGNORE] FROM 表名 [WHERE 条件1] [ORDER BY...] [LIMIT ...];

跳过事务删除：

TRUNCATE TABLE 表名;

因相关查询效率比较低，所以推荐使用表连接方式来实现表连接删除，
内连接：

# DELETE 表1 删除的是查询后的视图结果集表，不可为临时表，如子查询结果集
DELETE 表1,... FROM 表1 JOIN 表2 ON 条件 [WHERE 条件1] [ORDER BY...] [LIMIT ...];

外连接：

# DELETE 表1 删除的是查询后的视图结果集表，不可为临时表，如子查询结果集
DELETE 表1,... FROM 表1 [LEFT|RIGHT] JOIN 表2 ON 条件;

八、总结

• 创建表一定要遵守第三范式
• 掌握数据库的基本语法知识
• 掌握表的连接方法