一起学习SQL吧-《SQL基础教程》知识点

一起学习SQL吧-《SQL基础教程》知识点

菜鸟小白自学数据库,从《SQL基础教程》中轻松get最基础的SQL知识。建议同为新手的伙伴认真整理本书内容,结合书中实例及练习题,不断深化SQL知识点。别怕,SQL其实真的一点也不可怕~
借知乎整理本书内容,常读常新。

1. 数据库和SQL

1.1. 数据库是什么

1.1.1. 我们身边的数据库

  • 数据库是什么

数据库:将大量数据保存起来,通过计算机加工而成的可以进行高效访问的数据集合称为数据库(Database,DB)。

  • 数据库管理系统是什么

数据库管理系统:用来管理数据库的计算机系统称为数据库管理系统(Database Management System,DBMS)。

1.1.2. 为什么DBMS那么重要

  • 文本文件或者Excel管理数据的局限性

①无法多人共享数据②无法提供大量数据所需的格式③实现读写自动化需要编程技术能力④无法应对突发事故

1.1.3. DBMS种类

通过数据的保存格式(数据库种类)来进行分类的。

  • 层次型数据库(HDB)

最古老的数据库之一。现在已经很少用了。

  • 关系型数据库(RDB)

和Excel一样,采用行列二维表结构来管理数据,简单易懂。使用专门的SQL(Structured Query Language 结构化查询语言)语言对数据进行操作。

  • 关系数据库管理系统RDBMS

· Oracle Database:甲骨文公司

· SQL Server:微软公司

· DB2:IBM公司

· PostgreSQL:开源的RDBMS

· MySQL:开源的RDBMS

  • 面向对象数据库(OODB)

编程语言中有一种被称为面向对象的语言:包括Java和c++等

  • XML数据库(XMLDB)

XML:使用HTML那样的标签来表现数据结构的一种语言。

XML数据库可以对XML形式的大量数据进行高速处理。

  • 键值存储系统(KVS)

单纯用来保存查询所使用的主键(key)和值(value)的组合的数据库。近几年,随着键值存储系统被应用到Google等需要对大量数据进行超高速查询的web服务当中,它正逐渐为人们所关注。

《SQL基础教程》数据库是什么

1.2. 数据库的结构

1.2.1. RDBMS的常见系统结构

  • 客户端/服务器(C/S类型)

服务器:用来接收其他程序发出来的请求,并对该请求进行相应处理的程序(软件),或者是安装了此类程序的设备(计算机)。

客户端:向服务器发出请求的程序(软件),或者是安装了该程序的设备(计算机)。多个客户端还可以同时对同一个数据库进行读写操作。

1.2.2. 表的结构

由行和列组成的二维表来管理数据。 根据SQL语句的内容返回的数据,同样必须是二维表的形式,这也是关系数据库的特征之一。 一个数据库可以储存多个表。

  • 列(字段)
  • 行(记录):关系数据库以行为单位读写数据
  • 单元格:一个单元格只能输入一个数据

1.3. SQL概要

1.3.1. 标准SQL

SQL是用来操作关系数据库的语言。 学会标准的SQL就可以在各种RDBMS中书写SQL语句了。

1.3.2. SQL语句及其种类

SQL用关键字、表名、列名等组合而成一条SQL语句来描述操作内容。 关键字:那些含义或者使用方法事先已定义好的英语单词,例如“select”

  • DDL(数据定义语言)

· create:创建数据库和表等对象

· drop:删除数据库和表等对象

· alter:修改数据库和表等对象的结构

  • DML(数据操作语言)

· select:查询表中的数据

· insert:向表中插入数据

· update:变更表中的数据

· delete:删除表中的数据

  • DCL(数据控制语言)

· commit:确认数据库中的数据进行的变更

· rollback:取消对数据库中的数据进行的变更

· grant:赋予用户操作权限

· revoke:取消用户操作权限

《SQL基础教程》SQL语句种类

1.3.3. SQL的基本书写规则

· SQL语句要以分号”;“结尾

· SQL语句不区分大小写

· 常数的书写方式是固定的

字符串和日期常数需要使用单引号 ' 括起来,例如:’ABC‘,’2010-01-26‘

• 数字常数无需加注单引号,直接书写数字即可

· 单词需要用半角空格或者换行来分隔

1.4. 表的创建

1.4.1. 数据库的创建(create database语句)

create database<数据库名称>

1.4.2. 表的创建(create table语句)

create table <表名>

