SQL tutorials

SQL Literature

SQL运行在资料库管理系统（Database Management System），如MySQL，Postgre SQL，Microsoft SQL Server， Oracle，etc。

SQL练习平台：https://sqliteviz.com/

EXAMPLE

SQL 语法

Basis

SELECT * FROM “students”;

1. SELECT: 选取
2. *： 全部
3. FROM “students” ： 从 students表格查询，表格名称需要用""来做标注。

限制columns属性

如果不需要全部的columns，只需要部分，那么可以单独说明需要哪些columns，如：

SELECT 姓名，班级，成绩 FROM “students”;

限制rows属性

1. 限制rows的数目，如只需要看5行，i.e. 前1-5行

SELECT 姓名，班级，成绩 FROM “students” LIMIT 5;

若看完这前5行，想继续往后看之后的5行，i.e. 前6-10行

SELECT 姓名，班级，成绩 FROM “students” LIMIT 5 OFFSET 5;

OFFSET x像是将前x行屏蔽，再进行LIMIT y操作
因此，若是要看第11-15，OFFSET 10， LIMIT 5

SELECT 姓名，班级，成绩 FROM “students” LIMIT 5 OFFSET 10;

WHERE

文字比对

WHERE 是用来设定查询时的筛选条件
例如，在显示成绩时，只显示1年2班：

SELECT 姓名，班级，成绩 FROM “students”;
WHERE 班级 = '1年2班'

或者，在显示成绩时，就不显示1年2班，（不等于<>）

SELECT 姓名，班级，成绩 FROM “students”;
WHERE 班级 <> '1年2班'

ORDER BY

现在若是想要将同班的同学放在一起，可以使用ORDER BY,ORDER BY会针对一个column或多个columns进行排序。
单个column排序，e.g.

SELECT 姓名，班级，成绩 FROM “students”;
WHERE 班级 <> '1年2班'
ORDER BY 班级

这样就可以使得将同班同学放在一起，以排序

多个columns排序，e.g.

SELECT 姓名，班级，成绩 FROM “students”;
WHERE 班级 <> '1年2班'
ORDER BY 班级, 成绩；

这样就可以先按班级排序，再在班级内部按照成绩正序排序。

若是要按照成绩逆序排序，需要在成绩后添加DESC

SELECT 姓名，班级，成绩 FROM “students”;
WHERE 班级 <> '1年2班'
ORDER BY 班级, 成绩DESC；

单个人

现在只想查看某一个学生的信息：

SELECT 姓名，班级，成绩 FROM “students”;
WHERE 姓名 = '张小婷'

但如若忘记该学生姓名，只记得部分，可以使用如下查询：
1.

SELECT 姓名，班级，成绩 FROM “students”;
WHERE 姓名 LIKE '张%'

其中的“%”表示万用字元，表示单个或多个字元。如此系统会将所有张姓同学列出。

SELECT 姓名，班级，成绩 FROM “students”;
WHERE 姓名 LIKE '张_'

其中的“_”表示1个字元。如此系统会将所有张姓且名字只包含两个字的同学列出。

数字大小判断

SELECT 姓名，班级，成绩 FROM “students”;
WHERE 成绩 >= 80 AND 成绩< 90;

或者

SELECT 姓名，班级，成绩 FROM “students”;
WHERE 成绩 BETWEEN 80 AND 90;

AND 还可以更复杂的使用，如

SELECT 姓名，班级，成绩 FROM “students”;
WHERE 成绩 BETWEEN 80 AND 90 AND (班级 = '1年1班' OR 班级 = '1年2班');

也可以使用 IN来简化

SELECT 姓名，班级，成绩 FROM “students”;
WHERE 成绩 BETWEEN 80 AND 90 AND (班级 in ('1年1班' OR '1年2班'));

利用函数对多笔资料进行汇总和计算

常见函数

1. AVG 求平均值
2. SUM 求和
3. MAX 求最大值
4. MIN 求最小值
5. COUNT 计算数量

