MySQL数据库基本操作（增删改查）与用户授权

前言

SQL（Structured Query Language，结构化查询语言）是一种用于管理关系数据库系统的语言。SQL的设计目标是提供一种简单、直观的语言，使得用户可以通过编写SQL语句来处理他们想要的数据和操作。

一、结构介绍

1. 查看信息

1.1 查看数据库信息

1.2 查看数据库中的表信息

1.3 显示数据表的结构（字段）

2. 结构字段

3. 常用数据类型

二、SQL语言

1. 分类

2. DDL

2.1 创建数据库

2.2 创建数据表

2.3 获取表结构

2.4 删除数据库

2.5 删除数据表

3. DML

3.1 表中插入新数据

3.2 更新修改

3.3 删除表数据

4. DQL

5. 数据表的高级操作

5.1 清空表

5.2 三种删除命令区别

6. 临时表

7. 克隆表

8. DCL

8.1 修改表名

8.2 扩展表结构（增加字段）

8.3 修改字段（列）名，添加唯一键

8.4 删除

二、用户授权管理

1. 用户管理

1.1 新建用户

1.2 查看用户信息

1.3 重命名指定

1.4 删除用户

1.5 修改当前密码

1.6 修改其他用户密码

1.7 忘记密码

2. 用户授权

2.1 基本格式介绍

2.2 授权示例

2.3 查看权限

2.4 撤销权限

2.5 all privilege 权限

一、结构介绍

1. 查看信息

1.1 查看数据库信息

mysql> show databases;

1.2 查看数据库中的表信息

mysql> use mysql;     #首先要切换到mysql库内
mysql> show tables;  

#或者
mysql> show tables in mysql;

1.3 显示数据表的结构（字段）

mysql> use mysql;
mysql> describe user;

#或者
mysql> describe mysql.user;   #describe 库名.表名

2. 结构字段

Field:字段名称
type :数据类型
Null :是否允许为空值
key  :主键
Default:默认值
Extra  :扩展属性，例如:AUTO_INCREMENT：这个属性通常用于整数类型的字段，表示这个字段的值是自动递增的。

3. 常用数据类型

int：整型                   #用于定义整数类型的数据
float：单精度浮点4字节32位    #准确表示到小数点后六位		
double：双精度浮点8字节64位   #精确度更高
char：固定长度的字符类型	   #用于定义字符类型数据，如：20字节，只写入4个字符，依然占用20字节
#Char如果存入数据的实际长度比指定长度要小，会补空格至指定长度，如果存入的数据的实际长度大于指定长度，低版本会被截取，高版本会报错
varchar：可变长度的字符类型   #区别于char，如：20字节，只写入4个字符，占用4字节
text：文本
image：图片
decimal(5,2)：5个有效长度数字，小数点后面有2位
#指定长度数组 ，如：999.99

二、SQL语言

SQL语句用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能。在MySQL数据库中SQL语句不区分大小写。

1. 分类

① DDL：数据定义语言（创建、修改、删除数据库对象）

② DML：数据操纵语言（插入、修改、删除、查询数据）

③ DQL：数据查询语言（数据库中查询信息）

④ DCL：数据控制语言（数据库访问权限和安全性）

2. DDL

用于创建数据库对象，如库、表、索引等

2.1 创建数据库

格式为：create database 数据库名;
示例：
mysql> create database school;   #创建数据库school
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |       #information_schema数据库提供了关于数据库对象的元数据信息
| mysql              |       #mysql 是一个系统数据库
| school             |       #新建的数据库
| performance_schema |       #performance_schema数据库则提供了关于数据库服务器性能的详细信息  
| sys                |       #sys是一个用于性能监测和诊断的工具库
+--------------------+
5 rows in set (0.00 sec)

2.2 创建数据表

