SQL脚本语句（一）

SELECT - 从数据库表中获取数据
UPDATE - 更新数据库表中的数据
DELETE - 从数据库表中删除数据
INSERT INTO - 向数据库表中插入数据
CREATE DATABASE - 创建新数据库
ALTER DATABASE - 修改数据库
CREATE TABLE - 创建新表
ALTER TABLE - 变更（改变）数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引（搜索键）
DROP INDEX - 删除索引

SQL脚本实例：

查询Test表
select*from Test
–克隆Test表到test1表中
select *into test1 from Test
–删除Test表中列名a，b，c包含null的内容
delete from Test
where a is null
and b is null
and c is null
–更新Test表中列名a包含‘；’，‘？’‘Z’‘‘’’的内容删除掉
update Test set a=replace(dh,’;’,”)
update Test set a=replace(dh,’?’,”)
update Test set a=replace(dh,’Z’,”)
update Test set a=replace(dh,””,”)
–更新Test表中列名dh超过11位的后面全部删除
update Test set dh = SUBSTRING(dh,0,12)
–去除Test表包含重复的值
with temp as
(
select ROW_NUMBER() over(PARTITION BY dong,fj,yys,azdata,tc,name,dh ORDER BY id) num, dong,fj,yys,azdata,tc,name,dh from Test
– group by dong,fj,yys,azdata,tc,name,dh
)
delete temp where num >1
–更新Test表中列名yys内容替换(when替换成then)
–update Test set yys = case when yys = ‘中国电信’ then ‘电信’
when yys= ‘中国移动’ then ‘移动’
when yys=’联发科移动’ then ‘移动’
when yys=’长城宽带’ then ‘长城’
when yys=’长宽存量’ then ‘长城’ end
—-查询Test与fw表，列名房间楼栋对比是否一样
select * from test3 a
inner join (
select distinct dong,fj from test3
except
select ld,fj from fw3) b on a.dong = b.dong and a.fj = b.fj
select * from fw where ld like ‘58’