导图社区 SQL
- 687
- 81
- 13
- 举报
SQL
基于Access的SQL，适用于所有SQL学习，对SQL有一个系统完整的认识。
编辑于2020-09-17 13:31:34
- 相似推荐
- 大纲
SQL
1. SQL语法
1. 命令+条件子句 2. 未排序，记录无序 3. 语句不区分大小写 , 建议关键字为大写 4. 字符串用单引号'adc' 5. 语句忽略所有空格 6. 分号;分隔多行语句，单行建议加 7. 可写为单行或者多行(易阅读)
例子： SELECT 产品名 FROM 产品表; SELECT 产品名 FROM 产品表; SELECT 产品名 FROM 产品表;
注释
单行注释
--(两个连字符)
SELECT 产品名--这是一条备注 FROM 产品表;
多行注释
注释从/*开始，到*/结束
/*SELECT 产品名 FROM 产品表;*/
Access不支持
表名/字段名中有空格/-需用中括号[]
2. 数据类型
Microsoft Access 数据类型 数据类型 描述 存储 Text 用于文本或文本与数字的组合。最多 255 个字符。 Memo Memo 用于更大数量的文本。最多存储 65,536 个字符。注释：无法对 memo 字段进行排序。不过它们是可搜索的。 Byte 允许 0 到 255 的数字。 1 字节 Integer 允许介于 -32,768 与 32,767 之间的全部数字。 2 字节 Long 允许介于 -2,147,483,648 与 2,147,483,647 之间的全部数字。 4 字节 Single 单精度浮点。处理大多数小数。 4 字节 Double 双精度浮点。处理大多数小数。 8 字节 Currency 用于货币。支持 15 位的元，外加 4 位小数。提示：您可以选择使用哪个国家的货币。 8 字节 AutoNumber AutoNumber 字段自动为每条记录分配数字，通常从 1 开始。 4 字节 Date/Time 用于日期和时间 8 字节 Yes/No 逻辑字段，可以显示为 Yes/No、True/False 或 On/Off。在代码中，使用常量 True 和 False （等价于 1 和 0）。注释：Yes/No 字段中不允许 Null 值 1 比特 Ole Object 可以存储图片、音频、视频或其他 BLOBs（Binary Large OBjects）。 最多 1GB Hyperlink 包含指向其他文件的链接，包括网页。 Lookup Wizard 允许您创建一个可从下拉列表中进行选择的选项列表。 4 字节
3. 操作符
算术操作符
+
-
*
/
^ 乘方
\ 取整
Mod 取余
字符操作符
+，&
同VBA
赋值操作符
=(唯一)
数值型
where 工龄=20
日期型
Access/VBA
例：where 日期=#2019/8/20#
SQL Server
例：where 日期='2019/8/20'
文本型
例：where 地区='苏州'
比较操作符
=
>
<
>=
<=
<>
同VBA
ALL
一组值的比较都为TRUE，则TRUE
例：(1,2,3,4)>all (2,3)——4
SOME
一组值的比较中，部分为TRUE，则TRUE
例：(1,2,3,4)>some (2,3)——3、4
ANY
一组值的比较中任何一个为TRUE，则TRUE
例：(1,2,3,4)>any (2,3)——3、4
同义词
例子只是为了理解 some/any不支持 (1,2,3,4)
IN
操作数等于表达式列表中的一个，则TRUE
例：(1,2,3,4)=any (2,3)——2、3
例：(1,2,3,4) in (2,3)——2、3
例：(1,2,3,4)<>any (2,3)——1、4
例：(1,2,3,4) not in (2,3)——1、4
in(1,2,3,4) in('系统','TMS','WMS')
BETWEEN
匹配范围中的所有值，包含开始和结束值，则TRUE
例：between 20 and 30
EXISTS
子查询包含一些记录，则TRUE
LIKE
操作数与一种模式相匹配，则TRUE
例：北京市 like '%北京%'
通配符
% 任意多值
_ 单一值
[] 制定范围内的任意单个字符
LIKE '[B-K]book%'
所有5个字母中都以B到K中任意一个子母开头 开头且后面是字母book的字段数据
! 不在指定范围内的任意单个字符
LIKE '[!BC]%'
逻辑操作符
AND
两个布尔表达式都为TRUE，则TRUE
OR
两个布尔表达式的一个为TRUE，则TRUE
AND优先级更高 建议使用圆括号对操作符进行明确分组
NOT
对任何其它布尔操作符的值相反
IS [NOT] NULL
无值[否]，则为TRUE
……
WHERE子句操作符 共同组成表达式
4. 常用函数
数学函数
ABS(X)
绝对值
SIGN(X)
x为复数、零、正数返回-1、0、1
MOD(X,Y)
求余
FLOOR(X)
小于等于x的最大整数
CEILING(X)/CEIL(X)
大于等于x的最大整数
POWER(X,Y)
x的y次方的数值
ROUND(X,Y)
X按照小数点数d进行四舍五入
SQRT(X)
x的平方根
字符串函数
LCase
所有字母转小写
UCase
所有字母转大写
Mid
Left
Right
Len
Instr
搜索一个字符在另个一字符中最先出现的位置
同VBA
日期和时间 函数
Year
Month
Day
Time
当前时间
Date
当前日期
同VBA
Datediff
返回跨两个指定日期的日期和时间边界数
Dateadd
返回指定日期加上上一段时间后的新日期
聚集函数
Min
Max
SUM
AVG
算术平均数
同VBA
COUNT
COUNT(*) 对表中行的数目进行计数
COUNT(column) 对特定列进行计数，忽略Null值
5. SELECT语句
字段列表
各字段用逗号","分隔
例：SELECT 姓名,部门,工资
用星号"*"代表所有的字段
多表字段
数据表名.字段名
例：SELECT 信息表.姓名,考勤表.姓名
例：SELECT [信息表$].姓名,[考勤表$].姓名
SELECT子句顺序 SELECT FROM WHERE GROUP BY HAVING ORDER BY
6. 属性词
ALL
所有记录 默认
TOP
TOP 10： 就是前10条记录
SELECT TOP 5 cust_name
DISTINCT
DISTINCT 客户名称：提取不重复的客户名称(作用所有列)
例：SELECT DISTINCT 字段 FROM 数据表
7. 子句
FROM
唯一必须子句
定义
指定要查询的数据表 各数据表之间用逗号","分隔
数据库
例：FROM 数据表名
Excel
例：FROM [工作表$]
例：FROM 定义的名称
例：FROM [销售记录$A1:M100]
Select * from 数据表名 查询全部数据 用星号"*"代表所有的字段
WHERE
定义
指定查询的条件 用于标准的行级过滤
数值
例：WHERE 年龄<40
字符串
例：WHERE 部门='销售部'
日期
例：WHERE 日期=#2020/02/25#
例：WHERE 日期='2020/02/25'
GROUP BY
定义
指定分组（日期，部门，产品名称……）
例：GROUP BY 部门
HAVING 过滤分组
指定分组查询的条件 用于分组聚集值过滤
用法与WHERE类似
SELECT cust_id,COUNT(cust_id) FROM Orders GROUP BY cust_id HAVING COUNT(cust_id)>=2;
应结合ORDER BY保证数据排序 这是保证数据正确排序的唯一方法
ORDER BY
升序(ASC) 默认
降序(DESC)
支持多列排序
不支持别名排序
支持列位置排序 非选择列不可用
例：ORDER BY 工资总额, 年龄 DESC 工资升序，年龄降序
SELECT语句中最后一条子句 非选择列亦可排序
8. 计算字段
别名/导出列
用AS关键字赋予
可以是一个单词 ，字符串（需在引号中）
例：SELECT 姓名 AS name,部门,工资
拼接字段
使用+拼接多个列构成一个值
SELECT cust_id+'('+cust_address+')' FROM Customers;
算数计算
SELECT prod_id, quantity, item_price,quantity*item_price AS expanded_price FROM OrderItems WHERE OrderItems.order_num=20008;
测试计算
SELECT 3*2 省略FROM
9. 子查询
嵌套在其他查询中的查询
子查询总是从内向外处理
子查询难以阅读和调试，应格式化SQL
子查询进行过滤
SELECT cust_id FROM Orders WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id="RGAN01");
作为计算字段
SELECT cust_id, cust_name, (SELECT Count(*) FROM Orders WHERE Orders.cust_id=Customers.cust_id) AS oders FROM Customers ORDER BY Customers.cust_id;
WHERE中使用了完全列名
作为子查询的SELECT语句 只能查询单个列
10. 联结
定义
联结是一种机制，用来在一条SELECT语句中关联多个表返回一组输出
联结类型
等值联结 /内联结
两个表中匹配值相等
1.WHERE法 SELECT vend_name, prod_name, prod_price FROM Vendors,Products WHERE Vendors.vend_id = Products.vend_id; 2.INNER JOIN ON法(更规范，建议) SELECT vend_name, prod_name, prod_price FROM Vendors INNER JOIN Products ON Vendors.vend_id=Products.vend_id
联结多个表
方法一： SELECT prod_name,vend_name,prod_price, quantity FROM (Vendors INNER JOIN Products ON Vendors.vend_id = Products.vend_id) INNER JOIN OrderItems ON Products.prod_id = OrderItems.prod_id WHERE OrderItems.order_num=20007;
方法二 SELECT prod_name,vend_name,prod_price, quantity FROM OrderItems,Vendors, Products WHERE Vendors.vend_id = Products.vend_id AND Products.prod_id = OrderItems.prod_id AND OrderItems.order_num=20007;
连接4个数据表的用法： FROM ((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号 连接5个数据表的用法： FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号) INNER JOIN 表5 ON Member.字段号=表5.字段号
弊端
标准的联结（内联结）返回所有数据，相同的列甚至多次出翔
表别名
1.一条SELECT语句中多次使用相同的表 2.缩短SQL语句
SELECT C.cust_name, C.cust_contact FROM Customers AS C, Orders AS O, OrderItems AS OI WHERE C.cust_id = O.cust_id AND O.order_num = OI.order_num AND OI.prod_id='RGAN01' ORDER BY C.cust_name;
可用于WHERE子句、SELECT、ORDER BY子句
与列别名不一样，表别名不返回到客户端
自联结
子查询 SELECT cust_id, cust_name, cust_contact FROM Customers WHERE cust_name=(SELECT cust_name FROM Customers WHERE cust_contact='Jim Jones'); 自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句 SELECT C1.cust_id, C1.cust_name, C1.cust_contact FROM Customers AS C1,Customers AS C2 WHERE C1.cust_name=C2.cust_name AND C2.cust_contact='Jim Jones';
自然联结
自然联结排除多次出现，使每一列只返回一次
SELECT C.*, O.order_num, O.order_date, OI.prod_id, OI.quantity, OI.item_price FROM Customers AS C, Orders AS O, OrderItems AS OI WHERE C.cust_id=O.cust_id AND OI.order_num=O.order_num AND prod_id='RGAN01'; 自然联结要求你选择哪些唯一的列，通常一个表使用通配符*(SELECT *) 对其他的表使用明确的子集完成
外联结
LEFT OUTER JOIN 左外联结
SELECT Customers.cust_id, Orders.order_num FROM Customers LEFT OUNTER JOIN Orders ON Customers.cust_id = Orders.cust_id; 查询所有顾客，包含没有订单的顾客
RIGHT OUTER JOIN 右外联结
SELECT Customers.cust_id, Orders.order_num FROM Customers RIGHT OUNTER JOIN Orders ON Customers.cust_id = Orders.cust_id; 查询所有订单，包含没有顾客的订单
使用聚合函数的联结
SELECT Customers.cust_id, COUNT(Orders.order_num) FROM Customers INNER JOIN Orders ON Customers.cust_id = Orders.cust_id GROUP BY Customers.cust_id; 结果相同 SELECT Customers.cust_id, COUNT(*) FROM Customers INNER JOIN Orders ON Customers.cust_id = Orders.cust_id GROUP BY Customers.cust_id;
笛卡尔积 /叉联结
SELECT vend_name, prod_name, prod_price FROM Vendors,Products ORDER BY vend_name 第一张表每一行与第二张表中的每一行配对
WHERE千万别忘了
11. 组合
定义
执行多条SELECT语句，作为一个查询结果集返回
用途
1.在一个查询中从不同的表返回结构数据 2.对一个表执行多个查询，按一个查询返回数据
UNION 组合查询/复合查询
SELECT cust_name, cust_contact,cust_email FROM Customers WHERE cust_state IN('IL','IN','MI') UNION SELECT cust_name, cust_contact,cust_email FROM Customers WHERE cust_name='Fun4All'; 较复杂的过滤条件或者从多个表中检索数据，建议UNION SELECT cust_name, cust_contact,cust_email FROM Customers WHERE cust_state IN('IL','IN','MI') OR cust_name='Fun4All';
规则
1.必须由两条或两条以上的SELECT语句组成，各语句间用关键字UNION分隔 2.每个查询必须包含相同的列、表达式或聚合函数（次序可以不一致） 3.列数据类型必须兼容 4.查询结果集中自动去除重复的行（UNION ALL返回所有匹配行） 5.只能使用一条ORDER BY子句，并且位于最后一条SELECT语句后
多个表组合时， 不匹配列名可以使用别名
12. 插入数据
插入完整的行
不安全，避免使用，必须按照表中列的次序填充 INSERT INTO Customers VALUES('1000000006', 'Toy Land', '123 Any Street', 'New York', 'NY', '1111', 'USA', NULL, NULL); 表后括号中明确给出列名，VALUES必须按次序输入值 优点：表结构改变，语句依然正确 INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES('1000000006', 'Toy Land', '123 Any Street', 'New York', 'NY', '1111', 'USA', NULL, NULL);
1.不提供列名，必须给每个表列提供一个值 2.提供列名，必须给列出的每个列一个值
插入行的一部分
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country) VALUES('1000000006', 'Toy Land', '123 Any Street', 'New York', 'NY', '1111', 'USA');
省略列条件： 1.该列定义为允许NULL值 2.在表中给出了默认值
插入某些查询的结果
1.一次插入多行数据 2.列匹配条件为列的位置，第一列填充第一列 INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country) SELECT cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country FROM CustNew WHERE cust_id='1000000010'
一个表复制到另一个新表
如果只复制部分列，可以明确给出列名 SELECT* INTO CustCopy FROM Customers;
如果某列无值，应使用NULL值 (前提表允许该列指定为空值)
13. 更新和删除
UPDATE 更新表中数据
用途
1.更新表中的特定行 2.更新表中的所有行
用法
1.要更新的表 2.列名和它们的新值 3.确定要更新哪些行的过滤条件
1.更新单列 UPDATE Customers SET cust_email='kim@thetoystore.com' WHERE cust_id='1000000005' 2.更新多列（用逗号分隔列） UPDATE Customers SET cust_contact='Sam Roberts', cust_email='sam@tovland.com' WHERE cust_id='1000000006'; 3.支持使用子集 4.删除某列的值 UPDATE Customers SET cust_email=NULL WHERE cust_id='1000000005'
DELETE 删除表中数据
用途
1.删除表中的特定行 2.删除表中的所有行
用法
1.指定要删除数据的表 2.确定要删除哪些行的过滤条件
DELETE FROM Customers WHERE cust_id='1000000006';
更快的删除
TRUNCATE TABLE
外键可以 防止误删
切记勿省略 WHERE子句 否则更新或删除全部
14. 创建和操纵表
CREATE TABLE 创建表
用法
新的表名，在关键字CREATE TALBE之后给出 表列的名字、数据类型定义，指定NULL,用逗号分隔
主键不能设为NULL CREATE TABLE Products_1( prod_id TEXT(10) NOT NULL PRIMARY KEY , vend_id TEXT(10) NOT NULL , prod_name TEXT(254) NOT NULL , prod_price SINGLE NOT NULL , prod_desc Memo NULL );
ALTER TABLE 更新表
添加列
ALTER TABLE Vendors ADD vend_phone TEXT(20);
删除列
ALTER TABLE Vendors DROP COLUMN vend_phone ;
DROP TABLE 删除表
DROP TABLE CustCopy;
重命名表
不支持
不支持默认值
15. 其它
视图
就是查询
存储过程
事务
游标
高级特性
索引
主键
外键
约束
触发器
安全