1. 基本概念
- 表:表是一种结构化文件,可以用来存储某种特定的数据。数据库名和表名组合是唯一的。表由列组成,数据按行存储。
- 主键:表中的每一行都应该有一列或者多列能够唯一标识自己,确保任意两条记录不能重复。通过这一列或多列区分不同的行,称之为主键。
- 外键:关系数据库可以通过外键来实现一对一、一对多、多对多的关系。外键既可以设置数据库来约束,也可以不设置约束,仅依靠应用程序的逻辑来验证。
1.1 数值类型
如果是无符号类型,则范围是(0,有符号类型最大值*2+1)
类型 | 大小 | 范围 |
---|---|---|
int(常用) | 4字节 | (-2 147 483 648,2 147 483 647) |
bigint | 8字节 | (-9223372036854775808,9223372036854775807) |
float(常用) | 4字节 | 单精度浮点类型 |
double(常用) | 8字节 | 双精度浮点类型 |
1.2 日期和时间类型
类型 | 大小 | 格式 |
---|---|---|
Date | 3字节 | yyyy-MM-dd |
time | 3字节 | HH:MM:SS |
year | 1字节 | yyyy |
datetime | 8字节 | yyyy-MM-dd HH:MM:SS |
1.3 字符串类型
类型 | 大小 | 用途 |
---|---|---|
char | 0-255字节 | 定长字符串 |
varchar | 0-65535字节 | 变长字符串 |
Text | 0-65535字节 | 长文本数据 |
2. 基本语法
2.1 增加、删除、修改
增加 | INSERT INTO <表名> (字段1, 字段2, …) VALUES (值1, 值2, …); |
---|---|
更新 | UPDATE <表名> SET 字段1=值1, 字段2=值2, … WHERE … |
删除 | DELETE FROM <表名> WHERE …; |
- 总结:
- 使用update、delete语句需要带where语句,否则将会修改、删除所有行。
2.2 检索数据
所有列 | SELECT * FROM <表名>; |
---|---|
单个列或多个列 | SELECT 列1,列2 FROM <表名>; 返回指定的列,也可以给每个列起一个别名,语法是SELECT 列1 别名1,列2 别名2,… FROM … |
检索不同的值 | SELECT DISTINCT 列名1 FROM <表名>; |
- 总结
- 基本查询就是使用SELECT、FROM结合DISTINCT进行查询。
2.3 排序数据
按列排序 | SELECT * FROM <表名> ORDER BY <列名>; |
---|---|
按列位置排序 | SELECT * FROM <表名> ORDER BY 5; |
按列排序检索(升序、降序) | SELECT * FROM <表名> ORDER BY <列名> DESC; |
-
总结:
-
ORDER BY 子句取一个列或多个列的名字进行排序。多列排序时,只有第一个列相同时,才会通过第二列进行排序。
-
默认排序方向为升序,使用DESC为降序。当使用ORDER BY score DESC,name 时,只对score进行降序,而当score相同时,name使用升序。
-
2.3 条件查询
注意:在同时使用where子句和order by子句时,where应该位于order by子句之前
2.3.1 where操作符
操作符 | 说明 | 操作符 | 说明 |
---|---|---|---|
= | 等于 | > | 大于 |
<> | 不等于 | >= | 大于等于 |
!= | 不等于 | !> | 不大于 |
< | 小于 | BETWEEN | 在指定的两个值之间 |
<= | 小于等于 | IS NULL | 为NULL值 |
!< | 不小于 | NOT | 例:NOT id = 2;表示不符合id等于2的数据 |
2.3.2 排序语法
规则 | 语法 |
---|---|
=条件查询 | SELECT * FROM <表名> where score=80; |
AND条件查询 | SELECT * FROM <表名> where score=80 AND gender=‘M’; |
OR条件查询 | SELECT * FROM <表名> where score=80 OR gender=‘M’; |
IN条件查询 | SELECT * FROM <表名> where score IN (10,90); |
BETWEEN条件查询 | SELECT * FROM <表名> WHERE score BETWEEN 10 AND 80; |
NOT条件查询 | SELECT * FROM <表名> where NOT score=80; |
LIKE过滤查询 | SELECT * FROM <表名> WHERE name LIKE ‘a%’; |
like语法规则
- 通配符百分号(%)表示任意字符出现任意次数(包括0)。
- 通配符下划线(_)表示任意字符出现一次。
- 通配符方括号([])表示指定字符集。(匹配[]内任意一个字符)
总结
-
IN(x1,x2)表示查询包含x1,x2
-
使用BETWEEN关键字时,必须制定两个值,所需范围的低端值和高端值,必须使用AND分隔开。
-
优先级关系:!=>AND>OR
-
条件查询就是如何使用AND、OR、IN、NOT操作符结合WHERE进行查询。
2.4 分组查询
- 子句执行顺序:WHERE > GROUP BY > HAVING > ORDER BY
GROUP BY | SELECT class_id count(*) num FROM students GROUP BY class_id; |
---|---|
HAVING | SELECT COUNT() FROM students GROUP BY class_id HAVING COUNT()>=2; |
- 总结:
- GROUP BY子句按照class_id先进行分组,然后再分别计算总和。
- 将学生表根据班级分组,并且过滤出班级里学生数大于2的班级。
2.5 分页查询
分页语法 | SELECT * FROM <表名> LIMIT <行数> OFFSET <起始行>; |
---|
- LIMIT关键字表示检索的行数,OFFSET关键字表示从哪一行开始。(不包括开始的那一行,为开区间)
- LIMIT总是设定为pageSize。
- OFFSET计算公式为pageSize * (pageIndex - 1)。
2.6 聚合查询
2.6.1 函数总结
字符函数 | 说明 |
---|---|
length() | 计算字符的长度 |
concat() | 拼接字符串 |
upper() | 变成大写 |
lower() | 变成小写 |
lpad(“str”,length,“c”) | 用c左填充str使其长度为length |
rpad() | 右填充 |
replace(str,被替换字符,替换字符) | 替换str中的字符 |
sutstr(字符,开始位置) | 截取字符 |
instr(str,substr) | 在str中找到substr的位置 |
trim() | 去掉空格 |
trim(‘a’ from str) | 在str中去掉收尾的a |
CONCAT | 将多个列拼接在一起 |
数学函数 | 说明 |
---|---|
— | — |
round() | 四舍五入 |
ceil() | 向上取整 |
floor() | 向下取整 |
truncate() | 截断 |
mod() | 取余 |
COUNT() | 计算行的个数 |
SUM | 计算某一列的合计值,该列必须为数值 |
AVG | 计算某一列的平均值,该列必须为数值 |
MAX | 计算某一列的最大值 |
MIN | 计算某一列的最小值 |
日期函数 | 说明 |
---|---|
now() | 当前系统日期+时间 |
curdate() | 当前系统日期,不包含时间 |
curtime() | 当前时间,不包含日期 |
year() | 获取日期的指定部分 |
如果where条件没有匹配到任何行,这些函数将返回null。
其他函数 | 说明 |
---|---|
if(条件,a,b) | 类似三元运算符,成立返回a,不成立返回b |
case ** when ** case ** | switch case 的效果 |
2.6.2 聚合查询示例:
查询学生的总个数 | SELECT COUNT(*) num FROM students; |
---|---|
拼接字段 | SELECT CONCAT(NAME,‘(’,score,‘)’) FROM students; |
2.7 多表查询(联结查询)
多表查询 | SELECT * FROM <表1> <表2> |
---|
- 多表查询产生笛卡尔积,必须使用WHERE子句过滤检索出想要的数据。联结查询是SQL中一个最重要,最强大的特性。
2.8 连接查询
2.8.1 内连接
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;
- FROM <表一>先确定主表,INNER JOIN <表二>再确定需要连接的表,ON <条件> 确定条件。 INNER JOIN 连接只返回同时存在于两张表的行数据。
2.8.2 外连接
语法 | 含义 |
---|---|
FROM …LEFT OUTER JOIN | 返回左表都存在的行,右表中不存在的则填充NULL |
FROM …RIGHT OUTER JOIN | 返回右表中的所有行,左表中不存在,则用NULL来填充 |
FROM …FULL OUTER JOIN | 会把两张表中所有记录全部选择出来,自动把把对方不存在的填充为NULL |
2.9 组合查询
主要有两种情况需要使用组合查询:
- 在一个查询中从不同的表返回结构数据。
- 对一个表执行多个查询,按一个查询返回数据。
组合查询使用UNION连接多个SELECT语句。语法为:SELECT… UNION SELECT…
- 在对同一个表进行组合查询时,使用UNION和WHERE子句中使用OR相同。
- UNION中每个查询的必须包含相同的列、表达式、聚集函数。
- UNION从查询结果集中自动去除了重复的行。如果不想去除,则使用UNION ALL。
- 使用UNION组合查询时,如果使用ORDER BY子句排序,则必须放在最后一条SELECT中。
3. 语句执行顺序
书写sql语句时各关键字的顺序(其中序号为实际的执行顺序):
(5) SELECT
DISTINCT <select_list>
(1) FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
(2) WHERE <where_condition>
(3) GROUP BY <group_by_list>
(4) HAVING <having_condition>
(6) ORDER BY <order_by_condition>
(7) LIMIT <limit_number>
SQL 语句执行过程中,每一步都会产生一个虚拟表(VT),用来保存临时结果。
3.1 From
-
从 FROM 语句知道是从哪个表开始的。如果是关联查询,则对前两个表执行笛卡尔积,得到一个虚拟表 VT1,也就是两个表中每两两条数据进行连接,所以总共有(table1 的记录数 * table2 的记录数) 条记录。
-
通过 ON <join_condition> 进行条件过滤,根据 ON 指定的条件过滤掉不符合条件的数据行,得到 VT2。
-
如果是 OUTER JOIN,则通过 JOIN 的类型添加外部行。在 MySQL 中有两种,一种是 Left Join,把左表在第二步中过滤掉的行添加进来;另一个中是 Right Join,把右表在第二步中过滤掉的行添加进来,生成虚拟表 VT3。
-
如果是多个表关联,则将 VT3 再与下一个表连接,依次执行上述三步。最终得到一个新的虚拟表 VT3。
3.2 WHERE <where_condition>
- 对 VT3 进行 WHERE 过滤,生成虚拟表 VT4。
- 需要注意 ON 与 WHERE 的区别:
- ON:是在生成临时表时使用的条件,使用 ON 条件过滤后,如果是外连接,会再通过 JOIN 类型添加外部行。
- WHERE:是在生成临时表之后使用的条件。
3.3 GROUP BY <group_by_list>
- 对 VT4 按照 GROUP BY 指定的列进行分组,得到虚拟表 VT5。如果应用了 GROUP BY,则只能得到 GROUP BY 指定的列,或者是使用聚合函数。
3.4 HAVING <having_condition>
- 注意,HAVING 是对已经分组后的每一个组执行过滤,得到虚拟表 VT6。
3.5 SELECT DISTINCT <select_list>
-
从虚拟表 VT6 中,选择所需要的内容,生成虚拟表 VT7。
-
然后应用 DISTINCT,移除 VT7 中相同的行,生成新的虚拟表 VT8。
3.6 ORDER BY <order_by_condition>
- 对 VT8 中的内容按照指定的列进行排序,生成一个新的虚拟表 VT9。
3.7 LIMIT <limit_number>
- LIMIT 从 VT9 中选出从指定位置开始的指定行数据,生成最终的虚拟表 VT10,作为结果返回。
4 函数
4.1 文本处理函数
函数 | 说明 |
---|---|
Left() | 返回串左边的字符 |
Length() | 返回串的长度 |
Locate() | 找出串的一个子串 |
Lower() | 将串转换为小写 |
LTrim() | 去掉串左边的空格 |
Right() | 返回串右边的字符 |
RTrim() | 去掉串右边的空格 |
Soundex() | 返回串的SOUNDEX值 |
SubString() | 返回子串的字符 |
Upper() | 将串转换为大写 |
4.2 日期和时间处理函数
函数 | 说明 |
---|---|
AddDate() | 添加一个日期(天、周) |
AddTime() | 添加一个时间(时。分) |
CurDate() | 返回当前日期 |
CurTime() | 返回当前时间 |
Date() | 返回日期时间的日期部分 |
DateDiff() | 计算两个日期之差 |
Date_Add() | 高度灵活的日期计算函数 |
Date_Format | 返回一个格式化的日期或字符串 |
Day() | 返回一个日期的天数部分 |
DayOfWeek() | 对于一个日期返回相应的星期几 |
Hour() | 返回一个时间的小时部分 |
Minute() | 返回一个时间的分钟部分 |
Month() | 返回一个时间的月份部分 |
Now() | 返回当前日期 |
Second() | 返回一个时间的秒部分 |
Time() | 返回一个日期时间的时间部分 |
Year() | 返回一个日期的年份部分 |
4.3 数值处理函数
函数 | 说明 |
---|---|
Abs() | 返回一个数的绝对值 |
Cos() | 返回一个角度的余弦 |
Exp() | 返回一个数的指数值 |
Mod() | 返回除操作的余数 |
Pi() | 返回圆周率 |
Rand() | 返回一个随机数 |
Sin() | 返回一个角度的正弦 |
Sqrt() | 返回一个数的平方根 |
Tan() | 返回一个数的正切 |