常用的DML语句

浅浅的花香味﹌ 2022-03-21 16:28 294阅读 0赞

# DML #

数据操纵语言（Data Manipulation Language, DML）是用于数据库操作，对数据库其中的对象和数据运行访问工作的编程语句，通常是数据库专用编程语言之中的一个子集，例如在信息软件产业通行标准的SQL语言中，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入(意指新增或创建)、更新(修改)与删除(销毁)。在使用数据库的系统开发过程中，其中应用程序必然会使用的指令；而加上 SQL的SELECT语句，简单来说就是增删改查。

### INSERT ###

INSERT 是将数据插入到数据库对象中的指令，可以插入数据的数据库对象,有数据表以及可更新查看表两种。

-- 基本语法
    INSERT [INTO] tbl_name(col1,col2,col3) values(var1,var2,var3)

表结构

示例1：插入单行数据

INSERT INTO t1(id,name) VALUES(1,'li');

示例2: 插入多行数据

INSERT INTO t1(id,name) VALUES
    	(2,'zhang'),
    	(3,'wang'),
    	(4,'liao');

示例3:将t1表数据插入到t2表中

CREATE TABLE t2 LIKE t1;
    INSERT INTO t2 SELECT * FROM t1;

### DELETE ###

DELETE 指令为自数据库对象中删除数据的指令

-- 基本语法
    DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
        [PARTITION (partition_name [, partition_name] ...)]
        [WHERE where_condition]
        [ORDER BY ...]
        [LIMIT row_count]

表结构：

mysql> SELECT * FROM t1;
    +----+-------+
    | id | name  |
    +----+-------+
    |  1 | li    |
    |  2 | zhang |
    |  3 | wang  |
    |  4 | liao  |
    +----+-------+
    4 rows in set (0.00 sec)

示例1：删除id为3的行

DELETE FROM t1 
    WHERE
        id = 3

如果不加`where`子句就会直接删除表里面的数据。一般在线上很少有全表删除的需求。即使有也很少用`DELETE`删除，因为`DELETE`是逻辑删除很慢，如果真的要删全表一般是用

TRUNCATE TABLE  table_name

也可以使用 `DROP TABLE`命令来删除整个数据表，不过 `DROP TABLE` 命令不但会删除表中所有数据，还会将整个表结构从数据库中移除。如果想要重新向表中存储数据的话，必须重建该数据表。而`TRUNCATE TABLE`是物理删除，直接删除数据文件中的数据，但是会保留表结构，相当于只是清空表，而不是移除表

在生产环境中有时候数据表会多一列表示数据是否可见，如果删除则只需要吧数据标为不可见就行，这样避免了误删除会更安全。

### UPDATE ###

UPDATE 指令是依给定条件，将匹配条件的数据表中的数据更新为新的数值

-- 基本语法
    UPDATE [LOW_PRIORITY] [IGNORE] table_reference
        SET assignment_list
        [WHERE where_condition]
        [ORDER BY ...]
        [LIMIT row_count]

表结构

mysql> select * from t1;
    +----+-------+
    | id | name  |
    +----+-------+
    |  1 | li    |
    |  2 | zhang |
    |  4 | liao  |
    +----+-------+
    3 rows in set (0.00 sec)

示例：将id为4的数据行name改为he

UPDATE t1 
    SET 
        name = 'he'
    WHERE
        id = 4

### SELECT ###

SELECT是SQL数据操纵语言(DML)中用于查询表格内字段数据的指令，可搭配条件限制的子句(如where)或排列顺序的子句(如order)来获取查询结果.

