Mysql 基础操作：DDL、DML、CRUD 与常用命令

超、凢脫俗 2023-06-06 12:13 31阅读 0赞

**目录**

DDL 数据库相关

DDL 数据表相关

数据库 DML 语句

花样 insert into

数据库 DQL 基本查询

数据库 DQL 复合查询

MySQL 常用命令

where、from、exists 子查询

--------------------

[sql/Mysql 新建部门与员工表.sql · 汪少棠/my-document - Gitee.com][sql_Mysql _.sql _ _my-document - Gitee.com]

**SQL 语句分类：**

<table> 
 <tbody> 
  <tr> 
   <td> <p>DDL : 数据定义语句 (create alter drop)</p> <p>DML : 数据操作语句 (insert update delete)</p> <p>DQL : 数据查询语句(select)</p> <p>DCL : 数据控制语句(grant revoke commit rollback)</p> </td> 
  </tr> 
 </tbody> 
</table>

# DDL 数据库相关 #

<table> 
 <tbody> 
  <tr> 
   <td>显示 mysql 中所有数据库</td> 
   <td> <p>SHOW DATABASES ;</p> </td> 
  </tr> 
  <tr> 
   <td>显示数据库 db_name 的创建语句</td> 
   <td>SHOW CREATE DATABASE db_name ;</td> 
  </tr> 
  <tr> 
   <td>创建数据库</td> 
   <td> <p>CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, create_specification] ...]</p> <p>create_specification 可选值有:&nbsp;&nbsp;&nbsp;</p> <p>[DEFAULT] CHARACTER SET charset_name&nbsp; ：指定数据库采用的字符集，如 utf8、utf8mb4 等</p> <p>[DEFAULT] COLLATE collation_name ：指定数据库字符集的排序规则，如 utf8_general_ci、utf8_bin 等</p> </td> 
  </tr> 
  <tr> 
   <td>创建数据库 db_wmx，数据库使用 utf-8 编码,utf8_bin 排序规则</td> 
   <td> <p>CREATE DATABASE if NOT EXISTS db_wmx CHARACTER SET utf8 COLLATE utf8_bin;</p> </td> 
  </tr> 
  <tr> 
   <td>删除数据库 db_name</td> 
   <td>DROP DATABASE [IF EXISTS] db_name ;</td> 
  </tr> 
 </tbody> 
</table>

# DDL 数据表相关 #

<table> 
 <tbody> 
  <tr> 
   <td>查询当前数据库下所有的表名</td> 
   <td> <p>show TABLES;</p> </td> 
  </tr> 
  <tr> 
   <td>显示 person 创建表语句</td> 
   <td>show CREATE TABLE person;</td> 
  </tr> 
  <tr> 
   <td><strong>删除表</strong></td> 
   <td>DROP TABLE table_name;</td> 
  </tr> 
  <tr> 
   <td colspan="1"><strong>创建表</strong></td> 
   <td> <p>CREATE TABLE table_name(</p> <p>列名1 数据类型,</p> <p>列名2 数据类型,</p> <p>..</p> <p>列名n 数据类型</p> <p>) character set 字符集 collate 校对规则 engine 存储引擎方式</p> </td> 
  </tr> 
  <tr> 
   <td>1）字符集不指定时，默认与数据库保持一致，如 utf8、utf8mb4 等<br> 2）排序规则不指定时，默认与数据库保持一致，如 utf8_general_ci、utf8_bin 等<br> 3）存储引擎方式不指定时，默认与数据库保存一致，如 InnoDB 或者 MyISAM 等。</td> 
  </tr> 
  <tr> 
   <td colspan="1"><strong>修改表</strong></td> 
   <td>使用 ALTER TABLE 语句追加, 修改, 或删除列的语法.</td> 
  </tr> 
  <tr> 
   <td> <p>ALTER TABLE tablename ADD (column datatype [DEFAULT expr] [, column datatype]...); -- 追加列</p> <p>ALTER TABLE tablename MODIFY column datatype [DEFAULT expr]; -- 修改列</p> <p>ALTER TABLE tablename DROP (column); -- 删除列</p> <p>rename table 表名 to 新表名; -- 修改表的名称</p> <p>alter table student character set utf8; -- 修改表的字符集</p> </td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

