sql.sql

这个是本人在培训学校上SQL课的总结,很全面的.初学者可以看看.

--1. 数据库
1)概念 
数据库:存储数据的地方，是一种经过计算机处理过的数据的集合。
数据库管理系统:负责管理数据库，负责人与计算机交互使用。
2)创建数据库
create database database_name
on   ---用于指定数据库文件的位置，大小，增长幅度，最大值
(name=database_name_data,---指定数据文件的逻辑名
filename='', ---指定文件的物理位置
size=  ,---指定文件的初始化大小
maxsize= ,---指定文件的最大值
filegrowth= ---指定文件的增长幅度
)
log on
(name= ,
filename='',
size= ,
maxsize= ,
filegrowth= )
go
--------------------------
如果不指定日志文件，日志文件大小=数据文件/4
------------------------------------------
3)修改数据库
alter database database_name
--添加数据文件
alter database database_name
add file
(name= ,
filename='',
size= )
----------------------
--添加日志文件
alter database database_name
add log file
(name= ,
filename='',
size= )
-----------------------
--修改文件
alter database database_name
modify file(name=,size=)
----------------
--删除数据库文件
alter database database_name
remove file 文件逻辑名
--------------------------
--修改数据库名
a)sp_renamedb 旧名,新名
b)alter database database_name
  modify name 新名
--------------------
4)删除数据库
drop database database_name
---------------------------
--2.数据表
1)标识符:不能有空格，不能为保留字,第一个字符不能以数字开头，第一个字符可以是‘_’
------------
2)数据类型:
整数类型: int,smallint,tinyint,bigint
字符串： char,varchar,nchar,nvarchar
时间： datetime,smalldatetime
货币： money,smallmoney
实数： decimal
文本： text,image
-----------------
3)约束 constraint
主键约束： primary key
唯一键约束： unique
默认值约束： default
列检查约束： check
外键约束： foreign key
-------------------
4)创建表
create table table_name
(列名1 数据类型,
列名2 数据类型,
列名3 数据类型)
5)修改表
alter table table_name
--添加列
alter table table_name
add 列名 数据类型
--删除列
alter table table_name
drop column 列名
--添加约束
alter table table_name
add constraint 约束名 constraint_type(约束类型)
--删除约束 
alter table table_name
drop constraint 约束名
--停止约束
alter table table_name
nocheck constraint 约束名
--启用约束
alter table table_name
check constraint 约束名
--添加约束,并检查表中数据是否符合要求
alter table table_name
with check 
add constraint 约束名 constraint_type(约束类型)
--添加约束,不检查表中数据是否符合要求
alter table table_name
with nocheck 
add constraint 约束名 constraint_type(约束类型)
--删除表格
drop table table_name
----------------------
注意：在添加主键约束时，表中的列一定是 not null,
----------------------
3.数据的操纵语句
-----------------
--插入数据
insert into table_name
values('value','value','value','value') 
-----------
insert into table_name
select *from table_name
-----------
--修改数据
update table_name
set column1='value'
where expression
----------------
update table_name
set column1=(select*from table_name)
--------
update table_name
set column1='value'
where column2 in(select*from table_name)
--------------
--删除数据
delete from table_name
where expression
--------------
delete from table_name
where column1 in(select*from table_name)
----------------
4.查询语言
select *from table_name
where 条件
--------------
1)单表查询
--查询所有的列
select *from table_name
--指定列
select 列名1,列名2 from table_name
--加入说明性语句
select  列名1,'说明性语句',列名2 from table_name
--去掉重复的数据
select distinct 列名1 from table_name
--选者前几条数据
select top 5 * from table_name
--数据排序
select *from table_name
order by 列名 asc/desc
--使用聚合函数
sum():求和 avg():平均值 max():最大值 min():最小值
count():记录的行数
select sum(列名)from table_name
select avg(列名)from table_name
select max(列名)from table_name
select min(列名)from table_name
select count(列名)from table_name
------------------------------------
聚合函数和 group by 连用，表示对数据进行分组
------------------------------------
select column1,column2,sum(column3) from table_name
with cube|rollup
-----------------
--加上选择条件
比较运算：>,<,=,<>或 !=,<=,>= 
逻辑运算: between..and ,in(not in)
条件比较: not,and,or
--通配符
%,_,[],[^]
--使用计算列
+,-,*,/
--数据汇总
compute,compute by,
--转换字符
cast,convert,
-----------------------
2)多表查询(连接查询)
连接查询:通过表格中的相关信息，把几个表格连接起来当作一个表格使用的方式
关键字：
内连接: inner join
外连接: outer join
左外连接: left outer join
右外连接: right outer join
全外连接: full outer join
交叉连接: cross join
--使用 join 连接时，条件要使用 on 来表示
-----------------------
select s_名称,d_销量,f_单价,g_地址
from ss inner join dd 
on ss.s_编号=dd.s_编号 inner join ff
on ss.s_编号=ff.s_编号 inner join gg
on dd.d_编号=gg.d_编号
---------------------------------
select s_名称,d_销量,f_单价,g_地址
from ss ,dd , ff , gg
where ss.s_编号=dd.s_编号 
and ss.s_编号=ff.s_编号 
and dd.d_编号=gg.d_编号
-----------------------
3)嵌套查询(子查询):将一个select语句放在select,insert,update,delete命令的where语句中
                   作为比较条件使用,把这查询方式叫做嵌套查询。
                   把放在where 条件中的select 语句叫做子查询。
