一、关于SQL

1.1 简介

SQL（Structured Query Language，结构化查询语言）是一种用于管理关系型数据库的标准编程语言。它主要用于数据的查询、插入、更新和删除等操作。SQL最初在1970年代由IBM的研究人员开发，旨在处理关系数据模型。

MySQL 支持 SQL，用于对数据库进行查询、更新和管理。

在项目开发中，程序员需要掌握sql各种用法，应对各种复杂需求，务必熟练。

‍

1.2 发展

1970年代：SQL的前身，称为SEQUEL（Structured English Query Language），由IBM的Donald D. Chamberlin和Raymond F. Boyce开发。
1980年代：SQL成为关系数据库管理系统的标准语言。1986年，美国国家标准协会（ANSI）发布了第一个SQL标准。
1987年：国际标准化组织（ISO）采纳了SQL作为国际标准。
1990年代：随着互联网的兴起，SQL在Web应用中变得更加重要。SQL-92标准引入了更多的功能，如存储过程、触发器和面向对象的特性。
2000年代：SQL继续发展，引入了更多的数据类型和功能，以支持XML、JSON和其他现代数据格式。SQL:1999、SQL:2003和SQL:2006等标准相继发布。
2010年代：随着大数据和云计算的兴起，SQL被扩展以支持更大规模的数据集和分布式数据库系统。SQL:2011和SQL:2016等标准进一步增强了SQL的功能。

‍

1.3 功能

数据查询：使用SELECT语句从数据库中检索数据。
数据操作：包括INSERT、UPDATE和DELETE用于增加、修改和删除数据。
数据定义：使用CREATE、ALTER和DROP语句定义和修改数据库结构。
数据控制：通过GRANT和REVOKE管理用户权限。

‍

1.4 基本概念

数据库：数据的集合，通常以表格形式存储。
表：数据库中的基本单元，由行和列组成。
行：表中的一条记录。
列：表中数据的属性或字段。

‍

1.5 分类

1. 数据定义语言（DDL）

用于定义和管理数据库的结构，包括创建、修改和删除数据库对象（如表、索引等）。

常用命令：

CREATE：创建数据库或表。
ALTER：修改已有的数据库或表结构。
DROP：删除数据库或表。
TRUNCATE：清空表中的数据，但不删除表结构。

2. 数据操作语言（DML）

用于对数据库中的数据进行操作，包括插入、更新和删除记录。

常用命令：

INSERT：插入新记录。
UPDATE：更新已有记录。
DELETE：删除记录。
MERGE：合并数据（在一些数据库中可用）。

3. 数据查询语言（DQL）

用于从数据库中查询和检索数据，主要通过SELECT语句实现。

常用命令：

SELECT：查询数据，可以使用WHERE、ORDER BY、GROUP BY、HAVING等子句来过滤和排序结果。

4. 数据控制语言（DCL）

用于控制对数据库中数据的访问权限和安全性。

常用命令：

GRANT：授权用户对数据库对象的访问权限。
REVOKE：撤销用户对数据库对象的访问权限。

5. 事务控制语言（TCL）

用于处理数据库事务，确保数据的一致性和完整性。

常用命令：

COMMIT：提交事务，保存所有的更改。
ROLLBACK：回滚事务，撤销未提交的更改。
SAVEPOINT：设置一个事务中的保存点，以便在需要时进行回滚。

6. 扩展语言

一些数据库系统提供的扩展SQL语言，用于特定功能，如存储过程、触发器等。

示例：

CREATE PROCEDURE：创建存储过程。
CREATE TRIGGER：创建触发器。

‍

1.6 注释

在 MySQL 中，注释用于在 SQL 代码中添加说明性文字，以帮助开发者理解代码的作用。注释不会被数据库执行，可以用来解释复杂的 SQL 查询或记录开发过程中的想法。MySQL 支持多种形式的注释，主要包括以下几种：

1. 单行注释

