SQL



--私人笔记使用，请勿转载

 

--可以下载简易的H2数据库练习SQL，才8M大哦，直接运行bin下面的h2.bat 即可。
--注意字符串用''！
-------------一-------------
--查询中用到的关键词主要包含六个，并且他们的顺序依次为
--select--from--where--group by--having--order by 
--其中select和from是必须的，其他关键词是可选的，这六个关键词的执行顺序
--与sql语句的书写顺序并不是一样的，而是按照下面的顺序来执行
--from--where--group by--having--select--order by,
--from:需要从哪个数据表检索数据 
--where:过滤表中数据的条件
--group by:如何将上面过滤出的数据分组
--having:对上面已经分组的数据进行过滤的条件 
--select:查看结果集中的哪个列，或列的计算结果 搜索
--order by :按照什么样的顺序来查看返回的数据

 

-------------二-------------
--from后面的表关联，是自右向左解析的，而where条件的解析顺序是自下而上的。
--也就是说，在写SQL文的时候，尽量把数据量大的表放在最右边来进行关联，
--而把能筛选出【大量数据的条件】放在where语句的最下面。

 

-------------三、Join-------------
--left join   :左连接，返回左表中所有的记录以及右表中连接字段相等的记录。
--right join :右连接，返回右表中所有的记录以及左表中连接字段相等的记录。
--inner join :内连接，又叫等值连接，只返回两个表中连接字段相等的行。
--full join :外连接，返回两个表中的行：left join + right join
--cross join :结果是笛卡尔积，就是第一个表的行数乘以第二个表的行数。

 

 举例：

 

select * from a;
编号 姓名
---- ----------
1000 张三
2000 李四
3000 王五

 

select * from b;
编号 商品
---- ----------
1000 电视机
2000 录像机
4000 自行车

 

select a.*,b.* from a inner join b on a.编号=b.编号;
编号 姓名 编号 商品
---- ---------- ---- ----------
1000 张三 1000 电视机
2000 李四 2000 录像机

 

select a.*,b.* from a left join b on a.编号=b.编号;
编号 姓名 编号 商品
---- ---------- ---- ----------
1000 张三 1000 电视机
2000 李四 2000 录像机
3000 王五 空值 空值

 

select a.*,b.* from a right join b on a.编号=b.编号;
编号 姓名 编号 商品
---- ---------- ---- ----------
1000 张三 1000 电视机
2000 李四 2000 录像机
 空值 空值 4000 自行车

 

select a.*,b.* from a full join b on a.编号=b.编号;
编号 姓名 编号 商品
---- ---------- ---- ----------
1000 张三 1000 电视机
2000 李四 2000 录像机
3000 王五 空值 空值
 空值 空值 4000 自行车
 
-------------四、SQL练习题1-------------

 

 --1)group by
--info 表
--date result
--2005-05-09 win
--2005-05-09 lose
--2005-05-09 lose
--2005-05-09 lose
--2005-05-10 win
--2005-05-10 lose
--2005-05-10 lose

 

--如果要生成下列结果, 该如何写sql语句?
--　　         win lose
--2005-05-09 2 2
--2005-05-10 1 2

 

--(1)
Select date, count(a.result) as win from info a where result="win"  group by date join
     Select count(b.result) as lose from info b wehre b.result="lose"  group by date  on a.date=b.date

 

--(2)
Select date, count(a.result) as win from info a where result="win"  join
     Select count(b.result) as lose from info b wehre b.result="lose" on a.date=b.date
 group by date
 
--(3)
select a.date, a.result as win, b.result as lose from
　　(select date, count (result) as result from info where result = "win" group by date) as a
　　join
　　(select date, count (result) as result from info where result = "lose" group by date) as b

 

on a.date = b.date;
--(4)
select date , sum( case when result = "win" then 1 else 0 end ) as "win",
sum(case when result = "lose" then 1 else 0 end) as "lose" from info group by date;

 

--2)case ｗｈｅｎ then else......
--表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列

 

select (case when a>b then a else b), (case when b>c then b else c) from table;

 

--3)ｃａｓｅ　ｗｈｅｎ then．．．．．．ｗｈｅｎ then．．．．．．ｅｌｓｅ．．．．．．
 --有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，
 --请用一条sql语句查询出这三条记录并按以下条件显示出来（并写出您的思路）：
--大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。

 

--显示格式：
--语文 数学 英语
--及格 优秀 不及格
------------------------------------------

 

select (case when score>=80 then youxiu
when score >=60 then jige
else bujige) as ’语文‘，
(case when score >=80 then youxiu
when score >=60 then jige
else bujige) as ‘数学’，
(case when score>=80 then youxiu
when score>=60 then jige
else bujige) as ’英语‘
   from table;

 

--4).姓名：name 课程：subject 分数：score 学号：stuid
--张三 数学 89 1
--张三 语文 80 1
--张三 英语 70 1
--李四 数学 90 2
--李四 语文 70 2
--李四 英语 80 2
--题解：
--1.计算每个人的总成绩并排名(要求显示字段：姓名，总成绩)//Q1 主键是学号
select name, sum(score) as 总成绩 from studentscore group by name order by 总成绩；

 

