数据库(mysql）学习笔记

2022.04.09 文章 1 评

数据库 (Data Base)：

存储数据的仓库，数据是有组织的进行存储
数据库管理系统 (Data Base Management System)：
管理数据库的大型软件
SQL (Structured Query Language)：
结构化查询语言
操作关系型数据库的编程语言
定义操作所有关系型数据库的统一标准
常见的关系型数据库管理系统
Oracle：收费的大型数据库，Oracle公司的产品
MySQL：开源免费的中小型数据库，后来Sun公司收购了MySQL，而Sun公司又被Oracle收购
SQL Sever： Microsoft公司收费的中型的数据库。C#、.net等语言常使用
PostgreSQL：开源免费中小型的数据库
DB2：IBM 公司的大型收费数据库产品
SQLite：嵌入式的微型数据库，如作为Android的内置数据库
MariaDB：开源免费中小型的数据库

MySQL 安装与使用

MySQL 安装与使用(点击查看)

MySQL 数据模型

关系型数据库

关系型数据库是建立在关系模型基础上的数据库，关系型数据库是由多张能互相连接的 ==二维表== 组成的数据库
优点：

都是使用表结构，格式一致易于维护
使用通用的 SQL 语言操作，使用方便，可用于复杂查询
数据存储在磁盘中，安全

SQL

结构化查询语言，一门操作关系型数据库的编程语言
定义操作所有关系型数据库的统一标准
对于同一个需求，每一种数据库操作的方式可能会存在一些不一样的地方，我们称为 "方言"

SQL 通用语法

SQL 语句可以当行或多行书写，以分号结尾
MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写
注释
- 单行注释：==—— 注释内容== 或 ==# 注释内容（MySQL 特有）==
- 多行注释：==/ 注释 /==

SQL 分类

DDL（Data Denfinition Language）数据定义语言，用来定义数据库对象：数据库，表，列等
DML（Data Manipulation Language）数据操作语言，用来对数据库中表的数据进行增删改
DQL（Data Query Language）数据查询语言，用来查询数据库中表的（记录）数据
DCL（Data Control Language）数据控制语言，用来定义数据库的访问权限和安全级别，及创建用户
DDL：操作数据库，表等
DML：对表中的数据进行增删改
DQL：对表中的数据进行查询
DCL：对数据库进行权限控制

一、数据库的介绍和基本操作

1、基本命令

登陆数据库命令:

mysql -h localhost -u root -p

创建数据库命令:

create database test_db;

查看已经创建的数据库的定义

show create database test_db;

查看已经存在的所有数据库:

show databases;

删除数据库

drop database test_db;

注意删除数据库时要小心，不会给出提示，数据和数据表会一同删除。

2、数据库储存引擎

1)、查看引擎命令

使用如下命令查看系统所支持的引擎类型:

show engines;

2)、InnoDB引擎

InnoDB 是事务型数据库的首选引擎，支持事务安全表 (ACID ) ，支持行锁定和外键。
InnoDB 作为默认存储引擎，特性有:

InnoDB 给 MySQL 提供了具有提交、回滚和崩溃恢复能力的事务安全 (ACID 兼容)存储引擎。InnoDB 锁定在行级并且也在 SELECT 语句中提供一个类似 Oracle 的非锁定读。这些功能增加了多用户部署和性能。在 SQL 查询中，可以自由地将 InnoDB 类型的表与其他MySQL 的表的类型混合起来，甚至在同一个查询中也可以混合。
InnoDB 是为处理巨大数据量的最大性能设计。它的 CPU 效率可能是任何其他基于磁盘的关系数据库引擎所不能匹敌的。
InnoDB 存储引擎完全与 MySQL 服务器整合，InnoDB 存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB 将它的表和索引存在一个逻辑表空间中，表空间可以包含数个文件〈或原始磁盘分区) 。这与 MyISAM 表不同，比如在 MyISAM 表中每个表被存在分离的文件中。InnoDB 表可以是任何尺寸,，即使在文件尺寸被限制为 2GB 的操作系统上。
InnoDB 支持外键完整性约束 (FOREIGN KEY) 。存储表中的数据时, 每张表的存储都按主键顺序存放, 如果没有显示在表定义时指定主键，InnoDB 会为每一行生成一个 6B 的ROWID，并以此作为主键。
InnoDB 被用在众多需要高性能的大型数据库站点上。
InnoDB 不创建目录，使用 InnoDB 时，MySQL 将在 MySQL 数据目录下创建一个名为ibdata1 的 10MB 大小的自动扩展数据文件，以及两个名为 ib_logfile0 和 ib_logfilel 的 5MB大小的日志文件。