a.'=':表示结果是单个值
b.'in':表示结果是一组值的
c.'exists':表示存在
d.'union':用于连接表格
e.'union all':用于显示连接中的所有数据
f.'select into':在查询过程中创建表格
j.'#和##':表示临时的数据库对象。
---------------------------------
5.视图:是虚拟的表格。是基于表而存在的，用于查询特定的数据。
1)语法:
create view view_name
as
 select * from table_name

2)使用select语句来创建视图的注意事项：
--不能使用 order by,compute by 语句
--不能使用 into 语句
--不能使用临时表
3)创建视图时指定列名
create view 员工信息(编号,姓名,电话)
as
select 编号,姓+'.'+名,电话
from 员工信息表
--------------
4)修改视图
alter view 
5)察看视图的定义语句
sp_helptext view_name
6)使用加密语句:
 with encryption
7)察看视图的相关性(与哪些表或视图有关)
sp_depends view_name
8)删除视图:
drop view view_name
9)注意：当视图是多表视图时，如果要更新数据，不能通过视图来更新，
        需要通过创建视图的表格来更新数据，这样视图中的数据，就会自动更新。
         (创建视图的表有主键或唯一键约束)
------------------------------------------
6.Transact-SQL 高级编程
1)批处理:是一个SQL语句集合，这些语句作为一个单元来执行，一般的以go表示结束。(go可以省略)
2)注释:
--:表示单行注释
/* */：多行注释
3)变量. 局部变量(@)和全局变量(@@)
局部变量：使用一个@来表示，使用时必须先声明，才可使用，而且只能使用在某一范围内。
全局变量：使用两个@来表示，使用时用户不必声明,可以直接使用，在数据库范围内的任何角落都可以使用。
          全局变量是由系统声明的。
4)控制语句:
if...else:条件执行命令
begin...end:块语句命令
case:计算条件列表，返回多个可能结果表达式之一
while:循环语句命令。(waitfor delay:延时/waitfor time:定时输出/continue:表示继续/break:表示暂停)
print:显示语句命令。
--语法:
IF Boolean_expression
    { sql_statement | statement_block } 
 ELSE
    { sql_statement | statement_block } 
-----------------------------
BEGIN
    { 
        sql_statement
        | statement_block
    } 
END
----------------
CASE input_expression
    WHEN when_expression THEN result_expression
        ELSE else_result_expression
    END
CASE 搜索函数：
CASE
    WHEN Boolean_expression THEN result_expression
        ELSE else_result_expression
    END
--------------------
WHILE Boolean_expression
    { sql_statement | statement_block }
    BREAK
    { sql_statement | statement_block }
    CONTINUE
