By TreeDream

基本表的定义，修改，删除

建表考虑列名，数据类型，列级完整性，表级完整性（参照性约束）

create table Salvaging(    prj_num char(8) primary key,    prj_name varchar(50),    start_date datetime,    end_date datetime,    prj_status bit,);?create table Stock(    mat_num char(8) primary key,    mat_name varchar(50) not null,    speci varchar(20) not null,    warehouse char(20),    amount int,    unit decimal(18,2),    total as(amount*unit),    check(mat_num like ‘[m][0-9][0-9][0-9]‘),);?create table Out_stock(    prj_num char(8),    mat_num char(8),    amount int,    get_date datetime default getdate(),    department char(20),    primary key (prj_num,mat_num),    foreign key(prj_num) references Salvaging(prj_num),    foreign key(mat_num) references Stock(mat_num),);?alter table Salvaging add prj_director varchar(10);alter table Salvaging drop column prj_director?drop table Salvaging

简单查询

单表查询

select prj_name 项目名称,start_date 开始日期,end_date 结束日期,DATEDIFF(day,start_date,end_date) 抢修天数from Salvaging

select *from Stockwhere unit>=50 and unit<=100 --条件

in集合

select mat_num,speci,amount,warehousefrom Stockwhere warehouse not in (‘供电局1#仓库‘,‘供电局2#仓库‘)

字符匹配

select *from Stockwhere mat_name like ‘__绝缘%‘

NULL值(is)

select *from Stockwhere unit is null

排序：只能对最后的查询结果排序

select *from Stockwhere mat_name=‘护套绝缘电线‘order by unit desc  --默认从低到高(asc),desc从高到底

聚集函数

select MAX(amount),min(amount),avg(amount)from Out_stockwhere mat_num = ‘m001‘

分组

select prj_num 项目号,count(*) 物资种类from Out_stockgroup by prj_num

select prj_num 项目号,count(*) 物资种类from Out_stockgroup by prj_numhaving count(*)>=2

连接查询

等值与非等值查询

等值 与非等值根据连接谓词

广义笛卡尔积不带谓词，没有意义；

自然连接：在等值连接的基础上，去除重复列

select Salvaging.prj_num,Salvaging.prj_name,mat_num,amount,get_date,end_date,departmentfrom Salvaging,Out_stockwhere Salvaging.prj_num = Out_stock.prj_num

外连接查询

连接操作中，如果有一个关系没有与之对应，就不会有输出，但是也丢失了另一个关系的基本情况，解决方案是外连接

外连接：左外连接，右外连接，全外连接

select Salvaging.prj_num,Salvaging.prj_name,mat_num,amount,get_date,end_date,departmentfrom Salvaging left outer join Out_stock on (Salvaging.prj_num = Out_stock.prj_num)

复合条件查询

select distinct Salvaging.prj_num,Salvaging.prj_namefrom Salvaging,Out_stock,Stockwhere Salvaging.prj_num = Out_stock.prj_num and Out_stock.mat_num = Stock.mat_num and Stock.mat_name=‘护套绝缘电线‘

自身连接查询

select A.prj_numfrom Out_stock A,Out_stock Bwhere A.prj_num = B.prj_num and A.mat_num = ‘m001‘ and B.mat_num = ‘m002‘

嵌套查询

带谓词in的嵌套查询（子查询往往是一个集合）

select Stock.mat_name,speci,amountfrom Stockwhere warehouse in (        -- = 亦可        select warehouse        from Stock        where speci = ‘BVV-120‘ and mat_name = ‘护套绝缘电线‘    )

/*select Stock.mat_num,Stock.mat_namefrom Salvaging,Out_stock,Stockwhere Salvaging.prj_name=‘观澜站光缆抢修‘ and Salvaging.prj_num = Out_stock.prj_num and Out_stock.mat_num = Stock.mat_num*/?select mat_num,mat_namefrom Stockwhere mat_num in (    select mat_num    from Out_stock    where prj_num in (        select prj_num        from Salvaging        where prj_name = ‘观澜站光缆抢修‘    ))

带比较运算符的嵌套查询

select mat_num,mat_namefrom Stock s1where amount > (    select avg(amount)     from Stock s2    where s2.warehouse = s1.warehouse)

带any或all谓词的嵌套查询

select mat_name,speci,amountfrom Stockwhere warehouse <> ‘供电局1#仓库‘ and amount < ALL (    select amount    from stock    where warehouse = ‘供电局1#仓库‘)

带exists谓词的嵌套查询

select prj_namefrom Salvagingwhere exists (    select*    from Out_stock    where prj_num = Salvaging.prj_num and mat_num = ‘m001‘)

sql中没有全称量词，把全称量词转换为存在量词

--查询被所有工程使用过了的物资——没有一个工程没有使用过他select mat_name,specifrom Stockwhere not exists (    select*    from Salvaging    where not exists (        select*        from Out_stock        where mat_num = Stock.mat_num and prj_num = Salvaging.prj_num    ))

数据更新

插入数据

insertinto Salvagingvalues (‘20110011‘,‘观澜站电缆接地抢修‘,‘2011-2-3 0:00:00‘,‘2011-2-5 12:00:00‘,1)

插入查询结果

insertinto Prj_costselect prj_num,sum(out_stock.amount*unit)from Out_stock,Stockwhere Out_stock.mat_num = stock.mat_numgroup by prj_num

修改数据

update Stockset unit = 44.5where mat_num = ‘m020‘

删除数据

deletefrom Out_stockwhere prj_num = ‘20110001‘ and mat_num = ‘m001‘

视图

• 视图是数据库数据的特定子集。可以禁止所有用户访问数据库表，而要求用户只能通过视图操作数据，这种方法可以保护用户和应用程序不受某些数据库修改的影响。

• 视图是抽象的，他在使用时，从表里提取出数据，形成虚的表。 不过对他的操作有很多的限制 。

创建视图

create view s1_stockasselect mat_num,mat_name,speci,amount,unitfrom Stockwhere warehouse = ‘供电局1#仓库‘

查询视图

--像基本表一样查询视图select *from s1_stock

更新视图

--insert,deleteupdate s1_stockset amount = 100where mat_num = ‘m001‘

删除视图

drop view s1_stock

结构化查询语句