DATABASE SQL

来源：互联网发布：mysql e 执行sql 编辑：程序博客网时间：2024/04/27 21:49

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 PRODUCTWHERE P_PRICE < 50AND P_INDATE > '15-Jan-2010';

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

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

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

SELECT *FROM PRODUCTWHERE P_PRICE BETWEEN 50.00 AND 100.00;

SELECT P_CODE, P_DESCRIPT, V_CODE FROM PRODUCTWHERE V_CODE IS NULL;

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

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

SELECT *FROM VENDORWHERE V_CONTACT LIKE 'Johns_n';

SELECT *FROM PRODUCTWHERE V_CODE IN (21344, 24288);

-- V_CODE = 21344 OR V_CODE = 24288;

SELECT V_CODE, V_NAMEFROM VENDORWHERE V_CODE IN (SELECT V_CODE FROM PRODUCT);

SELECT *FROM VENDORWHERE 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 PRODUCTMODIFY (V_CODE CHAR(5));

Changing a Column’s Data Characteristics

ALTER TABLE PRODUCTMODIFY (P_PRICE DECIMAL(9,2));

Adding a Column

ALTER TABLE PRODUCTADD (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 VENDORDROP 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 PRODUCTSET P_SALECODE = '2' WHERE P_CODE = '1546-QQ2';

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

UPDATE PRODUCTSET 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 PRODUCTSET 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 PRODUCTSET 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 PRODUCTSET P_QOH = P_QOH + 20 WHERE P_CODE = '2232/QWE';

<pre name="code" class="sql">UPDATE PRODUCTSET 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 ASSELECT P_CODE AS PART_CODE, P_DESCRIPT AS PART_DESCRIPT,       P_PRICE AS PART_PRICE, V_CODEFROM 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 PARTFROM 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_PRICEFROM PRODUCTORDER BY P_PRICE;

SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICEFROM PRODUCTORDER BY P_PRICE ASC;

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

SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICEFROM PRODUCTORDER BY P_PRICE DESC;

Cascading order sequence: Multilevel ordered sequence

SELECT   EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONEFROM     EMPLOYEE ORDER BY EMP_LNAME, EMP_FNAME, EMP_INITIAL;

SELECT    P_DESCRIPT, V_CODE, P_INDATE, P_PRICEFROM      PRODUCTWHERE     P_INDATE < '21-Jan-2010'AND       P_PRICE <= 50.00ORDER 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 PRODUCTWHERE P_PRICE <= 10.00;

SELECT COUNT(*)FROM PRODUCTWHERE 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 PRODUCTWHERE 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_PRICEFROM   PRODUCTWHERE  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     PRODUCTWHERE    P_QOH*P_PRICE = (SELECT MAX(P_QOH*P_PRICE) FROM PRODUCT);

SUM

SELECT SUM(CUS_BALANCE) AS TOTBALANCEFROM   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_CODEFROM PRODUCTWHERE 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_PRICEFROM     PRODUCTGROUP BY V_CODE;</span>

<span style="font-weight: normal;">SELECT V_CODE, COUNT(DISTINCT (P_CODE))FROM PRODUCTGROUP 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     PRODUCTGROUP BY V_CODE;</span>

<span style="font-weight: normal;">SELECT   P_SALECODE, AVG(P_PRICE)FROM     PRODUCTGROUP 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 PRODUCTGROUP BY V_CODEHAVING 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 TOTCOSTFROM     PRODUCTGROUP BY V_CODEHAVING   (SUM(P_QOH*P_PRICE)>500)ORDER BY SUM(P_QOH*P_PRICE) DESC;</span>

Aggregate the total cost of products grouped by V_CODE.
0 0

mysql e 执行sql

原创粉丝点击