SQL实用教程程序源码.doc

269程序代码第2章上机实验SELECT Warequantity * UnitcostFROM OrderContentWHERE Order_ID=3SELECT sum(Warequantity * Unitcost)FROM OrderContent第3章正文CREATE DATABASE studentCREATE TABLE BILLS( name CHAR(10), birthday INT, address VARCHAR(20), city CHAR(10), sex CHAR(10), id INT NOT NULL, salary NUMERIC(10,2), pno INT)SET NOCOUNT OFFUSE studentIF EXISTS (SELECT name FROM sysindexes WHERE name = Id_ind) DROP INDEX Stu.Id_indGOUSE studentCREATE INDEX Id_indON Stu (Id)GOCREATE INDEX com_INDEX ON scott.emp(COMM);create index pk_p_mainon Stu(Id, Name) withpad_index,fillfactor = 100on primaryCREATE UNIQUE INDEX id_indexON Stu(id)GOALTER DATABASE studentADD LOG FILE ( NAME = test1log2, FILENAME = c:Program FilesMicrosoft SQL ServerMSSQLDatatest1log.ldf, SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB),( NAME = test1log3, FILENAME = c:Program FilesMicrosoft SQL ServerMSSQLDatatest1log.ldf, SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB)GOGOALTER DATABASE studentREMOVE FILE test1log3GOALTER TABLE StuADD mynewcolumn INT NULLALTER TABLE em ADD (emsex char(20)；CREATE TABLE doc_exb ( column_a INT, column_b VARCHAR(20) NULL) GOALTER TABLE doc_exb DROP COLUMN column_bGOEXEC sp_help doc_exbGODROP TABLE doc_exbGODROP DATABASE doc_exbDROP TABLE titles1DROP TABLE abcDROP INDEX authors.idindexDROP INDEX id_index；上机实验CREATE DATABASE WareCREATE TABLE dbo.Users (User_ID int IDENTITY (1, 1) NOT NULL ,User_Name nvarchar (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,Password nvarchar (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,Name nvarchar (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,Email nvarchar (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,Card nvarchar (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,Tell nvarchar (50) COLLATE Chinese_PRC_CI_AS NULL ) ON PRIMARYCREATE TABLE dbo.ware (ware_id int IDENTITY (1, 1) NOT NULL ,type_id int NOT NULL ,Model_id nvarchar (50) COLLATE Chinese_PRC_CI_AS NULL ,Model_Name nvarchar (50) COLLATE Chinese_PRC_CI_AS NULL ,StartPrice money NOT NULL ,SalePrice money NOT NULL ,wareshow nvarchar (800) COLLATE Chinese_PRC_CI_AS NULL ) ON PRIMARYCREATE TABLE dbo.waretype (type_ID int IDENTITY (1, 1) NOT NULL ,type_Name nvarchar (50) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON PRIMARYCREATE TABLE dbo.Shopping (ID int IDENTITY (1, 1) NOT NULL ,ShoppingID nvarchar (50) COLLATE Chinese_PRC_CI_AS NULL ,ware int NOT NULL ,wareQuantity int NOT NULL ,ShoppingDate datetime NOT NULL ) ON PRIMARYCREATE TABLE dbo.Orders (Order_ID int IDENTITY (1, 1) NOT NULL ,User_ID int NOT NULL ,OrderDate datetime NOT NULL ) ON PRIMARYCREATE TABLE dbo.OrderContent (Order_ID int NOT NULL ,ware_ID int NOT NULL ,wareQuantity int NOT NULL ,UnitCost money NOT NULL ) ON PRIMARY第4章正文IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = T1) DROP TABLE T1GOCREATE TABLE T1 ( column_1 int, column_2 varchar(30)INSERT T1VALUES (1, Row)insert into BILLSvalues(xiaoxing,2006,bailaohui,beijing,man,10,1000,123);insert into scott.emp(empno,ename,hiredate)values(7999,JONE,25-11月-05);CREATE TABLE T1 ( column_1 int identity, column_2 varchar(30) CONSTRAINT default_name DEFAULT (column default), column_3 int NULL, column_4 varchar(40)INSERT INTO T1 (column_4) VALUES (value)INSERT INTO T1 (column_2,column_4) VALUES (value, value)INSERT INTO T1 (column_2,column_3,column_4) VALUES (value,-44,value)SELECT * FROM T1USE pubsCREATE TABLE new_authors( au_id id, au_lname varchar(40), au_fname varchar(20), phone char (12), address varchar(40), city varchar(20), state char(2), zip char(5), contract bit)INSERT INTO new_authors SELECT TOP 10 * FROM authorsinsert into scott.emp(empno,ename,hiredate)(select empno+100,ename,hiredate from scott.empwhere empno=7000);create table scott.emas(select empno,ename,hiredatefrom scott.empwhere empno=8000);UPDATE publishersSET country = NULLUPDATE authors SET state = PC, city = Gary-g WHERE state = IN AND city = GaryUPDATE titlesSET ytd_sales = (SELECT SUM(qty) FROM salesWHERE sales.title_id = titles.title_idAND sales.ord_date IN (SELECT MAX(ord_date) FROM sales)FROM titles, salesupdate scott.empset empno=8002,ename=FORD,hiredate=03-12月-81where empno=8033;UPDATE authorsSET state = CA FROM (SELECT TOP 5 * FROM authors ORDER BY au_lname) AS t1WHERE authors.au_id = t1.au_idupdate scott.empset sal=(select sal+1000 from scott.empwhere empno=7666)where empno=7666;DELETE FROM new_authorsWHERE au_lname = Dulldelete from scott.emp where empno=8099;TRUNCATE TABLE new_authorstruncate table BILLS;上机实验insert into users values(xiao,123,xiao,,xiao,xiao)select * from usersUPDATE users SET User_Name = xiaojun, Password = mima WHERE User_Name = 123 AND Password = eeselect*from usersDELETE FROM usersWHERE User_Name = 13434select*from usersDELETE FROM usersselect*from users第5章正文SELECT column_1, column_2, column_3, column_4FROM T1select * from scott.emp;SELECT column_2, column_3FROM T1SELECT column_3, column_2FROM T1select empno,ename,job from scott.emp;SELECT distinct column_2FROM T1select distinct job from scott.emp;SELECT column_1 AS id, column_2, column_3, column_4FROM T1SELECT id = column_1, column_2, column_3, column_4FROM T1SELECT ename AS emp name,jobfrom scott.emp;SELECT column_1, column_2, column_3, column_4,1+2FROM T1select empno,ename,job,1+2from scott.emp;SELECT column_1,column_2,column_3,column_4,zi fu chuan lieFROM T1select empno,ename,job,empno+1000from scott.emp;select empno,ename,job,empno+salfrom scott.emp;select empno,ename,sal,empno+sal*1.5from scott.emp;SELECT TOP 2 *FROM T1SELECT avg(column_3)zongheFROM T1select avg(sal) 平均薪水from scott.emp;select count(sal) 记录总数from scott.emp;SELECT min(column_3)FROM T1select min(sal) 最少薪水from scott.emp;SELECT max(column_3)FROM T1select max(sal) 最高薪水from scott.emp;SELECT sum(column_3)FROM T1select avg(sal) 薪水总和from scott.emp;SELECT column_1, column_2, column_3, column_4FROM T1WHERE column_3=2000;SELECT column_1, column_2, column_3, column_4FROM T1WHERE column_2王开SELECT column_1, column_2, column_3, column_4FROM T1where column_350 and column_3=CLERK and sal50 or column_1=CLERK or sal3 or column_2王开 and column_3=CLERK or empno2000 and sal8000;SELECT column_1, column_2, column_3, column_4FROM T1where column_1 IN(1,2,3,4)select empno,ename,job,sal from scott.emp where sal IN(800,1600,3000,5000);SELECT column_1, column_2, column_3, column_4FROM T1where column_1 NOT IN(3,4)SELECT column_1, column_2, column_3, column_4FROM T1where column_3 BETWEEN 50 AND 120select empno,ename,job,sal from scott.emp where sal BETWEEN 800 and 5000;SELECT column_1, column_2, column_3, column_4FROM T1where column_3 not BETWEEN 50 AND 100SELECT column_1, column_2, column_3, column_4FROM T1where column_2 LIKE 王_select empno,ename,job,sal from scott.emp where ename like KIN_;SELECT column_1, column_2, column_3, column_4FROM T1where column_2 LIKE _SELECT column_1, column_2, column_3, column_4FROM T1where column_2 LIKE 王%select empno,ename,job,sal from scott.emp where ename like S%;SELECT column_1, column_2, column_3, column_4FROM T1where column_4 LIKE Vv%select empno,ename,job,sal from scott.emp where lower(ename) like S%;SELECT column_1, column_2, column_3, column_4FROM T1where column_2 NOT LIKE 张%andcolumn_2 NOT LIKE 王%SELECT column_1, column_2, column_3, column_4FROM T1WHERE column LIKE %20$% ESCAPE $select empno,ename,job,sal from scott.empwhere sal IS NULL;SELECT column_1, column_2, column_3, column_4FROM T1where column_4 IS NOT NULLselect empno,ename,job,sal from scott.empwhere sal IS NULL or sal2000 or sal6000ORDER BY empno;SELECT column_1, column_2, column_3, column_4FROM T1where column_3BORDER BY ename;SELECT column_1, column_2, column_3 as sal, column_4FROM T1where column_32000 or sal2000;SELECT column_1, avg(column_3), sum(column_3)FROM T1GROUP BY column_1HAVING sum(column_3) 60 or avg(column_3) 3 and Saleprice=5SELECT type_id,Model_Name,Startprice,Saleprice,WareshowFROM wareWHERE Wareshow IN(食品,饮料,家电,运输工具)SELECT type_id,Model_Name,Startprice,Saleprice,WareshowFROM wareWHERE Saleprice BETWEEN 10 and 10000第6章正文SELECT , systypes.xtype, systypes.length,systypes.uid,sysobjects.idFROM systypes,sysobjects WHERE systypes.uid=sysobjects.uidselect emp.empno, emp.ename, emp.deptno, dept.dname,dept.locfrom scott.emp,scott.dept;SELECT , systypes.xtype, systypes.length,systypes.uid,sysobjects.idFROM systypes JOIN sysobjects ON systypes.uid=sysobjects.uidselect emp.empno, emp.ename, emp.deptno, dept.dname,dept.locfrom scott.emp JOIN scott.deptON scott.emp.deptno=scott.dept.deptno;SELECT , systypes.xtype, systypes.length,systypes.uid,sysobjects.idFROM systypes CROSS JOIN sysobjects WHERE systypes.uid=sysobjects.uidselect emp.empno, emp.ename, emp.deptno, dept.dname,dept.locfrom scott.emp CROSS JOIN scott.deptWHERE scott.emp.deptno=scott.dept.deptno;SELECT *FROM authors AS a INNER JOIN publishers AS pON a.city=p.cityselect emp.empno, emp.ename, emp.deptno, dept.dname,dept.locfrom scott.emp INNER JOIN scott.deptON scott.emp.deptno=scott.dept.deptno;SELECT *FROM authors AS a INNER JOIN publishers AS pON a.cityp.cityselect emp.empno, emp.ename, emp.deptno, dept.dname,dept.locfrom scott.emp INNER JOIN scott.deptON scott.emp.deptno!=scott.dept.deptno and scott.emp.deptno=20;SELECT a.*,p.pub_idFROM authors AS a INNER JOIN publishers AS p ON a.city=p.cityselect emp.*, dept.dnamefrom scott.emp INNER JOIN scott.deptON scott.emp.deptno=scott.dept.deptno;SELECT p.pub_id,p.pub_name,s.stor_nameFROM publishers AS p LEFT JOIN stores sON p.state=s.stateselect emp.empno, emp.ename, emp.deptno, dept.dname,dept.locfrom scott.emp LEFT JOIN scott.deptON scott.emp.deptno=scott.dept.deptno;SELECT p.pub_id,p.pub_name,s.stor_nameFROM publishers AS p RIGHT JOIN stores sON p.state=s.stateselect emp.empno, emp.ename, emp.deptno, dept.dname,dept.locfrom scott.emp RIGHT JOIN scott.deptON scott.emp.deptno=scott.dept.deptno;SELECT p.pub_id,p.pub_name,s.stor_nameFROM publishers AS p FULL JOIN stores sON p.state=s.stateselect emp.empno, emp.ename, emp.deptno, dept.dname,dept.locfrom scott.emp FULL JOIN scott.deptON scott.emp.deptno=scott.dept.deptno;SELECT 编号=p.pub_id,名称=p.pub_nameFROM publishers AS p INNER JOIN publishers AS sON p.state=s.state WHERE p.city=s.citySELECT e.empno,m.enameFROM scott.emp e INNER JOIN scott.emp mON e.deptno=m.deptno WHERE e.empno=m.empno;SELECT p.pub_id,p.pub_name,s.stor_nameFROM publishers AS p CROSS JOIN stores AS sORDER BY p.pub_nameselect emp.empno, emp.ename, emp.deptno, dept.dname,dept.locfrom scott.emp CROSS JOIN scott.dept;SELECT pub_id,pub_nameFROM publishersWHERE state=CAUNIONSELECT stor_id,stor_nameFROM storesWHERE state=CAselect emp.deptnofrom scott.empunionselect dept.deptnofrom scott.dept;SELECT pub_id,pub_nameFROM publishersWHERE state=CAUNIONSELECT stor_idFROM storesWHERE state=CAselect emp.empno,emp.deptnofrom scott.empunionselect dept.deptno,dept.dnamefrom scott.dept;SELECT pub_id,pub_name,cityFROM publishersWHERE state=CAUNIONSELECT stor_id,stor_name,cityFROM storesWHERE state=CAORDER BY citySELECT name,idFROM emp3unionSELECT name,idFROM emp4ORDER BY name;SELECT pub_id,pub_name,city,stateFROM publishersUNION ALLSELECT stor_id,stor_name,city,stateFROM storesselect emp.deptnofrom scott.empunion allselect dept.deptnofrom scott.dept;select emp.deptnofrom scott.empINTERSECTselect dept.deptnofrom scott.dept;SELECT name,idFROM emp3INTERSECTSELECT name,idFROM emp4;select dept.deptnofrom scott.deptMINUSselect emp.deptnofrom scott.emp;select emp.deptnofrom scott.empMINUSselect dept.deptnofrom scott.dept;上机实验SELECT *FROM ware AS a INNER JOIN waretype AS bON a.type_idb.type_idSELECT a.*,b.type_NameFROM ware AS a INNER JOIN waretype AS bON a.type_id=b.type_idSELECT a.Model_id,a.Model_Name,a.Wareshow,b.type_NameFROM ware AS a LEFT JOIN waretype AS bON a.type_id=b.type_idSELECT a.Model_id,a.Model_Name,a.Wareshow,b.type_NameFROM ware AS a FULL JOIN waretype AS bON a.type_id=b.type_idSELECT a.Model_id,a.Model_Name,a.Saleprice,a.WareshowFROM ware AS a INNER JOIN ware AS bON a.type_id=b.type_idWHERE a.Wareshow=b.WareshowSELECT Model_NameFROM ware WHERE type_id=5UNIONSELECT type_NameFROM waretypeWHERE type_id=5第7章正文SELECT pub_idFROM publishersWHERE pub_name=BinnetSELECT 名称=title,数量=ytd_salesFROM titlesWHERE pub_id=0877SELECT 名称=title,数量=ytd_salesFROM titlesWHERE pub_id=(SELECT pub_id FROM publishers WHERE pub_name=Binnet)SELECT empno,ename, salFROM scott.empWHERE sal IN (SELECT MAX(sal)FROM scott.emp);SELECT MAX(sal)FROM scott.emp;SELECT empno,ename, salFROM scott.empWHERE sal = 5000;SELECT empno,ename, salFROM scott.empWHERE sal IN(SELECT salFROM scott.empWHERE ename = KING);SELECT e.empno,e.ename, e.salFROM scott.emp e,scott.emp mWHERE e.sal = m.sal AND m.ename = KING;SELECT 作者=a.au_lname,编者=a.au_fnameFROM authors aWHERE PC8888 IN(SELECT ta.title_idFROM titleauthor taWHERE a.au_id=ta.au_id)SELECT empno Department, ename ManagerFROM scott.emp eWHERE 0 = ALL(SELECT 2 * AVG(t2.advance)FROM titles t2WHERE t1.type = t2.type)SELECT 作者=a.au_lname,编者=a.au_fnameFROM authors aWHERE PC8888 IN(SELECT ta.title_idFROM titleauthor ta,authors aWHERE a.au_id=ta