-- 创建 book 表，结尾的字符集、排序规则、存储引擎通常不写与数据库保存默认即可
    CREATE TABLE if NOT EXISTS book (
      id int(11) NOT NULL AUTO_INCREMENT,
      title varchar(128) NOT NULL,
      info varchar(128) DEFAULT NULL,
      code varchar(64) DEFAULT NULL,
      PRIMARY KEY (id)
    ) CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB ;
    
    -- 新增 code 列：不允许为 null，默认值为 -，指定注释，如果表中已经有数据，则都会赋值为 -.
    -- FIRST：表示新增的列位于第一列，否则默认新增的列位于最后一列
    ALTER TABLE book ADD code VARCHAR(256) NOT NULL DEFAULT '-' COMMENT '编码' FIRST;
    -- AFTER xxx：表示新增的列位于 xxx 列的后面, 否则默认新增的列位于最后一列
    ALTER TABLE book ADD code2 VARCHAR(256) NOT NULL DEFAULT '-' COMMENT '二编码' AFTER title; 
    -- 修改 code2 列类型：同样需要指定数据类型，约束，注释等信息，可以使用 AFTER 关键字移动列的顺序.
    ALTER TABLE book MODIFY code2 VARCHAR(512) NOT NULL DEFAULT '-' COMMENT '二编码' AFTER type_id; 
    -- 修改列名称: code 改为 codes，同样需要指定列类型、约束、描述等信息.
    ALTER TABLE book CHANGE COLUMN code codes VARCHAR(256) NOT NULL; 
    ALTER TABLE book DROP codes; -- 删除 book 表的 codes 列
    RENAME TABLE books to book; -- 修改 book 表名为 books

# 数据库 DML 语句 #

1、使用 **INSERT 语句**向表中插入数据：INSERT INTO tablename \[(column \[, column...\])\] VALUES (value \[, value...\]);

<table> 
 <tbody> 
  <tr> 
   <td> <p>1）values 后插入的数据必须与字段的数据类型相同，位置必须与被加入的列的排列位置相对对应。</p> <p>2）数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。</p> <p>3）字符和日期型数据应包含在单引号中。</p> <p>4）想要插入空值时，表名后可以不指定列，或指定列并设置列的值为 null.</p> <p>5）表名后面不指定列时，默认为所有列插入数据.</p> </td> 
  </tr> 
 </tbody> 
</table>

INSERT INTO book(id,title,summary,code) VALUES(NULL,'西游记','经典',88.00); -- 插入数据
    -- 单条 sql 插入多组数据
    INSERT INTO dept(dname, loc) VALUES ('Java研发中心', '长沙麓谷'),('大数据开发中心','深圳科技园');
    INSERT INTO book VALUES(NULL,'水浒传','经典名著',98.00); -- 插入全部数据时，列名可以省略，values 中按顺序传入
    CREATE TABLE book_back SELECT * from book; -- 使用查询结果建表，相当于复制 book 表，然后新建了 book_back 表

2、使用 **update 语句**修改表中数据：UPDATE tbl\_name SET col\_name1=expr1 \[, col\_name2=expr2 ...\] \[WHERE where\_definition\]

<table> 
 <tbody> 
  <tr> 
   <td> <p>UPDATE 语法可以用新值更新原有表行中的各列。</p> <p>SET 子句指示要修改哪些列和要给予哪些值。</p> <p>WHERE 子句指定应更新哪些行，如没有 WHERE 子句，则更新所有的行。</p> </td> 
  </tr> 
 </tbody> 
</table>

UPDATE book set summary = '四大名著' WHERE title = '西游记'; -- 修改 西游记的描述为 四大名著

3、使用 **delete 语句**删除表中数据：delete from tbl\_name \[WHERE where\_definition\]

