【SQL】SQL语法小结

发布时间:2024年01月16日

相关资料

参考链接1:SQL 语法(超级详细)
参考链接2:史上超强最常用SQL语句大全
SQL练习网站:CSDN、牛客、LeetCode、LintCode
SQL相关视频:
推荐书籍:


数据分析对SQL的要求

基础部分

  • SELECT数据提取
  • SQL书写规则
  • where语句使用(包含like、between、in/or、逻辑判断)
  • 表的内敛、外联
  • 分组 子查询
  • 排序和去重
  • 聚合函数

进阶部分

  • CASE WHERE 的用法
  • 窗口函数如何使用
  • SQL的执行顺序
  • 变量的使用
  • 分组查询严格模式
  • 空值的影响处理
  • count(*) 和 count(列)
  • exit
  • 临时表
  • 自联接
  • 文本时间函数

SQL语法简介

数据库术语(关系型数据库)

  • 数据库(database) - 保存有组织的数据的容器(通常是一个文件或一组文件)。
  • 数据表(table) - 某种特定类型数据的结构化清单。
  • 模式(schema) - 关于数据库和表的布局及特性的信息。模式定义了数据在表中如何存储,包含存储什么样的数据,数据如何分解,各部分信息如何命名等信息。数据库和表都有模式。
  • 列(column) - 表中的一个字段。所有表都是由一个或多个列组成的。
  • 行(row) - 表中的一个记录。
  • 主键(primary key) - 一列(或一组列),其值能够唯一标识表中每一行。

SQL 语法

  • SQL(Structured Query Language),标准 SQL 由 ANSI 标准委员会管理,从而称为 ANSI SQL。
  • 各个 DBMS 都有自己的实现,如 PL/SQL、Transact-SQL 等。

SQL 语法结构

在这里插入图片描述
SQL 语法结构包括:

  • 子句 - 是语句和查询的组成成分。(在某些情况下,这些都是可选的。)
  • 表达式 - 可以产生任何标量值,或由列和行的数据库表
  • 谓词 - 给需要评估的 SQL 三值逻辑(3VL)(true/false/unknown)或布尔真值指定条件,并限制语句和查询的效果,或改变程序流程。
  • 查询 - 基于特定条件检索数据。这是 SQL 的一个重要组成部分。
  • 语句 - 可以持久地影响纲要和数据,也可以控制数据库事务、程序流程、连接、会话或诊断。

SQL 语法要点

  • SQL 语句不区分大小写,但是数据库表名、列名和值是否区分,依赖于具体的 DBMS 以及配置。
  • 多条 SQL 语句必须以分号(;)分隔。
  • 处理 SQL 语句时,所有空格都被忽略。SQL 语句可以写成一行,也可以分写为多行。
-- 一行 SQL 语句
UPDATE user SET username='robot', password='robot' WHERE username = 'root';
 
-- 多行 SQL 语句
UPDATE user
SET username='robot', password='robot'
WHERE username = 'root';
  • SQL 支持三种注释
## 注释1
-- 注释2
/* 注释3 */

SQL 分类

  • 数据定义语言(DDL)
    • 数据定义语言(Data Definition Language,DDL)是 SQL 语言集中负责数据结构定义与数据库对象定义的语言。
    • DDL 的主要功能是定义数据库对象。
    • DDL 的核心指令是 CREATE、ALTER、DROP。
  • 数据查询语言(DQL):DQL(Data Query Language)用来查询数据库中表的记录(数据)
  • 数据操纵语言(DML)
    • 数据操纵语言(Data Manipulation Language, DML)是用于数据库操作,对数据库其中的对象和数据运行访问工作的编程语句。
    • DML 的主要功能是 访问数据,因此其语法都是以读写数据库为主。
    • DML 的核心指令是 INSERT、UPDATE、DELETE、SELECT。这四个指令合称 CRUD(Create, Read, Update, Delete),即增删改查。
  • 事务控制语言(TCL)
    • 事务控制语言 (Transaction Control Language, TCL) 用于管理数据库中的事务。这些用于管理由 DML 语句所做的更改。它还允许将语句分组为逻辑事务。
    • TCL 的核心指令是 COMMIT、ROLLBACK。
  • 数据控制语言(DCL)
    • 数据控制语言 (Data Control Language, DCL) 是一种可对数据访问权进行控制的指令,它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。
    • DCL 的核心指令是 GRANT、REVOKE。
    • DCL 以控制用户的访问权限为主,因此其指令作法并不复杂,可利用 DCL 控制的权限有:CONNECT、SELECT、INSERT\UPDATE、DELETE、EXECUTE、USAGE、REFERENCES。
    • 根据不同的 DBMS 以及不同的安全性实体,其支持的权限控制也有所不同。
  • 常用的SQL数据库:MySQL 、SQL Server 、Oracle 、Sybase 、DB2(IBMDB2) 、PostgreSQL 、GaussDB

