SQL常用语句（面试必备）

SQL概念和标准SQL语言

• SQL英文全称是Structured Query Language，即结构化查询语言
• SQL是关系数据库操作的国际标准语言

SQL的功能

• 产生汇总统计表
• 从表和视图中检索数据
• 合并表和视图中的数据
• 建立表、视图和索引
• 修改、添加、提出表中的数据列
• 更新表中的数据值

SQL过程举例

Proc sql;/*开始SQL过程*/

  create table  test as  /*创建表*/

  select  a.comcd, a.lcomchnm, b.stkcd, b.lstknm,         a.csrciccd1, count(a.comcd) as n
  from resdat.cinfo a left join resdat.lstkinfo b 
  on  a. stkcd=b.stkcd
  where  estdt>'1jan1985'd
  group by a.csrciccd1
  order by a.csrciccd1; /*查询语句*/
  quit;/*终止SQL过程*/  

一、查询语句

• SELECT语句是PROC SQL的最主要、最常用的工具。
• 用来识别、检索和操作表中数据，使用子句可以设定查询条件。
• 常用子句及其固定顺序：
  • Select 选择的变量/列名
  • from 来源数据集/表名
  • where 选择条件
  • group by 分组标志（变量）
  • having 针对组的选择条件
  • order by 排序标志（变量）
• 其中只有select和from是必须的，其他子句都是可选的

1. SELECT子句

选择列

• 特定列：select+列名

• 多个列名用 “,” 分隔；按照语句中顺序显示列

• 所有列：select *

• 删除重复观测: select distinct+列名

• 如果有多个列，只删除所有变量取值都相同的观测

计算新列值; 为列分配别名; 指定列属性；

引用新计算的列：calculated +列名，只能在select和where子句中设定calculated列。

Proc sql;
    select  name, age, height from resdat.class;
    select  *  from resdat.class;
    select  distinct  age from resdat.class;
Quit;

Proc sql;
   select name, weight/height as whratio format=4.2 
from resdat.class;
Quit;

Proc sql;
   select name, age, weight/height as whratio format 4.2, 
         (1/calculated whratio) as hwratio format 6.4
   from resdat.class;
Quit;

2. WHERE子句

选择条件：where + 条件表达式

• 比较算符和逻辑算符还有一些等价形式。
• 所有算符前面加一个not算符，可以得到相反的选择条件。
• 条件较复杂时，可以将表达式用括号括起来，以突出逻辑关系、增强可读性。

几个重要条件算符

