SQL的基本语句和题型笔记

最近搞C#转全栈了，那么多小知识咱这脑子也记不住啊，小本子记上

首先是SQL基础语句，我用的是PgSql，可能会有细微差别，但sql语句都大差不差

基础sql语法

• 创建数据库
  CREATE DATABASE dbname;

• 创建主键
  id serial primary key

• 创建性别字段约束
  gender CHAR(1) CHECK (gender IN ( ‘男’,‘女’ ) )
  如果是多个约束：
  optype varchar(1) check(optype=‘N’ or optype=‘U’ or optype=‘D’),
  如果约束数字：
  reusedable int4 default(0) check(reusedable=0 or reusedable=1)

• 给已建表插入字段
  alter table student add age int not null default 0
  表示往student表中添加age字段，int类型，默认为0

• 删除表格（删除整个表格）
  DROP TABLE table_name;

• 删除表内所有数据
  truncate table student

• 删除表格字段
  alter table student drop column name

• 根据条件删除数据
  delete from table where id = ‘01’

• 修改字段类型
  alter table student alter column age int
  student表内的age字段改为int类型

• 更新数据
  update student set name = ‘小李’ where id = 1;
  id为1的数据，name改为小李

• 窗口函数返回排序区别

   row_number()：1，2，3，4
   rank()：1，2，2，4
   dense_rank()：1，2，2，3
  

• partition by用法

    partition by curriculumid order by score desc
    以curriculumid为组，对score 进行排名
  

• left join用法
  这个用于表之间的左连接，表一连接表二，用他们的相同字段做连接点

    select * from student1 s1 left join student2 s2 on s1.id=s2.id
  

• case when语句

//当name为小李的时候，则返回result，否则返回null
case when s.name='小李' then s.result else null end;

复杂类sql题型

场景是：
一张学生表

一张分数表

一张教师表

一张课程表

正常来讲大可不必这么麻烦，都只是为了锻炼和熟悉sql，从里面的题型里面开阔思维从而掌握逻辑，各位可以自行创建表格理解

• 查询男女各多少个

   select sex,count(sex='男'),count(sex='女')  from studentinfo group by sex;
  

• 查询平均成绩大于60分的学生的学号和平均成绩

   select studentId,avg(score) from scoreinfo group by studentId  having  avg(score)>60 ;
  

• 查询至少选修两门课程的学生学号

    select studentId from scoreinfo group by studentId having count(curriculumId)>=2;
  

• 查询出只选修了两门课程的全部学生的学号和姓名

select studentid,name
from studentinfo 
where studentid in(
	select studentid from scoreinfo s  group by studentid having count(curriculumId)=2
)

• 查询平均成绩大于85的所有学生的学号,姓名和平均成绩

select studentinfo.studentid,studentinfo.name,avg(scoreinfo.score) 
from studentinfo inner join scoreinfo
on studentInfo.studentid=scoreinfo.studentid
group by studentinfo.studentid
having avg(scoreinfo.score)>85;

• 检索“0001”课程分数小于60，按分数降序排列的学生信息

select s2.* from scoreinfo s inner join studentinfo s2 
on s.studentid = s2.studentid 
where s.curriculumid = '0001' and s.score < 60
order by s.score desc ;

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

select s.studentid ,s."name" ,avg(s2.score)
from  studentinfo s inner join scoreinfo s2 on s.studentid = s2.studentid 
where s2.studentid in(
	select studentid  from scoreinfo where < 60 and curriculumid is not null
)
group by s.studentid ,s."name" 
having count(s2.curriculumid)>=2; 

• 查询课程编号为“0001”的课程比“0002”的课程成绩高的所有学生的学号

select 
from (select * from scoreinfo where currculumid='0001')as s1 
inner join (select * from scoreinfo where curriculumid='0002') as s2
on s1.studentid=s2.studentid
where s1.score > s2.score;
```查询学生平均成绩及其名次

```sql
select s.studentid as "学号",s2."name" as "姓名" ,avg(s.score) as "平均分",
row_number() over(order by avg(s.score) desc) as "排名"
from scoreinfo s inner join studentinfo s2 
on s.studentid = s2.studentid 
group by s.studentid,s2."name"

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

select  c.curriculumname as "学科",s.score as "分数",
row_number() over(partition by c.curriculumid) as "排名"
from scoreinfo s inner join courseinfo c 
on s.curriculumid = c.curriculumid 

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

select s.*,c.curriculumname ,a.score from
(select *,row_number() over(partition by curriculumid order by score desc)
as ranking from scoreinfo) as a
inner join studentinfo s 
on a.studentid = s.studentid 
inner join courseinfo c
on a.curriculumid  = c.curriculumid  
where a.ranking in(2,3)

回想我一个前端刚学这些的时候简直是噩梦缠绕，祝你们学习顺利