SQL/MySQL--语句大全--增删改/库表/视图--命令大全/语法大全--分类/常用/示例/实例

一时失言乱红尘 2022-08-28 11:48 180阅读 0赞

原文网址：[SQL/MySQL--语句大全--增删改/库表/视图--命令大全/语法大全--分类/常用/示例/实例\_IT利刃出鞘的博客-CSDN博客][SQL_MySQL--_--_--_--_IT_-CSDN]

# 简介 #

本文介绍如下命令/语句：

*  数据增删改：插入数据、删除数据、修改（更新）数据
 *  数据库操作：创建数据库、删除数据库、查看数据库、导入导出
 *  表的操作：创建表、删除表、修改表；

SQL数据操作官网网址：[MySQL :: MySQL 8.0 Reference Manual :: 13.2 Data Manipulation Statements][MySQL _ MySQL 8.0 Reference Manual _ 13.2 Data Manipulation Statements]

# 数据 #

## 插入 ##

**插入**

<table> 
 <tbody> 
 <tr> 
 <td> 操作 </td> 
 <td style="width:406px;"> 命令/示例 </td> 
 <td style="width:221px;"> 说明 </td> 
 </tr> 
 <tr> 
 <td> 全列插入 </td> 
 <td style="width:406px;"> INSERT INTO&nbsp;表名 VALUES(...) 示例： 插入单个：INSERT INTO&nbsp;students VALUES(0, ’郭靖‘, 1, '蒙古', '2016-1-2'); 插入多个： INSERT INTO&nbsp;students VALUES (0, '郭靖', 1, '蒙古', '2016-1-2'), (0, '黄蓉', 1, '大陆', '2016-1-2'), </td> 
 <td style="width:221px;"> 值的顺序与表结构字段的顺序完全对应。此时，字段名列表不用填写 </td> 
 </tr> 
 <tr> 
 <td> 部分列插入 </td> 
 <td style="width:406px;"> INSERT INTO&nbsp;表名 (列1,...) VALUES(值1,...) 示例： INSERT INTO&nbsp;students(name, hometown, birthday) &nbsp; VALUES('黄蓉', '桃花岛', '2016-3-2'); </td> 
 <td style="width:221px;"> 值的顺序与给出的列顺序对应。此时需要根据实际的数据的特点 填写对应字段列表。 一次可以向表中插入一行数据，也可一次性插入多行数据，这样可减少与数据库的通信 </td> 
 </tr> 
 <tr> 
 <td> 全列多行插入 </td> 
 <td style="width:406px;"> INSERT INTO&nbsp;表名 VALUES(...),(...)...; 示例： INSERT INTO&nbsp;classes VALUES(0, 'python1'),(0, 'python2'); </td> 
 <td style="width:221px;"> </td> 
 </tr> 
 <tr> 
 <td> 部分列多行插入 </td> 
 <td style="width:406px;"> INSERT INTO&nbsp;表名(列1,...) VALUES(值1,...),(值1,...)...; 示例： INSERT INTO&nbsp;students(name) VALUES('杨康'),('杨过'),('小龙女'); </td> 
 <td style="width:221px;"> </td> 
 </tr> 
 <tr> 
 <td> 表1读出，插入表2 </td> 
 <td style="width:406px;"> 法1：SELECT col1, col2 into t2 from t1 法2：INSERT INTO&nbsp;t2(col1,col2,...) SELECT col1,col2,... from t1 </td> 
 <td style="width:221px;"> 法1：要求目标表t2不存在，因为在插入时会自动创建表t2，并将t1中指定字段数据复制到t2中。 法2：要求目标表t2必须存在，并且字段col1,col2...也必须存在。注意t2的主键约束，如果t2有主键而且不为空，则 col1，col2...中必须包括主键 </td> 
 </tr> 
 </tbody> 
</table>

**存在则更新，不存在则插入**

