首页

MySQL 基础

吴心役
2022-11-26

安装

  1. 下载压缩包

  2. 将压缩包解压到：c:\mysql

  3. 在c:\mysql 目录下创建 my.ini 文件，文件内容如下：

    [client]
# 设置mysql客户端默认字符集
default-character-set=utf8
 
[mysqld]
# 设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=C:\\web\\mysql-8.0.11
# 设置 mysql数据库的数据的存放目录，MySQL 8+ 不需要以下配置，系统自己生成即可，否则有可能报错
# datadir=C:\\web\\sqldata
# 允许最大连接数
max_connections=20
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

  4. 切换到 c:\mysql\bin 目录

  5. 初始化数据库 mysqld --initialize --console ，初始化完成后会输出 root 用户的临时密码，如下面的 APWCY5ws&hjQ 就是临时密码。

    2018-04-20T02:35:05.464644Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: APWCY5ws&hjQ

  6. 安装：mysqld install，每次重新启动的时候都要安装。

  7. 启动：net start mysql

  8. 登录：mysql -u root -p

    此时若提示无法登录，可以进行如下操作

    • 关闭：net stop mysql
    • 修改my.ini 文件：mysqld --defaults-file="c:\mysql\my.ini" --console --skip-grant-tables
    • 从第7步开始

  9. 输入密码

  10. 设置新密码

    • 永久密码：ALTER USER 'root'@'localhost' BY '新密码' PASSWORDXPIRE NEVER;
    • 期限密码：ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码' PASSWORD EXPIRE;

数据库基本概念

  1. RDBMS（关系型数据库管理系统）的特点：
    • 数据以表格的形式出现
    • 每行为各种记录名称
    • 每列为记录名称所对应的数据域
    • 许多的行和列组成一张表单
    • 若干的表单组成 database（数据库）
  2. RDBMS 术语：
    • 数据库：关联表的集合
    • 数据表：数据的矩阵
    • 列：一列（数据元素）包含了相同的数据
    • 行：一行（元组或记录）是一组相关的数据
    • 冗余：存储两倍的数据，冗余降低了性能，但提高了安全性
    • 主键：主键是唯一的。一个数据表中只能包含一个主键，可以用主键查询数据
    • 外键：用于关联两个表
    • 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引
    • 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据表中一列或多列的值进行排序的一种结构，类似于书籍和目录。
    • 参照完整性：参照的完整性要求关系中不允许引用不存在的实体，与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

MySQL基本命令

元数据：

  1. SELECT VERSION()：服务器版本信息。
  2. SELECT DATABASE()：当前数据库名（没有返回空）
  3. SELECT USER()：当前用户名
  4. SHOW STATUS：服务器状态
  5. SHOW VARIABLES：服务器配置变量

数据库操作：

  1. SHOW DATABASES：查看所有数据库。
  2. CREATE DATABASE 数据库名：创建数据库。
  3. DROP DATABASE 数据库名：删除数据库。
  4. USE 数据库名：选择数据库。
  5. SHOW TABLES：查数据库中的所有数据表。
  6. SHOW TABLE STATUS：查看数据库中所有的数据表的类型。