-- 基本语法
    SELECT
        [ALL | DISTINCT | DISTINCTROW ]
          [HIGH_PRIORITY]
          [STRAIGHT_JOIN]
          [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
          [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
        select_expr [, select_expr ...]
        [FROM table_references
          [PARTITION partition_list]
        [WHERE where_condition]
        [GROUP BY {col_name | expr | position}
          [ASC | DESC], ... [WITH ROLLUP]]
        [HAVING where_condition]
        [ORDER BY {col_name | expr | position}
          [ASC | DESC], ...]
        [LIMIT {
       [offset,] row_count | row_count OFFSET offset}]
        [PROCEDURE procedure_name(argument_list)]
        [INTO OUTFILE 'file_name'
            [CHARACTER SET charset_name]
            export_options
          | INTO DUMPFILE 'file_name'
          | INTO var_name [, var_name]]
        [FOR UPDATE | LOCK IN SHARE MODE]]

相关的表结构

-- Products存储产品信息。
    mysql> mysql> SELECT * FROM Products;
    +---------+---------+---------------------+------------+-----------------------------------------------------------------------+
    | prod_id | vend_id | prod_name           | prod_price | prod_desc                                                             |
    +---------+---------+---------------------+------------+-----------------------------------------------------------------------+
    | BNBG01  | DLL01   | Fish bean bag toy   |       3.49 | Fish bean bag toy, complete with bean bag worms with which to feed it |
    | BNBG02  | DLL01   | Bird bean bag toy   |       3.49 | Bird bean bag toy, eggs are not included                              |
    | BNBG03  | DLL01   | Rabbit bean bag toy |       3.49 | Rabbit bean bag toy, comes with bean bag carrots                      |
    | BR01    | BRS01   | 8 inch teddy bear   |       5.99 | 8 inch teddy bear, comes with cap and jacket                          |
    | BR02    | BRS01   | 12 inch teddy bear  |       8.99 | 12 inch teddy bear, comes with cap and jacket                         |
    | BR03    | BRS01   | 18 inch teddy bear  |      11.99 | 18 inch teddy bear, comes with cap and jacket                         |
    | RGAN01  | DLL01   | Raggedy Ann         |       4.99 | 18 inch Raggedy Ann doll                                              |
    | RYL01   | FNG01   | King doll           |       9.49 | 12 inch king doll with royal garments and crown                       |
    | RYL02   | FNG01   | Queen doll          |       9.49 | 12 inch queen doll with royal garments and crown                      |
    +---------+---------+---------------------+------------+-----------------------------------------------------------------------+
    9 rows in set (0.00 sec)

-- Vendors存储供应商信息，每个供应商占一行。
    mysql> SELECT * FROM Vendors;
    +---------+-----------------+-----------------+------------+------------+----------+--------------+
    | vend_id | vend_name       | vend_address    | vend_city  | vend_state | vend_zip | vend_country |
    +---------+-----------------+-----------------+------------+------------+----------+--------------+
    | BRE02   | Bear Emporium   | 500 Park Street | Anytown    | OH         | 44333    | USA          |
    | BRS01   | Bears R Us      | 123 Main Street | Bear Town  | MI         | 44444    | USA          |
    | DLL01   | Doll House Inc. | 555 High Street | Dollsville | CA         | 99999    | USA          |
    | FNG01   | Fun and Games   | 42 Galaxy Road  | London     | NULL       | N16 6PS  | England      |
    | FRB01   | Furball Inc.    | 1000 5th Avenue | New York   | NY         | 11111    | USA          |
    | JTS01   | Jouets et ours  | 1 Rue Amusement | Paris      | NULL       | 45678    | France       |
    +---------+-----------------+-----------------+------------+------------+----------+--------------+
    6 rows in set (0.00 sec)

（这里的测试数据来自《SQL必知必会》，下载地址在 [http://www.forta.com/books/0672336073/）][http_www.forta.com_books_0672336073]

##### 检索所有列 #####

SELECT 
        *
    FROM
        Products

##### 检索单个列 #####

-- 检索Products表中的prod_id列
    SELECT 
        prod_id
    FROM
        Products

##### 检索多个列 #####

-- 检索Product表中prod_id,prod_name,vend_id三个列
    SELECT 
        prod_id, prod_name, vend_id
    FROM*
        Products

##### 等值查询 #####

-- 查看prod_id为BR01的行
    SELECT 
        *
    FROM
        Products
    WHERE
        prod_id = 'BR01'

##### 模糊匹配 #####

-- 查询prod_name 以1开头的行
    SELECT 
        *
    FROM
        Products
    WHERE
        prod_name LIKE '1%'

##### 范围查询 #####

范围查询一般会用到`OR`,`IN`。  
除此之外还有一些操作符比如`AND`,`>`,`<`,`<=`,`>=`,`<>`,`NOT`等

示例1：查询金额（prod\_price）大于9的商品

SELECT 
        *
    FROM
        Products
    WHERE
        prod_price > 9

示例2：查询供应商id（vend\_id）为BRS01或FNG01的商品

SELECT 
        *
    FROM
        Products
    WHERE
        vend_id = 'BRS01' OR vend_id = 'FNG01'

或

SELECT 
        *
    FROM
        Products
    WHERE
        vend_id IN ('BRS01' , 'FNG01')

这两种方法是相同的，性能也是相同的，非常差一般可以用`UNION`改写，来提高性能

##### UNION #####

UNION用于把两个或者多个select查询的结果集合并成一个，进行合并的两个查询，其SELECT列表必须在数量和对应列的数据类型上保持一致，默认会去掉两个查询结果集中的重复行，默认结果集不排序，最终结果集的列名来自于第一个查询的SELECT列表

可以将上面的`OR`与`IN`语句改为下面的形式可以提高性能

SELECT 
        *
    FROM
        Products
    WHERE
        vend_id = 'BRS01' 
    UNION ALL SELECT 
        *
    FROM
        Products
    WHERE
        vend_id = 'FNG01'

> `UNION`会将结果去重，`UNION ALL`则不会去重复，所以一般`UNION ALL`性能高于`UNION`

##### ORDER BY #####

用来排序行,如果SELECT语句中没有ORDER BY子句，那么结果集中行的顺序是不可预料的

示例1：按价格排序(默认升序)

SELECT 
        *
    FROM
        Products
    ORDER BY prod_price

示例2：按价格排序（降序使用DESC）

SELECT 
        *
    FROM
        Products
    ORDER BY prod_price DESC

##### LIMIT #####

从结果集中选取行

示例1：按价格升序排列，只取第一行

SELECT 
        *
    FROM
        Products
    ORDER BY prod_price
    LIMIT 1

示例2：按价格升序排列，取2到4行

SELECT 
        *
    FROM
        Products
    ORDER BY prod_price
    LIMIT 3 OFFSET 1  --表示取3行，偏移量为1

或

SELECT 
        *
    FROM
        Products
    ORDER BY prod_price
    LIMIT 1 , 3 --表示取3行，偏移量为1

##### Group By #####

分组，`Group By`必须在`WHERE`之后`ORDER BY`之前，

SELECT 
        vend_id, COUNT(*) AS num_prods
    FROM
        Products
    GROUP BY vend_id

##### HAVING #####

`WHERE`子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分  
组之前过滤数据， where条件中不能包含聚组函数，使用where条件过滤出特定的行。  
`HAVING`子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚  
组函数，使用having 条件过滤出特定的组，也可以使用多个分组标准进行分组。

SELECT 
        vend_id, COUNT(*) AS num_prods
    FROM
        Products
    GROUP BY vend_id
    HAVING COUNT(*) > 2

##### 多表连接 #####

示例1：`WHERE`子句完成内连接  
也叫等值连接，主要是用`where`子句，基于两个表之间做相等测试。内联结相对于是查找出两张或两张以上表的交集  
![在这里插入图片描述][20190204135112729.png]

-- 查看产品名称，价格对应的供应商名称
    SELECT 
        vend_name, prod_name, prod_price
    FROM
        Vendors,
        Products
    WHERE
        Vendors.vend_id = Products.vend_id

示例2：内连接`INNER JOIN`

SELECT 
        *
    FROM
        Vendors
            INNER JOIN
        Products ON Vendors.vend_id = Products.vend_id

> MySQL默认的`JOIN`是`INNER JOIN`

示例3：自然连接`NATURAL JOIN`  
自然连接是在两张表中寻找那些数据类型和列名都相同的字段，然后自动地将他们连接起来，并返回所有符合条件按的结果

SELECT 
        *
    FROM
        Vendors
            NATURAL JOIN
        Products

> `INNER JOIN`与`NATURAL JOIN`有一些不同， `INNER JOIN`返回的所有列，包括值相等的列，比如这里例子是利用两个表中 vend\_id列进行联结，如果用 `INNER JOIN`会返回所有列，包括两个表的vend\_id列，如果是使用`NATURAL JOIN`则会避免重复的列，也就是vend\_id只显示一列

示例4：外连接  
在左外连接和右外连接时都会以一张表为基表，该表的内容会全部显示，然后加上两张表匹配的内容。 如果基表的数据在另一张表没有记录。 那么在相关联的结果集行中列显示为空值（NULL）

左外连接  
![在这里插入图片描述][20190204135143938.png]

SELECT 
        *
    FROM
        Products AS pro
            LEFT JOIN
        Vendors AS ven ON pro.vend_id = ven.vend_id
            AND ven.vend_state = 'CA'

右外连接  
右外连接与左外连接可以相互转换。

SELECT 
        *
    FROM
        Vendors AS ven
            LEFT JOIN
        Products AS pro ON pro.vend_id = ven.vend_id
            AND ven.vend_state = 'CA'

![在这里插入图片描述][2019020413520566.png]  
多个连接条件要用`AND`

参考链接

*  官方文档
 *  《MySQL必知必会》人民邮电出版社

[http_www.forta.com_books_0672336073]: http://www.forta.com/books/0672336073/%EF%BC%89
[20190204135112729.png]: /images/20220321/7ff33acc344b4c10940372412948e31e.png
[20190204135143938.png]: /images/20220321/c2d107fc329c40e5846cb2790bc23e95.png
[2019020413520566.png]: /images/20220321/fdb619dfd26746718f90ee38918ff845.png