SQL（structure query language），结构化查询语言，是一种用于存取数据以及查询、更新和管理关系型数据库的高级非过程化编程语言。通过使用SQL实现以下功能：提取查询数据、增删改数据、增删改数据库对象、数据保护控制、数据库安全控制。
-
常见数据库对象有表(table)、视图(view)、函数(function)、存储过程(procedure)、触发器(trigger)、索引(index)等。
-
SQL语句时可嵌套，强调分号结尾。
-
单行注释符： '#'，多行注释符：'/*'、'*/'。
一、语法分类
注：以MYSQL数据库为操作对象
1.数据定义语言 DDL（data definition language）
用于定义数据库相关对象，常用操作词：Create、Drop、Alter、Truncate。
① 数据库常用操作↓
#查所有库
SHOW databases;
#建库
CREATE database [if not exists] 库名 [character set 字符集名]；
#查看支持的字符集
SHOW character set;
#使用库
USE 库名;
#查建库详细信息
SHOW CREATE database '库名';
#修改库编码
ALERT database 库名 character set '编码类型';② 数据表常用操作↓
#查所有表
SHOW tables;
#删表
DROP table 表名;
#查表结构
DESC 表名;
#建表
CREATE table 表名(
字段1 类型 [约束1,…] COMMENT '字段注释1',
字段2 类型,
......
PRIMARY KEY (字段1)
)[约束] COMMENT '表注释';
#修改表名
ALERT table 表名 RENAME 新表名;
#添加/修改字段
ALERT table 表名 ADD/MODIFY 字段名 字段类型;
#修改字段名
1. ALERT table 表名 CHANGE 旧名 新名 字段类型;
2. ALERT table 表名 RENAME 旧名 TO 新名;（8.0版本后支持RENAME）
#删除字段
ALERT table 表名 DROP 字段名;
#清空表内容
TRUNCATE table 表名;③ 用户常用操作↓
#创建用户
CREATE user '用户名'@'主机名' identified by '密码';
#修改密码
ALERT user '用户名'@'主机名' identified with mysql_native_password by '新密码';
#删除用户
DROP user '用户名';④ 字段常用类型以及常用约束
| 字段类型 | 长度（byte） | 存储内容 | 约束类型 | 释义 | |
|---|---|---|---|---|---|
| TINYINT | 1 | 整数 | Default | 默认值 | |
| SMALLINT | 2 | Unique | 值唯一 | ||
| MEDIUMINT | 3 | Primary Key | 主键 | ||
| INT | 4 | Foreign Key | 外键 | ||
| BIGINT | 8 | Auto_increment | id自增长 | ||
| FLOAT | 4 | 小数 | Not Null | 值不为空，默认可为空 | |
| DOUBLE | 8 | ||||
| DECIMAL | 可变长度 | ||||
| CHAR | 255 | 字符串 | |||
| VARCHAR | 可变长度 | ||||
| BINARY | 255 | 二进制数据 | |||
| VARBINARY | 可变长度 | ||||
| DATETIME | 8 | 日期和时间 | |||
| DATE | 3 | 日期 | |||
| TIME | 3 | 时间 |
2.数据查询语言 DQL（data query language)
用于查询数据库中的数据，常用操作词：Select。
① 单表查询↓
#单表全查/单表筛查
SELECT * FROM 表名 [WHERE 条件];
#单表聚合查询
SELECT SUM(字段1) FROM 表名;
#单表分页查询（limit后只有一个数字则表示条数）
SELECT 字段1 FROM 表名 LIMIT 起始索引,条数;
#单表分组查询
SELECT 字段1 FROM 表名 GROUP BY 分组字段1,分组字段2… [HAVING 分组后筛查条件];
#单表排序查询（asc升序，desc降序；默认升序）
SELECT 字段1 FROM 表名 ORDER BY 排序字段1 [ASC/DESC],排序字段2…;② 多表联查↓
#外连接查询
SELECT 字段1,字段2… FROM 表1 [as] 别名1
LEFT/RIGHT JOIN 表2 别名2 ON 条件
LEFT/RIGHT JOIN 表3 别名3 ON 条件
……
[WHERE 条件1 AND 条件2
ORDER BY 排序字段1,…]
#嵌套查询
SELECT 字段1,... FROM 表1 表别名1 WHERE 表别名1.字段=(SELECT 字段x FROM 表2 WHERE 条件)
UNION
SELECT 字段3,... FROM 表3 表别名3 WHERE 表别名3.字段 in (SELECT 字段x FROM 表2 WHERE 条件);③ 查询语句的聚合函数及常用运算符
| 聚合函数 | 释义 | 运算符号 | 释义 | |
|---|---|---|---|---|
| SUM | 求总和 | >= | 大于等于 | |
| AVG | 求平均数 | <= | 小于等于 | |
| COUNT | 求总条数 | != 或 <> | 不等于 | |
| MAX | 求最大值 | % | 多个占位符 | |
| MIN | 求最小值 | _ | 单个占位符 |
-
其他条件关键词：and、or、like、not in、in、is null、is not null
-
其他函数：abs(求绝对值)、trim/ltrim/rtrim(去除字符串左右/左/右空格)、floor(向下取整)、ceil（向上取整）、round(四舍五入)、length(取字符串长度)、concat(连接字符串)、lower(全小写)、upper(全大写)、initcat(首字母大写)
④ 多表连接基本释义
- 内连接：通过某字段关联多表，分三种：等值连接（使用=）、非等值连接（!=、<、>=等运算符合）、自然连接（特殊的等值连接）。具体写法又区分显隐式，显示即使用了inner join，隐式则在表名间使用逗号间隔。关于自然连接与等值连接的个人理解：两者看起来都是使用了=，但具体执行逻辑却不一样。自然连接是针对列属性作比较，查询结果也会将比较列去重；而等值连接是针对列内容比较，查询结果不会去除重复列。
- 外连接：通过join关键词将多表横向拼接，分两种：左连接（left join）、右连接（right join）。例如：a left join b表示向a表添加b表，以a表数据为基础；右连接反之。个人认为从空间方面来想象一下会更好理解：left就是把前表至于后表左侧，right就是把前表置于后表右侧，最终遵循左到右的顺序，谁在前就以谁的数据为基础，后表匹配不上的则为null值。
⑤ 索引基本释义
- 依据列属性可划分为唯一索引、非唯一索引、单行索引、组合索引。
- 在数据表创建时，依据主键会默认创建一个主键索引，主键索引属于唯一索引，强调值唯一且不为null。在后续可添加普通列为非唯一索引，若还想添加唯一索引则使用unique约束且保证属性列无重复值。如需要查询多个列，则可以添加组合索引。
- 索引不宜过多，因为每次数据更新时，索引也会自动维护，避免过多的占用数据库资源，需要合理创建索引。
3.数据操作语言 DML（data manipulation language）
用来修改数据库中的数据，常用操作词：Insert、Update、Delete。
#新增数据（第一种采用了字段名与值对应匹配；第二种省略字段名则默认依次全匹配，空值也得填，不能跳）
1. INSERT INTO 表名(字段1,字段3,字段2…) values (值1,值3,值2…);
2. INSERT INTO 表名 values(值1,'',值3,…);
#更新数据
UPDATE 表名 SET 字段1=值1,字段2=… [WHERE 条件];
#删除数据（去掉where则表示逐条全删）
DELETE FROM 表名 [WHERE 条件];注：清空表的全部数据还可使用DDL中的： TRUNCATE table 表名; 但这种清除不可回滚。
4.数据控制语言 DCL（data control language）
用来控制数据的访问权限，常用操作词：Grant、Revoke、Deny、Commit、Rollback。
#查权
SHOW GRANT FOR '用户名'@'主机名';
#授权/不予授权
GRANT/DENY 权限列表 ON 库名.表名 TO '用户名'@'主机名';
#撤权
REVOKE 权限列表 ON 库名.表名 FROM '用户名'@'主机名';注：权限列表常见如下↓
- SELECT,UPDATE,DELETE,INSERT,ALTER,GRANT,DROP,SHUTDOWN,EXECUTE,CREATE table/view/index/user,SHOW view等(所有权限为 ALL PRIVILEGES)，具体数据库类型中可用的权限名称可查阅其官网文档。
- 关于DENY和REVOKE的个人理解：数据库中存在用户与角色两种类别，两者不能混淆。默认情况下，用户的权限来源于角色。故当使用REVOKE对用户进行撤权时，可能因为角色的原因，导致用户依然具有相应权限。尤其后续对角色授予其它权时，该用户也会有相应权限，那么这时候使用DENY针对性的让用户失去继承特定权限的资格，就能避免这种情况了，也就是彻底孤立了该用户。
综述，以上SQL语句也就是人们常说的数据库CRUD操作（create、read、update、delete）
二、SQL语句基本优化
- 避免全表查。两个方面：第一，根据需求选取字段(避免select *)；第二，使用on、where完成筛查，将能够筛除大部分数据的条件放到where子句中。
- 使用索引。避免索引失效：第一，不使用索引进行算术计算和函数计算；第二，不使用or、in、not in、like关键词(exists和not exists可以替换in和not in)；第三，不使用null值判断；第四，不使用!=或<>。
- 使用join、left/right join时，遵循小表在前，大表在后的原则，避免 重复/不必要 的数据关联。
- 使用union all替换union，去除不必要的结果排序。
- 表名称太长时使用表别名，方便阅读和区分。
- 在表设计时，使用varchar替换char，如果能确定存储内容为数字，那直接使用数值类型。
三、存储过程、触发器、函数
① 定义
- 触发器（trigger）：不能直接调用的特殊存储过程。与特定表或列的数据修改事件相关联，通过数据库某些事件触发自动执行；
- 存储过程（procedure）：一组用于实现特定功能或操作，能多次循环调用的预编译SQL语句；
- 函数（function）：一组实现计算或查询功能，无法循环调用且只能返回特定数据的方法；
② 示例
MySQL：
# 创建触发器
CREATE trigger 触发器名称 after/before insert/update/delete on 表名
BEGIN
...
END
===============================MySQL===================================
# 创建存储过程
CREATE procedure 存储过程名称(参数名 类型,out 参数名 类型)
BEGIN
...
END
# 调用
call 存储过程名;
===============================MySQL===================================
# 创建函数
CREATE function 函数名称(参数名 类型) RETURNS 返回类型
BEGIN
...
RETURN 参数;
END
# 调用
select 函数名;Oracle：
# 创建触发器
CREATE trigger 触发器名称 after/before insert/update/delete on 表名
BEGIN
...
END
===============================Oracle===============================
# 创建存储过程
CREATE procedure 存储过程名称(参数名 in/out 类型)
AS
BEGIN
...
END
# 调用
exec 存储过程名;
===============================Oracle===============================
# 创建函数
CREATE function 函数名称(参数名 类型) RETURNS 返回类型
AS
BEGIN
...
RETURN 返回参数;
END
# 调用
select 函数名;SQL Server：
# 创建触发器
CREATE trigger 触发器名称 on 表名 for insert/update/delete
AS
BEGIN
...
END
==========================SQL SERVER===============================
CREATE procedure 存储过程名称
［@参数名称1 类型, @参数名称2 类型 output］
AS
BEGIN
...
[RETURN '';]
END
# 调用存储过程
execute 存储过程名;
==========================SQL SERVER================================
# 创建函数
CREATE function 函数名称(@参数名称 类型)
RETURNS 返回值类型
AS
BEGIN
[DECLARE @参数名称 类型]
...
RETURN @参数名;
END
＃ 调用函数
select 函数名;四、简单练习
有一数据库为：school ，其拥有四张表。各表结构及表数据如下：
①student（学号，姓名，出生日期，性别）
②score（学号，课程号，成绩）
③course（课程号，课程名称，教师号）
④teacher（教师号，教师姓名）
/*模糊查询*/ //查询姓“王”的学生名单 select * from student where 姓名 like '王%'; //查询姓“王”的老师个数 select count(教师号) as 老师人数 from teacher where 教师姓名 like '王%'; /*聚合、分组查询*/ //查询课程号为“2”的总成绩 select 课程号,sum(成绩) as 总成绩 from score where 课程号='2'; //查询选了课程的学生人数 select COUNT(DISTINCT 学号) as 选课人数 from score; //查询各科成绩最高和最低的分 select 课程号,MAX(成绩) 最高分,MIN成绩) 最低分 from score group by 课程号; //查询男生、女生人数 select 性别,COUNT(学号) 人数 from student group by 性别; //查询平均成绩大于60分的学生学号和平均成绩 select 学号,AVG(成绩) 平均成绩 from score group by 学号 having AVG(成绩)>60; //查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列 select 课程号,AVG(成绩) 平均成绩 from score group by 课程号 order by 平均成绩,课程号 desc; /*多表联查*/ //查询所有课程成绩小于80分学生的学号、姓名 select A.学号,A.姓名 from student A left join score B on A.学号=B.学号 group by B.学号 having MAX(B.成绩<80); //查询没有学全所有课的学生的学号、姓名 select 学号,姓名 from student where 学号 in(select 学号 from score group by 学号 having count(课程号)< (select count(课程号) from course) ); //查询各科成绩前两名的记录 (select * from score where 课程号='1' order by 成绩 desc limit 2) union all (select * from score where 课程号='2' order by 成绩 desc limit 2) union all (select * from score where 课程号='3' order by 成绩 desc limit 2); //使用分段[100-85],[85-70],[70-60],[‹60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称 select A.课程号,A.课程名称, sum(case when B.成绩 between 85 and 100 then 1 else 0 end) as [100-85], sum(case when B.成绩<85 and B.成绩>=70 then 1 else 0 end) as [85-70], sum(case when B.成绩<70 and B.成绩>=60 then 1 else 0 end) as [70-60], sum(case when B.成绩<60 then 1 else 0 end) as [<60] from course A left join score B on A.课程号=B.课程号 group by A.课程号,A.课程名称; //查询课程编号为3且课程成绩在80分以上的学生的学号和姓名 select 学号,姓名 from student A left join score as B on A.学号=B.学号 where 课程号='3' and 成绩>80;
今日一首诗
秋风词 唐-李白
秋风清，秋月明，落叶聚还散，寒鸦栖复惊。
相亲相见知何日，此时此夜难为情！
以上内容，如有错误的理解，烦请大家不吝指正，谢谢哈👀
342
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
