sql小型

1. 學習sql有什麼用，具體用於什麼方面

SQL用於存儲數據，做一些管理系統，網站什麼的，都會使用sqlserver來存儲數據，SQL的作用：

SQL主要用於資料庫系列的軟體的查詢、匯總、寫入、刪改等方面的操控，具體應用於資料庫編程或資料庫數據的維護。

也就是說SQL一般用於資料庫編程，以及現有資料庫錯誤排查。所以一般涉及資料庫的編程人員或電腦信息化系統維護人員需要對SQL（也就是對資料庫）知識進行學習。

(1)sql小型擴展閱讀：

結構化查詢語言是高級的非過程化編程語言，允許用戶在高層數據結構上工作。它不要求用戶指定對數據的存放方法，也不需要用戶了解具體的數據存放方式，所以具有完全不同底層結構的不同資料庫系統, 可以使用相同的結構化查詢語言作為數據輸入與管理的介面。

結構化查詢語言語句可以嵌套，這使它具有極大的靈活性和強大的功能。

2. SQL小型資料庫系統設計

--------------創建資料庫--------------------------
create database bankDB
on primary
(
name = 'bankDB',
filename = 'e:\bank\bankDB.mdf',
size = 3MB,
maxsize = 100MB,
filegrowth = 15%
)
log on
(
name = 'bankDB_log',
filename = 'e:\bank\bankDB_log.ldf',
size = 2MB,
filegrowth = 1MB
)
go
--------------------創建數據表---------------------
use bankDB
gocreate table userInfo
(
customerID int identity(1,1) not null,
customerName varchar(10) not null,
PID char(18) not null,
telphone char(13) not null,
adress varchar(100)
)alter table userInfo
add constraint PK_customerID primary key(customerID)
alter table userInfo
add constraint CK_PID check(PID like '[1-9][0-9][0-9][0-9][0-9][0-9][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
PID like '[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]X' or
PID like '[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
alter table userInfo
add constraint CK_telphone check(telphone like '13[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or telphone 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]')
alter table userInfo
add constraint DF_telphone default('地址不詳') for adress
alter table userInfo
add constraint UQ_PID unique(PID)create table cardInfo
(
cardID char(19) not null,
curType varchar(5) not null,
savingType varchar(6) not null,
openDate datetime not null,
openMoney money not null,
balance money not null,
pass char(6) not null,
IsReportLoss char(2) not null,
customerID int not null
)alter table cardInfo
add constraint FK_customerID foreign key(customerID) references userInfo(customerID)
alter table cardInfo
add constraint PK_cardID primary key(cardID)
alter table cardInfo
add constraint DF_openDate default(getdate()) for openDate
alter table cardInfo
add constraint DF_pass default('888888') for pass
alter table cardInfo
add constraint DF_curType default('RMB') for curType
alter table cardInfo
add constraint CK_openMoney check(openMoney > 1)
alter table cardInfo
add constraint CK_balance check(balance > 1)
alter table cardInfo
add constraint CK_pass check(pass like '[0-9][0-9][0-9][0-9][0-9][0-9]')
alter table cardInfo
add constraint DF_cardID default('1010 3576 ' + convert(varchar(4),convert(int,rand()*10000)) + ' ' +convert(varchar(4),convert(int,rand()*10000))) for cardID
alter table cardInfo
add constraint DF_IsReportLoss default('否') for IsReportLoss
alter table cardInfo
add constraint CK_IsReportLoss check(IsReportLoss like '是' or IsReportLoss like '否')
alter table cardInfo
add constraint CK_savingType check(savingType like '定期' or savingType like '活期' or savingType like '定活期')create table transInfo
(
transDate datetime not null,
cardID char(19) not null,
transType char(4) not null,
transMoney money not null,
remark varchar(100)
)alter table transInfo
add constraint DF_transDate default(getdate()) for transDate
alter table transInfo
add constraint FK_cardID foreign key(cardID) references cardInfo(cardID)
alter table transInfo
add constraint CK_stransType check(transType like '存入' or transType like '支取')
alter table transInfo
add constraint CK_transMoney check(transMoney > 0)go
--------------------創建開戶用戶卡信息視圖---------
create view v_cus
as
select customerName, cardID,curType,savingType,telphone,adress
from cardInfo inner join userInfo on cardInfo.customerID = userInfo.customerID
where datediff(wk,openDate,getdate()) = 0-----------------------修改密碼--------------------
create proc changePWD
@cardID char(19),
@newpwd char(6),
@oldpwd char(6) = '888888'
as
declare @temppwd int
if((select count(*) from cardInfo where cardID = @cardID) = 0)
begin
print '您輸入的卡號不存在！'
return
end
select @temppwd = pass from cardInfo where cardID = @cardID
if(@temppwd = @oldpwd)
begin
update cardInfo set pass = @newpwd where cardID = @cardID
if(@@error = 0)
print '密碼修改成功！'
else
print '密碼修改失敗！'
end
else
print '您輸入的原始密碼不對，不能修改密碼！'----------------------銀行卡掛失-------------------
create proc ReportLoss
@cardID char(19),
@PID char(18)
as
if((select count(*) from cardInfo where cardID = @cardID) = 0)
begin
print '您輸入的卡號不存在！'
return
end
if((select IsReportLoss from cardInfo where cardID = @cardID) = '是')
begin
print '該卡已掛失！'
return
end
if((select PID from userInfo where customerID = (select customerID from cardInfo where cardID = @cardID)) = @PID)
begin
update cardInfo set IsReportLoss = '是' where cardID = @cardID
if(@@error = 0)
print '掛失成功！'
else
print '掛失失敗！'
end
else
begin
print '您輸入的身份證錯誤！不能掛失！'
return
end----------------------取消銀行卡掛失-------------------
create proc CancelReportLoss
@cardID char(19),
@PID char(18)
as
if((select count(*) from cardInfo where cardID = @cardID) = 0)
begin
print '您輸入的卡號不存在！'
return
end
if((select IsReportLoss from cardInfo where cardID = @cardID) = '否')
begin
print '該卡沒有掛失！不能進行此項操作！'
return
end
if((select PID from userInfo where customerID = (select customerID from cardInfo where cardID = @cardID)) = @PID)
begin
update cardInfo set IsReportLoss = '否' where cardID = @cardID
if(@@error = 0)
print '取消掛失成功！'
else
print '取消掛失失敗！'
end
else
begin
print '您輸入的身份證錯誤！不能取消掛失！'
return
end----------------------統計銀行的資金流通余額和盈利結算--------------------create proc StatMoney
as
select ((select sum(transMoney) as 資金流通余額 from transInfo where transType like '存入') - (select sum(transMoney) as 資金流通余額 from transInfo where transType like '支取'))
as 資金流通余額 select ((select sum(transMoney) as 資金流通余額 from transInfo where transType like '存入')*0.008
- (select sum(transMoney) as 資金流通余額 from transInfo where transType like '支取')*0.003)
as 盈利結算 ---------------------查詢本周開戶的卡號，顯示該卡的信息-------------------
create proc SearchNewCus
as
print '本周開戶卡號信息'
select * from v_cus -----------------------查詢本月交易金額最高的卡號-------------------------
create proc SearchHigh
as
select top 1 cardID, sum(transMoney) as 交易金額 from transInfo
group by cardID------------------------------查詢掛失帳號的客戶信息----------------------
create proc SearchLossCus
as
print '掛失帳號客戶信息'
select * from userInfo where customerID = (select customerID from cardInfo where IsReportLoss = '是')--------------------------催款提醒業務------------------------------------
create proc Awoke
as
select telphone, balance, customerName, cardID
from cardInfo inner join userInfo on cardInfo.customerID = userInfo.customerID
where balance < 200-------------------------銀行開戶-----------------------------------------
create proc OpenUser
@curType varchar(5),
@savingType varchar(6),
@openMoney money,
@PID char(18),
@telphone char(13),
@customerName varchar(10)
as
begin tran OpenU
declare @tag int
declare @tempID varchar(10)
set @tag = 0 insert into userInfo (customerName,PID,telphone) values (@customerName, @PID, @telphone)
set @tag = @tag + @@error
if(@tag = 0)
set @tempID = (select customerID from userInfo where PID = @PID)
insert into cardInfo (curType, savingType, openMoney, balance, customerID) values (@curType, @savingType, @openMoney, @openMoney, @tempID)
set @tag = @tag + @@error
if(@tag = 0)
begin
print '開戶成功！'
commit tran
end
else
begin
print '開戶失敗！'
rollback tran
end-----------------------------客戶存款-------------------------------------
create proc SaveMoney
@cardID char(19),
@transMoney money,
@transType char(4) = '存入'
as
if((select IsReportLoss from cardInfo where cardID = @cardID) = '是')
begin
print '該卡已掛失!無法對其進行操作!'
return
end
begin tran Saving
declare @tag int
set @tag = 0 insert into transInfo (cardID, transType, transMoney) values (@cardID, @transType, @transMoney)
set @tag = @tag + @@error
update cardInfo set balance = balance + @transMoney where cardID = @cardID
set @tag = @tag + @@error
if(@tag = 0)
begin
print '存款成功！'
commit tran
end
else
begin
print '存款失敗!'
rollback tran
end ------------------------------客戶取款------------------------------------
create proc DrawMoney
@cardID char(19),
@transMoney money,
@transType char(4) = '支取'
as
if((select IsReportLoss from cardInfo where cardID = @cardID) = '是')
begin
print '該卡已掛失!無法對其進行操作!'
return
end
begin tran Drawing
declare @tag int
set @tag = 0 insert into transInfo (cardID, transType, transMoney) values (@cardID, @transType, @transMoney)
set @tag = @tag + @@error
update cardInfo set balance = balance - @transMoney where cardID = @cardID
set @tag = @tag + @@error
if(@tag = 0)
begin
print '取款成功！'
commit tran
end
else
begin
print '取款失敗!'
rollback tran
end----------------------------轉帳------------------------------------------
create proc Transfer
@cardID_out char(19),
@cardID_in char(19),
@tranMoney money
as
if((select count(*) from cardInfo where cardID = @cardID_out or cardID = @cardID_in) <> 2)
begin
print '請確認兩張卡是否都存在！'
return
end
if((select IsReportLoss from cardInfo where cardID = @cardID_out) = '是')
begin
print @cardID_out + '該卡已掛失!無法對其進行操作!'
return
end
if((select IsReportLoss from cardInfo where cardID = @cardID_in) = '是')
begin
print @cardID_in + '該卡已掛失!無法對其進行操作!'
return
end
begin tran TransMoney
declare @tag int
set @tag = 0 update cardInfo set balance = balance - @tranMoney where cardID = @cardID_out
set @tag = @tag + @@error
update cardInfo set balance = balance + @tranMoney where cardID = @cardID_in
set @tag = @tag + @@error
if(@tag = 0)
begin
print '轉帳成功！'
commit tran
end
else
begin
print '轉帳失敗!'
rollback tran
end

3. MSSQL適合做個人小型軟體的資料庫么

1、MSSQL，不管網路的單機的，都適合。2、是要裝，一般都是把安裝腳本寫好，跟你的應用程序一起打包裝的。3、ACCESS對生成的安裝包比SQL要簡單，至於要安全，數據部分可以用MD5演算法加密，再寫進資料庫里。

4. SQL SERVER 算是一個小型的信息系統嗎請具體一下。還有就是小型信息系統還有哪些

SQL SERVER 是資料庫，如果自己開發的信息系統需要存放數據，那就可以使用該資料庫進行存儲數據。
銷售管理信息系統是一個信息系統，但是大型還是小型要看具體的業務，如果只是簡簡單單的登記銷售記錄。查看信息等，那就只能算小型的。以此類推，圖書管理系統、學籍管理系統、車輛管理系統等。

5. 如何用prontpage和SQL設計一個小型的資料庫系統(學生綜合測評排名系統)

首先在SQL中利用企業管理器或向導建立一個資料庫，命名為學生管理系統， 啟動SQL Sever服務，運行企業管理器，單擊要創建資料庫的伺服器左邊的加號圖標，展開樹形目錄，在「資料庫」節點上右擊滑鼠，在彈出的快捷菜單中選則「新建資料庫」命令，然後按照提示一步步建立資料庫，不再詳細敘述。 假設學生管理系統下有三個表，分別為學生表、課程表、修課表，表的結構