sql 练习系列：数据查询 （mysql导入数据）

继上一篇博文，继续练习，表都是一样的。

涉及到查询操作，所以先给三个表导入数据：

为了方便，在csv中写好数据，导入mysql：

因为有中文字符，所以要设置。

SHOW VARIABLES LIKE "%CHAR%";
set character_set_database=utf8;
SET character_set_server = utf8;
SHOW VARIABLES LIKE "%CHAR%";

查看已经完成修改（一次性的）

（或者修改my.ini 重启mysql

这样一来永久修改，或者我的电脑右击 “管理”进入之后选择“服务和应用程序”下的“服务"找到“mysql”之后右击选择“重新启动”。）

可以发现，结果是

接下来就可以插入数据了：

insert into s(sno,sid,sname,ssex,sbirthday,sdept,saddress,sage)
values('20130101','000001','李勇','男','19950101','computer','BeiJing',20);

为了更快，我直接在EXcel上写好然后保存成t1.csv文件，再用记事本设成utf8编码，接着：

load data local infile "D:/t1.csv" into table  s character set 'utf8'
fields terminated by ',' optionally enclosed by '"' escaped by '"' 
lines terminated by '\n'; 

导入即可

（注意，先删去有s,c外键的sc表，再插入数据或者导入数据）

相应的导入其他数据表：

load data local infile "D:/c.csv" into table  c character set 'utf8'
fields terminated by ',' optionally enclosed by '"' escaped by '"' 
lines terminated by '\n'; 

再建立SC表（之前担心外键影响删除操作删掉了）

create table SC (sno varchar(12),cno varchar(6),grade int, primary key(sno,cno),
foreign key (sno) references S(sno),foreign key (cno) references C(cno));

报错：Error Code: 1005. Can't create table 'test.sc' (errno: 150)    0.094 sec

原来 reference必须是其他表的主键。

问题出在那个s表上，之前为了修改方便设置了很多column为primary key。

alter table s drop primary key;
alter table s add primary key(sno);
create table SC (sno varchar(12),cno varchar(6),grade int, primary key(sno,cno),
foreign key (sno) references S(sno),foreign key (cno) references C(cno));

成功！

insert into sc (sno,cno,grade) values('20130102','1002',87)；

再继续插入相关数据：

数据：

20130102,1004,90

20130103,1004,93

20130104,1002,94

20130104,1003,80

20130101,1003,86

不知道为什么，我只能单行插入成功，而且不能涉及1001课程。。。

三张数据表：

数据的查询操作

聚合函数(sum,min,max,avg,count)的使用

