内容接Mysql02_selece_1点击跳转Mysql02_select_1

---

11、连接查询

表	字段1	字段2	字段3	字段4
stu	stno	name	class	card
sco	id	couno	stno	sco
cou	couno	name

1. 两表连接

   • 等值连接

     select *from stu,sco where stu.stno = sco.stno
     

   • 内连接

     select *from stu inner join sco on tu.stno=sco.stno
     

2. 三表连接

   • 等值

   select *from stu,sco where stu.stno = sco.stno 
   and sco.couno=cou.couno
   

   • 内连接

   select *from stu inner join sco on stu.stno=sco.stno
   inner join cou on
   sco.couno=cou.couno
   

3. 左连接/右连接

   • left join 左表全部显示
   • right join 右表全部显示

---

---

12 、自关联

数据库准备(areas)

adi	atitle	pid
1	asheng	NULL
2	ashi	1
3	bsheng	NULL
4	bshi	3

1. 查询'asheng'下的shi

select *from areas as Sheng,areas as Shi
where Sheng.aid = Shi.pid
and Sheng.atitle = 'asheng'

2. 查询'ashi下的qu

select *from areas as Shi,areas as qu
where Shi.aid = qu.pid
and Shi.atitle = 'ashi'

3. 查询'asheng'下的qu（三级)

select Sheng.atitle,qu.atitle
from 
areas as Sheng,areas as Shi,areas as qu
where 
Sheng.aid=Shi.pid and Shi.aid=qu.pid

13、子查询

• 标量子查询（返回一行一列）

  • 大于平均年龄的学生

    select *from stu where age > (select avg(age) from stu)
    

• 列级子查询

  • 23岁学生的成绩

    select stno from stu where age = 23 #得到age=23的学生 返回列
    select *from sco where stno in(select stno from stu where age=23) 
    

• 行级子查询

  • 查询男生中年龄最大的信息(将得到行返回——>(sex，age))

    select *from stu 
    where
    (sex,age)=(select sex,age from stu where sex='男' order by desc limit 0,1
    

子查询关键字 in

• 使用=any 和 =some替换

• 几个注意

  >any 大于最小即可 有大即可
  <any 小于最大即可
  <all 小于最小
  >all 大于最大
  

14、数据分表

• 查询出来的表写入另一张

  1. insert

     insert into g_ca(name) select distinct val from data
     

  2. 创建即insert

     drop table if exists P;
     creat table P(
     	id int primary kry auto_increment,
     	p_name varch(10)
     )
     select distinct p_name from data
     

     无对应字段名时将自动创建字段