【SQL基础MICK读书笔记】

第一章 搭建SQL学习环境

一、PostgreSQL下载与安装

1、下载地址

https://www.enterprisedb.com/downloads/postgres-postgresql-downloads#windows

2、文件配置

修改默认的安装路径 C:\Program Files\PostgreSQL\9.6 为 C:\PostgreSQL\9.6（方便其他用户访问）

二、通过PostgreSQL执行SQL语句

1、链接PostgreSQL数据库命令

cmd 进入命令界面，输入C:\PostgreSQL\9.5\bin\psql.exe –U postgres  然后再输入口令，命令提示符窗口显示出“ postgres=# ”，意味着连接成功。

2、SQL语句规范

“ ; ”是 SQL 的结束符，如果没有输入的话，即使按下回车键，SQL 语句也不会执行。因此，在执行 SQL 语句的时候，请大家注意不要忘记输入“ ; ”。

3、psql结束符

\q

第二章 数据库和SQL概念

一、数据库

1、数据库是什么？

数据库是经过计算机加工过的方便数据存取的大规模数据集合，由各种表及关系组成。

2、什么是数据库管理系统？

方便用户管理数据库的软件，database management system 简称 DBMS

3、数据库为啥用DBMS软件来管理，而不用excel 或者纸质文件，或者其他数据存储介质呢？

能够保证数据的结构性、一致性、安全性、可控性

4、关系型数据库的种类

Oracle Database 甲骨文

sql server 微软

DB2 IBM

posetgre SQL 开源

MYSQL 开源

5、数据库结构

客户端/服务器类型组织方式，多个客户端通过互联网，利用sql语句向服务器发送数据请求，服务器返回数据。

6、数据库构成----表结构

数据库 是通过众多表及关系构成的，表是由行和列构成的 二维表，列代表数据项目，称之为字段，每一列的数据属性一致；行代表一条数据记录，称之为记录，行列的交汇处是单元格，一个单元格只能存储一个数据，

二、SQL概述

1、标准SQL，是经过国际标准化组织规范的数据库语言，SQL语句不是过程化语句，让计算机如何实现结果，而是结果集的描述性语句，按照一定的语法结构，对想要的结果集进行描述，剩下的交给查询优化器。SQL由关键字、表名、列明组成一条SQL语句用来描述结果集。

2、SQL分类

a.DDL (DATA DENFINION LANGUAGE) 用来创建或者删除存储数据用的数据库以及数据库中的表等对象

CREATE  创建数据库和表视图等对象

DORP  删除数据库和表等对象

ALTER  修改数据库和表等对象

b.DML (Data Manipulation Language数据操纵语言) 用来查询及变更表中的记录。

SELCET  查询表中的数据

INSERT  向表插入数据

UPDATE  更新表中的数据

DELETE 删除表中的数据

c.DCL 数据控制语言用来确认或者取消对数据库中的数据进行的变更

COMMIT 确认对数据库中的数据进行的变更

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

GRANT赋予用户操作权限

REVOKE 取消用户的操作权限

3、SQL一般数据写规则

书写结束后以（；）结尾，关键字不区分大小写，日期和字符串需要使用单引号，数字不需要任何符号，关键字用半角空格区别开。

三、表的创建

1、创建表的语法规则 

CREATE TABLE <table1>  (数据类型)  约束条件,

                           <table2>  (数据类型)  约束条件,

                           <table3>  (数据类型)  约束条件,

   PRIMARY KEY (table1);

2、数据库、表名的命名规则

半角英文字母、数字、下划线（_）作为数据库、表和列的名称，以字母开头，同一个数据库里表名不能重复

 3、数据类型

 INTEGER ，整数型，该列不能存储小数

CHAR ，固定长度字符串，是当列中存储的字符串长度达不到最大长度的时候，使用半角空格进行补足。例如，我们向 CHAR(8) 类型的列中输入 ' abc ' 的时候，会以 'abc ' （ abc 后面有 5 个半角空格）的形式保存起来。

 VARCHAR，可变长度字符串，字符数未达到最大长度，也不会用半角空格补足

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

4、约束条件

is not null,null ,PRIMARY KEY ()

