1、 明数据库主键、外键的作用。以及独立主键的好处及坏处
SQL的主键和外键的作用：
外键取值规则：空值或参照的主键值。
(1)插入非空值时，如果主键表中没有这个值，则不能插入。
(2)更新时，不能改为主键表中没有的值。
(3)删除主键表记录时，你可以在建外键时选定外键记录一起级联删除还是拒绝删除。
(4)更新主键记录时，同样有级联更新和拒绝执行的选择。
简而言之，SQL的主键和外键就是起约束作用。
2、 简述什么是事物？事物存在哪些属性？
数据库事务是指作为单个逻辑工作单元执行的一系列操作。属性：ACID
1、一致性：将事务中所做的操作捆绑成一个原子单元，即对于事务所进行的数据修改等操作，要么全部执行，要么全部不执行。
2、原子性：事务在完成时，必须使所有的数据都保持一致状态，而且在相关数据中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构都应该是正确的。
3、隔离性：由并发事务所做的修改必须与任何其他事务所做的修改相隔离。事务查看数据时数据所处的状态，要么是被另一并发事务修改之前的状态，要么是被另一并发事务修改之后的状态，即事务不会查看由另一个并发事务正在修改的数据。这种隔离方式也叫可串行性。
4、持久性：事务完成之后，它对系统的影响是永久的，即使出现系统故障也是如此。
3、 如何写出高性能的SQL语句？
(1)整合简单,无关联数据库访问: 如果你有几个简单数据库查询语句,你可以把它们整合到个查询
中(即使它们的间没有关系)
(2)删除重复记录: 最高效删除重复记录思路方法 ( 使用了ROWID)例子: DELETE FROM
EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);
(3)用TRUNCATE替代DELETE: 当删除表中记录时,在通常情况下, 回滚段(rollback
segments ) 用来存放可以被恢复信息. 如果你没有 COMMIT事务,ORACLE会将数据恢复到删除的前状态(准确地说是恢复到执行删除命令的前状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复信息.当命令运行后,数据不能被恢复.因此很少资源被,执行时间也会很短. (译者按: TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML)
(4)选择最有效率表名顺序(只在基于规则优化器中有效): ORACLE 解析器按照从右到左顺序
处理FROM子句中表名FROM子句中写在最后表(基础表 driving table)将被最先处理在FROM子句中包含多个表情况下,你必须选择记录条数最少表作为基础表如果有3个以上表连接查询,那就需要选择交叉表(ersection table)作为基础表, 交叉表是指那个被其它表所引用表.
(5)WHERE子句中连接顺序．: ORACLE采用自下而上顺序解析WHERE子句,根据这个原理,
表的间连接必须写在其它WHERE条件的前, 那些可以过滤掉最大数量记录条件必须写在WHERE子句末尾.
(6)SELECT子句中避免使用 ' * ': ORACLE在解析过程中, 会将'*' 依次转换成所有列名, 这个
工作是通过查询数据字典完成, 这意味着将耗费更多时间
(7)减少访问数据库次数: ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引利用率,
绑定变量 , 读数据块等；
(8)在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问
检索数据量 ,建议值为200 (9)使用DECODE来减少处理时间: 使用DECODE可以避免重复扫瞄相同记录或重复连接相同表. (11) 用Where子句替换HAVING子句；
4、 请列举几种SQL优化的方法？
1:mysql所在服务器内核 优化；此优化可由系统运维人员完成
2：mysql配置参数优化（my.cnf）；此优化需进行压力测试来进行参数调整
3：sql语句及表优化
①、当只要一行数据时使用 LIMIT 1
②、为搜索字段建索引
③、在Join表的时候使用相当类型的列，并将其索引
④、千万不要 ORDER BY RAND()
⑤、避免 SELECT *
⑥、永远为每张表设置一个ID
使用 ENUM 而不是 VARCHAR
⑧、尽可能的使用 NOT NULL
⑨、固定长度的表会更快
垂直分割
拆分大的 DELETE 或 INSERT 语句
越小的列会越快
5、 一个表中的ID有多个记录，把所有这个id的记录查出，并显示共有多少条记录数（SQL实现）
1、 SELECT id,COUNT(id) FROM `table` GROUP BY id HAVING COUNT(id) > 1
2、 SELECT * FROM (SELECT id,COUNT(id) sumid FROM `table` GROUP BY id) t WHERE t.sumid > 1
6、 查询表A中存在ID重复三次以上的记录。（使用SQL实现）
1. SELECT id,COUNT(id) FROM `table` GROUP BY id HAVING COUNT(id) > 3
2. SELECT * FROM (SELECT id,COUNT(id) sumid FROM `table` GROUP BY id) t WHERE t.sumid > 3
7、 数据库连接池的工作机制是什么？
连接池的实现是以空间换时间。
J2EE服务器启动时会建立一定数量的池连接，并一直维持不少于此数目的池连接。客户
端程序需要连接时，池驱动程序会返回一个未使用的池连接并将其表记为忙。如果当前没有空闲连接，池驱动程序就新建一定数量的连接，新建连接的数量有配置参数决定。当使用的池连接调用完成后，池驱动程序将此连接表记为空闲，其他调用就可以使用这个连接。
8、 什么是JDBC
9、 PreparedStatement是什么？
PreparedStatement是java.sql包下面的一个借口，用来执行SQL语句查询，通过调用
connection.preparedStatement(sql)方法可以或得到PreparedStatement对象。数据库系统会对sql语句继续拧预编译处理，预处理语句预先编译好，者条预编译的sql查询语句能在建立的查询中重用，这样一来，比Statement对象的查询速度更快。
10、 预编译处理的优势（9中的）
企业开发中很多地方都强制性的使用PreparedStatement来做SQL查询
1、 PreparedStatement可以写动态参数化的查询，可以写带参数的sql查询语句，通过使用相同的sql语句和不同的参数值来做查询比创建一个不同的查询语句要好，如：
select num from tb_user where id = ?
2、 PreparedStatement比 Statement 更快
使用PreparedStatement最重要的一点好处是它拥有更佳的性能优势，SQL语句会预编译在数据库系统中。执行计划同样会被缓存起来，他允许数据库做了参数化查询。使用预处理语句比普通语句查询更快，因为做的工作更少（数据库对SQL语句的分析，编译，优化已经在第一次查询前完成）。为了减少数据库的负载，生产环节中的JDBC代码应该总是使用PreparedStatement。
3、 PreparedStatement可以防止SQL注入式攻击，
1. strSQL
2. = "SELECT
3. * FROM users WHERE name = '"
4. + userName + "'
5. and pw = '"+
6. passWord +"';"
恶意填入：
1. userName
2. = "1'
3. OR '1'='1";
4.
5. passWord
6. = "1'
7. OR '1'='1";
最终变成了：
1. strSQL
2. = "SELECT
3. * FROM users WHERE name = '1' OR '1'='1' and pw = '1' OR '1'='1';"
因为WHERE条件恒为真，这就相当于执行：
1. strSQL
2. = "SELECT
3. * FROM users;"';"
因此可以达到无账号密码亦可登录网站。如果恶意用户要是更坏一点，用户填入：
1. strSQL
2. = "SELECT
3. * FROM users;"
SQL语句变成了：
1. strSQL
2. = "SELECT
3. * FROM users WHERE name = 'any_value' and pw = ''; DROP TABLE users"
这样一来，虽然没有登录，但是数据表都被删除了。
然而使用PreparedStatement的参数化的查询可以阻止大部分的SQL注入。在使用参数化查询的情况下，数据库系统不会讲参数的内容视为SQL指令的一部分来处理，
而是在数据库完成SQL指令的编译后，才套用参数运行，因此就算参数中含有破坏性的指令，也不会被数据库所运行。
避免SQL注入的第二种方式：在组合SQL字符串的时候，先对所传入的参数做字符取代（将单引号字符取代为连续2个单引号字符，因为连续两个单引号字符在SQL数据库中会视为字符中的一个单引号字符），如下：
1. strSQL
2. = "SELECT
3. * FROM users WHERE name = '"
4. + userName + "';"
传入字符串：
1. userName
2. = "
3. 1' OR 1=1 "
把userName做字符替换后变成：
1. userName
2. = "
3. 1'' OR 1=1"
最后生成的SQL查询语句为：
1. strSQL
2. = "SELECT * FROM users WHERE name = '1''
3. OR 1=1'
4、 可读性、安全性高
PreparedStatement的局限性
1， 为了防止SQL注入攻击，PreparedStatement不允许一个占位符?有多个值，在执行有IN子句查询的时候这个问题变得棘手，例如：以下使用PreparedStatement就不会返回任何结果。
2， SELECT
3， * FROM loan WHERE loan_type IN (?)
4，
5， preparedSatement.setString(1,"'personal
6， loan', 'home loan', 'gold loan'");
11、 左连接、右连接和内连接的区别是什么？
假设数据库中有A、B两张表
left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).B表记录不足的地方均为NULL.
Right join以右表(B)为基础的,A表不足的地方用NULL填充.
inner join并不以谁为基础,它只显示符合条件的记录
12、 表和视图的区别以及各自的用途
13、 when is more faster IN and EXISTS? Why?
14、 SQL编程题
表结构：
15、 --Student(S#,Sname,Sage,Ssex) 学生表
16、 Student(sid,Sname,Sage,Ssex) 学生表
17、 CREATE TABLE student (
18、 sid varchar(10) NOT NULL,
19、 sName varchar(20) DEFAULT NULL,
20、 sAge datetime DEFAULT '1980-10-12 23:12:36',
21、 sSex varchar(10) DEFAULT NULL,
22、 PRIMARY KEY (sid)
23、 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
24、 Course(cid,Cname,tid) 课程表
25、 CREATE TABLE course (
26、 cid varchar(10) NOT NULL,
27、 cName varchar(10) DEFAULT NULL,
28、 tid int(20) DEFAULT NULL,
29、 PRIMARY KEY (cid)
30、 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
31、 SC(sid,cid,score) 成绩表
32、 CREATE TABLE sc (
33、 sid varchar(10) DEFAULT NULL,
34、 cid varchar(10) DEFAULT NULL,
35、 score int(10) DEFAULT NULL
36、 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
37、 Teacher(tid,Tname) 教师表
38、 CREATE TABLE taacher (
39、 tid int(10) DEFAULT NULL,
40、 tName varchar(10) DEFAULT NULL
41、 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
表数据：
1. insert into taacher(tid,tName) values (1,'李老师'),(2,'何以琛'),(3,'叶平');
2. insert into student(sid,sName,sAge,sSex) values ('1001','张三丰','1980-10-12 23:12:36','男'),('1002','张无极','1995-10-12 23:12:36','男'),('1003','李奎','1992-10-12 23:12:36','女'),('1004','李元宝','1980-10-12 23:12:36','女'),('1005','李世明','1981-10-12 23:12:36','男'),('1006','赵六','1986-10-12 23:12:36','男'),('1007','田七','1981-10-12 23:12:36','女');
3. insert into sc(sid,cid,score) values ('1','001',80),('1','002',60),('1','003',75),('2','001',85),('2','002',70),('3','004',100),('3','001',90),('3','002',55),('4','002',65),('4','003',60);
4. insert into course(cid,cName,tid) values ('001','企业管理',3),('002','马克思',3),('003','UML',2),('004','数据库',1),('005','英语',1);
Oracle表+数据
1. --ORACLE(表+数据)
2. CREATE TABLE student (
3. sid varchar2(10) NOT NULL,
4. sName varchar2(20) DEFAULT NULL,
5. sAge date ,
6. sSex varchar2(10) DEFAULT NULL,
7. PRIMARY KEY (sid)
8. )
9.
10. CREATE TABLE course (
11. cid varchar2(10) NOT NULL,
12. cName varchar2(10) DEFAULT NULL,
13. tid number(20) DEFAULT NULL,
14. PRIMARY KEY (cid)
15. )
16.
17. CREATE TABLE sc (
18. sid varchar2(10) DEFAULT NULL,
19. cid varchar2(10) DEFAULT NULL,
20. score number(10) DEFAULT NULL
21. )
22.
23.
24. CREATE TABLE teacher (
25. tid number(10) DEFAULT NULL,
26. tName varchar2(10) DEFAULT NULL
27. )
28.
29. insert into course(cid,cName,tid) values ('001','企业管理',3);
30. insert into course(cid,cName,tid) values ('002','马克思',3);
31. insert into course(cid,cName,tid) values ('004','数据库',1);
32. insert into course(cid,cName,tid) values ('005','英语',1);
33.
34. insert into sc(sid,cid,score) values ('1001','001',80);
35. insert into sc(sid,cid,score) values ('1001','002',60);
36. insert into sc(sid,cid,score) values ('1001','003',70);
37. insert into sc(sid,cid,score) values ('1002','001',85);
38. insert into sc(sid,cid,score) values ('1002','002',70);
39. insert into sc(sid,cid,score) values ('1003','004',90);
40. insert into sc(sid,cid,score) values ('1003','001',90);
41. insert into sc(sid,cid,score) values ('1003','002',99);
42. insert into sc(sid,cid,score) values ('1004','002',65);
43. insert into sc(sid,cid,score) values ('1004','003',50);
44. insert into sc(sid,cid,score) values ('1005','005',80);
45. insert into sc(sid,cid,score) values ('1005','004',70);
46. insert into sc(sid,cid,score) values ('1003','003',10);
47. insert into sc(sid,cid,score) values ('1003','005',10);
48.
49.
50. insert into student(sid,sName,sAge,sSex) values ('1001','张三丰',to_date('1980-10-12 23:12:36','YYYY-MM-DD HH24:MI:SS'),'男');
51. insert into student(sid,sName,sAge,sSex) values ('1002','张无极',to_date('1995-10-12 23:12:36','YYYY-MM-DD HH24:MI:SS'),'男');
52. insert into student(sid,sName,sAge,sSex) values ('1003','李奎',to_date('1992-10-12 23:12:36','YYYY-MM-DD HH24:MI:SS'),'女');
53. insert into student(sid,sName,sAge,sSex) values ('1004','李元宝',to_date('1980-10-12 23:12:36','YYYY-MM-DD HH24:MI:SS'),'女');
54. insert into student(sid,sName,sAge,sSex) values ('1005','李世明',to_date('1981-10-12 23:12:36','YYYY-MM-DD HH24:MI:SS'),'男');
55. insert into student(sid,sName,sAge,sSex) values ('1006','赵六',to_date('1986-10-12 23:12:36','YYYY-MM-DD HH24:MI:SS'),'男');
56. insert into student(sid,sName,sAge,sSex) values ('1007','田七',to_date('1981-10-12 23:12:36','YYYY-MM-DD HH24:MI:SS'),'女');
57.
58. insert into teacher(tid,tName) values (1,'李老师');
59. insert into teacher(tid,tName) values (2,'何以琛');
60. insert into teacher(tid,tName) values (3,'叶平');
SQL问题：
1. --1.查询“001”课程比“002”课程成绩高的所有学生的学号;
2. select a.sid from (select sid,score from SC where cid='001') a,(select sid,score
3. from SC where cid='002') b
4. where a.score>b.score and a.sid=b.sid;
5.
6. --2、查询平均成绩大于60分的同学的学号和平均成绩;
7. select sid,avg(score)
8. from sc
9. group by sid having avg(score) >60;
10.
11. --3、查询所有同学的学号、姓名、选课数、总成绩;
12. select Student.sid,Student.Sname,count(SC.cid),sum(score)
13. from Student left Outer join SC on Student.sid=SC.sid
14. group by Student.sid,Sname
15.
16. --4、查询姓“李”的老师的个数;
17. select count(distinct(Tname))
18. from Teacher
19. where Tname like '李%';
20.
21. --5、查询没学过“叶平”老师课的同学的学号、姓名;
22. select Student.sid,Student.Sname
23. from Student
24. where sid not in (select distinct( SC.sid) from SC,Course,Teacher where SC.cid=Course.cid and Teacher.tid=Course.tid and Teacher.Tname='叶平');
25.
26. --6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
27. A:select Student.sid,Student.Sname from Student,SC where Student.sid=SC.sid and SC.cid='001'and exists( Select * from SC as SC_2 where SC_2.sid=SC.sid and SC_2.cid='002');
28. B:SELECT s.sid,s.sName
29. FROM student s, (SELECT sid,COUNT(cid) FROM sc WHERE cid IN ('001','002') GROUP BY sid HAVING COUNT(cid)>=2) t WHERE s.sid = t.sid
30.
31. --7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
32. select sid,Sname
33. from Student
34. where sid in (select sid from SC ,Course ,Teacher where SC.cid=Course.cid and Teacher.tid=Course.tid and Teacher.Tname='叶平' group by sid having count(SC.cid)=(select count(cid) from Course,Teacher where Teacher.tid=Course.tid and Tname='叶平'));
35.
36. --8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
37. 1>Select sid,Sname from (select Student.sid,Student.Sname,score ,(select score from SC SC_2 where SC_2.sid=Student.sid and SC_2.cid='002') score2
38. from Student,SC where Student.sid=SC.sid and cid='001') S_2 where score2 <score;
39. 2>SELECT s.sid,s.sName FROM student s,
40. (SELECT sid,score FROM sc WHERE cid = '001') sc_1,
41. (SELECT sid,score FROM sc WHERE cid = '002') sc_2
42. WHERE sc_1.sid = sc_2.sid AND s.sid = sc_2.sid AND sc_2.score < sc_1.score
43.
44. --9、查询所有课程成绩小于60分的同学的学号、姓名;
45. select sid,Sname
46. from Student
47. where sid not in (select Student.sid from Student,SC where S.sid=SC.sid and score>60);
48.
49. --10、查询没有学全所有课的同学的学号、姓名;
50. 1>
51. select Student.sid,Student.Sname
52. from Student,SC
53. where Student.sid=SC.sid group by Student.sid,Student.Sname having count(cid) <(select count(cid) from Course);
54. 2>
55. SELECT s.sid,s.sname FROM student s,
56. (SELECT sid,COUNT(cid) FROM sc GROUP BY sid HAVING COUNT(cid) < (SELECT COUNT(cid) FROM course) )t
57. WHERE s.sid = t.sid
58. --11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;
59. select sid,Sname from Student,SC where Student.sid=SC.sid and cid in (select cid from SC where sid='1001');
60.
61. --12、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;
62. UPDATE sc,(SELECT c.cid,AVG(score) avgs FROM sc,course c,teacher t WHERE sc.cid = c.cid AND
63. c.tid = t.tid AND t.tName = '叶平' GROUP BY c.cid)sc_2 SET sc.score = sc_2.avgs WHERE sc.cid = sc_2.cid
64.
65. --13、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名;
66. select sid from SC where cid in (select cid from SC where sid='1002')
67. group by sid having count(*)=(select count(*) from SC where sid='1002');
68.
69. --14、删除学习“叶平”老师课的SC表记录;
70. DELETE FROM sc WHERE sc.cid IN (SELECT sc.cid FROM course c ,teacher t WHERE sc.cid = c.cid AND c.tid = t.tid AND t.tName = '叶平')
71.
72. --15、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示： 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分
73. SELECT sid as 学生ID
74. ,(SELECT score FROM SC WHERE SC.sid=t.sid AND cid='004') AS 数据库
75. ,(SELECT score FROM SC WHERE SC.sid=t.sid AND cid='001') AS 企业管理
76. ,(SELECT score FROM SC WHERE SC.sid=t.sid AND cid='005') AS 英语
77. ,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩
78. FROM SC AS t
79. GROUP BY sid
80. ORDER BY avg(t.score)
81.
82. --16、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
83. select cid "课程ID",max(score) "最高分",min(score) "最低分" from sc group by cid
84.
85. --17、按各科平均成绩从低到高和及格率的百分数从高到低排序
86. oracle>
87. SELECT t.cid AS 课程号,MAX(course.Cname)AS 课程名,nvl(AVG(score),0) AS 平均成绩
88. ,100 * SUM(CASE WHEN nvl(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数
89. FROM SC T,Course
90. WHERE t.cid=course.cid
91. GROUP BY t.cid
92. ORDER BY 100 * SUM(CASE WHEN nvl(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC
93. Mysql>
94. SELECT t.cid AS 课程号,MAX(course.Cname)AS 课程名,IFNULL(AVG(score),0) AS 平均成绩
95. ,100 * SUM(CASE WHEN IFNULL(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数
96. FROM SC T,Course
97. WHERE t.cid=course.cid
98. GROUP BY t.cid
99. ORDER BY 100 * SUM(CASE WHEN IFNULL(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC
100.
101. --18、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），OO&UML （003），数据库（004）
102. SELECT SUM(CASE WHEN cid ='001' THEN score ELSE 0 END)/SUM(CASE cid WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分
103. ,100 * SUM(CASE WHEN cid = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = '001' THEN 1 ELSE 0 END) AS 企业管理及格百分数
104. ,SUM(CASE WHEN cid = '002' THEN score ELSE 0 END)/SUM(CASE cid WHEN '002' THEN 1 ELSE 0 END) AS 马克思平均分
105. ,100 * SUM(CASE WHEN cid = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = '002' THEN 1 ELSE 0 END) AS 马克思及格百分数
106. ,SUM(CASE WHEN cid = '003' THEN score ELSE 0 END)/SUM(CASE cid WHEN '003' THEN 1 ELSE 0 END) AS UML平均分
107. ,100 * SUM(CASE WHEN cid = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = '003' THEN 1 ELSE 0 END) AS UML及格百分数
108. ,SUM(CASE WHEN cid = '004' THEN score ELSE 0 END)/SUM(CASE cid WHEN '004' THEN 1 ELSE 0 END) AS 数据库平均分
109. ,100 * SUM(CASE WHEN cid = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = '004' THEN 1 ELSE 0 END) AS 数据库及格百分数
110. FROM SC
111.
112. --19、查询不同老师所教不同课程平均分从高到低显示 要求显示：教师ID,教师姓名，课程ID,课程名称，平均成绩
113. SELECT MAX(t.tid) "教师ID",MAX(t.tName) "教师姓名",c.cid "课程ID", MAX(c.cName) "课程名称" ,AVG(sc.score) "平均成绩"
114. FROM sc,course c,teacher t WHERE sc.cid = c.cid AND c.tid = t.tid GROUP BY c.tid,c.cid
115. ORDER BY AVG(sc.score) DESC
116.
117. --20、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
118. SELECT SC.cid as 课程ID, Cname as 课程名称
119. ,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85]
120. ,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70]
121. ,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60]
122. ,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -]
123. FROM SC,Course
124. where SC.cid=Course.cid
125. GROUP BY SC.cid,Cname;
126.
127. --21、查询每门课程被选修的学生数
128. select cid,count(sid) from sc group by cid;
129.
130. --22、查询出只选修了一门课程的全部学生的学号和姓名
131. select SC.sid,Student.Sname,count(cid) AS 选课数
132. from SC ,Student
133. where SC.sid=Student.sid group by SC.sid ,Student.Sname having count(cid)=1;
134.
135. --23、查询男生、女生人数
136. Select count(Ssex) as 男生人数 from Student group by Ssex having Ssex='男';
137. Select count(Ssex) as 女生人数 from Student group by Ssex having Ssex='女';
138.
139. --24、查询姓“张”的学生名单
140. SELECT Sname FROM Student WHERE Sname like '张%';
141. 30、查询同名同性学生名单，并统计同名人数
142. SELECT sName,sSex ,COUNT(*) FROM student GROUP BY sName,sSex HAVING COUNT(*) > 1
143.
144. --25、1981年出生的学生名单(注：Student表中Sage列的类型是datetime)
145. Mysql>
146. select Sname, CONVERT(char (11),DATEPART(year,Sage)) as age
147. from student
148. where CONVERT(char(11),DATEPART(year,Sage))='1981';
149. Oracle>
150. select * from student where substr(to_char(sage,'yyyy-MM-dd'),1,4)= '1981'
151.
152. --26、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
153. Select cid,Avg(score) from SC group by cid order by Avg(score),cid DESC ;
154.
155. --27、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
156. select Sname,SC.sid ,avg(score)
157. from Student,SC
158. where Student.sid=SC.sid group by SC.sid,Sname having avg(score)>85;
159.
160. --28、查询课程名称为“数据库”，且分数低于60的学生姓名和分数
161. Select Sname,isnull(score,0)
162. from Student,SC,Course
163. where SC.sid=Student.sid and SC.cid=Course.cid and Course.Cname='数据库'and score <60;
164.
165. --29、查询所有学生的选课情况;
166. SELECT SC.sid,SC.cid,Sname,Cname
167. FROM SC,Student,Course
168. where SC.sid=Student.sid and SC.cid=Course.cid ;
169.
170. --30、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;
171. SELECT distinct student.sid,student.Sname,SC.cid,SC.score
172. FROM student,Sc
173. WHERE SC.score>=70 AND SC.sid=student.sid;
174.
175. --31、查询不及格的课程，并按课程号从大到小排列
176. select cid from sc where scor e <60 order by cid ;
177.
178. --32、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名;
179. select SC.sid,Student.Sname from SC,Student where SC.sid=Student.sid and Score>80 and cid='003';
180.
181. --33、求选了课程的学生人数
182. select count(*) from sc;
183.
184. --34、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩
185. select Student.Sname,score
186. from Student,SC,Course C,Teacher
187. where Student.sid=SC.sid and SC.cid=C.cid and C.tid=Teacher.tid and Teacher.Tname='叶平' and SC.score=(select max(score)from SC where cid=C.cid );
188.
189. --35、查询各个课程及相应的选修人数
190. select count(*) from sc group by cid;
191.
192. --36、查询不同课程成绩相同的学生的学号、课程号、学生成绩
193. select distinct A.sid,B.score from SC A ,SC B where A.Score=B.Score and A.cid <>B.cid ;
194.
195. --37、查询每门功课成绩最好的前两名
196. SELECT *
197. FROM sc t1
198. WHERE (
199. SELECT COUNT(*)
200. FROM sc t2
201. WHERE t1.cid=t2.cid
202. AND t2.score>=t1.score
203. ) <=2 ORDER BY t1.cid
204.
205. --38、统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
206. SELECT cid "课程号",COUNT(*) "选修人数" FROM sc GROUP BY cid HAVING COUNT(*) >10 ORDER BY COUNT(*) DESC,cid
207.
208. --39、检索至少选修两门课程的学生学号
209. select sid
210. from sc
211. group by sid
212. having count(*) > = 2
213.
214. --40、查询全部学生都选修的课程的课程号和课程名
215. SELECT s.sName,c.cName, COUNT(*) FROM student s,course c, sc WHERE s.sid = sc.sid AND sc.cid = c.cid GROUP BY sc.cid HAVING COUNT(*) = (SELECT COUNT(*) FROM student)
216.
217. --41、查询没学过“叶平”老师讲授的任一门课程的学生姓名
218. SELECT DISTINCT Sname FROM Student WHERE sid NOT IN (SELECT sid FROM Course,Teacher,SC WHERE Course.tid=Teacher.tid AND SC.cid=course.cid AND Tname='叶平');
219.
220. --42、查询两门以上不及格课程的同学的学号及其平均成绩
221. select sid,avg(ifnull(score,0)) from SC where sid in (select sid from SC where score <60 group by sid having count(*)>2)group by sid;
222.
223. --43、检索“004”课程分数小于60，按分数降序排列的同学学号
224. select sid from SC where cid='004'and score <60 order by score desc;
225.
226. --44、删除“1002”同学的“001”课程的成绩
227. delete from Sc where sid='1002' and cid='001';
行转列
1. CREATE TABLE stu_score (
2. grade_id varchar(10) DEFAULT NULL,
3. subject_name varchar(10) DEFAULT NULL,
4. max_score int(10) DEFAULT NULL
5. )
6. insert into `stu_score`(`grade_id`,`subject_name`,`max_score`) values('1','语文',98);
7. insert into `stu_score`(`grade_id`,`subject_name`,`max_score`) values('2','数学',95);
8. insert into `stu_score`(`grade_id`,`subject_name`,`max_score`) values('2','政治',87);
9. insert into `stu_score`(`grade_id`,`subject_name`,`max_score`) values('5','语文',97);
10. insert into `stu_score`(`grade_id`,`subject_name`,`max_score`) values('5','数学',100);
11. insert into `stu_score`(`grade_id`,`subject_name`,`max_score`) values('5','政治',92);
转换步骤
1. --1.查询出要求的结果格式
2. SELECT
3. CASE grade_id WHEN 1 THEN '一年级'
4. WHEN 2 THEN '二年级'
5. WHEN 5 THEN '五年级'
6. END AS '年级',
7. CASE subject_name WHEN '语文' THEN max_score END AS '语文',
8. CASE subject_name WHEN '数学' THEN max_score END AS '数学',
9. CASE subject_name WHEN '政治' THEN max_score END AS '政治'
10. FROM stu_score
11. --2,去除null
12. SELECT
13. CASE grade_id WHEN 1 THEN '一年级'
14. WHEN 2 THEN '二年级'
15. WHEN 5 THEN '五年级'
16. END AS '年级',
17. IFNULL(CASE subject_name WHEN '语文' THEN max_score END,0) AS '语文',
18. IFNULL(CASE subject_name WHEN '数学' THEN max_score END,0) AS '数学',
19. IFNULL(CASE subject_name WHEN '政治' THEN max_score END,0) AS '政治'
20. FROM stu_score
21. --3.合并结果。
22. SELECT
23. CASE grade_id WHEN 1 THEN '一年级'
24. WHEN 2 THEN '二年级'
25. WHEN 5 THEN '五年级'
26. END AS '年级',
27. MAX(IFNULL(CASE subject_name WHEN '语文' THEN max_score END,0)) AS '语文',
28. MAX(IFNULL(CASE subject_name WHEN '数学' THEN max_score END,0)) AS '数学',
29. MAX(IFNULL(CASE subject_name WHEN '政治' THEN max_score END,0)) AS '政治'
30. FROM stu_score
31. GROUP BY grade_id
扫一扫
45万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
