Sql学习笔记

Sql基础教程

第一章 数据库和SQL

1.数据库类型：层次数据库（Hdb）、关系数据库（RDB）、面向对象数据库（OODB）、XML数据库（XMLDB）、键值储蓄系统（KVS）

2.SQL语句种类：DDL（数据定义语言）：CREATE：创建数据库和表等对象

                                                                    DROP： 删除数据库和表等对象

                                                                    ALTER： 修改数据库和表等对象的结构

                            DML（数据操纵语言）：SELECT：查询表中的数据

                                                                    INSERT：向表中插入新数据

                                                                  

  ：更新表中的数据

                                                                    DELETE：删除表中的数据

                            DCL (数据控制语言)     ：COMMIT： 确认对数据库中的数据进行的变更

                                                           ROLLBACK：取消对数据库中的数据进行的变更

                             GRANT： 赋予用户操作权限

                                      REVOKE： 取消用户的操作权限  

3. SQL的基本书写规则：SQL语句要以分号（;）结尾

                                        SQL语句不区分大小写

                                         常数的书写方式是固定的

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

4.CREATE DATABASE语句         例：CREATE DATABASE<数据库名称> ;

5.数据类型指定：INTEGER型 用来指定存储整数的列的数据类型（数字型），不能存储小数。

                            CHAR型      CHAR 是 CHARACTER（字符）的缩写，是用来指定存储字符串

                                                的列的数据类型（字符型）。

                            VARCHAR型   可变长字符串

                            DATE型      用来指定存储日期（年月日）的列的数据类型（日期型）。

   约束的设置：两种约束：NIULL：NULL 是代表空白（无记录）的关键字

                                         主键（primary key）：主键（primary key）就是可 以特定一行数据的列

6.DROP TABLE语句 ：表的删除

7.ALTER TABLE语句：表定义的更新

         添加列的ALTER TABLE语句

       删除列的ALTER TABLE语句

  8.   使用插入行的指令语句 INSERT，就可以把表 1-2 中的数据都插入到 表中了。开头的 BEGIN TRANSACTION 语句是开始插入行的指令语句， 结尾的 COMMIT 语句是确定插入行的指令语句。   

第二章 查询基础

1. SELECT 语句

例：

1. 2.

*：代表全部列（如果使用星号的话，就无法设定列的显示顺序了）

1. 3.

---

---

---

 4.中文别名

5.从结果中删除重复行

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

在 WHERE 子句中使用 AND 运算符或者 OR 运算符，可以对多个查 询条件进行组合。

7.注释的书写方法

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

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

8.算术运算符

9.比较运算符

注：字符串类型的数据原则上按照字典顺序进行排序，不能与数字的大小顺序混淆。

例：'10' 和 '11' 同样都是以 '1' 开头的字符串，首先判定为比 '2' 小。

10.希望选取NULL记录时，需要在条件表达式中使用IS NULL运算符。

希望选取不 是NULL的记录时，需要在条件表达式中使用IS NOT NULL运算符。

11. 逻辑运算符

     NOT运算符

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

     OR运算符：OR 运算符在其两侧的查询条件有一个成立时整个查询条件都成立，其意思相当                             于 “或者”A。

注：可以使用文氏图

           可以使用括号强化处理

          AND运算符的优先级高于OR运算符。想要优先执行OR运算符时可以使用括号。

第三章 聚合与排序

1. 聚合函数：用于汇总的函数称为聚合函数或者聚集函数，统称为聚合函数。

COUNT：计算表中的记录数（行数）

SUM： 计算表中数值列中数据的合计值

AVG： 计算表中数值列中数据的平均值

MAX： 求出表中任意列中数据的最大值 MIN： 求出表中任意列中数据的最小值

MIN： 求出表中任意列中数据的最小值

注：COUNT函数的结果根据参数的不同而不同。COUNT(*)会得到包含NULL的数据 行数，而COUNT()会得到NULL之外的数据行数。

      MAX/MIN函数几乎适用于所有数据类型的列。SUM/AVG函数只适用于数值类型的列。

