数据库操作

创建数据库

CREATE DATABASE 数据库名称[相关特性]；

相关特性：
[CHARSET]
[COLLATE]
#示例
CREATE DATABASE my_shop
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

• 大写的字母是关键字（其实关键字并不区分大小写，只是我们人为用大小写区分关键字和其他变量

• 数据库名称可以是字母、数字或者下划线组成的任意字符串

• 语法内用[ ]括起来的参数是可选参数

• SQL语句以；结尾

• charset是字符集，表明数据库能够存储哪些字符

• collate是校对集，指定字符之间如何排序

一般来说，数据库的默认字符集是latin1，校对集是latin_swedish_ci，但latin1不支持中文，你可以在创建数据库时把字符集设置成utf8。

创建数据库就是在存储数据的文件夹data下生成一个与数据库同名的目录，用于保存此数据库相关内容

查看数据库

查看所有数据库

SHOW DATABASES；

查看指定数据库

SHOW CREATE DATABASE 数据库名称；

选择数据库

由于MySQL服务器中的数据需要存储在数据表中，而数据表需要存储在对应的数据库下，并且MySQL服务器又可以同时存在多个数据库，因此在对数据和数据表进行操作前需要选择数据库

USE 数据库名称；

除此之外可以在登录MySQL服务器时选择数据库

mysql -u 用户名 -p 密码 数据库名称

修改数据库

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

ALTER DATABASE 数据库名
CHARACTER SET = 新的字符集
COLLATE = 新的排序规则;

删除数据库

DROP DATABASE 数据库名称

备份

值得一提的是，在执行删除数据库操作之前，一定要备份需要保留的数据，确保数据的安全，使用mysqldump工具可以实现数据库的备份

#备份单个数据库
mysqldump -P 3306 -u root -p 密码 -B 数据库名 > 数据库备份存储的文件路径
#备份多个数据库
mysqldump -u root -p -B 数据库名1 数据库名2 ... > 数据库备份存储的文件路径

一般来讲，文件路径上加上扩展名.sql表示这是sql文本文件

mysqldump -P 3306 -u root -p -B db > test.sql

输入这段代码后，备份文件test.sql会直接生成在目录下，注意mysqldump是独立的软件程序而不是SQL语句，如果你处在MySQL界面中你需要先输入exit退出界面

这个备份文件不仅会备份我们的数据，还会备份历史上所有有效的操作，比如创建库、创建表语句

备份数据表

mysqldump -u root -p 数据库名 表名1 表名2 > 文件路径.sql

还原

我们刚刚用mysqldump生成了一个备份文件，现在我们需要把之前用mysqldump备份出来的.sql文件导回数据库

mysql> source 文件路径;
#示例
mysql> SOURCE ~/Study/MySQL/file.sql

~会自动解析成/home/你的用户名

注释

MySQL中单行注释以 # 开始标识，也支持标准SQL中的 -- 单行注释，但是为了区分其与SQL语句中负号和减法运算的混淆，在第二个短横线后面必须添加至少一个控制字符（空格、制表符等等）

#如果服务器中没有mydb数据库，则创建它
CREATE DATABASE IF NOT EXISTS mydb；

MySQL也支持标准SQL中的多行注释 /*    */ 

/*  填写多行注释
    查看当前服务器中所有数据库
*/

数据表操作

在MySQL数据库中，所有数据都存储在数据表中，如果要对数据进行添加、查看、修改、删除，首先需要在指定数据库中准备一张数据表。

创建数据表

创建数据表指的是在已有数据库中建立新表

​CREATE [temporary] TABLE [IF NOT EXISTS] 表名
(字段名 字段类型 [字段属性])；

​

• temporary表示临时表，在会话关闭时自动删除

• 字段名就是数据表的列名

• 字段类型设置字段中保存的数据类型 （这一列数据的数据类型）

• 字段属性指的是字段的某些特殊约束条件

在操作数据表之前，需要使用“USE 数据库名” 指定操作在哪个数据库中进行

创建数据表本质上是数据库在相应的数据目录里生成1-n个存放表数据的文件，数据库因为存储引擎不一样创建的表文件也不一样，比如在 MySQL 中，最常用的存储引擎是 InnoDB（默认）和 MyISAM

存储引擎MyISAM会生成三个文件：

• 表名.MYD：表数据

• 表名.frm：表结构

• 表名.MYI：表索引

InnoDB只会生成两个文件：

• 表名.frm：表结构

• 表名.ibd：表数据+表索引

查看数据表

MySQL提供了专门的SQL语句，用于查看某数据库存在的所有数据表、指定数据表的相关信息

SHOW TABLES [LIKE匹配模式];

不添加“LIKE匹配模式”就表示查看当前数据库中所有数据表；匹配模式符有两种：%和_ ，%表示匹配一个或者多个字符，_只能匹配一个字符

查看数据表的相关信息

除了能查看数据库下有哪些数据表，还可以查看数据表的相关信息，比如数据表的名称、存储引擎、创建时间等等。

SHOW TABLE STATUS [FROM数据库名] [LIKE匹配模式]

