【正文】 blic數(shù)據(jù)庫對象，操作類型Windows，混合三、簡答題數(shù)據(jù)庫的安全性是指保護數(shù)據(jù)庫以防止不合法的使用所造成的數(shù)據(jù)泄漏、更改或破壞。, 2499, 39。, 39。, 39。 GROUP BY Cname, Csex, Cage HAVING SUM(Smoney)10000INSERT INTO GoodsPhone VALUES (39。 AND 39。CREATE VIEW SellsQuantity(Gname, Price, Manufac, Squantity) AS SELECT Gname, Price, Manufac, SUM(Quantity) FROM Goods G JOIN Detail D ON = GROUP BY , Gname, Price, ManufacSELECT Gname, Price, Manufac FROM SellsQuantity WHERE Squantity = (SELECT MAX(Squantity) FROM SellsQuantity)CREATE VIEW SellsMoney(Sno, Sdate, Cname, Csex, Cage, Smoney) AS SELECT , Sdate, Cname, Csex, Cage, SUM(Quantity*Price) FROM Sells S, Customers C, Detail D, Goods G WHERE = AND = AND = GROUP BY , Sdate, Cname, Csex, CageSELECT Cname, Csex, Cage FROM SellsMoney WHERE Sdate BETWEEN 39。CREATE VIEW GoodsPhoneHuaWei AS SELECT * FROM GoodsPhone WHERE Manufac=39。四、SQL語言CREATE INDEX Index_Customers_Cname ON Customers(Cname)CREATE INDEX Index_Goods_GtypePrice ON Goods(Gtype, Price)CREATE INDEX Index_Sells_CnoSdate ON Sells(Cno, Sdate DESC)CREATE VIEW GoodsPhone AS SELECT * FROM Goods WHERE Gtype=39。從這個意義上講，視圖就像一個窗口，通過它用戶可以看到數(shù)據(jù)庫中自己感興趣的數(shù)據(jù)及其變化。與基本表不同，視圖是一張?zhí)摫?，在?shù)據(jù)庫中只存放視圖的定義（即SELECT語句），不存放視圖對應的數(shù)據(jù)（即SELECT語句的查詢結(jié)果）。 基本表在數(shù)據(jù)庫中既要存放它的定義，又要存放它的數(shù)據(jù)。但是，如果索引過大而不能放在內(nèi)存中，那么當需要時，就必須從磁盤中讀取索引塊，于是搜索一個索引項可能需要多次讀取磁盤塊，是一個相當耗時的過程。顯然，一張表可以創(chuàng)建多個輔助索引。聚集索引往往是稀疏索引，可以只存儲部分搜索碼值。所謂聚集索引就是指表中的元組按照索引中搜索碼指定的順序排序，使得具有相同搜索碼值的元組在物理上聚集在一起。當表按照搜索碼值有序存儲時，可以只為搜索碼的某些值建立索引項，這種索引稱為稀疏索引。)第四章一、單項選擇題ABADC ACCDB二、填空題搜索碼，搜索碼值有序稀疏，稠密，1，多249，16CLUSTEREDPRIMARY KEY，UNIQUE索引視圖三、簡答題模式DDL有CREATE TABLE語句，子模式DDL有CREATE VIEW語句，內(nèi)模式DDL有CREATE INDEX語句。, 2499, 39。, 39。, 39。2INSERT INTO Goods VALUES (39。2DELETE FROM Goods WHERE Gno=39。 AND NOT EXISTS (SELECT * FROM Sells S JOIN Detail D ON = WHERE = AND =))1SELECT Gno, Gname, Manufac FROM Goods G, (SELECT Gtype, MIN(Price) AS MinPrice FROM Goods GROUP BY Gtype) AS GtypePrice WHERE = AND Price=MinPriceUPDATE Goods SET Price=*Price WHERE Gtype=39。 GROUP BY Sno HAVING COUNT(*)=21SELECT Cno FROM Customers C WHERE NOT EXISTS (SELECT * FROM Goods G WHERE Gtype=39。 OR Gno=39。) 另一種做法： SELECT Sno FROM Detail WHERE Gno=39。 AND EXISTS (SELECT * FROM Detail WHERE Sno= AND Gno=39。)1SELECT Gno, Gname, Manufac FROM Goods G WHERE Price = (SELECT MIN(Price) FROM Goods WHERE Gtype=)1SELECT Gname, Price, Manufac FROM Goods WHERE Gno IN (SELECT Gno FROM Detail GROUP BY Gno HAVING SUM(Quantity)=ALL(SELECT SUM(Quantity) FROM Detail GROUP BY Gno))1SELECT Sno FROM Detail D WHERE Gno=39。)1SELECT Cno FROM Customers WHERE Cno NOT IN (SELECT Cno FROM Sells)1SELECT Cno, Cname, Mphone FROM Customers WHERE Cno NOT IN (SELECT Cno FROM Sells WHERE Sdate=39。 AND Gname=39。 另一種做法： SELECT SUM(Quantity) FROM Detail WHERE Gno IN (SELECT Gno FROM Goods WHERE Manufac=39。 AND Gname=39。SELECT , Cname, Mphone, Sdate, IsPay FROM Customers C LEFT JOIN Sells S ON =1SELECT , Sdate, Cname, SUM(Quantity*Price) FROM Sells S, Customers C, Detail D, Goods G WHERE = AND = AND = GROUP BY , Sdate, Cname1SELECT SUM(Quantity) FROM Detail D JOIN Goods G ON = WHERE Manufac=39。 AND 39。 AND Sdate BETWEEN 39。SELECT COUNT(*) FROM GoodsSELECT Gtype, COUNT(*), AVG(Price) FROM Goods GROUP BY GtypeSELECT Gtype FROM Goods GROUP BY Gtype HAVING AVG(Price)1000SELECT Sno, , Cname, Mphone FROM Sells S JOIN Customers C ON = WHERE IsPay=39。 AND Gname LIKE 39。 ORDER BY Price DESCSELECT Gname, Price FROM Goods WHERE Manufac=39。) ) ) CREATE TABLE Detail ( Sno char(14) FOREIGN KEY REFERENCES Sells(Sno) , Gno char(9) FOREIGN KEY REFERENCES Goods(Gno) , Quantity smallint NOT NULL CONSTRAINT ck_D CHECK ( Quantity BETWEEN 1 AND 100 ) , PRIMARY KEY ( Sno,Gno ) )ALTER TABLE Customers ADD CONSTRAINT ck1_C CHECK ( Cage = 10 ) ALTER TABLE Customers ADD CONSTRAINT ck2_C UNIQUE ( Mphone )ALTER TABLE Detail DROP CONSTRAINT ck_D ALTER TABLE Detail ADD CONSTRAINT ck_D CHECK ( Quantity BETWEEN 1 AND 1000 )SELECT Gname, Price FROM Goods WHERE Manufac=39。,39。) ) , Cage tinyint NOT NULL , Caddress varchar(50) , Mphone char(11) NOT NULL , Email varchar(30) ) CREATE TABLE Goods ( Gno char(9) PRIMARY KEY , Gname char(20) NOT NULL , Gtype char(8) NOT NULL , Price decimal(9,2) NOT NULL CHECK ( Price BETWEEN 1 AND 100000 ) , Manufac char(12) NOT NULL ) CREATE TABLE Sells ( Sno char(14) PRIMARY KEY , Sdate datetime NOT NULL , Saddress varchar(50) NOT NULL , Cno char(8) NOT NULL FOREIGN KEY REFERENCES Customers(Cno) , IsPay char(1) NOT NULL CHECK ( IsPay IN (39。,39。四、SQL語言 CREATE TABLE Customers ( Cno char(8) PRIMARY KEY , Cname char(12) NOT NULL , Csex char(2) NOT NULL CHECK ( Csex IN (39。(3)設置為空值（SET NULL），表示當刪除或修改被參照表的一個元組造成了參照表中某些元組的外碼違反了參照完整性約束，則系統(tǒng)會自動將參照表中所有違反參照完整性約束的元組的外碼設置為空值。三種處理方式：(1)拒絕（NO ACTION）執(zhí)行，即不允許執(zhí)行該操作，一般為默認違約處理方式。在SQL Server中可以在列級或表級用NOT NULL（屬性取非空值）、UNIQUE（屬性取值唯一）和CHECK（檢查屬性值是否滿足一個邏輯表達式）三種形式實現(xiàn)用戶定義的完整性。SQL語言中，在用CREATE TABLE語句創(chuàng)建表時，可以用PRIMARY