(<列名1> <数据类型> <该列所需约束>,

<列名2> <数据类型> <该列所需约束>,

<列名3> <数据类型> <该列所需约束>,

······

<该表的约束1>,<该表的约束2>,······);

1.4.3. 命名规则

· 只能用①半角英文②数字③下划线(_)作为数据库、表和列的名称。

· 名称必须以半角英文字母作为开头。

· 名称不能重复。

《SQL基础教程》

1.4.4. 数据类型的指定

所有的列都必须指定数据类型,每一列都不能存储与该列数据类型不符的数据。

• integer型:用来指定存储整数的列的数据类型(数字型),不能存储小数。一个int型数据占用4个字节。

• char型:指定存储字符串的列的数据类型(字符型)。需要指定长度,保存时“长度不够,空格来凑”。字符串是区分大小写的。最多255个字节。

• varchar型:(可变字符串类型)该类型的列以可变长字符串的形式来保存字符串的。“不需要用半角空格补足长度”。最多255个字节。

• date型:用来指定存储日期(年月日)的列的数据类型(日期型)。

1.4.5. 约束的设置

约束是除了数据类型之外,对列中存储的数据进行限制或者追加条件的功能。

  • not null约束:对列设定了不能输入空白,也就是必须输入的约束。
  • primary key():对列设定主键约束。主键就是可以唯一确定一行数据的列。

1.5. 表的删除和更新

1.5.1. 表的删除(drop table语句)

drop table <表名>;

删除的表是无法恢复的,要慎重。

1.5.2. 表定义的更新(alter table语句)

表的定义变更后无法恢复,仔细确认。

  • 添加列

alter table <表名> add column <列名和列的定义>;

添加多列时用括号括起来,多列间的定义用逗号分隔。

  • 删除列

alter table <表名> drop column <列名>;

删除多列时用括号括起来,多列间的定义用逗号分隔。

1.6. 向表中插入数据

1.6.1. insert into <表名> values (插入的数据,,)

《SQL基础教程》

2. 查询基础

2.1. select语句基础

2.1.1. 列的查询

从表中选取数据时需要使用select语句,也就是只从表中选出必要数据的意思。通过select语句查询并选取必要数据的过程称为匹配查询或者查询(query)。

select <列名>,······ from <表名>;

select子句列举了希望从表中查询出的列的名称;from子句则指定了选取出数据的表的名称。

• 查询多列时,用逗号进行分隔排列。

2.1.2. 查询出表中所有列

select * from<表名>;(星号可以代表所有列)

2.1.3. 为列设定别名

  • 使用as关键字为列设置别名
例:select product_id as id from product;

• 别名中可以使用汉语,使用汉语时需要用双引号“ ”括起来。

2.1.4. 常数查询

  • 直接查询字符串、数字、日期

2.1.5. 从结果中删除重复行

  • distinct关键字

select distinct <列名> from <表名>;

• 多条null数据,会结合为一条null数据

• distinct可以对多列之前使用,会将多列的数据进行组合,将重复的数据结合为一条

• distinct关键字只能用在第一个列名之前

2.1.6. 根据where语句来选择记录

  • 选出满足条件的数据

和Excel中根据条件对行进行过滤的功能是相同的。

select <列名>,······from <表名> where<条件表达式>;

• where子句必须紧跟在from子句之后

2.1.7. 注释的书写方法

  • 注释对SQL的执行没有任何影响

· 1行注释:书写在“--”之后,只能写在同一行。

两个半角--MySQL中需要在--之后加入半角空格,如果不加的话就不会被认为是注释。

· 多行注释:书写在“/*”和“*/”之间,可以跨多行。

《SQL基础教程》select基础

2.2. 算数运算符和比较运算符

2.2.1. 算数运算符

四则运算所使用的运算符(+、-、*、/)称为算数运算符。 运算符就是使用其两边的值进行四则运算或者字符串拼接、数值大小比较等运算,并返回结果的符号。

+、-、*、/可以使用括号提升优先级

• 需要注意null

• 所有包括null的运算结果还是null

2.2.2. 比较运算符

=、!=、<>、>=、<=、>、<

• 注意字符串类型数据原则上按照字典顺序进行排序

例:1、1-1、1-2、1-3、2、2-1、2-2、3
比较大小时,‘1-3’<‘2’
‘3’>'2-2'
  • 不能对null使用比较运算符

• 选取null值:is null

• 选项不为null的值:is not null

