什么是数据库：
狭义：储存数据的仓库
广义：可以对数据进行存储和管理的软件以及数据本身统称为数据库
或：数据库由表，关系，操作组成
为什么需要数据库：
a、几乎所有的应用软件的后台都需要数据库
b、数据库存储数据占用空间小，容易持久保存
c、存储比较安全
d、容易维护和升级
f、数据库移植比较容易
g、简化对数据的操作
h、为将来学习Oracle做准备
i、D/S架构里面包含数据库

--班级表
create table banji
(
banji_id int primary key,
banji_num int not null,
banji_name nvarchar(100)
)
--教师
create table jiaoshi
(
jiaoshi_id int primary key,
jiaoshi_name nvarchar(200)
)
--第三张表 用来模拟班级和教师的关系
create table banji_jiaoshi_mapping
(
banji_id int constraint fk_banji_id foreign key references banji(banji_id),
jiaoshi_id int foreign key references jiaoshi(jiaoshi_id),
kecheng nvarchar(20),
constraint pk_banji_id_jiaoshi_id primary key (banji_id, jiaoshi_id, kecheng)
)
--删除表
drop table banji_jiaoshi_mapping

use work
go
create table factory
(
fno varchar(10) NOT NULL primary key,
facdname varchar(10)
)
drop table factory
use work
go
create table workshop
(
workshop_no char(10) NOT NULL primary key,
wdname varchar(10),
waddress varchar(10),
wtel varchar(20),
fno varchar(10) NOT NULL,
foreign key (fno) references factory(fno)
)
use work
go
create table workman
(
wno char(10) NOT NULL primary key,
wname varchar(10),
wage int,
wtype varchar(10),
workshop_no char(10) NOT NULL,
foreign key (workshop_no) references workshop(workshop_no)
)
use work
go
create table storage
(
sno char(10) NOT NULL primary key,
sdname varchar(10),
stel varchar(20),
fno varchar(10) NOT NULL,
foreign key (fno) references factory(fno)
)
use work
go
create table product
(
pno varchar(10) NOT NULL primary key,
pname varchar(10),
pprice float,
workshop_no char(10) NOT NULL,
sno char(10) NOT NULL,
foreign key (workshop_no) references workshop(workshop_no),
foreign key (sno) references storage(sno)
)
use work
go
create table element
(
eno char(10) NOT NULL primary key,
eweight float,
eprice float,
sno char(10) NOT NULL,
foreign key (sno) references storage(sno)
)
use work
go
create table workshop_element
(
workshop_no char(10) NOT NULL ,
eno char(10) NOT NULL ,
primary key(workshop_no,eno),
foreign key (workshop_no) references workshop(workshop_no),
foreign key (eno) references element(eno)
)
use work
go
create table product_element
(
pno varchar(10) NOT NULL ,
eno char(10) NOT NULL ,
primary key(pno,eno),
foreign key (pno) references product(pno),
foreign key (eno) references element(eno)
)

