MySQL 数据库详解

MySQL 数据库的操作分类

根据数据库的对象层级，可以将SQL的基础操作分为四类：

• 数据库（DB）操作。
• 数据表（Table）操作。
• 数据字段（Field）操作。
• 数据操作。

一、数据库（DB）的基本操作

在终端的任何位置，输入如下命令，即可进入 mysql 命令的执行窗口：

mysql -u root -p

1、创建数据库
create database 数据库名称 [数据库选项];
数据库名称的命名规范：

• 由数字、字母和下划线组成。
• 不区分大小写。
• 不能以数字开头。
• 建议使用下划线法创建复杂的数据库名字。比如 db_ydl。

举例：

创建一个名为 db_ydl1 的数据库：

create database db_ydl1 ;

创建一个指定字符集的数据库：

create database db_ydl2 charset utf8MB4;

创建一个指定校对集的数据库：

create database db_ydl3 charset utf8MB4 collate utf8mb4_general_ci

2、查看数据库
查看有哪些数据库：(显示所有的数据库列表)

show databases;

查看 db_ydl1 这个数据库的具体创建指令是怎样的：

show create database db_ydl1;

备注：由于系统会加工，所以看到的结果不一定是真实的创建指令。

3、使用指定的数据库
使用指定的数据库：（也可以理解成：进入指定的数据库）

# 语法格式

use database_xxx;

# 举例

use db_ydl;

假设当前服务器连接中有很多个数据库（db_ydl1、db_ydl2），此时，我输入 use db_ydl2则代表我想使用 db_ydl2 这个数据库。

4、修改数据库的参数
我们一般很少修改数据库的名称，一般是去修改数据库的一些选项，比如：

• 修改字符集
• 修改校对集

语法格式：

alter database 数据库名称 [库选项]

举例1、修改数据库的字符集为gbk：

alter database db_ydl1 charset gbk;

举例2、修改数据库的校对集：

alter database db_ydl2 charset gbk collate gbk_chinese_ci;

备注：因为校对集是和字符集有关的，所以上方指令是在修改字符集的同时，修改校对集。

5、删除指定的数据库
语法格式：

drop database 数据库名称;

备注：删除数据库时，会清空当前数据库里的所有数据表，所以删除数据库的操作一定要谨慎。

二、数据表（Table）的基本操作

注意，我们最好先通过 use xxx_database 命令进入指定的数据库（DB），然后在当前数据库下，进行数据表（Table）的操作。

1、创建数据表
语法格式：

create table [数据库名].[表名] (
    字段名1 字段类型,
    ...
    ...
    字段名2 字段类型
) 表选项;

举例：

1、在当前数据库中创建数据表 table_ydl1，并新增主键 id 字段：

CREATE TABLE table_ydl1 (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY
);

2、在当前数据库中创建数据表 t_student1，并新增 name、age这连个字段：

create table t_student1(
    name varchar(255),
    age int
);

3、在指定的数据库 db_2 中创建数据表 t_student2：

create table db_2.t_student2(
    name varchar(255),
    age int
);

4、在当前数据库中创建数据表 t_student3（含表选项）：

create table t_student3(
    name varchar(255),
    age int
)engine Innodb charset utf8MB4;

举例4中的代码涉及到存储引擎，这里解释一下：

存储引擎是指数据存储和管理的方式，MySQL中提供了多种存储引擎，一般使用默认存储引擎 InnoDB。

• InnoDB：默认存储引擎；支持事务处理和外键；数据统一管理。
• MyIsam：不支持事务和外键；数据、表结构、索引独立管理；MySQL5.6以后不再维护。

6、扩展：如果想创建一个与已有表一样的数据表，MySQL提供了一种便捷的复制模式

2、复制数据表
如果想创建一个与已有表一样的数据表，MySQL提供了一种便捷的复制模式。

语法格式：（复制现有的表 table_xx1 到 table_xx2）

create table table_xx1 like 数据库名.table_xx2;

注意，这种复制模式，table_xx2 只会复制表 table_xx1 中的字段，不会复制表table_xx1中的数据。

举例：

 在当前数据库下，复制现有的表`t_ydl1` 到表 `t_ydl2`

create table t_ydl1 like t_ydl2;

 复制现有的表`t_ydl1` 到表 `t_ydl2`，是复制到 `db2`这个数据库中

create table t_ydl1 like db2.t_ydl2;

3、显示数据表的名称
在当前数据库下，显示所有的数据表：

show tables;

在指定的数据库中，显示所有的数据表：

show tables from db_ydl1;

显示数据表的创建指令：(查看 t_ydl1 这个数据表的具体创建指令是怎样的)