2.2.3. 逻辑运算符

  • not运算符

not不能单独使用,必须和其他查询条件组合起来使用。

  • and运算符和or运算符

and运算符在其两侧的查询条件都成立时整个查询条件才成立,其意思相当于“并且”。

• and运算符优先级高于or

  • or运算符在其两侧的查询条件有一个成立时整个查询都成立,相当于“或者”
  • 真值 · true · false · unknown
《SQL基础教程》运算符和比较运算

3. 聚合与排序

3.1. 对表进行聚合查询

3.1.1. 聚合函数

聚合函数就是将多行汇总为一行。输入多行输出一行。

  • count()函数:计算表中的记录数(行数)

count(*):可以查出null在内的全部数据行数

count(字段名):可以查出null之外的数据行数

  • sum()函数:计算表中数值列的数据合计值

• sum()以列名为参数时,即使包含null也可以计算出合计值

  • avg()函数:计算表中数值列的数据平均值

(值的合计)/(值的个数),null部分也参与个数

  • max()函数:求出表中任意列中数据的最大值,适用于几乎所有数据类型的列
  • min()函数:求出表中任意列中数据的最小值,适用于几乎所有数据类型的列
  • 使用聚合函数删除重复值(关键字distinct)

• 计算除去重复数据后的数据行数

select count(distinct 字段名)

《SQL基础教程》聚合函数

3.2. 对表进行分组

3.2.1. group by子句

select <列名1>,<列名2>,<列名3>,······from<表名>

where<条件> group by<列名1>,<列名2>,<列名3>,······;

• 使用GROUP BY子句时,会将表中的数据分为多 个组进行处理。

• GROUP BY子句的书写位置也有严格要求,一定要写在FROM 语句之后(如果有WHERE子句的话需要写在WHERE子句之后)

3.2.2. 聚合键中包含NULL的情况

当聚合键中包含NULL时, 也会将NULL作为一组特定的数据。

3.2.3. 与聚合函数和GROUP BY子句有关的常见错误

· ×在SELECT子句中书写了多余的列

※※在使用COUNT这样的聚合函数时,SELECT子句中的元素有严格的 限制。实际上,使用聚合函数时,SELECT子句中只能存在以下三种元素:

①常数②聚合函数③group by 子句中指定的列(聚合键)

· ×在GROUP BY子句中写了列的别名

· GROUP BY子句的结果能排序吗?group by 结果记录是无序排序的,随机的

· ×在WHERE子句中使用聚合函数

只有SELECT子句和HAVING子句(以及之后将要学到的 ORDER BY子句)中能够使用COUNT等聚合函数。

《SQL基础教程》group by

3.3. 为聚合结果指定条件

3.3.1. HAVING子句

  • HAVING子句和包含GROUP BY子句时的SELECT子句一样,能够使用的要素有一定的限制①常数②聚合函数③group by 子句中指定的列(聚合键)

· WHERE子句=指定所对应的条件 HAVING子句=指定所对应的条件

· 聚合键所对应的条件还是应该书写在WHERE子句之中

3.4. 对查询结果进行排序

3.4.1. ORDER BY 子句

SELECT <列名1>, <列名2>, <列名3»,…… FROM < 表名 >

ORDER BY <排序基准列1>,<排序基准列2>……;

  • 升序

使用升序进行排列时,正式的书写方式应该是使用关键字ASC,但是省略该关键宇时会默认使用升序进行排序。

  • 降序

在列名后面使用DESC关键字

3.4.2. 指定多个排序键

优先使用左侧的键,如果该列存在相同值的话,会接着参考右侧的键

3.4.3. NULL的排序

  • 不能对NULL使 用比较运算符,也就是说,不能对NULL和数字进行排序。也不能与字符串和曰期比较大小。

• 使用含有NULL的列作为排序键时,NULL会 在结果的开头或末尾汇总显示

3.4.4. 在排序键中可以使用显示别名

3.4.5. ORDER BY子句中可以使用的列

在order by 子句中可以使用select子句中未使用的列和聚合函数。

3.4.6. 不要使用列编号

《SQL基础教程》order by

4. 数据更新

4.1. 数据的插入(INSERT语句的使用方法)

4.1.1. inset语句

INSERT into <表名> (列1,列2,列3,······) values (值1,值2,值3······);

• 执行一次INSERT语句会插入一行数据

• 插入多行时,通常需要循环执行所需行数次的INSERT语句。

4.1.2. 列清单的省略