create table fac
(fname varchar(20) primary key,
fn_name varchar(20) not null,
);
create table room
(Rno varchar(10)primary key,
Rname varchar(20),
Rcall int,
Raddr varchar(20),
fname varchar(20)not null,
foreign key(fname)references fac(fname)
);
create table worker
(Wno varchar(10) primary key,
Wname varchar(20),
Wage int,
Wsex varchar(2) check(Wsex in ('男' , '女')),
Wtype varchar(20),
Rno varchar(10)not null,
foreign key(Rno)references room(Rno)
);
create table product
(Pno varchar(10)primary key,
Pname varchar(20),
Price numeric(5,1) default 0,
check(Price>0),
Rno varchar(10)not null,
foreign key(Rno)references room(Rno)
);
create table component
(Cno varchar(10)primary key,
Price numeric(5,1)default 0,
check(Price>0),
weight int
);
create table store
(Sno varchar(10)primary key,
Sname varchar(20),
Scall int,
fname varchar(20)not null,
foreign key(fname)references fac(fname)
);
create table room_com
(Rno varchar(10) not null,
Cno varchar(10) not null,
Stime datetime,
primary key(Rno,Cno),
foreign key(Rno)references room(Rno),
foreign key(Cno)references component(Cno)
);
create table pro_com
(Cno varchar(10) not null,
Pno varchar(10) not null,
number int,
primary key(Pno,Cno),
foreign key(Pno)references product(Pno),
foreign key(Cno)references component(Cno)
);
create table pro_str
(Pno varchar(10) not null,
Sno varchar(10) not null,
Strnum1 int,
primary key(Pno,Sno),
foreign key(Pno)references product(Pno),
foreign key(Sno)references store(Sno)
);
create table com_str
(Cno varchar(10) not null,
Sno varchar(10) not null,
Strnum2 int,
primary key(Cno,Sno),
foreign key(Cno)references component(Cno),
foreign key(Sno)references store(Sno)
);
create index roomRno on room(Rno)
create index roomRname on room(Rname)
create index proPno on product(Pno)
create index facfneme on fac(fname)
create index comCno on component(Cno)
create index strSno on store(Sno)
create proc worker1(
@Wno varchar(10),
@Wname varchar(20),
@Wage int,
@Wsex varchar(2),
@Wtype varchar(20),
@Rno varchar(10)
)
as
insert
into worker
values(@Wno,@Wname,@Wage,
@Wsex,@Wtype,@Rno);
create proc room1
(@Rno varchar(10),
@Rname varchar(20),
@Rcall int,
@Raddr char(8),
@fname varchar(20)
)
as
insert
into room
values(@Rno,@Rname,@Rcall,
@Raddr,@fname);
create proc product1
(@Pno varchar(10),
@Pname varchar(20),
@Price numeric(5,1),
@Rno varchar(10)
)
as
insert
into product
values(@Pno,@Pname,@Price,@Rno);
create proc component1
(@Cno varchar(10),
@Price float(10),
@weight float(10)
)
as
insert
into component
values(@Cno,@Price,@weight);
create proc store1
(@Sno varchar(10),
@Sname varchar(20),
@Scall int,
@fname varchar(20)
)
as
insert
into store
values(@Sno,@Sname,@Scall,@fname);
create proc fac1
(@fname varchar(20),
@fn_name varchar(20)
)
as
insert
into fac
values(@fname,@fn_name );
create proc room_com1
(@Rno varchar(10),
@Cno varchar(10),
@stime datetime
)
as
insert
into room_com
values(@Rno,@Cno,@stime);
create proc pro_com1
(@Cno varchar(10),
@Pno varchar(10),
@number int
)
as
insert
into pro_com
values(@Cno,@Pno,@number);
create proc pro_str1
(@Pno varchar(10),
@Sno varchar(10),
@Strnum1 int
)
as
insert
into pro_str
values(@Pno,@Sno,@Strnum1);
create proc com_str1
(@Cno varchar(10),
@Sno varchar(10),
@Strnum2 int
)
as
insert
into com_str
values(@Cno,@Sno,@Strnum2);
（2）存储数据
exec
fac1
@fname='奔驰',
@fn_name='张磊'
exec
room1
@Rno=1,
@Rname='刘基',
@Rcall=4254357,
@Raddr='长沙',
@fname='奔驰'
exec
room1
@Rno=2,
@Rname='刘择机',
@Rcall=4250046,
@Raddr='株洲',
@fname='奔驰'
exec
worker1
@Wno=102,
@Wname='刘雄峰' ,
@Wage=40 ,
@Wsex='男' ,
@Wtype='搬运工',
@Rno=1
exec
worker1
@Wno=103,
@Wname='刘芸' ,
@Wage=21 ,
@Wsex='女' ,
@Wtype='经理',
@Rno=2
exec
worker1
@Wno=101,
@Wname='郑凡' ,
@Wage=21 ,
@Wsex='女' ,
@Wtype='前台接待',
@Rno=1
exec
worker1
@Wno=104,
@Wname='黄晓明' ,
@Wage=27 ,
@Wsex='男' ,
@Wtype='技术人员',
@Rno=2
exec
product1
@Pno=201 ,
@Pname='轮胎' ,
@Price='500',
@Rno=1
exec
product1
@Pno=202 ,
@Pname='引擎' ,
@Price='9584.1',
@Rno=2
exec
product1
@Pno=203 ,
@Pname='挡风玻璃' ,
@Price='1584.1',
@Rno=1
exec
product1
@Pno=204 ,
@Pname='奔驰摩托' ,
@Price='9999.9',
@Rno=1
exec
product1
@Pno=205 ,
@Pname='奔驰货车' ,
@Price='9999.0',
@Rno=2
exec
component1
@Cno=c1002 ,
@Price='6520' ,
@weight='200'
exec
component1
@Cno=c1001 ,
@Price='520' ,
@weight='10'
exec
component1
@Cno=c1003 ,
@Price='925' ,
@weight='150'
exec
component1
@Cno=c1004 ,
@Price='654' ,
@weight='100'
exec
store1
@Sno=101 ,
@Sname='刘羽琦' ,
@Scall=6991431 ,
@fname='奔驰'
exec
store1
@Sno=102 ,
@Sname='吉鹏' ,
@Scall=6991254 ,
@fname='奔驰'
exec
room_com1
@Rno=1 ,
@Cno=c1002,
@stime='2009-05-26'
exec
room_com1
@Rno=2 ,
@Cno=c1003,
@stime='2009-06-01'
exec
pro_com1
@Cno=c1002,
@Pno=204,
@number=45
exec
pro_com1
@Cno=c1004,
@Pno=201,
@number=89
exec
pro_com1
@Cno=c1001,
@Pno=202,
@number=124
exec
pro_str1
@Pno=201 ,
@Sno=101 ,
@Strnum1=345
exec
pro_str1
@Pno=203 ,
@Sno=102 ,
@Strnum1=1254
exec
com_str1
@Cno=c1001,
@Sno=102,
@Strnum2=458
exec
com_str1
@Cno=c1004,
@Sno=101,
@Strnum2=1461
exec
com_str1
@Cno=c1002,
@Sno=101,
@Strnum2=1852
exec
com_str1
@Cno=c1003,
@Sno=102,
@Strnum2=2541
use xcm
exec sp_grantdbaccess 'zhanglei','xcm'
exec sp_addlogin'zhanglei','1234','xcm'
exec sp_addsrvrolemember 'zhanglei','sysadmin'
grant select,update
on product
to xcm;
grant update
on worker
to xcm;
grant update
on product
to xcm;
grant all privileges
on store
to xcm;
grant all privileges
on room
to xcm;
create view IS_store
as
select Sno,Sname,Scall
from store
create view IS_worker
as
select Wno,Wname,Wsex
from worker
where Wsex='女'
create view IS_product
as
select Pno,Pname,Price
from product
where Rno=1
select Pname,Price
from product,room
where product.Rno=room.Rno and room.Raddr='长沙'
update product
set pname='chetou'
where pno=203
select *
from product
where Price>2000.0
select* from room
select * from fac
select * from worker
select * from component
select * from product
select * from store
select *
from product
where Price>2000.0
select Pname,Price
from product,room
where product.Rno=room.Rno and room.Raddr='长沙'

