db2 sql语句将字符串转换为列名_SQL必知必会笔记

我通读了SQL必知必会一书，对于常用的SQL语法和概念笔记在这里列出来。

P.S.我累了，不想调知乎的格式了...因为md格式联结到这里序列有问题。

SQL必知必会笔记​mp.weixin.qq.com

基础定义

• 数据库：保存有组织的数据的容器（通常是一个文件或一组文件）。
• 表：某种特定类型数据的结构化清单。
• 模式：关于数据库和表的布局及特性的信息。
• 列：表中的一个字段，所有表都是由一个或多个列组成的。
• 数据类型：所允许的数据的类型。每个表列都有相应的数据类型，它限制（或允许）该列中存储的数据。
• 行：表中的一个记录。
• 主键：一列（或一组列），其值能够唯一标识表中每一行。（表中的任何列都可以作为主键，只要满足以下条件：1.任意两行都不具有相同的主键值；2.每一行都必须具有一个主键值；3.主键列中的值不允许修改或更新；4.主键值不能重用（如果某行从表中删除，它的主键不饿能赋给以后的新行）。
• 事务：一组SQL语句。
• 回退：撤销指定SQL语句的过程。
• 提交：将未存储的SQL语句结果写入数据库表。
• 保留点：事务处理中设置的临时占位符。

一般语句顺序举例

SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...

基本语法

• SELECT语句

SELECT prod_id, prod_name, prod_price
FROM Products;

• ORDER BY语句

SELECT prod_id, prod_name, prod_price
FROM Products
ORDER BY prod_price DESC, prod_name; --DESC表示以价格降序来排列产品，默认升序排列

• WHERE语句

SELECT prod_id, prod_name, prod_price
FROM Products
WHERE prod_price > 100
ORDER BY prod_price DESC, prod_name;

WHERE子句特殊操作符有如下：!=/<>（不等于）、!<（不小于）、!>（不大于）、BETWEEN（在指定两个值之间）、IS NULL（为NULL值）

• 分组数据GROUP BY子句和HAVING子句

SELECT COUNT(*) AS num_prods 
FROM Products 
GROUP BY vend_id; 

GROUP BY按vend_id排序并分组数据。子句使用的规定：①GROUP BY可包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组；②如果GROUP BY子句中嵌套了分组，数据将在最后指定的分组上进行汇总；③GROUP BY中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）；④除聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY子句中。

HAVING实现过滤分组

SELECT cust_id, COUNT(*) AS orders 
FROM Orders 
GROUP BY cust_id 
HAVING COUNT(*) >= 2; 

上面过滤COUNT(*)>=2的那些分组。

HAVING和WHERE的差别：WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响 HAVING子句中基于这些值过滤掉的分组。

• 操作符

1. AND操作符（当要通过不止一个列进行过滤）
2. OR操作符（检索匹配任意条件的行）

WHERE子句可以包含任意数目的AND和OR操作，允许两者结合以进行复杂、高级的过滤，但要注意通过圆括号对于操作符进行明确分组。举例如下
SELECT prod_name, prod_price
FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >= 10;

1. IN操作符（用来指定条件范围，范围中的每个条件都可以进行匹配）

SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN ('DLL01', 'BRS01') 
ORDER BY prod_name;

其优点在于：①在有很多合法选项时，IN操作符的语法更清楚，更直观；②在与其他AND和OR操作符组合使用IN时，求职顺序更容易管理；③IN操作符一般比一组OR操作符执行得更快；④IN的最大优点是可以包含其他SELECT语句，能更动态地建立WHERE子句。

1. NOT操作符

SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01' 
--等价于 WHERE vend_id <> 'DLL01'
ORDER BY prod_name;

• 通配符：用来匹配值的一部分的特殊字符。LIKE作为操作符，即WHERE子句中的谓语，后面接通配符确定搜索模式来匹配。搜索模式：由字面值、通配符或两者组合构成的搜索条件。

1. 百分号(%)通配符：匹配0个、1个或多个字符

SELECT prod_id, prod_name  
FROM Products  
WHERE prod_name LIKE 'Fish%';  --查找prod_name为Fish开头的记录

1. 下划线(_)通配符：匹配单个字符

SELECT prod_id, prod_name 
FROM Products 
WHERE prod_name LIKE '__ inch teddy bear'; --查找prod_name为 inch teddy bear前有两个字符的记录

