SQL学习笔记

基本查询：

SELECT * FROM students;

条件查询：

SELECT * FROM students WHERE score >= 80;

投影查询：

SELECT id, score, name FROM students;

排序：

SELECT id, name, gender, score FROM students ORDER BY score;

分页查询：

SELECT id, name, gender, score FROM students ORDER BY score DESC;

聚合查询：对于统计总数、平均数这类计算，SQL提供了专门的聚合函数，使用聚合函数进行查询，就是聚合查询，它可以快速获得结果。

SELECT COUNT(*) FROM students;

SUM	计算某一列的合计值，该列必须为数值类型

AVG	计算某一列的平均值，该列必须为数值类型
MAX	计算某一列的最大值
MIN	计算某一列的最小值

 多表查询：

SELECT * FROM students, classes;

SELECT
    students.id sid,
    students.name,
    students.gender,
    students.score,
    classes.id cid,
    classes.name cname
FROM students, classes;

SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c
WHERE s.gender = 'M' AND c.id = 1;

连接查询：

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;

INNER JOIN：

 LEFT OUTER JOIN是选出左表存在的记录：

 RIGHT OUTER JOIN是选出右表存在的记录：

 

FULL OUTER JOIN则是选出左右表都存在的记录：

 添加记录：

INSERT INTO students (class_id, name, gender, score) VALUES
  (1, '大宝', 'M', 87),
  (2, '二宝', 'M', 81);

SELECT * FROM students;

更新数据：

UPDATE students SET name='大牛', score=66 WHERE id=1;
-- 查询并观察结果:
SELECT * FROM students WHERE id=1;

UPDATE students SET score=score+10 WHERE score<80;
-- 查询并观察结果:
SELECT * FROM students;

删除数据：

DELETE FROM students WHERE id=1;
-- 查询并观察结果:
SELECT * FROM students;