软件测试：数据库笔记（2）

练习：
1、查询年龄最小的全部信息
select * from order by age limit 1

三个表连接
例：查询学生信息及学生的课程对应的成绩
select
*
from students stu, courses cs, scores sc 
where
stu.studentNo = sc.studentno and cs.courseNo = sc.courseNo
--------------------------------------
select * from students stu 
inner join scores sc on stu.studentNo = sc.studentNo       注：inner join 方法，表的顺序要注意
inner join courses cs on cs.courseNo = sc.courseNo

查询后过滤

例：查询王昭君的数据库成绩，要求显示姓名、课程号、成绩
select stu.name,cs.name as 课程名,cs.courseNo,sc.score
from students stu 
inner join scores sc on stu.studentNo = sc.studentNo       
inner join courses cs on cs.courseNo = sc.courseNo
where students.name='王昭君' and courses.name='数据库'
-----------------------------
select stu.name,cs.name,cs.courseNo,sc.score
from students stu,courses cs,scores sc
where 
stu.studentNo = sc.studentNo and cs.courseNo = sc.courseNo and stu.name='王昭君' and cs.name='数据库'
例：查询全班数据库成绩
select stu.name,cs.name,cs.courseNo,sc.score
from students stu,courses cs,scores sc
where 
stu.studentNo = sc.studentNo and cs.courseNo = sc.courseNo and cs.name='数据库'
例：查询男生最高成绩
select stu.name,cs.name as 课程名,cs.courseNo,sc.score
from students stu 
inner join scores sc on stu.studentNo = sc.studentNo       
inner join courses cs on cs.courseNo = sc.courseNo
where sex='男' order by score limit 1

左连接

join前面生成的结果为左表，join后面是右表，把左表的数据全部显示
例：查询所有学生的成绩，包括没有成绩的学生，显示课程名
select cs.name as 课程名,cs.courseNo,sc.score
from students stu 
left join scores sc on stu.studentNo = sc.studentNo       
left join courses cs on cs.courseNo = sc.courseNo

右连接

join前面生成的结果为左表，join后面是右表，把右表的数据全部显示
例：查询所有课程的成绩，包括没有成绩的课程
select
*
from scores
right join courses on scores.courseNo = courses.courseNo

例：查询所有课程的成绩，包括没有成绩的课程，包括学生信息
select
*
from scores
right join courses on scores.courseNo = courses.courseNo
left join students on students.studentNo =scores.studentNo


自关联

准备数据
create table areas(
aid int primary key, 
atitle varchar(20),
pid int 
);
insert into areas values ('130000', '河北省', NULL),
('130100', '石家庄市', '130000'), 
('130400', '邯郸市', '130000'), 
('130600', '保定市', '130000'), 
('130700', '张家口市', '130000'), 
('130800', '承德市', '130000'), 
('410000', '河南省', NULL), 
('410100', '郑州市', '410000'),
('410300', '洛阳市', '410000'), 
('410500', '安阳市', '410000'),
('410700', '新乡市', '410000'), 
('410800', '焦作市', '410000');
注：一个表中查询多次，必须要起别名
例：查询河南省的所有城市
select * from areas as sheng,areas as shi              
where sheng.aid=shi.pid and sheng.atitle='河南'
例：查询一共有多少个省
select count(*) from areas where pid is null;

添加区县数据
insert into areas values ('410101', '中原区', '410100'),
('410102', '二七区', '410100'),
('410103', '金水区', '410100');
例：查询郑州市的所有区县
select * from areas as sheng,areas as shi              
where sheng.aid=shi.pid and sheng.atitle='郑州市'
例：查询河南省的所有区县
select * from areas as sheng,areas as shi,areas as qu              
where sheng.aid=shi.pid and sheng.atitle='河南省' and shi.aid=qu.pid

标量子查询

