SQL-基础知识汇总(共58页).docx

精选优质文档-倾情为你奉上SQL 基础知识梳理（一）- 数据库与 SQL (2019.03) 目录····· 一、What's 数据库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 <表名>- (- <列名1> <数据类型> <约束>,- <列名1> <数据类型> <约束>,- .- <表约束1>,- <表约束2>,- .- )- 创建名为 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 语句基础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_x2'FROM 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 < 1000） 2.AND 运算符和 OR 运算符AND 运算符：并且，在两侧的查询条件都成立时整个查询条件才成立。OR 运算符：在两侧的查询条件就算只有一个成立时整个查询条件都成立。-示例SELECT shohin_mei, shiire_tankaFROM dbo.ShohinWHERE shohin_bunrui = '厨房用具' AND 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-1 Shohin 表 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 <列名1>, <列名2>, .-FROM <表名>-GROUP BY <列名1>, <列名2>, .;-示例SELECT shohin_bunrui AS '商品种类', COUNT(*) AS '数量'FROM dbo.ShohinGROUP BY shohin_bunrui;【备注】GROUP BY 子句中指定的列称为“聚合键”或“分组列”。【子句的书写顺序（暂定）】SELECT -> FROM -> WHERE -> GROUP BY 2.聚合键中包含 NULL 的情况SELECT shiire_tanka, COUNT(*)FROM dbo.ShohinGROUP BY shiire_tanka;【备注】聚合键中包含 NULL 时，在结果中也会以 NULL 行的形式表现出来。 3.WHERE 对 GROUP BY 执行结果的影响-语法-SELECT <列名1>, <列名2>, .-FROM <表名>-WHERE <表达式>-GROUP BY <列名1>, <列名2>, .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 <列名1>, <列名2>, .-FROM <表名>-GROUP BY <列名1>, <列名2>, .-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 <列名1>, <列名2>, .-FROM <表名>-ORDER BY <排序基准列1>, <排序基准列2>, .SELECT shohin_id, hanbai_tankaFROM dbo.ShohinORDER BY hanbai_tanka; -升序排列销售单价由低到高（升序）排序键：ORDER BY 子句中书写的列名。【书写顺序】SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY  2.升序（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 三类子句。【备注】如果要删除表全部数据时请使用 TRUNCATE <表名>，它比 DELETE 子句更高效。 三、更新数据1.UPDATE 子句的基本语法-语法-UPDATE <表名>-