基础-SQL操作，MySQL为主

作者: 植物大战代码 | 来源:发表于2020-07-28 20:18 被阅读0次

基础-SQL操作，MySQL为主
MySql数据库基础及IDE
My SQL 3
MySQL基础知识（系统归类）
Linux Mysql
Node.JS使用Sequelize操作MySQL
MySQL学习笔记---基础概念和一些基础SQL语句（一）
thinkphp操作mysql之SQl语句报错及解决方案
MySQL-SQL基础应用+Infomation_Schema介
clickhouse-sql 记录常用操作

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

基础-SQL操作，MySQL为主
内容来自《SQL必知必会》(4th)、基础SQL语法，以MySQL为主 1.基本数据库名词数据库：存放数据的容...
MySql数据库基础及IDE
一、基础操作--sql语句启动MySQL：service mysql start停止MySQL：service ...
My SQL 3
2.1 MySQL 基础（二）- 表操作 #学习内容# 1. MySQL表数据类型 2. 用SQL语句创建表 ...
MySQL基础知识（系统归类）
一、MySQL基础知识数据库的操作无非就是增删改查，具体的包括库操作、表操作和数据的操作。很多人说SQL语句难学...
Linux Mysql
Linux Mysql Mysql Workbench SQL 对大小写不敏感 SQL 分为两个部分数据操作语言...
Node.JS使用Sequelize操作MySQL
Node.JS提供了操作数据库的基础接口，我们可以通过mysql模块的query方法进行操作，但是需要编写SQL语...
MySQL学习笔记---基础概念和一些基础SQL语句（一）
MySQL学习笔记---基础概念和一些基础SQL语句（一）作者：zhangjunqiang MySQL学习笔记，...
thinkphp操作mysql之SQl语句报错及解决方案
thinkphp操作mysql SQl语句报错及解决方案 thinkphp操作mysql报错原因：排除语法错误（...
MySQL-SQL基础应用+Infomation_Schema介
MySQL-SQL基础应用+Infomation_Schema介绍-Day3 1、SQL 介绍 2、SQL 作用 ...
clickhouse-sql 记录常用操作
clickhouse-sql 记录常用操作 sql 相关主要记录 mysql 导入 clickhouse 过程其...