使用 -- ：在 -- 后面可以加上空格，直到行尾的内容都会被视为注释。 示例：

SELECT * FROM employees; -- 选择所有员工数据

使用 # ：也可以用 # 来表示单行注释。 示例：

SELECT * FROM employees; # 获取员工列表

2. 多行注释

使用 /* ... */ ：这种方式可以用于编写多行注释，任何位于这对符号之间的内容都会被视为注释。 示例：

这是一个多行注释

用于描述下面的查询

SELECT * FROM employees;

‍

注释的使用场景

解释复杂的 SQL 查询：在编写复杂的 SQL 语句时，注释可以帮助其他开发者（或自己）理解逻辑。
记录修改历史：在修改 SQL 脚本时，可以使用注释记录修改的原因和时间。
调试代码：在调试过程中，可以通过注释掉某些行来快速排查问题。

‍

注意事项

注释不能嵌套。例如，/* this is a /* nested */ comment */ 是不被允许的。
在 SQL 查询中，注释的使用不会影响查询的执行，但过多的注释可能会使代码变得难以阅读。

‍

二、数据定义语言（DDL）

2.1 `CREATE`

SQL 中的 CREATE 命令用于创建新的数据库对象，包括数据库、表、视图、索引等。

‍

创建库

CREATE DATABASE test;

USE test;

‍

创建表

CREATE TABLE mytable (

id INT NOT NULL AUTO_INCREMENT,

col1 INT NOT NULL DEFAULT 1,

col2 VARCHAR(45) NULL,

col3 DATE NULL,

PRIMARY KEY (`id`));

‍

创建索引

使用 CREATE INDEX 命令为表中的一列或多列创建索引，以提高查询性能。

CREATE INDEX index_name ON table_name (column1, column2, ...);

示例：

CREATE INDEX idx_student_name ON students (name);

‍

2.2 DROP-删除

SQL 中的 DROP 命令用于删除数据库对象，包括数据库、表、视图、索引等。使用 DROP 命令时，相关的所有数据和结构将被永久删除，因此在执行之前需谨慎。

‍

删除表

DROP TABLE mytable;

‍

删除索引

使用 DROP INDEX 命令来删除一个索引。

DROP INDEX index_name ON table_name;

示例：

DROP INDEX idx_student_name ON students;

‍

2.3 TRUNCATE—清空表

TRUNCATE是SQL中的一个命令，用于快速删除表中的所有记录。

Truncate是一个能够快速清空资料表内所有资料的SQL语法。并且能针对具有自动递增值的字段，做计数重置归零重新计算的作用。

‍

语法

TRUNCATE TABLE table_name;

当需要清空表并且重置自动递增ID时，我们可以使用SQL中的Truncate命令。Truncate与DELETE操作非常相似，但是具有更高的效率。因为它不会记录删除行的操作日志，并且仅将表截断到指定的位置，因此它会更快地执行表清空操作。

‍

示例

TRUNCATE TABLE students;

执行这个命令后，students表将被完全清空，自动递增ID计数器将被重置为1。

‍

2.4 ALTER-修改

SQL 中的 ALTER 命令用于修改现有数据库对象的结构，主要用于表（table）的更改。

‍

新增列

ALTER TABLE 表名

ADD 列名数据类型(长度) 位置;

其中，表名是你想要修改的表的名称，列名是你想要新增的字段名，数据类型是字段的数据类型，如INT、VARCHAR、DATETIME等，长度是数据类型的长度，比如VARCHAR(255)，位置是可选的，指定新列在表中的位置，可以是FIRST、AFTER 列名或者LAST。

‍

示例

例如，如果你有一个名为students的表，现在想要新增一个名为age的字段，数据类型为INT，并且希望这个字段在所有字段的最后，你可以使用以下SQL语句：

ALTER TABLE students

ADD age INT;

如果你想要在name字段之后添加age字段，可以使用：

ALTER TABLE students

ADD age INT AFTER name;

如果你想要在表中的第一个位置添加age字段，可以使用：