<table> 
 <tbody> 
  <tr> 
   <td> <p>如果不使用 where 子句，将删除表中所有数据。</p> <p>使用 delete 语句仅删除记录，不删除表本身。如要删除表，使用 drop table tbl_name 语句。</p> <p>同 insert 和 update 一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题。</p> <p>删除表中数据也可使用 TRUNCATE TABLE tbl_name 语句，delete 删除的数据可以恢复，truncate 虽然效率更高，但是删除的数据不可恢复，慎用。</p> </td> 
  </tr> 
 </tbody> 
</table>

DELETE FROM book WHERE title='西游记' ; -- 删除西游记
    DELETE FROM book ; -- 删除 book 表所有数据
    TRUNCATE book;  -- 删除 book 表所有数据

# 花样 insert into #

1、为了避免往数据库中插入重复数据，最常见的方式就是为字段设置唯一索引/约束，当插入重复数据时，抛出错误信息。

2、mysql 也能实现类似持久化框架一样功能，比如：如果数据不存在，则新增，已经存在时，则更新，或者先删除旧数据，然后插入新数据等等类似的功能。

3、mysql 判断数据是否重复的依据是：主键或者唯一约束，当插入的新数据的主键已经存在，或者新数据违反唯一约束时，都判定为重复数据，所以前提条件是插入的数据字段设置了主键或唯一索引。

3、演示表结构如下，pId 主键自增，pName 姓名，id\_card 身份证(带唯一索引约束)，salary 薪水，summary 描述。

**数据准备**：[sql/mysql/新建 person 用户表.sql · 汪少棠/material - Gitee.com][sql_mysql_ person _.sql _ _material - Gitee.com]

<table> 
 <tbody> 
  <tr> 
   <td><strong>insert ignore into</strong></td> 
   <td>插入数据时，如果数据存在，则忽略此次插入，当插入新数据时，MySQL 数据库会首先检索已有数据，如果存在，则忽略本次插入，如果不存在，则正常插入数据。</td> 
  </tr> 
  <tr> 
   <td><strong>on duplicate key update</strong></td> 
   <td>插入数据时，如果数据存在，则执行更新操作，如果不存在，则直接插入，注意更新的数据是 on duplicate key update 关键字后面的字段。</td> 
  </tr> 
  <tr> 
   <td><strong>replace into</strong></td> 
   <td>插入数据时，如果数据存在，则删除后再插入，如果不存在，则直接插入。</td> 
  </tr> 
 </tbody> 
</table>

INSERT ignore  INTO person(pId,pName,id_card,salary,summary) VALUES (13,'张无忌', '110101199003077467', '9999.99', '武学奇才');
    -- 如果存在 pId=13 的数据，则本次不会插入，如果存在 id_card=110101199003077467 的数据，本次也不会插入
    
    INSERT ignore  INTO person(pName,id_card,salary,summary) VALUES ('张无忌', '110101199003077467', '9999.99', '武学奇才');
    -- 如果存在 id_card=110101199003077467 的数据，本次不会插入
    
    INSERT INTO person(pName,id_card,salary,summary) VALUES ('张无忌', '110101199003077417', '888.99', '武学大才') on duplicate key update salary=3333,summary='武学巅峰';
    -- 如果存在 id_card=110101199003077467 的数据，且 on duplicate key update 后面的字段值与旧数据不相同时，则更新 on duplicate key update 后面的字段值
    -- 如果不存在 id_card=110101199003077467 的数据，则直接新增，此时 on duplicate key update 后面的值不会处理
    
    REPLACE INTO person(pName,id_card,salary,summary) VALUES ('张无忌', '110101199003077417', '12323.99', '武学大才');
    -- 如果存在 id_card=110101199003077467 的数据，则先删除旧数据，然后插入新数据

![20200915203021563.gif][]

# 数据库 DQL 基本查询 #

数据准备：[sql/mysql/新建书籍与分类表.sql · 汪少棠/material - Gitee.com][sql_mysql_.sql _ _material - Gitee.com]

1、基本 **select 语句**格式：

