s-q-l学习笔记

=============================第一章:建库建表===============================
sql中的用"--"和/*------*/注释

sql中的数据类型
{
int(4)个字节:   smallint(2)个字节:范围比int类型的范围小
tinyint(2)个字节:存0-255之间数(整数) numeric(位数，小数位数)例 :numeric(18.0)--18位整数0位小数
float
}

-----------建数据库的代码:

if exists(select * from sysdatabases name='stuDB')
drop database stuDB
create database stuDB
on[primary]
( --主数据库
name='stuDB_data' ,                    --主数据库的逻辑文件名
filename='D:\stuDB_data.mdf,   --主数据文件的物理文件名
size=5mb,                                   --主数据文件的初始大小
maxsize=100mb,                    --主数据文件的最大值
filegrowth=15%  --主数据文件的增长率
)
,
( --次要数据库
name='stuDB_data2',
filename='D:stuDB_data2.mdf',
size=20,
maxsize=100,
filegrowth=1
)
log on    --日记文件
(
name='stuDB_Log',
filename='D:\StuDB_Log.ldf',
size=10,
filegrowth=1
)
go


------(创建)删除表

use stuDB
if(exists select * from sysobjects where name='stuTable')
  drop table stuTable
create table stuInfo
(
id int identity(1,1) not null,
useName varchar(30) primary key not null,
password varchar(30) not null
)
go

--------添加约束

alter table stuInfo
add constraint PK_stuNo primary key(stuNo) --主键
add constraint UQ_stuID unique(stuID)  --唯一
add constraint DF_stuAddress default('地址不祥')  --默认
add constraint CK-stuAge check(stuAge between 15 and 40)        --检查
add constraint FK_stuNo foreign key(stuNo) references  stuInfo(stuNo)             --外键
--删除约束:alter table stuInfo drop constraint 约束名


------分配sql管理用户

1.创建登录身份:  sp_addlogin 'zhang','123'
2.创建用户       :   sp_grantdbaccess 'zhang','userZhang'
3.分配权限       :   grant select(update,delete) on stuInfo to userZhang

--撤消权限      :revoke select on stuInfo to userZhang
--拒绝访问     : deny select on stuInfo to userZhang

去除和登陆相关联的用户
exec sp_revokedbaccess 'userZhang'
删除
exec sp_droplogic 'zhang'
添加window登陆账户
exec sp_grantlogic 'window域名\域账户'

===============================第三章(sql变量)===============================

声明局部变量:
declare @+变量名+数据类型
例 :declare @name varchar(8)
给变量加值(二种方式)
1.set @name='张三'
          2.select @name=stuName from stuInfo where id=1
-------------
convert(varchar(5),@@error)--将错误号转成varchar类型

if-else语句:超过一条以上的用Begin - End
sql中的输出语有二种:
          print  @name或字符串
select @name as 自定义列名
print 语句要求(单个局部变量)或(字符串)表达式作为参数.
因此:print '当前错误编号:'+convert(varchar(5),@@error);

-------------

case end
select stuNo,
成绩=CASE
WHEN writerExam<60 THEN 'E'
WHEN writerExam BETWEEN 60 and 70 THEN   'D'
ELSE 'A'
form stuInfo

===============================第四章(子查询)===============================

1.子查询和比较运算符联合用时，必须保证子查询返回的值不能多于一个
2.update,insert,delete一起使用，语法类似于select语句

例 :select * from stuInfo where stuAge>(select stuAge from stuInfo where stuName='李')
in关键字:select stuName from stuInfo where stuNo IN(select stuNO from stuMarks where eam=60)

not in关键字:与in相反

exists(了查询):如果子查询的语句有记录则返回true
not exists(子查询):如果查询中没有记录录则返加true

求通过率:
avg(isPass*100)+'%': avg()函数返回一个整形值，因为isPass是一个整数，因此必须先扩100倍然后除以总人数得到通过率%。




===============================T-Sql综合应用===============================

1.在学员系统中，使用子查询统计投考的学员名单
  select * from stuInfo where stuNo NOT IN(select stuNo from stuMarks)
