Mysql(2)—SQL语法详解

本文涉及的产品
云原生数据库 PolarDB 分布式版,标准版 2核8GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介: SQL(Structured Query Language,结构化查询语言)是一种用于管理关系型数据库的标准编程语言。它主要用于数据的查询、插入、更新和删除等操作。SQL最初在1970年代由IBM的研究人员开发,旨在处理关系数据模型。

一、关于SQL

1.1 简介

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

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

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

1.2 发展

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

1.3 功能

  • 数据查询:使用SELECT语句从数据库中检索数据。
  • 数据操作:包括INSERTUPDATEDELETE用于增加、修改和删除数据。
  • 数据定义:使用CREATEALTERDROP语句定义和修改数据库结构。
  • 数据控制:通过GRANTREVOKE管理用户权限。

1.4 基本概念

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

1.5 分类

1. 数据定义语言(DDL)

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

  • 常用命令
  • CREATE:创建数据库或表。
  • ALTER:修改已有的数据库或表结构。
  • DROP:删除数据库或表。
  • TRUNCATE:清空表中的数据,但不删除表结构。

2. 数据操作语言(DML)

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

  • 常用命令
  • INSERT:插入新记录。
  • UPDATE:更新已有记录。
  • DELETE:删除记录。
  • MERGE:合并数据(在一些数据库中可用)。

3. 数据查询语言(DQL)

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

  • 常用命令
  • SELECT:查询数据,可以使用WHEREORDER BYGROUP BYHAVING等子句来过滤和排序结果。

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;

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

