SQL Learning Notes

• Select column from table where rownum

• Insert into table column values
• Update table set column where
• Delete from table
  
  
• Select DISTINCT column from table
• Select column from table ORDER BY column Desc
• And And OR
• Like '%ab% '
• Not like '%df% '
  
• Select column from table where column in ()
• Select column from table where column between ... And ...
• Alias
  Select column as column alias from table as table alias
• Inner joins within join, returns rows when matched
• Left join returns all rows from the table even if there is no match in the right table
• Right Join
• Full Join
  
  

• Combine result sets (not duplicates) of two or more SELECT statements
  Select E_name from Employees_china UNION select E_name from Employees_usa

• Merging two or more select result Sets
  

  Select E_name from Employees_china UNION ALL select E_name from Employees_usa

• Select INTO new table from old table often used for backup copies of tables
  
  
  
  
  
• Create DATABASE name
• Create indicates (column 1 type, column 2 type 、、、)
  
• Constraints
  
• Unique and PRIMARY key
  Each table can have multiple unique constraints, but only one primary KEY constraint per table
  
  
  

• FOREIGN key FOREIGN KEY constraint

• Check only allows a specific value
• Default Insert defaults to columns
• Index indexes
  
  
  
• Drop
• Alter
  
  
• Auto Increment self-increment
• View
• Is null and is not NULL
  
  
  
  SQL functions
  
  
• AVG (column name) to find the average of a column
• Count (column name) specifies the number of columns
• First (column) returns the value of the number one record for the specified column
• Last (column)
• Max ()
• Min ()
• SUM () returns the total value of a numeric column
• GROUP by combines aggregate functions to group result sets based on one or more columns
• Having
• UCase () field converted to uppercase
• LCase ()
• Extract characters in mid (column, start position, length) text field
• Len () returns the length of the text field
• Round (column, number of digits) rounds a numeric field to a specified number of decimal digits
• Now () returns the current date and time
• Format (field, required format) formats the display of the specified field

SQL Learning Notes