一些基本sql语句的使用

:idea: SQL语句编写规范
1. DMBS(数据库管理系统)
数据库管理系统（DBMS）是一组软件，主要是实现对共享数据有效的组织、管理和存取。
2. ＳＱＬ基本概念
SQL语言由命令、子句、运算和集合函数等构成。在SQL中，数据定义语言DDL（用来建立及定义数据表、字段以及索引等数据库结构）包含的命令有CREATE、DROP、ALTER；数据操纵语言DML（用来提供数据的查询、排序以及筛选数据等功能）包含的命令有SELECT、INSERT、UPDATE、DELETE。
图例：

3. DML语句语法
 查询（SELECT）
语法：SELECT [ALL|DISTINCT] <目标列表达式> [AS 列名]
[,<目标列表达式> [AS 列名] ...] FROM <表名> [，<表名>…]
[WHERE <条件表达式> [AND|OR <条件表达式>...]
[GROUP BY 列名 [HAVING <条件表达式>]]
[ORDER BY 列名 [ASC | DESC]]
解释：[ALL|DISTINCT] ALL：全部； DISTINCT：不包括重复行
<目标列表达式> 对字段可使用AVG、COUNT、SUM、MIN、MAX、运算符等
<条件表达式> 查询条件

比较 =、>,<,>=,<=,!=,<>,
确定范围 BETWEEN AND、NOT BETWEEN AND
确定集合 IN、NOT IN
字符匹配 LIKE（“%”匹配任何长度，“_”匹配一个字符）、NOT LIKE
空值 IS NULL、IS NOT NULL
子查询 ANY、ALL、EXISTS
集合查询 UNION（并）、INTERSECT（交）、MINUS（差）
多重条件 AND、OR、NOT
<GROUP BY 列名> 对查询结果分组
[HAVING <条件表达式>] 分组筛选条件
[ORDER BY 列名 [ASC | DESC]] 对查询结果排序；ASC：升序 DESC：降序
查询DEMO
 插入记录（INSERT）
语法：INSERT INTO <表名> [（<字段名1> [,<字段名2>, ...])] VALUES (<常量1> [,<常量2>, ...])
语法：INSERT INTO <表名> [（<字段名1> [,<字段名2>, ...])] 子查询
插入DEMO
 更新记录（UPDATE）
语法：UPDATE 〈表名〉
SET 列名1 = 常量表达式1[,列名2 = 常量表达式2 ...]
WHERE <条件表达式> [AND|OR <条件表达式>...]
更新DEMO

 删除记录（DELETE）
语法：DELETE FROM〈表名〉[WHERE <条件表达式> [AND|OR <条件表达式>...]]
删除DEMO

4. DML语句的应用场景
表名：student
表结构：
id(int4) name(varchar) age(int2)
1 John 20
2 Mary 18

 查询表中数据
例：SELECT id,name,age FROM student
解释：在查询表数据时，不能写SELECT * FROM 表名，一定要以字段的名字的方式出现，需要多少字段，就写多少字段。SELECT * 效率低下。
 按条件查询表中数据
例：SELECT id,name,age FROM student WHERE id = 1 AND name = ‘John’
解释：查询语句后跟WHERE关键字,然后写要查询的字段和该字段的值，如果查询条件为多个时，字段和字段之间要用AND关键字来拼接。
 分组查询表中数据
例：SELECT id FROM student GROUP BY id
解释：按表中字段id分组，显示id字段中的值，分组列id一定要出现在查询列中。
 分组查询表中数据并过滤
例：SELECT id,COUNT(age) FROM student GROUP BY id HAVING COUNT(age)>18
解释：查询的字段为，id，统计该id分组下的所有的age字段的数字总和，并过滤掉age字段总和大于18的数据。HAVING关键字必须要先有GROUP 关键字出现，然后才能写HAVING。
 对表中查询的数据进行排序
例1：SELECT id,name,age FROM student ORDER BY name ASC (升序)
例2：SELECT id,name,age FROM student ORDER BY name DESC(降序)
解释：第一个例子是查询student表中数据，并按name字段进行升序操作。第二个例子是查询student表中数据，并按name字段进行降序操作。
 查询表中数据，并过滤重复行
例：SELECT DISTINCT id FROM student
解释：查询student表中所有的id字段的数据，并过滤掉id 重复的数据
 查询表中数据，并按一定的范围过滤
