SQL复习

What is SQL?
- structured quert language
- manipulate databases

What can SQL do?
- exexute queries, retrieve data, insert records, update records, delete records from database;
- create new databases, create new tables in a database, create stored procedures in a database, create views in a database;
- set permissions on tables, procedures, and views.

RDBMS
- relational database management system;
- RDBMS is the basis for SQL
- The data in RDBMS is stored in database objects called tables. 
- Fields: the columns in a table.
- Record: a horizontal entity in a table.

- KKKK Keep in mind:
   - SQL keywords are not case sensitive
   - Some database systems require a semicolon at the end of each SQL statement.

- Some of the most important SQL commands
   - select
   - update 
   - delete 
   - insert into
   - create database
   - alter table
   - create table
   - drop table
   - create index
   - drop index

- SELECT statement
- SELECT DISTINCT statement
- WHERE clause: filter records.
- AND, OR, NOT
- ORDER BY 
    -  ASC ascending order by default
    - DESC reperesents descending order
    - note that if wants a column asc and others desc, you can write ORDER BY country asc, customers desc.

- INSERT INTO
   - two ways
   - the first way specifies both the column names and the values to be inserted.
       - INSERT INTO table_name( column1, column2, ...)  VALUES (values1, values2, ...);
   - the second way, if you want to add values for  all the columns of the table, you do not need to specify the column names in the SQL query.
       - INSERT INTO table_name VALUES (values1, values2....);

- NULL values
   - IS NULL / IS NOT NULL
   - If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.
   - test for null values?
         - use IS NULL in WHERE caluse.
         -  select column_name from table_name where column_name IS NOT NULL;

- UPDATE statement
   - UPDATE table_name SET column1= value1, column2 = value2,... WHERE  condition;
   - multiple fields update, use ','.

- DELETE statement
   - DELETE FROM table_name WHERE conditions;
   - DELETE FROM table_name; (means delete all records)

- TOP, LIMIT or ROWNUM Caluse
    - SQL Server/ MS Access :
          - SELECT TOP number| percent column_name FROM table_name WHERE condition;

   - MySQL Syntax
           - SELECT column_name from table_name WHERE condition LIMIT number;

   - Oracle Syntax 
          - SELECT column_name From table_name WHERE ROWNUM <= number;

- MIN() AND MAX()
        - SELECT MIN(column_name) FROM table_name WHERE condition;
        - SELECT MAX(column_name) FROM table_name WHERE condition;

- COUNT(), AVG() AND SUM()
- LIKE Operator
   - two wildcards
       - % - The percent sign represents zero, one, or multiple characters
       - _ - The underscore represents a single character

- IN Operator
     - IN / NOT IN

- BETWEEN Operator
    - select column_name from table_name WHERE column_name NOT BETWEEN 10 AND 20.

- Aliases 
      -  only exists for the duration of the query.

-  JOIN
     - Different types of SQL JOINS
          - (inner) JOIN
          - LEFT (OUTER) JOIN
          - RIGHT (OUTER) JOIN
          - FULL (OUTER) JOINS
- UNION Operator
     - the Same number of columns
     - the columns in each select statement must also be in the same order
     - the columns must  also have similar data types
   - UNION returns only distinct values by default.
   - UNION ALL returns all values, including duplicate values.

- GROUP BY
   - is used with aggregate functions(COUNT, MAX, MIN, AVG) to group the result-set by one or more columns.

- HAVING Clause
    - The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
    - SELECT column_name FROM table_name WHERE condition GROUP BY column_name HAVING condition ORDER BY column_name;

- EXISTS Operator
    - The EXISTS operator is used to test for the existence of any record in a subquery.
    - The EXISTS operator returns true if the subquery returns one or more records.

- ANY and ALL Operator
    - The ANY and ALL  operators are used with a WHERE or HAVING clause.
    - The ANY operator returns  true if any of the subquery values meet the condition.
    -  The ALL operator returns true if all of the subquery values meet the condition.
       - SELECT column_name FROM table_name WHERE  column_name operator ANY (SELECT column_name FROM table_name WHERE condition);
       - SELECT column_name FROM table_name WHERE column_name operator ALL (SELECT column_name FROM table_name WHERE condition);

- SELECT INTO 
    - copies data from one table into a new table.
    - SELECT column1, column2,.. INTO newtable [IN externaldb] FROM  oldtable WHERE condition;

- INSERT INTO SELECT 
   - copies data from one table and inserts it into another table.
         - INSERT INTO SELECT requires that data types in source and target tables match .
         - The existing records in the target table are unaffected.
   - INSERT INTO table2 (column1, column2, ...) SELECT column1, column2, ... FROM table1 WHERE condition;
   - INSERT INTO table2 SELECT * FROM table1 WHERE condition;

- NULL functions
   - (mysql)IFNULL(),  COALESCE()(联合），(SQL server) ISNULL(), (Oracle)NVL()
   - eg.
       - SELECT ProductName, unitprice*(unitsinstock + IFNULL(unitsonorder, 0)) FROM Products;
       or 
       - SELECT ProductName, unitprice*(unitsinstock + COALESCE(unitsonorder, 0)) FROM Products;

-