『 MySQL篇 』：MySQL表的CURD操作

MySQL 系列专栏持续更新中 … MySQL专栏
​

目录

• • 目录
  • • • 一、SQL语句
      • • – SQL通用语法
        • – 注释
        • – SQL语句分类
      • 二、 基础表操作
      • • – 创建表
        • – 查看库中的表
        • – 查看表结构
        • – 删除表
        • – 重命名表
      • 三、MySQL 中的增删查改操作
      • • – 增加（insert语句）
        • – 查询(select语句)
        • – 修改(update)
        • – 删除(delete)

一、SQL语句

操作关系型数据库的编程语言，定义了一套操作关系型数据库的统一标准，简称SQL。

– SQL通用语法

1 . SQL语句可以单行或多行书写，以分号结尾。

2 . SQL语句可以使用空格/缩进来增强语句的可读性。

​ 3 . MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。

– 注释

• 单行注释： – 注释内容 或者使用 # 注释内容 。
• 多行注释：/* 注释内容 */

– SQL语句分类

二、 基础表操作

– 创建表

• 同一个数据库中，不能有两个表的名字相同，表名和列名不能和SQL的关键词重复。

• 语法：

create table 表名(定义列1, 定义列2, .......);列 -> 变量名 数据类型

• 举例：

mysql> create table if not exists book(    ->   book_name varchar(32) comment '图书名称',    ->   book_author varchar(32)comment  '图书作者' ,    ->   book_price decimal(12,2) comment '图书价格',    ->   book_category varchar(12) comment '图书分类',    ->   publish_data timestamp    -> )character set utf8mb4;    Query OK, 0 rows affected (0.04 sec)

– 查看库中的表

• 语法：

  show tables;

• 举例：

  mysql> show tables;+--------------------+| Tables_in_mytestdb |+--------------------+| book               |+--------------------+1 row in set (0.00 sec)

– 查看表结构

• 语法：

  desc  表名；

• 举例：
  

MySQL数据库中的表结构主要包含以下几种信息： 字段名称，字段类型，是否允许为空，索引类型。默认值，扩充信息

– 删除表

• 语法：

drop table 表名

• 举例 :

mysql> desc test1;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| name  | varchar(10) | YES  |     | NULL    |       || age   | int(11)     | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+2 rows in set (0.02 sec)mysql> drop table test1;Query OK, 0 rows affected (0.04 sec)mysql> desc test1;ERROR 1146 (42S02): Table 'mytestdb.test1' doesn't exist

– 重命名表

• 语法：

  rename table old_name to new_name;

• 举例：

  mysql> rename table book to eBook;Query OK, 0 rows affected (0.05 sec)mysql> show tables;+--------------------+| Tables_in_mytestdb |+--------------------+| ebook              |+--------------------+1 row in set (0.00 sec)

三、MySQL 中的增删查改操作

CRUD 即增加(Create)、查询(Retrieve)、更新(Update)、删除(Delete)四个单词的首字母缩写

案例:

-- 创建一张图书表mysql> create table if not exists book(    ->   book_name varchar(32) comment '图书名称',    ->   book_author varchar(32)comment  '图书作者' ,    ->   book_price decimal(12,2) comment '图书价格',    ->   book_category varchar(12) comment '图书分类',    ->   publish_data timestamp    -> )character set utf8mb4;

– 增加（insert语句）

• 单行插入(全列)

  insert into 表名 values(对应列的参数列表); -- 一次插入一行

• 多行插入(全列)

  insert into 表名 values(对应列的实参列表), (对应列的参数列表), (对应列的参数列表);              -- 一次插入多行

• 指定列插入

  • values 后面( )中的内容, 个数和类型要和表名后面( )中指定的结构匹配.
  • 未被指定的列会以默认值进行填充.

  insert into 表名 (需要插入的列) values(对应列的参数列表); -- 一次插入一行insert into 表名 (需要插入的列) values(对应列的参数列表), (), ().... -- 一次插入多行

