SQL心得

介绍

找工作时出了出汗,还在Hackerank上面刷题。现在估计都忘了…

待解决

  • 拿到某一列最大值的那一行数据

例题解答

  1. 首字母是元音
    • WHERE LEFT(CITY,1) IN ('a','e','i','o','u');
    • WHERE CITY REGEXP '^[AEIOU]'

打印小星星

set @number = 21; /*这一步应该是设定变量?为什么不用Declare,省事!*/
select repeat('* ', @number := @number - 1) from information_schema.tables/*内置table可供遍历*/
LIMIT 20;
DECLARE @COUNTER INT = 20;
WHILE @COUNTER >= 0
BEGIN
    PRINT REPLICATE('* ', @COUNTER)
    SET @COUNTER = @COUNTER - 1
END

基础知识

  • Statement always end in a semi-colon;
  • 注释一般用/**/
  • Command 一般都用大写来表示
  • character在这里面被称为TEXT:(四种常见格式:INTEGER, TEXT, DATE, REAL)
  • missing value 用 NULL 表示
  • Line breaks don’t mean anything specific in SQL. We could write this entire query in one line, and it would run just fine.
  • AS is a keyword in SQL that allows you to rename a column or table using an alias. 跟sas的label一样,只改变输出不改变原数据
  • DISTINCT is used to return unique values in the output. It filters out all duplicate values in the specified column(s).
  • 判断等号的方式就是一个等于号
  • FROM some_table a 可以用a来指代这个table
  • 如果没有数据的时候要返回NULL 可以用双重SELECT 也可以把UNION SELECT NULL放在语句中
  • DROP TABLE xxx 彻底删除数据库
  • IN/NOT IN 判断属于情况
  • DELETE可以直接删除某一行观测值
  • 关于时间的函数:TO_DAYS(wt1.DATE) return the number of days between from year 0 to date DATE, subdate(w1.Date, k)可以把日期往前调k天
  • mod(id,2)=0 偶数 还可以用id % 2 =0

表中的任何列都可以作为主键,只要它满足以下条件:

  • 任意两行都不具有相同的主键值;
  • 每一行都必须具有一个主键值(主键列不允许NULL值);
  • 主键列中的值不允许修改或更新;
  • 主键值不能重用(如果某行从表中删除,它的主键不能赋给以后的新行)。

注释用#或者/**/

不等于可以用<>

Between and 是包含一个闭集

在SELECT语句中,子查询总是从内向外处理。在处理上面的SELECT语句时,DBMS实际上执行了两个操作。

作为子查询的SELECT语句只能查询单个列。企图检索多个列将返回错误。

if(判断,如果真,如果假)

ORDER BY RIGHT(NAME,3),ID; 可以根据NAME的最后三个字符来排序

定义一个变量 DECLARE @variable_name variable_type = xxx,赋值可以单独用SET @variable_name = xxx 来操作。不过我发现有时候不DECLARE直接SET也可以。

information_schema.tables 是内置的一个table,有61行