子查询返回的结果是一个值，一行一列
例：查询大于平均年龄的学生 
select * from students where age > (select avg(age) from students);
例：查询最小年龄的人
select * from students where age=(select min(age) from students);
例：查询王昭君的成绩，要求显示成绩
select score from scores where studentno=(SELECT studentNo from students where name='王昭君')
例：查询王昭君的数据库成绩
select * from scores where studentno=(SELECT studentNo from students where name='王昭君') 
and courseNo=(select courseno from courses where name ='数据库')

列子查询

子查询返回的结果是一列多行，要用in
例:查询18岁的学生的成绩，要求显示成绩
select * from students where age=18
select * from scores where studentNo in (select studentno from students where age=18)  注：括号里面的要返回学号不要写*

行子查询（基本用不到，看看即可）

子查询返回的结果是一行多列
例：查询男生中年龄最大的学生信息
select * from  students where (sex,age)=(select sex,age from students where sex='男'order by age limit 1 ) 

表子查询

例：查询数据库和系统测试的课程成绩
 select * from scores
 inner join 
 把表查询出来的结果当数据源使用
 (select * from courses where name in ('数据库','系统测试')) as c on scores.courseno=c.courseno
 注：这里的数据源需要起别名
 
 {
 其他方法：
 select * from scores
 inner join courses on scores.courseNo=courses.courseNo
 where courses.name='数据库' or courses.name='系统测试
 ------------------------------------
 select * from scores
 inner join courses on scores.courseNo=courses.courseNo
 where courses.name in('数据库','系统测试')
 }
 
 子查询中特定关键字使用
 
 01 in 范围
 select * from students where age in (select age from students where age between 18 and 20)
 02 any|some >any等于>some
 select * from students where age=any(select age from students where age between 18 and 20)
 select * from students where age=some(select age from students where age between 18 and 20)
 03 all
 select * from students where age>all(select age from students where age between 18 and 20)
 
 查询演练
 
 准备数据
 create table goods(    id int unsigned primary key auto_increment,    
 name varchar(150),    
 cate varchar(40),   
 brand_name varchar(40),  
 price decimal(10,3) default 0,  
 is_show bit default 1,   
 is_saleoff bit default 0 
 ); 
 insert into goods values(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',default,default ); 
 insert into goods values(0,'y400n 14.0英寸笔记本电脑','笔记本','联想','4999',default,defa ult); 
 insert into goods values(0,'g150th 15.6英寸游戏本','游戏本','雷神','8499',default,default ); 
 insert into goods values(0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799',default,default );
 insert into goods values(0,'x240 超极本','超级本','联想','4999',default,default); 
 insert into goods values(0,'u330p 13.3英寸超极本','超级本','联想','4299',default,default) ; 
 insert into goods values(0,'svp13226scb 触控超极本','超级本','索尼','7999',default,defaul t); 
 insert into goods values(0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','1998',default,d efault); 
 insert into goods values(0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','3388',default,de fault);
 insert into goods values(0,'ipad mini 配备 retina 显示屏','平板电脑','苹果','2788',defaul t,default);
 insert into goods values(0,'ideacentre c340 20英寸一体电脑 ','台式机','联想','3499',defau lt,default);
 insert into goods values(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',default,d efault); 
 insert into goods values(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',defau lt,default); 
 insert into goods values(0,'at7-7414lp 台式电脑 linux ）','台式机','宏碁','3699',default, default);
 insert into goods values(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288',default, default); 
 insert into goods values(0,'poweredge ii服务器','服务器/工作站','戴尔','5388',default,def ault);
 insert into goods values(0,'mac pro专业级台式电脑','服务器/工作站','苹果','28888',default ,default); 
 insert into goods values(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',default,defa ult);
 insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default);
 insert into goods values(0,'x3250 m4机架式服务器','服务器/工作站','ibm','6888',default,de fault); 
 insert into goods values(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',default,defa ult); 
 insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default)
 
 数据分表
 
 创建类型表
 creat table goods_cates(
 id int unsigned primary key auto_increment;
 cate_name varchar(10)
 )
 select distinct cate from goods
 把一个表查询出来的数据插入到另一个表中
 insert into goods_cates(cate_name)(select distinct cate from goods)