--2.Sum（）计算每个人的总成绩并排名(要求显示字段: 学号，姓名，总成绩)//Q1
（1）select stuid, name, sum(score) as 总成绩 from studentscore group by stuid order by 总成绩； --（错误查询）
（2）select distinct t1.stuid,t1.name,t2.allscore from stuscore t1,( select stuid,sum(score) as allscore
   from stuscore group by stuid)t2
     where t1.stuid=t2.stuid order by t2.allscore desc
 
--3.Max（）：计算每个人单科的最高成绩(要求显示字段: 学号，姓名，课程，最高成绩) //Q1
(1)select stuid, name, subject, max(score) as score from studentscore group by stuid; --（错误查询）
(2)select t1.stuid, t1.name, t1.subject, t2.score from studentscore t1， （select stuid, max(score) as score from studentscore group by stuid) t2
    where t1.stuid=t2.stuid and t1.score=t2.score;

 

--4.AVG（）：计算每个人的平均成绩（要求显示字段: 学号，姓名，平均成绩）//Q1
(1)select stuid, name, AVG（score） as averagescore from studentscore group by stuid;--（错误查询）
(2) select t1.stuid, t1.name, t2.avgscore from studentscore t1, (select stuid, avg(score) as avgscore from studentscore group by stuid) as t2 where t1.stuid=t2.stuid;

 

--5.Max（）：列出各门课程成绩最好的学生(要求显示字段: 学号，姓名,科目，成绩)//Q2 #2 and #3的区别
(1)select stuid, name, subject, max(score) as score from studentscore group by subject order by subject;--（错误查询）
(2)select t1.stuid, t1.name, t1.subject,t2.score from studentscore t1, (select stuid,distinct subject, max(score) as score from studentscore group by subject) as t2
   where t1.stuid=t2.stuid and t1.score=t2.score;
(3) select t1.stuid, t1.name, t1.subject, t2.score from studentscore t1,(select subject, max(score) as score from studentscore group by subject) t2
   where t1.subject=t2.subject and t1.score=t3.score
  
--6.Top2： 列出各门课程成绩最好的两位学生(要求显示字段: 学号，姓名,科目，成绩)
(1)select stuid, name, subject,score from studentscore where count(0) as rank in ('1','2') group by subject order by score desc. --（错误查询）
(2)select distinct t1.* from studentscore t1 where t1.stuid in (select top2 stuid from studentscore group by suject where subject=t1.subject order by score desc)
order by t1.subject;

 

--7.Sum（）/count（*）：统计如下：学号 姓名 语文 数学 英语 总分 平均分
(1)select stuid, name, (select score fron studentscore where subject= '语文'） as 语文，
（select score from studentscore where subject ='数学' ) as 数学, select score from studentscore hwere subject='英语' ) as
 英语, sum(score), avg(score) from studentscore group by stuid; --（错误查询）
(2) select stuid as 学号,name as 姓名,sum(case when subject=’语文’ then score else 0 end) as 语文,sum(case when subject=’数学’ then score else 0 end) as 数学,sum(case when subject=’英语’ then score else 0 end) as 英语,sum(score) as 总分,(sum(score)/count(*)) as 平均分 from stuscore group by stuid,name order by 总分 desc

 

--8．AVG（）：列出各门课程的平均成绩（要求显示字段：课程，平均成绩）
select subject, avg(score) from studentscore group by subject;

 

--9．Dense_Rank() or 存储过程:列出数学成绩的排名（要求显示字段：学号，姓名，成绩，排名）
(1)select stuid, name, score,count(0) as 排名 from studentscore where subject='数学' group by subject order by score；--（错误查询）
(2)declare @tmp table(pm int,name varchar(50),score int,stuid int)
insert into @tmp select null,name,score,stuid from stuscore where subject=’数学’ order by score desc
declare @id int
set @id=0;
update @tmp set @id=@id+1,pm=@id
select * from @tmp
(3)--oracle:
select DENSE_RANK () OVER(order by score desc) as row,name,subject,score,stuid from stuscore where subject=’数学’order by score desc
(4)--ms sql(最佳选择)
select (select count(*) from stuscore t1 where subject =’数学’ and t1.score>t2.score)+1 as row ,stuid,name,score from stuscore t2 where subject =’数学’ order by score desc

 

--10．Top 3：列出数学成绩在2-3名的学生（要求显示字段：学号，姓名,科目，成绩）
(1)select stuid, name, subject, score from studentscore where subject='数学' and count(0) as rank in ('2','3') group by suject
order  by score desc;--（错误查询）
-----先取出前三，然后从前三取出后二
(2)select t3.* from (select top 2 t2.*
     from
(select top 3 name,subject,score,stuid from stuscore where subject=’数学’order by score desc) t2 order by t2.score) t3
  order by t3.score

 