\G是MySQL客户端可以使用的结束符的一种，用于将显示的结果纵向排列。

• Name：数据表名称

• Engine：数据表的存储引擎

• Version：数据表的结构文件版本号

• Row_format：记录的存储格式，Dynamic表示动态

• Create_time：数据表创建时间

• Collation：数据表校对集

修改数据表

修改数据表名称

#单表操作
ALTER TABLE 旧表名 RENAME TO 新表名
#多表操作
ALTER TABLE 旧表名1 RENAME TO 新表名1,旧表名2 RENAME TO 新表名2...

修改表选项

数据表中的表选项字符集、存储引擎和校对集都可以通过alter table修改

alter table 表名 表选项 = 值

查看表结构

MySQL提供describe语句可以查看数据表中所有字段或者指定字段的信息，descirbe可以简写成desc；与show table status语句不同的是，decs语句是查看数据表的字段而不是数据表的自身信息

#查看所有字段信息
DESC 数据表名;
#查看指定字段的信息
DESC 数据表名 字段名;

查看数据表的创建语句

show create table 表名;

查看数据表结构

show column语句也可以查看表结构

show [full] columns from 数据表名;

可选项full表示显示详细内容，如果不添加full查询结果和desc一样，如果添加full选项可以查询到字段的权限、comment字段的注释信息等等

修改表结构

我们可以利用MySQL提供的alter table语句对字段名称、类型、位置进行修改、增加或删除

1.修改字段名

在MySQL中仅修改数据表中字段名称通常用CHANGE实现

alter table 数据表名 change  旧字段名 新字段名 字段类型;

需要注意的是，"字段类型"指的是新字段名的数据类型，即使与旧字段数据类型一样也必须重新设置

2.修改字段类型

在MySQL中仅修改数据表中字段类型通常用MODIFY实现

alter table 数据表名 modify 字段名 新类型;

3.修改字段的位置

数据表在创建时，字段编写的前后顺序就是其在数据库中存储的顺序。如果需要调整某个字段的位置，也可以通过MODIFY实现

alter table 数据表名
modify 字段名1 数据类型 [first|after 字段名2];

• first表示将字段名1调整为数据表的第一个字段

• after 字段名2表示将字段名1插入到字段名2后面

4.新增字段

对于已经构建好的数据表，可以通过ADD新增字段

alter table 数据表名
add 新字段名 字段类型 [first|after 字段名];

5.删除字段

将某个字段从数据表中删除可以通过DROP完成

alter table 数据表名 DROP 字段名;

删除数据表

drop table [if exits] 数据表1，数据表2... ;

[if exits]用于在删除一个不存在的数据表时防止产生错误，数据表一旦被删除，表中所有数据都将被清除。

数据操作

通常要想操作数据表里的数据，首先需要确保数据表中存在数据，MySQL中使用INSERT语句向数据表中添加数据，数据添加操作分为两种：一种是为所有字段添加数据，一种是为部分字段添加数据。

为所有字段添加数据

insert [into] 数据表名 values 值1,值2... ;

注意：插入数据的顺序必须与创建数据表时对应的字段顺序相同，可以通过desc语句查看数据表的字段顺序。

如果要插入中文字符记得把字符集设置为utf8。另外，对于已经添加数据的数据表，可以通过alter table ... change/modify 完成对字段字符集的设置。

alter table my_goods
modify name varchar(32) charset set utf8;

为部分字段添加数据

可以通过指定字段名的方式增加数据，其中指定的字段名可以是数据表中全部字段，也可以是部分字段。

insert into 数据表名 字段名1,字段名2... 
valuse 值1,值2... ;

多个字段名之间使用 “,” 隔开，字段名编写顺序可以与表结构不同，只需要跟添加的数据顺序相对应就行。对于未添加数据的字段，系统会为其添加默认值NULL

一次添加多行数据

MySQL提供了一种语法让我们可以向一张数据表中同时插入多条记录

insert into 数据表名
values (值列表1),(值列表2)... ;

insert into my_goods values
(1,49,'notebok.,10),
(2,12,'iphone',8200),
(3,70,'magazine',25);

查询数据

1.查询全部数据

可以用 * 通配符替代数据表中所有字段名

select * from 数据表名

如果你想看表里的数据，使用select语句；如果你想看表的结构，使用desc语句；如果你想看创建表的源代码，使用show语句

2.查询表中部分字段

查询数据时，可以在select语句的字段列表指定要查询的字段

select 字段名1,字段名2,... from 数据表名;

3.简单条件查询数据

如果想要查找除符合条件的数据，可以使用WHERE实现

select [*|字段名] 
from 数据表名 where 字段名=值;

select * from my_goods where id=1;

修改数据

MySQL提供UPDATE语句修改数据

update 数据表名
set 字段名1=值1,字段名2=值2,...
[where 条件]

如果没有where条件，那么表中所有对应字段的数据都会改成这个值，所以修改数据的时候需要慎重

删除数据

MySQL使用DELETE语句删除表中的数据

delete from 数据表名 [where 条件];

delete from my_goods where id=3;