MySQL数据库基本操作+用户管理+用户授权

MySQL数据库管理

一、数据库的基本概念

• 数据(Data)

  描述事物的符号记录

  包括数字，文字、图形、图像、声音、档案记录等

  以“记录”形式按统一的格式进行存储

• 表

  将不同的记录组织在一起用来存储具体数据

• 数据库

  表的集合，是存储数据的仓库

  以一定的组织方式存储的相互有关的数据集合

  是按照数据结构来组织、存储和管理数据的仓库

• 数据库管理系统(DBMS)

  是实现对数据库资源有效组织、管理和存取的系统软件

  数据库的建立和维护功能数据定义功能、数据操纵功能、数据库的运行管理功能 、通信功能

• 数据库系统
  是一个人机系统由硬件、OS、数据库、DBMS、应用软件和数据库用户组成
  用户可以通过DBMS或应用程序操作数据库

操作系统：Windows  Linux/Unix  MacOS
Linux系统：Centos (yum,dnf)  Ubuntu(apt-get)  Opensuse
国产系统：华为（欧拉）、阿里（龙蜥）、腾讯（tencentOS）、红旗、统信、麒麟 (银河麒麟、中标麒麟->Centos 、优麒麟->Ubuntu)
1
2
3

1、关系数据库相关概念

• 关系数据库系统是基于关系模型的数据库系统
• 关系模型的数据结构使用简单易懂的二维数据表
• 关系模型可用简单的“实体-关系”(E-R)图来表示
• E-R图中包含了实体(数据对象)、关系和属性三个要素

实体
也称为实例，对应现实世界中可区别于其他对象的“事件”或“事物”
如银行客户、银行账户等
1
2
3

属性
实体所具有的某一特性，一个实体可以有多个属性
如“银行客户”实体集中的每个实体均具有姓名、住址、电话等属性
1
2
3

联系
实体集之间的对应关系称为联系，也称为关系
如银行客户和银行账户之间存在“储蓄”的关系
1
2
3

所有实体及实体之间联系的集合构成一个关系数据库

关系数据库的存储结构是二维表格

在每个二维表中
每一行称为一条记录，用来描述一个对象的信息
每一列称为一个字段，用来描述对象的一个属性
1
2
3

1.2、关系型数据库代表产品

典型代表：
Oracle,MySQL，MariaDB
SQLServer、Sybase
Informix、access
DB2、FoxPRO
PostgreSQL

国产数据库：
阿里云（RDB）、华为（高斯）、阿里（Oceanbase）、腾讯（TDBA）、人大金仓、达梦、greateSQL
1
2
3
4
5
6
7
8
9

应用场景：
12306用户信息系统
淘宝账号系统
联通手机号信息系统
银行用户账号系统
网站用户信息系统
1
2
3
4
5
6

2、非关系数据库介绍

• 非关系数据库也被称作NoSQL (Not Only SQL)

• 存储数据不以关系模型为依据，不需要固定的表格式

• 非关系型数据库的优点

  数据库可高并发读写
  对海量数据高效率存储与访问
  数据库具有高扩展性与高可用性
  1
  2
  3

• 常用的非关系数据库:Redis、mongoDB等

存储结构：键值对 文档 索引 时间序列
缓存型：Redis Memcached
文档型：MongoDB
索引型：ElasticSearch
时序型：Prometheus InfluxDB
1
2
3
4
5

3、MySQL数据库介绍

• 一款深受欢迎的开源关系型数据库

• Oracle旗下的产品

• 遵守GPL协议，可以免费使用与修改

• 特点

  性能卓越、服务稳定
  开源、无版权限制、成本低
  多线程、多用户基于C/SI (客户端/服务器) 架构
  安全可靠
  1
  2
  3
  4

二、MySQL数据库管理

1、数据库基本操作

1.1、常用的数据类型

int ：整型   无符号[0,2^32-1]，有符号[-2^31,2^31-1]
float ：单精度浮点	4字节32位
double ：双精度浮点	8字节64位
char ：固定长度的字符类型
varchar ：可变长度的字符类型
text ：文本
image ：图片
decimal(5,2) ：5个有效长度数字，小数点后面有2位
1
2
3
4
5
6
7
8

1.2、查看数据库结构

1.2.1查看当前服务器中的数据库

SHOW DATABASES;	
#查看数据库
1
2