2.使用聚合函数删除重复值（关键字DISTINCT）

 DISTINCT 必须写在括号中。

3.GROUP BY子句

注：GROUP BY 子句的书写位置也有严格要求，一定要写在 FROM 语句之后（如果有 WHERE 子句的话需要写在 WHERE 子句之后）。

GROUP BY 子句就像切蛋糕那样将表进行了分组。在 GROUP BY 子句中指定的列称为聚合键或者分组列。

聚合键中包含NULL时，在结果中会以“不确定”行（空行）的形式表现出来。

4.使用WHERE子句时GROUP BY的执行结果

5.常见错误① ——在SELECT子句中书写了多余的列

  常见错误② ——在GROUP BY子句中写了列的别名

  常见错误③——GROUP BY子句的结果能排序吗

  常见错误④——在WHERE子句中使用聚合函数

6.HAVING语句

注：HAVING 子句中 能够使用的 3 种要素如下所示。 ● 常数 ● 聚合函数 ● GROUP BY子句中指定的列名（即聚合键）

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

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

将条件写在 WHERE 子句 中要比写在 HAVING 子句中的处理速度更快，返回结果所需的时间更短。

7.ORDER BY子句

在ORDER BY子句中可以使用SELECT子句中定义的别名。而WHERE中不能使用

SELECT子句中未包含的列也可以在ORDER BY子句中使用

在ORDER BY子句中可以使用SELECT子句中未使用的列和聚合函数。

8.指定升序或降序

ASC 和 DESC 是 ascendent（上升的）和 descendent（下降的）

9.SELECT 子 句的执行顺序在 GROUP BY 子句之后，ORDER BY 子句之前。

第四章 数据更新

1.INSERT语句

2.插入默认值：DEFAULT 约束

■通过显式方法插入默认值：在 VALUES 子句中指定 DEFAULT 关键字

■通过隐式方法插入默认值：

从其他表中复制数据

INSERT ... SELECT

例：

3.数据的删除：DROP TABLE语句和DELETE语句

         ①  DROP TABLE 语句可以将表完全删除

         ②  DELETE 语句会留下表（容器），而删除表中的全部数据

  4.DELETE语句

 注：DELETE语句的删除对象并不是表或者列，而是记录（行）。

        指定删除对象的DELETE语句（搜索型DELETE）

与 SELECT 语句不同的是，DELETE 语句中不能使用 GROUP BY、 HAVING 和 ORDER BY 三类子句，而只能使用WHERE 子句。

TRUNCATE 的语句

与 DELETE 不同的是，TRUNCATE 只能删除表中的全部数据，而不能通过 WHERE 子句指定条件来删除部分数据。

UPDATE语句

指定条件的UPDATE语句（搜索型UPDATE）

注：使用UPDATE语句可以将值清空为NULL（但只限于未设置NOT NULL约束的列）。

4.多列更新

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

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

6.创建事务

COMMIT——提交处理

ROLLBACK——取消处理

7.ACID特性

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

一致性（Consistency）：一致性指的是事务中包含的处理要满足数据库提前设置的约束，如主 键约束或者 NOT NULL 约束等。

隔离性（Isolation）：隔离性指的是保证不同事务之间互不干扰的特性。

持久性（Durability）：持久性也可以称为耐久性，指的是在事务（不论是提交还是回滚）结 束后，DBMS 能够保证该时间点的数据状态会被保存的特性。

第五章 复杂查询

1.视图

优点：于视图无需保存数据，因此可以节省存储设备的容量。

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

2.创建视图

3,视图的限制

① ——定义视图时不能使用ORDER BY子句

② ——对视图进行更新

4,删除视图

5.子查询：就是将用来定义视图的SELECT语句直接用于FROM子句当中。

6.标量子查询：标量子查询就是返回单一值的子查询。

 注：是该子查询 绝对不能返回多行结果。

7.关联子查询：

第六章 函数、谓词、CASE表达式

1.函数

● 算术函数（用来进行数值计算的函数）