格式：create table 数据库名（至少一个字段定义...）;
示例：
mysql> use school;
mysql> create table class(id int not null,name char(15) not null,score decimal(5,2),passwd char(45)default'',primary key(id));
Query OK, 0 rows affected (0.03 sec)
# id int not null：这是一个整数类型的列，且不允许为空。这通常用作表的主键，用于唯一标识每一行。
# name char(15) not null：这是一个字符类型的列，长度为 15，且不允许为空。用于存储学生的名字。
# score decimal(5,2)：这是一个十进制数类型的列，总共有 5 位数，其中 2 位是小数。用于存储学生的分数。
# passwd char(45) default ''：这是一个字符类型的列，长度为 45，且默认值为空字符串。用于存储学生的密码。
# primary key(id)：这是一个主键约束，指定 id 列作为主键。主键是用于唯一标识每一行的列，且每一行的主键值都必须唯一。

2.3 获取表结构

格式：desc 表名；
示例：
mysql> desc class;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |  #字段
+--------+--------------+------+-----+---------+-------+
| id     | int(11)      | NO   | PRI | NULL    |       |
| name   | char(15)     | NO   |     | NULL    |       |
| score  | decimal(5,2) | YES  |     | NULL    |       |
| passwd | char(45)     | YES  |     |         |       |
+--------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

2.4 删除数据库

格式：drop database 库名； #此方法删除库后，库以及库里的表一并删除，谨慎操作

2.5 删除数据表

格式：
drop table 表名；  #此方法删除表后，表的结构和内容一并删除，谨慎操作
#一般需要先use进入库中再删除；如果不用use进入库中，删除则需加上数据库名
mysql> drop table 库名.表名；

3. DML

用于对表中的数据进行管理，用来插入、删除和修改数据库中的数据

3.1 表中插入新数据

格式1：
use 库；
insert into 表名(字段1,字段2,...) values(字段1的值,字段2的值,...);
# values()中字符串建议加''
格式2：
insert into 表名 values(字段1的值,[字段2的值,]...);
# values()中部分字段值可以不写
示例：
mysql> insert into class(id,name,score,passwd) values(1,'zs',500.50,password('123456'));
# 向 class 表插入一行数据，id、name、score 和 passwd 是表 "class" 的列名，它们的值分别是 1、zs、500.50 和 123456，密码以密文显示
mysql> insert into class values(2,'lisi',666.00,666666);
# 向 class 表插入一行数据，id、name、score 和 passwd 是表 "class" 的列名，它们的值分别是 2、lisi、666.00 和 666666
mysql> select * from class;    #查看表信息
+----+------+--------+-------------------------------------------+
| id | name | score  | passwd                                    |
+----+------+--------+-------------------------------------------+
|  1 | zs   | 500.50 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
|  2 | lisi | 666.00 | 666666                                    |
+----+------+--------+-------------------------------------------+
2 rows in set (0.00 sec)

3.2 更新修改

格式：
update 表名 set 字段名1=值1[,字段名2=值2] where 条件表达式;
# set 关键字用于指定要更新的列和它们的新值
# where 关键字用于指定更新操作应该应用到哪些行。只有满足 where 子句的行才会被更新
示例：
mysql> update class set name='wang' where name='zs';
# 将表 class 中所有 name 列的值为 zs 的行的 name 列的值更新为 wang
mysql> update class set score='580.00' where id=2;
# 将表 class 中 id 列的值为 2 的行的 score 列的值更新为 580.00
mysql> select * from class;
+----+------+--------+-------------------------------------------+
| id | name | score  | passwd                                    |
+----+------+--------+-------------------------------------------+
|  1 | wang | 500.50 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
|  2 | lisi | 580.00 | 666666                                    |
+----+------+--------+-------------------------------------------+
2 rows in set (0.00 sec)

3.3 删除表数据

格式：
delete from 表名；                 #删整张表，谨慎操作
delete from 表名 where 条件表达式;  #指定删除匹配项
如果不用use进入库中：
delete from 库名.表名；
示例：
mysql> delete from class where id=2;
# 从名为 "class" 的表中删除 id 列的值为 2 的行
mysql> select * from class;
+----+------+--------+-------------------------------------------+
| id | name | score  | passwd                                    |
+----+------+--------+-------------------------------------------+
|  1 | wang | 500.50 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+----+------+--------+-------------------------------------------+
1 row in set (0.00 sec)

