mysql随笔系列-1
MySQL数据库管理
本人实验所用的MySQL数据库版本：5.5.56-MariaDB MariaDB Server 操作系统：centos7.5
1.创建数据库
MariaDB [(none)]> create database test1;
2.查看所有数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| test1 |
+--------------------+
5 rows in set (0.00 sec)
3.创建指定字符集数据库
查看库的字符集和校对规则：
MariaDB [(none)]> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
...................................................................略
实例：创建一个库，指定字符集为utf8
MariaDB [(none)]> create database test2_utf8 character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show create database test2_utf8;
+------------+---------------------------------------------------------------------+
| Database | Create Database |
+------------+---------------------------------------------------------------------+
| test2_utf8 | CREATE DATABASE `test2_utf8` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+------------+---------------------------------------------------------------------+
1 row in set (0.00 sec)
4.删除数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| test1 |
| test2_utf8 |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]> drop database test1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| test2_utf8 |
+--------------------+
5 rows in set (0.01 sec)
上述所示：删除数据库test1成功！
5.进入数据库中指定的库
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| test2_utf8 |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> use test2_utf8;
Database changed
MariaDB [test2_utf8]> select database();
+------------+
| database() |
+------------+
| test2_utf8 |
+------------+
1 row in set (0.00 sec)
6.查看进入当前数据库的用户
MariaDB [test2_utf8]> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
7.删除数据库中的账号
语法：drop user ‘user’@’主机域0 ’ <--注意引号，可以是单或双引号，但是不能不加。
MariaDB [mysql]> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)
MariaDB [mysql]> select user,host from mysql.user;
+------+-----------------------+
| user | host |
+------+-----------------------+
| root | 127.0.0.1 |
| root | ::1 |
| | localhost |
| root | localhost |
| | localhost.localdomain |
| root | localhost.localdomain |
+------+-----------------------+
6 rows in set (0.00 sec)
MariaDB [mysql]> drop user 'root'@'::1';
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> select user,host from mysql.user;
+------+-----------------------+
| user | host |
+------+-----------------------+
| root | 127.0.0.1 |
| | localhost |
| root | localhost |
| | localhost.localdomain |
| root | localhost.localdomain |
+------+-----------------------+
5 rows in set (0.00 sec)
8.查看数据库的用户
MariaDB [mysql]> select user,host from mysql.user;
+------+-----------------------+
| user | host |
+------+-----------------------+
| root | 127.0.0.1 |
| | localhost |
| root | localhost |
| | localhost.localdomain |
| root | localhost.localdomain |
+------+-----------------------+
5 rows in set (0.00 sec)
