本文遵循BY-SA版权协议，转载请附上原文出处链接。

本文作者: 黑伴白

本文链接: http://heibanbai.com.cn/posts/a193c35f/

SQL语句学习

什么是 SQL

SQL，全称Structured Query Language，意为结构化查询语言，是用于操作关系数据库（MySQL、Oracle、SQL Server 等）的编程语言。

SQL语句基础语法规范

• SQL语句仅可识别半角字符；
• 一条完整的SQL语句以分号结尾，但可以分单行或多行书写；
• SQL关键字不区分大小写，但为了与非关键字作区分，建议使用大写；

SQL 分类

• DDL：数据定义语言，用于定义和管理SQL数据库中的表结构和索引。
• DML：数据操作语言, 用于对数据库进行增、删、改、查这些操作。
• DCL：数据控制语言，主要用于控制不同权限的数据库用户对数据库表、视图等的访问。
• DQL：数据查询语言，用于从数据库获取信息，它也是大多数终端用户及应用程序员最经常使用的SQL语言。

DDL（Data Definition Language）：数据定义语言

操作数据库

创建库CREATE DATABASE

CREATE DATABASE test;

删除库DROP DATABASE

DROP DATABASE test;

修改库ALTER DATABASE

ALTER DATABASE test COLLATE Chinese_PRC_CI_AS;

数据类型

数字型（int，tinyint，decimal，float等）、日期和时间类型（Date，TIME等）、字符串类型（char，varchar等）。

CREATE TABLE NumTable (
    id INT,
    smallNum TINYINT,
    money DECIMAL(10,2),
    size FLOAT
);

CREATE TABLE DateTable (
    date_column DATE,
    time_column TIME
);

CREATE TABLE StringTable (
    fixed_length_name CHAR(50),
    variable_length_address VARCHAR(255)
);