ALTER TABLE students

ADD age INT FIRST;

请注意，在执行这些操作之前，确保你有足够的权限来修改数据库表，并且在对生产环境的数据库进行修改之前，应该先在测试环境中进行验证和备份。

‍

ALTER TABLE select_goods_policy_site

ADD COLUMN is_default TINYINT UNSIGNED COMMENT '默认政策：0-否；1-是' AFTER status;

‍

删除列

语法

ALTER TABLE your_table_name

DROP COLUMN column_to_delete;

‍

示例

ALTER TABLE select_goods_policy_site

DROP COLUMN is_default;

‍

修改列和属性

ALTER TABLE table_name

MODIFY column_name new_data_type;

ALTER TABLE test

MODIFY age VARCHAR(3);

‍

ALTER TABLE table_name

RENAME COLUMN old_column_name TO new_column_name;

‍

三、数据操作语言（DML）

3.1 INSERT

INSERT命令是SQL中用于向数据库表中添加新记录的基本命令。

‍

语法

INSERT INTO 表名 (列1, 列2, 列3, ...)

VALUES (值1, 值2, 值3, ...);

在这里，表名是你想要插入记录的表的名称，列1、列2、列3等是你想要插入数据的列的名称，值1、值2、值3等是你想要插入的具体数据值。

‍

示例

例如，假设你有一个名为students的表，它有id、name、age和class这四个字段，你想要插入一条新的学生记录，你可以使用以下SQL语句：

INSERT INTO students (id, name, age, class)

VALUES (1, '张三', 20, '计算机科学与技术1班');

‍

如果你没有指定列名，而是直接提供了值的顺序，那么你==必须按照表中列的顺序来插入值==。例如：

INSERT INTO students

VALUES (1, '李四', 19, '软件工程2班');

‍

单条插入：

INSERT INTO employees (first_name, last_name, age)

VALUES ('John', 'Doe', 30);

多条插入：

INSERT INTO employees (first_name, last_name, age)

VALUES ('Alice', 'Smith', 28),

('Bob', 'Johnson', 35);

使用SELECT插入：

INSERT INTO employees (first_name, last_name, age)

SELECT name, surname, age

FROM new_hires

WHERE start_date > '2023-01-01';

‍

3.2 DELETE—删除具体行

SQL中的DELETE命令用于从表中删除一条或多条记录。

语法

DELETE FROM table_name WHERE condition;

‍

示例

例如，要删除名为 student 的表格中学号为 1001 的行，可以使用以下语句：

DELETE FROM student WHERE s_id = 1001;

‍

删除多行

DELETE FROM student WHERE s_id IN (1001, 1002, 1003);

DELETE FROM student WHERE s_id = 1001 OR s_id = 1002 OR s_id = 1003;

‍

使用子查询：

DELETE FROM student WHERE s_id IN (SELECT s_id FROM other_table WHERE condition);

‍

3.3 UPDATE

UPDATE 是 SQL 中用于修改数据库表中现有记录的重要命令。

‍

语法

UPDATE table_name

SET column1 = value1, column2 = value2, ...

WHERE condition;

table_name：要更新的表的名称。
SET：指定需要修改的列及其新值。
WHERE：条件语句，用于指定哪些记录需要被更新。如果省略 WHERE 子句，表中的所有记录都会被更新。

‍

示例

如果你只想更新部分行，需要使用适当的 WHERE 条件来限制更新的行。例如，如果你只想更新国家为 "China" 的记录，可以使用以下 SQL 语句：

UPDATE tosot.select_agent_country

SET status = 0

WHERE country = 'China';

‍

不带条件，全表更新

update tosot.select_agent_country

set status=0

‍

四、数据查询语言（DQL）

4.1 SELECT

SELECT 是 SQL 中最常用的命令之一，用于从数据库中查询和检索数据。它允许用户从一个或多个表中选择特定的列，并可以应用各种条件和排序方式来过滤和组织结果。