例：SELECT id,name,age FROM student WHERE age BETWEEN 18 AND 20
解释：查询student表中所有字段，age字段的限定范围为大于等于18岁并且小于等于20岁的数据。该语句等同于SELECT id,name,age FROM student WHERE age >=18 AND age<=20, not BETWEEN…AND 则过滤掉不在此范围内的数据。
 查询表中数据，并按字段值进行匹配
例1：SELECT id,name,age FROM student WHERE name LIKE ‘J%’ (以英文字母J开头的数据)
例2：SELECT id,name,age FROM student WHERE name LIKE ‘%a%’ (中间只要出现英文字母a的数据)
例3：SELECT id,name,age FROM student WHERE name LIKE ‘%y’ (结尾为y的英文字母的数据)
解释：例1查询student表中所有数据，并且条件为name字段中的值是以英文字母J开头的数据。例2查询student表中所有数据，并且条件为name字段中的值是以中间只要出现英文字母a的数据。例3查询student表中所有数据，并且条件为name字段中的值是以结尾为y的英文字母的数据。
 查询表中数据为null的
例：SELECT id,name,age FROM student WHERE name IS NULL
解释：查询student表中所有字段，并且条件为name字段为null，not null表示要查出的数据是不为空。
注意：数据null和数据的字符串’’,是2个概念，前者是没有数据，后者是有数据但是数据为字符串’’。
 数据子查询
例1：SELECT id,name,age FROM student WHERE id = (SELECT id FROM student WHERE name = ‘John’)
例2：SELECT id,name,age FROM student AS s WHERE EXISTS (SELECT 0 FROM student AS s1 WHERE s.id = s1.id AND s1.name = ‘John’)
解释：例1查询student表中数据，并且是通过小括号查询的ID号来对应。例2是通过EXISTS 关键字来查询student中数据，如果小括号内的表达式为true,那么返回student表中数据，如果为false则查询不出数据。
注意：系统要求进行SQL优化，对效率比较低的SQL进行优化，使其运行效率更高，其中要求对SQL中的部分in/not in修改为EXISTS/not EXISTS
 集合查询
例1：(SELECT * FROM tableA) UNION (SELECT * FROM tableB) (并集)
例2：(SELECT * FROM tableA) EXCEPT (SELECT * FROM tableB) (差集)
例3：(SELECT * FROM tableA) INTERSECT(SELECT * FROM tableB) (交集)
解释：例1UNION 运算符通过组合其他两个结果表(例如 tableA 和 tableB)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL)，不消除重复行。两种情况下，派生表的每一行不是来自 tableA就是来自 tableB。
例2 EXCEPT运算符通过包括所有在 tableA中但不在 tableB中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT一起使用时 (EXCEPT ALL)，不消除重复行。
例3 INTERSECT运算符通过只包括 tableA和 tableB中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT一起使用时 (INTERSECT ALL)，不消除重复行。
 多重条件组合查询
例1：SELECT id,name,age FROM student WHERE id = 1 AND name = ‘John’ (并且)
例2：SELECT id,name,age FROM student WHERE id = 1 OR name = ‘Mary’ (或者)
例3：SELECT id,name,age FROM student WHERE id <> 1 (不等于)
解释：例1查询student表数据，并且要满足条件id = 1 并且 name = ‘John’的记录。例2 查询student 表数据，并且要满足条件id = 1 或者 name = ‘Mary’的记录。例3 查询student表数据，并且要满足条件id 不等于1的条件。
 插入数据库单条记录
例1：INSERT INTO student (id,name,age) values (3,’Terry’,22)
解释：向数据库插入数据，如果是所有字段的话可以不写列名，但是为了执行效率优化，规定必须写上列名，values 关键字后跟插入数据库中的值，排列顺序和前面所写的列名相对应。Id 对应值是3,name对应值是Terry，age对应值是22
例2：INSERT INTO student SELECT id,name,age FROM student WHERE id = 1
解释：向数据库插入数据，所插数据的值为SELECT语句所查询出的值。
 向数据库连续插入多条记录
例：INSERT INTO student(id,name,age) SELECT 11, 'abc',17
UNION SELECT 12, 'bcd',18
UNION SELECT 13, 'cde',19
解释：连续向数据库插入数据，SELECT 关键字 为第一条数据，第二条记录开始为UNION开头，后续格式一样。
 更新表中记录
