Basic Concept

• SQL is NOT case sensitive: select is the same as SELECT
• Some database systems require a ; at the end of each SQL statement.
  • Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.
• A database index helps speed up the retrieval of data from tables. When you query data from a table, MySQL checks if the indexes exist. Then MySQL uses the indexes to select exact physical corresponding rows of the table instead of scanning the whole table.

SQL PRIMARY KEY Constraint

• The PRIMARY KEY constraint uniquely identifies each record in a database table.
• Primary keys must contain UNIQUE values.
• A primary key column cannot contain NULL values. Most tables should have a primary key, and each table can have only ONE primary key.

Common Commands

• SELECT - extracts data from a database
  • SELECT column_name,column_name FROM table_name;
  • SELECT * FROM table_name;
• UPDATE - updates data in a database
• DELETE - deletes data from a database
• INSERT INTO - inserts new data into a database
• CREATE DATABASE - creates a new database
• ALTER DATABASE - modifies a database
• CREATE TABLE - creates a new table
• ALTER TABLE - modifies a table
• DROP TABLE - deletes a table
• CREATE INDEX - creates an index (search key)
• DROP INDEX - deletes an index

SELECT DISTINCT column_name,column_name FROM table_name;

• The SELECT DISTINCT statement is used to return only distinct (different) values.

SELECT column_name,column_name FROM table_name WHERE column_name operator value;

• The WHERE clause is used to extract only those records that fulfill a specified criterion.

• Operators:

  |Operators|Description| |---|---| |= |Equal| |<> |Not equal. Note: In some versions of SQL this operator may be written as !=| |> |Greater than| |< |Less than| |>= |Greater than or equal| |<= |Less than or equal| |BETWEEN| Between an inclusive range| |LIKE |Search for a pattern| |IN |To specify multiple possible values for a column| |OR|Logic or| |AND|Logic and|

SELECT column_name, column_name FROM table_name ORDER BY column_name ASC|DESC, column_name ASC|DESC;

• The ORDER BY keyword is used to sort the result-set.

INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);

• The INSERT INTO statement is used to insert new records in a table.

UPDATE table_name SET column1=value1,column2=value2,... WHERE some_column=some_value;

• The UPDATE statement is used to update records in a table.
• The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!

DELETE FROM table_name WHERE some_column=some_value;

• The DELETE statement is used to delete rows in a table.
• The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!

SELECT TOP number|percent column_name(s) FROM table_name;

• The SELECT TOP clause is used to specify the number of records to return.

Aggregate Functions

The aggregate functions allow you to perform calculations on a set of records and return a single value.

• Aggregate functions are often used with the MySQL GROUP BY keyword to perform calculations on each subgroup and return a single value for each subgroup.

• The MySQL GROUP BY keyword is used with the SELECT statement to group rows into subgroups by one or more columns or expressions. It is extremely useful when several records belong to a category and other records in the same table belong to another category and you want to compare between different categories rather than a single record.

• SQL aggregate functions return a single value, calculated from values in a column.

  • AVG() - Returns the average value
  • COUNT() - Returns the number of rows
  • FIRST() - Returns the first value
  • LAST() - Returns the last value
  • MAX() - Returns the largest value
  • MIN() - Returns the smallest value
  • SUM() - Returns the sum

SQL Scalar functions

• SQL scalar functions return a single value, based on the input value.
  • UCASE() - Converts a field to upper case
  • LCASE() - Converts a field to lower case
  • MID() - Extract characters from a text field
  • LEN() - Returns the length of a text field
  • ROUND() - Rounds a numeric field to the number of decimals specified
  • NOW() - Returns the current system date and time
  • FORMAT() - Formats how a field is to be displayed