一、库操作
1. 创建数据库
CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [,
create_specification] ...]
create_specification:
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name
create database表示为 固定用法db_name表示为数据库名称[ IF NOT EXISTS ]表示可选项CHARACTER SET: 指定数据库采用的字符集COLLATE: 指定数据库字符集的校验规则
💕 创建数据库
create database if not exists test1;
IF NOT EXISTS 表示：当前数据库不存在 test1，则创建数据库。
如果已经存在同名的数据库，则会报警，并且不会创建一个同名的数据库。
如果我们想要删除一个数据库，直接输入 drop database 数据库名，当我们删除一个数据库后，该数据库对应的目录也会被删除，若该目录下有表存在，则也会被直接删除。因此不建议随意删除数据库。
创建数据库的本质就是在 /var/lib/mysql 路径下创建一个目录，删除数据库的本质就是在 /var/lib/mysql 下删除一个目录。
2. 数据库的编码问题
创建数据库时，有两个编码集：数据库的字符集 和 数据库的校验集
数据库的字符集 —— 数据库存数据时，采用什么编码
如果没有对MySQL的配置文件进行过修改，则默认的编码格式时utf8，默认的校验规则是utf8_general_ci。
数据库的校验集 —— 支持数据库，进行字段比较使用的编码，本质是读取数据库中数据的采用的编码格式 ( 查找或匹配数据库，就需要进行比较字段，想要被比较，两者就需要先被读出来，此时两者的校验规则应该相同)
查看系统默认支持的字符集
💕 系统默认支持的字符集
show variables like 'character_set_database';
通过查看MySQL系统变量variables 当中的 character_set_database ，可以看到系统默认的字符集。
💕 系统默认支持的字符集校验规则
show variables like 'collation_database';
通过查看MySQL系统变量variables当中的collation_database，可以得知系统默认的字符集校验规则。
查看数据库支持的字符集
show charset;
字符集主要是控制用什么语言，例如：utf8 就可以使用中文。
查看数据库支持的字符集校验规则
show collation;
校验规则对数据库的影响
💕 字符集编码格式和字符集校验规则的区别
- 字符集编码格式指的是在存储数据时各个字符的底层编码，用于指定数据的存储格式。
- 字符集校验规则是在字符集内用于比较字符的一套规则，用于对数据进行对比。
例如我们在存储数据时是按照utf8的格式进行存储的，那么将来在对数据进行做对比时也必须按照utf8的格式进行对比，因为 “存数据” 和 “取数据” 的方式必须保持一致。
💕 校验规则对数据库的影响
字符集编码格式和校验规则必须是对应的，但实际上同一种字符集编码格式一般会有多种可选择的校验规则，比如 utf8编码格式的校验规则有 utf8_general_ci、utf8_bin、utf8_unicode_ci等校验规则。使用不同的校验规则操作数据库中的数据可能会得到不同的结果。例如：utf8_general_ci校验规则在进行比对数据时是不区分大小写的，而utf8_bin校验规则在对比数据时是区分大小写的。
3. 操纵数据库
查看数据库
使用show databases SQL可以查看系统中所有的数据库。
show databases;
显示创建语句
使用 show create database 数据库名 SQL 可以查看对应数据库的创建语句。
show create database test1;
/*!40100 DEFAULT CHARACTER SET utf8 */ 不是表示注释，而是表示当前MySQL的版本如果大于 4.10，则执行后面的SQL语句。
修改数据库
ALTER DATABASE db_name [[DEFAULT] CHARSET=character_name] [[DEFAULT] COLLATE=collation_name];
这里的修改数据库指的是修改数据库的字符集或校验规则。
删除数据库
DROP DATABASE [IF EXISTS] db_name;
删除数据库后该数据库对应的文件夹就被删除了。
这里需要说明的是，删除数据库后，该数据库下的所有表也会都被级联删除，因此不要随便删除数据库。
4. 数据库的备份和恢复
数据库的备份
首先，我们先创建一个数据库并且向其中插入一张表。
对指定数据库进行备份的命令：
mysqldump -P 端口号 -u 用户名 -p 密码 -B 数据库名1 数据库名2 ... > 数据库备份存储的文件路径
打开test.sql，我们可以发现文件中的内容就是我们在该数据库中执行的各种SQL命令，包括创建数据库、创建表、插入数据等SQL语句。
数据库的恢复
恢复数据库的SQL语句：
source 数据库备份存储的文件路径
先将指定数据库删除：
恢复数据库：
source /root/MySQL/test.sql
这里我们可以看到数据库已经被恢复出来了。
表的删除与恢复
💕 表备份
mysqldump -P3306 -u root -p Test student > /root/MySQL/table.sql
将表删除掉：
💕 表恢复
source /root/MySQL/table.sql;
这里我们看到表已经被完全恢复出来了。
5. 查看连接情况
使用 show processlist SQL来查看当前连接MySQL的用户。
show processlist;
- Id列：一个标识，可以在MySQL中通过kill id杀死指定id的线程。
- User列：显示当前用户，如果不是root，这个命令就只显示你权限范围内的SQL语句。
- Host列：显示这个语句是从哪个IP的哪个端口上发出的，可用来追踪出问题语句的用户。
- db列：当前执行的命令是在哪一个数据库上，如果没有指定数据库，则该值为NULL。
- Command列：显示当前连接执行的命令，一般就是休眠（Sleep）、查询（Query）和连接（Connect）。
- Time列：表示该线程处于当前状态的时间，单位是秒。\nState列：显示使用当前连接的SQL语句的状态。
- Info列：一般记录的是线程执行的语句，默认只显示前100个字符，如果要看全部信息，需要使用show full processlist。
show processlist可以告诉我们当前有哪些用户连接到我们的MySQL，如果查出某个用户不是你正常登录的，那么很有可能你的数据库被人入侵了，以后如果发现自己的数据库比较慢时，可以用这个SQL来查看数据库连接情况。
二、表操作
表操作至少需要会下面两类的SQL语句：
- DDL（Data Definition Language）数据定义语言：比如建表、删表、该表、新增列、删除列等。
- DML（Data Manipulation Language）数据操作语言：比如插入记录、删除记录、修改记录等。
创建表
创建表的SQL语句如下：
CREATE TABLE [IF NOT EXISTS] table_name(
field1 datatype1 [COMMENT '注释信息'],
field2 datatype2 [COMMENT '注释信息'],
field3 datatype3 [COMMENT '注释信息']
)[CHARSET=charset_name] [COLLATE=collation_name] [ENGINE=engine_name];
- SQL中大写的表示关键字，[ ]中代表的是可选项。
- field表示列名，datatype表示列的类型。
- CHARSET用于指定表所采用的编码格式，如果没有指定则以所在数据库的编码格式为准。
- COLLATE用于指定表所采用的校验规则，如果没有指定则以所在数据库的校验规则为准。
- ENGINE用于指定表所采用的存储引擎。
- COMMENT用于对指定列添加注释信息。
这里我们需要注意的是：MySQL默认使用的是InnoDB存储引擎，如果没有指定使用哪种存储引擎那么就会默认使用InnoDB存储引擎。
💕 创建表
查看表结构
使用 desc 表名 SQL可以查看表的结构。
desc user;
- Field表示该字段的名字。
- Type表示该字段的类型。
- Null表示该字段是否允许为空。
- Key表示索引类型，比如主键索引为PRI。
- Default表示该字段的默认值。
- Extra表示该字段的额外信息说明。
如果想要查看创建表时的相关细节，可以使用 show create table 表名SQL。如下：
show create table user\G
修改表
修改表的 SQL如下：
ALTER TABLE table_name ADD 新增列名 新增列的属性;
ALTER TABLE table_name MODIFY 列名 修改后的列属性;
ALTER TABLE table_name DROP 列名;
ALTER TABLE table_name RENAME [TO] 新表名;
ALTER TABLE table_name CHANGE 列名 新列名 新列属性;
先看一下表中原有的信息：
💕 新增列
alter table user add path varchar(128) comment '照片路径' after password;
新增一列SQL语句中的after表示将该列新增到哪一列之后，如果想要将新增列放在第一列，可以将after及其之后的语句换成 not null first。
通过desc命令查看确认新增成功。
这时我们插入一条用户信息后查看表中的信息就一目了然了。
💕 修改列类型
将user表中的password列的类型由char(30)改成char(50)。如下：
alter table user modify password char(50) comment '用户密码';
这里需要注意的是，如果需要修列类型后仍然保留comment字段，需要在修改时重新指定comment字段。
💕 修改列名
将user表中的password列的列明改成passwd。如下：
alter table user change password passwd char(50) comment '用户密码';
💕 修改表名
将表user的名字改成People
alter table user rename People;
💕 删除列
将user表中的birthday列删除。
alter table user drop birthday;
删除列后，该列所对应的内容全部都没有了。
删除表
DROP [TEMPORARY] TABLE [IF EXISTS] table_name;
- 在创建表语句中加上TEMPORARY关键字，那么服务器将创建出一个临时表，该表会在你与服务器的会话终止时自动消失。
- TEMPORARY表的名字可以与某个已有的永久表相同，当有TEMPORARY表存在时，对应的永久表会隐藏起来（即无法访问）。
- 为了避免重新连接后（TEMPORARY已经不存在），在未做检测的情况下调用DROP误删了对应永久表，因此在使用DROP删除临时表时需要带上TEMPORARY关键字。
删除掉user表如下：