2.查询缺考人数
     user stuDB
     go
     set NoCount on--不显示受影响的行数
     select  应到人数=(select count(*) from stuInfo),
                实到人数=(select count(*) from stuMarks),
                缺考人数=((select count(*) from stuInfo)-select count(*) from stuMarks),

3.统计考试通过情况并将结果存到newTable中
    if exists(select * from sysobjects where name='newTable')
        drop table newTable
   select stuName,stuInfo.stuNo,writlenExam,labExam,
             isPass=CASE
             WHEN wrilenExam>=60 and labExam>=60 THEN 1
             ELSE    0
             END
   into newTable from stuInfo Left Join stuMarks On(stuInfo.stuNo=stuMarks.stuNo)
4. 加分(平均分低的加分 )
   declare @avgWriter numeric(4,1) ,@avgLab numeric(4,1)
   declare @addsubject varchar(5),@addScore int
   select @avgWriter=AVG(writtenExam) from newTable where writtenExam is not null
   select @avgLab=AVG(LabExam) from newTable where LabExam is not null
   set @addScore=0
   If @avgWriter<@avgLab
     while(1=1)
     begin
         set @addSubject='笔试'
         update newTable set writtenExam=writtenExam+1
         set @addScore=@addScore+1
         if(select Max(writtenExam) from newTable)>=97
            break
      end
  Else
      whiel(1=1)
      begin
          set @addSubject='机试'
          update newTable set labExam=labExam+1
          set @addScore=@addScore+1
          if(select max(LabExam) from newTable) >=97
              break
      end
--因为提分，所以要更改isPass是否通过
      update newTable set isPass=CASE
WHEN writeenExam>=60 and LabExam>=60 THEN 1
ELSE 0
End
-------查看

    select 加分科目=@addSubject,加分值=@addScore

-------显示最终的通过情况

select 学号=stuNo,姓名=stuName,
  笔试成绩=case
  when writeenExam is null then '缺考'
  else Convert(varchar(5),writeenExam) End
,机试成绩=case
  when LabExam is null then '缺考'
  else Convert(varchar(5),LabExam) End
,是否通过=case
  when isPass=1 then '是'
  else ‘否’ End
  from newTable

---------显示通过率及通过人数

  select 总人数=count(*),通过人数=sum(isPass),通过率=(Convert(varchar(5),Avg(isPass*100))+'%') from newTable


对比sql语句
1.  select * from stuInfo where stuNo IN(select stuNo from stuMarks)
2.  select * from stuInfo where exists(stuInfo.stuNo=select * from stuMarks)
  这二句执行的结果一样(exists()函数：如果有记录就返回true)
  where :代表当前游标指向的记录是否显示（true/false）
  stuInfo.stuNo:代表当前游标指向的行的字段

===========================(结束)T-Sql综合应用===============================

===========================第五章(事务、索引、视图)==========================

----事务

事务：事务是一个不可分割的逻辑单元，作为一个整体（要么都执行，要么都不执行）
事务必备的四个属性(ACID)：1.原子性 2.一致性 3.隔离性 4.永久性

开始事务：Begin TransAction
提交事务：Commit TransAction
回滚事务：RollBack TransAction
一旦提交或回滚 事务就结事

@@Error：只能判断当前一条（前条）的语句是否有错，如果有错返回的值不为“0”
因此在事务中进行判断错误时应:@a=@a+@@Error ,if(@a<>0) 则提交事务

事务的分类：1.显示事务 2.隐性事务:set  Implicit_TreansAction on              3.自动提交事务

------索引

(this有索引高级讲解记事本文件,)

索引：编排数据的内部方法，相当于目录
索引的作用：提高查询的速度，改善数据库的性能。
聚集索引：只能有一个
非聚集索引：可以有多个

创建聚集索引：
  if exists(select * from sysIndexs where name='Ix_name')
       drop index Ix_name
create  Clustered  index Ix_name
on   stuInfo(stuNo)
wilth fillFactor=30  -- 填充因子
go

创建非聚集索引
create NonClustered  Ix_name2
on stuInfo(stuName)
wilth fillFactor=30
go

查询索引：
select * from stuInfo(index=Ix_name) where writeeExam BETEEN 60 AND 80

----视图

视图:是一张虚拟表，它是存储在数据库中的一条sql语句

创建视图:
create view v_name
as
select 姓名=stuName from stuInfo
go