InnoDB 不创建目录，使用 InnoDB 时，MySQL 将在 MySQL 数据目录下创建一个名为
ibdatal 的 10MB 大小的自动扩展数据文件，以及两个名为 ib_logfile0 和 ib_logfilel 的 SMB
大小的日志文件。

3)、MyISAM引擎

MyISAM 基于 ISAM 的存储引擎，并对其进行扩展。它是在 Web、数据存储和其他应用
环境下最常使用的存储引擎之一。MyISAM 拥有较高的插入、查询速度，但不支持事务。在
MyISAM 主要特性有:

大文件 (达 63 位文件长度) 在支持大文件的文件系统和操作系统上被支持。
当把删除、更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块来自动完成。
每个 MyISAM 表最大索引数是 64，这可以通过重新编译来改变。每个索引最大的列数是 16 个。
最大的键长度是 1000B，这也可以通过编译来改变。对于键长度超过 250B 的情况，一个超过 1024B 的键将被用上。
BLOB 和TEXT 列可以被索引。
NULL 值被允许在索引的列中。这个值占每个键的 0~1 个字节。
所有数字键值以高字节优先被存储以允许一个更高的索引压缩。
每表一个AUTO_INCREMENT 列的内部处理。MyISAM 为 INSERT 和 UPDATE 操作自动更新这一列。这使得 AUTO_INCREMENT 列更快〈至少 10%) 。在序列顶的值被删除之后就不能再利用。
可以把数据文件和索引文件放在不同目录。
每个字符列可以有不同的字符集。
有VARCHAR 的表可以固定或动态记录长度。
VARCHAR 和CHAR 列可以多达 64KB。

使用 MyISAM 引擎创建数据库，将生产 3 个文件。文件的名字以表的名字开始，扩展名指出文件类型， frm文件存储表定义，数据文件的扩展名为.MYD (MYData)，索引文件的扩展名是.MYI MYIndex) 。

4)、MEMORY引擎

MEMORY 存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。MEMORY 主要特性有:

MEMORY 表的每个表可以有多达 32 个索引，每个索引 16 列，以及 500B 的最大键长度。
MEMORY 存储引擎执行 HASH 和 BTREE 索引。
可以在一个MEMORY 表中有非唯一键。
MEMORY 表使用一个固定的记录长度格式。
MEMORY 不支持BLOB 或TEXT 列。
MEMORY 支持 AUTO_INCREMENT 列和对可包含NULL 值的列的索引。
MEMORY 表在所有客户端之间共享 (就像其他任何非 TEMPORARY 表) 。
MEMORY 表内容被存在内存中，内存是 MEMORY 表和服务器在查询处理时的空闲中创建的内部表共享。
当不再需要 MEMORY 表的内容时，要释放被 MEMORY 表使用的内存，应该执行DELETE FROM 或TRUNCATE TABLE，或者删除整个表〈使用DROP TABLE) 。

5)、存储引擎的选择

不同存储引擎都有各自的特点，以适应不同的需求。

如果要提供提交、回滚和崩溃恢复能力的事务安全 (ACID 兼容) 能力，并要求实现并发控制，InnoDB 是个很好的选择；
如果数据表主要用来插入和查询记录，则 MyISAM 引擎能提供较高的处理效率；
如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的 Memory 引擎，MySQL 中使用该引擎作为临时表，存放查询的中间结果；
如果只有 INSERT 和 SELECT 操作，可以选择 Archive 引擎，Archive 存储引擎支持高并发的插入操作，但是本身并不是事务安全的。Archive 存储引擎非常适合存储归档数据，如记录日志信息可以使用 Archive 引擎。

使用哪一种引擎要根据需要灵活选择, 一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求。使用合适的存储引擎，将会提高整个数据库的性能。

顺便说一下Mysql中单行注释是#，而不是--。

二、数据表的基本操作

1、创建数据表

use test_db;
create table tb_emp1
(
    id int(11),
    name varchar(15),
    deptID int(11),
    salary     float
);

使用下面语句查看此数据库存在的表

show tables;

1)、主键约束

主键，又称主码，是表中一列或多列的组合。主键约束〈Primary Key Constraint) 要求主键列的数据唯一，并且不允许为空!= null。主键能够唯一地标识表中的一条记录，可以结合外键来定义不同数据表之间的关系，并且可以加快数据库查询的速度。主键和记录之间的关系如同身份证和人之间的关系，它们之间是一一对应的。主键分为两种类型: 单字段主键和多字段联合主键。

单字段主键；
在定义完所有列之后定义主键；
多字段联合主键；

单字段约束:

create table tb_emp2
(
    id int(11) primary key,
    name varchar(15),
    deptID int(11),
    salary     float
);

后面约束:

