跳至主要內容

语法

苓渊大约 11 分钟sql

相关信息

SQL 的语法由各种用于数据操作、定义和控制的语句组成。每类语句都有特定的用途和语法结构。下面详细介绍 SQL 的主要语法分类，包括数据查询、数据操作、数据定义、事务控制和权限管理等。

数据查询语言(DQL)

SELECT 语句用于从数据库表中查询数据，是 SQL 中最常用的语句。基本语法如下：

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY column1, column2, ...]
[HAVING condition]
[ORDER BY column1 [ASC|DESC]]
[LIMIT number];
  • SELECT：指定要查询的列，使用 * 表示选择所有列。
  • FROM：指定要查询的表。
  • WHERE：条件过滤，返回满足条件的行。
  • GROUP BY：将结果按一列或多列进行分组。
  • HAVING：对分组后的结果进行条件过滤，类似 WHERE，但作用于分组后的数据。
  • ORDER BY：对结果排序，默认为升序，DESC 表示降序。
  • LIMIT：限制返回的行数。

示例： 查询员工表中年龄大于 30 岁的员工姓名，并按年龄降序排列，只返回前 5 名。

SELECT name, age
FROM employees
WHERE age > 30
ORDER BY age DESC
LIMIT 5;

Where

用于设置筛选数据的条件，只查询出满足条件的数据，属性DQL语句中可选的部分。

-- 查询出user_name列为lingyuan的数据
WHERE user_name = "lingyuan"
-- 查询出user_name列为lingyuan 且 age小于等于18的数据
WHERE user_name = "lingyuan" AND age <= 18
-- 查询出user_name为lingyuan 或 user_name为zenghong的数据
WHERE user_name = "lingyuan" OR user_name = "zenghong"

可用的操作符：

操作符说明
=等于
<小于
>大于
<> !=不等于
<= !>小于等于
>= !<大于等于
BETWEEN在两个值之间
IS NULL为 NULL 值
IS NOT NULL不为 NULL 值
  • NULL 与 0 和空字符串都不同，需要使用关键字判断列是否为 null.
  • AND 和 OR 用于连接多个过滤条件。优先处理 AND，当一个过滤表达式涉及到多个 AND 和 OR 时，可以使用 () 来决定优先级，使得优先级关系更清晰。
  • IN 操作符用于匹配一组值，其后也可以接一个 SELECT 子句，从而匹配子查询得到的一组值。
  • NOT 操作符用于否定一个条件。

DISTINCT

筛选查询的数据，使相同值只会出现一次。

作用于所有列，也就是所有列的值都相同才算相同。

SELECT DISTINCT col1, col2 FROM mytable;

LIMIT OFFSET

限制返回的行数和偏移量。

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

-- 返回前10行
LIMIT 10;
LIMIT 0, 10;

-- 返回第3 ~ 7行
LIMIT 2, 5;
LIMIT 5 OFFSET 2;

排序

使用 order by 关键字指定查询出的数据按列以某种方式排序。

ASC：升序(默认)，DESC：降序。

可以按多个列进行排序，并且为每个列指定不同的排序方式。

ORDER BY col1 DESC, col2 ASC;

通配符

通配符用在 where 语句中，只能用于文本字段。不滥用通配符，通配符位于开头处匹配会导致无法使用索引。

  • % 匹配 >=0 个任意字符；
  • _ 匹配 ==1 个任意字符；
  • [ ] 可以匹配集合内的字符，例如 [ab] 将匹配字符 a 或者 b。
  • 用脱字符 ^ 可以对其进行否定，也就是不匹配集合内的字符。
  • 使用 Like 来进行通配符匹配。

别名

为查询出的字段重命名，或者字段需要用作其他计算时，通常需要使用 AS 来取别名，否则输出的时候字段名为计算表达式。

SELECT col1 * col2 AS alias FROM mytable;

分组

分组就是把具有相同的数据值的行放在同一组中，对同一分组数据使用汇总函数进行处理，例如求分组数据的平均值等。

group by关键字可以指定查询的数据按某些列进行分组。

ORDER BY 也可以按汇总字段来进行排序。

SELECT col, COUNT(*) AS num
FROM mytable
GROUP BY col
ORDER BY num;

