MySQL:库表的基本操作

库操作

查看

• 查看存在哪些数据库：

show databases;

 

• 查看自己当前处于哪一个数据库：

select database();

 由于我不处于任何一个数据库中，此处值为NULL

• 查看当前有哪些用户连接到了MySQL：

show processlist;

 

---

创建

• 创建一个数据库

语法：

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

[if not exists]：如果数据库存在，就不创建该数据库。 一个数据库，对操作系统而言，本质就是一个目录，在我的ubuntu主机下，数据库存储在了/var/lib/mysql目录下，这个路径会因为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;

执行create database db_1 charset=utf8 collate utf8_general_ci： 在数据库对应的目录中，会存在一个文件dp.opt，其内部存储了数据库对应的校验集与字符集：

查看系统默认的字符集：

show variables like 'character_set_database';

 

• 查看系统默认的校验集：

show variables like 'collation_database';

 

---

删除

• 删除数据库：

drop database [if exists] 数据库名;

 

---

修改

修改数据，主要是指修改数据库使用的字符集和校验集。

• 修改字符集：

alter database 数据库名 charset=xxx;

 

• 修改校验集：

alter database 数据库名 collate xxx;

mysqldump -B bit_index > ~/test/bit_index.sql

 

---

备份

• 拷贝数据库指令

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

注意：该指令要在bash中执行，而不是数据库中。

比如备份bit_index数据库：

mysqldump -B bit_index > ~/test/bit_index.sql

上述指令指定了一个名为bit_index 数据库，将其保存在路径~/test/bit_index.sql。

查看一下sql内部的数据： 

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

• 还原数据库

source 路径.sql;

该指令要在MySQL内部执行，而不是命令行。

---

表操作

• 进入/切换 数据库：

use 数据库名;

 

---

创建

• 创建一个表：

create table [if not exists] 表名 (
	field1 datatype,
	field2 datatype,
	field3 datatype
) character set 字符集 collate 校验集 engine 存储引擎;

• [if not exists] ：可省略，如果不存在这个表，才创建
• field：列名
• datatype：该列的数据类型
• character set：指定字符集，若没有指定，与数据库使用的字符集一致
  • 也可写为：charset=字符集
• collate：指定校验集，若没有指定，与数据库使用的校验集一致
• engine：指定存储引擎
  • 也可写为：engine=存储引擎

注意事项：

1. 每一个列后面都有一个逗号，但是最后一个没有
2. 整个语句末尾有分号

此时在/var/lib/mysql下的boe_he目录下，出现了三个文件：t1.frm，t1.MYD，t1.MYI这三个文件共同维护了t1这张表。 可见：数据库中的表的本质，就是多个文件！

---

查看

• 查看当前数据库下有哪些表：

show tables;

 

• 查看表的结构：

desc 表名;

 

• Field：列名
• Type：列的数据类型
• Null：该列是否允许为空
• Key：索引类型
• Default：默认值
• Extra：扩充

• 查看建表时的语句：

show create table 表名;

 

执行语句后，展示了创建表t1时的语句，直接执行该语句，格式比较混乱，一般来说建议将末尾的分号;改为\G：

show create table 表名 \G

这个\G可以将数据以更加直观的方式展示出来：

每一列后面都有一个DEFAULT NULL，表示默认值为NULL，这个也是MySQL的优化，如果我们不指定默认值，就会自动设置默认值为NULL。 

---

删除

• 删除一个表：

drop table [if exists] 表名 [, 表名, ...];

 

 

修改

• 修改表名：

alter table 旧表名 rename [to] 新表名;

 

• 插入新的列

alter table 表名 add 列名 类型 comment 描述 after xxx;

• comment：相当于备注，用于表示这一列数据的含义
• after：指定新增的列插入到哪一列后面，如果不指定，默认插入到最后一列

对比前后的desc table_1，确实多出了age列。

• 删除列

alter table 表名 drop 列名;

 

• 修改列

alter table 表名 change 旧列名 新列名 类型;

id列改为stu_id列，并把类型从int改为了smallint。由于我把数据类型变小了，此时有可能会发生数据精度丢失，一般不建议这样操作。