Manipulation

  • CREATE TABLE celebs(
    id INTEGER,
    name TEXT,
    age INTEGER
    ); 创建table 可以在数据类型后面跟上NULL/NOT NULL 来要求是否允许缺失值,默认为NULL。可在接着在后面给出 DEFAULT xxx 来给出默认值。可以直接标注primary key
  • ALTER TABLE Vendors ADD CONSTRAINT PRIMARY KEY (vend_id); 也可以增添主键
  • 外键是表中的一列,其值必须列在另一表的主键中。外键是保证引用完整性的极其重要部分。
  • 只有不允许NULL值的列可作为主键,允许NULL值的列不能作为唯一标识。
  • INSERT INTO celebs (id, name, age) VALUES (2, ‘Beyonce Knowles’, 33);
    插入新数据。insert后面没有分号,每插入一行数据就要用一个insert?并且还可以在括号中列出来想插入哪些列的变量,没被填写的那些除非是auto-increment否则会被NULL代替。
  • 如果表的定义允许,则可以在INSERT操作中省略某些列。省略的列必须满足以下某个条件。1. 该列定义为允许NULL值(无值或空值)。2. 在表定义中给出默认值。这表示如果不给出值,将使用默认值。如果对表中不允许NULL值且没有默认值的列不给出值,DBMS将产生错误消息,并且相应的行插入不成功。
  • SELECT * INTO CustCopy FROM Customers; 可以实现复制表的功能。不过不管从多少个表中检索数据,数据都只能插入到一个表中。
  • UPDATE celebs
    SET age = 22
    WHERE id = 1; 将celebs数据中id=1的数据,age改为22 这个WHERE很关键,如果没有的话,【所有数据都会被改变】
  • 要删除某个列的值,可用UPDATE设置它为NULL(假如表定义允许NULL值)。
  • ALTER TABLE celebs ADD COLUMN
    twitter_handle TEXT; 增加新的一列
  • DELETE FROM celebs WHERE
    twitter_handle IS NULL; 删除推特账号是missing的数据。删除一行/多行
  • 如果要从Products表中删除一个产品,而这个产品用在OrderItems的已有订单中,那么DELETE语句将抛出错误并中止。这是总要定义外键的另一个理由。
  • 在UPDATE或DELETE语句使用WHERE子句前,应该先用SELECT进行测试,保证它过滤的是正确的记录,以防编写的WHERE子句不正确。
  • DROP TABLE 彻底删除一个表,而不只是删除其内容

唯一约束用来保证一列(或一组列)中的数据是唯一的。它们类似于主键,但存在以下重要区别。

  • 表可包含多个唯一约束,但每个表只允许一个主键。
  • 唯一约束列可包含NULL值。
  • 唯一约束列可修改或更新。
  • 唯一约束列的值可重复使用。
  • 与主键不一样,唯一约束不能用来定义外键。

Select

SELECT * FROM celebs; select会生成一个新的 result table

  • SELECT column1,column2, … FROM table; 【用逗号连接】
  • WHERE clause filters the result set to only include rows where the following condition is true.
  • IN操作符一般比一组OR操作符执行得更快。IN的最大优点是可以包含其他SELECT语句,能够更动态地建立WHERE子句。
  • LIKE is a special operator used with the WHERE clause to search for a specific pattern in a column. 像是在按照模式匹配字符串,不分大小写。用_下划线来指代任何单个字符,%代表任何单/多个字符
  • 通配符%看起来像是可以匹配任何东西,但有个例外,这就是NULL。子句WHERE prod_name LIKE '%'不会匹配产品名称为NULL的行。
  • 方括号([])通配符用来指定一个字符集,它必须匹配指定位置(通配符的位置)的一个字符。此通配符可以用前缀字符^(脱字号)来否定。
  • 通过IS NULL/IS NOT NULL 来判断是否为缺失值(大于小于等于号之类的不行)
  • BETWEEN 如果是两个字符的话不包含第二个字符,如果是两个数字则包含第二个数字
  • 可以用AND/OR 来连接多个condition
  • ORDER BY … DESC/ASC 降序或者升序排列(注意DESC放在列名的后面,DESCENDING也可以)。在指定一条ORDER BY子句时,应该保证它是SELECT语句中最后一条子句。如果它不是最后的子句,将会出现错误消息。通常,ORDER BY子句中使用的列将是为显示而选择的列。但是,实际上并不一定要这样,用非检索的列排序数据是完全合法的。
  • 如果有必要,可以混合匹配使用实际列名和相对列位置(1,2,3,…)。
  • 在同时使用ORDER BY和WHERE子句时,应该让ORDER BY位于WHERE之后,否则将会产生错误
  • LIMIT is a clause that lets you specify the maximum number of rows the result set will have. This saves space on our screen and makes our queries run faster.LIMIT always goes at the very end of the query. Also, it is not supported in all SQL databases.
  • Limit x,y = limit y offset x 表示的是从第x+1行开始,返回y个数据
  • SELECT name,
    CASE
    WHEN imdb_rating > 7 THEN ‘Good’
    WHEN imdb_rating > 5 THEN ‘Okay’
    ELSE ‘Bad’
    END AS ‘Rating’ #重命名
    FROM movies; CASE-END判断语句
  • COUNT(1) 可以辅助分组计数
