SQL note

structured query language
综合的、通用的、功能极强的关系数据库语言
包括数据定义(DDL)-create、数据操纵(DML)-insert、delete、update、select
数据控制-grant
是单进程多线程的关系性数据库
sql特点:1、一体化的语言，2、统一的语法结构、多种使用方式,3、高度非过程化,4、语言简洁,5、c/s结构,6、支持异类复制
2个API函数:DB_library,T_SQL
当前数据库系统的主要模型:层次、网状、关系
客户-服务端的连接管道:1、ODBC开放数据库连接，即数据库访问库
DBMS数据库管理系统 RDBMS关系型数据库管理系统

//数据库的基本操作
//文件、文件组
1)主要数据文件(启动信息、存取数据，每个数据库有且仅有一个)
2)次要数据文件(辅助存放数据,可以不要,也可以有多个)
3)事务日志文件(恢复数据库的日志信息,每个数据库至少有一个)

//命令建立数据库
数据库名称大小写严格
create database kris_db
on
primary (name=kris_dat,
filename='f:/kris/kris_dat.mdf',
size=25mb,
maxsize=50mb,
filegrowth=2mb)

log on
(name=kris_log,
filename='f"/kris/kris_log.ldf',
size=10mb,
maxsize=20mb,filegrowth=20%)

//察看数据库信息
一些属性名:
1)、version、collation(排序方式)、useraccess
use kris_db --打开数据库
select databasepropertyex('kris_db','collation')
2)、使用系统存储过程察看信息 sp_helpdb
exec sp_helpdb 'kris_db'

//管理数据库
1)、增加数据库容量
use kris_db
go
alter database kris_db
modify file
(name=kris_dat,size=30mb)
2)、缩小数据库容量
exec sp_dboption 'kris_db','single user',true
go
use kris_db
go
dbcc shrinkdatabase('kris_db')
go
exec sp_dboption 'kris_db','single user',false
go
3)、改名
exec sp_renamedb 'kris_db','nean_db'
4)、察看目前sql上共有哪几个数据库
use master
go
select name from sysdatabases
go
5)、删除数据库
use master
go
drop database kris_db
go

//系统数据库 page(44)

//对表的操作
1)、创建表
use kris_db
go
create table student
(id varchar(18) not null,
name varchar(10) not null,
sex char(2) not null,
age int not null)
go

另一种方法create table kris_db.dbo.my
(id char(4) not null,
name char(6) not null,
sex char(2) not null,
age int not null)
2)、添加记录
insert into student
values('1000','倪颖','男',22)
go
3)、删除
delete from student where id='1005'
go

4)、表内添加一列
use kris_db
go
alter table student
add score int not null default(80)
go
5)、删除不受约束的列
use kris_db
go
alter table student
drop column score1
go
6)、只给一行中的某些字段赋值
insert student(id,age)
values('1010',20)
7)、改表中列的信息
alter table __
alter column oper_email varchar(30)
add --添加列
8)、更新记录
use tours
go
update operator set oper_add='2 King''s St.,Los Angeles' where oper_cd='2'

//SQL语句
use pubs
go
select * from publishers where (pub_name like 'New%' and (city='Boston' or city='Chicago')) //带有%的只能用like

use northwind
go
select * from employees where upper(city) in ('LONDON','SEATTLE')
select distinct orderid from [order details] order by orderid desc

select top 10 * from customers

select top 90 percent * from customers //查询90%的记录

select title_id 图书号,type 类型,price 原价,price*0.7 现价 from titles where price is not null
where price between 15 and 20
或者现价=price*0.7或price*0.7 as 现价

use pubs
go
select type,sum(price),avg(price),count(*)
from titles
where type in ('business','mod_cook','trad_cook')
group by type

--找出每本价格超过10美元的书的种类及该种类书的平均价格
use pubs
go
select avg(price),type
from titles
where type not in (select type from titles where price<10 or price is null)
group by type

select name from student,score where score.语文=90

pubs中来自ca州的作家的全部作品和作家的代号
select au_id,title_id from titleauthor where au_id in (select au_id
from authors where state='ca')

exists
select title_id,au_id from titleauthor where exists
(select * from authors where authors.au_id=titleauthor.au_id and state='ca')

insert into text_table select * from customers

order by title_id
//having
//exsits
//无重复记录 distinct

//约束 P226
create table kris_db.dbo.student1
(stud_id char(4) constraint stu_prk primary key,
name char(6) not null,
birthday datetime,
zipcode char(6) constraint zip_chk check(zipcode like '[0-9][0-9][0-9][0-9][0-9][0-9]'),
salary money default(300))
//表中定义两个主键的写法，在最后写primary key(字段名1，字段名2)

