UPDATE , DELETE . DELETE , . , , , :
UPDATE Salespeople
SET comm = comm +.01
WHERE 2 <= (SELECT COUNT (cnum)
FROM Customers
WHERE Customers.snum = Salespeople.snum);
Peel Serres, , .
DELETE. , , :
UPDATE salespeople
SET comm = comm -.01
WHERE snum IN (SELECT snum
FROM Orders a
WHERE amt = (SELECT MIN (amt)
FROM Orders b
WHERE a.odate = b.odate));
DML
, (UPDATE), .
, , . , ( 1.), , , ( 2.), :
1.
SELECT AVG (rating)
FROM Customers;
= 200.
2.
DELETE
FROM Customers
WHERE rating < 200;
, . , , , , , .
: INSERT, . VALUES ( ), ( ). , , , , FROM, ( ). .
, , , FROM , FROM ( ).
DELETE UPDATE , , , . , , , . , , , . , , , FROM.
|
|
|
, SQL, . , . , . , , .
, , , ( ), ( ), .
SQL
1. , , Multicust, , . , ( ) .
2. , , .
3. , , $3,000.
(. A .)
, , - . , , . , , , .
, . , , . , SQL , . SQL DDL ( ), SQL.
SQL . , , , .
, , - , , , , .
18.
CREATE TABLE. . DML INSERT (. 15). CREATE TABLE , . . , , .
CREATE TABLE:
CREATE TABLE <table-name >
(<column name > <data type>[(<size>)],
<column name > <data type> [(<size>)]...);
2, . , , , ANSI. B.
|
|
|
SQL, ( , ). (_) .
. , . , , (. 14).
, . , , , , , .
, , , CHAR. , , . , ( NULL) . , = 1, , . , , , .
. (, Interbase) varchar , NULL, , , : (NULL) '' .
, , , , . , . city . , , ; , Employees, Smith, Smith.Employees - . , Smith (ID), ( ID SQL). 2, 22.
:
CREATE TABLE Saleepeople
(snum integer,
sname char (10),
city char (10),
comm declmal);
, . ( ), .
( ) . , , - , .
, , . 18 , . SQL. , .
SQL, , ANSI. ANSI , .
, . , , cnum=2999. , , , cnum 2999. cnum, 2999 , .
|
|
|
, DML INSERT, UPDATE DELETE, , , . , , , , .
. , , , . , , . .
(, ANSI ):
CREATE INDEX <index name> ON <table name>
(<column name> [,<column name>]...);
, , . - ( ). , . SQL , , , .
, , , snum .
CREATE INDEX Clientgroup ON Customers (snum);
, , , .
, , , , . . , , UNIQUE INDEX. num, , :
CREATE UNIQUE INDEX Custid ON Customers (cnum);
: , cnum. , , , . , , , , .
cnum . . 18 19.
, . . SQL , , , . , , . :
DROP INDEX <Index name>;
.
