mysql练习题整理

mysql练习题整理

CREATE TABLE Student(
SId VARCHAR(10),-- 学生编号
Sname VARCHAR(10),-- 学生姓名
Sage DATETIME,-- 学生年龄
Ssex VARCHAR(10)-- 学生性别
);
INSERT INTO Student VALUES('01' , '赵雷' , '1990-01-01' , '男');
INSERT INTO Student VALUES('02' , '钱电' , '1990-12-21' , '男');
INSERT INTO Student VALUES('03' , '孙风' , '1990-12-20' , '男');
INSERT INTO Student VALUES('04' , '李云' , '1990-12-06' , '男');
INSERT INTO Student VALUES('05' , '周梅' , '1991-12-01' , '女');
INSERT INTO Student VALUES('06' , '吴兰' , '1992-01-01' , '女');
INSERT INTO Student VALUES('07' , '郑竹' , '1989-01-01' , '女');
INSERT INTO Student VALUES('09' , '张三' , '2017-12-20' , '女');
INSERT INTO Student VALUES('10' , '李四' , '2017-12-25' , '女');
INSERT INTO Student VALUES('11' , '李四' , '2012-06-06' , '女');
INSERT INTO Student VALUES('12' , '赵六' , '2013-06-13' , '女');
INSERT INTO Student VALUES('13' , '孙七' , '2014-06-01' , '女');
-- 科目表
CREATE TABLE Course(
CId VARCHAR(10),-- 课程编号
Cname NVARCHAR(10),-- 课程名称
TId VARCHAR(10)-- 教师标号
);
INSERT INTO Course VALUES('01' , '语文' , '02');
INSERT INTO Course VALUES('02' , '数学' , '01');
INSERT INTO Course VALUES('03' , '英语' , '03');
-- 教师表
CREATE TABLE Teacher(
TId VARCHAR(10),-- 教师编号
Tname VARCHAR(10)-- 教师姓名
);
INSERT INTO Teacher VALUES('01' , '张三');
INSERT INTO Teacher VALUES('02' , '李四');
INSERT INTO Teacher VALUES('03' , '王五');
-- 成绩表
CREATE TABLE SC(
SId VARCHAR(10),-- 学生编号
CId VARCHAR(10),-- 课程编号
score DECIMAL(18,1)-- 分数
);
INSERT INTO SC VALUES('01' , '01' , 80);
INSERT INTO SC VALUES('01' , '02' , 90);
INSERT INTO SC VALUES('01' , '03' , 99);
INSERT INTO SC VALUES('02' , '01' , 70);
INSERT INTO SC VALUES('02' , '02' , 60);
INSERT INTO SC VALUES('02' , '03' , 80);
INSERT INTO SC VALUES('03' , '01' , 80);
INSERT INTO SC VALUES('03' , '02' , 80);
INSERT INTO SC VALUES('03' , '03' , 80);
INSERT INTO SC VALUES('04' , '01' , 50);
INSERT INTO SC VALUES('04' , '02' , 30);
INSERT INTO SC VALUES('04' , '03' , 20);
INSERT INTO SC VALUES('05' , '01' , 76);
INSERT INTO SC VALUES('05' , '02' , 87);
INSERT INTO SC VALUES('06' , '01' , 31);
INSERT INTO SC VALUES('06' , '03' , 34);
INSERT INTO SC VALUES('07' , '02' , 89);
INSERT INTO SC VALUES('07' , '03' , 98);

子查询方面:

exists子查询

1.查询有成绩的学生信息

SELECT *
FROM student
WHERE EXISTS(SELECT * FROM sc WHERE student.SId=sc.SId)

注：条件不确定情况用exists。

exists：是否存在的意思，exists子查询是用来判断条件是否满足（跨表），exists接在where之后，返回结果只有0和1。

带in关键字的子查询

2.查询没学过"张三"老师讲授的任一门课程的学生姓名

SELECT *
FROM student 
WHERE student.SId
NOT IN (SELECT student.SId FROM student LEFT JOIN sc ON student.SId=sc.SId
        WHERE EXISTS (SELECT *FROM teacher ,course WHERE teacher.Tname='张三'AND teacher.TId=course.TId AND course.CId=sc.CId));

带in关键字的子查询：使用in关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列中的值将供外层查询语句进行比较操作

in与exists的区别：

exists 与 in 最大的区别在于 in引导的子句只能返回一个字段；

exists : 强调的是是否返回结果集，不要求知道返回什么；

排序查询方面:

3.按各科成绩进行排序，并显示排名，Score 重复时合并名次

