数据库day（03）事务、约束、多表联查

关注

原创 2022-06-11 10:41:05 245 阅读 AI 写同款· GEO 优化 ›

智慧工地算法视频分析服务器区域入侵检测AI视频分析技术在煤矿的实战应用通过实时分析和处理井下视频资料，智慧矿山算法视频分析服务器不仅能够及时发现并预警人员违规行为和设备异常状况，还能够通过智能分析预防潜在的安全风险，从而显著提高煤矿的安全管理水平和生产效率。阅读详情

事务

概念：数据库事务(Database Transaction)，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。

特性：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中如果发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务隔离分为不同级别，包括

读未提交（Read uncommitted）安全性最差,可能发生并发数据问题,性能最好
读提交（read committed） Oracle默认的隔离级别
可重复读（repeatable read）MySQL默认的隔离级别,安全性较好,性能一般
串行化（Serializable）表级锁，读写都加锁，效率低下,安全性高,不能并发

查询mysql的隔离级别

在默认情况下，MySQL每执行一条SQL语句，都是一个单独的事务。如果需要在一个事务中包含多条SQL语句，那么需要手动开启事务和结束事务。

开启事务：start transaction；
结束事务：commit（提交事务）或rollback（回滚事务）

约束：

默认约束:default

CREATE TABLE test01(
id INT PRIMARY KEY AUTO_INCREMENT,
sex CHAR(3) DEFAULT '男'
)

检查约束：check

CREATE TABLE tb_user (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, #自增主键
NAME VARCHAR(50) NOT NULL UNIQUE, #非空，唯一索引
sex CHAR(2) DEFAULT '男', #默认值
age INT,
CHECK (age>0 AND age<=200) 
);

外键约束：子表里主键的值必须来自于主表，主表里的记录想要删除时，必须保证子表没有引用

CREATE TABLE tb_user(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
age INT,
phone VARCHAR(11)
)
CREATE TABLE tb_user_address(
user_id INT PRIMARY KEY,
daaress VARCHAR(100),
FOREIGN KEY(user_id) REFERENCES tb_user(id)
)

表关联 association

表的关系分为四种

一对一 one to one QQ和QQ邮箱，员工和员工编号

一对多 one to many 最常见，部门和员工，用户和订单

多对一 many to one 一对多反过来，员工和部门，订单和用户

多对多 many to many 老师和学生，老师和课程

多表联查：join，笛卡尔积，子查询

courses表：

scores表：

students表：

teachers表：

join，笛卡尔积查询练习：

#统计陈冰老师能讲的课程
SELECT tname,cname FROM courses JOIN teachers
ON teachers.tno=courses.tno
AND teachers.tname="陈冰"
#或者：
SELECT tname,cname FROM courses ,teachers
WHERE teachers.tno=courses.tno
AND teachers.tname="陈冰"

子查询：当子查询查询出的结果不止一个时，需要用in连接

SELECT tname FROM teachers WHERE tno=(
SELECT tno FROM courses WHERE cname="高等数学"
)

//三种方式查询可以教高等数学的老师
SELECT tname FROM teachers WHERE tno=(
SELECT tno FROM courses WHERE cname="高等数学"
)

SELECT teachers.tname FROM courses JOIN teachers
ON courses.tno=teachers.tno
WHERE courses.cname="高等数学"

SELECT teachers.tname FROM courses ,teachers
WHERE courses.tno=teachers.tno
AND courses.cname="高等数学"

课程回顾:

数据库相关SQL

查询所有 show databases;
创建 create database db1 charset=utf8/gbk;
查看数据库信息: show create database db1;
删除数据库: drop database db1;
使用: use db1;

表相关SQL

创建表: create table t1(name varchar(20),age int) charset=utf8/gbk;
查询所有: show tables;
查询表信息: show create table t1;
表字段: desc t1;
删除表: drop table t1;
修改表名: rename table t1 to t2;
添加表字段: alter table t1 add gender varchar(5) first/after xxx;
删除表字段: alter table t1 drop gender;
修改表字段: alter table t1 change 原名新名新类型;

数据相关SQL

