SQL练习

1、alter的用法

• ALTER TABLE 表名 ADD 列名/索引/主键/外键等；
• ALTER TABLE 表名 DROP 列名/索引/主键/外键等；
• ALTER TABLE 表名 ALTER 仅用来改变某列的默认值；
• ALTER TABLE 表名 RENAME 列名/索引名 TO 新的列名/新索引名；
• ALTER TABLE 表名 RENAME TO/AS 新表名;
• ALTER TABLE 表名 MODIFY 列的定义但不改变列名；
• ALTER TABLE 表名 CHANGE 列名和定义都可以改变。

2、查看班里abcd的最后一名：

法一：统计abcd分别有几个 case,when

SELECT grade ,CASE grade WHEN 'A' THEN(SELECT SUM(number) 
                                       FROM class_grade WHERE grade <='A')
                         WHEN 'B' THEN(SELECT SUM(number) 
                                       FROM class_grade WHERE grade <='B')
                         WHEN 'C' THEN(SELECT SUM(number) 
                                       FROM class_grade WHERE grade <='C')
                         WHEN 'D' THEN(SELECT SUM(number) 
                                       FROM class_grade WHERE grade <='D')
                         ELSE (SELECT SUM(number) FROM class_grade) 
                         END AS t_cnt
FROM class_grade
ORDER BY grade ASC;

法二：使用窗口函数sum(),over():

SELECT grade,SUM(number) OVER(ORDER BY grade ASC) AS t_cnt
FROM class_grade
ORDER BY grade ASC;

select grade, sum(number) over(order by grade) as t_rank
from class_grade

法三：使用自连接：

select t1.grade, sum(t2.number) as t_rank
from class_grade as t1
join class_grade as t2
on t1.grade >= t2.grade
group by t1.grade
order by t1.grade