DATABASE SQL

最新推荐文章于 2021-12-03 13:01:36 发布

yuemingchuidi

最新推荐文章于 2021-12-03 13:01:36 发布

阅读量351

点赞数

分类专栏： M.S.

M.S. 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

Structured Query Language (SQL)

CREATE

<span style="font-family: Arial, Helvetica, sans-serif;">CREATE SCHEMA AUTHORIZATION {creator};</span><span style="font-family: Arial, Helvetica, sans-serif;">				</span><span style="font-family: Arial, Helvetica, sans-serif;">//Seldom used directly</span>

CREATE TABLE <em>tablename</em>();

<pre name="code" class="sql">CREATE TABLE VENDOR (
V_CODE     INTEGER           NOT NULL  UNIQUE,
V_NAME     VARCHAR(35)       NOT NULL,
V_CONTACT  VARCHAR(15)       NOT NULL,
V_AREACODE CHAR(3)           NOT NULL,
V_PHONE    CHAR(8)           NOT NULL,
V_STATE    CHAR(2)           NOT NULL,
V_ORDER    CHAR(1)           NOT NULL,
PRIMARY KEY (V_CODE));

</pre><pre name="code" class="sql"><pre name="code" class="sql">CREATE TABLE PRODUCT (
P_CODE         VARCHAR(10)  NOT NULL    UNIQUE,
P_DESCRIPT     VARCHAR(35)  NOT NULL,
P_INDATE       DATE         NOT NULL,
P_QOH          SMALLINT     NOT NULL,
P_MIN          SMALLINT     NOT NULL,
P_PRICE        NUMBER(8,2)  NOT NULL,
P_DISCOUNT     NUMBER(5,2)  NOT NULL,
V_CODE         INTEGER,
PRIMARY KEY (P_CODE),
FOREIGN KEY (V_CODE) REFERENCES VENDOR ON UPDATE CASCADE);

Use one line per column (attribute) definition
Use spaces to line up attribute characteristics and constraints
Table and attribute names are capitalized
Command sequence ends with semicolon
ANSI SQL allows use of following clauses to cover CASCADE, SET NULL, or SET DEFAULT
- ON DELETE and ON UPDATE
a composite primary key
- PRIMARY KEY (INV_NUMBER, LINE_NUMBER),
- The order of the primary key components is important
  - because the indexing starts with the first-mentioned attribute, then proceeds with the next attribute, and so on
The ON UPDATE CASCADE specification
- ensures that if you make a change in any VENDOR’s V_CODE, that change is automatically applied to all foreign key references throughout the system (cascade) to ensure that referential integrity is maintained.
- (Although the ON UPDATE CASCADE clause is part of the ANSI standard, some RDBMSs, such as Oracle, do not support ON UPDATE CASCADE. If your RDBMS does not support the clause, delete it from the code shown here.)
An RDBMS will automatically enforce referential integrity for foreign keys.
- cannot have an invalid entry in the foreign key column
- cannot delete a vendor row as long as a product row references that vendor.
column names
- Do not use mathematical symbols such as +, −, and / in your
  - instead, use an underscore to separate words, if necessary
  - For example, PER-NUM might generate an error message, but PER_NUM is acceptable
- do not use reserved words
  - Reserved words are words used by SQL to perform specific functions.
  - For example, in some RDBMSs, the column name INITIAL will generate the message invalid column name.

OTHER

AND | OR | NOT

<span style="font-family: Arial, Helvetica, sans-serif;">SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE</span>

FROM PRODUCT
WHERE P_PRICE < 50
AND P_INDATE > '15-Jan-2010';

<pre name="code" class="sql">SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM  PRODUCT
WHERE (P_PRICE < 50 AND P_INDATE > '15-Jan-2010') 
OR V_CODE = 24288;

<pre name="code" class="sql">SELECT *
FROM PRODUCT
WHERE NOT (V_CODE = 21344);

BETWEEN | IS NULL | LIKE | IN | EXISTS | DISTINCT