数据表基本操作：

  1. CREATE TABLE 表名 (字段名 字段格式, 字段名 字段格式, ···)：创建数据表 ，例如：

    CREATE TABLE IF NOT EXISTS `表名`(
    `字段名` INT UNSIGNED AUTO_INCREMENT,
    `字段名` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

  2. DROP TABLE 表名：删除数据表。

  3. SHOW COLUMNS FROM 表名：查看数据表中的字段信息。

  4. INSERT INTO 表名 (字段名, ···) VALUES (值, ···)：向数据表中插入数据，例如：

    INSERT INTO 表名
(字段名, 字段名)
VALUES
(字段值, 字段值),
(字段值, 字段值);

  5. UPDATE 表名 SET 列名=新值, 列名=新值 WHERE 条件：修改数据表中的数据 ，where 指定更改的位置，不指定的话更改整个列。

  6. DELETE FROM 表名 WHERE 条件：删除数据表中的数据 ，where 指定删除的位置，不指定的话删除所有数据。

  7. ALTER TABLE 表名 DROP 列名：删除数据表中的某列。

  8. ALTER TABLE 表名 ADD 新增列名 新增列的类型 [AFTER 列名]：在数据表中新增列，AFTER 列名 表示将新列插入一列之后，FIRST 表示新增列为第一列。

  9. ALTER TABLE 表名 MODIFY 列名 列的类型：修改数据表中的列的类型。

  10. ALTER TABLE 表名 CHANGE 旧列名 新列名 新列的类型：修改表中的列名

  11. ALTER TABLE 旧表名 RENAME TO 新表名：修改数据表的表名。

  12. SELECT 字段名，字段名，··· FROM 表名 ：查询数据表中的数据 ，* 代指所有字段。

索引及索引操作

  1. 表建立后创建索引：CREATE INDEX [索引名] ON 表名(字段名(字段长度))

  2. 添加索引：ALTER TABLE 表名 ADD INDEX 索引名(字段名(字段长度))

  3. 创建时指定：INDEX 索引名(字段名(字段长度))

  4. 删除索引：DROP INDEX 索引名 ON 表名

  5. 显示索引信息：SHOW INDEX FROM 表名

  6. 索引分类：使用时，只需要将 INDEX 替换为相应索引的标识即可。

    INDEX 、 KEY ：普通索引，一个表中可以有多个。

    UNIQUE KEY：唯一索引，一个表中可以有多个。

    PRIMARY KEY：主键索引，一个表中只能由一个，主键的类型最好是数值且能自动增长。

    FULL TEXT：全文索引。

    FOREING KEY：外键索引，外键索引自动和对应的其他表的主键关联。

操作符

  1. AS：为数据表指定别名。

    语法：

    SELECT 表名.字段名 [,别名.字段名] FROM
表名 AS 别名 INNER JOIN 表名 AS 别名
ON 条件

  2. union ：用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中，多个 SELECT 语句会删除重复的数据。纵向连接，所有字段数量要相等。

    语法：

    SELECT 字段名 FROME 表名 [WHERE 条件]
UNION [ALL | DISTINCT]
SELECT 字段名 FROME 表名 [WHERE 条件]

参数：
ALL：返回的数据集中，包含重复数据。
DISTINCT：返回的数据集中，不包含重复数据。

  3. ORDER BY：根据一个或多个字段对数据集进行排序。

    语法：

    SELECT * FROM 表名
ORDER BY 字段名 [字段名···] [ASC | DESC]

参数：
ASC：升序排列，默认。
DESC：降序排列

  4. GROUP BY：根据一个或多个列对数据集进行分组，在分组的列上可以使用一些统计函数。

    语法：

    SELECT 字段名, 统计函数 FROM 表名
WHERE 条件
GROUP BY 字段名 [HAVING 条件]
ORDER BY 字段名 [ASC | DESC]

  5. WHERE 条件：按条件返回查询的内容。

    符号说明
    = 、 < 、 <= 、 > 、 >= 、 != 、 <> 、 !> 、 !<比较操作符
    BETWEEN 值 AND 值介于 值 之间
    NOT BETWEEN 值 AND 值不再 值 之间
    LINKE 、 IS NOT LINKE匹配字符
    IS NULL 、 IS NOT NULL是否为空
    AND 、 OR连接多个条件
    %通配符，匹配一个多多个字符
    -匹配一个字符

  6. [INNER | LEFT | RIGHT] JOIN：将多个表中的数据读出，并连接在一起。

    语法：

    SELECT 表名.字段名 FROM
表名 [INNER | LEFT | RIGHT] JOIN 表名
ON 字段=字段

参数：
INNER JOIN：内连接，求两个表的交集。
LEFT JOIN：外连接，获取左边表的所有记录，即使右边表没有对应的匹配。
RIGHT JOIN：外连接，获取右边表的所有记录，即使左边表没有对应的匹配。
ON 条件：连接的条件，以哪个字段为标准连接。

  7. NULL：空值，用于判断表中的空值。IS NULL 当值为 NULL 时返回，IS NOT NULL 当值不为 NULL 时返回。

  8. REGEXP 正则表达式：使用正则表达式进行匹配。

事务

系列数据库操作构成事务，事务的意义在于将数据库操作隔离起来，确认操作无误后再提交事务，若操作出现错误则可以进行回滚，将数据库恢复到事务开始时候的状态。

  • BEGIN ：开启一个事务。
  • COMMIT ：提交事务，此时对数据库的修改将变为永久修改。
  • ROLLBACK ：回滚，结束事务，并撤销正在进行的所有未提交的修改。
  • SAVEPOINT tag ：创建一个保存点，一个事务可以有多个保存点。
  • ROLLBACK TO tag ：把事务回滚到标记点。
  • SET TRANSACTION ：设置事务的隔离级别。

复制表

  1. 使用 SHOW CREATE TABLE table_name 语句，获取表结构信息，并复制。
  2. 使用 CREATE TABLE table_name 语句创建新表，并将旧表中的表结构信息复制到新表中作为新表的结构信息，这时已经创建了一个和旧表有相同结构的新表。注意：表结构信息包括，字段名，字段类型，索引等。
  3. 使用 INSERT INTO 新表名(字段名 ···) SELECT(字段名 ···) FROM(旧表名) 将旧表中的数据复制到新表中去。

练习题

创建表

  • 学生表

    s_id：学生ID，s_name：学生姓名，s_birth：学生生日，s_sex：学生性别

    create table student(
    s_id varchar(20),
    s_name varchar(20) not NULL,
    s_birth varchar(20) not NULL,
    s_sex varchar(10) not NULL,
    constraint pk_student PRIMARY KEY (s_id)
)

  • 课程表

    c_id：课程ID，c_name：课程名称，t_id：教师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)
)

  • 教师表

    t_id：教师ID，t_name：教师姓名

    create table teacher(
    t_id varchar(20),
    t_name varchar(20) not NULL DEFAULT '',
    PRIMARY KEY(t_id)
)

  • 成绩表

    s_id：学生ID，c_id：课程ID，s_score：课程分数

    create table score(
    s_id varchar(20),
    c_id  varchar(20),
    s_score int(3),
    PRIMARY KEY(s_id,c_id))

