数据库笔记

--数据库的分离和附加
--分离：选数据库（右键）--任务--分离
--附加：选数据库（右键）--附加 --select* from sysobjects  where  name= "proc_%"

--在数据库中如果想从上到下直接运行下来要注意的：
--用到master数据库；查系统数据库；判断自己现在要创建的数据库的名字与系统数据库中
数据库的名字是否相同，如果有那么自己可以改自己现在要创建的数据库名字，或判断这个
数据库是否还有用，如果没用就将其删掉，创建新数据库；（分两种：第一种：指定路径；第
二种：不需要指定路径。指定路径：注意要用到on primary（易忘记的）主数据文件（指定
路径时在后面加上.mdf）的名字（name）因为是字符串所以要用到单引号（这里的主数据文
件名字不能与数据库名字一致）把创建的数据库放在哪（存放的路径（filename），以便下次
要用时直接附加即可）、文件的初始大小（size）（主数据文件要求：初始大小不能小于3mb）、
增长类型（filegrowth）（可以按照大小增长、还可以按照百分数增长）、最大大小（maxsize）
（可以是无限制unlimited，也可以是规定最大大小），这是主数据文件如果有次数据文件（在
指定路径时在后面加上.ndf）就直接在主数据文件括号外用‘，’直接再创建次数据文件，一
个数据库中必须有一个主数据文件，可含多个次数据文件，同时还有日志文件log on也和上
面一样指定路径时主日志文件后面是.ldf，次日志文件后面是.ndf，日志文件在一个数据库
中必须有一个主日志文件可含有多个次日志文件。
use master
go

if exists(select * from sysdatabases where name='L')
drop database L
go

create database L
on primary
(
 
)
--如果数据库已经建成
--要添加次数据库
alter database mydata--注释：修改数据库，数据库的名字是mydata

add file--增加次数据文件
--同样和建主数据文件一样也要名字、路径等--------在往数据库中新增次数据文件时，次
数据文件可以不用和主数据文件放在同一个地方

modify file--修改数据库中的文件 指定要进行更改数据文件的名字、只能改maxsize所以
修改数据库时只能有两项

remove file  my_dbdata--从数据库中移除my_dbdata这个文件

--删除数据库
drop database mydata

--查看数据库
sp_helpdb mydata
--如果要查词是什么按：F1

--重命名数据库
sp_helpdb 'mydata','mydata1'
--后面一个是新的名字，前面一个是要改的数据库名字

use master
--使用master数据库

go
if exists(select * from sysdatabases where name='testdb')
 drop database testdb
--判断系统数据库中是否含有名叫testdb的数据库，如果有就删掉

go
--创建数据库
create database mydata
--create：创建；database：数据库
--:mydata是数据库名字
on primary
--primary可以省略，primary：主要的
--:在主数据文件上创建
(
  name='my_dbdata',
--:my_dbdate叫做逻辑名称 主数据文件的名称
  filename='G:\my_dbdata.mdf',
--:物理名称 主数据文件的路径,写路径是数据库一定要加上后缀名
  size=3mb,
--主数据文件的初始大小，且主数据文件最小不能为3mb，后面的mb可以省略
  filegrowth=1mb,
--文件的增长 可以是数字也可以是百分比
  maxsize=unlimited
--可以限制它的最大值也可以不限制。上是没有限制
),
(
 name='my_dbdata1',
 filename='d:\my_dbdata1.ndf'
)
--次数据文件，名字和后缀名都与主数据文件不一样
log on
--这是创建日志
(
  filename='G:\my_dbdata.ldf',
  --同上
)
--数据库中必须有一个主数据文件和一个日志文件，次数据文件和日志可以有多个。
--如果要用代码写数据文件次数据文件接在主数据文件后面（当主数据文件建完后在括号外
面用‘逗号’再建次数据文件。日志也是一样）

--如果数据库已经建成
--要添加次数据库
alter database mydata
--alter:修改
add file
--增加文件
(
  name=''
--不能和主数据文件的名字一样
  filename='G:\my_dbdata.ndf'
--路径：可以不和主数据文件放到一个盘中     
--（其它）
)

--修改数据库中的文件（就是在括号里面写的内容）
alter database mydata
modify file
--modify:修改
(
  name=''
--这里的名字指定要进行更改数据文件的名字（所以主数据文件和次数据文件的名字不能一
样）
  maxsize=100mb
--这里的100是该后的
) 

--删除文件
alter database mydata
remove file  my_dbdata
--remove：移除;
--移除要删文件的名字

--查看数据库
sp_helpdb mydata
--如果要查词是什么按：F1

--重命名数据库
sp_helpdb 'mydata','mydata1'
--后面一个是新的名字，前面一个是要改的数据库名字

--删除数据库
drop database mydata

--数据库的分离和附加
--分离：选数据库（右键）--任务--分离
--附加：选数据库（右键）--附加             -------常用的

--在创建表时：
--要使用你自己创建的数据库，如果不说明自己放在哪个数据库系统默认的是master中；判
断系统表中是否含有于自己所要创建的表明一样的表，如果有自己是不是要删掉或是改动现
要创建的表的名字sysobjects（易忘记）；创建表：首先明确表的结构，在表中分为行和列，
在数据库中所写的字段是表中的列，后面的是表的内容的字段类型，有些要注意在后面加上
范围，有的则不需要加。
--如果表已经建成
--alter table student

add birthdate datetime
--add +列名+对应的列的类型  --注释：在表中添加一列