SELECT *
FROM PRODUCT
WHERE P_PRICE BETWEEN 50.00 AND 100.00;

SELECT P_CODE, P_DESCRIPT, V_CODE FROM PRODUCT
WHERE V_CODE IS NULL;

SELECT V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM VENDOR
WHERE V_CONTACT LIKE 'Smith%';

SELECT V_NAME, V_CONTACT, V_AREACODE, V_PHONE 
FROM VENDOR
WHERE V_CONTACT NOT LIKE 'Smith%';

SELECT *
FROM VENDOR
WHERE V_CONTACT LIKE 'Johns_n';

SELECT *
FROM PRODUCT
WHERE V_CODE IN (21344, 24288);

-- V_CODE = 21344 OR V_CODE = 24288;

SELECT V_CODE, V_NAME
FROM VENDOR
WHERE V_CODE IN (SELECT V_CODE FROM PRODUCT);

SELECT *
FROM VENDOR
WHERE EXISTS (SELECT * FROM PRODUCT WHERE P_QOH <= P_MIN * 2);

list all vendors, but only if there are products with the quantity on hand, less than double the minimum quantity:

ADDITIONAL DATA DEFINITION COMMANDS

ALTER

Changing a Column’s Data Type

ALTER TABLE PRODUCT
MODIFY (V_CODE CHAR(5));

Changing a Column’s Data Characteristics

ALTER TABLE PRODUCT
MODIFY (P_PRICE DECIMAL(9,2));

Adding a Column

ALTER TABLE PRODUCT
ADD (P_SALECODE CHAR(1));

NOT possible to add the NOT NULL clause for the new column [p254]. DO NOT DO THAT!

can add the NOT NULL clause to the table structure after all of the data for the new column have been entered and the column no longer contains nulls.

Dropping a Column

ALTER TABLE VENDOR
DROP COLUMN V_ORDER;

Possible restrictions: you may not drop attributes that are involved in foreign key relationships, nor may you delete an attribute of a table that contains only that one attribute.

Advanced Data Updates

UPDATE PRODUCT
SET P_SALECODE = '2' 
WHERE P_CODE = '1546-QQ2';

UPDATE PRODUCT
SET P_SALECODE = '1'
WHERE P_CODE IN ('2232/QWE', '2232/QTY');

UPDATE PRODUCT
SET P_SALECODE = '1'
WHERE P_CODE = '2232/QWE' OR P_CODE = '2232/QTY';

UPDATE - might be very cumbersome. Fortunately, if a relationship can be established between the entries and the existing columns, the relationship can be used to assign values to their appropriate slots.

UPDATE PRODUCT
SET P_SALECODE = '2' WHERE P_INDATE < '25-Dec-2009';

<div class="page" title="Page 286"><div class="layoutArea"><div class="column"><p><span style="font-size: 9pt; font-family: Souvenir;"></span></p></div></div></div>UPDATE PRODUCT
SET P_SALECODE = '1'
WHERE P_INDATE >= '16-Jan-2010' AND P_INDATE <='10-Feb-2010';

The arithmetic operators are particularly useful in data updates

UPDATE PRODUCT
SET P_QOH = P_QOH + 20 
WHERE P_CODE = '2232/QWE';

<pre name="code" class="sql">UPDATE PRODUCT
SET P_PRICE = P_PRICE * 1.10 WHERE P_PRICE < 50.00;

If you are using Oracle, issue a ROLLBACK command to undo the changes made by the last two UPDATE statements.

Copying Parts of Tables

CREATE TABLE PART( 
PART_CODE           CHAR(8)   NOT NULL  UNIQUE,
PART_DESCRIPT       CHAR(35),
PART_PRICE          DECIMAL(8,2),
V_CODE              INTEGER,
PRIMARY KEY (PART_CODE));

INSERT INTO <em>target_tablename</em>[(<em>target_columnlist</em>)]
SELECT      <em>source_columnlist</em>
FROM        <em>source_tablename</em>;

