增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT)，_查询修改删除添加英文-优快云博客

本文链接：https://blog.youkuaiyun.com/kx0822/article/details/124296105

这篇博客详细介绍了MySQL中的基本操作，包括DDL（数据定义语言）如表操作和用户管理，以及DML（数据操纵语言）如INSERT、SELECT、UPDATE和DELETE。此外，还讲解了如何使用SHOW命令查看信息，获取帮助，以及如何进行权限管理，如GRANT和REVOKE。特别提到了TRUNCATE语句用于清空表内容，ALTER语句用于修改表结构。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

DDL操作

数据库操作

//创建数据库
//语法：CREATE DATABASE [IF NOT EXISTS] 'DB_NAME';
//创建数据库kk
mysql> CREATE DATABASE IF NOT EXISTS kk;
Query OK, 1 row affected (0.00 sec)

//查看当前实例有哪些数据库
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| kk                 |
+--------------------+
5 rows in set (0.00 sec)

//删除数据库
//语法：DROP DATABASE [IF EXISTS] 'DB_NAME';
//删除数据库kk
mysql> DROP DATABASE IF EXISTS kk;
Query OK, 0 rows affected (0.00 sec)

表操作

//创建表
//语法：CREATE TABLE table_name (col1 datatype 修饰符,col2 datatype 修饰符) ENGINE='存储引擎类型';
//在数据库kk里创建表k
mysql> CREATE DATABASE wangqingge;      //创建数据库kk
Query OK, 1 row affected (0.00 sec)

mysql> use kk;      //进入kk数据库
Database changed
mysql> CREATE TABLE kk (id int NOT NULL,name VARCHAR(100) NOT NULL,age tinyint);      //创建k表
Query OK, 0 rows affected (0.09 sec)


//查看当前数据库有哪些表
mysql> SHOW TABLES;
+----------------------+
| Tables_in_kk         |
+----------------------+
| k                    |
+----------------------+
1 row in set (0.00 sec)

用户操作

mysql用户帐号由两部分组成，如'USERNAME'@'HOST'，表示此USERNAME只能从此HOST上远程登录

这里（'USERNAME'@'HOST'）的HOST用于限制此用户可通过哪些主机远程连接mysql程序，其值可为：

IP地址，如：172.16.12.129
通配符
- %：匹配任意长度的任意字符，常用于设置允许从任何主机登录
- _：匹配任意单个字符

查看命令SHOW

mysql> SHOW CHARACTER SET;      //查看支持的所有字符集
mysql> SHOW ENGINES;        //查看当前数据库支持的所有存储引擎
mysql> SHOW DATABASES;          //查看数据库信息
mysql> SHOW TABLES FROM kk;         //不进入某数据库而列出其包含的所有表

//查看表结构
//语法：DESC [db_name.]table_name;
mysql> DESC kk;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   |     | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
| age   | tinyint(4)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.03 sec)

//查看某表的创建命令
//语法：SHOW CREATE TABLE table_name;
mysql> SHOW CREATE TABLE kk.kk;

获取帮助

//获取命令使用帮助
//语法：HELP keyword;
mysql> HELP CREATE TABLE;       //获取创建表的帮助

delete语句

select * from kk;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |   20 |
|  2 | jerry     |   23 |
|  3 | wangqing  |   25 |
|  4 | sean      |   28 |
|  5 | zhangshan |   26 |
|  6 | zhangshan |   20 |
|  7 | lisi      |   30 |
+----+-----------+------+
7 rows in set (0.00 sec)

mysql> delete from kk where id = 7;       //删除某条记录
Query OK, 1 row affected (0.00 sec)

mysql> select * from kk;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |   20 |
|  2 | jerry     |   23 |
|  3 | wangqing  |   25 |
|  4 | sean      |   28 |
|  5 | zhangshan |   26 |
|  6 | zhangshan |   20 |
+----+-----------+------+
6 rows in set (0.00 sec)

mysql> delete from kk;        //删除整张表的内容
Query OK, 6 rows affected (0.00 sec)

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

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

truncate语句

清空整张表的内容

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


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

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

INSERT语句

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

mysql> use kk;
Database changed
mysql> INSERT INTO kk (id,name,age) VALUE (1,'tom',20);      
Query OK, 1 row affected (0.01 sec)


mysql> INSERT INTO kk (id,name,age) VALUES (2,'jerry',23),(3,'wangqing',25),(4,'sean',28),(5,'zhangshan',26),(6,'zhangshan',20),(7,'lisi',NULL);     
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from kk;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |   20 |
|  2 | jerry     |   23 |
|  3 | wangqing  |   25 |
|  4 | sean      |   28 |
|  5 | zhangshan |   26 |
|  6 | zhangshan |   20 |
|  7 | lisi      | NULL |
+----+-----------+------+
7 rows in set (0.00 sec)

SELECT语句

mysql> use kk;
Database changed
mysql> select * from kk;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |   20 |
|  2 | jerry     |   23 |
|  3 | wangqing  |   25 |
|  4 | sean      |   28 |
|  5 | zhangshan |   26 |
|  6 | zhangshan |   20 |
|  7 | lisi      | NULL |
+----+-----------+------+
7 rows in set (0.00 sec)


查找

update语句

mysql> select * from kk;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |   20 |
|  2 | jerry     |   23 |
|  3 | wangqing  |   25 |
|  4 | sean      |   28 |
|  5 | zhangshan |   26 |
|  6 | zhangshan |   20 |
|  7 | lisi      | NULL |
+----+-----------+------+
7 rows in set (0.00 sec)
mysql> update kk set age = 30 where name = 'lisi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from kk where name = 'lisi';
+----+------+------+
| id | name | age  |
+----+------+------+
|  7 | lisi |   30 |
+----+------+------+
1 row in set (0.00 se

查看授权

mysql> SHOW GRANTS;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)





//查看指定用户wangqing的授权信息
mysql> SHOW GRANTS FOR kk;
+-----------------------------------------------+
| Grants for kk@%                         |
+-----------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'kk'@'%' |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR 'kk'@'localhost';
+-------------------------------------------------------+
| Grants for kk@localhost                         |
+-------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'kk'@'localhost' |
+-------------------------------------------------------+
1 row in set (0.00 sec)

取消授权REVOKE

//语法：REVOKE priv_type,... ON db_name.table_name FROM 'username'@'host';

mysql> REVOKE ALL ON *.* FROM 'wangqing'@'172.16.12.129';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

注意：mysql服务进程启动时会读取mysql库中的所有授权表至内存中：

GRANT或REVOKE等执行权限操作会保存于表中，mysql的服务进程会自动重读授权表，并更新至内存中
对于不能够或不能及时重读授权表的命令，可手动让mysql的服务进程重读授权表

mysql> FLUSH PRIVILEGES;

alter 修改表结构

给表重命名

mysql> alter table kk rename kk1; 
Query OK, 0 rows affected (0.00 sec) 


设置age
mysql> alter table kk modify age tinyint not null; 
Query OK, 0 rows affected (0.01 sec) 
Records: 0 Duplicates: 0 Warnings: 0 


增加字段dd 设置int

mysql> alter table kk1 add dd int (11); 
Query OK, 0 rows affected (0.02 sec) 
Records: 0 Duplicates: 0 Warnings: 0 


删除字段 dd
mysql> alter table kk1 drop column dd; 
Query OK, 0 rows affected (0.02 sec) 
Records: 0 Duplicates: 0 Warnings: 0