SELECT
    	[ DISTINCT ] *|{ column1 | expression1, column2 | expression2.column3 | expression3..}
    FROM 表名
    WHERE 表达式
    GROUP BY 列1,列2...
    HAVING 表达式
    ORDER BY 列1 ASC | DESC, 列2 ASC | DESC
    LIMTT offset, number; 
    
    -- GROUP BY xxx HAVING 分组通常与聚合函数一起使用：max(最大值)、min(最小值)、sum(求和)、avg(平均值)、count(总行数)
    -- limit 用于限制结果条数(分页)，offset 表示偏移量/起始行位置，从0开始，不写时默认为0，number 表示取的条数.
    -- where 条件针对的是表中的列进行查询，having 条件是针对查询结果进行再次过滤
    -- Order by 指定排序的列，排序的列即可是表中的列名，也可以是 as 设置的别名，Asc 升序（默认）、Desc 降序。

2、可使用 as 关键字设置列或者表的别名：SELECT columnname as 别名 from 表名 as 别名;

3、表达式中常用的运算符有：

<table> 
 <thead> 
  <tr> 
   <th style="width:203px;">运算符</th> 
   <th style="width:485px;">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="width:203px;">&lt;, &lt;= , &gt;, &gt;=, =, !=</td> 
   <td style="width:485px;">比较运算符</td> 
  </tr> 
  <tr> 
   <td style="width:203px;">BETWEEN ...AND...</td> 
   <td style="width:485px;">显示在某一区间的值</td> 
  </tr> 
  <tr> 
   <td style="width:203px;">IN(set)，not in(set)</td> 
   <td style="width:485px;">显示在in列表中的值，例：in(100,200)，括号中的个数不建议超过 1000</td> 
  </tr> 
  <tr> 
   <td style="width:203px;">LIKE '张pattern'</td> 
   <td style="width:485px;"> <p>模糊查询.Like语句中，% 代表零个或多个任意字符，_ 代表一个字符</p> <p>like '%xxx%' 表示包含 xxx，like 'xxx%' 表示以 xxx 开头的，like '%xxx' 表示以 xxx 结尾的。</p> </td> 
  </tr> 
  <tr> 
   <td style="width:203px;">IS NULL，is not null</td> 
   <td style="width:485px;">判断是否为null，或者不为&nbsp; null.</td> 
  </tr> 
  <tr> 
   <td style="width:203px;">and、or、not</td> 
   <td style="width:485px;">逻辑运算符，与 或 非，例：where not(salary&gt;100);</td> 
  </tr> 
 </tbody> 
</table>

SELECT * from book; -- 查询整个表的数据
    SELECT title,price,publish from book; -- 查询指定列
    SELECT DISTINCT title,price,publish from book; -- 查询指定列，同时去掉重复的结果
    -- as 关键字可以省略; 别名有空格时，必须使用引号包裹;
    SELECT t.title as 标题,price 价格,publish '发布 时间' from book as t; 
    SELECT * from book WHERE price > 50; -- 价格大于 50 的书籍
    SELECT * from book WHERE price BETWEEN 40 AND 80; -- 价格在 [40,80] 之间的书籍
    SELECT * from book WHERE title in('西游记','三国演义');
    SELECT * from book WHERE title not in('西游记','三国演义');
    SELECT * from book WHERE info like '%金庸%' and price < 100;
    SELECT * from book WHERE type_id is NULL; -- 书籍类型为 null 的数据
    SELECT * from book WHERE info is NOT NULL; -- 描述不为 null 的数据
    SELECT * from book ORDER BY price DESC;-- 按价格倒序
    SELECT * from book ORDER BY publish desc, price ASC LIMIT 5;-- 按发布时间先倒序，再按价格升序，取 5 条数据
    -- LIMIT 用于分页，对前面的结果只取其中一部分
    SELECT * from book WHERE price > 30 ORDER BY publish desc LIMIT 2,5;
    
    -- 查询每个分类下书籍的个数，以及最贵的价格
    SELECT type_id,max(price),count(1) from book t GROUP BY t.type_id;
    -- 查询每个分类下面平均售价超过 50 的分类 ID 以及评价售价信息
    SELECT type_id, avg(price) avg_price FROM book WHERE type_id IS NOT NULL GROUP BY type_id HAVING avg_price > 50;