四、表的删除和更新

DROP TABLE product 

增加或者删除表中某一列

ALTER TABLE prodcut add  product_pingying varchar(100)

ALTER TABLE procut drop product_pinying

五、向表插入数据

INSERTE INTO product VALUSE (v1,v2,v3,v4,v5,v6)

第三章 SQL查询基础

一、SQL基础

1、SQL关键词书写顺序

select  from where  group by   hanving   order by （SQL 中子句的书写顺序是固定的，不能随意更改。）

2、查询优化器 关键词执行顺序

 from  where group by haiving seclect order by

3、select colunm1，colunm2，colunm3，colunm4 from tabel (查询多列，列用英文半角逗号隔开)

     select * from tabel (查询所有的列)

     select  DISTINCT colunm1 from table( 去重)

4、注释

●单行注释

书写在“--”之后，只能写在同一行。
● 多行注释
书写在“/*”和“*/”之间，可以跨多行。

二、算术运算符和比较运算符以及逻辑运算符

1、算数运算符

SELECT 子句中可以使用常数或者表达式（包括算数运算），SQL 中也可以像平常的运算表达式那样使用括号 ( ) 。括号中运算表达式的优先级会得到提升，进行算数运算时需要注意数据是否为NULL，如果是null，那么运算结果是null

2、比较运算符

字符串类型的数据原则上按照字典顺序进行排序，不能与数字的大小顺序混淆。由于 NULL数值不确定，所以不能对 NULL 使用比较运算符，如果需要判断是不是null，那么只能使用语法iis/is not null

3、逻辑运算符

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

 

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

4、逻辑符运算顺序

由于AND 运算符优先于 OR 运算符，所以可借助（）改变运算顺序，列入查找出“商品种类为办公用品并且“登记日期是 2009 年 9 月11日或者 2009 年 9 月 20 日”

错误SQL

SELECT product_name, product_type, regist_date
FROM Product
WHERE product_type = ' 办公用品 '
AND regist_date = '2009-09-11'
OR regist_date = '2009-09-20';

该语句执行结果是，查找出登记日期是'2009-09-11'的 ' 办公用品 '或者登记日期'2009-09-20'的其他商品类型，那么如何得到上述答案呢？需要对错误SQL进行小小的改动即可实现。

修改后的SQL

SELECT product_name, product_type, regist_date
FROM Product
WHERE product_type = ' 办公用品 '
AND (regist_date = '2009-09-11'
OR regist_date = '2009-09-20');

5、逻辑运算符和真值

第四章 聚合函数与排序

一、聚合函数的分类和注意事项

1、聚合函数的理解

聚合，就是把多条数据汇总成一条数据，主要的作用就是汇总，汇总方式可以是（count,sum,avg,max,min）有些类似于excel数据透视表的汇总功能。函数就像一个盒子，你输入一些参数，他就给你返回一个值，SQL的聚合汇总功能是由他的聚合函数来实现的，人们输入参数得到自己想要的结果。

2、聚合函数注意事项

SUM/avg参数只能是数值，求和求平均数，不用数值拿到用字符或者日期？

max/min参数可以是任何数值类型

3、去重聚合的使用方法

去重的关键词是DISTINCT，如不重复计数  count(Distinct colunm1) 先去重再计数，不重复求和sum(Distinct colunm1) 先去重再求和

4、聚合时，参数列含有NULL数据，怎么处理？

聚合函数会无视他，除count(*)外

二、分组

1、分组的形象化理解，分组就是以某一列或某几列为划分维度把数据集切成多块，类似于切蛋糕，关键词是Group by，经常与聚合函数配合使用，集合函数+GroupBy 就是excel中的数据透视表。

有这么一组数据，如下图：

 

通过 SELECT count (product_type),product_type  group by product_type

 

 

 

2、分组注意事项

如果分组列有null,那么null会以空行的形式展现出来。

如果加入了对行的过滤条件，那么执行顺序是 FROM  ---- WHERE-----GROUP BY----SELECT；

如果还对分组的结果集指定条件，那么执行顺序是 FROM---WHERE-----GROUP BY----HAVING---SELCET

