python-day42_MySQL数据库
MySQL
单机程序(自己DB)
单机程序(公用DB)
MySQL:是用于管理文件的一个软件
- 服务端软件
- socket服务端
- 本地文件操作
- 解析指令【SQL语句】
- 客户端软件(各种各样)
- socket客户端
- 发送指令
- 解析指令【SQL语句】
PS:
- DBMS数据库管理系统
- SQL语句
技能:
- 安装 服务端和客户端
- 连接
- 学习SQL语句规则;指示服务端做任意操作
其他类似软件:
关系型数据库:sqllite,db2,oracle,access,sql server MySQL
非关系型数据库:MongoDB,redis
1. MySQL安装
https://dev.mysql.com/downloads/
Windows:
解压放置到任意目录
初始化
服务端:E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld --initialize-insecure
# 用户名 root 密码:空
启动服务端:
E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld\mysqld
客户端连接:
E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld\mysql -u root -p
发送指令:
show databases;
create database db1;
drop database db1;
环境变量的配置:
E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin
mysqld
windows服务:
E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld --install
E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld --remove
net start mysql
net stop mysql
2. 关于连接
文件夹【数据库】
文件【表】
数据行【行】
数据行
数据行
连接:
默认:用户root
show databases;
use 数据库名称;
show tables;
select * from 表名;
select name,age,id from 表名;
mysql数据库user表
use mysql;
select user,host from user;
1)创建用户:
create user 'alex'@'192.168.1.1' identified by '123123';
create user 'alex'@'192.168.1.%' identified by '123123';
create user 'alex'@'%' identified by '123123';
2)授权:
权限 人
grant select,insert,update on db1.* to 'alex'@'%';
grant all privileges on db1.t1 to 'alex'@'%';
grant SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE,Show view,Create view,Index on national.* to 'national'@'%' identified by '123456';
show grants for fangming; # 查看用户权限
revoke all privileges on db1.t1 from 'alex'@'%';
3. 学习SQL语句规则
操作文件夹
create database db2;
create database db2 default charset utf8; ***** 建数据库时指定了字符集后,新建表时可以不用再指定字符集
show databases;
drop database db2;
操作文件
show tables;
create table t1(id int,name char(10)) default charset=utf8;
create table t1(id int,name char(10))engine=innodb default charset=utf8;
create table t3(id int auto_increment,name char(10))engine=innodb default charset=utf8; *****
# innodb 支持事务,原子性操作
# myisam myisam
create table t1(
列名 类型 null, # 表示本列内容插入的数据可以为空,默认就是这个
列名 类型 not null,
列名 类型 not null auto_increment primary key,
id int,
name char(10)
)engine=innodb default charset=utf8;
auto_increment 表示:自增
primary key: 表示 约束(不能重复且不能为空); 加速查找
not null: 是否为空
数据类型:
数字:
tinyint
int
bigint
FLOAT
0.00000100000123000123001230123
DOUBLE
0.00000000000000000000100000123000123001230123
0.00000100000123000000000000000
decimal
0.1
字符串:
char(10) #无论多少个字符,按最大定长存放。 速度快()
root
root
varchar(10) #按实际使用的字符长度+1(字符长度值)存放,如实际是2个字符,就存2+1个字符的位置。 节省空间
root
PS: 创建数据表时,定长的列往前面放
text
上传文件:
文件存硬盘
db存路径
时间类型
DATETIME
enum 枚举类型
set 里面的元素可以多个组合
create table t1(
id int signed not null auto_increment primary key,
num decimal(10,5),
name char(10)
)engine=innodb default charset=utf8;
清空表:
delete from t1; # 如后续再新增表项,新增表项的数字,是从以前的表项数基础上自动新增
truncate table t1; # 新增表项时,表项数字从1开始新增
删除表:
drop table t1;
查看表结构:
desc t10; #查看表结构(各列的属性设置)
操作文件中内容
插入数据:
insert into t1(id,name) values(1,'alex');
insert into tb1(name,age) values('alex',18),('egon',19),('yuan',20);
删除:
delete from t1;
delete from t1 where id<6
修改:
update t1 set age=18;
update t1 set age=18 where age=17;
查看数据:
select * from t1;
外键:
create table userinfo(
uid int auto_increment primary key,
name varchar(32),
department_id int,
xx_id int,
constraint fk_user_depar foreign key (department_id) references department(id)
)engine=innodb default charset=utf8;
create table department(
id bigint auto_increment primary key,
title char(15)
)engine=innodb default charset=utf8;
# 关联的外键必须是主键,否则关联不成功
4 对于自增补充:
show create table t10;
show create table t10 \G; #查看创建这个表的命令, G为改为按列看
alter table t10 AUTO_INCREMENT=20; #修改这个表
MySQL: 自增步长
基于会话级别:
show session variables like 'auto_inc%'; 查看全局变量
set session auto_increment_increment=2; 设置会话步长
# set session auto_increment_offset=10; 起始值
基于全局级别:
show global variables like 'auto_inc%'; 查看全局变量
set global auto_increment_increment=2; 设置会话步长
# set global auto_increment_offset=10;
SqlServer:自增步长:
基础表级别:
CREATE TABLE `t5` (
`nid` int(11) NOT NULL AUTO_INCREMENT,
`pid` int(11) NOT NULL,
`num` int(11) DEFAULT NULL,
PRIMARY KEY (`nid`,`pid`) #多个键做为一个组合主键
) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=2 DEFAULT CHARSET=utf8
CREATE TABLE `t6` (
`nid` int(11) NOT NULL AUTO_INCREMENT,
`pid` int(11) NOT NULL,
`num` int(11) DEFAULT NULL,
PRIMARY KEY (`nid`,`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=20 DEFAULT CHARSET=utf8
5. 唯一索引
create table t1(
id int ....,
num int,
xx int,
unique 唯一索引名称 (列名,列名),
constraint ....
)
PS:
唯一:
约束不能重复(可以为空)
PS: 主键不能重复(不能为空)
加速查找
1. 外键的变种 a. 用户表和部门表 用户: 1 alex 1 2 root 1 3 egon 2 4 laoyao 3 部门: 1 服务 2 保安 3 公关 ===》 一对多 b. 用户表和博客表 用户表: 1 alex 2 root 3 egon 4 laoyao 博客表: FK() + 唯一 1 /yuanchenqi/ 4 2 /alex3714/ 1 3 /asdfasdf/ 3 4 /ffffffff/ 2 ===> 一对一 create table userinfo1( id int auto_increment primary key, name char(10), gender char(10), email varchar(64) )engine=innodb default charset=utf8; create table admin( id int not null auto_increment primary key, username varchar(64) not null, password VARCHAR(64) not null, user_id int not null, unique uq_u1 (user_id), CONSTRAINT fk_admin_u1 FOREIGN key (user_id) REFERENCES userinfo1(id) )engine=innodb default charset=utf8; c. 用户表(百合网) 相亲记录表 示例1: 用户表 相亲表 示例2: 用户表 主机表 用户主机关系表 ===》多对多 多个对象之间的相互关系,单独进行存档 create table userinfo2( id int auto_increment primary key, name char(10), gender char(10), email varchar(64) )engine=innodb default charset=utf8; create table host( id int auto_increment primary key, hostname char(64) )engine=innodb default charset=utf8; create table user2host( id int auto_increment primary key, userid int not null, hostid int not null, unique uq_user_host (userid,hostid), CONSTRAINT fk_u2h_user FOREIGN key (userid) REFERENCES userinfo2(id), CONSTRAINT fk_u2h_host FOREIGN key (hostid) REFERENCES host(id) )engine=innodb default charset=utf8;
6,SQL数据操作补充
增: insert insert into tb11(name,age) values('alex',12); insert into tb11(name,age) values('alex',12),('root',18); #批量插入数据 insert into tb12(name,age) select name,age from tb11; # 从别一个表拷数据过来 删: delete delete from tb12; delete from tb12 where id !=2 delete from tb12 where id =2 delete from tb12 where id > 2 delete from tb12 where id >=2 delete from tb12 where id >=2 or name='alex' 改: update update tb12 set name='alex' where id>12 and name='xx' update tb12 set name='alex',age=19 where id>12 and name='xx'
查: select select * from tb12; select id,name from tb12; select id,name from tb12 where id > 10 or name ='xxx'; select id,name as cname from tb12 where id > 10 or name ='xxx'; select name,age,11 from tb12; # 增加一个常量(11),常量会在结果中做为列显示出来 其他: select * from tb12 where id != 1 select * from tb12 where id in (1,5,12); select * from tb12 where id not in (1,5,12); select * from tb12 where id in (select id from tb11) select * from tb12 where id between 5 and 12; 通配符: select * from tb12 where name like "a%" #%指任意多个字符,_指一个字符 select * from tb12 where name like "a_"
6,分页:limit
select * from tb12 limit 10; #10条一页
select * from tb12 limit 0,10; #从第0条开始,10条一页
select * from tb12 limit 10,10;
select * from tb12 limit 20,10;
select * from tb12 limit 10 offset 20;
从第20行开始读取,读取10行;
结合Python分页:
# page = input('请输入要查看的页码')
# page = int(page)
# (page-1) * 10
# select * from tb12 limit 0,10; 1
# select * from tb12 limit 10,10;2
7,排序: order by
select * from tb12 order by id desc; 大到小
select * from tb12 order by id asc; 小到大
select * from tb12 order by age desc,id desc;
取后10条数据
select * from tb12 order by id desc limit 10;
8,分组:
select count(id),max(id),part_id from userinfo5 group by part_id;
count
max
min
sum
avg
**** 如果对于聚合函数结果进行二次筛选时,必须使用having ****
即: where后面不能跟函数,having后面可以跟函数
select count(id),part_id from userinfo5 group by part_id having count(id) > 1;
select count(id),part_id from userinfo5 where id > 0 group by part_id having count(id) > 1;
9,连表操作:
由LEFT JOIN,RIGHT JOIN,INNER JOIN连接
WHERE只能放在连表后面
select * from userinfo5,department5; select * from userinfo5,department5 where userinfo5.part_id = department5.id
# userinfo5,department5这2张表,根据userinfo5.part_id和department5.id相等的关系,连在一起输出
select * from userinfo5 left join department5 on userinfo5.part_id = department5.id;
select * from department5 left join userinfo5 on userinfo5.part_id = department5.id;
# 按userinfo5表在左边全部显示
# select * from userinfo5 right join department5 on userinfo5.part_id = department5.id
# 按department5右边表的所有项全部显示
select * from userinfo5 inner join department5 on userinfo5.part_id = department5.id
#inner join,会将出现null的行隐藏。即左边有数据行,右边表又没有数据的行,进行隐藏
# 多张表相关联
select *
from department5
left join userinfo5 on userinfo5.part_id = department5.id
left join userinfo6 on userinfo5.part_id = department5.id;
select score.sid, student.sid
from score
left join student on score.student_id = student.sid #score.student_id和student.sid关联起来
left join course on score.course_id = course.cid #score.course_id和course.cid,把第3个表关联起来
left join class on student.class_id = class.cid #第4个表class也关联起来
left join teacher on course.teacher_id=teacher.tid;
#5个表同时关联起来,一起输出
10,between and
11,数据库作业:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
insert into course(cname,teach_id) values('语文',2),('数学',3),('英语',3); insert into student(sname,gender,class_id) values('大波','男',3),('少伟','女',3),('老妖','女',3),('张三丰','男',2); insert into score(student_id,corse_id,number) values(1,3,63),(2,4,80),(2,6,70),(3,6,65),(3,5,88),(4,2,50),(4,5,98),(5,2,66),(5,5,83),(6,1,50),(6,6,74),(7,3,91),(7,5,68); insert into score(student_id,corse_id,number) values(1,1,60),(1,2,59),(2,2,100);
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
二、操作表 1、自行创建测试数据 2、查询“生物”课程比“物理”课程成绩高的所有学生的学号; 3、查询平均成绩大于60分的同学的学号和平均成绩; 4、查询所有同学的学号、姓名、选课数、总成绩; 5、查询姓“李”的老师的个数; 6、查询没学过“叶平”老师课的同学的学号、姓名; 7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名; 8、查询学过“叶平”老师所教的所有课的同学的学号、姓名; 9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名; 10、查询有课程成绩小于60分的同学的学号、姓名; 11、查询没有学全所有课的同学的学号、姓名; 12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名; 13、查询至少学过学号为“001”同学所选课程中任意一门课的其他同学学号和姓名; 14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名; 15、删除学习“叶平”老师课的SC表记录; 16、向SC表中插入一些记录,这些记录要求符合以下条件:①没有上过编号“002”课程的同学学号;②插入“002”号课程的平均成绩; 17、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩,按如下形式显示: 学生ID,语文,数学,英语,有效课程数,有效平均分; 18、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分; 19、按各科平均成绩从低到高和及格率的百分数从高到低顺序; 20、课程平均分从高到低显示(现实任课老师); 21、查询各科成绩前三名的记录:(不考虑成绩并列情况) 22、查询每门课程被选修的学生数; 23、查询出只选修了一门课程的全部学生的学号和姓名; 24、查询男生、女生的人数; 25、查询姓“张”的学生名单; 26、查询同名同姓学生名单,并统计同名人数; 27、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列; 28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩; 29、查询课程名称为“数学”,且分数低于60的学生姓名和分数; 30、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名; 31、求选了课程的学生人数 32、查询选修“杨艳”老师所授课程的学生中,成绩最高的学生姓名及其成绩; 33、查询各个课程及相应的选修人数; 34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩; 35、查询每门课程成绩最好的前两名; 36、检索至少选修两门课程的学生学号; 37、查询全部学生都选修的课程的课程号和课程名; 38、查询没学过“叶平”老师讲授的任一门课程的学生姓名; 39、查询两门以上不及格课程的同学的学号及其平均成绩; 40、检索“004”课程分数小于60,按分数降序排列的同学学号; 41、删除“002”同学的“001”课程的成绩;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
2、查询“生物”课程比“物理”课程成绩高的所有学生的学号; SELECT A.student_id,A.corse_id,A.number,A.cname,B.corse_id,B.number,B.cname FROM (SELECT * FROM score INNER JOIN course ON score.corse_id=course.cid AND course.cname = "生物") AS A INNER JOIN (SELECT * FROM score INNER JOIN course ON score.corse_id=course.cid AND course.cname = "物理") AS B ON A.student_id=B.student_id WHERE A.number>B.number; ~3、查询平均成绩大于60分的同学的学号和平均成绩; SELECT A.student_id,student.sname,A.avg_num FROM (select student_id,avg(number) AS avg_num from score group by student_id having avg(number)>70) AS A LEFT JOIN student ON student.sid=A.student_id; 4、查询所有同学的学号、姓名、选课数、总成绩; SELECT score.student_id,student.sname,COUNT(1) AS corse_num,SUM(number) AS "总成绩" FROM score left join student on score.student_id=student.sid GROUP BY student_id; ~5、查询姓“李”的老师的个数; select count(tid) from teacher where tname like '李%'; 6、查询没学过“叶平”老师课的同学的学号、姓名; SELECT student.sid,student.sname FROM student WHERE student.sid NOT IN (SELECT score.student_id FROM score INNER JOIN (SELECT course.cid FROM course LEFT JOIN teacher ON course.teach_id=teacher.tid WHERE teacher.tname="饭岛") AS A ON score.corse_id=A.cid GROUP BY score.student_id ); ~7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名; select score.sid,student.sname from score left join student on score.student_id = student.sid where corse_id=1 and corse_id=2; 8、查询学过“叶平”老师所教的所有课的同学的学号、姓名; SELECT student.sid,student.sname FROM student WHERE student.sid IN (SELECT score.student_id FROM score INNER JOIN (SELECT course.cid FROM course LEFT JOIN teacher ON course.teach_id=teacher.tid WHERE teacher.tname="饭岛") AS A ON score.corse_id=A.cid GROUP BY score.student_id ); 9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名; SELECT student.sid,student.sname FROM student LEFT JOIN (SELECT A.student_id FROM (SELECT * FROM score WHERE corse_id = 1) AS A INNER JOIN (SELECT * FROM score WHERE corse_id = 2) AS B ON A.student_id=B.student_id WHERE A.number>B.number) AS C ON student.sid=C.student_id WHERE student.sid=C.student_id; 10、查询有课程成绩小于60分的同学的学号、姓名; SELECT student.sid,student.sname FROM student LEFT JOIN score ON score.student_id=student.sid WHERE score.number <60; 11、查询没有学全所有课的同学的学号、姓名; SELECT student.sid,student.sname FROM student INNER JOIN (SELECT A.student_id FROM (SELECT score.student_id,COUNT(*) AS cou_count FROM score GROUP BY score.student_id) AS A WHERE A.cou_count=(SELECT COUNT(*) FROM course)) AS B ON student.sid=B.student_id; 12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名; SELECT A.student_id,student.sname FROM (SELECT score.student_id FROM score WHERE score.corse_id IN (SELECT corse_id FROM score WHERE student_id =1)) AS A INNER JOIN student ON student.sid=A.student_id GROUP BY A.student_id ; 13、查询至少学过学号为“001”同学所选课程的其他同学学号和姓名; 14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名; SELECT student_id FROM score WHERE student_id IN (SELECT student_id FROM score WHERE student_id!=2 GROUP BY student_id HAVING COUNT(1) =(SELECT COUNT(1) FROM score WHERE student_id = 2)) AND corse_id IN (SELECT corse_id FROM score WHERE student_id = 2) GROUP BY student_id HAVING COUNT(1)=(SELECT COUNT(1) FROM score WHERE student_id = 2); 15、删除学习“叶平”老师课的SC表记录; DELETE FROM score WHERE corse_id IN (SELECT cid FROM course WHERE course.teach_id=(SELECT tid FROM teacher WHERE tname="波多" )); 16、向SC表中插入一些记录,这些记录要求符合以下条件:①没有上过编号“002”课程的同学学号; ②插入“002”号课程的平均成绩; INSERT INTO score(student_id,corse_id,number) SELECT student_id,2,(SELECT AVG(number) FROM score WHERE corse_id=2) AS S1 FROM score WHERE student_id NOT IN (SELECT student_id FROM score WHERE corse_id =2) GROUP BY student_id; 17、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩, 按如下形式显示: 学生ID,语文,数学,英语,有效课程数,有效平均分; SELECT student_id, (SELECT number FROM score AS S1 WHERE corse_id=(SELECT cid FROM course WHERE cname="语文") AND S1.student_id=S2.student_id) AS "语文", (SELECT number FROM score AS S3 WHERE corse_id=(SELECT cid FROM course WHERE cname="数学") AND S3.student_id=S2.student_id) AS "数学", (SELECT number FROM score AS S4 WHERE corse_id=(SELECT cid FROM course WHERE cname="英语") AND S4.student_id=S2.student_id) AS "英语", (SELECT COUNT(1) FROM score AS S5 WHERE S5.student_id=S2.student_id) AS "课程数", AVG(number) AS AVG_num FROM score AS S2 GROUP BY student_id ORDER BY AVG_num; 18、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分; SELECT course.cid,max(number),min(number) FROM course LEFT JOIN score ON score.corse_id=course.cid GROUP BY course.cid; 19、按各科平均成绩从低到高和及格率的百分数从高到低顺序; SELECT corse_id,AVG(number) AS AVG_num, SUM(CASE WHEN number<60 THEN 0 ELSE 1 END)/SUM(1) AS GT60 FROM score AS S2 GROUP BY corse_id ORDER BY AVG_num,GT60 DESC; 20、课程平均分从高到低显示(现实任课老师); SELECT A.corse_id,A.AVG_num,B.tname FROM (SELECT corse_id,AVG(number) AS AVG_num FROM score GROUP BY corse_id ORDER BY AVG_num DESC) AS A LEFT JOIN (SELECT course.cid,teacher.tname FROM teacher LEFT JOIN course ON teacher.tid=course.teach_id) AS B ON A.corse_id=B.cid; 21、查询各科成绩前三名的记录:(不考虑成绩并列情况) SELECT corse_id , (SELECT number FROM score AS S2 WHERE S1.corse_id=S2.corse_id ORDER BY number DESC LIMIT 0,1) AS first_num, (SELECT number FROM score AS S3 WHERE S3.corse_id=S1.corse_id ORDER BY number DESC LIMIT 1,1) AS second_num, (SELECT number FROM score AS S4 WHERE S4.corse_id=S1.corse_id ORDER BY number DESC LIMIT 2,1) AS third_num FROM score AS S1 GROUP BY corse_id; 22、查询每门课程被选修的学生数; SELECT corse_id,COUNT(1) FROM score GROUP BY corse_id; 23、查询出只选修了一门课程的全部学生的学号和姓名; SELECT S1.student_id,student.sname,S1.corse_num FROM (SELECT student_id,COUNT(1) AS corse_num FROM score GROUP BY student_id) AS S1 LEFT JOIN student ON student.sid=S1.student_id WHERE S1.corse_num=1; 24、查询男生、女生的人数; SELECT gender,COUNT(gender) FROM student GROUP BY gender ; 25、查询姓“张”的学生名单; SELECT * FROM student WHERE sname LIKE "张%"; 26、查询同名同姓学生名单,并统计同名人数; SELECT sname,COUNT(sname) AS S1 FROM student GROUP BY sname HAVING S1>1; 27、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列; SELECT corse_id,AVG(number) AS AVG_num FROM score GROUP BY corse_id ORDER BY AVG_num,corse_id DESC; 28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩; SELECT A.student_id,student.sname,A.AVG_num FROM (SELECT score.student_id,AVG(number) AS AVG_num FROM score GROUP BY score.student_id) AS A LEFT JOIN student ON student.sid=A.student_id WHERE A.AVG_num>85; 29、查询课程名称为“数学”,且分数低于60的学生姓名和分数; SELECT student.sname,B.number FROM student INNER JOIN (SELECT score.student_id,score.number FROM score INNER JOIN (SELECT cid FROM course WHERE cname="数学") AS A ON score.corse_id=A.cid WHERE score.number>60) AS B ON student.sid=B.student_id WHERE B.number<60; 30、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名; SELECT A.student_id,student.sname FROM (SELECT student_id FROM score WHERE number>80 AND corse_id=3) AS A LEFT JOIN student ON A.student_id=student.sid; 31、求选了课程的学生人数 SELECT COUNT(1) FROM (SELECT student_id,COUNT(1) AS S1 FROM score GROUP BY student_id HAVING S1>0) AS A; 32、查询选修“杨艳”老师所授课程的学生中,成绩最高的学生姓名及其成绩; SELECT student.sname,MAX(number) FROM score LEFT JOIN student ON score.student_id=student.sid WHERE corse_id IN (SELECT course.cid FROM teacher LEFT JOIN course ON teacher.tid=course.teach_id WHERE teacher.tname="波多") ; 33、查询各个课程及相应的选修人数; SELECT corse_id,COUNT(*) FROM score GROUP BY corse_id ; 34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩; SELECT score.student_id,score.corse_id,A.number FROM (SELECT number,corse_id,student_id,COUNT(*) AS result FROM score GROUP BY number HAVING result>1) AS A LEFT JOIN score ON A.number=score.number ; 35、查询每门课程成绩最好的前两名; SELECT corse_id, (SELECT student_id FROM score AS S2 WHERE S2.corse_id=S1.corse_id LIMIT 0,1) AS first_num, (SELECT student_id FROM score AS S3 WHERE S3.corse_id=S1.corse_id LIMIT 1,1) AS second_num FROM score AS S1 GROUP BY corse_id; 36、检索至少选修两门课程的学生学号; SELECT student_id,COUNT(1) AS S1 FROM score GROUP BY student_id HAVING S1>1; 37、查询全部学生都选修的课程的课程号和课程名; SELECT corse_id,COUNT(1) AS S1 FROM score GROUP BY corse_id HAVING (SELECT COUNT(1) FROM student)=S1; 38、查询没学过“叶平”老师讲授的任一门课程的学生姓名; 39、查询两门以上不及格课程的同学的学号及其平均成绩; SELECT B.student_id,AVG(score.number) FROM (SELECT A.student_id FROM (SELECT student_id,COUNT(number) AS result FROM score WHERE number <60 GROUP BY student_id) AS A WHERE result>1) AS B LEFT JOIN score ON B.student_id=score.student_id GROUP BY B.student_id; 40、检索“004”课程分数小于60,按分数降序排列的同学学号; SELECT student_id FROM score WHERE number<60 ORDER BY number DESC ; 41、删除“002”同学的“001”课程的成绩; DELETE FROM score WHERE student_id=2 AND corse_id=1;