分组规定：

  • GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前。
  • 除了汇总字段外，SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出（MySQL例外）。
  • NULL 的行会单独分为一组。
  • 大多数 SQL 实现不支持 GROUP BY 列具有可变长度的数据类型。

汇总函数

函数作用
AVG()计算平均数
MIN()计算最小值
MAX()计算最大值
SUM()计算求和
COUNT()计算结果行数，如果根据求行数的字段为null那么就不计算行数

HAVING

WHERE 过滤行，HAVING 则过滤分组，行过滤应当先于分组过滤。

分组过滤条件必须使用 HAVING 关键字。

WHERE 关键字的查询条件不能使用函数，要使用函数必须使用 HAVING 关键字。

SELECT col, COUNT(*) AS num
FROM mytable
WHERE col > 2
GROUP BY col
HAVING num >= 2;

子查询

在一个查询中嵌套另一个查询，嵌套的查询语句就是子查询，子查询的结果可以被本次查询语句使用，可以将子查询的结果作为 WHRER 语句的过滤条件。

  • 单行子查询:子句只会查询出一个结果。
  • 多行子查询:子句会查询出多个结果。
-- 1、查询工资比JONES高的所有员工。  
select ename 
from emp
where sal > 
 (select sal 
   from emp
   where ename = 'JONES');
/*关联查询方法*/
select e2.ename,e2.sal 
from emp e1,emp e2 
where e1.ename = 'JONES' 
  and e2.sal>e1.sal;

-- 2、和工资大于2000的人在同一个部门的人
select ename,deptno 
from emp
where deptno in
 (select distinct deptno from emp
     where sal>2000);
/*关联查询方法*/
select distinct e2.ename,e2.deptno,e2.sal 
from emp e1,emp e2 
where e1.sal>2000 
  and e2.deptno = e1.deptno;

EXISTS

判断是否存在。如果子句有返回结果,那么返回true.否则返回false. NOT EXISTS 和 EXISTS 相反。

-- 1、查询有员工的部门信息。
SELECT d.* FROM dept d
WHERE EXISTS
 (SELECT empno FROM emp e
     WHERE d.deptno = e.deptno);

连接

连接用于连接多个表，使用 JOIN 关键字，并且条件语句使用 ON .

连接可以替换子查询，并且比子查询的效率一般会更快。

可以用 AS 给列名、计算字段和表名取别名，给表名取别名是为了简化 SQL 语句以及连接相同表。

内连接

内连接又称等值连接，使用 INNER JOIN 关键字。

可以不明确使用 INNER JOIN，而使用普通查询并在 WHERE 中将两个表中要连接的列用等值方法连接起来。

在没有条件语句的情况下返回笛卡尔积。

-- 显式inner join
SELECT A.value, B.value
FROM tablea AS A INNER JOIN tableb AS B
ON A.key = B.key;

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

外连接

外连接保留了没有关联的那些行。分为左外连接，右外连接以及全外连接，左外连接就是保留左表没有关联的行。

左连接：left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

右连接：right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。

检索所有顾客的订单信息，包括还没有订单信息的顾客。

SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

UNION

使用 UNION 来组合两个查询，如果第一个查询返回 M 行，第二个查询返回 N 行，那么组合查询的结果一般为 M+N 行。

每个查询必须包含相同的列、表达式和聚集函数。默认会去除相同行，如果需要保留相同行，使用 UNION ALL.

UNION 只能包含一个 ORDER BY 子句，并且必须位于语句的最后。

SELECT col
FROM mytable
WHERE col = 1
UNION
SELECT col
FROM mytable
WHERE col =2;

伪列

在插入数据时数据库自动给改行数据生成的唯一的 rowid.

  • rowid在插入数据时数据库自动给改行数据生成的唯一 rowid，查数据最快。
  • rownum查询返回结果集中行的序号，可以用来限制查询返回的行数。
  • rownum在排序之前就已经存在，排序会打乱 rownum 的排序。
  • 伪列只能直接查找等于 1 和 <，若要查找大于 1 和一个区间，需要把 rownum 查出来变成一个列再使用。
-- 1、查询年龄最小的三位学员
select * from 
 (select * from studentinfo
     order by stuage)
where rownum <= 3;

-- 2、查询 Java 考试成绩第二名的学员信息
select * from
 (select exam.*,rownum r from(
  select * from studentexam exam 
        where examsubject = 'Java' 
        order by examresult desc))
