常用管理SQL语句之库操作

1.创建数据库

create database db_name;
show create database db_name\G  #\G是为了调整显示的格式，查看建表语句

2.创建utf8或者gbk字符集数据库

create database db_name DEFUALT CHARACTER SET gbk COLLATE gbk_chinese_ci;
create database db_name DEFUALT CHARACTER SET utf8 COLLATE utf8_general_ci;

3.显示数据库

show databases;
show databases like 'db_name';
show databases like 'db_name%'; #%为通配符，即匹配db_name开头的所有数据库

4.切换数据库

use dn_name;
select database(); #查看当前所在的数据库
show variables like "character_set_database"; #查看数据库字符集
show variables like "collation_database";

5.查看数据库表信息

show tables;
show tables from dn_name; #在库外面查看表信息

7.查看数据库用户列表

select user,host from mysql.user;

8.创建数据库用户

create user '用户'@'主机' identified by '密码';
select user,host from mysql.user where user='123';

9.删除数据库用户

drop user '用户'@'主机';
delete from mysql.user where user='用户' and host='主机';

10.授权数据库用户

grant all privileges on db_name.* to 'username'@'hostname' identified by 'passwd';
show grants for '用户'@'主机'; #查看用户对应的权限