AVG

直接将Average函数套用在成绩column，查出所有同学的平均分数

SELECT AVG(成绩) FROM “students”;

PS.

1. 可利用上述函数得到的值命名为新的值

SELECT AVG(成绩) AS 成绩平均, MAX(成绩) AS 最高分
FROM “students”;

2. 对于所求值四舍五入

SELECT ROUND(AVG(成绩))
FROM “students”;

ROUND(AVG(成绩)) 四舍五入保留整数位
ROUND(AVG(成绩), 1) 四舍五入并保留小数1位

3. 想分类的并不是全班同学的平均，而是各班的平均

SELECT ROUND(AVG(成绩))
FROM “students”;
GROUP BY 班级;
ORDER BY 成绩平均 DESC; //引入排序

注意，如果使用了GROUP BY分组后的资料，要使用WHERE筛选，需要改为HAVING

SELECT ROUND(AVG(成绩)) AS 平均分
FROM “students”;
GROUP BY 班级;
HAVING 平均分 >= 80;
ORDER BY 成绩平均 DESC; //引入排序

关键字还需要以一定顺序进行书写：

COUNT

1. 计算整个表格的总行数

SELECT COUNT(*) 
FROM “students”;

2. 计算表格中某个column的行数

SELECT COUNT(社团) 
FROM “students”;

数字不同是因为，COUNT在针对单一column进行计数时，会自动忽略null。

2. COUNT 与DISTINCT 进行配合
   DISTINCT用于排除表格中重复的资料

SELECT COUNT(DISTINCT(社团)) 
FROM “students”;

此时，代码表示学校共有四种不同的社团。

SELECT DISTINCT(社团)
FROM “students”;

这样就能显示出不同社团的名字

用SQL如何建立表格

表格的创立

可以用SQL来补齐表格的相关信息

CREATE TABLE clubs(
社团编号 INT PRIMARY KEY，
社团名称 VARCHAR(15),
);

1.创建CREATE 表格TABLE
2.在其中补充需要的column：如社团编号，社团名称
3. 并需要指定column储存的资料是什么类型，在这里INT表示整数，VARCHAR表示字符串，对于VARCHAR需要指出字数上限
4. 表格内需要指定一个column为 PRIMARY KEY（就像是身份证号，不可以重复！！也不可以是NULL）用于识别每一笔的资料，如这里指定社团编号为PRIMARY KEY。

P.S. 如何删除表格，使用DROP

DROP TABLE clubs2;

表格中资料存储

使用 INSERT INTO 表格名（需要插入资料的column标题）

INSERT INTO clubs (社团编号，社团名称)

现在开始输入资料，使用VALUE ()，注意括号内的顺序必须要和column相互对应。

INSERT INTO clubs (社团编号，社团名称)
VALUES (101, 'Guitar Club'), (102, 'Piano Clube');

更新表格中的资料

注意，在更新表格时，要用WHERE说明更新哪一笔记录，若未设定会导致所有社团名称都UPDATE。

UPDATE clubs;
SET 社团名称 = 'Dance Club'
WHERE 社团编号 = 101;

同样，对于删除表格中某一笔记录

DELETE
FROM clubs;
WHERE 社团编号 = 101

SQL跨表格查询

SELECT students.姓名， students.社团， clubs.社团名称
FROM students //先引入1个表格
LEFT JOIN clubs //加入另一个表格
On students.社团 = clubs.社团编号
WHERE 班级 = '1年1班'

表格之间如何连接，此处是用students的社团连接上clubs的社团编号

1. INNER JOIN
   只返回两个表中匹配的记录
2. LEFT JOIN
   以左表为中心，返回左表的所有数据，即使右表中没有匹配；右表没有匹配时用 NULL 填充。
3. RIGHT JOIN
   以右表为中心，返回右表的所有数据，即使左表中没有匹配。
4. FULL JOIN
   返回两个表的所有记录，不匹配的部分用 NULL 填充。