alter column stuid varchar(50)  --修改stuid这一列中的字段类型范围将其改为50
--alter +column+列名+所要改的类型的范围

drop column birthdate --删除不需要的列
--drop+column+列名 

--查看表
sp_help student

--修改表名
sp_rename student,shudent1

--删除表
drop table student

--往以建表中插入数据
--插入数据分为往表中所有列分别插入，或者是指定列下面插入数据，如果是所有的在
values后面可以加上所有列的列名也可以不加列名，如果是指定的列下面插入数据那么在
values后面就必须加上是哪个列要加上数据，在后面写上列名，两种都要注意所加的要‘对
应’======into可以省掉
insert into friends values(10000,10001)
insert into student(stuid,stuname) values('001','大大')

--如果一个表已经建好
--发现插入的数据是错误的就要进行修改
update student set stusex='女'where stuname='小小'--注释：修改student表设置（将
正确的数据写在设置后面要注意的是：要强调是哪一列要改成什么）后面where是条件（一
定是与要修改的数据是在同一行，选定这一行除错误数据外的其他列的列名，指定列名所对
应的这一行正确的数据）

--删除表中的所有数据
delete table student
truncate table student
--两者的区别在于：delete是一条数据一条数据的删掉，而truncate是直接删除表中的所
有数据，所以当数据很多时就会出现快慢之分，还有一个很重要的区别是：数据虽然删掉但
表的结构还在

--删除指定条件下的数据
delete student where stuid='001' and stusex='女'
--直接是删除表然后就是接条件（通常状况下删除的是一整行数据）

--删除表
--drop table 表名

--建表是要注意的：表中的细节

--切换数据库：use mydata   --在执行的左边

--判断表是否已经存在
if exists(select * from sysobjects where name='student' and type='u')
 drop table student
go
--创建表
create table student
--table:表
(
  stuid varchar(20),
--stuid表示第二列的名字（因为第一列是系统默认的），varchar是这一列中‘数据’的类
型
  stuname varchar(20),
)

--增加列（也叫增加字段）
alter table student
add birthdate datetime
add+列名+对应的列的类型

--修改字段（只能修改数据类型的范围）
alter table student
alter column stuid varchar(50)

--删除字段
alter table student
drop column birthdate

--查看表
sp_help student

--修改表名
sp_rename student,shudent1
--同数据库一样

--往表里面插入数据
--插入部分数据
insert into student(stuid,stuname)
--insert:插入；into可以省略，()不能省略，如果是每一列都要插入数据（）可以省略一
定要做到’对应‘
values('001','大大')                                   

--如果表已建成但发现有数据出错
--修改数据
update student set stusex='女'
--修改student表设置哪一列要改成什么
where stuname='小小'
--条件是其它列的列名=....但要与改的内容那一行对应
--如果是修改所有的数据不需要条件也就是不用where直接改
--eg：将一个人的年龄加2岁
update student set stuage=stuage+2
where stuname='小小'

--删除指定条件的数据(一般是行的)
delete student
where stuid='001' and stusex='女'

--删除所有数据(数据虽然删了，但表的结构还是没变。eg：主键还在等等)
delete table student
truncate table student
--区别是：delete是一行一行的删除而truncate是直接删除

--删除表
drop table student

--自动增长
stuid int identity(100,2)
--identity只能用于整型；后面的100是初始值，2是一次增长多少，也可以不加那就是默
认的从1开始每次增长1
--如果用了自动增长那就可以免条件
insert into student(stuname)
values('大大')
--这个表中本来是有两列的但添加内容时只写了stuname因为有个条件是自动增长另一列是
不用写的，也可以不用（）在values中也只需要写stuname的不然就错了

--不能为空
stuname varchar(20) not null
--这一列的内容不能不写，不然就报错

--主键(就是这一列是唯一的且不能为空。要定义这列为主键首先就要规定这一列不能为空)
stuid varchar(20) constraint pk_stuid primary key
--constraint pk_stuid可以省略

create database person
create table student
(
  stuid varchar(20) constraint pk_stuid primary key,
--主键约束constraint pk_stuid可以省略
  stuname varchar(20),
  stuage int constraint ck_stuage check(stuage between 15 and 30),
                                  --check(stuage>15 and stuage<30)
--检查约束（设定年龄的范围）
  stusex char(2) check(stusex='男' or stusex='女')constraint df_stusex default '
男'
                                               --默认约束、如果不填就默认的是’男
‘
                 --check(stusex in ('男','女'))
)

insert into student
values('001','大大',20,'男')

--表2课程表
create table course
(
  courseid varchar(20) primary key,
  coursename varchar(50) constraint up_coursename unique,
                                                --唯一约束（与主键的区别是它可以
有一项是空）
)

--表3成绩表
create table score
(
  stuid varchar(20) constraint fk_stuid foreign key references student(stuid)on
delete cascade on update cascade,
--这个最突出的地方就是用到了'外键',用外键就是和别的表'联系'    
--级联删除：如果你要删除一个人的信息，找到一列对应项就可以将 它所对应的一行全部删
掉
--级联更新：如果把一个学生的编号修改了，那么在成绩表里面对应的把那个学生的编号也
修改了
--constraint fk_stuid foreign key是可以省略的
  courseid varchar(20) references course(courseid),
  score int check(score between 0 and 100),
  constraint pk_score primary key(stuid,courseid)
--复合主键
)

--如果一个表已经建好想再添加约束
--增加主键约束
alter table student1
alter column stuid varchar(20) not null
add constraint pk_stuid primary key(stuid)
--要想增加主键约束条件：列中的内容必须不能为空