‍

语法

SELECT column1, column2, ...

FROM table_name

WHERE condition

ORDER BY column ASC|DESC

LIMIT number;

column1, column2, ... ：要查询的列名，可以使用 * 表示选择所有列。
FROM table_name：指定要查询的表名。
WHERE condition：可选，设置过滤条件以限制返回的记录。
ORDER BY：可选，指定结果的排序方式，可以按升序（ASC）或降序（DESC）。
LIMIT：可选，限制返回的记录数。

‍

示例

例如，要从名为 users 的表中检索所有用户的姓名和年龄，可以执行以下 SELECT 语句：

SELECT name, age

FROM users;

如果要添加条件，例如只检索年龄大于等于 18 岁的用户，则可以：

SELECT name, age

FROM users

WHERE age >= 18;

这样就只会返回年龄大于等于 18 岁的用户的姓名和年龄信息。

‍

备注：如果SELECT后没有指定列，需要为*，否则会报错

‍

查看mysql版本

select version();

‍

4.2 WHERE-过滤数据

WHERE 子句是 SQL 中用于过滤数据的重要部分。它允许用户指定条件，以从数据库中检索特定的记录。

‍

语法

基本语法如下：

SELECT column1, column2, ...

FROM table_name

WHERE condition;

WHERE 子句在 FROM 子句之后，但在 ORDER BY 子句之前。
对于聚合查询中的分组过滤，使用 HAVING 子句。

‍

常用条件

比较运算符：可以使用如 =、!=、<、>、<= 和 >=。

SELECT * FROM employees WHERE salary > 50000;

逻辑运算符：支持 AND、OR 和 NOT 来组合条件。

SELECT * FROM employees WHERE department = 'Sales' AND salary > 50000;

模糊匹配：使用 LIKE 进行部分匹配。

SELECT * FROM employees WHERE name LIKE 'J%'; -- 所有以 J 开头的名字

空值检查：使用 IS NULL 或 IS NOT NULL 来检查空值。

SELECT * FROM employees WHERE manager_id IS NULL;

AND 和 OR 用于连接多个过滤条件。优先处理 AND，当一个过滤表达式涉及到多个 AND 和 OR 时，可以使用 () 来决定优先级，使得优先级关系更清晰。

‍

示例

示例1—等于或大于

‍

select name

from city

where id>4060;

‍

示例2—不匹配检查

‍

示例3—范围值检查

select id,name

from city

where id between 100 and 109;

‍

示例4—空值检查

SELECT语句有一个特殊的WHERE子句，可用来检查具有NULL值的列。

这个WHERE子句就是IS NULL子句。其语法如下：

‍

AND操作符

select id,name

from city

where id>201 and population<731200 limit 10;

select id,population,name

from city

where id>201 and population<731200 limit 10;

‍

OR操作符

OR操作符与AND操作符不同，它指示MySQL检索匹配任一条件的行。

select id,population,name

from city

where id=201 or population=731200 limit 10;

‍

IN操作符

select id,name

from city

where id in(1,3,4);

 在使用长的合法选项清单时，IN操作符的语法更清楚且更直观。

 在使用IN时，计算的次序更容易管理（因为使用的操作符更少）。

 IN操作符一般比OR操作符清单执行更快。

 IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建

立WHERE子句。第14章将对此进行详细介绍。

‍

NOT操作符

select id,name

from city

where id not in(1,3,4)

order by id desc limit 30;

‍

4.3 DISTINCT-去重

DISTINCT 是 MySQL 中的一个关键字，用于从查询结果中去除重复的值。它主要用在 SELECT 语句中，以确保返回的结果集中的每一行都是唯一的。

‍

注意：

性能：使用 DISTINCT 可能会影响查询性能，尤其是在处理大量数据时，因为数据库需要进行额外的计算来识别重复项。
NULL 值：如果某列包含 NULL 值，DISTINCT 将把 NULL 视为唯一值，因此在返回结果中会显示一次 NULL。

