mysql数据库的DML操作（增删改查）

碳烤小肥杨..

已于 2023-08-31 17:51:44 修改

阅读量69

点赞数 7

文章标签： mysql 运维 linux

于 2023-08-31 17:49:41 首次发布

本文链接：https://blog.csdn.net/m0_64505752/article/details/132608445

版权

mysql数据库的DML操作（增删改查）

[root@ftx ~]# mysql -uroot -p12345678   //首先登录到数据库中
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.7.43 MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;     //查看数据库信息
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use school;    //进入到我们之前创建的school这个数据库当中
Database changed
mysql> create table student(id int not null primary key,name varchar(20),age tinyint);    //在数据库中创建一张表，其中有我们设置的id（设有主键，不能为空），name（设为最多20个字符串），age（设为最大127岁）
Query OK, 0 rows affected (0.00 sec)

mysql> desc school.student;   //查看创建的表的结构
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> create table student10(id int not null primary key auto_increment,name varchar(20),age tinyint);    //这张表中新加的auto_incerment表示自动增长
Query OK, 0 rows affected (0.00 sec)

mysql> desc school.student10;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| age   | tinyint(4)  | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
| student10        |
+------------------+
2 rows in set (0.00 sec)

获取帮助

//获取命令使用帮助
//语法：HELP keyword;
mysql> HELP CREATE TABLE;       //获取创建表的帮助
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression
 ......
 ......

查看进程列表

在这里插入图片描述

读锁（共享锁S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改

写锁（互斥锁X）：允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。 InnoDB所有的DML操作默认加写锁。 select可以通过for update加写锁，并且会锁住所有索引，不仅仅是索引覆盖的索引。

在这里插入图片描述

1.DML操作

DML操作包括增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT)，均属针对表的操作。

1.1 INSERT语句

//DML操作之增操作insert
//语法：INSERT [INTO] table_name [(column_name,...)] {VALUES | VALUE} (value1,...),(...),...

mysql> insert student(id,name,age) value(1,'test',15);    //一次插入一条记录
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | test |   15 |
+----+------+------+
1 row in set (0.00 sec)

mysql> insert student(id,name,age) values(2,'jerry',19),(3,'zhangsan',23),(4,'lisi',28),(5,'wangwu',20);   //一次插入多条记录
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | test     |   15 |
|  2 | jerry    |   19 |
|  3 | zhangsan |   23 |
|  4 | lisi     |   28 |
|  5 | wangwu   |   20 |
+----+----------+------+
5 rows in set (0.00 sec)

1.2 SELECT语句

字段column表示法

表示符	代表什么？
*	所有字段
as	字段别名，如col1 AS alias1 当表名很长时用别名代替

条件判断语句WHERE

操作类型	常用操作符
操作符	>，<，>=，<=，=，!= BETWEEN column# AND column# LIKE：模糊匹配 RLIKE：基于正则表达式进行模式匹配 IS NOT NULL：非空 IS NULL：空
条件逻辑操作	AND OR NOT

ORDER BY：排序，默认为升序（ASC）

ORDER BY语句	意义
ORDER BY ‘column_name’	根据column_name进行升序排序
ORDER BY ‘column_name’ DESC	根据column_name进行降序排序
ORDER BY ’column_name’ LIMIT 2	根据column_name进行升序排序并只取前2个结果
ORDER BY ‘column_name’ LIMIT 1,2	根据column_name进行升序排序并且略过第1个结果取后面的2个结果

//DML操作之查操作select
//语法：SELECT column1,column2,... FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];
mysql> use school;
Database changed
mysql> select * from student;   //查看所有的列
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | test     |   15 |
|  2 | jerry    |   19 |
|  3 | zhangsan |   23 |
|  4 | lisi     |   28 |
|  5 | wangwu   |   20 |
+----+----------+------+
5 rows in set (0.00 sec)

mysql> select name from student;   //查看name这一列数据
+----------+
| name     |
+----------+
| test     |
| jerry    |
| zhangsan |
| lisi     |
| wangwu   |
+----------+
5 rows in set (0.00 sec)

mysql> select * from student order by age;   //降序排列
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | test     |   15 |
|  2 | jerry    |   19 |
|  5 | wangwu   |   20 |
|  3 | zhangsan |   23 |
|  4 | lisi     |   28 |
+----+----------+------+
5 rows in set (0.00 sec)

