首先我們有一個(gè)school數(shù)據(jù)庫(kù)，然后它有如下結(jié)構(gòu)的四張表：
接下來(lái)通過(guò)SQL語(yǔ)句創(chuàng)建對(duì)應(yīng)的數(shù)據(jù)庫(kù)和數(shù)據(jù)表(英語(yǔ)字母統(tǒng)一用小寫(xiě)，好看一點(diǎn))：
創(chuàng)建school數(shù)據(jù)庫(kù)（指定字符集）；
create database school charset=utf8;
切換到school數(shù)據(jù)庫(kù)；
use school;
創(chuàng)建student數(shù)據(jù)表；
create table student (
s_id varchar(10) collate utf8_bin default null,
s_name varchar(20) collate utf8_bin default null,
s_brithday date default null,
s_sex char(2) collate utf8_bin default null
) engine=innodb default charset=utf8 collate=utf8_bin;
向student表插入測(cè)試數(shù)據(jù)
insert into student values ('01', '趙雷', '1990-01-01', '男');
insert into student values ('02', '錢(qián)電', '1990-12-21', '男');
insert into student values ('03', '孫風(fēng)', '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', '女');
創(chuàng)建course數(shù)據(jù)表
create table course (
c_id varchar(10) collate utf8_bin default null,
c_name varchar(20) collate utf8_bin default null,
t_id varchar(10) collate utf8_bin default null
) engine=innodb default charset=utf8 collate=utf8_bin;
向course表插入測(cè)試數(shù)據(jù)
insert into course values ('01', '語(yǔ)文', '02');
insert into course values ('02', '數(shù)學(xué)', '01');
insert into course values ('03', '英語(yǔ)', '03');
創(chuàng)建sc數(shù)據(jù)表
create table sc (
s_id varchar(10) collate utf8_bin default null,
c_id varchar(10) collate utf8_bin default null,
score decimal(4,0) default null
) engine=innodb default charset=utf8 collate=utf8_bin;
向sc表插入測(cè)試數(shù)據(jù)
insert into sc values ('01', '01', '80');
insert into sc values ('01', '02', '90');
insert into sc values ('01', '03', '99');
insert into sc values ('02', '01', '70');
insert into sc values ('02', '02', '60');
insert into sc values ('02', '03', '80');
insert into sc values ('03', '01', '80');
insert into sc values ('03', '02', '80');
insert into sc values ('03', '03', '80');
insert into sc values ('04', '01', '50');
insert into sc values ('04', '02', '30');
insert into sc values ('04', '03', '20');
insert into sc values ('05', '01', '76');
insert into sc values ('05', '02', '87');
insert into sc values ('06', '01', '31');
insert into sc values ('06', '03', '34');
insert into sc values ('07', '02', '89');
insert into sc values ('07', '03', '98');
insert into sc values ('07', '01', '99');
創(chuàng)建teacher數(shù)據(jù)表
create table teacher (
t_id varchar(10) collate utf8_bin default null,
t_name varchar(20) collate utf8_bin default null
) engine=innodb default charset=utf8 collate=utf8_bin;
向teacher插入測(cè)試數(shù)據(jù)
insert into teacher values('01', '張三');
insert into teacher values('02', '趙六');
insert into teacher values('03', '王五');
實(shí)際操作練習(xí)
統(tǒng)計(jì)同學(xué)的男女生個(gè)數(shù)
SELECT
SUM(CASE WHEN s_sex = '男' THEN 1 ELSE 0 END) 男生個(gè)數(shù),
SUM(CASE WHEN s_sex = '女' THEN 1 ELSE 0 END) 女生個(gè)數(shù)
FROM
student;
統(tǒng)計(jì)同學(xué)的男女生占比（保留兩位小數(shù)）
SELECT
cast(SUM(CASE WHEN s_sex = '男' THEN 1 ELSE 0 END)/count(1) AS DECIMAL(18,2)) 男生比例,
cast(SUM(CASE WHEN s_sex = '女' THEN 1 ELSE 0 END)/count(1) AS DECIMAL(18,2)) 女生比例
FROM
student;
查詢"01"課程比"02"課程成績(jī)高的學(xué)生的信息及課程分?jǐn)?shù)
SELECT
a.*, b.score 課程01的分?jǐn)?shù),
c.score 課程02的分?jǐn)?shù)
FROM
student a,
sc b,
sc c
WHERE
a.s_id = b.s_id
AND a.s_id = c.s_id
AND b.c_id = '01'
AND c.c_id = '02'
AND b.score > c.score;
查詢"李"姓老師的數(shù)量
SELECT
count(t_name) 李姓老師的數(shù)量
FROM
teacher
WHERE
t_name LIKE '李%'
查詢語(yǔ)文課程的任職老師姓名
SELECT
t_name
FROM
teacher
WHERE
t_id = (
SELECT
t_id
FROM
course
WHERE
c_name = '語(yǔ)文'
);
查詢"張三"老師教過(guò)同學(xué)的信息
SELECT DISTINCT
student.*
FROM
student,
course,
sc,
teacher
WHERE
student.s_id = sc.s_id
AND course.c_id = sc.c_id
AND course.t_id = teacher.t_id
AND teacher.t_name = '張三';
查詢"張三"老師沒(méi)教過(guò)的同學(xué)信息
SELECT
*
FROM
student
WHERE
s_id NOT IN (
SELECT DISTINCT
student.s_id
FROM
student,
course,
sc,
teacher
WHERE
student.s_id = sc.s_id
AND course.c_id = sc.c_id
AND course.t_id = teacher.t_id
AND teacher.t_name = '張三'
);
查詢平均成績(jī)大于60分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名以及平均分（保留兩位小數(shù)）
SELECT
student.s_id,
student.s_name,
cast(
avg(sc.score) AS DECIMAL (18, 2)
) avg_score
FROM
student,
sc
WHERE
student.s_id = sc.s_id
GROUP BY
student.s_id
HAVING
avg_score > 60;
查詢?nèi)我庖婚T(mén)課程都大于80分的學(xué)生信息
SELECT
*
FROM
student
WHERE
s_id IN (
SELECT
s_id
FROM
sc
GROUP BY
s_id
HAVING
min(score) >= 80
);
查詢有一門(mén)課程分?jǐn)?shù)超過(guò)80分的學(xué)生信息
SELECT
*
FROM
student
WHERE
s_id IN (
SELECT
s_id
FROM
sc
GROUP BY
s_id
HAVING
max(score) >= 80
);
查詢沒(méi)有學(xué)全所有課程的同學(xué)信息
SELECT
s.*
FROM
student s,
sc
WHERE
s.s_id = sc.s_id
GROUP BY
s.s_id
HAVING
count(c_id) < (
SELECT
count(c_id)
FROM
course
);
統(tǒng)計(jì)每門(mén)課程各等級(jí)（60分以下 D等級(jí)，60分~75分（不含75分） C等級(jí)，75分~85分（不含85分） B等級(jí)，85分以上 A等級(jí)）分?jǐn)?shù)的人數(shù)
SELECT
c_id,
SUM(CASE WHEN score >=85 THEN 1 ELSE 0 end) A,
SUM(CASE WHEN score>=75 AND score <85 THEN 1 ELSE 0 END) B,
SUM(CASE WHEN score>=60 AND score<75 THEN 1 ELSE 0 END) C,
SUM(case WHEN score < 60 THEN 1 ELSE 0 END) D
FROM
sc
GROUP BY
c_id;
查詢數(shù)學(xué)課程的前三名的學(xué)生姓名
SELECT
*
FROM
student
WHERE
s_id IN (
SELECT
s_id
FROM
(
SELECT
sc.s_id
FROM
sc,
course
WHERE
sc.c_id = course.c_id
AND course.c_name = '數(shù)學(xué)'
ORDER BY
sc.score DESC
LIMIT 3
) AS t
);
查詢每門(mén)課的前三名的同學(xué)的信息
待研究
未完待續(xù)
