[关闭]
@Sarah 2015-12-01T04:50:20.000000Z 字数 9101 阅读 1018

Chapter 7 SQL Fundamentals

SQL

The basic commands and functions of SQL
SQL for Data Declaration(DDL)
SQL for Data Management and Retrieval (DML)
Anatomy of an SQL Statement

一、Data definition language(DDL)

physical design+ maintenance

Commands that define a database, including creating, altering, and dropping tables and establishing constraints
Define schema objects, primarily table structures and indexes.

◦ CREATE
◦ ALTER
◦ DROP

1.Create

  1. //Syntax
  2. CREATE TABLE tablename (
  3. column name1  datatype   constraint, 
  4. column name 2 datatype    constraint
  5. );

SQL Constraints

  • NOT NULL constraint
    • Ensures that column does not accept nulls
    • UNIQUE constraint
    • Ensures that all values in column are unique
    • DEFAULT constraint
    • Assigns value to attribute when a new row is added to table
    • CHECK constraint
    • Validates data when attribute value is entered

2.Alter

  1. //ALTER TABLE Syntax:
  3. ALTER TABLE tablename ADD PRIMARY KEY(fieldname);
  5. //For foreign key, use FOREIGN KEY in place of PRIMARY KEY
  1. //Syntax:
  3. ALTER TABLE tablename 
  4. ADD|MODIFY|RENAME COLUMN| DROP columnnames;
  6. //Three options:
  7. ADD      //adds a column
  8. MODIFY  //changes column characteristics
  9. DROP   //deletes a column
  12. //Can also be used to: 
  14. //Add table constraints 
  15. ALTER TABLE tablename ADD PRIMARY KEY(fieldname);
  18. //Remove table constraints 
  19. ALTER TABLE emp DROP CONSTRAINT ck_bonus2;

3.Drop

  1. //Syntax:
  2. DROP TABLE tablename;

二、Data manipulation language(DML)

implementation

Commands that maintain and query a database
Manipulate schema objects, primarily table rows.

◦ INSERT
◦ SELECT
◦ COMMIT
◦ UPDATE
◦ ROLLBACK
◦ DELETE

1.SELECT

Displaying table information
most powerful and complex statement
Nearly any question regarding who, what, when, what if and how may can be answered with SELECT.
Once you understand the SELECT statement you can apply it to UPDATE, INSERT, and DELETE statements.
SELECT statement is composed of several distinct keywords known as clauses. Each clause has one or more keywords.
There is no limit on the number of columns you can specify in the SELECT clause. 

  1. //Viewing a User’s Table Names
  2. SELECT table_name FROM user_tables;
  3. SELECT * FROM user_tables;
  5. //View constraint information
  6. SELECT * FROM user_constraints
  7. SELECT constraint_name, constraint_type, table_name FROM USER_CONSTRAINTS
  9. //Viewing Tablespace information
  10. DESCRIBE user_tablespaces
  11. SELECT * FROM USER_TABLESPACES
  13. //Used to list contents of table
  14. SELECT columnlist
  15. FROM tablename;
  16. //Columnlist represents one or more attributes, separated by commas
  18. //Asterisk (*) can be used as wildcard character to list all columns

Fine-tune SELECT command by adding restrictions to search criteria using:

◦ Conditional restrictions
◦ Arithmetic operators
◦ Logical operators
◦ Special operators

Selecting Rows with Conditional Restrictions
Select partial table contents by placing restrictions on rows to be included in output

  1. //Add conditional restrictions to SELECT statement, using WHERE clause
  2. SELECT columnlist
  3. FROM tablelist
  4. [ WHERE conditionlist ] ;

Arithmetic Operators: The Rule of Precedence

Perform operations within parentheses
Perform power operations
Perform multiplications and divisions
Perform additions and subtractions

Additional SELECT Query Keywords

Logical operators work well in the query environment
SQL provides useful functions that:

Count

Find minimum and maximum values
Calculate averages, etc.

SQL allows user to limit queries to:
◦ Entries having no duplicates
◦ Entries whose duplicates may be grouped

2.Saving Table Changes

