首页 > 代码库 > mysql DCL数据控制语言
mysql DCL数据控制语言
-- 维护性操作 都是在cmd下操作的
连接数据库:
本机:mysql [-h localhost] -u account -p
远程:mysql [-h remote_ip] -u account -p
显示当前所有数据库:show databases;
切换数据库:use db_name;
显示当前数据库下的表:show tables;
授权远程访问:
grant privileges on 数据库.* to 用户名@登录主机 identified by password(pwd_string);
是授权生效:
重启mysql服务
flush privileges;
privileges:
SELECT,INSERT,...
实例:授权 grand select on studymysql.* to root@‘172.19.105.5‘ indentified by ‘123456‘
更新权限 flush privileges 或者 重启mysql set start mysql
退出 mysql:exit
收回访问权限:
revoke privileges on 数据库.* from 用户名@登录主机;
-- 用户管理:
-- 新建用户
create user user_name identified by ‘password_value‘;
-- 修改用户
rename user user_name to new_user_name;
set password for user_name = password(‘new_password‘);
-- 删除用户
drop user user_name[,user_name2,...];
-- 查看用户
select user,host from mysql.user;
-- 查看在线用户
SELECT SUBSTRING_INDEX(host, ‘:‘, 1) AS host_short,
GROUP_CONCAT(DISTINCT user) AS users,
COUNT(*) AS threads
FROM information_schema.processlist
GROUP BY host_short
ORDER BY COUNT(*), host_short;
-- 创建数据库
create database db_name [default character set utf8 collate utf8_general_ci];
实例:create database school default character set utf8 collate utf8_general_ci;
-- 删除数据库
drop database db_name;
实例:drop database school;
-- 备份数据库
mysqldump -u user_name -p db_name > backupfile
mysqldump -u user_name -p db_name [table_name]> backupfile
实例:mysqldump - u root -p employees > employees.sql
-- 还原数据库
mysql -u root -p
确定要还原的数据库是否存在,不存在则创建
切换到要还原的数据库上
执行还原操作:source backup.sql
实例:1.mysql -u root -p
2.show databases
3.creat databases employees default character set utf8
4.user employees
5.source employees.sql
-- 程序设计
mysql DCL数据控制语言