看着像天书……

CREATE database bankDB
on
(
name = bankDB,
filename = 'D:\ATM\bankDB.mdf'
)
--建立用户信息表useinfo
create table userinfo
(
customerID int identity(1,1) not null, --identity为列的识别
customerName varchar(16) not null, --开户人姓名
PID varchar(18) not null, --身份证号
telephone varchar(14) not null, --联系电话
address varchar(50) --联系地址
)
--建立银行卡信息表tablecardinfo
create table cardinfo
(
cardID char(20) not null, --卡号
curtype varchar(10) not null, --货币类型
savingType varchar(10) not null, --存款类型
openDate datetime not null, --开户日期
openMoney money not null, --开户金额 ,money代表货币或货币值的数据类型,代表八个字节
balance money not null, --余额
pass int not null, --用户密码
IsReportLoss char(2) not null, --是否挂失
customeID int not null, --顾客编号
)
--创建交易信息表transinfo
create table transinfo
(
transDATE date not null, --交易日期
cardID char(20) not null, --卡号
transType varchar(5) not null, --交易类型
transMoney money not null, --交易金额
remark varchar(50) --备注信息
)
--为userinfo 表中客户ID（customerID）添加主键约束
alter table userinfo --ALTER TABLE 语句用于在已有的表中添加、修改或删除列。
add constraint pk_customerID primary key(customeRID) --constraint主要是增加约束
--为userinfo 表中客户ID的身份证（PID）添加唯一约束
alter table userinfo
add constraint uq_PID unique(PID)
--为userinfo 表中客户ID的身份证号码添加检查约束，长度为15或18
alter table userinfo
add constraint ck_PID check(len(PID) = 15 or len(PID) = 18)
--为userinfo 表中客户电话号码添加检查约束
alter table userinfo
add constraint ck_telephone check(
telephone like
('[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
or telephone like'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
or telephone like'[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
--为cardinfo 表中客户ID（cardID）添加主键约束
alter table cardinfo --ALTER TABLE 语句用于在已有的表中添加、修改或删除列。
add constraint pk_cardIDID primary key(cardID)
--为cardinfo 表中的卡号ID（cardID）添加约束，必须是以‘1010 3576’ 开头，长度为12
alter table cardinfo
add constraint ck_cardID check(
cardID like '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]')
--为cardinfo 表中的货币类型（curType）添加默认约束，默认为RMB
alter table cardinfo
add constraint df_curTYPE default('RMB')for curType
--为cardinfo表中的存储类型添加约束，只能是‘活期，定活两便和定期’
alter table cardinfo
add constraint ck_savingType check(
savingType like '活期' or savingType like '定活两便' or savingType like '定期')
--cardinfo 表中开卡日期默认为当天日期
alter table cardinfo
add constraint df_openDate default(getdate()) for openDate --DEFAULT 约束用于向列中插入默认值
--cardinfo表中大的开户金额不能低于1元
alter table cardinfo
add constraint ck_openMoney check(openMoney >= 1)
--cardinfo表中大的余额不能低于1元
alter table cardinfo
add constraint ck_balance check(balance >= 1)

数据库…蕴含了俺滴血与汗呐