alter table teacher convert to character set utf8

插入数据

  1. 学生表数据

    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' , '女');

  2. 课程表数据

    insert into course values('01' , '语文' , '02');
insert into course values('02' , '数学' , '01');
insert into course values('03' , '英语' , '03');

  3. 教师表数据

    insert into teacher values('01' , '张三');
insert into teacher values('02' , '李四');
insert into teacher values('03' , '王五');

  4. 成绩表数据

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

习题

  1. 查询每门课程被选修的学生数

    select s.c_id, c.c_name, count(*) 
from 
(score s
join
course c
on s.c_id = c.c_id)
group by s.c_id

  2. 查询出只有两门课程的全部学生的学号和姓名

    select * from student where s_id in
(select s_id from score
group by s_id
having count(*) = 2)

  3. 查询男生、女生人数

    select s_sex, count(*)
from student
group by s_sex

  4. 查询名字中含有"风"字的学生信息

    select * from student
where s_name like '%风%'

  5. 查询同名同性学生名单，并统计同名人数

    select a.s_name,a.s_sex,count(*) from 
student a  
join
student b on a.s_id !=b.s_id and a.s_name = b.s_name and a.s_sex = b.s_sex
group by a.s_name,a.s_sex

  6. 查询1990年出生的学生名单

    select * from student
where s_birth like '1990%'

  7. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

    select c_id, avg(s_score) from score
group by c_id
order by avg(s_score) desc, c_id asc

  8. 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

    select st.s_id, st.s_name, avg(s.s_score) from 
(score s join student st
on s.s_id = st.s_id)
group by s.s_id
having avg(s.s_score) >= 85

  9. 查询课程名称为"数学"，且分数低于60的学生姓名和分数

    select c.c_name, t.s_name, s.s_score
from 
(score s join course c on s.c_id = c.c_id
join student t on s.s_id = t.s_id)
where c.c_name = '数学' and s.s_score < 60

  10. 查询所有学生的课程及分数情况

    select st.s_id, st.s_name,
sum(case c.c_name when '语文' then s.s_score else 0 end) as '语文',
sum(case c.c_name when '数学' then s.s_score else 0 end) as '数学',
sum(case c.c_name when '英语' then s.s_score else 0 end) as '英语',
sum(s.s_score) '总分'
from
(student st left outer join score s on st.s_id = s.s_id
left outer join course c on s.c_id = c.c_id)
group by st.s_id

  11. 查询任何一门课程成绩在70分以上的姓名、课程名称和分数

    select st.s_name, c.c_name, s.s_score
from
(score s join student st on s.s_id = st.s_id
join course c on s.c_id = c.c_id)
where s.s_score >= 70

  12. 查询不及格的课程

    select s.s_id, c.c_name, s_score from 