如果对分组结果不但限定了过滤条件还是指定了顺序，那么执行顺序是 FROM---WHERE--GROUP BY---HAVING-----SELECT---ORDER BY

注意，SQL关键词的书写顺序与执行顺序不一致，（为啥？目前不清楚，为啥按照以上顺序执行，我也不清楚）书写顺序：SELECT--FROM---WHERE---GROUP BY---HAVING--ORDER BY

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

（1）、SELECT 字句 不能出现聚合列之外的字段，只能是 常数、聚合函数、聚合列，因为聚合的结果将多行汇总成一行，如果出现聚合列之外的列，会出现一对多的情况，没有办法匹配。

（2）、GROUP BY字句不能使用 SELECT 字句中的别名，因为SELECT的执行顺序是在GROUP BY之后，你给他一个别名，他不知道是谁，怎么分组

（3）、GROUP BY 子句的结果集顺序是随机的

（4）、WHERE 子句 不能使用聚合函数作为过滤条件，因为 聚合函数只能在SELCET 字句之后，而SELECT字句的执行顺序是在 WHERE字句之后，找不到条件啊

三、对GROUP BY的结果集 进行指定条件

1、通过聚合函数和GROUP BY已经完成聚合分组了，那么如何取出一组还是多组呢？如何对分组指定条件呢？，使用HAVING字句

2、HAVING子句要素可以是常数、聚合函数，聚合列

3、WHERE和HAVING有啥区别？

首选，过滤对象不一样，WHERE是行过滤，HAVING是对分组进行过滤。

其次，语句执行性能不一样，HAVING因为是对分组进行过滤，过滤前查询器对数据进行了排序，having比WHERE 慢

最后，WHERE 子句指定条件所对应的列创建索引，这样也可以大幅提高处理速度

四、ORDER BY

由于SELECT结果集是无序且偶然的，那么当需要对结果集排序时需要使用ORDER BY关键字，ORDER BY 需要在SELCET 之后，ORDER BY使用语法如下：

1、升序/降序 （ASC/DESC）

2、可以指定单列或者多列进行排序，该列可以没有出现在SELECT 字句中的列，也可以是聚合函数

3、ORDER BY之后也可以使用列的名别

4、如果排序列有NULL值，null可能出现在行首或者行尾。

第五章 数据更新

一、插入数据

1、插入数据的基本语法

INSERTE into teble1 <列1，列2，列3，列4，列5>  VALUES  <V1，V2，V3，V4，V5>  就是给对应的列赋值

2、插入数据的注意要点

原则上，执行一次 INSERT 语句会插入一行数据。也可一次执行多行插入，多行插入可以省略列清单

例如：INSERTE into teble1   VALUES  <V1，V2，V3，V4，V5>

                                               VALUES  <V6，V7，V8，V9，V10>

                                               VALUES  <V11，V12，V13，V14，V15>

插入NULL

如果插入的值有NULL，那么直接在给相应的值赋Null值

插入默认值

