CS-Notes SQL 语法全解：从 DDL/DML 到子查询、连接、存储过程、触发器与事务管理

CS-Notes SQL 语法全解从 DDL/DML 到子查询、连接、存储过程、触发器与事务管理【免费下载链接】CS-Notes:books: 技术面试必备基础知识、Leetcode、计算机操作系统、计算机网络、系统设计项目地址: https://gitcode.com/GitHub_Trending/cs/CS-Notes本篇基于 CS-Notes 仓库中的 notes/SQL.md 整理系统覆盖 SQL 的完整语法体系模式与基础约定、建表与改表、增删改查、过滤与通配符、汇总/文本/日期/数值函数、分组与子查询、四种连接方式、组合查询、视图、存储过程、游标、触发器、事务、字符集与权限管理。读完后读者能够独立完成 MySQL 环境下从建库建表、日常 CRUD 到存储过程与触发器开发的完整链路并结合仓库中 notes/MySQL.md 的索引与查询优化知识理解每条语句背后的执行原理。一、SQL 基础与约定模式Schema定义了数据如何存储、存储什么样的数据以及数据如何分解等信息数据库和表都有模式。两条重要的约定主键的值不允许修改也不允许复用不能将已经删除的主键值赋给新数据行的主键SQL 语句本身不区分大小写但表名、列名和值是否区分大小写依赖于具体的 DBMS 及其配置。SQLStructured Query Language的标准 SQL 由 ANSI 标准委员会管理因而称为 ANSI SQL各个 DBMS 都有自己的实现如 PL/SQL、Transact-SQL 等。SQL 支持三种注释形式# 注释 SELECT * FROM mytable; -- 注释 /* 注释1 注释2 */数据库的创建与使用CREATE DATABASE test; USE test;二、创建表与修改表建表一个典型的建表语句涵盖了自增、默认值、可空、主键等列约束的用法CREATE TABLE mytable ( # int 类型不为空自增 id INT NOT NULL AUTO_INCREMENT, # int 类型不可为空默认值为 1不为空 col1 INT NOT NULL DEFAULT 1, # 变长字符串类型最长为 45 个字符可以为空 col2 VARCHAR(45) NULL, # 日期类型可为空 col3 DATE NULL, # 设置主键为 id PRIMARY KEY (id));改表添加列ALTER TABLE mytable ADD col CHAR(20);删除列ALTER TABLE mytable DROP COLUMN col;删除表DROP TABLE mytable;三、插入、更新与删除插入普通插入INSERT INTO mytable(col1, col2) VALUES(val1, val2);将检索出来的数据插入另一张表INSERT INTO mytable1(col1, col2) SELECT col1, col2 FROM mytable2;将一个表的内容插入到一个新表以查询结果建表CREATE TABLE newtable AS SELECT * FROM mytable;更新UPDATE mytable SET col val WHERE id 1;删除DELETE FROM mytable WHERE id 1;TRUNCATE TABLE可以清空整张表也就是删除所有行TRUNCATE TABLE mytable;实战要点使用 UPDATE 和 DELETE 操作时一定要带 WHERE 子句否则会把整张表的数据都破坏掉。可以先用等价的 SELECT 语句验证影响范围确认无误后再执行修改防止错误删除。四、查询基础DISTINCT 与 LIMITDISTINCT相同值只会出现一次。需要特别注意DISTINCT 作用于 SELECT 列表的所有列也就是说所有列的值都相同才算一行重复。SELECT DISTINCT col1, col2 FROM mytable;LIMITLIMIT 限制返回的行数。它最多带两个参数第一个参数为起始行号从 0 开始第二个参数为返回的总行数。返回前 5 行SELECT * FROM mytable LIMIT 5;SELECT * FROM mytable LIMIT 0, 5;返回第 3~5 行SELECT * FROM mytable LIMIT 2, 3;五、排序ASC升序默认DESC降序可以按多个列排序并且为每一列指定不同的排序方式SELECT * FROM mytable ORDER BY col1 DESC, col2 ASC;从仓库中 notes/MySQL.md 的索引章节可以看到InnoDB 的 BTree 索引因为有序性除了用于查找还可以用于排序和分组——ORDER BY如果恰好命中索引顺序数据库可以利用索引的有序性避免额外排序这正是“索引能加速排序”这一结论的来源。六、过滤不进行过滤的数据量往往非常大导致通过网络传输了多余的数据从而浪费网络带宽。因此应尽量使用 SQL 语句在服务器端过滤不必要的数据而不是把所有数据传回客户端后再过滤。SELECT * FROM mytable WHERE col IS NULL;WHERE 子句可用的操作符操作符说明等于小于大于!不等于!小于等于!大于等于BETWEEN在两个值之间IS NULL为 NULL 值需要注意NULL 与 0、空字符串都不同判断 NULL 必须使用IS NULL而不是 NULL。逻辑组合与集合操作AND 和 OR用于连接多个过滤条件。AND 优先处理当一个表达式同时涉及多个 AND 和 OR 时使用()显式决定优先级使逻辑更清晰。IN匹配一组值其后也可以接一个 SELECT 子句从而匹配子查询得到的一组值。NOT否定一个条件。七、通配符通配符同样用在过滤语句中但只能用于文本字段。%匹配 0 个或多个任意字符_匹配恰好 1 个任意字符[ ]匹配集合内的字符例如[ab]将匹配字符 a 或者 b。用脱字符^可以对其进行否定即不匹配集合内的字符。使用LIKE进行通配符匹配SELECT * FROM mytable WHERE col LIKE [^AB]%; -- 不以 A 和 B 开头的任意文本性能提醒不要滥用通配符通配符位于开头处的匹配会非常慢。原因可以从 notes/MySQL.md 的 BTree 原理中得到印证索引支持的是有序键的区间查找而LIKE %abc这种前缀不定的模式无法确定键值区间从而无法走索引扫描只能退化为全表扫描。八、计算字段在数据库服务器上完成数据的转换和格式化的工作往往比在客户端上快得多且转换和格式化后的数据量更小还能减少网络通信量。计算字段通常需要使用AS来取别名否则输出时字段名会变成整个计算表达式SELECT col1 * col2 AS alias FROM mytable;CONCAT()用于连接两个字段。许多数据库会使用空格把一个值填充为列宽因此连接的结果可能出现不必要的空格使用TRIM()可以去除首尾空格SELECT CONCAT(TRIM(col1), (, TRIM(col2), )) AS concat_col FROM mytable;九、内置函数各个 DBMS 的函数都不相同因此函数不具备可移植性以下主要列举 MySQL 的函数。汇总函数函数说明AVG()返回某列的平均值COUNT()返回某列的行数MAX()返回某列的最大值MIN()返回某列的最小值SUM()返回某列值之和AVG()会忽略 NULL 行。使用 DISTINCT 可以只汇总不同的值SELECT AVG(DISTINCT col1) AS avg_col FROM mytable;文本处理函数函数说明LEFT()取左边的字符RIGHT()取右边的字符LOWER()转换为小写字符UPPER()转换为大写字符LTRIM()去除左边的空格RTRIM()去除右边的空格LENGTH()长度SOUNDEX()转换为语音值其中SOUNDEX()可以将一个字符串转换为描述其语音表示的字母数字模式用于模糊的“同音”匹配SELECT * FROM mytable WHERE SOUNDEX(col1) SOUNDEX(apple)日期和时间处理函数日期格式YYYY-MM-DD时间格式HH:MM:SS函数说明ADDDATE()增加一个日期天、周等ADDTIME()增加一个时间时、分等CURDATE()返回当前日期CURTIME()返回当前时间DATE()返回日期时间的日期部分DATEDIFF()计算两个日期之差DATE_ADD()高度灵活的日期运算函数DATE_FORMAT()返回一个格式化的日期或时间串DAY()返回一个日期的天数部分DAYOFWEEK()对于一个日期返回对应的星期几HOUR()返回一个时间的小时部分MINUTE()返回一个时间的分钟部分MONTH()返回一个日期的月份部分NOW()返回当前日期和时间SECOND()返回一个时间的秒部分TIME()返回一个日期时间的时间部分YEAR()返回一个日期的年份部分mysql SELECT NOW();2018-4-14 20:25:11数值处理函数函数说明SIN()正弦COS()余弦TAN()正切ABS()绝对值SQRT()平方根MOD()余数EXP()指数PI()圆周率RAND()随机数十、分组分组GROUP BY把具有相同数据值的行放在同一组中然后可以对同一分组的数据使用汇总函数处理例如求分组数据的平均值。指定的分组字段除了用于分组也会自动按该字段排序SELECT col, COUNT(*) AS num FROM mytable GROUP BY col;GROUP BY 自动按分组字段排序而 ORDER BY 还可以按汇总字段排序SELECT col, COUNT(*) AS num FROM mytable GROUP BY col ORDER BY num;WHERE 过滤行HAVING 过滤分组且行过滤先于分组过滤SELECT col, COUNT(*) AS num FROM mytable WHERE col 2 GROUP BY col HAVING num 2;分组的规定GROUP BY 子句出现在 WHERE 子句之后、ORDER BY 子句之前除了汇总字段外SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出NULL 的行会单独分为一组大多数 SQL 实现不支持 GROUP BY 列具有可变长度的数据类型。十一、子查询子查询中只能返回一个字段的数据。可以将子查询的结果作为 WHERE 语句的过滤条件SELECT * FROM mytable1 WHERE col1 IN (SELECT col2 FROM mytable2);下面的语句检索出每个客户的订单数量子查询会对第一个查询检索出的每个客户执行一次相关子查询SELECT cust_name, (SELECT COUNT(*) FROM Orders WHERE Orders.cust_id Customers.cust_id) AS orders_num FROM Customers ORDER BY cust_name;这种逐行执行的方式说明子查询在大表上的开销可能很高——后文的连接JOIN在很多场景下可以替代子查询且效率一般更快。十二、连接连接用于连接多个表使用 JOIN 关键字并且连接条件用 ON 而不是 WHERE。连接可以替换子查询并且比子查询的效率一般会更快。可以用 AS 给列名、计算字段和表名取别名给表名取别名是为了简化 SQL 语句以及连接相同的表。内连接内连接又称等值连接使用 INNER JOIN 关键字SELECT A.value, B.value FROM tablea AS A INNER JOIN tableb AS B ON A.key B.key;也可以不明确使用 INNER JOIN而使用普通查询并在 WHERE 中将两个表中要连接的列用等值方法连接起来SELECT A.value, B.value FROM tablea AS A, tableb AS B WHERE A.key B.key;自连接自连接可以看成内连接的一种只是连接的表是自身。例一张员工表包含员工姓名和所属部门要找出与 Jim 处在同一部门的所有员工姓名。子查询版本SELECT name FROM employee WHERE department ( SELECT department FROM employee WHERE name Jim);自连接版本SELECT e1.name FROM employee AS e1 INNER JOIN employee AS e2 ON e1.department e2.department AND e2.name Jim;自然连接自然连接把同名列通过等值测试连接起来同名列可以有多个。内连接和自然连接的区别内连接需要显式提供连接的列而自然连接自动连接所有同名列。SELECT A.value, B.value FROM tablea AS A NATURAL JOIN tableb AS B;外连接外连接保留了没有关联的那些行。分为左外连接、右外连接和全外连接左外连接保留左表中没有关联的行。检索所有顾客的订单信息包括还没有订单的顾客SELECT Customers.cust_id, Customer.cust_name, Orders.order_id FROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id Orders.cust_id;customers 表cust_idcust_name1a2b3corders 表order_idcust_id11213343左外连接结果cust_idcust_nameorder_id1a11a23c33c42bNull可以看到顾客 b 没有订单左外连接将其保留右表列填充为 NULL——这正是外连接区别于内连接的关键行为。十三、组合查询使用UNION组合两个查询如果第一个查询返回 M 行第二个查询返回 N 行那么组合查询的结果一般为 MN 行。约束每个查询必须包含相同的列、表达式和聚集函数默认会去除重复行如果需要保留重复行使用UNION ALL整个语句只能包含一个 ORDER BY 子句并且必须位于语句的最后。SELECT col FROM mytable WHERE col 1 UNION SELECT col FROM mytable WHERE col 2;十四、视图视图是虚拟的表本身不包含数据也就不能对其进行索引操作。对视图的操作和对普通表的操作一样。视图的好处简化复杂的 SQL 操作比如复杂的连接只使用实际表的一部分数据通过只给用户访问视图的权限保证数据的安全性更改数据格式和表示。CREATE VIEW myview AS SELECT Concat(col1, col2) AS concat_col, col3*col4 AS compute_col FROM mytable WHERE col5 val;十五、存储过程存储过程可以看成是对一系列 SQL 操作的批处理。使用存储过程的好处代码封装保证了一定的安全性代码复用由于是预先编译因此具有很高的性能。分隔符问题在命令行中创建存储过程需要自定义分隔符。因为 mysql 命令行以;作为语句结束符而存储过程体内也包含分号会被错误地当成结束符造成语法错误所以先用delimiter //把结束符临时改为//。存储过程包含 in、out 和 inout 三种参数给变量赋值需要用 SELECT INTO 语句每次只能给一个变量赋值不支持集合操作delimiter // create procedure myprocedure( out ret int ) begin declare y int; select sum(col1) from mytable into y; select y*y into ret; end // delimiter ;调用并读取输出参数call myprocedure(ret); select ret;十六、游标在存储过程中使用游标可以对一个结果集进行移动遍历。游标主要用于交互式应用其中用户需要对数据集中的任意行进行浏览和修改。使用游标的四个步骤声明游标这个过程没有实际检索出数据打开游标取出数据关闭游标。delimiter // create procedure myprocedure(out ret int) begin declare done boolean default 0; declare mycursor cursor for select col1 from mytable; # 定义了一个 continue handler当 sqlstate 02000 这个条件出现时会执行 set done 1 declare continue handler for sqlstate 02000 set done 1; open mycursor; repeat fetch mycursor into ret; select ret; until done end repeat; close mycursor; end // delimiter ;关键点在于sqlstate 02000这是“结果集耗尽”的条件通过declare continue handler注册处理器在游标取完最后一行后将done置 1从而结束repeat ... until循环。十七、触发器触发器会在某个表执行 DELETE、INSERT、UPDATE 语句时自动执行。必须指定在语句执行之前还是之后自动执行BEFORE用于数据验证和净化AFTER用于审计跟踪将修改记录到另一张表中。INSERT 触发器包含一个名为 NEW 的虚拟表CREATE TRIGGER mytrigger AFTER INSERT ON mytable FOR EACH ROW SELECT NEW.col into result; SELECT result; -- 获取结果DELETE 触发器包含一个名为 OLD 的虚拟表并且是只读的UPDATE 触发器同时包含 NEW 和 OLD 两个虚拟表其中 NEW 可以被修改而 OLD 是只读的。限制MySQL 不允许在触发器中使用 CALL 语句也就是不能调用存储过程。十八、事务管理基本术语事务transaction一组 SQL 语句回退rollback撤销指定 SQL 语句的过程提交commit将未存储的 SQL 语句结果写入数据库表保留点savepoint事务处理中设置的临时占位符可以只对它发布回退区别于回退整个事务。边界条件不能回退 SELECT 语句回退也没有意义也不能回退 CREATE 和 DROP 语句。MySQL 默认是隐式提交每执行一条语句就把这条语句当成一个事务进行提交。出现START TRANSACTION语句时关闭隐式提交COMMIT或ROLLBACK执行后事务自动关闭重新恢复隐式提交。将 autocommit 设置为 0 可以取消自动提交autocommit 标记是针对每个连接而不是针对服务器的。保留点的回退规则如果没有设置保留点ROLLBACK 会回退到 START TRANSACTION 语句处如果设置了保留点并在 ROLLBACK 中指定该保留点则只回退到该保留点START TRANSACTION // ... SAVEPOINT delete1 // ... ROLLBACK TO delete1 // ... COMMIT十九、字符集基本术语字符集character set字母和符号的集合编码collation 的基础某个字符集成员的内部表示校对collation指定如何比较主要用于排序和分组。除了给表指定字符集和校对外也可以给列指定CREATE TABLE mytable (col VARCHAR(10) CHARACTER SET latin COLLATE latin1_general_ci ) DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;也可以在排序、分组时临时指定校对SELECT * FROM mytable ORDER BY col COLLATE latin1_general_ci;二十、权限管理MySQL 的账户信息保存在mysql这个数据库中USE mysql; SELECT user FROM user;创建账户新创建的账户没有任何权限CREATE USER myuser IDENTIFIED BY mypassword;修改账户名RENAME USER myuser TO newuser;删除账户DROP USER myuser;查看权限SHOW GRANTS FOR myuser;授予权限账户用usernamehost的形式定义username%表示默认主机名任意主机GRANT SELECT, INSERT ON mydatabase.* TO myuser;删除权限GRANT 和 REVOKE 可在几个层次上控制访问权限整个服务器使用GRANT ALL和REVOKE ALL整个数据库使用ON database.*特定的表使用ON database.table特定的列特定的存储过程。REVOKE SELECT, INSERT ON mydatabase.* FROM myuser;更改密码必须使用 PASSWORD() 函数进行加密SET PASSWORD FOR myuser PASSWORD(new_password);延伸阅读仓库中的关联笔记本文的语法内容以 MySQL 为落地实现仓库中还有几篇可直接衔接深入的笔记notes/MySQL.mdBTree 索引原理、InnoDB 与 MyISAM 存储引擎对比、使用 EXPLAIN 分析查询、索引优化与数据切分/复制可解释本文过滤、排序、通配符等章节背后的性能结论notes/SQL 语法.md与本篇同源、结构一致的 SQL 语法对照笔记notes/SQL 练习.md16 道经典 SQL 练习题重复邮件、第二高薪水、自连接比较薪资等适合把子查询、连接、分组等语法练熟notes/数据库系统原理.md从数据库系统原理角度补充事务、并发控制与存储管理知识与本文的事务管理章节互为表里。参考资料Ben Forta. SQL 必知必会 [M]. 人民邮电出版社, 2013.【免费下载链接】CS-Notes:books: 技术面试必备基础知识、Leetcode、计算机操作系统、计算机网络、系统设计项目地址: https://gitcode.com/GitHub_Trending/cs/CS-Notes创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考