DML语句(数据操纵语言)用法部分

增删改查(CURD)

插入数据

插入完整的行

INSERT INTO user
VALUES (10, 'root', 'root', 'xxxx@163.com');

插入行的一部分

INSERT INTO user(username, password, email)
VALUES ('admin', 'admin', 'xxxx@163.com');

插入查询出来的数据

INSERT INTO user(username)
SELECT name
FROM account;

更新数据

UPDATE 语句用于更新表中的记录

UPDATE user
SET username='robot', password='robot'
WHERE username = 'root';

删除数据

  • DELETE 语句用于删除表中的记录。
  • TRUNCATE TABLE 可以清空表,也就是删除所有行。

删除表中的指定数据

DELETE FROM user
WHERE username = 'robot';

清空表中的数据

TRUNCATE TABLE user;

DQL语句(数据查询语言)用法部分

查询数据

  • SELECT 语句用于从数据库中查询数据。
  • DISTINCT 用于返回唯一不同的值。它作用于所有列,也就是说所有列的值都相同才算相同。
  • LIMIT
  • 限制返回的行数。可以有两个参数,第一个参数为起始行,从 0 开始;第二个参数为返回的总行数。
  • ASC :升序(默认)
  • DESC :降序

查询单列

SELECT prod_name
FROM products;

查询多列

ELECT *
FROM products;

查询不同值

SELECT DISTINCT
vend_id FROM products;

限制查询结果

-- 返回前 5 行
SELECT * FROM mytable LIMIT 5;
SELECT * FROM mytable LIMIT 0, 5;
-- 返回第 3 ~ 5 行
SELECT * FROM mytable LIMIT 2, 3;

子查询

  • 子查询是嵌套在较大查询中的 SQL 查询。
  • 子查询也称为内部查询或内部选择,而包含子查询的语句也称为外部查询或外部选择。
  • 子查询可以嵌套在 SELECT,INSERT,UPDATE 或 DELETE 语句内或另一个子查询中。
  • 子查询通常会在另一个 SELECT 语句的 WHERE 子句中添加。
  • 您可以使用比较运算符,如 >,<,或 =。比较运算符也可以是多行运算符,如IN,ANY 或 ALL。
  • 子查询必须被圆括号 () 括起来。
  • 内部查询首先在其父查询之前执行,以便可以将内部查询的结果传递给外部查询。执行过程可以参考下图:
    在这里插入图片描述

子查询的子查询

SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
                  FROM orders
                  WHERE order_num IN (SELECT order_num
                                      FROM orderitems
                                      WHERE prod_id = 'RGAN01'));

WHERE

  • WHERE 子句用于过滤记录,即缩小访问数据的范围。
  • WHERE 后跟一个返回 true 或 false 的条件。
  • WHERE 可以与 SELECT,UPDATE 和 DELETE 一起使用。
  • 可以在 WHERE 子句中使用的操作符
    在这里插入图片描述

SELECT 语句中的 WHERE 子句

SELECT * FROM Customers
WHERE cust_name = 'Kids Place';

UPDATE 语句中的 WHERE 子句

UPDATE Customers
SET cust_name = 'Jack Jones'
WHERE cust_name = 'Kids Place';

DELETE 语句中的 WHERE 子句

DELETE FROM Customers
WHERE cust_name = 'Kids Place';

IN 和 BETWEEN

  • IN 操作符在 WHERE 子句中使用,作用是在指定的几个特定值中任选一个值。
  • BETWEEN 操作符在 WHERE 子句中使用,作用是选取介于某个范围内的值。

IN示例

SELECT *
FROM products
WHERE vend_id IN ('DLL01', 'BRS01');

between示例

SELECT *
FROM products
WHERE prod_price BETWEEN 3 AND 5;

AND、OR、NOT

  • AND、OR、NOT 是用于对过滤条件的逻辑处理指令。
  • AND 优先级高于 OR,为了明确处理顺序,可以使用 ()。
  • AND 操作符表示左右条件都要满足。
  • OR 操作符表示左右条件满足任意一个即可。
  • NOT 操作符用于否定一个条件。

AND示例

SELECT prod_id, prod_name, prod_price
FROM products
WHERE vend_id = 'DLL01' AND prod_price <= 4;

OR示例

SELECT prod_id, prod_name, prod_price
FROM products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

NOT示例

SELECT *
FROM products
WHERE prod_price NOT BETWEEN 3 AND 5;

LIKE

  • LIKE 操作符在 WHERE 子句中使用,作用是确定字符串是否匹配模式。
  • 只有字段是文本值时才使用 LIKE。
  • LIKE 支持两个通配符匹配选项:% 和 _。
  • 不要滥用通配符,通配符位于开头处匹配会非常慢。
  • % 表示任何字符出现任意次数。
  • _ 表示任何字符出现一次。

