An article explaining the CURD operation of MySQL tables in detail

This article will give you a detailed understanding of the CURD operation of MySQL tables. I hope it will be helpful to you!

1. SQL statement

is a programming language for operating relational databases and defines a set of operations A unified standard for relational databases, referred to as SQL.

-SQL general syntax

1. SQL statements can be written in a single line or multiple lines and end with a semicolon.

2. SQL statements can use spaces/indentation to enhance the readability of the statement.

3. The SQL statements of the MySQL database are not case-sensitive, and it is recommended to use uppercase letters for keywords.

- Comment

• Single-line comment: – Comment content or use # comment content.
• Multi-line comments: /* Comment content*/

-SQL statement classification

Category	Description
DDL (deifnition)	Data definition language (used to define database objects, databases, tables, Field)
DML(manipulation)	Data manipulation language (add, delete and modify data in the database table)
DQL(query)	Data query language, used to query records in tables in the database
DCL(control)	Data control language, used To create a database user and control database access permissions

2. Basic table operations

-Create table

• Same database , two tables cannot have the same name, and table names and column names cannot repeat SQL keywords.

• Grammar:

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

• Example:

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)

- View Tables in the library

• Syntax:

  show tables;

• Example:

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

- View table structure

• Syntax:

  desc  表名；

• Example:
  

The table structure in the MySQL database mainly contains the following information: field name, field type, whether empty is allowed, and index type. Default value, extended information

-Delete table

• Syntax:

drop table 表名

• Example:

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

• Syntax:

  rename table old_name to new_name;

• Example:

  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)

3. Add, delete, check and modify operations in MySQL

##CRUD means increase (Create) , Query (Retrieve), Update (Update), Delete (Delete) acronym of the four words

Case:

-- 创建一张图书表
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;

- Add (insert statement)

• Single row insertion (all columns)

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

• Multiple row insertion (all columns)

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

• Insert the specified column into the content in ( ) after

  values. The number and type must match the structure specified in ( ) after the table name.
  • Unspecified columns will be filled with default values.

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

• Case

  # 单行输入
  
  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)

• After inserting data The table is as shown in the figure:

In MySQL, the efficiency of inserting multiple records one after another is lower than inserting multiple records at once. Records are inserted together for the following reasons:

Network request and response time overhead, each insertion will have a certain time overhead.
• The database server saves the data on the hard disk , during IO operations, the impact of the number of operations is greater than the amount of data.
• For each SQL operation, internally opened transactions will also occupy a certain amount of overhead.

- Query (select statement)

Full column query

Syntax

select * from 表名

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

Use with caution in enterprise-level databases. It is easy to fill up the I/O or network bandwidth. If an external user client wants to access the server through broadband, the server will not be able to respond correctly.

Example

select * from book;

##Specify column query

• select 列名...  from  表名

Example

• 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)

Query your field as an expression

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

Example

• -- 查询图书涨价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)

will express Expression or field specified alias query

mysql supports giving an alias to the queried expression. Using as can make the query results more intuitive and the code more readable.

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

Example

• -- 将涨价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)

Duplication query

• select distinct 列名 from 表名

Example

• --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)

There are no duplicate book_name elements in the query results, achieving the deduplication effect.

Sort query

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

Example

• # 按照书的价格升序进行排列
  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)

When using sorted query, the ascending query asc can be omitted, that is, the default is ascending order, and the null value must be the smallest among them.

• Multiple fields can be sorted, and the priority is in the order of writing.

Example

• # 查询按照价格升序 ,年份降序
  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;

Conditional query

When we use queries, we usually have various preconditions, and then we need to use conditional queries to complete.

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

Comparison Operator

运算符	说明
>, >=,	大于，大于等于，小于，小于等于
=	等于，null 不安全，例如 null = null 的结果是 null(false)
	等于，null 安全，例如 null null 的结果是 true(1)
!=,	不等于
between a0 and a1	范围匹配，[a0, a1]，如果 a0
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，在同时使用时，需要使用小括号()包裹优先执行的部分

• 案例

-- 查询图书价格低于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: 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语句之前，应该先备份数据库，以防万一要找回删除过的数据。

示例

#删除图书表中图书单价大于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 语句来控制删除的数量和条件

【相关推荐：mysql视频教程】

The above is the detailed content of An article explaining the CURD operation of MySQL tables in detail. For more information, please follow other related articles on the PHP Chinese website!