1 基本查询
SELECT * FROM 表名;
SELECT * FROM students;
students表
id | class_id | name | gender | score |
---|
1 | 1 | 小白 | M | 87 |
2 | 2 | 小黄 | F | 90 |
3 | 1 | 小橘 | F | 72 |
4 | 2 | 小红 | M | 92 |
5 | 1 | 小粉 | M | 69 |
2 条件查询
SELECT * FROM 表名 WHERE 条件;
SELECT * FROM students WHERE score > 70;
id | class_id | name | gender | score |
---|
1 | 1 | 小白 | M | 87 |
2 | 2 | 小黄 | F | 90 |
3 | 1 | 小橘 | F | 72 |
4 | 2 | 小红 | M | 92 |
查询条件用WHERE连接,查询逻辑包括NOT, AND, OR,优先级依次减小。
SELECT * FROM students WHERE NOT gender = 'F';
id | class_id | name | gender | score |
---|
1 | 1 | 小白 | M | 87 |
4 | 2 | 小红 | M | 92 |
5 | 1 | 小粉 | M | 69 |
SELECT * FROM students WHERE gender = 'M' AND score > 80;
id | class_id | name | gender | score |
---|
1 | 1 | 小白 | M | 87 |
4 | 2 | 小红 | M | 92 |
SELECT * FROM students WHERE gender = 'F' OR class_id = 1;
id | class_id | name | gender | score |
---|
1 | 1 | 小白 | M | 87 |
2 | 2 | 小黄 | F | 90 |
3 | 1 | 小橘 | F | 72 |
5 | 1 | 小粉 | M | 69 |
3 映射查询
映射查询可以从表中选择字段进行查询,不会对表结构产生影响。
SELECT 字段 FROM 表名;
SELECT id, name, score FROM students;
id | name | score |
---|
1 | 小白 | 87 |
2 | 小黄 | 90 |
3 | 小橘 | 72 |
4 | 小红 | 92 |
5 | 小粉 | 69 |
映射查询可搭配条件查询
SELECT id, name, score FROM students
WHERE score < 80;
4 排序
排序语句可以对数据进行排列
SELECT * FROM 表名 ORDER BY 字段;
SELECT * FROM students ORDER BY score;
id | class_id | name | gender | score |
---|
5 | 1 | 小粉 | M | 69 |
3 | 1 | 小橘 | F | 72 |
1 | 1 | 小白 | M | 87 |
2 | 2 | 小黄 | F | 90 |
4 | 2 | 小红 | M | 92 |
从小到大排序:ASC;从大到小排序:DESC
ORDER BY默认从小到大排序(ASC)
排序语句也可搭配条件查询,排序语句需要放在条件语句后面
SELECT * FROM students
WHERE gender = 'M'
ORDER BY score DESC;
id | class_id | name | gender | score |
---|
4 | 2 | 小红 | M | 92 |
1 | 1 | 小白 | M | 87 |
5 | 1 | 小粉 | M | 69 |