MYSQL数据库习题练习

基础习题练习

创建表

-- 如果表 student存在就删除
drop TABLE if EXISTS student;

-- 创建表 student
CREATE TABLE student (
    id INT(10) PRIMARY key,
    -- 学生姓名
    name VARCHAR (10),
    -- 学生年龄
    age INT (10) NOT NULL,
    -- 学生性别
    gander varchar(2)
);

-- 课程表
drop TABLE if EXISTS course;

CREATE TABLE course (
    id INT (10) PRIMARY key,
    -- 课程名
    name VARCHAR (10) ,
    -- 课程 id
    t_id INT (10)
);

-- 教师表
drop TABLE if EXISTS teacher;
CREATE TABLE teacher(
    id INT (10) PRIMARY key,
    -- 教师名称
    name VARCHAR (10)
);


drop TABLE if EXISTS scores;

-- 成绩表
CREATE TABLE scores(
    -- 学生 id
    s_id INT ,
    -- 分数
    score INT (10),
    -- 课程号
    c_id INT (10) ,
    PRIMARY key(s_id,c_id)
);

数据插入

插入数据

insert into student (id,name,age,gander)VALUES(1,'白杰',19,'男');
insert into student (id,name,age,gander)VALUES(2,'连宇栋',19,'男');
insert into student (id,name,age,gander)VALUES(3,'邸志伟',24,'男');
insert into student (id,name,age,gander)VALUES(4,'李兴',11,'男');
insert into student (id,name,age,gander)VALUES(5,'张琪',18,'男');
insert into student (id,name,age,gander)VALUES(6,'武三水',18,'女');
insert into student (id,name,age,gander)VALUES(7,'张志伟',16,'男');
insert into student (id,name,age,gander)VALUES(8,'康永亮',23,'男');
insert into student (id,name,age,gander)VALUES(9,'杨涛瑞',22,'女');
insert into student (id,name,age,gander)VALUES(10,'王杰',21,'男');
insert into course (id,name,t_id)VALUES(1,'数学',1);
insert into course (id,name,t_id)VALUES(2,'语文',2);
insert into course (id,name,t_id)VALUES(3,'c++',3);
insert into course (id,name,t_id)VALUES(4,'java',4);
insert into course (id,name)VALUES(5,'php');
insert into teacher (id,name)VALUES(1,'张楠');
insert into teacher (id,name)VALUES(2,'老孙');
insert into teacher (id,name)VALUES(3,'薇薇姐');
insert into teacher (id,name)VALUES(4,'磊磊哥');
insert into teacher (id,name)VALUES(5,'大微姐');
insert into scores (s_id,score,c_id)VALUES(1,80,1);
insert into scores (s_id,score,c_id)VALUES(1,56,2);
insert into scores (s_id,score,c_id)VALUES(1,95,3);
insert into scores (s_id,score,c_id)VALUES(1,30,4);
insert into scores (s_id,score,c_id)VALUES(1,76,5);
insert into scores (s_id,score,c_id)VALUES(2,35,1);
insert into scores (s_id,score,c_id)VALUES(2,86,2);
insert into scores (s_id,score,c_id)VALUES(2,45,3);
insert into scores (s_id,score,c_id)VALUES(2,94,4);
insert into scores (s_id,score,c_id)VALUES(2,79,5);
insert into scores (s_id,score,c_id)VALUES(3,65,2);
insert into scores (s_id,score,c_id)VALUES(3,85,3);
insert into scores (s_id,score,c_id)VALUES(3,37,4);
insert into scores (s_id,score,c_id)VALUES(3,79,5);
insert into scores (s_id,score,c_id)VALUES(4,66,1);
insert into scores (s_id,score,c_id)VALUES(4,39,2);
insert into scores (s_id,score,c_id)VALUES(4,85,3);
insert into scores (s_id,score,c_id)VALUES(5,66,2);
insert into scores (s_id,score,c_id)VALUES(5,89,3);
insert into scores (s_id,score,c_id)VALUES(5,74,4);
insert into scores (s_id,score,c_id)VALUES(6,80,1);
insert into scores (s_id,score,c_id)VALUES(6,56,2);
insert into scores (s_id,score,c_id)VALUES(6,95,3);
insert into scores (s_id,score,c_id)VALUES(6,30,4);
insert into scores (s_id,score,c_id)VALUES(6,76,5);
insert into scores (s_id,score,c_id)VALUES(7,35,1);
insert into scores (s_id,score,c_id)VALUES(7,86,2);
insert into scores (s_id,score,c_id)VALUES(7,45,3);
insert into scores (s_id,score,c_id)VALUES(7,94,4);
insert into scores (s_id,score,c_id)VALUES(7,79,5);
insert into scores (s_id,score,c_id)VALUES(8,65,2);
insert into scores (s_id,score,c_id)VALUES(8,85,3);
insert into scores (s_id,score,c_id)VALUES(8,37,4);
insert into scores (s_id,score,c_id)VALUES(8,79,5);
insert into scores (s_id,score,c_id)VALUES(9,66,1);
insert into scores (s_id,score,c_id)VALUES(9,39,2);
insert into scores (s_id,score,c_id)VALUES(9,85,3);
insert into scores (s_id,score,c_id)VALUES(9,79,5);
insert into scores (s_id,score,c_id)VALUES(10,66,2);
insert into scores (s_id,score,c_id)VALUES(10,89,3);
insert into scores (s_id,score,c_id)VALUES(10,74,4);
insert into scores (s_id,score,c_id)VALUES(10,79,5);

问题引出

原因

主要原因: 这是因为我们存放的中文，而我们的表并不支持中文字符集

查看字符集	字符集
`show variables like '%char%'`

解决方案:

设置 server 和 database 为 utf8 类型

set character_set_server=utf8;
set character_set_database=utf8;

注意: 基本上可以解决

为解决时深入解决方案

-- 查看创建表的字符集
show create table student;
-- 修改字符集
alter table table_name default character set = gb2312 [utf8]; 
alter table table_name convert to character set gb2312 [utf8];

练习

SQL练习

1、查询‘01’号学生的姓名和各科成绩。 

2、查询各个学科的平均成绩，高成绩。 

3、查询每个同学的高成绩及科目名称。 

4、查询所有姓张的同学的各科成绩。 

5、查询每个课程高分的同学信息。 

6、查询名字中含有“张”和‘李’字的学生信息和各科成绩 

7、查询平均成绩及格的同学的信息。

8、将学生按照总分数进行排名。 

9、查询数学成绩的高分、低分、平均分。

10、将各科目按照平均分排序。 

11、查询老师的信息和他所带科目的平均分。 

12、查询被“张楠”和‘‘老孙’叫的课程的高分和平均分。 

13、查询查询每个同学的好成绩的科目名称。 

14、查询所有学生的课程及分数。

15、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名。

16、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩。 

17、查询有不及格课程的同学信息。 

18、求每门课程的学生人数。 

19、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列 。

20、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩。 

21、查询有一门课程成绩在90分以上的学生信息

22、查询出只有三门课程的全部学生的学号和姓名 。

23、查询有不及格课程的课程信息。

24、检索至少选修四门课程的学生学号。

25、查询没有学全所有课程的同学的信息 。

26、查询学全所有课程的同学的信息。 

27、 查询各学生都选了多少门课。

28、查询课程名称为”数学”，且分数低于60的学生姓名和数学分数。

29、查询学过”张楠”老师授课的同学的信息 。

30、查询没学过”张楠”老师授课的同学的信息。