SQL Review (W3school) Notes

SQL Basics

1.SQL not sensitive to case

2.DML(Data Manipulation language) and DDL(data definition Language)

A) DML

1. SELECT
2. UPDATE
3. DELETE
4. INSERT into

b) DDL

1. CREATE DATABASE
2. ALTER DATABASE
3. CREATE TABLE
4. ALTER TABLE
5. DROP TABLE
6. CREATE INDEX
7. DROP INDEX

3.DISTINCT: Remove Duplicate values

A) SELECT DISTINCT column name from table name

4.WHERE clause

A) notable operator between, like

b) Text value using single quotation marks, value not

5.ORDER by sentence

A) desc descending

b) ASC Ascending

6.INSERT into statement

A) syntax

1. INSERT into table_name VALUES (VAL1, val2 ...)
2. INSERT into table_name (column1, Column2 ...) VALUES (Val1, val2 ...)

7.UPDATE Statements

A) syntax: UPDATE table name set column name = new value WHERE column name = a value

8.DELETE Statements

A) syntax:DELETE from table name WHERE column name = value

b) Delete all rows delete from table_name

SQL Advanced

1.TOP sentence (MySQL is Limit)

A) returns the number of entries for the record (useful for tables with large amounts of data)

1. SELECT TOP number|percent column_name (s) from table_name
2. SELECT column_name (s) from table_name LIMIT number

2. Wildcard characters

A)% replaces one or more characters

b) _ instead of one character

c) any single character of the [charlist] Word columns

d) [^charlist] or [!charlist] Any single character not in Word columns

3.IN operator

A) allows us to Specify multiple values in the WHERE clause

b) SELECT column_name (s) from table_name WHERE column_name in (Val1, val2 ...)

4.BETWEEN operator

A) Select a range of data between two values

5.Alias ( alias )

A) Table alias SELECT column_name (s) from table_name as Alias_name

b) Column alias SELECT column_names as alias_name from table_name

6.JOIN

A) to query data from these tables based on the relationship between the columns in two or more tables

b) Several SQL joins

1. JOIN: At least one match in the table, the row is returned
2. Left JOIN: Returns all rows from the table, even if there is no match in the right table
3. Right JOIN: Returns all rows from the correct table even if there is no match in the left table
4. Full JOIN: Returns a row if there is a match in one of the tables

7.INNER Join ( same as join )

A) SELECT column_name (s) from table_name1 INNER JOIN table_name2 on table_name1.column_name = Table_name2.column_name

8.LEFT JOIN

A) SELECT column_name (s) from table_name1 left JOIN table_name2 on table_name1.column_name = Table_name2.column_name

b) All rows of the left table are listed

9.RIGHT JOIN

A) SELECT column_name (s) from table_name1 right JOIN table_name2 on table_name1.column_name = Table_name2.column_name

b) All rows of the right table are listed

10.FULL JOIN

A) SELECT column_name (s) from table_name1 full JOIN table_name2 on table_name1.column_name = Table_name2.column_name

b) A row is returned as long as there is a match

11.UNION operator

A) a result set used to merge two or more SELECT statements (must have the same number of columns, the columns must have similar data types, and the order of the columns must be the same)

b) SELECT column_name (s) from table_name1

UNION

SELECT column_name (s) from table_name2

c) UNION All lists duplicate values

12.SELECT into

A) Select the data from one table and insert it into another table

b) often used to create backup copies of tables or to archive records

c) SELECT * into new_table_name [in externaldatabase] from Old_tablename

13.CREATE DATABASE database_name

14.CREATE TABLE

A) CREATE table table name

(

Column name 1 data type,

Column name 2 data type,

......

)

15. Constraint Constraints

A) NOT NULL does not accept null values

b) Unique (can have multiple unique constraints)

c) PRIMARY key ( primary key ) has only one

d) FOREIGN KEY

1. FOREIGN key in one table points to PRIMARY key in another table
2. Actions to prevent the connection between broken tables
3. Prevents illegal data from being inserted into the foreign key column, because it must be one of the values in the table it only wants

e) CHECK to limit the range of merit in the column

f) default is used to insert defaults into columns

16.CREATE index create indexes in table

A) Index: Enables database applications to find data faster without reading the entire table

b) Updating tables with indexes is more time-consuming, so it is reasonable to silently create indexes on columns (and tables) that are often searched

c) CREATE INDEX index_name on table_name (column_name)

d) Unique index (two columns cannot have the same index value)CREATE unique index index_name on table_name (column_name)

17.DROP Deleting indexes, tables, and databases

18.TRUNCATE table Delete only table contents do not delete tables

19.AUTO INCREMENT

20.VIEW ( view ) a table of visualizations based on the result set of a SQL statement

21.Date function

A) MySQL Date function

1. Now ()   return current date and time
2. curdate ()   Return to current time
3. DATE ()   lift date or date / date part of the time expression
4. EXTRACT ()   return date /
5. Date_add ()   add a specified time interval to a date
6. date_sub ()   Subtract a specified time interval from a date
7. DATEDIFF ()   Returns the number of days between two dates
8. date_format ()   display dates in different formats /

b) SQL Server Date function

1. GETDATE () returns the current date and time
2. DATEPART () returns a separate part of the date / time
3. DATEADD () Adds or subtracts a specified time interval from a date
4. DATEDIFF () returns a time between two dates
5. CONVERT () Displays the date / time in a different format

c) MySql Date data type

1. DATE YYYY-MM-DD
2. DATETIME YYYY-MM-DD HH:MM:SS
3. TIMESTAMP YYYY-MM-DD HH:MM:SS
4. Year Yyyy/yy
5. SQL Server Date data type
6. DATE YYYY-MM-DD
7. DATETIME YYYY-MM-DD HH:MM:SS
8. smalldatetime YYYY-MM-DD HH:MM:SS
9. TIMESTAMP the only number

D) If you want to make the query simple and easier to maintain, do not use the time part in the date!

22.NULL Value

A) test NULL value

1. Is NULL
2. is not NULL

23.NULL function

A) MySQL with ifnull and coalesce functions

24. Data type http://www.w3school.com.cn/sql/sql_datatypes.asp

25.SQL server -rdbms

SQL Review (W3school) Notes