MySQL-学习笔记

最后更新于 2022-09-04 961 次阅读


数据库 (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 数据模型

关系型数据库

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

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

      SQL

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

SQL 通用语法

  1. SQL 语句可以当行或多行书写,以分号结尾
  2. MySQL 数据库的 SQL 语句不区分大小写,关键字建议使用大写
  3. 注释
    • 单行注释:==—— 注释内容== 或 ==# 注释内容(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 为 INSERTUPDATE 操作自动更新这一列。这使得 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,可以通过namedeptID来确定一个唯一的员工。

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;

image-20220222112014214

  • 去除重复记录
select distinct 字段列表 from 表名;

select distinct address from stu;  --去重

image-20220222112548533 image-20220222112622865

  • 起别名
as: -- as也可以省略

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

image-20220222112121460 image-20220222112719395

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;

image-20220222142624065


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

image-20220222142636522


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

image-20220222142539681


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

image-20220222142737863


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

image-20220222142842413


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

image-20220222142911778


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

image-20220222142940122


-- 查询英语成绩为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;

image-20220222143029364
image-20220222143052430
image-20220222143128540


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

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

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

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

image-20220222143455079
image-20220222143556093
image-20220222143631681


6、排序查询-ORDER BY

  1. 排序查询语法
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;

image-20220222152341135


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

image-20220222152531778


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

image-20220222152728674

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


7、分组查询-GROUP BY

聚合函数

  1. 概念:将一列数据作为一个整体,进行纵向计算
  2. 聚合函数的分类:
函数名 功能
cout(列名) 统计数量(一般选用不为NULL的列)
max(列名) 最大值
min(列名) 最小值
sum(列名) 求和
avg(列名) 平均值
  1. 聚合函数语法:
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

image-20220223180249193 image-20220223180300401


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

image-20220223180346822


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

image-20220223180458954


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

image-20220223180523784


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

image-20220223180547155


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

image-20220223180609947


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

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

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

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

image-20220223175710568


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

image-20220223175811526


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

image-20220223175842569


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

image-20220223175914288


where 和 having 区别:

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

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

8、 分页查询-LIMIT

  1. 分页查询语法
select 字段列表 from 表名 LIMIT 起始索引,查询条目数;
  • 起始索引:从0开始

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

注意:

  1. 分页查询 LIMIT 是 MySQL 数据库的特有语言
  2. Oracle 分页查询使用 rownumber
  3. 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) * 每页显示的条目数

image-20220223213733495

总结:

9 、多表查询

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

image-20220225163206710

1)、 内连接

  • 内连接查询语法
-- 隐式内链接
select 字段列表 from 表1,表2... where 条件;

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

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

image-20220225163206710

示例:

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 条件;


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

image-20220225163206710 

示例:

~~~mysql
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)、子查询

子查询概念:

  • 查询中嵌套查询,称嵌套查询为子语句

子查询根据查询结果的不同,作用不同:

  • 单行单列
  • 多行单列
  • 多行多列
  1. 子查询根据查询结果的不同,作用不同:

    • 单行单列:作为条件值,使用 = != > < 等进行条件判断
    select 字段列表 from 表 where 字段名 = (子查询);
  • 多行单列:作为条件值,使用in等关键字进行条件判断
select 字段列表 from 表 where 字段名 in (子查询);
  • 多行多列:作为虚拟表
select 字段列表 from (子查询) where 条件;

10、 约束

  1. 约束的概念
    • 约束是作用于表中列上的规则,用于限制加入表的数据
    • 约束的存在保证了数据库中数据的正确性、有效性和完整性
  2. 约束的分类
约束名称 描述 关键字
非空约束 保证列中所有数据不能有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 );

image-20220224000024329

  1. 非空约束

概念:

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

语法:

  1. 添加约束
-- 创建表时添加非空约束
creat table 表名 (
   列名 数据类型 NOT NULL,
   ... 
);
-- 建完表后添加非空约束
alter table 表名 modify 字段名 数据类型 not null;
  1. 删除约束
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 );

image-20220223234147926

1)、唯一约束

概念:

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

语法:

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

creat table 表名 (
   列名 数据类型,
   ...
   [constraint][约束名称] unique(列名)
);
-- 建完表后添加唯一约束
alter table 表名 modify 字段名 数据类型 unique;
  1. 删除约束
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 );

image-20220223235129398

2)、主键约束

  1. 概念:
    • 主键是一行数据的唯一标识,要求非空且唯一
    • 一张表只能有一个主键
  2. 语法:
    1. 添加约束
-- 创建表时添加主键约束
creat table 表名 (
   列名 数据类型 primary key [auto_increment],
   ... 
);

creat table 表名 (
   列名 数据类型,
   ...
   [constraint][约束名称] primary key(列名)
);
-- 建完表后添加主键约束
alter table 表名 add primary key(字段名);
  1. 删除约束
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 );

image-20220224000642589

  1. 默认约束

概念:

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

语法:

  1. 添加约束
