MYSQL的简单查询

1.创建学生成绩（xscj）数据库，在数据库中创建student、course、score、teachers四张数据表。

create database xscj;
use xscj;
CREATE TABLE student
(
s_no bigint(11) UNSIGNED NOT NULL AUTO_INCREMENT  unique key comment'学生学号',
s_name VARCHAR(50)NOT NULL comment'学生姓名',
s_sex VARCHAR(2) comment'性别',
s_bir date  comment'出生日期',
phone VARCHAR(13) unique key comment'电话',
email VARCHAR(50) unique key comment'电子邮箱',
PRIMARY KEY(s_no)
);
CREATE TABLE score
(
s_no bigint(11) UNSIGNED NOT NULL  comment'学生学号' ,
c_no VARCHAR(13) NOT NULL DEFAULT ' ' comment'课程编号',
daily float comment'平时成绩',
final float comment'结业成绩',
primary key(s_no,c_no)
);
CREATE TABLE course
(
c_no CHAR(13) NOT NULL DEFAULT ' ' comment'课程编号',
c_name VARCHAR(50) NULL comment'课程名称',
t_no CHAR(10) NOT NULL DEFAULT ' ' comment'教师编号',
hour float null comment'学分' ,
week int(2) null comment'教学周',
semeste int(1) null comment'开课学期',
PRIMARY KEY(t_no,c_no)
);
CREATE TABLE teacher
(
t_no char(30)NOT NULL COMMENT '教师号',
t_name varchar(30)NOT NULL COMMENT '教师姓名',
major char(30) NULL COMMENT '专业',
prof char(30) NULL COMMENT '职称',
department char(30) COMMENT'院系部门',
PRIMARY KEY(t_no)
);

2.插入数据

INSERT
  INTO `student`
VALUES
  ('18122221320', '张凯', '男', '2001/5/6', '13245678547', '36451@qq.com'),
  ('18122221321', '赵峰', '男', '1999/5/4', '13945678548', 'ABTHE@SOHU.COM'),
  ('18122221322', '王伟', '男', '2003/8/9', '13245678549', '45SSEEE@163.COM'),
  ('18122221323', '李成', '男', '2001/9/12', '13245678550', '78962@qq.com'),
  ('18122221324', '赵晓月', '女', '2002/12/4', '13545678551', '3232459@qq.com'),
  ('18122221325', '王俊鑫', '男', '2003/4/7', '13845678552', '8989456@qq.com'),
  ('18137221506', '王枫', '男', '2001/10/5', '13645678553', 'WSWS4875@SOHU.COM'),
  ('18137221507', '李浩', '男', '2000/11/23', '13888128558', '46SSEEE@163.COM'),
  ('18137221508', '孙琳', '女', '2002/5/1', '13522078555', '124578@qq.com'),
  ('19112100070', '陈晨', '男', '2003/4/8', '13243271256', '457822SW@SOHU.COM'),
  ('19112100071', '吴司', '男', '1999/12/4', '13244673357', 'JEUD@163.COM'),
  ('19112100072', '李珊', '女', '1998/12/25', '13955089958', '78451@qq.com'),
  ('19112100073', '钱峰', '男', '2000/4/1', '13945322359', 'BBBTIH@SOHU.COM'),
  ('19112100074', '张雯', '女', '2000/12/3', '13545612360', 'SSSMOU@163.COM');
INSERT
  INTO `score`
VALUES
  ('18122221320', 'c08123', '85', '95'),
  ('18122221320', 'a01564', '88', '93'),
  ('18122221320', 'C06108', '92', '90'),
  ('18122221321', 'c08123', '85', '94'),
  ('18122221321', 'C06108', '70', '82'),
  ('18122221322', 'c08123', '60', '64'),
  ('18122221322', 'a01327', '53', '50'),
  ('18122221322', 'a01564', '64', '50'),
  ('18122221323', 'a01564', '88', '84'),
  ('18122221323', 'C05103', '85', '86'),
  ('18122221324', 'c08123', '77', '81'),
  ('18122221324', 'C05103', '95', '86'),
  ('18122221325', 'C05103', '88', '92');
INSERT
  INTO `course`
VALUES
  ('c08123', '数据库技术', 't07019', '4', '16', '4'),
  ('c08123', '数据库技术', 't03117', '4', '16', '4'),
  ('C06108', '数据结构', 't07019', '4', '16', '3'),
  ('C06108', '数据结构', 't01247', '4', '16', '3'),
  ('C05103', '计算机原理', 't01247', '4', '16', '2'),
  ('C05103', '计算机原理', 't03117', '4', '16', '2'),
  ('a01327', '高等数学', 't00458', '4', '16', '2'),
  ('a01327', '高等数学', 't00578', '4', '16', '2'),
  ('a01564', '马克思原理', 't04410', '2', '12', '1'),
  ('a01564', '马克思原理', 't04115', '2', '12', '1');
