SQL基础知识汇总(2019).doc

SQL 基础知识梳理（一）- 数据库与 SQL (2019.03)目录 Whats 数据库 数据库结构 SQL 概要 创建数据库与表 删除和更新表一、Whats 数据库1.数据库（Database，DB）：将大量数据保存起来，通过计算机加工而成的可以进行高效访问的数据集合。如：大型-银行存储的信息，小型-电话簿。2.数据库管理系统（Batabase Management System，DBMS）：用来管理数据库的计算机系统。3.关系型数据库（Relational Database，RDB）：目前应用最广泛的数据库。4.结构化查询语言（Structured Query Language，SQL）：专门用于操作 RDB。5.常见的 5 种关系型数据库管理系统（Relational Database Management System，RDBMS）：Oracle Database：甲骨文公司SQL Server：微软公司DB2：IBM 公司PostgreSQL：开源MySQL：开源二、数据库结构1.服务器：用于接收并处理其它程序发出的请求的程序（软件），或者是安装此类程序的设备（计算机）。2.客户端：向服务器发出请求的程序（软件），或者是安装此类程序的设备（计算机）。3.表（table）：类似 Excel，由行和列组成的二维表。4.字段：表的列（垂直方向）。5.记录：表的行（水平方向）。【注意】关系数据库必须以行为单位进行数据读写。6.单元格：行列交汇处。【注意】与 Excel 不同，一个单元格只能输入一个数据。三、SQL 概要1.SQL 语句：用关键字、表名和列名等组合而成的一条语句。2.3 种SQL 语句种类：（1）DDL（Data Definition Language，数据定义语言）：创建、删除或修改数据库以及数据库中的表等对象。CREATE：创建数据库和表等对象DROP：删除数据库和表等对象ALTER：修改数据库和表等对象（2）DML（Data Manipulation Language，数据操作语言）：查询或修改表中的记录。SELECT：查询表中的数据INSERT：向表中插入数据UPDATE：修改表中的数据DELETE：删除表中的数据（3）DCL（Data Control Language，数据控制语言）：确认或取消对数据库中的数据变更的执行操作，以及对用户的操作数据库中的对象权限进行设定。COMMIT：确认对数据库中的数据进行的变更ROLLBACK：取消对数据库中的数据进行的变更GRANT：赋予用户的操作权限REVOKE：取消用户的操作权限【备注】DML 使用最频繁。3.SQL 语句书写规范：以分号（；）结尾；大小写不敏感，不区分关键字的大小写；【注意】插入到表中的数据是区分大小写的，如“HI”、“Hi”和“hi”都不同。该系列随笔将采用“关键字大写，表名和列名的首字母大写”的格式。单词使用半角空格或换行符隔开4.常数的书写方式：字符串、日期：用单引号括起来（），如Hello，2016-09-24。数字：直接书写，不用加单引号，如：5。四、创建数据库与表1.数据库创建：- 语法：CREATE DATABASE CREATE DATABASE Shop - 创建名为 Shop 的数据库2.表的创建- 语法：- CREATE TABLE - (- ,- ,- .- ,- ,- .- )- 创建名为 Shohin 的表CREATE TABLE Shohin( shohin_id CHAR(4) NOT NULL, shohin_mei VARCHAR(100) NOT NULL, shohin_bunrui VARCHAR(32) NOT NULL, hanbai_tanka INTEGER , shiire_tanka INTEGER , torokubi DATE , PRIMARY KEY (shohin_id) );3.命名规则：只能使用半角英文字母、数字、下划线（_）作为数据库、表和列的名称；规范要求命名以半角英文字符开头；名称不能重复。4.数据类型的指定：声明该列的数据类型，是一种约束。5.数据类型介绍：INTEGER：整型，意味不能存储小数；CHAR：字符串型，如 CHAR（10）和 CHAR（100）中的括号表名该列可以存储的字符串的最大长度。它是“定长字符串”，如 CHAR(8) 表示在列中插入abc 时会自动保存成 abc （后面5个半角空格）的形式；VARCHAR：字符串型，类似 CHAR，不同的是它是“可变长字符串”，如VARCHAR(8) 在插入 abc，保存的就是字符串 abc；DATE：日期类型；. .6.约束：非空约束：NULL 是空白（无记录）的意思的关键字，NOT NULL 表示必须输入的约束。主键约束：主键是可以确定一行数据的列，一般通过它取特定行的数据，它是唯一的，不允许重复。. .五、更新和删除表1.删除表- 语法：DROP TABLE DROP TABLE Shohin; - 删除名为 Shohin 的表【注意】被删的表，无法恢复。2.更新表（1）添加列- 语法：ALTER TABLE ADD ;ALTER TABLE Shohin ADD Shohin_mei_Kana VARCHAR(100); - 在 Shohin 中添加名为 Shohin_mei_Kana 类型为 VARCHAR(100) 的列（2）删除列- 语法：ALTER TABLE DROP COLUMN ;ALTER TABLE Shohin DROP COLUMN Shohin_mei_Kana; - 删除 Shohin 表中名为 Shohin_mei_Kana 的列【注意】表定义变更后无法恢复。SQL 基础知识梳理（二） - 查询基础目录 SELECT 语句基础 算术运算符和比较运算符 逻辑运算符一、SELECT 语句基础1.查询指定列：SELECT 关键字-语法：-SELECT , . - 希望查询列的名称-FROM - 指定选取数据的表- 从 Shohin 中取 3 列SELECT shohin_id, shohin_mei, hanbai_tanka- 列的顺序可以任意指定，逗号（“,”）分隔，查询结果的顺序和 SELECT 子句中的顺序相同FROM Shohin;2.查询表的所有列：星号（*）-语法-SELECT * - 星号（*）代表所有列-FROM ;【备注】使用星号（*）的话就无法设定列的显示顺序3.为列设定别名：AS 关键字SELECT shohin_id AS Id, shohin_mei AS Name, shiire_tanka PriceFROM Shohin; - 不用 AS 关键字也可以SELECT shohin_id AS 编号, shohin_mei AS 名称, shiire_tanka 价格FROM Shohin; - 设定汉语别名：加上双引号（）或单引号（）4.常数的查询SELECT 产品 AS Product, - 产品：字符串常数 38 AS Price, - 38：数字常数 2016-09-30 AS 生产日期 - 2009-02-24：日期常数【备注】字符串和日期使用单引号（）。5.从结果中删除重复行：DISTINCT原图（1）SELECT DISTINCT shohin_bunrui FROM dbo.Shohin;-使用 DISTINCT 移除shohin_bunrui 列中的重复数据（2）DISTINCT对 NULL 类型的处理：存在多条 NULL 值的行时，会结合为一条 NULL 数据。SELECT DISTINCT shiire_tanka FROM dbo.Shohin;（3）多列之前使用 DISTINCTSELECT DISTINCT shohin_bunrui, torokubiFROM dbo.ShohinDISTINCT 会将多个列的数据进行组合，将重复的数据结合为一条。【注意】DISTINCT 关键字只能用在第一个列名之前。6.筛选记录：WHEREWHERE 子句中可以指定“某一列的值和这个字符串相等”或者“某一列的值大于这个数字”等条件，找出只符合该条件的记录。-语法：-SELECT , .-FROM -WHERE ;SELECT shohin_id, shohin_mei, shohin_bunruiFROM dbo.ShohinWHERE shohin_bunrui = 衣服;- shohin_bunrui = 衣服：为条件表达式选取行之后，再输出列【备注】WHERE 子句：首先通过该子句查询出符合指定条件的记录，再选取出 SELECT 语句指定的列。【注意】SQL 子句的书写格式是固定的，不能随意更改。如 WHERE 子句必须紧跟在 FROM 子句后。7.注释的写法注释对于 SQL 的执行没有任何影响。- 单行注释/* 多行注释*/二、算术运算符和比较运算符1.算术运算符SELECT shohin_mei, hanbai_tanka, hanbai_tanka * 2 AS hanbai_tanka_x2FROM dbo.Shohin;图 商品单价的两倍四则算术运算符含义运算符加法+减法-乘法*除法/括号（“(”“)”）可以提高表达式的优先级。2.需要注意 NULLSELECT 5 + NULL, 10 - NULL, 1 * NULL, 4 / NULL, NULL / 9;【备注】所有包含 NULL 的计算，结果肯定为 NULL。3.比较运算符比较运算符运算符含义=相等不等=大于等于大于=小于等于小于-示例1：SELECT shohin_mei, shohin_bunruiFROM dbo.ShohinWHERE hanbai_tanka = 500;选取hanbai_tanka 列为 500 的记录-示例2SELECT shohin_mei, shohin_bunruiFROM dbo.ShohinWHERE hanbai_tanka 500;-示例3SELECT shohin_mei, shohin_bunruiFROM dbo.ShohinWHERE hanbai_tanka != 500;选取hanbai_tanka 列的值不是 500 的记录-示例4SELECT *FROM dbo.ShohinWHERE hanbai_tanka - shiire_tanka = 500;3.对字符串使用不等号时的注意事项创建表原图-示例：选取出大于2的数据的 SELECT 语句SELECT *FROM dbo.CharsWHERE chr 2;【注意】chr 为字符串类型，对字符串类型的数据进行大小比较时，跟数字不一样。4.不能对 NULL 使用比较运算符-示例1：SELECT shohin_mei, shiire_tankaFROM dbo.ShohinWHERE shiire_tanka = NULL; -错误的 SELECT 语句-示例2SELECT shohin_mei, shiire_tankaFROM dbo.ShohinWHERE shiire_tanka IS NULL; -选取 NULL 的记录-示例3SELECT shohin_mei, shiire_tankaFROM dbo.ShohinWHERE shiire_tanka IS NOT NULL; -选取不为 NULL 的记录【注意】希望选取 NULL 记录时，使用 IS NULL；希望选取不是 NULL 的记录时，使用 IS NOT NULL。三、逻辑运算符1.NOT 运算符：取反-示例：SELECT *FROM dbo.ShohinWHERE NOT hanbai_tanka = 1000;-等价于 hanbai_tanka 1000取hanbai_tanka 列不大于 1000 的记录（hanbai_tanka = 3000;SELECT shohin_mei, shiire_tankaFROM dbo.ShohinWHERE shohin_bunrui = 厨房用具 OR hanbai_tanka = 3000;【备注】多个查询条件进行组合时，需要使用 AND 运算符或者 OR 运算符。2.通过括号进行强化-示例1SELECT shohin_mei, shohin_bunrui, torokubiFROM dbo.ShohinWHERE shohin_bunrui = 办公用品 AND torokubi = 2009-09-11 OR torokubi = 2009-09-20;-示例2SELECT shohin_mei, shohin_bunrui, torokubiFROM dbo.ShohinWHERE shohin_bunrui = 办公用品 AND (torokubi = 2009-09-11 OR torokubi = 2009-09-20);【备注】AND 运算优先于 OR 运算，想要优先执行 OR 运算时可以使用括号。SQL 基础知识梳理（三） - 聚合和排序目录 对表进行聚合查询 对表进行分组 为聚合结果指定条件 对查询结果进行排序一、对表进行聚合查询1.聚合函数（1）5 个常用函数：COUNT：计算表中的记录（行）数。SUM：计算表中数值列的数据合计值。AVG：计算表中数值列的数据平均值。MAX：求出表中任意列中数据的最大值。MIN：求出表中任意列中数据的最小值。（2）聚合：将多行汇总成一行。图1-1Shohin 表2.计算表中数据的行数-示例SELECT COUNT(*)- *：参数，这里代表全部列FROM dbo.Shohin;3.计算 NULL 以外数据的行数将 COUNT(*) 的参数改成指定对象的列，就可以得到该列的非 NULL 行数。SELECT COUNT(shiire_tanka)FROM dbo.Shohin;只计算非 NULL 的行【备注】除了 COUNT 函数，其它函数不能将星号作为参数。【备注】COUNT 函数的结果根据参数的不同而不同。COUNT(*) 会得到包含 NULL 的数据行数，而 COUNT() 会得到 NULL 之外的数据行数。4.计算合计值SELECT SUM(hanbai_tanka) AS sum_hanbai_tanka, -总和 AVG(hanbai_tanka) AS avg_hanbai_tanka, -平均 MAX(hanbai_tanka) AS max_hanbai_tanka, -最大 MIN(hanbai_tanka) AS min_hanbai_tanka -最小FROM dbo.Shohin;【备注】所有的聚合函数，如果以列名为参数，会无视 NULL 值所在的行。SELECT MAX(torokubi),-torokubi 为日期 MIN(torokubi)FROM dbo.Shohin【备注】MAX/MIN 函数几乎适用于所有数据类型的列。SUM/AVG 函数只适用于数值类型的列。5.使用聚合函数删除重复值（关键字 DISTINCT）-示例1：计算去除重复数据后的数据行数SELECT COUNT(DISTINCT shohin_bunrui)FROM dbo.Shohin;-示例2：先计算数据行数再删除重复数据的结果SELECT DISTINCT COUNT(shohin_bunrui)FROM dbo.Shohin;【备注】在聚合函数的参数中使用 DISTINCT（示例1），可以删除重复数据。DISTINCT 不仅限于 COUNT 函数，所有的聚合函数都可以使用。二、对表进行分组1.GROUP BY 子句-语法：-SELECT , , .-FROM -GROUP BY , , .;-示例SELECT shohin_bunrui AS 商品种类, COUNT(*) AS 数量FROM dbo.ShohinGROUP BY shohin_bunrui;【备注】GROUP BY 子句中指定的列称为“聚合键”或“分组列”。【子句的书写顺序（暂定）】SELECT - FROM - WHERE - GROUP BY2.聚合键中包含 NULL 的情况SELECT shiire_tanka, COUNT(*)FROM dbo.ShohinGROUP BY shiire_tanka;【备注】聚合键中包含 NULL 时，在结果中也会以 NULL 行的形式表现出来。3.WHERE 对 GROUP BY 执行结果的影响-语法-SELECT , , .-FROM -WHERE -GROUP BY , , .SELECT shiire_tanka, COUNT(*)FROM dbo.ShohinWHERE shohin_bunrui = 衣服GROUP BY shiire_tanka这里是先根据 WHERE 子句指定的条件进行过滤，然后再进行聚合处理。【执行顺序】FROM - WHERE - GROUP BY - SELECT。这里是执行顺序，跟之前的书写顺序是不一样的。4.与聚合函数和 GROUP BY 子句有关的常见错误（1）易错：在 SELECT 子句中书写了多余的列SELECT 子句只能存在以下三种元素：常数聚合函数GROUP BY 子句中指定的列名（即聚合键）易错点1【总结】使用 GROUP BY 子句时，SELECT 子句不能出现聚合键之外的列名。（2）易错：在 GROUP BY 子句中写了列的别名易错点2回顾之前说的执行顺序，SELECT 子句是在 GROUP BY 子句之后执行。所以执行到GROUP BY 子句时无法识别别名。【总结】GROUP BY 子句不能使用 SELECT 子句中定义的别名。（3）易错：GROUP BY 子句的结果能排序吗？【解答】它是随机的。如果想排序，请使用 ORDER BY 子句。【总结】GROUP BY 子句结果的显示是无序的。（4）易错：在 WHERE 子句中使用聚合函数易错点3【总结】只有 SELECT 子句和 HAVING 子句（以及 ORDER BY 子句）中能够使用聚合函数。三、为聚合结果指定条件1.HAVING 子句WHERE 子句智能指定记录（行）的条件，而不能用来指定组的条件。【备注】HAVING 是 HAVE（拥有）的现在分词。-语法：-SELECT , , .-FROM -GROUP BY , , .-HAVING 【书写顺序】SELECT - FROM - WHERE - GROUP BY - HAVINGSELECT shohin_bunrui, COUNT(*)FROM dbo.ShohinGROUP BY shohin_bunruiHAVING COUNT(*) = 2从通过商品种类进行聚合分组后的结果中，取出“包含数据的行数为 2 行”的数据2.HAVING 子句的构成要素（1）3 要素：常数聚合函数GROUP BY 子句中指定的列名（即聚合键）易错用法3.HAVING 与 WHERE有些条件可以写在 HAVING 子句中，又可以写在 WHERE 子句中。这些条件就是聚合键所对应的条件。结果一样【建议】虽然结果一样，聚合键对应的条件应该写在 WHERE 子句中，不是 HAVING 子句中。【理由】WHERE 子句的执行速度比 HAVING 快。意义：WHERE 子句 = 指定行所对应的条件，HAVING 子句 = 指定组所对应的条件。四、对查询结果进行排序1.ORDER BY 子句-语法：-SELECT , , .-FROM -ORDER BY , , .SELECT shohin_id, hanbai_tankaFROM dbo.ShohinORDER BY hanbai_tanka; -升序排列销售单价由低到高（升序）排序键：ORDER BY 子句中书写的列名。【书写顺序】SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY2.升序（ASC）和降序（DESC）：SELECT shohin_id, hanbai_tankaFROM dbo.ShohinORDER BY hanbai_tanka DESC; -降序排列【备注】ORDER BY 子句中排列顺序时会默认使用升序（ASC）进行排列。3.指定多个排序键SELECT shohin_id, shohin_mei, hanbai_tanka, shiire_tankaFROM dbo.ShohinORDER BY hanbai_tanka, shohin_id;4.NULL 值的顺序：排序键中包含 NULL 时，会在开头或末尾进行汇总。5.在排序键中使用 SELECT 子句中的别名SELECT shohin_id AS id, shohin_mei, hanbai_tanka AS htFROM dbo.ShohinORDER BY ht, id;【执行顺序】FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY【备注】ORDER BY 子句可以使用 SELECT 子句中定义的别名，GROUP BY 子句不能使用别名。6.ORDER BY 子句中使用聚合函数SELECT shohin_bunrui, COUNT(*)FROM dbo.ShohinGROUP BY shohin_bunruiORDER BY COUNT(*);7.不建议使用列的编号进行排序，虽然可以SELECT shohin_id , shohin_mei , shohin_bunrui , hanbai_tanka , shiire_tanka , torokubiFROM dbo.ShohinORDER BY hanbai_tanka DESC, shohin_id;SELECT shohin_id , shohin_mei , shohin_bunrui , hanbai_tanka , shiire_tanka , torokubiFROM dbo.ShohinORDER BY 4 DESC, 1;-这里使用列的编号，不推荐使用【备注】在 ORDER BY 子句中不要使用列的编号。SQL 基础知识梳理（四） - 数据更新目录 插入数据 删除数据 更新数据 事务一、插入数据1.INSERT 语句的基本语法-语法：-INSERT INTO (列1, 列2, .) VALUES (值1, 值2, .)-示例INSERT INTO dbo.Shohin ( shohin_id , shohin_mei , shohin_bunrui , hanbai_tanka , shiire_tanka , torokubi )VALUES ( 0011 , - shohin_id - char(4) T 恤衫 , - shohin_mei - varchar(100) 衣服 , - shohin_bunrui - varchar(32) 1000 , - hanbai_tanka - int 500 , - shiire_tanka - int GETDATE() - torokubi - date );（1）列名和值用逗号隔开，分别放在括号里，这种形式称为清单。 ( shohin_id , shohin_mei , shohin_bunrui , hanbai_tanka , shiire_tanka , torokubi ) -列清单 ( 0011 , - shohin_id - char(4) T 恤衫 , - shohin_mei - varchar(100) 衣服 , - shohin_bunrui - varchar(32) 1000 , - hanbai_tanka - int 500 , - shiire_tanka - int GETDATE() - torokubi - date ) -值清单 一次性多行 INSERT2.省略列清单INSERT INTO dbo.ShohinVALUES ( 0014 , - shohin_id - char(4) T 恤衫 , - shohin_mei - varchar(100) 衣服 , - shohin_bunrui - varchar(32) 1000 , - hanbai_tanka - int 500 , - shiire_tanka - int GETDATE() - torokubi - date );3.插入 NULL 值INSERT INTO dbo.ShohinVALUES ( 0015 , - shohin_id - char(4) T 恤衫 , - shohin_mei - varchar(100) 衣服 , - shohin_bunrui - varchar(32) 1000 , - hanbai_tanka - int NULL , - shiire_tanka - int GETDATE() - torokubi - date );【备注】插入 NULL 值的列不能设置为 NOT NULL 约束。4.插入默认值-示例：创建表时设置默认值CREATE TABLE ShohinIns( id CHAR(4) NOT NULL, num INT DEFAULT 0 -DEFAULT 默认约束)-示例：INSERT 时显示插入默认值INSERT INTO dbo.ShohinIns ( id, num )VALUES ( 001, - id - char(4) DEFAULT - num - int )-示例：INSERT 隐式插入默认值INSERT INTO dbo.ShohinIns( id )VALUES (002)【备注】建议使用显示的方式插入值。如果插入的时候隐式省略了列名，并且该列没有指定默认值的时候，该列的值会被设定为 NULL（如果省略的列设置了 NOT NULL 约束的话，执行会报错）。【总结】省略 INSERT 语句的列名，就会自动设定该列的默认值（没有默认值时会设定为 NULL）。5.从其它表复制数据-语法INSERT . SELECT .-示例INSERT dbo.ShohinIns ( id, num )SELECT shohin_id, shiire_tankaFROM dbo.Shohin-示例INSERT INTO dbo.ShohinIns ( id, num )SELECT id=1, num =2;-示例INSERT INTO dbo.ShohinIns ( id, num )SELECT 1, 2;【备注】INSERT 语句中的 SELECT 语句中，可以使用 WHERE 子句或者 GROUP BY 子句等任何 SQL 语法。（但使用 ORDER BY 子句并不会产生任何效果。）二、删除数据1.2 种 删除数据的方法：（1）DROP TABLE 语句：删除表（2）DELETE 语句：删除表的数据-语法-DELETE FROM ;-示例DELETE FROM dbo.ShohinIns;【备注】DELETE 语句删除的对象是记录（行），不是表和列。2.指定删除对象的 DELETE 语句-语法-DELETE FROM -WHERE ;-示例DELETE FROM dbo.ShohinWHERE hanbai_tanka = 4000;【备注】可以通过 WHERE 子句指定对象条件来删除部分数据。【备注】DELETE 语句中不能使用 GROUP BY、HAVING 和 ORDER BY 三