手机网站 自适应,制作自己的网站需要什么软件好,网站建设所需要的东西,wordpress发送邮件出现502版权申明#xff1a;本文用于个人学习记录#xff0c;学习课程为黑马程序员的mysql教程。如需获取官方的学习视频和文档资料#xff0c;请至黑马程序员官方获取。下面附上教学视频的链接地址#xff0c;向提供免费教学视频的老师致敬#xff0c;学如逆水行舟#xff0c;不…版权申明本文用于个人学习记录学习课程为黑马程序员的mysql教程。如需获取官方的学习视频和文档资料请至黑马程序员官方获取。下面附上教学视频的链接地址向提供免费教学视频的老师致敬学如逆水行舟不进则退共勉
黑马程序员 MySQL数据库入门到精通从mysql安装到mysql高级、mysql优化全囊括_哔哩哔哩_bilibili
1、多表关系 项目开发中在进行数据库表结构设计时会根据业务需求及业务模块之间的关系分析并设计表结构由于业务之间相互关联所以各个表结构之间也存在着各种联系基本上分为三种 一对多(多对一) 多对多 一对一 1.1 一对多
案例: 部门 与 员工的关系 关系: 一个部门对应多个员工一个员工对应一个部门 实现: 在多的一方建立外键指向一的一方的主键 1.2 多对多
案例: 学生 与 课程的关系 关系: 一个学生可以选修多门课程一门课程也可以供多个学生选择 实现: 建立第三张中间表中间表至少包含两个外键分别关联两方主键 对应的SQL脚本
-- 多表查询
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);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);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);
1.3 一对一
案例: 用户 与 用户详情的关系 关系: 一对一关系多用于单表拆分将一张表的基础字段放在一张表中其他详情字段放在另一张表中以提升操作效率 实现: 在任意一方加入外键关联另外一方的主键并且设置外键为唯一的(UNIQUE) 对应的SQL
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);
2、多表查询概述
2.1 数据准备
-- 创建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 empadd 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);
2.2 概述 多表查询就是指从多张表中查询数据。 2.3 分类
连接查询 内连接相当于查询 A 、 B 交集部分数据 外连接 左外连接查询左表所有数据以及两张表交集部分数据 右外连接查询右表所有数据以及两张表交集部分数据 自连接当前表与自身的连接查询自连接必须使用表别名 子查询 3、内连接 1隐式内连接 SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ; 2显式内连接 SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ; -- 内连接演示
-- 1. 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)
-- 表结构empdept
-- 连接条件emp.dept_id dept.id
-- 16条数据因为有一个员工没有部门信息
select emp.name, dept.name from emp, dept where emp.dept_id dept.id;
-- 起别名, 起了别名就只能通过别名.
select e.name, d.name from emp e, dept d where e.dept_id d.id;-- 2. 查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现) --- INNER JOIN ... ON ...
-- 表结构empdept
-- 连接条件emp.dept_id dept.id
select e.name,d.name from emp e inner join dept d on e.dept_id d.id;
-- inner可以省略
select e.name,d.name from emp e join dept d on e.dept_id d.id; 表的别名 : ①. tablea as 别名 1 , tableb as 别名 2 ; ②. tablea 别名 1 , tableb 别名 2 ; 注意事项一旦为表起了别名就不能再使用表名来指定对应的字段了此时只能够使用别名来指定字段。 4、外连接 1左外连接 SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ; 左外连接相当于查询表1(左表)的所有数据当然也包含表1和表2交集部分的数据。
2右外连接 SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ; 右外连接相当于查询表2(右表)的所有数据当然也包含表1和表2交集部分的数据。
-- 外连接演示
-- 1. 查询emp表的所有数据和对应的部门信息左外连接
-- 由于需求中提到要查询emp的所有数据所以是不能内连接查询的需要考虑使用外连接查询。
-- 表结构emp, dept
-- 连接条件emp.dept_id dept.id
-- 17条数据左外连接会完全包含左表的数据
select e.*, d.name from emp e left outer join dept d on e.dept_id d.id;
-- outer关键字可以省略
select e.*, d.name from emp e left join dept d on e.dept_id d.id;-- 2. 查询dept表的所有数据和对应的员工信息右外连接
-- 由于需求中提到要查询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 中表结构的先后顺序就可以了。而我们在日常开发使用时更偏向于左外连接。 5、自连接
5.1 自连接查询 自连接查询顾名思义就是自己连接自己也就是把一张表连接查询多次。我们先来学习一下自连接 的查询语法 SELECT 字段列表 FROM 表 A 别名 A JOIN 表 A 别名 B ON 条件 ... ; 而对于自连接查询可以是内连接查询也可以是外连接查询。
-- 自连接
-- 1. 查询员工及其所属领导的名字
-- 表结构emp
-- 16条数据
select a.name, b.name from emp a, emp b where a.managerid b.id;-- 2. 查询所有员工emp及其领导的名字emp如果员工没有领导也需要查询出来
-- 17条数据
select a.name, b.name from emp a left join emp b on a.managerid b.id;
select a.name 员工, b.name 领导 from emp a left join emp b on a.managerid b.id; 注意事项 : 在自连接查询中必须要为表起别名要不然我们不清楚所指定的条件、返回的字段到底是哪一张表的字段。 5.2 联合查询 对于union查询就是把多次查询的结果合并起来形成一个新的查询结果集。 SELECT 字段列表 FROM 表 A ... UNION [ ALL ] SELECT 字段列表 FROM 表 B ....; 对于联合查询的多张表的列数必须保持一致字段类型也需要保持一致。 union all 会将全部的数据直接合并在一起union 会对合并之后的数据去重。
-- 联合查询
-- 对于联合查询的多张表的列数必须保持一致字段类型也需要保持一致。
-- union all 会将全部的数据直接合并在一起union 会对合并之后的数据去重。
-- union all, union
-- 1. 将薪资低于5000的员工和年龄大于50岁的员工全部查询出来
-- 会有重复数据
select * from emp where salary 5000
union all
select * from emp where age 50;-- 去重
select * from emp where salary 5000
union
select * from emp where age 50;
-- select * from emp where salary 5000 || emp.age 50; 6、子查询
6.1 概述 1). 概念 SQL 语句中嵌套 SELECT 语句称为嵌套查询又称子查询。 SELECT * FROM t1 WHERE column1 ( SELECT column1 FROM t2 ); 子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。 2). 分类 根据子查询结果不同分为 标量子查询子查询结果为单个值 列子查询(子查询结果为一列) 行子查询(子查询结果为一行) 表子查询(子查询结果为多行多列) 根据子查询位置分为 A. WHERE之后 B. FROM之后 C. SELECT之后 6.2 标量子查询 子查询返回的结果是单个值数字、字符串、日期等最简单的形式这种子查询称为标量子查询 常用的操作符 -- 子查询
-- 标量子查询
-- 子查询返回的结果是单个值数字、字符串、日期等最简单的形式这种子查询称为标量子查询。
-- 常用的操作符
-- 1. 查询“销售部”的所有员工信息
-- a. 查询“销售部”部门ID
select id from dept where name 销售部;
-- b. 根据销售部部门ID查询员工信息
select * from emp where dept_id 4;
-- 用一条sql子查询搞定
select * from emp where dept_id (select id from dept where name 销售部);-- 2. 查询在“方东白”入职之后的员工信息
-- a. 查询方东白的入职日期
select entrydate from emp where name 方东白;
-- b. 根据方东白的入职日期查询在“方东白”入职之后的员工信息
select * from emp where entrydate 2009-02-12;
-- 合并
select * from emp where entrydate (select entrydate from emp where name 方东白); 6.3 列子查询 子查询返回的结果是一列可以是多行这种子查询称为列子查询。 常用的操作符 IN 、 NOT IN 、 ANY 、 SOME 、 ALL -- 列子查询
-- 子查询返回的结果是一列可以是多行这种子查询称为列子查询。
-- 常用的操作符IN 、NOT IN 、 ANY 、SOME 、 ALL
-- 1. 查询“销售部”和“市场部”的所有员工信息
-- a. 查询“销售部”和“市场部”的部门ID
select id from dept where name 销售部 or name 市场部;
-- b. 根据部门ID查询员工信息
select * from emp where dept_id 2 or dept_id 4;
-- 合并
select * from emp where dept_id in (select id from dept where name 销售部 or name 市场部);-- 2. 查询比财务部所有人工资都高的员工信息
-- a. 查询所有财务部的人员工资
select salary from emp where dept_id (select id from dept where name 财务部);
-- b. 比财务部所有人工资都高的员工信息
select * from emp where salary all (select salary from emp where dept_id (select id from dept where name 财务部));-- 3. 查询比研发部其中任意一人工资高的员工信息
-- a. 查询研发部所有人工资
select salary from emp where dept_id (select id from dept where name 研发部);
-- b. 比研发部任意一人工资高的员工信息
select * from emp where salary any (select salary from emp where dept_id (select id from dept where name 研发部));
-- any\some 一个满足条件就行
select * from emp where salary some (select salary from emp where dept_id (select id from dept where name 研发部));
6.4 行子查询
子查询返回的结果是一行可以是多列这种子查询称为行子查询。 常用的操作符 、 、 IN 、 NOT IN -- 行子查询
-- 子查询返回的结果是一行可以是多列这种子查询称为行子查询。
-- 常用的操作符 、 、IN 、NOT IN
-- 1. 查询与 张无忌 的薪资及直属领导相同的员工信息
-- a. 查询张无忌的薪资及直属领导
select salary, managerid from emp where name 张无忌;
-- b. 查询与 张无忌 的薪资及直属领导相同的员工信息
select * from emp where (salary,managerid) (select salary, managerid from emp where name 张无忌);
6.5 表子查询 子查询返回的结果是多行多列这种子查询称为表子查询。 常用的操作符 IN -- 表子查询
-- 子查询返回的结果是多行多列这种子查询称为表子查询。
-- 常用的操作符IN
-- 1. 查询与 鹿杖客 , 宋远桥 的职位和薪资相同的员工信息
-- a. 查询 鹿杖客 , 宋远桥 的职位和薪资
select job, salary from emp where name 鹿杖客 || name 宋远桥;
-- b. 查询与 鹿杖客 , 宋远桥 的职位和薪资相同的员工信息
select * from emp where (job,salary) in (select job, salary from emp where name 鹿杖客 || name 宋远桥);-- 2. 查询入职日期是 2006-01-01 之后的员工信息 , 及其部门信息
-- a. 入职日期是 2006-01-01 之后的员工信息
select * from emp where entrydate 2006-01-01;
-- b. 查询这部分员工, 对应的部门信息;
select e.*, d.* from (select * from emp where entrydate 2006-01-01) e left join dept d on e.dept_id d.id; 7、多表查询案例 数据环境准备 : -- 多表查询案例
-- 准备工作创建薪资等级表并且插入数据
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. 查询员工的姓名、年龄、职位、部门信息 隐式内连接
-- 表: emp , dept
-- 连接条件 : emp.dept_id dept.id
select e.name, e.age, e.salary, d.* from emp e, dept d where e.dept_id d.id;-- 2. 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息显式内连接
-- 表: emp , dept
-- 连接条件 : emp.dept_id dept.id
select e.name, e.age, e.salary, d.* from emp e join dept d on d.id e.dept_id where e.age 30;-- 3. 查询拥有员工的部门ID、部门名称
-- 表: emp , dept
-- 连接条件 : emp.dept_id dept.id
-- distinct 对查询结果去重
select distinct d.id, d.name from emp e, dept d where e.dept_id d.id;-- 4. 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来(外连接)
-- 表: emp , dept
-- 连接条件 : emp.dept_id dept.id
-- 外连接
select e.*, d.name from emp e left outer join dept d on d.id e.dept_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 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;-- 6. 查询 研发部 所有员工的信息及 工资等级
-- 表: emp , salgrade , dept
-- 连接条件 : 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.salary between s.losal and s.hisal and e.dept_id d.id)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 e where e.name 灭绝;
select * from emp where salary (select salary from emp e where e.name 灭绝);-- 9. 查询比平均薪资高的员工信息
-- select avg(salary) from emp;
select * from emp where salary (select avg(salary) from emp);-- 10.查询低于本部门平均工资的员工信息
select avg(salary) from emp where dept_id 1;
select avg(salary) from emp where dept_id 2;
select * from emp e where salary (select avg(salary) from emp where dept_id e.dept_id);-- 11. 查询所有的部门信息, 并统计部门的员工人数
-- select count(*) from emp where dept_id 1;
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;
