免费又实用的网站,wordpress本地主机,网站开发服务费计入什么科目,vps如何wordpress文章目录 SQL基本操作1 设计表操作1.1 关系表字段1.2 关系表创建1.3 关系表数据1.4 关系表关系 2 SQL操作2.1 SQL 1-102.2 SQL 11-202.3 SQL 21-302.4 SQL 31-402.5 SQL 41-50 SQL基本操作
1 设计表操作
1.1 关系表字段
1 学生表 student
s_ids_names_births_sex学生编号学… 文章目录 SQL基本操作1 设计表操作1.1 关系表字段1.2 关系表创建1.3 关系表数据1.4 关系表关系 2 SQL操作2.1 SQL 1-102.2 SQL 11-202.3 SQL 21-302.4 SQL 31-402.5 SQL 41-50 SQL基本操作
1 设计表操作
1.1 关系表字段
1 学生表 student
s_ids_names_births_sex学生编号学生姓名学生年月学生性别
2 课程表 course
c_idc_namet_id课程编号课程名称教师标号
3 教师表 teacher
t_idt_name教师编号教师姓名
4 成绩表 score
s_idc_ids_score学生编号课程编号课程分数
1.2 关系表创建
1 创建 student 表
CREATE TABLE student(
s_id VARCHAR(20),
s_name VARCHAR(20) NOT NULL DEFAULT ,
s_birth VARCHAR(20) NOT NULL DEFAULT ,
s_sex VARCHAR(10) NOT NULL DEFAULT ,
PRIMARY KEY(s_id)
) ENGINEINNODB DEFAULT CHARSETutf8;2 创建 course 表
CREATE TABLE course(
c_id VARCHAR(20),
c_name VARCHAR(20) NOT NULL DEFAULT ,
t_id VARCHAR(20) NOT NULL,
PRIMARY KEY(c_id)
) ENGINEINNODB DEFAULT CHARSETutf8;3 创建 teacher 表
CREATE TABLE teacher(
t_id VARCHAR(20),
t_name VARCHAR(20) NOT NULL DEFAULT ,
PRIMARY KEY(t_id)
) ENGINEINNODB DEFAULT CHARSETutf8;4 创建score表
CREATE TABLE score(
s_id VARCHAR(20),
c_id VARCHAR(20),
s_score INT(3),
PRIMARY KEY(s_id,c_id)
) ENGINEInnoDB DEFAULT CHARSETutf8;1.3 关系表数据
INSERT INTO student VALUES(01 , 赵雷 , 1990-01-01 , 男);
INSERT INTO student VALUES(02 , 钱电 , 1990-12-21 , 男);
INSERT INTO student VALUES(03 , 孙风 , 1990-05-20 , 男);
INSERT INTO student VALUES(04 , 李云 , 1990-08-06 , 男);
INSERT INTO student VALUES(05 , 周梅 , 1991-12-01 , 女);
INSERT INTO student VALUES(06 , 吴兰 , 1992-03-01 , 女);
INSERT INTO student VALUES(07 , 郑竹 , 1989-07-01 , 女);
INSERT INTO student VALUES(08 , 王菊 , 1990-01-20 , 女);INSERT INTO course VALUES(01 , 语文 , 02);
INSERT INTO course VALUES(02 , 数学 , 01);
INSERT INTO course VALUES(03 , 英语 , 03);INSERT INTO teacher VALUES(01 , 张雪峰);
INSERT INTO teacher VALUES(02 , 李佳琦);
INSERT INTO teacher VALUES(03 , 王思聪);INSERT INTO score VALUES(01 , 01 , 80);
INSERT INTO score VALUES(01 , 02 , 90);
INSERT INTO score VALUES(01 , 03 , 99);
INSERT INTO score VALUES(02 , 01 , 70);
INSERT INTO score VALUES(02 , 02 , 60);
INSERT INTO score VALUES(02 , 03 , 80);
INSERT INTO score VALUES(03 , 01 , 80);
INSERT INTO score VALUES(03 , 02 , 80);
INSERT INTO score VALUES(03 , 03 , 80);
INSERT INTO score VALUES(04 , 01 , 50);
INSERT INTO score VALUES(04 , 02 , 30);
INSERT INTO score VALUES(04 , 03 , 20);
INSERT INTO score VALUES(05 , 01 , 76);
INSERT INTO score VALUES(05 , 02 , 87);
INSERT INTO score VALUES(06 , 01 , 31);
INSERT INTO score VALUES(06 , 03 , 34);
INSERT INTO score VALUES(07 , 02 , 89);
INSERT INTO score VALUES(07 , 03 , 98);1.4 关系表关系 2 SQL操作
2.1 SQL 1-10
1、查询 “01” 课程比 “02” 课程成绩高的学生的信息及课程分数
SELECT a.* ,b.s_score AS 01_score,c.s_score AS 02_score
FROM student a
JOIN score b
ON a.s_idb.s_id AND b.c_id01
LEFT JOIN Score c
ON a.s_idc.s_id AND c.c_id02 OR c.c_id NULL
WHERE b.s_scorec.s_score;2、查询01课程比02课程成绩低的学生的信息及课程分数
SELECT a.* ,b.s_score AS 01_score,c.s_score AS 02_score
FROM student a
LEFT JOIN score b
ON a.s_idb.s_id AND b.c_id01 OR b.c_idNULL
JOIN score c
ON a.s_idc.s_id AND c.c_id02
WHERE b.s_scorec.s_score;3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT b.s_id,b.s_name,ROUND(AVG(a.s_score),2) AS avg_score
FROM student b
JOIN score a ON b.s_id a.s_id
GROUP BY b.s_id,b.s_name
HAVING ROUND(AVG(a.s_score),2)60;4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 (包括有成绩的和无成绩的)
SELECT b.s_id,b.s_name,ROUND(AVG(a.s_score),2) AS avg_score
FROM student b
LEFT JOIN score a
ON b.s_id a.s_id
GROUP BY b.s_id,b.s_name
HAVING ROUND(AVG(a.s_score),2)60
UNION
SELECT a.s_id,a.s_name,0 AS avg_score
FROM student a
WHERE a.s_id NOT IN (SELECT DISTINCT s_id FROM score);5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT a.s_id,a.s_name,
COUNT(b.c_id) AS sum_course,
SUM(b.s_score) AS sum_score
FROM student a
LEFT JOIN score b
ON a.s_idb.s_id
GROUP BY a.s_id,a.s_name;6、查询李姓老师的数量
SELECT COUNT(t_id) FROM teacher WHERE t_name LIKE 李%;7、查询学过张雪峰老师授课的同学的信息
SELECT a.*
FROM student a
JOIN score b
ON a.s_idb.s_id
WHERE b.c_id IN(
SELECT c_id FROM course WHERE t_id (
SELECT t_id FROM teacher WHERE t_name 张雪峰));8、查询没学过张雪峰老师授课的同学的信息
SELECT c.*
FROM student c
WHERE c.s_id NOT IN(
SELECT a.s_id FROM student a JOIN score b ON a.s_idb.s_id
WHERE b.c_id IN(
SELECT c_id FROM course WHERE t_id (
SELECT t_id FROM teacher WHERE t_name 张雪峰)));9、查询学过编号为01并且也学过编号为02的课程的同学的信息
SELECT a.*
FROM student a,score b,score c
WHERE a.s_id b.s_id
AND a.s_id c.s_id
AND b.c_id01
AND c.c_id02;10、查询学过编号为01但是没有学过编号为02的课程的同学的信息
SELECT a.*
FROM student a
WHERE a.s_id IN
(SELECT s_id FROM score WHERE c_id01 )
AND a.s_id NOT IN
(SELECT s_id FROM score WHERE c_id02)2.2 SQL 11-20
11、查询没有学全所有课程的同学的信息
SELECT s.*
FROM student s
WHERE s.s_id IN(
SELECT s_id FROM score WHERE s_id NOT IN(
SELECT a.s_id FROM score a
JOIN score b ON a.s_id b.s_id AND b.c_id02
JOIN score c ON a.s_id c.s_id AND c.c_id03
WHERE a.c_id01))12、查询至少有一门课与学号为01的同学所学相同的同学的信息
SELECT *
FROM student
WHERE s_id IN(
SELECT DISTINCT a.s_id FROM score a WHERE a.c_id IN(
SELECT a.c_id FROM score a WHERE a.s_id01));13、查询和01号的同学学习的课程完全相同的其他同学的信息 方式1
SELECT * FROM student WHERE s_id IN(
SELECT DISTINCT d.s_id FROM (
SELECT b.s_id , c.c_id FROM (
SELECT s.s_id,COUNT(a.c_id) c_num
FROM student s,score a
WHERE s.s_id a.s_id
GROUP BY s.s_id
HAVING c_num(SELECT COUNT(c_id) FROM score WHERE s_id01)) b
JOIN score c
WHERE b.s_id c.s_id AND c.c_id IN (
SELECT c_id FROM score WHERE s_id01)) d)
AND s_id !01; 第一步先查询s_id是01的学生的课程号和课程号的个数
SELECT COUNT(c_id) FROM score WHERE s_id01
SELECT c_id FROM score WHERE s_id01
第二步求学生c_id的个数是3的s_id,并且c_id在第一步查询的c_id的值内
SELECT b.s_id , c.c_id FROM (
SELECT s.s_id,COUNT(a.c_id) c_num
FROM student s,score a
WHERE s.s_id a.s_id
GROUP BY s.s_id
HAVING c_num(
SELECT COUNT(c_id)
FROM score WHERE s_id01)) b
JOIN score c WHERE b.s_id c.s_id AND c.c_id IN (
SELECT c_id FROM score WHERE s_id01)
第三步查询第二步的结果中的s_id
第四步查询学生信息且s_id在第三步的s_id中 并且去除掉编号是01的学生信息方式2
SELECT *
FROM student
WHERE s_id IN (
SELECT s_id FROM (
SELECT s_id, COUNT( s_id ) cou
FROM (
SELECT * FROM score WHERE s_id IN (
SELECT s_id FROM (
SELECT s_id, COUNT( s_id ) COUNT
FROM score WHERE s_id ! 01 GROUP BY s_id ) t1
WHERE t1.count (
SELECT COUNT( c_id ) FROM score WHERE s_id 01 ))) t3
WHERE t3.c_id IN (
SELECT c_id FROM score WHERE s_id 01 ) GROUP BY s_id ) t4
WHERE t4.cou(SELECT COUNT( c_id ) FROM score WHERE s_id 01))– 14、查询没学过张三老师讲授的任一门课程的学生姓名
select a.s_name from student a where a.s_id not in ( select s_id from score where c_id (select c_id from course where t_id ( select t_id from teacher where t_name ‘张三’)) group by s_id);
– 15、查询两门及其以上不及格课程的同学的学号姓名及其平均成绩
select a.s_id,a.s_name,ROUND(AVG(b.s_score)) from student a left join score b on a.s_id b.s_id where a.s_id in( select s_id from score where s_score60 GROUP BY s_id having count(1)2) GROUP BY a.s_id,a.s_name
– 16、检索01课程分数小于60按分数降序排列的学生信息
select a.*,b.c_id,b.s_score from student a,score b where a.s_id b.s_id and b.c_id‘01’ and b.s_score60 ORDER BY b.s_score DESC;
– 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select a.s_id,(select s_score from score where s_ida.s_id and c_id‘01’) as 语文, (select s_score from score where s_ida.s_id and c_id‘02’) as 数学, (select s_score from score where s_ida.s_id and c_id‘03’) as 英语, round(avg(s_score),2) as 平均分 from score a GROUP BY a.s_id ORDER BY 平均分 DESC;
– 18.查询各科成绩最高分、最低分和平均分以如下形式显示课程ID课程name最高分最低分平均分及格率中等率优良率优秀率 –及格为60中等为70-80优良为80-90优秀为90
select a.c_id,b.c_name,MAX(s_score),MIN(s_score),ROUND(AVG(s_score),2), ROUND(100*(SUM(case when a.s_score60 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 及格率, ROUND(100*(SUM(case when a.s_score70 and a.s_score80 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 中等率, ROUND(100*(SUM(case when a.s_score80 and a.s_score90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 优良率, ROUND(100*(SUM(case when a.s_score90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 优秀率 from score a left join course b on a.c_id b.c_id GROUP BY a.c_id,b.c_name
– 19、按各科成绩进行排序并显示排名(实现不完全) – mysql没有rank函数
select a.s_id,a.c_id, i:i 1 as i保留排名, k:(case when scorea.s_score then k else i end) as rank不保留排名, score:a.s_score as score from ( select s_id,c_id,s_score from score WHERE c_id‘01’ GROUP BY s_id,c_id,s_score ORDER BY s_score DESC )a,(select k:0,i:0,score:0)s union select a.s_id,a.c_id, i:i 1 as i, k:(case when scorea.s_score then k else i end) as rank, score:a.s_score as score from ( select s_id,c_id,s_score from score WHERE c_id‘02’ GROUP BY s_id,c_id,s_score ORDER BY s_score DESC )a,(select k:0,i:0,score:0)s union select a.s_id,a.c_id, i:i 1 as i, k:(case when scorea.s_score then k else i end) as rank, score:a.s_score as score from ( select s_id,c_id,s_score from score WHERE c_id‘03’ GROUP BY s_id,c_id,s_score ORDER BY s_score DESC )a,(select k:0,i:0,score:0)s
– 20、查询学生的总成绩并进行排名
select a.s_id, i:i1 as i, k:(case when scorea.sum_score then k else i end) as rank, score:a.sum_score as score from (select s_id,SUM(s_score) as sum_score from score GROUP BY s_id ORDER BY sum_score DESC)a, (select k:0,i:0,score:0)s
2.3 SQL 21-30
– 21、查询不同老师所教不同课程平均分从高到低显示
select a.t_id,c.t_name,a.c_id,ROUND(avg(s_score),2) as avg_score from course a left join score b on a.c_idb.c_id left join teacher c on a.t_idc.t_id GROUP BY a.c_id,a.t_id,c.t_name ORDER BY avg_score DESC; 1 2 3 4 – 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
select d.,c.排名,c.s_score,c.c_id from ( select a.s_id,a.s_score,a.c_id,i:i1 as 排名 from score a,(select i:0)s where a.c_id‘01’ )c left join student d on c.s_idd.s_id where 排名 BETWEEN 2 AND 3 UNION select d.,c.排名,c.s_score,c.c_id from ( select a.s_id,a.s_score,a.c_id,j:j1 as 排名 from score a,(select j:0)s where a.c_id‘02’ )c left join student d on c.s_idd.s_id where 排名 BETWEEN 2 AND 3 UNION select d.*,c.排名,c.s_score,c.c_id from ( select a.s_id,a.s_score,a.c_id,k:k1 as 排名 from score a,(select k:0)s where a.c_id‘03’ )c left join student d on c.s_idd.s_id where 排名 BETWEEN 2 AND 3;
– 23、统计各科成绩各分数段人数课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
select distinct f.c_name,a.c_id,b.85-100,b.百分比,c.70-85,c.百分比,d.60-70,d.百分比,e.0-60,e.百分比 from score a left join (select c_id,SUM(case when s_score 85 and s_score 100 then 1 else 0 end) as 85-100, ROUND(100*(SUM(case when s_score 85 and s_score 100 then 1 else 0 end)/count()),2) as 百分比 from score GROUP BY c_id)b on a.c_idb.c_id left join (select c_id,SUM(case when s_score 70 and s_score 85 then 1 else 0 end) as 70-85, ROUND(100(SUM(case when s_score 70 and s_score 85 then 1 else 0 end)/count()),2) as 百分比 from score GROUP BY c_id)c on a.c_idc.c_id left join (select c_id,SUM(case when s_score 60 and s_score 70 then 1 else 0 end) as 60-70, ROUND(100(SUM(case when s_score 60 and s_score 70 then 1 else 0 end)/count()),2) as 百分比 from score GROUP BY c_id)d on a.c_idd.c_id left join (select c_id,SUM(case when s_score 0 and s_score 60 then 1 else 0 end) as 0-60, ROUND(100(SUM(case when s_score 0 and s_score 60 then 1 else 0 end)/count(*)),2) as 百分比 from score GROUP BY c_id)e on a.c_ide.c_id left join course f on a.c_id f.c_id
– 24、查询学生平均成绩及其名次
select a.s_id, i:i1 as ‘不保留空缺排名’, k:(case when avg_scorea.avg_s then k else i end) as ‘保留空缺排名’, avg_score:avg_s as ‘平均分’ from (select s_id,ROUND(AVG(s_score),2) as avg_s from score GROUP BY s_id)a,(select avg_score:0,i:0,k:0)b;
– 25、查询各科成绩前三名的记录 – 1.选出b表比a表成绩大的所有组 – 2.选出比当前id成绩大的 小于三个的
select a.s_id,a.c_id,a.s_score from score a left join score b on a.c_id b.c_id and a.s_scoreb.s_scoregroup by a.s_id,a.c_id,a.s_score HAVING COUNT(b.s_id)3ORDER BY a.c_id,a.s_score DESC1 2 3 4 – 26、查询每门课程被选修的学生数
select c_id,count(s_id) from score a GROUP BY c_id1 – 27、查询出只有两门课程的全部学生的学号和姓名
select s_id,s_name from student where s_id in( select s_id from score GROUP BY s_id HAVING COUNT(c_id)2); 1 2 – 28、查询男生、女生人数
select s_sex,COUNT(s_sex) as 人数 from student GROUP BY s_sex1 – 29、查询名字中含有风字的学生信息
select * from student where s_name like %风%;1 – 30、查询同名同性学生名单并统计同名人数
select a.s_name,a.s_sex,count(*) from student a JOIN student b on a.s_id !b.s_id and a.s_name b.s_name and a.s_sex b.s_sex
GROUP BY a.s_name,a.s_sex1 2 3
2.4 SQL 31-40
– 31、查询1990年出生的学生名单
select s_name from student where s_birth like ‘1990%’ 1 – 32、查询每门课程的平均成绩结果按平均成绩降序排列平均成绩相同时按课程编号升序排列
select c_id,ROUND(AVG(s_score),2) as avg_score from score GROUP BY c_id ORDER BY avg_score DESC,c_id ASC 1 – 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select a.s_id,b.s_name,ROUND(avg(a.s_score),2) as avg_score from score a left join student b on a.s_idb.s_id GROUP BY s_id HAVING avg_score85 1 2 – 34、查询课程名称为数学且分数低于60的学生姓名和分数
select a.s_name,b.s_score from score b LEFT JOIN student a on a.s_idb.s_id where b.c_id(select c_id from course where c_name 数学) and b.s_score601 2 – 35、查询所有学生的课程及分数情况
select a.s_id,a.s_name, SUM(case c.c_name when ‘语文’ then b.s_score else 0 end) as ‘语文’, SUM(case c.c_name when ‘数学’ then b.s_score else 0 end) as ‘数学’, SUM(case c.c_name when ‘英语’ then b.s_score else 0 end) as ‘英语’, SUM(b.s_score) as ‘总分’ from student a left join score b on a.s_id b.s_id left join course c on b.c_id c.c_id GROUP BY a.s_id,a.s_name
– 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
select a.s_name,b.c_name,c.s_score from course b left join score c on b.c_id c.c_id left join student a on a.s_idc.s_id where c.s_score70
– 37、查询不及格的课程
select a.s_id,a.c_id,b.c_name,a.s_score from score a left join course b on a.c_id b.c_id where a.s_score60
–38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
select a.s_id,b.s_name from score a LEFT JOIN student b on a.s_id b.s_id where a.c_id ‘01’ and a.s_score80 1 2 – 39、求每门课程的学生人数
select count(*) from score GROUP BY c_id;
– 40、查询选修张三老师所授课程的学生中成绩最高的学生信息及其成绩
– 查询老师id select c_id from course c,teacher d where c.t_idd.t_id and d.t_name‘张三’ – 查询最高分可能有相同分数 select MAX(s_score) from score where c_id‘02’ – 查询信息 select a.*,b.s_score,b.c_id,c.c_name from student a LEFT JOIN Score b on a.s_id b.s_id LEFT JOIN course c on b.c_idc.c_id where b.c_id (select c_id from course c,Teacher d where c.t_idd.t_id and d.t_name‘张三’) and b.s_score in (select MAX(s_score) from Score where c_id(select c_id from course c,Teacher d where c.t_idd.t_id and d.t_name‘张三’))
2.5 SQL 41-50
– 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select DISTINCT b.s_id,b.c_id,b.s_score from score a,score b where a.c_id ! b.c_id and a.s_score b.s_score 1 – 42、查询每门功成绩最好的前两名 – 牛逼的写法
select a.s_id,a.c_id,a.s_score from Score a where (select COUNT(1) from Score b where b.c_ida.c_id and b.s_scorea.s_score)2 ORDER BY a.c_id
– 43、统计每门课程的学生选修人数超过5人的课程才统计。要求输出课程号和选修人数查询结果按人数降序排列若人数相同按课程号升序排列
select c_id,count(*) as total from score GROUP BY c_id HAVING total5 ORDER BY total,c_id ASC
– 44、检索至少选修两门课程的学生学号
select s_id,count(*) as sel from score GROUP BY s_id HAVING sel2
– 45、查询选修了全部课程的学生信息
select * from student where s_id in( select s_id from score GROUP BY s_id HAVING count(*)(select count(*) from course))1 2 –46、查询各学生的年龄 – 按照出生日期来算当前月日 出生年月的月日则年龄减一
select s_birth,(DATE_FORMAT(NOW(),’%Y’)-DATE_FORMAT(s_birth,’%Y’) - (case when DATE_FORMAT(NOW(),’%m%d’)DATE_FORMAT(s_birth,’%m%d’) then 0 else 1 end)) as age from student;
– 47、查询本周过生日的学生
select * from student where WEEK(DATE_FORMAT(NOW(),‘%Y%m%d’))WEEK(s_birth) select * from student where YEARWEEK(s_birth)YEARWEEK(DATE_FORMAT(NOW(),‘%Y%m%d’)) select WEEK(DATE_FORMAT(NOW(),‘%Y%m%d’)) 1 2 3 – 48、查询下周过生日的学生
select * from student where WEEK(DATE_FORMAT(NOW(),‘%Y%m%d’))1 WEEK(s_birth) 1 – 49、查询本月过生日的学生
select * from student where MONTH(DATE_FORMAT(NOW(),’%Y%m%d’)) MONTH(s_birth)
– 50、查询下月过生日的学生
select * from student where MONTH(DATE_FORMAT(NOW(),‘%Y%m%d’))1 MONTH(s_birth)
