MySQL 50 题。
数据库。
CREATE SCHEMA ` new_schema` DEFAULT CHARACTER SET utf8mb4 ;
Operation failed: There was an error while applying the SQL script to the database .
Executing:
CREATE SCHEMA ` new_schema` DEFAULT CHARACTER SET utf8mb4 ;
ERROR 1044 : Access denied for user 'lyfgeek' @'%' to database 'new_schema'
SQL Statement:
CREATE SCHEMA ` new_schema` DEFAULT CHARACTER SET utf8mb4
CREATE SCHEMA ` mysql_fifty` DEFAULT CHARACTER SET utf8 ;
CREATE TABLE ` lyfgeek` . ` student`
(
` id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键 id。' ,
` s_id` VARCHAR ( 45 ) NOT NULL DEFAULT '' COMMENT '学生 id。' ,
` s_name` VARCHAR ( 45 ) NOT NULL DEFAULT '' COMMENT '姓名。' ,
` s_birth` VARCHAR ( 45 ) NOT NULL DEFAULT '' COMMENT '出生年月。' ,
` s_sex` VARCHAR ( 45 ) NOT NULL DEFAULT '' COMMENT '性别。' ,
` db_insert_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'db_insert_time。' ,
` db_update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'db_update_time。' ,
` db_deleted` INT NOT NULL DEFAULT 0 COMMENT 'db_deleted。' ,
PRIMARY KEY ( ` id` , ` s_id` ) ,
UNIQUE INDEX ` s_id_UNIQUE` ( ` s_id` ASC ) VISIBLE
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COMMENT = '学生。' ;
CREATE TABLE ` lyfgeek` . ` course`
(
` id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键 id。' ,
` c_id` VARCHAR ( 45 ) NOT NULL DEFAULT '' COMMENT '课程 id。' ,
` c_name` VARCHAR ( 45 ) NOT NULL DEFAULT '' COMMENT '课程名称。' ,
` t_id` VARCHAR ( 45 ) NOT NULL DEFAULT '' COMMENT '教师 id。' ,
` db_insert_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'db_insert_time。' ,
` db_update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'db_update_time。' ,
` db_deleted` INT NOT NULL DEFAULT 0 COMMENT 'db_deleted。' ,
PRIMARY KEY ( ` id` ) ,
UNIQUE INDEX ` c_id_UNIQUE` ( ` c_id` ASC ) VISIBLE
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COMMENT = '成绩。' ;
CREATE TABLE ` lyfgeek` . ` teacher`
(
` id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键 id。' ,
` t_id` VARCHAR ( 45 ) NOT NULL DEFAULT '' COMMENT '教师 id。' ,
` t_name` VARCHAR ( 45 ) NOT NULL DEFAULT '' COMMENT '姓名。' ,
` db_insert_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'db_insert_time。' ,
` db_update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'db_update_time。' ,
` db_deleted` INT NOT NULL DEFAULT 0 COMMENT 'db_deleted。' ,
PRIMARY KEY ( ` id` , ` t_id` ) ,
UNIQUE INDEX ` t_id_UNIQUE` ( ` t_id` ASC ) VISIBLE
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COMMENT = '教师。' ;
CREATE TABLE ` lyfgeek` . ` score`
(
` id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键 id。' ,
` s_id` VARCHAR ( 45 ) NOT NULL DEFAULT '' COMMENT '学生 id。' ,
` c_id` VARCHAR ( 45 ) NOT NULL DEFAULT '' COMMENT '课程 id。' ,
` s_score` INT NULL DEFAULT NULL COMMENT '成绩。' ,
` db_insert_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'db_insert_time。' ,
` db_update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'db_update_time。' ,
` db_deleted` INT NOT NULL DEFAULT 0 COMMENT 'db_deleted。' ,
PRIMARY KEY ( ` id` )
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COMMENT = '成绩。' ;
INSERT INTO ` lyfgeek` . ` student` ( ` s_id` , ` s_name` , ` s_birth` , ` s_sex` )
VALUES ( '01' , '学生 1' , '2021-01-01' , '男' ) ;
INSERT INTO ` lyfgeek` . ` student` ( ` s_id` , ` s_name` , ` s_birth` , ` s_sex` )
VALUES ( '02' , '学生 2' , '2022-02-02' , '男' ) ;
INSERT INTO ` lyfgeek` . ` student` ( ` s_id` , ` s_name` , ` s_birth` , ` s_sex` )
VALUES ( '03' , '学生 3' , '2023-03-03' , '男' ) ;
INSERT INTO ` lyfgeek` . ` student` ( ` s_id` , ` s_name` , ` s_birth` , ` s_sex` )
VALUES ( '04' , '学生 4' , '2024-04-04' , '男' ) ;
INSERT INTO ` lyfgeek` . ` student` ( ` s_id` , ` s_name` , ` s_birth` , ` s_sex` )
VALUES ( '05' , '学生 5' , '2025-05-05' , '女' ) ;
INSERT INTO ` lyfgeek` . ` student` ( ` s_id` , ` s_name` , ` s_birth` , ` s_sex` )
VALUES ( '06' , '学生 6' , '2026-06-06' , '女' ) ;
INSERT INTO ` lyfgeek` . ` student` ( ` s_id` , ` s_name` , ` s_birth` , ` s_sex` )
VALUES ( '07' , '学生 7' , '2027-07-07' , '女' ) ;
INSERT INTO ` lyfgeek` . ` student` ( ` s_id` , ` s_name` , ` s_birth` , ` s_sex` )
VALUES ( '08' , '学生 8' , '2028-08-08' , '女' ) ;
INSERT INTO ` lyfgeek` . ` course` ( ` c_id` , ` c_name` , ` t_id` )
VALUES ( '01' , '语文' , '02' ) ;
INSERT INTO ` lyfgeek` . ` course` ( ` c_id` , ` c_name` , ` t_id` )
VALUES ( '02' , '数学' , '01' ) ;
INSERT INTO ` lyfgeek` . ` course` ( ` c_id` , ` c_name` , ` t_id` )
VALUES ( '03' , '英语' , '03' ) ;
INSERT INTO ` lyfgeek` . ` teacher` ( ` t_id` , ` t_name` )
VALUES ( '01' , '老师 1' ) ;
INSERT INTO ` lyfgeek` . ` teacher` ( ` t_id` , ` t_name` )
VALUES ( '02' , '老师 2' ) ;
INSERT INTO ` lyfgeek` . ` teacher` ( ` t_id` , ` t_name` )
VALUES ( '03' , '老师 3' ) ;
INSERT INTO ` lyfgeek` . ` score` ( ` s_id` , ` c_id` , ` s_score` )
VALUES ( '01' , '01' , '80' ) ;
INSERT INTO ` lyfgeek` . ` score` ( ` s_id` , ` c_id` , ` s_score` )
VALUES ( '01' , '02' , '90' ) ;
INSERT INTO ` lyfgeek` . ` score` ( ` s_id` , ` c_id` , ` s_score` )
VALUES ( '01' , '03' , '99' ) ;
INSERT INTO ` lyfgeek` . ` score` ( ` s_id` , ` c_id` , ` s_score` )
VALUES ( '02' , '01' , '70' ) ;
INSERT INTO ` lyfgeek` . ` score` ( ` s_id` , ` c_id` , ` s_score` )
VALUES ( '02' , '02' , '60' ) ;
INSERT INTO ` lyfgeek` . ` score` ( ` s_id` , ` c_id` , ` s_score` )
VALUES ( '02' , '03' , '80' ) ;
INSERT INTO ` lyfgeek` . ` score` ( ` s_id` , ` c_id` , ` s_score` )
VALUES ( '03' , '01' , '80' ) ;
INSERT INTO ` lyfgeek` . ` score` ( ` s_id` , ` c_id` , ` s_score` )
VALUES ( '03' , '02' , '80' ) ;
INSERT INTO ` lyfgeek` . ` score` ( ` s_id` , ` c_id` , ` s_score` )
VALUES ( '03' , '03' , '80' ) ;
INSERT INTO ` lyfgeek` . ` score` ( ` s_id` , ` c_id` , ` s_score` )
VALUES ( '04' , '01' , '50' ) ;
INSERT INTO ` lyfgeek` . ` score` ( ` s_id` , ` c_id` , ` s_score` )
VALUES ( '04' , '02' , '30' ) ;
INSERT INTO ` lyfgeek` . ` score` ( ` s_id` , ` c_id` , ` s_score` )
VALUES ( '04' , '03' , '20' ) ;
INSERT INTO ` lyfgeek` . ` score` ( ` s_id` , ` c_id` , ` s_score` )
VALUES ( '05' , '01' , '76' ) ;
INSERT INTO ` lyfgeek` . ` score` ( ` s_id` , ` c_id` , ` s_score` )
VALUES ( '05' , '02' , '87' ) ;
INSERT INTO ` lyfgeek` . ` score` ( ` s_id` , ` c_id` , ` s_score` )
VALUES ( '06' , '01' , '31' ) ;
INSERT INTO ` lyfgeek` . ` score` ( ` s_id` , ` c_id` , ` s_score` )
VALUES ( '06' , '03' , '34' ) ;
INSERT INTO ` lyfgeek` . ` score` ( ` s_id` , ` c_id` , ` s_score` )
VALUES ( '07' , '02' , '89' ) ;
INSERT INTO ` lyfgeek` . ` score` ( ` s_id` , ` c_id` , ` s_score` )
VALUES ( '07' , '03' , '98' ) ;
sql。
SELECT st. * ,
t. s01,
t. s02
FROM ( SELECT sc. ` s_id` ,
MAX ( CASE
WHEN sc. ` c_id` = '01' THEN sc. ` s_score`
END ) s01,
MAX ( CASE
WHEN sc. ` c_id` = '02' THEN sc. ` s_score`
END ) s02
FROM ` score` sc
GROUP BY sc. ` s_id` ) t,
` student` st
WHERE t. s01 > t. s02
AND t. ` s_id` = st. ` s_id` ;
SELECT st. * ,
sc1. ` s_score` ,
sc2. ` s_score`
FROM ` student` st,
` score` sc1,
` score` sc2
WHERE st. ` s_id` = sc1. ` s_id`
AND sc1. ` s_id` = sc2. ` s_id`
AND sc1. ` c_id` = '01'
AND sc2. ` c_id` = '02'
AND sc1. ` s_score` > sc2. ` s_score` ;
SELECT ` student` . * ,
sc1. ` s_score` ,
sc2. ` s_score`
FROM ` student`
INNER JOIN
` score` sc1 ON ` student` . ` s_id` = sc1. ` s_id`
AND sc1. ` c_id` = '01'
INNER JOIN
` score` sc2 ON sc1. ` s_id` = sc2. ` s_id`
AND sc2. ` c_id` = '02'
WHERE sc1. ` s_score` < sc2. ` s_score`
OR sc1. ` s_score` IS NULL ;
SELECT sc. ` s_id` ,
( SELECT ` s_name`
FROM ` student` st
WHERE st. ` s_id` = sc. ` s_id` ) AS s_name,
AVG ( sc. ` s_score` ) avg_score
FROM ` score` sc
GROUP BY sc. ` s_id`
HAVING avg_score >= 60 ;
SELECT st. ` s_id` ,
st. ` s_name` ,
AVG ( sc. ` s_score` )
FROM ` student` st,
` score` sc
WHERE st. ` s_id` = sc. ` s_id`
GROUP BY st. ` s_id`
HAVING AVG ( sc. ` s_score` >= 60 ) ;
SELECT st. ` s_id` ,
st. ` s_name` ,
AVG ( sc. ` s_score` ) AS avg_score
FROM ` student` st,
` score` sc
WHERE st. ` s_id` = sc. ` s_id`
GROUP BY sc. ` s_id`
HAVING AVG ( sc. ` s_score` ) < 60
UNION
SELECT st. ` s_id` ,
st. ` s_name` ,
0 AS avg_score
FROM ` student` st
WHERE st. ` s_id` NOT IN ( SELECT DISTINCT ` s_id`
FROM ` score` ) ;
SELECT st. ` s_id` ,
st. ` s_name` ,
COUNT ( sc. s_id) AS sum_course,
IFNULL( SUM ( sc. ` s_score` ) , 0 ) AS sum_score
FROM ` student` st
LEFT OUTER JOIN
` score` sc ON st. ` s_id` = sc. ` s_id`
GROUP BY st. ` s_id` ;
SELECT COUNT ( ` t_id` )
FROM ` teacher`
WHERE ` t_name` LIKE '李%' ;
SELECT st. *
FROM ` student` st
JOIN
` score` sc ON st. ` s_id` = sc. ` s_id`
WHERE sc. ` c_id` IN ( SELECT ` c_id`
FROM ` course`
WHERE ` t_id` = ( SELECT ` t_id`
FROM ` teacher`
WHERE ` t_name` = '老师 1' ) ) ;
SELECT st. *
FROM ` student` st
INNER JOIN
` score` sc ON st. ` s_id` = sc. ` s_id`
WHERE sc. ` c_id` IN ( SELECT ` c_id`
FROM ` course` c
INNER JOIN
` teacher` t ON c. ` t_id` = t. ` t_id`
WHERE ` t` . ` t_name` = '老师 1' ) ;
SELECT *
FROM ` student` st
WHERE st. s_id NOT IN ( SELECT st. ` s_id`
FROM ` student` st
JOIN
` score` sc ON st. ` s_id` = sc. ` s_id`
WHERE sc. ` c_id` IN ( SELECT ` c_id`
FROM ` course`
WHERE ` t_id` = ( SELECT ` t_id`
FROM ` teacher`
WHERE ` t_name` = '老师 1' ) ) ) ;
SELECT *
FROM ` student`
WHERE ` s_id` NOT IN ( SELECT sc. ` s_id`
FROM ` teacher` t,
` score` sc,
` course` c
WHERE t. ` t_id` = c. ` t_id`
AND c. ` c_id` = sc. ` c_id`
AND t. ` t_name` = '老师 1' ) ;
SELECT *
FROM ` student`
WHERE NOT EXISTS ( SELECT 1
FROM ( SELECT sc. ` s_id`
FROM ` teacher` t,
` score` sc,
` course` c
WHERE t. ` t_id` = c. ` t_id`
AND c. ` c_id` = sc. ` c_id`
AND t. ` t_name` = '老师 1' ) t
WHERE t. s_id = student. s_id) ;
SELECT st. *
FROM ` student` st,
` score` sc1,
` score` sc2
WHERE st. ` s_id` = sc1. ` s_id`
AND st. ` s_id` = sc2. ` s_id`
AND sc1. ` c_id` = '01'
AND sc2. ` c_id` = '02' ;
SELECT st. *
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' ) ;
SELECT st. *
FROM ` student` st,
( SELECT ` s_id` ,
MAX ( IF ( ` c_id` = '01' , ` s_score` , NULL ) ) s01,
MAX ( IF ( ` c_id` = '02' , ` s_score` , NULL ) ) s02
FROM ` score`
GROUP BY ` s_id` ) t
WHERE t. ` s_id` = st. ` s_id`
AND t. s01 IS NOT NULL
AND t. s02 IS NULL ;
SELECT st. * ,
COUNT ( sc. ` c_id` ) count_sc
FROM ` student` st
LEFT JOIN
` score` sc ON st. ` s_id` = sc. ` s_id`
GROUP BY st. ` s_id`
HAVING COUNT ( sc. ` c_id` ) < ( SELECT COUNT ( ` c_id` )
FROM ` course` ) ;
SELECT st. *
FROM ` student` st
WHERE st. ` s_id` IN ( SELECT ` s_id`
FROM ` score`
WHERE ` s_id` NOT IN ( SELECT sc1. ` s_id`
FROM ` score` sc1
JOIN
` score` sc2 ON sc1. ` s_id` = sc2. ` s_id`
AND sc2. ` c_id` = '02'
JOIN
` score` sc3 ON sc1. ` s_id` = sc3. ` s_id`
AND sc3. ` c_id` = '03'
WHERE sc1. ` c_id` = '01' ) ) ;
SELECT *
FROM ` student`
WHERE ` s_id` IN ( SELECT DISTINCT ` s_id`
FROM ` score`
WHERE ` c_id` IN ( SELECT ` c_id`
FROM ` score`
WHERE ` s_id` = '01' ) )
GROUP BY 1 , 2 , 3 , 4 ;
CREATE TABLE s01_sc_temp AS
SELECT t1. * , sc. ` c_id` cid2
FROM ( SELECT st. * ,
t2. ` c_id`
FROM ` student` st,
( SELECT ` c_id`
FROM ` score`
WHERE ` s_id` = '01' ) t2) t1
LEFT JOIN
` score` sc ON t1. ` s_id` = sc. ` s_id`
AND t1. ` c_id` = sc. ` c_id`
UNION
SELECT t. * ,
sc. ` c_id` cid2
FROM ( SELECT st. * ,
b. ` c_id`
FROM ` student` st,
( SELECT ` c_id`
FROM ` score`
WHERE ` s_id` = '01' ) b) t
RIGHT JOIN
` score` sc ON t. ` s_id` = sc. ` s_id`
AND t. ` c_id` = sc. ` c_id` ;
SELECT *
FROM ` student`
WHERE ` s_id` NOT IN ( SELECT ` s_id`
FROM s01_s_temp
WHERE cid2 IS NULL
OR ` c_id` IS NULL )
AND ` s_id` != '01' ;
SELECT *
FROM ` student`
WHERE ` s_id` IN ( SELECT DISTINCT ` s_id`
FROM ` score`
WHERE ` s_id` != '01'
AND ` c_id` IN ( SELECT ` c_id`
FROM ` score`
WHERE ` s_id` = '01' )
GROUP BY ` s_id`
HAVING COUNT ( 1 ) = ( SELECT COUNT ( 1 )
FROM ` score`
WHERE ` s_id` = '01' ) ) ;
SELECT st. ` s_name`
FROM ` student` st
WHERE st. ` s_id` NOT IN ( SELECT ` s_id`
FROM ` score`
WHERE ` c_id` = ( SELECT ` c_id`
FROM ` course`
WHERE ` t_id` = ( SELECT ` t_id`
FROM ` teacher`
WHERE ` t_name` = '老师 1' ) )
GROUP BY s_id) ;
SELECT st. ` s_id` ,
st. ` s_name` ,
AVG ( sc. ` s_score` ) avg_score
FROM ` student` st
LEFT JOIN
` score` sc ON st. ` s_id` = sc. ` s_id`
GROUP BY st. ` s_id`
HAVING SUM ( IF ( sc. ` s_score` >= 60 , 0 , 1 ) ) >= 2 ;
SELECT st. ` s_id` ,
st. ` s_name` ,
AVG ( sc. ` s_score` ) avg_score
FROM ` student` st
LEFT JOIN
` score` sc ON st. ` s_id` = sc. ` s_id`
GROUP BY st. ` s_id`
HAVING SUM ( IF ( sc. ` s_score` >= 60 , 0 , 1 ) ) >= 2 ;
SELECT st. ` s_id` ,
st. ` s_name` ,
ROUND ( AVG ( sc. ` s_score` ) )
FROM student st
LEFT JOIN
score sc ON st. ` s_id` = sc. ` s_id`
WHERE st. ` s_id` IN ( SELECT ` s_id`
FROM ` score`
WHERE ` s_score` < 60
GROUP BY ` s_id`
HAVING COUNT ( 1 ) >= 2 )
GROUP BY st. ` s_id` , st. ` s_name` ;
SELECT st. * ,
sc. ` s_score`
FROM ` score` sc
RIGHT JOIN
` student` st ON sc. ` s_id` = st. ` s_id`
WHERE sc. ` c_id` = '01'
AND sc. ` s_score` < 60
ORDER BY ` s_score` DESC ;
SELECT st. * ,
sc. ` c_id` ,
sc. ` s_score`
FROM ` student` st,
` score` sc
WHERE st. ` s_id` = sc. ` s_id`
AND sc. ` c_id` = '01'
AND sc. ` s_score` < 60
ORDER BY sc. ` s_score` DESC ;
SELECT sc. ` s_id` ,
( SELECT ` s_score`
FROM ` score`
WHERE ` s_id` = sc. ` s_id`
AND ` c_id` = '01' ) AS 语文,
( SELECT ` s_score`
FROM ` score`
WHERE ` s_id` = sc. ` s_id`
AND ` c_id` = '02' ) AS 数学,
( SELECT ` s_score`
FROM ` score`
WHERE ` s_id` = sc. ` s_id`
AND ` c_id` = '03' ) AS 英语,
ROUND ( AVG ( ` s_score` ) , 2 ) AS 平均分
FROM ` score` sc
GROUP BY sc. ` s_id`
ORDER BY 平均分 DESC ;
SELECT sc. ` c_id` ,
c. ` c_name` ,
MAX ( ` s_score` ) ,
MIN ( ` s_score` ) ,
ROUND ( AVG ( ` s_score` ) , 2 ) ,
ROUND ( 100 * ( SUM ( IF ( sc. ` s_score` >= 60 , 1 , 0 ) ) / SUM ( IF ( sc. ` s_score` , 1 , 0 ) ) ) ,
2 ) AS 及格率,
ROUND ( 100 * ( SUM ( IF ( sc. ` s_score` >= 70 AND sc. ` s_score` <= 80 , 1 , 0 ) ) / SUM ( IF ( sc. ` s_score` , 1 , 0 ) ) ) ,
2 ) AS 中等率,
ROUND ( 100 * ( SUM ( IF ( sc. ` s_score` >= 80 AND sc. ` s_score` <= 90 , 1 , 0 ) ) / SUM ( IF ( sc. ` s_score` , 1 , 0 ) ) ) ,
2 ) AS 优良率,
ROUND ( 100 * ( SUM ( IF ( sc. ` s_score` >= 90 , 1 , 0 ) ) / SUM ( IF ( sc. ` s_score` , 1 , 0 ) ) ) ,
2 ) AS 优秀率
FROM ` score` sc
LEFT JOIN
` course` c ON sc. ` c_id` = c. ` c_id`
GROUP BY sc. ` c_id` , c. ` c_name` ;
SELECT sc1. ` c_id` ,
sc1. ` s_id` ,
sc1. ` s_score` ,
COUNT ( sc2. ` s_score` ) + 1 AS ` rank`
FROM ` score` sc1
LEFT JOIN
` score` sc2 ON sc1. ` s_score` < sc2. ` s_score`
AND sc1. ` c_id` = sc2. ` c_id`
GROUP BY sc1. ` c_id` , sc1. ` s_id` , sc1. ` s_score`
ORDER BY sc1. ` c_id` , ` rank` ;
SELECT t1. ` s_id` ,
@i := @i + 1 AS i,
@k := ( IF ( @score = t1. sum_score, @k , @i ) ) AS ` rank` ,
@score := t1. sum_score AS score
FROM ( SELECT ` s_id` ,
SUM ( ` s_score` ) AS sum_score
FROM ` score`
GROUP BY ` s_id`
ORDER BY sum_score DESC ) t1,
( SELECT @k := 0 , @i := 0 , @score := 0 ) AS kis;
SELECT c. ` t_id` ,
t. ` t_name` ,
c. ` c_id` ,
ROUND ( AVG ( ` s_score` ) , 2 ) AS avg_score
FROM ` course` c
LEFT JOIN
` score` sc ON c. ` c_id` = sc. ` c_id`
LEFT JOIN
` teacher` t ON c. ` t_id` = t. ` t_id`
GROUP BY c. ` c_id` , c. ` t_id` , t. ` t_name`
ORDER BY avg_score DESC ;
SELECT d. * ,
si2. 排名,
si2. ` s_score` ,
si2. ` c_id`
FROM ( SELECT sc. ` s_id` ,
sc. ` s_score` ,
sc. ` c_id` ,
@i := @i + 1 AS 排名
FROM ` score` sc,
( SELECT @i := 0 ) as i2
WHERE sc. c_id = '01' ) as si2
LEFT JOIN
student d ON si2. s_id = d. s_id
WHERE 排名 BETWEEN 2 AND 3
UNION
SELECT st. * ,
sj2. 排名,
sj2. s_score,
sj2. c_id
FROM ( SELECT sc. s_id,
sc. s_score,
sc. c_id,
@j := @j + 1 AS 排名
FROM ` score` sc,
( SELECT @j := 0 ) as j2
WHERE sc. c_id = '02' ) as sj2
LEFT JOIN
` student` st ON sj2. s_id = st. s_id
WHERE 排名 BETWEEN 2 AND 3
UNION
SELECT d. * ,
s. 排名,
s. ` s_score` ,
s. ` c_id`
FROM ( SELECT sc. ` s_id` ,
sc. ` s_score` ,
sc. ` c_id` ,
@k := @k + 1 AS 排名
FROM ` score` sc,
( SELECT @k := 0 ) as k2
WHERE sc. ` c_id` = '03' ) as s
LEFT JOIN
` student` d ON s. ` s_id` = d. ` s_id`
WHERE 排名 BETWEEN 2 AND 3 ;
SELECT DISTINCT c. ` c_name` ,
sc. ` c_id` ,
t1. ` 85-100` ,
t1. 百分比,
t2. ` 70-85` ,
t2. 百分比,
t3. ` 60-70` ,
t3. 百分比,
t4. ` 0-60` ,
t4. 百分比
FROM ` score` sc
LEFT JOIN
( SELECT ` c_id` ,
SUM ( IF ( ` s_score` > 85 AND ` s_score` <= 100 , 1 , 0 ) ) AS ` 85-100` ,
ROUND ( 100 * ( SUM ( IF ( ` s_score` > 85 AND ` s_score` <= 100 , 1 , 0 ) ) / COUNT ( * ) ) , 2 ) AS 百分比
FROM ` score`
GROUP BY ` c_id` ) t1 ON sc. ` c_id` = t1. ` c_id`
LEFT JOIN
( SELECT ` c_id` ,
SUM ( IF ( ` s_score` > 70 AND ` s_score` <= 85 , 1 , 0 ) ) AS ` 70-85` ,
ROUND ( 100 * ( SUM ( IF ( ` s_score` > 70 AND ` s_score` <= 85 , 1 , 0 ) ) / COUNT ( * ) ) , 2 ) AS 百分比
FROM ` score`
GROUP BY ` c_id` ) t2 ON sc. ` c_id` = t2. ` c_id`
LEFT JOIN
( SELECT ` c_id` ,
SUM ( IF ( ` s_score` > 60 AND ` s_score` <= 70 , 1 , 0 ) ) AS ` 60-70` ,
ROUND ( 100 * ( SUM ( IF ( ` s_score` > 60 AND ` s_score` <= 70 , 1 , 0 ) ) / COUNT ( * ) ) , 2 ) AS 百分比
FROM ` score`
GROUP BY ` c_id` ) t3 ON sc. ` c_id` = t3. ` c_id`
LEFT JOIN
( SELECT ` c_id` ,
SUM ( IF ( ` s_score` >= 0 AND ` s_score` <= 60 , 1 , 0 ) ) AS ` 0-60` ,
ROUND ( 100 * ( SUM ( IF ( ` s_score` >= 0 AND ` s_score` <= 60 , 1 , 0 ) ) / COUNT ( * ) ) , 2 ) AS 百分比
FROM ` score`
GROUP BY ` c_id` ) t4 ON sc. ` c_id` = t4. ` c_id`
LEFT JOIN
` course` c ON sc. ` c_id` = c. ` c_id` ;
SELECT t. ` s_id` ,
@i := @i + 1 AS '不保留空缺排名' ,
@k := ( IF ( @avg_score = t. avg_score, @k , @i ) ) AS '保留空缺排名' ,
@avg_score := avg_score AS '平均分'
FROM ( SELECT ` s_id` ,
ROUND ( AVG ( ` s_score` ) , 2 ) AS avg_score
FROM score
GROUP BY ` s_id` ) t,
( SELECT @avg_score := 0 , @i := 0 , @k := 0 ) b;
SELECT sc1. ` s_id` ,
sc1. ` c_id` ,
sc1. ` s_score`
FROM ` score` sc1
LEFT JOIN
` score` sc2 ON sc1. ` c_id` = sc2. ` c_id`
AND sc1. ` s_score` < sc2. ` s_score`
GROUP BY sc1. ` s_id` , sc1. ` c_id` , sc1. ` s_score`
HAVING COUNT ( sc2. ` s_id` ) < 3
ORDER BY sc1. ` c_id` , sc1. ` s_score` DESC ;
SELECT ` c_id` ,
COUNT ( ` s_id` )
FROM ` score`
GROUP BY ` c_id` ;
SELECT ` s_id` ,
` s_name`
FROM ` student`
WHERE ` s_id` IN ( SELECT ` s_id`
FROM ` score`
GROUP BY ` s_id`
HAVING COUNT ( ` c_id` ) = 2 ) ;
SELECT ` s_sex` ,
COUNT ( ` s_sex` ) AS 人数
FROM student
GROUP BY s_sex;
SELECT *
FROM ` student`
WHERE ` s_name` LIKE '%风%' ;
SELECT st1. ` s_name` ,
st1. ` s_sex` ,
COUNT ( * )
FROM ` student` st1
JOIN
` student` st2 ON st1. ` s_id` != st2. ` s_id`
AND st1. ` s_name` = st2. ` s_name`
AND st1. ` s_sex` = st2. ` s_sex`
GROUP BY st1. ` s_name` , st1. ` s_sex` ;
SELECT ` s_name`
FROM ` student`
WHERE ` s_birth` LIKE '1990%' ;
SELECT ` c_id` ,
ROUND ( AVG ( ` s_score` ) , 2 ) AS avg_score
FROM ` score`
GROUP BY ` c_id`
ORDER BY avg_score DESC , ` c_id` ASC ;
SELECT sc. ` s_id` ,
st. ` s_name` ,
ROUND ( AVG ( sc. ` s_score` ) , 2 ) AS avg_score
FROM ` score` sc
LEFT JOIN
` student` st ON sc. ` s_id` = st. ` s_id`
GROUP BY ` s_id`
HAVING avg_score >= 85 ;
SELECT st. ` s_name` ,
sc. ` s_score`
FROM ` score` sc
LEFT JOIN
` student` st ON st. ` s_id` = sc. ` s_id`
WHERE sc. ` c_id` = ( SELECT ` c_id`
FROM ` course`
WHERE ` c_name` = '数学' )
AND sc. s_score < 60 ;
SELECT st. ` s_id` ,
st. ` s_name` ,
SUM ( IF ( c. ` c_name` = '语文' , sc. ` s_score` , 0 ) ) AS '语文' ,
SUM ( IF ( c. ` c_name` = '数学' , sc. ` s_score` , 0 ) ) AS '数学' ,
SUM ( IF ( c. ` c_name` = '英语' , sc. ` s_score` , 0 ) ) AS '英语' ,
SUM ( sc. ` s_score` ) AS '总分'
FROM ` student` st
LEFT JOIN
` score` sc ON st. ` s_id` = sc. ` s_id`
LEFT JOIN
` course` c ON sc. ` c_id` = c. ` c_id`
GROUP BY st. ` s_id` , st. ` s_name` ;
SELECT st. ` s_name` ,
c. ` c_name` ,
sc. ` s_score`
FROM ` course` c
LEFT JOIN
` score` sc ON c. ` c_id` = sc. ` c_id`
LEFT JOIN
` student` st ON st. ` s_id` = sc. ` s_id`
WHERE sc. ` s_score` >= 70 ;
SELECT sc. ` s_id` ,
sc. ` c_id` ,
c. ` c_name` ,
sc. ` s_score`
FROM ` score` sc
LEFT JOIN
` course` c ON sc. ` c_id` = c. ` c_id`
WHERE sc. ` s_score` < 60 ;
SELECT sc. ` s_id` ,
st. ` s_name`
FROM ` score` sc
LEFT JOIN
` student` st ON sc. ` s_id` = st. ` s_id`
WHERE sc. ` c_id` = '01'
AND sc. ` s_score` > 80 ;
SELECT COUNT ( * )
FROM ` score`
GROUP BY ` c_id` ;
SELECT ` c_id`
FROM ` course` c,
` teacher` t
WHERE c. ` t_id` = t. ` t_id`
AND t. ` t_name` = '老师 1' ;
SELECT MAX ( ` s_score` )
FROM ` score`
WHERE ` c_id` = '02' ;
SELECT st. * ,
sc. ` s_score` ,
sc. ` c_id` ,
c. ` c_name`
FROM ` student` st
LEFT JOIN
` score` sc ON st. ` s_id` = sc. ` s_id`
LEFT JOIN
` course` c ON sc. ` c_id` = c. ` c_id`
WHERE sc. ` c_id` = ( SELECT ` c_id`
FROM ` course` c,
` teacher` t
WHERE c. ` t_id` = t. ` t_id`
AND t. ` t_name` = '老师 1' )
AND sc. s_score IN ( SELECT MAX ( s_score)
FROM score
WHERE c_id = '02' ) ;
SELECT c. ` c_name` , sc. ` s_score` , st. *
FROM ` course` c,
` score` sc,
` teacher` t,
` student` st
WHERE t. ` t_id` = c. ` t_id`
AND c. ` c_id` = sc. ` c_id`
AND st. ` s_id` = sc. ` s_id`
AND t. ` t_name` = '老师 1'
AND sc. ` s_score` IN ( SELECT MAX ( ` s_score` )
FROM ` course` ,
` score` ,
` teacher` ,
` student`
WHERE ` teacher` . ` t_id` = ` course` . ` t_id`
AND ` course` . ` c_id` = ` score` . ` c_id`
AND ` student` . ` s_id` = ` score` . ` s_id`
AND ` teacher` . ` t_name` = '老师 1' ) ;
SELECT DISTINCT sc2. ` s_id` ,
sc2. ` c_id` ,
sc2. ` s_score`
FROM ` score` sc1,
` score` sc2
WHERE sc1. ` c_id` != sc2. ` c_id`
AND sc1. ` s_score` = sc2. ` s_score` ;
SELECT sc1. ` s_id` ,
sc1. ` c_id` ,
sc1. ` s_score`
FROM ` score` sc1
WHERE ( SELECT COUNT ( 1 )
FROM ` score` sc2
WHERE sc2. ` c_id` = sc1. ` c_id`
AND sc2. ` s_score` >= sc1. ` s_score` ) <= 2
ORDER BY sc1. ` c_id` ;
SELECT ` c_id` ,
COUNT ( * ) AS total
FROM ` score`
GROUP BY ` c_id`
HAVING total > 5
ORDER BY total DESC , ` c_id` ;
SELECT ` s_id` ,
COUNT ( * )
FROM ` score`
GROUP BY ` s_id`
HAVING COUNT ( * ) >= 2 ;
SELECT *
FROM ` student`
WHERE ` s_id` IN ( SELECT ` s_id`
FROM ` score`
GROUP BY ` s_id`
HAVING COUNT ( * ) = ( SELECT COUNT ( * )
FROM ` course` ) ) ;
SELECT ` s_birth` ,
( DATE_FORMAT( NOW ( ) , '%Y' ) - DATE_FORMAT( ` s_birth` , '%Y' ) -
( IF ( DATE_FORMAT( NOW ( ) , '%m%d' ) > DATE_FORMAT( ` s_birth` , '%m%d' ) ,
0 ,
1 ) ) ) AS age
FROM ` student` ;
SELECT *
FROM ` student`
WHERE WEEK( DATE_FORMAT( NOW ( ) , '%Y%m%d' ) ) = WEEK( ` s_birth` ) ;
SELECT *
FROM ` student`
WHERE YEARWEEK( ` s_birth` ) = YEARWEEK( DATE_FORMAT( NOW ( ) , '%Y%m%d' ) ) ;
SELECT WEEK( DATE_FORMAT( NOW ( ) , '%Y%m%d' ) ) ;
SELECT *
FROM ` student`
WHERE WEEK( DATE_FORMAT( NOW ( ) , '%Y%m%d' ) ) + 1 = WEEK( ` s_birth` ) ;
SELECT *
FROM ` student`
WHERE MONTH ( DATE_FORMAT( NOW ( ) , '%Y%m%d' ) ) = MONTH ( ` s_birth` ) ;
SELECT *
FROM ` student`
WHERE MONTH ( DATE_FORMAT( NOW ( ) , '%Y-%m-%d' ) ) + 1 = MONTH ( ` s_birth` ) ;
SELECT
st. * , t. s01, t. s02
FROM
( SELECT
sc. ` s_id` ,
MAX ( CASE
WHEN sc. ` c_id` = '01' THEN sc. ` s_score`
END ) s01,
MAX ( CASE
WHEN sc. ` c_id` = '02' THEN sc. ` s_score`
END ) s02
FROM
` score` sc
GROUP BY sc. ` s_id` ) t,
` student` st
WHERE
t. s01 > t. s02 AND t. ` s_id` = st. ` s_id` ;
SELECT
st. * , sc1. ` s_score` , sc2. ` s_score`
FROM
` student` st,
` score` sc1,
` score` sc2
WHERE
st. ` s_id` = sc1. ` s_id`
AND sc1. ` s_id` = sc2. ` s_id`
AND sc1. ` c_id` = '01'
AND sc2. ` c_id` = '02'
AND sc1. ` s_score` > sc2. ` s_score` ;
SELECT
` student` . * , sc1. ` s_score` , sc2. ` s_score`
FROM
` student`
INNER JOIN
` score` sc1 ON ` student` . ` s_id` = sc1. ` s_id`
AND sc1. ` c_id` = '01'
INNER JOIN
` score` sc2 ON sc1. ` s_id` = sc2. ` s_id`
AND sc2. ` c_id` = '02'
WHERE
sc1. ` s_score` < sc2. ` s_score`
OR sc1. ` s_score` IS NULL ;
SELECT
sc. ` s_id` ,
( SELECT
` s_name`
FROM
` student` st
WHERE
st. ` s_id` = sc. ` s_id` ) AS s_name,
AVG ( sc. ` s_score` ) avg_score
FROM
` score` sc
GROUP BY sc. ` s_id`
HAVING avg_score >= 60 ;
SELECT
st. ` s_id` , st. ` s_name` , AVG ( sc. ` s_score` )
FROM
` student` st,
` score` sc
WHERE
st. ` s_id` = sc. ` s_id`
GROUP BY st. ` s_id`
HAVING AVG ( sc. ` s_score` >= 60 ) ;
SELECT
st. ` s_id` , st. ` s_name` , AVG ( sc. ` s_score` ) AS avg_score
FROM
` student` st,
` score` sc
WHERE
st. ` s_id` = sc. ` s_id`
GROUP BY sc. ` s_id`
HAVING AVG ( sc. ` s_score` ) < 60
UNION SELECT
st. ` s_id` , st. ` s_name` , 0 AS avg_score
FROM
` student` st
WHERE
st. ` s_id` NOT IN ( SELECT DISTINCT
` s_id`
FROM
` score` ) ;
SELECT
st. ` s_id` ,
st. ` s_name` ,
COUNT ( sc. s_id) AS sum_course,
IFNULL( SUM ( sc. ` s_score` ) , 0 ) AS sum_score
FROM
` student` st
LEFT OUTER JOIN
` score` sc ON st. ` s_id` = sc. ` s_id`
GROUP BY st. ` s_id` ;
SELECT
COUNT ( ` t_id` )
FROM
` teacher`
WHERE
` t_name` LIKE '李%' ;
SELECT
st. *
FROM
` student` st
JOIN
` score` sc ON st. ` s_id` = sc. ` s_id`
WHERE
sc. ` c_id` IN ( SELECT
` c_id`
FROM
` course`
WHERE
` t_id` = ( SELECT
` t_id`
FROM
` teacher`
WHERE
` t_name` = '老师 1' ) ) ;
SELECT
st. *
FROM
` student` st
INNER JOIN
` score` sc ON st. ` s_id` = sc. ` s_id`
WHERE
sc. ` c_id` IN ( SELECT
` c_id`
FROM
` course` c
INNER JOIN
` teacher` t ON c. ` t_id` = t. ` t_id`
WHERE
` t` . ` t_name` = '老师 1' ) ;
SELECT
*
FROM
` student` st
WHERE
st. s_id NOT IN ( SELECT
st. ` s_id`
FROM
` student` st
JOIN
` score` sc ON st. ` s_id` = sc. ` s_id`
WHERE
sc. ` c_id` IN ( SELECT
` c_id`
FROM
` course`
WHERE
` t_id` = ( SELECT
` t_id`
FROM
` teacher`
WHERE
` t_name` = '老师 1' ) ) ) ;
SELECT
*
FROM
` student`
WHERE
` s_id` NOT IN ( SELECT
sc. ` s_id`
FROM
` teacher` t,
` score` sc,
` course` c
WHERE
t. ` t_id` = c. ` t_id`
AND c. ` c_id` = sc. ` c_id`
AND t. ` t_name` = '老师 1' ) ;
SELECT
*
FROM
` student`
WHERE
NOT EXISTS ( SELECT
1
FROM
( SELECT
sc. ` s_id`
FROM
` teacher` t, ` score` sc, ` course` c
WHERE
t. ` t_id` = c. ` t_id`
AND c. ` c_id` = sc. ` c_id`
AND t. ` t_name` = '老师 1' ) t
WHERE
t. s_id = student. s_id) ;
SELECT
st. *
FROM
` student` st,
` score` sc1,
` score` sc2
WHERE
st. ` s_id` = sc1. ` s_id`
AND st. ` s_id` = sc2. ` s_id`
AND sc1. ` c_id` = '01'
AND sc2. ` c_id` = '02' ;
SELECT
st. *
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' ) ;
SELECT
st. *
FROM
` student` st,
( SELECT
` s_id` ,
MAX ( IF ( ` c_id` = '01' , ` s_score` , NULL ) ) s01,
MAX ( IF ( ` c_id` = '02' , ` s_score` , NULL ) ) s02
FROM
` score`
GROUP BY ` s_id` ) t
WHERE
t. ` s_id` = st. ` s_id`
AND t. s01 IS NOT NULL
AND t. s02 IS NULL ;
SELECT
st. * , COUNT ( sc. ` c_id` ) count_sc
FROM
` student` st
LEFT JOIN
` score` sc ON st. ` s_id` = sc. ` s_id`
GROUP BY st. ` s_id`
HAVING COUNT ( sc. ` c_id` ) < ( SELECT
COUNT ( ` c_id` )
FROM
` course` ) ;
SELECT
st. *
FROM
` student` st
WHERE
st. ` s_id` IN ( SELECT
` s_id`
FROM
` score`
WHERE
` s_id` NOT IN ( SELECT
sc1. ` s_id`
FROM
` score` sc1
JOIN
` score` sc2 ON sc1. ` s_id` = sc2. ` s_id`
AND sc2. ` c_id` = '02'
JOIN
` score` sc3 ON sc1. ` s_id` = sc3. ` s_id`
AND sc3. ` c_id` = '03'
WHERE
sc1. ` c_id` = '01' ) ) ;
SELECT
*
FROM
` student`
WHERE
` s_id` IN ( SELECT DISTINCT
` s_id`
FROM
` score`
WHERE
` c_id` IN ( SELECT
` c_id`
FROM
` score`
WHERE
` s_id` = '01' ) )
GROUP BY 1 , 2 , 3 , 4 ;
CREATE TABLE s01_sc_temp AS SELECT t1. * , sc. ` c_id` cid2 FROM
( SELECT
st. * , t2. ` c_id`
FROM
` student` st, ( SELECT
` c_id`
FROM
` score`
WHERE
` s_id` = '01' ) t2) t1
LEFT JOIN
` score` sc ON t1. ` s_id` = sc. ` s_id`
AND t1. ` c_id` = sc. ` c_id`
UNION SELECT
t. * , sc. ` c_id` cid2
FROM
( SELECT
st. * , b. ` c_id`
FROM
` student` st, ( SELECT
` c_id`
FROM
` score`
WHERE
` s_id` = '01' ) b) t
RIGHT JOIN
` score` sc ON t. ` s_id` = sc. ` s_id`
AND t. ` c_id` = sc. ` c_id` ;
SELECT
*
FROM
` student`
WHERE
` s_id` NOT IN ( SELECT
` s_id`
FROM
s01_s_temp
WHERE
cid2 IS NULL OR ` c_id` IS NULL )
AND ` s_id` != '01' ;
SELECT
*
FROM
` student`
WHERE
` s_id` IN ( SELECT DISTINCT
` s_id`
FROM
` score`
WHERE
` s_id` != '01'
AND ` c_id` IN ( SELECT
` c_id`
FROM
` score`
WHERE
` s_id` = '01' )
GROUP BY ` s_id`
HAVING COUNT ( 1 ) = ( SELECT
COUNT ( 1 )
FROM
` score`
WHERE
` s_id` = '01' ) ) ;
SELECT
st. ` s_name`
FROM
` student` st
WHERE
st. ` s_id` NOT IN ( SELECT
` s_id`
FROM
` score`
WHERE
` c_id` = ( SELECT
` c_id`
FROM
` course`
WHERE
` t_id` = ( SELECT
` t_id`
FROM
` teacher`
WHERE
` t_name` = '老师 1' ) )
GROUP BY s_id) ;
SELECT
st. ` s_id` , st. ` s_name` , AVG ( sc. ` s_score` ) avg_score
FROM
` student` st
LEFT JOIN
` score` sc ON st. ` s_id` = sc. ` s_id`
GROUP BY st. ` s_id`
HAVING SUM ( IF ( sc. ` s_score` >= 60 , 0 , 1 ) ) >= 2 ;
SELECT
st. ` s_id` , st. ` s_name` , AVG ( sc. ` s_score` ) avg_score
FROM
` student` st
LEFT JOIN
` score` sc ON st. ` s_id` = sc. ` s_id`
GROUP BY st. ` s_id`
HAVING SUM ( IF ( sc. ` s_score` >= 60 , 0 , 1 ) ) >= 2 ;
SELECT
st. ` s_id` , st. ` s_name` , ROUND ( AVG ( sc. ` s_score` ) )
FROM
student st
LEFT JOIN
score sc ON st. ` s_id` = sc. ` s_id`
WHERE
st. ` s_id` IN ( SELECT
` s_id`
FROM
` score`
WHERE
` s_score` < 60
GROUP BY ` s_id`
HAVING COUNT ( 1 ) >= 2 )
GROUP BY st. ` s_id` , st. ` s_name` ;
SELECT
st. * , sc. ` s_score`
FROM
` score` sc
RIGHT JOIN
` student` st ON sc. ` s_id` = st. ` s_id`
WHERE
sc. ` c_id` = '01' AND sc. ` s_score` < 60
ORDER BY ` s_score` DESC ;
SELECT
st. * , sc. ` c_id` , sc. ` s_score`
FROM
` student` st,
` score` sc
WHERE
st. ` s_id` = sc. ` s_id`
AND sc. ` c_id` = '01'
AND sc. ` s_score` < 60
ORDER BY sc. ` s_score` DESC ;
SELECT sc. ` s_id` ,
( SELECT ` s_score`
FROM ` score`
WHERE ` s_id` = sc. ` s_id`
AND ` c_id` = '01' ) AS 语文,
( SELECT ` s_score`
FROM ` score`
WHERE ` s_id` = sc. ` s_id`
AND ` c_id` = '02' ) AS 数学,
( SELECT ` s_score`
FROM ` score`
WHERE ` s_id` = sc. ` s_id`
AND ` c_id` = '03' ) AS 英语,
ROUND ( AVG ( ` s_score` ) , 2 ) AS 平均分
FROM ` score` sc
GROUP BY sc. ` s_id`
ORDER BY 平均分 DESC ;
SELECT sc. ` c_id` ,
c. ` c_name` ,
MAX ( ` s_score` ) ,
MIN ( ` s_score` ) ,
ROUND ( AVG ( ` s_score` ) , 2 ) ,
ROUND ( 100 * ( SUM ( IF ( sc. ` s_score` >= 60 , 1 , 0 ) ) / SUM ( IF ( sc. ` s_score` , 1 , 0 ) ) ) ,
2 ) AS 及格率,
ROUND ( 100 * ( SUM ( IF ( sc. ` s_score` >= 70 AND sc. ` s_score` <= 80 , 1 , 0 ) ) / SUM ( IF ( sc. ` s_score` , 1 , 0 ) ) ) ,
2 ) AS 中等率,
ROUND ( 100 * ( SUM ( IF ( sc. ` s_score` >= 80 AND sc. ` s_score` <= 90 , 1 , 0 ) ) / SUM ( IF ( sc. ` s_score` , 1 , 0 ) ) ) ,
2 ) AS 优良率,
ROUND ( 100 * ( SUM ( IF ( sc. ` s_score` >= 90 , 1 , 0 ) ) / SUM ( IF ( sc. ` s_score` , 1 , 0 ) ) ) ,
2 ) AS 优秀率
FROM ` score` sc
LEFT JOIN
` course` c ON sc. ` c_id` = c. ` c_id`
GROUP BY sc. ` c_id` , c. ` c_name` ;
SELECT
sc1. ` c_id` ,
sc1. ` s_id` ,
sc1. ` s_score` ,
COUNT ( sc2. ` s_score` ) + 1 AS ` rank`
FROM
` score` sc1
LEFT JOIN
` score` sc2 ON sc1. ` s_score` < sc2. ` s_score`
AND sc1. ` c_id` = sc2. ` c_id`
GROUP BY sc1. ` c_id` , sc1. ` s_id` , sc1. ` s_score`
ORDER BY sc1. ` c_id` , ` rank` ;
SELECT
t1. ` s_id` ,
@i := @i + 1 AS i,
@k := ( IF ( @score = t1. sum_score, @k , @i ) ) AS ` rank` ,
@score := t1. sum_score AS score
FROM
( SELECT
` s_id` , SUM ( ` s_score` ) AS sum_score
FROM
` score`
GROUP BY ` s_id`
ORDER BY sum_score DESC ) t1,
( SELECT @k := 0 , @i := 0 , @score := 0 ) AS kis;
SELECT
c. ` t_id` ,
t. ` t_name` ,
c. ` c_id` ,
ROUND ( AVG ( ` s_score` ) , 2 ) AS avg_score
FROM
` course` c
LEFT JOIN
` score` sc ON c. ` c_id` = sc. ` c_id`
LEFT JOIN
` teacher` t ON c. ` t_id` = t. ` t_id`
GROUP BY c. ` c_id` , c. ` t_id` , t. ` t_name`
ORDER BY avg_score DESC ;
SELECT d. * ,
si2. 排名,
si2. ` s_score` ,
si2. ` c_id`
FROM ( SELECT sc. ` s_id` ,
sc. ` s_score` ,
sc. ` c_id` ,
@i := @i + 1 AS 排名
FROM ` score` sc,
( SELECT @i := 0 ) as i2
WHERE sc. c_id = '01' ) as si2
LEFT JOIN
student d ON si2. s_id = d. s_id
WHERE 排名 BETWEEN 2 AND 3
UNION
SELECT st. * ,
sj2. 排名,
sj2. s_score,
sj2. c_id
FROM ( SELECT sc. s_id,
sc. s_score,
sc. c_id,
@j := @j + 1 AS 排名
FROM ` score` sc,
( SELECT @j := 0 ) as j2
WHERE sc. c_id = '02' ) as sj2
LEFT JOIN
` student` st ON sj2. s_id = st. s_id
WHERE 排名 BETWEEN 2 AND 3
UNION
SELECT d. * ,
s. 排名,
s. ` s_score` ,
s. ` c_id`
FROM ( SELECT sc. ` s_id` ,
sc. ` s_score` ,
sc. ` c_id` ,
@k := @k + 1 AS 排名
FROM ` score` sc,
( SELECT @k := 0 ) as k2
WHERE sc. ` c_id` = '03' ) as s
LEFT JOIN
` student` d ON s. ` s_id` = d. ` s_id`
WHERE 排名 BETWEEN 2 AND 3 ;
SELECT DISTINCT c. ` c_name` ,
sc. ` c_id` ,
t1. ` 85-100` ,
t1. 百分比,
t2. ` 70-85` ,
t2. 百分比,
t3. ` 60-70` ,
t3. 百分比,
t4. ` 0-60` ,
t4. 百分比
FROM ` score` sc
LEFT JOIN
( SELECT ` c_id` ,
SUM ( IF ( ` s_score` > 85 AND ` s_score` <= 100 , 1 , 0 ) ) AS ` 85-100` ,
ROUND ( 100 * ( SUM ( IF ( ` s_score` > 85 AND ` s_score` <= 100 , 1 , 0 ) ) / COUNT ( * ) ) , 2 ) AS 百分比
FROM ` score`
GROUP BY ` c_id` ) t1 ON sc. ` c_id` = t1. ` c_id`
LEFT JOIN
( SELECT ` c_id` ,
SUM ( IF ( ` s_score` > 70 AND ` s_score` <= 85 , 1 , 0 ) ) AS ` 70-85` ,
ROUND ( 100 * ( SUM ( IF ( ` s_score` > 70 AND ` s_score` <= 85 , 1 , 0 ) ) / COUNT ( * ) ) , 2 ) AS 百分比
FROM ` score`
GROUP BY ` c_id` ) t2 ON sc. ` c_id` = t2. ` c_id`
LEFT JOIN
( SELECT ` c_id` ,
SUM ( IF ( ` s_score` > 60 AND ` s_score` <= 70 , 1 , 0 ) ) AS ` 60-70` ,
ROUND ( 100 * ( SUM ( IF ( ` s_score` > 60 AND ` s_score` <= 70 , 1 , 0 ) ) / COUNT ( * ) ) , 2 ) AS 百分比
FROM ` score`
GROUP BY ` c_id` ) t3 ON sc. ` c_id` = t3. ` c_id`
LEFT JOIN
( SELECT ` c_id` ,
SUM ( IF ( ` s_score` >= 0 AND ` s_score` <= 60 , 1 , 0 ) ) AS ` 0-60` ,
ROUND ( 100 * ( SUM ( IF ( ` s_score` >= 0 AND ` s_score` <= 60 , 1 , 0 ) ) / COUNT ( * ) ) , 2 ) AS 百分比
FROM ` score`
GROUP BY ` c_id` ) t4 ON sc. ` c_id` = t4. ` c_id`
LEFT JOIN
` course` c ON sc. ` c_id` = c. ` c_id` ;
SELECT t. ` s_id` ,
@i := @i + 1 AS '不保留空缺排名' ,
@k := ( IF ( @avg_score = t. avg_score, @k , @i ) ) AS '保留空缺排名' ,
@avg_score := avg_score AS '平均分'
FROM ( SELECT ` s_id` ,
ROUND ( AVG ( ` s_score` ) , 2 ) AS avg_score
FROM score
GROUP BY ` s_id` ) t,
( SELECT @avg_score := 0 , @i := 0 , @k := 0 ) b;
SELECT
sc1. ` s_id` , sc1. ` c_id` , sc1. ` s_score`
FROM
` score` sc1
LEFT JOIN
` score` sc2 ON sc1. ` c_id` = sc2. ` c_id`
AND sc1. ` s_score` < sc2. ` s_score`
GROUP BY sc1. ` s_id` , sc1. ` c_id` , sc1. ` s_score`
HAVING COUNT ( sc2. ` s_id` ) < 3
ORDER BY sc1. ` c_id` , sc1. ` s_score` DESC ;
SELECT
` c_id` , COUNT ( ` s_id` )
FROM
` score`
GROUP BY ` c_id` ;
SELECT
` s_id` , ` s_name`
FROM
` student`
WHERE
` s_id` IN ( SELECT
` s_id`
FROM
` score`
GROUP BY ` s_id`
HAVING COUNT ( ` c_id` ) = 2 ) ;
SELECT
` s_sex` , COUNT ( ` s_sex` ) AS 人数
FROM
student
GROUP BY s_sex;
SELECT
*
FROM
` student`
WHERE
` s_name` LIKE '%风%' ;
SELECT
st1. ` s_name` , st1. ` s_sex` , COUNT ( * )
FROM
` student` st1
JOIN
` student` st2 ON st1. ` s_id` != st2. ` s_id`
AND st1. ` s_name` = st2. ` s_name`
AND st1. ` s_sex` = st2. ` s_sex`
GROUP BY st1. ` s_name` , st1. ` s_sex` ;
SELECT
` s_name`
FROM
` student`
WHERE
` s_birth` LIKE '1990%' ;
SELECT
` c_id` , ROUND ( AVG ( ` s_score` ) , 2 ) AS avg_score
FROM
` score`
GROUP BY ` c_id`
ORDER BY avg_score DESC , ` c_id` ASC ;
SELECT
sc. ` s_id` ,
st. ` s_name` ,
ROUND ( AVG ( sc. ` s_score` ) , 2 ) AS avg_score
FROM
` score` sc
LEFT JOIN
` student` st ON sc. ` s_id` = st. ` s_id`
GROUP BY ` s_id`
HAVING avg_score >= 85 ;
SELECT
st. ` s_name` , sc. ` s_score`
FROM
` score` sc
LEFT JOIN
` student` st ON st. ` s_id` = sc. ` s_id`
WHERE
sc. ` c_id` = ( SELECT
` c_id`
FROM
` course`
WHERE
` c_name` = '数学' )
AND sc. s_score < 60 ;
SELECT st. ` s_id` ,
st. ` s_name` ,
SUM ( IF ( c. ` c_name` = '语文' , sc. ` s_score` , 0 ) ) AS '语文' ,
SUM ( IF ( c. ` c_name` = '数学' , sc. ` s_score` , 0 ) ) AS '数学' ,
SUM ( IF ( c. ` c_name` = '英语' , sc. ` s_score` , 0 ) ) AS '英语' ,
SUM ( sc. ` s_score` ) AS '总分'
FROM ` student` st
LEFT JOIN
` score` sc ON st. ` s_id` = sc. ` s_id`
LEFT JOIN
` course` c ON sc. ` c_id` = c. ` c_id`
GROUP BY st. ` s_id` , st. ` s_name` ;
SELECT
st. ` s_name` , c. ` c_name` , sc. ` s_score`
FROM
` course` c
LEFT JOIN
` score` sc ON c. ` c_id` = sc. ` c_id`
LEFT JOIN
` student` st ON st. ` s_id` = sc. ` s_id`
WHERE
sc. ` s_score` >= 70 ;
SELECT
sc. ` s_id` , sc. ` c_id` , c. ` c_name` , sc. ` s_score`
FROM
` score` sc
LEFT JOIN
` course` c ON sc. ` c_id` = c. ` c_id`
WHERE
sc. ` s_score` < 60 ;
SELECT
sc. ` s_id` , st. ` s_name`
FROM
` score` sc
LEFT JOIN
` student` st ON sc. ` s_id` = st. ` s_id`
WHERE
sc. ` c_id` = '01' AND sc. ` s_score` > 80 ;
SELECT
COUNT ( * )
FROM
` score`
GROUP BY ` c_id` ;
SELECT
` c_id`
FROM
` course` c,
` teacher` t
WHERE
c. ` t_id` = t. ` t_id`
AND t. ` t_name` = '老师 1' ;
SELECT
MAX ( ` s_score` )
FROM
` score`
WHERE
` c_id` = '02' ;
SELECT
st. * , sc. ` s_score` , sc. ` c_id` , c. ` c_name`
FROM
` student` st
LEFT JOIN
` score` sc ON st. ` s_id` = sc. ` s_id`
LEFT JOIN
` course` c ON sc. ` c_id` = c. ` c_id`
WHERE
sc. ` c_id` = ( SELECT
` c_id`
FROM
` course` c,
` teacher` t
WHERE
c. ` t_id` = t. ` t_id`
AND t. ` t_name` = '老师 1' )
AND sc. s_score IN ( SELECT
MAX ( s_score)
FROM
score
WHERE
c_id = '02' ) ;
SELECT
c. ` c_name` , sc. ` s_score` , st. *
FROM
` course` c,
` score` sc,
` teacher` t,
` student` st
WHERE
t. ` t_id` = c. ` t_id`
AND c. ` c_id` = sc. ` c_id`
AND st. ` s_id` = sc. ` s_id`
AND t. ` t_name` = '老师 1'
AND sc. s_score IN ( SELECT
MAX ( s_score)
FROM
course,
score,
teacher,
student
WHERE
teacher. t_id = course. t_id
AND course. c_id = score. c_id
AND student. s_id = score. s_id
AND teacher. t_name = '老师 1' ) ;
SELECT DISTINCT
sc2. ` s_id` , sc2. ` c_id` , sc2. ` s_score`
FROM
` score` sc1,
` score` sc2
WHERE
sc1. ` c_id` != sc2. ` c_id`
AND sc1. ` s_score` = sc2. ` s_score` ;
SELECT
sc1. ` s_id` , sc1. ` c_id` , sc1. ` s_score`
FROM
` score` sc1
WHERE
( SELECT
COUNT ( 1 )
FROM
` score` sc2
WHERE
sc2. ` c_id` = sc1. ` c_id`
AND sc2. ` s_score` >= sc1. ` s_score` ) <= 2
ORDER BY sc1. ` c_id` ;
SELECT
` c_id` , COUNT ( * ) AS total
FROM
` score`
GROUP BY ` c_id`
HAVING total > 5
ORDER BY total DESC , ` c_id` ;
SELECT
` s_id` , COUNT ( * )
FROM
` score`
GROUP BY ` s_id`
HAVING COUNT ( * ) >= 2 ;
SELECT
*
FROM
` student`
WHERE
` s_id` IN ( SELECT
` s_id`
FROM
` score`
GROUP BY ` s_id`
HAVING COUNT ( * ) = ( SELECT
COUNT ( * )
FROM
` course` ) ) ;
SELECT
` s_birth` ,
( DATE_FORMAT( NOW ( ) , '%Y' ) - DATE_FORMAT( ` s_birth` , '%Y' ) - ( IF ( DATE_FORMAT( NOW ( ) , '%m%d' ) > DATE_FORMAT( ` s_birth` , '%m%d' ) ,
0 ,
1 ) ) ) AS age
FROM
` student` ;
SELECT
*
FROM
` student`
WHERE
WEEK( DATE_FORMAT( NOW ( ) , '%Y%m%d' ) ) = WEEK( ` s_birth` ) ;
SELECT
*
FROM
` student`
WHERE
YEARWEEK( ` s_birth` ) = YEARWEEK( DATE_FORMAT( NOW ( ) , '%Y%m%d' ) ) ;
SELECT WEEK( DATE_FORMAT( NOW ( ) , '%Y%m%d' ) ) ;
SELECT
*
FROM
` student`
WHERE
WEEK( DATE_FORMAT( NOW ( ) , '%Y%m%d' ) ) + 1 = WEEK( ` s_birth` ) ;
SELECT
*
FROM
` student`
WHERE
MONTH ( DATE_FORMAT( NOW ( ) , '%Y%m%d' ) ) = MONTH ( ` s_birth` ) ;
SELECT
*
FROM
` student`
WHERE
MONTH ( DATE_FORMAT( NOW ( ) , '%Y-%m-%d' ) ) + 1 = MONTH ( ` s_birth` ) ;