<table> 
 <tbody> 
 <tr> 
 <td style="width:107.2pt;"> 操作 </td> 
 <td style="width:218.85pt;"> 命令/示例 </td> 
 <td> 说明 </td> 
 </tr> 
 <tr> 
 <td style="width:107.2pt;"> ON DUPLICATE KEY UPDATE </td> 
 <td style="width:218.85pt;"> INSERT INTO `table` (`a`, `b`) VALUES&nbsp;(1, 2)&nbsp; ON DUPLICATE KEY UPDATE `b`=`b`+1; </td> 
 <td> 插入行时若导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则执行旧行UPDATE。 如果列a被定义为UNIQUE，并且包含值1，则以下两个语句具有相同的效果： INSERT INTO `table` (`a`, `b`) VALUES&nbsp;(1, 2)&nbsp; ON DUPLICATE KEY UPDATE `b`=`b`+1;&nbsp; UPDATE `table` SET `b`=`b`+1 WHERE `a`=1; </td> 
 </tr> 
 <tr> 
 <td style="width:107.2pt;"> REPLACE INTO </td> 
 <td style="width:218.85pt;"> REPLACE INTO&nbsp;tb(col_name, ...) VALUES(xxx, ...) REPLACE INTO&nbsp;tb(col_name, ...) SELECT &nbsp;... REPLACE INTO&nbsp;tb SET col_name=value, ... </td> 
 <td> &nbsp;&nbsp;&nbsp;&nbsp;若发现表中已有此行数据（根据主键或唯一索引判断）则先删除此行数据，然后插入新数据；否则，直接插入新数据。 插入数据的表必须有主键或者是唯一索引！否则，REPLACE INTO&nbsp;会直接插入数据，导致表中出现重复的数据。 若表包含多个唯一索引，并且新行复制了在不同的唯一索引中的不同旧行的值，则有可能是一个单一行替换了多个旧行。 注意： REPLACE INTO会删老记录，若表中有自增主键，会出问题。 频繁REPLACE INTO&nbsp;会造成新纪录的主键的值迅速增大。 </td> 
 </tr> 
 </tbody> 
</table>

## **删除** ##