INSERT INTO ProductIns (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0007', ' 擦菜板 ', ' 厨房用具 ', DEFAULT, 790, '2009-04-28');

从其他表中复制数据

INSERT INTO ProductIns (列1，列2，列3，列4，列5) SELECT 列1，列2，列3，列4，列5 from tabe where ---group by----having---orderby

二、数据删除

1、DROP 和DELETE区别

DROP 是对容器和数据的删除，DELETE 是对表内的数据删除，表头还在

2、对某一范围的数据进行删除

DELETE FORM tabel WHERE --

注意：可以通过WHERE控制删除数据范围，但是不能使用GROUP BY ORDER BY  HAVING

3、全表数据的快捷删除

TRUNCATE 不需要where 数据控制范围，直接删除数据保留容器

三、更新数据

1、不加控制条件的单列更新

UPDATE table set colunm1=表达式

2、加控制条件的单列更新

UPDATE table set colunm1=表达式 WHERE 

3、多列更新

UPDATE table set colunm1=表达式1，set colunm2=表达式2---WHERE

第六章 复杂查询

第一、视图

1、视图是什么？

视图其实就是一张临时表，从SELECT 的角度看没啥区别

2、视图和表有啥差异？视图有啥优点？

表存储在硬盘中，占空间，视图不会把数据保存到硬盘中，实际也不会存储在任何地方，存的只是SELECT语句，不占空间，方便调用。

4、创建和删除视图

CREATE VIEW 视图名字1（视图列1，视图列2---） AS  SELCET----

 DROP VIEW

5、使用视图注意的事项

SELECT 字句可以使用除ORDER BY之外的任何字句

视图最好不要嵌套否则影响 SQL性能

为了保障数据的一致性，视图最好不要进行更新

第二、子查询

1、子查询和视图的区别

视图是用于保存SELECT 语句的临时表，子查询是一次性的视图

2、子查询执行顺序

先执行子查询，然后在执行外层查询

3、子查询性能

原则上一个SELECT 语句可以嵌套多层子查询，但是子查询嵌套多了会影响查询性能

4、标量子查询

返回结果是一行、一列的查询，可以理解为一个数据值

例如：SELECT avg(sale_price) from product 

所以标量子查询可以写在where 字句后面与 比较运算符进行比较，也可以写在having 字句后，也可以写在select 字句后。

第三、关联子查询

关联子查询和 GROUP BY 子句一样，也可以对表中的数据进行切分。
●关联子查询的结合条件如果未出现在子查询之中就会发生错误。

SELECT
product _type,
product_name,
sale_price
FROM
Product AS P1
WHERE
sale_price > (
SELECT
AVG (sale_price)
FROM
Product AS P2
WHERE
P1.product_type = P2.product_type
GROUP BY
product_type
);

第七章、case 表达式

一、什么是case表达式？

case 表达式用于表达条件分支的函数，本质上和1+1没啥区别，具有计算功能

二、case表达式语法

CASE when colunm1=value1 then 表达式1 

          when colunm2=value2 then 表达式2 

         when colunm3=value3 then表达式3 

ELSE 表达式4 END

注意：else 语句虽然可以省略，但是为了语句的逻辑清晰，建议不要省略。case 表达式是标准SQL 各DBMS 通用，但是mysql中可以使用IF

三、case表达式使用

1、行转列

集合函数和group by 是行方向的变化，如果把行变成列，需要sum（Case when --then--else--end）as 列名

例如：

SELECT product_type,
SUM(sale_price) AS sum_price
FROM Product
GROUP BY product_type;

执行结果：

product_type | sum_price
--------------+----------
衣服        | 5000
办公用品 | 600
厨房用具 | 11180

 

-- 对按照商品种类计算出的销售单价合计值进行行列转换
SELECT SUM(CASE WHEN product_type = ' 衣服 '
THEN sale_price ELSE 0 END) AS sum_price_clothes,
SUM(CASE WHEN product_type = ' 厨房用具 '
THEN sale_price ELSE 0 END) AS sum_price_kitchen,
SUM(CASE WHEN product_type = ' 办公用品 '
THEN sale_price ELSE 0 END) AS sum_price_office
FROM Product;

执行结果：

sum_price_clothes | sum_price_kitchen | sum_price_office
------------------+-------------------+-----------------
5000 | 11180 | 600

2、case表达式书写位置

可以写在任何位置

 

第八章 函数和谓词

第一节、函数概念及分类

函数像就一个黑盒子，输入参数就回返回值，SQL函数有：

字符串函数（字符串的拼接、截取、长度测量）

时间日期函数 （）

算术运算符函数 （+ - */）

转换函数 (upper lower datefaomat year month,day---)

聚合函数 (sum count avg min max)

第二节、具体函数说明

1、ABS 绝对值函数，参数列中如果有数值为负数，那么会输出正数，null除外。

2、MOD 余数函数，MOD（被除数，除数）例如 MOD(7,3) 那么余数是1，余数都是正数。

3、 ROUND ——四舍五入 

4、字符串函数

|| ——字符串拼接 

注意：MySQL使用 CONCAT 函数来完成字符串的拼接

5、length （长度）用于计算字符串长度，以字节为单位，其中一个英文就是一个字节，汉字是两个字节

LENGTH(wl) 返回2    LENGTH(中国)返回4

6、LOWER ——小写转换，把大写的英文变成小写的英文

7、 UPPER ——大写转换  把小写的英文变成大写的英文

8、 SUBSTRING 函数 可以截取出字符串中的一部分字符串

      SUBSTRING（截取对象，截取位置，截取字符数量）

 9、CURRENT_DATE 返回系统当前日期

10、CURRENT_TIME  返回系统当前时间

11、CURRENT_TIMESTAMP 返回系统当前时间和日期 （时间戳）

12、EXTRACT( 日期元素 FROM  日期 ) 截取时间函数。

SELECT CURRENT_TIMESTAMP,
EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year,
EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month,
EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day,
EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour,
EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS minute,
EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;

now | year | month | day | hour | minute | second
--------------------------+------+-------+-----+------+-------+-------
2010-04-25 19:07:33.987+09 | 2010 | 4 | 25 | 19 | 7 | 33.987

第三节谓词

谓词的返回值全都是真值（ TRUE/FALSE/UNKNOWN ）。这也是谓词和函数的最大区别。一言以蔽之，谓词的作用就是“判断是否存在满足某种条件的记录”。如果存在这样的记录就返回真（ TRUE ），如果不存在就返回假（ FALSE ）。

1、LIKE 模糊查询

'abc%','%abc','%abc%'    查询abc开头，以abc结尾，或者中间包含'abc' 的字符串，%代表任意字符串

2、BETWEEN 谓词——范围查询

col1 BETWEEN v1 and v2       范围包含v1,v2

3、IS NULL 、 IS NOT NULL ——判断是否为 NULL

4、IN 谓词—— OR 的简便用法

当有多个OR时可以使用IN代替 ，如果不在范围内 可以用 NOT IN

子查询可以作为 IN 谓词的参数，例如：

ELECT product_name, sale_price
FROM Product
WHERE product_id IN (SELECT product_id
FROM ShopProduct
WHERE shop_id = '000C');

5、exist 谓词的使用方法

EXIST 通常都会使用关联子查询作为参数, 作为 EXIST 参数的子查询中经常会使用 SELECT * 。

SELECT product_name, sale_price
FROM Product AS P ①
WHERE EXISTS (SELECT *
FROM ShopProduct AS SP 
WHERE SP.shop_id = '000C'
AND SP.product_id = P.product_id);

第九章 集合运算

第一节集合运算类别

集合运算就是对满足同一规则的记录进行的加减等四则运算。

UNION /UNION ALL 两个表行方向的加法

INTERSECT 求交集

 EXCEPT 求差集

cross jion 两个表相乘

第二节 

1、UNION 

product1和product2的字段数量和性质性质完全一致，ORDER BY 子句只能在最后使用，并且结果是去重的，如果不需要去重需使用UNIONALL 操作符，请看如下韦恩图：

 

 

2、INTERSECT  求两个集合中的公共部分，语法和 UNION 完全一样

  EXCEPT 求两个集合的差集，语法和 UNION 完全一样

 

第三节 表的链接计算

联结（ JOIN ）就是将其他表中的列添加过来，进行“添加列”的集合运算。UNION 或者INTERSET 集合运算是以行（纵向）为单位进行操作，表现为行的增加或者减少，而联结（JION）则是以列（横向）为单位进行的。表现为列的增加或减少。

1、内联结—— INNER JOIN

返回的是A表和B表的公共部分 语法是SELECT <select_list> from tableA INNER JION tabelB ON A.KEY=B.KEY

2、外连接--OUT JION

返回的是主表的全部结果集，有LEFT OUT JION 和RIGTH OUT JION

 

 

第十章 窗口函数和GROUP ING A运算符

 

窗口函数并非是指有窗口的函数，这里的窗口是指分组后的限定范围，语法 <窗口函数> over（partition by clo1 order by col2）其中partition by 是可以省略的

窗口函数分为 聚合函数类型的窗口函数和专用窗口函数

聚合函数类型的：sum count max min avg

专用窗口函数：rank  DENS_rank  row_number (使用这三个专用窗口函数时需要使用任何参数)

窗口函数只能写在SELECT 字句后面。