多表关联查询

	create table student (
    no INT(10) ,
    name VARCHAR(20),
    sex VARCHAR(20) ,
    birthday datetime not null,
    class int
    );
    
    create table score (
    no INT(10)  NOT NULL  UNIQUE  PRIMARY KEY  AUTO_INCREMENT ,
    cno VARCHAR(20) ,
    degree INT(10)
    );
	
Insert into score(no,cno,degree) values(5001,’3-105’,69);
Insert into score(no,cno,degree) values(5001,’5-102’,55);
Insert into score(no,cno,degree) values(503,’4-108’,85);
Insert into score(no,cno,degree) values(5004,’3-105’,77);
Insert into score(no,cno,degree) values(5005,’3-245’,100);
Insert into score(no,cno,degree) values(5006,’3-105’,53);
Insert into score(no,cno,degree) values(5003,’4-109’,45);
Insert into score(no,cno,degree) values(5008,’3-105’,98);
Insert into score(no,cno,degree) values(5004,’4-109’,68);
Insert into score(no,cno,degree) values(5010,’3-105’,88);
Insert into score(no,cno,degree) values(5003,’3-105’,98);
Insert into score(no,cno,degree) values(5005,’4-109’,68);
Insert into score(no,cno,degree) values(5002,’3-105’,88);
Insert into score(no,cno,degree) values(107,’3-105’,98);
Insert into score(no,cno,degree) values(108, ’4-109’,68);
Insert into score(no,cno,degree) values(109,’3-105’,88);
Insert into score(no,cno,degree) values(109,’4-109’,80);
Insert into score(no,cno,degree) values(107,’3-111’,88);
Insert into score(no,cno,degree) values(5003,’3-111’,80);


Insert into student(no,name,sex,birthday,class) values(5001,’李白’,’男’,’1987-7-22 00:00:00’,95001);
Insert into student(no,name,sex,birthday,class) values(5002,’夏洛特’,’女’,’1987-11-15 00:00:00’,95002);
Insert into student(no,name,sex,birthday,class) values(5003,’王昭君’,’女’,’1987-10-05 00:00:00’,95001);
Insert into student(no,name,sex,birthday,class) values(5004,’李信’,’男’,’1987-9-25 00:00:00’,95003);
Insert into student(no,name,sex,birthday,class) values(5005,’百里守约’,’男’,’1987-7-17 00:00:00’,95004);
Insert into student(no,name,sex,birthday,class) values(5006,’虞姬’,’女’,’1987-6-18 00:00:00’,95005);
Insert into student(no,name,sex,birthday,class) values(5007,’孙尚香’,’女’,’1987-8-29 00:00:00’,95006);
Insert into student(no,name,sex,birthday,class) values(5008,’马可波罗’,’男’,’1987-6-15 00:00:00’,95007);
Insert into student(no,name,sex,birthday,class) values(5009,’牛魔’,’女’,’1987-6-23 00:00:00’,95008);
Insert into student(no,name,sex,birthday,class) values(5010,’张飞’,’女’,’1987-6-24 00:00:00’,95002);
Insert into student(no,name,sex,birthday,class) values(108,’廉颇’,’男’,’1987-6-15 00:00:00’,95007);
Insert into student(no,name,sex,birthday,class) values(109,’韩信’,’男’,’1987-6-23 00:00:00’,95008);
Insert into student(no,name,sex,birthday,class) values(107,’云中君’,’男’,’1987-6-24 00:00:00’,95001);

#以class降序输出student的所有记录(student表全部属性)
select * from student ORDER BY class DESC;
#列出student表中所有记录的name、sex和class列
select name,sex,class from student;
#输出student中不姓王的同学的姓名。
SELECT * from student WHERE name not like '王%';
#输出成绩为85或86或88或在60-80之间的记录(no,cno,degree)
select * from score where degree=85 or degree=86 or degree=88 or degree BETWEEN 60 and 80;
#输出班级为95001或性别为‘女’ 的同学(student表全部属性)
select * from student where class=95001 OR sex='女';
#以cno升序、degree降序输出score的所有记录。(score表全部属性)
select * from score order by cno,degree DESC;
#输出男生人数及这些男生分布在多少个班级中 
select COUNT(*),count(DISTINCT class) FROM student where sex='男';
#列出存在有85分以上成绩的课程编号。
select distinct cno from score where degree>85;
#输出95001班级的学生人数
select COUNT(*) from student where class=95001;
#输出‘3-105’号课程的平均分
select AVG(degree) from score where cno="3-105";
#输出student中最大和最小的birthday日期值
select MIN(birthday),MAX(birthday) FROM student;
#显示95001和95004班全体学生的全部个人信息(不包括选课)。(student表全部属性)
select * from student where class=95001 OR class=95004;
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值