• 案例

  # 单行输入mysql> insert into book values('计算机网络','谢希仁',45,'计算机类','2020-12-25 12:51:00');Query OK, 1 row affected (0.01 sec)#多行输入mysql> insert into book values('计算机组成原理','王峰',45,'硬件类','2020-12-12 12:00:00'),    -> ('微机原理','李华',97,'硬件类','2000-12-19 20:00:00');    Query OK, 2 rows affected (0.04 sec)Records: 2  Duplicates: 0  Warnings: 0#指定列插入mysql>insert into book(book_name,book_author,publish_data) values ('软件工程','张三','2020-05-06 12:00:00');Query OK, 1 row affected (0.02 sec)

• 插入数据后的表如图所示:

  

在MySQL当中 , 多条记录逐次插入的效率是要低于一次把多条纪录一起插入的 ,原因如下:

• 网络请求和响应时间开销 , 每次插入都会有一定的时间开销.
• 数据库服务器是把数据保存在硬盘上的 , IO操作时,操作的次数带来的影响大于数据量.
• 每一次sql操作,内部开启的事务也会占据一定的开销.

– 查询(select语句)

• 全列查询

语法

select * from 表名--  * 表示通配符, 可以匹配表中的所有列.

企业级别的数据库中慎用, 容易把I/O或者网络带宽吃满，如果有外边的用户客户端要通过宽带访问服务器时，服务器就无法做出正确的响应．

示例

select * from book;

• 指定列查询

select 列名...  from  表名

• 示例

)mysql> select book_name from book;+----------------+| book_name      |+----------------+| 计算机网络     || 计算机组成原理 || 微机原理       || 软件工程       |+----------------+4 rows in set (0.01 sec)mysql> select book_author,book_price from book;+-------------+------------+| book_author | book_price |+-------------+------------+| 谢希仁      |      45.00 || 王峰        |      45.00 || 李华        |      97.00 || 张三        |       NULL |+-------------+------------+4 rows in set (0.00 sec)                  

• 查询你字段为表达式

select 字段或表达式, 字段或表达式... from 表名;

• 示例

-- 查询图书涨价10元后所有图书的名称作者和价格mysql> select book_name ,book_author,book_price + 10 from book;+----------------+-------------+-----------------+| book_name      | book_author | book_price + 10 |+----------------+-------------+-----------------+| 计算机网络     | 谢希仁      |           55.00 || 计算机组成原理 | 王峰        |           55.00 || 微机原理       | 李华        |          107.00 || 软件工程       | 张三        |            NULL |+----------------+-------------+-----------------+4 rows in set (0.00 sec)

• 将表达式或者字段指定别名查询

mysql中支持给所查询的表达式取一个别名 , 使用 as 可以使查询结果更加直观 , 代码的可读性也会更强.

select 列名或表达式 as 别名, ... from 表名;

• 示例

-- 将涨价20元后的图书价格取为别名newpricemysql> select book_name,book_author,book_price + 20 as newprice from book;+----------------+-------------+----------+| book_name      | book_author | newprice |+----------------+-------------+----------+| 计算机网络     | 谢希仁      |    65.00 || 计算机组成原理 | 王峰        |    65.00 || 微机原理       | 李华        |   117.00 || 软件工程       | 张三        |     NULL |+----------------+-------------+----------+4 rows in set (0.00 sec)

• 去重查询

select distinct 列名 from 表名

• 示例

--book 表中插入一条重复的book_name数据mysql> insert into book values('计算机网络','张华',89,'计算机类','2020-11-23 11:00:00');Query OK, 1 row affected (0.00 sec)mysql> select book_name from book;+----------------+| book_name      |+----------------+| 计算机网络     || 计算机组成原理 || 微机原理       || 软件工程       || 计算机网络     |+----------------+5 rows in set (0.00 sec)mysql> select distinct book_name from book;+----------------+| book_name      |+----------------+| 计算机网络     || 计算机组成原理 || 微机原理       || 软件工程       |+----------------+4 rows in set (0.00 sec)