插入: insert into t1(字段1名,字段2名) values(值1,值2),(值1,值2),(值1,值2);
查询: select name,age from t1 where 条件;
修改: update t1 set xxx=xxx,xxx=xxx where 条件;
删除: delete from t1 where 条件;

综合练习题2

创建数据库newdb1, 字符集utf8并使用 create database newdb1 charset=utf8; use newdb1;
在数据库中创建员工表emp 字段:id,name,sal(工资),deptId(部门id) 字符集utf8 create table emp(id int,name varchar(50),sal int,deptId int)charset=utf8;
创建部门表dept 字段:id,name,loc(部门地址) 字符集utf8 create table dept(id int,name varchar(20),loc varchar(50))charset=utf8;
部门表插入以下数据: 1 神仙部天庭 2 妖怪部盘丝洞 insert into dept values(1,'神仙部','天庭'),(2,'妖怪部','盘丝洞');
员工表插入一下数据: 1 悟空 5000 1 , 2 八戒 2000 1 , 3 蜘蛛精 8000 2 , 4 白骨精 9000 2 insert into emp values(1,'悟空',5000,1),(2,'八戒',2000,1),(3,'蜘蛛精',8000,2),(4,'白骨精',9000,2);
查询工资6000以下的员工姓名和工资 select name,sal from emp where sal<6000;
修改神仙部的名字为取经部
update dept set name='取经部' where name='神仙部';
给员工添加奖金comm字段 alter table emp add comm int;
修改员工表中部门id为1的奖金为500 update emp set comm=500 where deptId=1;
把取经部的地址改成五台山 update dept set loc="五台山" where name='取经部';
修改奖金字段为性别gender字段类型为varchar alter table emp change comm gender varchar(5);
修改孙悟空和猪八戒性别为男
update emp set gender='男' where deptId=1;
删除没有性别的员工(null不能用= 要用is) delete from emp where gender is null;
删除性别字段
alter table emp drop gender;
删除表和删除数据库 drop table emp; drop table dept; drop database newdb1;

SQL分类

DDL: 数据定义语言, 包括: 数据库向和表相关的SQL
DML: 数据操作语言, 包括: 增删改查
DQL: 数据查询语言, 只包括查询 select
TCL: 事务控制语言, 包含和事务相关的内容
DCL: 数据控制语言, 包含用户管理,权限分配相关SQL

*.sql 批处理文件

把老师的emp.zip解压出来得到一个emp.sql文件, 把这个文件放到某个盘的根目录比如 D盘根目录, 然后在命令行执行以下指令
格式: source 路径; 举例: source d:/emp.sql;
测试: show databases; 检查里面是否多了一个empdb; show tables; 会出现两个表 emp 和dept select * from emp; 检查里面的数据
如果出现乱码执行 set names utf8;

数据类型

整数: int(m)和bigint(m) , bigint相当于Java中的long, m代表显示长度,m=5 存18 得到00018, 需要结合zerofill关键字使用举例: create table t1(age int(5) zerofill); insert into t1 values(18); select * from t1;
浮点数: double(m,d) m代表总长度, d代表小数长度 m=5,d=3 23.123 举例: create table t2(price double(5,3)); insert into t2 values(54.321); insert into t2 values(54.332211); insert into t2 values(5433.2211); //会报错或出现99.999
字符串:
- char(m): 固定长度, m=5 存"abc" 占5个字符长度, 执行效率略高,当存储内容长度固定时使用,比如:性别 m最大值255
- varchar(m):可变长度, m=5 存"abc" 占3个字符长度,更节省存储空间,m最大值65535, 长度在255以内的建议使用
- text(m):可变长度, m最大值65535 , 建议长度大于255时使用
日期:
- date:保存年月日
- time:保存时分秒
- datetime:保存年月日时分秒,默认值null
- timestamp(时间戳举例1970年1月1日的毫秒数):保存年月日时分秒,默认值为当前系统时间测试: create table t3(t1 date,t2 time,t3 datetime,t4 timestamp); insert into t3 values("2021-11-22","10:38:20",null,null); insert into t3 values(null,null,"2021-11-22 10:38:20",null);

主键约束

