网站的文字被爬取了,东莞网站建设+信科网络,高校网络网站建设意义及措施,沈阳有资质做网站的公司有哪些多表查询
多表关系:
一对多 案例: 部门 与 员工的关系 关系: 一个部门对应多个员工#xff0c;一个员工对应一个部门 实现: 在多的一方建立外键#xff0c;指向一的一方的主键 多对多 案例: 学生 与 课程的关系 关系: 一个学生可以选修多门课程#xff0c;一门课程也可以…多表查询
多表关系:
一对多 案例: 部门 与 员工的关系 关系: 一个部门对应多个员工一个员工对应一个部门 实现: 在多的一方建立外键指向一的一方的主键 多对多 案例: 学生 与 课程的关系 关系: 一个学生可以选修多门课程一门课程也可以供多个学生选择 实现: 建立第三张中间表中间表至少包含两个外键分别关联两方主键
-- student
create table student(id int auto_increment primary key comment 主键ID,name varchar(10) comment 姓名,no varchar(10) comment 学号
) comment 学生表;insert into student values (null, 黛绮丝, 2000100101),(null, 谢逊,
2000100102),(null, 殷天正, 2000100103),(null, 韦一笑, 2000100104);-- course
create table course(id int auto_increment primary key comment 主键ID,name varchar(10) comment 课程名称
) comment 课程表;insert into course values (null, Java), (null, PHP), (null , MySQL) ,
(null, Hadoop);-- student_course
create table student_course(id int auto_increment comment 主键 primary key,studentid int not null comment 学生ID,courseid int not null comment 课程ID,constraint fk_courseid foreign key (courseid) references course (id),constraint fk_studentid foreign key (studentid) references student (id)
)comment 学生课程中间表;insert into student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2),
(null,2,3),(null,3,4);一对一 案例: 用户 与 用户详情的关系 单表拆分 关系: 一对一关系多用于单表拆分将一张表的基础字段放在一张表中其他详情字段放在另一张表中以提升操作效率 实现: 在任意一方加入外键关联另外一方的主键并且设置外键为唯一的(UNIQUE)
create table tb_user(id int auto_increment primary key comment 主键ID,name varchar(10) comment 姓名,age int comment 年龄,gender char(1) comment 1: 男 , 2: 女,phone char(11) comment 手机号
) comment 用户基本信息表;create table tb_user_edu(id int auto_increment primary key comment 主键ID,degree varchar(20) comment 学历,major varchar(50) comment 专业,primaryschool varchar(50) comment 小学,middleschool varchar(50) comment 中学,university varchar(50) comment 大学,userid int unique comment 用户ID,constraint fk_userid foreign key (userid) references tb_user(id)
) comment 用户教育信息表;insert into tb_user(id, name, age, gender, phone) values
(null,黄渤,45,1,18800001111),
(null,冰冰,35,2,18800002222),
(null,码云,55,1,18800008888),
(null,李彦宏,50,1,18800009999);insert into tb_user_edu(id, degree, major, primaryschool, middleschool,
university, userid) values
(null,本科,舞蹈,静安区第一小学,静安区第一中学,北京舞蹈学院,1),
(null,硕士,表演,朝阳区第一小学,朝阳区第一中学,北京电影学院,2),
(null,本科,英语,杭州市第一小学,杭州市第一中学,杭州师范大学,3),
(null,本科,应用数学,阳泉第一小学,阳泉区第一中学,清华大学,4);多表查询
就是指从多张表中查询数据。
-- 数据准备
MySQL [test] create database db CHARACTER SET utf8mb4;
MySQL [test] use db-- 创建dept表并插入数据
create table dept(id int auto_increment comment ID primary key,name varchar(50) not null comment 部门名称
)comment 部门表;INSERT INTO dept (id, name) VALUES (1, 研发部), (2, 市场部),(3, 财务部), (4,
销售部), (5, 总经办), (6, 人事部);-- 创建emp表并插入数据
create table emp(
id int auto_increment comment ID primary key,name varchar(50) not null comment 姓名,age int comment 年龄,job varchar(20) comment 职位,salary int comment 薪资,entrydate date comment 入职时间,managerid int comment 直属领导ID,dept_id int comment 部门ID
)comment 员工表;-- 添加外键
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id);INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id)
VALUES
(1, 金庸, 66, 总裁,20000, 2000-01-01, null,5),
(2, 张无忌, 20, 项目经理,12500, 2005-12-05, 1,1),
(3, 杨逍, 33, 开发, 8400,2000-11-03, 2,1),
(4, 韦一笑, 48, 开发,11000, 2002-02-05, 2,1),
(5, 常遇春, 43, 开发,10500, 2004-09-07, 3,1),
(6, 小昭, 19, 程序员鼓励师,6600, 2004-10-12, 2,1),
(7, 灭绝, 60, 财务总监,8500, 2002-09-12, 1,3),
(8, 周芷若, 19, 会计,48000, 2006-06-02, 7,3),
(9, 丁敏君, 23, 出纳,5250, 2009-05-13, 7,3),
(10, 赵敏, 20, 市场部总监,12500, 2004-10-12, 1,2),
(11, 鹿杖客, 56, 职员,3750, 2006-10-03, 10,2),
(12, 鹤笔翁, 19, 职员,3750, 2007-05-09, 10,2),
(13, 方东白, 19, 职员,5500, 2009-02-12, 10,2),
(14, 张三丰, 88, 销售总监,14000, 2004-10-12, 1,4),
(15, 俞莲舟, 38, 销售,4600, 2004-10-12, 14,4),
(16, 宋远桥, 40, 销售,4600, 2004-10-12, 14,4),
(17, 陈友谅, 42, null,2000, 2011-10-12, 1,null);查询
select * from dept,emp; -- 102 rows in set (0.00 sec) 6*17120 笛卡尔积
select * from emp , dept where emp.dept_id dept.id;
-- 由于id为17的员工没有dept_id字段值所以在多表查询时根据连接查询的条件并没有查询到。多表查询分类连接查询内连接相当于查询A、B交集部分数据隐式内连接SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;显式内连接SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;外连接左外连接查询左表所有数据以及两张表交集部分数据右外连接查询右表所有数据以及两张表交集部分数据自连接当前表与自身的连接查询自连接必须使用表别名子查询内连接
-- 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)表结构: emp , dept连接条件: emp.dept_id dept.id
select * from emp , dept where emp.dept_id dept.id ;
select emp.name , dept.name from emp , dept where emp.dept_id dept.id ;-- 为每一张表起别名,简化SQL编写
select e.name,d.name from emp e , dept d where e.dept_id d.id;
select e.name,d.name from emp e , dept where emp.dept_id dept.id ; -- 报错已别名-- 查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现) --- INNER JOIN ... ON
select e.name, d.name from emp e inner join dept d on e.dept_id d.id;
-- 为每一张表起别名,简化SQL编写
select e.name, d.name from emp e join dept d on e.dept_id d.id; -- inner可以省略外连接
左外连接左外连接相当于查询表1(左表)的所有数据包含交集SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;
右外连接右外连接相当于查询表2(右表)的所有数据包含交集SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;-- 查询emp表的所有数据, 和对应的部门信息
由于需求中提到要查询emp的所有数据所以是不能内连接查询的需要考虑使用外连接查询。
表结构: emp, dept
连接条件: emp.dept_id dept.id
select * from emp e left outer join dept d on e.dept_id d.id;
select e.*, d.name from emp e left outer join dept d on e.dept_id d.id;
select e.*, d.name from emp e left join dept d on e.dept_id d.id; -- outer可以省略查询dept表的所有数据, 和对应的员工信息(右外连接)
select d.*, e.* from emp e right outer join dept d on e.dept_id d.id;
select d.*, e.* from dept d left outer join emp e on e.dept_id d.id;
左外连接和右外连接是可以相互替换的只需要调整在连接查询时SQL中表结构的先后顺序就可以了。
而我们在日常开发使用时更偏向于左外连接。自连接查询
自连接查询顾名思义就是自己连接自己也就是把一张表连接查询多次。而对于自连接查询可以是内连接查询也可以是外连接查询。
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ; -- 必须用别名查询员工 及其 所属领导的名字 -- 16 rows没有领导的没查出
select a.name , b.name from emp a , emp b where a.managerid b.id;查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来 -- 17 rows
select a.name 员工, b.name 领导 from emp a left join emp b on a.managerid
b.id;联合查询
对于union查询就是把多次查询的结果合并起来形成一个新的查询结果集。
SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;对于联合查询的多张表的列数必须保持一致字段类型也需要保持一致。
union all 会将全部的数据直接合并在一起 union 会对合并之后的数据去重。-- 将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来.
-- 去重 8 rows in set (0.00 sec)
select * from emp where salary 5000
union
select * from emp where age 50;-- 未去重 9 rows in set (0.00 sec)
select * from emp where salary 5000
union all
select * from emp where age 50;子查询
SQL语句中嵌套SELECT语句称为嵌套查询又称子查询。
子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。
SELECT * FROM t1 WHERE column1 ( SELECT column1 FROM t2 );根据子查询结果不同分为A. 标量子查询子查询结果为单个值B. 列子查询(子查询结果为一列)C. 行子查询(子查询结果为一行)D. 表子查询(子查询结果为多行多列)根据子查询位置分为A. WHERE之后B. FROM之后C. SELECT之后标量子查询
子查询返回的结果是单个值数字、字符串、日期等最简单的形式这种子查询称为标量子查询。 常用的操作符
案例:
A. 查询 销售部 的所有员工信息
完成这个需求时我们可以将需求分解为两步
①. 查询 销售部 部门ID
select id from dept where name 销售部; -- 4 一条结果
②. 根据 销售部 部门ID, 查询员工信息
select * from emp where dept_id 4;select * from emp where dept_id (select id from dept where name 销售部);B. 查询在 方东白 入职之后的员工信息
完成这个需求时我们可以将需求分解为两步
①. 查询 方东白 的入职日期
select entrydate from emp where name 方东白; -- 2009-02-12一条结果
②. 查询指定入职日期之后入职的员工信息
select * from emp where entrydate 2009-02-12;select * from emp where entrydate (select entrydate from emp where name 方东
白);列子查询
子查询返回的结果是一列可以是多行这种子查询称为列子查询。 常用的操作符IN 、NOT IN 、 ANY 同SOME 有任意一个满足条件、 ALL满足所有返回值
-- 查询 销售部 和 市场部 的所有员工信息
分解为以下两步:
①. 查询 销售部 和 市场部 的部门ID
select id from dept where name 销售部 or name 市场部; -- 2,4 一列
②. 根据部门ID, 查询员工信息
select * from emp where dept_id in(2,4);select * from emp where dept_id in (select id from dept where name 销售部 or
name 市场部);-- 查询比 财务部 所有人工资都高的员工信息
分解为以下两步:
①. 查询所有 财务部 人员工资
select id from dept where name 财务部; -- 3
select salary from emp where dept_id (select id from dept where name 财务部); -- 3个结果
②. 比 财务部 所有人工资都高的员工信息
select * from emp where salary all ( select salary from emp where dept_id
(select id from dept where name 财务部) );-- 查询比研发部其中任意一人工资高的员工信息
分解为以下两步:
①. 查询研发部所有人工资
select salary from emp where dept_id (select id from dept where name 研发部);
②. 比研发部其中任意一人工资高的员工信息
select * from emp where salary any ( select salary from emp where dept_id
(select id from dept where name 研发部) );行子查询
子查询返回的结果是一行可以是多列这种子查询称为行子查询。 常用的操作符 、 、IN 、NOT IN
案例:
A. 查询与 张无忌 的薪资及直属领导相同的员工信息 ;
这个需求同样可以拆解为两步进行:
①. 查询 张无忌 的薪资及直属领导
select salary, managerid from emp where name 张无忌; # 返回单行两列数据
-------------------
| salary | managerid |
-------------------
| 12500 | 1 |
-------------------
②. 查询与 张无忌 的薪资及直属领导相同的员工信息 ;
select * from emp where (salary,managerid) (select salary, managerid from emp
where name 张无忌);表子查询
子查询返回的结果是多行多列这种子查询称为表子查询。 常用的操作符IN
-- 查询与 鹿杖客 , 宋远桥 的职位和薪资相同的员工信息
分解为两步执行:
①. 查询 鹿杖客 , 宋远桥 的职位和薪资
select job, salary from emp where name 鹿杖客 or name 宋远桥;
----------------
| job | salary |
----------------
| 职员 | 3750 |
| 销售 | 4600 |
----------------
②. 查询与 鹿杖客 , 宋远桥 的职位和薪资相同的员工信息
select * from emp where (job,salary) in ( select job, salary from emp where name 鹿杖客 or name 宋远桥 );-- 查询入职日期是 2006-01-01 之后的员工信息 , 及其部门信息
分解为两步执行:
①. 入职日期是 2006-01-01 之后的员工信息
select * from emp where entrydate 2006-01-01; # 返回表去连表查询
②. 查询这部分员工, 对应的部门信息;
select e.*, d.* from (select * from emp where entrydate 2006-01-01) e left
join dept d on e.dept_id d.id ;多表查询案例
-- 主要涉及到的表就三张emp员工表、dept部门表、salgrade薪资等级表新建 。
create table salgrade(
grade int,
losal int,
hisal int
) comment 薪资等级表;insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,30000);-- 1、查询员工的姓名、年龄、职位、部门信息 隐式内连接
select e.name , e.age , e.job , d.name from emp e , dept d where e.dept_id d.id;-- 2、查询年龄小于30岁的员工的姓名、年龄、职位、部门信息显式内连接
select e.name , e.age , e.job , d.name from emp e inner join dept d on e.dept_id d.id where e.age 30;-- 3、查询拥有员工的部门ID、部门名称 distinct 去重
select distinct d.id , d.name from emp e , dept d where e.dept_id d.id;-- 4、查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来(外连接)
select e.*, d.name from emp e left join dept d on e.dept_id d.id where e.age 40 ;-- 5、查询所有员工的工资等级
表: emp , salgrade
连接条件 : emp.salary salgrade.losal and emp.salary salgrade.hisal
-- 方式一
select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary s.losal and e.salary s.hisal;
-- 方式二
select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary between s.losal and s.hisal;-- select e.* , s.grade ,s.losal,s.hisal from emp e , salgrade s ; 笛卡尔积 -- 6、查询 研发部 所有员工的信息及 工资等级
表: emp , salgrade , dept 3个表至少两个条件
连接条件 : emp.salary between salgrade.losal and salgrade.hisal , emp.dept_id dept.id
查询条件 : dept.name 研发部select e.* , s.grade from emp e , dept d , salgrade s where e.dept_id d.id and (
e.salary between s.losal and s.hisal ) and d.name 研发部;-- 7、查询 研发部 员工的平均工资
表: emp , dept
连接条件 : emp.dept_id dept.id
select avg(e.salary) from emp e, dept d where e.dept_id d.id and d.name 研发部;-- 8、查询工资比 灭绝 高的员工信息。
①. 查询 灭绝 的薪资
select salary from emp where name 灭绝;
②.查询比她工资高的员工数据
select * from emp where salary ( select salary from emp where name 灭绝 );-- 9、 查询比平均薪资高的员工信息
①. 查询员工的平均薪资
select avg(salary) from emp;
②. 查询比平均薪资高的员工信息
select * from emp where salary ( select avg(salary) from emp );-- 10、查询低于本部门平均工资的员工信息
①. 查询指定部门平均薪资
select avg(e1.salary) from emp e1 where e1.dept_id 1;
select avg(e1.salary) from emp e1 where e1.dept_id 2;
②. 查询低于本部门平均工资的员工信息
select * from emp e2 where e2.salary ( select avg(e1.salary) from emp e1 where e1.dept_id e2.dept_id );
-- select *, ( select avg(e1.salary) from emp e1 where e1.dept_id e2.dept_id ) 平均 from emp e2 where e2.salary ( select avg(e1.salary) from emp e1 where e1.dept_id e2.dept_id );-- 11、查询所有的部门信息, 并统计部门的员工人数
select d.id, d.name , ( select count(*) from emp e where e.dept_id d.id ) 人数 from dept d;-- 12、查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称
表: student , course , student_course
连接条件: student.id student_course.studentid , course.id student_course.courseid
select s.name , s.no , c.name from student s , student_course sc , course c where s.id sc.studentid and sc.courseid c.id ;事务
是一组操作的集合它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求即这些操作要么同时成功要么同时失败。
事务操作 数据准备
drop table if exists account;create table account(id int primary key AUTO_INCREMENT comment ID,name varchar(10) comment 姓名,money double(10,2) comment 余额
) comment 账户表;insert into account(name, money) VALUES (张三,2000), (李四,2000);1
完整的操作
-- 1. 查询张三余额
select * from account where name 张三;
-- 2. 张三的余额减少1000
update account set money money - 1000 where name 张三;
-- 3. 李四的余额增加1000
update account set money money 1000 where name 李四;事务操作
-- 方式一SELECT autocommit ; -- 查看/设置事务提交方式 1自动 0 手动
SET autocommit 0 ; -- 设置成手动
COMMIT; -- 提交事务
ROLLBACK; -- 回滚事务-- 方式二
start transaction-- 开启事务update account set money money - 1000 where name 张三;
update account set money money 1000 where name 李四;commit; -- 如果正常执行完毕, 则提交事务
-- rollback; -- 如果执行过程中报错, 则回滚事务事务四大特性简称ACID
原子性Atomicity事务是不可分割的最小操作单元要么全部成功要么全部失败。
一致性Consistency事务完成时必须使所有的数据都保持一致状态。
隔离性Isolation数据库系统提供的隔离机制保证事务在不受外部并发操作影响的独立环境下运行。
持久性Durability事务一旦提交或回滚它对数据库中的数据的改变就是永久的。并发事务问题
1). 赃读一个事务读到另外一个事务执行过还没有提交的数据。
2). 不可重复读一个事务先后读取同一条记录但两次读取的数据不同称之为不可重复读。
3). 幻读一个事务按照条件查询数据时没有对应的数据行但是在插入数据时又发现这行数据已经存在好像出现了 幻影。事务隔离级别
Read uncommitted
Read committed -- oracle 默认的 不会脏读
Repeatable Read(默认) -- mysql默认只会产生幻读
Serializable -- 事务间需等待不能并行
-- 注意事务隔离级别越高数据越安全但是性能越低。SELECT TRANSACTION_ISOLATION; -- 查看事务隔离级别SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED |READ COMMITTED | REPEATABLE READ | SERIALIZABLE }据库中的数据的改变就是永久的。 ### 并发事务问题mysql
1). 赃读一个事务读到另外一个事务执行过还没有提交的数据。
2). 不可重复读一个事务先后读取同一条记录但两次读取的数据不同称之为不可重复读。
3). 幻读一个事务按照条件查询数据时没有对应的数据行但是在插入数据时又发现这行数据已经存在好像出现了 幻影。事务隔离级别
Read uncommitted
Read committed -- oracle 默认的 不会脏读
Repeatable Read(默认) -- mysql默认只会产生幻读
Serializable -- 事务间需等待不能并行
-- 注意事务隔离级别越高数据越安全但是性能越低。SELECT TRANSACTION_ISOLATION; -- 查看事务隔离级别SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED |READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
