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，数据库作业：

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);

作业数据导入

python-day42_MySQL数据库

二、操作表
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”课程的成绩；

作业题目

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;

作业草稿

python-day42_MySQL数据库

相关推荐