SQL学习笔记_统计各个部门的班级数,要求显示部门编号,部门名称和班级数量-CSDN博客

本文链接：https://blog.csdn.net/qq_43672268/article/details/109712055

关系数据库概述

用来管理数据。不用关心怎样存储数据，使用数据库软件提供的接口来读写数据
数据模型：
- 层次模型：以上下级的层次关系来组织数据，像一棵树
- 网状模型：网状结构
- 关系模型：一张表，其中的每个数据都可以通过行号+列号来唯一确认（占绝对市场份额）

数据类型：

名称	类型	说明
INT	整型	4字节整数类型；范围大约正负21亿
BIGINT	长整型	8字节整数类型；范围大约正负922亿亿
REAL,FLOAT(24)	浮点型	4字节浮点数；范围大约正负10的38次
DOUBLE	浮点型	8字节浮点数；范围大约正负10的308次
DECIMAL(M,N)	高精度小数	由用户指定精度的小数；例如DECIMAL(20,10)表示一共20位，其中小数10位，通常用于财务计算
CHAR(N)	定长字符串	存储指定长度的字符串；例如CHAR(100)总是存储100个字符的字符串
CARCHAR(N)	变长字符串	存储可变长度的字符串；例如VARCHAR(100)可以存储0-100个字符的字符串
BOOLEAN	布尔类型	存储True或者False
DATE	日期类型	存储日期；例如2018-10-12
TIME	时间类型	存储时间；例如12：20：59
DATETIME	日期和时间	存储日期+时间；例如2018-06-22 12：20：59

SQL

SQL是结构化查询语言的缩写，用来访问和操作数据库系统。SQL语句既可以查询数据库中的数据，也可以添加、更新和删除数据库中的数据，还可以对数据库进行管理和维护操作。不同的数据库，都支持SQL

关系模型

