資源描述：
《sql語句練習(xí)——創(chuàng)建學(xué)生信息數(shù)據(jù)庫》由會員上傳分享，免費在線閱讀，更多相關(guān)內(nèi)容在工程資料-天天文庫。
1、SQL語句練習(xí)——創(chuàng)建學(xué)生信息數(shù)據(jù)庫--創(chuàng)建數(shù)據(jù)庫stuDBifexists(selectfromsysdatabaseswherename='stuDB')dropdatabasestuDBcreatedatabasestuDBon(name='stuDB_data',filename='D:stuDB_data.mdf',filegrowth=15%)go--創(chuàng)建表stuInfo_1usestuDBifexists(selectfromsysobjectswherename='stuInfo_1')drop
2、tablestuInfo_1createtablestuInfo_1(stuNamevarchar(10)notnull,--學(xué)生姓名stuNovarchar(20)notnull,--學(xué)號stuSexvarchar(4)notnull,--性別stuAgeintnotnull,--年齡stuSeatintidentity(1,1)notnull,--座位號stuAddresstext--地址)go--添加約束altertablestuInfo_1addconstraintUQ_stuNounique(stuNo
3、)altertablestuInfo_1addconstraintCK_stuNocheck(stuNolike's253%')altertablestuInfo_1addconstraintCK_stuSexcheck(stuSex='男'orstuSex='女')altertablestuInfo_1addconstraintCK_stuAgecheck(stuAgebetween15and40)altertablestuInfo_1addconstraintCK_stuSeatcheck(stuSeatbe
4、tween1and30)altertablestuInfo_1addconstraintDF_stuAddressdefault('地址不詳')forstuAddress--插入測試數(shù)據(jù)insertintostuInfo_1values('張秋力','s25301','男','18','北京海淀')insertintostuInfo_1values('李斯文','s25303','女','22','河南洛陽')insertintostuInfo_1values('張思德','s25305','男','22','湖
5、北武漢')insertintostuInfo_1values('梅超風','s25306','女','25','河北邯鄲')insertintostuInfo_1values('李恩達','s25307','男','27','湖南湘潭')insertintostuInfo_1values('趙鵬飛','s25308','男','23','新疆伊利')--添加SQL帳戶usestuDBgo--建立登錄名execsp_addlogin'banzhuren','111111'execsp_addlogin'jiaoyu
6、an','123456'go--允許訪問數(shù)據(jù)庫execsp_grantdbaccess'banzhuren','bzrDBUser'execsp_grantdbaccess'jiaoyuan','jyDBUser'go--賦予用戶操作數(shù)據(jù)表的權(quán)利grantselect,insert,updateonstuInfo_1tobzrDBUsergrantselectonstuInfo_1tojyDBUsergo