--增加检查约束
alter table student1
add constraint ck_stusex check(stusex in ('男','女'))

--增加缺省约束
alter table student1
add constraint df_stusex default '男'for stusex

--增加唯一约束
alter table course1
add constraint uq_coursename1 unique (coursename)

--增加外键约束
alter table score1
add constraint fk_stuid foreign key(stuid) references student1(stuid)

--删除约束
alter table score
drop constraint pk_stuid

use pubs
select * from dbo.authors
--select：查询；从那个表查

select au_id,au_lname from dbo.authors

select au_lname+'.'au_fname from dbo.authors

select title_id,title,[type],price*0.8 as '打折后'from dbo.titles
                     --如果出现关键字就要用到[];打折后=price*0.8、as可以省略

select top 10 *from dbo.authors
--查前十的

select top 10 percent * from  dbo.authors 
--查前百分之10的

select distinct au_id from dbo.authors
--如果au_id有重复的就要用到distinct

--条件查询
select au_lname,au_fname,city from dbo.authors
where state='ca'

select * from dbo.titles
where pub_id='0877'and price>16

select * from dbo.titles
where advance<=5500 and ([type]='business' or [type]='psychology')
--因为and和or没有优先级所以用到了（）

select title,pubdate from dbo.titles
where pubdate between '1991-1-1' and '1991-12-31'
--日期类型日期要用引号

select * from dbo.authors
where state='ca' or state='in' or state='md'
--在查询中or表示‘和’的意思；而and则是两个条件都要满足
where state in ('ca','in','md')

--模糊查询
select phone from dbo.authors
where phone like '415%'
--查以415开头的电话号码，%可以代替多个字符、_下划线一个字符、[]匹配其中的任何一
个、[^]不匹配其中的任何一个

select * from dbo.authors
where au_fname like'[cs]hery1'

select pub_name from dbo.publishers
where pub_name like '[^abcdef]%'
where pub_name like '[^a-f]%'                      
--[^]表示不是里面任何一个字符，查出来的结果就是：不含abcdef开头的

--排序查询
select title,price from dbo.titles
--查的是title这一列还有pric列
where [type]='business'
--条件是type这一列中的business
order by price desc
--找到business对应的价格把他们进行排序
--排序要用到order by默认的是升序（asc）降序是desc

--not和<>的用法
select * from dbo.titles
where [type]<>'business'
where not [type]='business'
--找type这一列要找的内容不包含business

select * from dbo.titles
where royalty is not null
--查royalty这一列中不是空的所有项

--统计查询
select count(*) from titles
where [type]='business'
--这里的count是数的意思，要数的是type这一列中business有多少个

select count(distinct state)from dbo.authors
--数作者所在的州有几个但不能重复

select avg(price) from dbo.titles
where type='business'
--查type这一列中的business的平均价格
--avg（）、max（）、min（）、sum（）聚合函数（统计函数）

--分组统计查询
select [type],avg(price) from dbo.titles
--查询type这一列，求每一组对应价格的平均值
group by [type]
--将type这一列中的相同项分到一起

select [type],avg(price) from dbo.titles
where royalty=10
group by [type]
--查type这一列，按照是一样的进行分组,取royalty=10的，再求各组的平均值

select [type],avg(price) from dbo.titles
group by [type]
having avg(price)>19
--求平均价格大于19的组。
--如果聚合函数要做条件一定是在having后面且在group by后面

select getdate()
--得到当前系统日期

select dateadd(yy,3,getdate())
--在当今日期上加三年
select year('1991-1-2')
--只查年
select month('1991-1-2')
--select datediff(--月日年任选一，被减数，减数)

select datediff(yy,stubirthday,getdate())from student

--要想‘连接’就一定要找到共同的列
--交叉连接
select * from dbo.titleauthor cross join dbo.titles
where dbo.titleauthor.title_id=dbo.titles.title_id

--内连接
select buyers.buyer_id ,buyer_name ,prod_id,qty
from buyers inner join sales
on buyers.buyer_id=sales.buyers_id

--外连接
select buyers.buyer-id,buyer_name,prod_id,qty
from buyers left outer join sales
on buyers.bu

--自连接

--嵌套子查询
select au_id,au_lname,au_fname from dbo.authors
where au_id in
(
  select au_id from dbo.titleauthor
  where title_id in
  (
     select title_id from dbo.titles
     where [type]='popular_comp'
  )
)

--相关子查询
select au_lname,au_fname from dbo.authors
where 100 in
(
  select royaltyper from dbo.titleauthor
  where dbo.authors.au_id=dbo.titleauthor.au_id
)

--自连接的另一种方法      待解决
select emp_id from dbo.employee a
where hire_date in
(
  select hire_date from dbo.employee b
  where a.emp_id<>b.emp_id
)

--子查询中用到增删改
update dbo.titles set price=price*2
where pub_id in
(
  select pub_id from dbo.publishers
  where pub_name='new moon books'
)

--批量插入
insert t_test
--这个t_test是新建的一个表
select emp_id,fname from dbo.employee
--将从表dbo.employee中查到的数据插入到新表中，因为用insert只能插入一条数据所以
这里没有values

--any和all
select title,price from dbo.titles
where price>all
(
  select price from dbo.titles
  where [type]='business'
)

select title,price from dbo.titles
where price<any
(
  select price from dbo.titles
  where [type]='business'
)

--exists和not exists
select * from dbo.authors
where not exists
(
  select * from dbo.titleauthor
  where dbo.authors.au_id=dbo.titleauthor.au_id
)