关系数据库是建立在关系模型上的。而关系模型本质上就是若干个存储数据的二维表，可以看作为Excel表。
表的每一行称为记录（Record），记录是一个逻辑意义上的数据。
表的每一列成为字段（Column），同一个表的每一行数据都拥有相同的若干字段
字段定义了数据类型（整型、浮点型、字符串、日期等），以及是否允许为NULL。注意NULL表示字段数据不存在。一个整型字段如果为NULL不表示它的值为0，同样的，一个字符串型字段为NULL也不表示它的值为空串''。，通常情况下字段应该避免允许为NULL。
关系数据库的表和表之间需要建立“一对多”，“多对一”和“一对一”的关系，这样才能够按照应用程序的逻辑来组织和存储数据。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tsAmvWLA-1605453954836)(https://s3.ax1x.com/2020/11/14/DCal90.png)]

主键

什么叫主键：

对于关系表，有个很重要的约束，就是任意两条记录不能重复。不能重复不是指两条记录不完全相同，而是指能够通过某个字段唯一区分出不同的记录，这个字段被称为主键。
选取主键的原则：
- 不使用任何业务相关的字段作为主键。因此，身份证号、手机号、邮箱地址这些看上去可以唯一的字段，均不可用作主键。
- 作为主键最好是完全业务无关的字段，我们一般把这个字段命名为id
  - 自增整数类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用自己预先生成主键；
  - 全局唯一GUID类型：使用一种全局唯一的字符串作为主键，类似8f55d96b-8acc-4636-8cb8-76bf8abc2f57。GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了GUID算法，可以自己预算出主键
联合主键：
- 关系数据库允许通过多个字段唯一标识记录，即两个或更多的字段都设置为主键，这种主键被称为联合主键。
- 只要不是两个主键都相同就可以（不常用，会带来复杂度的上升）

外键

什么叫外键：

将数据与另一张表关联起来，这种成为外键。例如在students表中，添加字段class_id，将students记录与class表关联起来
设置外键的方式：
- ```
// 定义外键约束
ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);
// 删除外键约束
ALTER TABLE students
DROP FOREIGN KEY fk_class_id;
```
- 使用应用程序来保证逻辑的正确性。例如：如查找小明的班级：先在students中找到小明对应的class_id，应用程序接着在classes中查找id为1的记录得到小明所在班级为一班，此为应用程序级别
多对多关系：
- 多对多关系实际上是通过两个一对多关系实现的，即通过一个中间表，关联两个一对多关系，就形成了多对多关系。
- 一对一关系是指，一个表的记录对应到另一个表的唯一一个记录。

索引

在关系数据库中，如果有上万甚至上亿条记录，在查找记录的时候，想要获得非常快的速度，就需要使用索引。
什么是索引：
- 索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。
```
// 根据score列进行查询，就可以对score列创建索引
ALTER TABLE students
ADD INDEX idx_score (score);
```
唯一索引：
- 在设计关系数据表的时候，看上去唯一的列，例如身份证号、邮箱地址等，因为他们具有业务含义，因此不宜作为主键。但是，这些列根据业务要求，又具有唯一性约束：即不能出现两条记录存储了同一个身份证号。这个时候，就可以给该列添加一个唯一索引。

查询数据

在关系数据库中，最常见的操作就是查询。

基本查询：

// 查询表中所有数据
SELECT * FROM <表名>

条件查询：

1. SELECT * FROM <表名> WHERE <条件表达式>
2. SELECT * FROM <表名> WHERE <条件表达式1> AND <条件表达式2>
3. SELECT * FROM <表名> WHERE <条件表达式1> OR <条件表达式2>
4. SELECT * FROM <表名> WHERE NOT <条件表达式2>

// 例如：查询students表中不是二班的数据
SELECT * FROM students WHERE NOT class_id = 2;

要组合三个或者更多的条件，就需要用小括号()表示如何进行条件运算。如果不加括号，条件运算按照NOT、AND、OR的优先级进行，即NOT优先级最高，其次是AND，最后是OR。加上括号可以改变优先级。

SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';

常见的条件表达式：

条件	表达式举例	说明
使用=判断相等	score=80	字符串需要用单引号括起来
使用>判断大于	score>80	字符串比较根据ASCII码，中文字符比较根据数据库设置
使用>=判断大于或相等	score >= 80
使用<判断小于	score < 80
使用<=判断小于或相等	score <= 80
使用<>判断不相等	score <> 80
使用LIKE判断相似	name LIKE ‘ab%’	%表示任意字符，例如’ab%‘将匹配’ab’，‘abc’，'abcd’0

投影查询：
- 使用SELECT *表示查询表的所有列，使用SELECT 列1, 列2, 列3则可以仅返回指定列，这种操作称为投影。
```
// 例如，从students表中返回id、score和name这三列
SELECT id, score, name FROM students;
```
- 使用SELECT 列1, 列2, 列3 FROM ...时，还可以给每一列起个别名，这样，结果集的列名就可以与原表的列名不同。它的语法是SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ...
```
// 例如，以下SELECT语句将列名score重命名为points，而id和name列名保持不变
SELECT id, score points, name FROM students;
```

排序

使用ORDER BY可以对结果集进行排序；

// 按照成绩从低到高进行排序
SELECT id, name, gender, score FROM students ORDER BY score;

// 加上`DESC`表示“倒序” :
SELECT id, name, gender, score FROM students ORDER BY score DESC;

// ORDER BY score DESC, gender表示先按score列倒序，如果有相同分数的，再按gender列排序
SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;

分页查询

分页：分页实际上就是从结果集中“截取”出第M~N条记录。这个查询可以通过LIMIT OFFSET子句实现

// 1. 先把所有学生按照成绩从高到低进行排序
SELECT id, name, gender, score FROM students ORDER BY score DESC;

// 2. 把结果集分页，每页3条记录。要获取第1页的记录，可以使用LIMIT 3 OFFSET 0
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0

// 2. 如果要查询第2页，那么我们只需要“跳过”头3条记录，也就是对结果集从3号记录开始查询，把OFFSET设定为3：
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 3;
*注意*：这里的OFFSET 3 指的从第三条记录开始查，只能查到第2页的3条数据

使用LIMIT OFFSET可以对结果集进行分页，每次查询返回结果集的一部分；

分页查询需要先确定每页的数量和当前页数，然后确定LIMIT和OFFSET的值。

聚合查询：

对于统计总数、平均数这类计算，SQL提供了专门的聚合函数，使用聚合函数进行查询，就是聚合查询，它可以快速获得结果。

// 查询students表一共有多少条记录,使用COUNT()函数
SELECT COUNT(*) FROM students;

// COUNT(*)表示查询所有列的行数，要注意聚合的计算结果虽然是一个数字，但查询的结果仍然是一个二维表，只是这个二维表只有一行一列，并且列名是COUNT(*)

// 通常，使用聚合查询时，我们应该给列名设置一个别名，便于处理结果
SELECT COUNT(*) num FROM students;

条件聚合查询：
- COUNT(*)和COUNT(id)实际上是一样的效果。另外注意，聚合查询同样可以使用WHERE条件，因此我们可以方便地统计出有多少男生、多少女生、多少80分以上的学生等：
```
// 条件聚合查询
SELECT COUNT(*) boys FROM students WHERE gender = 'M'; 
```
- 除了COUNT()函数外，SQL还提供了如下聚合函数
  
  函数说明
  SUM 计算某一列的合计值，该列必须为数值类型
  AVG 计算某一列的平均值，该列必须为数值类型
  MAX 计算某一列的最大值
  MIN 计算某一列的最小值
- MAX()和MIN()函数并不限于数值类型。如果是字符类型，MAX()和MIN()会返回排序最后和排序最前的字符
- 计算平均数
```
SELECT AVG(score) average FROM students WHERE gender = 'M';
```
- 如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL
- 分组聚合查询：
```
// GROUP BY 按照class_id, gender分组查询
SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;
```

函数	说明
SUM	计算某一列的合计值，该列必须为数值类型
AVG	计算某一列的平均值，该列必须为数值类型
MAX	计算某一列的最大值
MIN	计算某一列的最小值

多表查询：

SELECT查询不但可以从一张表查询数据，还可以从多张表同时查询数据。查询多张表的语法是：SELECT * FROM <表1> <表2>

// 多表查询时，要使用表名.列名这样的方式来引用列和设置别名，这样就避免了结果集的列名重复问题。但是，用表名.列名这种方式列举两个表的所有列实在是很麻烦，所以SQL还允许给表设置一个别名，让我们在投影查询中引用起来稍微简洁一点：

SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c;

// 多表查询也是可以添加WHERE条件
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;

连接查询：

连接查询是另一种类型的多表查询。连接查询对多个表进行JOIN运算，简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。

// 例如，我们想要选出students表的所有学生信息，可以用一条简单的SELECT语句完成：
SELECT s.id, s.name, s.class_id, s.gender, s.score FROM students s;

// 假设我们希望结果集同时包含所在班级的名称，上面的结果集只有class_id列，缺少对应班级的name列。存放班级名称的name列存储在classes表中，只有根据students表的class_id，找到classes表对应的行，再取出name列，就可以获得班级名称。
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;

INNER JOIN查询注意：
- 先确定主表，仍然使用FROM <表1>的语法；
- 再确定需要连接的表，使用INNER JOIN <表2>的语法；
- 然后确定连接条件，使用ON <条件...>，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接；
- 可选：加上WHERE子句、ORDER BY等子句。

外连接查询：
- 外连接查询包含:
  - LEFT OUTER JION: 选出左边存在的记录，右边的不存在的数据填充为NULL
  - RIGHT OUTER JION: 选出右边存在的记录，不存在的自动填充为NULL
  - FULL OUTER JION: 选出左右两个都存在的记录，自动填充NULL
  - 内连接INNER JION: 相当于两张表的并集，左右都存在的数据

修改数据

对于增、删、改，对应的SQL语句分别是：

INSERT：插入新记录；
UPDATE：更新已有记录；
DELETE：删除已有记录。

INSTER

使用INSERT，我们就可以一次向一个表中插入一条或多条记录

INSERT语句的基本语法是：

INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);