执行顺序是，先通过where子句查询出满足条件的记录，然后使用聚合语句进行统计计算，最后用having对分组统计后的数据进行进一步的筛选。(其实前面已经用到了）

（1）显示选课人数少于5人的课程名字

select cname from sc,c where sc.cno=c.cno group by sc.cno having count(sc.sno)<5; 

或者：

select cname from c where cno in (select cno from sc group by cno having count(sno)<5);

查询年龄小于20的学生信息；

select * from s where sage<20;

查询不是计算机系、数学系、外语系的学生姓名和性别；

select * from s where sdept not in ('math','computer','foreign language');

查询所有姓“刘”的学生信息。

select * from s where sname like '刘%';

查询所有学生信息，要求按学院升序，按出生日期降序

select * from s order by sdept asc ,sbirthday desc; /*前者优先级更高*/

查询选修了课程的学生人数

S elect count(distinct sno) from sc

统计各学院男生和女生的人数

select count(sno) ,sdept,ssex from s group by sdept,ssex; 

查询选修了3门课以上的学生学号

select sno,count(cno) from sc group by sno having count(cno)>3; 

显示各课程及格的人数

--select count(distinct sno),cno from sc group by cno having grade>=60;  使用having的话grade就需要在select 中

Select cno, count(*)  from sc where grade>60 group by cno;  

--使用where 的话grade不用在select 中

显示两门课（包括）以上不及格的学生学号

select sno from sc where grade<60 group by sno having count(cno)>=2;

查询每一门的间接先修课的信息（自身连接）

select * from c c1,c c2 where c1.cno=c2.cpno;

查询所有学生的选课情况（左外连接）

select * from s left join sc on sc.sno=s.sno;

结果：

20130102	2	刘向	男	1/2/1995 0:00	math	SiChuan	17	20130102	1002	94
20130102	2	刘向	男	1/2/1995 0:00	math	SiChuan	17	20130102	1003	89
20130102	2	刘向	男	1/2/1995 0:00	math	SiChuan	17	20130102	1004	89
20130103	3	翠竹	女	1/3/1995 0:00	foreign language	ShanDong	29	20130103	1002	75
20130103	3	翠竹	女	1/3/1995 0:00	foreign language	ShanDong	29	20130103	1003	90
20130103	3	翠竹	女	1/3/1995 0:00	foreign language	ShanDong	29	20130103	1004	89
20130104	4	小雨	女	1/4/1995 0:00	cultrue	YunNan	18	20130104	1002	75
20130104	4	小雨	女	1/4/1995 0:00	cultrue	YunNan	18	20130104	1004	88
20130101	1	李咏	男	1/1/1995 0:00	computer	BeiJing	20	NULL	NULL	NULL

查询所有课程的被选情况（右外连接）

select * from c right join sc on sc.cno =c.cno;

结果：

cno	cname	cpno	ccredit	sno	cno	grade
1002	大学英语		2	20130102	1002	94
1003	基础数学		2	20130102	1003	89
1004	音乐鉴赏		1	20130102	1004	89
1002	大学英语		2	20130103	1002	75
1003	基础数学		2	20130103	1003	90
1004	音乐鉴赏		1	20130103	1004	89
1002	大学英语		2	20130104	1002	75
1004	音乐鉴赏		1	20130104	1004	88

查询与“王丹” 同岁的“计算机学院”的学生信息

select * from s where sage=(select sage from s where sname='王丹') 

and sdept ='computer'; 

找出每个学生超过他的选修课程平均成绩的课程号

Select sno,cno from sc sc1 where sc1.grade>(select avg(grade) from sc where sc.sno=sc1.sno);

查询所有选修了“10001”课程的学生信息

select * from s ,sc where s.sno=sc.sno and cno='1001';

查询选修了两门及两门以上的课程的学生信息

select s.* from s,sc where s.sno=sc.sno group by sno having count(cno)>=2; 

查询只被一个学生选修了的课程信息

select c.* from sc,c where sc.cno=c.cno group by cno having count(sno)=1; 

查询没有选课的学生信息

select * from s where s.son not in (select sno from sc);  

查找名字为刘某某的学生信息，要求名字必须是3个字。

select *from s where sname like '刘__'; /*双下划线*/

嵌套查询与子查询

(1) 查询10002课程的平均分，以及各学生本门课成绩与平均分的差值

select avg(grade),grade-avg(grade) dif from sc where cno='1002'; 

(2)查询最低分低于80分的学生信息。

select s.* from s,sc where s.sno=sc.sno group by sno having min(grade)<80; 

或者：

Select * from s where sno in( select distinct sno from sc where grade<80); 

(3)查询至少有两门课超过85分的学生的基本信息

select * from s where sno in (select sno from sc where grade>85 group by sno having count(cno)>=2 );

(4) 显示平均分超过85分的学生的学号、姓名、学院

select sno,sname,sdept from s where sno in (select sno from sc group by sno having avg(grade)>85);

(5) 查询至少一门课成绩不及格的学生信息

select * from s where sno in (select sno from sc where grade <60 group by sno having count(cno)>0);

(6) 被全部学生都选修了的课程

select cno from sc group by cno having count(sno)=(select count(sno) from s);

/*主键 不用group by*/

(7) 被全部学生都不选修的课程

select cno from c where cno not in (select cno from sc ); 

(8)同时选修了1003和1004课程的学生

select sno from sc where cno='1004' and sno in(select sno from sc where cno='1003');

/*不能写成这样：

select sno from sc where sno in(select sno from sc where cno='1004') and 

sno in(select sno from sc where cno='1003');

那样会产生重复元组，加上distinct倒可以

*/

select distinct sno from sc where sno in(select sno from sc where cno='1004') and 

sno in(select sno from sc where cno='1003');

(9)求选修1003课程的最高分、最低分、平均分。

select max(grade),min(grade),avg(grade) from sc where cno='1003';

(10）分别找出选修人次超过2人次的课程及人次数

select count( distinct sno), cno from sc group by cno having  count( distinct sno)>2;