题目1
表结构
Student(S#,Sname,Sage,Ssex) 学生表
S#:学号;Sname:学生姓名;Sage:学生年龄;Ssex:学生性别
Course(C#,Cname,T#) 课程表
C#,课程编号;Cname:课程名字;T#:教师编号
SC(S#,C#,score) 成绩表
S#:学号;C#,课程编号;score:成绩
Teacher(T#,Tname) 教师表
T#:教师编号; Tname:教师名字
问题:
1、查询“001”课程比“002”课程成绩高的所有学生的学号;
select a.S# from (select s#,score from SCwhere C#='001') a,(select s#,score from SC where C#='002') b where a.score>b.score and a.s#=b.s#;
2、查询平均成绩大于60分的同学的学号和平均成绩;
select S#,avg(score) from sc group by S#having avg(score) >60;
3、查询所有同学的学号、姓名、选课数、总成绩;
select Student.S#,Student.Sname,count(SC.C#),sum(score) from Student left Outer join SC onStudent.S#=SC.S# group by Student.S#,Sname
题目2
表内容:
2005-05-09胜
2005-05-09胜
2005-05-09负
2005-05-09负
2005-05-10胜
2005-05-10负
2005-05-10负
如果要生成下列结果, 该如何写sql语句?
rq 胜 负
2005-05-092 2
2005-05-101 2
SELECTa.rq, a.count '胜', b.count '负'from (SELECT rq, count(shengfu) count FROM tmp WHERE shengfu='胜')a,
(SELECTrq, count(shengfu) count FROM tmp WHERE shengfu='胜')b where a.rq = b.rq
题目3
从table1,table2中取出如table3所列格式数据
月份mon 部门dep 业绩yj
-------------------------------
一月份 01 10
一月份 02 10
一月份 03 5
二月份 02 8
二月份 04 9
三月份 03 8
table2
部门dep 部门名称dname
--------------------------------
01 国内业务一部
02 国内业务二部
03 国内业务三部
04 国际业务部
table3 (result)
部门dep 一月份 二月份 三月份
--------------------------------------
01 10 null null
02 10 8 null
03 null 5 8
04 null null 9
------------------------------------------
SELECTa.dept,a.yj 一月份, b.yj 二月份,c.yj 三月份
from(select dept, yj from aa WHERE mon = '1') a
LEFTJOIN (select dept, yj from aa WHERE mon = '2') b ON a.dept = b.dept
LEFTJOIN (select dept, yj from aa WHERE mon = '3') c ON b.dept = c.dept
题目4
表形式如下:
Year Salary
2000 1000
2001 2000
2002 3000
2003 4000
想得到如下形式的查询结果
Year Salary
2000 1000
2001 3000
2002 6000
2003 10000
子查询:
SELECTa.year, (SELECT SUM(salary) from aa b where b.year <= a.year) from aa a
GROUPBY a.year
连接查询:
SELECTa.year, sum(b.salary) FROM aa a, aa b WHERE b.year >= a.year
GROUPBY a.year
题目5
year monthamount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
子查询:
SELECTyear,
(SELECTaccount from aa a1 where month=1 and a1.year = a.year) account1,
(SELECTaccount from aa a2 where month=2 and a2.year = a.year) account2,
(SELECTaccount from aa a3 where month=3 and a3.year = a.year) account3,
(SELECTaccount from aa a4 where month=4 and a4.year = a.year) account4
FROMaa a GROUP BY a.year
连接查询:
SELECTa1.year year, a1.account account1, a2.account account2,a3.accountaccount3,a4.account account4
FROM(select year, account from aa WHERE month=1) a1 ,
(select year, account from aa WHERE month=2)a2 ,
(select year, account from aa WHERE month=3)a3 ,
(select year, account from aa WHERE month=4)a4
WHEREa1.year = a2.year
anda2.year = a3.year
anda3.year = a4.year
GROUPBY a1.year
题目6
用一条SQL语句查询出每门课都大于80分的学生姓名
name kecheng fenshu
张三 语文 81
张三 数学 75
李四 语文 76
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 90
selectdistinct name from table where name not in (select distinct name from tablewhere fenshu<=80)
题目7
学生表 如下:
自动编号 学号 姓名课程编号课程名称分数
1 2005001 张三 0001 数学 69
2 2005002 李四0001 数学 89
3 2005001 张三0001 数学 69
删除除了自动编号不同,其他都相同的学生冗余信息
delete tablename where 自动编号 not in(select min(自动编号) from tablenamegroup by 学号,姓名,课程编号,课程名称,分数)
题目8
有三张表,学生表S,课程C,学生课程表SC,学生可以选修多门课程,一门课程可以被多个学生选修,通过SC表关联。
1)写出建表语句;
2)写出SQL语句,查询选修了所有选修课程的学生;
3)写出SQL语句,查询选修了至少5门以上的课程的学生。
1)建表语句如下(mysql数据库):
create table s(id integerprimary key, name varchar(20)); create table c(id integer primary key, namevarchar(20)); create table sc( sid integer references s(id), cid integerreferences c(id), primary key(sid,cid) );
2)SQL语句如下:
select stu.id, stu.namefrom s stu where (select count(*) from sc where sid=stu.id) = (select count(*)from c);
3)SQL语句如下:
select stu.id, stu.namefrom s stu where (select count(*) from sc where sid=stu.id)>=5;
题目9 CASE
表中有A B C三列,用SQL语句实现:当A列大于B列时选择A列否则选择B列,当B列大于C列时选择B列否则选择C列。
SELECT(CASE WHEN aa>bb THEN aa ELSE bb END),(CASE WHEN bb>cc THEN bb ELSE ccEND) FROM abc
有一张表,里面有3个字段:语文,数学,英语。其中有1条记录分别表示语文70分,数学80分,英语58分,请用一条sql语句查询出这三条记录并按以下条件显示出来:
大于或等于80表示优秀,大于或等于60表示及格,小于60分表示不及格。
显示格式:
语文 数学 英语
及格 优秀 不及格
SELECT(CASE WHEN 语文>80 THEN '优秀'WHEN 语文 > 60 THEN '及格'ELSE '不及格' END) 语文,
(CASEWHEN 数学>80 THEN '优秀'WHEN 数学 > 60 THEN '及格'ELSE '不及格' END) 数学,
(CASEWHEN 英语>80 THEN '优秀'WHEN 英语 > 60 THEN '及格'ELSE '不及格' END) 英语
FROMaa
题目10 DATE
请取出tmp表中日期(td字段)为当天的所有记录?
select* from tmp WHERE date(dt) = date(now());
注:日期函数:
函数 | 描述 |
返回当前的日期和时间 | |
返回当前的日期 | |
返回当前的时间 | |
提取日期或日期/时间表达式的日期部分 | |
返回日期/时间按的单独部分 | |
给日期添加指定的时间间隔 | |
从日期减去指定的时间间隔 | |
返回两个日期之间的天数 | |
用不同的格式显示日期/时间 |
SQL效率问题
MySQL其他知识点
1. 一张表,里面有ID自增主键,当insert了17条记录之后,删除了第15,16,17条记录,再把Mysql重启,再insert一条记录,这条记录的ID是18还是15
InnoDB是把表的主键最大值放到内存里面,所以MYSQL重启之后就会丢失(MYSQL默认)
MyISAM是把表的主键最大值放到文件里面,所以MYSQL重启之后不会丢失
MySQL在什么情况下容易损坏?MySQL表损坏后的主要现象是什么?数据表损坏的修复方式有哪些
1.突然断电
2.未关闭mysql服务就关机
MySQL表损坏后的主要现象是什么?
Table ‘p’ is marked as crashed and should be repaired
数据表损坏的修复方式有哪些?
REPAIR TABLE p
4. mysql、oracle、sqlserver的默认端口是
3306,1521,1433