show create table t_ydl1; # 备注：由于系统会加工，所以看到的结果不一定是真实的创建指令。

4、查询（查找）数据表的名称
根据 表名称 查询数据表，也可以理解成：按条件显示部分数据表。

根据数据表的表名称查找数据表时，需要用到关键词like，而且还要涉及到两个符号：

• % 表示匹配任意多个字符。
• _ 表示匹配任意一个字符（固定位置）。

上面这两个模糊查询的符号，大家要牢记。我们来看看具体的例子。

语法举例：

show tables like '%like_';    # _表示匹配一个字符（固定位置），%表示匹配N个字符

% 符号举例：

查询表名称中，包含 “ydl” 这个关键字的表（“ydl”这个关键字的前后可能都有内容）

show tables like '%ydl%';

查询表名称以“ydl”开头的表（这个命令应该很实用）

show tables like 'ydl%';

_符号举例：

根据 表名称 来查询表，查询条件是：表名称以“ydl”开头，而且要确保 ydl 的后面有三个字符（因为我在 ydl 的后面写了三个下划线）。

show tables like 'ydl___';

5、desc：查看数据表的表结构
查看数据表的表结构，就是查看这张表中定义了哪些字段，以及这些字段是如何定义的。通过这种方式，我们可以清晰地了解数据的存储形式。

项目开发中，领导在检查我们的工作时，首先看的就是我们的表中定义了哪些字段。所以说，这种方式，还是很实用的。

语法格式：

• # 方式1
• desc 表名称;
• # 方式2
• describe 表名称;
• # 方式3
• show columns from 表名称;
• 上面的三种方式，效果都一样，三选一即可。

6、修改数据表的表名称和表选项
修改数据表的表名称：

在当前数据库下，修改数据表的表名称：

rename table 原表名 to 新表名;

指定某个数据库，然后修改数据表的表名称：

rename table 数据库名.原表名 to 数据库名.新表名;

修改数据表的表名称：

alter table table1 charset gbk;

7、删除数据表
语法格式：

drop table 数据表名称;

 三、字段（Field）的基本操作
数据表 table 创建好了之后，我们就可以开始在这张表中新增字段了。

1、新增字段
语法格式：

alter table 表名 add [column] 字段名 字段类型 [字段属性] [字段位置];

注意事项：

新增字段时，必须制指定字段类型。
[column]、 [字段属性]、[字段位置] 这几个都是选填，其他是必填。
追加字段时，这个字段的顺序默认排在最后。
举例：

新增字段 name:

alter table table_ydl1 add name varchar(255);

新增字段 age：

alter table table_ydl1 add age int;

2、新增字段时，设置字段的位置（顺序）
在新增字段时，它的顺序是默认放在最后面的，当然，我们也可以人工指定它的顺序。

在修改字段的位置时，我们可以用到下面这两个关键字：

• first 放到最前面
• after 放到某个字段的后面

语法格式：

alter table 表名 add 新字段名 字段类型 字段位置;

举例1：

在 name字段的后面，新增一个 sex 字段：

alter table t_ydl1 add sex varchar(255) default null comment '性别' after name;

注意，上方举例中，如果是新建 varchar 类型的字段，一定要指定 varchar 的长度（比如255），否则报错。

举例2：

新增一个 id 字段，放到最前面：

alter table t_ydl1 add id int first;

3、change：修改现有字段的字段名
修改现有字段的字段名，是通过 change 关键字，不是通过 modify 关键字（后者会报错，执行失败）。

语法格式：

格式1（精简版）

alter table 表名 change 原字段名 新字段名 字段类型;

 格式2（完整版）

alter table 表名 change 原字段名 新字段名 字段类型 [字段属性] [位置];

注意：

修改字段名时，一定要设置新字段的字段类型。
虽然 change 关键字也可以修改现有字段的字段属性、字段位置，但我们一般是通过 modify 关键字来做（下面会讲）。
举例：

修改字段名 sex 为 sexy：

alter table t_ydl2 change sex sexy varchar(255);

4、modify：修改现有字段的的字段类型、字段位置、字段属性
语法格式：

alter table 表名 modify 现有字段的字段名 现有字段的字段类型 [字段属性] [位置]；

举例1、针对现有的字段 name 和 age，更换这两个字段的顺序：

注意，这里的 age 后面一定要跟上它的字段类型，否则执行失败

alter table t_ydl1 modify age int after name;

修改字段的默认值
若本身存在默认值，则先删除

alter table 表名 alter column 字段名 drop default;

若本身不存在则可以直接设定

alter table 表名 alter column 字段名 set default 默认值;

5、删除字段
删除字段的同时，会删除字段对应的数据。删除字段的操作不可逆，请谨慎操作。

