SQL实用教程程序源码.docx
- 文档编号:14901449
- 上传时间:2023-06-28
- 格式:DOCX
- 页数:60
- 大小:24.17KB
SQL实用教程程序源码.docx
《SQL实用教程程序源码.docx》由会员分享,可在线阅读,更多相关《SQL实用教程程序源码.docx(60页珍藏版)》请在冰点文库上搜索。
SQL实用教程程序源码
程序代码
第2章
上机实验
SELECTWarequantity*Unitcost
FROMOrderContent
WHEREOrder_ID=3
SELECTsum(Warequantity*Unitcost)
FROMOrderContent
第3章
正文
CREATEDATABASEstudent
CREATETABLEBILLS
(nameCHAR(10),
birthdayINT,
addressVARCHAR(20),
cityCHAR(10),
sexCHAR(10),
idINTNOTNULL,
salaryNUMERIC(10,2),
pnoINT)
SETNOCOUNTOFF
USEstudent
IFEXISTS(SELECTnameFROMsysindexes
WHEREname='Id_ind')
DROPINDEXStu.Id_ind
GO
USEstudent
CREATEINDEXId_ind
ONStu(Id)
GO
CREATEINDEXcom_INDEX
ONscott.emp(COMM);
createindexpk_p_main
onStu(Id,Name)
with
pad_index,
fillfactor=100
on[primary]
CREATEUNIQUEINDEXid_index
ONStu(id)
GO
ALTERDATABASEstudent
ADDLOGFILE
(NAME=test1log2,
FILENAME='c:
\ProgramFiles\MicrosoftSQLServer\MSSQL\Data\test1log.ldf',
SIZE=5MB,
MAXSIZE=100MB,
FILEGROWTH=5MB),
(NAME=test1log3,
FILENAME='c:
\ProgramFiles\MicrosoftSQLServer\MSSQL\Data\test1log.ldf',
SIZE=5MB,
MAXSIZE=100MB,
FILEGROWTH=5MB)
GO
GO
ALTERDATABASEstudent
REMOVEFILEtest1log3
GO
ALTERTABLEStu
ADDmynewcolumnINTNULL
ALTERTABLEemADD(emsexchar(20));
CREATETABLEdoc_exb(column_aINT,column_bVARCHAR(20)NULL)
GO
ALTERTABLEdoc_exbDROPCOLUMNcolumn_b
GO
EXECsp_helpdoc_exb
GO
DROPTABLEdoc_exb
GO
DROPDATABASEdoc_exb
DROPTABLEtitles1
DROPTABLEabc
DROPINDEXauthors.idindex
DROPINDEXid_index;
上机实验
CREATEDATABASEWare
CREATETABLE[dbo].[Users](
[User_ID][int]IDENTITY(1,1)NOTNULL,
[User_Name][nvarchar](50)COLLATEChinese_PRC_CI_ASNOTNULL,
[Password][nvarchar](50)COLLATEChinese_PRC_CI_ASNOTNULL,
[Name][nvarchar](50)COLLATEChinese_PRC_CI_ASNOTNULL,
[Email][nvarchar](50)COLLATEChinese_PRC_CI_ASNOTNULL,
[Card][nvarchar](50)COLLATEChinese_PRC_CI_ASNOTNULL,
[Tell][nvarchar](50)COLLATEChinese_PRC_CI_ASNULL
)ON[PRIMARY]
CREATETABLE[dbo].[ware](
[ware_id][int]IDENTITY(1,1)NOTNULL,
[type_id][int]NOTNULL,
[Model_id][nvarchar](50)COLLATEChinese_PRC_CI_ASNULL,
[Model_Name][nvarchar](50)COLLATEChinese_PRC_CI_ASNULL,
[StartPrice][money]NOTNULL,
[SalePrice][money]NOTNULL,
[wareshow][nvarchar](800)COLLATEChinese_PRC_CI_ASNULL
)ON[PRIMARY]
CREATETABLE[dbo].[waretype](
[type_ID][int]IDENTITY(1,1)NOTNULL,
[type_Name][nvarchar](50)COLLATEChinese_PRC_CI_ASNOTNULL
)ON[PRIMARY]
CREATETABLE[dbo].[Shopping](
[ID][int]IDENTITY(1,1)NOTNULL,
[ShoppingID][nvarchar](50)COLLATEChinese_PRC_CI_ASNULL,
[ware][int]NOTNULL,
[wareQuantity][int]NOTNULL,
[ShoppingDate][datetime]NOTNULL
)ON[PRIMARY]
CREATETABLE[dbo].[Orders](
[Order_ID][int]IDENTITY(1,1)NOTNULL,
[User_ID][int]NOTNULL,
[OrderDate][datetime]NOTNULL
)ON[PRIMARY]
CREATETABLE[dbo].[OrderContent](
[Order_ID][int]NOTNULL,
[ware_ID][int]NOTNULL,
[wareQuantity][int]NOTNULL,
[UnitCost][money]NOTNULL
)ON[PRIMARY]
第4章
正文
IFEXISTS(SELECTTABLE_NAMEFROMINFORMATION_SCHEMA.TABLES
WHERETABLE_NAME='T1')
DROPTABLET1
GO
CREATETABLET1(column_1int,column_2varchar(30))
INSERTT1
VALUES(1,'Row')
insertintoBILLS
values('xiaoxing','2006','bailaohui','beijing','man',10,1000,123);
insertintoscott.emp(empno,ename,hiredate)
values(7999,'JONE','25-11月-05');
CREATETABLET1
(column_1intidentity,
column_2varchar(30)
CONSTRAINTdefault_nameDEFAULT('columndefault'),
column_3intNULL,
column_4varchar(40)
)
INSERTINTOT1(column_4)
VALUES('value')
INSERTINTOT1(column_2,column_4)
VALUES('value','value')
INSERTINTOT1(column_2,column_3,column_4)
VALUES('value',-44,'value')
SELECT*FROMT1
USEpubs
CREATETABLEnew_authors
(
au_idid,
au_lnamevarchar(40),
au_fnamevarchar(20),
phonechar(12),
addressvarchar(40),
cityvarchar(20),
statechar
(2),
zipchar(5),
contractbit
)
INSERTINTOnew_authors
SELECTTOP10*
FROMauthors
insertintoscott.emp(empno,ename,hiredate)
(selectempno+100,ename,hiredatefromscott.emp
whereempno>=7000);
createtablescott.em
as(
selectempno,ename,hiredate
fromscott.emp
whereempno>=8000);
UPDATEpublishers
SETcountry=NULL
UPDATEauthors
SETstate='PC',city='Gary-g'
WHEREstate='IN'ANDcity='Gary'
UPDATEtitles
SETytd_sales=
(SELECTSUM(qty)FROMsales
WHEREsales.title_id=titles.title_id
ANDsales.ord_dateIN(SELECTMAX(ord_date)FROMsales))
FROMtitles,sales
updatescott.emp
setempno=8002,ename='FORD',hiredate='03-12月-81'
whereempno=8033;
UPDATEauthors
SETstate='CA'
FROM(SELECTTOP5*FROMauthorsORDERBYau_lname)ASt1
WHEREauthors.au_id=t1.au_id
updatescott.emp
setsal=
(selectsal+1000fromscott.emp
whereempno=7666)
whereempno=7666;
DELETEFROMnew_authors
WHEREau_lname='Dull'
deletefromscott.empwhereempno=8099;
TRUNCATETABLEnew_authors
truncatetableBILLS;
上机实验
insertintousers
values('xiao','123','xiao','xiao@','xiao','xiao')
select*fromusers
UPDATEusers
SETUser_Name='xiaojun',Password='mima'
WHEREUser_Name='123'ANDPassword='ee'
select*fromusers
DELETEFROMusers
WHEREUser_Name='13434'
select*fromusers
DELETEFROMusers
select*fromusers
第5章
正文
SELECTcolumn_1,column_2,column_3,column_4
FROMT1
select*fromscott.emp;
SELECTcolumn_2,column_3
FROMT1
SELECTcolumn_3,column_2
FROMT1
selectempno,ename,jobfromscott.emp;
SELECTdistinctcolumn_2
FROMT1
selectdistinctjobfromscott.emp;
SELECTcolumn_1ASid,column_2,column_3,column_4
FROMT1
SELECTid=column_1,column_2,column_3,column_4
FROMT1
SELECTenameAS"empname",job
fromscott.emp;
SELECTcolumn_1,column_2,column_3,column_4,1+2
FROMT1
selectempno,ename,job,1+2
fromscott.emp;
SELECTcolumn_1,column_2,column_3,column_4,'zifuchuanlie'
FROMT1
selectempno,ename,job,empno+1000
fromscott.emp;
selectempno,ename,job,empno+sal
fromscott.emp;
selectempno,ename,sal,empno+sal*1.5
fromscott.emp;
SELECTTOP2*
FROMT1
SELECTavg(column_3)zonghe
FROMT1
selectavg(sal)平均薪水
fromscott.emp;
selectcount(sal)记录总数
fromscott.emp;
SELECTmin(column_3)
FROMT1
selectmin(sal)最少薪水
fromscott.emp;
SELECTmax(column_3)
FROMT1
selectmax(sal)最高薪水
fromscott.emp;
SELECTsum(column_3)
FROMT1
selectavg(sal)薪水总和
fromscott.emp;
SELECTcolumn_1,column_2,column_3,column_4
FROMT1
WHEREcolumn_3<100
selectempno,ename,salfromscott.emp
wheresal>=2000;
SELECTcolumn_1,column_2,column_3,column_4
FROMT1
WHEREcolumn_2>'王开'
SELECTcolumn_1,column_2,column_3,column_4
FROMT1
wherecolumn_3>50andcolumn_3<120
selectempno,ename,job,salfromscott.emp
wherejob>='CLERK'andsal<=3000;
SELECTcolumn_1,column_2,column_3,column_4
FROMT1
wherecolumn_3>50orcolumn_1<3
selectempno,ename,job,salfromscott.emp
wherejob>='CLERK'orsal<=2000;
SELECTcolumn_1,column_2,column_3,column_4
FROMT1
wherenotcolumn_1=2
selectempno,ename,job,salfromscott.emp
wherenotjob='CLERK';
SELECTcolumn_1,column_2,column_3,column_4
FROMT1
wherecolumn_1>3orcolumn_2>'王开'andcolumn_3<120
selectempno,ename,job,salfromscott.emp
wherejob>='CLERK'orempno>2000andsal<8000;
SELECTcolumn_1,column_2,column_3,column_4
FROMT1
wherecolumn_1IN(1,2,3,4)
selectempno,ename,job,salfromscott.emp
wheresalIN(800,1600,3000,5000);
SELECTcolumn_1,column_2,column_3,column_4
FROMT1
wherecolumn_1NOTIN(3,4)
SELECTcolumn_1,column_2,column_3,column_4
FROMT1
wherecolumn_3BETWEEN50AND120
selectempno,ename,job,salfromscott.emp
wheresalBETWEEN800and5000;
SELECTcolumn_1,column_2,column_3,column_4
FROMT1
wherecolumn_3notBETWEEN50AND100
SELECTcolumn_1,column_2,column_3,column_4
FROMT1
wherecolumn_2LIKE'王_'
selectempno,ename,job,salfromscott.emp
whereenamelike'KIN_';
SELECTcolumn_1,column_2,column_3,column_4
FROMT1
wherecolumn_2LIKE'__'
SELECTcolumn_1,column_2,column_3,column_4
FROMT1
wherecolumn_2LIKE'王%'
selectempno,ename,job,salfromscott.emp
whereenamelike'S%';
SELECTcolumn_1,column_2,column_3,column_4
FROMT1
wherecolumn_4LIKE'[Vv]%'
selectempno,ename,job,salfromscott.emp
wherelower(ename)like'S%';
SELECTcolumn_1,column_2,column_3,column_4
FROMT1
wherecolumn_2NOTLIKE'张%'and
column_2NOTLIKE'王%'
SELECTcolumn_1,column_2,column_3,column_4
FROMT1
WHEREcolumnLIKE’%20$%%’ESCAPE’$’
selectempno,ename,job,salfromscott.emp
wheresalISNULL;
SELECTcolumn_1,column_2,column_3,column_4
FROMT1
wherecolumn_4ISNOTNULL
selectempno,ename,job,salfromscott.emp
wheresalISNULLorsal<5000;
selectempno,ename,job,salfromscott.emp
wheresal>2000orsal<6000
ORDERBYempno;
SELECTcolumn_1,column_2,column_3,column_4
FROMT1
wherecolumn_3<200
ORDERBYcolumn_3DESC
selectempno,ename,job,salfromscott.emp
whereename>'B'
ORDERBYename;
SELECTcolumn_1,column_2,column_3assal,column_4
FROMT1
wherecolumn_3<200
ORDERBYsal
selectempno,ename,job,salfromscott.emp
wheresal>2000orsal<6000
ORDERBY4;
selectempno,ename,job,salfromscott.emp
ORDERBYsal,empno;
SELECTcolumn_1,count(column_3)
FROMT1
GROUPBYcolumn_1
selectempno,sum(sal)fromscott.emp
GROUPBYsal,empno;
SELECTcolumn_1,column_2,count(*)
FROMT1
GROUPBYcolumn_1,column_2
selectempno,sal,count(*)fromscott.emp
GROUPBYempno,sal;
selectempno,count(*)fromscott.emp
GROUPBYempno
HAVINGSUM(sal)>2000;
SELECTcolumn_1,avg(column_3),sum(column_3)
FROMT1
GROUPBYcolumn_1
HAVINGsum(column_3)>60or
avg(column_3)<30
上机实验
SELECT*
FROMware
SELECTtype_id,Model_Name,Startprice,Saleprice,Wareshow
FROMware
SELECTtype_id
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 实用教程 程序 源码