create table tb_emp3
(
    id int(11),
    name varchar(15),
    deptID int(11),
    salary     float,
    primary key(id)
);

联合约束：假设没有主键id，可以通过name和deptID来确定一个唯一的员工。

create table tb_emp4
(
    id int(11),
    name varchar(15),
    deptID int(11),
    salary     float,
    primary key(name,deptID)
);

2)、外键约束

外键用来在两个表的数据之间建立链接，它可以是一列或者多列。一个表可以有一个或多个外键。外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。
外键 : 首先它是表中的一个字段，它可以不是本表的主键，但对应另外一个表的主键。外键主要作用是保证数据引用的完整性，定义外键后，不允许删除在另一个表中具有关联关系的行。外键的作用是保持数据的一致性、完整性。例如，部门表 tb_dept 的主键是id，在员工表tb_emp5中有一个键 deptId 与这个 id 关联。

有关主表和从表:

主表(父表) : 对于两个具有关联关系的表而言，相关联字段中主键所在的那个表即是主表。
从表(子表) : 对于两个具有关联关系的表而言，相关联字段中外键所在的那个表即是从表。

需要注意:

子表的外键必须要关联父表的主键；
相关联的数据类型必须匹配；
先删子表，再删父表；

下面的例子tb_emp5(员工表)中的deptID关联部门表中的ID(主键)：

//父表
create table tb_dept1
(
    id int(11)primary key,
    name varchar(22) not null,
    location varchar(50)
)

//子表
create table tb_emp5
(
    id int(11) primary key,
    name varchar(25),
    deptID int(11),
    salary float,
    constraint fk_emp5_dept foreign key(deptID) references tb_dept1(id)
)

3)、非空约束

非空约束指定的字段不能为空，如果添加数据的时候没有指定值，会报错。

create table tb_emp6
(
    id int(11) primary key,
    name varchar(15) not null,
    deptID int(11),
    salary     float
);

4)、唯一性约束

唯一性要求该列唯一；
允许为空，但只能出现一个空值；
唯一性可以确保一列或几列不出现重复值；

create table tb_dept2
(
    id int(11)primary key,
    name varchar(22) unique,
    location varchar(50)
);

create table tb_dept3
(
    id int(11)primary key,
    name varchar(22),
    location varchar(50),
    constraint N_uq unique(name)  #N_uq是约束名
);

注意UNIQUE和主键约束(PRIMARY KEY )的区别:

一个表中可以有多个字段声明为UNIQUE，但只能有一个PRIMARY KEY 声明；
声明为 PRIMAY KEY 的列不允许有空值，但是声明为 UNIQUE的字段允许空值 (NULL) 的存在。

5)、默认约束

指定了默认约束之后，如果没有指定值，就用默认的。

create table tb_emp7
(
    id int(11) primary key,
    name varchar(15) not null,
    deptID int(11) default 111,
    salary     float
);

6)、设置表的属性自加

在数据库应用中，经常希望在每次插入新记录时，系统自动生成字段的主键值。可以通过为表主键添加AUTO_INCREMENT 关键字来实现。
默认的，在MySQL 中 AUTO _INCREMENT的初始值是 1，每新增一条记录，字段值自动加 1。
一个表只能有一个字段使用AUTO_INCREMENT 约束，且该字段必须为主键的一部分。
AUTO_INCREMENT 约束的字段可以是任何整数类型 (TINYINT、SMALLIN、INT、BIGINT 等) 。

create table tb_emp8
(
    id int(11) primary key auto_increment,
    name varchar(15) not null,
    deptID int(11),
    salary     float
);

7)、查看表的结构

desc可以查看表的字段名，数据类型，是否为主键，是否默认值。

desc tb_emp8;

效果如图

查看表的详细结构，可以看储存引擎，和字符编码

show create table tb_emp8;

2、修改数据表

1)、修改表名

将表tb_dept3改为tb_deptment3

alter table tb_dept3 rename tb_deptment3;

查看数据库中的表

show tables;

修改表名不会改变结构，desc前后结果一样。

2)、修改字段的数据类型

# 修改表字段的数据类型,把name列的数据类型改为varchar(33)
alter table tb_dept1 modify name varchar(33);

3)、修改字段名

# 修改表的字段名,不改数据类型 将tb_dept1中的location字段改成loc
alter table tb_dept1 change location loc varchar(50);

# 修改表的字段名,并且改变数据类型, 同时改变数据类型
alter table tb_dept1 change loc location varchar(60);

change也可以只改变数据类型，但是一般不要轻易改变数据类型。

4)、添加字段

有三种添加方式:

①默认在最后面添加；
②在第一个位置添加first；
③和指定的位置添加after；

