Harvey Li
首页 标签 归档 关于

SQL常用命令

2024-03-18 巧记

SQL基础

SQL语句主要分为3个类别：

  • DDL（Data Definition Language）语句：数据定义语句，定义了不同的数据段、数据库、表、列、索引等数据对象。常用的语句关键字包括create、drop、alter等。

  • DML（Data Manipulation Language）语句：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。常用的语句关键字包括insert、delete、update和select等。

  • DCL（Data Control Language）语句：数据控制语句，用于控制不同数据段的直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。常用的语句关键字包括grant、revoke等。

DDL语句

创建数据库

1
CREATE DATABASE dbname;

要知道系统中都存在哪些数据库：

1
SHOW DATABASES;

选择要操作的数据库：

1
USE dbname;

删除数据库

1
DROP DATABASE dbname;

创建表

1
2
3
4
5
6
CREATE TABLE tablename (
    column_name_1 column_type_1 constraints,
    column_name_2 column_type_2 constraints,
    ...
    column_name_n column_type_n constraints
)

表创建完毕后，要查看表的定义：

1
DESC tablename;

为了得到更全面的表定义信息，有时需要查看创建表的SQL语句：

1
SHOW CREATE TABLE tablename;

删除表

1
DROP TABLE tablename;

修改表

  1. 修改表类型

    1
    ALTER TABLE tablename MODIFY [COLUMN] column_definition;

    例如，修改表emp的ename字段定义，改为varchar(20)：

    1
    alter table emp modify ename varchar(20);

  2. 添加表字段

    1
    ALTER TABLE tablename ADD [COLUMN] column_definition;

    例如，在表emp中新添加字段age，类型为int(3)：

    1
    alter table emp add age int(3);

  3. 删除表字段

    1
    ALTER TABLE tablename DROP [COLUMN] col_name;

    例如，将字段age删除掉：

    1
    alter table emp drop age;

  4. 字段改名

    1
    ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition;

    例如，将age改为age1，同时修改字段类型为int(4)：

    1
    alter table emp change age age1 int(4);

    change和modify都可以修改表的定义，不同的是change后面需要写两次列名。但change的优点是可以修改列名称，而modify不能。

  5. 修改字段排列顺序（MySQL扩展）

    ADD增加的新字段默认加在表的最后位置，而CHANGE和MODIFY默认不改变字段的位置。添加和修改语法都有一个可选项FIRST | AFTER column_name，可以用来修改字段在表中的位置。

    例如，将新增的字段birth date加载ename之后：

    1
    alter table emp add birth date after ename;

    修改age字段，把它放在最前面：

    1
    alter table emp modify age int(3) first;

  6. 更改表名

    1
    ALTER TABLE tablename RENAME [TO] new_tablename;

DML语句

插入记录

1
2
INSERT INTO tablename (field1, field2, ..., fieldn)
VALUES (value1, value2, ..., valuen);

也可以不用指定字段名称，但是values后面的顺序应与字段的排列顺序一致。

含可空的字段、非空但是含有默认值的字段以及自增字段，可以不在insert后的字段列表中出现，values后面只写对应字段名称的值。没写的字段自动设置为NULL、默认值、自增的下一个数字。

在 MySQL 中，可以一次性插入多条记录：

1
2
3
4
5
6
INSERT INTO tablename (field1, field2, ..., fieldn)
VALUES
(record1_value1, record1_value2, ..., record1_valuen),
(record2_value1, record2_value2, ..., record2_valuen),
...,
(recordn_value1, recordn_value2, ..., recordn_valuen);

更新记录

1
2
3
UPDATE tablename
SET field1=value1, field2=value2, ..., fieldn=valuen
[WHERE condition];

在 MySQL 中，update命令可以同时更新多个表中的数据：

1
2
update t1, t2, ..., tn set t1.field1=expr1, ..., tn.fieldn=exprn
[where conditon];

删除记录

1
2
DELETE FROM tablename
[WHERE condition]

在 MySQL 中，可以一次删除多个表的数据：