主键: 表示数据唯一性的字段称为主键
约束: 创建表时,给表字段添加的限制条件
主键约束:限制主键的值唯一且非空.
举例: create table t4(id int primary key,name varchar(20)); insert into t4 values(1,'aaa'); insert into t4 values(1,'bbb'); 报错重复数据 insert into t4 values(null,'ccc'); 报错不能为null

主键约束+自增

自增规则:从历史最大值+1
create table t5(id int primary key auto_increment,name varchar(20)); insert into t5 values(null,'aaa'); insert into t5 values(null,'bbb'); insert into t5 values(10,'ccc'); insert into t5 values(null,'ddd'); delete from t5 where id>=10; insert into t5 values(null,'eee');
truncate table t5; //把表删除并重新创建此时计数器才会清零

去重distinct

查询员工表中有哪几种不同的工作? select distinct job from emp;
查询员工表中有哪几个不同的部门id?
select distinct dept_id from emp;

is null 和 is not null

查询没有上级领导的员工姓名 select name from emp where manager is null;
查询有上级领导的员工姓名 select name from emp where manager is not null;

and和or

多个条件同时满足时使用and
多个条件满足一个就可以时使用or

查询1号部门工资高于2000的员工信息 select * from emp where dept_id=1 and sal>2000;
查询3号部门的员工或者工资等于5000的员工信息 select * from emp where dept_id=3 or sal=5000;
查询孙悟空和猪八戒的员工信息 select * from emp where name="孙悟空" or name="猪八戒";

比较运算符 > < >= <= = !=和<>

查询不是程序员的姓名和工资 select name,sal from emp where job!="程序员"; select name,sal from emp where job<>"程序员";

两者之间between x and y 包含x和y

查询工资在2000到3000之间的员工信息 select * from emp where sal between 2000 and 3000; select * from emp where sal not between 2000 and 3000; 范围取反

in关键字

当查询某个字段的值为多个值的时候使用in

查询工资为3000,1500,5000的员工信息 select * from emp where sal=3000 or sal=1500 or sal=5000; select * from emp where sal in(3000,1500,5000);
查询孙悟空,猪八戒和白骨精的信息 select * from emp where name in('孙悟空','猪八戒','白骨精');

综合练习题

查询1号部门有哪几种不同的工作? select distinct job from emp where dept_id=1;
查询1号部门中有上级领导的员工信息 select * from emp where dept_id=1 and manager is not null;
查询工作是程序员,销售和人事的员工信息 select * from emp where job in('程序员','销售','人事');
查询有奖金的员工信息
select * from emp where comm>0;

模糊查询like

%: 代表0或多个未知字符
_:代表1个未知字符
举例: 以x开头 x% 以x结尾 %x 包含x %x% 第二个字符是x x% 以x开头以y结尾 x%y 第二个是x倒数第三个是y x%y

查询姓孙员工信息
select * from emp where name like "孙%";
查询名字以精结尾的员工姓名 select name from emp where name like "%精";
查询工作第二个字是售的员工姓名和工作 select name,job from emp where job like "_售%";
查询名字中包含僧并且工资大于2000的员工姓名和工资 select name,sal from emp where name like "%僧%" and sal>2000;

排序 order by

格式: order by 字段名 asc(默认升序)/desc降序;

查询员工姓名和工资,按照工资升序 select name,sal from emp order by sal;
查询员工姓名和工资,按照工资降序 select name,sal from emp order by sal desc;
查询1号部门的姓名和工资并且按照工资降序排序 select name,sal from emp where dept_id=1 order by sal desc;
查询每个员工的姓名,工资和部门id按照部门id升序排序,如果部门id相同则按照工资降序排序 select name,sal,dept_id from emp order by dept_id,sal desc;

分页查询 limit

格式: limit 跳过的条数,请求的条数(每页的条数)
举例: 跳过的条数=(请求页数-1)*每页条数第一页的5条数据 limit 0,5 第一页的10条 limit 0,10 第2页的10条 limit 10,10 第5页的10条 limit 40,10 第8页的9条 limit 63,9
第15页的3条 limit 42,3