# 添加字段(默认在最后面添加)
alter table tb_dept1 add managerID int(10);

# 添加字段(默认在最后面添加)(非空约束)
alter table tb_dept1 add column1 int(10) not null;

# 添加字段(在第一个位置添加)
alter table tb_dept1 add column2 int(10) first;

# 添加字段(在指定位置后面添加)
alter table tb_dept1 add column3 int(10) after name;

5)、删除字段

# 删除字段, 删除tb_dept1的column3字段
alter table tb_dept1 drop column3;

6)、修改字段的排列位置

# 修改字段的排列位置(改到第一个位置)
alter table tb_dept1 modify column1 int(10) first;
# 修改字段的位置为指定的位置
alter table tb_dept1 modify column2 int(10) after name;

7)、更改表的储存引擎

# 查看数据表的定义
show create table tb_deptment3;
# 更改数据表的引擎
alter table tb_deptment3 engine = MyISAM;

8)、删除表的外键约束

create table tb_emp9
(
    id int(11)primary key,
    deptID int(11),
    name varchar(25),
    salary float,
    constraint fk_emp9_dept foreign key(deptID) references tb_dept1(id)
)

# 删除外键约束
alter table tb_emp9 drop foreign key fk_emp9_dept;

3、删除数据表

# 删除表
drop table if exists tb_emp9;

注意注意: 删除有关联的数据表的父表的时候，先删除外键再删除父表

4、查询表中数据

查询语法：

select
    字段列表
from
    表名列表
where
    条件列表
group by
    分组字段
having
    分组后条件
order by
    排序字段
limit
    分页限定

查询多个字段

select 字段列表 from 表名;
select * from 表名; -- 查询所有数据

-- 基础查询 ===========
-- 查询 name age 这两列
select name,age from stu;

-- 查询所有列的数据，列名的列表可以使用*代替，但是不建议使用
select * from stu;

-- 查询地址信息，看同学都来自哪一个城市
select address from stu;

-- 查询姓名，数学和英语成绩
select name,math,english from stu;
select name,math as 数学成绩,english as 英语成绩 from stu;

去除重复记录

select distinct 字段列表 from 表名;

select distinct address from stu;  --去重

起别名

as: -- as也可以省略

select name,math,english from stu;
select name,math as 数学成绩,english as 英语成绩 from stu;

5、条件查询-WHERE

条件查询语法

select 字段列表 from 表名 where 条件列表

条件：

操作符	描述
=	等于
<> 或 !=	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN...AND...	在某个范围内（都包含）
LIKE 占位符	搜索某种模式，模糊查询 ==“_单个任意字符”== ==“%多个任意字符”==
IN(...)	多选一
IS NULL	是NULL
AND 或 &&	并且
OR 或 \	\	或者

示例：

select * from stu;

-- 条件查询
-- 1.查询年龄大于20岁的学员信息
select * from stu where age > 20;

-- 查询年龄大于等于20岁的学员信息
select * from stu where age >= 20;

-- 查询 大于等于20岁 并且 年龄 小于等于30岁 的学员信息
select * from stu where age >= 20 and age <= 30;
select * from stu where age between 20 and 30;

-- 查询入学日期在 '1998-09-01' 到 '1999-09-01'之间的学员信息
select * from stu where hire_date between'1998-09-1' and '1999-09-01';

-- 查询年龄等于18岁的学员信息
select * from stu where age=18;

-- 查询年龄不等于18岁的学员信息
select * from stu where age!=18;
select * from stu where age<>18;

-- 查询年龄等于18岁或者年龄等于20岁或者年龄等于22岁的学员信息
select * from stu where age=18 or age=20 or age=22;
select * from stu where age in(18,20,22);

-- 查询英语成绩为null的学员信息
-- 注意：NULL值的比较不能使用 = , !=比较，需要使用 is / is not比较
select * from stu where english=NULL; -- 错误的，查不到信息
select * from stu where english is NULL;
select * from stu where english is not NULL;

-- 模糊查询 like ==========
/*
   通配符：
     1. _: 代表单个任意字符
     2. %: 代表任意个数字符
*/

-- 1. 查询姓'马'的学员信息
select * from stu where name like '马%';

-- 2. 查询第二个字是'花'的学员信息
select * from stu where name like '_化%';

-- 3. 查询名字中包含'德'的学员信息
select * from stu where name like '%德%';

6、排序查询-ORDER BY

排序查询语法

select 字段列表 from 表名 order by 排序字段名1 [排序方式1],排序字段名2 [排序方式2] ...;

排序方式：

ASC：升序排列（默认值）
DESC：降序排列

注意：如果有多个排序条件，当前边的条件值一样时，才会根据第二条件进行排序

