#创建expr03数据库
create database expr03;
#查看数据库建立情况
show databases;
#进入expr03数据库
use expr03;
#创建数据表student
create table student(
stu_id int unsigned primary key comment’学号’,
stu_name varchar(20) not null comment’姓名’,
sex varchar(2) comment’性别’,
birth year comment’出生年份’,
department varchar(20) not null comment’院系’,
addr varchar(50) comment’家庭住址’
)character set utf8 collate utf8_bin;
#查看创建情况
show tables;
#插入6个信息
insert into student (stu_id,stu_name,sex,birth,department,addr)
values (901,’张飞’,’m’,1985,’cmp’,’hb’);
insert into student (stu_id,stu_name,sex,birth,department,addr)
values (902,’关羽’,’m’,1986,’chi’,’sx’);
insert into student (stu_id,stu_name,sex,birth,department,addr)
values (903,’貂蝉’,’f’,1990,’chi’,’sx’);
insert into student (stu_id,stu_name,sex,birth,department,addr)
values (904,’刘备’,’m’,1990,’eng’,’hb’);
insert into student (stu_id,stu_name,sex,birth,department,addr)
values (905,’小乔’,’f’,1991,’eng’,’ah’);
insert into student (stu_id,stu_name,sex,birth,department,addr)
values (906,’赵云’,’m’,1988,’eng’,’hb’);
#查看数据表信息
desc student;
select * from student;
#创建数据表score
create table score(
score_id int unsigned primary key auto_increment comment’编号’,
stu_id int not null comment’学号’,
c_name varchar(20) comment’课程名’,
grade int comment’分数’
)character set utf8 collate utf8_bin;
#查看创建情况
show tables;
#在数据表score中插入10条信息
insert into score (score_id,stu_id,c_name,grade)
values (1,901,’cmp’,98);
insert into score (score_id,stu_id,c_name,grade)
values (2,901,’eng’,80);
insert into score (score_id,stu_id,c_name,grade)
values (3,902,’cmp’,65);
insert into score (score_id,stu_id,c_name,grade)
values (4,902,’chi’,88);
insert into score (score_id,stu_id,c_name,grade)
values (5,903,’chi’,95);
insert into score (score_id,stu_id,c_name,grade)
values (6,904,’cmp’,70);
insert into score (score_id,stu_id,c_name,grade)
values (7,904,’eng’,92);
insert into score (score_id,stu_id,c_name,grade)
values (8,905,’eng’,94);
insert into score (score_id,stu_id,c_name,grade)
values (9,906,’cmp’,90);
insert into score (score_id,stu_id,c_name,grade)
values (10,906,’eng’,85);
#查看情况
desc score;
select * from score;
#查询全体学生的学号和姓名
select stu_id,stu_name from student;
#查询全体学生的详细记录(单表)
select * from student;
#查询英语系的学生姓名、年龄、系别
select stu_name,(YEAR(CURDATE()) – birth),department from student;
#查询所有选修过课程的学生学号（不重复）
select distinct stu_id from score;
#查询考试90分以上的学生学号（不重复）
select distinct stu_id from score where grade>=90;
#查询不是英语系、计算机系的学生性别、年龄、系别
select sex,(YEAR(CURDATE()) – birth),department from student where department!=’eng’ and department!=’cmp’;
#查询出生于1980到1989年的学生学号、姓名、系别、出生日期；
select stu_id,stu_name,department,birth from student where birth between 1980 and 1989;
#查询姓张的学生情况
select * from student where stu_name like “张%”;
#查询姓刘或姓赵的学生情况
select * from student where stu_name like “刘%” or stu_name like “赵%”;
#查询姓刘且名字为两个字的学生情况
select * from student where stu_name like “刘__”;
#查询1990年以后出生的学生姓名
select stu_name,birth from student where birth >=1990;
#查询英语系男同学的学生姓名
select department,stu_name from student where department=’eng’ and sex=’m’;
#查询女同学或者来自sx的男同学的学生姓名、性别及家庭住址
select stu_name,sex,addr from student where sex=’f’ or addr=’sx’;
#查询成绩在90到95之间的课程名称及学生学号
select c_name,stu_id,grade from score where grade between 90 and 95;
#查询英语系中河北籍学生的人数
select count(stu_id) from student where department=’eng’ and addr=’hb’;
#统计年龄在34岁以下的男、女性的数量
select sex,count(stu_id) from student where (YEAR(CURDATE()) – birth)<=34
group by sex; //注意要先设置年龄条件再进行分组，在分组之后只能能进行聚合函数的计算
#查询山西学生的最大年龄，最小年龄及山西学生的总人数
select max((YEAR(CURDATE()) – birth)),min((YEAR(CURDATE()) – birth)),count(stu_id) from student
where addr=’sx’
group by (YEAR(CURDATE()) – birth); //使用聚合函数(max\min)时要使用分组
#查询学生语文成绩的总和
select sum(grade) from score where c_name=’chi’;
#查询计算机课程的最高成绩和最低成绩
select c_name,max(grade),min(grade) from score where c_name=’cmp’
group by c_name;
#查询学生英语成绩的平均值
select avg(grade) from score where c_name=’eng’;
#查询每位学生的平均成绩
select stu_id,avg(grade) from score group by stu_id;
#查询选修每门课程的人数
select c_name,count(stu_id) from score group by c_name;
#将每门课程的成绩由高到低排序
select a.c_name,a.grade from score a
left join score b on a.c_name = b.c_name
and a.grade <= b.grade
group by a.c_name,a.grade
order by a.c_name,a.grade desc;
#查询每门课程的最高成绩，且按降序排列
select c_name,max(grade) from score group by c_name
order by group_concat(grade) desc;
#查询每门课程的平均成绩，且按降序排列
select c_name,avg(grade) from score group by c_name order by avg(grade) desc;
#查询每位学生的最低成绩，且查询结果按其成绩的降序排列
select stu_id,min(grade) from score group by stu_id order by min(grade) desc;
#用LENGTH(str)函数查询student表中stu_id为905的stu_name字段的字符长度
select stu_id,stu_name,length(stu_name) from student where stu_id=905;
#用CONCAT(s1,s2,……)函数将student表中stu_id为905的stu_namer字段中数据与“：”和“a beautiful girl”字符串连接起来
select concat(stu_name,’ a beautiful girl’) from student where stu_id=905;
#查询每门课程选课学生的stu_id
select c_name,group_concat(stu_id) from score group by c_name;
整体的代码：
#创建expr03数据库
create database expr03;
#查看数据库建立情况
show databases;
#进入expr03数据库
use expr03;
#创建数据表student
create table student(
stu_id int unsigned primary key comment'学号',
stu_name varchar(20) not null comment'姓名',
sex varchar(2) comment'性别',
birth year comment'出生年份',
department varchar(20) not null comment'院系',
addr varchar(50) comment'家庭住址'
)character set utf8 collate utf8_bin;
#查看创建情况
show tables;
#插入6个信息
insert into student (stu_id,stu_name,sex,birth,department,addr)
values (901,'张飞','m',1985,'cmp','hb');
insert into student (stu_id,stu_name,sex,birth,department,addr)
values (902,'关羽','m',1986,'chi','sx');
insert into student (stu_id,stu_name,sex,birth,department,addr)
values (903,'貂蝉','f',1990,'chi','sx');
insert into student (stu_id,stu_name,sex,birth,department,addr)
values (904,'刘备','m',1990,'eng','hb');
insert into student (stu_id,stu_name,sex,birth,department,addr)
values (905,'小乔','f',1991,'eng','ah');
insert into student (stu_id,stu_name,sex,birth,department,addr)
values (906,'赵云','m',1988,'eng','hb');
#查看数据表信息
desc student;
select * from student;
#创建数据表score
create table score(
score_id int unsigned primary key auto_increment comment'编号',
stu_id int not null comment'学号',
c_name varchar(20) comment'课程名',
grade int comment'分数'
)character set utf8 collate utf8_bin;
#查看创建情况
show tables;
#在数据表score中插入10条信息
insert into score (score_id,stu_id,c_name,grade)
values (1,901,'cmp',98);
insert into score (score_id,stu_id,c_name,grade)
values (2,901,'eng',80);
insert into score (score_id,stu_id,c_name,grade)
values (3,902,'cmp',65);
insert into score (score_id,stu_id,c_name,grade)
values (4,902,'chi',88);
insert into score (score_id,stu_id,c_name,grade)
values (5,903,'chi',95);
insert into score (score_id,stu_id,c_name,grade)
values (6,904,'cmp',70);
insert into score (score_id,stu_id,c_name,grade)
values (7,904,'eng',92);
insert into score (score_id,stu_id,c_name,grade)
values (8,905,'eng',94);
insert into score (score_id,stu_id,c_name,grade)
values (9,906,'cmp',90);
insert into score (score_id,stu_id,c_name,grade)
values (10,906,'eng',85);
#查看情况
desc score;
select * from score;
#查询全体学生的学号和姓名
select stu_id,stu_name from student;
#查询全体学生的详细记录(单表)
select * from student;
#查询英语系的学生姓名、年龄、系别
select stu_name,(YEAR(CURDATE()) - birth),department from student;
#查询所有选修过课程的学生学号（不重复）
select distinct stu_id from score;
#查询考试90分以上的学生学号（不重复）
select distinct stu_id from score where grade>=90;
#查询不是英语系、计算机系的学生性别、年龄、系别
select sex,(YEAR(CURDATE()) - birth),department from student where department!='eng' and department!='cmp';
#查询出生于1980到1989年的学生学号、姓名、系别、出生日期；
select stu_id,stu_name,department,birth from student where birth between 1980 and 1989;
#查询姓张的学生情况
select * from student where stu_name like "张%";
#查询姓刘或姓赵的学生情况
select * from student where stu_name like "刘%" or stu_name like "赵%";
#查询姓刘且名字为两个字的学生情况
select * from student where stu_name like "刘__";
#查询1990年以后出生的学生姓名
select stu_name,birth from student where birth >=1990;
#查询英语系男同学的学生姓名
select department,stu_name from student where department='eng' and sex='m';
#查询女同学或者来自sx的男同学的学生姓名、性别及家庭住址
select stu_name,sex,addr from student where sex='f' or addr='sx';
#查询成绩在90到95之间的课程名称及学生学号
select c_name,stu_id,grade from score where grade between 90 and 95;
#查询英语系中河北籍学生的人数
select count(stu_id) from student where department='eng' and addr='hb';
#统计年龄在34岁以下的男、女性的数量
select sex,count(stu_id) from student where (YEAR(CURDATE()) - birth)<=34
group by sex; //注意要先设置年龄条件再进行分组，在分组之后只能能进行聚合函数的计算
#查询山西学生的最大年龄，最小年龄及山西学生的总人数
select max((YEAR(CURDATE()) - birth)),min((YEAR(CURDATE()) - birth)),count(stu_id) from student
where addr='sx'
group by (YEAR(CURDATE()) - birth); //使用聚合函数(max\min)时要使用分组
#查询学生语文成绩的总和
select sum(grade) from score where c_name='chi';
#查询计算机课程的最高成绩和最低成绩
select c_name,max(grade),min(grade) from score where c_name='cmp'
group by c_name;
#查询学生英语成绩的平均值
select avg(grade) from score where c_name='eng';
#查询每位学生的平均成绩
select stu_id,avg(grade) from score group by stu_id;
#查询选修每门课程的人数
select c_name,count(stu_id) from score group by c_name;
#将每门课程的成绩由高到低排序
select a.c_name,a.grade from score a
left join score b on a.c_name = b.c_name
and a.grade <= b.grade
group by a.c_name,a.grade
order by a.c_name,a.grade desc;
#查询每门课程的最高成绩，且按降序排列
select c_name,max(grade) from score group by c_name
order by group_concat(grade) desc;
#查询每门课程的平均成绩，且按降序排列
select c_name,avg(grade) from score group by c_name order by avg(grade) desc;
#查询每位学生的最低成绩，且查询结果按其成绩的降序排列
select stu_id,min(grade) from score group by stu_id order by min(grade) desc;
#用LENGTH(str)函数查询student表中stu_id为905的stu_name字段的字符长度
select stu_id,stu_name,length(stu_name) from student where stu_id=905;
#用CONCAT(s1,s2,……)函数将student表中stu_id为905的stu_namer字段中数据与“：”和“a beautiful girl”字符串连接起来
select concat(stu_name,' a beautiful girl') from student where stu_id=905;
#查询每门课程选课学生的stu_id
select c_name,group_concat(stu_id) from score group by c_name;