SELECT * FROM Customers
WHERE Country IN ('Germany','UK'); #IN 可以从多个元素中找东西

要想从一个表中检索多个列,列名之间必须以逗号分隔,但最后一个列名后不加。

使用通配符有一个大优点。由于不明确指定列名(因为星号检索每一列),所以能检索出名字未知的列。

不要过度使用通配符。如果其他操作符能达到相同的目的,应该使用其他操作符。

在确实需要使用通配符时,也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处,搜索起来是最慢的。

SELECT TOP 50 PERCENT * FROM Customers; #可以选出前50%的数据

计算字段&常用函数

TRIM/RTRIM/LTRIM 去掉左右的空格

‘+’ 或者|| 可以用来拼接字符

虽然SELECT通常用于从表中检索数据,但是省略了FROM子句后就是简单地访问和处 理表达式,例如SELECT 3 * 2;将返回6,SELECT Trim(’ abc ');将返回abc,SELECT Now();使用Now()函数返回当前日期和时间。现在你明 白了,可以根据需要使用SELECT语句进行检验。

LEFT/RIGHT 返回最左(右)面的字符

SOUNDEX 返回字符串的SOUNDEX值,匹配发音

PI()返回圆周率

GRESTEST 返回最大值

Aggregate Functions

  • SELECT COUNT(*) FROM Table; 返回一共有多少行
  • SUM/MAX/MIN/AVG/ROUND
  • AVG()只能用来确定特定数值列的平均值,而且列名必须作为函数参数给出。为了获得多个列的平均值,必须使用多个AVG()函数。AVG()函数忽略列值为NULL的行。
  • 在用于文本数据时,MAX()返回按该列排序后的最后一行。MAX()函数忽略列值为NULL的行。
  • 如果指定列名,则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)。类似地,DISTINCT必须使用列名,不能用于计算或表达式。
  • GROUP BY 按照某一指标分类。后面可以接数字,表示按照SELECT语句中第几个column来分组
  • When we want to limit the results of a query based on an aggregate property, use HAVING. HAVING statement always comes after GROUP BY, but before ORDER BY and LIMIT. 事实上,目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是,WHERE过滤行,而HAVING过滤分组。
  • 在指定别名以包含某个聚集函数的结果时,不应该使用表中实际的列名。虽然这样做也算合法,但许多SQL实现不支持,可能会产生模糊的 错误消息。

GROUP BY子句可以包含任意数目的列,因而可以对分组进行嵌套,更细致地进行数据分组。

GROUP BY子句中列出的每一列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在SELECT中使用表达式,则必须在GROUP BY子 句中指定相同的表达式。不能使用别名。

大多数SQL实现不允许GROUP BY列带有长度可变的数据类型

如果分组列中包含具有NULL值的行,则NULL将作为一个分组返回。如果列中有多行NULL值,它们将分为一组。

GROUP BY子句必须出现在WHERE子句之后,ORDER BY子句之前。

一般在使用GROUP BY子句时,应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法。千万不要仅依赖GROUP BY排序数据。

联结

由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。(直接SELECT FROM x1, x2, x3;)有时,返回笛卡儿积的联结,也称叉联结(cross join)。

目前为止使用的联结称为等值联结(equijoin),它基于两个表之间的相等测试。这种联结也称为内联结(inner join)。

SQL允许对表起别名,来允许在一条SELECT语句中多次使用相同的表。(自联结)

OUTER JOIN 允许左边的表的数据在右边的表中没有出现过(这是 LEFT OUTER JOIN的定义,如果是RIGHT OUTER JOIN 就反过来)

还存在另一种外联结,就是全外联结(full outer join),它检索两个表中的所有行并关联那些可以关联的行。与左外联结或右外联结包含一个表 的不关联的行不同,全外联结包含两个表的不关联的行。(MySQL不支持)

组合查询

使用UNION很简单,所要做的只是给出每条SELECT语句,在各条语句之间放上关键字UNION。如果组合四条SELECT语句,将要使用三个UNION关键字

UNION中的每个查询必须包含相同的列、表达式或聚集函数(不过,各个列不需要以相同的次序列出)。

