基础-SQL操作，MySQL为主

内容来自《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

锁image

用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到一个新表，检查新表，重命名旧表或者删除，旧表名字命名新表，再进行一些索引外键等等的创建

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 215,874评论 6赞 498
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 92,102评论 3赞 391
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 161,676评论 0赞 351
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 57,911评论 1赞 290
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 66,937评论 6赞 388
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 50,935评论 1赞 295
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,860评论 3赞 416
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,660评论 0赞 271
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,113评论 1赞 308
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,363评论 2赞 331
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,506评论 1赞 346
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,238评论 5赞 341
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,861评论 3赞 325
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,486评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,674评论 1赞 268
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,513评论 2赞 368
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,426评论 2赞 352