数据准备
# 创建数据库
create database day04_db;
# 使用数据库
use day04_db;
# 准备数据
create table category (
cid varchar(32) primary key ,
cname varchar(50)
);
create table products(
pid varchar(32) primary key ,
pname varchar(50),
price int,
flag varchar(2), #是否上架标记为：1表示上架、0表示下架
category_id varchar(32),
constraint products_fk foreign key (category_id) references category (cid)
);
# 分类
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
INSERT INTO category(cid,cname) VALUES('c004','奢侈品');
# 商品
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','联想',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','海尔',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','雷神',5000,'1','c001');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','JACK JONES',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','真维斯',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','花花公子',440,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','劲霸',2000,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','香奈儿',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','相宜本草',200,'1','c003');
连接查询
交叉连接(笛卡尔积):select 字段名 from 左表 cross join 右表; （cross可以省略）
内连接：select 字段名 from 左表 inner join 右表 on 关联条件; （inner可以省略）
左外连接：select 字段名 from 左表 left join 右表 on 关联条件;
右外连接：select 字段名 from 左表 right join 右表 on 关联条件;
示例
# 交叉连接:
select * from products cross join category;
# 需求1: 查询商品名称,商品价格,以及对应的分类名称
# 使用内连接：
# 方式一：显式
select pname,price,cname from products p inner join category c on p.category_id = c.cid;
# 方法二：隐式
select pname,price,cname from products p,category c on p.category_id = c.cid
# 需求2: 查询所有分类下的商品信息,要求展示分类名称,商品名称,商品价格
# 使用左外连接
select cname,pname,price from category c left outer join products p on c.cid = p.category_id;
# 需求3: 查询所有的商品信息,要求展示分类名称,商品名称,商品价格
# 使用右外连接
select cname,pname,price from category c right outer join products p on c.cid = p.category_id;
子查询
定义：一个select语句作为另外一个select语句的条件或者表进行查询（对于新手很友好，建议使用。）
==注意：当子查询语句作为表存在进行查询时需要设置别名==
示例
# 需求1: 查询'服饰'这个分类对应的所有商品
# 方式1:作为条件
select *
from products
where category_id = (select cid from category where cname = '服饰');
# 方式2:作为表
select *
from products p
inner join (select * from category where cname = '服饰') c # 当子查询语句作为表存在进行查询时需要设置别名
on p.category_id=c.cid;
自连接
自连接: 本质就是内外连接,唯一区别是左表和右表是同一张表
注意: 自连接为了作为多个表使用,必须起别名进行区分
示例：
# 数据准备
create table emp(
empno int primary key, -- 员工编号
ename varchar(10), -- 员工姓名
job varchar(9), -- 员工工作
mgr int, -- 员工直属领导编号
hiredate date, -- 入职时间
sal double, -- 工资
comm double, -- 奖金
deptno int -- 所在部门
);
insert into emp values(7369,'smith','职员',7566,"1980-12-17",800,null,20);
insert into emp values(7499,'allen','销售员',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'ward','销售员',7698,'1981-02-22',1250,500,30);
insert into emp values(7566,'jones','经理',7839,'1981-04-02',2975,null,20);
insert into emp values(7654,'martin','销售员',7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'blake','经理',7839,'1981-05-01',2850,null,30);
insert into emp values(7782,'clark','经理',7839,'1981-06-09',2450,null,10);
insert into emp values(7788,'scott','职员',7566,'1987-07-03',3000,2000,20);
insert into emp values(7839,'king','董事长',null,'1981-11-17',5000,null,10);
insert into emp values(7844,'turners','销售员',7698,'1981-09-08',1500,50,30);
insert into emp values(7876,'adams','职员',7566,'1987-07-13',1100,null,20);
insert into emp values(7900,'james','职员',7698,'1981-12-03',1250,null,30);
insert into emp values(7902,'ford','销售员',7566,'1981-12-03',3000,null,20);
insert into emp values(7934,'miller','职员',7782,'1981-01-23',1300,null,10);
# 需求：返回员工(职员或者销售员)和所属经理的姓名。
# 自查询方式:
select y.ename,j.ename from emp y,emp j where y.mgr = j.empno;
