Sql基础语句

//查询表中字段类型,长度
select a.name 表名,b.name 字段名,c.name 字段类型,c.length 字段长度
from sysobjects a,syscolumns b,systypes c where a.id=b.id and a.name='po_details' and a.xtype='U'
and b.xtype=c.xtype
//查询表里面都有哪些存储过程
select * from sysobjects where xtype='p'
//定义两个变量求乘积
declare @h int=2,@y int=5
select @h*@y as 积
//sql删除表中无用字段
alter table bwstudent(表名称) drop column Shobdy(无用的表字段名字)
/*  嵌套查询(简单)
select * from PO_Details where taskid in (select taskid from po_main where cinvcode='51023402-0060' and id in(1081,1082,1083,1084))
   多层嵌套(可支持255层)
select * from  bpminsttasks where TaskID in (select TaskID from PO_Details where AutoID in (select AutoID from PO_Main))
*/

//sql查询一个范围内的数据
select * from user where age between 10 and 30
//SQL中with用法
/*
with相当于给整条sql语句起条别名,在后续联查过程中,将整条sql结果当做表,选择要拼接显示结果的字段
*/
with AA AS(select a.SN,a.TaskID,a.ccusperson,a.CCusName from PO_Main a),
BB as (select b.TaskID,b.CInvName,b.CSupplierName,b.AutoID,b.DeliveryMemo,b.DeliveryTempQuantity from PO_Details b)
select AA.CCusName,AA.SN,AA.TaskID,BB.CInvName from AA  join BB ON AA.TaskID=BB.TaskID

//全球唯一标识符C#写法 (数据库定义关键字: uniqueidentifier)
在C#中的语法:
Console.WriteLine(System.Guid.NewGuid());
Console.ReadKey();
System.Guid.NewGuid().ToString()全球唯一标识符,是一个字母数字标识符,用于指示产品的唯一性安装.在很多流行软件应用程序,例如浏览器媒体播放器,都使用GUID.
GUID的格式: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx(32个x).都是由0~9或a~f范围内的一个十六进制的数字.
--------------------------------------sql升降序写法----------------------------
```csharp
降序：SELECT * FROM kc(表名) ORDER BY cpbh(字段名) DESC
升序：SELECT * FROM kc(表名) ORDER BY cpbh(字段名) ASC
语法：
sql可以根据字段进行排du序，其中，DESC表示降序，ASC表示升序
order by 字段名zhi  DESC;按照字段名降序排序
order by 字段名 ASC;按照字段名升序排序
实例：
一、/*查询学生表中姓名、学号，并以学号降序排序*/
select name,StuID from Students_information order by StuID desc   /**order by 以什么排序，默认为升序,desc是降序*/
二、/*查询学生表中前5名学生的姓名，学号，并以学号升序排列*/
select top 5 name,StuID from Students_information order by StuID        /*order by 默认为升序*/
扩展资料：
一、ORDER BY 语句
ORDER BY 语句用于根据指定的列对结果集进行排序。
ORDER BY 语句默认按照升序对记录进行排序。
如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。
二、SQL 排序多个字段
order by 多个字段，每个字段后面都有排序方式，默认ASC
例如：select table a order by a.time1 ,a.time2 desc,a.time3 asc
```w3school-SQL ORDER BY 子句
-------------------------------sql读取系统日期和时间的方法------------------
/*
VS中获取系统日期、时间
 DateTime dt = System.DateTime.Now;//定义dt获取系统的现在时间，各式为:YY-DD-HH MM:SS
 this.txb_enddate.Text = dt.ToShortDateString();//ToShortDateString()函数，将dt转换成短日期格式，即格式：YY-DD-HH。 this.txb_enddate(文本框）。
 this.txb_ruyuantime.Text = dt.TimeOfDay.ToString()//TimeOfDay.ToString()函数，将dt转换成短日期格式，即格式：mm:ss。this.txb_ruyuantime(文本框）。
*/
--获取当前日期(如：yyyymmdd)
select CONVERT (nvarchar(12),GETDATE(),112)
--获取当前日期(如：yyyymmdd hh:MM:ss)
select GETDATE()
--获取当前日期(如：yyyy-mm-dd)
Select Datename(year,GetDate())+'-'+Datename(month,GetDate())+'-'+Datename(day,GetDate())
--获取当前日期(如：yyyy/mm/dd)
select DATENAME(YEAR,GETDATE())+'/'+DATENAME(MONTH,GETDATE())+'/'+DATENAME(DAY,GETDATE())
--获取几种日期
select DATENAME(YEAR,GETDATE()) --年份(YYYY)
select DATENAME(YY,GETDATE())
select DATENAME(MM,GETDATE())--月份
select DATENAME(DD,GETDATE()) --日期
select dateName(hh,getdate()) --获取小时
select DATENAME(MI,GETDATE()) --获取分钟
select DATENAME(SECOND,GETDATE()) --获取秒
select DATENAME(WEEK,GETDATE())--获取当前星期(周)是这一年中的第几个星期(周)
select DATENAME(WEEKDAY,GETDATE()) --星期几 

 //sql循环删除，执行时间为1240毫秒