%示例

SELECT prod_id, prod_name, prod_price
FROM products
WHERE prod_name LIKE '%bean bag%';

_示例

SELECT prod_id, prod_name, prod_price
FROM products
WHERE prod_name LIKE '__ inch teddy bear';

连接和组合

连接(JOIN)

  • 如果一个 JOIN 至少有一个公共字段并且它们之间存在关系,则该 JOIN 可以在两个或多个表上工作。
  • 连接用于连接多个表,使用 JOIN 关键字,并且条件语句使用 ON 而不是 WHERE。
  • JOIN 保持基表(结构和数据)不变。
  • JOIN 有两种连接类型:内连接和外连接。
    • 内连接又称等值连接,使用 INNER JOIN关键字。在没有条件语句的情况下返回笛卡尔积。
      • 自连接可以看成内连接的一种,只是连接的表是自身而已。
      • 自然连接是把同名列通过 = 测试连接起来的,同名列可以有多个。
      • 内连接 vs 自然连接:内连接提供连接的列,而自然连接自动连接所有同名列。
    • 外连接返回一个表中的所有行,并且仅返回来自次表中满足连接条件的那些行,即两个表中的列是相等的。外连接分为左外连接、右外连接、全外连接(Mysql 不支持)。
      • 左外连接就是保留左表没有关联的行。、
      • 右外连接就是保留右表没有关联的行。
  • 连接 vs 子查询:连接可以替换子查询,并且比子查询的效率一般会更快。
    在这里插入图片描述

内连接(INNER JOIN)

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

自连接

SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM customers c1, customers c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';

自然连接(NATURAL JOIN)

SELECT *
FROM Products
NATURAL JOIN Customers;

左连接(LEFT JOIN)

SELECT customers.cust_id, orders.order_num
FROM customers LEFT JOIN orders
ON customers.cust_id = orders.cust_id;

右连接(RIGHT JOIN)

SELECT customers.cust_id, orders.order_num
FROM customers RIGHT JOIN orders
ON customers.cust_id = orders.cust_id;

组合(UNION)

  • UNION 运算符将两个或更多查询的结果组合起来,并生成一个结果集,其中包含来自 UNION 中参与查询的提取行。
  • 基本规则
    • 所有查询的列数和列顺序必须相同。
    • 每个查询中涉及表的列的数据类型必须相同或兼容。
    • 通常返回的列名取自第一个查询。
  • 默认会去除相同行,如果需要保留相同行,使用 UNION ALL。
  • 只能包含一个 ORDER BY 子句,并且必须位于语句的最后。
  • 应用场景
    • 在一个查询中从不同的表返回结构数据。
    • 对一个表执行多个查询,按一个查询返回数据。

组合查询

SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_state IN ('IL', 'IN', 'MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_name = 'Fun4All';

JOIN vs UNION

  • JOIN 中连接表的列可能不同,但在 UNION 中,所有查询的列数和列顺序必须相同。
  • UNION 将查询之后的行放在一起(垂直放置),但 JOIN 将查询之后的列放在一起(水平放置),即它构成一个笛卡尔积。

函数


排序和分组

ORDER BY

ORDER BY
  • 用于对结果集进行排序。
    • ASC :升序(默认)
    • DESC :降序
  • 可以按多个列进行排序,并且为每个列指定不同的排序方式
    指定多个列的排序方向
SELECT * FROM products
ORDER BY prod_price DESC, prod_name ASC;

GROUP BY

  • GROUP BY 子句将记录分组到汇总行中。
  • GROUP BY 为每个组返回一个记录。
  • GROUP BY 通常还涉及聚合:COUNT,MAX,SUM,AVG 等。
  • GROUP BY 可以按一列或多列进行分组。
  • GROUP BY 按分组字段进行排序后,ORDER BY 可以以汇总字段来进行排序。

分组

SELECT cust_name, COUNT(cust_address) AS addr_num
FROM Customers GROUP BY cust_name;

分组后排序

SELECT cust_name, COUNT(cust_address) AS addr_num
FROM Customers GROUP BY cust_name
ORDER BY cust_name DESC;

HAVING

  • HAVING 用于对汇总的 GROUP BY 结果进行过滤。
  • HAVING 要求存在一个 GROUP BY 子句。
  • WHERE 和 HAVING 可以在相同的查询中。
  • HAVING vs WHERE
    • WHERE 和 HAVING 都是用于过滤。
    • HAVING 适用于汇总的组记录;而 WHERE 适用于单个记录。

使用 WHERE 和 HAVING 过滤数据

SELECT cust_name, COUNT(*) AS num
FROM Customers
WHERE cust_email IS NOT NULL
GROUP BY cust_name
HAVING COUNT(*) >= 1;

TCL语句(事务控制语言)用法部分

事务处理

事务在数据库中的作用是确保一组相关的操作要么全部执行成功,要么全部不执行。这有助于维护数据库的一致性、可靠性和完整性。以下是事务运行的基本过程和在实际项目中的作用:

事务运行的基本过程:

  1. 开始事务: 事务的执行始于 BEGIN TRANSACTION 命令。开始事务后,所有后续的数据库操作都被看作是一组原子操作。

  2. 执行数据库操作: 在事务中执行一系列数据库操作,比如插入数据、更新数据、删除数据等。这些操作形成了一个数据库操作序列。

  3. 判断事务成功或失败: 在事务执行期间,如果任何一个操作失败,系统会判定整个事务失败。

  4. 提交事务: 如果事务中的所有操作都成功执行,可以通过 COMMIT 命令提交事务,使得所有的修改永久生效。

  5. 回滚事务: 如果事务中的任何操作失败或者发生错误,可以通过 ROLLBACK 命令回滚事务,取消所有的修改,使数据库回到事务开始前的状态。

在实际项目中的作用:

  1. 确保数据一致性: 事务保证了数据库从一个一致的状态到另一个一致的状态的过渡。如果事务中的任何操作失败,数据库会回滚到事务开始前的状态,确保数据的一致性。

  2. 处理并发操作: 在多用户并发访问数据库的环境中,事务隔离性确保了每个事务的操作互相独立,避免了相互干扰和数据冲突。

  3. 支持原子操作: 事务确保一组操作要么全部执行成功,要么全部失败。这对于执行一系列相关的数据库操作,如转账等,是非常关键的。

  4. 持久性保证: 事务的持久性特性确保了一旦事务提交,其结果将永久存储在数据库中,即使系统故障也不会导致数据丢失。

  5. 提高系统可靠性: 通过使用事务,可以更好地处理系统故障和异常,从而提高系统的可靠性和稳定性。

总的来说,事务是数据库管理系统中非常重要的概念,它为开发人员提供了一种有效的方式来维护数据的完整性和一致性,特别是在复杂的应用程序中,事务管理对于确保数据的正确性至关重要。

  • 不能回退 SELECT 语句,回退 SELECT 语句也没意义;也不能回退 CREATE 和 DROP 语句。
  • MySQL 默认是隐式提交,每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 START TRANSACTION 语句时,会关闭隐式提交;当 COMMIT 或 ROLLBACK 语句执行后,事务会自动关闭,重新恢复隐式提交。
  • 通过 set autocommit=0 可以取消自动提交,直到 set autocommit=1 才会提交;autocommit 标记是针对每个连接而不是针对服务器的。
  • 指令
    • START TRANSACTION - 指令用于标记事务的起始点。
    • SAVEPOINT - 指令用于创建保留点。
    • ROLLBACK TO - 指令用于回滚到指定的保留点;如果没有设置保留点,则回退到 START TRANSACTION 语句处。
    • COMMIT - 提交事务。
-- 开始事务
START TRANSACTION;
 
-- 插入操作 A
INSERT INTO `user`
VALUES (1, 'root1', 'root1', 'xxxx@163.com');
 
-- 创建保留点 updateA
SAVEPOINT updateA;
 
-- 插入操作 B
INSERT INTO `user`
VALUES (2, 'root2', 'root2', 'xxxx@163.com');
 
-- 回滚到保留点 updateA
ROLLBACK TO updateA;
 
-- 提交事务,只有操作 A 生效
COMMIT;

DCL语句(数据控制语言)用法部分

权限控制

  • GRANT 和 REVOKE 可在几个层次上控制访问权限:
    • 整个服务器,使用 GRANT ALL 和 REVOKE ALL;
    • 整个数据库,使用 ON database.*;
    • 特定的表,使用 ON database.table;
    • 特定的列;
    • 特定的存储过程。
  • 新创建的账户没有任何权限。
  • 账户用 username@host 的形式定义,username@% 使用的是默认主机名。
  • MySQL 的账户信息保存在 mysql 这个数据库中。
USE mysql;
SELECT user FROM user;

创建账户

CREATE USER myuser IDENTIFIED BY 'mypassword';

修改账户名

UPDATE user SET user='newuser' WHERE user='myuser';
FLUSH PRIVILEGES;

删除账户

DROP USER myuser;

查看权限

SHOW GRANTS FOR myuser;

授予权限

GRANT SELECT, INSERT ON *.* TO myuser;

删除权限

REVOKE SELECT, INSERT ON *.* FROM myuser;

更改密码

SET PASSWORD FOR myuser = 'mypass';

存储过程

游标

触发器

文章来源:https://blog.csdn.net/Lenhart001/article/details/135617001
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。