语法-常用语法汇总

查询

条件查询

在SQL中，条件查询需要使用WHERE关键字。常见的条件有：

• and
• or
• not
• between and 或者 not between and
• in
• is null
• is not null
• exists
• not exists

-- 查询年龄在20和25之间的数据，不包括25
SELECT * FROM table_name WHERE age BETWEEN 20 AND 24;

-- 查询年龄在20和25之间的数据，不包括25
SELECT * FROM table_name WHERE age IN(20,21,22,23,24);

-- 查询table1表中的id存在于table2表中的数据
SELECT * FROM table1 
WHERE EXISTS (SELECT * FROM table2 WHERE table1.id = table2.id);

提示

• MySQL：between包括边界值
• Oracle：between包括边界值
• SQL Server：between包括边界值，not between不包括 日期时，后边界值为2017年04月06日，就等价于2017-04-06 00:00:00

模糊查询

在SQL中，模糊查询需要使用LIKE关键字。常见的通配符有% _ [] [^ ] [! ]当需要转义时，使用反斜杠``

• LIKE 'en%'，匹配以“en”开始的字符串，例如“english languages”、“end”；
• LIKE '%en%'，匹配包含“en”的字符串，例如“length”、“when are you”；
• LIKE '%en'，匹配以“en”结束的字符串，例如“ten”、“when”；
• LIKE 'Be_'，匹配以“Be”开始，再加上一个任意字符的字符串。例如“Bed”、“Bet”；
• LIKE '_e%'，匹配一个任意字符加上“e”开始的字符串，例如“her”、“year”。
• LIKE '[ab]' 匹配保护a或b的
• LIKE '[^abc]'，匹配非a、b、c的字符串。例如“d”、“f”、“g”；

-- 查询名字以“张”开始的
SELECT * FROM table_name WHERE name LIKE '张%';

-- 查询名字以“张”结尾的
SELECT * FROM table_name WHERE name LIKE '%张';

-- 查询名字包含“张”的
SELECT * FROM table_name WHERE name LIKE '%张%';

-- 查询名字以“张”开头，后面跟着任意一个字符的字符串
SELECT * FROM table_name WHERE name LIKE '张_';

-- 匹配一个任意字符+张的字符串
SELECT * FROM table_name WHERE name LIKE '_张%';

LIKE 匹配大小写注意

• Oracle 和 PostgreSQL 默认区分 LIKE 中的大小写，PostgreSQL 提供了不区分大小写的 ILIKE 运算符；
• MySQL 和 SQL Server 默认不区分 LIKE 中的大小写。

出了Like外 我们还可以使用REGEXP_LIKE 正则表达式函数进行模糊查询

mysql 和 Oracle 使用 REGEXP_LIKE

SELECT * FROM table_name WHERE REGEXP_LIKE(name, '^张');

PostgreSQL 正则匹配符

• ~* 匹配某个正则表达式，不区分大小写；
• !~ 不匹配某个正则表达式，区分大小写；
• !~* 不匹配某个正则表达式，不区分大小写。

SELECT * FROM table_name WHERE name ~ '^张';

排序

ORDER BY：用于对查询结果进行排序，默认是升序排序ASC，可以使用DESC关键字进行降序排序。

SELECT * FROM table_name ORDER BY age DESC;

除此之外还可以进行多字段排序规则，在多字段排序时数据会先按照第一个字段排序,如果第一个字段的值相同，再按照第二个字段排序！

-- 先按age降序排序，再按name升序排序
SELECT * FROM table_name ORDER BY age DESC, name ASC;

分组查询

GROUP BY：用于对查询结果进行分组，通常与聚合函数一起使用。

分组查询，分组查询时，如果需要条件过滤请使用having

使用时需要注意 ONLY_FULL_GROUP_BY 错误。MySOL中 如果在select 或 haveing 中查询的字段没有使用聚合函数（如 SUM AVG）等会出现报错。解决方案

1. 使用ANY_VALUE() 对非聚合函数进行包裹
2. 禁用mysql的 ONLY_FULL_GROUP_BY

SELECT age, COUNT(*) FROM table_name GROUP BY age;

分组查询常使聚合函数配合使用，常见的聚合函数有：

• COUNT()：返回指定字段的行数。
• SUM()：返回指定字段的和。
• AVG()：返回指定字段的平均值。
• MAX()：返回指定字段的最大值。
• MIN()：返回指定字段的最小值。

