【MySQL】库与表的操作

1. 库的操作

1.1 库的创建

语法：

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：指定数据库字符集的校验规则（用于字段比较的编码，本质就是读取数据库数据时采用的编码格式）

当我们创建数据库没有指定字符集和校验规则时，系统使用默认字符集：utf8，校验规则是：utf8_ ai_ ci

• 创建一个使用utf8字符集的test2数据库

• 创建一个使用utf8字符集，并带校验规则的数据库。

• 查看系统默认的字符集以及校验规则

show variables like 'character_set_database';	//字符集

show variable like 'collation_database';	//校验规则

• 查看系统支持的字符集以及校验规则

show charset;
show collation;

1.2 校验规则对数据库的影响

• 不区分大小写

创建一个数据库，校验规则使用utf8_ general_ ci(不区分大小写)

• 区分大小写

创建一个数据库，校验规则使用utf8_ bin(区分大小写)

• 进行查询

• 进行排序

1.3 查看数据库

1. 查看所有数据库

show database;

2. 显示创建语句

show create database 数据库名；

说明：

• MySQL 建议我们关键字使用大写，但是不是必须的。
• 数据库名字的反引号``,是为了防止使用的数据库名刚好是关键字
• /*!40100 default… */ 这个不是注释，表示当前mysql版本大于4.01版本，就执行这句话

3. 查看当前在哪个数据库中

select database();

1.4 修改数据库

ALTER DATABASE db_name[alter_spacification [,alter_spacification]...]

alter_spacification:
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name

注意：MySQL不直接支持重命名数据库，但你可以通过导出数据、创建新数据库、导入数据的方式来实现；对数据库的修改主要指的是修改数据库的字符集，校验规则

1.5 删除数据库

DROP DATABASE [IF EXISTS] db_ name;

执行删除之后的结果:
数据库内部看不到对应的数据库；对应的数据库文件夹被删除，级联删除，里面的数据表全部被删

1.6 备份与恢复

1. 备份

指令：

mysqldump  [-P 3306] -u root  -p 密码 -B 数据库名 > 备份到哪里

-B：指定该选项，会有创建数据库的信息；若不指定，恢复时要先自己创建数据库，然后使用数据库，再使用source来还原

test2backup.sql 文件里的内容，其实把我们整个创建数据库、创建表、导入数据的语句都装载这个文件中。

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

mysqldump -u root -p 数据库名 表名1 表名2 > 要备份到哪个文件

• 同时备份多个数据库

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

2. 还原

source + 备份的文件的路径；

1.7 查看数据库连接情况

show processlist;

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

2. 表的操作

2.1 创建表

create table 表名(
	列名	  类型 [comment '描述'],
	列名	  类型 [comment '描述'],
	列名	  类型 [comment '描述']
)[character set 字符集 collate 校验规则 engine 存储引擎];	//分号不能少

说明：
[ ] 为可选项
character set 字符集，如果没有指定字符集，则以所在数据库的字符集为准
collate 校验规则，如果没有指定校验规则，则以所在数据库的校验规则为准

存储引擎的不同，会造成磁盘文件的种类、数量不同，如下：

2.2 查看表结构

desc 表名；

2.3 修改表

在项目实际开发中，经常修改某个表的结构，比如字段名字、字段大小、字段类型、表的字符集类型、表的存储引擎等等。
如果还有需求，涉及到添加字段、删除字段等等，这时我们就需要修改表。

• 新增列

ALTER TABLE 表名 ADD (column_name datatype [DEFAULT expr] [after column_name][,columndatatype]...);

• [DEFAULT expr]：这是一个可选部分，用于为新列指定默认值
• [after column_name] ： 在哪一列后面新增

• 修改列属性

ALTER TABLE 表名 MODIFY (column_name datatype [DEFAULT expr][,columndatatype]...);

修改了列的属性，列原来的属性会被覆盖掉

• 删除列

ALTER TABLE 表名 DROP (column_name);

删除列一定要小心，删除列后，其对应的列数据都没了

• 修改表名

ALTER TABLE 表名 rename to 新的表名

• 修改列名

alter table 表名  change 旧列名 新列名 新列属性;

2.4 删除表

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

• TEMPORARY：如果你想要删除的是临时表，就使用这个关键字。
• 可以一次性删除多个表，只需用逗号分隔它们的名称即可

---

对于数据库以及数据表的操作，叫做数据定义语言DDL，它们的操作都是 create、drop、alter、show，注意与后面表中数据的操作区分。