列数据类型必须兼容:类型不必完全相同,但必须是DBMS可以隐含转换的类型(例如,不同的数值类型或不同的日期类型)。

UNION从查询结果集中自动去除了重复的行;换句话说,它的行为与一条SELECT语句中使用多个WHERE子句条件一样。因为Indiana州有一个 Fun4All单位,所以两条SELECT语句都返回该行。使用UNION时,重复的行会被自动取消。这是UNION的默认行为,如果愿意也可以改变它。事实上,如果想返回所有的匹配行,可使用UNION ALL而不是UNION。

Multiple Tables

  • Combining Tables:
    SELECT orders.order_id,
    customers.customer_name
    FROM orders
    JOIN customers
    ON orders.customer_id = customers.customer_id
  • 默认的join是 inner-join,如果某一行对应的行数据不匹配的话,就把它删掉。left-join 保全先select的table中所有的信息,将后select中没有的信息用missing来填
  • CROSS JOIN 返回所有可能种排列组合
  • UNION 相当于r里面的rbind,直接将两个column一样的table上下组合起来
  • WITH previous_results AS (
    SELECT …
    )
    SELECT *
    FROM previous_results
    JOIN other_table
    ON … = …; WITH可以将运算中生成的新table一起绑起来

##Constrains

They can be used to tell the database to reject inserted data that does not adhere to a certain restriction.

  • CREATE TABLE celebs (

    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE,
    date_of_birth TEXT NOT NULL,
    date_of_death TEXT DEFAULT ‘Not Applicable’,
    );

  • PRIMARY KEY: columns can be used to uniquely identify the row.

  • UNIQUE columns have a different value for every row. This is similar to PRIMARY KEY except a table can have many different UNIQUE columns

  • NOT NULL columns must have a value.

  • DEFAULT columns take an additional argument that will be the assumed value for an inserted row if the new row does not specify a value for that column.

Table Transformation

While working with databases, we often need to transform data from one format to achieve a desired result

Subqueries

A non-correlated subquery is a subquery that can be run independently of the outer query and can be used to complete a multi-step transformation. 选择部分只出现在FROM里面

  • SELECT * FROM flights
    WHERE origin in (
    SELECT code
    FROM airports
    WHERE elevation < 2000
    ); 在一个table中以另一个table为选择条件,找出子表
  • SELECT a.dep_month,
    a.dep_day_of_week,
    AVG(a.flight_distance) AS average_distance
    FROM (
    SELECT dep_month,
    dep_day_of_week,
    dep_date,
    SUM(distance) AS flight_distance
    FROM flights
    GROUP BY 1,2,3
    ) a
    GROUP BY 1,2
    ORDER BY 1,2; 在一个表内做一些选择与统计量计算

In a correlated subquery, the subquery can not be run independently of the outer query(选择部分出现在SELECT里面). The order of operations is important in a correlated subquery:

  1. A row is processed in the outer query.
  2. Then, for that particular row in the outer query, the subquery is executed.
  • SELECT id FROM flights AS f
    WHERE distance<(
    SELECT AVG(distance)
    FROM flights
    WHERE carrier = f.carrier); 这个AS f 很关键,要不然无法和下面这个select的flights区分开来

Set Operation

Merge 2 rows called a join, merge 2 columns called a union.

  • 若是想让UNION包含重复的元素,需要在后面加上关键词 ALL
  • INTERSECT 连接两个SELECT,只包含相同的行
  • EXCEPT 连接两个SELECT,只包含第一个有第二个没有的行

Conditional Aggregates

  • COUNT(CASE WHEN ) 可以返回特定的求和
  • SELECT origin,
    SUM(a.distance) AS total_flight_distance,
    SUM(CASE WHEN carrier = ‘DL’ THEN distance ELSE 0 END) as total_delta_flight_distance
    FROM flights AS a
    GROUP BY origin; SUM的求和要放在Then后面

Date, Time and String

Date

Dates are often written in the following format(同时也是函数名)

  1. Date: YYYY-MM-DD
  2. Datetime or Timestamp: YYYY-MM-DD hh:mm:ss
  • DATETIME(time1, '+3 hours', '40 minutes', '2 days');
    

    Would return a time 3 hours, 20 minutes, and 2 days after time1.

  • YEAR(xxx)来返回年份

  • 获取系统日期:CURRENT_DATE()

