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;
今日一首诗
秋风词 唐-李白
秋风清,秋月明,落叶聚还散,寒鸦栖复惊。
相亲相见知何日,此时此夜难为情!
以上内容,如有错误的理解,烦请大家不吝指正,谢谢哈👀