</pre><pre name="code" class="sql">INSERT INTO PART (PART_CODE, PART_DESCRIPT, PART_PRICE, V_CODE) 
SELECT           P_CODE, P_DESCRIPT, P_PRICE, V_CODE FROM PRODUCT;

Rapidly create a new table based on selected columns and rows of an existing table: the new table will copy the attribute names, data characteristics, and rows of the original table

The Oracle version of the command is:

CREATE TABLE PART AS
SELECT P_CODE AS PART_CODE, P_DESCRIPT AS PART_DESCRIPT,
       P_PRICE AS PART_PRICE, V_CODE
FROM PRODUCT;

The MS Access version of this command is:

SELECT P_CODE AS PART_CODE, P_DESCRIPT AS PART_DESCRIPT, P_PRICE AS PART_PRICE, V_CODE INTO PART
FROM PRODUCT;

NOTE:

no entity integrity (primary key) or referential integrity (foreign key) rules are automatically applied to the new table.

Adding Primary and Foreign Key Designations

Define the primary key

ALTER TABLE PART
     ADD    PRIMARY KEY (PART_CODE);

define the foreign key

ALTER TABLE PART
     ADD FOREIGN KEY (V_CODE) REFERENCES VENDOR;

incorporate both changes at once

ALTER TABLE PART
     ADD    PRIMARY KEY (PART_CODE)
     ADD    FOREIGN KEY (V_CODE) REFERENCES VENDOR;

Designate composite primary keys and multiple foreign keys

ALTER TABLE LINE
     ADD PRIMARY KEY (INV_NUMBER, LINE_NUMBER)
     ADD FOREIGN KEY (INV_NUMBER) REFERENCES INVOICE
     ADD FOREIGN KEY (PROD_CODE) REFERENCES PRODUCT;

Deleting a Table from the Database

DROP TABLE PART;

You can drop a table only if that table is not the “one” side of any relationship. If you try to drop a table otherwise, the RDBMS will generate an error message indicating that a foreign key integrity violation has occurred.

Additional SELECT Query Keywords

SQL allows user to limit queries to entries:

Having no duplicates
Whose duplicates may be grouped

Ordering a Listing

SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE
FROM PRODUCT
ORDER BY P_PRICE;

SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE
FROM PRODUCT
ORDER BY P_PRICE ASC;

以上两者返回相同结果：the default order is ascending

SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE
FROM PRODUCT
ORDER BY P_PRICE DESC;

Cascading order sequence: Multilevel ordered sequence

SELECT   EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE
FROM     EMPLOYEE 
ORDER BY EMP_LNAME, EMP_FNAME, EMP_INITIAL;

SELECT    P_DESCRIPT, V_CODE, P_INDATE, P_PRICE
FROM      PRODUCT
WHERE     P_INDATE < '21-Jan-2010'
AND       P_PRICE <= 50.00
ORDER BY  V_CODE, P_PRICE DESC;

Note that within each V_CODE, the P_PRICE values are in descending order.

both are in descending order (figure 7.19)

Listing Unique Values

SELECT DISTINCT columnlist FROM tablelist;

SELECT DISTINCT V_CODE 
FROM   PRODUCT;

Access places nulls at the top of the list; Oracle places it at the bottom

The placement of nulls does not affect the list contents.

In Oracle, you could use ORDER BY V_CODE NULLS FIRST to place nulls at the top of the list.

The ORDER BY clause must always be listed last in the SELECT command sequence.

Aggregate Functions

most of the remaining input and output sequences are presented using the Oracle RDBMS

COUNT

SELECT COUNT(DISTINCT V_CODE)
FROM PRODUCT;

SELECT COUNT(DISTINCT V_CODE)
FROM PRODUCT
WHERE P_PRICE <= 10.00;

SELECT COUNT(*)
FROM PRODUCT
WHERE P_PRICE <= 10.00;

OK: COUNT(P_PRICE+10)

