Mysql数据库管理

一、数据库基本操作

1.1 查看数据库结构

1.1.1 查看数据库信息

1.1.2 查看数据库中表的信息

1.1.3 显示数据库表的结构

1.2 常用的数据类型

1.3 SQL 语句概述

1.3.1 SQL语言

1.3.2 SQL分类

1.3.3 使用DDL语句新建库、表

1.3.4 使用DML语句对表中的数据进行管理

1.3.5 使用DQL从数据表中查找符合条件的数据记录

1.3.6 数据表高级操作

1.3.7 DCL语句设置用户权限(用户不存在时，则新建用户)

二、数据库用户管理

2.1 新建用户

2.2 查看用户信息

2.3 重命名指定

2.4 删除用户

2.5 修改当前密码

2.6 修改其他用户密码

2.7 忘记root密码的解决办法

2.8 数据库用户授权

---

一、数据库基本操作

1.1 查看数据库结构

1.1.1 查看数据库信息

show database；

1.1.2 查看数据库中表的信息

use 数据库名

show tables；

1.1.3 显示数据库表的结构

describe 表名 也可以使用desc 表名

1.2 常用的数据类型

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

1.3 SQL 语句概述

1.3.1 SQL语言

Structured Query Language的缩写，即结构化查询语言，关系型数据库的标准语言，用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能。

1.3.2 SQL分类

DDL：数据定义语言，用于创建数据库对象，如库、表、索引等

DML：数据操纵语言，用于对表中的数据进行管理,用来插入、删除和修改数据库中的数据

DQL：数据查询语言，用于从数据表中查找符合条件的数据记录

DCL：数据控制语言，用于设置或者更改数据库用户或角色权限（数据控制语句，用于控制不通数据段直接的许可和访问级别的语句，这些语句定义了数据库、表、字段、用户的访问权限和安全级别,如COMMIT、ROLLBACK、GRANT、REVOKE）

1.3.3 使用DDL语句新建库、表

（1）创建新的数据库

CREATE DATABASE 数据库名;
（2）创建新的表

CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...][,PRIMARY KEY (主键名)]);
#主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。

（3）删除指定的数据表

use 数据库名

DROP TABLE 表名;

（4）删除指定的数据库
DROP DATABASE 数据库名;

1.3.4 使用DML语句对表中的数据进行管理

（1）insert 

INSERT INTO 表名(字段1,字段2[,...]) VALUES (字段1的值，字段2的值,...);

（2）update

UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式];

（3）delete

DELETE FROM 表名 [WHERE 条件表达式];

1.3.5 使用DQL从数据表中查找符合条件的数据记录

SELECT 字段名1,字段名2[,...] FROM 表名[WHERE 条件表达式];

显示前三行

显示三行后的前两行

以竖向的方式显示

1.3.6 数据表高级操作

（1）清空表

DELETE FROM 表名

注意：如果表中有自增长字段，使用DELETE FROM 删除所有记录后，再次新添加的记录会从原来最大的记录ID后面继续自增写入记录。

或

TRUNCATE TABLE 表名

注意：使用TRUNCATE TABLE 清空表内数据后，ID会从1开始重新记录。

汇总：

drop table 表名
属于DDL，不可回滚（无法恢复)，不可带where，表内容和结构删除，删除速度快。

truncate table 表名
属于DDL，不可回滚，不可带where，表内容删除，删除速度快。

delete from 表名
属于DML，可回滚(可恢复)，可带where，表结构在，表内容要看where执行的情况，删除速度慢，需要逐行删除。

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

（2）临时表

临时表创建成功之后，使用SHOWTABLES命令是看不到创建的临时表的，临时表会在连接退出后被销毁。

如果在退出连接之前，也可以可执行增删改查等操作，比如使用DROP TABLE语句手动直接删除临时表。

只会保存在内存中，在数据库退出连接之前的所有操作，都是在内存中进行的，不会保存在磁盘里面，退出连接后，临时表会释放掉。

（3）克隆表

（1）复制表结构

（2）复制内容

（3）内容备份但结构不一样

1.3.7 DCL语句设置用户权限(用户不存在时，则新建用户)

（1）修改表名和表结构

ALTER TABLE 旧表名 RENAME 新表名;

（2）扩展表结构(增加字段)

ALTER TABLE 表名 ADD address varchar(50) default '地址不详';

（3）修改字段(列)名，添加唯一键

ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [unique key];

（4）删除字段

ALTER TABLE 表名 DROP 字段名;

二、数据库用户管理

2.1 新建用户

CREATE USER '用户名'@'来源地址' [IDENTIFIED BY [PASSWORD] '密码'];

2.2 查看用户信息

USE mysql;
SELECT User,authentication_string,Host from user;

2.3 重命名指定

RENAME USER 'zhangsan'@'localhost' TO 'lisi'@'localhost';

2.4 删除用户

DROP USER 'lisi'@'localhost' ;

2.5 修改当前密码

SET PASSWORD = PASSWORD('abc123');

2.6 修改其他用户密码

SET PASSWORD FOR 'user1'@'localhost' = PASSWORD('abc123');

2.7 忘记root密码的解决办法

修改/etc/my.cnf 配置文件，免密登陆mysql
vim /etc/my.cnf
[mysqld]
skip-grant-tables    #添加，使登录mysql不使用授权表

systemctl restart mysqld
mysql    #直接登录

然后使用SQL语句修改密码
UPDATE mysql.user SET AUTHENTICATION_STRING = PASSWORD('abc123') where user='root';

FLUSH PRIVILEGES;
quit
mysql -u root -pabc123
PS:最后再把/etc/my.cnf 配置文件里的skip-grant-tables 删除，并重启mysql服务

2.8 数据库用户授权

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'来源地址' [IDENTIFIED BY '密码'];

（1）允许用户lisi在所有终端远程连接mysql，并拥有所有权限

GRANT ALL [PRIVILEGES] ON *.* TO 'lisi'@'%' IDENTIFIED BY '123456';

（2）刷新权限

flush privileges;