查询数据 - 简书

查询数据

基本查询
ALTER TABLE students ADD CONSTRAINT fk_class_id FOREIGN KEY (class_id) REFERENCES classes (id)
ALTER TABLE students DROP FOREIGN KEY fk_class_id;
ALTER TABLE students ADD INDEX idx_score (score);
ALTER TABLE students ADD INDEX idx_name_score (name, score);
ALTER TABLE students ADD UNIQUE INDEX uni_name (name);
ALTER TABLE students ADD CONSTRAINT uni_name UNIQUE (name);
条件查询
SELECT * FROM students;
SELECT * FROM <表名> WHERE <条件表达式>
投影查询
SELECT 列1, 列2, 列3 FROM ...
SELECT id, score, name FROM students;
SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ...
SELECT id, score points, name FROM students;
排序
SELECT id, name, gender, score FROM students ORDER BY score;
SELECT id, name, gender, score FROM students ORDER BY score DESC;
SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;
SELECT id, name, gender, score FROM students WHERE class_id = 1 ORDER BY score DESC;
分页查询
LIMIT <M> OFFSET <N>
SELECT id, name, gender, score FROM students ORDER BY score DESC LIMIT 3 OFFSET 0;
Empty result set
聚合查询
SELECT COUNT(*) FROM students;
SELECT COUNT(*) num FROM students;
SELECT COUNT(*) boys FROM students WHERE gender = 'M';
SELECT AVG(score) average FROM students WHERE gender = 'M';
SELECT AVG(score) average FROM students WHERE gender = 'X';
NULL
SELECT COUNT(*) num FROM students GROUP BY class_id;
SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;
SELECT name, class_id, COUNT(*) num FROM students GROUP BY class_id;
SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;
SELECT class_id,AVG(score) avgScore FROM students GROUP BY class_id;
SELECT class_id,gender,AVG(score) avgScore FROM students GROUP BY class_id,gender;
多表查询
SELECT * FROM <表1> <表2>
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;
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, s.gender, s.score FROM students s;
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;
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score FROM students s RIGHT OUTER JOIN classes c ON s.class_id = c.id;
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score FROM students s LEFT OUTER JOIN classes c ON s.class_id = c.id;
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score FROM students s FULL OUTER JOIN classes c ON s.class_id = c.id;

最后编辑于：2020.07.07 19:55:08