INSERT
  INTO `teacher`
VALUES
  ('t01247', '程瑞', '软件工程', '副教授', '计算机学院'),
  ('t07019', '刘泽', '软件工程', '讲师', '软件学院'),
  ('t04213', '汪凌灵', '网络技术', '副教授', '计算机学院'),
  ('t04115', '刘珊', '哲学', '讲师', '人文学院'),
  ('t00458', '李泽峰', '数学', '助教', '基础课程学院'),
  ('t00578', '张伦', '数学', '讲师', '基础课程学院'),
  ('t02145', '王乐', '英语', '助教', '基础课程学院'),
  ('t04410', '王伟', '哲学', '副教授', '人文学院'),
  ('t03117', '孙艳', '软件工程', '讲师', '软件学院');

3.实验操作过程

（1）、select...from...where基本子句的使用。

①在student表中查询出生日期在2003年以前的学生的学号、姓名、电话和年龄；

select s_no,s_name,phone,s_bir FROM student where s_bir<20030101;

②在score表中查询结业成绩高于90分的学生的学号和课程号,并按照学号排序；

select s_no,c_no FROM score where final>90 order by s_no ASC;

③查询学号分别为18122221324、18137221508和19112100072学生的课程号、平时成绩和结业成绩；

 select s_no,c_no,daily,final FROM score where s_no=18122221324 AND 18137221508 AND 19112100071;

 ④查询选修课程号为c08123的学生的学号和结业成绩，并且要求平时成绩在85~100分；

 select s_no,final FROM score where c_no='c08123' AND  daily>= 85 AND daily<=100;

⑤在 student表中显示所有姓赵的学生的姓名、生日和电子邮件；

 select s_name,s_bir,email FROM student where s_name LIKE '赵%';

⑥在 score表中显示结业成绩高于90分的学生的学号、课程号和结业成绩；

select s_no,c_no,final FROM score where final>=90;

⑦查询计算机学院专业为“软件工程”或“网络技术”的教师的教师号、姓名和职称。

select t_no,t_name,prof FROM teacher where department='计算机学院' AND(major='软件工程'||major='网络技术');

4. 排序、分组和限定记录的查询

①在student表中输出在“2002-12-4”年前出生的的学生的学号、姓名、出生日期和电话,并按照“出生日期”的降序排列。

select s_no,s_name,s_bir,phone FROM student where s_bir<20021202 order by s_bir DESC;

②在score表中查询总评成绩大于85分的学生的学号、课程号和总评成绩,并先按照课程号的升序、再按照总评成绩的降序排列。总评成绩计算公式如下：总评成绩=daily*0.2+final*0.8;

 select s_no,c_no,daily*0.2+final*0.8 FROM score where daily*0.2+final*0.8>85 order by daily*0.2+final*0.8 DESC;

③利用 group by子句对 score表数据分组,显示每个学生的学号和平均总评成绩。总评成绩计算公式如下:总评成绩=daily*0.3+final*0.7;

 select s_no,AVG(daily*0.3+final*0.7),daily*0.3+final*0.7 FROM score where s_no group by s_no ;

④使用group by关键字对score表中的c_no字段进行分组查询。可以查看每门课程平时成绩的平均分和总分。

 select s_no,c_no,AVG(daily),sum(daily) FROM score group by c_no;

⑤查询选课在2门以上且各门课程结业成绩均高于85分的学生的学号及其总成绩,查询结果按总成绩降序列出；总成绩=sum( daily*0.3+final*0.7)。

  select s_no,sum(daily*0.3+final*0.7) FROM score where final>85 group by s_no HAVING count(c_no>2);

⑥查询 score表中结业成绩 final高于90分的成绩,按照平时成绩 daily进行升序排列,从編号1开始,查询3条记录。

 select * FROM score where final>90 order by daily ASC LIMIT 1,3;

3.聚合函数的应用

①查询score表中学生的总结业成绩大于275分的学生学号、总结业成绩及平均结业成绩;

 select s_no,AVG(final),SUM(final) FROM score group by s_no HAVING SUM(final)>275;

 ②查询选修课程号为c05103的学生的结业成绩最高分、最低分及之间相差的分数;

  select c_no,MAX(final),MIN(final),MAX(final)-MIN(final)FROM score where c_no='c05103';

③查询 score表中每个学生的平时成绩和结业成绩的平均分、总分。

 select s_no,AVG(daily),AVG(final),SUM(daily),SUM(final) FROM score group by s_no ;