1.2.2查看数据库中包含的表

USE 数据库名;
SHOW TABLES;
或
SHOW TABLES FROM 数据库名;
1
2
3
4

use mysql;
show tables;
1
2

1.2.3查看表的结构（字段）

USE 数据库名;
DESCRIBE [数据库名.]表名;
可缩写成：DESC 表名;
1
2
3

use mysql;
desc user;
1
2

1.3、SQL语句

SQL语句用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能。

1.3.1 SQL语言分类

DDL：数据定义语言，用于创建数据库对象，如库、表、索引等
DML：数据操纵语言，用于对表中的数据进行管理
DQL：数据查询语言，用于从数据表中查找符合条件的数据记录
DCL：数据控制语言，用于设置或者更改数据库用户或角色权限
1
2
3
4

1.4、创建及删除数据库和表

1.4.1 创建新的数据库

CREATE DATABASE 数据库名;
create database kkk;
#创建数据库kkk
1
2
3

1.4.2 创建新的表

use kkk;
#使用数据库kkk
create table www(id int,name char(4),age int,sex char(2),hobby varchar(20));
#创建表www，有5个字段
#CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...][,PRIMARY KEY (主键名)]);
#主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。
1
2
3
4
5
6

create table yyy(id int,name char(4),age int,sex char(2),hobby varchar(20),PRIMARY KEY(id));
#创建表yyy，指定id字段为主键
1
2

1.4.3 删除指定的数据表

DROP TABLE [数据库名.]表名;	
#如不用USE进入库中，则需加上数据库名
drop table yyy;
#删除表yyy
1
2
3
4

1.4.4 删除指定的数据库

DROP DATABASE 数据库名;
drop database kkk;
#删除数据库kkk
1
2
3

1.5、管理表中的数据记录

1.5.1 向数据表中插入新的数据记录

INSERT INTO 表名(字段1,字段2[,...]) VALUES(字段1的值,字段2的值,...);
insert into www (id,name,age,sex,hobby) values (1,'wyx',21,'女','sing');
#字符串要用引号引起来
1
2
3

INSERT INTO 表名 VALUES(字段1的值,字段2的值,...);
#如果按照表中的字段顺序插入，可以不加字段
insert into www values (2,bkg,22,'男','smoke');
1
2
3

insert into www (id,name,hobby) values (3,'张三','打架');
#只插入3个字段
1
2

1.5.2 查询数据记录

SELECT 字段名1,字段名2[,...] FROM 表名 [WHERE 条件表达式];
selet * from www;
#查询数据表中的所有记录
selet * from www where id=1 or id=3;
#查询数据表www中id=1和3的数据记录
1
2
3
4
5

#分页查询
select * from www limit 3;
#查看前3行数据
select * from www limit 2,2;
#查看第3行和第4行数据
#2，2表示第2行之后的连续两行记录（不包括第2行）
select * from www limit 9,11;
#查看第10行到20行数据
1
2
3
4
5
6
7
8

#纵向查询
select * from www\G
#纵向查看每行记录字段的值
1
2
3

#只看所指定的字段
select name,hobby from www;
#查看name和hobby字段
1
2
3

1.5.3 修改、更新数据表中的数据记录

UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式];
#不加条件就会修改所有字段，加上条件就只会修改条件范围内的字段
1
2

update wwww set age=23,sex='男';
#修改表中的age字段和sex字段
update wwww set age=20,sex='女' where id=1;
#修改www表中id为1的字段
1
2
3
4

1.5.4 在数据表中删除指定的数据记录

DELETE FROM 表名 [WHERE 条件表达式];
#不加where会把表中的数据记录都删掉
delete from www where id=1 or id=3;
#删除表中id=1和3的字段
delete from www where id>=1 and id<=3;
#删除id1-3号数据
1
2
3
4
5
6

1.6、修改表名和表结构

1.6.1 修改表名

ALTER TABLE 旧表名 RENAME 新表名;
alter table www rename www1;
alter table www1 rename www;
1
2
3

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

