mysql数据库综合练习

1.先建立一个专门数据库

create database mysql_exampleTest; #创建数据库
use mysql_exampleTest;  #进入数据库

2.建立相关数据表

# 学生表
CREATE TABLE Student(
s_id VARCHAR(20),
s_name VARCHAR(20) NOT NULL DEFAULT '',
s_birth VARCHAR(20) NOT NULL DEFAULT '',
s_sex VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(s_id)
);

# 课程表
CREATE TABLE Course(
c_id VARCHAR(20),
c_name VARCHAR(20) NOT NULL DEFAULT '',
t_id VARCHAR(20) NOT NULL,
PRIMARY KEY(c_id)
);

# 教师表
CREATE TABLE Teacher(
t_id VARCHAR(20),
t_name VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(t_id)
);

# 成绩表
CREATE TABLE Score(
s_id VARCHAR(20),
c_id VARCHAR(20),
s_Score INT(3),
PRIMARY KEY(s_id,c_id)
);

3.插入数据

# 插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');


# 课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

# 教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');


# 成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

4.检查数据是否正常插入

select *from Student;
select *from Course;
select *from Teachar;
select *from Score;

 

5.插入数据报错

如果出现以下错误则

insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
ERROR 1366 (HY000): Incorrect string value: '\xE8\xB5\xB5\xE9\x9B\xB7' for column 's_name' at row 1

就是说Sname列的数据值的字符集不正确。
解决方案：分别查看数据库、表、报错列字段的字符集，错误就是由于三者不一致造成的，设置一致.

查看方法如下

show create table Student；

 

然后把字符集修改为如下即可 

 

6.实现要求 

6.1     查询"01"课程比"02"课程成绩高的学生的信息及课程分数

select st.*,sc.s_Score as '语文' ,sc2.s_Score '数学' 
from Student st #别名
left join Score sc on sc.s_id=st.s_id and sc.c_id='01' 
left join Score sc2 on sc2.s_id=st.s_id and sc2.c_id='02'  
where sc.s_Score>sc2.s_Score;

为了防止产生二义性，对表使用了别名，Score 表第 1次

出现的别名为sc，第2次出现的别名为sc2。

left join 左连接的结果包括LEFT OUTER子中指定的左表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果行中，右表的所有选择列表列均为空值。

as改变标题栏名字为数学和语文

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

函数	作用
AVG()	返回某列平均值
COUNT()	返回某列行数
MAX()	返回某列最大值
MIN()	返回某列最小值
SUM()	返回某列总和

​
select st.s_id,st.s_name,ROUND(AVG(sc.s_Score),2) cjScore 
from Student st
left join Score sc on sc.s_id=st.s_id
group by st.s_id having AVG(sc.s_Score)>=60;

​

 “列函数对于GROUP BY子句定义的每个组各返回一个结果”，AVG函数对每个学生返回一个结果，就是每个学生的平均值。

ROUND(X,D): 返回'参数X'四舍五入的有 D 位小数的一个数字。如果D为0，结果将没有小数点或小数部分;如果D为负数，这时是指定小数点左边的D位整数位为0,同时小数位均为0

6.3. 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

select st.* from Student st 
inner join Score sc on sc.s_id = st.s_id
inner join Course c on c.c_id=sc.c_id and c.c_id="01"
where st.s_id in (
select st2.s_id from Student st2 
inner join Score sc2 on sc2.s_id = st2.s_id
inner join Course c2 on c2.c_id=sc2.c_id and c2.c_id="02"
)

内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表

中与连接条件相匹配的数据行，组合成新记录，也就是说，在内连接查询中，只有满足

条件的记录才能出现在结果关系中。

6.4  查询学生平均成绩及其名次

select a.*, count(distinct b.平均成绩) '名次'
from
(select st.s_id, st.s_name,
(case
    when avg(s.s_Score) is null then 0 else avg(s.s_Score) end) '平均成绩' 
    from Student st left join Score s on st.s_id=s.s_id
group by st.s_id, st.s_name) a,
(select st.s_id,
(case 
    when avg(s.s_Score) is null then 0 else avg(s.s_Score) end) '平均成绩' 
    from Student st left join Score s on st.s_id=s.s_id
group by st.s_id) b
where a.平均成绩<=b.平均成绩
group by a.s_id
order by a.平均成绩 desc;

CASE WHEN [expr] THEN [result1]... ELSE [default] END
搜索函数可以使用表达式 expr 判断，并且返回第一个符合条件的值。

order by 顺序排序

6.5  查询本月过生日的学生

select st.* from Student st
where month(now())=month(date_format(st.s_birth,'%Y%m%d'));
#本月1月

同理本周用week替换month即可下一周，下一月在后面加一即可如下 

由于下一月也就是二月无人过所以返回空。