sql学习——查询数据

最近在廖雪峰老师的网站学习sql，以下为学习记录，方便日后查询。

https://www.liaoxuefeng.com/wiki/1177760294764384

一些基本概念：

1.主键

在关系数据库中，一张表中的每一行数据被称为一条记录。一条记录就是由多个字段组成的。

对于关系表，有个很重要的约束，就是任意两条记录不能重复。不能重复不是指两条记录不完全相同，而是指能够通过某个字段唯一区分出不同的记录，这个字段被称为主键。

对主键的要求，最关键的一点是：记录一旦插入到表中，主键最好不要再修改，因为主键是用来唯一定位记录的，修改了主键，会造成一系列的影响。

选取主键的一个基本原则是：不使用任何业务相关的字段作为主键。

因此，身份证号、手机号、邮箱地址这些看上去可以唯一的字段，均不可用作主键。

作为主键最好是完全业务无关的字段，我们一般把这个字段命名为id。常见的可作为id字段的类型有：

A.自增整数类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用自己预先生成主键；

B.全局唯一GUID类型：使用一种全局唯一的字符串作为主键，类似8f55d96b-8acc-4636-8cb8-76bf8abc2f57。GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了GUID算法，可以自己预算出主键。

对于大部分应用来说，通常自增类型的主键就能满足需求。我们在students表中定义的主键也是BIGINT NOT NULL AUTO_INCREMENT类型。

2.联合主键

关系数据库实际上还允许通过多个字段唯一标识记录，即两个或更多的字段都设置为主键，这种主键被称为联合主键。

3.外键

一个表中的某一列能把数据与另一张表关联起来，这种列称为外键。

关系数据库通过外键可以实现一对多、多对多和一对一的关系。外键既可以通过数据库来约束，也可以不设置约束，仅依靠应用程序的逻辑来保证。

4.索引

索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。

通过对数据库表创建索引，可以提高查询速度。

通过创建唯一索引，可以保证某一列的值具有唯一性。

数据库索引对于用户和应用程序来说都是透明的。

 

在廖老师的网站中，提供了一个在线sql的工具，利用提前准备好的students和classes的表，我做了一些查询和修改的基本练习。常用的操作如下：

1.基本查询

--查询数据库表的数据
--SELECT * FROM <表名>
--SELECT是关键字，表示将要执行一个查询，*表示“所有列”，FROM表示将要从哪个表查询，本例中是students
--查询结果是一个二维表，它包含列名和每一行的数据
--SELECT语句其实并不要求一定要有FROM子句。SELECT 100+200也会有结果
SELECT * FROM student

2.条件查询

--SELECT语句可以通过WHERE条件来设定查询条件，查询结果是满足查询条件的记录。
--SELECT * FROM <表名> WHERE <条件表达式>
--条件表达式可以用<条件1> AND <条件2>表达满足条件1并且满足条件2。第二种条件是<条件1> OR <条件2>，第三种条件是NOT <条件>，表示“不符合该条件”的记录
SELECT * FROM students WHERE score>=90
SELECT * FROM students WHERE score>=80 AND score<=90 
SELECT * FROM students WHERE score BETWEEN 80 AND 90
SELECT * FROM students WHERE score>=80 or gender="F"
SELECT * FROM students WHERE NOT gender="F"
--上述NOT gender="F" 等同于gender<>"F"
--如果不加括号，条件运算按照NOT、AND、OR的优先级进行，即NOT优先级最高，其次是AND，最后是OR。加上括号可以改变优先级

3.投影查询

--如果我们只希望返回某些列的数据，而不是所有列的数据，我们可以用SELECT 列1, 列2, 列3 FROM ...，让结果集仅包含指定列。这种操作称为投影查询。
--SELECT 列1 别名1，列2 别名2 FROM <表名>
SELECT id new_id, score new_score, name new_name FROM students
SELECT id new_id, score new_score, name new_name FROM students WHERE gender="F"
--gender虽然没有投影出来，但是在条件筛选中依旧可以使用，如果用投影出来的列作为筛选条件，应该用原列名

4.排序

--查询结果集通常是按照id排序的，也就是根据主键排序。如果要按照其他条件排序，可选择ORDER BY
SELECT id, name, gender, score FROM students ORDER BY score
SELECT id, name, gender, score FROM students ORDER BY score DESC,gender
--默认的排序规则是ASC：“升序”，即从小到大。ASC可以省略，即ORDER BY score ASC和ORDER BY score效果一样
-- 带WHERE条件的ORDER BY
SELECT id, name, gender, score
FROM students
WHERE class_id=1
ORDER BY score DESC