例：UPDATE student SET id = ‘33’ WHERE name = ‘John’
解释：更新数据库中记录，把name字段值为John的数据的id 字段值改成33
注意：更新语句编写后，一定要加入WHERE条件,否则会更新表中所有记录。
 删除表中记录
例：DELETE FROM student WHERE name = ‘John’
解释：删除数据库中记录，把name字段值为John的数据从数据库中删除
注意：删除语句编写后，一定要加入WHERE条件,否则会删除表中所有记录。
5. 数据库函数
 求和函数——SUM()
例：SELECT SUM(age) FROM student
解释：求和函数SUM( )用于对数据求和，返回选取结果集中所有值的总和。SUM()函数只能作用于数值型数据，即列中的数据必须是数值型的。该SQL语句返回student表中所有age数的总和。
 计数函数——COUNT()
例1：SELECT COUNT(*) FROM student
例2：SELECT COUNT(id) FROM student
解释：COUNT()函数用来计算表中记录的个数或者列中值的个数，计算内容由SELECT语句指定。使用COUNT函数时，必须指定一个列的名称或者使用星号，星号表示计算一个表中的所有记录。COUNT(*)，计算表中行的总数，即使表中行的数据为NULL，也被计入在内。COUNT(column)，计算column列包含的行的数目，如果该列中某行数据为NULL，则该行不计入统计总数。
 最大/最小值函数—MAX()/MIN()
例1：SELECT MAX(id) FROM student
例2：SELECT MIN(id) FROM student
解释：当需要了解一列中的最大值时，可以使用MAX()函数；同样，当需要了解一列中的最小值时，可以使用MIN()函数。例1获得student表中最大的id字段数据。例2获得student表中最小的id字段数据。
 均值函数——AVG()
例：SELECT AVG(age) FROM student
解释：AVG()函数的执行过程实际上是将一列中的值加起来，再将其和除以非NULL值的数目。所以，与SUM( )函数一样，AVG()函数只能作用于数值型数据，即列中的数据必须是数值型的。
 COALESCE（）函数
例：SELECT id,name,age FROM student WHERE COALESCE(name,’’) LIKE ‘J%’
解释：coalesce函数是一个转换函数，能把括号内的字段值为null的转换成所需要的数据。该例子就是把name字段为null的数据转换成字符串空。
6. 多表连接
 LEFT JOIN（左链接）
例：SELECT * FROM A LEFT JOIN B ON A.aid = B.bid
解释：LEFT JOIN是以A表的记录为基础的,A可以看成左表,B可以看成右表,LEFT JOIN是以左表为准的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).
B表记录不足的地方均为NULL。
 RIGHT JOIN（右链接）
例：SELECT * FROM A RIGHT JOIN B ON A.aid = B.bid
解释：和LEFT JOIN的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充。
 INNER JOIN (内链接)
例：SELECT * FROM A INNER JOIN B ON A.aid = B.bid
解释：INNER JOIN并不以谁为基础,它只显示符合条件的记录. 还有就是INNER JOIN 可以结合WHERE语句来使用。如SELECT * FROM A INNER JOIN B ON A.aid = B.bid WHERE A.name <> ‘’
7. 表和列的别名
例：SELECT id AS id1,name AS name1,age AS age1 FROM student AS s
解释：别名是为了方便在多表查询中，指定出什么别名表中的哪个字段。同时也方便在使用数据库内置函数中，数据库使用默认列名的情况出现。
8. 练习
建立表stuinfo 并模拟数据，按上述的DML操作教程，来练习SQL脚本编写。

9.查询的基础上面在进行一次查询...
SELECT
count(temp_tbl.*)
FROM
(SELECT
substr(employee_code,1,10)
FROM
employee
WHERE
company_leave_date is null
GROUP BY
substr(employee_code,1,10)
ORDER BY
substr(employee_code,1,10)
) as temp_tbl

10.关于case when then else 基本结构:
1.以case开头以end结尾。
2.case后面接字段when后面接条件。
3.then后面接出了else条件外显示的结果。
example：
select
（case name when “zhou” than sex else “女” end） as nm
from
student
table student：
name sex
1 zhow 男
2 zhou 女
抽出的结果是than后面字段的结果 ：“男”！