CREATE TABLE Employee (
	id INT NOT NULL COMMENT '员工号',
	department_id varchar(100) NULL COMMENT '部门ID',
	FirstName varchar(100) NULL,
	LastName varchar(100) NULL,
	Name varchar(200) NULL COMMENT '姓名',
	Age INT NULL COMMENT '年龄',
	Department varchar(255) NULL COMMENT '部门',
	JobTitle varchar(255) NULL COMMENT '职位',
	Salary DECIMAL NULL COMMENT '薪资',
	EmpDate DATE NULL COMMENT '入职日期',
	CreateTime DATETIME NULL COMMENT '录入时间',
	ModifyTime TIMESTAMP NULL COMMENT '修改时间',
	Remark varchar(255) NULL COMMENT '备注',
	CONSTRAINT Employee_PK PRIMARY KEY (id)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE test.Orders (
	OrderID varchar(100) NOT NULL COMMENT '订单ID',
	CustomerID varchar(100) NULL COMMENT '客户ID',
	Remark varchar(100) NULL COMMENT '备注',
	CONSTRAINT Orders_PK PRIMARY KEY (OrderID)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci
COMMENT='订单表';

CREATE TABLE test.Customers (
	CustomerID varchar(100) NOT NULL COMMENT '客户ID',
	CustomerName varchar(100) NULL COMMENT '客户姓名',
	Remark varchar(100) NULL COMMENT '备注',
	CONSTRAINT Customers_PK PRIMARY KEY (CustomerID)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci
COMMENT='客户表';

CREATE TABLE test.Attendance (
	id INT NOT NULL COMMENT '员工号',
	WorkDay DATE NOT NULL COMMENT '打卡日',
	Remark varchar(100) NULL COMMENT '备注',
	CONSTRAINT Attendance_PK PRIMARY KEY (id,WorkDay)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci
COMMENT='考勤表';

操作表

创建表CREATE TABLE，删除表DROP TABLE，修改表ALTER TABLE，重命名表 RENAME TABLE。

CREATE TABLE TestTable (
    ID int,
	Name varchar(255),
	Age int
);

DROP TABLE TestTable;

ALTER TABLE TestTable ADD Email varchar(255);

ALTER TABLE TestTable MODIFY COLUMN Age smallint;

ALTER TABLE TestTable DROP COLUMN Age;

RENAME TABLE TestTable TO TmpTable;

ALTER TABLE test.Employee CHANGE Remark City varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '备注';
ALTER TABLE test.Employee MODIFY COLUMN City varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '城市';

DML（Data Manipulation Language）：数据操作语言

插入数据

利用INSERT INTO语句添加一条或多条记录。

INSERT INTO TestTable (id, name, department_id) VALUES (1, 'Li Ming', 101);
INSERT INTO TestTable (name, department_id) VALUES ('Wang Gang', 102);
INSERT INTO TestTable VALUES (3, 'Zhang San', 103);
INSERT INTO Employee (id, department_id, FirstName, LastName, Name, Age, Department, JobTitle, Salary, EmpDate, CreateTime, ModifyTime, Remark) VALUES(1, 'DEP_001', 'Xiao', 'Bai', 'XiaoBai', 27, '销售部', '销售经理', 10000, '2018-08-30', '2018-08-30 00:00:00', '2018-08-30 00:00:00', NULL);

修改数据

用UPDATE语句可以修改表中的数据。

UPDATE employees SET department_id = 201 WHERE name = 'LiMing';
UPDATE employees SET name = 'LiuYan' WHERE id = 2;
UPDATE employees SET name = 'ZhuRongji', department_id = 'DEP_202' WHERE id = 3;

删除数据

DELETE FROM语句用于在表中删除一条或者多条记录。

DELETE FROM employees WHERE id = 1;
DELETE FROM employees WHERE name = 'LiGang';
DELETE FROM employees WHERE department_id = 'DEP_202';

DCL（Data Control Language）：数据控制语言

创建用户

使用CREATE USER语句创建新的数据库用户。

--在"localhost"上创建一个名为"newuser"的新用户，密码是"password"
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

给用户授权

使用GRANT语句为用户分配访问权限。

-- 在localhost上给"username"用户赋予运database_name数据库中table_name表的SELECT，INSERT和DELETE权限
GRANT SELECT, INSERT, DELETE ON database_name.table_name TO 'username'@'localhost';

撤销授权

使用REVOKE语句撤销用户的访问权限。

-- 在localhost上撤销"username"用户对database_name数据库中table_name表的INSERT权限
REVOKE INSERT ON database_name.table_name FROM 'username'@'localhost';

查看用户权限

使用SHOW GRANTS语句查看用户当前的权限列表。

-- 显示"localhost"上"username"用户的所有权限
SHOW GRANTS FOR 'username'@'localhost';

删除用户

用DROP USER语句来删除一个用户。

-- 删除"localhost"上名为"username"的用户
DROP USER 'username'@'localhost';

修改用户密码（以root身份）

通过ALTER USER语句可以修改用户的密码。

-- 修改在"localhost"上username用户的密码为"newpassword"
ALTER USER 'username'@'localhost' IDENTIFIED BY 'newpassword';

DQL（Data Query Language）：数据查询语言

基础查询

利用SELECT 语句可以查询数据库中的数据。

-- 查询Employees表中的所有记录
SELECT * FROM Employee;
-- 查询Employee表中的FirstName和LastName字段所有记录
SELECT FirstName, LastName FROM Employee;

条件查询

在 SQL 中，AND 和 OR 操作符的优先级与常见的布尔逻辑相同，AND 操作符的优先级高于 OR 操作符。

通过使用WHERE子句来设定查询条件。

-- 查询Employee表中Salary大于5000的所有记录
SELECT * FROM Employee WHERE Salary > 5000;
-- 查询Employee表中年龄小于等于30的员工的firstName和lastName字段记录
SELECT FirstName, LastName FROM Employee WHERE Age <= 30;
-- 查询Employee表中Salary大于5000且年龄小于等于30的所有记录
SELECT * FROM Employee WHERE Salary > 5000 AND Age <= 30;
-- 查询Employee表中Salary大于5000或年龄小于等于30的所有记录
SELECT * FROM Employee WHERE Salary > 5000 OR Age <= 30;

模糊查询

使用LIKE子句，配合通配符%和_进行模糊查询。

% 匹配任意字符序列（包括空字符序列）

_ 匹配任意单个字符

-- 查询Employee表中FirstName以John开头的所有记录
SELECT * FROM Employee WHERE FirstName LIKE 'John%';
-- 查询Employee表中FirstName包含John的所有记录
SELECT * FROM Employee WHERE FirstName LIKE '%John%';
-- 查询Employee表中LastName以son+一个任意字符结束的所有记录
SELECT FirstName, LastName FROM Employee WHERE LastName LIKE '%son_';

字段控制查询

运用DISTINCT关键字进行去重查询。

-- 查询Employee表中，City字段去重之后的所有城市记录
SELECT DISTINCT City FROM Employee;
-- 查询Employee表中，Age小于50岁的员工的Age和Salary字段组合记录，并进行去重处理
SELECT DISTINCT Age, Salary FROM Employee WHERE Age<50;

排序

用ORDER BY子句按照一个或多个列进行排序。

单列排序：

-- 按'工资'列的升序返回Employee表中的所有行
SELECT * FROM Employee ORDER BY Salary;

多列排序：

-- 首先根据'工资'列的升序对Employee表中的行进行排序，然后在工资相同的情况下，根据'Age'列的降序进行排序
SELECT * FROM Employee ORDER BY Salary, Age DESC;

聚合函数

包括COUNT，SUM，MAX，MIN，AVG等函数。

COUNT：

-- 返回Employee表的总行数
SELECT COUNT(*) FROM Employee;

SUM：

-- 返回Employee表中所有员工的薪水总和
SELECT SUM(Salary) FROM Employee;

MAX：

-- 返回Employee表中员工的最大年龄
SELECT MAX(Age) FROM Employee;

MIN：

-- 返回Employee表中员工的最小年龄
SELECT MIN(Age) FROM Employee;

AVG：

-- 返回Employee表中员工的平均薪水
SELECT AVG(Salary) FROM Employee;

分组查询(HAVING)

GROUP BY子句是用于结合聚合函数，依据一个或多个列进行分组。

按单列分组：

-- 返回每个部门的员工数量
SELECT Department, COUNT(*) FROM Employee GROUP BY Department;

按多列分组：

-- 返回每个部门及职位下员工的平均薪水
SELECT Department, JobTitle, AVG(Salary) FROM Employee GROUP BY Department, JobTitle;

分组后过滤：

HAVING子句在SQL中用于对GROUP BY子句生成的分组结果进行过滤。它类似于WHERE子句，但是可以用于过滤GROUP BY分组后的结果集，而不是原始表中的行。HAVING的这个特性,如果前面对字段起了别名，后面的having是可以直接使用别名来限制的 ，但是where不可以，只能采用数据库原有的数据字段

在这个语法中，首先使用GROUP BY子句对数据进行分组，然后使用HAVING子句对分组结果进行筛选。只有满足条件的分组才会包含在查询结果中

-- 返回每个部门及职位下员工的平均薪水大于5000的部分
SELECT Department, JobTitle, AVG(Salary) FROM Employee GROUP BY Department, JobTitle HAVING AVG(Salary) > 5000;

LIMIT

用来限定查询结果的起始行，以及总行数。

简单LIMIT：

-- 返回前10个工资最高的员工
SELECT * FROM Employee ORDER BY Salary DESC LIMIT 10;

带偏移量的LIMIT：

-- 返回工资排名第6到第15的10个员工
SELECT * FROM Employee ORDER BY Salary DESC LIMIT 10 OFFSET 5;

ROUND

ROUND函数用于将数字四舍五入到指定的小数位数。它通常用于处理浮点数，以便在进行计算或显示时保留指定的精度。

以下是ROUND函数的基本语法和示例：

SELECT ROUND(number, decimals)

其中：

• number 是要四舍五入的数字。
• decimals 是要保留的小数位数，可以是正数表示四舍五入到小数点右侧的位数，负数表示四舍五入到小数点左侧的位数（即将数字变成最接近的整数）。

示例：

四舍五入到整数：

SELECT ROUND(12.345); -- 结果为 12

四舍五入到一位小数：

SELECT ROUND(12.345, 1); -- 结果为 12.3

四舍五入到两位小数：

SELECT ROUND(12.345, 2); -- 结果为 12.35

四舍五入到整数位：

SELECT ROUND(12.345, -1); -- 结果为 10

在计算中使用ROUND：

SELECT ROUND(5/3.0, 2); -- 结果为 1.67

多表连接查询

内连接：INNER JOIN

INNER JOIN用于根据两个或多个表之间的相关列的值来结合这些表中的行。它会返回满足连接条件的行，即两个表中的相关列的值相等的行

SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

左连接：LEFT JOIN

左表在左连接（left join）中指的是在查询语句中写在LEFT JOIN关键字左边的表。LEFT JOIN会返回左表中的所有行，无论是否在右表中找到匹配的行。如果右表中没有匹配的行，则结果集中右表的列将包含NULL值

SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

右连接：RIGHT JOIN

返回右表中所有行，以及左表中符合连接条件的行

SELECT Orders.OrderID, Customers.CustomerName FROM Orders RIGHT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

全外连接：FULL JOIN

返回两个表中所有行，如果某行在一个表中没有匹配，另一个表中也没有匹配，则用NULL填充

SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

笛卡尔积：CROSS JOIN

SELECT Products.ProductName, Suppliers.SupplierName FROM ProductsCROSS JOIN Suppliers;

笛卡尔积是集合论中的一个基本概念，由法国数学家笛卡尔（René Descartes）首次引入。它描述了两个集合之间所有可能的有序对的集合。在笛卡尔积中，每个元素都与另一个集合中的每个元素形成一对，这样就生成了所有可能的组合。

在数据库中，笛卡尔积用于联接操作，其中两个表的笛卡尔积被用来生成所有可能的组合，然后通过条件筛选出需要的结果。

UNION 和 UNION ALL

UNION 和 UNION ALL 用于将两个或多个 SELECT 语句的结果合并成一个结果集。它们的区别在于对重复行的处理方式。

• UNION：UNION 操作符将两个或多个 SELECT 语句的结果合并，并去除重复的行。如果某一行在多个 SELECT 语句的结果中出现了多次，那么在 UNION 结果中只会保留一次。

• UNION ALL：UNION ALL 操作符也将两个或多个 SELECT 语句的结果合并，但是不会去除重复的行。它会保留所有的行，即使某一行在多个 SELECT 语句的结果中出现了多次，也会在 UNION ALL 结果中保留多次。

因此，如果你希望合并两个结果集并且不关心重复行，可以使用 UNION ALL，这样可以获得更好的性能。如果你希望合并两个结果集并且确保结果集中不包含重复的行，可以使用 UNION。

CASE

CASE函数用于根据条件返回不同的值。它类似于其他编程语言中的switch或if-else语句。

select
	case
		when condition1 then result1
		when condition2 then result2...
		else default_result
	end as alias
from
	table_name

在这个语法中：

• condition1, condition2等是条件，用于确定返回哪个结果。
• result1, result2等是在条件为真时返回的结果。
• default_result是当所有条件都不满足时返回的默认值。
• alias是为返回的结果指定的别名。

窗口函数

窗口函数的语法如下所示：

<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)

• <窗口函数>：窗口函数可以是专用窗口函数（例如 rank(如果有并列名次的行，会占用下一名次的位置 1 1 3)、dense_rank(如果有并列名次的行，不占用下一名次的位置 1 1 2)、

  row_number(如果有并列名次的行，也不考虑并列名次的情况 1 2 3) 等）或聚合函数（例如 sum、avg、count、max、min 等）。

• over：表示使用窗口函数的开始，后面跟随括号内的内容。

• partition by：用于指定窗口函数操作的分组条件，即将数据划分成不同的分组。

• order by：用于指定窗口函数内部排序的规则，即按照哪些列对分组内的数据进行排序。

在使用窗口函数时，需要注意以下几点：

• 分组条件是可选的，如果不指定(不写partition by )，则窗口函数将对整个结果集进行计算。
• 排序规则也是可选的，如果不指定(不写order by)，则窗口函数将在分组内按照未指定的顺序进行计算。

示例：

-- 使用rank窗口函数对销售额进行排名，按部门分组
select
	department,
	amount,
	rank() over (partition by department order by amount desc) as sales_rank
from sales;

在这个示例中，rank() 是窗口函数，用于计算每个部门的销售额排名。over 子句指定了窗口函数的操作范围，包括分组条件（按部门分组）和排序规则（按销售额降序排列）。

SQL编程建议

• 使用标准的SQL关键字：使用标准的SQL关键字，比如 SELECT, INSERT, UPDATE, DELETE, WHERE 等。
• 遵循语法规则：SQL语句需要遵循一定的语法规则，比如 SELECT 语句后面应该跟了表名和要查询的列名。
• 使用合适的数据类型：在创建表格或添加数据时，应选择正确和适合的数据类型。
• 注释的使用：注释可以帮助他人或者自己理解代码，包括在单行中使用 “–” 或者在多行中使用 “/* … */“ 。
• 使用适当的排序：使用 ORDER BY 语句以对结果进行适当的排序。
• 区分大小写：虽然大部分SQL平台都是不区分大小写的，但是要注意某些平台可能仍然做大小写区分。
• 合理使用索引：使用索引可以加快查询速度，但要注意不要过度使用，因为索引会占用磁盘空间，并且在插入、删除和更新操作时可能使性能下降。
• 防止SQL注入：在编写可执行参数的SQL语句时，确保参数已正确转义。
• 利用子查询：子查询可以在一个SQL语句中执行另一个SQL语句，帮助解决复杂的查询问题。
• 适当使用表别名：为表设置适当的表别名，不仅可以减少SQL语句的复杂度，也能尽量避免出错，让SQL语句更加的直观、易理解。

---

蚂蚁再小也是肉🥩！