数据库简介
SQL
SQL简介
SQL分类
小结：
数据库模型和JAVA的关系
DDL
DDL主要用于查询数据库，表和字段。
-- 查询所有数据库
show databases ;
-- 创建数据库
create database db02;
DDL - 表结构
创建表
-- 创建: 基本语法
create table tb_user(
id int comment 'ID, 唯一标识',
username varchar(20) comment '用户名',
name varchar(10) comment '姓名',
age int comment '年龄',
gender char(1) comment '性别'
) comment '用户表';
-- 创建: 基本语法 (约束)
create table tb_user(
id int primary key auto_increment comment 'ID, 唯一标识',
username varchar(20) not null unique comment '用户名',
name varchar(10) not null comment '姓名',
age int comment '年龄',
gender char(1) default '男' comment '性别'
) comment '用户表';
字段类型
字符串类型：
日期类型：
注意：一个表中需要有creat_time, update_time，并且两者都是not null的。
DDL - 查看表结构
-- DDL： 查看表结构
-- 查看：当前数据库下的表
show tables;
-- 查看： 查看指定表结构
desc tb_emp;
-- 查看： 数据库的建表语句
show create table tb_emp;
DDL - 修改表结构
-- DDL：修改表结构
-- 修改：为表 tb_emp 添加字段 qq varchar(11)
alter table tb_emp add qq varchar(11) comment 'QQ';
-- 修改：修改 tb_emp 字段类型 qq varchar(13)
alter table tb_emp modify qq varchar(13) comment 'QQ';
-- 修改： 修改 tb_emp 字段名 qq 为 qq_num varchar(13)
alter table tb_emp change qq qq_num varchar(13) comment 'QQ';
-- 修改：删除 tb_emp 的 qq_num 字段
alter table tb_emp drop column qq_num;
-- 修改：将 tb_emp 表名修改为 emp
rename table tb_emp to emp;
DDL - 删表
-- DDL：删除表结构
-- 删除：删除 tb_emp 表
drop table if exists tb_emp;
小结：
DML – 数据操作语言
DML - 插入数据 - insert
-- 1. 为 tb_emp 表的 username， name， gender 字段插入值
insert into tb_emp(username, name, gender, create_time, update_time ) values('wuji', '无忌', 1, now(), now());
-- 2. 为 tb_emp 表的 所有字段插入值
insert into tb_emp(id, username, password, name, gender, image, job, entrydate, create_time, update_time)
values (null, 'zhiruo', '123', '周芷若', 2, '1.jpg', 1, '2010-01-01', now(), now());
insert into tb_emp values (null, 'zhiruo2', '123', '周芷若', 2, '1.jpg', 1, '2010-01-01', now(), now());
-- 3. 批量为 tb_emp 表的 username， name， gender 字段插入数据
insert into tb_emp(username, name, gender, create_time, update_time ) values
('weifuwang', '韦一笑', 1, now(), now()), ('xieshiwang', '谢逊', 1, now(), now());
DML - 更新数据 - update
-- DML：更新数据 - update
-- 1. 将 tb_emp 表的ID为1员工 姓名name字段更新为’张三‘
update tb_emp set name = '张三', update_time = now() where id = 1;
-- 2. 将 tb_emp 表的所有员工的入职日期更新为 ’2010-01-01‘
update tb_emp set entrydate = '2010-01-01', update_time = now();
DML - 删除数据 - delete
-- DML：删除数据 - delete
-- 1. 删除 tb_emp 表中 ID为1的员工
delete from tb_emp where id = 1;
小结：
DQL – 数据查询语言
建表
-- 员工管理(带约束)
create table tb_emp
(
id int unsigned primary key auto_increment comment 'ID',
username varchar(20) not null unique comment '用户名',
password varchar(32) default '123456' comment '密码',
name varchar(10) not null comment '姓名',
gender tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
image varchar(300) comment '图像',
job tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管',
entrydate date comment '入职时间',
create_time datetime not null comment '创建时间',
update_time datetime not null comment '修改时间'
) comment '员工表';
-- 准备测试数据
INSERT INTO tb_emp (id, username, password, name, gender, image, job, entrydate, create_time, update_time)
VALUES (1, 'jinyong', '123456', '金庸', 1, '1.jpg', 4, '2000-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:35'),
(2, 'zhangwuji', '123456', '张无忌', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:37'),
(3, 'yangxiao', '123456', '杨逍', 1, '3.jpg', 2, '2008-05-01', '2022-10-27 16:35:33', '2022-10-27 16:35:39'),
(4, 'weiyixiao', '123456', '韦一笑', 1, '4.jpg', 2, '2007-01-01', '2022-10-27 16:35:33', '2022-10-27 16:35:41'),
(5, 'changyuchun', '123456', '常遇春', 1, '5.jpg', 2, '2012-12-05', '2022-10-27 16:35:33',
'2022-10-27 16:35:43'),
(6, 'xiaozhao', '123456', '小昭', 2, '6.jpg', 3, '2013-09-05', '2022-10-27 16:35:33', '2022-10-27 16:35:45'),
(7, 'jixiaofu', '123456', '纪晓芙', 2, '7.jpg', 1, '2005-08-01', '2022-10-27 16:35:33', '2022-10-27 16:35:47'),
(8, 'zhouzhiruo', '123456', '周芷若', 2, '8.jpg', 1, '2014-11-09', '2022-10-27 16:35:33', '2022-10-27 16:35:49'),
(9, 'dingminjun', '123456', '丁敏君', 2, '9.jpg', 1, '2011-03-11', '2022-10-27 16:35:33', '2022-10-27 16:35:51'),
(10, 'zhaomin', '123456', '赵敏', 2, '10.jpg', 1, '2013-09-05', '2022-10-27 16:35:33', '2022-10-27 16:35:53'),
(11, 'luzhangke', '123456', '鹿杖客', 1, '11.jpg', 2, '2007-02-01', '2022-10-27 16:35:33',
'2022-10-27 16:35:55'),
(12, 'hebiweng', '123456', '鹤笔翁', 1, '12.jpg', 2, '2008-08-18', '2022-10-27 16:35:33', '2022-10-27 16:35:57'),
(13, 'fangdongbai', '123456', '方东白', 1, '13.jpg', 1, '2012-11-01', '2022-10-27 16:35:33',
'2022-10-27 16:35:59'),
(14, 'zhangsanfeng', '123456', '张三丰', 1, '14.jpg', 2, '2002-08-01', '2022-10-27 16:35:33',
'2022-10-27 16:36:01'),
(15, 'yulianzhou', '123456', '俞莲舟', 1, '15.jpg', 2, '2011-05-01', '2022-10-27 16:35:33',
'2022-10-27 16:36:03'),
(16, 'songyuanqiao', '123456', '宋远桥', 1, '16.jpg', 2, '2010-01-01', '2022-10-27 16:35:33',
'2022-10-27 16:36:05'),
(17, 'chenyouliang', '12345678', '陈友谅', 1, '17.jpg', null, '2015-03-21', '2022-10-27 16:35:33',
'2022-10-27 16:36:07'),
(18, 'zhang1', '123456', '张一', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:09'),
(19, 'zhang2', '123456', '张二', 1, '2.jpg', 2, '2012-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:11'),
(20, 'zhang3', '123456', '张三', 1, '2.jpg', 2, '2018-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:13'),
(21, 'zhang4', '123456', '张四', 1, '2.jpg', 2, '2015-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:15'),
(22, 'zhang5', '123456', '张五', 1, '2.jpg', 2, '2016-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:17'),
(23, 'zhang6', '123456', '张六', 1, '2.jpg', 2, '2012-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:19'),
(24, 'zhang7', '123456', '张七', 1, '2.jpg', 2, '2006-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:21'),
(25, 'zhang8', '123456', '张八', 1, '2.jpg', 2, '2002-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:23'),
(26, 'zhang9', '123456', '张九', 1, '2.jpg', 2, '2011-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:25'),
(27, 'zhang10', '123456', '张十', 1, '2.jpg', 2, '2004-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:27'),
(28, 'zhang11', '123456', '张十一', 1, '2.jpg', 2, '2007-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:29'),
(29, 'zhang12', '123456', '张十二', 1, '2.jpg', 2, '2020-01-01', '2022-10-27 16:35:33', '2022-10-27 16:36:31');
DQL – 基本查询
-- ================== DQL：基本查询 ===============================
-- 1. 查询指定字段 name，entrydate 并返回
select name, entrydate from tb_emp;
-- 2. 查询返回所有字段
-- 推荐
select id,username,password,name,gender,image,job,entrydate,create_time,update_time from tb_emp;
-- 不推荐，不直观，性能低
select * from tb_emp;
-- 3. 查询所有员工的 name，entrydate， 并起别名(姓名， 入职日期)
select name 姓名, entrydate 入职日期 from tb_emp;
-- 4. 查询已有员工关联了哪几种职位(不要重复)
select distinct job from tb_emp;
DQL – 条件查询
-- ==================DQL: 条件查询 ===========================
-- 1. 查询 姓名 为 杨逍 的员工
select * from tb_emp where name = '杨逍';
-- 2. 查询 id 小于等于5 的员工信息
select * from tb_emp where id <= 5;
-- 3. 查询 没有分配职位 的员工信息
select * from tb_emp where job is null;
-- 4. 查询 有职位 的员工信息
select * from tb_emp where job is not null;
-- 5. 查询 密码不等于 ‘123456‘ 的员工信息
select * from tb_emp where password != '123456';
-- 6. 查询入职日期 在 ’2000-01-01‘ (包含) 到 ’2010-01-01‘（包含） 之间的员工信息
select * from tb_emp where entrydate >= '2000-01-01' and entrydate <= '2010-01-01';
select * from tb_emp where entrydate between '2000-01-01' and '2010-01-01';
-- 7. 查询入职时间在 ’2000-01-01‘（包含）到’2010-01-01‘（包含） 且 性别为女 的员工信息
select * from tb_emp where entrydate between '2000-01-01' and '2010-01-01' and gender = 2;
-- 8. 查询 职位是 2 （讲师）， 3，（学工主管）， 4，（教研主管） 的员工信息
select * from tb_emp where job = 2 or job = 3 or job = 4;
select * from tb_emp where job in (2, 3, 4);
-- 9. 查询 姓名 为两个字的员工信息
select * from tb_emp where name like '__';
-- 10. 查询 姓 ’张’ 的员工信息
select * from tb_emp where name like '张%';
DQL – 分组查询
-- ==================DQL: 分组查询 ===========================
-- 聚合函数: 不对null值进行计算的。
-- 1. 统计该企业员工数量 --conut
-- A. count(字段)
select count(id) from tb_emp;
select count(job) from tb_emp;
-- B. count(常量)
select count(‘A’) from tb_emp;
-- C. count(*) -- 推荐
select count(*) from tb_emp;
-- 2. 统计该企业最早入职的员工 -- min
select min(entrydate) from tb_emp;
-- 3. 统计该企业最迟入职的员工
select max(entrydate) from tb_emp; -- max
-- 4. 统计该企业员工 ID 的平均值 -- avg
select avg(id) from tb_emp;
-- 5. 统计该其一员工的 ID 之和 - sum
select sum(id) from tb_emp;
DQL – 分组查询
-- 分组
-- 1. 根据性别分组，统计男性和女性员工的数量 - count(*)
select gender,count(*) from tb_emp group by gender;
-- 2. 先查询入职时间在 ‘2015-01-01’（包含） 以前的员工，并对结果根据职位分组，获取员工数量大于等于 2 的职位
select job,count(*) from tb_emp where entrydate <= '2015-01-01' group by job having count(*) >= 2;
DDL – 排序查询
-- =================== 排序查询 ===========================
-- 1. 根据入职时间，对员工进行升序排序(默认) - asc
select * from tb_emp order by entrydate asc;
select * from tb_emp order by entrydate;
-- 2. 根据入职时间，对员工进行降序排序 - desc
select * from tb_emp order by entrydate desc;
-- 3. 根据 入职时间 对公司的员工进行 升序排序， 入职时间相同 ， 再按照 更新时间 进行降序排序
select * from tb_emp order by entrydate, update_time desc;
DQL – 分页查询
-- ==================== 分页查询 ========================
-- 1. 从起始索引0 开始查询员工数据，每页展示5条记录
select * from tb_emp limit 0,5;
-- 2. 查询 第 1 页 员工数据， 每页展示5条记录
select * from tb_emp limit 0,5;
-- 3. 查询 第 2 页 员工数据， 每页展示5条记录
select * from tb_emp limit 5,5;
-- 4. 查询 第 3 页 员工数据， 每页展示5条记录
select * from tb_emp limit 10,5;
-- 起始索引 = （页码 - 1） * 每页展示的记录数
小结
一对多案例
数据准备：
-- 员工
create table tb_emp
(
id int unsigned primary key auto_increment comment 'ID',
username varchar(20) not null unique comment '用户名',
password varchar(32) default '123456' comment '密码',
name varchar(10) not null comment '姓名',
gender tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
image varchar(300) comment '图像',
job tinyint unsigned comment '职位, 说明：1 班主任, 2 讲师, 3 学生主管, 4 教研主管',
entrydate date comment '入职时间',
dept_id int unsigned comment '归属的部门ID',
create_time datetime not null comment '创建时间',
update_time datetime not null comment '修改时间'
) comment '员工表';
-- 部门
create table tb_dept
(
id int unsigned primary key auto_increment comment 'ID',
name varchar(10) not null unique comment '部门名称',
create_time datetime not null comment '创建时间',
update_time datetime not null comment '修改时间'
) comment '部门表';
-- 插入测试数据
insert into tb_dept (id, name, create_time, update_time)
values (1, '学工部', now(), now()),
(2, '教研部', now(), now()),
(3, '咨询部', now(), now()),
(4, '就业部', now(), now()),
(5, '人事部', now(), now());
INSERT INTO tb_emp
(id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time)
VALUES (1, 'jinyong', '123456', '金庸', 1, '1.jpg', 4, '2000-01-01', 2, now(), now()),
(2, 'zhangwuji', '123456', '张无忌', 1, '2.jpg', 2, '2015-01-01', 2, now(), now()),
(3, 'yangxiao', '123456', '杨逍', 1, '3.jpg', 2, '2008-05-01', 2, now(), now()),
(4, 'weiyixiao', '123456', '韦一笑', 1, '4.jpg', 2, '2007-01-01', 2, now(), now()),
(5, 'changyuchun', '123456', '常遇春', 1, '5.jpg', 2, '2012-12-05', 2, now(), now()),
(6, 'xiaozhao', '123456', '小昭', 2, '6.jpg', 3, '2013-09-05', 1, now(), now()),
(7, 'jixiaofu', '123456', '纪晓芙', 2, '7.jpg', 1, '2005-08-01', 1, now(), now()),
(8, 'zhouzhiruo', '123456', '周芷若', 2, '8.jpg', 1, '2014-11-09', 1, now(), now()),
(9, 'dingminjun', '123456', '丁敏君', 2, '9.jpg', 1, '2011-03-11', 1, now(), now()),
(10, 'zhaomin', '123456', '赵敏', 2, '10.jpg', 1, '2013-09-05', 1, now(), now()),
(11, 'luzhangke', '123456', '鹿杖客', 1, '11.jpg', 1, '2007-02-01', 1, now(), now()),
(12, 'hebiweng', '123456', '鹤笔翁', 1, '12.jpg', 1, '2008-08-18', 1, now(), now()),
(13, 'fangdongbai', '123456', '方东白', 1, '13.jpg', 2, '2012-11-01', 2, now(), now()),
(14, 'zhangsanfeng', '123456', '张三丰', 1, '14.jpg', 2, '2002-08-01', 2, now(), now()),
(15, 'yulianzhou', '123456', '俞莲舟', 1, '15.jpg', 2, '2011-05-01', 2, now(), now()),
(16, 'songyuanqiao', '123456', '宋远桥', 1, '16.jpg', 2, '2010-01-01', 2, now(), now()),
(17, 'chenyouliang', '123456', '陈友谅', 1, '17.jpg', NULL, '2015-03-21', NULL, now(), now());
数据库思路分析：
我们可以看到在新增员工的页面中，有一个选择部门的选项，
也就是说我们需要把两张表连起来，这样我们就需要用到中间列和外键约束。
我们需要一个中间列来关联每个员工的id，当我们删除部门表中的选项时，
同样当前用户也要从user中删除。那么我们就需要创建一个dep_id来关联id
那么问题来了？
怎么关联呢？ 这里我们需要引入一个外键约束的概念。
外键约束
图形用户界面创建外键约束
物理外键和逻辑外键的优缺点分析
一对一案例
-- ===========================================一对一=====================================
create table tb_user
(
id int unsigned primary key auto_increment comment 'ID',
name varchar(10) not null comment '姓名',
gender tinyint unsigned not null comment '性别, 1 男 2 女',
phone char(11) comment '手机号',
degree varchar(10) comment '学历'
) comment '用户信息表';
insert into tb_user
values (1, '白眉鹰王', 1, '18812340001', '初中'),
(2, '青翼蝠王', 1, '18812340002', '大专'),
(3, '金毛狮王', 1, '18812340003', '初中'),
(4, '紫衫龙王', 2, '18812340004', '硕士');
create table tb_user_card
(
id int unsigned primary key auto_increment comment 'ID',
nationality varchar(10) not null comment '民族',
birthday date not null comment '生日',
idcard char(18) not null comment '身份证号',
issued varchar(20) not null comment '签发机关',
expire_begin date not null comment '有效期限-开始',
expire_end date comment '有效期限-结束',
user_id int unsigned not null unique comment '用户ID',
constraint fk_user_id foreign key (user_id) references tb_user (id)
) comment '用户信息表';
insert into tb_user_card
values (1, '汉', '1960-11-06', '100000100000100001', '朝阳区公安局', '2000-06-10', null, 1),
(2, '汉', '1971-11-06', '100000100000100002', '静安区公安局', '2005-06-10', '2025-06-10', 2),
(3, '汉', '1963-11-06', '100000100000100003', '昌平区公安局', '2006-06-10', null, 3),
(4, '回', '1980-11-06', '100000100000100004', '海淀区公安局', '2008-06-10', '2028-06-10', 4);
代码分析
user_id int unsigned not null unique comment '用户ID',
constraint fk_user_id foreign key (user_id) references tb_user (id)
第一行我们创建一个user_id来关联用户id
第二行我们要将user_id 和 id 添加外键约束
多对多的案例
思路分析
因为这个案例不是简单的一对一，而是多对多。
体现在一名学生可以选修多门课程，一门课程也可以被多名学生选
也就是说我们通过查询学生的id就可以知道当前学生选了哪些课程
同样的，通过查询每门课程的id就可以知道当前课程被哪些学生所选
因此我们需要创建一张中间表tb_student_card，tb_student_card中要包含两列数据：
student_id 和 course_id 并分别对各自的id添加相应的外键约束。
这样三张表就互相确立了关系。
具体关系图如下：
代码如下：
-- ======================================多对多=============================
create table tb_student
(
id int auto_increment primary key comment '主键ID',
name varchar(10) comment '姓名',
no varchar(10) comment '学号'
) comment '学生表';
insert into tb_student(name, no)
values ('黛绮丝', '2000100101'),
('谢逊', '2000100102'),
('殷天正', '2000100103'),
('韦一笑', '2000100104');
create table tb_course
(
id int auto_increment primary key comment '主键ID',
name varchar(10) comment '课程名称'
) comment '课程表';
insert into tb_course (name)
values ('Java'),
('PHP'),
('MySQL'),
('Hadoop');
create table tb_student_course
(
id int auto_increment comment '主键' primary key,
student_id int not null comment '学生ID',
course_id int not null comment '课程ID',
constraint fk_courseid foreign key (course_id) references tb_course (id),
constraint fk_studentid foreign key (student_id) references tb_student (id)
) comment '学生课程中间表';
insert into tb_student_course(student_id, course_id)
values (1, 1),
(1, 2),
(1, 3),
(2, 2),
(2, 3),
(3, 4);
代码分析
create table tb_student_course
(
id int auto_increment comment '主键' primary key,
student_id int not null comment '学生ID',
course_id int not null comment '课程ID',
constraint fk_courseid foreign key (course_id) references tb_course (id),
constraint fk_studentid foreign key (student_id) references tb_student (id)
) comment '学生课程中间表';
具体分析看上述的思路hh
苍穹外卖多表设计
1. 建立关系图
2. 分析数据关系
分类与菜品是 1 : n 的关系,
因此分类表中的 id 关联菜品表中的 category_id.
分类与套餐也是 1 : n 的关系,
因此分类表中的 id 同时要关联套餐表中的category_id.
套餐与菜品是多对多的关系,
因此我们要创建一张中间表来记录
每一个套餐关联了哪些菜品以及关联的菜品的份数有几份。
具体步骤如下图:
建表如下图：
小结
多表查询
*数据准备：*
-- 部门管理
create table tb_dept
(
id int unsigned primary key auto_increment comment '主键ID',
name varchar(10) not null unique comment '部门名称',
create_time datetime not null comment '创建时间',
update_time datetime not null comment '修改时间'
) comment '部门表';
insert into tb_dept (id, name, create_time, update_time)
values (1, '学工部', now(), now()),
(2, '教研部', now(), now()),
(3, '咨询部', now(), now()),
(4, '就业部', now(), now()),
(5, '人事部', now(), now());
-- 员工管理
create table tb_emp
(
id int unsigned primary key auto_increment comment 'ID',
username varchar(20) not null unique comment '用户名',
password varchar(32) default '123456' comment '密码',
name varchar(10) not null comment '姓名',
gender tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
image varchar(300) comment '图像',
job tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管, 5 咨询师',
entrydate date comment '入职时间',
dept_id int unsigned comment '部门ID',
create_time datetime not null comment '创建时间',
update_time datetime not null comment '修改时间'
) comment '员工表';
INSERT INTO tb_emp
(id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time)
VALUES (1, 'jinyong', '123456', '金庸', 1, '1.jpg', 4, '2000-01-01', 2, now(), now()),
(2, 'zhangwuji', '123456', '张无忌', 1, '2.jpg', 2, '2015-01-01', 2, now(), now()),
(3, 'yangxiao', '123456', '杨逍', 1, '3.jpg', 2, '2008-05-01', 2, now(), now()),
(4, 'weiyixiao', '123456', '韦一笑', 1, '4.jpg', 2, '2007-01-01', 2, now(), now()),
(5, 'changyuchun', '123456', '常遇春', 1, '5.jpg', 2, '2012-12-05', 2, now(), now()),
(6, 'xiaozhao', '123456', '小昭', 2, '6.jpg', 3, '2013-09-05', 1, now(), now()),
(7, 'jixiaofu', '123456', '纪晓芙', 2, '7.jpg', 1, '2005-08-01', 1, now(), now()),
(8, 'zhouzhiruo', '123456', '周芷若', 2, '8.jpg', 1, '2014-11-09', 1, now(), now()),
(9, 'dingminjun', '123456', '丁敏君', 2, '9.jpg', 1, '2011-03-11', 1, now(), now()),
(10, 'zhaomin', '123456', '赵敏', 2, '10.jpg', 1, '2013-09-05', 1, now(), now()),
(11, 'luzhangke', '123456', '鹿杖客', 1, '11.jpg', 5, '2007-02-01', 3, now(), now()),
(12, 'hebiweng', '123456', '鹤笔翁', 1, '12.jpg', 5, '2008-08-18', 3, now(), now()),
(13, 'fangdongbai', '123456', '方东白', 1, '13.jpg', 5, '2012-11-01', 3, now(), now()),
(14, 'zhangsanfeng', '123456', '张三丰', 1, '14.jpg', 2, '2002-08-01', 2, now(), now()),
(15, 'yulianzhou', '123456', '俞莲舟', 1, '15.jpg', 2, '2011-05-01', 2, now(), now()),
(16, 'songyuanqiao', '123456', '宋远桥', 1, '16.jpg', 2, '2007-01-01', 2, now(), now()),
(17, 'chenyouliang', '123456', '陈友谅', 1, '17.jpg', NULL, '2015-03-21', NULL, now(), now());
案例1情景：
查询员工的姓名 ， 及所属的部门名称
假如我这样写:
select * from tb_emp,tb_dept;
那么返回的数据是：
到底为什么会这样呢？
因为我们没有连接两张表。
代码修改后：
select * from tb_emp,tb_dept where tb_emp.dept_id = tb_dept.id;
那么我们要怎么连接两张表进行查询呢？
连接查询
内连接
内连接取交集，外连接取并集
-- ======================== 内连接 =========================
-- A. 查询员工的姓名 ， 及所属的部门名称 （隐式内连接实现）
select tb_emp.name,tb_dept.name from tb_emp,tb_dept where tb_emp.dept_id = tb_dept.id;
-- 起别名
select e.name , d.name from tb_emp e , tb_dept d where e.dept_id = d.id;
-- B. 查询员工的姓名 ， 及所属的部门名称 （显式内连接实现）
select tb_emp.name,tb_dept.name from tb_emp join tb_dept on tb_emp.dept_id = tb_dept.id;
外连接
左外连接
左外连接会完全包含左边的数据,也就是员工表的数据
-- A. 查询员工表 所有 员工的姓名，和对应的部门名称 （左外连接）
select e.name,d.name from tb_emp e left join tb_dept d on e.dept_id = d.id;
右外连接
右外连接会完全包含右边的数据, 也就是部门表中的数据
-- B. 查询部门表 所有 部门的名称， 和对应的员工名称 （右外连接）
select e.name,d.name from tb_emp e right join tb_dept d on e.dept_id = d.id;
-- 修改成左外连接
select e.name,d.name from tb_dept d left join tb_emp e on e.dept_id = d.id;
注意:项目开发过程中基本都是使用左外连接
因为右外连接可以修改成左外连接.
子查询
标量子查询
-- 标量子查询
-- A. 查询 “教研部” 的所有员工信息
-- a. 查询 教研部 的部门ID
select id from tb_dept where name = '教研部';
-- b. 再查询该部门ID下的员工信息
select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部');
-- B. 查询在 “方东白” 入职之后的员工信息
select * from tb_emp where entrydate > (select entrydate from tb_emp where name = '方东白');
列子查询
-- 列子查询
-- A. 查询 “教研部” 和 “咨询部” 的所有员工信息
-- a. 查询 “教研部” 和 “咨询部” 的部门ID - tb_dept
select id from tb_dept where name = '教研部' or name = '咨询部';
-- b. 根据部门ID， 查询该部门下的员工信息 - tb_emp
select * from tb_emp where dept_id in (3, 2);
select * from tb_emp where dept_id in (select id from tb_dept where name = '教研部' or name = '咨询部');
像这种根据id列进行的子查询叫做列子查询
行子查询 和 表子查询
-- 行子查询
-- A. 查询与 “韦一笑” 的入职日期 及 职位都相同的员工信息 ；
-- a. 查询韦一笑的入职日期 及 职位
select entrydate,job from tb_emp where name = '韦一笑';
-- b. 查询与其入职日期 及 职位都相同的员工信息 ；
select * from tb_emp where entrydate = '2007-01-01' and job = 2;
select * from tb_emp where (entrydate, job) = ('2007-01-01', 2);
select * from tb_emp where (entrydate, job) = (select entrydate,job from tb_emp where name = '韦一笑');
-- 表子查询
-- A. 查询入职日期是 “2006-01-01” 之后的员工信息 ， 及部门名称
-- a. 查询入职日期是 “2006-01-01” 之后的员工信息
select * from tb_emp where entrydate > '2006-01-01';
-- b. 查询这部分员工信息以及部门名称 - tb_dept
select e.*,d.name from (select * from tb_emp where entrydate > '2006-01-01') e , tb_dept d where e.dept_id = d.id;
表子查询代码思路
思路先找到入职日期大于2006-01-01的员工信息这些员工信息组成一张表，
然后我们要查询这张表的员工信息以及其对应的部门名称，
就要使用内连接将dept_id和部门id关联