(score s left join course c on s.c_id=c.c_id)
where s_score<60

  13. 查询课程编号为01且课程成绩在80分以上的学生的学号和姓名

    select s.s_id, s.c_id, st.s_name, s.s_score
from
(score s left join student st on s.s_id=st.s_id)
where s.c_id='01' and s.s_score>=80

  14. 求每门课程的学生人数

    select c_id, count(c_id) from score group by c_id

  15. 查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

    select t.t_name, st.s_id, st.s_name, s.s_score
from
(score s left join course c on s.c_id=c.c_id
left join teacher t on c.t_id=t.t_id
left join student st on s.s_id=st.s_id)
where t.t_name='张三'
group by s.s_score desc
limit 1

  16. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

    select distinct s.s_id, s.c_id, s.s_score from
score s cross join score sc
where s.s_score=sc.s_score and s.c_id!=sc.c_id

  17. 查询每门功成绩最好的前两名

    select s.s_id, s.c_id, s.s_score from
score s left join score sc
on s.c_id=sc.c_id and s.s_score<sc.s_score
group by s.s_id, s.c_id having count(sc.s_id) < 2
order by s.c_id,s.s_score desc

  18. 统计每门课程的学生选修人数（超过5人的课程才统计）。

    要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。

    select c_id, count(c_id) from score 
group by c_id having count(c_id)>5
order by count(c_id)desc ,c_id asc

  19. 检索至少选修两门课程的学生学号

    select s_id, count(c_id) from score
group by s_id
having count(c_id)>=2

  20. 查询选修了全部课程的学生信息

    select * from student where s_id in
(select s_id from score group by s_id
having count(s_id)=(select count(*) from course))

  21. 查询各学生的年龄

    select s_name,
(DATE_FORMAT(now(),'%Y')-DATE_FORMAT(cast(s_birth as date),'%Y'))-
(case when DATE_FORMAT(now(),'%m%d')>DATE_FORMAT(cast(s_birth as date),'%m%d') then 0 else 1 end) as age
from student

  22. 查询本周过生日的学生

    select * from student where WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))=WEEK(s_birth)

  23. 查询下周过生日的学生

    select * from student where week(date_format(now(),'%Y%m%d'))+1=week(s_birth)

  24. 查询本月过生日的学生

    select * from student where MONTH(DATE_FORMAT(NOW(),'%Y%m%d')) = MONTH(s_birth)

  25. 查询下月过生日的学生

    select * from student where MONTH(DATE_FORMAT(NOW(),'%Y%m%d'))+1 = MONTH(s_birth)

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

    select *
from student st inner join score sc
on st.s_id = sc.s_id and sc.c_id = '01'
left outer join score s
on st.s_id = s.s_id and s.c_id = '02'
where sc.s_score > s.s_score;

  27. 查询"01"课程比"02"课程成绩低的学生的信息及课程分数

    select *
from student st inner join score sc
on st.s_id = sc.s_id and sc.c_id = '01'
left join score s
on st.s_id = s.s_id and s.c_id = '02'
where sc.s_score < s.s_score;

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

    select s.s_id,  st.s_name, round(sum(s.s_score)/count(*),2) avg_score
from 
score s left join student st
on s.s_id = st.s_id
group by s.s_id
having sum(s.s_score)/count(*) >= 60

  29. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

    select st.s_id, st.s_name, count(s.c_id), sum(s.s_score)
from
student st left join score s
on st.s_id = s.s_id
group by st.s_id;

  30. 查询"李"姓老师的数量

    select count(*)
from teacher
where t_name like '李%'

  31. 查询学过"张三"老师授课的同学的信息

    select t.t_name, st.*
from score s left join course c
on s.c_id = c.c_id
left join teacher t
on c.t_id = t.t_id
left join student st
on s.s_id = st.s_id
where t.t_id = '01'
order by s.s_id

  32. 查询没学过"张三"老师授课的同学的信息

    select *
from student
where s_id not in
(select s.s_id
from score s left join course c
on s.c_id = c.c_id
left join teacher t
on c.t_id = t.t_id
left join student st
on s.s_id = st.s_id
where t.t_id = '01'
order by s.s_id)

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

    select *
from score s inner join student st
on s.s_id = st.s_id
where s.c_id = '01' or s.c_id = '02'
group by s.s_id
having count(*)>1

  34. 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

    #-----------形式一-----------