//删除约束
alter table __
drop constraint __
//察看表的一些信息
use tours
go
exec sp_help cruise
//常用的统计函数
sum(),avg(),min(),max(),count()
//索引
每一个表中只能有一个聚集索引
use northwind
go
create clustered index customerindex(用户取名) on text_table(customerid)
create unique index company_index on text_table(companyname)
create index country_city on text_table(country,city)
exec sp_rename 'text_table.customer_index','customer_index1' //索引改名
//视图
优点:1、隐蔽数据库的复杂性2、控制用户提取数据3、简化用户管理

/*通过在pubs数据库中表authors中的au_id、phone字段和表jobs
中job_id、job_desc字段创建一个名为my_view1的视图，该视图中
的记录还必须满足字段au_id的第一个字符为"1%",字段job_desc
的第一个字符为"N"的条件.*/
create view my_view1
as
select authors.au_id,authors.phone,jobs.job_id,jobs.job_desc
from authors,jobs where authors.au_id like '1%' and
jobs.job_desc like 'N%'
察看视图源代码exec sp_helptext my_view1
修改视图alter view ___
as
select _______ from _______ where

update __
set ___ where ____

//定义变量 P265
declare 变量名 数据类型
变量名首字符必须是"@"
select @next='fdfdf'
set @next='fdf'

//事务
use pubs
go
begin tran
go
delete employee --删除表中数据
go
select 'before rollback'
go
select * from employee
go
rollback tran
go
select 'after rollback'
go
select * from employee
go

//流控制
if 语句
use pubs
go
if (select count(*) from authors where state='ca')>0
begin
select 'there are '+convert(varchar(8),count(*))+' authors in the state of ''CA'''
from authors where state='CA'
end
else
select 'there are no authors that live in ''CA'''

while语句
use pubs
go
select min(price )as 'minimum price ',max(price)as 'max price '
from titles
go
begin tran
go
while (select avg(price) from titles)<$30
begin
update titles
set price=price*2
if(select max(price)from titles)>$50
break
else
continue
end
select min(price)as 'minimum price',max(price) as 'max price'
from titles
go
select 'prices are getting out of hand'
go
rollback tran
go

//游标
1、游标是一种数据访问的控制，允许用户访问单位的数据行，而不对整个行集进行操作
2、游标的特性：处理特性和功能特性
游标的处理方式决定了其数据类型，反之也成立
1)动态游标 (static) 在tempdb中只存储游标当前值
2)静态游标 (dynamic) 在tempdb中存储整个游标结果集
3)键集游标 (keyset) 所有键值都存储在tempdb中
4)仅向前游标(fast_forward) 只能向前移动

3、游标状态:
declare--定义游标 oper--打开游标 fetch--检索请求的行
close--关闭游标 deallocate--删除游标 static--静态游标
dynamic--动态游标 keyset--键集游标 fast_forward--仅向前游标
scroll

1)、use northwind
go
declare @i int
set @i=1
declare cust_cursor cursor
for select customerid,companyname,address,city,country from customers
open cust_cursor
while (@i<=10)
begin
fetch next from cust_cursor
set @i=@i+1
end
close cust_cursor
deallocate cust_cursor

2)、use pubs
go
declare @t smallint
select @t=30
declare c2 cursor scroll for
select ord_num,qty from sales
where qty<@t
declare @ordnum varchar(20),@qty smallint
set @t=5
open c2
fetch absolute 3 from c2
go
close c2
deallocate c2

3)、use northwind
go
declare employee_cursor5 cursor for
select firstname,lastname,title,homephone
from northwind.dbo.employees
where firstname like 'M%'
open employee_cursor5
fetch next from employee_cursor5
while @@fetch_status=0 --2个@表示全局变量
begin
fetch next from employee_cursor5
end
close employee_cursor5
deallocate employee_cursor5
go

4)、use northwind
go
declare product_cursor cursor for
select
productname,unitprice from products
where productname like 'Gu%'
order by productname
open product_cursor
--perform the first fetch
fetch next from product_cursor
--check @@fetch_status to see if there are any more rows to fetch
while @@fetch_status=0
begin
--this is executed as long as the previous fetch succeeds
fetch next from product_cursor
end
close product_cursor
deallocate product_cursor
go

5)、use northwind
go
declare @product_name nvarchar(40),@unit_price money
declare product_cursor cursor for
select productname,unitprice from products
where productname like 'Gu%'
order by productname
open product_cursor
--perform the first fetch
fetch next from product_cursor
into @product_name,@unit_price
--check @@fetch_status to see if there are any more rows to fetch
while @@fetch_status=0
begin
--concatenate and display the current values in the variables
print 'the product name is: '+convert(varchar(40),@product_name)
print 'the unit price is '+convert(varchar(10),@unit_price)
print '----------------------------------------------------'
--this is executed as long as the previous fetch succeeds
fetch next from product_cursor
into @product_name ,@unit_price
end
close product_cursor
deallocate product_cursor
go