ALTER TABLE 表名 ADD address varchar(50) default '地址不详';
#default '地址不详'：表示此字段设置默认值 地址不详；可与NOT NULL配合使用
alter table www ADD address varchar(50) default '地址不详';
1
2
3

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

ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [unique key];
#一个表中可以有多个唯一键，但是只能有一个主键，唯一键字段可以有null值，但是不能重复，主键不能有null值
desc www;
#查看表结构
alter table www change name username varchar(10) unique key;
#修改name字段，把name修改为username，数据类型改为varchar(10),并添加唯一键
1
2
3
4
5
6

1.6.4 删除字段

ALTER TABLE 表名 DROP 字段名;
alter table www drop address;
#删除address字段
1
2
3

1.7、MySQL的约束特性

primary key：主键约束 字段的值不能重复，且不能为null，一个自建的表只能有一个主键
unique key：唯一性约束 字段的值不能重复，能为null，一个表可以有多个唯一键
not null：非空约束 字段的值不能为null
default：默认值约束  字段的值如果没有设置则使用默认值自动填充
auto_increment：自增约束 字段的值如果没有设置默认会从1开始每次自动递增1，要求自增字段必须设置主键
foreign key：外键约束 保证相关联的表数据的完整性和一致性

int(N) zerofill  零填充
1
2
3
4
5
6
7
8

use kkk;
create table if not exists info (
id int(4) zerofill primary key auto_increment,				
#指定主键的第二种方式
name varchar(10) not null default '匿名',
cardid int(18) not null unique key,
hobby varchar(50));
-------------------------------------------------------------------
#if not exists：表示检测要创建的表是否已存在，如果不存在就继续创建
#int(4) zerofill：表示若数值不满4位数，则前面用“0”填充，例0001
#auto_increment：表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增；自增长字段数据不可以重复；自增长字段必须是主键；如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次
#unique key：表示此字段唯一键约束，此字段数据不可以重复；一张表中只能有一个主键, 但是一张表中可以有多个唯一键
#not null：表示此字段不允许为NULL
1
2
3
4
5
6
7
8
9
10
11
12
13

#插入数据
insert into info (name,cardid,hobby) values ('zhangsan',11111111,'running');
insert into info (name,cardid,hobby) values ('lisi',222222,'jumping');
insert into info (name,cardid,hobby) values ('wangwu',33333333,'sleeping');
1
2
3
4

2、数据表高级操作

2.1、克隆表

将数据表的数据记录生成到新的表中

方法1：
create table test1 like info;				
#通过 LIKE 方法，复制 info 表结构生成 test1 表
insert into test1 select * from info;
#此方法能保证新表的表结构、表数据 跟旧表都是一致的
1
2
3
4
5

方法二：
create table test2 (select * from test1);
#此方法创建的新表的表数据和旧表是一样的，但可能会出现新表的表结构和旧表的不一致
SELECT * from test1;
SELECT * from test2;
#查看表数据
desc test1;
desc test2;
#查看表结构
1
2
3
4
5
6
7
8
9

2.2、清空表，删除表内的所有数据

方法一：
delete from info;
#DELETE清空表后，返回的结果内有删除的记录条目；DELETE工作时是一行一行的删除记录数据的；如果表中有自增长字段，使用DELETE FROM 删除所有记录后，再次新添加的记录会从原来最大的记录 ID 后面继续自增写入记录。
1
2
3

insert into info (name,cardid,hobby) values ('zhaoliu',444444,'swimming');
#插入数据
select * from info;
#查询表数据
delete from info;
#清空表
select * from info;
#空的
insert into info (name,cardid,hobby) values ('zhaoliu',444444,'swimming');
#再次插入会发现新添加的记录会从原来最大的记录 ID 后面继续自增写入记录
1
2
3
4
5
6
7
8
9
10

方法二：
truncate table info;
#TRUNCATE 清空表后，没有返回被删除的条目；TRUNCATE 工作时是将表结构按原样重新建立，因此在速度上 TRUNCATE 会比 DELETE 清空表快；
1
2
3

insert into info (name,cardid,hobby) values ('zhangsan',11111111,'running');
insert into info (name,cardid,hobby) values ('lisi',222222,'jumping');
#插入数据
select * from info;
#查询表数据
truncate table info;
#清空表
insert into info (name,cardid,hobby) values ('zhangsan',11111111,'running');
#再次插入数据记录会从1开始自增
1
2
3
4
5
6
7
8
9

2.3、创建临时表