--查询
--单表查询
select  top 3  distinct *           
from
where               between ...and .   in   like    and not  or
group by          max()   min()  sum()  avg()  count()
order by           asc    desc
--连接查询
--交叉连接  cross join    where
--内连接   inner join    on
--外连接  
--左外  left outer join
--右外 right outer join
--全外 full outer join
--自链接
--子查询  in   > all  any  <

--前九章

--第十章开始
--数据完整性：数据的精确性和可靠性
--约束：确保数据的准确性和一致性
--完整性约束：与表有关的约束、域约束、断言

Not Null：不能为空(主要是表中列所对应的值)非空约束
Primary Key：主键(唯一标识)主键约束
Unique ：唯一性唯一约束
Foreign Key：外键(作用于两张表的联系)外键约束
--Check：检查约束
--解释：一张表中可以有多个字段充当主键（称作复合主键）
--eg：
--主键约束和唯一约束的区别
--主键约束不能为空，并且是唯一标识的作用
--意思是说：如果一张表中某个字段定义了逐渐约束那么在往表中添加数据的时候不是不能
让这列是空的
--唯一约束可以是null也就是可以允许一个为空的
--
--外键一般和主键结合使用
--外键就是将两个表联系起来的
--靠什么关系进行连接呢？就是通过主键
--如果存在两张表第一张表的某个字段定义了主键约束那么可以将这个字段作为另一张表中
某个字段的纽带，然这两张表产生关联，如果外键约束那么相对应的字段必须是定义了主键
约束的。
--检查约束：能进行简单的判断
--eg：
--constraint Check_One check(id in (1,2,3,4) and salary>1000 and salary<5000)

--视图                           

--创建视图                      
create view 视图名         --主要是后面的查询语句    
as                             
select ~~~~~~~~~~~             
                              
--查询视图                       
select * from 视图名      --查看视图是查看表里面的东西（实际上是通过视图来查询表）     
                               
--查看视图                       
sp_helptext 视图名        --查看视图中的select语句

--视图的加密  
create view 视图名        --将视图加密，一般将视图加密后就无法查到视图中的select
语句；同时
with encryption           --加密是在刚开始创建视图时就要操作的，比创建视图多一个
as                        -- with encryption
select ~~~~~~~~~~~

--修改视图
alter view 视图名         --因为修改视图修改的是表的结构所以用到的是alter不是
update
as
select ~~~~~~~~~~~

--删除视图
drop view 视图名

--索引
--创建索引
create nonclustered index 索引名字on 表名(按照什么字段创建索引（字段可以是多个，
用逗号隔开）)
--这里的索引时非簇集索引，如果是要创建簇集索引就将前面的non去掉，还有一总索引是
唯一索引只需将单词改成unique就行了
--索引
--当数据库中某个表的数据多到一定程度时，查询起来就非常慢，如果在表中定义了索引，
就可以提高检索数据的能力。

--为什么会提高检索数据的能力呢？
--创建表时，存储的实际上是有两部分组成的：
--1.存放表的的数据页面；.存放索引页面
--注：相对来说索引页面要小得多

--数据库中做查询操作时：系统先搜索索引页面，从中找到所需要的数据指针，通过数据指
针找数据

--索引分类
--簇索引（clusteredindex）、非簇索引（nonclusteredindex）
--区别：簇索引一张表中唯一，非簇索引一张表中可以同时有多个

--簇索引：对表的物理数据页面中的数据进行排序，重新存储到磁盘上
--优点：查找数据非常快
--缺点：需要的空间特别大

--创建索引
create index 索引名字on 表名（表中的哪个字段）
--注：在哪个表的那一列上面建立或是那几个字段上面建立索引，多列时越靠前的越是快，
一般默认的是非簇索引

--还有unique，clustered
--当建表时如果某个字段上面定义了主键，就自动生成一个簇索引
--
--流程控制
--定义变量
--赋值
--条件
-- 条件
--条件

begin...end
--相当于C#中的{}
--当要执行的语句只有一句话的时候就可以省略begin...end
if...else
--用来判断当某个条件满足的时候执行某段代码，条件不满足时执行另外一段代码

while
数据库中只有while循环
Break：结束循环
Continue：结束当前循环执行下一轮循环
Declare：定义变量

给变量赋值：Select、set
两者的区别
前者可以一次给多个变量赋值，后者一次只能给一个变量赋值
eg：set @变量名=''
select @变量名='',@变量名='',
/*定义变量时要注意的是：关键字declare+@加变量名（这里的@绝对不能省略）+要定义的
变量的类型（如果定义的变量是字符串，则一定要写变量的长度）eg：declare @i int；declare
@str varchar（20）。
变量定义完之后一定要给变量赋上初始值，真是就要用到赋值时的关键字set、select（区
别：用set的时候是只能给一个变量赋值，而select可以给多个变量一起赋值eg：set @i=1；
select @i=2,@j=3）。
变量定义完后，一定要看是否存在，所以就要将所定义的变量查出来、或者是打印出来这时
就要用到关键字select或者是print，语法是：select+@变量名；或者是print+@变量名。
*/

--查找姓名为Davolio的员工信息
select * from dbo.Employees where LastName='Davolio'

--查询产品表中平均单价如果大于30元，就显示产品平均单价超过30元，否则就显示产品
平均单价没有超过30元！
declare @price float
select @price=avg(UnitPrice) from dbo.Products
if(@price>30)

--或者是
if(select avg(UnitPrice) from dbo.Products)>30

