SQL -day 1

1. 学生、课程、学生与课程之间的多对多联系，三个模式如下：

• 学生（学号，姓名，性别，专业号）
• 课程（课程号，课程名，学分）
• 选修（学号，课程号，成绩）

下列选项对于上述模式的参照完整性叙述不正确的是（D）

A.“选修”关系中的“学号”是一个外键，引用了“学生”中的“学号”

B.“选修”关系中的“课程号”是一个外键，引用了“课程”中的“课程号”

C.“学号”、“课程号”分别为“学生”、“课程“关系中的主键

D.“学生”关系中的属性“学号”可以唯一标识一个元组，但不可以唯一标识学生实体

# 外键和另一张表的主键关联，不能创建对应表中不存在的外键

2. select语句完整语法如下：

1. select  目标表的列名或列表达式序列
2. from 基本表名和（或）视图序列
3. [where 行条件表达式]
4. [group by  列名序列 [having  组条件表达式]]
5. [order by 列名 [asc | desc]]

则sql语句的执行顺序是：2,3,4,1,5

# 写法顺序：select--from--where--group by--having--order by 

# 执行顺序：from--where--group by--having--select--order by

# select要放后面，如果有order by，则order by放最后，因为order by 是对结果进行排序

3. Mysql中表student_table(id,name,birth,sex)，插入如下记录：

• ('1001' , '' , '2000-01-01' , '男');
• ('1002' , null , '2000-12-21' , '男');
• ('1003' , NULL , '2000-05-20' , '男');
• ('1004' , '张三' , '2000-08-06' , '男');
• ('1005' , '李四' , '2001-12-01' , '女');

执行 select * from student_table where length(name) >= 0 的结果行数是（3）

# 空字符串结果为0，空值NUll不显示

4. 写一段SQL，已知衬衫表SHIRTABLE，请你实现通过窗口函数实现，根据不同的衬衫种类shirt_type，按照销售单价shirt_price从低到高的顺序创建排序表（A）

A. SELECT shirt_name, shirt_type, shirt_price，

    RANK() OVER (PARTITION BY shirt _type ORDER BY shirt_price) AS ranking

    FROM SHIRTABLE

B. SELECT shirt _name, shirt_type, shirt _price，

    PARTITION BY shirt _type ORDER BY shirt _price AS ranking

    FROM SHIRTABLE

C. SELECT shirt _name, shirt_type, shirt _price，

    RANK (PARTITION BY shirt _type ORDER BY shirt _price) AS ranking

    FROM SHIRTABLE

D. SELECT shirt _name, shirt_type, shirt _price，

    RANK() OVER (PARTITION BY shirt_type) AS ranking

    FROM SHIRTABLE

# 窗口函数

over()窗口函数的语法结构 及常与over()一起使用的分析函数

• over()窗口函数的语法结构
• 常与over()一起使用的分析函数

1、over()窗口函数的语法结构

over()函数中包括三个函数：分区partition by 列名、排序order by 列名、指定窗口范围rows between 开始位置 and 结束位置（可用\可以不用）

若over()函数中不使用这三个函数，窗口大小是针对查询产生的所有数据，如果指定了分区，窗口大小是针对每个分区的数据

# partition by
partition by可理解为group by 分组。over(partition by 列名)搭配分析函数时，分析函数按照每一组每一组的数据进行计算的。

B rows between 开始位置 and 结束位置
是指定窗口范围，比如第一行到当前行。而这个范围是随着数据变化的。over(rows between 开始位置 and 结束位置)搭配分析函数时，分析函数按照这个范围进行计算的。

2 常与over()一起使用的分析函数：

2.1 聚合类

avg()、sum()、max()、min()

2.2 排名类

rank() 按照值排序时产生一个自增编号，值相等时会重复，会产生空位（如：1、3、3、6、9）
dense_rank() 按照值排序时产生一个自增编号，值相等时会重复，不会产生空位（如：1、2、2、3、3、4）

row_number() 按照值排序时产生一个自增编号，不会重复（如：1、2、3、4、5、6）

5. Mysql中表student_table(id,name,birth,sex)，查询男生、女生人数分别最多的3个姓氏及人数，正确的SQL是（C）

A
SELECT   sex ,substr(name,1,1) as first_name ,count(*) as c1 
from student_table where length(name) >=1
group by first_name , sex order by  sex ,c1 desc limit 3 ;

B
SELECT   sex ,substr(name,1,1) as first_name ,count(*) as c1 
from student_table where length(name) >=1  and sex = '男'
group by first_name  order by  sex ,c1 desc limit 3 
union all 
SELECT   sex ,substr(name,1,1) as first_name ,count(*) as c1 
from student_table where length(name) >=1  and sex = '女'
group by first_name  order by  sex ,c1 desc limit 3 ;

C
select * from (
SELECT   sex ,substr(name,1,1) as first_name ,count(*) as c1 
from student_table where length(name) >=1  and sex = '男'
group by first_name  order by  sex ,c1 desc limit 3 
) t1 
UNION all
select * from (
SELECT   sex ,substr(name,1,1) as first_name ,count(*) as c1 
from student_table where length(name) >=1  and sex = '女'
group by first_name  order by  sex ,c1 desc limit 3 
) t2 ;

D
select * from (
SELECT   sex ,substr(name,1,1) as first_name ,count(*) as c1 
from student_table where length(name) >=1  and sex = '男'
group by first_name  having c1 >=3  order by  sex ,c1 desc 
) t1 
UNION all
select * from (
SELECT   sex ,substr(name,1,1) as first_name ,count(*) as c1 
from student_table where length(name) >=1  and sex = '女'
group by first_name having c1 >=3  order by  sex ,c1 desc limit 3 
) t2 ;