/*
   排序查询：
        语法：select 字段列表 from 表名 order by 排序字段名1 [排序方式1],排序字段名2 [排序方式2] ...;
            排序方式：
                ASC：升序排列（默认值）
                    DESC：降序排列
*/

-- 1.查询学生信息，安装年龄升序排列
select * from stu order by age ASC;
select * from stu order by age;

-- 2. 查询学生信息，按照数学成绩降序排列
select * from stu order by math DESC;

-- 3. 查询学生信息，按照数学成绩降序排列，如果数学成绩一样，再按照英语成绩升序排列
select * from stu order by math DESC,english ASC;

注意：如果有多个排序条件，当前边的条件值一样时，才会根据第二条件进行排序

7、分组查询-GROUP BY

聚合函数

概念：将一列数据作为一个整体，进行纵向计算
聚合函数的分类：

函数名	功能
cout(列名)	统计数量（一般选用不为NULL的列）
max(列名)	最大值
min(列名)	最小值
sum(列名)	求和
avg(列名)	平均值

聚合函数语法：

select 聚合函数名(列名) from 表;

注意：NULL 值不参与所有聚合函数的运算

/*
sum():求和，且求和的列值必须为number数据类型
count()：统计记录记录数，且不能为空
   1. 主键：非空且唯一
     2. *
max()：求一组值中的最大值，列值的类型可以为数据类型也可以为字符类型
min()：求一组值中的最小值，列值的类型可以为数据类型也可以为字符类型
avg()：求平均值，且求平均值的列值必须为number数据类型
*/

-- 1. 统计班级一共有多少个学生
select count(id) from stu;  -- 8
select count(english) from stu; -- 7

-- 2. 查询数学成绩的最高分
select max(math) from stu;

-- 3. 查询数学成绩的最低分
select min(math) from stu;

-- 4. 查询数学成绩的总分
select sum(math) from stu;

-- 5. 查询数学成绩的平均分
select avg(math) from stu;

-- 6. 查询英语成绩的最低分
select min(english) from stu;

分组查询语法

select 字段列表 from 表名 [Where 分组条件前限定] group by 分组字段名 [Having 分组后条件过滤];

注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义

/*
    分组函数
            select 字段列表 from 表名 [where 分组前条件限定] group by分组字段名 [having 分组后条件过滤]
*/

-- 1. 查询男同学和女同学各自的数学平均分
select sex,avg(math) from stu group by sex;

-- 2. 查询男同学和女同学各自的数学平均分以及各自人数
select sex,avg(math),count(*) from stu group by sex;

-- 3. 查询男同学和女同学各自的数学平均分以及各自人数要求分数低于70分以下的不参与分组
select sex,avg(math),count(*) from stu where math>=70 group by sex;

-- 4. 查询男同学和女同学各自的数学平均分以及各自人数要求分数低于70分以下的不参与分组,分组之后人数大于2 
select sex,avg(math),count(*) from stu where math>=70 group by sex having count(*)>2;

where 和 having 区别：

执行时机不一样：where 是分组之前进行限定，不满足 where 条件，则不参与分组，而 having 是分组之后对结果进行过滤
可判断条件不一样：where 不能对聚合函数进行判断，having 可以

执行顺序：where > 聚合函数 > having

8、分页查询-LIMIT

分页查询语法

select 字段列表 from 表名 LIMIT 起始索引,查询条目数;

起始索引：从0开始

计算公式：起始索引 = (当前页码-1) * 每页显示的条数

注意：

分页查询 LIMIT 是 MySQL 数据库的特有语言
Oracle 分页查询使用 rownumber
SQL Server 分页查询使用 top

示例：

/*
    Select 字段列表 From 表名 Limit 起始索引,查询条目数
        起始索引：从0开始
*/

select * from stu;
-- 1. 从0开始查询，查询3条数据
select * from stu limit 0,3;

-- 2. 每页显示3条数据，查询第一页数据
select * from stu limit 0,3;

-- 3. 每页显示3条数据查询第2页数据
select * from stu limit 3,3;

-- 4. 每页显示3条数据查询第3页数据
select * from stu limit 6,3

-- 起始索引 = (当前页码-1) * 每页显示的条目数

总结：

9 、多表查询

笛卡尔积：取A,B集合所有组合情况
多表查询：从多张表查询数据
- 连接查询
  - 内连接：相当于查询A B交集数据
  - 外连接：
    1. 左外连接：相当于查询A表所有数据的交集部分数据
    2. 右外连接：相当于查询B表所有数据的交集部分数据
- 子查询

1)、内连接

内连接查询语法

-- 隐式内链接
select 字段列表 from 表1,表2... where 条件;