COUNT always returns the number of non-null values in the given column. (Whether the column values are computed or show stored table row values is immaterial.)

the syntax COUNT(*) returns the number of total rows returned by the query, including the rows that contain nulls.

The COUNT(*) aggregate function is used to count rows in a query result set

The COUNT(column) aggregate function counts the number of non-null values in a given column

MAX and MIN

Which product has the highest price?

SELECT P_CODE, P_DESCRIPT, P_PRICE 
FROM PRODUCT
WHERE P_PRICE = MAX(P_PRICE);

INCORRECT!!!!

MAX(columnname) can be used only in the column list of a SELECT statement. Also, in a comparison that uses an equality symbol, you can use only a single value to the right of the equals sign.

SELECT P_CODE, P_DESCRIPT, P_PRICE
FROM   PRODUCT
WHERE  P_PRICE = (SELECT MAX(P_PRICE) FROM PRODUCT);

the nested query is composed of two parts:

The inner query, which is executed first.
The outer query, which is executed last. (the outer query is always the first SQL command you encounter—in this case, SELECT.)

find out the product that has the oldest date: MIN(P_INDATE).

find out the most recent product: MAX(P_INDATE).

SELECT   * 
FROM     PRODUCT
WHERE    P_QOH*P_PRICE = (SELECT MAX(P_QOH*P_PRICE) FROM PRODUCT);

SUM

SELECT SUM(CUS_BALANCE) AS TOTBALANCE
FROM   CUSTOMER;

SELECT SUM(P_QOH * P_PRICE) AS TOTVALUE FROM PRODUCT;

AVG

<span style="font-weight: normal;">SELECT AVG(P_PRICE) FROM PRODUCT;</span>

<span style="font-weight: normal;">SELECT P_CODE, P_DESCRIPT, P_QOH, P_PRICE, V_CODE
FROM PRODUCT
WHERE P_PRICE > (SELECT AVG(P_PRICE) FROM PRODUCT)
ORDER BY P_PRICE DESC;</span>

the query uses nested SQL commands and the ORDER BY clause examined earlier.

Grouping Data

The GROUP BY clause is valid only when used in conjunction with one of the SQL aggregate functions, such as COUNT, MIN, MAX, AVG, and SUM.

“How many products are supplied by each vendor?”

<span style="font-weight: normal;"><span style="font-weight: normal;"></span><pre name="code" class="sql"><pre name="code" class="sql">SELECT   V_CODE, P_CODE, P_DESCRIPT, P_PRICE
FROM     PRODUCT
GROUP BY V_CODE;
</span>

<span style="font-weight: normal;">SELECT V_CODE, COUNT(DISTINCT (P_CODE))
FROM PRODUCT
GROUP BY V_CODE;</span>

It will generate a “not a GROUP BY expression” error!

<span style="font-weight: normal;">SELECT   P_SALECODE, MIN(P_PRICE)
FROM     PRODUCT
GROUP BY V_CODE;</span>

<span style="font-weight: normal;">SELECT   P_SALECODE, AVG(P_PRICE)
FROM     PRODUCT
GROUP BY V_CODE;</span>

The GROUP BY Feature's HAVING Clause

Extension of GROUP BY feature
Applied to output of GROUP BY operation
Used in conjunction with GROUP BY clause in second SQL command set
Similar to WHERE clause in SELECT statement

SELECT V_CODE, COUNT(DISTINCT (PCODE)), AVG(P_PRICE)
FROM PRODUCT
GROUP BY V_CODE
HAVING AVG(P_PRICE) < 10;

If you use the WHERE clause instead of the HAVING clause - produce an error message.

<span style="font-weight: normal;">SELECT   V_CODE, SUM(P_QOH * P_PRICE) AS TOTCOST
FROM     PRODUCT
GROUP BY V_CODE
HAVING   (SUM(P_QOH*P_PRICE)>500)
ORDER BY SUM(P_QOH*P_PRICE) DESC;</span>