注意事项
  • 注释不能嵌套。例如,/* 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 列名 数据类型(长度) 位置;

其中,表名是你想要修改的表的名称,列名是你想要新增的字段名,数据类型是字段的数据类型,如INTVARCHARDATETIME等,长度是数据类型的长度,比如VARCHAR(255)位置是可选的,指定新列在表中的位置,可以是FIRSTAFTER 列名或者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的表,它有idnameageclass这四个字段,你想要插入一条新的学生记录,你可以使用以下SQL语句:

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

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

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

INSERT INTO students

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

  1. 单条插入

INSERT INTO employees (first_name, last_name, age)

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

  1. 多条插入

INSERT INTO employees (first_name, last_name, age)

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

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

  1. 使用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;

  • 逻辑运算符:支持 ANDORNOT 来组合条件。

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

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

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

  • 空值检查:使用 IS NULLIS 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;

示例

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

SELECT DISTINCT department

FROM employees;

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

SELECT DISTINCT department, position

FROM employees;

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

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 通常与 SELECTUPDATEDELETE 语句结合使用,用于过滤结果集。基本语法如下:

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中的一条用于将查询结果集中的数据按照一个或多个列进行分组的命令。它通常与聚合函数(如COUNTSUMAVG等)结合使用,以便对每个分组应用计算。

语法

SELECT column1, COUNT(*)

FROM table_name

GROUP BY column1;

  1. 数据分组
  • GROUP BY可以将具有相同特征的行组织在一起,便于进行汇总和分析。
  1. 与聚合函数结合使用
  • 通常与聚合函数(如COUNTSUMAVG等)搭配使用,对每个分组执行计算。
  1. 支持多个列分组
  • 可以指定多个列作为分组依据。

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

示例

  1. 基本示例

SELECT department, COUNT(*) AS employee_count

FROM employees

GROUP BY department;

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

  1. 使用多个列

SELECT department, job_title, AVG(salary) AS average_salary

FROM employees

GROUP BY department, job_title;

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

  1. 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;

示例

假设有两个表,employees(员工表)和departments(部门表)。

  1. INNER JOIN示例

SELECT e.first_name, d.department_name

FROM employees e

INNER JOIN departments d ON e.department_id = d.id;

此查询返回所有员工及其对应的部门名称。

  1. LEFT JOIN示例

SELECT e.first_name, d.department_name

FROM employees e

LEFT JOIN departments d ON e.department_id = d.id;

此查询返回所有员工及其对应的部门名称,若某员工没有部门,则部门名称为NULL

  1. RIGHT JOIN示例

SELECT e.first_name, d.department_name

FROM employees e

RIGHT JOIN departments d ON e.department_id = d.id;

此查询返回所有部门及其对应的员工,若某部门没有员工,则员工姓名为NULL

  1. FULL JOIN示例

SELECT e.first_name, d.department_name

FROM employees e

FULL JOIN departments d ON e.department_id = d.id;

此查询返回所有员工和所有部门的信息,未匹配的部分显示为NULL

  1. CROSS JOIN示例

SELECT e.first_name, d.department_name

FROM employees e

CROSS JOIN departments d;

此查询返回员工和部门的所有可能组合。

连表分页查询

MySQL中的联表分页查询可以使用LIMIT和OFFSET子句结合使用,同时使用JOIN子句实现多个表的联合查询,下面是一个示例:

假设我们有两个表:users和orders,它们之间有一个关联字段user_id,我们需要查询所有用户及其订单,按照订单号升序排序,并分页显示。

SELECT *

FROM users

LEFT JOIN orders ON users.id = orders.user_id

ORDER BY orders.order_no ASC

LIMIT 10 OFFSET 20;

这个查询语句将返回第21-30条记录,LIMIT 10指定返回10条记录,OFFSET 20指定跳过前20条记录。LEFT JOIN将users和orders表连接在一起,ON条件指定了它们之间的关联字段。ORDER BY指定了按照订单号升序排序。

在实际应用中,需要根据具体需求进行调整,例如可以根据不同字段排序、使用INNER JOIN或RIGHT JOIN等连接方式、设置不同的LIMIT和OFFSET值来调整查询结果。

4.9 子查询

简介

SQL中的子查询(Subquery)是指在一个SQL语句中嵌套另一个SQL查询。子查询可以用于多种场景,例如在SELECTINSERTUPDATEDELETE语句中,或者在WHEREFROMHAVING子句中。通过使用子查询,可以实现更复杂的数据检索与处理。

  • 优点
  • 灵活性:可以进行复杂的数据过滤、聚合或比较。
  • 可读性:可以将复杂的逻辑分解成更小、更易理解的部分。
  • 复用性:子查询可以作为视图或临时表的替代,方便重复使用。
  • 缺点
  • 性能:复杂的子查询可能导致性能下降,特别是在大数据集上。
  • 理解和维护:过度使用子查询可能使SQL语句难以理解和维护。
  • 子查询的使用可以提高查询的灵活性和复杂性,但也要注意,过度使用子查询可能会导致性能问题。在某些情况下,使用 JOIN 或其他类型的查询可能会更有效。此外,一些数据库管理系统对子查询的支持和优化程度可能不同,因此在设计查询时应考虑到这些因素。

语法

SELECT column1, column2

FROM table

WHERE column3 IN (SELECT column3 FROM another_table WHERE condition);

类型

  1. 单行子查询
  • 返回单行结果的子查询,通常用于比较运算符(如=<>)。

SELECT employee_id, first_name

FROM employees

WHERE salary > (SELECT AVG(salary) FROM employees);

  1. 多行子查询
  • 返回多行结果的子查询,通常与INANYALL等运算符一起使用。

SELECT employee_id, first_name

FROM employees

WHERE department_id IN (SELECT id FROM departments WHERE location = 'New York');

  1. 相关子查询
  • 该子查询依赖于外部查询中的列。在外部查询每处理一行时,都会执行一次相关子查询。

SELECT e1.first_name, e1.salary

FROM employees e1

WHERE e1.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e1.department_id = e2.department_id);

  1. 非相关子查询
  • 独立于外部查询的子查询,只需执行一次。

SELECT first_name

FROM employees

WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales');

使用场景

  • 数据过滤:使用子查询来筛选符合特定条件的数据。例如,查找薪资高于所有员工的平均薪资的员工。
  • 数据计算:在查询中进行聚合运算,例如计算某个部门的平均薪资。
  • 复杂逻辑处理:对于需要多次引用同一查询结果的情况,使用子查询可以使逻辑更加清晰。

示例

假设有两个表:employees(员工表)和departments(部门表)。

  1. 查找薪资高于平均薪资的员工

SELECT first_name, salary

FROM employees

WHERE salary > (SELECT AVG(salary) FROM employees);

  1. 查找所在部门在“销售”部门的员工

SELECT first_name

FROM employees

WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales');

  1. 查找每个部门中薪资最高的员工(使用关联子查询):

SELECT e1.first_name, e1.salary

FROM employees e1

WHERE e1.salary = (SELECT MAX(e2.salary) FROM employees e2 WHERE e1.department_id = e2.department_id);

4.10 分页查询

SQL分页查询是一种用于从大数据集中提取特定范围内记录的技术,常用于显示数据列表(如在网页上显示的产品列表、用户信息等)。分页查询可以有效地减少一次性加载的数据量,提高系统的响应速度和用户体验。

语法

MySQL 的分页查询可以使用 LIMITOFFSET 关键字。以下是一个基本的分页查询 SQL 语句的结构:

SELECT *

FROM your_table

WHERE your_conditions

ORDER BY your_column

LIMIT page_size OFFSET (page_index - 1) * page_size;

分页查询通常涉及两个主要参数:

  • 当前页码:表示用户希望查看的页数。
  • 每页记录数:表示每一页中显示的记录数量。

示例

假设你有一个表 material_components,并且你想要分页查询这个表,查询条件是 enable_flag = 'ENABLE',并按 modify_time 排序。你可以这样写 SQL 语句:

SELECT *

FROM material_components

WHERE enable_flag = 'ENABLE'

ORDER BY modify_time DESC

LIMIT 10 OFFSET 20; -- 例如,获取第 3 页,每页 10 条记录

解释

  • LIMIT:限制返回的记录数,这里是 10 条。
  • OFFSET:跳过前面多少条记录,这里是 20 条,即从第 21 条开始返回(对应于第 3 页)。

另一种写法

在 MySQL 中,LIMIT 可以使用两个参数来实现,效果与上面的写法相同:

SELECT *

FROM material_components

WHERE enable_flag = 'ENABLE'

ORDER BY modify_time DESC

LIMIT 20, 10; -- 跳过前 20 条,返回 10 条记录

在这个例子中,20 是偏移量,10 是限制的记录数。

五、数据控制语言(DCL)

数据控制语言(Data Control Language,DCL)是 SQL 的一个子集,用于控制对数据库对象的访问权限以及用户权限的管理。DCL 主要包括两个关键命令:

  1. GRANT:授予权限
  2. REVOKE:撤销权限

这两条命令用于管理数据库用户和角色的权限,确保数据的安全性和完整性。

常见权限类型

在 DCL 中,常见的权限类型包括:

  • SELECT:查询数据。
  • INSERT:插入新记录。
  • UPDATE:更新现有记录。
  • DELETE:删除记录。
  • EXECUTE:执行存储过程。
  • ALL PRIVILEGES:授予所有权限。

5.1 GRANT

GRANT 命令用于向用户或角色授予特定的权限。权限可以是对表、视图、存储过程等对象的操作权限,如 SELECT、INSERT、UPDATE 和 DELETE 等。

语法:

GRANT privilege_type ON object TO user;

示例:

授予用户 johnemployees 表的查询权限:

GRANT SELECT ON employees TO 'john';

还可以授予多个权限:

GRANT SELECT, INSERT ON employees TO 'john';

5.2 REVOKE

REVOKE 命令用于撤销已经授予的权限。通过该命令,可以限制某个用户或角色对数据库对象的访问权限。

语法:

REVOKE privilege_type ON object FROM user;

示例:

撤销用户 johnemployees 表的查询权限:

REVOKE SELECT ON employees FROM 'john';

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
13天前
|
SQL 运维 关系型数据库
MySQL 运维 SQL 备忘
MySQL 运维 SQL 备忘录
38 1
|
22天前
|
SQL 存储 关系型数据库
SQL文件导入MySQL数据库的详细指南
数据库中的数据转移是一项常规任务,无论是在数据迁移过程中,还是在数据备份、还原场景中,导入导出SQL文件显得尤为重要。特别是在使用MySQL数据库时,如何将SQL文件导入数据库是一项基本技能。本文将详细介绍如何将SQL文件导入MySQL数据库,并提供一个清晰、完整的步骤指南。这篇文章的内容字数大约在
45 1
|
1天前
|
SQL 关系型数据库 MySQL
MySql5.6版本开启慢SQL功能-本次采用永久生效方式
MySql5.6版本开启慢SQL功能-本次采用永久生效方式
6 0
|
1天前
|
SQL 关系型数据库 MySQL
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
6 0
|
7天前
|
SQL 数据库
SQL数据库基础语法入门
[link](http://www.vvo.net.cn/post/082935.html)
|
14天前
|
SQL 存储 关系型数据库
mysql 数据库空间统计sql
mysql 数据库空间统计sql
31 0
|
14天前
|
SQL 存储 关系型数据库
mysql SQL必知语法
本文详细介绍了MySQLSQL的基本语法,包括SELECT、FROM、WHERE、GROUPBY、HAVING、ORDERBY等关键字的使用,以及数据库操作如创建、删除表,数据类型,插入、查询、过滤、排序、连接和汇总数据的方法。通过学习这些内容,读者将能更好地管理和操
9 0
|
18天前
|
存储 SQL 关系型数据库
Mysql学习笔记(二):数据库命令行代码总结
这篇文章是关于MySQL数据库命令行操作的总结,包括登录、退出、查看时间与版本、数据库和数据表的基本操作(如创建、删除、查看)、数据的增删改查等。它还涉及了如何通过SQL语句进行条件查询、模糊查询、范围查询和限制查询,以及如何进行表结构的修改。这些内容对于初学者来说非常实用,是学习MySQL数据库管理的基础。
74 6
|
15天前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
50 3
Mysql(4)—数据库索引
|
18天前
|
SQL Ubuntu 关系型数据库
Mysql学习笔记(一):数据库详细介绍以及Navicat简单使用
本文为MySQL学习笔记,介绍了数据库的基本概念,包括行、列、主键等,并解释了C/S和B/S架构以及SQL语言的分类。接着,指导如何在Windows和Ubuntu系统上安装MySQL,并提供了启动、停止和重启服务的命令。文章还涵盖了Navicat的使用,包括安装、登录和新建表格等步骤。最后,介绍了MySQL中的数据类型和字段约束,如主键、外键、非空和唯一等。
57 3
Mysql学习笔记(一):数据库详细介绍以及Navicat简单使用

相关产品

  • 云数据库 RDS MySQL 版