select *
from(
select s.s_id, s.c_id, st.s_name, st.s_birth 
from score s inner join student st
on s.s_id = st.s_id
where s.c_id = '01' or s.c_id = '02'
group by s.s_id
having count(*) = 1
) a
where a.c_id = '01'

#-----------形式二-----------
select *
from student st
where st.s_id in (select s_id from score where c_id = '01')
and st.s_id not in (select s_id from score where c_id = '02');

  35. 查询没有学全所有课程的同学的信息

    select *
from student st
where st.s_id  not in (
select s_id from score group by s_id having count(*) =3)

  36. 查询没有学全所有课程的同学的信息

    select *
from student st
where st.s_id  not in (
select s_id from score group by s_id having count(*) =3)

  37. 查询至少有一门课与学号为"01"的同学所学相同的同学的信息

    select * from student
where s_id in
(select b.s_id from
(select s_id, c_id from score where s_id = '01') a
cross join
(select s_id, c_id from score where s_id != '01') b
where a.c_id = b.c_id
group by b.s_id)

  38. 查询和"01"号的同学学习的课程完全相同的其他同学的信息

    select * from student
where s_id in    
(select b.s_id from
(select s_id, c_id from score where s_id = '01') a
cross join
(select s_id, c_id from score where s_id != '01') b
where a.c_id = b.c_id
group by b.s_id
having count(*) = 3)

  39. 查询没学过"张三"老师讲授的任一门课程的学生姓名

    select * from student
where s_id not in
(select s.s_id
from score s inner join course c
on s.c_id = c.c_id
inner join teacher t
on c.t_id = t.t_id
where t.t_name = '张三')

  40. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

    select st.* , a.avg_score
from student st inner join
(select s_id, sum(s_score)/count(*) avg_score from score
where s_score < 60
group by s_id
having count(*) >= 2) a
where st.s_id = a.s_id

  41. 检索"01"课程分数小于60，按分数升序排列的学生信息

    select * from student st
inner join
(select s_id, s_score from score
where c_id = '01' and s_score < 60) s
on st.s_id = s.s_id
order by s.s_score asc

  42. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

    #-----------形式一-----------
select st.s_id, st.s_name, s.s_score, sc.sum_score from student st
left join score s
on st.s_id = s.s_id
left join 
(select s_id, sum(s_score) sum_score from score
group by s_id) sc
on st.s_id = sc.s_id
order by sc.sum_score desc

#-----------形式二-----------
select s.s_id,
(select s_score from score where s_id = s.s_id and c_id = '01') languages,
(select s_score from score where s_id = s.s_id and c_id = '02') maths,
(select s_score from score where s_id = s.s_id and c_id = '03') english,
avg(s.s_score) avg_score
from score s
group by s.s_id
order by avg_score desc

  43. 查询各科成绩最高分、最低分和平均分：

    以如下形式显示： 课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 （及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90)

    select s.c_id,c.c_name, max(s.s_score), min(s.s_score), avg(s.s_score),
  sum(case when s.s_score >= 60 then 1 else 0 end)/
  sum(case when s.s_score then 1 else 0 end) a,
  sum(case when s.s_score between 70 and 80 then 1 else 0 end)/
  sum(case when s.s_score then 1 else 0 end) b,
  sum(case when s.s_score between 80 and 90 then 1 else 0 end)/
  sum(case when s.s_score then 1 else 0 end) c,
  sum(case when s.s_score >= 90 then 1 else 0 end)/
  sum(case when s.s_score then 1 else 0 end) d
from score s
left join 
(select *
from course) c
on s.c_id = c.c_id
group by s.c_id
#-----------注意标注代码的异同点-----------
select s.c_id,c.c_name, max(s.s_score), min(s.s_score), avg(s.s_score),
  sum(case when s.s_score >= 60 then 1 else 0 end)/
  sum(case when s.s_score then 1 else 0 end) a,
  sum(case when s.s_score between 70 and 80 then 1 else 0 end)/
  sum(case when s.s_score then 1 else 0 end) b,
  sum(case when s.s_score between 80 and 90 then 1 else 0 end)/
  sum(case when s.s_score then 1 else 0 end) c,
  sum(case when s.s_score >= 90 then 1 else 0 end)/
  sum(case when s.s_score then 1 else 0 end) d