-- 显式内连接
select 字段列表 from 表1 [insert] join 表2 on 条件;

内连接相当于查询A B交集数据

示例：

drop table if exists emp;
drop table if exists dept;

# 创建部门表
create table dept(
   did int primary key auto_increment,
     dname varchar(20)
);

# 创建员工表
create table emp(
   id int primary key auto_increment, 
     name varchar(10),
     gender char(1),
     salary double, -- 工资
     join_date date, -- 入职日期
     dep_id int,
     foreign key (dep_id) references dept(did) -- 外键，关联部门表（部门表的主键）
);

-- 添加部门的数据
insert into dept (dname) values ('研发部'),('市场部'),('财务部'),('销售部');

-- 添加员工数据
insert into emp (name,gender,salary,join_date,dep_id) VALUES
('孙悟空','男',7200,'2013-02-24',1),
('猪八戒','男',3600,'2010-12-02',2),
('唐僧','男',9000,'2008-08-08',2),
('白骨精','女',5000,'2015-10-07',3),
('蜘蛛精','女',4500,'2011-03-14',1),
('小白龙','男',2500,'2011-02-14',null);

select * from dept;
select * from emp;

-- 多表查询
select * from emp,dept;

-- 笛卡尔积：有A,B两个集合 取A,B所有的组合情况

-- 需要消除无效数据

-- 怎么消除？查询emp 和 dept 的数据，emp.dep_id = dept.did
-- 隐式内连接
select * from emp,dept where emp.dep_id = dept.did;

-- 查询emp的name,gender,dept表的dname
select emp.name,emp.gender,dept.dname from emp,dept where emp.dep_id = dept.did;

-- 给表起别名
select t1.name,t1.gender,t2.dname from emp t1 , dept t2 where t1.dep_id = t2.did;

-- 显式内连接
select * from emp inner join dept on emp.dep_id = dept.did;

select * from emp join dept on emp.dep_id = dept.did;

2)、外连接

外连接查询语法

-- 左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件;

-- 右外连接
select 字段列表 from 表1 right [outer] join 表2 on 条件;

左外连接：相当于查询A表所有数据的交集部分数据
右外连接：相当于查询B表所有数据的交集部分数据

示例：

drop table if exists emp;
drop table if exists dept;

# 创建部门表
create table dept(
   did int primary key auto_increment,
     dname varchar(20)
);

# 创建员工表
create table emp(
   id int primary key auto_increment, 
     name varchar(10),
     gender char(1),
     salary double, -- 工资
     join_date date, -- 入职日期
     dep_id int,
     foreign key (dep_id) references dept(did) -- 外键，关联部门表（部门表的主键）
);

-- 添加部门的数据
insert into dept (dname) values ('研发部'),('市场部'),('财务部'),('销售部');

-- 添加员工数据
insert into emp (name,gender,salary,join_date,dep_id) VALUES
('孙悟空','男',7200,'2013-02-24',1),
('猪八戒','男',3600,'2010-12-02',2),
('唐僧','男',9000,'2008-08-08',2),
('白骨精','女',5000,'2015-10-07',3),
('蜘蛛精','女',4500,'2011-03-14',1),
('小白龙','男',2500,'2011-02-14',null);

select * from dept;
select * from emp;

-- 左外连接
-- 查询emp表中所有的数据和对应的部门信息
select * from emp left join dept on emp.dep_id = dept.did;

-- 右外连接
-- 查询dept表中所有的数据和对应的员工信息
select * from emp right join dept on emp.dep_id = dept.did;

-- 一般情况下我们使用左外连接

3)、子查询

子查询概念：

查询中嵌套查询，称嵌套查询为子语句

子查询根据查询结果的不同，作用不同：

单行单列
多行单列
多行多列

子查询根据查询结果的不同，作用不同：
- 单行单列：作为条件值，使用 = != > < 等进行条件判断
```
select 字段列表 from 表 where 字段名 = (子查询);
```

多行单列：作为条件值，使用in等关键字进行条件判断

select 字段列表 from 表 where 字段名 in (子查询);

多行多列：作为虚拟表

select 字段列表 from (子查询) where 条件;

10、约束

约束的概念
- 约束是作用于表中列上的规则，用于限制加入表的数据
- 约束的存在保证了数据库中数据的正确性、有效性和完整性
约束的分类

约束名称	描述	关键字
非空约束	保证列中所有数据不能有NULL值	NOT NULL
唯一约束	保证列中所有数据各不相同	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
检查约束	保证列中的值满足某一条件	CHECK
默认约束	保存数据时，未指定值则采用默认值	DEFAULT
外键约束	外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性	FOREIGN KEY

Tips：MySQL 不支持检查约束

