MySQL之对数据库和表的操作

一、创建数据库

语法：

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

说明：

1、大写的表示关键字。
2、[]是可选项。if not exists 表示如果数据库不存在就创建，如果存在，就不做任何事。
3、CHARACTER SET：指定数据库采用的字符集。
4、COLLATE：指定数据库字符集的校验规则。

演示：

创建数据库时，也可以开始就设置好数据库的字符集和校验规则。

create database db3 charset=utf8 collate utf8_general_ci;

二、字符集和校验规则

什么是字符集和校验规则？

创建数据库的时候，一般有两个编码集：

数据库编码集：数据库未来存储数据的编码方式。

数据库校验集：支持数据库进行字段比较使用的编码，本质也是一种读取数据库中数据采用的编码格式。

数据库无论对数据做任何操作，都必须保证操作和编码必须是编码一致的。存数据的时候用什么编码，取的时候就得用什么对应的编码去取。比如说，一本书是用中文写的，那么就必须要让会中文的人来读才能够读懂内容。

1、查看系统默认字符集

show variables like 'character_set_database';

2、查看系统默认的校验规则

show variables like 'collation_database';

3、查看数据库支持的字符集

字符集主要是控制用什么语言。比如utf8就可以使用中文。

show charset;

4、查看数据库支持的字符集校验规则

show collation;

5、校验规则对数据库的影响

~ 创建一个数据库，校验规则使用utf8_ general_ ci。

create database if not exists test1 collate utf8_general_ci;

然后我们查看一下其校验规则：

接着在test1数据库中创建一张表：

create table person(name varchar(20));

再插入一些数据，之后查看插入的结果：

使用下面的语句进行查询：

select * from person where name='a';

~ 创建一个数据库，校验规则使用utf8_ bin：

create database if not exists test2 collate utf8_bin;

使用下面的语句进行查询：

select * from person where name='a';

从上面的结果中，我们可以发现，在select查询中，utf8_ general_ ci是不会区分大小写的，而utf8_ bin是会区分大小写的。

再来看看下面test2（使用utf8_ bin校验规则）数据库中的我们根据name的排序结果：因为其区分大小写且小写字母的ascii码大于大写字母，所以会得到下面的结果。

select * from person order by name;

再来看看下面test1（使用utf8_ general_ ci校验规则）数据库中的我们根据name的排序结果：

因为utf8_ general_ ci不会区分大小写，所以a和A就相当于同一个字符，会被放在一起。

三、操作数据库

1、查看数据库

show databases;

作用：可以查看当前MySQL客户端下已经存在的所有数据库。

2、查看创建语句

show create database test1;

3、修改数据库

一般来说，我们对数据库的修改主要是修改数据库的字符集和校验规则。

用法：

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

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

4、数据库的删除

用法：

DROP DATABASE [IF EXISTS] db_name;

演示：

注：对应的数据库文件夹被删除，里面的数据表全部被删。特别注意的是，除非特殊情况，不要随意删除数据库。

5、备份和恢复

数据库的备份和恢复

备份，语法：在Linux命令行下执行

mysqldump -P 端口号 -u root -p 密码 -B 数据库名 > 数据库备份存储的文件路径

恢复，语法：在登陆mysql客户端，执行下面语句：

source 备份文件所在路径;

先删除test2数据库：

然后从备份恢复数据库：

某个数据库中某张表的备份

如果备份的不是整个数据库，而是其中的一张表：

mysqldump -u root -p 数据库名 表名1 表名2 > 数据库被备份后所在路径

同时备份多个数据库

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

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

6、查看目前所在数据库

我们可以使用下面的语句查看目前所在的数据库。

select database();

7、查看数据库连接情况

show processlist;

使用该语句可以查看当前有多少个客户端连接这个服务器。

四、对表的操作

1、创建表

语法：

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

说明：

field：表示列名。

datatype：表示列的类型。

character set 字符集：如果没有指定字符集，则以所在数据库的默认字符集为准。

collate 校验规则：如果没有指定校验规则，则以所在数据库的默认校验规则为准。

engine 存储引擎：指定存储引擎，如果没有指定存储引擎，则使用默认存储引擎。

2、查看表结构

语法：

desc 表名;

说明：

field：字段名字 type：字段类型 Null：是否允许为空 key：索引类型

default：默认值 extra：扩充

3、修改表

先在上面的users表中插入一些数据：

添加字段：

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

在users表中添加一个字段，用于保存用户的生日。

修改字段：

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

** 删除字段：**

ALTER TABLE tablename DROP (column);

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

修改表名

alter table 原表名 rename to 新表名

修改字段名

alter table 表名 change 原列名称 新列名称
——新字段需要完整定义

4、删除表

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