分页查询

语法：LIMIT offset,count offset 可选表示从查询结果中的第几行开始返回数据，count表示返回的记录数 offset可以是正整数或负整数。正整数表示从查询结果的起始位置开始返回数据，负整数表示从查询结果的末尾位置开始返回数据。count必须是正整数，表示返回的记录数。

SELECT * FROM table LIMIT 10, 5  // 将返回从第11行开始的5条记录。
SELECT * FROM table LIMIT 10 // 将返回前10条记录。

去重

DISTINCT：用于去除查询结果中的重复行。

SELECT DISTINCT name FROM table_name;

联接查询

JOIN：用于将两个或多个表中的行组合在一起，通常用于根据某个共同的字段进行关联查询。

• INNER JOIN：内连接，返回两个表中匹配的行。
• LEFT JOIN：左连接，返回左表中的所有行，以及右表中匹配的行。
• RIGHT JOIN：右连接，返回右表中的所有行，以及左表中匹配的行。
• FULL JOIN：全连接，返回两个表中的所有行，以及匹配的行。

-- 内连接
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

-- 左连接
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;

-- 右连接
SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id;

-- 全连接
SELECT * FROM table1 FULL JOIN table2 ON table1.id = table2.id;

NATURAL JOIN(natural join) 是一种特殊的 JOIN 操作，它会自动根据两个表中列名相同的列进行连接。它会隐式地使用这些相同名称的列作为连接条件。

SELECT * FROM table1 NATURAL JOIN table2;

注意事项

• 列名匹配：NATURAL JOIN 会自动使用两个表中列名相同的列进行连接。如果列名不匹配，则不会进行连接。
• 隐式连接条件：由于 NATURAL JOIN是隐式的，可能会导致意外的结果，特别是在表中有多个同名列的情况下。
• 结果集中的列：使用 NATURAL JOIN 时，结果集中只会保留一个副本的连接列。
• 性能考虑：虽然 NATURAL JOIN 简化了语法，但在某些情况下，显式地指定连接条件（如 INNER JOIN）可能更清晰和高效。

示例

假设我们有两个表 employees 和 departm ents，结构如下：

employees 表

id	name	department_id
1	Alice	1
2	Bob	2

departments 表

id	name
1	HR
2	Engineering

SELECT employees.name AS employee_name, departments.name AS department_name
FROM employees
NATURAL JOIN departments;

使用 NATURAL JOIN 连接这两个表，会得到以下结果：

id	name	department_name
1	Alice	HR
2	Bob	Engineering

联合查询

UNION：用于将两个或多个查询结果合并为一个结果集，并去除重复的行。

SELECT * FROM table1 
UNION 
SELECT * FROM table2;

UNION ALL：用于将两个或多个查询结果合并为一个结果集，不去除重复的行。

SELECT * FROM table1 UNION ALL SELECT * FROM table2;

子查询

子查询在SQL中，子查询是指在一个查询语句中嵌套另一个查询语句。子查询可以用于筛选、排序、分组等操作。

-- 查询年龄大于平均年龄的数据
SELECT * FROM table_name WHERE age > (SELECT AVG(age) FROM table_name);

常见函数

• count()：返回指定字段的行数。
• sum()：返回指定字段的和。
• avg()：返回指定字段的平均值。
• max()：返回指定字段的最大值。
• min()：返回指定字段的最小值。
• concat()：将多个字符串连接在一起。
• substring()：返回字符串的子字符串。
• length()：返回字符串的长度。
• lower()：将字符串转换为小写。
• upper()：将字符串转换为大写。
• trim()：去除字符串两端的空格。
• date()：返回日期。

-- 查询每个部门中年龄最大的员工
SELECT department, MAX(age) FROM table_name GROUP BY department;

-- 查询每个部门中年龄最大的员工的名字
SELECT department, name FROM table_name WHERE (department, age) IN (SELECT department, MAX(age) FROM table_name GROUP BY department);

-- 查询每个部门中年龄最大的员工的名字
SELECT department, name FROM table_name WHERE (department, age) IN (SELECT department, MAX(age) FROM table_name GROUP BY department);

-- 查询每个部门中年龄最大的员工的名字
SELECT department, name FROM table_name WHERE (department, age) IN (SELECT department, MAX(age) FROM table_name GROUP BY department);

常见的日期函数