● 字符串函数（用来进行字符串操作的函数）

● 日期函数（用来进行日期操作的函数）

● 转换函数（用来转换数据类型和值的函数）

● 聚合函数（用来进行数据聚合的函数）

2.算术函数

● +（加法） ● -（减法） ● *（乘法） ●   /（除法）

ABS函数：ABS 是计算绝对值的函数。

MOD函数：：MOD 是计算除法余数（求余）的函数，是 modulo 的缩写。

ROUND函数：ROUND 函数用来进行四舍五入操作。

3.字符串函数

||函数：拼接

LENGTH函数：—字符串长度

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

REPLACE函数：使用 REPLACE 函数，可以将字符串的一部分替换为其他的字符串（

REPLACE(String,from_str,to_str) 即：将String中所有出现的from_str替换为to_str

SUBSTRING函数

UPPER函数：UPPER 函数只能针对英文字母使用，它会将参数中的字符串全都转 换为大写

4.日期函数

CURRENT_DATE：CURRENT_DATE 函数能够返回 SQL 执行的日期，也就是该函数执 行时的日期。

CURRENT_TIME：CURRENT_TIME 函数能够取得 SQL 执行的时间，也就是该函数执 行时的时间

CURRENT_TIMESTAMP：CURRENT_TIMESTAMP 函数具有 CURRENT_DATE + CURRENT_ TIME 的功能。

EXTRACT：使用 EXTRACT 函数可以截取出日期数据中的一部分，例如“年” “月”，或者“小时”“秒”等

5.转换函数：一是数 据类型的转换，简称为类型转换，在英语中称为 castA ；另一层意思是值 的转换。

CAST函数：类型转换

COALESCE函数：COALESCE 是 SQL 特有的函数。该函数会返回可变参数 A 中左侧开 始第1个不是 NULL 的值。

6.谓词：● LIKE ● BETWEEN ● IS NULL、IS NOT NULL ● IN ● EXISTS

7.LIKE谓词——字符串的部分一致查询

前方一致查询：

中间一致查询

后方一致查询

注：% 是代表“0 字符以上的任意字符串

      _（下划线）代表了“任意 1 个字符”

8.BETWEEN谓词——范围查询

9.IS NULL、IS NOT NULL——判断是否为NULL

10.IN谓词——OR的简便用法

注：但需要注意的是，在使用IN 和 NOT IN 时是无法选取出 NULL 数据的。

11.NOT IN和子查询

12.EXIST谓词

13.CASE 表达式：为（条件）分支

CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。但是，由于搜索 CASE 表达式包含了简单 CASE 表达式的全部功能。

第七章 集合运算

1.表的加法——UNION

注：集合运算符会除去重复的记录。

              注意事项①——作为运算对象的记录的列数必须相同

              注意事项②——作为运算对象的记录中列的类型必须一致

              注意事项③——可以使用任何SELECT语句，但ORDER BY子句只 能在最后使用一次

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

注：在集合运算符中使用ALL选项，可以保留重复行。

3.选取表中公共部分——INTERSECT

4.记录的减法——EXCEPT

注：EXCEPT 有一点与 UNION 和 INTERSECT 不同，需要注意一下。 那就是在减法运算中减数和被减数的位置不同，所得到的结果也不相同。

5.联结（JOIN）：简单来说，就是将其他表中的 列添加过来，进行“添加列”的运算。该操作通常用于无法从 一张表中获取期望数据（列）的情况。

6.内联结——INNER JOIN

内联结要点①——FROM子句

内联结要点②——ON子句   进行内联结时必须使用ON子句，并且要书写在FROM和WHERE之间。

内联结要点③ ——SELECT子句

使用联结时SELECT子句中的列需要按照“.”的格式进行书写。

内联结和WHERE子句结合使用

7.外联结——OUTER JOIN

外联结要点①——选取出单张表中全部的信息

外联结要点②——每张表都是主表吗？（指定主表的关键字是 LEFT 和 RIGHT。顾名思义，使用 LEFT 时 FROM 子句中写在左侧的表是主表，使用 RIGHT 时右侧的表是主表。）