<table> 
 <tbody> 
 <tr> 
 <td style="width:51pt;"> 操作 </td> 
 <td style="width:285pt;"> 命令/示例 </td> 
 <td style="width:267pt;"> 说明 </td> 
 </tr> 
 <tr> 
 <td style="width:51pt;"> 删除所有数据 </td> 
 <td style="width:285pt;"> TRUNCATE&nbsp;table table_name （table可省略） </td> 
 <td style="width:267pt;"> 整体删除 (速度较快) 不写服务器log 不激活trigger (触发器) 重置Identity (标识列、自增字段)，自增列重新从1开始记录 </td> 
 </tr> 
 <tr> 
 <td style="width:51pt;"> 删除所有数据 </td> 
 <td style="width:285pt;"> DELETE FROM&nbsp;table_name //DELETE&nbsp;* FROM&nbsp;table_name这样是不可以的。 </td> 
 <td style="width:267pt;"> 逐条删除 (速度较慢) 写服务器log 激活trigger (触发器) Identity( (标识列、自增字段)&nbsp;依旧是接着被删除的最近的那一条记录ID加1后进行记录。 修改自增起始位置的方法：ALTER TABLE table_name AUTO_INCREMENT=n; &nbsp;//n：想要开始的位置 </td> 
 </tr> 
 <tr> 
 <td style="width:51pt;"> 删除部分数据 </td> 
 <td style="width:285pt;"> DELETE&nbsp;FROM&nbsp;table_name WHERE&nbsp;条件 示例：DELETE&nbsp;FROM&nbsp;students WHERE&nbsp;id=5; </td> 
 <td style="width:267pt;"> </td> 
 </tr> 
 </tbody> 
</table>

## **修改** ##

<table> 
 <tbody> 
 <tr> 
 <td> 操作 </td> 
 <td> 命令/示例 </td> 
 <td> 说明 </td> 
 </tr> 
 <tr> 
 <td> 修改 </td> 
 <td> update 表名 set 列1=值1,列2=值2... where 条件 示例： update students set gender=0,hometown='北京' where id=5; </td> 
 <td> </td> 
 </tr> 
 </tbody> 
</table>

# 数据库操作 #

## **基本操作** ##

<table> 
 <tbody> 
 <tr> 
 <td> 操作 </td> 
 <td> 命令 </td> 
 <td> 示例 </td> 
 </tr> 
 <tr> 
 <td> 切换数据库 </td> 
 <td> use database 数据库名 </td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td> 查看所有数据库 </td> 
 <td> show databases; </td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td> 查看当前使用的数据库 </td> 
 <td> select database(); </td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td> 创建数据库 </td> 
 <td> create database&nbsp;数据库名 [[DEFAULT] CHARACTER SET [=] charset_name] [[DEFAULT] COLLATE [=] collation_name]; </td> 
 <td> create database my_database DEFAULT&nbsp;CHARACTER SET = utf8; </td> 
 </tr> 
 <tr> 
 <td> 删除数据库 </td> 
 <td> drop database 数据库名&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 或：drop database if exists 数据库名 </td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td> 查看数据库中所有表 </td> 
 <td> show tables; </td> 
 <td> </td> 
 </tr> 
 </tbody> 
</table>

## **导入导出** ##

<table> 
 <tbody> 
 <tr> 
 <td>操作</td> 
 <td>命令</td> 
 <td>示例</td> 
 </tr> 
 <tr> 
 <td>导出整个数据库</td> 
 <td>mysqldump -u 用户名 -p 数据库名 &gt; 导出的文件名</td> 
 <td>mysqldump -u dbuser -p dbname &gt; dbname.sql</td> 
 </tr> 
 <tr> 
 <td>导入整个数据库</td> 
 <td> 法1：mysql -u 用户名 -p密码&nbsp;[数据库名] &lt; sql文件名 如果数据库存在，或sql文件中没有创建数据库的语句，则需要指定数据库名；否则，无需指定。 法2：source </td> 
 <td> 法1：mysql -u 用户名 -p密码 [数据库名] &lt; sql文件名 法2： use abc; set names utf8; source /home/abc/abc.sql; </td> 
 </tr> 
 </tbody> 
</table>

# 表的操作 #

## **查看** ##

<table> 
 <tbody> 
 <tr> 
 <td style="width:107px;"> 操作 </td> 
 <td style="width:248px;"> 命令 </td> 
 <td style="width:334px;"> 示例 </td> 
 </tr> 
 <tr> 
 <td style="width:107px;"> 查看表结构 </td> 
 <td style="width:248px;"> DESC&nbsp;表名 </td> 
 <td style="width:334px;"> </td> 
 </tr> 
 <tr> 
 <td style="width:107px;"> 查看表引擎 </td> 
 <td style="width:248px;"> SHOW TABLE STATUS&nbsp;from db_name where name='table_name'; </td> 
 <td style="width:334px;"> </td> 
 </tr> 
 </tbody> 
</table>

## 创建 ##

示例

**示例**

CREATE TABLE t_class(
        id INT UNSIGNED AUTO_INCREMENT NOT NULL,
        name VARCHAR(10),
        PRIMARY KEY(id)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    CREATE TABLE t_student(
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        sid INT UNSIGNED DEFAULT null,
        name VARCHAR(20) DEFAULT '',
        age TINYINT UNSIGNED DEFAULT 0,
        gender enum('man','woman'),
        CONSTRAINT fk_student_class FOREIGN KEY (sid) REFERENCES t_class (id)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;

**命令详解**

CREATE \[TEMPORARY\] TABLE \[IF NOT EXISTS\] tbl\_name

(create\_definition,...) \[table\_options\] \[partition\_options\]

create\_definition:  
col\_name column\_definition \[index...\] \[CONSTRAINT...\]

column\_definition：（常用定义）

data\_type \[NOT NULL | NULL\] \[DEFAULT default\_value\]

\[AUTO\_INCREMENT\] \[\[PRIMARY\] KEY\]

table\_option:（常用选项）

ENGINE \[=\] ENGINE\_NAME

\[DEFAULT\] CHARACTER SET \[=\] CHARSET\_NAME

ENGINE\_NAME:（常用选项）

InnoDB, MyISAM, MEMORY, MERGE, ARCHIVE, ...

常用约束（CONSTRAINT）：

唯一约束：UNIQUE （不可重复）

非空约束：NOT NULL （不能为空必须指定值）

主键约束：PRIMARY KEY (同时有UNIQUE和NOT NULL约束)

外键约束：FOREIGN KEY

详细见：help ‘CREATE TABLE’;

## **修改** ##

**修改表（普通）**

<table> 
 <tbody> 
 <tr> 
 <td> 操作 </td> 
 <td> 命令 </td> 
 <td> 示例 </td> 
 </tr> 
 <tr> 
 <td> 修改编码 </td> 
 <td> ALTER TABLE&nbsp;`table_name` CONVERT TO&nbsp;CHARACTER SET utf8; </td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td> 修改表引擎 </td> 
 <td> ALTER TABLE&nbsp;table_name ENGINE='引擎名'; </td> 
 <td> ALTER TABLE&nbsp;table_name ENGINE=InnoDB; </td> 
 </tr> 
 <tr> 
 <td> 添加字段 </td> 
 <td> ALTER TABLE&nbsp;表名 ADD 列名 类型 </td> 
 <td> ALTER TABLE&nbsp;t_students ADD birthday datetime; </td> 
 </tr> 
 <tr> 
 <td> 修改字段–重命名 </td> 
 <td> ALTER TABLE&nbsp;表名 CHANGE 原名 新名 类型及约束 </td> 
 <td> ALTER TABLE&nbsp;t_students CHANGE birthday birth DATATIME NOT NULL; </td> 
 </tr> 
 <tr> 
 <td> 修改字段–不重命名 </td> 
 <td> ALTER TABLE&nbsp;表名 modify 列名 类型及约束 </td> 
 <td> ALTER TABLE&nbsp;t_students MODIFY birth date NOT NULL; </td> 
 </tr> 
 </tbody> 
</table>

**修改表（约束）**

ALTER TABLE 表名 ADD CONSTRAINT 约束名称 增加的约束类型 （列名）

<table> 
 <tbody> 
 <tr> 
 <td> 操作 </td> 
 <td> 命令 </td> 
 <td> 示例 </td> 
 </tr> 
 <tr> 
 <td> 主键约束 </td> 
 <td> ALTER TABLE&nbsp;表名 ADD CONSTRAINT 约束名 PRIMARY KEY&nbsp;(列名)&nbsp; </td> 
 <td> ALTER TABLE&nbsp;t_emp ADD CONSTRAINT xxx PRIMARY KEY(`id`); </td> 
 </tr> 
 <tr> 
 <td> 唯一约束 </td> 
 <td> ALTER TABLE&nbsp;表名 ADD CONSTRAINT 约束名 UNIQUE&nbsp;(列名)&nbsp; </td> 
 <td> </td> 
 </tr> 
 <tr> 
 <td> 外键约束 </td> 
 <td> ALTER TABLE&nbsp;表名 ADD CONSTRAINT 约束名 FOREIGN KEY(外键列名) references 外表表名(外表列名) [on ref_options] </td> 
 <td> ALTER TABLE&nbsp;tb1 ADD CONSTRAINT xxx FOREIGN KEY(`gid`) REFERENCES tb2(`id`); </td> 
 </tr> 
 <tr> 
 <td> 默认约束 </td> 
 <td> ALTER TABLE&nbsp;表名 ADD CONSTRAINT 约束名 约束类型 默认值 FOR 列名 </td> 
 <td> ALTER TABLE&nbsp;t_emp ADD CONSTRAINT xxx DEFAULT 20 FOR age </td> 
 </tr> 
 <tr> 
 <td> CHECK约束 </td> 
 <td> ALTER TABLE&nbsp;表名 ADD CONSTRAINT 约束名 CHECK(条件) </td> 
 <td> ALTER TABLE&nbsp;t_emp ADD CONSTRAINT xxx CHECK(age&gt;20) </td> 
 </tr> 
 </tbody> 
</table>

## 删除 ##

**删除表（普通）**

<table> 
 <tbody> 
 <tr> 
 <td> 操作 </td> 
 <td> 命令 </td> 
 <td> 示例 </td> 
 </tr> 
 <tr> 
 <td colspan="3"> 删除表（普通） </td> 
 </tr> 
 <tr> 
 <td> 删除表 </td> 
 <td> DROP TABLE&nbsp;表名 DROP TABLE&nbsp;IF EXISTS 表名 </td> 
 <td> DROP TABLE&nbsp;t_students; DROP TABLE&nbsp;IF EXISTS t_students; </td> 
 </tr> 
 <tr> 
 <td> 删除字段 </td> 
 <td> ALTER TABLE&nbsp;表名 DROP&nbsp;列名 示例：ALTER TABLE&nbsp;students DROP&nbsp;birthday;&nbsp; </td> 
 <td> </td> 
 </tr> 
 </tbody> 
</table>

**删除表（索引）**

# 视图 #

命令

<table> 
 <tbody> 
 <tr> 
 <td> 操作 </td> 
 <td> 命令 </td> 
 <td> 示例/说明 </td> 
 </tr> 
 <tr> 
 <td> 创建视图 </td> 
 <td> CREATE VIEW view_name AS SELECT语句 </td> 
 <td> CREATE VIEW product_customers AS SELECT cust_name, cust_contact, prod_id FROM customers, orders, orderitems WHERE customers.cust_id = orders.cust_id &nbsp; &nbsp; AND orderitems.order_num = orders.order_num </td> 
 </tr> 
 <tr> 
 <td> 删除视图 </td> 
 <td> DROP VIEW view_name </td> 
 <td> DROP VIEW product_customers </td> 
 </tr> 
 <tr> 
 <td> 修改视图 </td> 
 <td> 先DROP再CREATE 或 CREATE OR REPLEASE VIEW view_name AS SELECT语句 </td> 
 <td> CREATE OR REPLACE VIEW product_customers AS SELECT cust_name, cust_contact, prod_id FROM customers, orders, orderitems WHERE customers.cust_id = orders.cust_id &nbsp; &nbsp; AND orderitems.order_num = orders.order_num </td> 
 </tr> 
 </tbody> 
</table>

用法：

SELECT cust_name, cust_contact
    FROM product_customers
    WHERE prod_id = 'TNT2';

[SQL_MySQL--_--_--_--_IT_-CSDN]: https://knife.blog.csdn.net/article/details/120519328
[MySQL _ MySQL 8.0 Reference Manual _ 13.2 Data Manipulation Statements]: https://dev.mysql.com/doc/refman/8.0/en/sql-data-manipulation-statements.html