MySQL：MySQL库和表的基本操作

以下内容都属于DDL！！

一、库的增删查改

1.1 查看数据库

show databases;

查看当前位于哪个数据库中  select basebase（）

显示创建语句  show create database 数据库名；

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

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

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

1.2 创建数据库

本质：在/var/lib/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、[] 是可选项

3、数据库创建的时候有两个编码集：

CHARACTER SET: 指定数据库采用的字符集 ——>数据库未来存储数据的编码方式

COLLATE: 指定数据库字符集的校验规则 ——>支持数据库，进行字段比较使用的编码，本质上也是一种读取数据库中数据采用的编码格式

数据库无论对数据做任何操作，都必须保证操作和编码必须是编码一致的！！

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

使用案例：

1、用if not exitsts选项  创建一个已经存在的数据库（我们会发现他报告warning）

2、创建一个使用utf字符集，并带校对规则的 db3 数据库。

1.3 删除数据库

DROP DATABASE [IF EXISTS] db_ name; 

执行删除之后的结果:

1、数据库内部看不到对应的数据库

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

注意：不要随意删除数据库，也不要随意对他进行重命名（不支持）

使用案例：

1.4 修改数据库

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

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

使用举例：alter database test2 charset=gbk collate gbk_chinese_ci;

二、认识系统编码（字符集和校验规则）

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

show variables like 'character_set_database';
show variables like 'collation_database';

我们可以不只看database,还可以看connection（链接）和server（服务）

2.2 查看数据库支持的字符集和字符集校验规则

show charset;

 show collation;

2.3 验证不同校验码编码的影响

1、创建一个数据库test1，校验规则使用utf8_ general_ ci[不区分大小写]

create database test1 collate utf8_general_ci;

2、创建一个数据库test2，校验规则使用utf8_ bin[区分大小写]

create database test2 collate utf8_bin; 

进入第一个数据库，暂时没有表

use test1；

show tables；

我们开始尝试建表

create table if not exists person(name varchar(20));

用desc我们查看表结构的详细信息

desc person；

插入数据并检索数据

insert into person values(‘a’);//采用编码规则

*select from person；//采用校验规则

检索name为a的数据——>比较也是用 校验规则 utf8_general_c不区分大小写

按照上述方法查看test2 utf8_bin 区分大小写

不区分大小写进行排序

select * from person order by name;

区分大小写进行排序

结论：校验集是会影响比较结果的 ！！

三、数据库的备份和恢复

3.1 备份数据库

【退出链接】mysqldump -P3306 -u root -p 密码 -B 数据库名 > 数据库备份存储的文件路径

这时，可以打开看看 test2.sql 文件里的内容，其实把我们整个创建数据库，建表，导入数据的语句都装载这个文件中。

3.2 还原数据库

mysql> source /root/test2.sql;

我们先把test2给删了  然后再还原。

他会把备份文件里的sql语句全部都跑一遍。

3.3 其他情况

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

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

同时备份多个数据库 ？

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

如果备份一个数据库时，没有带上-B参数， 在恢复数据库时，需要先创建空数据库，然后使用数据库，再使用source来还原。//如果带了B 那么备份文件里会有create database这样的语句

四、查看当前数据库的具体链接情况

show processlist

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

五、表的操作

5.1 增加表

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

说明：

field 表示列名

datatype 表示列的类型

characterset 字符集，如果没有指定字符集，则以所在数据库的字符集为准

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

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

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

users.frm：表结构

users.MYD：表数据

users.MYI：表索引

user2的存储引擎是InnoDB，在数据目中有两个不同的文件 ，分别是：

users.frm：表结构

users.ibd：表数据

区别： MyIsam的数据和索引是分开存储的的。而InnoDB是数据和索引一起存储

5.2 查看表

如果我们忘记了当前所在数据库的名称，我们可以用select database（）

查看当前数据库有哪些表：show tables；

查看表结构（详细信息）：desc 表名

但这个并没有显示我们建表时的一些字符集和校验规则和存储引擎，因此如果 我们想要获得更详细的信息，就需要显示创建语句：show create table user1;

这样看得不舒服，我们可以加上一个\G选型

其实你看起来好像和你创建的写法不一样，但实际上是因为mysqld对你的sql语句进行语法分析词法分析的时候，可能你的sql语句写法不是很标准，他会帮你转化成比较标准化的写法，留下你的所有操作痕迹

5.3 修改表

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

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);

使用案例：

1、修改表名

mysql> alter table user1 rename to user;（to可以省略）

2、添加两条记录

mysql> insert into users values(1,‘a’,‘b’,‘1982-01-04’),(2,‘b’,‘c’,‘1984-01-04’);

3、添加一列新字段

mysql> alter table users add image_path varchar(128) comment ‘这是用户的头像路径’ after birthday;（ 对原来表中的数据没有影响，且新字段 默认是null）

4、修改某一列的类型属性（比如长度）

mysql> alter table user modify name varchar(30);

需要注意的是 他会把原来所有的属性给直接覆盖 ！！（name字段的comment描述没有了）

5、删除password列

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

mysql> alter table user drop password;

6、修改某一列的名称

mysql> alter table user change name xingming varchar(60); –新字段需要完整定义

5.4 删除表

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

drop table user2；

注意： 轻易不要做修改或者删除 ，表跟数据库一样，在所有的业务处理过程中都靠近底层、后端，所以 表结构、表名称、表是否存在等修改直接决定了所有使用该数据库的上层要不要改变！ ！

作为一个程序员来说，如果一旦开发了很长时间且表结构已经建好了，一旦在后期出现了需要修改表结构的情况，那么整个上层的业务逻辑可能都需要被改变！！因此这样的影响是非常巨大的！