--下面公用
begin
  print '产品平均单价超过30元！'
end
else
begin
  print '产品平均单价没有超过30元！'
end

--做这样的题目先看题目意思：‘查询产品表中平均单价’那就先将avg()求出来
select avg(UnitPrice) from dbo.Products 
--查到的结果是28.8663，这个数要与30进行比较

--接着：‘平均单价如果大于30元’要看单价是不是大于30元的
--查到的结果是小于30的可以将查到的结果直接作为条件；或者是定义一个一个变量将平均
值付给它，将这个变量作为条件和30比较大小
--这里讲到得是流程控制语句所以还是用赋值的方法

--例：查找titles表中如果有编号为‘TC5555’的书，就删掉它，并提示‘删除成功’,否
则提示‘编号没有找到’
declare @num varchar(20) set @num='TC5555'
if(select count(*) from dbo.titles where title_id=@num)>0
begin
  delete dbo.titles where title_id=@num
  print '删除成功！'
end
else
  print '编号没有找到'

--做这样的题目时，先查询一下看是否存在
select * from dbo.titles where title_id='tc5555'--这样是为了构思
--查询的结果是空的，说明这题要打印的是‘编号没有找到’
--怎样写出条件呢？就是这题的重点，所以必须是数出来的，当记录大于0时说明存在否则
就是不存在的，这样同时也用到了这讲的知识点定义变量

--在pubs数据库中查询每个作者所居住州的中文名称
select au_fname,au_lname,state,洲=
(
   case
     when state='ca' then '加利福利亚'
   else '其他'
   end
)
from dbo.authors

/*做这题时首先要知道数据库中是否含有州的中文名字，这是做题的重点，如果连着都不知
道话就没办法做后面的了
所以先查询一下select * from dbo.authors 查询这样的一个查询语句很显然的知道数据库
中是不不含有州的中文名字的，所以要想到将简写的转变成中文的，自然就像用到了case
和其的用法--------这时需要记住的*/

select au_fname,au_lname,state,洲=
(
   case state
     when 'ca' then '加利福利亚'
   else '其他'
   end
)
from dbo.authors
/*区别：上面一个可以用在不等于的情况下*/

--求１+２+３+……+１００
declare @num int,@sum int
 select @num=1,@sum=''
while(@num<=100)
begin
  set @sum=@sum+@num
  set @num=@num+1
end
print @sum

declare @num int,@sum int
 select @num=0,@sum=''
while(@num<100)
begin
  set @num=@num+1
  set @sum=@sum+@num 
end
print @sum
--做这一题首先要知道原理
--和C#一样看要定义那些变量，各个变量是干什么的，怎么用
--因为在数据库中只有while循环所以也要讲while与C#中的for区别考虑进去（关键是自
增或自减时）
--循环的三要素：循环变量看是什么、范围（或者叫步长）、增还是减
--特别注意的是语法《重点》

--打印1到100，其中能被3整除的数不用打印
declare @num int set @num=1
while(@num<100)
begin
  set @num=@num+1--设置循环变量的自增
  if(@num%3=0) --判断的条件
  continue--当上面的条件被满足时就跳到下一轮循环
  print @num--打印不符合上面if条件的值
end
--这题要注意的是continue的用法（结束当前循环跳到下一轮循环）

--计算1*2*……*100，如果计算结果>10000，则停止计算
declare @num int,@sum int select @num=1,@sum=1
while(@num<100)
begin 
  set @sum=@sum*@num
  set @num=@num+1--设置循环变量的自增------------这里可以将这放在上面但是给变量
赋值时就要重新考虑是从几开始的也就是放在@sum上面时
  if(@sum>10000)
  break--当满足这个条件的时就要将循环打断
end
print @sum
--这一个要注意的是break的用法（彻底结束循环）

--打印九九乘法表
declare @i int,@j int,@str varchar(200)
select @i=1,@j=1,@str=''
while(@i<=9)
begin
  select @j=1,@str=''--这里是要特别注意的：当第一轮循环完时就将内层循环的变量和输
出的结果重新赋值
  while(@j<=@i)
  begin
    set @str=@str+cast(@j as varchar(10))+'*'+cast(@i as
varchar(10))+'='+cast(@i*@j as varchar(10))+space(3)
/*这里有数据类型的转换：关键字（cast,convert）语法不一样的需要记住的*/
    set @j=@j+1--设置内层循环变量的值自增
  end
   print @str
   set @i=@i+1--设置外层循环变量的值自增
end
--如果定义变量时将外层循环的值定义成1那么外层循环变量的自增将是在最后面的

--求100到999内的水仙花数
declare @i int,@a int,@b int ,@c int
select @i=100
while(@i<1000)
begin
  set @a=@i/100
  set @b=@i%100/10
  set @c=@i%10
  if(@a*@a*@a+@b*@b*@b+@c*@c*@c=@i)
  print @i
  set @i=@i+1
end

--过2秒钟后显示当前系统时间
select getdate()
waitfor delay '00:00:02'

--到中午12点，显示“放学了”。

--判断登陆
create database D
go
use D
go
create table P
(
  p_name varchar(50),
  P_pwd varchar(50)
)
insert into P values('杨辉','123')
select * from p

declare @name varchar(50),@pwd varchar(50)
select @name='杨辉',@pwd='123'
if(select count(*) from p where p_name=@name)>0
begin
  if(select count(*) from p where p_name=@name and P_pwd=@pwd)>0
  begin
    print '登陆成功！'
  end
  else
  begin
    print '密码错误！'
  end
end
else
begin
  print '用户名错误！'