4. DQL

用于从数据表中查找符合条件的数据记录

格式：
select 字段名1[,字段名2],... from 表名[where 条件表达式]; #字段没有要求可以写成*
如果不用use进入库中:
select 字段名1[,字段名2],... from 库名.表名;
示例：
mysql> select * from class;
# 查询名为 class 的表中选择所有列和行的信息
mysql> select * from class;
+----+------+--------+-------------------------------------------+
| id | name | score  | passwd                                    |
+----+------+--------+-------------------------------------------+
|  1 | wang | 500.50 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
|  2 | lisi | 580.00 | 666666                                    |
+----+------+--------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql> select name,score from class where name='wang';
# 从名为 class 的表中选择 name 和 score 两列，匹配 name 列的值为 wang 的信息。
+------+--------+
| name | score  |
+------+--------+
| wang | 500.50 |
+------+--------+
1 row in set (0.00 sec)

其他示例：

mysql> select name from class\G;
# 以列表方式竖向显示
*************************** 1. row ***************************
name: wang
*************************** 2. row ***************************
name: lisi

mysql> select * from class limit 1;
# 只显示头1行
+----+------+--------+-------------------------------------------+
| id | name | score  | passwd                                    |
+----+------+--------+-------------------------------------------+
|  1 | wang | 500.50 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+----+------+--------+-------------------------------------------+

mysql> select * from class limit 1,1;
# 显示第1行后的前1行
+----+------+--------+--------+
| id | name | score  | passwd |
+----+------+--------+--------+
|  2 | lisi | 580.00 | 666666 |
+----+------+--------+--------+

5. 数据表的高级操作

5.1 清空表

方法一：

格式：
delect from 表名;
# delete 清空表后，返回的结果内有删除的记录条目
# delete 工作时是逐行的删除数据；如果表中有自增长字段，使用 delete from 删除所有记录后，再次新添加的记录会从原来最大的记录ID后面继续自增写入记录

方法二：

truncate table 表名;
# truncate 清空表后，没有返回被删除的条目
#TRUNCATE 工作时是将表结构按原样重新建立，因此在速度上 truncate 会比 delete 清空表快；使用truncate table 清空表内数据后，id 会从 1 开始重新记录

5.2 三种删除命令区别

特性/命令	drop table 表名	truncate table 表名	delete from 表名
语言类	属于DDL	属于DDL	属于DML
回滚恢复	不可回滚（无法恢复）	不可回滚（无法恢复）	可回滚（可恢复）
是否带where	不可带where	不可带where	可带where
删除情况	表内容和结构删除	表内容删除（结构保留）	表结构保留，表内容视where执行情况
删除速度	删除速度快	删除速度快	删除速度慢，需要逐行删

总结：

不再需要一张表的时候，用drop
想删除部分数据行时候，用delete，并且带上where子句
保留表结构而删除所有数据的时候用truncate
删除速度：drop> truncate > delete
delete 安全性最好

6. 临时表

临时表是一种特殊的数据表，在生产环境中，有时候会用临时表做一些性能测试。它只在当前的数据库会话中存在（临时存放内存中），当会话结束时，临时表就会被自动删除。