临时表创建成功之后，使用SHOW TABLES命令是看不到创建的临时表的，临时表会在连接退出后被销毁。临时表只能在当前会话中有效。 如果在退出连接之前，也可以可执行增删改查等操作，比如使用 DROP TABLE 语句手动直接删除临时表。

CREATE TEMPORARY TABLE 表名 (字段1 数据类型,字段2 数据类型[,...][,PRIMARY KEY (主键名)]);
1

create temporary table test3 (id int,name varchar(10),sex char(3));
#创建临时表
insert into test3 values (1,'张安','男'),(2,'里斯','男'),(3,'丽娟','女');
#插入数据
select * from test3;
#查询表数据
show tables;
#查看表，看不到
desc test3;
#查看表结构
delete from test3 where id=3;
#删除id=3的记录
1
2
3
4
5
6
7
8
9
10
11
12

2.4、创建外键约束

保证数据的完整性和一致性

外键的定义：如果同一个属性字段X在表一中是主键，而在表二中不是主键，则字段X称为表二的外键。

主键表和外键表的理解：
（1）以公共关键字作主键的表为主键表（父表、主表）
（2）以公共关键字作外键的表为外键表（从表、外表）
1
2
3

注意：与外键关联的主表的字段必须设置为主键。要求从表不能是临时表，主从表的字段具备相同的数据类型、字符长度和约束。

主键表： alter table 表名 add primary key (主键字段);
外键表： alter table 表名 add foreign key (外键字段) references 主键表名 (主键字段);
1
2

#使用kkk数据库
use kkk;
#创建主表 profession
create table profession (proid int,proname varchar(20));
#创建从表 student
create table student (id int,name varchar(10),age int,sex char(2),proid int);
#为主表 profession 添加一个主键约束
alter table profession add primary key (proid);
#查看是否添加主键
desc profession;
#以SQL语句的方式显示表结构
show create table profession\G
#为从表 student 表添加外键，并将 student 表的 proid 字段和 profession 表的 proid 字段建立外键关联。外键名建议以“FK_”开头。
alter table student add constraint FK_proid foreign key (proid) references profession (proid);
#查看从表结构
show create table student\G
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

#测试外键约束有没有效果
#插入新的数据记录时，要先主表再从表
insert into profession values(1,'云计算');
insert into profession values(2,'大数据');
insert into profession values(3,'JAVA');
insert into profession values(4,'前端');
insert into profession values(5,'UI');
#查看主表数据
select * from profession;
#插入从表数据
insert into student values(1,'zhangsan',18,'男',1);
insert into student values(2,'lisi',19,'男',3);
insert into student values(3,'wangwu',20,'男',2);
insert into student values(4,'丽丽',20,'女',5);
#查看从表数据
select * from student;
#这时再插入proid为6的数据会发现插入不了，因为有外键约束外键关联，profession表的proid最大到5，没有6
insert into student values(5,'丽丽',20,'女',6);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

#删数数据记录时，要先从表再主表，也就是说删除主键表的记录时必须先删除其他与之关联的表中的记录。
delete from student where proid=1;
delete from profession where proid=1;
1
2
3

alter table student drop foreign key FK_proid;
#删除外键关联
alter table student drop key FK_proid;
#删除外键约束
1
2
3
4

三、数据库用户管理

1、新建用户

CREATE USER '用户名'@'来源地址' [IDENTIFIED BY [PASSWORD] '密码'];
-------------------------------------------------------------------
'用户名'：指定将创建的用户名
'来源地址'：指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、主机名的形式，本地用户可用localhost，允许任意主机登录可用通配符%
'密码'：若使用明文密码，直接输入'密码'，插入到数据库时由Mysql自动加密；
        若使用加密密码，需要先使用SELECT PASSWORD('密码'); 获取密文，再在语句中添加 PASSWORD '密文';
        若省略“IDENTIFIED BY”部分，则用户的密码将为空（不建议使用）
1
2
3
4
5
6
7

CREATE USER 'songsong'@'localhost' IDENTIFIED BY '123456';
#创建用户songsong，localhost本地登录，密码是123456
CREATE USER 'songsong'@'%' IDENTIFIED BY '123456';
#%允许任意主机登录
1
2
3
4

2、查看用户信息

#创建后的用户保存在 mysql 数据库的 user 表里
#查看用户
USE mysql;
select user,authentication_string,Host from user;

#查看当前登录用户
select user();
1
2
3
4
5
6
7