对表进行全列INSERT时,可以省略表名后的列清单。这时 VALUES子句的值会默认按照从左到右的顺序陚给每一列。

4.1.3. 插入NULL

  • INSERT语句中想给某一列陚予NULL值时,可以直接在VALUES 子句的值清单中写入NULL。

想要插入NULL的列一定不能设置NOT NULL约束

4.1.4. 插入默认值

  • 默认值的设定,可以通过 在创建表的CREATE TABLE语句中设置DEFAULT约束来实现。

“DEFAULT <默认值 >”的形式来设定默认值。

如果在创建表的同时设定了默认值,就可以在INSERT语句中自动 为列赋值了

• 通过显示方法插入默认值

• 在VALUES子句中指定DEFAULT关键字

• 通过隐示方法插入默认值

说到省略列名,还有一点要说明一下。如果省略了没有设定默认值的列的话,该列的值就会被设定为NULL。

4.1.5. 从其他表中复制数据

执行INSERT ... SELECT语句

4.2. 数据的删除(DELETE语句的使用方法)

4.2.1. DROP TABLE语句和DELETE语句

  • DROP TABLE语句可以将表完全删除
  • DELETE语句会留下表(容器),而删除表中的全部数据。

4.2.2. DELETE语句的基本语法

DELETE from <表名>;

• 删除的对象是记录(行)

4.2.3. 指定删除对象的DELETE语句(搜索型DELETE )

  • 指定了删除对象的DELETE语句称为搜索型 DELETE。

DELETE FROM < 表名>WHERE <条件>;

《SQL基础教程》insert/delete

4.3. 数据的更新(UPDATE语句的使用方法)

4.3.1. UPDATE语句的基本语法

UPDATE <表名>SET <列名> =<表达式>;

4.3.2. 指定条件的UPDATE语句(搜索型UPDATE )

  • 指定更新对象的UPDATE语句 称为搜索型UPDATE语句。

UPDATE<表名>SET<列名>=<表达式>WHERE < 条件>;

4.3.3. 使用null进行更新

但是只限于未设置not null约束的列。

4.3.4. 多列更新

  • 使用逗号将列分隔排列
  • 将列用()括起来的列表形式
《SQL基础教程》update

4.4. 事务

4.4.1. 什么是事务

  • 事务代表了对表中数据进行更新的单位

事务就是需要在同一个处理单元中执 行的一系列更新处理的集合。

4.4.2. 创建事务

  • 开始语句

SQLServer、PostgreSQL:BEGIN TRANSACTION

MySQLSTART:TRANSACTION

  • 结束语句

• 亊务结束语句(COMMIT或者ROLLBACK)

• ROLLBACK 取消处理

取消事务包含的全部更新处理的结束指令

相当于文件处理中的放弃保存。一旦回滚,数据库就会回复到事务开始之 前的状态

4.4.3. ACID特性

DBMS的事务都道循四种标准规格的约定

• 原子性(Atomicity )

原子性是指在事务结束时,其中所包含的更新处理要么全部执行,要 么完全不执行的特性。也就是要么占有一切要么一无所有。

• —致性(Consistency )一致性也称为完整性

事务中包含的处理,要满足数据库提前设置的约束

• 隔离性(Isolation )

保证不同事务之间互不干扰的特性。该特性保证了事务 之间不会互相嵌套。

• 持久性(Durability)

事务(不论是提交还是回滚)一旦结束,DBMS会保证该时点的数据状态得以保存的特性。即使由于系统故 障导致数据丢失,数据库也一定能通过某种手段进行恢复。

《SQL基础教程》事务

5. 复杂查询

5.1. 视图

5.1.1. 视图保存的是SELECT语句

  • 视图的优点

• 视图无需保存数据,因此可以节省存储设备的容量。

• 可以将频繁使用的SELECT语句保存成视图,就不用每次都重新书写了。

5.1.2. 创建视图的方法

CREATE VIEW 视图名称(<视图列名1>, <视图列名2>,……)AS<SELECT 语句>

5.1.3. 视图的限制

  • 定义视图时不能使用ORDER BY子句
  • 对视图进行更新

• 对视图和表需要同时进行更新,因此通过聚合得到的视图无法进行更新

• 能够更新视图的情况

• 不是通过聚合得到的视图就可以进行更新

5.1.4. 删除视图

  • DROP VIEW

DROP VIEW视图名称(<视图列名1>, <视图列名2>,······)

《SQL基础教程》视图

5.2. 子查询