查询工资最高的前三个人的信息 select * from emp order by sal desc limit 0,3;
查询员工的id和名字,请求第二页的3条数据 select id,name from emp limit 3,3;
查询工资最低的员工信息
select * from emp order by sal limit 0,1;

综合练习题

查询员工表中3号部门工资高于1500的员工信息 select * from emp where dept_id=3 and sal>1500;
查询2号部门员工或者没有领导的员工信息 select * from emp where dept_id=2 or manager is null;
查询有领导的员工姓名,工资按照工资降序排序 select name,sal from emp where manager is not null order by sal desc;
查询2号和3号部门的员工姓名和入职日期hiredate 按照入职日期降序排序 select name,hiredate from emp where dept_id in(2,3) order by hiredate desc;
查询名字中包含僧和包含精的员工姓名 select name from emp where name like "%僧%" or name like "%精%";
查询工资高于2000的工作有哪几种? select distinct job from emp where sal>2000;
查询工资升序第4页的2条数据 select * from emp order by sal limit 6,2;

别名

select name from emp; select name as "名字" from emp; select name "名字" from emp; select name 名字 from emp;

数值计算

查询每个员工的姓名,工资和年终奖(3个月的工资) select name,sal,3*sal 年终奖 from emp;
给3号部门的员工每人涨薪5块钱
update emp set sal=sal+5 where dept_id=3;

聚合函数

可以对查询的多条数据进行统计查询, 统计方式包括: 求平均值,最大值,最小值,求和,计数

平均值avg(字段名)

查询2号部门的平均工资 select avg(sal) from emp where dept_id=2;
查询销售的平均工资 select avg(sal) from emp where job="销售";

最大值max(字段名)

查询1号部门的最高工资 select max(sal) from emp where dept_id=1;

最小值min(字段名)

查询1号部门的最低工资 select min(sal) from emp where dept_id=1;

求和sum(字段名)

查询1号部门的工资总和 select sum(sal) from emp where dept_id=1;

计数count(*)

查询工资高于2000的员工人数 select count(*) from emp where sal>2000;
查询2号部门的平均工资,最高工资,最低工资,工资总和,人数 select avg(sal) 平均工资,max(sal) 最高工资,min(sal) 最低工资,sum(sal) 工资总和,count(*) 人数 from emp where dept_id=2;

综合练习

查询1号部门名字中包含僧的员工信息 select * from emp where dept_id=1 and name like "%僧%";
查询2号和3号部门中工资高于1500的员工人数 select count(*) from emp where dept_id in(2,3) and sal>1500;
查询名字里面包含精的最高工资 select max(sal) from emp where name like "%精%";
查询程序员的平均工资 select avg(sal) from emp where job="程序员";
查询销售的最低工资 select min(sal) from emp where job="销售";
查询有领导的员工人数 select count(*) from emp where manager is not null;
查询3号部门的人数和平均工资,起别名 select count(*) 人数,avg(sal) 平均工资 from emp where dept_id=3;

分组查询group by

将某个字段相同值的数据划分为一组, 然后以组为单位进行统计查询

查询每个部门的平均工资 select dept_id,avg(sal) from emp group by dept_id;
查询每种工作的平均工资 select job,avg(sal) from emp group by job;
查询每个部门的最高工资 select dept_id,max(sal) from emp group by dept_id;
查询每种工作的人数 select job,count(*) from emp group by job;
查询每个部门工资高于2000的人数 select dept_id,count(*) from emp where sal>2000 group by dept_id;
查询每个部门有领导的员工的人数 select dept_id,count(*) from emp where manager is not null group by dept_id;

having关键字

where后面只能写普通字段的条件,不能写聚合函数函数.
having关键字作用和where类似都是用来添加条件的, 但是having后面专门写聚合函数条件,而且having要和group by分组查询结合使用, 写在分组关键字的后面