Number

  • SELECT (number1 + number2) 直接四则运算
  • SELECT CAST(number1 AS REAL) 转换格式
  • SELECT ROUND(number, precision) 取整
  • MAX/MIN

Strings

  • A || ‘ ’ || B 用空格连接A和B
  • REPLACE(string,from_string,to_string) 字符串替换

视图

视图仅仅是用来查看存储在别处数据的一种设施。视图本身不包含数据,因此返回的数据是从其他表中检索出来的。在添加 或更改这些表中的数据时,视图将返回改变过的数据。

为什么要用视图:

  • 重用SQL语句。
  • 简化复杂的SQL操作。
  • 在编写查询后,可以方便地重用它而不必知道其基本查询细节。
  • 使用表的一部分而不是整个表。
  • 保护数据。可以授予用户访问表的特定部分的权限,而不是整个表的访问权限。
  • 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

视图用CREATE VIEW语句来创建。利用视图,可一次性编写基础的SQL,然后根据需要多次使用。

视图常见用途:

  1. 利用视图简化复杂的联结
  2. 用视图重新格式化检索出的数据
  3. 用视图过滤不想要的数据
  4. 使用视图与计算字段

存储过程

存储过程就是为以后使用而保存的一条或多条SQL语句。可将其视为批文件,虽然它们的作用不仅限于批处理。

有点难 不知道这个具体是干嘛的

管理事务处理

使用事务处理(transaction processing),通过确保成批的SQL操作要么完全执行,要么完全不执行,来维护数据库的完整性。如果没有错误发生,整组语句提交给(写到)数据库表;如果发生错误,则进行回退(撤销),将数据库恢复到某个已知且安全的状态。

在使用事务处理时,有几个反复出现的关键词。下面是关于事务处理需要知道的几个术语:

  • 事务(transaction)指一组SQL语句;
  • 回退(rollback)指撤销指定SQL语句的过程;
  • 提交(commit)指将未存储的SQL语句结果写入数据库表;
  • 保留点(savepoint)指事务处理中设置的临时占位符(placeholder),可以对它发布回退(与回退整个事务处理不同)。

事务处理用来管理INSERT、UPDATE和DELETE语句。不能回退SELECT语句(回退SELECT语句也没有必要),也不能回退CREATE或DROP操作。事 务处理中可以使用这些语句,但进行回退时,这些操作也不撤销。

SQL的ROLLBACK命令用来回退(撤销)SQL语句

一般的SQL语句都是针对数据库表直接执行和编写的。这就是所谓的隐式提交(implicit commit),即提交(写或保存)操作是自动进行的。 在事务处理块中,提交不会隐式进行。不过,不同DBMS的做法有所不同。有的DBMS按隐式提交处理事务端,有的则不这样。 进行明确的提交,使用COMMIT语句。

要支持回退部分事务,必须在事务处理块中的合适位置放置占位符。这样,如果需要回退,可以回退到某个占位符。 在SQL中,这些占位符称为保留点。在MariaDB、MySQL和Oracle中创建占位符,可使用SAVEPOINT语句:

游标

有时,需要在检索出来的行中前进或后退一行或多行,这就是游标的用途所在。游标(cursor)是一个存储在DBMS服务器上的数据库查询, 它不是一条SELECT语句,而是被该语句检索出来的结果集。在存储了游标之后,应用程序可以根据需要滚动或浏览其中的数据。

不同的DBMS支持不同的游标选项和特性。常见的一些选项和特性如下。

  • 能够标记游标为只读,使数据能读取,但不能更新和删除。
  • 能控制可以执行的定向操作(向前、向后、第一、最后、绝对位置、相对位置等)。
  • 能标记某些列为可编辑的,某些列为不可编辑的。
  • 规定范围,使游标对创建它的特定请求(如存储过程)或对所有请求可访问。
  • 指示DBMS对检索出的数据(而不是指出表中活动数据)进行复制,使数据在游标打开和访问期间不变化。

本博客所有文章除特别声明外,均采用 CC BY-SA 4.0 协议 ,转载请注明出处!