查询结果当中,没有了重复的book _ name 元素,达到了去重效果.

• 排序查询

select 列名 from 表名 order by 列名 asc(升序)/desc(降序); #  想要排序的列

• 示例

# 按照书的价格升序进行排列mysql> select book_name,book_price from book order by book_price asc;+----------------+------------+| book_name      | book_price |+----------------+------------+| 软件工程       |       NULL || 计算机网络     |      45.00 || 计算机组成原理 |      45.00 || 计算机网络     |      89.00 || 微机原理       |      97.00 |+----------------+------------+5 rows in set (0.00 sec)#按照书的价格降序进行排列mysql> select book_name,book_price from book order by book_price desc;+----------------+------------+| book_name      | book_price |+----------------+------------+| 微机原理       |      97.00 || 计算机网络     |      89.00 || 计算机网络     |      45.00 || 计算机组成原理 |      45.00 || 软件工程       |       NULL |+----------------+------------+5 rows in set (0.00 sec)

• 使用排序查询时 , 升序查询 asc 可以省略, 即默认为升序排列, null值一定为其中最小的.
• 可以对多个字段进行排序,优先级按照书写的顺序进行.

• 示例

# 查询按照价格升序 ,年份降序select name,price,age from book order by price asc,age desc;#查询按照总成绩进行降序select name,english+math+chinese as total from grade order by total desc;

• 条件查询

当我们使用查询时, 通常具有各种各样的前提条件 , 此时就需要使用条件查询来完成.

select 列名.. from 表名..where + 条件

比较运算符

逻辑运算符

注：

• WHERE条件可以使用表达式，但不能使用别名。

• AND的优先级高于OR，在同时使用时，需要使用小括号()包裹优先执行的部分

• 案例

-- 查询图书价格低于50的图书作者和图书名称mysql> select book_name,book_author from book where book_price < 50;+----------------+-------------+| book_name      | book_author |+----------------+-------------+| 计算机网络     | 谢希仁      || 计算机组成原理 | 王峰        |+----------------+-------------+2 rows in set (0.05 sec)  -- 查询图书价格等于97的图书作者mysql> select book_name ,book_author from book where book_price = 97;+-----------+-------------+| book_name | book_author |+-----------+-------------+| 微机原理  | 李华        |+-----------+-------------+1 row in set (0.00 sec)-- 查询图书价格在50 - 100 之间的图书名称mysql> select book_name from book where book_price between 50 and 100;+------------+| book_name  |+------------+| 微机原理   || 计算机网络 |+------------+2 rows in set (0.02 sec)\-- 查询图书价格在此范围内的图书名称mysql> select book_name from book where book_price in (12,45);+----------------+| book_name      |+----------------+| 计算机网络     || 计算机组成原理 |+----------------+2 rows in set (0.00 sec)

模糊查询

• % 匹配任意多个（包括 0 个）字符
• _ 匹配严格的一个字符

#查询姓张的作者的书本价格书名.mysql> select book_price,book_name,book_author from book where book_author like '张%';+------------+------------+-------------+| book_price | book_name  | book_author |+------------+------------+-------------+|       NULL | 软件工程   | 张三        ||      89.00 | 计算机网络 | 张华        |+------------+------------+-------------+2 rows in set (0.00 sec)# 查询前缀为'计算机'后缀为七个字的书籍名称mysql> select book_name from book where book_name like '计算机____';+----------------+| book_name      |+----------------+| 计算机组成原理 |+----------------+#查询前缀为'计算机'的书籍名称并去重mysql> select distinct book_name from book where book_name like '计算机%';+----------------+| book_name      |+----------------+| 计算机网络     || 计算机组成原理 |+----------------+2 rows in set (0.00 sec)

• 分页查询

分页查询即将查询出的结果 , 按页进行呈现,并不是一次性展现出来,这种模式就是分页查询, mysql当中使用limit来实现分页查询.

• limit 子句当中接受一个或者两个参数 , 这两个参数的值为0 或者正整数

两个参数的limit子句的用法