自动增长：

-- 演示自动增长 auto_increment：当列是数字类型且是唯一约束
INSERT INTO emp ( ename, joindate, salary, bonus )
VALUES
    ( '赵六', '1999-11-11', 8800, NULL );

INSERT INTO emp ( id, ename, joindate, salary, bonus )
VALUES
    ( null, '赵六2', '1999-11-11', 8800, NULL );

非空约束

概念：

非空约束用于保证列中所有数据不能有 NULL 值

语法：

添加约束

-- 创建表时添加非空约束
creat table 表名 (
   列名 数据类型 NOT NULL,
   ... 
);

-- 建完表后添加非空约束
alter table 表名 modify 字段名 数据类型 not null;

删除约束

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

示例：

DROP TABLE
IF
    EXISTS emp;-- 员工表
CREATE TABLE emp (
    id INT PRIMARY KEY auto_increment,-- 员工id，主键且自增长
    ename VARCHAR ( 50 ) NOT NULL UNIQUE,-- 员工姓名，非空且唯一
    joindate date NOT NULL,-- 入职日期，非空
    salary DOUBLE ( 7, 2 ) NOT NULL,-- 工资，非空
    bonus DOUBLE ( 7, 2 ) DEFAULT 0 -- 奖金，如果没有奖金默认为0
    
);

INSERT INTO emp ( id, ename, joindate, salary, bonus )
VALUES
    ( 1, '张三', '1999-11-11', 8800, 5000 );

INSERT INTO emp ( id, ename, joindate, salary, bonus )
VALUES
    ( 2, '李四', '1999-11-11', 8800, 5000 );

-- 演示非空约束
INSERT INTO emp ( id, ename, joindate, salary, bonus )
VALUES
    ( 2, '李四', '1999-11-11', 8800, 5000 );

1)、唯一约束

概念：

唯一约束用于保证列中所有数据各不相同

语法：

添加约束

-- 创建表时添加唯一约束
creat table 表名 (
    列名 数据类型 unique [auto_increment],
    -- auto_increment：当不指定值时自动增长
   ... 
);

creat table 表名 (
   列名 数据类型,
   ...
   [constraint][约束名称] unique(列名)
);

-- 建完表后添加唯一约束
alter table 表名 modify 字段名 数据类型 unique;

删除约束

alter table 表名 drop index 字段名

示例：

DROP TABLE
IF
    EXISTS emp;-- 员工表
CREATE TABLE emp (
    id INT PRIMARY KEY auto_increment,-- 员工id，主键且自增长
    ename VARCHAR ( 50 ) NOT NULL UNIQUE,-- 员工姓名，非空且唯一
    joindate date NOT NULL,-- 入职日期，非空
    salary DOUBLE ( 7, 2 ) NOT NULL,-- 工资，非空
    bonus DOUBLE ( 7, 2 ) DEFAULT 0 -- 奖金，如果没有奖金默认为0
    
);

INSERT INTO emp ( id, ename, joindate, salary, bonus )
VALUES
    ( 1, '张三', '1999-11-11', 8800, 5000 );

INSERT INTO emp ( id, ename, joindate, salary, bonus )
VALUES
    ( 2, '李四', '1999-11-11', 8800, 5000 );

-- 演示唯一约束
INSERT INTO emp ( id, ename, joindate, salary, bonus )
VALUES
    ( 3, '李四', '1999-11-11', 8800, 5000 );

2)、主键约束

概念：
- 主键是一行数据的唯一标识，要求非空且唯一
- 一张表只能有一个主键
语法：
1. 添加约束

-- 创建表时添加主键约束
creat table 表名 (
   列名 数据类型 primary key [auto_increment],
   ... 
);

creat table 表名 (
   列名 数据类型,
   ...
   [constraint][约束名称] primary key(列名)
);

-- 建完表后添加主键约束
alter table 表名 add primary key(字段名);

删除约束

alter table 表名 drop primary key;

示例：

DROP TABLE
IF
    EXISTS emp;-- 员工表
CREATE TABLE emp (
    id INT PRIMARY KEY,-- 员工id，主键且自增长
    ename VARCHAR ( 50 ) NOT NULL UNIQUE,-- 员工姓名，非空且唯一
    joindate date NOT NULL,-- 入职日期，非空
    salary DOUBLE ( 7, 2 ) NOT NULL,-- 工资，非空
    bonus DOUBLE ( 7, 2 ) DEFAULT 0 -- 奖金，如果没有奖金默认为0
    
);

select * from emp;

INSERT INTO emp ( id, ename, joindate, salary, bonus )
VALUES
    ( 1, '张三', '1999-11-11', 8800, 5000 );