end

--分页
create proc pag1 @num int,@page int as
if(@page=1)
    select top (@num) * from  dbo.Products order by productid
else
select top (@num) * from dbo.Products where ProductID not in (select
top((@page-1)*@num) ProductID from dbo.Products order by ProductID) order by
ProductID

execute pag1 10,2
--这里是创建一个带参数的存储过程
--作用是：当一个表中的数据很多时，为了看得方便将其分页处理，以便于查看。
--首先要考虑的是：有多少条数据、要按怎样的情况分，也就是一页有多少条数据。所以要
定义2各变量分别表示页数、和一页之中数据的条数；
--这里要注意的是：第一页不能忘记。就是当页码数是1时就要另外的考虑进去，当页码是
1的时用一个SQL语句来讲第一页的数据显示出来
select top (@num) * from  dbo.Products order by productid
--这句话将的是当用户输入一个数字（这个数字是一页的数据条数）后面的分组（是按照编
号进行的）；也就是说当用户想查看第一页时就在执行存储过程的时候将参数填上（分别是页
码、一页的条数）
--后面与前面是有一定的关系的，就是将前面的数据‘去除’
--当要查看的数据不是第一页的而是其它页时，就要将前面的数据不用考虑进去所以要找出
其中的关系（也就是：当要查看第二页时将第一页的数据不作考虑，就是查看第二页时数据
not in第一页中所以出去前面的影响的SQL语句是select top((@page-1)*@num) ProductID
from dbo.Products  order by ProductID）
--后面还要接一个分组，是针对所有数据的

--使用Northwind
--1) 如果产品单价有小于20元的就显示“ok”
if(select count(*) from dbo.Products where UnitPrice<20)>0
print 'OK'--如果上面的条件满足就将OK打印出来
--上面的SQL语句查询出来的是一个结果，所以可以用来作为条件，如果不是唯一值是不能
作为条件使用的

if exists(select * from dbo.Products where UnitPrice<20)
print 'OK'
--上面是判断是否存在价格小于20的如果存在就将OK打印出来

if(select min(UnitPrice) from dbo.Products)<20
print 'OK'
--这里的最小值也是只有一个

--2) 如果产品单价'有'小于100元的就显示“yes”否则就显示“no”
if(select count(*) from dbo.Products where UnitPrice<100)>0
print 'yes'
else
print 'no'
--这里是数表中的记录如果价格有小于100的数出来的结果如果是大于0的证明有，有就将
yes打印出来，如果没有就将no打印出来

--3) 如果产品的平均单价大于20就显示“yes”，否则显示“no”
if(select avg(UnitPrice) from dbo.Products)>20
print 'yes'
else
print 'no'

--4) 如果产品平均单价大于20就将平均单价减1，如果小于18就显示“继续”，并且再
继续减1，如果小于15，就显示“停”最后显示这个数。
declare @price float
select @price=avg(UnitPrice) from dbo.Products
if(@price>20)
begin
  while(1=1)
    begin 
    set @price=@price-1
    if(@price<18)
    print '继续'
    if(@price<15)
      begin
      print '停'
      print @price       
      break
      end        
    end
end

--5) 查询产品表中所有信息，并在3秒钟后再将结果显示出来。
select * from dbo.Products
waitfor delay '00:00:03'

--利用玩具数据库完成
--6) 查询是否有“拉尔森”这个顾客，若有，则显示该顾客的信息，没有，则作出相应
提示。
if(select count(*) from dbo.购物者 where 名='拉尔森')>0
select * from dbo.购物者 where 名='拉尔森'
else
print '没有'
--这里是如果存在就将其查询出来，如果没有就打印没有

--7) 需要获得定单号为“000003”的货物的运送状态，如果该批货物已经投递，则显示
消息“货物已经到达”，否则显示消息“货物尚未到达”（提示：如果已经投递，则运输状态
属性值为“d”，未投递为“s”）。
if(select 运送状态 from dbo.运输情况 where 定单号='000003')='s'
print '货物尚未到达' 
else
print '货物已经到达'

--8) 增加一个新的订单信息如下：
--insert into 定单详情
--values（‘000011’，‘000008’，100，‘N’，NULL，NULL，14.99）
--但是需要先检查玩具的库存是否充足，如果库存量不足，则给出相应的提示，并且拒绝新
记录的增加。
--首先分析：怎么样才算库存不足（就是订单的数量（这里的数量是用户所还要买的数量）
是否大于玩具表中的数量）
if exists(select 数量 from dbo.玩具 where 玩具号='000011')<100
print '库存不足！'
else
insert into dbo.定单详情 values('000011','000008',100,'n',null,null,14.99)

--9) 统计各个玩具的库存情况，按以下格式显示
--玩具号  玩具名  库存量  状态
--其中状态取决于库存量，若库存量在 80 以上，状态则为“充足”，在 60 到 80
--之间，状态则为“一般”，否则为“不足
select 玩具号,玩具名,数量,状态=
(
  case
    when 数量>80 then '充足'
    when 数量<60 then '不足'
    else '一般'
  end
)
from dbo.玩具

--10) 修改各包装的价格，若价格金额达到 2 或以上，则增加 1；在 1.5 和 2 之间，则
增加 1.5，否则增加 2。
update dbo.包装 set 包装价格=
(
  case
    when 包装价格>2 then 包装价格+1
    when 包装价格<1.5 then 包装价格+2
    else 包装价格+1.5
  end
)
from dbo.包装

