SQL基本语法

1. select：选择

select * from table_name;
select distinct column_x  from table_name;  #distinct:去重
 #选top
select top number column_name(s) from table_name;  # top numer
select top percentage column_name(s) from table_name; # top percentage
select column_name(s) from table_name limit number; # limit
#min, max, avg, sum, count...
select min(column_x) from table_name;
select max(colums_x) from table_name;

2. where：筛选

select column_0, column_1, ... 
from table_name where condition;
# use like in condition
select column0, column1, ...
from table_name
where column_n like pattern; # '%':>=0 characters; '_':1 character
#in, between
where column_n in (...);
where column_n between value_1 and value2;

condition操作符：=, >, <, >=, <=, between, like, in, and, or, not

3. order by:排序

select column1, column2, ...
from table_name
order by column1, column2, ... asc; #或dsc

4. insert into：插入行

insert into table_name (column1, column2, column3, ...)
values  (value1, value2, value3, ...);

insert into table_name values  (value1, value2, value3, ...);

5. update：修改行value

update table set column1=value1, column2=value2, ... where condition; #where语句：optional

6.delete：删除部分行

delete from table_name where condition; 

where语句 optional，不选则为删除所有
7.as：重命名table或行

select column_x as col_x from table_name;
select column_x from table_name as tb;

8.join

• 一般join指inner join，交集；其他（left，right和full均为outer join）

• 各种join图示

  
  SQL-Join.jpg
• self join：通常用于同一个表不同行之间的关系

select column_x
from table_name tb_1, table_name tb_2
where condition;

9.union：用于合并两个或多个 SELECT 语句的结果集，默认去重，不去重用union all

select column_name(s) from table_name1
union
select column_name(s) from table_name2;

10.group by:根据一个或多个列对结果集进行分组

#eg: 得到不同国家对应的用户总数
select count(customer_id), country
from customers
group by country;

11.having：having和where均引导条件语句，但是区别是having是：在查询返回结果集以后对查询结果进行的过滤操作，支持聚合操作（avg，count等），where约束来自数据库的数据。

# 筛选平均工资高于3000的部门
select deparment, avg(salary) as average from salary_info 
group by deparment having average > 3000

12.exists: 在遍历每一条记录时，exists 后值为true，则将该条记录加入；否则不加入

select column(s) from table_name where exists(...) 

**exists和in的区别 https://www.jianshu.com/p/f212527d76ff **

13. any, all:引导一个范围，限定operator比较的范围

select column(s) from table_name
where column operator all (select column_name from table_name where condition);  
#operator：>, <, =, ...

14.select into:存储为新的表

select column(s) into new_tabbe [in externalDB]
from old_table where condition;

15.case:类似c语言的case

case
    when condition_0 then res_0
    when condition_1 then res_1
    ...
    (else res_1)
end

16.null的处理

#eg:ifnull是mysql的用法，对应还有isnull（sql server）
select column1 * ifnull(column2, 0)
from table_name

17.procedure

#create
create procedure_name
as
sql statement
go
#execute
exec procedure_name
#带参数的例子
create select_stu @city nvarchar(30), @habit nvarchar(30)
as
select * from student where city = @city and habit = @habit
go;
exec select_stu city="beijing", habit="reading"