【无标题】

万能语句

select * from 

1. 创建

 CREATE TABLE 成本表(
单据号 varCHAR(9), 
预算单位 varCHAR(18),
井号 varCHAR(9),
施工单位 CHAR(16), 施工内容 CHAR(8),设备费 Float,其它费用 Float,
结算金额 Float,
结算人 varCHAR(8),
结算日期 DATE,
)

2. 修改

# 新建列
ALTER TABLE cost ADD 施工单位 CHAR(16), 施工内容 CHAR(8), 结算金额 int, 结算人 varCHAR(8),结算日期 DATE,

# 修改列
ALTER TABLE 成本表 ALTER COLUMN 入账人 varchar(8);
select * from 成本表;

# 数据表增加主码约束条件
alter table 油水井表 alter column 井号 varchar(10) not null;# 值非空
alter table 单位表 add unique(单位名称);# 列值唯一
alter table 油水井表 add check(井别 = '油井' or 井别 = '水井');# 限制取值

alter table 油水井表 add primary key (井号 );# 加主码
alter table 材料消耗表 add foreign key(物码) references 材料表(物码); # 加外码

3. 删除

drop table 成本表 [RESTRICT|CASCADE]

delete from 汇总表;
drop table 汇总表;
# 前者是删除数据表内的数据，数据表仍然存在，后者则是删除了数据表，数据表不存在了。

4. 索引

# 创建索引
CREATE INDEX  Yvno ON 成本表(预算日期);
create unique index SCno on SC(Sno ASC,Cno DESC) # ASC升序DESC降序

# 修改索引
ALTER INDEX SCno RENAME TO SCSno # 只能改名

# 删除索引
DROP INDEX Yvno ON 成本表;
DROP INDEX 成本表.Jieno ;

数据的操作

1. 插入

INSERT INTO 材料表
VALUES
('wm001', '材料一', '吨 ' ,'10'),
('wm002', '材料二', '米','10'),
('wm003', '材料三', '桶','10') ,
('wm004', '材料四', '袋','10');

2. 修改

# 更新数据
UPDATE 成本表
SET 人工费  = 人工费 + 200,
    结算金额 = 结算金额 + 200
WHERE 单据号 = 'zy2020005'

# 删除数据
delete
from 作业项目表
where 入账金额 is NULL;

# 撤销
begin tran;
insert into 单位表 ()
values (1, 2);
select * from 单位表;

rollback tran;
select * from 单位表;

3. 查询

通式

SELECT [ALL|DISTINCT] 目标列表达式
FROM 表名或视图名[别名][,表名或视图名] # 别名用在自身连接和相关子查询上可以把一个表当成俩用，nice！
[WHERE 条件表达式]
[GROUP BY 列名[HAVING 条件表达式]]
[ORDER BY 列名 [ASC|DESC]]

# 使用别名和表达式
select Sname NAME，'Year of Birth:' BIRTH, 2014-Sage BIRTHDAY, LOWER(Sdept)
from Student
# 使用运算
select distinct 单据号,单价 * 消耗数量 总价 
from 材料消耗表,材料表
where 材料消耗表.物码 in(select 物码 from 材料表 where 名称='材料三') 
		and 单价 * 消耗数量>=2000

3.1 条件查询 P93

# 比较大小
select * from 成本表
where 预算日期>='2020-5-1' AND 预算日期<='2020-5-28'
      AND 预算单位=(select 单位代码 from 单位表 where 单位名称='采油一矿二队');
      
# 确定范围+多表查询
select * from 成本表,单位表
where 预算单位 = 单位代码 AND 单位名称 = '采油一矿二队'
      AND 结算日期 between '2020-5-1' and '2020-5-28'; 
      
# 确定集合+子查询
select  * from 材料消耗表 
where 单据号 in 
      (select 单据号 from 成本表,单位表
	   where 预算单位 = 单位代码 and 结算日期 between '2020-5-1' and '2020-5-28' 
       and 单位名称 = '采油一矿二队'
	  );
	  
# 字符匹配 %任意个  _一个 课本
SELECT Sname, Sno FROM Student 
where Sname like '_阳%'

# 空值+去重
select distinct 入账人 from 成本表
where 入账日期 is not null;  

3.2 聚集函数，GROUP BY，ORDER BY P97

where作用于表选择数据，having作用于组选择组别

聚集函数只能用在select子句和having后面，不能用在where里面

select 单位名称,项目数量=count(结算金额),结算金额总和 = sum(结算金额) from 成本表,单位表
where 预算单位=单位代码
Group by 单位名称

3.3 嵌套查询

select 施工单位 from 成本表
where 井号 in (select 井号 from 油水井表 where 井别='油井' 
				and 单位代码 in (
                    select 单位代码 from 单位表 where 单位名称 like '采油一矿%'
                )
            )
            
# 相关子查询——每个学生超过他自己选修课程平均成绩的课程号
select Sno,Cno from SC x 
where Grade>=(select avg(Grade) from SC y 
              where y.Sno = x.Sno)

3.4 集合查询 P100

# UNION 并  INTERSECT  交   EXCEPT 差
select 单据号 from 成本表 where 施工单位 = '作业公司作业一队'
union
select 单据号 from 成本表 where 施工单位 = '作业公司作业二队';

4. 综合操作

-- 3⑴ 建立汇总表(包含3个属性列：★施工单位、★年月、◆结算金额)保存各个施工单位每月的结算金额总和。
create table 汇总表
       (施工单位 varchar(50),
	    年月 varchar(50),
		结算金额 decimal(10, 2)
	   )

-- 3⑵ 用子查询将各个施工单位每月的结算金额总和插入到所建立的数据表中。
insert into 汇总表(施工单位,年月,结算金额)
       select 单位名称,year(结算日期)*100+month(结算日期),结算金额总和 = sum(结算金额)
       from 成本表,单位表
       where 预算单位 = 单位代码
       group by 单位名称,year(结算日期)*100+month(结算日期);
select * from 汇总表;

-- 3⑶ 用带子查询的修改语句将采油一矿油井作业项目的结算人改为“李兵”。
begin transaction;
update 成本表
set 结算人='李兵'
where 预算单位 in
      (select 单位代码 from 单位表
	   where 单位名称 like '采油一矿%'
	  );
select * from 成本表;
rollback;

-- 3⑷ 用带子查询的删除语句删除采油一矿油井作业项目。
begin transaction
delete
from 成本表
where 预算单位 in
      (select 单位代码 from 单位表
	   where 单位名称 like '采油一矿%'
	  );

rollback;

视图

create view V1
as
select 成本表.*,材料消耗表.消耗数量,材料消耗表.物码
from 成本表,材料消耗表
where 成本表.单据号 = 材料消耗表.单据号;

select *
from v1
where 单据号 = 'zy2020001';