查询视图
select * from v_name

数据库中的（表）与（视图）的名不能相同，因此查询表的视图语法与查询普通表的语法一样


==========================第六章（存储过程）===================================

存储过程的分类：1.系统存储过程 2.用户自定义过程

系统存储过程:  sp_helptext :显示未加密的存储过程，视图，触发器的文本

  sp_help :查看表的所有信息
  sp_helpConstraint:查看表的约束
  exec:  执行存储过程，在存储过程中可加可不加

------------------自定义存储过程：---------------------------------

create proc[edure] p_name
@name varchar(20),@age int    ----参数不可以不加declear ,也可不写这二个参数（就成了无参存储过程）
as
sql语句
go

create proc p_name
@name varchar(30)='admin'    --默认值
,@age int =20
  as  sql语句
  go

  exec p_name @int=50 ---姓名采用默认值

--------------调用自定义存储过程--------------------------------

exec p_name         ---无参
exec p_name        ----默认值
exec p_name 'admin',30                 ---有参
exec p_name @age=20,@name='admin'    -----有参

--------------带输出参数的存储过程 ----------------------
create proc  p_name
@notPassSum int OUTPUT,
@wri int =60,
@lab int =60
as

  go

  declare @sum int
  exec p_name @sum OUTPUT,64
  print @sum
1. 如果当前的存储过程中有输出参数，则必须赋值(如果上面的代码)
2. 如果存储过程有输出参数，则执行这个存储过程时必须用一个变量接这个输出参数
3. 存储过程也以表的形式存储在服务器上，在sysobjects表中

--------------存储过程示例-------------------------------

create proc AA
@sum int OUTPUT
as
set @sum=3+5
go

declare @sum1 int   (如果有多个输出参数，则可接指定的输出参数：@sum=@sum1)
exec AA @sum1 out put
print @sum1

------------------删除存储过程----------------------------

drop proc AA

--------------自定义抛出错误---------------------------

Raiserro('及格线错误,请指定0-100之间的分数',16,1) 1:状态1-127
return 从当前程序退出


======================================第七章（触发器）=================================

(this讲解“触发器和二张特殊表”.有记事本文件)

触发器是一种特殊的存储过程，能够在多表之间执行特殊的业务规则（机制）
触发器是对表进行，增、删、改、时会自动执行的存储过程
触发器有三种类型：insert,delete,update

每个触发器都有2张表：Inserted 和 Deleted 表，存在内存中，只读，等触发器的工作完成了，就删除

------创建Insert触发器-------

create Trigger  t_name
on Info
for Insert
As
     Decaler @type char(4),@outMoney
     select @type=transType,@outMoney=transMoney from INSERTED
     if(@type='去取')
        update band set currentMoney=currmentMoney-@outMoney
     else
         update band set currentMoney=currentMoney+@outMoney
      if @@Error<>0
         begin
print '交易失败'
Rollback TransAction
                return
          end
       print '交易成功!交易金额：'+convert(varchar(20),@outMoney)
go


---------创建update触发器---------

create Triger t_name
on bank
for update
as
       declare @beforeMoney Money ,@alterMoney Money
       select @beforeMoney=currentMoney from deleted
       select @alterMoney=currentMoney from inserted
       if ABS(@alterMoney-@beforeMoney)>20000
               begin
   print '交易失败,'
  Raiserror('自定义错误,i不能超过二万元',16,1)
   Rollback  TransAction
end
go

---------创建delete触发器--------

create Trigger t_name
on pub_Info
for delete
As
if(select pub_id from deleted=100)
  begin
      print '不能删除为100的信息'
      Rollback TransAction
  end



创建的触发器存在sysobjects表中

-------删除触发器----

drop trgger t_name



--------------------------Sql的经验--------------------------------------------------------------------------

如果要替换Sql中的字段里部分值： 
update titles from set imageFile=replace(imageFile,'image',image/')

sql中三张表连接:select * from a inner join b on (a.关联列=b.关联列) inner join (a.关联列=c.关联列)



A. 重命名表
下例将表 customers 重命名为 custs。

EXEC sp_rename 'customers', 'custs'

B. 重命名列
下例将表 customers 中的列 contact title 重命名为 title。

EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'