查询每个部门的平均工资要求平均工资大于2000 select dept_id,avg(sal) from emp group by dept_id having avg(sal)>2000 ; 别名用法 select dept_id,avg(sal) a from emp group by dept_id having a>2000 ;
查询每种工作的人数,只查询人数大于1 的 select job,count(*) c from emp group by job having c>1;
查询每个部门的工资总和,只查询有领导的员工, 并且要求工资总和大于5400 select dept_id,sum(sal) s from emp where manager is not null group by dept_id having s>5400;
查询每个部门的平均工资, 只查询工资在1000到3000之间的,并且过滤掉平均工资低于2000的 select dept_id,avg(sal) a from emp where sal between 1000 and 3000 group by dept_id having a>=2000;

各个关键字的顺序

select * from 表名 where 普通字段条件 group by 分组字段名 having 聚合函数条件 order by 排序字段名 desc limit 跳过条数,请求条数;

子查询(嵌套查询)

use empdb;

查询工资大于2号部门平均工资的员工信息 select avg(sal) from emp where dept_id=2; select * from emp where sal>(select avg(sal) from emp where dept_id=2);
查询工资高于程序员最高工资的员工信息 select max(sal) from emp where job="程序员"; select * from emp where sal>(select max(sal) from emp where job="程序员");
查询工资最高的员工信息 select * from emp where sal=(select max(sal) from emp);
查询和孙悟空相同工作的员工信息 select * from emp where job=(select job from emp where name='孙悟空') and name!='孙悟空';
查询拿最低工资员工的同事们的信息(同事指同一部门) select min(sal) from emp; select dept_id from emp where sal=(select min(sal) from emp); select * from emp where dept_id=(select dept_id from emp where sal=(select min(sal) from emp)) and sal!=(select min(sal) from emp);

表和表之间如何建立关系

一对一: 在AB 任意一张表里面添加一个建立关系的字段指向另外一张表的主键
一对多: 在一对多的两张表中, 在"多"的表里面添加建立关系的字段指向另外一张表的主键
多对多: 创建一个单独的关系表, 表里面有两个字段指向另外两个表的主键
多对多举例: 苍老师: 小红,小花,小绿传奇哥: 小花,小绿 create table t(id int primary key auto_increment,name varchar(20)); create table s(id int primary key auto_increment,name varchar(20)); create table t_s(tid int ,sid int); insert into t values(null,'苍老师'),(null,'传奇哥'); insert into s values(null,'小红'),(null,'小花'),(null,'小绿'); insert into t_s values(1,1),(1,2),(1,3),(2,2),(2,3);

老师表:t
学生表:s
关系表:t_s
1. 查询每个老师名字和对应的学生名字 select t.name,s.name from t join t_s ts on t.id=ts.tid join s on s.id=ts.sid;
2. 查询苍老师的学生姓名 select s.name from t join t_s ts on t.id=ts.tid join s on s.id=ts.sid where t.name="苍老师";

关联查询

查询存在关联关系的表的查询方式称为关联查询
关联查询的方式包括: 等值连接, 内连接, 外连接

等值连接

格式: select * from A,B where 关联关系 and 其它条件;

查询工资高于2000的员工的姓名和对应的部门名 select e.name,d.name from emp e,dept d where e.dept_id=d.id and sal>2000;
查询程序员的姓名,工资,部门名,部门地点
select e.name,sal,d.name,loc from emp e,dept d where e.dept_id=d.id and job='程序员';

内连接

等值连接和内连接查询到的是一样的数据, 推荐使用内连接
格式: select * from A join B on 关联关系 where 其它条件;

查询工资高于2000的员工的姓名和对应的部门名 select e.name,d.name from emp e join dept d on e.dept_id=d.id where sal>2000;
查询程序员的姓名,工资,部门名,部门地点
select e.name,sal,d.name,loc from emp e join dept d on e.dept_id=d.id where job='程序员';

外连接

等值连接和内连接查询到的是两个表的交集数据
外连接查询到的是一张表的全部和另外一张表的交集
格式: select * from A left/right join B on 关联关系 where 其它条件; insert into emp(name) values("灭霸");

查询所有员工姓名和对应的部门名 select e.name,d.name from emp e left join dept d on e.dept_id=d.id;
查询所有部门的名字,地址, 和对应的员工姓名,工资. select d.name,loc,e.name,sal from emp e right join dept d on e.dept_id=d.id;