set statistics time on;
declare @timer datetime = getdate();
delete from goods
select datediff(ms, @timer, getdate()) as [执行时间(毫秒)];
set statistics time off;

//sql通过字段,查找在哪张表中出现过
select sysobjects.name from syscolumns inner join sysobjects on syscolumns.id = sysobjects.id and sysobjects.xtype = 'u' where syscolumns.name = '字段名字'
//sql修改表字段类型和大小写转换
alter table 表名 alter column 字段名 int(修改的类型)
update Department(表名) set Dname(字段名) = lower(小写)(Dname)
update Department(表名) set Dname(字段名) = upper(大写)(Dname)
//sql备份
--- 创建 备份数据的 device
use BPMDB_OA
exec sp_addumpdevice 'disk', 'testback', 'D:\Program Files (x86)\Youdao'
--- 开始 备份
backup database BPMDB_OA to testback 
//sql根据已有的表创建新表： 
A：create table tab_new like tab_old (使用旧表创建新表)
B：create table tab_new as select col1,col2... from tab_old definition only
//sql增加一个列
Alter table tabname(表名) add  admins(字段名)  nvarchar(255) (字段类型)
//sql添加主键
Alter table tabname add primary key(col) 
//sql创建索引
create [unique] index idxname on tabname(col....) 
删除索引：drop index idxname
//sql创建视图
create view viewname as select statement 
删除视图：drop view viewname
-----------------------------sql几个简单的基本的sql语句--------------------------------
create table table1
(
field1 int primary key,
field2 nvarchar(200)
)
--选择：
select * from table1 where field1>5
--插入：
insert into table1(field1,field2) values('10','ouluote')
--删除：
delete from table1 where field1=10
--更新：
update table1 set field1=10 where field1=3 
--查找：
select * from table1 where field2 like '%yun%'  ---like的语法很精妙，自己博客有篇文章单独讲解!
--排序：
select * from table1 order by field2
--总数：
select count(*) as totalcount from table1
--求和：
select sum(field1) as sumvalue from table1
--平均：
select avg(field1) as avgvalue from table1
--最大：
select max(field1) as maxvalue from table1
--最小：
select min(field1) as minvalue from table1
-----------------------------------字符串函数-----------------------------------
--charindex 查找一个指定字符串在
 select charindex('a','name')  --另一个字符串中的起始位置 返回 2
--len 字符串长度 
select len('name') --返回 4
--ltrim 清除字符左边的空格
 select upper(' name ') --返回 "name "