from score s
left join 
course c
on s.c_id = c.c_id
group by s.c_id

  44. 按各科成绩进行排序，并显示排名

    set @rang := 0;
select *, @rang := @rang + 1
from score
order by s_score desc

  45. 查询学生的总成绩并进行排名

    set @rang := 0;
select a.s_id, a.sum_score, @rang := @rang + 1
from 
(select s_id, sum(s_score) sum_score from score
group by s_id
order by sum_score desc) a

  46. 查询不同老师所教不同课程平均分从高到低显示

    select s.s_id, t.t_name, s.c_id, avg(s.s_score) avg_score
from score s left outer join course c
on s.c_id = c.c_id
left outer join teacher t
on c.t_id = t.t_id
group by s.c_id
order by avg(s.s_score) desc

  47. 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

    set @one := 0;
set @two := 0;
set @three := 0;
select s.s_id, st.s_name, st.s_birth, st.s_sex, s.rang, s.s_score
from
(select s_id, c_id, s_score, @one :=@one + 1 rang
from score
where c_id = '01'
order by s_score desc) s
join student st
on s.s_id = st.s_id
where s.rang = 2 or s.rang = 3
union all
select s.s_id, st.s_name, st.s_birth, st.s_sex, s.rang, s.s_score
from
(select s_id, c_id, s_score, @two :=@two + 1 rang
from score
where c_id = '02'
order by s_score desc) s
join student st
on s.s_id = st.s_id
where s.rang = 2 or s.rang = 3
union all
select s.s_id, st.s_name, st.s_birth, st.s_sex, s.rang, s.s_score
from
(select s_id, c_id, s_score, @three :=@three + 1 rang
from score
where c_id = '03'
order by s_score desc) s
join student st
on s.s_id = st.s_id
where s.rang = 2 or s.rang = 3

  48. 统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

    select a.c_id, co.c_name, a.A, a.p,b.B, b.p,c.C,c.p,d.D,d.p from
(select c_id, sum(case when s_score > 85 and s_score <= 100 then 1 else 0 end) A,
100 * sum(case when s_score > 85 and s_score <= 100 then 1 else 0 end) / count(*) p
from score
group by c_id) a
left join
(select c_id, sum(case when s_score > 70 and s_score <= 85 then 1 else 0 end) B,
100 * sum(case when s_score > 70 and s_score <= 85 then 1 else 0 end) / count(*) p
from score
group by c_id) b
on a.c_id = b.c_id
left join
(select c_id, sum(case when s_score > 60 and s_score <= 70 then 1 else 0 end) C,
100 * sum(case when s_score > 60 and s_score <= 70 then 1 else 0 end) / count(*) p
from score
group by c_id) c
on a.c_id = c.c_id
left join
(select c_id, sum(case when s_score > 0 and s_score <= 60 then 1 else 0 end) D,
100 * sum(case when s_score > 0 and s_score <= 60 then 1 else 0 end) / count(*) p
from score
group by c_id) d
on a.c_id = d.c_id
left join
(select * from course) co
on a.c_id = co.c_id

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

    #-----------形式一-----------
set @i = 0;
set @j = 0;
set @avg_s = 0;
select s.s_id, 
@i := @i + 1 '不保留空缺排名',
@j := (case when @avg_s = s.avg_score then @j else @i end) '保留空缺排名',
@avg_s := s.avg_score '平均分',
s.avg_score
from
(select s_id, avg(s_score) avg_score from score
group by s_id
order by avg_score desc)s

#-----------形式二-----------
select a.s_id,
@i:=@i+1 as '不保留空缺排名',
@k:=(case when @avg_score=a.avg_s then @k else @i end) as '保留空缺排名',
@avg_score:=avg_s as '平均分'
from (select s_id,ROUND(AVG(s_score),2) as avg_s from score GROUP BY s_id ORDER BY avg_s DESC)a,(select @avg_score:=0,@i:=10,@k:=0)b;

  50. 查询各科成绩前三名的记录

    #-- 1.选出sc表比s表成绩大的所有组 #-- 2.选出比当前id成绩大的 小于三个的

    select * from 
score s left join score sc
on s.c_id = sc.c_id and s.s_score < sc.s_score
group by s.s_id, s.c_id having count(sc.s_id) < 3
order by s.c_id,s.s_score desc
上一篇 linux 基础
目录
下一篇 周易阅读准备