SQL查询数据

基本查询

语法:SELECT * FROM <表名>

1
2
3
4
SELECT * FROM students;
SELECT * FROM classes;
SELECT 100+200; // 计算
SELECT 1; // 测试数据库连接

条件查询

语法:SELECT * FROM <表名> WHERE <条件表达式>

1
2
3
4
5
6
SELECT * FROM students WHERE score >= 80;
SELECT * FROM students WHERE score >= 80 AND gender = 'M';
SELECT * FROM students WHERE score >= 80 OR gender = 'M';
SELECT * FROM students WHERE NOT class_id = 2;
SELECT * FROM students WHERE class_id <> 2; // 与上一句等价
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';

不加括号,条件运算按照NOTANDOR的优先级进行
查询分数在60分(含)~90分(含)之间的学生可以使用的WHERE语句是:

1
2
WHERE score >= 60 AND score <= 90;
WHERE score BETWEEN 60 AND 90;

常用的条件表达式

条件 举例
使用=判断相等 name = 'abc',字符串需要用单引号括起来
使用<>判断不相等 score <> 80
使用LIKE判断相似 name LIKE 'ab%'%表示任意字符,例如'ab%'将匹配'ab','abc','abcd'

投影查询

语法:SELECT 列1, 列2, 列3 FROM ...SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ...

1
2
3
SELECT id, score, name FROM students;
SELECT id, score points, name FROM students;
SELECT id, score points, name FROM students WHERE gender = 'M';

排序

1
2
3
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; // 先按score列倒序,如果有相同分数的,再按gender列排序

ORDER BY子句要放到WHERE子句后面

1
2
3
4
SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;

分页查询

通过LIMIT <M> OFFSET <N>子句实现

1
2
3
4
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;

确定LIMITOFFSET应该设定的值:

  • LIMIT总是设定为pageSize
  • OFFSET计算公式为pageSize * (pageIndex - 1)

在MySQL中,LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15

聚合查询

聚合函数

1
2
3
4
5
SELECT COUNT(*) FROM students;
SELECT COUNT(*) num FROM students; // 使用聚合查询并设置结果集的列名为num
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';

常用聚合函数:
函数 | 说明
— | —
SUM | 计算某一列的合计值,该列必须为数值类型
AVG | 计算某一列的平均值,该列必须为数值类型
MAX | 计算某一列的最大值
MIN | 计算某一列的最小值

问题:在分页查询之前,如何计算一共有几页?
假如分页每页3行记录:

1
SELECT ceil(count(*) / 3) FROM students;

分组

1
2
3
4
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 * FROM <表1> <表2>
这种多表查询又称笛卡尔查询,由于结果集是目标表的行数乘积,使用笛卡尔查询时要非常小心

1
2
3
4
5
6
7
8
9
10
11
SELECT * FROM students, classes;

// 给列设置别名
SELECT
students.id sid,
students.name,
students.gender,
students.score,
classes.id cid,
classes.name cname
FROM students, classes;

FROM子句给表设置别名的语法是FROM <表名1> <别名1>, <表名2> <别名2>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
// 给表设置别名
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 ... FROM <表1> INNER JOIN <表2> ON <条件...>;

1
2
3
4
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;

外连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
// LEFT
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;

// RIGHT
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;

// FULL, MySQL报错
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;
打赏
  • 版权声明: 本博客所有文章除特别声明外,著作权归作者所有。转载请注明出处!
  • Copyrights © 2020-2021 zhangguoliu
  • 访问人数: | 浏览次数:

请我喝杯咖啡吧~

支付宝
微信