--rtrim 清除字符右边的空格 
select rtrim('name               ')
--返回 " name"
--right 从字符串右边返回指定数目的字符 
select right('name',2)  --返回 me
--replace 替换一个字符串中的字符 
select replace('name','a','b')--返回 nbme
--stuff 在一个字符串中， 
select stuff('name',2,3,'bsp')--删除指定长度的字符， 返回 nbsp  并在该位置插入一个新的字符
------------------------------------------------数学函数----------------------------------------
--rand 从0-1之间的随机float值 
select rand()                 --返回 0.79854628433
--abs 取数值表达式的绝对值 
select abs(-43)               --返回 43
--ceiling 向上取整，取大于或等于 
select ceiling(43.5)         --返回 44  指定数值，表达式的最小整数
--floor 向下取整，取小于或等于 
select floor(43.5)           --返回 43   指定表达式的最大整数
--power 取数值表达式的幂值 
select power(5,2)            --返回 25
--round 将数值表达式四舍五入为指定精度 
select round(43.54,1)        --返回 43.50
--sign 正数返回+1，负数返回-1，0返回0 
select round(-43,2,9)        --返回 -1
--sqrt 取浮点表达式的平方根 
select sqrt(9)               --返回 3
--------------------------------------系统函数---------------------------------------
--convert 转换数据类型 
select convert(varchar(5),12345)  --返回 12345
--current_user 返回当前用户的名字 
--host_name 返回当前用户登录的计算机名字 
select host_name()               --DESKTOP-NSRKVED
--system_user 返回当前所登录的用户名称 
select system_user               --返回 sa /windows
--user_name 从给定的用户ID返回用户名 
select user_name(1)              --返回 从任意数据库中返回“dbo”
-----------------------------------多分支条件判断---------------------------------
create table t_students
(
pid  int primary key identity,
pname varchar(50),
moneys decimal
)
insert into t_students values
('hyx','8300'),
('jj','3500'),
('yq','8500'),
('zsy','1000')
select p.*,(case
            when moneys<5000 and moneys>2000 then'低薪阶层' 
            when moneys>=5000 and moneys<=10000 then'中薪阶层' 
            when moneys>10000 then'高薪阶层' 
            else'实习生' END) grade from t_students p;
            
---------------------------having运用(having必须搭配 group by使用)---------------------------
create table classes
(
cid int primary key identity,
tname varchar(50),
tsum int,
tase float
)
-- 列出班级人数小于等于30个人的班级 
SELECT tsum,COUNT(*) FROM classes GROUP BY tsum HAVING COUNT(*)<=30; 
-- 列出班级总分大于800分的班级 
SELECT cid,SUM(tase) FROM classes GROUP BY cid HAVING SUM(tase)>=800;
//sql全球唯一标识符的定义及使用
declare @myid uniqueidentifier
set @myid = newid()
print 'value of @myid is: '+ convert(varchar(255), @myid)
//原有表中创建新表,并导入数据
导入数据并生成表
select * into ykd(新表名) from yuncut(原有表) 
-------------------------------------数据库备份与还原-----------------------------------
--使用SQL最简单备份,还原数据库
//备份 
 backup database master to disk='D:\app'
// 还原 
 restore database Test from disk='D:\app'
//为了方便以后的使用,开始对语句进行简单的封装->存储过程
// 备份数据库
if exists(select 1 from sys.procedures where name='sp_BackupDB')
    drop procedure sp_BackupDB
go
create procedure sp_BackupDB
    @savePath nvarchar(4000) -- 备份数据库保存位置(目录)
    ,@dbName nvarchar(4000) -- 需要进行备份的数据库
    ,@bakName nvarchar(4000) -- 备份文件的名称(不含扩展名)
as begin
    declare @sql nvarchar(4000)
    /* 验证路径 */
    if(charindex('/',reverse(@savePath))!=1) begin
        set @savePath=@savePath+'/'
    end
    /* 拼SQL并执行 */
    set @sql='backup database '+@dbName+' to disk='''+@savePath+@bakName+'.bak'''
    exec sp_executesql @sql

    /* 返回执行结果(1=成功,0=失败) */
    if(@@error=0) begin
        return 1
    end
    return 0
