有计算机成绩张三,MySQL基础练习题

https://blog.csdn.net/yang_guang3/article/details/83113110

思路：

1、根据提问或者需求分析想要的结果是什么

Select XXXXX

2、确定想要得到XXXXX的话需要涉及到哪些表

Select XXX from YYYY

3、分析是否需要条件 where > ,< ,= ,<> , >=, <=,in,between，and，or

Select XXX from YYY where ZZZZ

4、是否需要函数

时间函数，数学函数

5、分组

看到(“每个??”)一般情况下都需要group by.

6、如果想要的结果不能一步到位

需要多个步骤的，那么此时我们使用的就是子查询，子查询我们可以按照单个步骤进行查询，然后把多个步骤组装起来

7、表连接，关联查询，联合查询。

明显特征：结果集中的列涉及到多张表。表连接去除笛卡尔积中的错误数据

1.需求2.表格3.条件4.函数5.分组6.步骤7.关联

练习题:

1.创建student和score表

2.为student表和score表增加记录

DROP TABLE IF EXISTS`student`;CREATE TABLE`student` (

`id`int(10) NOT NULLAUTO_INCREMENT,

`name`char(20) NOT NULL,

`sex`char(20) DEFAULT NULL,

`birth`year(4) DEFAULT NULL,

`department`char(10) DEFAULT NULL,

`address`char(10) NOT NULL,PRIMARY KEY(`id`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS`score`;CREATE TABLE`score` (

`id`int(10) NOT NULLAUTO_INCREMENT,

`stu_id`int(10) NOT NULL,

`c_name`char(20) DEFAULT NULL,

`grade`int(10) DEFAULT NULL,PRIMARY KEY(`id`),KEY`score_ibfk_1` (`stu_id`),CONSTRAINT `score_ibfk_1` FOREIGN KEY (`stu_id`) REFERENCES `student` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

insert into `student`(`id`,`name`,`sex`,`birth`,`department`,`address`) values (1,’张老大’,’男’,1985,’计算机系’,’北京市海淀区’);insert into `student`(`id`,`name`,`sex`,`birth`,`department`,`address`) values (2,’张老二’,’男’,1986,’中文系’,’北京市昌平区’);insert into `student`(`id`,`name`,`sex`,`birth`,`department`,`address`) values (3,’张三’,’女’,1990,’中文系’,’湖南省永州市’);insert into `student`(`id`,`name`,`sex`,`birth`,`department`,`address`) values (4,’李四’,’男’,1990,’英语系’,’辽宁省阜新市’);insert into `student`(`id`,`name`,`sex`,`birth`,`department`,`address`) values (5,’王五’,’女’,1991,’英语系’,’福建省厦门市’);insert into `student`(`id`,`name`,`sex`,`birth`,`department`,`address`) values (6,’王六’,’男’,1988,’计算机系’,’湖南省衡阳市’);

insert into `score`(`id`,`stu_id`,`c_name`,`grade`) values (1,1,’计算机’,98);insert into `score`(`id`,`stu_id`,`c_name`,`grade`) values (2,1,’英语’,80);insert into `score`(`id`,`stu_id`,`c_name`,`grade`) values (3,2,’计算机’,65);insert into `score`(`id`,`stu_id`,`c_name`,`grade`) values (4,2,’中文’,88);insert into `score`(`id`,`stu_id`,`c_name`,`grade`) values (5,3,’中文’,95);insert into `score`(`id`,`stu_id`,`c_name`,`grade`) values (6,3,’计算机’,70);insert into `score`(`id`,`stu_id`,`c_name`,`grade`) values (7,4,’计算机’,70);insert into `score`(`id`,`stu_id`,`c_name`,`grade`) values (8,4,’英语’,92);insert into `score`(`id`,`stu_id`,`c_name`,`grade`) values (9,5,’英语’,94);insert into `score`(`id`,`stu_id`,`c_name`,`grade`) values (10,5,’计算机’,90);insert into `score`(`id`,`stu_id`,`c_name`,`grade`) values (11,6,’计算机’,90);insert into `score`(`id`,`stu_id`,`c_name`,`grade`) values (12,6,’英语’,85);

问题：

1.查询student表的所有记录

SELECT * FROM STUDENT;

2.查询student表的第2条到4条记录

SELECT * FROM STUDENT LIMIT 1,3;

3.从student表查询所有学生的学号(id)、姓名(name)和院系(department)的信息

SELECT ID,NAME,DEPARTMENT FROM STUDENT;

4.从student表中查询计算机系和英语系的学生的信息

SELCET * FROM STUDENT WHERE DEPARTMENT IN (‘计算机系’,’英语系’);

5.从student表中查询年龄18~35岁的学生信息

SELECT * FROM STUDENT WHERE YEAR(NOW())-BIRTH BETWEEN 18 AND 35;SELECT * FROM STUDENT WHERE DATE_FORMAT(NOW(),’%Y’)-BIRTH BETWEEN 18 AND 35;

分析:日期转化字符串函数DATE_FORMAT(date,format) 或 YEAR(date)

6.查询每个院系有多少人

SELECT COUNT(*),DEPARTMENT FROM STUDENT GROUP BY DEPARTMENT;

分析:

1.涉及表STUDENT

2.每个院系—>按照院系分组,GROUP BY 配合聚合函数COUNT()

7.查询每个科目的最高分

SELECT C_NAME,MAX(GRADE) FROM SCORE GROUP BYC_NAME;SELECT C_NAME,MAX(GRADE) ‘最高分’ FROM SCORE GROUP BYC_NAME;SELECT C_NAME,MAX(GRADE) AS ‘最高分’ FROM SCORE GROUP BY C_NAME;

分析:

1.涉及表SCORE

2.每个科目—>按照科目分组,GROUP BY 配合聚合函数MAX()

8.查询李四的考试科目(c_name)和考试成绩(grade)

SELECT B.C_NAME,B.GRADE FROM STUDENT A,SCORE B WHERE A.ID = B.STU_ID AND A.NAME = ‘李四’;SELECT B.C_NAME,B.GRADE FROM STUDENT A INNER JOIN SCORE B ON A.ID = B.STU_ID WHERE A.NAME = ‘李四’;SELECT B.C_NAME,B.GRADE FROM STUDENT A INNER JOIN SCORE B ON A.ID = B.STU_ID AND A.NAME = ‘李四’;

分析:

1.涉及表STUDENT,SCORE,等值连接(内连接)

2.注意,内连接的on where和on and是一致的，都是将临时表生成好后，再对临时表进行过滤的条件

9.所有学生的信息和考试信息

SELECT A.*,B.* FROM STUDENT A,SCORE B WHERE A.ID =B.STU_ID;SELECT A.*,B.* FROM STUDENT A INNER JOIN SCORE B ON A.ID = B.STU_ID;

10.计算每个学生的总成绩

SELECT A.NAME,SUM(B.GRADE) FROM STUDENT A,SCORE B WHERE A.ID = B.STU_ID GROUP BY A.NAME;

分析:

1.涉及表STUDENT,SCORE,等值连接(内连接)

2.每个学生—>分组查询,GROUP BY 配合聚合函数SUM()

11.计算每个考试科目的平均成绩

SELECT C_NAME,AVG(GRADE) FROM SCORE GROUP BY C_NAME;

分析:

1.涉及表SCORE

2.每个科目—> 分组查询,GROUP BY 配合聚合函数AVG()

12.查询计算机成绩低于95的学生信息

SELECT A.* FROM STUDENT A,SCORE B WHERE A.ID = B.STU_ID AND B.C_NAME = ‘计算机’ AND B.GRADE < 95;SELECT * FROM STUDENT WHERE ID IN (SELECT STU_ID FROM SCORE WHERE C_NAME = ‘计算机’ AND GRADE < 95);

分析:涉及表STUDENT,SCORE 等值连接或者子查询

13.查询同时参加计算机和英语考试的学生的信息

SELECT * FROM STUDENT WHERE ID IN (SELECT STU_ID FROM SCORE WHERE C_NAME = ‘计算机’ AND STU_ID IN (SELECT STU_ID FROM SCORE WHERE C_NAME = ‘英语’));

分析:先查出同时参加英语和计算机考试的stu_id

14.将计算机考试成绩按从高到低进行排序

SELECT C_NAME,GRADE FROM SCORE WHERE C_NAME = ‘计算机’ ORDER BY GRADE DESC;

分析:排序 ORDER BY ,升序 ASC ,降序 DESC

15.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩

SELECT A.NAME,A.DEPARTMENT,B.C_NAME,B.GRADE FROM STUDENT A,SCORE B WHERE A.ID = B.STU_ID AND A.NAME LIKE ‘王%’ OR ‘张%’;

分析:模糊查询 LIKE

16.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩

SELECT A.NAME,YEAR(NOW())-A.BIRTH ‘年龄’,A.DEPARTMENT,B.C_NAME,B.GRADE FROM STUDENT A INNER JOIN SCORE B ON A.ID = B.STU_ID AND A.ADDRESS LIKE ‘%湖南%’;

分析:

1.涉及表STUDENT,SCORE

2.模糊查询LIKE

3.年龄—>日期转化字符串函数YEAR(NOW())

17.查询出每门课程都大于80分的学生姓名

SELECTA.NAME,MIN(B.GRADE)FROMSTUDENT AINNER JOIN SCORE B ON A.ID =B.STU_IDGROUP BYA.NAMEHAVING

MIN(B.GRADE) > 80;

分析:即查询每个学生每门课程成绩都大于80的

18.查询平均分最高的科目名字

SELECT C_NAME,AVG(GRADE) AS avg FROM SCORE GROUP BYC_NAME;SELECT T.C_NAME,MAX(T.avg) FROM ((SELECT C_NAME,AVG(GRADE) AS avg FROM SCORE GROUP BY C_NAME) AS T);

分析:

1.先查询出所有科目的平均分,并将查询结果集起别名T

2.再从T中查询出平均分最高的科目名字 MAX()

19.查询语文成绩比张三低的学生的信息

SELECT B.GRADE FROM STUDENT A INNER JOIN SCORE B ON A.ID = B.STU_ID WHERE B.C_NAME = ‘中文’ AND A. NAME = ‘张三’;SELECTA.*,B.C_NAME,B.GRADEFROMSTUDENT ALEFT JOIN SCORE B ON A.ID =B.STU_IDWHEREB.C_NAME= ‘中文’

AND B.GRADE

AND A. NAME = ‘张三’);

分析:1.先查出张三的中文成绩X,再查中文成绩大于X的.

2.MySQL数据库两个表外连接一般怎么选择主表呢? 这个主要看需求,你主要获取哪个表的数据,一般就以该表作为主表.另一个表的数据如果关联不上,获取列是以null表示的.

3.A和B关联的,那就使用inner join ,A全有,B中关联A的,那就使用LEFT JOIN ,A中关联B的,B全有,那就RIGHT JOIN