‍

语法

SELECT DISTINCT column1, column2, ...

FROM table_name;

‍

示例

单列去重：假设有一个名为 employees 的表，包含 department 列，你想获取所有不同的部门：

SELECT DISTINCT department

FROM employees;

多列去重：如果你想要根据多个列的组合来去重，例如获取所有不同的部门和职位组合，可以这样写：

SELECT DISTINCT department, position

FROM employees;

与其他聚合函数结合：有时可以与聚合函数结合使用，例如查看不同部门的人数：

SELECT COUNT(DISTINCT department) AS unique_departments

FROM employees;

‍

4.4 LIMIT-限制返回行数

LIMIT 是 SQL 中用于限制查询结果集返回行数的关键字。它通常与 SELECT 语句结合使用，允许用户指定希望返回的记录数。LIMIT 在很多数据库系统中都有支持，如 MySQL、PostgreSQL 和 SQLite 等。

‍

语法

在最简单的形式中，LIMIT 后面紧跟一个数字，表示返回的最大记录数。

SELECT * FROM employees LIMIT 5;

限制返回的行数。可以有两个参数，第一个参数为起始行，从 0 开始；第二个参数为返回的总行数。

‍

示例

返回前 5 行:

SELECT *

FROM mytable

LIMIT 5;

SELECT *

FROM mytable

LIMIT 0, 5;

返回第 3 ~ 5 行:

SELECT *

FROM mytable

LIMIT 2, 3;

‍

select name from city limit 5;

‍

select name from city limit 2,4;

‍

4.5 `ORDER BY`-排序

ORDER BY 是 SQL 中用于对查询结果进行排序的关键字。通过 ORDER BY，用户可以指定希望按照一个或多个列的值对返回的记录进行升序或降序排列。

‍

语法

ORDER BY 通常与 SELECT 语句结合使用，语法结构如下：

SELECT column1, column2, ...

FROM table_name

ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

ASC（升序）是默认选项。
DESC（降序）会将结果按从高到低的顺序排列。

‍

示例

请看下面的例子：

select name

from city

order by name limit 10;

按照字母顺序排序

‍

按多个列排序

经常需要按不止一个列进行数据排序。例如，如果要显示雇员清单，

可能希望按姓和名排序（首先按姓排序，然后在每个姓中再按名排序）。

如果多个雇员具有相同的姓，这样做很有用。

select id,name

from city

order by id, name

limit 10;

‍

指定排序方向

数据排序不限于升序排序（从A到Z）。这只是默认的排序顺序，还可

以使用ORDER BY子句以降序（从Z到A）顺序排序。为了进行降序排序，必须指定DESC关键字。

与DESC相反的关键字是ASC（ASCENDING），在升序排序时可以指定它。

但实际上，ASC没有多大用处，因为升序是默认的（如果既不指定ASC也不指定DESC，则假定为ASC）。

select id,name

from city

order by id desc, name

limit 10;

‍

4.6 LIKE-用通配符进行过滤

LIKE 是 SQL 中用于在 WHERE 子句中进行模式匹配的关键字。它允许用户通过通配符来搜索特定模式的文本数据，非常适合于查找包含某些字符或字符串的记录。

‍

语法

LIKE 通常与 SELECT、UPDATE 或 DELETE 语句结合使用，用于过滤结果集。基本语法如下：

SELECT column1, column2, ...

FROM table_name

WHERE column_name LIKE pattern;

pattern 是要匹配的字符串，可以包含通配符。

‍

通配符

在 LIKE 查询中，最常用的两个通配符是：

百分号 ( % ) ：表示零个或多个字符。例如，'A%' 匹配以 A 开头的任何字符串。
下划线 ( _ ) ：表示一个单个字符。例如，'A_' 匹配以 A 开头并有一个字符的任何字符串。

‍

示例

查找所有以 "J" 开头的员工姓名：

SELECT * FROM employees WHERE name LIKE 'J%';