end
//还原数据库
  if exists(select 1 from sys.procedures where name='sp_RestoreDB')
      drop procedure sp_RestoreDB
  go
  create procedure sp_RestoreDB
     /* 数据库还原后的保存位置(目录)(使用系统默认保存位置:-1) */
      @savePath nvarchar(4000)
     ,@backFile nvarchar(4000) -- 需要还原的数据库备份文件
     ,@defaultName nvarchar(4000) -- 数据库原始名称(备份的原数据库名称)不包含扩展名
    /* 为数据库重命名(使用数据库默认名称:-1)不包含扩展名
       如果目录已存在该名称的数据库,将会被覆盖 */
     ,@dbName nvarchar(4000)
 as begin
     declare @newName nvarchar(4000),@sql nvarchar(4000)
    /* 获取数据库名称 */
     if(@dbName='-1') begin
         set @newName=@defaultName
     end else begin
        set @newName=@dbName
     end
     /* 结束所有对当前数据库的连接 */
    if exists(select 1 from sys.sysprocesses where dbid=db_id(@defaultName)) begin
        declare #cs_spid cursor -- 声明游标
         for
        select #cs_spid=convert(varchar,spid) from sys.sysprocesses where dbid=db_id(@defaultName)
         open #cs_spid
            declare @spid varchar(20)
           fetch next from #cs_spid into @spid -- 赋值并前进到下一条
            while(@@fetch_status=0) begin -- 在fetch失败前执行
              exec ('kill '+@spid) -- 结束对操作库的连接(exec执行SQL语句1)
                fetch next from #cs_spid into @spid
           end
       close #cs_spid
         deallocate #cs_spid -- 释放游标
     end
     /* 创建执行语句   */
     set @sql='restore database '+@newName+' from disk='''+@backFile+''' with replace'
     if(@savePath!='-1') begin
         -- 验证路径
        if(charindex('/',reverse(@savePath))!=1) begin
             set @savePath=@savePath+'/'
         end
      set @sql=@sql+', move '''+@defaultName+''' to '''+@savePath+@newName+'.mdf'''
        set @sql=@sql+', move '''+@defaultName+'_log'' to '''+@savePath+@newName+'_log.ldf'''
    end
     /* 执行操作 */
     exec sp_executesql @sql -- (exec执行SQL语句2)
     /* 返回执行结果(1=成功,0=失败) */
     if(@@error=0) begin
        return 1
     end
   return 0
 end
-----------------------------------------游标基础-----------------------------------------------
declare stu cursor scroll   --声明游标(scroll游标方向)
for
select id,name,xh from xsb  --声明游标表
go
open stu                    --打开游标
go
fetch next from stu         --读取当前行的下一行，并使其置为当前行(刚开始时游标置于表头的前一行，即若表是从0开始的，游标最初置于-1处，所以第一次读取的是头一行)
fetch prior from stu        --读取当前行的前一行,并使其置为当前行
fetch first from stu        --读取游标的第一行，并使其置为当前行（不能用于只进游标）
fetch last from stu         --读取游标的最后一行，并使其置为当前行（不能用于只进游标）
fetch absolute 2 from stu   --读取从游标头开始向后的第2行，并将读取的行作为新的行
fetch relative 3 from stu   --读取从当前行开始向后的第3行，并将读取的行作为新的行
fetch relative-2 from stu   --读取当前行的上两行，并将读取的行作为新的行
--读取数据结束
go
close stu                   --关闭游标
go
deallocate stu              --删除游标
go
-----------------------------------------触发器----------------------------------------
create table inserted
(
CommodityId   int primary key identity,
Commodityname  varchar(50),
picture   int null,
inprice   int,
outprice  int,
amount  int
)
create table CommodityInfo
(
Commodityinfoid  int primary key identity,
Commodityinfoname  varchar(50)
)
alter table CommodityInfo add Amount  int      --CommodityInfo  增加一个字段

IF EXISTS (SELECT *FROM sysobjects WHERE name='tr_SaleCommodity')
    DROP TRIGGER tr_SaleCommodity
GO
CREATE TRIGGER tr_SaleCommodity
ON inserted FOR INSERT      --FOR/AFTER为后触发器(添加数据后触发)
AS
    BEGIN
        IF EXISTS (
            SELECT  * FROM inserted I INNER JOIN CommodityInfo C ON I.CommodityId=C.Commodityinfoid
            where i.amount>c.Amount
        )
            BEGIN
                ROLLBACK  --后触发器
                PRINT '商品的销售量大于商品的库存量'
            END    
        ELSE
            BEGIN
                UPDATE CommodityInfo
                SET Amount=Amount-(SELECT Amount FROM inserted)
                WHERE Commodityinfoid IN
                (
                    SELECT CommodityId FROM inserted
                )
            END
    END
