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 NULL AUTO_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 NULL AUTO_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 BY C_NAME;
SELECT C_NAME,MAX(GRADE) '最高分' FROM SCORE GROUP BY C_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分的学生姓名

SELECT
    A.NAME,MIN(B.GRADE)
FROM
    STUDENT A
INNER JOIN SCORE B ON A.ID = B.STU_ID
GROUP BY A.NAME
HAVING
MIN(B.GRADE) > 80;

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

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

SELECT C_NAME,AVG(GRADE) AS avg FROM SCORE GROUP BY C_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 = '张三';

SELECT
    A.*,B.C_NAME,B.GRADE
FROM
    STUDENT A
LEFT JOIN SCORE B ON A.ID = B.STU_ID
WHERE
    B.C_NAME = '中文'
AND B.GRADE < (
    SELECT
        B.GRADE
    FROM
        STUDENT A
    LEFT JOIN SCORE B ON A.ID = B.STU_ID
    WHERE
        B.C_NAME = '中文'
    AND A. NAME = '张三'
);
分析:
1.先查出张三的中文成绩X,再查中文成绩大于X的.
2.MySQL数据库两个表外连接一般怎么选择主表呢?    这个主要看需求,你主要获取哪个表的数据,一般就以该表作为主表.另一个表的数据如果关联不上,获取列是以null表示的.
3.A和B关联的,那就使用inner join ,A全有,B中关联A的,那就使用LEFT JOIN ,A中关联B的,B全有,那就RIGHT JOIN