格式：
create temporary table 表名 (字段1 数据类型[,字段2 数据类型],... [PRIMARY KEY (主键名)]);
# 临时表创建成功之后，使用 show tables 命令是看不到创建的临时表的，临时表会在连接退出后被销毁
# 在退出连接之前，也可以可执行增删改查等操作，比如使用 drop table 语句手动直接删除临时表
示例：
mysql> create temporary table class1 (id int(4) zerofill primary key auto_increment,name varchar(15) not null,cardid int(18) not null unique key,hobby varchar(60),shouji int(11) unique key);
# int(4) zerofill：表示若数值不满4位数，则前面用"0"填充，例0001
# auto_increment：表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增;自增长字段数据不可以重复;自增长字段必须是主键;如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次
# unique key：表示此字段唯一键约束，此字段数据不可以重复:一张表中只能有一个主键，但是一张表中可以有多个唯一键
# not null：表示此字段不允许为NULL
# primary key(id)：这是一个主键约束，指定 id 列作为主键。主键是用于唯一标识每一行的列，且每一行的主键值都必须唯一。
mysql> insert into class1 values(1,'hong',111111,'sing',138); 
mysql> insert into class1 values(2,'ming',111111,'dance',139);
ERROR 1062 (23000): Duplicate entry '111111' for key 'cardid'
# cardid唯一键约束，无法创建相同的值
mysql> insert into class1 values(2,'ming',222222,'dance',139);
mysql> insert into class1 (name,cardid,hobby,shouji) values('yang',333333,'rap',181);
mysql> select * from class1;
+------+------+--------+-------+--------+
| id   | name | cardid | hobby | shouji |
+------+------+--------+-------+--------+
| 0001 | hong | 111111 | sing  |    138 |
| 0002 | ming | 222222 | dance |    139 |
| 0003 | yang | 333333 | rap   |    181 |
+------+------+--------+-------+--------+
3 rows in set (0.00 sec)

mysql> exit
mysql> use school;
mysql> select * from class1;
ERROR 1146 (42S02): Table 'school.class1' doesn't exist
# 退出即失效

7. 克隆表

克隆表是指在数据库中创建一个已存在表的副本。克隆表包含了原表的结构和数据，但是它们是独立的，对克隆表的修改不会影响原表。克隆表通常用于备份数据，或者在进行复杂的数据处理时进行实验，以避免对原表的影响。

方法1：like 方法
create table 新表名 like 旧表名;    
#复制格式，通过like方法，复制旧表结构生成新表
insert into 新表名 select * from 旧表名;     
#备份内容
方法2：show create table 方法
create table 新表名 (select * from 旧表名);   
#复制旧表数据到新表中，即将旧数据表的数据记录生成到新的表中
示例：方法1
mysql> use school;
mysql> create table class1 like class;
mysql> desc class1;            
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | int(11)      | NO   | PRI | NULL    |       |
| name   | char(15)     | NO   |     | NULL    |       |
| score  | decimal(5,2) | YES  |     | NULL    |       |
| passwd | char(45)     | YES  |     |         |       |
+--------+--------------+------+-----+---------+-------+
# 查看表结构，可见格式字段包括主键都存在
mysql> insert into class1 select * from class;
mysql> select * from class1;
+----+------+--------+-------------------------------------------+
| id | name | score  | passwd                                    |
+----+------+--------+-------------------------------------------+
|  1 | wang | 500.50 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
|  2 | lisi | 580.00 | 666666                                    |
+----+------+--------+-------------------------------------------+
# 查看表内容都存在

示例：方法2
mysql> create table class2 (select * from class);
mysql> select * from class2;
+----+------+--------+-------------------------------------------+
| id | name | score  | passwd                                    |
+----+------+--------+-------------------------------------------+
|  1 | wang | 500.50 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
|  2 | lisi | 580.00 | 666666                                    |
+----+------+--------+-------------------------------------------+
# 查看表内容都存在
mysql> desc class2;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | int(11)      | NO   |     | NULL    |       |
| name   | char(15)     | NO   |     | NULL    |       |
| score  | decimal(5,2) | YES  |     | NULL    |       |
| passwd | char(45)     | YES  |     |         |       |
+--------+--------------+------+-----+---------+-------+
# 查看表结构，格式字段存在，缺少主键
mysql> show create table class2\G     #获取数据表的表结构、索引等信息
*************************** 1. row ***************************
       Table: class2
Create Table: CREATE TABLE "class2" (
  "id" int(11) NOT NULL,
  "name" char(15) NOT NULL,
  "score" decimal(5,2) DEFAULT NULL,
  "passwd" char(45) DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8

总结：克隆表有两种

① 迁移表：表的结构、属性、约束、数据等

② 备份数据：表的属性、内容（数据）

8. DCL

修改表名和表结构