--11) 将每件玩具的价格增加 0.5,直到玩具的平均价格达到 24.5,但是不超过 24.5，任
何一件玩具的价格最高不得超过 53。

--12) 如果员工人数超过100，就显示“很多”否则显示“太少”
if(select count(*) from dbo.Employees)>100
print '很多'
else
print '太少'
 
--13) 声明一个变量，赋值为“10”，再循环减小1，当等于8时，显示“现在是8”，当等
于6时显示“现在是6”，当小于5时，显示“小于5了”，到这个变量为2时，退出循环。
declare @i int
set @i=10
while(@i>=2)
begin
  set @i=@i-1
  if(@i=8)
  print '现在是8'
  if(@i=6)
  print '现在是6'
  if(@i<5)
  print '小于5了'
end

--14) 声明三个变量分别保存产品单价的平均数、最大数、最小数，并分别产生“最大数”、
“最小数”、“平均数”这三个字段，等待4秒钟后显示出来。
declare @a float,@b float,@c float
select @a=avg(UnitPrice),@b=max(UnitPrice),@c=min(UnitPrice) from dbo.Products
select @a 平均数,@b 最大数,@c 最小数
waitfor delay '00:00:04'

--第13讲 存储过程（一）
/*为什么会出现存储过程？
优点：模块化的程序设计、高效率的执行、减少网络流量、保证系统的安全性
什么是模块化的程序设计？
把大型软件按照规定的原则划分成一个个较小的、相互独立但又相互关联的模块，叫做模块
化设计
*/
/*什么是存储过程？
是一组预编译好的完成特定功能的SQL语句。
它存储在服务器上的一个对象，可通过对象名来调用
*/

/*首先是判断数据库中是否含有这样的一个存储过程*/
if（select * from sysobjects where name='存储过程名字' and type='p'）
drop procedure '存储过程名字'

/*系统存储过程（名字以sp_开头）扩展存储过程（名字以xp_开头）临时存储过程（名字以
#开头）远程存储过程、自定义存储过程*/
--重点是：自定义存储过程

--语法
create procedure 过程名
  as
  sql语句组

执行格式
 [execute] 过程名
--中括号表示可以省略的部分，担当不是第一句话时就不能省略（最好不要省略，每次都将
其加上）
--专业解释：
/*创建时：create proc 应放在一个单独的批中
执行时：如果是批中的第一条命令,可以省略execute
执行时：一个批中可以执行多个存储过程*/

/*创建不带参数的存储过程*/
--eg：
eg:create proc p_5 as select getdate()
/*这里的procedure简写成proc，p_5是存储过程名字，后面接的是一个sql语句*/

--创建之后就是执行存储过程
execute p_5
--这是不带参数的存储过程，如果带参数就不一样了，如果还想将带一点就直接选中存储过
程名字直接运行就可以了

--存储过程的重新命名
sp_rename oldname,newname

--查看存储过程
sp_help

/*如果一个存储过程已经建立了，发现错了这时就可以对存储过程进行修改或者是删除后重
新建立*/
--修改存储过程很简单，只需将要执行的内容进行修改，修改好后直接将create改成alter
就行了
--另一种是将存储过程删掉然后再重新建了一个
drop procedure p_5
--注：系统存储过程是不能删除的

--------------------------------------------------------------------------------
----------------------
存储过程题目

--1、 简答题：
--1) 存储过程的修改和删除关键字分别是什么
/*修改的关键字是：alter；删除的关键字是：drop*/

/*只要是存储过程在做题目时先不用管它，先将后面的条件依次满足再说，将后面写好后只
需在前面加上一句create procedure p_1 as，这里的p_1是存储过程的名字*/

--2、 问答题：
--利用northwind数据库完成
--1) 创建存储过程，查询员工信息表中，尊称为“ms.”的员工的编号和姓名。
create procedure p_1 as
select EmployeeID,FirstName,LastName from dbo.Employees where
TitleOfCourtesy='ms.'

--2) 创建存储过程，查询产品信息表中，价格大于平均价格的产品信息。
create procedure p
as
select * from dbo.Products where UnitPrice>
(select avg(UnitPrice) from dbo.Products)

--利用pubs数据库完成
--3) 创建存储过程，查询图书信息表中，图书类型为“popular_comp”，且图书单价大于
平均单价的图书编号、图书名称及图书价格，以打印的形式输出
declare @a varchar(100),@b varchar(100),@c float
select @a=title_id,@b=title,@c=price from dbo.titles where type='popular_comp' and
price>(select avg(price) from dbo.titles)
print @a,@b,@c

--4) 创建存储过程，查询作者信息表中，作者所在州为“ca”的作者信息
create proc p_4 as select * from dbo.authors where state='ca'

--5) 创建存储过程，打印输出当前日期和时间
create proc p_5 as select getdate()

--6) 创建存储过程，查询作者名中有“s”的作者信息
create proc p_6 as select * from dbo.authors where au_lname like '%s%'

--7) 创建存储过程，查询编号为“0877”的出版社名
create proc p_7 as select pub_name from dbo.publishers where pub_id='0877'

--8) 创建存储过程，查询图书单价在10元以上的图书信息--Northwind
create proc p_8 as select * from dbo.Products where UnitPrice>10

--利用学生库完成：
--1、 建立存储过程显示所有学生的记录
create proc p_1 as select * from dbo.student

--2、 建立存储过程显示所有女生的记录
create proc p_2 as select * from dbo.student where stusex='女'

--3、 建立存储过程显示所有软件学院的男生记录
create proc p_3 as select * from dbo.student where studepartment='软件工程' and
stusex='男'