8.交叉联结——CROSS JOIN

第八章　SQL高级处理

1.窗口函数：窗口函数也称为 OLAP 函数 。OLAP 是 OnLine Analytical Processing 的简称，意思是对数据库数据 进行实时分析处理。

由于专用窗口函数无需参数，因此通常括号中都是空的。

2。能够作为窗口函数使用的函数

① 能够作为窗口函数的聚合函数（SUM、AVG、COUNT、MAX、MIN）

② RANK、DENSE_RANK、ROW_NUMBER 等专用窗口函数

3.RANK函数：RANK 是用来计算记录排序的函数

PARTITION BY 能够设定排序的对象范围。

ORDER BY 能够指定按照哪一列、何种顺序进行排序。

4.无需指定PARTITION BY

5.专用窗口函数的种类

●RANK函数 计算排序时，如果存在相同位次的记录，则会跳过之后的位次。

例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、4 位……

●DENSE_RANK函数 同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。

例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、2 位……

●ROW_NUMBER函数 赋予唯一的连续位次。

例）有 3 条记录排在第 1 位时：1 位、2 位、3 位、4 位……

6.窗口函数的适用范围

原则上窗口函数只能在SELECT子句中使用。

7.作为窗口函数使用的聚合函数

8.将AVG函数作为窗口函数使用

8.计算移动平均

●指定框架（汇总范围）

 这里我们使用了 ROWS（“行”）和 PRECEDING（“之前”）两个关键 字，将框架指定为“截止到之前 ~ 行”，因此“ROWS 2 PRECEDING”就是将框架指定为“截止到之前 2 行”，也就是将作为汇总对象的记录限 定为如下的“最靠近的 3 行”。● 自身（当前记录） ● 之前 1行的记录 ● 之前 2行的记录

使用关键字 FOLLOWING（“之后”）替换 PRECEDING，就可以指 定“截止到之后 ~ 行”作为框架了

10.两个ORDER BY

11.

12.同时得到合计行

13.ROLLUP——同时得出合计和小计

GROUPING 运算符包含以下 3 种 A。

 ●ROLLUP

●CUBE

●GROUPING SETS

14.ROLLUP的使用方法

■将“登记日期”添加到聚合键当中

15.GROUPING函数

16.CUBE——用数据来搭积木

补充:

1.LIMTIT函数（分页）

SELECT employee_id,last_name

FROM employees

LIMIT 0,20;

SELECT employee_id,last_name

FROM employees

LIMIT　20 ,４0;　——第二页

LIMIT（偏移量，分页数）：LIMIT必须放在SELECT语句的最后

2.多表查询

SELECT employee_id,department_name

FROM employees,departments

WHERE employees.`employee_id`=departments.`department_id`;

注：如果查询语句中出现了多个表中都纯在的字段，则必须指明此字段所在的表

自链接 VS   非自链接    等值连接 VS 等值连接   内连接 VS 外连接

3.第六章多表查询题目

# 1.显示所有员工的姓名，部门号和部门名称。 （所有：用外连接）

# 2.查询90号部门员工的job_id和90号部门的location_id

# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city

# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name # 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’

# 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式 employees Emp# manager Mgr# kochhar 101 king 100

# 7.查询哪些部门没有员工

# 8. 查询哪个城市没有部门

# 9. 查询部门名为 Sales 或 IT 的员工信息

4.

5.

6.

子查询题目

#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

#5.查询在部门的location_id为1700的部门工作的员工的员工号

#6.查询管理者是King的员工姓名和工资

#8.查询平均工资最低的部门信息

#9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）

#10.查询平均工资最高的 job 信息

#11.查询平均工资高于公司平均工资的部门有哪些?

#12.查询出公司中所有 manager 的详细信息

#13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?

#14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

#17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'

#18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询）

#19.查询每个部门下的部门人数大于 5 的部门名称（相关子查询）

7.

8.主外键都支持add 主键和其他都是modify