SQL-数据库基础知识学习笔记

了解SQL

SQL相关术语

1.数据库（database)：存储相关数据的容器。通常指的是关系型数据库（SQL Server、Mysql等），这种数据库内部用二维表来组织数据，一张表即一个关系，一行即一条记录，一列即一个字段或属性。
2.表（table）:在database中组织数据的一种形式，存储指定类型的数据。同一个数据库中，表的名字是唯一的。
3.列（colum）：列是表中的一个字段，表由多个列即字段组成，每一列都有指定的数据类型。
4.数据类型（datatype）：存储数据的格式，类似编程语言中对数据类型的定义，有一些比如货币、日期时间这样的不同于编程语言的数据类型。
4.行（row）：表中的一个记录，顾名思义。
5.主键（primary key）：本质是表中的列（可以有一个或多个主键），其值可以唯一标识每一行。
成为主键的条件：
任意两行都具有不相同的主键值；
主键列值必须存在，即不为null；
主键列中的值不准修改或更新；
某一行被删除，主键的值不能赋给新的行；
6.外键（foreign key）：将多个表关联起来的列，本质是主键。
7.SQL：全称为结构化查询语句。不同的关系数据库可能有不同的界面操作，但是SQL语句几乎对所有的数据库来说是公用的。

注释

用SQL Server来进行实现：
1.单行注释：

2.多行注释

数据查询

SELECT语句

SELECT 用来从表中选择一个或多个列的值出来，以新表的形式展现，通常与” FROM“配合

查询单个列

SELECT column FROM table
SQL语句不区分大小写，SELECT与select是一样的。

查询多个列

SELECT column1,column2,column3… FROM table
注意：SQL语句和编程语言一样，符号必须为英文符号

查询所有列

SELECT * FROM table

特殊查询

仅显示不同的值

SELECT DISTINCT column FROM table
将所查询列中重复出现的值省略

限制结果

SELECT TOP number/percentage column FROM table
TOP关键字限制返回前几行，后面可以给出行数或者比例

排序查询

如果不排序，数据库一般以数据添加到表里的顺序显示，但如果之后出现更改/删除，这个顺序一定会受到影响。可以使用ORDER BY语句进行排序。

ORDER BY语句

ORDER BY语句后取一个或多个列名，按照这些列的数据进行排序。
使用ORDER BY语句要是此语句为查询语句的最后一个子句，否则可能出现问题。

按单个列排序

SELECT column FROM table
ORDER BY column
既可以按照要查询的列排序，也可以按照非查询的列进行排序

按多个列排序

SELECT column1，column2，column3 FROM table
ORDER BY column1，column2
首先按column1，再按column2排序；
若column1中不存在重复值，则不会在按column2排序

按列的相对位置排序

SELECT column1，column2，column3 FROM table
ORDER BY 2，3
2，3指的是相对位置，即被选中的列中的第二列与第三列——column2、column3；
若SELECT后的列名改变，排序依据的列名也会对应改变；
显然无法按照未选中的列进行排序；

指定排序方向

排序有升序排序和降序排序，通过不同的关键字来表示。

降序排序（DESC）

SELECT column FROM table
ORDER BY column DESC
如果是按多个列排序，只有第一个列名按倒序排列

升序排序（ASC）

一般排序均为升序排序，不特别声明ASC（可加可不加）

SELECT column FROM table
ORDER BY column ASC

有条件的查询

WHERE 语句

WHERE子句为SELECT语句的查询设置搜索条件，也称过滤条件，符合WHERE子句后条件的查询结果才会显示。WHERE子句在FROM子句之后

WHERE子句操作符

WHERE子句的操作符是WHERE子句后的条件可以具有多种功用，条件始终是布尔类型

检查单个值

SELECT column FROM table
WHERE column>30

不匹配检查

SELECT column FROM table
WHERE column<>30
<>和!=都是不等于，但是不是所有数据库管理系统两种都支持

范围检查

SELECT column FROM table
WHERE column BETWEEN 30 AND 40

空值检查

SELECT column FROM table
WHERE column is not null

组合WHERE子句

正如运算符有算数运算符（+、-、*、/等）和逻辑运算符（与或非），WHERE子句的操作符也有逻辑操作符。WHERE子句的逻辑操作符，与（AND）、或（OR）、非（NOT）、包含(IN)。

AND操作符

AND操作符即“与”的含义，将多个筛选条件进行合并，查询到的内容符合所有条件
SELECT column FROM table
WHERE column>30 AND column<>38

OR操作符

OR操作符即“或”的含义将多个筛选条件合并，查询到的内容至少符合其中一个条件
SELECT column FROM table
WHERE column is null OR column<30

特别注意：AND操作符和OR操作符同时使用时，AND操作符优先级更高，会优先组合；使用圆括号可以改变优先级，道理都懂

IN操作符

