MySQL中级查询

外键（foreign key）

• 定义

  让当前表字段的值在另一个表的范围内选择

• 语法

  foreign key(参考字段名 stu_id)
references 主表 财务表 (被参考字段名 id)
on delete 级联动作
on update 级联动作

• 级联动作

  cascade
  数据级联删除、更新(参考字段)
  restrict(默认)
  从表有相关联记录,不允许主表操作
  set null
  主表删除、更新,从表相关联记录字段值为NULL

• 使用规则

  1、主表、从表字段数据类型要一致
2、主表被参考字段 ：KEY的一种，一般为主键

• 示例

表1、缴费信息表(财务)

id 姓名 班级 缴费金额
1 唐伯虎 AID19 300
2 点秋香 AID19 300
3 祝枝山 AID19 300
create database db2 charset utf8;
use db2;
create table master(
id int primary key,
name varchar(20),
class char(5),
money decimal(6,2)
)charset=utf8;

insert into master values(1, '唐伯虎', 'AID19', 300),(2, '点秋香', 'AID19', 300),(3, '祝枝山', 'AID19', 300);

表2、学生信息表(班主任) – 做外键关联

stu_id 姓名 缴费金额
1 唐伯虎 300
2 点秋香 300
create table slave(
stu_id int,
name varchar(20),
money decimal(6,2),
foreign key(stu_id) references master(id)
on delete cascade on update cascade
)charset=utf8;
添加数据
insert into slave values(1, '唐伯虎', 300),(2,'点秋香',300),(3,'祝枝山',300);

• 查看外键名

show create table 表名;

• 删除外键

alter table 表名 drop foreign key 外键名;

• 已有表添加外键

alter table 表名 add foreign key(参考字段) references 主表(被参考字段) on delete 级联动作 on update 级联动作

嵌套查询(子查询)

• 定义
  把内层的查询结果作为外层的查询条件

• 语法格式

select ... from 表名 where 条件(select ....);

• 示例

1、把攻击值小于平均攻击值的英雄名字和攻击值显示出来
select name, attack from sanguo where attack <(select avg(attack) from sanguo);
2、找出每个国家攻击力最高的英雄的名字和攻击值(子查询)
select name, attack from sanguo where (country, attack) in(select country, max(attack) from sanguo group by country);

多表查询

省表

create table if not exists province(
id int primary key auto_increment,
pid int,
pname varchar(15)
)default charset=utf8;

insert into province values
(1, 130000, '河北省'),
(2, 140000, '陕西省'),
(3, 150000, '四川省'),
(4, 160000, '广东省'),
(5, 170000, '山东省'),
(6, 180000, '湖北省'),
(7, 190000, '河南省'),
(8, 200000, '海南省'),
(9, 200001, '云南省'),
(10,200002,'山西省');

市表

create table if not exists city(
id int primary key auto_increment,
cid int,
cname varchar(15),
cp_id int
)default charset=utf8;

insert into city values
(1, 131100, '石家庄市', 130000),
(2, 131101, '沧州市', 130000),
(3, 131102, '廊坊市', 130000),
(4, 131103, '西安市', 140000),
(5, 131104, '成都市', 150000),
(6, 131105, '重庆市', 150000),
(7, 131106, '广州市', 160000),
(8, 131107, '济南市', 170000),
(9, 131108, '武汉市', 180000),
(10,131109, '郑州市', 190000),
(11,131110, '北京市', 320000),
(12,131111, '天津市', 320000),
(13,131112, '上海市', 320000),
(14,131113, '哈尔滨', 320001),
(15,131114, '雄安新区', 320002);

区表

create table if not exists county(
id int primary key auto_increment,
coid int,
coname varchar(15),
copid int
)default charset=utf8;

insert into county values
(1, 132100, '正定县', 131100),
(2, 132102, '浦东新区', 131112),
(3, 132103, '武昌区', 131108),
(4, 132104, '哈哈', 131115),
(5, 132105, '安新县', 131114),
(6, 132106, '容城县', 131114),
(7, 132107, '雄县', 131114),
(8, 132108, '嘎嘎', 131115);

• 笛卡尔积
  select 字段名列表 from students, students2, setudents3

• 多表查询
  select 字段名列表 from 表名列表 where 条件;

• 示例

1、显示省和市的详细信息
河北省 石家庄市
河北省 廊坊市
湖北省 武汉市
select province.pname , city.cname from province, city where city.cp_id = province.pid;
2、显示 省 市 县 详细信息
select province.pname, city.cname , county.coname from province, city, county where city.cp_id = province.pid and county.copid = city.cid;

连接查询

• 内连接（结果同多表查询，显示匹配到的记录）

select 字段名 from 表1 inner join 表2 on 条件 inner join 表3 on 条件;
例如1 : 显示省市详细信息
select province.pname, city.cname from province inner join city on province.pid = city.cp_id;
例如2 : 显示 省 市 县 详细信息
select province.pname, city.cname, county.coname from province inner join city on province.pid = city.cp_id inner join county on city.cid = county.copid;

• 左外连接
  以 左表 为主显示查询结果

select 字段名 from 表1 left join 表2 on 条件 left join 表3 on 条件;
例如1 : 显示 省 市 详细信息（要求省全部显示）
select province.pname, city.cname from province left join city on province.pid = city.cp_id;

• 右外连接
  用法同左连接,以右表为主显示查询结果

select 字段名 from 表1 right join 表2 on 条件 right join 表3 on 条件;