MySql库和表的操作

---

theme: juejin

库的操作

创建数据库

创建数据库的基本语法：

CREATE DATABASE [IF NOT EXISTS] db_name [[DEFAULT] CHARSET=charset_name] [[DEFAULT] COLLATE=collation_name];

• SQL中大写的表示关键字，官方建议使用大写，但是小写也可以，实际是不区分大小写的。
• [ ]中代表的是可选项。
• CHARSET用于指定数据库所采用的编码格式。
• COLLATE用于指定数据库所采用的校验规则。

如果创建数据库时未指明数据库的编码格式或校验规则，则默认使用MySQL配置文件中对应的编码格式或校验规则。一般默认都是utf-8。

使用示例：

1. 创建一个名为db1的数据库。

create database db1;

2. 指定编码格式gb2312创建数据库db2

create database db2 charset=gb2312;

一般编码格式指定后，校验规则也会自动设定的。指定校验规则在创建数据库时添加COLLATE选项即可。

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

  • show variables like 'character_set_database';查看系统默认的字符集。
    

  • show variables like 'collation_database';查看系统默认的校验规则。

    

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

  • show charset; 查看数据库支持的字符集
    

  • show collation; 查看数据库支持的校验规则

    

校验规则对数据库的影响

• 字符集编码格式指的是在存储数据时各个字符的底层编码，用于指定数据的存储格式。
• 字符集校验规则是在字符集内用于比较字符的一套规则，用于对数据进行比对。
  比如我们存储数据的时候是按照utf8的格式进行存储的，那么将来在对数据做比对时也必须按照utf8的格式进行比对，因为“存数据”和“取数据”的方式必须保持一致。如果是其他格式进行对比，那么就会出现乱码的情况。

使用不同的校验规则操作数据库中的数据可能会得到不同的结果，比如utf8_general_ci校验规则在比对数据时是不区分大小写的，而utf8_bin校验规则在对比数据时则是区分大小写的。

如果字符集使用的是utf8，那么采用上面两种校验规则都是可以的，如果使用utf8_general_ci校验规则。那么在该数据库中的表中进行查询的时候，是不区分大小写的，查a的时候，A也会被查到。在建库的时候就要确定好校验规则。

操作数据库

查看数据库

show databases;

使用show databaseSQL可以查看系统中所有的数据库

显示创建语句

使用show create database 数据库名SQL可以查看对应数据库的创建语句。

比如查看上面两个库db1和db2。

• 数据库名字的反引号``,是为了防止使用的数据库名和关键字冲突
• /*!40100 DEFAULT CHARACTER SET utf8 */不是注释，它表示当前MySQL版本如果大于4.10，则执行后面的SQL语句。

修改数据库

ALTER DATABASE db_name [[DEFAULT] CHARSET=character_name] [[DEFAULT] COLLATE=collation_name];

• 修改数据库一般是修改字符集或者校验规则。
  比如将数据库db2字符集修改为gbk;

修改完字符集后，校验规则也会修改为默认的。要指定的话加COLLATE选项即可。

删除数据库

就是经常听到的删库。语句如下：

DROP DATABASE [IF EXISTS] db_name;

• IF EXISTS 选项表示如果存在删除。

比如删除数据库db2

drop database db2;

删除后，存储路径下对应的目录也就被删除了。

执行删除之后的结果:

数据库内部看不到对应的数据库 对应的数据库文件夹被删除，级联删除，里面的数据表全部被删。
在实际工作中，不要随便删除数据库，就算要删除，先备份。否则容易删库跑路。

数据库的备份和恢复

备份

语法：(这不是SQL语句)
mysqldump -P 端口号 -u 用户名 -p 密码 -B 数据库名1 数据库名2 ... > 数据库备份存储的文件路径

mysqldump 是一个用于备份 MySQL 数据库的命令行工具，而不是 SQL 语句。

输入mysql密码后就可以进行备份了。

这个.sql文件的内容就是在该数据库中执行的各种SQL命令。

恢复数据库
恢复SQL语句

source 数据库备份存储的文件路径

比如恢复刚才备份的.sql文件

恢复数据库就是顺序执行.sql文件中的SQL语句，执行完毕后，数据库也就恢复出来了。

表的操作

表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。

表操作至少会涉及如下两类SQL语句：

• DDL（Data Definition Language）数据定义语言：比如建表、删表、该表、新增列、删除列等。
• DML（Data Manipulation Language）数据操作语言：比如插入记录、删除记录、修改记录等。

下面只涉及到DDL语言，不涉及到DML语言

创建表

语法：

CREATE TABLE [IF NOT EXISTS] table_name(
	field1 datatype1 [COMMENT '注释信息'],
	field2 datatype2 [COMMENT '注释信息'],
	field3 datatype3  [COMMENT '注释信息']
)[CHARSET=charset_name] [COLLATE=collation_name] [ENGINE=engine_name];

• IF NOT EXISTS表示不存在在进行创建。
• field1表示列名。
• datatype 表示列的类型。
• CHARSET 字符集，如果没有指定字符集，则以所在数据库的字符集为准。
• COLLATE 校验规则，如果没有指定校验规则，则以所在数据库的校验规则为准。
• ENGINE用于指定表所采用的存储引擎。

示例：在operator_table数据库中创建表user1。

1. 先创建数据库operator_table

create database operator_table;

2. 使用这个库

use operator_table;

3. 创建表

mysql> create table user1(
    -> id int comment "用户id",
    -> name varchar(20) 
    -> )charset=utf8 engine=MyISAM;

创建完成后，会在对应的目录下创建三个文件

• 采用的存储引擎不同，创建表时所产生的文件是不一样的
• 采用InnoDB存储引擎建表，会产生对应的xxx.frm（表结构）和xxx.ibd（表数据+表索引）文件。
• 采用MyISAM存储引擎建表，会产生对应的xxx.frm（表结构）、xxx.MYD（表数据）和xxx.MYI（表索引）文件。

查看表结构

使用desc 表名可以查看表的结构

desc user1;

• Fileld:表示该字段的名字
• Type:表示该字段的类型。
• Null:表示该字段是否允许为空。
• Key:表示索引类型，比如主键索引为PRI。
• Default:表示该字段的默认值。
• Extra:表示该字段的额外信息说明。

要查看创建表时的所有细节，可以使用show create table 表名SQL。如下：

show create table user1 \G

一般查看的时候加个\G，不加的话那个格式打印出来很别扭。

修改表

修改表的SQL语句如下：

ALTER TABLE table_name ADD 新增列名 新增列的属性;

ALTER TABLE table_name MODIFY 列名 修改后的列属性;

ALTER TABLE table_name DROP 列名;/

ALTER TABLE table_name RENAME [TO] 新表名;

ALTER TABLE table_name CHANGE 列名 新列名 新列属性;

先向空白的表中添加几条数据

示例：新增一列password用来保存密码。

alter table user1 add password char(30) after name;

新增一列，列名为password，数据类型为cahr,新增到name属性后面。

修改结果：

示例：修改类的数据类型

将user表中的name列的数据类型从varcahr(20)修改到varcahr(50)

alter table user1 modify name varchar(50);

示例：修改列名

将name列名修改为usernaem。

alter table user1 change name username varchar(50);

示例：修改表名

将user1表名修改为user_table

alter table user1 rename user_table;

示例：删除列

删除user_table的password列

删除表

删除表的SQL语句如下：

DROP [TEMPORARY] TABLE [IF EXISTS] table_name;