IN操作符表示包含在一定条件范围内，此条件范围表示为圆括号括起来，内部由逗号分隔,功能与OR操作符相当，但更有优势一些。
SELECT column FROM table
WHERE column IN (”C001“，”C002“)

NOT操作符

NOT操作符用来否定其他条件，不单独使用。
SELECT column FROM table
WHERE column NOT IN (”C001“，”C002“)

通配符

LIKE操作符用来使用通配符，通配符是用来定义要查询内容的格式的。

百分号（%）通配符

%通配符表示任何字符出现了任意次数。

例：查询学生信息表（S）中所有姓杨的同学的所有信息
SELECT * FROM S
WHERE 姓名 LIKE ‘杨%’

下划线（_）通配符

作用与”%“相同，但是只能匹配单个字符，不能多也不能少

还以上个例子为例，
例：查询学生信息表（S）中姓杨的同学的所有信息
SELECT * FROM S
WHERE 姓名 LIKE ‘杨_’
这样查询出的杨同学都是 杨某，而杨某某、杨某某某某都不会被查询出来

方括号（[]）通配符

用来指定一个字符集，用来匹配指定位置的一个字符，和”或“的含义很像。
例：查询学生信息表（S）中所有姓杨或姓赵的同学的所有信息
SELECT * FROM S
WHERE 姓名 LIKE ‘[杨赵]%’
否定含义，在[内部最前边加^]
例：查询学生信息表（S）中除了姓杨和姓赵的同学的所有信息
SELECT * FROM S
WHERE 姓名 LIKE ‘[^杨赵]%’

创建计算字段

当表中没有对我们直接有用的数据时，在运行SELECT语句内创建的。

拼接字段

将多个值连接起来成为单个值。
例：查询学生信息表（S）中所有学生的姓名和学号，姓名学号作为单个值显示，学号在括号内。
SELECT RTRIM（姓名）+‘（’+RTRIM(学号)+‘）’ FROM S
RTRIM()函数用来消除右边填充的空格，LTRIM()则是左边，TRIM（）则是左右两边

使用别名

AS语句来给原来表中的列命名一个别名。
SELECT name AS 姓名 FROM S

执行算术计算

对查询出的数据进行计算
SELECT 商品名，单价，个数，单价*个数 AS 总金额
FROM 货物

使用数据处理函数

SQL语言的函数因对应的数据库管理系统不同而不同

使用函数

大多数SQL实现支持以下类型的函数：
用于处理文本字符串（如删除或填充值，转换值为大写或小写）的文本函数；
用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数；
用于处理日期和时间值并从这些值中提取特定成分（如返回两个日期之差，检查日期有效性）的日期和时间函数；
返回 DBMS 正使用的特殊信息（如返回用户登录信息）的系统函数；

文本处理函数

日期和事件处理函数

只提SQL Server

DATEPART()函数

DATEPART(日期中的成分,日期)，两个参数，第一个参数是日期中的年、月或日，第二个参数是被从中返回的日期。

GETDATE()函数

获取当前日期

year()函数、month()函数、day()函数

year（日期）直接获取年份，剩余两个函数同理。

数值处理函数

汇总数据

聚集函数用来汇总表的数据

聚集函数

单纯地汇总数据，而不需要把具体数据查询出来。
确定表中行数（或者满足某些条件的行数）；
获得表中某些行的和；
找出某些列的最大值、最小值、平均值

注意：使用聚集函数后要给新列取别名；
如果要聚集唯一值，可以使用之前提到的DISTINCT

AVG()函数

求某一列的平均值，要分别求多个列的平均值要使用多个AVG()函数

COUNT()函数

COUNT()函数有两种使用方式：
COUNT(*)对表中行的数量进行计数；
COUNT（column）对特定列中的值进行计数，忽略null

MAX()函数

求某一列的最大值

MIN()函数

求某一列的最小值

SUM()函数

求指定列的值的总和，求和函数。

数据分组

GROUP BY语句

聚集函数在分组之前是对整个表来进行聚集的，使用GROUP BY语句可以将整个表的内容分组，并在组内进行聚集函数运算。
注意：
如果在 SELECT 中使用表达式，则必须在 GROUP BY 子句中指定相同的表达式。不能使用别名。；
除聚集计算语句外， SELECT 语句中的每一列都必须在 GROUP BY 子句中给出；
GROUP BY 子句必须出现在 WHERE 子句之后， ORDER BY 子句之前

HAVING语句

分组之后，HAVING语句用来筛选符合条件的分组，作用与WHERE相似，但是
WHERE语句用来筛选行，HAVING语句用来筛选分组
引用《SQL必知必会》中说的 “WHERE 在数据分组前进行过滤， HAVING 在数据分组后进行过滤。”很形象

子查询

使用子查询即对查询语句进行嵌套使用，作为子查询的SELECT语句只能查询单个列
可以用来计算字段也可以用来做筛选的条件
例：从学生信息表（S）中查询阳光中学学生的学号
SELECT 学号 FROM S
WHERE 姓名 IN (SELECT 姓名 FROM S WHERE 学校=“阳光中学”)