3、重命名用户

RENAME USER 'songsong'@'localhost' TO 'wang'@'%';
#重命名用户，也可以修改来源地址
select user,authentication_string,Host from user;
#查看用户
1
2
3
4

4、修改用户密码

SET PASSWORD = PASSWORD('abc123');
#修改当前用户登录密码
1
2

#修改其他用户登录密码
方法1：
SET PASSWORD FOR 'songsong'@'%' = PASSWORD('123abc');
方法2：
alter user 'songsong'@'%' identified by 'abc123';
1
2
3
4
5

5、删除用户

DROP USER 'wang'@'%';
#删除用户wang
select user,authentication_string,Host from user;
#查看用户
1
2
3
4

6、忘记 root 密码的解决办法

（1）修改 /etc/my.cnf 配置文件，不使用密码直接登录到 mysql

vim /etc/my.cnf
[mysqld]
skip-grant-tables					
#添加，使登录mysql不使用授权表
systemctl restart mysqld
#重启
mysql
#直接登录
1
2
3
4
5
6
7
8

（2）使用 update 修改 root 密码，刷新数据库

UPDATE mysql.user SET AUTHENTICATION_STRING = PASSWORD('abc123') where user='root'[and host='localhost'];
#修改root密码，可以指定host，也可以不指定
FLUSH PRIVILEGES;
quit
#刷新权限，然后退出
mysql -u root -pabc123
#登录
1
2
3
4
5
6
7

注意：最后再把 /etc/my.cnf 配置文件里的 skip-grant-tables 删除，并重启 mysql 服务。

四、数据库用户授权

1、授予权限

GRANT语句：专门用来设置数据库用户的访问权限。当指定的用户名不存在时，GRANT语句将会创建新的用户；当指定的用户名存在时， GRANT 语句用于修改用户信息。mysql 8.0以后grant只能做权限的授权，不能做用户的创建。

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'来源地址' [IDENTIFIED BY '密码'];
----------------------------------------------------------------------------------------------------------
#权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔，如“select,insert,update”。使用“all”表示所有权限，可授权执行任何操作。
#数据库名.表名：用于指定授权操作的数据库和表的名称，其中可以使用通配符“*”。例如，使用“kgc.*”表示授权操作的对象为 kgc数据库中的所有表。
#'用户名@来源地址'：用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。来源地址可以是域名、IP地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址，如“%.kgc.com”、“192.168.80.%”等。
#IDENTIFIED BY：用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略“IDENTIFIED BY”部分，则用户的密码将为空。
1
2
3
4
5
6
7
8

mysql -u songsong -p123abc
#songsong用户登录
show databases;
#查看数据库，发现只有一个库，之前root用户登录创建的库都看不到，说明create创建的用户只有登录权限，没有任何库、表的操作权限
1
2
3
4

grant insert,update,select on kkk.* to 'songsong'@'%';
#授予songsong用户插入，修改，查看数据的权限
flush privileges;
#刷新权限，立即生效
1
2
3
4

show databases;
#查看数据库，有kkk了
use kkk;
show tables;
#可以查看表
select * from www;
#查看表中的数据
insert into www values (7,'李丽',18,'女','爱美');
#可以插入数据
1
2
3
4
5
6
7
8
9

SHOW GRANTS FOR 'songsong'@'%';
#查看权限
1
2

grant all on *.* to 'songsong'@'%' identified by '123abc';
#给用户所有权限
flush privileges;
#刷新权限，立即生效
SHOW GRANTS FOR 'songsong'@'%';
#查看权限
1
2
3
4
5
6

quit
mysql -u songsong -p123abc
#重新登录
show databases;
#所有数据库都能看到
use kkk;
select * from www;
#查看表中的数据
delete from www where sex is null;
#删除性别为空的用户
1
2
3
4
5
6
7
8
9
10

2、查看权限

SHOW GRANTS FOR 用户名@来源地址;
1

SHOW GRANTS FOR 'songsong'@'%';
#查看权限
1
2

3、撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@来源地址;
1

revoke all on *.* from 'songsong'@'%';
#撤销权限
SHOW GRANTS FOR 'songsong'@'%';
##USAGE权限只能用于数据库登陆，不能执行任何操作；USAGE权限不能被回收，即 REVOKE 不能删除用户。
1
2
3
4