SQL-创建数据库及简单查询语句

--（2）查询出账户余额小于10元的所有银行卡号。
select cardId from dbo.T_account where balance<10
--（3）查询出存款类型为“定期”的客户编号、客户名称、身份证号、性别、联系电话。
select customerId,customerName,PID,sex,telephone from dbo.T_customer
where customerId in(select customerId from dbo.T_account where savingType='一年定期')
--（4）查询出取款金额大于5000元的所有客户名称（消除重复项）。
select distinct customerName from dbo.T_customer
--在T_customer客户表中将客户编号为“001”的客户姓名改为“张力”
update dbo.T_customer set customerName='张力'where customerId='001'
--（1）查询出客户编号为“002”的客户姓名。
select customerName from dbo.T_customer where customerId='002'
insert into T_customer(customerId,customerName,PID,sex,telephone)
values('003','李晓晨','430203197611031757','男','13907310003')
insert into T_account(cardId,customerId,password,savingType,openTime,balance,isReportLos)
values('6222000020130001','001','123456','活期','2010-10-10','3200','否')
insert into T_account(cardId,customerId,password,savingType,openTime,balance,isReportLos)
--开户时间默认值为当前系统时间
alter table T_account
add constraint df_openTime default getdate() for openTime
--录入数据
insert into T_customer(customerId,customerName,PID,sex,telephone)
--创建数据库BankDB
create database BankDB
on primary
(
name='BankDB_data',
filename='E:\Data\BankDB_data.mdf',
size=5,
maxsize=20,
filegrowth=10%
)
log on
(
name='BankDB_log',
alter table dbo.T_account
add constraint pk_cardId primary key (cardId)
alter table dbo.T_transInfo
add constraint pk_transId primary key (transId)
--外键
alter table T_account
values('001','张丽','430281198907064463','女','13907310001')
insert into T_customer(customerId,customerName,PID,sex,telephone)
values('002','王蒙','430202198107163775','男','13907310002')
where customerId in
(select customerId from dbo.T_account where cardId in
(select cardId from dbo.T_transInfo where transMoney>1000)
)
--（1）创建视图V_account查询客户名称为张丽6222000020130003','003','123456','一年定期','2013-5-5','50000','否')
insert into T_transInfo(cardId,transTime,transType,transMoney)
values('6222000020130001','2010-10-15','开户','1000')
values('6222000020130003','2013-5-15','存款','1000')
--①在T_customer客户表中添加一条记录：“004，张辉名，430102198809093012，男，13278666666”；
insert into T_customer(customerId,customerName,PID,sex,telephone)
add constraint fk_customerId foreign key (customerId)references dbo.T_customer(customerId)
alter table T_transInfo
add constraint fk_cardId foreign key (cardId)references dbo.T_account(cardId)
customerName varchar(40),
PID varchar(18),
sex char(2),
telephone varchar(12)
)
--创建银行卡表
create table T_account
(
cardId varchar(20) not null ,
customerId varchar(20),
create view V_account
as
select cardId from dbo.T_account
where customerId in(select customerId from dbo.T_customer where customerName='张丽')
--（2）创建存储P_account过程，根据客户编号统计出此客户拥有多少张银行卡。
create procedure P_account(@customerId varchar(20))
as
select COUNT(*) from dbo.T_account where customerId=@customerId
exec P_account '001'
values('004','张辉名','430102198809093012','男','13907310004')
--②在T_transInfo交易记录表删除银行卡ID为“6222000020130001”的所有存款记录；
delete from dbo.T_transInfo
where cardId='6222000020130001'
password char(6),
savingType varchar(8),
openTime datetime,
balance float,
isReportLos varchar(2)
)
--创建客户信息表
create table T_transInfo
(
transId int identity(1,1) not null ,
insert into T_transInfo(cardId,transTime,transType,transMoney)
values('6222000020130002','2013-4-15','取款','2000')
insert into T_transInfo(cardId,transTime,transType,transMoney)
values('6222000020130002','002','123456','活期','2012-8-5','1500','否')
insert into T_account(cardId,customerId,password,savingType,openTime,balance,isReportLos)
cardId varchar(20),
transTime datetime,
transType varchar(4),