sql笔记（一）简单数据操作

插入

INSERT INTO tbl(col1,col2...) VALUES(val1,val2...),(val1,val2...),(val1,val2...);

更新

-- 这会把一整列的数据都更新，除非配合使用WHERE子句
UPDATE tbl SET field=val, field=val;

删除

-- 这会删除表格中的所有数据，除非配合WHERE子句
DELETE FROM tbl;

查询

SELECT col FROM tbl;

DISTINCT

去除查询结果中相同的行（每个字段都相同才可以）

SELECT DISTINCT col FROM tbl;

别名

SELECT语句中可以给字段或者表格取别名，使用关键字AS，不过这个AS是可选的，在【原名】和【别名】之间直接用空格隔开，效果一样

/*
1. 字段的别名必须是一个字符串（但是定义的时候不加引号也是可以的），作用于查询结果，不能代码中，也就是说查询结果中id列的列名会显示为【序号】，而不是【id】，但是在该SELECT语句后面的子句中不能用【序号】来代替【id】
2. 表格的别名是作用于代码的，后续子句中可以用【别名】代替【原名】，比如示例中用p代替people
*/
SELECT id AS '序号' FROM people as p where p.id=1;

LIMIT（MySQL & sqlite）

限制SELECT查询输出的行数
用LIMIT可以方便的输出表格的前几行，但是要输出倒数几行的话，目前我只知道需要表格有一个id列，然后根据id列倒序排列再用LIMIT子句

-- 读取n行数据
SELECT col FROM tbl LIMIT n;
-- 从第m行开始，读取n行数据
SELECT col FROM tbl LIMIT m,n;

OFFSET

设置SELECT语句从第几行开始查询
sql表格的行数的下标也是从0开始的

-- 这两行代码效果相同
SELECT col FROM tbl LIMIT n,m;
SELECT col FROM tbl LIMIT m OFFSET n;

rownum（oracle）

rownum是oracle中的一个伪列，表示行号，和mysql的offset不同，他是从1开始的。
所以如果希望查询表格的前十行，只需要select * from tbl where rownum <= 10;即可。

ORDER BY ... [DESC]

SELECT * FROM tbl ORDER BY col [DESC];

-- 根据多个条件排列结果，当前一个条件相等时，再使用后一个条件
-- 如果都过条件都是升序，那可以不加[DESC/ASC]，否则多个条件都加上[DESC/ASC]
SELECT * FROM tbl ORDER BY col1 [DESC/ASC], col2 [DESC/ASC];

oracle还支持nulls first/last关键字，自由选择null值排在最前面还是最后面。
select * from tbl order by col desc nulls first;

UNION 和 UNION ALL

UNION和UNION ALL都是纵向连接多条SELECT语句的结果。
举个例子，以下为表1，表2，以及用UNION连接的查询结果。

SELECT col1 FROM tbl1 UNION SELECT col2 FROM tbl2;

id id id
1 2 1
2

所以UNION和UNION ALL对多条SELECT语句的查询结果有如下要求：

1. 必须有相同数量的列
2. 对应列（第一列对应第一列，第二列对应第二列）的列名和数据类型必须一样

UNION和UNION ALL的区别是UNION会删除结果中相同的行，而UNION ALL不会。

WHERE

... WHERE condition AND/OR condition;

1. 和其他语言不同的是，数值大小判断中的【等于】是【=】，不是【==】
2. 一般用来比较数值大小，对于字符串，一般只比较是否相等，WHERE field=val;

LIKE

用在WHERE子句中，用作字符串的匹配，可以使用如下通配符：
%，表示任意字符。
_，表示任意1个字符。
[charlist]，该字符序列中的任意一个字符。
[^charlist]，不在该字符序列中的任意一个字符。

... WHERE name LIKE '%TT%';

REGEXP（MySQL）

正则表达式，用法和LIKE一样。

[NOT] BETWEEN AND

mysql中BETWEEN...AND...包括上下边界。
BETWEEN...AND...可以作用与数字，字符串，时间的话应该也没问题吧，二进制内容估计不行。

SELECT * FROM tbl WHERE col BETWEEN val1 AND val2;

[NOT] EXISTS

SELECT * from tbl1 WHERE [NOT] EXISTS (SELECT * FROM tbl2 WHRER ...)

EXISTS连接的多个SELECT类似于嵌套for循环，用java写的话，大概就像这样：

List result=new ArrayList();
for(int i:tbl1){
    for(int j:tbl2){
        if(i==j){
            result.add(i);
        }
    }
}

1. EXISTS并不关心子SELECT中返回的数据，只关心有没有数据返回，有则为真，否则为假
2. EXISTS中的内外两条SELECT必须查询不同的表格，且子SELECT的WHERE语句中必须对两张表格中字段做比较判断，否则EXISTS将永远返回真，或者永远返回假，那EXISTS就没意义了
3. 第二条SELECT中用到的tbl1中的字段不必存在于第一条SELECT中，只需要在tbl1中即可，比如SELECT col1 from tbl1 WHERE EXISTS (SELECT * FROM tbl2 WHRER tbl2.col1=tbl1.col2)

对于上面说的第二点，举几个例子

/*
正确的做法
第二条SELECT中只查询了tbl2，但是依旧使用了tbl1.col，这个tbl1.col就是前一条SELECT传进来的
也就是说EXISTS中的SELECT相当于是内层for循环
*/
SELECT * from tbl1 WHERE EXISTS (SELECT * FROM tbl2 WHERE tb1.col=tb2.col);

/*
错误的做法
第二条SELECT中没有tbl1的数据，所以EXISITS中的SELECT永远返回真，或者永远返回假
EXISTS无法起到筛选数据的作用
*/
SELECT * from tbl1 WHERE EXISTS (SELECT * FROM tbl2 WHERE tbl2.col > var);

/*
错误的做法
第二条SELECT没用到tbl2中的数据，能够筛选tbl1的数据，但是EXISTS语句在这里没有意义
直接一个SELECT+WHERE就可以了
*/
SELECT * from tbl1 WHERE EXISTS (SELECT * FROM tbl2 WHERE tbl1.col > var);

是否需要用EXISTS

从结果来看，EXISTS中的第二个SELECT中查询的表格只是用作对比，查询结果只会包含第一条SELECT查询的表格中的数据。
但是不使用EXISTS也可以做到。

SELECT tbl1.* FROM tbl1,tbl2 WHERE tbl1.col=tbl2.col;

[NOT] IN

IN的作用是给定一个元素集合，只要列中的元素存在于这个元素集合中，就返回真。
这个元素集合可以直接给定，也可以使用SELECT子句的查询结果，但是，SELECT的查询结果必须是单列的。

SELECT * FROM tbl1 WHERE tbl1.col IN (var1,var2,var3...);
SELECT * FROM tbl1 WHERE tbl1.col IN (SELECT col FROM tb2);

IN只能判断某一个字段是否在查询结果中，如果希望判断某几个字段，可以用括号。
(col1,col2) IN (SELECT ...)

IN与EXISTS效率比较

子查询表大时，则用EXISTS；大小相似时都可以；子查询表小时，用IN。
NOT EXISTS效率一定比NOT IN好。