1. 对于id等自增属性或设定值不需要列出，在INSERT语句中可以不出现
2. 添加的字段可以不和表中的字段顺序保持一致，但是添加的值和字段名顺序必须一致
3. 可以一次性添加多条记录，只需要在VALUES子句中指定多个记录值，每个记录是由(...)包含的一组值

例如：
INSERT INTO students (class_id, name, gender, score) VALUES
  (1, '大宝', 'M', 87),
  (2, '二宝', 'M', 81);

UPDATE

使用UPDATE，我们就可以一次更新表中的一条或多条记录。

UPDATE语句的基本语法是：

UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;

// 例如：
UPDATE students SET name='大牛', score=66 WHERE id=1;

// 可以一次更新多条数据：
UPDATE students SET name='小牛', score=77 WHERE id>=5 AND id<=7;

// 可以在更新字段的操作中使用表达式：
UPDATE students SET score=score+10 WHERE score<80;

// UPDATE语句可以没有WHERE条件，这时表中的所有数据都会被改变，例如：
UPDATE students SET score=60;

DELETE

使用DELETE，我们就可以一次删除表中的一条或多条记录。

DELETE语句的基本语法是：

DELETE FROM <表名> WHERE ...;

// 例如：删除students表中id=1的记录
DELETE FROM students WHERE id=1;

// 可以删除多条记录
DELETE FROM students WHERE id>=5 AND id<=7;

// 当WHERE没有匹配到条件时，不会删除任何一个数据

// 和UPDATE类似，不带WHERE条件的DELETE语句会删除整个表的数据