mysql> select * from student order by age desc;   //升序排列
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  4 | lisi     |   28 |
|  3 | zhangsan |   23 |
|  5 | wangwu   |   20 |
|  2 | jerry    |   19 |
|  1 | test     |   15 |
+----+----------+------+
5 rows in set (0.00 sec)

mysql> select * from student order by age limit 3;  //升序排列并只取前3个结果
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | test   |   15 |
|  2 | jerry  |   19 |
|  5 | wangwu |   20 |
+----+--------+------+
3 rows in set (0.00 sec)

mysql> select * from student order by age limit 1,3;  //进行升序排序并且略过第1个结果取后面的3个结果
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  2 | jerry    |   19 |
|  5 | wangwu   |   20 |
|  3 | zhangsan |   23 |
+----+----------+------+
3 rows in set (0.00 sec)

mysql> select * from student where age >= 20;  //查看表中数据age大于等于20的行
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  3 | zhangsan |   23 |
|  4 | lisi     |   28 |
|  5 | wangwu   |   20 |
+----+----------+------+
3 rows in set (0.00 sec)

mysql> select * from student where age >= 20 and name = 'lisi';  //查看表中数据age大于等于20的行并且name=lisi的行
+----+------+------+
| id | name | age  |
+----+------+------+
|  4 | lisi |   28 |
+----+------+------+
1 row in set (0.00 sec)

mysql> select * from student where age between 18 and 26;   //查看表中数据age大于18小于26的行
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  2 | jerry    |   19 |
|  3 | zhangsan |   23 |
|  5 | wangwu   |   20 |
+----+----------+------+
3 rows in set (0.00 sec)

mysql> select * from student where age is not null;  //查看表中age不为空的行
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | test     |   15 |
|  2 | jerry    |   19 |
|  3 | zhangsan |   23 |
|  4 | lisi     |   28 |
|  5 | wangwu   |   20 |
+----+----------+------+
5 rows in set (0.00 sec)

1.3 update语句

//DML操作之改操作update
//语法：UPDATE table_name SET column1 = new_value1[,column2 = new_value2,...] [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];

mysql> select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | test     |   15 |
|  2 | jerry    |   19 |
|  3 | zhangsan |   23 |
|  4 | lisi     |   28 |
|  5 | wangwu   |   20 |
+----+----------+------+
5 rows in set (0.00 sec)

mysql> update student set age = 25 where name = 'lisi';  //将lisi的age修改为25
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student where name = 'lisi';
+----+------+------+
| id | name | age  |
+----+------+------+
|  4 | lisi |   25 |
+----+------+------+
1 row in set (0.00 sec)

2.4 delete语句

//DML操作之删操作delete
//语法：DELETE FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];

mysql> select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | test     |   15 |
|  2 | jerry    |   19 |
|  3 | zhangsan |   23 |
|  4 | lisi     |   25 |
|  5 | wangwu   |   20 |
+----+----------+------+
5 rows in set (0.00 sec)

mysql> delete from student where id = 5;  //删除id为5的一行记录
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;   //删除整张表的内容
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | test     |   15 |
|  2 | jerry    |   19 |
|  3 | zhangsan |   23 |
|  4 | lisi     |   25 |
+----+----------+------+
4 rows in set (0.00 sec)

mysql> delete from student;    //再次查看已经没有表
Query OK, 4 rows affected (0.00 sec)

mysql> select * from student;
Empty set (0.00 sec)

2.5 truncate语句

truncate与delete的区别：

语句类型	特点
delete	DELETE删除表内容时仅删除内容，但会保留表结构 DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项可以通过回滚事务日志恢复数据非常占用空间
truncate	删除表中所有数据，且无法恢复表结构、约束和索引等保持不变，新添加的行计数值重置为初始值执行速度比DELETE快，且使用的系统和事务日志资源少通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据不能用于加入了索引视图的表

语句类型

特点

delete

DELETE删除表内容时仅删除内容，但会保留表结构 DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项可以通过回滚事务日志恢复数据非常占用空间

truncate

删除表中所有数据，且无法恢复表结构、约束和索引等保持不变，新添加的行计数值重置为初始值执行速度比DELETE快，且使用的系统和事务日志资源少通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据不能用于加入了索引视图的表

//语法：TRUNCATE table_name;

mysql> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | test |   16 |
+----+------+------+
1 row in set (0.01 sec)

mysql> truncate student;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
Empty set (0.00 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

----------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
±------±------------±-----±----±--------±------+
3 rows in set (0.00 sec)