mysql数据库知识梳理总结
即使再小的帆也能远航~
一. 目录
数据库介绍
数据库安装
SQL概念
DDL
DML操作表中的数据
DQL单表查询
数据库备份和还原
DCL
数据库表的约束
表与表之间的关系
多表查询
子查询
索引
数据库设计
视图
数据库函数
事务
存储过程
游标
触发器
二.内容
数据库介绍
数据的存储方式
| 存储位置 | 优点 | 缺点 |
|---|---|---|
| 内存(程序) | 速度快 | 不能永久保存,数据是临时状态 |
| 文件(硬盘) | 数据可以永久保存 | 操作起来不方便 |
| 数据库 | 1.可以永久保存 2.查询数据快 3.对数据管理方便 | 占用资源;付费 |
什么是数据库
- 数据库是存放数据的仓库
- 数据库的本质还是一个文件系统,还是以文件的方式存在服务器的电脑上的
- 所有的关系型数据库都可以使用通用的 SQL 语句进行管理
数据库的安装
MySql8版本安装
- 略
数据库服务的启动与登录
MySQL的服务启动方式有两种
MySQL数据库的端口号是3306
手动启动
- 以管理员的身份运行cmd
- net start mysql80 //开启
- mysql -u用户名 -p密码 (-h主机) //利用用户名和密码登陆,由于MySQL 服务在本机,所以-h可以不写:
- 完成以上三步启动成功,关闭为: net stop mysql80
通过服务的方式启动
- 在电脑左下角搜"服务"
- 找到"MySQL80"
- 手动开启/关闭
MySQL的目录结构
MySQL重点目录
| 目录结构 | 描述 | 位置 |
|---|---|---|
| MySQL Server 8.0\bin | 所有MySQL的可执行文件 | C:\Program Files\MySQL\MySQL Server 8.0 |
| MySQL Server 8.0\my.ini | Mysql的配置文件,一般不建议去修改 | C:\ProgramData\MySQL\MySQL Server 8.0 |
| MySQL Server 8.0\Data | 数据库文件所在的文件夹 | C:\ProgramData\MySQL\MySQL Server 8.0 |
1.ProgramData是一个隐藏文件夹
2.bin (binary)既是:二进制, 里面存放的一般是可执行的二进制文件。二进制即是机器代码，汇编语言编译后的结果。我们编译的是高级语言，把高级语言翻译为机器语言后，才能被计算机执行。
bin是文件夹的名字，该文件夹里面存放的一般是可执行的二进制文件，我们通常使用较大型的软件时都会发现有这个名称的文件夹。
作用：(1).bin目录下存放的是项目调试之后的可运行文件。
在bin下有两个文件夹，一个是Debug，另一个是Release，它们都是自动生成的，是看启动调试时选择哪个就生成哪个文件 夹，Release比Debug小，但是这两个文件夹下存放的东西都是一样的。
(2)、当你调试项目的时候，在Debug文件夹下会生成项目名称.exe、项目名称.pdb、项目名称.vshost.exe，双击.exe就能运行你的项目。
在此文件夹下除了这些，还有一些项目的依赖项，比如 *.dll等，有的需要你自己添加到Debug文件夹下的，比如 .ini，.rpt等。
Navicat图形化工具-客户端
1.Navicat Premium 是一套数据库开发工具，让你从单一应用程序中同时连接多个数据库,可以快速轻松地创建、管理和维护数据库。
2.使用navicat 连接数据库
3.在断网的情况下,开启了MySQL服务器之后,无法打开Navicat Premium,会显示"2005 Unknown MySQL server host ‘localhost’(0)"
-
错误原因:在有网络的情况下,会自动解析LocalHost为127.0.01,而在断网的情况下,localhost只是一个字符串,不代表一个ip地址
-
解决方案: 1.只需将ip改为127.0.0.1即可,在MySQL的配置文件(my.ini)中
2.联网(哈哈哈)
localhost即127.0.0.1 127.0.0.1是回送地址，指本地机，一般用来测试使用。
数据库管理系统(DBMS)
1.DBMS，database management system，数据管理系统，指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。
2.MySQL，是基于客户机-服务器的DBMS（C/S，客户端 / 服务端），有别于基于共享文件系统的DBMS
3.DB，database，数据库，存储数据的仓库，它保存了一系列有组织的数据，**数据库的好处：**可以持久化数据到本地、结构化查询，数据库存储数据的特点：数据存放到表中，然后表再放到库中；一个库中可以有多张表，每张表具有唯一的表名用来标识自己；表中有一个或多个列，列又称为“字段”，相当于Java中的“属性”；表中的每一行数据，相当于Java中“对象”
SQL的概念
SQL的作用
Structured Query Language 结构化查询语言
是一种所有关系型数据库的查询规范，不同的数据库都支持。 通用的数据库操作语言，可以用在不同的数据库中。 不同的数据库 SQL 语句有一些区别
SQL语法
SQL语句的关键字不区分大小写，即：select和SELECT的作用完全一样。
SQL分类
1) Data Definition Language (DDL 数据定义语言) 如：建库，建表。
2) Data Manipulation Language(DML 数据操纵语言)，如：对表中的记录操作增删改。
3) Data Query Language(DQL 数据查询语言)，如：对表中的查询操作。
4) Data Control Language(DCL 数据控制语言)，如：对用户权限的设置
DDL
DDL语句是用来操纵数据库对象的语句
MySQL支持的数据类型
| 列类型 | 说明 |
|---|---|
| int | 整数 |
| decimal(m,n) | 精确小数类型;这个数据最多m位数(小数点前后加起来),小数点后必须n位数 |
| varchar | 可变长度字符串类型 |
| char(m) | 定长字符串类型;长度必须是m个 |
Navicat中的注释
-- sql的注释
#sql的注释
/*
sql的多行注释
*/
常见的数据库对象
| 数据库对象名称 | 对应关键字 | 描述 |
|---|---|---|
| 表 | Table | 列就是字段,相当于java中的属性, 行就是记录,相当于java中的对象;先有列(属性),后有行(对象) |
| 约束 | Constraint | 执行数据校验的规则,用于保证数据完整性的规则 |
| 视图 | View | 一个或多个数据表里数据的逻辑显示,视图并不存储数据 |
| 索引 | Index | 提高查询性能,相当于书的目录 |
| 函数 | Function | 完成特定的计算,具有返回值 |
| 存储过程 | Procedure | 完成一次完整的业务处理,没有返回值,但可通过传出参数将多个值传给调用环境 |
| 触发器 | Trigger | 相当于一个事件监视器,当数据库发生特定事件后,触发器被触发,完成相应的处理 |
因为存在上面几种数据数据库对象，所以在create语句后面可以跟不同的关键字，用于表示要创建哪种对象，例如创建表使用create table，创建索引使用create index，创建视图使用create view，drop和alter后面也需要跟不同的关键字来表示删除、修改哪种数据库对象。
常见DDL操作
DDL操作数据库
-
创建数据库
create database 数据库名; -
判断数据库是否存在,不存在则创建数据库
create database if not exists 数据库名; -
查看所有数据库
show databases; -
查看某个数据库的定义信息
show create database 数据库名; -
删除数据库
drop database 数据库名; -
使用/切换数据库
use 数据库名;
DDL操作表结构
创建表
create table 表名(
字段名1 字段类型1,
字段名1 字段类型1,
字段名2 字段类型2,
字段名3 字段类型3
);
-- 非结尾字段用末尾逗号,结尾字段末尾啥也不加
#一个SQL语句的结束末尾要加分号";"
查看当前数据库里的所有表
show tables;
查看表结构
desc 表名;
查看创建表的SQL语句
show create table 表名;
删除表
drop table if exists 表名;
修改表结构之添加列add
alter table 表名 add 列名 类型;
修改表结构之修改列类型
alter table 表名 modify 列名 新类型;
修改表结构之修改列名
alter table 表名 change 旧列名 新列名 类型;
修改表结构之删除列drop
alter table 表名 drop 列名;
修改表结构之修改表名
alter table 旧表名 rename to 新表名;
DML操作表中的数据
用于对表中的记录进行增删改操作
insert-插入记录
insert (into) 表名 (字段名) values (字段值);
INSERT INTO 表名：表示往哪张表中添加数据
( 字段名 1, 字段名 2, …) ： 要给哪些字段设置值
VALUES (值 值 1, 值 值 2, …) ：设置具体的值
-- 演示
# 插入所有列
insert into stu (stu_id,stu_name,birthday) values (1,'紫霞仙子','1000-10-10');
# 插入所有列
insert into stu values (2,'青霞仙子','1000-10-09');
# 插入部分列
insert into stu (stu_id,stu_name) values (3,'至尊宝');
- 创建stu2表和stu表的表结构一致:
create table stu2 like stu;
- 向stu2表中插入数据: 可以把stu表中的数据复制到stu2中:
insert into stu2 select * from stu;
update-更新表记录
update 表名 set 字段名1=字段值1,字段名2=字段值2,字段名3=字段值3 [where 条件表达式];
#如果没有where 条件表达式,就把整个表的这个字段名的值全给改了
update 表名 set 字段名=值;
-- 修改所以行
-- 演示
# stu表新增一列sex
alter table stu add sex char(1);
# 不带条件修改 , 修改所有人的性别为'女'
update stu set sex = '女';
# 带条件修改 , 修改stu_id是3的birthday为'1000-05-05'
update stu set birthday = '1000-05-05' where stu_id = 3;
# 同时修改sex和birthday
update stu set sex = '男',birthday = '1000-05-10' where stu_id = 3;
delete-删除数据
delete from 表名 [where 条件表达式];
-- 如果没有指定where子句，MySQL表中的所有记录将被删除。
- delete删除是物理删除,平常所说的删库跑路就是这个删除;但是这个删除会保留主键id.
truncate删除表中的记录
truncate table 表名;
- truncate 相当于删除表的结构，再创建一张表。所以主键id也会删掉.
update-逻辑删除
工作中最常用的就是逻辑删除
加一个int类型的字段nn,默认nn=1,使用update将"删除"的元素set nn=2;
举例:和平精英玩家退游了,也要把他们数据存储起来,做分析,欢迎他们以后回归…而不能删除
DQL单表查询
简单查询
查询表所有行和列的数据
select * from 表名;
查询指定列的数据,多个列之间以逗号分隔
select 字段名1,字段名2,字段名3....from 表名;
指定列的别名进行查询
使用别名的好处:显示的时候使用新名字,并不会去修改表的结构,一般是用在多表联合查询的地方
对列指定起别名
select 字段名1 as 别名1,字段名2 as 别名2,字段名3 as 别名3.....from 表名;
对表和列同时指定起别名
select 字段名1 as 别名1,字段名2 as 别名2.....from 表名 as 表的别名;
注:表使用别名的原因,用于多表查询;
清除重复值
查询指定列并且结果不出现重复数据:
select distinct 字段名 from 表名;
查询结果参与运算
某列数据和固定值运算
select 列名1+固定值 from 表名;
某列数据和其他列数据参与运算
select 列名1+列名2 from 表名;
注:参与运算的必须是数据类型!!!
条件查询
实际应用中,一般要指定查询的条件,对记录进行过滤
条件查询
select 字段名 from 表名 where 条件;
比较运算符
| 比较运算符 | 描述 |
|---|---|
| <> | <>在SQL中表示不等于,在mysql中也可以使用!=;没有== |
| between…and… | 在一个范围之内,包头又包尾; |
| in(…); in也是关键字 | 集合表示多个值,使用逗号分隔 |
| is null | 查询某一列为 null 的值，注：不能写=null |
逻辑运算符
| 逻辑运算符 | 说明 |
|---|---|
| and 或 && | 与;SQL中建议用前者,后者不通用 |
| or 或 || | 或 |
| not 或 ! | 非 |
like关键字
-- like 表示模糊查询
select * from 表名 where 字段名 like '通配符字符串';
Mysql通配符
| 通配符 | 说明 |
|---|---|
| % | 匹配任意个任意字符 |
| _ | 匹配任意一个字符 |
-- 示例
-- 查询姓马的学生
select * from student3 where name like '马%';
select * from student3 where name like '马';
-- 查询姓名中包含'德'字的学生
select * from student3 where name like '%德%';
-- 查询姓马，且姓名有两个字的学生
select * from student3 where name like '马_';
排序
通过order by子句,可以将查询出的结果进行排序(排序只是显示方式,不影响数据库中数据的顺序)
select 字段名 from 表名 where 字段=值 order by 字段名1 [asc|desc],字段名2 [asc|desc];
#注:不说的话默认是升序排序
-- asc 升序 desc 降序
组合排序: 同时对多个字段进行排序，如果第 1 个字段相等，则按第 2 个字段排序，依次类推
select 字段名 from 表名 where 字段=值 order by 字段名 1[asc|desc],字段名 2[asc|desc];
#例
-- 查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩升序排序
select * from student order by age desc, math asc;
聚合函数
之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，
它是对一列的值进行计算，然后返回一个结果值。聚合函数会忽略空值 NULL。
五个聚合函数:
| sql中的聚合函数 | 作用 |
|---|---|
| max(列名) | 求这一列最大值 |
| min(列名) | 求这一列最小值 |
| avg(列名) | 求这一例平均值 |
| count(列名) | 统计这一列有多少条记录 |
| sum(列名) | 对这一列求总和 |
语法
select 聚合函数(列名) from 表名;
#演示
-- 查询学生总数
select count(id) as 总人数 from student;
select count(*) as 总人数 from student;
-- 查询年龄大于 20 的总数
select count(*) from student where age>20;
-- 查询数学成绩总分
select sum(math) 总分 from student;
-- 查询数学成绩平均分
select avg(math) 平均分 from student;
-- 查询数学成绩最高分
select max(math) 最高分 from student;
-- 查询数学成绩最低分
select min(math) 最低分 from student;
注: 数值+null=null,这可不行=>去空操作
ifnull(列名x,替换值y)
-- 如果列名x不为空,返回这列的值;如果x为null,则用y替换x
#例
-- 查询 id 字段，如果为 null，则使用 0 代替
select ifnull(id,0) from student;
-- 我们可以利用 IFNULL()函数，如果记录为 NULL，给个默认值，这样统计的数据就不会遗漏
select count(ifnull(id,0)) from student;
分组
分组查询是指使用group by语句对查询的信息进行分组,相同数据作为同一组
select 字段1,字段2...from 表名 group by 分组字段[having 条件];
group by 将分组字段结果中相同内容作为一组，并且返回每组的第一条数据，所以单独分组没什么用处。分组的目的就是为了统计，一般分组会跟聚合函数一起使用
-- 按性别进行分组，求男生和女生数学的平均分
select sex, avg(math) from student3 group by sex;
having与where区别
| 子名 | 作用 |
|---|---|
| where子句 | 1) 对查询结果进行分组前，将不符合 where 条件的行去掉，即在分组之前过滤数据，即先过滤再分组。 2) where 后面不可以使用聚合函数 |
| having子句 | 1) having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤。 2) having 后面可以使用聚合函数 |
发挥作用的先后顺序: where>group by>having
分页limit语句
limit是限制的意思,限制显示的结果集
-- 格式: limit n,m; n--记录起始行的下标,从0开始!!;m--本次查看的行数
#eg: 每次查看4条,每页显示4条,看第三页
select * from emp where sal>100 order by sal asc limit 8,4;
结果集表里的数据行下标从0开始,每页查询size条,查看第page页
公式:limit (page-1)*size,size;
-- 如果第一个参数是 0 可以省略写：
select * from student3 limit 5;
-- 最后如果不够 5 条，有多少显示多少
select * from student3 limit 10,5;
数据库备份和还原
备份
在服务器进行数据传输、数据存储和数据交换，就有可能产生数据故障。比如发生意外停机或存储介质损坏.这时，如果没有采取数据备份和数据恢复手段与措施，就会导致数据的丢失，造成的损失是无法弥补与估量的。
备份格式:cmd里,未登陆时
mysqldump -u用户名 -p密码 数据库 > 文件路径
//演示:备份之前使用的stu表所在的数据库demo的数据到d:\demo.sql文件中
C:\Users\Administrator>mysqldump -uroot -p123456 demo > d:\demo.sql
// Administrator的简写形式是Admin，中文意思就是“系统管理员”。即
//的“超级用户”。administrator是电脑里权限不受控的人
//C:\Users\Administrator>的意思就是使用的是Administrator用户。
还原
还原格式(还是在cmd里面):mysql中的命令,需要登录后(cmd登陆)才可以操作
use 数据库;
source 导入文件的路径;
//演示:创建demo2数据库,把d:\demo.sql里的表和数据还原到demo2库中
net start mysql80
mysql -uroot -p123456
create database demo2;
use demo2;
show tables;
source d:/demo.sql;
Navicat图形化界面备份和还原
备份数据库中的数据:
-
选中数据库, 右键 ”转储sql文件” “结构和数据”
-
指定导出路径，保存成.sql 文件即可。
还原数据库中的数据:
-
创建新数据库demo3
-
表区域右键“运行 SQL 文件”， 指定要执行的 SQL 文件，执行
-
表区域右键刷新
DCL
创建用户
我们现在默认使用的都是 root 用户，超级管理员，拥有全部的权限。但是，一个公司里面的数据库服务器上面可能同时运行着很多个项目的数据库。所以，我们应该可以根据不同的项目建立不同的用户，分配不同的权限来管理和维护数据库。
语法
create user '用户名' @ '主机名' identified by '密码' ;
关键字说明
| 关键字 | 说明 |
|---|---|
| ‘用户名’ | 将要创建的用户名 |
| ‘主机名’ | 指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost,如果想让该用户可以从任意远程主机登陆,可以使用通配符% |
| ‘密码’ | 该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器 |
演示
# 创建 user1 用户，只能在 localhost 这个服务器登录 mysql 服务器，密码为 123
create user 'user1'@'localhost' identified by '123';
# 创建 user2 用户可以在任何电脑上登录 mysql 服务器，密码为 123
create user 'user2'@'%' identified by '123';
删除用户语法
drop user '用户名' @ '主机名';
# 删除用户user2
drop user 'user2'@'%';
授权
用户创建之后,没什么权限!需要给用户授权
grant 权限1,权限2.....on 数据库名.表名 to '用户名' @ '主机名';
关键字说明
| 关键字 | 说明 |
|---|---|
| grant…on…to | 授权关键字 |
| 权限 | 授予用户权限,如create,alter,select,insert,update等,如果要授予所有的权限则使用all |
| 数据库名,表名 | 该用户可以操作哪个数据库的哪些表。如果要授予该用户对所有数据库和表的相应操作权限则可用表示，如.* |
| ’ 用户名’@’ 主机名’ | 给哪个用户授权，注：有 2 对单引号 |
演示
# 给 user1 用户分配对 demo这个数据库操作的权限：创建表，修改表，插入记录，更新记录，查询
grant create,alter,insert,update,select on demo.* to 'user1'@'localhost';
# 给 user2 用户分配所有权限，对所有数据库的所有表
grant all on *.* to 'user2'@'%';
删除授权语法
语法
revoke 权限1,权限2...on 数据库.表名 revoke all on test.* from 'user1'@'localhost'; ' 用户名'@' 主机名';
关键字说明
| 关键字 | 说明 |
|---|---|
| revoke…on…from | 撤销授权关键字 |
| 权限 | 用户权限,如create,alter,select,insert,update等,所有的权限则使用all |
| 数据库名. 表名 | 对哪些数据库的哪些表，如果要取消该用户对所有数据库和表的操作权限则可用表示，如.* |
| ’ 用户名’@’ 主机名’ | 给哪个用户撤销 |
演示
# 撤销 user1 用户对 demo数据库所有表的操作的权限
revoke all on demo.* from 'user1'@'localhost';
查看权限语法
show grants for '用户名'@'主机名';
# 查看 user1 用户的权限
show grants for 'user1'@'localhost';
修改密码
alter user '用户名'@'主机名' identified by '新密码';
# 修改root用户的密码为 root
alter user 'root'@'localhost' IDENTIFIED by 'root';
数据库表的约束
概念
一个表中如果添加了约束,不正确的数据将无法插入到表中.约束在创建表的时候添加比较合适
约束种类
| 约束名 | 约束关键字 |
|---|---|
| 主键约束 | primary key |
| 唯一约束 | unique |
| 非空约束 | not null |
| 外键约束 | foreign key |
主键约束
作用:唯一标识数据库中的每一条记录
通常不用业务字段作为主键,单管给每张表设计一个id的字段,把id作为主键(大白话:主键不是表的属性,是单独拿出来做唯一标识用的);主键是给数据库和程序使用的,不是给最终的客户使用的.所以主键有没有含义不重要,只要不重复,非空就行.
主键特点:非空,唯一
创建主键方式
-
在创建表的时候给字段添加主键
字段名 字段类型 primary key; -
在已有表中添加主键
alter table 表名 add primary key(字段名); -
删除主键
alter table 表名 drop primary key; -
主键自增
主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值auto_increment ;表示自动增长(字段类型必须是整数类型int) -
默认地 AUTO_INCREMENT 的开始值是 1,insert插入数据时,主键的字段可以是null,有了初始值之后就会+1
-
创建表时指定auto_increment的起始值
CREATE TABLE 表名(
列名 int primary key AUTO_INCREMENT
) AUTO_INCREMENT=起始值;
- 创建表后修改auto_increment的起始值
alter table 表名 auto_increment=起始值;
-
DELETE 和 TRUNCATE 对自增长的影响:
DELETE：删除所有的记录之后，自增长没有影响。
TRUNCATE：删除以后，自增长又重新开始。TRUNCATE是把表结构DROP 之后 再CREATE。
唯一约束
概念:表中某一列不能出现重复的值,但可以为null
唯一约束的基本格式
字段名 字段类型 unique
非空约束
概念:列值不能为null
非空约束的基本语法
字段名 字段类型 not null;
默认值
字段名 字段类型 default 默认值;
疑问:如果一个字段设置了非空与唯一约束，该字段与主键的区别?
1) 主键在一个表中，只能有一个。不能出现多个主键。主键可以单列，也可以是多列。
2) 自增长只能用在主键上
外键约束
单表的缺点:
- 数据冗余
- 后期还会出现增删改的问题
什么是外键:在从表中与主表主键对应的那一列(主表主键有n行=>有n个主键=>丛键只能从n个主键里面选出一个来)
主表:一方,用来约束别人的表
从表:多方,被别人约束的表
语法
-- 新建表时增加外键
constraint 外键约束名 foreign key(外键字段名) references 主表名(主键字段名)
-- 已有表增加外键
alter table 从表 add constraint 外键约束名 foreign key (外键字段名) references 主表(主键名)
-- 演示
-- 创建从表 employee 并添加外键约束 emp_depid_fk
-- 多方，从表
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int, -- 外键对应主表的主键
-- 创建外键约束
constraint emp_depid_fk foreign key (dep_id) references department(id)
);
删除外键
alter table 从表 drop foreign key 外键名称;
级联操作:在修改和删除主表的主键时,同时更新或删除
| 级联操作语法 | 描述 |
|---|---|
| on update cascade | 级联更新，只能是创建表的时候创建级联关系。更新主表中的主键，从表中的外键列也自动同步更新 |
| on delete cascade | 级联删除 |
| on delete set null | 删除时从表外键字段设置为null |
演示
constraint emp_depid_fk foreign key (dep_id) references department(id) on update cascade on delete set null
表与表之间的关系
一对多
建标原则:在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键
从主表(一方)来看是一对多(一个主键有多个值相同的从键);从从表(多方)来看是多对一(多个值相同的从键有一个主键);从从表的一个从键来看是一对一.
多对多
建标原则:需要创立第三张表,这个第三张表至少要有两个字段分别作为多1,多2的外键,指向各自一方的主键.
一对一(基本不用,一对一直接建成一张表就行了)
多表查询
select * from emp,dept;
-- 会出现笛卡尔积,导致产生大量冗余数据
内连接
用左边表的记录去匹配右边表的记录,若符合条件则显示
.eg:从表外键=主表外键;
隐式内连接:看不到join 关键字,条件使用where 指定(mysql的方言)
语法
select * from 左表,右表 where 条件
-- 演示
select * from emp,dept where emp.dept_id = dept.id;
显示外连接(SQL语句通用)
select * from 左表 inner join 右表 on 条件
左外连接
内连接只会让多张表中满足条件的列出来组成一张大表;
如果想让某张表全部显示,其他的表只显示满足条件的来组成一张大表,那就要用外连接
-- 左外连接
select * from 左表 left join 右表 on 条件
-- 左表会全部显示出来,右表只显示符合条件的
右外连接
select * from 左表 right join 右表 on 条件
子查询
概念
-
一个查询的结果做为另一个查询的条件
-
有查询的嵌套，内部的查询称为子查询
-
子查询要使用括号
子查询的三种情况
子查询的结果是单行单列
-- 展示一个子查询
select max(salary) from emp;
子查询的结果是多行单列
-- 展示一个子查询
select dept_id from emp where join_date < '2013-05-01';
子查询的结果是多行多列
-- 展示一个子查询
select * from emp where salary between 4000 and 8000;
子查询的结果是一个值的时候
子查询结果只要是单行单列，肯定在 WHERE 后面作为条件，父查询使用：比较运算符，如：> 、<、<>、=等
select 查询字段 from 表 where 字段=(子查询);
-- 演示
-- 1) 查询最高工资是多少
select max(salary) from emp;
-- 2) 根据最高工资到员工表查询到对应的员工信息
select * from emp where salary = (select max(salary) from emp);
-- 1) 查询平均工资是多少
select avg(salary) from emp;
-- 2) 到员工表查询小于平均的员工信息
select * from emp where salary < (select avg(salary) from emp);
子查询的结果是多行单列的时候
子查询的结果是单行多列,结果集类似于一个数组,父查询使用in运算符
select 查询字段 from 表 where 字段 in(子查询);
-- 演示:查询工资大于5000 的员工,来自哪些部门的名字
-- 先查询大于 5000 的员工所在的部门 id
select dept_id from emp where salary > 5000;
-- 再查询在这些部门 id 中部门的名字 [Err] 1242 - Subquery returns more than 1 row
select name from dept where id = (select dept_id from emp where salary > 5000);
-- 使用in
select name from dept where id in (select dept_id from emp where salary > 5000);
-- 演示: 查询开发部与财务部所有的员工信息
-- 先查询开发部与财务部的 id
select id from dept where name in('开发部','财务部');
-- 再查询在这些部门 id 中有哪些员工
select * from emp where dept_id in (select id from dept where name in('开发部','财务部'));
子查询的结果是多行多列
子查询的结果只要是多列(那这个子查询的结果就是一个表啊),肯定在from后面作为表
select * from (子查询) 表别名 where 条件;
-- 子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段
-- 查询出 2011 年以后入职的员工信息，包括部门名称
-- 在员工表中查询 2011-1-1 以后入职的员工
select * from emp where join_date >='2011-1-1';
-- 查询所有的部门信息，与上面的虚拟表中的信息组合，找出所有部门 id 等于的 dept_id
select * from dept d, (select * from emp where join_date >='2011-1-1') e where d.id= e.dept_id ;
子查询小结:
子查询结果只要是单列，则在 WHERE 后面作为条件
子查询结果只要是多列，则在 FROM 后面作为表进行二次查询
索引
概念
索引是存放在模式中的一个数据库对象,虽然索引总是从属于数据表,但它和数据表一样从属于数据库对象.**创建索引的唯一作用就是加速对表的查询,索引通过使用快速路径访问方法来快速定位数据,从而减少磁盘的I/O**
索引作为数据库对象,在数据字典中独立存放,但不能独立存在必须从属于某个表.
创建索引的两种方式
自动:当在表上定义主键约束,唯一约束的和外键约束时,系统会为该数据自动创建对应的索引.
手动
create index 索引名 on table 表名(字段);
-- 演示
# 在stu表的 stu_name 和 birthday 列上 创建索引
create index name_birth on stu (stu_name , birthday);
查看索引
show index from 表名;
删除索引的两种方式
自动:数据表被删除时,该表上的索引自动删除
手动
drop index 索引名 on 表名
-- 演示
# 删除stu上的 name_birth 的索引
drop index name_birth on stu;
数据库设计
数据规范化
好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响。建立科学的，规范的数据库就需要满足一些规则来优化数据的设计和存储，这些规则就称为范式。
三大范式
目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF） 、第四范式(4NF）和第五范式（5NF，又称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF） ，其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了。
1NF
数据库表的每一列都是不可分割的原子数据项，不能是集合、数组等非原子数据项。**即表中的某个列有多个值时，必须拆分为不同的列。**简而言之，第一范式每一列不可再拆分，称为原子性。
表的每个字段 反映 表主体(学生)的一种特征
2NF
在满足第一范式的前提下，表中的每一个字段都完全依赖于主键。所谓完全依赖是指不能存在仅依赖主键一部分的列。简而言之，第二范式就是在第一范式的基础上所有列完全依赖于主键列。当存在一个复合主键包含多个主键列的时候，才会发生不符合第二范式的情况。比如有一个主键有两个列，不能存在这样的属性，它只依赖于其中一个列，这就是不符合第二范式。第二范式的特点：
1) 一张表只描述一件事情。
- 表中的每一列都完全依赖于主键
例:
- 借书证表:
| 学生证号 | 学生证名称 | 学生证办理时间 | 借书证号 | 借书证名称 | 借书证办理时间 |
|---|---|---|---|---|---|
- 分成两张表
| 学生证号 | 学生证名称 | 学生证办理时间 |
|---|---|---|
| 借书证号 | 借书证名称 | 借书证办理时间 |
3NF
在满足第二范式的前提下，表中的每一列都直接依赖于主键，而不是通过其它的列来间接依赖于主键。简而言之，第三范式就是所有列不依赖于其它非主键列，也就是在满足 2NF 的基础上，任何非主列不得传递依赖于主键。所谓传递依赖，指的是如果存在"A → B → C"的决定关系，则 C 传递依赖于 A。因此，满足第三范式的数据库表应该不存在如下依赖关系：主键列 → 非主键列 x → 非主键列 y
例: 学生信息表
…
存在传递的决定关系:
学号 -> 所在学院 -> 学院地点
拆分为两张表:
| 学号 | 姓名 | 年龄 | 所在学院的编号(外键) |
|---|---|---|---|
| 学院编号 | 所在学院 | 学院地点 |
|---|---|---|
三大范式小结:
| 范式 | 特点 |
|---|---|
| 1NF | 原子性: 表中每列不可再拆分。 |
| 2NF | 不产生局部依赖，一张表只描述一件事情。 |
| 3NF | 不产生传递依赖，表中每一列都直接依赖于主键。而不是通过其它列间接依赖于主键。 |
视图
视图看上去像一个数据表,但它不是数据表,因为他并不能存储数据.视图只是一个或多个数据表中数据的逻辑显示,即一个查询结果,视图适合多表连接浏览时使用,不适合增,删,改;视图可以对应一个基本表,也可以对应多个基本表
视图和表的区别:
- 若表是计算机中的文件,那么视图就是桌面的快捷方式
- 表是内容,视图是窗口.
- 视图是已经编译好的SQL语句,而表不是
- 视图的建立和删除只影响视图本身,不影响对应的基本表
视图作用:
当一个查询需要频繁的作为子查询使用,视图可以简化代码,直接调用而不是每次都去重复写这个东西
创建视图的语法格式
create or replace view 视图名 as subquery查询结果
-- ，如果该视图不存在，则创建视图；如果指定视图名已经存在，则使用新
-- 视图替换原有视图。后面的subquery是一个查询语句，这个查询语句可以
-- 非常的复杂。
建立视图后，使用视图与使用数据表就没有什么区别了，但通常只是查询视图数据，不能修改视图里的数据，因为视图本身没有存储任何数据。
修改视图里的数据 , 原表也会受到影响.
为了强制不允许改变视图的数据，MySQL允许在创建视图时使用with check option子句，使用该子句创建的视图不允许修改，如下所示
create or replace view view_emp_sal5000 as select * from emp where salary>=5000 with check option;
删除视图使用如下语句
drop view 视图名;
-- drop view view_emp_dept;
数据库函数
每个数据库都会在标准的SQL基础上扩展一些函数,SQL中的函数和java语言中的方法有点相似，但SQL中的函数是独立的程序单元，也就是说，调用函数时无须使用任何类、对象作为调用者，而是直接使用函数即可。
function name(参数1,参数2,参数3..........)
mysql提供了几个处理null的函数
ifnull(exper1,exper2)：#如果exper1为null，则返回exper2，否则返回exper1
nullif(exper1,exper2)：#如果exper1和exper2相等，则返回null，否则返回exper1
if(exper1,exper2,exper3)：#类似于？：三目运算符，如果exper1为true，不等于0且不等于null，则返回exper2，否则返回exper3
isnull(exper1)：#判断exper1是否为null，如果为null作为查询结果返回为0
-- 演示
# 如果dept_id列为null，则返回没有部门
select name, ifnull(dept_id,'没有部门') as deptid from emp;
# 如果name列的值为孙悟空，则返回null
select name, nullif(name,'孙悟空') 演示 from emp;
# 如果dept_id列为null则返回没有部门,不为null则返回具体的dept_id值
select name, if(isnull(dept_id),'没有部门',dept_id) dept_id from emp;
mysql单行函数的用法
# 选出emp表中tea_name列的长度
select char_length(name) from emp;
# 为指定日期添加一定的时间
# 使用date_add函数需要两个参数，日期、数值类型带单位
select date_add('1998-01-02',interval 12 month); -- year month day
# 使用adddate函数更简单
select adddate('1998-01-02',3); #只能加天数
# 获取当前日期
select curdate();
# 获取当前时间
select curtime();
# 对字符串使用MD5进行加密
select MD5('test');
mysql还提供了一个case函数,该函数是一个流程控制函数
-- case函数有两个用法，case函数的第一个用法的语法格式如下：
case value
when compare_value1 then result1
when compare_value2 then result2
else result3
end
case函数用value和后面的compare_value1、compare_value2、…依次进行比较，如果value和指定的compare_value1相等，则返回对应的result，否则返回else后的result。
-- 演示
# gender为 ‘女’显示为 ‘美女’
select name ,
(case gender
when '女' then '美女'
else gender
end) gender from emp;
-- case得第二种用法
case
when condition1 then result1
when condition2 then result2
……..
else result
end
在第二种用法中，condition1、conditon2都是返回一个boolean值的条件表达式，因此这种用法更加灵活
-- 演示
# 查询每个人的姓名,薪水,和提升之后的薪水.
# 提升方案: 小于4000的 提升为原来的2倍 在4000到6000之间的提升为原来的1.5倍 , 大于6000的不变
select name , salary ,
(
case
when salary<=4000 then salary*2
when salary>4000 and salary<=6000 then salary*1.5
else salary
end
) 提升后的薪水 from emp;
事务
事务执行是一个整体，所有的 SQL 语句都必须执行成功。如果其中有 1 条 SQL 语句出现异常，则所有的SQL 语句都要回滚，整个业务执行失败。
mysql中可以有两种方式进行事物的操作:
1)手动提交事务
start transaction -- 开启事务
commit -- 提交事务
rollback -- 回滚事务
2)自动提交事务(默认是自动提交事务)
事务原理
事务开启之后, 所有的操作都会临时保存到事务日志中, 事务日志只有在得到 commit 命令才会同步到数据表中，其他任何情况都会清空事务日志(rollback，断开连接)
事物的隔离级别
事物的四大特性 ACID
| 事务特性 | 含义 |
|---|---|
| 原子性 | 每个事务都是一个整体，不可再拆分，事务中所有的 SQL 语句要么都执行成功，要么都失败 |
| 一致性 | 事务在执行前数据库的状态与执行后数据库的状态保持一致。如：转账前2个人的 总金额是2000 ，转账后2个人总金额也是2000。 |
| 隔离性 | 事务与事务之间不应该相互影响，执行时保持隔离的状态。 |
| 持久性 | 一旦事务执行成功，对数据库的修改是持久的。就算关机，也是保存下来的 |
事物的并发访问问题:
事务在操作时的理想状态： 所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个数据。可能引发并发访问的问题：
| 并发访问的问题 | 含义 |
|---|---|
| 脏读 | 一个事务中读取了另一个事务中尚未提交的数据 |
| 不可重复读 | 一个事务中两次读取的数据内容不一致，要求的是一个事务中多次读取时数据是一致的， 这是事务 update 时引发的问题 |
| 幻读 | 一个事务中两次读取的数据的数量不一致，要求在一个事务多次读取的数据的数量是一致的，这是 insert 或 delete 时引发的问题. |
mysql事务有四种隔离级别
| 级别 | 名字 | 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|---|---|
| 1 | 读未提交 | read uncommitted | 是 | 是 | 是 |
| 2 | 读已提交 | read committed | 否 | 是 | 是 |
| 3 | 可重复读 | repeatable read | 否 | 否 | 是 |
| 4 | 串行化 | serializable | 否 | 否 | 否 |
隔离级别越高，性能越差，安全性越高。
mysql的默认隔离级别为第三级别 repeatable read
-- 查看数据库隔离级别
show variables like '%transaction_isolation%';
-- 设置当前会话(连接)或全局的事务隔离级别
set session|global transaction isolation level 级别字符串
演示脏读
略
存储过程
可以创建存储过程，存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批处理文件，虽然它们的作用不仅限于批处理。
格式
drop procedure if exists 存储过程名;
create procedure 存储过程名(in 入参1 类型,in 入参2 类型.......,out 返回值 类型...)
begin
SQL语句
end;
-- 调用存储过程
call 存储过程名(入参1,入参2,....@返回值);
select @返回值;
-- 删除存储名
drop procedure 存储过程名;
演示
#创建一个歌手表
drop table if exists tsinger;
create table if not exists tsinger(
sid int primary key auto_increment, -- 主键
sname varchar(20), -- 名字
sex char(1), -- 性别
salary decimal(7,2), -- 薪水
birthday date, -- 生日
display varchar(100) unique -- 描述
);
insert into tsinger values(1001,'谭维维','女',55555.55,'1982-10-08','我是歌手');
insert into tsinger values(null,'徐梦圆','男',1000.00,'1990-01-01','china');
insert into tsinger values(null,'刘德华','男',99999.99,'1961-09-27','唱哭了');
insert into tsinger values(null,'洛天依','女',10000.99,'2012-07-12','虚拟歌姬');
insert into tsinger (sname,sex,display) values('张三','男','测试专用');
drop procedure if exists pro1;
# 插入指定名字和指定描述的歌手, 并拿到歌手总数量.
create procedure pro1(in spc_name varchar(20), in spc_display varchar(100), out sum int)
begin
# 插入指定名字和指定描述的歌手
insert into tsinger (sname,display) values (spc_name,spc_display);
# 查询歌手数量返回
set sum = (select count(*) from tsinger);
end;
call pro1('张韶涵','隐形的翅膀',@count);
select @count;
select * from tsinger ORDER BY sid desc limit 1;
# 删除存储过程pro1
drop procedure pro1;
# 删除存储过程pro1
drop procedure pro1;
声明变量
-- 1.使用declare 声明变量
declare 变量名 类型 default 初始值;
-- 2.使用set声明变量,变量的值可以来自子查询
set @变量=(查询);
-- 演示
# 存储过程3:修改salary为null的salary为 表中最小的salary
drop procedure if exists pro3;
create procedure pro3()
BEGIN
set @min_sal = (select min(salary) from tsinger);
update tsinger set salary = @min_sal where salary is null;
end;
call pro3();
存储过程带有分支和循环
分支
if 条件
then 操作
end if;
if 条件
then 操作
else 操作
end if;
演示
# 创建存储过程pro2, 指定姓名和描述新增歌手,返回salary为null的歌手数量
drop PROCEDURE if EXISTS pro2;
create procedure pro2(in spc_name varchar(20),in spc_display varchar(100),out count int)
BEGIN
# 指定的名字为null时 ,设置为系统测试+随机字符串
if ISNULL(spc_name)
then set spc_name = concat('系统测试',SUBSTR(UUID(),1,8));
end if;
insert into tsinger (sname , display) values (spc_name,spc_display);
set count = (select count(*) from tsinger where salary is null);
end;
循环
while 条件
do 操作
end while;
repeat 操作
until 条件
end repeat;
-- 类似java里的do-while 不管条件成立与否首先执行一次
演示
# 存储过程5: 插入指定数量的记录到tsinger表中, 名字和描述采用uuid随机,性别依次为'女''男'
drop PROCEDURE IF EXISTS pro5;
create PROCEDURE pro5(in count int)
BEGIN
declare a int default 1;
declare gender char(1) default '女';
while a<=count DO
if a%2!=0 then set gender = '女';
else set gender = '男';
end if;
insert into tsinger (sname,sex,display) values (SUBSTR(UUID(),1,8),gender,UUID());
set a = a+1;
end while;
end;
call pro5(4);
# 存储过程6: 向tsinger表中插入指定数量的记录, sname采取随机
drop PROCEDURE if exists pro6;
create procedure pro6(in count int)
BEGIN
repeat
insert into tsinger (sname) values (SUBSTR(UUID(),1,8));
set count = count +1;
until count>=10 end repeat;
end;
# 100 已经满足 >=10 停止循环的条件 , 但还是执行一次
call pro6(100);
存储过程中带有case when
存储过程中使用case when语句
之前介绍过case when语句 这里直接演示
# 存储过程4:按照指定的性别新增歌手
# 如果性别为'女',则设置salary为10000
# 如果性别为'男',则设置salary为表中最小的salary
drop procedure if exists pro4;
create procedure pro4(in spc_sex char(1))
BEGIN
declare sal decimal(7,2) default 0;
case spc_sex
when '女' then set sal = 10000;
when '男' then set sal = (select min(salary) from tsinger);
else set sal = null;
end case;
insert into tsinger (sex,salary) values(spc_sex,sal);
end;
call pro4(null);
call pro4('女');
call pro4('男');
存储过程带事务
# 存储过程7: 按照指定的名字和描述新增歌手. 并删除名字长度=8的男生.
-- 遇到异常返回0. 成功加入返回1
# 存储过程7: 按照指定的名字和描述新增歌手. 并删除名字长度=8的男生.遇到异常返回0. 成功加入返回1
-- 分析: display字段有唯一约束 , 所以在新增操作时有可能违反约束而报出异常.
drop PROCEDURE if exists pro_transaction;
create procedure pro_transaction (in spc_name varchar(20),in spc_display varchar(100),out result int)
BEGIN
declare flag int default 1;
# 当事务操作中报出异常时设置flag的值为0
declare continue handler for SQLEXCEPTION set flag = 0 ;
# 开启事务
start transaction;
# 新增操作 spc_display有可能违反唯一性约束
insert into tsinger (sname,display) values (spc_name,spc_display);
# 删除操作 删除名字字符长度为8的男生
delete from tsinger where char_length(sname) = 8 and sex = '男';
# 根据是否出现异常,提交或回滚
if flag = 1 then
commit;
else
rollback;
end if;
set result = flag;
end;
# 事务回滚 表中存在display为'隐形的翅膀'的记录
call pro_transaction('老王','隐形的翅膀',@result);
select @result;
# 事务提交 表中不存在display为'踩不灭的烟头'的记录
call pro_transaction('杨坤','踩不灭的烟头',@result);
select @result;
游标
游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标充当指针的作用。尽管游标能遍历结果集中的所有行，但是它一次只能指向一行。
游标的一个常见用途就是保存查询结果，以便以后使用。游标的结果集是由select语句产生，如果处理过程需要重复使用一个记录集，那么创建一次游标而重复使用若干次，比重复查询数据库要快的多。
大部分数据库设计语言都能使用游标来检索SQL数据库中的数据，在程序中嵌入游标和在程序中嵌入SQL语句相同。
# 存储过程中使用游标:
# 计算tsinger中salary的总和并返回,当记录中display为null时 就设置为随机字符串uuid
drop procedure if exists pro_cursor;
create procedure pro_cursor(out sum decimal(11,2))
BEGIN
declare flag int default 1;
declare singer_id int default 0;
declare singer_sal decimal(7,2) default 0;
declare singer_dis varchar(100) default '';
# 声明游标cur1
declare cur1 cursor for select sid,salary,display from tsinger;
declare continue handler for not found set flag = 0;
set sum = 0;
# 开启游标
open cur1;
# 从游标中取出数据
fetch cur1 into singer_id,singer_sal,singer_dis;
# 循环操作
while flag = 1 do
if singer_sal is not null then
set sum = sum + singer_sal;
end if;
if singer_dis is null then
update tsinger set display = UUID() where sid = singer_id;
end if;
# 游标继续向下走
fetch cur1 into singer_id,singer_sal,singer_dis;
end while;
# 关闭游标
close cur1;
end;
触发器
触发器（trigger）是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，当对一个表进行增、删、改操作时就会触发，或者更改表结构时也会触发。
触发器是一种与表操作有关的数据库对象，当触发器所在的表上发生指定事件时，将会调用该对象，即表的操作事件触发表上触发器的执行。
触发器分为三类：DML触发器，DDL触发器，登录触发器。我们重点来看一下DML触发器和DDL触发器。DML触发器是指在数据库中发生DML操作时将会触发的事件。DDL触发器是指在数据库中发DDL操作时将会触发的事件。
-- 创建触发器的sql语句
create trigger trigger_name trigger_time trigger_event on table_name for each row
-- 触发器是与表操作相关的数据库对象，所以触发的命名与表相关，在创建
-- 触发器时需要指定表名table_name，表为永久性的表，所以触发器不能
-- 与临时表和视图建立关联。
-- trigger：创建触发器的关键字，定义触发器名称。
-- trigger_name：触发器的名称。
-- trigger_time：触发器的执行时间，它的值为before或者after，是指
-- 在激活它的语句之前还是之后执行。一般情况下是在激活它的语句运行完
-- 成后再执行触发器。
#trigger_event：触发事件，它的值可以使insert、update、delete。在#这个三个动作上我们需要了解数据库里两个临时的虚拟表：deleted、#inserted，在mysql中使用old和new关键字来表示。
#for each row：对每行都添加触发操作，只要被更改了就触发。
| 动作 | deleted表 | inserted表 |
|---|---|---|
| insert | 不存储记录 | 存储新插入的记录 |
| update | 存放之前的记录 | 存放更新后的记录 |
| delete | 存放被删除的记录 | 不存储记录 |
# 创建记录表trecord记录 tsinger表中display列的修改记录
drop table if exists trecord;
create table trecord(
rid varchar(32) primary key, -- 主键
sid int , -- 歌手id
sname varchar(20), -- 歌手名字
display varchar(100), -- 歌手描述
newdisplay varchar(100), -- 歌手新描述
updatetime datetime -- 修改时间
);
# 创建一个触发器,每当tsinger表修改描述时,记录修改详情
create trigger update_display_record after update on tsinger for each row
BEGIN
declare rid varchar(32) default '';
set rid = REPLACE(UUID(),'-','');
insert into trecord values (rid,old.sid,old.sname,old.display,new.display,now());
end;
# 修改 张韶涵的性别为女 , salary为99999.99 , display为 Angela Zhang
update tsinger set sex = '女',salary=99999.99,display = 'Angela Zhang' where sname = '张韶涵';
# 查看trecord表
select * from trecord;
# 查看触发器
show triggers;
扫一扫
975
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
