基础-SQL操作，MySQL为主
2020-07-28 本文已影响0人
植物大战代码
内容来自《SQL必知必会》(4th)、
基础SQL语法，以MySQL为主
1.基本数据库名词
数据库：存放数据的容器
表：同一类数据的结构化存储，表名唯一
列：同种数据类型和意义的数据占据的表的一个字段
行：表按行存储
主键：一列或几列来唯一的标识每一行，不允许修改更新，不能重用
外键：***
2.语法
注释
行内做注释：
SELECT prod_name -- 注释在这里
FROM Products;
单行注释:
#注释在这里
SELECT prod_name
FROM Products;
多行注释：
/* 注释
在这里*/
SELECT prod_name
FROM Products;
SELECT 检索
检索单列：
SELECT prod_name
FROM Products;
检索多列：
SELECT a,b,c
FROM Products;
检索所有列：
SELECT *
FROM Products;
检索单列去重复：
SELECT DISTINCT a
FROM Products;
检索多列去重复：
SELECT DISTINCT a,b,c
FROM Products;
(DISTINCT 不能部分作用，他作用于后面所有列)
MySQL中只查询前五行的结果：
SELECT a
FROM Products
LIMIT 5; ---这就是MySQL的分页查询！！！
( Oracle中：SELECT a FROM Products WHERE ROWNUM <=5; )
MySQL中从第3行起的5行数据：
SELECT a
FROM Products
LIMIT 5 OFFSET 3;#也就是从第4行到第8行这五行数据
MySQL支持另一种写法:LIMIT 3,5;
ORDER BY排序检索
单列排序
SELECT prod_name
FROM Products
ORDER BY prod_name#这个子句一定是最后最后的位置
按多个列排序：
SELECT prod_id,prod_price,prod_name
FROM Products
ORDER BY prod_price, prod_name#先后顺序
按列的位置排序(尽量不用):
SELECT prod_id,prod_price,prod_name
FROM Products
ORDER BY 2,3#SELECT 中的第2第3列
降序排列：
SELECT prod_id,prod_price,prod_name
FROM Products
ORDER BY prod_price DESC, prod_name# DESC 只作用于单行
WHERE过滤行(条件)
| 含义 | 操作符 |
|---|---|
| 等于 | = |
| 大于 | > |
| 小于 | < |
| 大于等于 | >= |
| 小于等于 | <= |
| 不等于 | <>，!= |
| 不大于 | !> |
| 不小于 | !< |
| 两个值之间 | BETWEEN |
| 空值 | IS NULL |
某列定值过滤：
SELECT prod_id,prod_price,prod_name
FROM Products
WHERE prod_price = 3.49;
范围过滤：
SELECT prod_id,prod_price,prod_name
FROM Products
WHERE prod_price BETWEEN 5 AND 10;
空值(无值)检查：
SELECT prod_id,prod_price,prod_name
FROM Products
WHERE prod_price IS NULL;
条件的 AND 且
条件的 OR 或 (前一个表达式为真则后表达式不运算)
AND 的优先级比 OR 要高
建议不管优先级，多用括号：
SELECT prod_id,prod_price,prod_name
FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = 'DLL11') AND prod_price <= 4;
多值 OR 用 IN 代替
IN 后面的括号里还可以放SQL语句：
SELECT prod_id,prod_price,prod_name
FROM Products
WHERE vend_id IN ('DLL01', 'BRS01')
ORDER BY prod_name;
用 NOT 来否定条件：
SELECT prod_id,prod_price,prod_name
FROM Products
WHERE vend_id NOT IN ('DLL01', 'BRS01')
LIKE通配符用于文本字段
| 通配符 | 匹配对象 |
|---|---|
| % | 任何字符出现任意次(包括0)，除了NULL |
| _ | 匹配单个字符(出现1次)比如一个数字需要两个_ |
| [] | 字符集中的某个字符可匹配单个字符 |
| [^XX] | 表否，相当于在WHERE后面直接加NOT |
找出所有以Fish开头的产品：
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE 'Fish%'# 搜索是可以区分大小写的
#%可以放在开头结尾也可以放在中间，可以替代各种字符，唯一不会匹配NULL
找出所有姓名是以J或M开头的联系人：
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%'
ORDER BY cust_contact;
格式输出（计算字段）
| 功能 | 语法 |
|---|---|
| 字段拼接 | + 或 ||, MySQL中用Concat()函数 |
| 去掉空格 | RIRIM()去右边，LTRIM()去左边，TRIM()去左右两边 |
| 取别名(导出列) | AS |
| 算术运算符 | +，-，*，/ （常在做运算后取别名） |
MySQL字段拼接，且去空格：
SELECT Concat(TRIM(vend_name), '(', TRIM(vend_country), ')')
AS vend_title
FROM Vendors
ORDER BY vend_name;
计算可单独处理：
SELECT 3*2 -- 输出结果6
SELECT TRIM(' abc ')--#输出abc
SELECT Now() -- 输出当前日期和时间
数据处理函数
函数在不同数据库中差异较大
| 常用文本处理函数 | 字符串操作 |
|---|---|
| LEFT()，RIGHT() | 左边字符,左边字符 |
| LENGTH() | 长度 |
| LOWER()，UPPER() | 转小写,转大写 |
| LTRIM(),RTRIM(),TRIM() | 去空格 |
| SOUNDEX() | soundex 匹配发音 |
SELECT cust_name, cust_contact
FROM Customers
WHERE SOUNDEX(A) = SOUNDEX('love')
还有日期和时间处理，数值的处理函数，稍后整理
5个集聚函数
| 函数 | 说明 |
|---|---|
| AVG() | 某列平均值(忽略NULL) |
| COUNT() | 某列行数(括号中为*时不忽略NULL) |
| MAX()、MIN() | 排序后最大/小的一个值，包括文本排序，忽略NULL |
| SUM() | 求和 |
聚集函数的参数 DISTINCT忽略重复值
特定供应商提供产品的平均价格：
SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';
具有电子邮件地址的客户数(就是地址不为空)
订单总额：
SELECT SUM(quantity*item_price) AS total_price
FROM OrderItems
WHERE order_num = 20005;
组合使用聚集函数：
SELECT COUNT(*) AS num_items,
MIN(A) AS price_min,
MAX(A) AS price_max,
AVG(A) AS price_avg
FROM Products;
分组GROUP BY 和 HAVING
GROUP BY对每一组进行聚集计算和其他操作
分组可以嵌套，分组后所有指定列同步计算，所有NULL单独作为一组
SELECT中所有字段名必须在GROUP BY中给出
位置在WHERE和ORDER BY之间
分组输出：按vend_id列分组计数
SELECT vensd_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;
可以用相对位置：GROUP BY 2，1 按选择的第二个列分组，然后按第一个列分组
HAVING 过滤分组后的聚集行 WHERE过滤的是简单行
HAVING 在分组后执行 而WHERE会在分组前执行
HAVING可以代替所有的WHERE
分组情景：返回提供10个
只留下计数大于等于2的分组，而这个计数就是基于分组的，所以用HAVING：
SELECT cust_id,COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
列出具有两个以上产品且其价格大于等于4的供应商：
SELECT vend_id, COUNT(*) AS num_prods--4显示
FROM Products
WHERE prod_price >= 4--1取出价格大于等于4的行
GROUP BY vend_id --2对vend_id进行分组
HAVING COUNT(*) >= 2--2取出计数大于2的分组
书写顺序和执行顺序：
SELECT--5选择指定列
FROM--1读表
WHERE--2条件过滤行
GROUP BY--3分组
HAVING--4分组过滤
ORDER BY--6排序后显示
MySQL的语句执行顺序
MySQL的语句一共分为11步，如下图所标注的那样，最先执行的总是FROM操作，最后执行的是LIMIT操作。其中每一个操作都会产生一张虚拟的表，这个虚拟的表作为一个处理的输入，只是这些虚拟的表对用户来说是透明的，但是只有最后一个虚拟的表才会被作为结果返回。如果没有在语句中指定某一个子句，那么将会跳过相应的步骤。
下面我们来具体分析一下查询处理的每一个阶段
FROM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1
ON: 对虚表VT1进行ON筛选，只有那些符合<join-condition>的行才会被记录在虚表VT2中。
JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, rug from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止。
WHERE： 对虚拟表VT3进行WHERE条件过滤。只有符合<where-condition>的记录才会被插入到虚拟表VT4中。
GROUP BY: 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5.
CUBE | ROLLUP: 对表VT5进行cube或者rollup操作，产生表VT6.
HAVING： 对虚拟表VT6应用having过滤，只有符合<having-condition>的记录才会被 插入到虚拟表VT7中。
SELECT： 执行select操作，选择指定的列，插入到虚拟表VT8中。
DISTINCT： 对VT8中的记录进行去重。产生虚拟表VT9.
ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10.
LIMIT：取出指定行的记录，产生虚拟表VT11, 并将结果返回。
多表查询(子查询）
方法一：用IN语句多层嵌套：作为子查询的SELECT只能包含单列
方法二：
数据库表都是关系表，现有：
Orders表：订单编号，客户ID, 订单日期
OrderItems表：订单编号，...（等等订单物品之类的列）
Customers表：客户ID,...（客户其他信息）
(分析：OrderItems表 订单--物品；Customers表：客户--客户身份信息；
Orders表：订单--客户)
现需要列出订购了物品RGAN01的所有顾客的身份信息（就是需要从物品--订单--客户--身份信息，这样一个步骤）
SELECT cust_name, cust_contact--客户身份信息
FROM Customers
WHERE cust_id IN （SELECT cust_id--客户id
FROM Order
WHERE order_num IN(SELECT order_num --订单编号
FROM OrderItems
WHERE prod_id = 'RGAN01'));
联结 JOIN ON
为什么要联结？
数据分成多个表可以更高效的存储和管理，那么在查询存在在多个表中的关联数据的时候，就要把用到联结，如果不用联结的话返回的结果是笛卡尔积，一般是用where语句实现等值联结，也有join on的语法代替where，联结部分改变只是
from where部分，
内连接 FROM A INNER JOIN B ON ******
就是等值连接,只是把符合条件的行值连接起来
自连接
使用表别名使自联结代替子查询，表现出得性能比where的子查询更好
自然联结
第一个表用通配符，再指出其他的列，使相同的列只出现一次
内连接都是自然连接，自动去重
外连接 FROM A INNER JOIN B ON
在多表连接时需要包括其中某个表中无匹配项的行和字段，空值填充
左连接就是保留完整左表，右连接就是保留完整右表，左右都保留就是全外联结
MySQL中不支持全外联结，可以用组合查询UNION实现
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
with three tables t1, t2, t3:
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
LEFT JOIN t3 ON t2.id = t3.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
LEFT JOIN t3 ON t2.id = t3.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
RIGHT JOIN t3 ON t2.id = t3.id
————————————————
版权声明：本文为CSDN博主「xiaoxiang-chen」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/fengzijinliang/article/details/52513050
组合查询 UNION
将多个select查询的结果合并在一起，在每段SELECT语句中间加入关键字UNION,他在结果中会自动去除重复行，使用关键字union all就不会消除重复行，如果需要对最终结果进行排序，就把ordered by关键字放在最末尾
其他的UNION有
except(检索只在第一个表存在而第二个不存在的行)，
intersect(检索两个表都存在的行)
这些都可以用联结实现
表中插入数据 INSERT
1.插入完整的行
INSERT INTO Customers(xxxx,
xxx,
...,
x)--指定列名
VALUES('1',
'2',
...,
'10')--要对应好各个字段的值
列可以省略部分列，前提是表的该列允许NULL类型的值或者他不允许NULL但是他会给出默认值，如果不满足就会插入失败
2.与SELECT FROM WHERE语句一起使用，实现查询直接插入，也可以实现多行插入
INSERT INTO Customers(xxxx,
xxx,
...,
x)--指定列名
SELECT xxxx,
xxx,
...,
x
FROM Products
WHERE prod_name LIKE 'Fish%';--要对应好各个字段的值
3.整表的复制
MySQL语法如下：
CREATE TABLE CustCopy(新表) AS
SELECT *
FROM Customers
WHERE ---
GROUP BY 等等，还可以联结
更新 UPDATE - SET - WHERE
删除 DELETE FROM -WHERE
用WHERE语句避免更新所有的行，单独使用UPDATE就是更新所有行该列的值
1.更新某一行某一列：
UPDATE Customers
SET cust_email = xxx
WHERE cust_id = xx--把某一行某一列值设为NULL相当于删除
2.用一个表的数据去更新另一个表的数据,UPDATE语句是否支持FROM
删除的是表的内容就算内容全部删除也会留下空表
如果删除所有行 可以使用TRUNCATE TABLE
有一些原则
1.注意考虑where语句
2.保证每个表都有主键
3.在更新删除之前，先用select 进行测试
4.强制实施，不允许删除与其他表关联的行
5.提前使用约束避免一些操作
表创建CREATE 表结构修改ALTER 删除表DROP
只有不允许NULL的列可以作为主键
创建表：指定列表名，列名，数据类型，值的限制
CREATE TABLE Xxxxx:
{
x text NOT NULL,--不允许空值
xx INTEGER ,--默认允许NULL，DB2要求必须指定
...
xxxxxxx INTEGER NOT NULL DEFAULT 1,--设定默认值，常用！
}
可设置默认值，添加数据时候不提供值就会自动加入默认值
常用时间作为默认值，
**** DEFAULT CURRENT_DATE()--这是MySQL的写法
ALTER TABLE Vendors
ADD vend_phone CHAR(20);--增加一列，删除用DROP
DROP TABLE CustCopy;--
ALTER TABLE之前要注意备份，该语句不能撤销
复杂表结构的删除一般只有，先把数据copy到一个新表，检查新表，重命名旧表或者删除，旧表名字命名新表，再进行一些索引外键等等的创建