Changes made to table contents are not physically saved on disk until:

◦ Database is closed
◦ Program is closed
◦ COMMIT command is used

  1. //Syntax
  2. COMMIT [WORK];
  4. //Will permanently save any changes made to any table in the database

3.INSERT

◦ Used to enter data into table
◦ 

  1. //Syntax: 
  2. INSERT INTO columnname
  3. VALUES (value1, value2, … , valueN);

When entering values, notice that:

◦ Row contents are entered between parentheses
◦ Character and date values are entered between apostrophes
◦ Numerical entries are not enclosed in apostrophes
◦ Attribute entries are separated by commas
◦ A value is required for each column
Use NULL for unknown values

4.UPDATE

Modify data in a table

  1. //Syntax:
  3. UPDATE tablename
  4. SET columnname = expression [, columnname = expression]
  5. [WHERE conditionlist];

5.Indexes

6.ROLLBACK

Undoes changes since last COMMIT
Brings data back to prechange values

COMMIT and ROLLBACK only work with commands to add, modify, or delete table rows

  1. //Syntax:
  2. ROLLBACK;

7.DELETE

Deletes a table row

  1. //Syntax:
  2. DELETE FROM tablename
  3. [WHERE conditionlist ];
  4. //WHERE condition is optional

8.Logical Operators: AND, OR, and NOT

9.Special Operators

  • BETWEEN: checks whether attribute value is within a range

  • IS NULL: checks whether attribute value is null

  • LIKE: checks whether attribute value matches given string pattern

  • IN: checks whether attribute value matches any value within a value list

  • EXISTS: checks if subquery returns any rows

10.Ordering a Listing

ORDER BY clause is useful when listing order is important

  1. //Syntax:
  2.     SELECT columnlist
  3.     FROM tablelist
  4.     [WHERE conditionlist]
  5.     [ORDER BY columnlist [ASC | DESC]];

Ascending order by default

11.Listing Unique Values

DISTINCT clause produces list of only values that are different from one another

  1. //Example:
  2. SELECT DISTINCT V_CODE
  3. FROM    PRODUCT;
  • Access places nulls at the top of the list
  • Oracle places it at the bottom
  • Placement of nulls does not affect list contents
    Aggregate Functions

12.function

13.Grouping Data

Frequency distributions created by GROUP BY clause within SELECT statement [在where后面 在order by 前面]

  1. //Syntax:
  2. SELECT  columnlist
  3. FROM        tablelist
  4. [WHERE  conditionlist]
  5. [GROUP BY   columnlist]
  6. [HAVING conditionlist]
  7. [ORDER BY   columnlist [ASC | DESC] ] ;

14.Joining Database Tables

Joining Tables with an Alias

Recursive Joins

Alias is especially useful when a table must be joined to itself

Recursive query

Use aliases to differentiate the table from itself

SELECT Clauses

1.SELECT(required)
◦ List the columns (and expressions) that should be returned from the query
2.FROM(required)
◦ Indicate the table(s) or view(s) from which data will be obtained
** 3.WHERE**
◦ Indicate the conditions under which a row will be included in the result
4.GROUP BY
◦ Used with aggregate functions to summarize data
5.HAVING
◦ Filters the result of aggregate functions in grouped information, similar to WHERE
6. ORDER BY
◦ Sorts the result according to specified criteria

三、Data Control Language (DCL)

implementation+ maintenance

Commands that control a database, including administering privileges and committing data

SQL Environment

Terminology

Relation = table
Tuple or record = row
Attribute or field = column

Data Types

Data type selection is usually dictated by nature of data and by intended use
Supported data types:

◦ Number(L,D), Integer, Smallint, Decimal(L,D)
◦ Char(L), Varchar(L), Varchar2(L)
◦ Date, Time, Timestamp
◦ Real, Double, Float
◦ Interval day to hour
◦ Many other types
◦ RDBMS specific

有用的ppt
此处输入图片的描述

此处输入图片的描述
此处输入图片的描述
此处输入图片的描述
此处输入图片的描述
此处输入图片的描述

添加新批注
在作者公开此批注前，只有你和作者可见。
回复批注