• YEAR()：返回日期的年份。
• MONTH()：返回日期的月份。
• DAY()：返回日期的天数。
• HOUR()：返回日期的小时数。
• MINUTE()：返回日期的分钟数。
• SECOND()：返回日期的秒数。
• DATE()：返回日期的日期部分。
• TIME()：返回日期的时间部分。
• DATEDIFF()：返回两个日期之间的天数差。
• DATE_ADD()：返回日期加上指定时间间隔后的日期。

常见窗口函数

• row_number()：返回结果集中每一行的行号。
• rank()：返回结果集中每一行的排名，排名相同的行具有相同的排名。
• ednse_rank()：返回结果集中每一行的排名，排名相同的行具有相同的排名，且排名之间没有间隔。
• ntile()：将结果集划分为指定数量的桶，并返回每一行所在的桶号。
• lead()：返回结果集中每一行的下一行的值。
• lag()：返回结果集中每一行的上一行的值。
• first_value()：返回结果集中每一行的第一个值。

-- rank 示例
SELECT name, age, rank() OVER (ORDER BY age DESC) as rank FROM table_name;

-- ednse_rank 示例
SELECT name, age, dense_rank() OVER (ORDER BY age DESC) as rank FROM table_name;

表操作

创建表

CREATE TABLE：用于创建新的表。

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ...
);

插入

INSERT INTO：用于向表中插入新的行。

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

INSERT INTO 语句也可以用于插入多行数据。

INSERT INTO table_name (column1, column2, column3) 
VALUES 
(value1, value2, value3),
(value4, value5, value6),
(value7, value8, value9);

更新表

UPDATE：用于更新表中的数据。

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

删除表

DELETE FROM：用于删除表中的数据。

DELETE FROM table_name WHERE condition;

DELETE FROM 语句也可以用于删除多行数据。

DELETE FROM table_name WHERE condition;

字段操作

添加字段

ALTER TABLE：用于添加新的字段。

ALTER TABLE table_name 
ADD column_name datatype;

删除字段

ALTER TABLE：用于删除字段。

ALTER TABLE table_name 
DROP COLUMN column_name;

修改字段

ALTER TABLE：用于修改字段。

ALTER TABLE table_name 
MODIFY column_name datatype;

重命名字段

ALTER TABLE：用于重命名字段。

ALTER TABLE table_name 
CHANGE old_column_name new_column_name datatype;

索引操作

CREATE INDEX：用于创建索引。

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

DROP INDEX：用于删除索引。

DROP INDEX index_name ON table_name;

---

数据库操作

创建数据库

CREATE DATABASE：用于创建新的数据库。

CREATE DATABASE database_name;

删除数据库

DROP DATABASE：用于删除数据库。

DROP DATABASE database_name;

选择数据库

USE：用于选择要操作的数据库。

USE database_name;

其它常见操作

-- 登录
mysql -u root -p

-- 设置密码
set password for root@localhost=password('新密码'); (方法一)
alter user user() identified by '新密码'; (方法二)

-- 设置密码时使用加密方式
alter user 'root'@'localhost' identified  with mysql_native_password by  '密码';

-- 刷新权限
flush privileges

-- 查看数据库
show databases;

-- 查看表
show tables;

-- 查看表结构
desc table_name;

-- 查看表索引
show index from table_name;

-- 查看表创建语句
show create table table_name;

-- 查看用户权限
show grants for 'username'@'host';

-- 查看用户
select user,host from mysql.user;

-- 查看当前用户
select user();

-- 查看当前数据库
select database();

-- 查看当前时间
select now();

---

权限操作

新建用户

CREATE USER：用于创建新的用户。

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

删除用户

DROP USER：用于删除用户。

DROP USER 'username'@'host';

授予权限

GRANT：用于授予用户权限。

GRANT privilege_type ON database_name.table_name TO 'username'@'host';

REVOKE：用于撤销用户的权限。

REVOKE privilege_type ON database_name.table_name FROM 'username'@'host';

---

事务操作

BEGIN TRANSACTION：用于开始一个事务。 COMMIT：用于提交事务，将事务中的所有更改保存到数据库中。 ROLLBACK：用于回滚事务，取消事务中的所有更改。

BEGIN TRANSACTION;
-- 执行一些操作
COMMIT;

回滚操作

BEGIN TRANSACTION;
-- 执行一些操作
ROLLBACK;