Mysql经典练习题50题

最新推荐文章于 2024-04-21 22:37:14 发布
最新推荐文章于 2024-04-21 22:37:14 发布
阅读量586 收藏 14
点赞数
分类专栏： MySQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42072280/article/details/108919330
版权

https://www.cnblogs.com/zsty/p/10109125.html

http://www.manongjc.com/article/108264.html

 

                 

 

 

DROP DATABASE IF EXISTS anan50;
CREATE DATABASE anan50;
USE anan50;

-- 建表
-- 学生表
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`)
);
-- 课程表
CREATE TABLE `Course`(
	`c_id`  VARCHAR(20),
	`c_name` VARCHAR(20) NOT NULL DEFAULT '',
	`t_id` VARCHAR(20) NOT NULL,
	PRIMARY KEY(`c_id`)
);
-- 教师表
CREATE TABLE `Teacher`(
	`t_id` VARCHAR(20),
	`t_name` VARCHAR(20) NOT NULL DEFAULT '',
	PRIMARY KEY(`t_id`)
);
-- 成绩表
CREATE TABLE `Score`(
	`s_id` VARCHAR(20),
	`c_id`  VARCHAR(20),
	`s_score` INT(3),
	PRIMARY KEY(`s_id`,`c_id`)
);
-- 插入学生表测试数据
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、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
/*

*/
-- anan ----------------
-- (1)
SELECT
	t1.`s_id` , t1.`s_score` score_01, t2.`s_score` score_02
FROM 
	Score t1,
	Score t2
WHERE
	t1.`s_id` = t2.`s_id` 
	AND t1.`c_id` = 01 AND t2.`c_id` = 02
	AND t1.`s_score` > t2.`s_score`;

-- (2)
SELECT * FROM Student;

-- (3)
SELECT t4.*, t3.score_01, t3.score_02 
FROM 
	Student t4,
	(SELECT
		t1.`s_id` t1_id , t1.`s_score` score_01, t2.`s_score` score_02
	FROM 
		Score t1,
		Score t2
	WHERE
		t1.`s_id` = t2.`s_id` 
		AND t1.`c_id` = 01 AND t2.`c_id` = 02
		AND t1.`s_score` > t2.`s_score`) t3
WHERE 
	t4.`s_id` = t3.t1_id;

	
-- 参考 ----------------	

SELECT st.*,sc.s_score AS '语文' ,sc2.s_score '数学' 
FROM student st
LEFT JOIN score sc ON sc.s_id=st.s_id AND sc.c_id='01' 
LEFT JOIN score sc2 ON sc2.s_id=st.s_id AND sc2.c_id='02' OR sc2.`c_id` = NULL 
WHERE sc.s_score>sc2.s_score;



-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
SELECT st.*,sc.s_score AS '语文' ,sc2.s_score '数学' 
FROM student st
LEFT JOIN score sc ON sc.s_id=st.s_id AND sc.c_id='01' OR sc.c_id = NULL 
LEFT JOIN score sc2 ON sc2.s_id=st.s_id AND sc2.c_id='02'  
WHERE sc.s_score<sc2.s_score;



-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
-- anan ----------------
SELECT s_id, COUNT(c_id) FROM Score GROUP BY s_id;
SELECT s_id, COUNT(c_id), AVG(s_score) av FROM Score GROUP BY s_id;
SELECT s_id, COUNT(c_id), AVG(s_score) av FROM Score GROUP BY s_id HAVING av >= 60;

SELECT
	st.s_id, st.`s_name`, t_av.av
FROM 
	Student st,
	(SELECT s_id, COUNT(c_id), AVG(s_score) av FROM Score GROUP BY s_id HAVING av >= 60) t_av
WHERE 
	st.`s_id` = t_av.s_id;

-- 参考 ----------------
-- ROUND 函数用于把数值字段舍入为指定的小数位数。
SELECT st.s_id,st.s_name,ROUND(AVG(sc.s_score),2) cjScore FROM student st
LEFT JOIN score sc ON sc.s_id=st.s_id
GROUP BY st.s_id HAVING AVG(sc.s_score)>=60;



-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
-- 参考 ----------------
SELECT DISTINCT s_id FROM score;
--
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);
--
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;
--
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);
                
-- 参考 ----------------
SELECT st.s_id,st.s_name,(CASE WHEN ROUND(AVG(sc.s_score),2) IS NULL THEN 0 ELSE ROUND(AVG(sc.s_score)) END ) cjScore 
FROM student st
LEFT JOIN score sc ON sc.s_id=st.s_id
GROUP BY st.s_id HAVING AVG(sc.s_score)<60 OR AVG(sc.s_score) IS NULL;



-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
-- anan ----------------
SELECT * FROM Student st JOIN Score sc ON st.s_id = sc.`s_id` ;
SELECT st.`s_id`, st.`s_name` FROM Student st JOIN Score sc ON st.s_id = sc.`s_id`;
SELECT st.`s_id`, st.`s_name` FROM Student st JOIN Score sc ON st.s_id = sc.`s_id` GROUP BY sc.s_id;

SELECT st.`s_id`, st.`s_name`, COUNT(sc.c_id) '选课总数', SUM(sc.s_score) '总成绩' 
FROM Student st JOIN Score sc ON st.s_id = sc.`s_id` GROUP BY sc.s_id;

SELECT st.`s_id`, st.`s_name`, COUNT(sc.c_id) '选课总数', SUM(sc.s_score) '总成绩' 
FROM Student st LEFT JOIN Score sc ON st.s_id = sc.`s_id` GROUP BY sc.s_id;

SELECT st.`s_id`, st.`s_name`, COUNT(sc.c_id) '选课总数', IFNULL(SUM(sc.s_score),0) '总成绩' 
FROM Student st LEFT JOIN Score sc ON st.s_id = sc.`s_id` GROUP BY sc.s_id;


-- 6、查询"李"姓老师的数量 
-- anan ----------------
SELECT * FROM Teacher WHERE t_name LIKE '李%';
SELECT COUNT(t_id) FROM Teacher WHERE t_name LIKE '李%';


-- 7、查询学过"张三"老师授课的同学的信息 
-- anan ----------------
SELECT 
	t1.*
FROM
	Student t1,
	Course t2,
	Teacher t3,
	Score t4
WHERE 
	t3.`t_name`='张三'
	AND t2.`t_id`=t3.`t_id`
	AND t2.`c_id`=t4.`c_id`
	AND t1.`s_id`=t4.`s_id`;


-- 参考 ----------------
SELECT st.* FROM student st 
LEFT JOIN score sc ON sc.s_id=st.s_id
LEFT JOIN course c ON c.c_id=sc.c_id
LEFT JOIN teacher t ON t.t_id=c.t_id
 WHERE t.t_name="张三";




-- 8、查询没学过"张三"老师授课的同学的信息
-- anan ----------------
SELECT * FROM Student WHERE s_id NOT IN 
(SELECT 
	t1.s_id
FROM
	Student t1,
	Course t2,
	Teacher t3,
	Score t4
WHERE 
	t3.`t_name` = '张三'
	AND t2.`t_id`=t3.`t_id`
	AND t2.`c_id`=t4.`c_id`
	AND t1.`s_id`=t4.`s_id`);



-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

-- anan ----------------
SELECT st.* FROM Student st
LEFT JOIN Score sc1 ON st.`s_id` = sc1.`s_id`
LEFT JOIN Score sc2 ON st.`s_id` = sc2.`s_id`
WHERE sc1.`c_id` = '01' AND sc2.`c_id` = '02';

-- 参考 ----------------
SELECT st.*

FROM
	Student st,
	Score sc1,
	Score sc2
WHERE 
	st.`s_id` = sc1.`s_id` 
	AND st.`s_id` = sc2.`s_id` 
	AND sc1.`c_id` = '01' 
	AND sc2.`c_id` = '02';



-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

-- anan ----------------
SELECT * FROM Student st WHERE st.`s_id` IN 
(SELECT st.s_id

FROM
	Student st,
	Score sc1
WHERE 
	st.`s_id` = sc1.`s_id` 
	AND sc1.`c_id` = '01' )
AND 
st.`s_id` NOT IN
(SELECT st.s_id

FROM
	Student st,
	Score sc1
WHERE 
	st.`s_id` = sc1.`s_id` 
	AND sc1.`c_id` = '02');

-- 参考 ----------------

SELECT *
FROM 
	Student st 
WHERE 
	st.`s_id` IN 
	(SELECT sc1.s_id FROM Score sc1 WHERE sc1.`c_id` = '01' )
	AND st.`s_id` NOT IN
	(SELECT sc1.s_id FROM Score sc1 WHERE sc1.`c_id` = '02' );



-- 11、查询没有学全所有课程的同学的信息 
-- anan ----------------
SELECT * FROM Student st
WHERE st.`s_id` NOT IN
(SELECT sc1.s_id

FROM
	Score sc1,
	Score sc2,
	Score sc3
WHERE 
	sc1.`s_id` = sc2.`s_id` 
	AND sc2.`s_id` = sc3.`s_id`
	AND sc1.`c_id` = '01' 
	AND sc2.`c_id` = '02'
	AND sc3.`c_id` = '03');



-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息 

-- anan ----------------

SELECT * FROM Student st WHERE st.`s_id` IN
(SELECT sc2.s_id FROM Score sc2	 WHERE sc2.`s_id` != '01'
	AND sc2.`c_id` IN (SELECT sc.c_id FROM Score sc	WHERE sc.`s_id` = '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_id='01')
    );


-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息 
-- anan 参考了一下别人的----------------

SELECT st.* FROM Student st WHERE st.`s_id` IN 
(SELECT sc.`s_id` 
FROM Score sc 
WHERE sc.`c_id` IN 
(SELECT sc1.`c_id` FROM Score sc1 WHERE sc1.`s_id`='01')
GROUP BY sc.s_id
HAVING COUNT(sc.`c_id`) = (SELECT COUNT(sc1.`c_id`) FROM Score sc1 WHERE sc1.`s_id`='01'));


-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
-- anan ----------------
SELECT t1.`s_name` FROM Student t1 WHERE t1.`s_id` NOT IN
(SELECT t4.`s_id` FROM Score t4 WHERE t4.`c_id` IN 
(SELECT t2.`c_id` FROM Course t2 WHERE t2.`t_id` IN 
(SELECT t3.`t_id` FROM Teacher t3 WHERE t3.`t_name` = '张三')));

-- 参考 ----------------
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);
-- 参考 ----------------
SELECT st.s_name FROM student st 
WHERE st.s_id NOT IN (
SELECT sc.s_id FROM score sc 
INNER JOIN course c ON c.c_id=sc.c_id
INNER JOIN teacher t ON t.t_id=c.t_id AND t.t_name="张三"
);



-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
-- anan ----------------
SELECT st.`s_id`, st.`s_name`, ROUND(AVG(sc.`s_score`)) avg_score FROM Score sc 
JOIN Student st ON st.`s_id` = sc.`s_id`
WHERE sc.`s_score` < 60
GROUP BY sc.`s_id` HAVING COUNT(1) >= 2;


-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息
-- anan 没有显示出来分数----------------
SELECT st.* FROM Student st WHERE st.`s_id` IN 
(SELECT sc.`s_id` FROM Score sc WHERE sc.`c_id` = '01' AND sc.`s_score` < 60 ORDER BY sc.`s_score` DESC);

-- anan 显示出了分数----------------
SELECT st.*, t1.score
FROM Student st,
((SELECT sc.`s_id` id, sc.`s_score` score FROM Score sc WHERE sc.`c_id` = '01' AND sc.`s_score` < 60 ORDER BY sc.`s_score` DESC)) t1
WHERE st.`s_id` = t1.id;

-- 参考 ----------------
SELECT st.*,sc.s_score FROM student st 
INNER JOIN score sc ON sc.s_id=st.s_id AND sc.c_id="01" AND sc.s_score<60
ORDER BY sc.s_score DESC;

-- 参考 ----------------
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_score<60 ORDER BY b.s_score DESC;



-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
 -- 可加round,case when then else end 使显示更完美
 -- anan ----------------
SELECT 
	sc.`s_id`, AVG(sc.`s_score`) avg_score
FROM 
	Score sc
GROUP BY
	sc.`s_id`
ORDER BY 
	avg_score DESC; 
 
 -- 
SELECT 
	st.*, sc.*, t1.avg_score
FROM 
	Student st,
	Score sc,
	(SELECT 
		sc.`s_id`, AVG(sc.`s_score`) avg_score
	FROM 
		Score sc
	GROUP BY
		sc.`s_id`
	ORDER BY 
		avg_score DESC) t1
WHERE 
	sc.`s_id` = t1.`s_id` AND st.`s_id` = sc.`s_id`
ORDER BY
	t1.avg_score DESC
;
	
-- 参考 ----------------	

SELECT a.s_id,(SELECT s_score FROM score WHERE s_id=a.s_id AND c_id='01') AS 语文,
	(SELECT s_score FROM score WHERE s_id=a.s_id AND c_id='02') AS 数学,
	(SELECT s_score FROM score WHERE s_id=a.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

-- anan + 参考 ----------------
SELECT 
	c.`c_id`, 
	c.`c_name`, 
	MAX(sc.`s_score`) '最高分', 
	MIN(sc.`s_score`) '最低分', 
	AVG(sc.`s_score`) '平均分', 
	(SELECT COUNT(s_id) FROM Score WHERE s_score >= 60 AND c_id = c.`c_id`)/ COUNT(*) '及格率',
	(SELECT COUNT(s_id) FROM Score WHERE s_score >= 70 AND s_score < 80 AND c_id = c.`c_id`)/ COUNT(*) '中等率',
	(SELECT COUNT(s_id) FROM Score WHERE s_score >= 80 AND s_score < 90 AND c_id = c.`c_id`)/ COUNT(*) '优良率',
	(SELECT COUNT(s_id) FROM Score WHERE s_score >= 90 AND c_id = c.`c_id`)/ COUNT(*) '优秀率'
FROM 
	Course c,
	Score sc
WHERE c.`c_id` = sc.`c_id`
GROUP BY sc.`c_id` ;


-- 参考 ----------------
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_score>=60 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_score>=70 AND a.s_score<80 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_score>=80 AND a.s_score<90 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_score>=90 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 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;




    SELECT a.s_id,a.c_id,
        @i:=@i +1 AS i保留排名,
        @k:=(CASE WHEN @score=a.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 @score=a.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 @score=a.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、查询学生的总成绩并进行排名
-- anan + 参考----------------
SELECT st.*, (CASE WHEN SUM(sc.`s_score`) IS NULL THEN 0 ELSE SUM(sc.`s_score`) END) '总分' 
FROM Student st
LEFT JOIN Score sc
ON st.`s_id` = sc.`s_id`
GROUP BY sc.`s_id`
ORDER BY SUM(sc.`s_score`) DESC;

-- anan + 参考----------------
SELECT t1.*, (@i:=@i+1) '排名'
FROM
	(SELECT st.*, (CASE WHEN SUM(sc.`s_score`) IS NULL THEN 0 ELSE SUM(sc.`s_score`) END) '总分' 
	FROM Student st
	LEFT JOIN Score sc
	ON st.`s_id` = sc.`s_id`
	GROUP BY sc.`s_id`
	ORDER BY SUM(sc.`s_score`) DESC) t1,
	(SELECT @i:=0) AS init;



-- 参考 ----------------
SELECT a.s_id,
    @i:=@i+1 AS i,
    @k:=(CASE WHEN @score=a.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



-- anan ----------------
-- 参考 ----------------

/*
-- ROUND 函数用于把数值字段舍入为指定的小数位数。
*/

/*
数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。
在使用left join时，on和where条件的区别如下：
1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

*/

/*
count(*) 将返回表格中所有存在的行的总数包括值为 null 的行，
count(列名) 将返回表格中除去 null 以外的所有行的总数 (有默认值的列也会被计入)
按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)，所以我建议你，尽量使用count(*)。
*/

/*
为查询的数据排序列号的SQL语句
SELECT (@i:=@i+1),t.* FROM table_name t,(SELECT @i:=0) AS j
*/

 

安安csdn
关注
  • 0
    点赞
  • 踩
  • 14
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
MySQL练习题及答案-11页
06-01
MySQL练习题及答案
sql语句练习50题(Mysql版)
Coder-Wf
09-10 3716
表名和字段 –1.学生表 Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别 –2.课程表 Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号 –3.教师表 Teacher(t_id,t_name) –教师编号,教师姓名 –4.成绩表 Score(s_id,c_id,s_scor...
零基础学MySQl数据库，全网最细最简单的MySQl数据库学习(实时更新)
最新发布
m0_74301606的博客
04-21 1371
数据库中表的本质就是一堆文件在数据库的data目录下是保存所有的表的内容。安装我们的数据库管理系统就是对我们各个数据库进行管理，各个数据库里面又存放着很多表，就是上图的三层结构。当我们在SQLyog里面打开一个表可以看到相应的存储引擎。如下图所示。展示所有引擎存储引擎的特点介绍常用的三种引擎：（MyISAM）(InnoDB) (MEMORY)1.MyISAM不支持事务，也不支持外键，但其访问速度快，对事务完整性没有要求2.InnoDB存储引擎提供了具有提交，回滚和崩溃恢复能力的事务安全。
MySQL数据库习题
m0_59420288的博客
12-05 4678
大家好，分享一些MySQL练习题，都是自己整理的考试例题附有答案，可用作巩固知识点，也可以用来考前复习。 创建数据库： 创建teacher表： 创建class表： 创建course表 创建student表 创建score表 2.插入下图数据 有两种方式插入数据（演示两表）：例：插入class表数据 例：插入teacher表数据
Mysql经典例题练习与总结
qq_42265608的博客
06-14 3324
Mysql练习题
MySQL综合练习（50道）
weixin_45890771的博客
05-15 2493
MYSQL查询练习经典50道
50道MySQL经典练习题
12-14
这个是网上流传的50道SQL练习题，最近拿来练习，刚做完，这里把我做的答案给大家做个参考，如有错误，还请告知。 另外，做题的时候先不要看答案（答案不唯一，只要满足要求即可），要有自己的一个思考过程，这样做...
MySQL经典练习50题
08-08
MySQL经典题库50题，无答案。MySQL经典题库50题，无答案。
mysql 数据库练习50题
09-20
sql语句练习，支持mysql. 表包含：老师、学生、课程、成绩、年级、班级、老师教的班级表 多表数据查询的sql练习。
MySQL50道练习题.zip
04-13
MySQL50道练习题
MySQL经典练习题.sql
05-27
mysql经典练习题以及答案
MySQL经典50道练习题及全网最详细解析
m0_67401417的博客
08-02 1132
1.学表Student(SId,Sname,Sage,Ssex)SId学编号Sname学姓名Sage出年Ssex学性别2.课程表Course(CId,Cname,TId)CId课程编号Cname课程名称TId教师编号3.教师表Teacher(TId,Tname)TId教师编号Tname教师姓名4.成绩表SC(SId,CId,score)SId学编号CId课程编号score分数。...
MySQL基础篇 | 经典三十四道练习题
m0_61933976的博客
09-18 5662
关于MySQL的三十四道练习题，大佬们都会做吗
MySQL练习题全部
m0_52254303的博客
08-23 2563
sql分析
Mysql经典50题--第一弹
查数菇乐园
09-18 4935
在初学时期看到了大家都在发的Mysql经典50题，做过一遍之后的第二次回顾
[Mysql] 经典 50 题
licn188的博客
07-22 6878
Mysql 经典 50题
MySQL经典习题
mr2744279038的博客
05-29 1778
(select jno from spj,p,s where s.city='天津' and color='红' and s.sno=spj.sno and p.pno=spj.pno);select jname from spj,s,j where s.sno=spj.sno and s.city='上海' and j.jno=spj.jno;#36.查询所有学生报名选课课程的详细情况，要求包括已报名选修课程的学生，也包括未报名选修课程的学生情况，显示内容为学号、课程编号和课程名称。
MySQL学习：50道经典题目（含参考答案）(更新中）
weixin_42661635的博客
04-27 7841
首先根据course_id将成绩表分为两张表，筛选出" 1 "课程比" 2 "课程成绩高的学生，再与学生表关联。1. 查询" 1 "课程比" 2 "课程成绩高的学生的信息（学号、姓名、性别、出生日期）及课程分数。利用自连接，统计比当前分数高的分数有几个，排名就是几加1。使用case when语句，多个排序条件时用逗号分隔。利用课程数量，计算出所有课程数和每个学生的选课数量。先筛选与" 1 "号同学选课数量相同的同学，再剔除。10. 查询没有学全所有课程的同学的信息。选了" 1 "号同学没选的课的同学。
mysql经典50道练习题pdf
08-17
MySQL经典50道练习题pdf是一本经典的MySQL数据库练习题集合，对于想要提高MySQL数据库操作能力的人士来说是非常有价值的。 这本练习题集合涵盖了各种MySQL数据库操作方面的题目，包括数据库创建、表创建、数据插入...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

安安csdn

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值