本帖最后由 Mint97 于 2020-5-13 20:42 编辑
SQL 基础知识梳理（一）- 数据库与 SQL
目录
一、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.表的创建 [url=] [/url]
-- 语法：-- CREATE TABLE <表名>-- (-- <列名1> <数据类型> <约束>,-- <列名1> <数据类型> <约束>,-- ...-- <表约束1>,-- <表约束2>,-- ...-- )[url=][/url]
[url=][/url]
-- 创建名为 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) );
五、更新和删除表 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 基础知识梳理（二） - 查询基础
序 这是《SQL 基础知识梳理（一）- 数据库与 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）多列之前使用 DISTINCT
SELECT DISTINCT shohin_bunrui, torokubiFROM dbo.Shohin
DISTINCT 会将多个列的数据进行组合，将重复的数据结合为一条。
【注意】DISTINCT 关键字只能用在第一个列名之前。
6.筛选记录：WHERE
WHERE 子句中可以指定“某一列的值和这个字符串相等”或者“某一列的值大于这个数字”等条件，找出只符合该条件的记录。
--语法：--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.需要注意 NULL
SELECT 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 的记录 --示例2
SELECT shohin_mei, shohin_bunruiFROM dbo.ShohinWHERE hanbai_tanka <> 500;
--示例3
SELECT shohin_mei, shohin_bunruiFROM dbo.ShohinWHERE hanbai_tanka != 500;
选取 hanbai_tanka 列的值不是 500 的记录
--示例4
SELECT *FROM dbo.ShohinWHERE hanbai_tanka - shiire_tanka >= 500;
3.对字符串使用不等号时的注意事项
  -- DDL：创建表CREATE TABLE Chars(chr CHAR(3) NOT NULL,PRIMARY KEY (chr));-- DML：插入数据INSERT INTO Chars VALUES ('1');INSERT INTO Chars VALUES ('2');INSERT INTO Chars VALUES ('3');INSERT INTO Chars VALUES ('10');INSERT INTO Chars VALUES ('11');INSERT INTO Chars VALUES ('222');
创建表
原图
--示例：选取出大于'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.通过括号进行强化
--示例1
SELECT 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 基础知识梳理（四） - 数据更新
序 这是《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 ) --值清单
INSERT INTO dbo.Shohin ( shohin_id , shohin_mei , shohin_bunrui , hanbai_tanka , shiire_tanka , torokubi )VALUES ( '0012' , -- shohin_id - char(4) 'T 恤衫' , -- shohin_mei - varchar(100) '衣服' , -- shohin_bunrui - varchar(32) 1000 , -- hanbai_tanka - int 500 , -- shiire_tanka - int GETDATE() -- torokubi - date ), ( '0013' , -- shohin_id - char(4) 'T 恤衫' , -- shohin_mei - varchar(100) '衣服' , -- shohin_bunrui - varchar(32) 1000 , -- hanbai_tanka - int 500 , -- shiire_tanka - int GETDATE() -- torokubi - date );
一次性多行 INSERT
2.省略列清单
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 <表名>-- SET <列名> = <表达式>
2.指定条件的 UPDATE 语句
--语法
--UPDATE <表名>-- SET <列名> = <表达式>--WHERE <条件>;
--示例
UPDATE dbo.Shohin SET hanbai_tanka = hanbai_tanka * 10WHERE shohin_bunrui = '厨房用具';
3、使用 NULL 进行更新
--示例UPDATE dbo.Shohin SET torokubi = NULLWHERE shohin_id = '008';
【备注】只有未设置 NOT NULL 约束和主键约束的列才可以清空为 NULL。
4.多列更新
--示例UPDATE dbo.Shohin SET hanbai_tanka = hanbai_tanka * 10, --逗号分割 shiire_tanka = shiire_tanka / 2WHERE shohin_bunrui = '厨房用具'
四、事务 1.事务：需要在同一个处理单元中执行的一系列更新处理的集合。
2.创建事务：
--语法--事务开始语句-- DML 语句①;-- DML 语句②;-- ...--事务结束语句(COMMIT 或 ROLLBACK);
（1）COMMIT - 提交处理：提交事务包含的全部更新处理的结束指令。一旦提交，无法恢复到事务开始前的状态。
（2）ROLLBACK - 取消处理：取消事务包含的全部更新处理的结束指令。一旦回滚，数据库就会回复到事务开始之前的状态。
--示例
BEGIN TRANSACTION; UPDATE dbo.Shohin SET hanbai_tanka = hanbai_tanka - 1000 WHERE shohin_mei = '运动 T 恤'; UPDATE dbo.Shohin SET hanbai_tanka = hanbai_tanka + 1000 WHERE shohin_mei = 'T 恤衫'; DELETE FROM dbo.Shohin WHERE shohin_id = '0015';ROLLBACK; -- COMMIT
3.ACID 特性
（1）原子性（Atomicity）：在事务结束时，其中包含的更新处理要么全部执行，要么完全不执行。
（2）一致性（Consistency）：事务中包含的处理，要满足数据库提前设置的约束，也称完整性。
（3）隔离性（lsolation）：保证不同事务之前互不干扰的特性。
（4）持久性（Durability）：事务一旦结束，DBMS 会保证该时点的数据状态得以保存的特性，也称耐久性。
备注 这里采用 MS SQL Server 进行验证，不保证所有的 DBMS 执行结果正确。
|
楼主
