SQL练习

1.新建表sg不难自行解决。
但是需要限制grade的范围，因此我们需要用到约束

创建表时添加约束：

CREATE TABLE `Student` (
	`Age` tinyint(4) NOT NULL DEFAULT '20' CHECK (Age between 15 and 30),
	）ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建表之后添加约束：

alter table sg add constraint check(grade >0 and grade <100);
或者alter table sg add constraint check(grade between 0 and 100);

但是mysql中的约束不管用，添加 check约束之后向表中插入不合法数据依然会正常插入（实验有效）；

解决办法
使用触发器：添加触发器的代码：

DELIMITER $
create trigger sgcheck before insert on sg for each row
begin
if new.grade<0 or new.grade>100 then set new.grade=0;end if;
end $
DELIMITER ;

添加触发器之后，再在表中添加非法数据，就会触发触发器，就直接将非法数据置为0

2.求学号为1、3、4、等的学生成绩：考察关键字 IN

select * from sg where sno in(1,4,7,24)

3.考察关键字MAX和子查询
求比上述同学成绩都高的成绩

select * from sg where grade >(select max(grade) from sg where  sno in(1,12,23))

4.查询成绩评级，并按照成绩等级排列，每一个成绩等级默认是按照学号升序
考察case关键字

SELECT sno,grade,
CASE 
WHEN grade>=90 THEN 'A'
WHEN grade BETWEEN 80 AND 89 THEN 'B'
WHEN grade BETWEEN 70 AND 79 THEN 'C'
WHEN grade BETWEEN 60 AND 69 THEN 'D'
WHEN grade <=59 THEN 'E'
END AS degree
FROM sg 
ORDER BY degree

5.查询在上述分配区间的人数
考察count(*)以及group by

SELECT count(*),
CASE 
WHEN grade>=90 THEN 'A'
WHEN grade BETWEEN 80 AND 89 THEN 'B'
WHEN grade BETWEEN 70 AND 79 THEN 'C'
WHEN grade BETWEEN 60 AND 69 THEN 'D'
WHEN grade <=59 THEN 'E'
END AS degree
FROM sg 
group BY degree

备注知识点：

创建触发器的语法如下：

CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt

trigger_time 是触发器的触发时间，可以是BEFORE 或者AFTER，BEFORE 的含义指在检查约束前触发，而AFTER 是在检查约束后触发。
而trigger_event 就是触发器的触发事件，可以是INSERT、UPDATE 或者DELETE。
对同一个表相同触发时间的相同触发事件，只能定义一个触发器。

触发器只能创建在永久表（Permanent Table）上，不能对临时表（Temporary Table）创建触发器。

举例

 DELIMITER $$
　　CREATE TRIGGER ins_film
　　AFTER INSERT ON film FOR EACH ROW BEGIN
　　INSERT INTO film_text (film_id, title, description)
　　VALUES (new.film_id, new.title, new.description);
　　END;
$$
delimiter ;

删除触发器

mysql> drop trigger ins_film;

查看已有的触发器

SELECT * FROM information_schema.`TRIGGERS` where trigger_name="sgcheck";