查找所有包含 "son" 的姓氏：

SELECT * FROM employees WHERE last_name LIKE '%son%';

查找所有第二个字符为 "a" 的名字：

SELECT * FROM employees WHERE name LIKE '_a%';

‍

4.7 GROUP BY—分组查询

GROUP BY是SQL中的一条用于将查询结果集中的数据按照一个或多个列进行分组的命令。它通常与聚合函数（如COUNT、SUM、AVG等）结合使用，以便对每个分组应用计算。

‍

语法

SELECT column1, COUNT(*)

FROM table_name

GROUP BY column1;

数据分组：

GROUP BY可以将具有相同特征的行组织在一起，便于进行汇总和分析。

与聚合函数结合使用：

通常与聚合函数（如COUNT、SUM、AVG等）搭配使用，对每个分组执行计算。

支持多个列分组：

可以指定多个列作为分组依据。

‍

在使用GROUP BY时，SELECT子句中只能包含分组列或聚合函数返回的列。
HAVING是在分组后对结果进行过滤，而WHERE是在分组前进行过滤。

‍

示例

基本示例：

SELECT department, COUNT(*) AS employee_count

FROM employees

GROUP BY department;

此查询统计每个部门的员工数量。

使用多个列：

SELECT department, job_title, AVG(salary) AS average_salary

FROM employees

GROUP BY department, job_title;

此查询按部门和职位分组，并计算每个组合的平均薪资。

与HAVING结合使用：

HAVING子句用于过滤分组后的结果，==通常与====GROUP BY====结合使用==。

SELECT department, COUNT(*) AS employee_count

FROM employees

GROUP BY department

HAVING COUNT(*) > 5;

此查询只返回员工数大于5的部门。

‍

4.8 JOIN-连表查询

SQL 中的 JOIN 操作用于组合来自两个或多个表的数据，根据它们之间的关系进行匹配

JOIN 将多个表中的行根据特定的条件连接在一起，形成一个新的结果集。这在关系型数据库中非常常见，因为数据通常分布在多个表中。

‍

语法

在MySQL中，可以使用JOIN语句进行表的连接查询。JOIN语句可以根据两个或多个表之间的关联条件将它们连接在一起，从而获取相关联的数据。

SELECT columns

FROM table1

JOIN table2 ON table1.common_column = table2.common_column;

SELECT

t.id, t.name, t.sex, t.head_img,

t1.tel, t1.address AS userAddress,

t2.province, t2.city

FROM

user t

LEFT JOIN user_address t1 ON t1.user_id = t.id

LEFT JOIN area t2 ON t2.id = t1.area_id

WHERE (

t.id = ?

AND t1.tel LIKE ?

AND t.id > ?)

‍

JOIN 类型

INNER JOIN：

仅返回两个表中匹配的行。
语法示例：

SELECT *

FROM table1

INNER JOIN table2 ON table1.id = table2.foreign_id;

LEFT JOIN (或 LEFT OUTER JOIN) ：

返回左表的所有行，以及右表中匹配的行。如果没有匹配，右表的列返回 NULL。
语法示例：

SELECT *

FROM table1

LEFT JOIN table2 ON table1.id = table2.foreign_id;

RIGHT JOIN (或 RIGHT OUTER JOIN) ：

返回右表的所有行，以及左表中匹配的行。如果没有匹配，左表的列返回 NULL。
语法示例：

SELECT *

FROM table1

RIGHT JOIN table2 ON table1.id = table2.foreign_id;

FULL JOIN (或 FULL OUTER JOIN) ：

返回两个表中的所有行，匹配的行合并，不匹配的行填充 NULL。
语法示例：

SELECT *

FROM table1

FULL JOIN table2 ON table1.id = table2.foreign_id;

CROSS JOIN：

返回两个表的笛卡尔积，每一行与另一表的每一行都组合在一起。
语法示例：

SELECT *

FROM table1

CROSS JOIN table2;

‍