登录MySQL数据库
格式:mysql -u 用户名 -h 主机名或IP地址 -P 端口号 -p 密码
1、显示数据库
SHOW DATABASES;
MySQL5.7.20默认数据库
information_schema
mysql #必须存在的,用于描述用户访问权限;
performance_schema
sys
2、创建数据库
CREATE DATABASE 数据库实例名;
1 实战演练如下:
2 1)#utf8编码,创建一个名为:cuixiaozhao的MySQL数据库实例。
3 mysql> CREATE DATABASE cuixiaozhao DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
4 Query OK, 1 row affected (0.01 sec)
5 mysql> SHOW CREATE DATABASE cuixiaozhao;
6 +-------------+----------------------------------------------------------------------+
7 | Database | Create Database |
8 +-------------+----------------------------------------------------------------------+
9 | cuixiaozhao | CREATE DATABASE `cuixiaozhao` /*!40100 DEFAULT CHARACTER SET utf8 */ |
10 +-------------+----------------------------------------------------------------------+
11 row in set (0.00 sec)
12 mysql>
13 2)#gbk编码,创建一个名为:cuixiaosi的MySQL数据库实例。
14 mysql> CREATE DATABASE cuixiaosi DEFAULT CHARSET gbk COLLATE gbk_chinese_ci;
15 Query OK, 1 row affected (0.00 sec)
16 mysql> SHOW CREATE DATABASE cuixiaosi;
17 +-----------+-------------------------------------------------------------------+
18 | Database | Create Database |
19 +-----------+-------------------------------------------------------------------+
20 | cuixiaosi | CREATE DATABASE `cuixiaosi` /*!40100 DEFAULT CHARACTER SET gbk */ |
21 +-----------+-------------------------------------------------------------------+
22 row in set (0.00 sec)
23 mysql>
View Code
默认编码格式为:latin1,不推荐使用,建议使用如下utf8编码方式进行创建!
#utf8编码
CREATE DATABASE 数据库实例名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
#gbk编码
CREATE DATABASE 数据库实例名 DEFAULT CHARSET gbk COLLATE gbk_chinese_ci;
3、声明查看数据的定义
SHOW CREATE DATABASE 数据库实例名;
1 举例如下:2
3 mysql> SHOW CREATE DATABASE cuixiaozhao;#查看创建好的数据库实例-cuixiaozhao的定义。4 +-------------+----------------------------------------------------------------------+
5 | Database | Create Database |
6 +-------------+----------------------------------------------------------------------+
7 | cuixiaozhao | CREATE DATABASE `cuixiaozhao` /*!40100 DEFAULT CHARACTER SET utf8*/ |
8 +-------------+----------------------------------------------------------------------+
9 1 row in set (0.00sec)10
11 mysql> SHOW CREATE DATABASE cuixiaosi;#查看创建好的数据库实例-cuixiaosi的定义。12 +-----------+-------------------------------------------------------------------+
13 | Database | Create Database |
14 +-----------+-------------------------------------------------------------------+
15 | cuixiaosi | CREATE DATABASE `cuixiaosi` /*!40100 DEFAULT CHARACTER SET gbk*/ |
16 +-----------+-------------------------------------------------------------------+
17 1 row in set (0.00sec)18
19 mysql>
View Code
4、使用数据库实例
USE 数据库实例名;
1 实战演练如下:2 mysql> USEcuixiaozhao;#使用数据库实例cuixiaozhao;3 Databasechanged4 mysql>
View Code
5、删除数据库
DROP 数据库实例名;
1 实战演练如下:2 mysql> DROP DATABASEcuixiaosi;3 Query OK, 0 rows affected (0.00sec)4
5 mysql>SHOW DATABASES;6 +--------------------+
7 | Database |
8 +--------------------+
9 | information_schema |
10 | cuixiaozhao |
11 | mysql |
12 | performance_schema |
13 | sys |
14 +--------------------+
15 5 rows in set (0.00sec)16
17 mysql>
18
19 注意:使用DROP DATABASE命令时要非常谨慎,MySQL不会给予任务提示确认信息,DROP DATABASE声明删除数据库后,数据库中存储的所有数据表和数据也将一并被删除且不可恢复。
View Code
6、MySQL用户管理及授权
创建用户
CREATE USER [email protected] IDENTIFIED BY ‘密码‘;
修改用户
修改密码
SET PASSWORD FOR [email protected] = PASSWORD(‘新密码‘);
删除用户
1 实战演练如下:2 mysql> CREATE USER ‘cuixiaozhao‘@‘localhost‘ IDENTIFIED BY ‘Ab123456!@#$‘;3
4 Query OK, 0 rows affected (0.00sec)5
6 mysql> RENAME USER ‘cuixiaozhao‘@‘localhost‘ TO ‘zhaoxiaocui‘@‘localhost‘;7 Query OK, 0 rows affected (0.00sec)8
9 mysql> SET PASSWORD FOR ‘zhaoxiaocui‘@‘localhost‘ = PASSWORD(‘$#@!654321bA‘);10 Query OK, 0 rows affected, 1 warning (0.00sec)11
12 mysql> DROP USER ‘zhaoxiaocui‘@‘localhost‘;13 Query OK, 0 rows affected (0.00sec)14
15 mysql>
View Code
查看权限
授权
GRANT ALL ON *.* TO [email protected];
撤销授权
REVOKE ALL ON *.* FROM [email protected];
1 实战演练如下:2
3 mysql> CREATE USER ‘cuixiaozhao‘@‘localhost‘ IDENTIFIED BY ‘Ab123456!@#$‘;4
5 Query OK, 0 rows affected (0.00sec)6
7 mysql> SHOW GRANTS FOR ‘cuixiaozhao‘@‘localhost‘;8 +-------------------------------------------------+
9 | Grants for cuixiaozhao@localhost |
10 +-------------------------------------------------+
11 | GRANT USAGE ON *.* TO ‘cuixiaozhao‘@‘localhost‘ |
12 +-------------------------------------------------+
13 1 row in set (0.00sec)14
15 mysql> GRANT ALL ON *.* TO ‘cuixiaozhao‘@‘localhost‘;16 Query OK, 0 rows affected (0.00sec)17
18 mysql> SHOW GRANTS FOR ‘cuixiaozhao‘@‘localhost‘;19 +----------------------------------------------------------+
20 | Grants for cuixiaozhao@localhost |
21 +----------------------------------------------------------+
22 | GRANT ALL PRIVILEGES ON *.* TO ‘cuixiaozhao‘@‘localhost‘ |
23 +----------------------------------------------------------+
24 1 row in set (0.00sec)25
26 mysql> REVOKE ALL ON *.* FROM ‘cuixiaozhao‘@‘localhost‘;27 Query OK, 0 rows affected (0.00sec)28
29 mysql> SHOW GRANTS FOR ‘cuixiaozhao‘@‘localhost‘;30 +-------------------------------------------------+
31 | Grants for cuixiaozhao@localhost |
32 +-------------------------------------------------+
33 | GRANT USAGE ON *.* TO ‘cuixiaozhao‘@‘localhost‘ |
34 +-------------------------------------------------+
35 1 row in set (0.00sec)36
37 mysql>
View Code
all privileges 除grant外的所有权限;
select 仅查权限;
select,insert 查和插入权限;
usage 无访问权限;
alter 使用alter table;
alter routine 使用alter procedure和drop procedure;
create 使用create table;
create routine 使用create procedure;
create temporary tables 使用create temporary tables;
create user 使用create user、drop
user、rename user和revoke all
privileges;
create view 使用create view;
delete 使用delete;
drop 使用drop table;
execute 使用call和存储过程;
file 使用select into outfile 和
load data infile;
grant option 使用grant 和 revoke;
index 使用index;
insert 使用insert;
lock tables 使用lock table;
process 使用show full processlist;
select 使用select;
show databases 使用show databases;
show view 使用show view;
update 使用update;
reload 使用flush;
shutdown 使用mysqladmin shutdown(关闭MySQL);
super 使用change master、kill、logs、purge、master和set global,还允许mysqladmin调试登录;
replication client 服务器位置的访问;
replication slave 主从复制时使用;
7、其他项目说明
1、对于目标数据库以及内部其他:
数据库名.* #指定数据库中的所有表
数据库名.表 #指定数据库中的某张表
数据库名.存储过程 #指定数据库中的存储过程
*.* #所有数据库中的所有表
2、用户名及IP地址
[email protected] #用户只能在该IP下才能访问
[email protected]%‘ #用户只能在该IP段下才能访问(通配符%表示任意)
[email protected]%‘ #用户可以在任意IP下访问(默认IP地址为%)
8、FLUSH PRIVILEGES;#将数据读取到内存中,从而立即生效。