select 元素1,元素2  from 表名  limit offset,count;#offset参数指定要返回的第一行的偏移量。第一行的偏移量为0，而不是1。#count指定要返回的最大行数。

示例:

mysql> select book_author from book limit 2, 3;+-------------+| book_author |+-------------+| 李华        || 张三        || 张华        |+-------------+3 rows in set (0.02 sec)#表示获取列表当中偏移量为2(表示从第3行开始), 最大行数为3的作者名称

带有一个参数的limit子句的用法

select 列名1.列名2 from 表名 limit count;#  表示从结果集的开头返回的最大行数为count;#  获取前count行的记录

等同于

select 列名1 ,列名2 from 表名 limit 0 , count;# 第一行的偏移量为0

示例

mysql> select book_price from book limit 5;+------------+| book_price |+------------+|      45.00 ||      45.00 ||      97.00 ||       NULL ||      89.00 |+------------+5 rows in set (0.00 sec)# 获取表中前五行的图书价格 , 最大行数为5

• limit 结合 order by 语句 和其他条件可以获取n个最大或者最小值

  select book_name,book_price from book order by book_price desc limit 3; #获取价格前三高的图书名称和图书价格mysql> select book_price,book_name from book order by book_price desc limit 3;+------------+------------+| book_price | book_name  |+------------+------------+|      97.00 | 微机原理   ||      89.00 | 计算机网络 ||      45.00 | 计算机网络 |+------------+------------+3 rows in set (0.01 sec)

• 使用limit 获取第n高个最大值

偏移量从0开始，所以要指定从n – 1 开始，然后取一行记录

#示例:获取价格第二高的图书名称 mysql> select book_name from book order by book_price desc limit 1,1;+------------+| book_name  |+------------+| 计算机网络 |+------------+1 row in set (0.00 sec)

– 修改(update)

MySQL当中使用update关键字来对数据进行修改 , 既可以修改单列又可以修改多列.

update 表名 set 列名1 = 值 , 列名2 = 值 ... where 限制条件下修改

• SET子句指定要修改的列和新值。要更新多个列，请使用以逗号分隔的列表。以字面值，表达式或子查询的形式在每列的赋值中来提供要设置的值。
• 第三，使用WHERE子句中的条件指定要更新的行。WHERE子句是可选的。 如果省略WHERE子句，则UPDATE语句将更新表中的所有行。

示例:

#将书名为'软件工程'的图书价格修改为66元mysql> update book set book_price = 66 where book_name = '软件工程';Query OK, 1 row affected (0.05 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select book_price from book where book_name = '软件工程';+------------| book_price |+------------+|      66.00 |+------------+1 row in set (0.00 sec)#将所有的图书价格修改为原来的二倍mysql> update book set book_price = 2 * book_price;Query OK, 5 rows affected (0.02 sec)Rows matched: 5  Changed: 5  Warnings: 0#更新成功mysql> select book_price from book;+------------+| book_price |+------------+|      90.00 ||      90.00 ||     194.00 ||     132.00 ||     178.00 |+------------+5 rows in set (0.00 sec)

– 删除(delete)

要从表中删除数据,需要使用delete 语句, delete 语句的 用法如下

delete from 表名 where + 条件

首先指定需要删除数据的表,其次使用条件指定where子句中删除的行记录, 如果行匹配条件,这些行记录将会删除.

WHERE子句是可选的。如果省略WHERE子句，DELETE语句将删除表中的所有行 , 请注意，一旦删除数据，它就会永远消失。因此，在执行DELETE语句之前，应该先备份数据库，以防万一要找回删除过的数据。

示例

#删除图书表中图书单价大于150的图书记录mysql> delete from book where book_price > 150;Query OK, 2 rows affected (0.01 sec)mysql> select book_price from book;+------------+| book_price |+------------+|      90.00 ||      90.00 ||     132.00 |+------------+3 rows in set (0.00 sec)

MySQL中delete 语句也可以结合limit语句 和 order by 语句来控制删除的数量和条件