-- 演示主键约束，要求唯一且非空
INSERT INTO emp ( id, ename, joindate, salary, bonus )
VALUES
    ( NULL, '张三', '1999-11-11', 8800, 5000 );

INSERT INTO emp ( id, ename, joindate, salary, bonus )
VALUES
    ( 1, '张三', '1999-11-11', 8800, 5000 );

INSERT INTO emp ( id, ename, joindate, salary, bonus )
VALUES
    ( 2, '李四', '1999-11-11', 8800, 5000 );

默认约束

概念：

保存数据时，未指定值采用默认值

语法：

添加约束

-- 创建表时添加默认约束
create table 表名(
    列名 数据类型 default 默认值,
    ...
);

-- 建完表后添加默认约束
alter table 表名 alter 列名 set default 默认值

删除约束

alter table 表名 alter 列名 drop default;

示例：

DROP TABLE
IF
    EXISTS emp;-- 员工表
CREATE TABLE emp (
    id INT PRIMARY KEY auto_increment,-- 员工id，主键且自增长
    ename VARCHAR ( 50 ) NOT NULL UNIQUE,-- 员工姓名，非空且唯一
    joindate date NOT NULL,-- 入职日期，非空
    salary DOUBLE ( 7, 2 ) NOT NULL,-- 工资，非空
    bonus DOUBLE ( 7, 2 ) DEFAULT 0 -- 奖金，如果没有奖金默认为0
    
);


INSERT INTO emp ( id, ename, joindate, salary, bonus )
VALUES
    ( 1, '张三', '1999-11-11', 8800, 5000 );

INSERT INTO emp ( id, ename, joindate, salary, bonus )
VALUES
    ( 2, '李四', '1999-11-11', 8800, 5000 );

-- 演示默认约束
INSERT INTO emp ( id, ename, joindate, salary )
VALUES
    ( 3, '王五', '1999-11-11', 8800 );

INSERT INTO emp ( id, ename, joindate, salary, bonus )
VALUES
    ( 4, '赵六', '1999-11-11', 8800, NULL );

3)、外键约束

概念：

外键用来让连个表的数据之间建立链接，保证数据的一致性和完整性

语法：

添加约束

-- 创建表时添加外键约束
create table 表名(
   列名 数据类型,
   ...
   [constraint] [外键名称] foreign key(外键列名) references 主表(主表列名)
);

-- 建完表后添加外键约束
alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称)

删除约束

alter table 表名 drop foreign key 外键名称;

示例：

-- 删除表
drop table if exists emp;
drop table if exists dept;

-- 部门表
create table dept(
  id int primary key auto_increment,
    dep_name varchar(20),
    addr varchar(20)
);

-- 员工表
create table emp(
  id int primary key auto_increment,
    name varchar(20),
    age int,
    dep_id int,
    
    -- 添加外键 dep_id,关联dept表的id主键
    constraint fk_emp_dept foreign key(dep_id) references dept(id)
);

-- 添加2个部门
insert into dept(dep_name,addr) VALUES
('研发部','广州'),('销售部','深圳');

-- 添加员工,dep_id 表示员工所在的部门
insert into emp(`NAME`,age,dep_id) VALUES
('张三',20,1),
('李四',20,1),
('王五',20,1),
('赵六',20,2),
('孙七',22,2),
('周八',18,2);

-------------------
select * from emp;

-- 删除外键
alter table emp drop foreign key fk_emp_dept;

-- 建完表后，添加外键
alter table emp add constraint fk_emp_dept foreign key(dep_id) references dept(id)

-- 查另一张表
select * from dept;

试图删除主表的某列数据：

三、数据库设计

1)、软件的研发步骤

graph LR
    A(需求分析) --> B(设计) --> C(编码) --> D(测试) --> E(安装部署)

数据库设计概念

数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型
建立数据库中的表结构以及表与表之间的关联关系的过程
有哪些表？表里有哪些字段？表和表之间有什么关系？

数据库设计的步骤
1. 需求分析（数据是什么？数据具有哪些属性？数据与属性的特点是什么？）
2. 逻辑分析（通过ER图对数据库进行逻辑建模，不需要我们考虑所选用的数据库管理系统）
3. 物理设计（根据数据库自身的特点，把逻辑设计转换为物理设计
4. 维护设计（1.对新的需求进行建表; 2.表优化）

表关系

一对一：

如：用户和用户详情

一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另外一张表，用于提升查询性能

一对多（多对一）：

如：部门和员工

一个部门对应多个员工，一个员工对应一个部门

多对多：

如：商品和订单

一个商品对应多个订单，一个订单包含多个商品

2)、表关系之一对多

一对多（多对一）

如：部门表和员工表