SQL 语法笔记大全

--创建数据库
create database students
go
--切换到自定义的数据库
use students
go
--建表
---检查即将创建的表是否存在
if exists
(
 select*from sysobjects where name='class'and type='u'
)
drop table class
go
---班级（班级号-主键约束，班级名-非空约束）
create table class
(
 cno varchar(10) primary key,
 cname varchar(20) not null,
)
/*
identity(1,1)就是自动增加,第一个参数是种子值,第二个是增量值;
primary key是主键
*/
--char为固定容纳字符，无论您输入的字符为多少，其所占有空间仍为8000个ANSI；
--varchar为可变容纳字符，也就是说他 会随着字符数的多少而自动改变存储长度，可以节省大量空间。
create table employee
(
 emno varchar(10) primary key,
 emname varchar(20) not null,
 emsex char(2) check (emsex in('男','女')) default '男',
 addr varchar(100) not null,
 emid varchar(20),
 emlb money,
 birth datetime
)

--插入表里的值
insert into buyin values('1001','000001','2004-11-24',10,'100')

--数据类型
     数据类型                数据类型名称

   数字数据类型        int,integer,smallint,tinyint,bigint,real,float,decimal,numeric,binary,varbinary

   字符串数据类型      char,nchar,varchar,nvarchar

   日期时间类型        datetime,smalldatetime

   其他数值类型        bit,text,ntext,image,money,smallmoney,timestamp,uniqueidentifier,sysname,sql_variant table

--查看表sp_help
sp_help students
go

--修改表
--添加字段
alter table students add class int

--修改字段
alter table students
alter column class varchar(20)

--删除字段
alter table students
drop column class

--查看数据库信息
sp_helpdb
go

--删除一行数据
delete from table_name where

--删除整个表的所有数据（相对于完全删除一个表所有记录，truncate 比 delete速度快的多）
delete  from tableName
or
truncate table tableName

--创建一个简单的视图
drop view titlepri_view

create view titlepri_view
as
select title_id,title,type,price
from titles where price >10
go

create view emphierarchy
as
 select a.employeeid,a.lastname,a.firstname,b.lastname'上司姓' from employees a,employees b where a.reportsto=b.employeeid and b.reportsto is not null

select * from emphierarchy

--T3
update emphierarchy set firstname='jones' where employeeid='4'

----创建聚集索引
create clustered index 索引名
on 表名(字段名)
--创建非聚集索引
create nonclustered index 索引名
on 表名(字段名)

--非聚集索引，无序nonclustered
--聚集索引，有序
--聚集索引和非聚集索引的区别
 --聚集索引：若某表创建了主键约束，则主键字段自动带上了聚集索引。一张表中只能创建一个聚集，所以若当前
--  表已经创建了主键约束，或是已经在某字段上建立了聚集索引，则不允许再次在其他字段上创建一个聚
--  集索引。
-- 非聚集索引：允许在一张表中建立多个非聚集索引。
--
-- 聚集索引和非聚集索引的区别：在某字段上建立了聚集索引后，该字段自动进行升序排序，并提高查找效率；
--       在某字段上建立了非聚集索引后，该字段不进行排序，仅提高查找效率。

---------------------------------------------------
declare @name char(10)定义一个变量
 select @name=cname from recruitmentagencies
 print @name

 
DECLARE @Avg1 VARCHAR(255)
  IF (SELECT AVG(Unitprice)
   FROM Products) > 30
  BEGIN
   SET @Avg1='产品平均单价超过30元'
   PRINT @Avg1
  END
  ELSE
  BEGIN
   SET @Avg1='产品平均单价没有超过30元'
   PRINT @Avg1
  END
  
DECLARE @Avg1 VARCHAR(255)
 SET @Avg1=
  CASE
      WHEN (SELECT AVG(Unitprice)
        FROM Products) > 10
          THEN '产品平均单价超过10元'
      WHEN (SELECT AVG(Unitprice)
        FROM Products) < 10
          THEN '产品平均单价没有超过10元'
  END
  PRINT @Avg1

 
DECLARE @Price DECIMAL(10,4)
 SELECT @Price=AVG(Unitprice)FROM Products
 WHILE @Price > 10
 BEGIN
  SELECT @Price = @Price - 1
  IF  @Price < 20
   BREAK
  ELSE
   PRINT @Price
   continue  
 END
 
