数据库 (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交集数据
- 外连接:
- 左外连接:相当于查询A表所有数据的交集部分数据
- 右外连接:相当于查询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 条件;
1. 左外连接:相当于查询A表所有数据的交集部分数据
2. 右外连接:相当于查询B表所有数据的交集部分数据
示例:
~~~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)、子查询
子查询概念:
- 查询中嵌套查询,称嵌套查询为子语句
子查询根据查询结果的不同,作用不同:
- 单行单列
- 多行单列
- 多行多列
-
子查询根据查询结果的不同,作用不同:
- 单行单列:作为条件值,使用
= != > <
等进行条件判断
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)、主键约束
- 概念:
- 主键是一行数据的唯一标识,要求非空且唯一
- 一张表只能有一个主键
- 语法:
- 添加约束
-- 创建表时添加主键约束
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,为这个业务系统构造出最优的数据存储模型
-
建立数据库中的表结构以及表与表之间的关联关系的过程
-
有哪些表?表里有哪些字段?表和表之间有什么关系?
-
数据库设计的步骤
-
需求分析(数据是什么?数据具有哪些属性?数据与属性的特点是什么?)
-
逻辑分析(通过ER图对数据库进行逻辑建模,不需要我们考虑所选用的数据库管理系统)
-
物理设计(根据数据库自身的特点,把逻辑设计转换为物理设计
-
维护设计(1.对新的需求进行建表; 2.表优化)
-
表关系
-
一对一:
如:用户 和 用户详情
一对一关系多用于表拆分,将一个实体中经常使用的字段放一张表,不经常使用的字段放另外一张表,用于提升查询性能
-
一对多(多对一):
如:部门 和 员工
一个部门对应多个员工,一个员工对应一个部门
-
多对多:
如:商品 和 订单
一个商品对应多个订单,一个订单包含多个商品
2)、表关系之一对多
一对多(多对一)
如:部门表 和 员工表
一个部门对应多个员工,一个员工对应一个部门
实现方式:在多的一方建立外键,指向一的一方的主键
3)、表关系之多对多
多对多:
如:商品 和 订单
一个商品对应多个订单,一个订单包含多个商品
实现方式:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
示例:
/*多对多:
如:商品 和 订单
一个商品对应多个订单,一个订单包含多个商品
实现方式:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
*/
-- 删除表
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);
4)、表关系之一对一
一对一:
如:用户 和 用户详情
一对一关系多用于表拆分,将一个实体中经常使用的字段放一张表,不经常使用的字段放另外一张表,用于提升查询性能
实现方式:在任意一方加入外键,关联另一方主键,并且设置外键为==唯一(UNIQUE)==
5)、 总结
-
一对多实现方式
- 在多的一方建立外键关联一的一方主键
-
多对多实现方式
- 建立第三张中间表
- 中间表至少包含2个外键,分别关联双方主键
-
一对一实现方式
- 在任意一方建立外键,关联对方主键,并且设置外键唯一
四、 事务
数据库的事务 (Transaction) 是一种机制、一个操作序列,包含了==一组数据库操作命令== 事务把所有的命令作为一个整体一起向系统提交或撤销操作请求,即这一组数据库命令 ==要么同时成功,要么同时失败==
事务是一个不可分割的工作逻辑单元
简单来说就是防止异常事件影响数据库中的值
-- 开启事务
start transaction;
或者 begin;
-- 提交事务
commit;
-- 回滚事务
rollback;
示例:
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 的理想解决方案
- 这套全面的前端工具为数据库管理、开发和维护提供了一款直观而强大的图形界面
Comments 1 条评论
博主 蝉时雨
Warning: 获取IP地理位置失败 in /www/wwwroot/www.veloma.cn/wp-content/themes/Sakurairo-master/inc/classes/IpLocation.php on line 226
Unknown
看看