1.用一條SQL 語(yǔ)句 查詢(xún)出每門(mén)課都大于80 分的學(xué)生姓名
name kecheng fenshu
張三 語(yǔ)文 81
張三 數(shù)學(xué) 75
李四 語(yǔ)文 76
李四 數(shù)學(xué) 90
王五 語(yǔ)文 81
王五 數(shù)學(xué) 100
王五 英語(yǔ) 90
select distinct name from table where name not in (select distinct name from table where fenshu<=80)
select name from table group by name having min(fenshu)>80
2. 學(xué)生表 如下:
自動(dòng)編號(hào) 學(xué)號(hào) 姓名 課程編號(hào) 課程名稱(chēng) 分?jǐn)?shù)
1 2005001 張三 0001 數(shù)學(xué) 69
2 2005002 李四 0001 數(shù)學(xué) 89
3 2005001 張三 0001 數(shù)學(xué) 69
刪除除了自動(dòng)編號(hào)不同, 其他都相同的學(xué)生冗余信息
delete tablename where 自動(dòng)編號(hào) not in(select min( 自動(dòng)編號(hào)) from tablename group by學(xué)號(hào), 姓名, 課程編號(hào), 課程名稱(chēng), 分?jǐn)?shù))
3.一個(gè)叫 team 的表，里面只有一個(gè)字段name, 一共有4 條紀(jì)錄陪拘，分別是a,b,c,d, 對(duì)應(yīng)四個(gè)球?qū)υ浦桑F(xiàn)在四個(gè)球?qū)M(jìn)行比賽纺座，用一條sql 語(yǔ)句顯示所有可能的比賽組合.
你先按你自己的想法做一下织中，看結(jié)果有我的這個(gè)簡(jiǎn)單嗎？
SELECT * FROM group_table g1 JOIN group_table g2 WHERE g1.name < g2.name
4 使用sql語(yǔ)句改變下表結(jié)構(gòu)
原始數(shù)據(jù)結(jié)構(gòu)
+------+-------+-------+
| name | stage | score |
+------+-------+-------+
| A | 基 | 1 |
| B | 基 | 2 |
| C | 基 | 3 |
| A | 爬 | 1 |
| B | 爬 | 3 |
| C | 爬 | 2 |
| A | SQL | 1 |
| B | SQL | 1 |
| C | SQL | 1 |
+------+-------+-------+
目標(biāo)數(shù)據(jù)結(jié)構(gòu)
+------+------+------+------+
| name | 基 | 爬 | SQL |
+------+------+------+------+
| A | 1 | 1 | 1 |
| B | 2 | 3 | 1 |
| C | 3 | 2 | 1 |
+------+------+------+------+
SELECT name,
MAX(CASE WHEN stage='基' THEN score ELSE null END) AS '基',
MAX(CASE WHEN stage='爬' THEN score ELSE null END) AS '爬',
MAX(CASE WHEN stage='SQL' THEN score ELSE null END) AS 'SQL'
FROM scores GROUP BY name
5.建表
創(chuàng)建學(xué)生表 Student
字段包括 學(xué)號(hào)任洞，姓名穆役， 性別，出生年月日旁蔼， 班級(jí)
create table Student(
sno varchar(20) primary key,
sname varchar(20) not null,
ssex varchar(10) not null,
sbirthday datetime,
calss varchar(20)
)
創(chuàng)建教師表 teacher
字段包括 教師編號(hào)锨苏，教師姓名， 教師性別棺聊，出生年月日伞租， 職稱(chēng)， 所在部門(mén)
create table teacher(
tno varchar(20) primary key,
tname varchar(20) not null,
tsex varchar(10) not null,
tbirthday datetime,
prof varchar(20) not null,
depart varchar(20) not null
)
創(chuàng)建課程表 Course
字段包括 課程編號(hào)限佩，課程名稱(chēng)葵诈， 教師編號(hào)
create table Course(
cno varchar(20) primary key,
cname varchar(20) not null,
tno varchar(10) not null,
foreign key(tno) references teacher(tno)
)
創(chuàng)建成績(jī)表 Score
字段包括 學(xué)號(hào)，課程號(hào)祟同，成績(jī)
create table Score(
sno varchar(20) primary key,
cno varchar(10) not null,
degree decimal,
foreign key(sno) references Student(sno),
foreign key(cno) references Course(cno)
);
練習(xí)題：
1 查詢(xún)score表中的最高分的學(xué)生號(hào)碼作喘，課程號(hào)碼
select sno,cno
from Score
where degree = (
select max(degree) from Score
);
2 查詢(xún)score表中至少有兩名學(xué)生選修的并以3為開(kāi)頭的課程的平均成績(jī)
select cno, avg(degree)
from Score
where con like '3%'
group by con
having count(1) >= 2;
3 查詢(xún)分?jǐn)?shù)大于70 小于90 的學(xué)生號(hào)碼
// ' between 70 and 90 介于之間
select sno
from Score
where degree between 70 and 90 / where degree>70 and degree<90;
4 查詢(xún)所有學(xué)生的 sname, cno 和 degree 列數(shù)據(jù)
select sname, cno, degree
from Student, Score
on Student.sno = Score.sno;
5 查詢(xún) “95031” 班學(xué)生每門(mén)課平均分
select cno, avg(degree)
from Score
where sno in(
select sno
from Student
where class="95031"
) group by cno;
6 查詢(xún)和學(xué)號(hào) 108, 101 學(xué)生同年出生的學(xué)生信息
// year() ： 獲取年份函數(shù)
select *
from Student
where year(sbirthday) in (
select year(sbirthday)
from Student
where sno in(108, 101)
);
7 查詢(xún)“張旭” 老師任課的學(xué)生成績(jī)
select *
from Score where cno (
select cno
from Course where tno = (
select tno
from teacher
where tname = "張旭"
)
)
8 查詢(xún)選修某課程的同學(xué)人數(shù)大于5的教師姓名
select tname
from teacher where tno in(
select tno
from Course where cno in (
select cno
from Score group by cno having count(1) > 5
)
)
9 查詢(xún)編號(hào)為 ‘3-105’ 課程 且成績(jī)至少高于選修編號(hào)‘3-245’ 同學(xué)的 Cno,Sno, Degree 再以Degree降序排序
// any：至少一個(gè) desc：降序
select Cno, Sno, Degree
from Score
where cno='3-105' and degree > any(
select degree from Score where cno = '3-245'
) order by degree desc;
10 查詢(xún)所有老師和學(xué)生的 name sex birthday
//求并集
select tname tsex tbirthday from teacher
union
select sname ssex sbirthday from Student
11
select sno, cno, grade
from Score, grade
where degree between low and upp