• in: 符合选项之一； in+(选项1，选项2，…）
• between-and： 选择一定范围
• like：部分或全部匹配； %可代替任意数量的字符； _可代替一个字符

proc sql;
   select * from resdat.class where age in (12 14 16);
   select * from resdat.class where age between 12 and 15;
   select * from resdat.class where (sex='F' and weight>100) or (sex='M' and weight<=90);
   select * from resdat.class where name like '%h%';
   select * from resdat.class where name like '_h%';
 quit;

3. GROUP BY和ORDER BY子句

• Group by一般与汇总函数结合使用，汇总函数按照GROUP BY子句进行分组汇总：
  • 如果没有GROUP BY，就默认整个表为一组；
  • 如果没有汇总函数，则GROUP BY单纯分组。
• Order by对结果排序

proc sql;
 select name, sex, count(sex) as n, avg(height) as avgh 
 from resdat.class
 group by sex
 order by name desc;
quit;

4. HAVING子句

HAVING筛选组数据

• HAVING对组设定条件，进行检索筛选；
• 在GROUP BY和汇总函数后运行，可以引用汇总函数结果。

与WHERE子句的区别：

• WHERE对观测设定条件，进行检索筛选；
• 在GROUP BY和汇总函数前运行，不可以引用汇总函数结果。

proc sql;
 select age, avg(height) as avgh 
 from resdat.class
 group by age
 having avgh>60;
quit;

SELECT语句综合练习

选择resdat.class中，名字首字母不是 B 的学生数据。

• 按年龄分组统计平均体重（avgw）、每组人数（n）
• 输出平均体重大于100的组，列示年龄、平均体重、每组人数
• 按照年龄倒序排列

proc sql;
 select age, avg(weight) as avgw, count(*) as n 
 from resdat.class
 where name not like 'B%'
 group by age
 having avgw>100
 order by age desc;
quit;

二、多表查询

• FROM后面直接加一个表名，实现从单个表中检索数据涉及多个表，常使用连接或子查询。
• 连接查询： 从多个表中选取数据 数据来自不同的表
• 子查询： 通过表与表之间的联系选取数据 选择一个表的数据时， 需要参照其他表的信息。

1. JOIN连接——交叉连接

交叉连接（CROSS JOIN）/简单连接 （,）：

最基本的连接，笛卡尔积形式，所有可能组合。

proc sql;
  select * from resdat.china cross join resdat.usa;
  select * from resdat.china , resdat.usa;
quit;

如果观测行数很多，这种连接会产生巨大的数据结果，且绝大部分没有意义，可用其他JOIN连接选择相应子集。

JOIN连接——内部连接

• 返回两表中匹配连接条件的行：表a inner join 表b on 连接条件。
  • 查询时经常遇到两个表有相同列，为了引用清楚，在列前加表名；
  • 为了书写和阅读方便，可在from子句中设定表的别名；
  • 关键词inner, as可选；
  • 仍然可以使用where、order by等子句。

例：
proc sql;
  select * from resdat.china as a inner join resdat.usa as b 
  on a.level=b.level;
quit;

等价于：
proc sql;
  select * from resdat.china a , resdat.usa b 
  where a.level=b.level;
quit;

JOIN连接——外部连接

• 返回内部连接的行以及部分不匹配连接条件的行；
  • 左外部连接(LEFT JOIN)：内部连接行+左边表中不符合匹配条件的行；
  • 右外部连接(RIGHT JOIN)：内部连接行+右边表中不符合匹配条件的行；
  • 完全外部连接(FULL JOIN)：内部连接行+左右两表中不符合匹配条件的行。

例：
proc sql;
 select * from resdat.china a left join resdat.usa b on a.level=b.level;
 select * from resdat.china a right join resdat.usa b on a.level=b.level;
 select * from resdat.china a full join resdat.usa b on a.level=b.level;
quit;

JOIN连接和MERGE语句比较

• DATA中的MERGE和SQL过程中的JOIN语句

  在很多情况下可以产生相同结果：

  • 所有行匹配且无重复值情况：merge=inner join
  • 部分行匹配且无重复值情况：merge=full join

• 区别在于：

  • 有重复值的情况：merge按照位置顺序合并；join根据具体值连接，可以产 生所有组合，并可以选择其子集；
  • JOIN连接之前不需要排序；
  • JOIN可以进行不等值连接

• 所以总的来说，JOIN比MERGE更灵活。

JOIN连接综合练习

按照level相同的原则匹配中美两队队员，分别选择：
中国队所有队员的匹配情况；美国队所有队员的匹配情况；
中美两队成功匹配的队员；中美两队所有队员的匹配情况

参考程序：
proc sql;
 select * from resdat.china a left join resdat.usa b on a.level=b.level;
 select * from resdat.china a right join resdat.usa b on a.level=b.level;
 select * from resdat.china a inner join resdat.usa b on a.level=b.level;
 select * from resdat.china a full join resdat.usa b on a.level=b.level;
quit;

思考分别如何实现：美国队内部按照level相同原则匹配？
按照美国队员比中国队员level低的原则匹配？
proc sql;
 select * from resdat.usa a left join resdat.usa b on a.level=b.level;
 select * from resdat.china a left join resdat.usa b on a.level>b.level;
quit;

2. 子查询

子查询——简单子查询

在一个查询语句中，用到了另一个查询的结果

• 简单子查询（不相关子查询）：
• 子查询独立于外部查询；
• 子查询返回的结果要与该查询外的算符相对应：
  • 如果是比较算符如>、=等，子查询只能返回一个值；
  • 如果是条件算符如in、any、all等，子查询可以返回多个值。

proc sql;
 select * from resdat.usa 
   where level<(select max(level) from resdat.china);
 select * from resdat.usa 
   where level in (select distinct level from resdat.china);
quit;

子查询——混合子查询

混合子查询：子查询用到外部查询传输进来的值，该子查询运行后再给外部查询返回结果；可以返回单值或多值。

• 其实子查询可以拆分成两个单独的查询，学习创建表之后可以实现

proc sql;
 select * from resdat.yrret a
 where (select stktype from resdat.lstkinfo b 
        where a.stkcd=b.stkcd)='A'
    and '01jan2005'd<=date<='31dec2005'd;
quit;

合并查询（SET算符）

• 合并查询：对两个完全匹配的查询结果进行并、差、交运算与JOIN的横向连接不同，SET算符是竖直的连接。

  select 语句1 + SET算符 + select 语句2

• UNION:产生多个查询中所有的非重复观测

• EXCEPT：产生只属于第一个查询、不属于第二个查询的观测

• INTERSECT：产生两个查询共同的观测

• OUTER UNION：对多个查询结果直接连接

三、创建与更新表

• 创建表：CREATE TABLE + 表名
• 列定义方式： CREATE TABLE + 表名+（列名、类型、长度、格式、标签）
• 从查询结果创建表： CREATE TABLE + 表名 + AS + SELECT语句
  • 新表包括SELECT语句所选择的列，且保持原来的列属性

proc sql;
 create table class 
   (code char(6), name char(20), 
    date num informat=date9. format=data9.);
quit;

proc sql;
 create table resdat.class1 as select * from resdat.class;
quit;

利用表过渡，将子查询拆分成两个查询

例：
proc sql;
 select * from resdat.usa 
   where level in (select distinct level from resdat.china);
quit;

拆分：
proc sql;
 create table a as select distinct level from resdat.china;
 create table b as select * from resdat.usa a join a b
   on a.level=b.level;
quit;

使用DATA SET选项：在from语句中的表名后，可以使用DATA SET选项语句。

• 在表中插入行： insert into+表名
• 用SET子句插入行：insert into+表名+ set + 根据列名赋值
• 用VALUES子句插入行： insert into+表名+ VALUES +(对应列的位置赋值)

proc sql;
 insert into resdat.class1  set name='Lily', age1=13, sex='F', height=60.5;
 insert into resdat.class1  values ('Lucy', 'F', 14, ., 89);
 select * from resdat.class1;
quit;

还可以对表进行以下操作：更新观测、删除观测；增加列、修改列、删除列；删除表 等等。

• 在这些功能上，PROC SQL与DATA步相比，没什么优势，因此不做赘述
• PROC SQL还包括视图功能：
  • 对视图（Views）的各种操作与表(Table)基本相同
  • 差别在于：
    • 视图不储存数据，只是储存一个查询语句；
    • 在使用该视图时调用该查询并展现结果。