# 数据库 DQL 复合查询 #

** 数据准备**：[sql/mysql/新建书籍与分类表.sql · 汪少棠/material - Gitee.com][sql_mysql_.sql _ _material - Gitee.com]

<table> 
 <tbody> 
  <tr> 
   <td colspan="2" style="width:102px;"> <p>1、有时在实际应用中，为了<span style="color:#fe2c24;">合并多个 select 语句的结果，可以使用集合操作符号 union , union all.</span></p> <p><span style="color:#fe2c24;">2、被合并的两个结果集的列个数与列类型必须一 一对应，列名称可以不对应，此时以将第一个结果集的列名为准.</span></p> </td> 
  </tr> 
  <tr> 
   <td style="width:102px;"><strong>union</strong></td> 
   <td style="width:586px;">该操作符用于取得两个结果集的并集，并对结果自动去重.</td> 
  </tr> 
  <tr> 
   <td style="width:102px;"><strong>union all</strong></td> 
   <td style="width:586px;">该操作赋与 union 相似，但是不会对结果自动去重，而且不会排序.</td> 
  </tr> 
  <tr> 
   <td style="width:102px;"><strong>内连接</strong></td> 
   <td style="width:586px;"> <p>实际上就是利用 where 子句对两张表形成的笛卡尔积进行筛选，这也是在开发过程中用的最多的连接查询。</p> <p><span style="color:#fe2c24;">内连接的语法：select 字段 表1 inner join 表2 on 连接条件 where ....</span></p> </td> 
  </tr> 
  <tr> 
   <td style="width:102px;"><strong>左外连接</strong></td> 
   <td style="width:586px;"> <p>如果左侧表中的数据需要完整显示，就是左外连接；没有匹配的记录字段的值为 null.</p> </td> 
  </tr> 
  <tr> 
   <td style="width:102px;"><strong>右外连接</strong></td> 
   <td style="width:586px;">如果右侧表中的数据需要完整显示，就是右外连接；没有匹配的记录字段的值为 null.</td> 
  </tr> 
 </tbody> 
</table>

-- 求并集，被合并的两个结果集的列个数与列类型必须一 一对应，列名称可以不对应，此时以将第一个结果集的列名为准.
    SELECT id,title,price,publish,info,type_id FROM book WHERE price <= 40 
    UNION 
    SELECT id as id2,title,price as price2,publish,info,type_id FROM book WHERE price >= 80;
    SELECT * FROM book WHERE price > 60 UNION ALL SELECT * FROM book WHERE type_id = 1;
    
    -- 内连接：INNER JOIN 关键字可以省略
    SELECT t1.*,t2.name FROM book t1 INNER JOIN book_type t2 ON t1.type_id = t2.id WHERE t1.price > 50;
    -- 等价于
    SELECT t1.*,t2.name FROM book t1, book_type t2 WHERE t1.type_id = t2.id AND t1.price > 50;
    
    -- 左外连接查询所有书籍及其分类，没有指定分类的书籍的记录的 type_id、name 会为 NULL
    SELECT t1.*,t2.name FROM book t1 LEFT JOIN book_type t2 ON t1.type_id = t2.id;
    -- 右外连接查询所有书籍分类，以及分类下的书籍
    SELECT t2.name,t1.* FROM book t1 RIGHT JOIN book_type t2 ON t1.type_id = t2.id;
    -- 多张表的左外连接格式：select 字段 表1 left join 表2 on 连接条件1 left join 表3 on 连接条件2 where 筛选条件

# MySQL 常用命令 #

