常用SQL_数据库查询总结1

SQLSERVER环境

一、增删改查

简单根据语句的作用，分类整理一下。

1、增（创建表、复制表、创建视图、创建存储过程）

--创建表
	CREATE TABLE TEST(
	    ID varchar (50) NOT NULL,
	    NAME varchar (50) NULL) ;

--复制表  与Oracle一致
  --复制表结构和数据
  CREATE TABLE USER4COPY AS SELECT * FROM USER;
  --复制表结构
  CREATE TABLE USER4COPY AS SELECT * FROM USER WHERE  1 = 2;

--创建视图
--判断是否存在
	IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'VW_TEST')
	 DROP VIEW VW_TEST  --存在的话删除
	GO
	CREATE VIEW VW_TEST AS SELECT * FROM TEST1    
              
--创建存储过程
	IF (EXISTS (SELECT * FROM SYS.OBJECTS WHERE NAME = 'GETUSER')) 
	  DROP PROC GETUSER
	GO
	CREATE PROC GETUSER
	@ID INT --参数
	AS
	SET NOCOUNT ON;  --不返回计数，提高应用程序性能
	SELECT * FROM USER WHERE ID = @ID  --执行SQL语句
--调用存储过程
	EXEC GETUSER 201909260001;

2、删（删除表，删除行数据，清空表数据）

drop table 表名称
delete from 表名称 where 列名称 = 值
truncate table 表名称;--清空

3、改（改行数据，改表结构，增加行数据）

update 表名称 set 字段='值' where 条件;
alter table FSSCOASQD add gdzt varchar(2);--增加字段
alter table FSSCOASQD add hetqdzje decimal(18,6) --增加字段
insert into test1(ID,NAME,SEX,AGE) values('1','1','1','1');

4、查

select * from 表名称 where 条件;
select distinct 字段名 from 表名称 where 条件;--去除重复值
select count(*) from user_tab_cols where table_name='表名';--查询表的列数
select table_name from user_tables;--查询所有表名称
select * from all_tables where owner='用户名';--查询用户名下所有表名称
select owner from dba_tables where table_name='表名称';--查询表的所有者
select count(*), LSBMZD_BMBH FROM LSBMZD GROUP BY  LSBMZD_BMBH HAVING  count(*)>1;-- 查询重复项及其次数
        
SQL server
select name from sysobjects where xtype='u' and name like '%wqsx%'
        
if exists (SELECT 1 FROM  sysobjects  WHERE  id = object_id('VM_ROYWSQCX') and type = 'V')         drop view VM_ROYWSQCX;--如果存在视图，则删除视图
        
select count(1) as col_Count FROM syscolumns WHERE id = OBJECT_ID('表名');--查表列数
                                                    
sp_help TBTZZB ;--展示表结构
sp_columns TBTZZB ;--展示表列
        
Oracle
select t.*, t.rowid from t;--通过rowid查看行数—rowid是每一行的唯一标识符

二、其他常用sql语句

1、insert和 select 结合实现“插入某字段在数据库中的最大值＋1”

insert into ZZY_NBZZD(ID,ZYJE) values ('1111',(select max(ZYJE+1) from ZZY_NBZZD));

2、使用正则过滤查询结果（Oracle）

select BANKACCOUNTID from BANKACCOUNTS;
select BANKACCOUNTID from BANKACCOUNTS where regexp_replace(BANKACCOUNTID,'\d','') is not null;--过滤掉纯数字
select BANKACCOUNTID from BANKACCOUNTS where regexp_replace(BANKACCOUNTID,'^[-\+]?\d+(\.\d+)?$','') is not null;--过滤掉纯字母
--去掉not就变成筛选出纯数字和纯字母了

3、读取XML格式文件，openxml方法

declare @idoc int
declare @GoodsXml nvarchar(max);
set @GoodsXml='<OrderItemGoods>
<Goods><GoodsAttributeId>21</GoodsAttributeId><GoodsCount>2</GoodsCount><GoodsPrice>1.1</GoodsPrice><GoodsId>1</GoodsId></Goods>
<Goods><GoodsId>2</GoodsId><GoodsAttributeId>22</GoodsAttributeId><GoodsCount>2</GoodsCount><GoodsPrice>1.1</GoodsPrice></Goods>
<Goods><GoodsId>3</GoodsId><GoodsAttributeId>23</GoodsAttributeId><GoodsCount>2</GoodsCount><GoodsPrice>1.1</GoodsPrice></Goods>
</OrderItemGoods>'
exec sp_xml_preparedocument @idoc output, @GoodsXml ;
select
    T.GoodsId,
    T.GoodsAttributeId,
    T.GoodsCount,
    T.GoodsPrice
from
openxml(@idoc, '/OrderItemGoods/Goods',2)
with
(
  GoodsId bigint 'GoodsId',
  GoodsAttributeId bigint,
  GoodsCount int,
  GoodsPrice float
)as T
--where T.GoodsId=1
--系统sp_xml_removedocument删除SQLServer服务器内存中的XML数据
exec sp_xml_removedocument @idoc ;

5、日期类型

--1、日期作差（SQL server）
(select DateDiff (day,'2019-9-11 11:07:00.260',getdate()))--返回date2 与date1两个日期之间的差值 date2-date1
Year Yy yyyy 年 
Quarter Qq q 季 
Month Mm m 月
Day of year Dy y 一年的日数
Day Dd d 日
Weekday Dw w 一周的日数
Week Wk ww 周，
Hour Hh h 时
Minute Mi n 分钟
Second Ss s 秒
Millisecond Ms

--2、日期转换
CONVERT(VARCHAR(22), ROBXZT_ZTSJ, 120);--2019-07-02 16:35:03
select FORMAT(getdate(),'yyyy-MM-dd');--2019-10-11

6、字符串（替换）

SQL> select REPLACE('fgsgswsgs', 'fk' ,'j') from dual;--fgsgswsgs
SQL> select REPLACE('fgsgswsgs', 'sg' ,'eeerrrttt') from dual;--fgeeerrrtttsweeerrrttts

wahson 20191014