where r = 2;

数据操作语言(DML)

插入数据

INSERT INTO 语句用于向表中插入新数据。

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

示例： 向员工表中插入一条新记录。

INSERT INTO employees (name, age)
VALUES ('John Doe', 28);

更新数据

UPDATE 语句用于更新表中现有的数据。

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

示例： 将员工表中 John Doe 的年龄更新为 30 岁。

UPDATE employees
SET age = 30
WHERE name = 'John Doe';

删除数据

DELETE 语句用于从表中删除数据。

DELETE FROM table_name
WHERE condition;

示例： 删除员工表中所有年龄大于 60 岁的记录。

DELETE FROM employees
WHERE age > 60;

数据定义语言(DDL)

创建表

CREATE TABLE 语句用于创建新的表，并指定每列的数据类型。

CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
);

示例： 创建一个 employees 表，其中包含 id、name 和 age 列。

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

修改表

ALTER TABLE 语句用于修改表结构，添加、删除或修改列。

添加列：

ALTER TABLE table_name ADD column_name datatype;

删除列：

ALTER TABLE table_name DROP COLUMN column_name;

示例： 向员工表中添加一个 email 列。

ALTER TABLE employees ADD email VARCHAR(255);

删除表

DROP TABLE 语句用于删除表及其所有数据。

DROP TABLE table_name;

示例： 删除 employees 表。

DROP TABLE employees;

数据控制语言(DCL)

授予权限

GRANT 语句用于为用户授予权限。

GRANT privilege_name ON object TO user;

示例： 授予用户 john 对数据库 employees_db 的所有权限。

GRANT ALL PRIVILEGES ON employees_db.* TO 'john'@'localhost';

撤销权限

REVOKE 语句用于撤销用户的权限。

REVOKE privilege_name ON object FROM user;

示例： 撤销用户 john 对数据库 employees_db 的所有权限。

REVOKE ALL PRIVILEGES ON employees_db.* FROM 'john'@'localhost';

事务控制语言(TCL)

启动事务

START TRANSACTION 语句用于显式开启事务。

START TRANSACTION;

提交事务

COMMIT 语句用于提交事务，将所有修改永久保存。

COMMIT;

回滚事务

ROLLBACK 语句用于回滚事务，撤销未提交的修改。

ROLLBACK;

示例： 执行事务操作，若某步失败则回滚。

START TRANSACTION;
UPDATE employees SET age = age + 1 WHERE id = 1;
DELETE FROM employees WHERE id = 2;

-- 若某个操作失败
ROLLBACK;

主键

数据库中每个表都要有一个主键，唯一不重复且不能为空，主键不应是表中有意义的属性列。

主键生成策略：

  • 自动增长的数字。
  • 随机生成的 32 位字符串。

序列自增

-- 创建一个序列
create sequence seq_student_id
--最小值
minvalue 1
--最大值
maxvalue 99999
--步长
increment by 1

-- 插入数据时使用 序列名.nextval 获取序列的值作为主键
insert into student(id, name, age) values(seq_student_id.nextval, 'lee', 20)

触发器自增

触发器自动化给主键 id 自增。

-- 创建一个序列
create sequence seq_student_id
minvalue 10
maxvalue 99999
increment by 1;

-- 使用触发器自增主键
create trigger tri_student_id
-- 在插入表 bank_info 操作前触发
before insert on student
for each row
begin
 select seq_student_id.nextval into :new.id from dual;
end;

auto_increment key

MYSQL 数据库提供的关键字，在创建数据表时为主键加上 auto_increment key 即可。

create table user(
 uid int auto_increment key primary key 
);

sys_guid

sys_guid()：系统根据当前时间和机器码，生成全球唯一的一个 32 位序列号。

insert into student(id, name, age) values(sys_guid(), 'lee', 20)

时间自动生成

数据表中常见的创建时间和修改时间，可以通过自动生成的方式实现。

CURRENT_TIMESTAMP

-- 建表时
CREATE TABLE `course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- 修改列
ALTER TABLE `course` MODIFY COLUMN  `create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' ;

-- 新增一列
ALTER TABLE `course` ADD COLUMN `update_time` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' ;
上次编辑于:
贡献者: lingyuan