php笛卡尔积和子查询,三、单表、多表、子查询

一、单表查询的关键字

(一)where条件比较运算符：> < >= <= <> !=

between 80 and 100 ：在80和100之间，闭区间，包含80和100

in(23,45,23)：值是23或45或23

like'wick%'：以wick开头，%代表任意多字符，_标识一个字符

逻辑运算符：and or not

(二)分组group by分组是指所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组

分组必须和聚合函数联用，优先级低于where

(1)聚合函数

聚合函数聚合的是组的内容，如果没有分组，则默认一组count：计数

count内的字段的类型可以为任意类型

sum：求和

sum内的字段的类型必须为数字类型

max：最大值

min：最小值

avg：平均值select 聚合函数，分组的字段 from 表名 group by 分组的字段

# 聚合函数和分组字段的顺序随便

# 例子

# 统计男生和女生的人数(以性别分组)

select count(id),gender from employee group by gender;

# 统计每个部门中最大的年龄(以部门分组)

select depart_id,max(age) from employee group by depart_id;

(三)二次筛选having

用来对group by分组之后的数据再进再次筛选，相当于where，优先级低于group byselect depart_id,avg(age) from employee group by depart_id having avg(age) >35;

(四)升降序order by

根据字段名排序，可以对多个字段进行排序asc ：升序，不写的话默认升序

desc：降序# age desc, id asc;

# 表示： 先对age进行降序， 如果age有相同的行， 则对id进行升序

# 例子

select * from employee order by age desc, id desc

(五)limit

优先级低于order bylimit 行索引 数据显示的函数

#例子

# 标识从行索引为0开始，总共显示10条数据

select * from employee limit 0 10;

(六)关键字的执行优先级

从上到下优先级递减：from

where

group by

having

select

distinct

order by

limit

二、多表操作

(一)多表创建

(1)约束条件：foreign key和unique

创建表时，一张表关联另一张表，约束其id在其范围内作用无需重复存储，减少占用的空间

需要修改时，只需要修改对应表中的数据

(2)一对多

一个表中的id可以被另一个表多次调用，比如员工信息表和部门表，可以有多个员工是同一个部门# 语法

constraint 外键名 foreign key (被约束的字段) references 约束的表 (约束的字段)

# 例子

# 一张部门表，一张员工信息表，用id表示部门，约束该id必须在部门表的范围内

create table department(

id int auto_increment primary key,

name varchar(32) not null default ''

)charset utf8;

insert into department (name) values ('研发部');

insert into department (name) values ('运维部');

insert into department (name) values ('前台部');

insert into department (name) values ('小卖部');

# 约束user_info中的字段depart_id只能是department中id中的一个

create table user_info(

id int auto_increment primary key,

name varchar(32) not null default '',

depart_id int not null default 1,

constraint fk_user_depart foreign key (depart_id) references department (id)

)charset utf8;

insert into userinfo (name, depart_id) values ('zekai', 1);

insert into userinfo (name, depart_id) values ('xxx', 2);

insert into userinfo (name, depart_id) values ('zekai1', 3);

insert into userinfo (name, depart_id) values ('zekai2', 4);

insert into userinfo (name, depart_id) values ('zekai3', 1);

insert into userinfo (name, depart_id) values ('zekai4', 2);

#报错

insert into userinfo (name, depart_id) values ('zekai4', 5);

(3)多对多

一个表可以多次调用两外两个表中的id，从而达到节省空间，和便于管理的目的# 创建表boy，并插入数据

create table boy (

id int auto_increment primary key,

bname varchar(32) not null default ''

)charset utf8;

insert into boy (bname) values ('zhangsan'),('lisi'),('zhaoliu');

# 创建表girl，并入数据

create table girl (

id int auto_increment primary key,

gname varchar(32) not null default ''

)charset utf8;

insert into girl (gname) values ('cuihua'),('gangdan'),('jianguo');

# 创建表boytogirl，外键为表boy和表gilr

create table boytogilr(

id int auto_increment primary key,

bid int not null default 1,

gid int not null default 1,

constraint fk_boyyogilr_boy foreign key (bid) references boy(id)),

constraint fk_boyyogilr_gilr foreign key (gid) references girl(id)

)charset utf8;

insert into boy2girl (bid, gid) values (1,1),(1,2),(2,3),(3,3),(2,2);

# 多表查询

select * from boy left join boy2girl on boy.id = boy2girl.bid left join girl on girl.id=boy2girl.gid;

(4)一对一一个表中的id只能被另一个表调用一次，比如员工信息表和薪水表，一个员工只能有一种薪水

约束条件unique：唯一约束，指定某列不能重复# 创建员工信息表

create table user (

id int auto_increment primary key,

name varchar(32)not null default ''

)charset utf8;

insert into user (name) values ('zhangsan'),('zekai'),('kkk');

# 创建薪水表

create table priv(

id int auto_increment primary key,

salary float not null default 0,

uid int not null default 1,

constraint fk_priv_user foreign key (uid) references user(id),

unique(uid)

)charset utf8;

insert into priv (salary, uid) values (2000, 1);

insert into priv (salary, uid) values (2800, 2);

insert into priv (salary, uid) values (3000, 3);

insert into priv (salary, uid) values (6000, 1); # 为1的uid只能使用一次，因此会报错

(二)多表联查

(1)左连接left join

优先显示左表全部记录# 语法

select 表名1.字段1,表名2.字段2 from 表名1 left join 表名2 on 表名1.字段3 = 表名2.字段1;

# 例子

select userinfo.name,departmen .name from userinfo left join department on userinfo.depart_id = department.id;

(2)右连接right join

优先显示右表全部记录# 语法同最left join一样

# 例子

select userinfo.name,departmen .name from userinfo right join department on userinfo.depart_id = department.id;

(3)内连接inner join

只连接匹配的行，等同于where# 例子

select * from department inner join userinfo on department.id=userinfo.depart_id;

(4)交叉连接

不适用任何匹配条件，生成笛卡尔积select * from employee,department;

三、子查询(额外补充)

讲一个查询语句嵌套在另一个查询语句中

效率低，一般不适用in

比较运算符

exists：内层返回真假值，若返回Ture，外层语句查询，否则，不进行查询# in

select id,name from department

where id in (select dep_id from employee group by dep_id having avg(age) > 25);

# 比较运算符

select name,age from emp where age > (select avg(age) from emp);

# exists

select * from employee where exists (select id from department where id=200);