SQL语句（数据库级别的命令集合）

 数据库内部存储结构

登录数据库

登录数据库
mysql -h服务器地址 -u用户名 -p密码(不安全的登录形式)

退出数据库
exit

以一种相对安全的方式进行登录
mysql -h服务器地址 -u用户名 -p回车+密码

数据库用户相关的操作 

# 查看用户
### mysql.user mysql数据库中user表示系统内置用于存放用户的

select User,Password,Host from mysql.user; 

结果展示：
+------+----------+-----------+
| User | Password | Host      |
+------+----------+-----------+
| root |          | localhost |
| root |          | 127.0.0.1 |
| root |          | ::1       |
|      |          | localhost |
+------+----------+-----------+
4 rows in set (0.00 sec)


-----------------------------------------------------------------------------------------


#修改用户密码

set password for username@'localhost' = password('newpassword');

结果展示：密码是加过密的
+------+-------------------------------------------+
| user | password                                  |
+------+-------------------------------------------+
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root |                                           |
| root |                                           |
|      |                                           |
+------+-------------------------------------------+
4 rows in set (0.00 sec)


-----------------------------------------------------------------------------------------


#创建用户

create user "username"@"localhost" identified by "password"

结果展示：
+-------+-------------------------------------------+-----------+
| user  | password                                  | host      |
+-------+-------------------------------------------+-----------+
| root  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost |
| root  |                                           | 127.0.0.1 |
| root  |                                           | ::1       |
|       |                                           | localhost |
| yiqi  |                                           | localhost |
| yiqi2 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost |
+-------+-------------------------------------------+-----------+
6 rows in set (0.00 sec)


-----------------------------------------------------------------------------------------


#删除用户

delete from mysql.user where user="yiqi" and host="localhost";

结果展示：
+-------+-------------------------------------------+-----------+
| user  | password                                  | host      |
+-------+-------------------------------------------+-----------+
| root  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost |
| root  |                                           | 127.0.0.1 |
| root  |                                           | ::1       |
|       |                                           | localhost |
| yiqi2 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost |
+-------+-------------------------------------------+-----------+
5 rows in set (0.00 sec)


-----------------------------------------------------------------------------------------


#权限分配

grant all privileges on *.* to "yiqi2"@"localhost";
flush privileges;

结果展示： 你就只看是不是都由 N 变成了 Y
+-----------+-------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| Host      | User  | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |
+-----------+-------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| localhost | yiqi2 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | N          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        | NULL                  |
+-----------+-------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+

数据库基本操作

# 查询所有的数据库
show databases; #这里面一定要注意，这里有s

结果展示： 这四个数据库是安装后自动创建的
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)


-----------------------------------------------------------------------------------------


# 创建数据库
create database 数据库的名称 [编码](可有可无，如果没有的话，就是这个服务器的默认编码)

效果展示：
### 未加编码的
mysql> create database wustyq;  ### 千万不要加引号
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| wustyq             |
+--------------------+
5 rows in set (0.00 sec)

### 加编码的
mysql> create database my_database character set gbk;
Query OK, 1 row affected (0.00 sec)

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

-----------------------------------------------------------------------------------------


# 查看当前数据库的编码
show create database 数据名称

结果展示：默认和安装时勾选的编码一致
mysql> show create database wustyq;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| wustyq   | CREATE DATABASE `wustyq` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)


-----------------------------------------------------------------------------------------


# 修改当前数据库的编码
alter database wustyq character set 编码形式 

效果展示：
mysql> alter database wustyq character set utf8;
mysql> show create database wustyq;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| wustyq   | CREATE DATABASE `wustyq` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)


-----------------------------------------------------------------------------------------


# 删除数据库
drop database 数据库名称

效果展示：
mysql> drop database wustyq;
Query OK, 0 rows affected (0.09 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| my_database        |
| my_db              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)


-----------------------------------------------------------------------------------------


# 修改数据库名称
rename database 旧的数据库名称 to 新的数据库名称（5.1.23之后被废弃了）
暴力方法：
首先，我们退出 mysql 然后 找到我们数据库安装目录的 data 下 找到我们的数据库 修改文件夹名称即可

效果展示：暴力修改的结果
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| my                 |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)


-----------------------------------------------------------------------------------------


# 切换数据库
use 数据库名称

效果展示：
mysql> use my;
Database changed


-----------------------------------------------------------------------------------------


# 查看当前使用的数据库
select database();

效果展示：
mysql> select database();
+------------+
| database() |
+------------+
| my         |
+------------+
1 row in set (0.00 sec)


-----------------------------------------------------------------------------------------