1. 方括号([])通配符：指定一个字符集，它必须匹配指定位置的一个字符

SELECT cust_contact 
FROM Customers 
WHERE cust_contact LIKE '[JM]%' --查找所有名字以J或M起头的联系人
ORDER BY cust_contact; 

使用通配符的技巧：

不要过度使用通配符。通配符搜索一般比前面讨论的其他搜索要耗费更长的处理时间。
在确定需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。把通配符放在开始处，搜索是最慢的。
仔细注意通配符的位置。

• 函数

函数语法提取字符串的组成部分Access使用 MID()； DB2、 Oracle、 PostgreSQL和 SQLite使用 SUBSTR()；MySQL和SQL Server使用SUBSTRING()数据类型转换Access和Oracle使用多个函数，每种类型的转换有一个函数；DB2 和PostgreSQL使用CAST()；MariaDB、MySQL和SQL Server使用 CONVERT()取当前日期Access使用 NOW()； DB2和 PostgreSQL使用 CURRENT_DATE； MariaDB和MySQL使用CURDATE()；Oracle使用SYSDATE；SQL Server使用GETDATE()；SQLite使用DATE()

可移植：所编写的代码可以在多个系统上运行。SQL函数不是可移植的。

1. 文本处理函数

函数形式作用RTRIM()/LTRIM()去除列值右边/左边的空格UPPER()/LOWER()将文本转换为大写/小写LEFT()返回字符串左边的字符LENGTH()返回字符串的长度SOUNDEX()返回字符串的SOUNDEX值

SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法，考虑了类似的发音字符和音节，使得能对字符串进行发音比较而不是字母比较。

1. 日期和时间处理函数

不同软件都有对应的数据处理函数，举例如下：

SELECT order_num 
FROM Orders 
WHERE DATEPART(yy, order_date) = 2012; --SQL Server
--Access WHERE DATEPART('yyyy', order_date) = 2012; 
--PostgreSQL WHERE DATE_PART('year', order_date) = 2012; 
--Oracle WHERE to_number(to_char(order_date, 'YYYY')) = 2012; 
--Oracle的to_date函数用来将两个字符串转换为日期
--MySQL WHERE YEAR(order_date) = 2012; 
--SQLite WHERE strftime('%Y', order_date) = '2012';

1. 数值处理函数

函数形式作用ABS()绝对值COS()余弦EXP()指数值PI()圆周率SIN()正弦SQRT()平方根TAN()正切

• 汇总数据

1. 聚集函数：对某些行运行的函数，计算并返回一个值。

函数形式作用AVG()平均值，只用于确定特定数值列的平均值，且列名必须作为函数参数给出，忽略列值为NULL的行COUNT()行数，COUNT(*)对行数目进行计数，包括NULL，COUNT(column)对特定列中具有值的行进行计数，忽略NULL值MAX()最大值，查找最大的数值或日期值，忽略列值为NULL的行MIN()最小值，忽略列值为NULL的行SUM()某列值之和，忽略列值为NULL的行

1. 聚集不同值：只包含不同的值，指定DISTINCT参数。
2. 组合聚集函数

SELECT COUNT(*) AS num_items
       , MIN(prod_price) AS price_min
       , MAX(prod_price) AS price_max
       , AVG(prod_price) AS price_avg 
FROM Products; 

• 使用子查询：任何SQL语句都是查询，此术语一般指SELECT语句。

SELECT cust_id 
FROM Orders 
WHERE order_num IN (
    SELECT order_num                     
    FROM OrderItems                     
    WHERE prod_id = 'RGAN01'
); 

子查询总是从内向外处理，首先执行子查询结果，返回两个订单号20007和20008，这两值以IN操作符要求的逗号分隔的格式传递给外部查询的WHERE子句。

作为子查询的SELECT语句只能查询单个列。

还可以作为计算字段使用子查询。

SELECT cust_name
       , cust_state
       , (SELECT COUNT(*) FROM Orders WHERE Orders.cust_id = Customers.cust_id) AS orders FROM Customers  ORDER BY cust_name;  

• 表联结

可伸缩：能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序 称为可伸缩性好（scale well）。

1. 创建联结

①笛卡尔积/叉联结：由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目 将是第一个表中的行数乘以第二个表中的行数。

②内联结：等值联结，基于两个表之间的相等测试。

SELECT vend_name, prod_name, prod_price 
FROM Vendors, Products 
WHERE Vendors.vend_id = Products.vend_id; --WHERE作为过滤条件