5.2.1. 子查询和视图

  • 视图并不是用来保存数据的,而是通过保存读取数据的SELECT语句的方法来为用户提供便利的工具。

· 子査询就是将用来定义视图的SELECT语句直接用于FROM子句当中

• 子查询作为内层查询会优先执行

5.2.2. 子查询的名称

原则上子査询必须设定名称,因此请大家尽量从处理内容的角度出发为子査询设定恰当的名称。

5.2.3. 标量子查询

  • 标量子査询则有一个特殊的限制,那就是必须而且只能返回1行1 列的结果。也就是返回表中某一行的某一列的值

• 在WHERE子句中使用标置子查询

  • 标置子查询的书写位置

• 能够使用常数或者列名的地方,无论是SELECT子句、GROUP BY子句、HAVING子句,还是ORDER BY子句,几乎所有的地方都可以使用。

  • 使用标置子查询时的注意事项

• 该子查询绝对不能返回多行结果。

5.2.4. 关联子查询

  • 在细分的组内进行比较时,需要关联子查询。

· 结合条件一定要写在子查询中

《SQL基础教程》子查询

6. 函数、谓词、CASE表达式

6.1. 各种各样的函数

6.1.1. 算术函数

  • 加法
  • 减法
  • 乘法
  • 除法
  • ABS 绝对值:ABS (数值)
  • MOD 求余:MOD (被除数,除数)
  • ROUND 四舍五入:ROUND (对象数值,保留小数的位数)

6.1.2. 字符串函数

  • II 拼接:字符串1字符串2
  • LENGTH 字符串长度:LENGTH(字符串)表示字符串中包含多少个字符
  • LOWER 小写转换:LOWER(宇符串}

• LOWER函数只能针对英文字母使用,它会将参数中的字符串全都转换为小写

  • REPLACE 字符串的替换:REPIACE(对象字符串,替换前的字符串,替换后的字符串)
  • SUBSTRING 宇符串的截取:SUBSTRING(对象字符串 FROM 截取的起始位置 FOR 截取的宇符数)

截取的起始位置从字符串最左侧开始计算

  • UPPER 大写转换:UPPER(字符串)它只能针对英文字母使用

6.1.3. 日期函数

  • CURRENT_DATE 当前日期
  • CURRENT_TIME 当前时间
  • CURRENT_TIMESTAMP 当前日期和时间

6.1.4. 转换函数

  • CAST 类型转换:CAST(转换前的值 AS 想要转换的数据类型)
  • COALESCE 将NULL转换为其他值
《SQL基础教程》函数

6.2. 谓词

6.2.1. 谓词,通俗来讲就是需要满足特定 条件的函数。该条件就是“返回值是真值”

  • LIKE谓词 字符串的部分一致查询

和=区别:=只有在字符串完全一致时才为真,当需要进行字符串的部分一致查询时需要使用like

前方一致査询,例:‘b%’

中间一致查询,例:‘%b%’

后方一致査询:例:‘%b’

  • BETWEENT谓词:范围查询,可以包含临界值
  • IS NULL. IS NOT NULL:判断是否为NULL
  • IN谓词 OR的简便用法 • not in
  • EXIST谓词

6.3. CASE表达式

6.3.1. 是一种进行运算的功能

CASE WHEN <判断表达式> THEN <表达式>

WHEN <判断表达式> THEN <表达式>

WHEN <判断表达式> THEN <表达式>

······

ELSE <表达式>

END

《SQL基础教程》谓词、case表达式

7. 集合运算

7.1. 表的加法

7.1.1. 集合运算的注意事项

  • 作为运算对象的记录的列数必须相同
  • 表的加减法

· 作为运算对象的记录中列的类型必须一致

· 可以使用任何SELECT语句,但ORDER BY子句只能在最后使用一次

7.1.2. 表的加法 UNION

UNION相当于并集

7.1.3. 包含重复行的集合运算 ALL选项

在UNION的结果中保留重复行的语法,UNION ALL

7.1.4. 选取表中公共部分 INTERSECT

7.1.5. 记录的减法 EXCEPT

7.2. 联结(以列为单位对表进行联结)

7.2.1. 将其他表中的 列添加过来,进行“添加列”的运算

  • 内联结 INNER JOIN

FROM子句

ON子句

联结条件

SELECT子句

  • 外联结 OUTER JOIN
  • 交叉联结 CROSS JOIN
《SQL基础教程》集合运算
编辑于 2018-09-21