/*1 SQL查询*/
/*1.1 简介*/
PROC SQL;
CREATE TABLE CLASS AS
SELECT height,weight,name FROM XIE.CLASS
WHERE SEX="男";
QUIT;
/*1.2 单表查询*/
PROC SQL;
CREATE TABLE A1 AS
SELECT height,weight,name
FROM XIE.CLASS
WHERE age>12
GROUP BY sex
ORDER BY height;
SELECT name, height
FROM XIE.CLASS
WHERE name='托马斯';
/*(一)选择列表SELECT*/
/*1、选择所有列 */
SELECT *
FROM xie.class;
/*2、选择部分列并指定它们的显示次序 */
SELECT name,height
FROM xie.class;
/*3、更改列标题 */
SELECT name as 姓名, height as 身高
FROM xie.class;
/*4、删除重复行 */
SELECT ALL SEX,AGE
FROM xie.class;
SELECT DISTINCT SEX,AGE
FROM xie.class;
/*5、选择不同的行 */
SELECT DISTINCT age
FROM xie.class;
/*
data xie.class_score ;
merge xie.class xie.student;
if name="" then delete;
drop sex age height weight;
run;
*/
proc sql noprint;
select age, sex
into :age, :sex
from xie.class
where name="简";
%put &age &sex;
/*(二)FROM子句*/
PROC SQL;
SELECT class_score.name,sex
FROM xie.class,xie.class_score
WHERE class.name=class_score.name;
SELECT a.name,sex
FROM xie.class AS a,xie.class_score AS b
WHERE a.name=b.name;
SELECT a.name,sex
FROM xie.class a,xie.class_score b
WHERE a.name=b.name;
SELECT a.name,b.math,total.sum
FROM xie.class a,xie.class_score b,
(SELECT name,sum(math,phys,chem,literat,history,english) AS sum
FROM xie.class_score ) AS total
WHERE a.name=b.name=total.name AND total.sum>360;
/*(三)使用WHERE子句设置查询条件*/
SELECT *
FROM xie.class
WHERE age>12;
SELECT *
FROM xie.class
WHERE age between 10 and 30;
SELECT *
FROM xie.class
WHERE age in(12,13,14);
SELECT *
FROM xie.class
WHERE name like "%%斯";
SELECT *
FROM xie.class
WHERE age not is null;
/*(四)查询结果分组GROUP BY、排序ORDER BY*/
SELECT *
FROM xie.class
ORDER BY age desc,name ASC ;
SELECT *
FROM xie.class
GROUP BY sex ;
/*(五)函数*/
/*(六)其他语句*/
/*1.CREATE(创建表, 索引, 视图, 同义词, 过程, 函数, 数据库链接)*/
CREATE TABLE a1 AS
SELECT *
FROM xie.class;
/*2.ALTER(改变表, 索引, 视图等)*/
/*在表的后面增加一个字段*/
ALTER TABLE a1 ADD average num;
/*删除一个字段*/
ALTER TABLE a1 drop average;
DROP TABLE a1;
CREATE TABLE a1 AS
SELECT *
FROM xie.class;
DELETE FROM a1;
INSERT INTO A1 VALUES ( 'ZHANG','男',12,66,120);
UPDATE A1
SET SEX='女'
WHERE NAME='ZHANG';
/*1.3 跨表查询*/
CREATE TABLE a2 AS
SELECT a.name,a.sex,b.sum
FROM xie.class a,
(SELECT name,sum(math,phys,chem,literat,history,english) AS sum
FROM xie.class_score ) AS b
WHERE a.name=b.name ;
/*1.4 嵌套查询*/
/*1.5 案例*/
proc sql;
/*创建表*/
create table student
(Sno char(4)NOT NULL UNIQUE,/*列级完整性约束条件：取值唯一，不许缺失*/
Sname CHAR(20) UNIQUE,/*列级完整性约束条件：取值唯一*/
Ssex char(1),
Sdept char(3),
Salary num,
Birth num informat=date7.
format=date7.,
Hired num informat=date7.
format=date7.);
/*写入信息*/
insert into student
values('1639', '李四', 'F','TA1',42260,'26JUN70'd,'28JAN91'd)
values('1065', '张立', 'M','ME3',38090,'26JAN54'd,'07JAN92'd)
values('1400', '李勇', 'M','ME1',29769.'05NOV67'd,'16OCT90'd)
values('1561' ,'刘晨', 'M',null,36514,'30NOV63'd,'07OCT87'd)
values('1221', '王敏', 'F','FA3',.,'22SEP63'd,'04OCT94'd ) ;
/*修改*/
alter table student
add age num;
/*创建查询*/
title 'PROCLIB.PAYLIST Table';
select *
from student;
/*删除表*/
drop table student;
quit;