--4、 建立存储过程显示所有王姓学生的记录
create proc p_4 as select * from dbo.student where stuname like '王%'

--5、 建立存储过程显示所有年龄在20岁以上的男生记录
create proc p_5 as select * from dbo.student where stusex='男' and
datediff(yy,stubirthday,getdate())>20
--这里用到的是日期相减用到的是datediff这个关键字，同时要知道其语法

--6、 建立存储过程显示所有软件学院的、1989年以后出生的女生记录
create proc p_6 as select * from dbo.student where stusex='女' and studepartment like
'软件%' and year(stubirthday)>1989
--这里用到的是从时间中只取出年

--7、 建立存储过程显示 所有非数码学院的年龄在25岁以下的女生记录
create proc p_7 as select * from dbo.student where
datediff(yy,stubirthday,getdate())<25 and stusex='女'and studepartment<>'数码学院
'
--这里的非就是不等于的意思

--8、 建立存储过程显示所有数码学院或网络学院的21岁以上的女生记录
create proc p_8 as select * from dbo.student where stusex='女' and
datediff(yy,stubirthday,getdate())>21 and studepartment like '[(数码)(网络)]%'
--这里的数码、网络是一体的因为有括号，如果去掉小括号就是从中选数字、码字、网字、
络开头的

--1、 创建一个存储过程，作用是从3个变量中找出最大的那个打印出来
declare @a int,@b int,@c int
select @a=2,@b=3,@c=5
while(1=1)
begin
   if(@a>@b and @a>@c)
   select @a a的值
   if(@b>@a and @b>@c)
   select @b b的值
   if(@c>@b and @c>@a)
   select @c c的值
break
end

--将只进行互换
declare @a int,@b int,@c int --换值
select @a=2,@b=3,@c=''
while(1=1)
begin
  set @c=@a
  set @a=@b
  set @b=@c
  select @a a的值,@b b的值
  break 
end

--2、 创建一个存储过程，作用是打印九九乘法表
declare @i int,@j int,@str varchar(200)
--定义三个变量
select @i=1,@j=1,@str=''
--给定义的变量赋值
while(@i<=9)
begin
  select @j=1,@str=''/*当第一层循环完时就重新给被乘数和字符串重新赋值*/

  while(@j<=@i)
  begin
  set @str=@str+cast(@j as varchar(10))+'*'+cast(@i as varchar(10))+'='+cast(@j*@i
as varchar(10))+space(3)
  set @j=@j+1--被乘数的自增
  end/*计算过程*/

  set @i=@i+1--乘数的自增
  print @str--打印字符串
end

--3、 创建一个存储过程，作用是求100以内质数的和
 /*label1.Text = "";
            int sum = 0;
            for (int i = 2; i < 100; i++)
            {
                bool bla = true;
                for (int j = 2; j < i; j++)
                {
                    if (i % j == 0)
                    {
                        bla = false;
                    }               
                }
                if (bla == true)
                {
                    sum += i;
                    label1.Text = sum.ToString();
                } 
*/  
declare @sum int,@a int,@b int,@x int select @sum='',@a=2,@b=2,@x=1
while(@a<100)
begin
 
end                             

--4、 创建3个存储过程，分别打印下列图形
*
**
***
****
******
 /*
label1.Text = "";
            for (int i = 1; i <= 5; i++)
            {
                for (int j = 1; j <= i; j++)
                {
                    label1.Text += "*";
                }
                label1.Text += "\n";
            }
*/
create procedure pt_1 as
declare @i int,@j int ,@str varchar(50) select @i=1,@j=5,@str=''
while(@i<=5)
begin
  set @i=@i+1
  while(@j>0)
  begin
    set @str=@str+ '*'+space(1)
    set @j=@j-1
    print @str
  end 
end
 
execute pt_1

     *
    ***
   *****
  *******
declare @a int,@b int,@c int,@str varchar(50)
select @a=1,@b=1,@c=1,@str=''
while(@a<6)--横排的循环
begin
  select @b=1,@c=1,@str=''
/*当循环执行一次时就将空格循环、*的循环归为1打印的就归为空的*/
  while(@b<6-@a)-- 空格的循环
/*在第一排事先打空格再打*，空格与行数的关系是：空格循环的条件，再循环内打出空格、
设置空格变量的自增*/
  begin
    set @str=@str+space(1)
    set @b=@b+1
  end
  while(@c<=2*@a-1)--*的循环
/*在第一排打*首先要知道*的位置，设置*/
  begin
    set @str=@str+'*'
    set @c=@c+1
  end
  print @str
  set @a=@a+1
end

    *
   ***
  *****
 *******
  *****
   ***
    *

declare @a int,@b int,@c int,@str varchar(50)
select @a=1,@b=1,@c=1,@str=''
while(@a<6)--横排的循环
begin
  select @b=1,@c=1,@str=''
  while(@b<6-@a)
  begin
    set @str=@str+space(1)
    set @b=@b+1
  end
  while(@c<=2*@a-1)
   begin
    set @str=@str+'*'
    set @c=@c+1
  end
  print @str
  set @a=@a+1
end

select @a=4,@b=1,@c=1,@str=''
while(@a<5)--横排的循环
begin
  select @b=1,@c=1,@str=''
  while(@b<6-@a)
  begin
    set @str=@str+space(1)
    set @b=@b+1
  end
  while(@c<=2*@a-1)
   begin
    set @str=@str+'*'
    set @c=@c+1
  end
  print @str
  set @a=@a-1
end

函数