<table> 
 <tbody> 
  <tr> 
   <td><strong>命令</strong></td> 
   <td><strong>描述</strong></td> 
  </tr> 
  <tr> 
   <td> <p>SELECT DATABASE();</p> </td> 
   <td>-- 查看当前数据库名称</td> 
  </tr> 
  <tr> 
   <td>SHOW CREATE DATABASE <span style="color:#f33b45;">wangmx</span></td> 
   <td>-- 查看数据库创建信息（<span style="color:#f33b45;">wangmx是数据库名称</span>）</td> 
  </tr> 
  <tr> 
   <td>SELECT now(), user(), version();</td> 
   <td>-- 显示当前时间、用户名、数据库版本</td> 
  </tr> 
  <tr> 
   <td>&nbsp;SHOW TABLES FROM wangmx;</td> 
   <td>-- 查看数据库下的所有表（<span style="color:#f33b45;">wangmx是数据库名称</span>）</td> 
  </tr> 
  <tr> 
   <td>CREATE TABLE person2 LIKE person ;</td> 
   <td><span style="color:#f33b45;">-- 复制表结构,不含数据，包括主外键，索引等</span></td> 
  </tr> 
  <tr> 
   <td> <p>CREATE TABLE person3 AS SELECT * FROM person;</p> </td> 
   <td>-- 复制表结构和数据，不包括主外键，索引等</td> 
  </tr> 
  <tr> 
   <td>DROP TABLE IF EXISTS person2;</td> 
   <td>-- 删除表</td> 
  </tr> 
  <tr> 
   <td>TRUNCATE TABLE person2;</td> 
   <td>-- 清空表数据</td> 
  </tr> 
  <tr> 
   <td>desc <span style="color:#f33b45;">person</span>;</td> 
   <td>-- 查看表结构，如字段名称与类型等<span style="color:#f33b45;">（person 是表名）</span></td> 
  </tr> 
 </tbody> 
</table>

# **where、from、exists** 子查询 #

<table> 
 <tbody> 
  <tr> 
   <td><strong>where 型子查询</strong></td> 
   <td>把子查询的结果作为外层查询的比较条件。</td> 
  </tr> 
  <tr> 
   <td><strong>from 型子查询</strong></td> 
   <td>把子查询的结果当成临时表，供外层 sq1再 次查询</td> 
  </tr> 
  <tr> 
   <td><strong>exists 型子查询</strong></td> 
   <td>把外层的查询结果代入到子查询看否成立。子查询有值时返回 true，否则返回 false。</td> 
  </tr> 
 </tbody> 
</table>

-- where 子查询：查询每个分类下价格最贵的书籍
    SELECT * from book t where (t.type_id,t.price) in (SELECT type_id,MAX(price) from book GROUP BY type_id);
    -- from  子查询：查询每个分类下价格最贵的书籍
    SELECT t1.* from book t1,(SELECT type_id,MAX(price) as price from book GROUP BY type_id) t2 where t1.type_id=t2.type_id and t1.price = t2.price;
    -- EXISTS 子查询：查询每个分类下价格最贵的书籍
    SELECT t1.* from book t1 where 
    EXISTS (SELECT t2.type_id, MAX(price) price from book t2 GROUP BY t2.type_id HAVING t1.type_id=t2.type_id and t1.price = price);

[sql_Mysql _.sql _ _my-document - Gitee.com]: https://gitee.com/wangmx1993/my-document/blob/master/sql/Mysql%20%E6%96%B0%E5%BB%BA%E9%83%A8%E9%97%A8%E4%B8%8E%E5%91%98%E5%B7%A5%E8%A1%A8.sql
[sql_mysql_ person _.sql _ _material - Gitee.com]: https://gitee.com/wangmx1993/material/blob/master/sql/mysql/%E6%96%B0%E5%BB%BA%20person%20%E7%94%A8%E6%88%B7%E8%A1%A8.sql
[20200915203021563.gif]: /images/20230601/11bce49cb4fa45019a41cb5c8ba2c619.png
[sql_mysql_.sql _ _material - Gitee.com]: https://gitee.com/wangmx1993/material/blob/master/sql/mysql/%E6%96%B0%E5%BB%BA%E4%B9%A6%E7%B1%8D%E4%B8%8E%E5%88%86%E7%B1%BB%E8%A1%A8.sql