GO
insert into inserted values
('ijw',10,20,30,40)
select * from inserted
-----------------------------------------------------------------------------------------
--1.从一张表中选数据插入到另一张表中
select name,age,email(原有表字段名称) into new_table_name(新表名) from old_table_name(原有表名) --new_table_name表不必事先存在（复制旧表的架构和数据）
select * into table_new(新表名) from old_table_name(原有表名) where old_table_name.id=1(条件)   --只复制旧表架构(条件可有可无)

--2.identity与select into合用   插入行号
set identity_insert old_table_name on
insert old_table_name (id,name,age,email) values(1,'hxp',25,'hxp@163.com')
set identity_insert old_table_name off  --一张表中只能有一列为identity，如果指定了自增列，又需要插入指定id的值，需要停止indentiy，执行后再开启

select IDENTITY(int,1001,1)(id自增指定从哪开始) as name,age,email (字段)
into old_table_name_backup(新表名)  from old_table_name(原有表)

--3.再有identity列的插入时,需要返回当前行的identity的列值(SCOPE_IDENTITY()函数)
insert into old_table_name(email) values('测试')
select SCOPE_IDENTITY()

--4.返回架构范围内对象的数据库对象标识号：object_id()
--查询表是否存在
select OBJECT_ID('old_table_name')                     --存在有值  没有 null
select id from sysobjects where name ='old_table_name' --同上
---------------------------------------Sql事务-------------------------------------------------
```csharp
create table lives
(
id int primary key identity,
Eat varchar(50),
Play varchar(50),
Numb int
)
---开启事务
begin tran
--错误捕捉机制，看好啦，这里也有的。并且可以嵌套。
begin try    
   --语句正确
   insert into lives (Eat,Play,Numb) values ('李四','足球',1)   
    --加入保存点
   save tran pigOneIn
   insert into lives (Eat,Play,Numb) values ('张三','足球',2)
   insert into lives (Eat,Play,Numb) values ('王五','篮球',3)
end try
begin catch
   select Error_number() as ErrorNumber,  --错误代码
          Error_severity() as ErrorSeverity,  --错误严重级别，级别小于10 try catch 捕获不到
          Error_state() as ErrorState ,  --错误状态码
          Error_Procedure() as ErrorProcedure , --出现错误的存储过程或触发器的名称。
          Error_line() as ErrorLine,  --发生错误的行号
          Error_message() as ErrorMessage  --错误的具体信息
   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
      rollback tran   
end catch
if(@@trancount>0)
rollback tran pigOneIn 

--表本身为空表，ID ,Numb为int 类型，其它为nvarchar类型
select * from lives
--------------------------------------------------------------------------
begin transaction                         --开始事务
declare @sumerror int =0                  --定义局部变量
update lives set Numb=999 where id=7     
set @sumerror=@sumerror+@@error
update lives set Eat='王五' where id=28    
set @sumerror=@sumerror+@@error
if(@sumerror<>0)
begin
    --失败了
    print '数据有误,请联系管理员'
    rollback transaction        --失败回滚
end
else
begin
    --成功了
    print '提交成功'
    commit transaction         --成功提交
end  
---------------------------------事务经典案例,银行汇款--------------------------
begin transaction        --开始事务
declare @errorSum int    --定义局部变量，用于累计事务执行过程中的错误
set @errorSum =0         --给变量赋初值,用于后期判断是否成功 ,变量大于0就是失败 
/*--转帐--*/
update lives set Numb=Numb-800 where Eat='张三'     --张三扣款800
set @errorSum=@errorSum+@@error                     --累计是否有错误
update lives set Numb=Numb+800 where Eat='李四'     --李四收入800
set @errorSum=@errorSum+@@error                     --累计是否有错误
print '查看转帐事务过程中的余额'
select * from lives                                 --汇款前先查看一下账户                            
/*--根据是否有错误，确定事务是提交还是回滚--*/
if (@errorSum<>0)
    begin
        print '交易失败，回滚事务.'
        rollback transaction                        --回滚事务
    end
else
    begin
        print '交易成功，提交事务，写入硬盘，永久保存！'
        commit transaction                         --提交事务
    end
go
print '查看转帐后的余额'
select * from lives
go

---