连接表

相同的数据出现多次决不是一件好事，这是关系数据库设计的基础。关系表的设计就是要把信息分解成多个表，一类数据一个表。各表通过某些共同的值互相关联（所以才叫关系数据库）。
简单说，联结是一种机制，用来在一条 SELECT 语句中关联表，因此称为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。

WHERE子句的连接

（本例来自所用课本，分了三个表 学生信息S,课程信息C,成绩SC）
例：查询学号为”C01“的学生的”数据库“课程的成绩
SELECT 成绩 FROM SC,C,S
WHERE SC.学号=S.学号 AND C.课程号=SC.课程号 AND 课程名=“数据库”

内连接

INNER JOIN连接两个表，连接条件用ON语句给出，INNER是可加可不加的。内连接的效果和上述WHERE子句的连接相同，取两表相同字段具有相同值得记录。
内连接可以用下图的方式描述表字段的出现与否。（来自 菜鸟教程）

外连接

外连接分三种左外连接LEFT OUTER JOIN;右外连接RIGHT OUTER JOIN;全外连接FULL OUTER JOIN，外连接是相对来说可以实现复杂一点的查询的，课本上经常遇到的一个例子，我这里有一个学生信息表S，和一个课程信息表C，还有成绩表SC，现在我想查询每个同学选修了什么课程，没选课的学生你显示null。这时候就可以用到左外连接或者右外连接了。

SELECT 姓名,课程名 FROM
S LEFT OUTER JOIN C
ON S.选修课程号=C.课程号
上下两个效果相同
SELECT 姓名,课程名 FROM
C LEFT OUTER JOIN S
ON S.选修课程号=C.课程号

三种外连接可以用下图的方式描述表字段的出现与否。（来自 菜鸟教程）

表别名

使用AS语句，用法与字段别名相同，只不过AS在表名之后

插入数据

INSERT语句

插入完整的行

INSERT INTO 表名（…）
VALUES(…)
VALUES（）内是按照表规定的数据类型而指定的值，构成表中新的一行；
表名（…）内是表的列名，不一定按表的顺序排列，优势在于表结构改变不影响行数据的输入

插入部分行

对应改变表名（）内的列名即可

插入查询的数据

使用INSERT SELECT语句
INSERT INTO 表名（…）
SELECT … FROM table
SELECT后的内容要与表（）内的列名内容一致

从一个表复制到另一个表

使用SELECT INTO语句

SELECT * INTO table2 FROM table1
与下面的语句作用相同
CREATE TABLE table2 AS SELECT * FROM table1

更新和删除数据

使用UPDATE和DELETE语句

更新数据

UPDATE table
SET field=新值
WHERE 筛选条件

删除数据

DELETE FROM table
WHERE 筛选条件
若没有筛选条件，则是直接删除整个表

创建与操作表

创建表

CREATE TABLE 表名{
属性1 ……，
属性2……，
属性3……，
…
}
DEFAULT语句用来设置默认值

更新表

这个更新表主要指更新表的属性，做一些增加或删除属性、主键、约束的操作
ALTER TABLE 表名
ADD/DROP……

删除表

DROP TABLE table
永久删除，无法撤销

视图

视图的作用是为了减少重复查询的工作，个人感觉就像是”按某些条件在某些表中进行查询，觉得有用，这个页面给我留着。“
CREATE VIEW 视图名 AS ……

存储过程

存储过程就像是java语言中的方法，python、C++等语言中的函数，固定某些操作，直接调用，不用再去实现，有时你还能传参，舒服！

执行存储过程

执行存储过程用到EXECUTE语句，用法如下：

EXECUTE 存储过程名（参数列表）

创建存储过程

目前只学习了SQL Server，下面是SQL Server的例子（仍用课本上的三个表）：
获取对应学号同学的各科成绩
CREATE PROCEDURE grade{
DECLARE @number varchar（10） //声明一个局部变量，接收传入的参数
SELECT 成绩 FROM SC
WHERE 学号=@number
}

高级SQL特性

约束

CONSTRAINT语句，在创建表的时候可以利用约束对表属性进行限制，主键和外键也算是约束，主键由PRIMARY KEY定义，不可改动，外键由REFERENCES定义。

检查约束CHECK

CHECK加筛选条件可以确保插入表此属性栏的数据满足筛选条件。

索引

方便检索和排序，唯一命名。

CREATE INDEX 索引名
ON 表名(列名)

触发器

顾名思义，在进行某些操作时触发一些设定好的操作，可以引起触发的操作有INSERT插入,DELETE删除,UPDATE更新

CREATE TRIGGER 触发器名
ON 表名
FOR 操作名 // INSERT, UPDATE,DELETE，多项操作的话用逗号隔开
AS
引起的操作