--------------
7.函数:用于执行某项特定功能
1)时间函数：
getdate():用于显示系统当前时间
day():用于返回指定日期中的日的部分
month():用于返回指定日期中的月的部分
year():用于返回指定日期中的年的部分
dateadd(指定的时间部分,加上的数值,指定的时间)
datediff():返回跨两个指定日期的日期和时间边界数
--语法
   datediff ( datepart , startdate , enddate ) 
              定的时间部分,开始时间,结束时间
-----------------
select 编号,姓名,datediff(year,出生日期,getdate())年龄
from 学员表
-----------
2)系统函数：
current_user:当前用户
select current_user
host_name():返回工作站名称(操作系统名)
select host_name()
--------------------
3)字符串函数：
LOWER():将大写字符数据转换为小写字符数据后返回字符表达式。
select lower('ABDEF')
----------------------
upper():小转大
select upper('asddfs')
----------------------
ascii():把字符转换成数字
select ascii('a'),ascii('A')
-----------------------------
char():把数字转换成字符
select char(11)
---------------
4)自定义函数：
--标量函数：用于返回单个值的函数，
--语法：
create function fun_单价(@编号 int)
returns money  ---表示返回值的类型
as
 begin
   declare @单价 money  ---通过变量来返回
    select @单价=单价 from 产品表  ---把变量付值
    where 编号=@编号  ---通过函数所带的参数
  return @单价 ---把变量的值返回
 end
-----
--内嵌表值函数:表的结构与函数中查询语句结构相同
--语法：
create function fun_aa(@id int)
returns table
as
 return select sum(数量)和,avg(数量)平均值,
         max(数量)最大值,min(数量)最小值
        from aa where 编号=@id
--多语句表值函数：表的结构是函数自己定义的，查询语句只需匹配就可以
--语法：
create function fun_aa_tab(@id int)
returns @tab table(和 int,平均值 decimal(5,1),最大值 int,最小值 int)
as
 begin
  insert into @tab
  select sum(数量),avg(数量),max(数量),min(数量)
  from aa where 编号=@id
  return
 end
5)修改函数:
alter function function_name
----------------------------
6)删除函数:
drop function function_name
----------------------------
8.存储过程：存储过程是保存起来的可以接受和返回用户提供的参数的 Transact-SQL 语句的集合.
            用于执行客户需求的某种功能。以一个名称存储，并作为一个单元处理，可以接受用
            户参数,也可以有返回参数，参数可以是一个，或多个。
----------------------
--分类：
--系统过程：主要用于从系统表中获取信息，一般以'sp_'为开头。
--用户自定义过程：主要用于完成用户的某项功能而编辑的。
--临时过程：用于临时执行某个任务而编写的，以#和##为开头。
--扩展存储过程：是sql 可以动态加载并执行的动态连接库文件(.dll).以'xp_'为开头。
-------------------
--过程的优点：
1）可以减少网络流量
2）增强代码的共享性和重用性
3）加快系统的运行速度
4）保证安全性
------------------------
1)系统过程:
sp_helpdb --查看数据库信息的
sp_renamedb --改数据库名
sp_rename --改数据库对象名(表，列，视图，函数，过程，触发器)
sp_helptext --查看定义对象的命令(视图，过程，触发器,自定义函数)
------------------------
2)用户自定义过程：
--语法：
create proc  procedure_name
@parameter datatype  
as  
sql_statement [ ...n ]

--使用过程实现四则运算
create proc p_aa
@a char(2),@b int,@c int
as
select case when @a='和'then @b+@c
            when @a='差'then @b-@c
            when @a='积'then @b*@c
            when @a='商'then @b/@c
        end as 结果
3)alter proc:修改
4)执行过程:
--1)直接使用名字
--2)execute 过程名
-------------------
/*在SQL中，存储过程的参数分为两类，一类为输入参数，另一类是输出参数，在过程中，
输入参数直接添加就可以，输出参数结尾使用 output表名使输出参数
使用带有输出参数的过程，需声明输出参数在过程中才能使用，