MySQL常用管理SQL语句

目录

• • ddl
  • dcl
  • dml
  • 1.ddl语句之管理数据库
  • 2.ddl&&dcl语句之管理用户

前言

ddl

ddl，中文为数据定义语言，ddl的特点是对数据库内部的对象进行create（创建）、alter（修改）、drop（删除）等操作，负责管理数据库的基础数据，不涉及对表中内容的操作和更改。

dcl

dcl，中文为数据控制语言，ddl的特点是对数据库内部的对象grant（用户授权）、revoke（权限回收）、commit（提交）、rollback（回滚）。

dml

dml，中文为数据操作语言，dml的特点是对数据库内部的对象insert（增）、delete（删）、update（改）、select（查），主要针对数据库中表内的数据进行操作。

1.ddl语句之管理数据库

1.1.创建数据库

create database test;  #创建默认字符集数据库
create database test_gbk character set gbk collate gbk_chinese_ci;  #创建gbk字符集数据库
create database test_utf8 character set utf8 collate utf8_general_ci;  #创建utf8字符集数据库
show create database test\g  #查看建库语句

1.2.显示数据库

show databases;  #查看所有数据库
show databases like 'test';  #匹配test字符串的数据库
show databases like 'test%';  #%为通配符，表示匹配以test开头的所有数据库

1.3.切换数据库

use test;  #切换到test库
select database();  #查看当前管理员所在的库名

1.4.查看数据库包含的表信息

1.4.1.切换到数据库里面去查看表信息

use test;
show tables;

1.4.2.在库外面查看库里的表信息

show tables from test;  #查看指定库test中包含的表
show tables in test_gbk;
show tables from mysql like 'db%';  #匹配包含指定字符开头的表

1.5.删除数据库

drop database test_gbk;  #删除test_gbk数据库
show databases like 'test_gbk';

2.ddl&&dcl语句之管理用户

2.1.查看当前数据库的用户列表

查看数据库用户列表属于dml负责的部分内容。

select user,host from mysql.user;  #这里的select关键字表示查询，是dml语句的关键字之一，user和host为要查找的mysql表的字段，from表示去哪查，mysql.user是mysql库里的user表。数据库的标准用户由“用户”@“主机名”共同组成的，两者加起来是数据库用户的唯一标识。

2.2.创建数据库用户

create user test1@localhost identified by '123456';
select user,host from mysql.user;  #查看所有数据库用户
create user test2@'192.168.1.%' identified by '123456';
select user,host from mysql.user where user='test2;  #查看指定数据库用户
show grants for test2@'192.168.1.%';
#usage表示连接权限

2.3.删除数据库用户

drop user 'test1'@'localhost';  #删除数据库用户命令
select user,host from mysql.user where user='test1';  #删除后检查数据库用户
flush privileges;  #使得处理用户后，对数据库生效，有数据库改动的情况，执行这个命令

delete from mysql.user where user='test2' and host='192.168.1.%';
flush privileges;

2.4.授权数据库用户

2.4.1.创建test3用户，对test库具备所有权限，允许从localhost主机登录管理数据库，密码是123456。

grant all privileges on test.* to 'test3'@'localhost' identified by '123456';
select user,host from mysql.user where user='test3';
show grants for 'test3'@'localhost';
#all privileges就是授权的权限

2.4.2.授权与root同等地位的system用户权限。

show grants for root@localhost;  #查看root用户的权限
grant all on *.* to 'system'@'localhost' identified by '123456' with grant option;
grant proxy on ''@'' to 'system'@'localhost' with grant option;  #允许创建代理用户
show grants for system@localhost;

2.5.授权的权限列表

show grants for 'test3'localhost';
#all privileges就是授权的权限
revoke select on test.* from 'test3'@'localhost';
show grants for 'test'@'localhost';
#权限all被拆分成了更细的权限

mysql的all privileges的权限列表

权限	说明
select	查询（数据）
insert	插入（数据）
update	修改（数据）
delete	删除（数据）
create	创建（数据库、表等对象）
drop	删除（数据库、表等对象）
reload	重载
shutdown	关闭
process	进程
file	文件
references	参考资料
index	索引
alter	修改（数据库、表等对象）
show databases	查看数据库
super	超级权限
create temporary tables	创建临时表
lock tables	锁表
execute	执行
replication slave	从复制权限
replication client	从客户端复制
create view	创建视图
show view	查看视图
create routine	创建存储过程
alter routine	修改存储过程
create user	创建用户
event	事件
trigger	触发器
create tablespace	创建表空间

2.6.企业中grant授权权限问题说明

2.6.1.企业里主数据库用户的授权问题说明

在企业生产环境中，如果是以web形式连接数据库的用户，那么尽量不要授予all权限，最好是分拆授权，比如，授予select、insert、update、delete等适合web使用的dml语句关键字权限。

grant select,insert,update,delete on oldboy.* to test3@'172.16.1.%' identified by '123456';

#注意：授权用户权限时有如下3条安全红线不要轻易跨过。
1、权限不能用all，要应用select、insert、update、delete等具体权限。
2、库不能用"*.*"，而应用"oldboy.*"格式具体到库。
3、主机不能用%，而应用内网ip段，即'192.168.0.%'格式。

php程序语言连接mysql的代码：

<?php
  //$link_id=mysql_connect('数据库主机名','用户','密码')；
    $link_id=mysql_connect('172.16.1.7')
    if($link_id){
        echo "mysql successful by oldboy !";
    }else{
        echo mysql_error();
    }
?>