MySQL练习（单表查询和多表查询）

单表查询

一.创建素材

DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cs_id` int(11) NOT NULL COMMENT '课程编号',
  `cs_name` varchar(50) NOT NULL COMMENT '课程名称',
  `cs_credit` tinyint(255) unsigned DEFAULT NULL COMMENT '课程学分',
  `cs_type` char(12) DEFAULT NULL COMMENT '课程类别',
  `cs_depart` char(6) DEFAULT NULL COMMENT '院系名称',
  PRIMARY KEY (`cs_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `course` */
insert  into `course`(`cs_id`,`cs_name`,`cs_credit`,`cs_type`,`cs_depart`) 
values 
(5200313,'数据库原理及应用',4,'核心专业','信工'),
(5203314,'计算机导论',4,'通识教育','信工'),
(5219314,'数据结构',5,'专业核心','信工'),
(5223013,'大学物理',4,'专业基础','信工'),
(5227614,'毕业实习',4,'集中实践','信工'),
(5230912,'云计算',2,'共同选修','信工'),
(5236212,'机器学习',2,'共同选修','信工'),
(5237514,'c语言',4,'专业基础','信工'),
(5245112,'区块链',2,'任意选修','信工'),
(7200422,'知识产权法',2,'任意选修','文法'),
(20201833,'概率论',3,'专业基础','基础'),
(20202336,'高等数学',6,'专业基础','基础'),
(29299131,'劳动教育',1,'集中实践','学务');
/*Table structure for table `student` */

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `stu_id` bigint(11) unsigned NOT NULL COMMENT '学号',
  `stu_name` char(12) NOT NULL COMMENT '姓名',
  `stu_sex` enum('男','女') DEFAULT NULL COMMENT '性别',
  `stu_age` tinyint(255) unsigned DEFAULT NULL COMMENT '年龄',
  `stu_major` char(9) DEFAULT NULL COMMENT '专业',
  `stu_college` char(12) DEFAULT NULL COMMENT '学院',
  PRIMARY KEY (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `student` */
insert  into `student`(`stu_id`,`stu_name`,`stu_sex`,`stu_age`,`stu_major`,`stu_college`) 
values 
(201804550101,'郭奎','男',22,'计科','信工学院'),
(201804550102,'吕宇航','男',18,'计科','信工学院'),
(201804550103,'张豪辉','女',19,'计科','信工学院'),
(201804550107,'丁志杰','男',17,'金融学','金贸学院'),
(201804550109,'范伟','男',19,'金融学','金贸学院'),
(201804550116,'张依婷','女',17,'大数据','信工学院'),
(201804550120,'张维','男',19,'计科','信工学院'),
(201804550121,'朱柳阳','女',20,'计科','信工学院'),
(201804550144,'谭兵炎','男',20,'大数据','信工学院'),
(201804550153,'杨志强','男',17,'大数据','信工学院');

二、练习题目
1.查询全部课程的信息。

select * from course;

2.查询信工学院开设的课程名、课程号及学分。

select cs_name,cs_id,cs_credit from course where cs_depart='信工';

3.查询学分超过3学分的课程代码、课程名和开课单位。

select cs_id,cs_name,cs_depart from course where cs_credit >3;

4.查询计科专业和大数据专业的学生信息。

select * from student where stu_major='计科' or stu_major='大数据';

5.查询不是信工学院的学生姓名和学号。

select stu_name,stu_id from student where stu_major!='信工';

6.查询年龄是17,18,19的学生姓名和专业。

select * from student where stu_age in (17,18,19);

7.查询学分在2到4之间课程的信息。

select * from course where cs_credit between 2 and 4 ;

8.查询课程名称中带“数据”的课程名、课程号及开课单位。

select cs_name,cs_id,cs_depart from course where cs_name like '%数据%';

9.查询信工学院的的专业有哪些。

select  distinct stu_major from student where stu_college='信工学院';

10.查询年龄为空的学生信息。

select * from student where stu_age is null;

11.查询不是信工学院开设的集中实践课的开课单位和课程名称。

select cs_depart,cs_name from course where cs_type='集中实践' and cs_depart!='信工';

12.查询信工学院开设的课程的类型有哪些。

select cs_type from course where cs_depart='信工';

13.查询学生所在的专业个数。

select count( distinct stu_major) stu_major  from student;

14.查询信工学院开设的课程的平均学分。

select avg(cs_credit) from course where cs_depart='信工';

15.查询学生的信息，查询结果按姓名升序排序。

select * from student order by stu_name asc;

16.查询 每个专业的学生的最大年龄、最小年龄和平均年龄，查询结果按平均年龄降序排列。

select stu_major, max(stu_age) 最大年龄,min(stu_age) 最小年龄,avg(stu_age) 平均年龄 from studdent group by stu_major order by avg(stu_age) desc;

17.查询每个开课单位开设的课程门数的，查询结果按课程门数升序排列。

select cs_depart ,count(*) 开设门数 from course group by cs_depart order by count(*) asc;

18.查询单位开课门数少于2门的开课单位和课程名称。

select cs_depart,count(*) 开设门数 from course  group by cs_depart having count(*)<2;

多表查询

1.创建素材
因为和单表查询中的表有表名冲突所以我们切换一个数据库

use db1;
#创建第一张表student
CREATE  TABLE  student (
id  INT(10)  NOT NULL  UNIQUE  PRIMARY KEY  ,
name  VARCHAR(20)  NOT NULL ,
sex  VARCHAR(4)  ,
birth  YEAR,
department  VARCHAR(20) ,
address  VARCHAR(50) 
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
#创建第二张表
CREATE  TABLE  score (
  id  INT(10)  NOT NULL  UNIQUE  PRIMARY KEY  AUTO_INCREMENT ,
  stu_id  INT(10)  NOT NULL , 
  c_name  VARCHAR(20) ,
  grade  INT(10)
  )ENGINE=InnoDB DEFAULT CHARSET=utf8;

#向第一张表student插入数据
INSERT INTO student VALUES( 901,'张老大', '男',1985,'计算机系', '北京市海淀区');
INSERT INTO student VALUES( 902,'张老二', '男',1986,'中文系', '北京市昌平区');
INSERT INTO student VALUES( 903,'张三', '女',1990,'中文系', '湖南省永州市');
INSERT INTO student VALUES( 904,'李四', '男',1990,'英语系', '辽宁省阜新市');
INSERT INTO student VALUES( 905,'王五', '女',1991,'英语系', '福建省厦门市');
INSERT INTO student VALUES( 906,'王六', '男',1988,'计算机系', '湖南省衡阳市');

#向第二张表score插入数据
INSERT INTO score VALUES(NULL,901, '计算机',98);
INSERT INTO score VALUES(NULL,901, '英语', 80);
INSERT INTO score VALUES(NULL,902, '计算机',65);
INSERT INTO score VALUES(NULL,902, '中文',88);
INSERT INTO score VALUES(NULL,903, '中文',95);
INSERT INTO score VALUES(NULL,904, '计算机',70);
INSERT INTO score VALUES(NULL,904, '英语',92);
INSERT INTO score VALUES(NULL,905, '英语',94);
INSERT INTO score VALUES(NULL,906, '计算机',90);
INSERT INTO score VALUES(NULL,906, '英语',85);

2、练习题目
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表中查询计算机系和英语系的学生的信息

select * from student where department='计算机系' or department='英语系';

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

select * from student where (year( now() ) - birth) between 18 and 22;

6.从student表中查询每个院系有多少人

select department,count(*) 人数 from student group  by department asc;

7.从score表中查询每个科目的最高分

select c_name 科目,max(grade) 最高成绩  from score group by c_name;

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

select c_name,grade from score left join student on score.stu_id=student.id where name='李四';

9.用连接的方式查询所有学生的信息和考试信息

select * from score left join student on score.stu_id=student.id ;

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

select student.name 姓名 ,sum(grade) 总成绩 from score left join student on score.stu_id=student.id group by name; 

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

select c_name 科目,avg(grade) as 平均成绩  from score group by c_name;

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

select * from score left join student on score.stu_id=student.id where grade<95 and c_name='计算机';

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

select * from student where id =any (select stu_id from score 
     where stu_id in(
    select stu_id from
    score where c_name='计算机')
    and c_name='英语');

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

select * from score  where c_name='计算机' order by  grade desc;

15.从student表和score表中查询出学生的学号，然后合并查询结果

#UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
#请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
select id from student union select stu_id from score;

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

select name,department,score.c_name,score.grade from student 
left join score on score.stu_id=student.id;

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

select name,(year(now()-birth)) as 年龄,department,score.c_name,score.grade from student 
left join score on score.stu_id=student.id 
where address like '湖南%';
或者
select name,( year( now() - birth) ) as 年龄,department,score.c_name,score.grade from student 
left join score on score.stu_id=student.id 
where address regexp '^湖南.*';