语法格式：

alter table 表名 drop 字段名;

举例：（删除 t_ydl1 表中的 age 这个字段）

alter table t_ydl1 drop age;

四、数据的基本操作

1、新增数据
方式1、全字段插入：

语法格式：

insert into 表名 values(值1, 值2, ... 最后一个值);

解释：

值的顺序必须与所有字段的顺序一致。
值的数据类型也必须与字段定义的数据类型一致。
举例（给 t_ydl1 这个表中插入一条完整的数据）：

insert into t_ydl1 values(3, 'ydl', 28);

方式1、部分字段插入：

语法格式：

insert into 表名 (字段1, 字段2, 字段3) values(值1, 值2, 值3);

解释：

-字段的顺序可以随意，但值的顺序必须要与前面的字段顺序一一对应，数据类型也要一致。

举例（给 t_ydl1 这个表中的指定字段插入数据）：

insert into t_ydl1 (id, name) values(4, 'aaaaa');

2、查询数据
查询数据的操作，占sql日常操作的95%以上。

语法格式：

select xxx from 表名;

举例：

查询表中的所有数据：

select * from t_ydl1;

查询表中 name、age 这两个字段的数据：

select name, age from t_ydl2;

查询表中 id=2 的数据：

select * from t_ydl3 where id = 2;

3、修改数据
语法格式：

update 表名 set (字段1 = 新值1, 字段2 = 新值2) [where 条件筛选];

解释：

我们通常是结合 where 条件语句来修改数据。
修改数据之前，要先保证表里面有数据。如果这张表是空表，那么，执行这个命令后，等于没执行。
举例：

将表中，name 这个字段的值全部修改为bbbb：

update t_ydl1 set name = 'bbbb';

id = 3 的这条记录中，修改 name 和 age 这两个字段的值：

update t_ydl1 set name = 'cccc', age = '34' where id = 3;

4、删除数据
删除字段的操作不可逆，请谨慎操作。

语法格式：

delete from 表名 [where 条件];

解释：

执行删除操作之后，匹配到的整条记录，都会删除。
删除数据之前，要先保证表里面有数据。如果这张表是空表，那么，执行这个命令后，等于没执行。
举例：

删除表中 id = 2的记录：

delete from t_ydl1 where id = 2;

5、join 联表查询
联表查询命令

• tableA inner join tableB：表 A 与表 B 匹配的行会出现在结果中。
• tableA left join tableB：表 A 与表 B 匹配的行会出现在结果中。表 A 中独有的数据，对应表 B 中用 null 填充。
• tableA right join tableB：表 A 与表 B 匹配的行会出现在结果中。表 B 中独有的数据，对应表 A 中用 null 填充。

光是这样看，不好理解，我们来举个例子。

举例

现在有下面这两张表：作者表 author、图书表 book。

表 1、作者表 author：

id    authorId    authorName
1    11    aaaa
2    12    bbbb
3    88    cccc

表 2、图书表 book：

id    bookId    bookName    authorId
1    201    1dddd    11
2    202    2cccc    11
3    203    3eeee    11
4    204    4ffff      12
5    205    5gggg   12
6    206    6hhhh    12
7    777    7lllllll    99

注意，表2中的每本图书都有对应的 authorId，这个 authorId 就是对应表1中的 authorId。通过 authorId 把两张表关联起来。

通过联表查询上面的两张表，我们来对比一下查询结果。

情况 0：（inner join）

SELECT * FROM author INNER JOIN book;

查询结果：

上面这种查询，没有意义，因为没有加任何查询条件。

情况 1：（inner join）

SELECT * FROM author INNER JOIN book ON author.authorId = book.authorId;

查询结果：

上面这行命令，跟下面这行命令等价：

SELECT * FROM author, book WHERE author.authorId = book.authorId;

情况 2：（left join）

SELECT * FROM author LEFT JOIN book ON author.authorId = book.authorId;

查询结果：

情况 3：（right join）

SELECT * FROM author RIGHT JOIN book ON author.authorId = book.authorId;

情况 4：（cross join）

select * from t1 cross join t2 cross join t3; 和 select * from t1,t2,t3;  相同

情况 5：

select * from author inner join book on  author.authorId = book.authorId;
等价于 select * from author cross join book where author.authorId = book.authorId;

情况 6：

 select * from author left outer join book on author.authorId = book.authorId; 左外连接，左边的数据会全局展示出来，右侧没有对应的数据会 插入null 

left outer join"可以简写为 "left join" 

情况 7：

 select * from author right outer join book on author.authorId = book.authorId; 右外连接，右边的数据会全局展示出来，左侧没有对应的数据会 插入null 

right outer join"可以简写为 "right  join"