--WAITFOR命令是让程序停顿指定的时间再运行或在指定时间运行。
过2秒钟后显示当前系统时间。
  WAITFOR DELAY '00:00:02'
  PRINT(GETDATE())
  
-----存储过程概述
是一组预编译好的完成特定功能的SQL语句
是存储在服务器上的一个对象
可通过对象名来调用
优点
模块化的程序设计
高效率的执行
减少网络流量
保证系统的安全性
Create procedure proc_print
 As
  declare @chara char(10),@charb char(10)
  print @chara+space(5)+@charb
  
create procedure pub_title
as
 declare @title_id tid,@title varchar(80),@price money,@money varchar(20)
 select @title_id=title_id,@title=title,@price=price from titles where price>(select avg(price) from titles)and type='popular_comp'
 set @money=cast(@price as varchar(20))
 print @title_id
 print @title
 print @money     

exec pub_title

将pubs数据库中的proc_print存储过程更名为print_proc
sp_rename proc_print，print_proc

修改proc_print，将其功能改为打印输出“abc”
alter procedure proc_print
as
 print ‘abc’

 
--创建带输入参数的存储过程的示例
CREATE PROCEDURE Titles_Pub
 @v_pubid char(4) 
AS  
 SELECT * FROM titles
 WHERE pub_id = @v_pubid

 执行存储过程的示例： EXECUTE Titles_Pub ‘1389'
 
 --什么是触发器
 一种特殊类型的存储过程，
不同于存储过程。
触发器主要是通过事件进行触发而被自动执行的
存储过程可以通过存储过程名称而被直接调用。
触发器的作用
禁止无效的修改

级联修改相关表格

执行较为复杂的约束操作

触发器的类型
insert 触发器：监视insert操作
delete 触发器：监视delete操作
update 解发器：监视update操作

设计一个应用，每删除一个学生信息，则打印消息“delete successful”
Create Trigger DeleteTrigger
on stuInfo
for delete
as
   print 'delete successful'
  
  
---什么是函数
 函数是用于封装经常执行的逻辑的子例程
函数的类型
标量函数（返回单个数据值）
内嵌表值函数（返回一个结果集）
多语句表值函数(返回一个结果集）

标量函数格式
create function 函数名(@参数名 类型……）
returns 数据类型
as
 begin
  函数体
  return 返回值
 end
调用格式： 拥用者.函数名（实参）

内嵌表值函数格式
create function 函数名(@参数名 类型……）
returns   Table
as
 return  select 语句
调用格式： 拥用者.函数名（实参）

多语句表值函数格式
 create function 函数名(@参数名 类型……）
 returns  @变量名 Table(表定义）
 as
  begin
   语句组
   return
  end
调用格式： 拥用者.函数名（实参）
--例：一个简单的多语句表值函数
create function fun_3(@id int,@name varchar(12),@age int)
returns @table_name
 table( tId int, tName varchar(12), tAge int)
as
begin
 insert into @table_name values(@id,@name,@age)
 return
end
go

--调用多语句表值函数
select * from fun_3(101,'tom',25)

begin transaction
insert into students values('001','zhangsan')
insert into students values('002','lisi')
commit transaction

create procedure pro_01
@出仓库编号 varchar(5),
@入仓库编号 varchar(5),
@商品编号 varchar(5),
@调度数量 varchar(5)
as
begin transaction
 declare @库存数量 int
 set @库存数量=(select 库存数量 from 库存表 where 仓库编号=@出仓库编号 and 商品编号=@商品编号)
 if(@调度数量>@库存数量)
 begin
  print'库存量不足!'
  rollback
  
 end
 else
 begin
  
  update 库存表 set 库存数量=((select 库存数量 from 库存表 where 仓库编号=@出仓库编号 and 商品编号=@商品编号)-@调度数量) where 仓库编号=@出仓库编号 and 商品编号=@商品编号
  update 库存表 set 库存数量=((select 库存数量 from 库存表 where 仓库编号=@入仓库编号 and 商品编号=@商品编号)+@调度数量) where 仓库编号=@入仓库编号 and 商品编号=@商品编号
 commit transaction
 end
 
exec pro_01 '001','002','s01','5'