SELECT sc.CId , 
CASE WHEN @fontscore=score THEN @curRank WHEN @fontscore:=score THEN @curRank:=@curRank+1 END AS rank,sc.score 
FROM (SELECT @curRank:=0 ,@fontage:=NULL) AS t ,sc 
ORDER BY sc.score DESC

4.查询学生的总成绩，并进行排名，总分重复时不保留名次空缺（当前成绩和之前成绩一样时，这次的排名就会和上次一样）

SELECT t1.*,
CASE WHEN @fontscore=t1.sumscore THEN @currank WHEN @fontscore:= t1.sumscore THEN @currank:=@currank+1 END AS rank
FROM (SELECT sc.SId, SUM(score) AS sumscore FROM sc GROUP BY sc.SId ORDER BY SUM(score) DESC) AS t1,(SELECT @currank:=0,@fontscore:=NULL) AS t;

5.查询学生的总成绩，并进行排名，总分重复时保留名次空缺（当前成绩和之前成绩一样时，这次的排名也会加一）

SELECT t1.*,@currank:= @currank+1 AS rank
FROM (SELECT sc.SId, SUM(score) FROM sc GROUP BY sc.SId ORDER BY SUM(score) DESC) AS t1,(SELECT @currank:=0) AS t;

数据库中的@

数据库中＠代表是局部变量声明。

:=与=的区别

:=是赋值，=在set和update中为赋值，剩下的都为判断。

case when的用法

case when:两种用法

• 简单函数：case [col_name] when [valuse] then [result]…else [default] end

  ​ 作用：枚举这个字段所有可能的值

• 搜索函数：case when [expr] then [result1]…else [default] end

​ 作用：搜索函数可以写判断，并且搜算函数只会返回第一个符合条件的值，其他case值会被忽略。

inner join 与left join,right join之间的区别

6.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT student.*,t1.score
FROM student INNER JOIN (SELECT sc.SId,sc.score, CASE WHEN @fontage=sc.score THEN @rank WHEN @fontage:=sc.score THEN @rank:=@rank+1 END AS rank FROM course ,teacher ,sc,(SELECT @fontage:=NULL,@rank:=0) AS t WHERE course.CId=sc.CIdand course.TId=teacher.TId AND teacher.Tname='张三'ORDER BY sc.score DESC) AS t1 
ON student.SId=t1.SId
WHERE t1.ran`course`k=1

• left join（左连接查询）：返回包括左表中的所有数据和右表中联结字段相等的记录。
• right join（右链接查询）：返回包括右表中的所有数据和左表中联结字段相等的记录。
• inner join（等值链接）：只返回两个表中连接字段相同的行。

7.查询没有学全所有课程的同学的信息

SELECT DISTINCT student.*
FROM (SELECT student.SId,course.CId FROM student,course ) AS t1 
LEFT JOIN (SELECT sc.SId,sc.CId FROM sc)AS t2  
ON t1.SId=t2.SId AND t1.CId=t2.CId ,student   
WHERE t2.SId IS NULL AND t1.SId=student.SId;

8.查询没学过"张三"老师讲授的任一门课程的学生姓名

SELECT *
FROM student 
WHERE student.SId
NOT IN (SELECT student.SId FROM student LEFT JOIN sc ON student.SId=sc.SId WHERE EXISTS (SELECT *FROM teacher ,course WHERE teacher.Tname='张三'AND teacher.TId=course.TId AND course.CId=sc.CId));

9.查询每门功课最好的前两名

SELECT *
FROM sc AS t1
WHERE (SELECT COUNT(1) FROM sc AS t2 WHERE t1.CId=t2.CId AND t2.score >t1.score)<2;

时间方面:

10.查询本月过生日的同学

SELECT *
FROM student
WHERE MONTH (student.`Sage`)=MONTH (NOW());

11.查询本周过生日的同学

SELECT *
FROM student
WHERE WEEK(NOW())=WEEK(student.`Sage`)

12.查询下个月过生日的同学

SELECT *FROM student WHERE EXTRACT(YEAR_MONTH FROM student.Sage)=EXTRACT(YEAR_MONTH FROM DATE_ADD(CURDATE(),INTERVAL 1 MONTH))

13.根据年份查询学生的年龄

SELECT student.SId AS 学生编号,student.Sname AS 学生姓名,YEAR(NOW())-YEAR(student.`Sage`) AS 学生年龄
FROM student;

14.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

