MySQL表的CURD操作(数据的增删改查)

吾爱主题阅读：170 2023-04-07 17:32:00 评论：0

一、SQL语句

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

- SQL通用语法

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

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

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

- 注释

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

- SQL语句分类

分类	说明
DDL（deifnition）	数据定义语言（用来定义数据库对象，数据库，表，字段）
DML(manipulation)	数据操纵语言（对数据库表中的是数据进行增删改）
DQL(query)	数据查询语言，用来查询数据库中表的记录
DCL(control)	数据控制语言，用来创建数据库用户，控制数据库的访问权限

二、基础表操作

- 创建表

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

语法：

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

举例：

1 2 3 4 5 6 7 8 9 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)

- 查看库中的表

语法：

1	`show tables;`

举例：

1 2 3 4 5 6 7 mysql> show tables; + --------------------+ | Tables_in_mytestdb | + --------------------+ | book | + --------------------+ 1 row in set (0.00 sec)

- 查看表结构

语法：

desc 表名；

举例：

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

- 删除表

语法：

drop table 表名

举例 :

- 重命名表

语法：

rename table old_name to new_name;

举例：

1 2 3 4 5 6 7 8 9 10 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)四个单词的首字母缩写

案例:

1 2 3 4 5 6 7 8 -- 创建一张图书表 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语句）

单行插入(全列)

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

多行插入(全列)

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

指定列插入

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

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

案例

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 # 单行输入 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语句)

全列查询

语法

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

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

示例

1	`select` `*` `from` `book;`

指定列查询

select 列名... from 表名

示例

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 )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 表名;

示例

1 2 3 4 5 6 7 8 9 10 11 -- 查询图书涨价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 表名;

示例

1 2 3 4 5 6 7 8 9 10 11 -- 将涨价20元后的图书价格取为别名newprice mysql> 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 _ name 元素,达到了去重效果.

排序查询

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

示例

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 # 按照书的价格升序进行排列 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值一定为其中最小的.

可以对多个字段进行排序,优先级按照书写的顺序进行.

示例

1 2 3 4 # 查询按照价格升序 ,年份降序 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 + 条件

比较运算符

运算符	说明
>, >=, <, <=	大于，大于等于，小于，小于等于
=	等于，null 不安全，例如 null = null 的结果是 null(false)
<=>	等于，null 安全，例如 null <=> null 的结果是 true(1)
!=, <>	不等于
between a0 and a1	范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回 true(1)
in (option, …)	如果是 option 中的任意一个，返回 true(1)
is null	是 null
is not null	不是 null
like	模糊匹配; % 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符

逻辑运算符

运算符	说明
and	多个条件必须为 true , 结果才为true
or	任意一个条件为true 结果才为true
not	条件为true , 结果为false

注：

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

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

案例

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 -- 查询图书价格低于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当中使用limit来实现分页查询.

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

两个参数的limit子句的用法

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

示例:

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

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

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

等同于

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

示例

1 2 3 4 5 6 7 8 9 10 11 12 13 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个最大或者最小值

1 2 3 4 5 6 7 8 9 10 11 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 开始，然后取一行记录

1 2 3 4 5 6 7 8 #示例:获取价格第二高的图书名称 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语句将更新表中的所有行。

示例:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 #将书名为 '软件工程' 的图书价格修改为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: 0 mysql> 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语句之前，应该先备份数据库，以防万一要找回删除过的数据。

示例

1 2 3 4 5 6 7 8 9 10 11 12 13 #删除图书表中图书单价大于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)