1
2
DELETE t1, t2, ..., tn FROM t1, t2, ..., tn
[WHERE condition]

如果from后面的表名用别名，则delete后面也要用相应的别名，否则会提示语法错误。

查询记录

基本语法：

1
2
SELECT * FROM tablename
[WHERE condition];

查询不重复的记录：

1
SELECT DISTINCT colname FROM tablename;

排序与限制：

1
2
3
SELECT * FROM tablename
[WHERE condition]
[ORDER BY field1 [DESC|ASC], field2 [DESC|ASC], ..., fieldn [DESC|ASC];

DESC表示降序排列，如果不写排序关键字，默认为ASC升序排列。每个排序字段可以有不同的排列顺序。

如果排序字段的值一样，则继续按照第二个排序字段进行排序，以此类推。如果后续没有排序字段，则前面的字段相同的记录会无序排列。

对于排序后的记录，如果希望只显示一部分，可以使用LIMIT关键字来实现：

1
2
SELECT ...
[LIMIT [offset_start, ]row_count];

offset_start表示起始偏移量，row_count表示显示的行数。默认情况下起始偏移量为0，只需要写行数就可以，表示前n条记录。

聚合

1
2
3
4
5
6
SELECT [field1, field2, ..., fieldn] fun_name
FROM tablename
[WHERE where_condition]
[GROUP BY field1, field2, ..., fieldn
[WITH ROLLUP]]
[HAVING having_condition];

  • fun_name表示聚合函数，常用的有sum、count、avg、max、min。

  • GROUP BY表示要进行分类聚合的字段。

  • WITH ROLLUP是可选参数，表明对分类聚合后的结果进行再汇总。

  • HAVING表示对分类后的结果进行条件的过滤。

HAVING和WHERE的区别在于，HAVING是对聚合后的结果进行条件的过滤，而WHERE是在聚合前就对记录进行过滤。如果逻辑允许，尽可能用WHERE先过滤记录，因为这样结果集减小，聚合的效率将大大提高，然后再根据逻辑看是否用HAVING再进行过滤。

表连接

最常用的是内连接。例如：

1
2
select ename, deptname from emp, dept
where emp.deptno=dept.deptno;

外连接又分为左连接和右连接，二者可以相互转化：

1
2
select ename, deptname from emp left join dept on emp.deptno=dept.deptno;
select ename, deptname from dept right join emp on emp.deptno=dept.deptno;

子查询

进行查询的时候，需要的条件是另外一个select语句的结果。用于子查询的关键字主要包括in、not in、=、!=、exists、not exists等。

例如，从emp表中查询出所以部门在dept表中的所有记录：

1
2
3
select * from emp where deptnp in (
    select deptno from dept
);

如果子查询记录唯一，可以用=代替in。

某些情况下，子查询可以转化为表连接：

1
2
3
select * from emp where deptno in (select deptno from dept);
-- 等同于
select emp.* from emp, dept where emp.deptno=dept.deptno;

记录联合

将多个表的数据按照一定的查询条件查询出来后，将结果合并到一起显示出来，需要用到union和union all关键字。

1
2
3
4
5
6
SELECT * FROM t1
UNION | UNION ALL
SELECT * FROM t2
...
UNION | UNION ALL
SELECT * FROM tn;

UNION ALL是把结果集直接合并到一起，而UNION是把UNION ALL后的结果进行一次DISTINCT即去重。

DCL语句

创建一个用户z1，具有对sakila数据库中的所有表的SELECT/INSERT权限：

1
2
grant select, insert on sakila.*
to 'z1'@'localhost' identified by '123';

由于权限变更，收回INSERT，只能进行SELECT操作：

1
revoke insert on sakila.* from 'z1'@'localhost';

复杂查询

视图

视图保存的是SELECT语句，从视图中读取数据时，视图会在内部执行SELECT语句并创建一张临时表。这样有两个优点：

  • 由于无需保存数据，因此可以节省存储设备的容量。

  • 可以保存频繁使用的SELECT语句，不用每次都重新书写了，而且会随着原表的变化自动更新。

1
2
3
CREATE VIEW view_name(col1, col2, ...)
AS
<SELECT statement>

SELECT语句中列的排列顺序，要与视图中列的排列顺序相同。

视图的限制：

  • 定义时不能使用ORDER BY子句

  • 更新有严格限制，只有当定义视图的SELECT语句满足以下条件：

    • SELECT子句中未使用DISTINCT

    • FROM子句中只有一张表

    • 未使用GROUP BY子句

    • 未使用HAVING子句

删除视图：

1
DROP VIEW [IF EXISTS] view_name;

高级处理

窗口函数

语法

窗口函数的通用形式：

1
2
<窗口函数> OVER ([ PARTITION BY <列名> ]
                      ORDER BY <排序用列名> )

PARTITON BY子句（可选参数）指示如何将查询行划分为组，类似于GROUP BY子句的分组功能，但是 PARTITION BY子句并不具备GROUP BY子句的汇总功能，并不会改变原始表中记录的行数。

ORDER BY子句指示如何对每个分区中的行进行排序，即决定窗口内，是按哪种规则（字段）来排序的。

以窗口函数RANK()为例：

1
2
3
4
SELECT product_name, product_type, sale_price,
       RANK() OVER (PARTITION BY product_type
                        ORDER BY sale_price) AS ranking
  FROM product;

PARTITION BY能够设定窗口对象范围。本例中，为了按照商品种类进行排序，我们指定了product_type。即一个商品种类就是一个小的“窗口”。

ORDER BY能够指定按照哪一列、何种顺序进行排序。为了按照销售单价的升序进行排列，我们指定了sale_price。

专用窗口函数

常用的有：

  • RANK()：计算排序时，如果存在相同位次的记录，则会跳过之后的位次。例如有3个记录在第1位：1 1 1 4 …

  • DENSE_RANK()：计算排序，即使存在相同位次的记录，也不会跳过之后的位次。例如有3个记录在第1位：1 1 1 2 …

  • ROW_NUMBER()：赋予唯一的连续位次。例如有3个记录在第1位：1 2 3 4 …

1
2
3
4
5
SELECT product_name, product_type, sale_price,
       RANK() OVER (ORDER BY sale_price) AS ranking,
       DENSE_RANK() OVER (ORDER BY sale_price) AS dense_ranking,
       ROW_NUMBER() OVER (ORDER BY sale_price) AS row_num
  FROM product;

作为窗口函数使用的聚合函数

语法与专用窗口函数相同。但不像RANK()那样括号中的内容为空，而是与之前一样需要指定列。

1
2
3
4
SELECT product_id, product_name, sale_price,
       SUM(sale_price) OVER (ORDER BY product_id) AS current_sum,
       AVG(sale_price) OVER (ORDER BY product_id) AS current_avg
  FROM product;

计算移动平均

窗口函数就是将表以窗口为单位进行分割，并在其中排序或聚合的函数。实际上，还可以指定更加详细的汇总范围，称为框架 (frame)。

1
2
3
4
5
<窗口函数> OVER (ORDER BY <排序用列名>
                 ROWS n PRECEDING )  

<窗口函数> OVER (ORDER BY <排序用列名>
                 ROWS BETWEEN n PRECEDING AND m FOLLOWING)

PRECEDING将框架指定为“截止到之前 n 行”，加上自身行；

FOLLOWING将框架指定为“截止到之后 m 行”，加上自身行。

1
2
3
4
SELECT product_id, product_name, sale_price,
       AVG(sale_price) OVER (ORDER BY product_id
                              ROWS 2 PRECEDING) AS moving_avg
  FROM product;

1
2
3
4
SELECT product_id, product_name, sale_price,
       AVG(sale_price) OVER (ORDER BY product_id
                              ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg  
  FROM product;

框架指定为 “之前1行” + “之后1行” + “自身”。

注意事项

  • 原则上，窗口函数只能在SELECT子句中使用。
  • 窗口函数OVER中的ORDER BY子句并不会影响最终结果的排序，只是用来决定窗口函数按何种顺序计算。

存储过程

基本语法

1
2
3
4
5
6
7
8
[delimiter //]($$，可以是其他特殊字符，为了替换; ）
CREATE
    [DEFINER = user]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] 
[BEGIN]
  routine_body
[END//]($$，可以是其他特殊字符）

这些语句被用来创建一个存储例程（或函数）。也就是说，指定的例程被服务器知道了。默认情况下，一个存储例程与默认数据库相关联。要将该例程明确地与一个给定的数据库相关联，需要在创建该例程时将其名称指定为 db_name.sp_name。

使用 CALL 语句调用一个存储过程。而要调用一个存储的函数时，则要在表达式中引用它。在表达式计算期间，该函数返回一个值。

routine_body 由一个有效的SQL例程语句组成。它可以是一个简单的语句，如 SELECT 或 INSERT，或一个使用 BEGIN 和 END 编写的复合语句。复合语句可以包含声明、循环和其他控制结构语句。在实践中，存储函数倾向于使用复合语句，除非例程主体由一个 RETURN 语句组成。

参数介绍

存储过程和函数的参数有三类，分别是：IN，OUT，INOUT，其中：

  • IN 是入参。每个参数默认都是一个 IN 参数。如需设定一个参数为其他类型参数，请在参数名称前使用关键字 OUT 或 INOUT 。一个IN参数将一个值传递给一个过程。存储过程可能会修改这个值，但是当存储过程返回时，调用者不会看到这个修改。
  • OUT 是出参。一个 OUT 参数将一个值从过程中传回给调用者。它的初始值在过程中是 NULL ，当过程返回时，调用者可以看到它的值。
  • INOUT ：一个 INOUT 参数由调用者初始化，可以被存储过程修改，当存储过程返回时，调用者可以看到存储过程的任何改变。

对于每个 OUT 或 INOUT 参数，在调用过程的 CALL 语句中传递一个用户定义的变量，以便在过程返回时可以获得其值。如果你是在另一个存储过程或函数中调用存储过程，你也可以将一个常规参数或本地常规变量作为 OUT 或 INOUT 参数传递。如果从一个触发器中调用存储过程，也可以将 NEW.col_name 作为一个 OUT 或 INOUT 参数传递。

应用示例

给定一个国家代码，计算在 world 数据库的城市表中出现的该国家的城市数量。使用 IN 参数传递国家代码，使用 OUT 参数返回城市计数：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> DELIMITER //
mysql> DROP PROCEDURE IF EXISTS citycount //
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE PROCEDURE citycount (IN country CHAR(3), OUT cities INT)
       BEGIN
         SELECT COUNT(*) INTO cities FROM world.city
         WHERE CountryCode = country;
       END//
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql> CALL citycount('CHN', @cities); -- cities in China
Query OK, 1 row affected (0.01 sec)

    -> SELECT @cities;
+---------+
| @cities |
+---------+
|     363 |
+---------+
1 row in set (0.04 sec)

创建表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> use world;
Database changed
mysql> DELIMITER $$
mysql> CREATE DEFINER=`root`@`localhost` PROCEDURE `product_test`()
BEGIN
    #Routine body goes here...
    CREATE TABLE product_test like shop.product;
END$$
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER;
mysql> call `product_test`();
Query OK, 0 rows affected (0.04 sec)

mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
| product_test    |
+-----------------+
4 rows in set (0.02 sec)

插入数据：

1
2
3
4
5
6
7
8
9
10
11
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_product_test`()
BEGIN
    declare i int;
    set i=1;
    while i<9 do
        set @pcid = CONCAT('000', i);
        PREPARE stmt FROM 'INSERT INTO product_test() SELECT * FROM shop.product where product_id= ?';
        EXECUTE stmt USING @pcid;
        set i=i+1;
    end while;
END

本文作者：liyijie

本文链接：https://liyijie.cn/2024/SQL-commands/

文章默认以 署名-非商业性使用-相同方式共享 授权。

更新于 2024-03-20

# 数据库# SQL
上一篇
自动化运维（二）：Python
下一篇
LLM 学习笔记