其中表连接可以实现子查询的功能。举例如下：

使用子查询

SELECT cust_name, cust_contact 
FROM Customers 
WHERE cust_id IN (
    SELECT cust_id 
    FROM Orders 
    WHERE order_num IN (
        SELECT order_num
        FROM OrderItems                                       
        WHERE prod_id = 'RGAN01'
    )
); 

使用表联结

SELECT cust_name
       , cust_contact 
FROM Customers, Orders, OrderItems 
WHERE Customers.cust_id = Orders.cust_id AND OrderItems.order_num = Orders.order_num AND prod_id = 'RGAN01'; 

③高级联结（自联结、自然联结、外联结INNER JOIN）

• 组合查询

1. UNION并：必须由两条或两条以上的SELECT语句组成，语句之间用UNION分隔；每个查询必须包含相同的列、表达式或聚集函数；列数据类型必须兼容。

备注：UNION ALL不去重，而UNION去重。

实际操作

1. 创建计算字段

• 概念
  字段：基本上与列的意思相同，经常互换使用，不过数据库列一般称为列，而术语字段通常与计算字段一起使用。
  拼接：将值联结在一起构成单个值。
• 操作
  ①计算字段
  ②拼接字段（可用+或||表示，Access和 SQL Server使用+号。DB2、Oracle、PostgreSQL、SQLite和 Open Office Base使用||）
  SELECT vend_name + ' (' + vend_country + ')' AS vend_title --SELECT vend_name || ' (' || vend_country || ')'
  FROM Vendors
  ORDER BY vend_name;
  在MySQL中使用的语句为(利用concat()函数)
  SELECT Concat(vend_name, ' (', vend_country, ')') AS vend_title
  FROM Vendors
  ORDER BY vend_name;
  ③格式化数据（去掉空格用TRIM()函数，RTRIM()去掉字符串右边空格，LTRIM()去掉左边空格）
  SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' AS vend_title --SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')'
  FROM Vendors
  ORDER BY vend_name;

1. 插入数据

①插入完整的行

INSERT INTO Customers VALUES('1000000006', 'Toy Land', '123 Any Street',  'New York', 'NY', '11111', 'USA', NULL, NULL); 

②插入部分行

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', '11111', 'USA'); 

③插入检索出的数据

INSERT INTO Customers(cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country) 
SELECT cust_id
       , cust_contact
       , cust_email
       , cust_name
       , cust_address
       , cust_city
       , cust_state
       , cust_zip
       , cust_country 
FROM CustNew;

④把一个表复制到另一个表

SELECT * 
INTO CustCopy 
FROM Customers; 
CREATE TABLE CustCopy AS 
SELECT * FROM Customers; --将Customers表的整个内容复制到新表中

1. 更新数据

客户 1000000005现在有了电子邮件地址，因此他的记录需要更新，语句如下：

UPDATE Customers 
SET cust_email = 'kim@thetoystore.com' --SET命令用来将新值赋给被更新的列
WHERE cust_id = '1000000005'; 

1. 删除数据

DELETE FROM Customers 
WHERE cust_id = '1000000006'; 

1. 创建表

CREATE TABLE Products 
(     
    prod_id CHAR(10) NOT NULL,
    vend_id CHAR(10) NOT NULL,
    prod_name CHAR(254) NOT NULL,
    prod_price DECIMAL(8,2) NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 1,
    prod_desc VARCHAR(1000) NULL 
); 

默认值经常用于日期或时间戳列。例如，通过指定引用系统日期的函数或变量，将系统日期用作默认日期。MySQL用户指定DEFAULT CURRENT_DATE()，Oracle用户指定DEFAULT SYSDATE，而SQL Server用户指定DEFAULTGETDATE()。

DBMS函数/变量AccessNOW()DB2CURRENT_DATEMySQLCURRENT_DATE()OracleSYSDATEPostgreSQLCURRENT_DATESQL ServerGETDATE()SQLitedate('now')

1. 更新表

更新表定义，可以使用ALTER TABLE，在ALTER TABLE之后给出要更改的表名。

ALTER TABLE Vendors ADD vend_phone CHAR(20); 

1. 删除表

DROP TABLE CustCopy; 

之后会给出数据分析岗位笔试内容解答和LEETCODE习题解答，和在互联网大厂中常用的HIVE SQL语法和知识点进行介绍。