MySQL数据库的权限问题操作及基本增删改查操作

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

前面我们讲了mysql的基本内容，现在我们详细的了解一下 MySQL 中的具体操作。

what's the SQL

SQL(Structured Query Language 即结构化查询语言)

SQL 语言主要用于存储数据、更新数据和管理关系数据系统，由 IBM 开发。

SQL语句分为三种：

DDL 语句数据库定义语言：数据库、表、视图、索引、存储过程，例如create、alter
DML 语句数据库操纵语言：插入数据 insert、删除数据 delete、更新数据 update、查询 select
DCL 语句数据库控制语言：例如控制用户的访问权限grant、revoke

系统数据库

MySQL 刚创建完成的时候会生成四个系统数据库：information_schema、performance_schema、mysql、test、

- information_schema：虚拟库，不占用磁盘内存，存储的是数据库启动后的参数
- performance_schema：用于收集数据库服务器性能的参数，记录处理查询请求时发生的各种事件、锁等现象
- mysql：授权库，存储系统用户的权限信息
- test：MySQL数据库系统自动创建的测试数据库

创建用户

首先数据库需要有创建用户才可以登陆，这里创建用户共有三种形式：

# 创建新用户
create user 'gggg'@'hostlocal' identified by '';
#在本机上创建一个密码为123的用户，只可在本机进行操作create user 'alex'@'%' identified by '';
#创建一个密码为123的alex用户，%表示所有人都可在任何机器上登陆create user 'eva'@'192.168.20.%' identified by '';
#创建一个密码为123的eva用户，只有在该局域网下的机器可登陆
#此时的用户若想登陆，必须输入创建用户的计算机的ip
mysql -h192.168.20.97 -ueva -p123;

为了使新创建的用户有权限对数据库进行操作（增删改查，insert delete alter select），我们需要对该用户进行授权。如一般用户只可查看数据库里的库中的内容或者只让其查看某个表的内容，或者赋予某个用户可以修改数据的权利，就得赋予其一定的权限。注意，权限是你创建时给予的，你可以为了防止数据被篡改或者防止数据泄露只赋予其很少的权限。

这里我们需要了解一个关于数据库内容的知识点。数据库管理系统，即软件（如mysql，oracle等）管理数据库（即文件夹），数据库整理表（即文件夹中有文件），表又是存放记录的（即文件中有文件内容），所以赋予权限时也需要进行分层。

级别1：若你赋予其对数据库的某项操作权限，意味着该用户就可以对数据库中所有的库，以及每个库中所有的表，每个表中所有的记录进行该项操作；
级别2：若你只赋予其对某个指定的库的某项操作权限，那就意味着该用户只可对该指定的库，以及该库中所有的表，该库中的表中所有的记录进行该项操作；
级别3：若你只赋予其对某个指定的库中指定的表进行某项操作权限，那就意味着该用户只可对该指定的库中的指定的表，以及该表中的所有记录进行该项操作；
级别4：若你只赋予其对某个指定的库中的指定的表中的指定的某项记录进行某项操作的权限，那就意味着该用户只仅仅可对该记录进行该项操作

#创建对不同级别拥有操作权限的用户，下面用查看操作select举例
#级别1：对所有库拥有权限
grant select on *.* to 'gggg1'@'localhost' identified by '';#级别2：对指定库db1拥有权限
grant select on db1.* to 'gggg2'@'localhost' identified' by '123';#级别3：对指定库下的指定表拥有权限db1.t1
grant select on db1.t1 to 'gggg3'@'localhost' identified by '';#级别4：仅对某字段拥有权限
grant select (id,name) on db1.ti to 'gggg4'@'localhost' identified by '';#赋予查看id和name的权限grant select (id,name),update (name) on db1.t1 to 'gggg5'@'localhost' identified by '';
#赋予查看id和name的权限，并赋予修改name的权限#删除权限
revoke select on db1.* to 'gggg5'@'%';#修改完权限后记得手动刷新
flush privileges;

上述的 select 可根据需要换成以下任何权限：

权限指定符允许的操作
alter	修改表和索引
create	创建数据库和表
delete	删除表中已有记录
drop	删除数据库和表
index	创建或删除索引
insert	插入数据
reference
select	查询表记录
update	更新表记录
file	读写服务器上的文件
process	查看、杀死线程
reload	重新授权表或清空日志、主机缓存、表缓存
shutdown	关机
all privileges	所有权限
usage	无权限

基本增删改查操作

以上是创建拥有相应权限的用户的操作。那么在拥有权限后，登陆该用户就可以对数据库进行操作了，对数据库的操作又分为对库（文件夹）的操作、对表（文件）的操作和对记录（文件内容）的操作。操作即增删改查。

#对库进行操作（文件夹）
#增：
create database db1 charset utf8;#在data目录下新增一个db1库（db1文件夹）
#查：
show databases;#查看data目录下所有的库（所有的文件夹）
show create database db1;#查看新创建的db1库
#改：
alter database db1 charset gbk;#将db1的字符编码改为gbk
#删：
drop database db1;#删除data目录下的db1库#对表进行操作（文件）
#首先要切换到指定文件夹下：use db1;
#增：
create table t1(id int,name char(10));#新建一个表，表中的字段必须表上数据类型，char表示字符串
create table t1(id int,name char(10))engine=innodb,default charset gbk;
#新建一个表，可以指定引擎，不指定的话默认的引擎就是innodb，default是指定其字符编码，若不指定默认为该库的字符编码
#查：
show tables;#查看所有表
show create table t1;#查看创建的表
desc t1;#查看表结构，比show好用
#改：
alter table t1 add age int;#给t1表加一个字段age，仍需指定数据类型
alter table t1 modify name char(12);#修改name数据类型的字节数
#删：
drop table t1;#对记录进行操作（文件内容）
#增：
insert into db1.t1 values(1,'gggg1'),(2,'gggg2');#按位置传参
insert into db1.t1 (name) values('gggg'),('alex');#指定只传name，id默认为null
#查：
select * from t1;#查看t1中全部记录
select name,id from t1;#查看表中指定字段
#改：
update t1 set name='SB' where id=4;#将id为4的字段的name修改为SB
update t1 set name='SB' where name='alex';#将name为alex的字段的name修改为SB
update t1 set name='';#将name的值都修改为空，即清空所有name的内容
#删：
delete from t1 where id=4;#删除指定字段
#清空：
delete from t1;将记录全部删掉，也可认为是清空
truncate t1;#真正意义上的清空，当数据量较大时，这个的速度比delete快，推荐使用这个清空方式

这里补充一个内容，当我们要向表中新增记录时，每次都要输入id和name，而id是顺序增加的，每次自己手动加入会显得疲劳，这里我们就可以用到自增id的方式

#自增id（就是增加一个约束，该约束可以使系统自己添加id）
create table t1 (id int primary key auto_increment,name char(10));
#primary key的意思为主键，主键不为空且唯一
#在一个字段中只能哟一个主键，通常是idcreate table t1 (id int not full unique,name char(10));
#not full unique的意思就是不为空且独一无二，所以该命令与上面作用一样，但推荐使用上者#设定自增id后，增加字段就无需指定id
insert into t1(name) values ('gggg1'),('gggg2');#此时id会自动按序生成

最后说一下拷贝表结构的操作

#拷贝表结构
create table t2 select * from t1 where 1=2;#只拷贝表结构，不拷贝表内容
alter table t2 modify id int primary key auto_increment;#将表修改成自增id