--11．存储过程： 求出李四的数学成绩的排名
（1）select
(select name, count(0) as rank from studentscore where  subject='数学' order by score desc ) from studentscore --（错误查询）
where name='李四'
（2）--局部变量用一个@，全局用两个@
declare @tmp table(pm int,name varchar(50),score int,stuid int)
insert into @tmp select null,name,score,stuid from stuscore where subject=’数学’ order by score desc
declare @id intset @id=0;
update @tmp set @id=@id+1,pm=@id
select * from @tmp where name=’李四’

 

--12．count（*）： 统计如下：课程 不及格（0-59）个 良（60-80）个 优（81-100）个
（1）select subject, ( case when score <=59 then 1 else 0 end), (case wehn score>=60 and score<=80 then 1 else 0 end), count (case when score>=81 and score <=100 then 1 else 0 end) from studentscore group by subject;
（2）select subject, (select count(*) from stuscore where score<60 and subject=t1.subject) as 不及格,(select count(*) from stuscore where score between 60 and 80 and subject=t1.subject) as 良,(select count(*) from stuscore where score >80 and subject=t1.subject) as 优
from stuscore t1 group by subject

 

--13．统计如下：数学:张三(50分),李四(90分),王五(90分),赵六(76分)//Q3

 

declare @s varchar(1000)set @s=select @s =@s+’,’+name+'('+convert(varchar(10),score)+’分)’ from stuscore where subject=’数学’ set @s=stuff(@s,1,1,)
print ‘数学:’+@s
--删除指定长度的字符，并在指定的起点处插入另一组字符。
--STUFF ( character_expression , start , length ,character_expression )，i.e. SELECT STUFF('abcdef', 2, 3, 'ijklmn')
-->>aijklmnf

 

--14.计算科科及格的人的平均成绩
(1)select subject, average( select score from studentscore where score>=60 group by subject )
from studentscore group by subject;--（错误查询）
--查询平均成绩不小于60的人的平均成绩
(2)select distinct t1.stuid,t2.avgscore from stuscore t1,(select stuid,avg(score) as avgscore from stuscore group by stuid ) t2,(select stuid from stuscore where score<60 group by stuid) t3 where t1.stuid=t2.stuid and t1.stuid!=t3.stuid;
---大于60分的科目数为3
(3)select name,avg(score) as avgscore from stuscore s
where (select sum(case when i.score>=60 then 1 else 0 end) from stuscore i where i.name= s.name)=3
group by name

 

--5).not in or group by...... having： 查询出每门课都大于80 分的学生姓名

 

--name   kecheng   fenshu
 --张三      语文       81
 --张三      数学       75
 --李四      语文       76
 --李四      数学       90
 --王五      语文       81
 --王五      数学       100
 --王五      英语       90
（1）select name from studentscore where name in
 (select  (select name from studentscore where subject= '语文' and score >80) from
 studentscore where subject='数学' and score>80
 ） and subject='英语' and score>80; --（错误查询）
（2）
--查出分数小于80的名字， 剩下的就是都是大于80分的
 select distinct name from table where name not in (select distinct name from table where fenshu<=80)
（3）--查出所有最小分数大于80的人
 select name from table group by name having min(fenshu)>80
 --having 对已经分组的进行过滤
 
 
-------------五、SQL练习题2-------------

 

某班学生和考试成绩信息分别如下表Student和表Achievement所示：
ID Name
1  Jack
2  Marry
3  Rose
4  Bob
5  John
6         Betty
7         Robert
 

 

ID         Mark
1         90
2         96
3         88
4         86
5         83
6         85

 

表Student   ， 表Achievement

 

其中ID为学生的编号，Name为姓名，Mark为成绩，请针对以下问题，写出相应的SQL语句：
--1、        请查询成绩>85分的学生的姓名；
select name from student where id in (select id from achievement where mark>85) and student.id=achievement.id ordre by student.id
select name from student where id not in (select id from achievement where mark<=85) and student.id=achievement.id order by student.id;

 

--2、        请查询成绩>=90分的人数；
select (case when mark>=90 then 1 else 0 end) from achievement;
select coun(*) form achievement where mark>=90;

 

--3、        Robert此次考试考了80分，但是成绩没能录入表中，请将其成绩添加进去；
insert into achievement(id,mark)  value('6','90');

 

--4、        请将Rose的成绩修改为87；
update achievement set mark='87' where id id=(select id from student where name='Rose') and student.id=achievement.id;
--5、        请删除Betty的记录；
delete from achievement where id=(select id from student where name='betty') and student.id=achievement.id;
delete from student where name='Betty';
 
-------------六、一些简单SQL-------------
select replace('jessica', 'ca','e');
select substring('abcdef',0,2);
select length('jessica');
select char(65);
select instr ('jessica','s');
select concat('Jessica','.','liu') as name;

 

select now();
select current_date()+current_time();

 

select rand();
select floor(4.7);
select floor(rand()*100);

 

show tables;
show columns from table1;