7)、use northwind
go
select top 9 companyname,city,country
from suppliers
order by companyname
declare suppliers_cursor scroll cursor
for select top 9 companyname,city,country
from suppliers
order by companyname
open suppliers_cursor
fetch last from suppliers_cursor
fetch prior from suppliers_cursor --prior :返回结果集中当前行的前一行并减少当前行数为返回行行数
fetch absolute 2 from suppliers_cursor
fetch relative 3 from suppliers_cursor
fetch relative -2 from suppliers_cursor
fetch first from suppliers_cursor
close suppliers_cursor
deallocate suppliers_cursor

6)、set nocount on --执行sql语句后的信息中包含一条表示该sql语句所影响的行数信息,使用该命令且设置为on时将不显示该行数信息
use pubs
go
create table sampletable(samplerecord int)
go

insert into sampletable values(111)
insert into sampletable values(222)
go

print '--set cursor_close_on_commit on'
go
set cursor_close_on_commit on --在事务当中测试游标是否可以访问，当设置为on时，事务提交后游标不能被访问；off时，事务提交后游标可以被访问
go
print '--begin tran'
begin tran
declare scursor cursor for
select samplerecord
from sampletable
open scursor
print '--commit tran'
commit tran
print '--try to use cursor'
fetch next from scursor
close scursor
deallocate scursor
go
print '--set cursor_close_on_commit off'
go
set cursor_close_on_commit off
go
print '--begin tran'
begin tran
print '--declare and open cursor'
declare scursor cursor for
select samplerecord
from sampletable
open scursor
print '--commit tran'
commit tran
print '--try to use cursor'
fetch next from scursor
close scursor
deallocate scursor
go
drop table sampletable
go

7)、pubs数据库中创建一个可以在sales表中循环的游标，作用：报告订单数量是多还是少，判断条件：ord_num group by ,sum(qty)<25，打印结果:order number __ has a low odrer quanlity,反之，high

use pubs
go
declare @ord_num varchar(20)
declare @var_sum smallint
declare cur_sales cursor for
select ord_num,sum(qty) from sales group by ord_num
open cur_sales
fetch next from cur_sales
into @ord_num,@var_sum
while @@fetch_status=0
begin
if @var_sum<25
print 'order number '+convert(varchar(20),@ord_num)+' has a low odrer quanlity'
else
print 'order number '+convert(varchar(20),@ord_num)+' has a high odrer quanlity'
fetch next from cur_sales
into @ord_num,@var_sum
end
close cur_sales
deallocate cur_sales

//存储过程
创建存储过程
use northwind
go
create proc my_procedure
as
select * from orders where orderdate>='1/1/1998' and shipcountry='usa'
执行
use northwind
exec my_procedure
go
显示当前数据库中存储过程的一些信息
exec sp_stored_procedures
显示存储过程的文本代码
exec sp_helptext my_procedure
带有输入参数的存储过程
use northwind
go
create proc my_procedure2
@startdate datetime,@enddate datetime,@country varchar(20)='usa'
as
if (@startdate is null or @enddate is null or @country is null)
begin
raiserror('Null value are invalid',5,5)
return
end
select * from orders where orderdate between @startdate and @enddate
and shipcountry=@country
go
exec my_procedure2 @startdate='3/12/1998',@enddate='1/1/1999',@country='France' //加名字可以不用按顺序
go
带有返回值的存储过程
use Northwind
go
create proc dbo.my_procedure3
@startdate datetime,@enddate datetime,@country varchar(20)='USA',
@recordcount int OUTPUT
as
if @startdate is null or @enddate is null or @country is null
begin
raiserror('NUL value are invalid',5,5)
return
end
select * from dbo.orders
where orderdate between @startdate and @enddate and shipcountry=@country
select @recordcount=@@rowcount
go
use northwind
go
declare @recordnumber int
exec my_procedure3 '7/1/1997','2/1/1998','Germany',@recordnumber output
print 'The order count is:'+str(@recordnumber)

eg:use pubs
go
create proc m1
@a int, @b int,
@c int output
as
set @c=@a+@b
go
use pubs
go
declare @d int
exec m1 5,6,
@d output
select 'the result is:',@d
drop proc m1
go
修改存储过程
alter proc ___
删除存储过程
drop proc ___

//触发器
例1 、use pubs
go
if exists (select name from sysobjects
where name='reminder' and type='TR')
drop trigger reminder
go
create trigger reminder
on titles
for insert,update
as raiserror('50009',16,10)
go

/*在数据库norhwind中的customers下建立Delee触发器delete_custonmers,
使得在删除表customers中记录的同时自动检查表order中是否有该客户
的记录,如果存在廖膏户记录,则取消删除.*/
use northwind
go
create trigger delete_customers
on customers
for delete
as
if(select count(*) from orders inner join deleted
on orders.customerID=Deleted.CustomerID)>0
begin
raiserror('You can not delete the customer with the order record./
the transaction will be cancelled',10,1)
rollback tran
end