开发者社区> 多吉> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

sql基础

简介: 1 use master 2 go 3 if(exists (select * from sys.databases where name = 'testHome')) 4 drop database testHome 5 go 6 cre...
+关注继续查看
   1 use master
   2 go
   3 if(exists (select * from sys.databases where name = 'testHome'))
   4     drop database testHome
   5 go
   6 create database testHome
   7 on(
   8     name = 'testHome',
   9     filename = 'c:\testHome.mdf'
  10 )
  11 log on(
  12     name = 'testHome_Log',
  13     filename = 'c:\testHome=Log.ldf'
  14 )
  15 go
  16 if(exists (select * from sys.databases where name = 'testHome'))
  17     drop database testHome
  18 go
  19 create database testHome
  20 on primary(
  21     name = 'testHome_data',
  22     fileName = 'c:\testHome_data.mdf',
  23     size = 3mb,
  24     maxSize = 50mb,
  25     fileGrowth = 10%
  26 )
  27 log on(
  28     name = 'testHome_log',
  29     fileName = 'c:\testHome_log.ldf',
  30     size = 1mb,
  31     fileGrowth = 1mb
  32 )
  33 go
  34 if (exists (select * from sys.databases where name = 'testHome'))
  35     drop database testHome
  36 go
  37 create database testHome
  38 on primary(
  39     name = 'testHome_data',
  40     filename = 'c:\testHome_data.mdf',
  41     size = 10mb,
  42     maxSize = 50mb,
  43     fileGrowth = 10%
  44 ),(
  45     name = 'testHome2_data',
  46     filename = 'c:\testHome2_data.mdf',
  47     size = 10mb,
  48     maxsize = 50mb,
  49     fileGrowth = 10%
  50 )log on(
  51     name = 'testHome_log',
  52     filename = 'c:\testHome_log.log',
  53     size = 1mb,
  54     fileGrowth = 1mb
  55 ),(
  56     name = 'testHome2_log',
  57     filename = 'c:\testHome2_log.log',
  58     size = 1mb,
  59     fileGrowth = 1mb
  60 )go
  61 if (exists (select * from sys.objects where name = 'classes'))
  62     print '存在';
  63 go
  64 if (exists (select  * from sys.objects where object_id = object_id('student')))
  65     print '存在';
  66 go
  67 if(object_id('student','U') is not null)
  68     print '存在';
  69 go
  70 if (exists (select * from sys.columns where object_id = object_id('student') and name='idCard'))
  71     alter table student drop column idCard
  72 go
  73 if(exists (select * from information_schema.columns where table_name = 'student' and column_name = 'tel'))
  74     alter table student drop column tel
  75 go
  76 if(exists (select * from sys.objects where name = 'classes'))
  77     drop table classes
  78 go
  79 create table classes(
  80     id int primary key identity(1,2),
  81     name varchar(22) not null,
  82     createDate datetime default getDate()
  83 )
  84 go
  85 if(exists (select * from sys.objects where object_id = object_id('dtudent')))
  86     drop table student
  87 go
  88 create table student(
  89     id int identity(1,1) not null,
  90     name varchar(20),
  91     age int,
  92     sex bit,
  93     cid int
  94 )go
  95 alter table student add address varchar(50) not null;
  96 alter table student alter column address varchar(20);
  97 alter table student drop column number;
  98 
  99 alter table student
 100 add address varchar(22),
 101     tel varchar(11),
 102     idCard varchar(3);
 103 
 104 if(exists (select * from sys.columns where object_id = object_id('student') and name = 'idCard'))
 105     alter table student drop column idCard
 106 go
 107 if(exists (select * from information_schema.columns where table_name = 'student' and column_name = 'tel'))
 108     alter table student drop column tel
 109 go
 110 
 111 alter table studet
 112     add number varchar(20) null constraint no_uk unique;
 113 
 114 alter table student
 115     add constraint pk_id primary key(id);
 116 
 117 alter table student
 118     add constraint fk_cid foreign key(cid) references classes(id)
 119 go
 120 
 121 alter table student
 122     add constraint name_uk unique(name);
 123 
 124 alter table student with nocheck
 125     add constraint check_age check (age > 1);
 126 alter table student 
 127     add constraint ck_age check (age >= 15 and age <= 50)
 128 
 129 alter table student
 130     add constraint sex_def default 1 for sex;
 131 
 132 alter table student
 133     add createDate smalldatetime null
 134     constraint createDate_def default getDate() with values;
 135 
 136 alter table student add
 137     id int identity constraint id primary key,
 138     number int null
 139     constraint uNumber references classes(number),
 140     createDate decimal(3,3)
 141     constraint createDate default 2010-6-1
 142 go
 143 
 144 alter table student drop constraint no_uk;
 145 
 146 insert into classes(name) values('1班');
 147 insert into classes values('2班','2011-6-15');
 148 insert into classes(name) values('3班');
 149 insert into classes values('4班',default);
 150 
 151 insert into student values('zhangsan',22,1,1);
 152 insert into student values('lisi',25,0,1);
 153 insert into student values('wangwu',24,1,3);
 154 insert into student values('zhaoliu',23,0,3);
 155 insert into student values('mazi',21,1,5);
 156 insert into student values('wangmazi',28,0,5);
 157 insert into student values('jason',null,0,5);
 158 insert into student values(null,null,0,5);
 159 
 160 insert into student
 161 select 'bulise' name,age,sex,cid
 162 from student
 163 where name = 'tony';
 164 
 165 insert into student
 166     select 'jack',23,1,5 union
 167     select 'tom',24,0,3 union
 168     select 'wendy',25,1,3 union
 169     select 'tony',26,0,5;
 170 
 171 select * from classes;
 172 select * from student;
 173 select 8d,'bulise' name,age,sex,cid from student
 174 where name = 'tony';
 175 select *,(select max(age) from student) from student
 176 where name = 'tony';
 177 
 178 update student set name='hoho',sex=1 where id =1;
 179 
 180 delete from student where id=1;
 181 
 182 select * into stu from student;
 183 select * into stu1 from (select * from stu) t;
 184 select * from stu;
 185 select * from stu1;
 186 
 187 exec sp_help student;
 188 exec sp_help classes;
 189 
 190 declare @id char(10)
 191 declare @age int
 192     select @id = 22
 193     set @age = 55
 194     print convert(char(10),@age) + '#' + @id
 195     select @age,@id
 196 go
 197 
 198 declare @name varchar(20);
 199 declare @result varchar(200);
 200 set @name = 'jack';
 201 set @result = @name + ' say: hellw world!';
 202 select @result
 203 go
 204 
 205 declare @id int, @name varchar(20);
 206 set @id = 1;
 207 select @name = name from student where id = @id;
 208 select @name;
 209 go
 210 
 211 declare @name varchar(20);
 212 select @name = 'jack';
 213 select * from student where name = @name;
 214 go
 215 
 216 select @@identity;
 217 select identity(int,1,1) as id into tab from student;
 218 select * from tab;
 219 select @@rowcount;
 220 select @@cursor_rows;
 221 select @@error;
 222 select @@procid;
 223 
 224 set datefirst 1;
 225 select @@datefirst as '星期的第一天', datepart(dw,getDate()) as '今天是星期';
 226 select @@dbts;
 227 select @@lock_timeout;
 228 select @@max_connections;--同时进行的最大用户链接数
 229 select @@max_precision as  'Max Precision';--返回decimal和numeric数据类型所用的精度级别
 230 select @@servername;
 231 select @@servicename;
 232 select @@spid;--当前会话进程Id
 233 select @@textSize;
 234 select @@version;
 235 
 236 select @@connections;
 237 select @@pack_received;
 238 select @@cpu_busy;
 239 select @@pack_sent;
 240 select @@timeticks;
 241 select @@idle;
 242 select @@total_errors;
 243 select @@io_busy;
 244 select @@total_read;--读取磁盘次数
 245 select @@packet_errors;--发生的网络数据包错误数
 246 select @@total_write;--sqlserver执行的磁盘写入次数
 247 
 248 select 1 +2;
 249 select @@language;
 250 select user_name();
 251 
 252 print 1+2;
 253 print @@language;
 254 print user_name();
 255 --print在输出值不少字符串的情况下,需要用convet转换成字符串才能正常输入,而且字符串的长度在超过8000的字符以后,后面的将不会显示。
 256 
 257 if 2 > 3
 258     print '2 > 3';
 259 else 
 260     print '2 < 3';
 261 
 262 if(2 > 3)
 263     print '2 > 3';
 264 else if(3 > 2)
 265     print '3 > 2';
 266 else
 267     print 'other';
 268 
 269 declare @id char(10),
 270         @pid char(20),
 271         @name varchar(20);
 272 set @name = '广州';
 273 select @id = id from ab_area where areaName = @name;
 274 select @pid = pid from ab_area where id = @id;
 275 print @id + '#' + @pid;
 276 
 277 if @pid > @id
 278     begin
 279         print @id + '%';
 280         select * from ab_area where pid like @id + '%';
 281     end
 282 else
 283     begin
 284         print @id + '%';
 285         print @id + '#' + @pid;
 286         select * from ab_area where pid = @pid;
 287     end
 288 go
 289 
 290 declare @i int;
 291     set @i = 1;
 292 while(@i < 11)
 293     begin
 294         print @i;
 295         set @i = @i + 1;
 296     end
 297 go
 298 
 299 declare @i int;
 300     set @i = 1;
 301 while(@i < 11)
 302     begin
 303         if(@i < 5)
 304             begin
 305                 set @i = @i + 1;
 306                 continue;
 307             end
 308         print @i;
 309         set @i = @i + 1;
 310     end
 311 go
 312 
 313 declare @i int;
 314     set @i = 1;
 315 while(1=1)
 316     begin
 317         print @i;
 318         if(@i >= 5)
 319             begin
 320                 set @i = @i + 1;
 321                 break;
 322             end
 323         set @i = @i + 1;
 324     end
 325 go
 326 
 327 select *,
 328     case sex
 329         when 1 then ''
 330         when 0 then ''
 331         else '火星人'
 332     end as '性别'
 333 from student;
 334 
 335 select areaName,'区域类型'=case
 336         when areaType = '' then areaName + areaType
 337         when areaType = '' then 'city'
 338         when areaType = '' then 'area'
 339         else 'other'
 340     end
 341 from ab_area;
 342 
 343 use master
 344 go
 345 
 346 --延时执行,类似于定时器、休眠等
 347 waitfor delay '00:00:03';--定时三秒后执行
 348 print '定时三秒后执行';
 349 
 350 select * from student;
 351 
 352 select all sex from student;
 353 select distinct sex from student;
 354 select count(*) from student;
 355 select count(sex) from student;
 356 select count(distinct sex) from student;
 357 
 358 select top 3 * from student;
 359 select id as 编号,name  '名称',sex 性别 from student;
 360 select id,name,s.id,s.name from student s;
 361 
 362 select (age + id) col from student;
 363 select s.name + '-' + c.name from classes c,student s where s.cid = c.id;
 364 
 365 select * from student where id =2;
 366 select * from student where id >7;
 367 select * from student where id < 3;
 368 select * from student where id<>3;
 369 select * from student where id >=3
 370 select * from student where id<=5;
 371 select * from student where id!>3;
 372 select * from student where id!<5;
 373 select * from student where id > 2 and sex = 1;
 374 select * from student where id =2 or sex =1;
 375 select * from student where id between 2 and 5;
 376 select * from student where id not between 2 and 5;
 377 select * from student where name like '%a%';
 378 select * from student where name like '%[a][o]%';
 379 select * from student where name not like '%a%';
 380 select * from student where name like 'ja%';
 381 select * from student where name not like '%[j,n]%';
 382 select * from student where name like '%[j,n,a]%';
 383 select * from student where name like '%[^ja,as,on]%';
 384 select * from student where name like '%[ja_on]%';
 385 select * from student where id in (1,2);
 386 select * from student where id not in (1,2);
 387 select * from student where age is null;
 388 select * from student where age is not null;
 389 select * from student order by name;
 390 select * from student order by name desc;
 391 select * from student order by name asc;
 392 select count(age),age from student group by age;
 393 select count(*),sex from student group by sex;
 394 select count(*),sex from student group by sex,age order by age;
 395 select count(*),sex from student where id>2 group by sex order by sex;
 396 select count(*),(sex*id) new from student where id>2 group by sex*id order by sex*id;
 397 select count(*),age from student group by all age;
 398 select count(*),age from student group by age having age is not null;
 399 select count(*),cid,sex from student group by cid,sex having cid > 1;
 400 select count(*),age from student group by age having count(age)>=2;
 401 select count(*),cid,sex from student group by cid,sex having cid>1 and max(cid)>2;
 402 
 403 select * from (
 404     select id,name from student where sex =1
 405 ) t where t.id > 2;
 406 
 407 select *,(select count(*) from student where cid = classes.id) as num
 408 from classes order by num;
 409 select * from student where cid in(
 410     select id from classes where id > 2 and id < 4
 411 );
 412 select * from student where cid not in(
 413     select id from classes where name ='2班'
 414 );
 415 select * from student where exists(
 416     select * from classes where id =student.cid and id=3
 417 );
 418 select * from student where not exists(
 419     select * from classes where id = student.cid
 420 );
 421 select * from student where cid=5 and age > all (
 422     select age from student where cid =3 
 423 );
 424 select * from student where cid =5 and age > any (
 425     select age from student where cid =3
 426 );
 427 select * from student where cid =5 and age > some (
 428     select age from student where cid =3 
 429 );
 430 select distinct sex from student;
 431 select count(sex),count(distinct sex) from student;
 432 
 433 select age from student
 434 where age > 20 order by age compute sum(age) by age;
 435 
 436 select id,sex,age from student
 437 where age > 20 order by sex,age compute sum(age) by sex;
 438 
 439 select age from student
 440 where age > 20 order by age,id compute sum(age);
 441 
 442 select id,age from student
 443 where age > 20 order by age compute sum(age),max(id);
 444 
 445 select count(*),sex from student group by sex with cube;
 446 select count(*),age,sum(age) from student where age is not null group by  age with cube;
 447 
 448 select s.id,s.name ,cid,c.name,row_number() over(order by c.name) as number
 449 from student s,classes c where cid=c.id;
 450 select id,name,rank() over(order by cid) as rank from student;
 451 select s.id,s.name,cid,c.name,rank() over(order by c.name) as rank
 452 from student s,classes c where cid=c.id;
 453 select s.id,s.name,cid,c.name,dense_rank() over(order by c.name) as dense
 454 from student s,classes c where cid=cid;
 455 select s.id,s.name,cid,c.name,row_number() over(partition by c.name order by s.id) as rank
 456 from student s,classes c where cid=c.id;
 457 select s.id,s.name,cid,c.name,rank() over(partition by c.name order by s.id) as rank
 458 from student s,classes c where cid=c.id;
 459 select s.id,s.name,cid,c.name,dense_rank() over (partition by c.name order by s.id) as rank 
 460 from student s,classes c where cid = c.id;
 461 select s.id,s.name,cid,c.name,ntile(5) over(order by c.name) as ntile
 462 from student s,classes c where cid=c.id;
 463 
 464 select id,name from student where name like 'ja%'
 465 union--并集
 466 select id,name from student where id=4;
 467 
 468 select * from student where name like 'ja%'
 469 union all
 470 select * from student;
 471 
 472 select * from student where name like 'ja%'
 473 intersect--交集
 474 select * from student;
 475 
 476 select * from student where name like 'ja%'
 477 except--减集(除相同部分)
 478 select * from student where name like 'jas%';
 479 
 480 --公式表表达式(查询表的时候,有时候中间表需要重复使用,这些子查询被重复查询调用,不但效率低,而且可读性地,不利于理解。那么公示表表达式可以解决这个问题)
 481 --我们可以将公示表表达式(CET)视为临时结果集,在select,inset,update,delete或者create view 语句的执行范围内进行定义
 482 with statNum(id,num) as(
 483     select cid,count(*)
 484     from student
 485     where id > 0
 486     group by cid
 487 )
 488 select id,num from statNum order by id;
 489 
 490 with statNum(id,num) as(
 491     select cid,count(*)
 492     from student
 493     where id > 0
 494     group by cid
 495 )
 496 select max(id),avg(num) from statNum;
 497 
 498 select s.id,s.name,c.id,c.name from student s,classes c where s.cid = c.id;
 499 select s.id,s.name,c.id,c.name from student s left join classes c on s.cid = c.id;
 500 select s.id,s.name,c.id,c.name from student s right join classes c on s.cid = c.id;
 501 select s.id,s.name,c.id,c.name from student s inner join classes c on s.cid = c.id;
 502 select s.id,s.name,c.id,c.name from student s join classes c on s.cid = c.id;
 503 select s.id,s.name,c.id,c.name from student s cross join classes c where s.cid=c.id;--交叉链接查询,结果是一个笛卡尔乘积
 504 select distinct s.* from student s,student s1 where s.id <> s1.id and s.sex = s1.sex;--自连接
 505 
 506 select 
 507     max(age) max_age,
 508     min(age) min_age,
 509     count(age) count_age,
 510     avg(age) avg_age,
 511     sum(age) sum_age,
 512     var(age) var_age
 513 from student;
 514 
 515 select dateAdd(day,3,getDate());--加天
 516 select dateAdd(year,3,getDate());--加年
 517 select dateAdd(hour,3,getDate());--加小时
 518 --返回跨两个指定日期的日期边界数和时间边界数
 519 select dateDiff(day,'2016-12-19',getDate());
 520 select dateDiff(second,'2017-12-19 11:40:00',getDate());--相差秒数
 521 select dateDiff(hour,'2017-12-19 08:00:00',getDate());--相差小时数
 522 select dateName(month,getDate());--当前月份
 523 select dateName(minute,getdate());--当前分钟
 524 select dateName(weekday,getDate());--当前星期
 525 select dateName(second,getDate());--当前秒数
 526 select day(getDate());--返回当前日期天数
 527 select day('2011-06-30')
 528 select month(getDate())
 529 select month('2017-12-19')
 530 select year(getDate())
 531 select year('2016-11-11')
 532 select getdate();
 533 select getUTCDate();
 534 
 535 select pi();
 536 select rand(100),rand(50),rand(),rand();
 537 select round(rand(),3),round(rand(100),5)--精确小数位
 538 --精确位数,负数表示小数点前
 539 select round(123.456,2),round(254.124,-2);
 540 select round(123.4657,1,2);
 541 
 542 select col_name(object_id('student'), 1);--返回列名
 543 select col_name(object_id('student'), 2);
 544 --该列数据类型长度
 545 select col_length('student', col_name(object_id('student'), 2)); 
 546 --该列数据类型长度
 547 select col_length('student', col_name(object_id('student'), 1)); 
 548 --返回类型名称、类型id
 549 select type_name(type_id('varchar')), type_id('varchar');
 550 --返回列类型长度
 551 select columnProperty(object_id('student'), 'name', 'PRECISION');
 552 --返回列所在索引位置
 553 select columnProperty(object_id('student'), 'sex', 'ColumnId');
 554 
 555 select ascii('a');--字符转换ascii值
 556 select ascii('A');
 557 select char(97);--ascii值转换字符
 558 select char(65);
 559 select nchar(65);
 560 select nchar(45231);
 561 select nchar(32993);--unicode转换字符
 562 select unicode('A'), unicode('');--返回unicode编码值
 563 select soundex('hello'), soundex('world'), soundex('word');
 564 select patindex('%a', 'ta'), patindex('%ac%', 'jack'), patindex('dex%', 'dexjack');--匹配字符索引
 565 select 'a' + space(2) + 'b', 'c' + space(5) + 'd';--输出空格
 566 select charIndex('o', 'hello world');--查找索引
 567 select charIndex('o', 'hello world', 6);--查找索引
 568 select quoteName('abc[]def'), quoteName('123]45');
 569 --精确数字
 570 select str(123.456, 2), str(123.456, 3), str(123.456, 4);
 571 select str(123.456, 9, 2), str(123.456, 9, 3), str(123.456, 6, 1), str(123.456, 9, 6);
 572 select difference('hello', 'helloWorld');--比较字符串相同
 573 select difference('hello', 'world');
 574 select difference('hello', 'llo');
 575 select difference('hello', 'hel');
 576 select difference('hello', 'hello');
 577 select replace('abcedef', 'e', 'E');--替换字符串
 578 select stuff('hello world', 3, 4, 'ABC');--指定位置替换字符串
 579 select replicate('abc#', 3);--重复字符串
 580 select subString('abc', 1, 1), subString('abc', 1, 2), subString('hello Wrold', 7, 5);--截取字符串
 581 select len('abc');--返回长度
 582 select reverse('sqlServer');--反转字符串
 583  
 584 select left('leftString', 4);--取左边字符串
 585 select left('leftString', 7);
 586 select right('leftString', 6);--取右边字符串
 587 select right('leftString', 3);
 588 select lower('aBc'), lower('ABC');--小写
 589 select upper('aBc'), upper('abc');--大写
 590 --去掉左边空格
 591 select ltrim(' abc'), ltrim('# abc#'), ltrim('  abc');
 592 --去掉右边空格
 593 select rtrim(' abc    '), rtrim('# abc#   '), rtrim('abc');
 594 
 595 select current_user;
 596 select user;
 597 select user_id(), user_id('dbo'), user_id('public'), user_id('guest');
 598 select user_name(), user_name(1), user_name(0), user_name(2);
 599 select session_user;
 600 select suser_id('sa');
 601 select suser_sid(), suser_sid('sa'), suser_sid('sysadmin'), suser_sid('serveradmin');
 602 select is_member('dbo'), is_member('public');
 603 select suser_name(), suser_name(1), suser_name(2), suser_name(3);
 604 select suser_sname(), suser_sname(0x01), suser_sname(0x02), suser_sname(0x03);
 605 select is_srvRoleMember('sysadmin'), is_srvRoleMember('serveradmin');
 606 select permissions(object_id('student'));
 607 select system_user;
 608 select schema_id(), schema_id('dbo'), schema_id('guest');
 609 select schema_name(), schema_name(1), schema_name(2), schema_name(3);
 610  
 611 select app_name();--当前会话的应用程序名称
 612 select cast(2011 as datetime), cast('10' as money), cast('0' as varbinary);--类型转换
 613 select convert(datetime, '2011');--类型转换
 614 select coalesce(null, 'a'), coalesce('123', 'a');--返回其参数中第一个非空表达式
 615 select collationProperty('Traditional_Spanish_CS_AS_KS_WS', 'CodePage');
 616 select current_timestamp;--当前时间戳
 617 select current_user;
 618 select isDate(getDate()), isDate('abc'), isNumeric(1), isNumeric('a');
 619 select dataLength('abc');
 620 select host_id();
 621 select host_name();
 622 select db_name();
 623 select ident_current('student'), ident_current('classes');--返回主键id的最大值
 624 select ident_incr('student'), ident_incr('classes');--id的增量值
 625 select ident_seed('student'), ident_seed('classes');
 626 select @@identity;--最后一次自增的值
 627 select identity(int, 1, 1) as id into tab from student;--将studeng表的烈属,以/1自增形式创建一个tab
 628 select * from tab;
 629 select @@rowcount;--影响行数
 630 select @@cursor_rows;--返回连接上打开的游标的当前限定行的数目
 631 select @@error;--T-SQL的错误号
 632 select @@procid;
 633  
 634 set datefirst 7;--设置每周的第一天,表示周日
 635 select @@datefirst as '星期的第一天', datepart(dw, getDate()) AS '今天是星期';
 636 select @@dbts;--返回当前数据库唯一时间戳
 637 set language 'Italian';
 638 select @@langId as 'Language ID';--返回语言id
 639 select @@language as 'Language Name';--返回当前语言名称
 640 select @@lock_timeout;--返回当前会话的当前锁定超时设置(毫秒)
 641 select @@max_connections;--返回SQL Server 实例允许同时进行的最大用户连接数
 642 select @@MAX_PRECISION AS 'Max Precision';--返回decimal 和numeric 数据类型所用的精度级别
 643 select @@SERVERNAME;--SQL Server 的本地服务器的名称
 644 select @@SERVICENAME;--服务名
 645 select @@SPID;--当前会话进程id
 646 select @@textSize;
 647 select @@version;--当前数据库版本信息
 648  
 649 select @@CONNECTIONS;--连接数
 650 select @@PACK_RECEIVED;
 651 select @@CPU_BUSY;
 652 select @@PACK_SENT;
 653 select @@TIMETICKS;
 654 select @@IDLE;
 655 select @@TOTAL_ERRORS;
 656 select @@IO_BUSY;
 657 select @@TOTAL_READ;--读取磁盘次数
 658 select @@PACKET_ERRORS;--发生的网络数据包错误数
 659 select @@TOTAL_WRITE;--sqlserver执行的磁盘写入次数
 660 select patIndex('%soft%', 'microsoft SqlServer');
 661 select patIndex('soft%', 'software SqlServer');
 662 select patIndex('%soft', 'SqlServer microsoft');
 663 select patIndex('%so_gr%', 'Jsonisprogram');
 664 
 665 --查询所有已创建函数
 666 select definition,* from sys.sql_modules m join sys.objects o on m.object_id = o.object_id
 667 and type in('fn', 'if', 'tf');
 668 --创建函数
 669 if (object_id('fun_add','fn') is not null)
 670     drop function fun_add
 671 go
 672 create function fun_add(@num1 int,@num2 int)
 673     returns int
 674 with execute as caller
 675 as 
 676     begin
 677         declare @result int;
 678         if(@num1 is null)
 679             set @num1 =0;
 680         if(@num2 is null)
 681             set @num2 = 0;
 682         set @result = @num1 + @num2;
 683         return @result;
 684     end
 685 go
 686 --调用函数
 687 select dbo.fun_add(id, age) from student;
 688 --自定义函数,字符串连接
 689 if(object_id('fun_append','fn') is not null)
 690     drop function fun_append
 691 go
 692 create function fun_append(@args nvarchar(1024),@args2 nvarchar(1024))
 693     returns nvarchar(2048)
 694 as
 695     begin
 696         return @args + @args2;
 697     end
 698 go
 699 select dbo.fun_append(name,'abc') from student;
 700 --修改函数
 701 alter function fun_append(@args nvarchar(1024),@args2 nvarchar(1024))
 702     returns nvarchar(1024)
 703 as
 704     begin
 705         declare @result varchar(1024);
 706         --coalesce返回第一个部位null的值
 707         set @args = coalesce(@args,'');
 708         set @args2 = coalesce(@args,'');
 709         set @result = @args + @args;
 710         return @result;
 711     end
 712 go
 713 select dbo.fun_append(name,'#abc') from student;
 714 --返回table对象函数
 715 select name, object_id, type from sys.objects where type in ('fn', 'if', 'tf') or type like '%f%';
 716  
 717 if (exists (select * from sys.objects where type in ('fn', 'if', 'tf') and name = 'fun_find_stuRecord'))
 718     drop function fun_find_stuRecord
 719 go
 720 create function fun_find_stuRecord(@id int)
 721     returns table
 722 as
 723     return (select * from student where id = @id);
 724 go
 725  
 726 select * from dbo.fun_find_stuRecord(2);
 727 
 728 --联合索引
 729 if (exists (select * from sys.indexes where name = 'idx_uqe_clu_stu_name_age'))
 730     drop index student.idx_uqe_clu_stu_name_age
 731 go
 732 create unique clustered index idx_uqe_clu_stu_name_age
 733 on student(name, age);
 734  
 735 if (exists (select * from sys.indexes where name = 'idx_cid'))
 736     drop index student.idx_cid
 737 go
 738  
 739 if (exists (select * from sys.indexes where name = 'idx_cid'))
 740     drop index student.idx_cid
 741 go
 742  
 743 --非聚集索引
 744 create nonclustered index idx_cid
 745 on
 746 student (cid)
 747 with fillFactor = 30;    --填充因子
 748  
 749 --聚集索引
 750 if (exists (select * from sys.indexes where name = 'idx_sex'))
 751     drop index student.idx_sex
 752 go
 753 create clustered index idx_sex
 754 on
 755 student(sex);
 756  
 757 --聚集索引
 758 if (exists (select * from sys.indexes where name = 'idx_name'))
 759     drop index student.idx_name
 760 go
 761 create unique index idx_name
 762 on
 763 student(name);
 764 --创建视图
 765 if(exists (select * from sys.objects where name = 'v_stu'))
 766     drop view v_stu
 767 go
 768 create view v_stu
 769 as
 770 select id,name,age,sex from student;
 771 go
 772 --修改视图
 773 alter view v_stu
 774 as
 775 select id,name,sex from student;
 776 go
 777 alter view v_stu(编号,名称,性别)
 778 as 
 779     select id,name,sex from student
 780 go
 781 select * from v_stu;
 782 select * from information_schema.views;
 783 --加密视图
 784 if(exists (select * from sys.objects where name='v_student_info'))
 785     drop view v_student_info
 786 go
 787 create view v_student_info
 788 with encryption --加密
 789 as
 790     select id,name,age from student
 791 go
 792 select * from information_schema.views
 793 where table_name like 'v_stu';
 794 
 795 exec sp_databases;--查看你数据库
 796 exec sp_tables;        --查看表
 797 exec sp_columns student;--查看列
 798 exec sp_helpIndex student;--查看索引
 799 exec sp_helpConstraint student;--约束
 800 exec sp_stored_procedures;
 801 exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句
 802 exec sp_rename student, stuInfo;--修改表、索引、列的名称
 803 exec sp_renamedb myTempDB, myDB;--更改数据库名称
 804 exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库
 805 exec sp_helpdb;--数据库帮助,查询数据库信息
 806 exec sp_helpdb master;
 807 
 808 --表重命名
 809 exec sp_rename 'stu', 'stud';
 810 select * from stud;
 811 --列重命名
 812 exec sp_rename 'stud.name', 'sName', 'column';
 813 exec sp_help 'stud';
 814 --重命名索引
 815 exec sp_rename N'student.idx_cid', N'idx_cidd', N'index';
 816 exec sp_help 'student';
 817 
 818 --查询所有存储过程
 819 select * from sys.objects where type = 'P';
 820 select * from sys.objects where type_desc like '%pro%' and name like 'sp%';
 821 --创建存储过程
 822 if (exists (select * from sys.objects where name='proc_get_student'))
 823     drop proc proc_get_student
 824 go
 825 create proc proc_get_student
 826 as
 827     select * from student;
 828 go
 829 --调用、执行存储过程
 830 exec proc_get_student
 831 --修改存储过程
 832 alter proc proc_get_student
 833 as
 834 select * from student;
 835 go
 836 --带参存储过程
 837 if(object_id('proc_find_stu','p') is not null)
 838     drop proc proc_find_stu
 839 go
 840 create proc proc_find_stu(@startId int,@endId int)
 841 as
 842     select * from student where id between @startId and @endId
 843 go
 844 exec proc_find_stu 2,4;
 845 go
 846 --带通配符参数存储过程
 847 if(object_id('proc_findStudentByName','p') is not null)
 848     drop proc proc_findStudentByName
 849 go
 850 create proc proc_findStudentByName(@name varchar(20) = '%j%',@nextName varchar(20) = '%')
 851 as
 852     select * from student where name like @name and name like @nextName;
 853 go
 854 exec proc_findStudentByName;
 855 exec proc_findStudentByName '%o%','t%'
 856 go
 857 --带出入参数存储过程
 858 if(object_id('proc_getStudentRecord','p') is not null)
 859     drop proc proc_getStudentRecord
 860 go
 861 create proc proc_getStudentRecord(
 862     @id int,--默认输入参数
 863     @name varchar(20) out,--输出参数
 864     @age varchar(20) output--输入输出参数
 865 )
 866 as
 867     select @name = name, @age = age from student where id = @id and sex =@age;
 868 go
 869 declare @id int,
 870         @name varchar(20),
 871         @temp varchar(20);
 872 set @id = 7;
 873 set @temp =1;
 874 exec proc_getStudentRecord @id,@name out, @temp output;
 875 select @name,@temp;
 876 print @name + '#' + @temp;
 877 --不缓存存储过程(with recompile)
 878 if(object_id('proc_temp','p') is not null)
 879     drop proc proc_temp
 880 go
 881 create proc proc_temp
 882 with recompile
 883 as
 884     select * from student;
 885 go
 886 exec proc_temp;
 887 --加密存储过程(with encryption)
 888 if(object_id('proc_temp_encryption','p') is not null)
 889     drop proc proc_temp_encryption
 890 go
 891 create proc proc_temp_encryption
 892 with encryption
 893 as
 894     select * from student;
 895 go
 896 exec proc_temp_encryption;
 897 exec sp_helptext 'proc_temp';
 898 exec sp_helptext 'proc_temp+encryption';
 899 --带游标参数存储过程
 900 if(object_id('proc_cursor','p') is not null)
 901     drop proc proc_cursor
 902 go
 903 create proc proc_cursor
 904     @cur cursor varying output
 905 as
 906     set @cur = cursor forward_only static for
 907     select id,name,age from student;
 908     open @cur;
 909 go
 910 --调用
 911 declare @exec_cur cursor;
 912 declare @id int,
 913         @name varchar(20),
 914         @age int
 915 exec proc_cursor @cur = @exec_cur output;--调用存储过程
 916 fetch next from @exec_cur into @id,@name,@age;
 917 while (@@fetch_status = 0)
 918 begin
 919     fetch nex from @exec_cur into @id,@name,@age;
 920     print 'id:'+convert(varchar,@id) + ',name:'+@name+',age:'+convert(char,@age);
 921 end
 922 close @exec_cur;
 923 deallocate @exec_cur;--删除游标
 924 
 925 --存储过程,row_number完成分页
 926 if(object_id('pro_page','p') is not null)
 927     drop proc proc_cursor
 928 go
 929 create proc pro_page
 930     @startIndex int,
 931     @endIndex int
 932 as
 933     select count(*) from product;
 934     select * from(
 935         select row_number() over(order by pid) as rowId,* from product
 936     )temp
 937     where temp.rowId between @startIndex and @endIndex
 938 go
 939 --drop proc pro_page
 940 exec pro_page 1,4
 941 --分页村粗过程
 942 if(object_id('pro_page','p') is not null)
 943     drop proc pro_stu
 944 go
 945 create procedure pro_stu(
 946     @pageIndex int,
 947     @pageSize int
 948 )
 949 as
 950     declare @startRow int,@endRow int
 951     set @startRow = (@pageIndex - 1) * @pageSize + 1
 952     set @endRow = @startRow + @pageSize -1
 953     select * from(
 954         select *,row_number() over (order by id asc) as number from student
 955     )t
 956     where t.number between @startRow and @endRow;
 957 
 958 exec pro_stu 2,2;
 959 
 960 --开始事务
 961 begin transaction tran_bank;
 962 declare @tran_error int;
 963     set @tran_error = 0;
 964     begin try
 965         update bank set totalMoney = totalMoney - 10000 where userName = 'jack';
 966         set @tran_error = @tran_error + @@error;
 967         update bank set totalMoney = totalMoney + 10000 where userName = 'jason';
 968         set @tran_error = @tran_error + @@error;
 969     end try
 970     begin catch
 971         print '出现异常,错误编号:'+convert(varchar,error_number())+',错误信息:'+error_message();
 972         set @tran_error = @tran_error + 1;
 973     end catch
 974 if(@tran_error > 0)
 975     begin
 976         rollback tran;
 977         print '转账失败,取消交易';
 978     end
 979 else
 980     begin
 981         commit tran;
 982         print '转账成功';
 983     end
 984 go
 985 --错误消息存储过程
 986 if(object_id('proc_error_info') is not null)
 987     drop procedure proc_error_info
 988 go
 989 create proc proc_error_info
 990 as
 991     select
 992         error_number() '错误编号',
 993         error_message() '错误消息',
 994         error_severity() '严重性',
 995         error_state() '状态号',
 996         error_line() '错误行号',
 997         error_procedure() '错误对象(存储过程或触发器)名称';
 998 go
 999 --简单try catch 示例
