数据库SQL语句学习2

表t_student

1.删除表中id>10的数据

DELETE FROM t_student WHERE id > 10;

2.将表中id为1的数据删除标识更新为1，姓名更新为zhangsan

UPDATE t_student 
SET delete_flag = 1 , 
student_name = 'zhangsan' 
WHERE 
  id = 1;

3.表中插入学号为4000，姓名为李四，性别为F，删除标识为1的数据

INSERT t_student (student_id , student_name , sex , delete_flag)
VALUES
  (4000 , "李四" , 'F' , 1);

表t_score

1.找出成绩表中重复的数据

SELECT student_id , project_name , MAX(score) , COUNT(*) 
FROM t_score 
GROUP BY student_id , Project_name;
HAVING COUNT(*) > 1;  -- 数据出现次数大于1

t_student

t_score

连表查询

1.查询两表中学号相同的数据

SELECT * 
FROM t_score 
INNER JOIN t_student ON t_score.student_id = t_student.student_id;

2.

SELECT t.id , t.student_id as '学号' , student_name , score , sex
FROM t_score t
RIGHT JOIN t_student s ON t.student_id = s.student_id;

RIGHT JOIN 会以右边表为主表，先去左表找到跟它匹配的数据，然后显示出来，左表没有跟它匹配的数据就以空的形式展示出来

等同于

SELECT t.id , t.student_id as '学号' , student_name , score , sex
FROM t_student s
LEFT JOIN t_score t ON t.student_id = s.student_id;

INNER JOIN 必须两边的表都有数据，LEFT(RIGHT) JOIN 不需要

3.将学生的成绩查询分为优（90-100），良（75-90），可（60-75），差（小于60）

方法一
SELECT 
id , 
student_id , 
project_name , 
score ,
CASE
    WHEN score >= 90 THEN '优'
    WHEN score >= 75 AND score < 90 THEN '良'
    WHEN score >= 60 AND score < 75 THEN '可'
    ELSE '差'
END AS score_level  -- 新增列显示成绩
FROM
  t_score;
方法二
SELECT 
id , 
student_id , 
project_name , 
score ,
IF(score >= 90 , '优' , 
   IF(score >= 75 , '良' ,
      IF(score >= 60 , '可' , '差')
     )
  ) AS score_level
FROM
  t_score;

4.子查询

SELECT *
FROM t_score
WHERE student_id IN (
    SELECT student_id
    FROM t_student
    WHERE id > 5
)