mysql字符集,显示,连接,删除,创建用户,授权等

show create database mysql\G
*************************** 1. row ***************************
Database: mysql
Create Database: CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)

note:如果编译时不指定字符集,默认就是拉丁

若指定了,则以后创建对应字符集数据库就无需额外指定了。

数据库要支持创建库的字符集,例如
-DEXTRA_CHARSETS=gbk, gb23l2, utf8, ascii

 

创建一个GBK数据库:

mysql>create database yjz DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
Query OK, 1 row affected (0.00 sec)

##CHARACTER SET 指定一个字符集,或者是个校对的规则。字符集是一套规则,编码,校对规则是比较字符编码的方式。

mysql>show create database yjz\G
*************************** 1. row ***************************
Database: yjz
Create Database: CREATE DATABASE `yjz` /*!40100 DEFAULT CHARACTER SET gbk */
1 row in set (0.00 sec)

##\G看起来效果更好

 

创建一个utf8的数据库:

mysql> create database yjz1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> show create database yjz1;
+———-+—————————————————————+
| Database | Create Database |
+———-+—————————————————————+
| yjz1 | CREATE DATABASE `yjz1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+———-+—————————————————————+
1 row in set (0.00 sec)

note:字符集不一致时数据库中文内容乱码的罪魁祸首。

 

显示某个数据库:

mysql> show databases like ‘%yj%’;
+—————–+
| Database (%yj%) |
+—————–+
| yjz |
| yjz1 |
+—————–+

(% 为匹配所有内容)

 

显示当前数据库:

mysql> use yjz       #use为连接数据库
Database changed
mysql> select database();
+————+
| database() |
+————+
| yjz |
+————+

 

mysql> show tables;   #进到库里之后
Empty set (0.00 sec)

mysql> show tables like ‘user’
-> ;
Empty set (0.00 sec)

mysql> show tables from yjz;
Empty set (0.00 sec)

mysql> show tables in yjz;
Empty set (0.00 sec)

 

查看版本,用户,时间:

mysql> select version();
+———–+
| version() |
+———–+
| 5.5.32 |
+———–+
1 row in set (0.00 sec)

mysql> select user();
+—————-+
| user() |
+—————-+
| root@localhost |
+—————-+
1 row in set (0.00 sec)

mysql> select now();
+———————+
| now() |
+———————+
| 2018-09-09 08:56:06 |
+———————+
1 row in set (0.01 sec)

 

 

删除数据库:

mysql> drop database yjz1;
Query OK, 0 rows affected (0.24 sec)

 

删除多余用户:

 

创建用户,授权:

创建yjz用户,对test库具备所有权限,允许从localhost主机登陆管理数据库,密码为111

mysql> create database test1;
Query OK, 1 row affected (0.00 sec)

mysql> grant all privileges on test.* to ‘yjz’@’localhost’ identified by ‘111’;
Query OK, 0 rows affected (0.06 sec)

查看授权后的用户列表

mysql> select user,host from mysql.user;
+——+———–+
| user | host |
+——+———–+
| root | % |
| root | 127.0.0.1 |
| yjz | localhost |
+——+———–+

查看授权用户yjz的具体权限:

mysql> show grants for ‘yjz’@’localhost’;

| Grants for yjz@localhost |

| GRANT USAGE ON *.* TO ‘yjz’@’localhost’ IDENTIFIED BY PASSWORD ‘*832EB84CB764129D05D498ED9CA7E5CE9B8F83EB’ |
| GRANT ALL PRIVILEGES ON `test`.* TO ‘yjz’@’localhost’


收回权限:

取消yjz3的只读权限(SELECT):

mysql> revoke select on test.* from yjz3@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for yjz3@localhost;
+——————————————————————————————————————————————————————————————————————————+
| Grants for yjz3@localhost |
+——————————————————————————————————————————————————————————————————————————+
| GRANT USAGE ON *.* TO ‘yjz3’@’localhost’ IDENTIFIED BY PASSWORD ‘*832EB84CB764129D05D498ED9CA7E5CE9B8F83EB’ |
| GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test`.* TO ‘yjz3’@’localhost’ |
+——————————————————————————————————————————————————————————————————————————+
2 rows in set (0.00 sec)

取消它的所有权限:

mysql> revoke all on test.* from yjz3@localhost;
Query OK, 0 rows affected (0.02 sec)

mysql> show grants for yjz3@localhost;
+————————————————————————————————————-+
| Grants for yjz3@localhost |
+————————————————————————————————————-+
| GRANT USAGE ON *.* TO ‘yjz3’@’localhost’ IDENTIFIED BY PASSWORD ‘*832EB84CB764129D05D498ED9CA7E5CE9B8F83EB’ |
+————————————————————————————————————-+
1 row in set (0.00 sec)


创建个新用户,查看权限:

mysql> create user yjz2@’%’ identified by ‘111’;

mysql> show grants for yjz2@’%’;

| Grants for yjz2@% |
| GRANT USAGE ON *.* TO ‘yjz2’@’%’ IDENTIFIED BY PASSWORD ‘*832EB84CB764129D05D498ED9CA7E5CE9B8F83EB’ |

##USAGE就是没有权限。

 

create和grant配合:

mysql> create user yjz3@localhost identified by ‘111’;
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on test.* to yjz3@localhost;
Query OK, 0 rows affected (0.01 sec)

 

 

 

mysql> create user test@’192.168.43.%’ identified by ‘111’;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

##

[root@52 ~]# mysql -utest -p111
ERROR 1045 (28000): Access denied for user ‘test’@’localhost’ (using password: YES)

此时想要用test用户连接必须:

[root@52 ~]# mysql -utest -p111 -h 192.168.43.52
Welcome to the MySQL monitor. Commands end with ; or \g.

如果还要指明端口用-P

 

 

 


 

查看表结构

mysql> use mysql
Database changed
mysql> show tables;
+—————————+
| Tables_in_mysql |
+—————————+
| columns_priv |
| db

 

mysql> desc db
-> ;
+———————–+—————+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———————–+—————+——+—–+———+——-+
| Host | char(60) | NO | PRI | | |

 

也可以:

mysql> show columns from db;
+———————–+—————+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———————–+—————+——+—–+———+——-+
| Host | char(60) | NO | PRI | | |

 

查看建表的语句:

mysql> show create table db \G
*************************** 1. row ***************************
Table: db
Create Table: CREATE TABLE `db` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT ”,
`Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT ”,

 

 

 

Author: YJZ

发表评论

电子邮件地址不会被公开。