5.分页查询

--分页实际上就是从结果集中“截取”出第M~N条记录。这个查询可以通过LIMIT <M> OFFSET <N>子句实现。
SELECT id, name, gender
FROM students 
ORDER BY score DESC
LIMIT 3 OFFSET 0
--述查询LIMIT 3 OFFSET 0表示，对结果集从0号记录开始，最多取3条。注意SQL记录集的索引从0开始。
--分页查询的关键在于，首先要确定每页需要显示的结果数量pageSize（这里是3），然后根据当前页的索引pageIndex（从1开始），确定LIMIT和OFFSET应该设定的值：
--LIMIT总是设定为pageSize；
--OFFSET计算公式为pageSize * (pageIndex - 1)。
--OFFSET超过了查询的最大数量并不会报错，而是得到一个空的结果集。
--OFFSET是可选的，如果只写LIMIT 15，那么相当于LIMIT 15 OFFSET 0。在MySQL中，LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15。

6.聚合查询

--对于统计总数、平均数这类计算，SQL提供了专门的聚合函数，使用聚合函数进行查询，就是聚合查询，它可以快速获得结果。
--通常，使用聚合查询时，我们应该给列名设置一个别名，便于处理结果。F_number为赋给COUNT(*)结果的别名，注，COUNT(*)和COUNT(id)同样效果
SELECT COUNT(*) F_number FROM students WHERE gender="F"
--函数	说明
--SUM	计算某一列的合计值，该列必须为数值类型
--AVG	计算某一列的平均值，该列必须为数值类型
--MAX	计算某一列的最大值
--MIN	计算某一列的最小值
--注意，MAX()和MIN()函数并不限于数值类型。如果是字符类型，MAX()和MIN()会返回排序最后和排序最前的字符
SELECT AVG(score) average FROM students WHERE gender="F"
--如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL
--分组聚合功能
SELECT class_id, COUNT(*) number FROM students GROUP BY class_id
--聚合查询的列中，只能放入分组的列。

7.多表查询

--查询多张表的语法是：SELECT * FROM <表1> <表2>
SELECT * FROM students,classes
--这种一次查询两个表的数据，查询的结果也是一个二维表，它是students表和classes表的“乘积”，即students表的每一行与classes表的每一行都两两拼在一起返回。结果集的列数是students表和classes表的列数之和，行数是students表和classes表的行数之积
--由于存在两个表中有相同列名的问题，所以可以利用投影查询中"设置列的别名"来给各自的id和name取名
SELECT 
s.id sid,
s.name sname,
s.gender,
c.id cid,
c.name cname
FROM students s,classes c
--多表查询也是可以添加WHERE条件的
SELECT 
s.id sid,
s.name sname,
s.gender,
c.id cid,
c.name cname
FROM students s,classes c
WHERE s.gender="F"
AND c.id=1

8.连接查询

--连接查询对多个表进行JOIN运算，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。
--SELECT ... FROM tableA ??? JOIN tableB ON tableA.column1 = tableB.column2;
SELECT s.id, s.name, s.class_id, c.name, s.gender, s.score 
FROM students s
INNER JOIN classes c
ON s.class_id=c.id
--先确定主表，仍然使用FROM <表1>的语法；
--再确定需要连接的表，使用INNER JOIN <表2>的语法；
--然后确定连接条件，使用ON <条件...>，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接；
--可选：加上WHERE子句、ORDER BY等子句。

--OUTER JOIN
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;
--INNER JOIN只返回同时存在于两张表的行数据
--RIGHT OUTER JOIN返回右表都存在的行。如果某一行仅在右表存在，那么结果集就会以NULL填充剩下的字段。
--LEFT OUTER JOIN则返回左表都存在的行。如果我们给students表增加一行，并添加class_id=5，由于classes表并不存在id=5的行，所以，LEFT OUTER JOIN的结果会增加一行，对应的class_name是NULL
--使用FULL OUTER JOIN，它会把两张表的所有记录全部选择出来，并且，自动把对方不存在的列填充为NULL

参考：https://www.liaoxuefeng.com/wiki/1177760294764384/1179610621264704