1000 begin try
1001     select 1/0;
1002 end try
1003 begin catch
1004     exec proc_error_info;--调用错误消息存储过程
1005 end catch
1006 go
1007 --简单try catch 示例,无法处理错误
1008 begin try
1009     select * * from student;
1010 end try
1011 begin catch
1012     exec proc_error_info
1013 end catch
1014 go
1015 --简单try catch示例,不处理错误(不存在的表对象)
1016 begin try
1017     select * from st;
1018 end try
1019 begin catch
1020     exec proc_error_info
1021 end catch
1022 go
1023 --异常处理,能处理存储过程(触发器)中(不存在表对象)的错误信息
1024 if(object_id('proc_select') is not null)
1025     drop procedure proc_select
1026 go
1027 create proc proc_select
1028 as
1029     select * from st;
1030 go
1031 begin try
1032     exec proc_select;
1033 end try
1034 begin catch
1035     exec proc_error_info;
1036 end catch
1037 go
1038 --无法提交的事务
1039 if(object_id('temp_tab','u') is not null)
1040     drop table temp_tab
1041 go
1042 create table temp_tab(
1043     id int primary key identity(100000,1),
1044     name varchar(200)
1045 )
1046 go
1047 begin try
1048     begin tran;
1049     --没有createTime字段
1050     alter table temp_tab drop column createTime;
1051     commit tran;
1052 end try
1053 begin catch
1054     exec proc_error_info;--显示异常信息
1055     if(xact_state() = -1)
1056     begin
1057         print '会话具有活动事务,但出现了致使事务被归类为无法提交的事务的错误。'
1058             +'会话无法体骄傲事务或回滚到保存点;它只能请求完成回滚事务。'
1059             +'会话在回滚事务之前无法执行任何写操作。会话在回滚事务之前只能执行读操作。'
1060             +'事务回滚之后,会话便可执行读写操作并可开始新的事务。';
1061     end
1062     else if(xact_state() = 0)
1063     begin
1064         print '会话没有活动事务。';
1065     end
1066     else if(xact_state() = 1)
1067     begin
1068         print '会话具有活动事务。会话可以执行任何操作,包括写入数据和提及事务。';
1069     end
1070 end catch
1071 go
1072 ---异常、错误信息表
1073 if (object_id('errorLog', 'U') is not null)
1074     drop table errorLog
1075 go
1076 create table errorLog(
1077     errorLogID int primary key identity(100, 1),    --ErrorLog 行的主键。
1078     errorTime datetime default getDate(),            --发生错误的日期和时间。
1079     userName sysname default current_user,            --执行发生错误的批处理的用户。
1080     errorNumber int,                                --发生的错误的错误号。
1081     errorSeverity int,                                --发生的错误的严重性。
1082     errorState int,                                    --发生的错误的状态号。
1083     errorProcedure nvarchar(126),                    --发生错误的存储过程或触发器的名称。
1084     errorLine int,                                    --发生错误的行号。
1085     errorMessage nvarchar(4000)
1086 )
1087 go
1088 --
1089 --存储过程:添加异常日志信息
1090 if (object_id('proc_add_exception_log', 'p') is not null)
1091     drop proc proc_add_exception_log
1092 go
1093 create proc proc_add_exception_log(@logId int = 0 output)
1094 as
1095 begin
1096     set nocount on;
1097     set @logId = 0;
1098     begin try
1099         if (error_number() is null)
1100             return;
1101         
1102         if (xact_state() = -1)
1103         begin
1104             print '会话具有活动事务,但出现了致使事务被归类为无法提交的事务的错误。'
1105                 + '会话无法提交事务或回滚到保存点;它只能请求完全回滚事务。'
1106                 + '会话在回滚事务之前无法执行任何写操作。会话在回滚事务之前只能执行读操作。'
1107                 + '事务回滚之后,会话便可执行读写操作并可开始新的事务。';
1108         end
1109         else if (xact_state() = 0)
1110         begin
1111             print '会话没有活动事务。';
1112         end
1113         else if (xact_state() = 1)
1114         begin
1115             print '会话具有活动事务。会话可以执行任何操作,包括写入数据和提交事务。';
1116         end
1117         
1118         --添加日志信息
1119         insert into errorLog values(getDate(), 
1120             current_user, error_number(), 
1121             error_severity(), error_state(), 
1122             error_procedure(), 
1123             error_line(), error_message());
1124         --设置自增值
1125         select @logId = @@identity;
1126     end try
1127     begin catch
1128         print '添加异常日志信息出现错误';
1129         exec proc_error_info;--显示错误信息
1130         return -1;
1131     end catch
1132 end
1133 go
1134 --
1135 ---处理异常信息示例
1136 declare @id int;
1137 begin try
1138     begin tran;
1139     --删除带有外键的记录信息
1140     delete classes where id = 1;
1141     commit tran;
1142 end try
1143 begin catch
1144     exec proc_error_info;--显示错误信息
1145     if (xact_state() <> 0)
1146     begin
1147         rollback tran;
1148     end
1149     exec proc_add_exception_log @id output
1150 end catch
1151 select * from errorLog where errorLogID = @id;
1152 go
1153 --创建一个游标
1154 declare cursor_stu cursor scroll for
1155     select id, name, age from student;
1156 --打开游标
1157 open cursor_stu;
1158 --存储读取的值
1159 declare @id int,
1160         @name nvarchar(20),
1161         @age varchar(20);
1162 --读取第一条记录
1163 fetch first from cursor_stu into @id, @name, @age;
1164 --循环读取游标记录
1165 print '读取的数据如下:';
1166 --全局变量
1167 while (@@fetch_status = 0)
1168 begin
1169     print '编号:' + convert(char(5), @id) + ', 名称:' + @name + ', 类型:' + @age;
1170     --继续读取下一条记录
1171     fetch next from cursor_stu into @id, @name, @age;
1172 end
1173 --关闭游标
1174 close area_cursor;
1175 
1176 --删除游标
1177 --deallocate area_cursor;
1178 
1179 --创建insert插入类型触发器
1180 if (object_id('tgr_classes_insert', 'tr') is not null)
1181     drop trigger tgr_classes_insert
1182 go
1183 create trigger tgr_classes_insert
1184 on classes
1185     for insert --插入触发
1186 as
1187     --定义变量
1188     declare @id int, @name varchar(20), @temp int;
1189     --在inserted表中查询已经插入记录信息
1190     select @id = id, @name = name from inserted;
1191     set @name = @name + convert(varchar, @id);
1192     set @temp = @id / 2;    
1193     insert into student values(@name, 18 + @id, @temp, @id);
1194     print '添加学生成功!';
1195 go
1196 --插入数据
1197 insert into classes values('5班', getDate());
1198 --查询数据
1199 select * from classes;
1200 select * from student order by id;
1201 
1202 --delete删除类型触发器
1203 if (object_id('tgr_classes_delete', 'TR') is not null)
1204     drop trigger tgr_classes_delete
1205 go
1206 create trigger tgr_classes_delete
1207 on classes
1208     for delete --删除触发
1209 as
1210     print '备份数据中……';    
1211     if (object_id('classesBackup', 'U') is not null)
1212         --存在classesBackup,直接插入数据
1213         insert into classesBackup select name, createDate from deleted;
1214     else
1215         --不存在classesBackup创建再插入
1216         select * into classesBackup from deleted;
1217     print '备份数据成功!';
1218 go
1219 --
1220 --不显示影响行数
1221 --set nocount on;
1222 delete classes where name = '5班';
1223 --查询数据
1224 select * from classes;
1225 select * from classesBackup;
1226 
1227 --update更新类型触发器
1228 if (object_id('tgr_classes_update', 'TR') is not null)
1229     drop trigger tgr_classes_update
1230 go
1231 create trigger tgr_classes_update
1232 on classes
1233     for update
1234 as
1235     declare @oldName varchar(20), @newName varchar(20);
1236     --更新前的数据
1237     select @oldName = name from deleted;
1238     if (exists (select * from student where name like '%'+ @oldName + '%'))
1239         begin
1240             --更新后的数据
1241             select @newName = name from inserted;
1242             update student set name = replace(name, @oldName, @newName) where name like '%'+ @oldName + '%';
1243             print '级联修改数据成功!';
1244         end
1245     else
1246         print '无需修改student表!';
1247 go
1248 --查询数据
1249 select * from student order by id;
1250 select * from classes;
1251 update classes set name = '五班' where name = '5班';
1252 
1253 if (object_id('tgr_classes_update_column', 'TR') is not null)
1254     drop trigger tgr_classes_update_column
1255 go
1256 create trigger tgr_classes_update_column
1257 on classes
1258     for update
1259 as
1260     --列级触发器:是否更新了班级创建时间
1261     if (update(createDate))
1262     begin
1263         raisError('系统提示:班级创建时间不能修改!', 16, 11);
1264         rollback tran;
1265     end
1266 go
1267 --测试
1268 select * from student order by id;
1269 select * from classes;
1270 update classes set createDate = getDate() where id = 3;
1271 update classes set name = '四班' where id = 7;
1272 
1273 if (object_id('tgr_classes_inteadOf', 'TR') is not null)
1274     drop trigger tgr_classes_inteadOf
1275 go
1276 create trigger tgr_classes_inteadOf
1277 on classes
1278     instead of delete/*, update, insert*/
1279 as
1280     declare @id int, @name varchar(20);
1281     --查询被删除的信息,病赋值
1282     select @id = id, @name = name from deleted;
1283     print 'id: ' + convert(varchar, @id) + ', name: ' + @name;
1284     --先删除student的信息
1285     delete student where cid = @id;
1286     --再删除classes的信息
1287     delete classes where id = @id;
1288     print '删除[ id: ' + convert(varchar, @id) + ', name: ' + @name + ' ] 的信息成功!';
1289 go
1290 --test
1291 select * from student order by id;
1292 select * from classes;
1293 delete classes where id = 7;
1294 
1295 if (object_id('tgr_message', 'TR') is not null)
1296     drop trigger tgr_message
1297 go
1298 create trigger tgr_message
1299 on student
1300     after insert, update
1301 as raisError('tgr_message触发器被触发', 16, 10);
1302 go
1303 --test
1304 insert into student values('lily', 22, 1, 7);
1305 update student set sex = 0 where name = 'lucy';
1306 select * from student order by id;
1307 
1308 alter trigger tgr_message
1309 on student
1310 after delete
1311 as raisError('tgr_message触发器被触发', 16, 10);
1312 go
1313 --test
1314 delete from student where name = 'lucy';
1315 
1316 --禁用触发器
1317 disable trigger tgr_message on student;
1318 --启用触发器
1319 enable trigger tgr_message on student;
1320 
1321 --查询已存在的触发器
1322 select * from sys.triggers;
1323 select * from sys.objects where type = 'TR';
1324 
1325 --查看触发器触发事件
1326 select te.* from sys.trigger_events te join sys.triggers t
1327 on t.object_id = te.object_id
1328 where t.parent_class = 0 and t.name = 'tgr_valid_data';
1329 
1330 --查看创建触发器语句
1331 exec sp_helptext 'tgr_message';
1332 
1333 if ((object_id('tgr_valid_data', 'TR') is not null))
1334     drop trigger tgr_valid_data
1335 go
1336 create trigger tgr_valid_data
1337 on student
1338 after insert
1339 as
1340     declare @age int,
1341             @name varchar(20);
1342     select @name = s.name, @age = s.age from inserted s;
1343     if (@age < 18)
1344     begin
1345         raisError('插入新数据的age有问题', 16, 1);
1346         rollback tran;
1347     end
1348 go
1349 --test
1350 insert into student values('forest', 2, 0, 7);
1351 insert into student values('forest', 22, 0, 7);
1352 select * from student order by id;
1353 
1354 if (object_id('log', 'U') is not null)
1355     drop table log
1356 go
1357 create table log(
1358     id int identity(1, 1) primary key,
1359     action varchar(20),
1360     createDate datetime default getDate()
1361 )
1362 go
1363 if (exists (select * from sys.objects where name = 'tgr_student_log'))
1364     drop trigger tgr_student_log
1365 go
1366 create trigger tgr_student_log
1367 on student
1368 after insert, update, delete
1369 as
1370     if ((exists (select 1 from inserted)) and (exists (select 1 from deleted)))
1371     begin
1372         insert into log(action) values('updated');
1373     end
1374     else if (exists (select 1 from inserted) and not exists (select 1 from deleted))
1375     begin
1376         insert into log(action) values('inserted');
1377     end
1378     else if (not exists (select 1 from inserted) and exists (select 1 from deleted))
1379     begin
1380         insert into log(action) values('deleted');
1381     end
1382 go
1383 --test
1384 insert into student values('king', 22, 1, 7);
1385 update student set sex = 0 where name = 'king';
1386 delete student where name = 'king';
1387 select * from log;
1388 select * from student order by id;
View Code

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
初识 Sql Server存储过程
开篇语 之前的公司并未使用存储过程来做项目,所以小生对存储过程的调用、使用也是一知半解,刚好这家公司就大量用到了存储过程 这次做的功能,为了保持风格一致,也是需要使用存储过程来实现动态sql和数据分页 下面一起来看看如何实现的吧(小白一枚,不喜勿喷,请轻拍)~ 调用存储过程(其中condit...
759 0
sqlServer存储过程
1、创建存储过程报错:     'CREATE/ALTER PROCEDURE' 必须是查询批次中的第一个语句。 解决方法: use databaseName 后面要加上一句: GO ...
780 0
SQLSERVER存储过程语法详解
SQL SERVER存储过程语法: Create PROC [ EDURE ] procedure_name [ ; number ]     [ { @parameter data_type }         [ VARYING ] [ = default ] [ OUTPUT ]     ] [ ,...n ]   [ WITH     { RECOMPILE | ENCRY
1582 0
+关注
多吉
数据库相关技术专家
73
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载