MySQL：库和表的操作

一. 查看数据库

show databases;//查看数据库
show create database 数据库名；//显示创建语句

示例：

说明：

MySQL 建议我们关键字使用大写，但是不是必须的。

数据库名字的反引号``,是为了防止使用的数据库名刚好是关键字。

/*!40100 default.... */ 这个不是注释，表示当前mysql版本大于4.01版本，就执行这句话。

二. 创建数据库

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, 
create_specification] ...]
create_specification:
 [DEFAULT] CHARACTER SET charset_name
 [DEFAULT] COLLATE collation_name

说明：

• 大写的表示关键字
• [] 是可选项
• CHARACTER SET: 指定数据库采用的字符集
• COLLATE: 指定数据库字符集的校验规则

案例：创建名为sys的数据库：

create database sys;

创建数据库对于Linux而言，就相当于创建了一个目录，在我的ubuntu主机下，这个数据库存储在了/var/lib/mysql目录下：

三. 字符集和校验规则

在MySQL中，数据库有两种字符编码集合：

字符集：字符存储时，采用的编码方式。

校验集：字符读取时，采用的编码方式。

查看系统支持的字符集：

show charset;

查看系统所支持的校验集：

show collation;

注意：字符集和编码集的名字示平台而定会有差异。

创建数据库，指定字符集，下面两种都可以：

create database [if not exists] 数据库名 charset=xxx;
create database [if not exists] 数据库名 character set xxx;

创建数据库，指定校验集：

create database [if not exists] 数据库名 collate xxx;

示例：

该指令创建了数据库db_1，并指定字符集为utf8，校验集为utf8mb3_general_ci。

如果创建的时候不指定字符集和校验集，此时会使用系统默认的。

查看系统默认字符集：

show variables like 'character_set_database';

查看系统默认校验集：

show variables like 'collation_database';

四. 修改和删除数据库

4.1 数据库修改

ALTER DATABASE db_name
[alter_spacification [,alter_spacification]...]
alter_spacification:
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name

对数据库的修改主要指的是修改数据库的字符集、校验规则。

例子：将 mytest 数据库字符集改成 gbk。

mysql> alter database mytest charset=gbk;
Query OK, 1 row affected (0.00 sec)
mysql> show create database mytest;
+----------+----------------------------------------------------------------+
| Database | Create Database                                               |
+----------+----------------------------------------------------------------+
| mytest   | CREATE DATABASE `mytest` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+----------------------------------------------------------------+

**但是请记住：尽量在库创建之前想好要干嘛，不要在后面有很多数据的时候去修改，会很麻烦。 **

4.2 数据库删除

DROP DATABASE [IF EXISTS] db_ name;

执行删除之后的结果:

数据库内部看不到对应的数据库。

对应的数据库文件夹被删除，级联删除，里面的数据表全部被删。

五. 备份与恢复

5.1 备份

语法（该指令要在bash中执行，而不是mysql处）：

# mysqldump -P3306 -u root -p 密码 -B 数据库名 > 数据库备份存储的文件路径

需要注意的是：备份的文件路径需要提前创建

例如：

mysqldump -p -B test_db > ./test/test_db.sql

将test_db这个表备份到路径下。

可以发现：**.sql文件并不是简单的拷贝一份数据库，而是保留了整个数据库的SQL语句。**

5.2 还原

mysql> source D:/mysql-5.7.22/mytest.sql;

5.3 注意事项

如果备份的不是整个数据库，而是其中的一张表，怎么做？

# mysqldump -u root -p 数据库名 表名1 表名2 > D:/mytest.sql

同时备份多个数据库：

# mysqldump -u root -p -B 数据库名1 数据库名2 ... > 数据库存放路径

如果备份一个数据库时，没有带上-B参数， 在恢复数据库时，需要先创建空数据库，然后使用数据库，再使用source来还原。

5.4 查看连接情况

show processlist;

可以告诉我们当前有哪些用户连接到我们的MySQL，如果查出某个用户不是你正常登陆的，很有可能你的数据库被人入侵了。以后大家发现自己数据库比较慢时，可以用这个指令来查看数据库连接情况。

六. 创建表

语法：

CREATE TABLE table_name (
 field1 datatype,
 field2 datatype,
 field3 datatype
) character set 字符集 collate 校验规则 engine 存储引擎;

说明：

• field 表示列名
• datatype 表示列的类型
• character set 字符集，如果没有指定字符集，则以所在数据库的字符集为准
• collate 校验规则，如果没有指定校验规则，则以所在数据库的校验规则为准

例子：

create  table users (
   id int,
   name varchar(20),
   password char(32),
   birthday date
) character set utf8 engine MyISAM;

不同的存储引擎，创建表的文件不一样。

users 表存储引擎是 MyISAM ，在数据目中有三个不同的文件，分别是：

• users.frm：表结构
• users.MYD：表数据
• users.MYI：表索引

而如果使用InnoDB引擎，则只会生成.frm和.ibd后缀的文件。

七. 查看表结构

desc 表名;

八. 修改表

ALTER TABLE tablename ADD (column datatype [DEFAULT expr][,column 
datatype]...);
ALTER TABLE tablename MODIfy (column datatype [DEFAULT expr][,column 
datatype]...);
ALTER TABLE tablename DROP (column);

**这里也要提醒：在创建表之前就把表的基本框架想好，不要后续表中有很多数据的时候去修改，是很麻烦的。 **

修改name，将其长度改为60

删除passwd列

注意：删除字段一定要小心，删除字段及其对应的列数据都没了。

修改表名为employee（下面的to可以省掉）

将name列修改为xingming

九. 删除表

语法;

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...

例子：

drop table t1;

总结：

好了，到这里今天的知识就讲完了，大家有错误一点要在评论指出，我怕我一人搁这瞎bb，没人告诉我错误就寄了。

祝大家越来越好，不用关注我（疯狂暗示）