-- 创建表时添加默认约束
create table 表名(
    列名 数据类型 default 默认值,
    ...
);
-- 建完表后添加默认约束
alter table 表名 alter 列名 set default 默认值
  1. 删除约束
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 );

image-20220223235721291

3)、外键约束

概念:

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

语法:

  1. 添加约束
-- 创建表时添加外键约束
create table 表名(
   列名 数据类型,
   ...
   [constraint] [外键名称] foreign key(外键列名) references 主表(主表列名)
);
-- 建完表后添加外键约束
alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称)
  1. 删除约束
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;

image-20220224162044815

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

image-20220224162104254

三、数据库设计

1)、软件的研发步骤

graph LR
    A(需求分析) --> B(设计) --> C(编码) --> D(测试) --> E(安装部署)
  1. 数据库设计概念
  • 数据库设计就是根据业务系统的具体需求,结合我们所选用的DBMS,为这个业务系统构造出最优的数据存储模型

  • 建立数据库中的表结构以及表与表之间的关联关系的过程

  • 有哪些表?表里有哪些字段?表和表之间有什么关系?

  1. 数据库设计的步骤

    1. 需求分析(数据是什么?数据具有哪些属性?数据与属性的特点是什么?)

    2. 逻辑分析(通过ER图对数据库进行逻辑建模,不需要我们考虑所选用的数据库管理系统)

    3. 物理设计(根据数据库自身的特点,把逻辑设计转换为物理设计

    4. 维护设计(1.对新的需求进行建表; 2.表优化)

表关系

  • 一对一:

    如:用户 和 用户详情

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

  • 一对多(多对一):

    如:部门 和 员工

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

  • 多对多:

    如:商品 和 订单

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

2)、表关系之一对多

一对多(多对一)

如:部门表 和 员工表

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

实现方式:在多的一方建立外键,指向一的一方的主键

image-20220224171059643

3)、表关系之多对多

多对多:

如:商品 和 订单

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

实现方式:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键

image-20220224171532571

示例:

/*多对多:
    如:商品 和 订单
    一个商品对应多个订单,一个订单包含多个商品
    实现方式:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
*/

-- 删除表 
drop table if exists tb_order_goods;
drop table if exists tb_order;
drop table if exists tb_goods;

-- 订单表
create table tb_order(
  id int primary key auto_increment,
    payment double(10, 2),
    payment_type tinyint,
    status tinyint
);

-- 商品表
create table tb_goods(
  id int primary key auto_increment,
    title varchar(100),
    price double(10,2)
);

-- 订单商品中间表
create table tb_order_goods(
  id int primary key auto_increment,
  order_id int,
    goods_id int,
    count int
);

-- 建完表后,添加外键
alter table tb_order_goods add constraint fk_order_id foreign key(order_id) references tb_order(id);
alter table tb_order_goods add constraint fk_goods_id foreign key(goods_id) references tb_goods(id);

image-20220224173137446

4)、表关系之一对一

一对一:

如:用户 和 用户详情

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

实现方式:在任意一方加入外键,关联另一方主键,并且设置外键为==唯一(UNIQUE)==

image-20220224173333529

image-20220224173406525

5)、 总结

  • 一对多实现方式

    • 在多的一方建立外键关联一的一方主键
  • 多对多实现方式

    • 建立第三张中间表
    • 中间表至少包含2个外键,分别关联双方主键
  • 一对一实现方式

    • 在任意一方建立外键,关联对方主键,并且设置外键唯一

四、 事务

数据库的事务 (Transaction) 是一种机制、一个操作序列,包含了==一组数据库操作命令== 事务把所有的命令作为一个整体一起向系统提交或撤销操作请求,即这一组数据库命令 ==要么同时成功,要么同时失败==

事务是一个不可分割的工作逻辑单元

简单来说就是防止异常事件影响数据库中的值

image-20220228132145196

-- 开启事务
start transaction;
或者 begin;

-- 提交事务
commit;

-- 回滚事务
rollback;

image-20220228222545295

示例:

drop table if exists account;

-- 创建账户表
create table account (
   id int primary key auto_increment,
     name varchar(10),
     money double(10, 2)
);

-- 添加数据
insert into account(name,money) VALUES ('张三',1000),('李四',1000);

select * from account;

-- 开启事务
begin;
-- 转账操作
-- 1. 查询李四的余额
select name,money from account where name='李四';

-- 李四金额-500
update account set money = money - 500 where name='李四';

假设这里出现错误

-- 张三金额+500
update account set money = money + 500 where name='张三';

-- 提交事务
commit;

-- rollback 回滚事务
rollback;

事务四大特征 (ACID)

  • 原子性(Atomicty):事务是不可分割的最小操作单位,要么同时成功,要么同时失败
  • 一致性(Consistency):事务完成时,必须使所有的数据都保持一致状态
  • 隔离性(Isolation):多个事务之间,操作的可见性
  • 持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的

五、 图形化客户端工具

Navicat

  • Navicat for MySQL 是管理和开发 MySQL 或 MariaDB 的理想解决方案
  • 这套全面的前端工具为数据库管理、开发和维护提供了一款直观而强大的图形界面

Navicat 官方网站