-- 方法一
SELECT student.SId AS 学生编号,student.Sname AS 学生姓名,TIMESTAMPDIFF(YEAR,student.Sage,CURDATE()) AS 学生年龄
FROM student
-- 方法二
SELECT student.`SId`,student.`Sage`,(DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(student.`Sage`,'%Y'))-
(CASE WHEN DATE_FORMAT(NOW(),'%m%d')< DATE_FORMAT(student.`Sage`,'%m%d') THEN 1 ELSE 0 END) AS age
FROM student;

15.查询下个月过生日的学生

SELECT *
FROM student 
WHERE EXTRACT(MONTH FROM student.Sage)=EXTRACT(MONTH FROM DATE_ADD(CURDATE(),INTERVAL 1 MONTH))

16.查询下周过生日的同学

select *
from student
where WEEK(NOW())+1=WEEK(student.`Sage`)

date_format()函数

• 定义和用法：

​ DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。

• 语法：DATE_FORMAT(date,format)
  date 参数是合法的日期。format 规定日期/时间的输出格式。

• 例如：

​ 想要得到一个日期里的年：DATE_FORMAT(student.Sage,’%Y’)

​ 想得到一个日期里的月和日：DATE_FORMAT(NOW(),’%m%d’）

三、格式 描述
%a 缩写星期名，%b 缩写月名，%c 月，数值，%D 带有英文前缀的月中的天，%d 月的天，数值(00-31)，%e 月的天，数值(0-31)，%f 微秒，%H 小时 (00-23)，%h 小时 (01-12)，%I 小时 (01-12)，%i 分钟，数值(00-59)，%j 年的天 (001-366)，%k 小时 (0-23)，%l 小时 (1-12)，%M 月名，%m 月，数值(00-12)，%p AM 或 PM，%r 时间，12-小时（hh:mm:ss AM 或 PM），%S 秒(00-59)，%s 秒(00-59)，%T 时间, 24-小时 (hh:mm:ss)，%U 周 (00-53) 星期日是一周的第一天，%u 周 (00-53) 星期一是一周的第一天，%V 周 (01-53) 星期日是一周的第一天，与 %X 使用，%v 周 (01-53) 星期一是一周的第一天，与 %x 使用，%W 星期名，%w 周的天 （0=星期日, 6=星期六），%X 年，其中的星期日是周的第一天，4 位，与 %V 使用，%x 年，其中的星期一是周的第一天，4 位，与 %v 使用，%Y 年，4 位，%y 年，2 位

timestampdiff()函数

• 语法：timestampdiff(interval,datetime_expr1,datetime_expr2)

• 说明：返回日期或日期时间表达式datetime_expr1和datetime_expr2之间的整数差。其结果的单位由interval参数给出，其参数值必须是FRAC_SECOND表示间隔是毫秒，SECOND秒，MINUTE分钟，HOUR小时，DAY天，WEEK星期，MONTH月，QUARTER季度，YEAR年，其中一个。

curdate() 和 current_date()函数

• CURDATE() 和 CURRENT_DATE() 函数的作用相同，将当前日期按照“YYYY-MM-DD”或“YYYYMMDD”格式的值返回，具体格式根据函数用在字符串或数字语境中而定。

extract()函数

• EXTRACT() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。

• 语法格式：(unit from date)

• Unit 值：
  MICROSECOND,SECOND,MINUTE,HOUR,DAY,WEEK,MONTH,QUARTER,YEAR,SECOND_ MICROSECOND,MINUTE_ MICROSECOND,MINUTE_ SECOND,HOUR_ MICROSECOND,HOUR_ SECOND,HOUR_ MINUTE,DAY_MICROSECOND,DAY_ SECOND,DAY_ MINUTE,DAY_ HOUR,YEAR_ MONTH

date_add()函数

• date_add()函数语法:

DATE_ADD(date,INTERVAL expr unit)

• 备注:date_add()和adddate()是同义词.

• 参数说明:

date:起始日期或者起始时间

expr:指定的是一个间隔值,在起始时间中增加或者减少,注意:expr是一个字符串.对于负值间隔,可以以"-"开头

unit:表示的是一个单位,比如,加上的是1天还是一个小时.

week(date,mode)和yearweek(date,mode)函数

• week(date,mode):
  - date：要获得周数的日期
  - mode：是一个可选参数，用于确定周数计算的逻辑。它允许你指定本周是从星期一还是星期日开始的，他们的返回周数应该在0-52或者0-53之间。如果忽略mode参数，默认情况下week函数将使用default_week_format系统变量的值（默认值为0）

• yearweek(date,mode):基本与week使用方法相同，但是返回值不同，此函数返回的是年份和周数，week返回的是周数。

concat( , )函数

将两个字符串连接为一个字符串，例如：concat(name,fenshu)—>namefenshu