一些重要的SQL命令

• SELECT - extracts data from a database
• 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

The SQL SELECT Statement

The SELECT statement is used to select data from a database.

SELECT Syntax

SELECT column1, column2, …
FROM table_name;

SELECT FROM table_name*;

The SQL SELECT DISTINCT Statement

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

SELECT DISTINCT column1, column2, …
FROM table_name;

The SQL WHERE Clause

The WHERE clause is used to filter records.

WHERE Syntax

SELECT column1, column2, …
FROM table_name
WHERE condition;

The SQL AND, OR and NOT Operators

The WHERE clause can be combined with AND, OR, and NOT operators.

AND Syntax

SELECT column1, column2, …
FROM table_name
WHERE condition1 AND condition2 AND condition3 …;

OR Syntax

SELECT column1, column2, …
FROM table_name
WHERE condition1 OR condition2 OR condition3 …;

NOT Syntax

SELECT column1, column2, …
FROM table_name
WHERE NOT condition;

The SQL ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

ORDER BY Syntax

SELECT column1, column2, …
FROM table_name
ORDER BY column1, column2, … ASC|DESC;

The SQL INSERT INTO Statement

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

INSERT INTO Syntax

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

or:

INSERT INTO table_name
VALUES (value1, value2, value3, …);

NULL Value

A field with a NULL value is a field with no value.

How to Test for NULL Values?

We will have to use the IS NULL and IS NOT NULL operators instead.

IS NULL Syntax

SELECT column_names FROM table_name
WHERE column_name IS NULL;

IS NOT NULL Syntax

SELECT column_names FROM table_name
WHERE column_name IS NOT NULL;

The SQL UPDATE Statement

The UPDATE statement is used to modify the existing records in a table.

UPDATE Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;

The SQL DELETE Statement

The DELETE statement is used to delete existing records in a table.

DELETE Syntax

DELETE FROM table_name
WHERE condition;

The SQL SELECT TOP Clause

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

MySQL Syntax:

SELECT column_name(s)
FROM table_name WHERE condition
LIMIT number;

SQL Server / MS Access Syntax:

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

he SQL MIN() and MAX() Functions

The MIN() function returns the smallest value of the selected column.

The MAX() function returns the largest value of the selected column.

MIN() Syntax

SELECT MIN(column_name)
FROM table_name
WHERE condition;

MAX() Syntax

SELECT MAX(column_name)
FROM table_name
WHERE condition;

The SQL COUNT(), AVG() and SUM() Functions

The COUNT() function returns the number of rows that matches a specified criteria.

The AVG() function returns the average value of a numeric column.

The SUM() function returns the total sum of a numeric column.

COUNT() Syntax

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

AVG() Syntax

SELECT AVG(column_name)
FROM table_name
WHERE condition;

SUM() Syntax

SELECT SUM(column_name)
FROM table_name
WHERE condition;

The SQL LIKE Operator

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards used in conjunction with the LIKE operator:

• % - The percent sign represents zero, one, or multiple characters
• _ - The underscore represents a single character

LIKE Syntax

SELECT column1, column2, …
FROM table_name
WHERE columnN LIKE pattern;

SQL LIKE Examples

The following SQL statement selects all customers with a CustomerName starting with “a”:

Example

SELECT * FROM Customers
WHERE CustomerName LIKE ‘a%’;

The following SQL statement selects all customers with a CustomerName ending with “a”:

Example

SELECT * FROM Customers
WHERE CustomerName LIKE ‘%a’;

The following SQL statement selects all customers with a CustomerName that have “or” in any position:

Example

SELECT * FROM Customers
WHERE CustomerName LIKE ‘%or%’;

The following SQL statement selects all customers with a CustomerName that have “r” in the second position:

Example

SELECT * FROM Customers
WHERE CustomerName LIKE ‘_r%’;

The following SQL statement selects all customers with a CustomerName that starts with “a” and are at least 3 characters in length:

Example

SELECT * FROM Customers
WHERE CustomerName LIKE ‘a_%_%’;

The following SQL statement selects all customers with a ContactName that starts with “a” and ends with “o”:

Example

SELECT * FROM Customers
WHERE ContactName LIKE ‘a%o’;

The following SQL statement selects all customers with a CustomerName that does NOT start with “a”:

Example

SELECT * FROM Customers
WHERE CustomerName NOT LIKE ‘a%’;

The SQL IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.

The IN operator is a shorthand for multiple OR conditions.

IN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, …);

or:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

The SQL BETWEEN Operator

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

BETWEEN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

SQL Aliases

SQL aliases are used to give a table, or a column in a table, a temporary name.

Alias Column Syntax

SELECT column_name AS alias_name
FROM table_name;

Alias Table Syntax

SELECT column_name(s)
FROM table_name AS alias_name;

SQL JOIN

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:

• (INNER) JOIN: Returns records that have matching values in both tables
• LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table
• RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table
• FULL (OUTER) JOIN: Return all records when there is a match in either left or right table

SQL INNER JOIN Keyword

The INNER JOIN keyword selects records that have matching values in both tables.

INNER JOIN Syntax

SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

SQL LEFT JOIN Keyword

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.

LEFT JOIN Syntax

SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

Note: In some databases LEFT JOIN is called LEFT OUTER JOIN.

SQL RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.

RIGHT JOIN Syntax

SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

Note: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

SQL FULL OUTER JOIN Keyword

The FULL OUTER JOIN keyword return all records when there is a match in either left (table1) or right (table2) table records.

Note: FULL OUTER JOIN can potentially return very large result-sets!

FULL OUTER JOIN Syntax

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

SQL Self JOIN

A self JOIN is a regular join, but the table is joined with itself.

Self JOIN Syntax

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

The SQL UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT statements.

• Each SELECT statement within UNION must have the same number of columns
• The columns must also have similar data types
• The columns in each SELECT statement must also be in the same order

UNION Syntax

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

UNION ALL Syntax

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

The SQL GROUP BY Statement

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

GROUP BY Syntax

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)ORDER BY column_name(s);

The SQL HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

HAVING Syntax

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)HAVING conditionORDER BY column_name(s);

The SQL EXISTS Operator

The EXISTS operator is used to test for the existence of any record in a subquery.

EXISTS Syntax

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

The SQL ANY and ALL Operators

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.

ANY Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name FROM table_name WHERE condition);

ALL Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name FROM table_name WHERE condition);

The SQL SELECT INTO Statement

The SELECT INTO statement copies data from one table into a new table.

SELECT INTO Syntax

Copy all columns into a new table:

SELECT
INTO newtable [IN externaldb]
FROM oldtableWHERE condition*;

Copy only some columns into a new table:

SELECT column1, column2, column3, …
INTO newtable [IN externaldb]
FROM oldtableWHERE condition;

The SQL INSERT INTO SELECT Statement

The INSERT INTO SELECT statement 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 SELECT Syntax

Copy all columns from one table to another table:

INSERT INTO table2
SELECT FROM table1WHERE condition*;

Copy only some columns from one table into another table:

INSERT INTO table2 (column1, column2, column3, …)
SELECT column1, column2, column3, …
FROM table1
WHERE condition;

INSERT INTO SELECT Syntax

Copy all columns from one table to another table:

INSERT INTO table2
SELECT FROM table1WHERE condition*;

Copy only some columns from one table into another table:

INSERT INTO table2 (column1, column2, column3, …)
SELECT column1, column2, column3, …
FROM table1
WHERE condition;

SQL Database

The SQL CREATE DATABASE Statement

The CREATE DATABASE statement is used to create a new SQL database.

Syntax

CREATE DATABASE databasename;

The SQL DROP DATABASE Statement

The DROP DATABASE statement is used to drop an existing SQL database.

Syntax

DROP DATABASE databasename;

The SQL CREATE TABLE Statement

The CREATE TABLE statement is used to create a new table in a database.

Syntax

CREATE TABLE table_name (

• column1 datatype*,
• column2 datatype*,
• column3 datatype*,
  ….
  );

The SQL DROP TABLE Statement

The DROP TABLE statement is used to drop an existing table in a database.

Syntax

DROP TABLE table_name;

SQL ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

ALTER TABLE - ADD Column

To add a column in a table, use the following syntax:

ALTER TABLE table_name
ADD column_name datatype;

ALTER TABLE - DROP COLUMN

To delete a column in a table, use the following syntax (notice that some database systems don’t allow deleting a column):

ALTER TABLE table_name
DROP COLUMN column_name;

ALTER TABLE - ALTER/MODIFY COLUMN

To change the data type of a column in a table, use the following syntax:

SQL Server / MS Access:

ALTER TABLE table_name
ALTER COLUMN column_name datatype;

My SQL / Oracle (prior version 10G):

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

Oracle 10G and later:

ALTER TABLE table_name
MODIFY column_name datatype;

SQL Constraints

SQL constraints are used to specify rules for data in a table.

SQL Create Constraints

Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER TABLE statement.

Syntax

CREATE TABLE table_name (

• column1 datatype constraint*,
• column2 datatype constraint*,
• column3 datatype constraint*,
  ​ ….
  );

SQL Constraints

SQL constraints are used to specify rules for the data in a table.

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.

Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.

The following constraints are commonly used in SQL:

• NOT NULL - Ensures that a column cannot have a NULL value
• UNIQUE - Ensures that all values in a column are different
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
• FOREIGN KEY - Uniquely identifies a row/record in another table
• CHECK - Ensures that all values in a column satisfies a specific condition
• DEFAULT - Sets a default value for a column when no value is specified
• INDEX - Used to create and retrieve data from the database very quickly

SQL NOT NULL Constraint

By default, a column can hold NULL values.

The NOT NULL constraint enforces a column to NOT accept NULL values.

Example

CREATE TABLE Persons (
​ ID int NOT NULL,
​ LastName varchar(255) NOT NULL,
​ FirstName varchar(255) NOT NULL,
​ Age int
);

SQL UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are different.

SQL UNIQUE Constraint on CREATE TABLE

The following SQL creates a UNIQUE constraint on the “ID” column when the “Persons” table is created:

SQL Server / Oracle / MS Access:

CREATE TABLE Persons (
​ ID int NOT NULL UNIQUE,
​ LastName varchar(255) NOT NULL,
​ FirstName varchar(255),
​ Age int
);

MySQL:

CREATE TABLE Persons (
​ ID int NOT NULL,
​ LastName varchar(255) NOT NULL,
​ FirstName varchar(255),
​ Age int,
​ UNIQUE (ID)
);

To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons (
​ ID int NOT NULL,
​ LastName varchar(255) NOT NULL,
​ FirstName varchar(255),
​ Age int,
​ CONSTRAINT UC_Person UNIQUE (ID,LastName)
);

To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);

DROP a UNIQUE Constraint

To drop a UNIQUE constraint, use the following SQL:

MySQL:

ALTER TABLE Persons
DROP INDEX UC_Person;

SQL Server / Oracle / MS Access:

ALTER TABLE Persons
DROP CONSTRAINT UC_Person;

SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table.

SQL PRIMARY KEY on CREATE TABLE

The following SQL creates a PRIMARY KEY on the “ID” column when the “Persons” table is created:

MySQL:

CREATE TABLE Persons (
​ ID int NOT NULL,
​ LastName varchar(255) NOT NULL,
​ FirstName varchar(255),
​ Age int,
​ PRIMARY KEY (ID)
);

SQL Server / Oracle / MS Access:

CREATE TABLE Persons (
​ ID int NOT NULL PRIMARY KEY,
​ LastName varchar(255) NOT NULL,
​ FirstName varchar(255),
​ Age int
);

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons (
​ ID int NOT NULL,
​ LastName varchar(255) NOT NULL,
​ FirstName varchar(255),
​ Age int,
​ CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);

SQL PRIMARY KEY on ALTER TABLE

To create a PRIMARY KEY constraint on the “ID” column when the table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD PRIMARY KEY (ID);

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);

DROP a PRIMARY KEY Constraint

To drop a PRIMARY KEY constraint, use the following SQL:

MySQL:

ALTER TABLE Persons
DROP PRIMARY KEY;

SQL Server / Oracle / MS Access:

ALTER TABLE Persons
DROP CONSTRAINT PK_Person;

SQL FOREIGN KEY Constraint

A FOREIGN KEY is a key used to link two tables together.

A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.

SQL FOREIGN KEY on CREATE TABLE

The following SQL creates a FOREIGN KEY on the “PersonID” column when the “Orders” table is created:

MySQL:

CREATE TABLE Orders (
​ OrderID int NOT NULL,
​ OrderNumber int NOT NULL,
​ PersonID int,
​ PRIMARY KEY (OrderID),
​ FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

SQL Server / Oracle / MS Access:

CREATE TABLE Orders (
​ OrderID int NOT NULL PRIMARY KEY,
​ OrderNumber int NOT NULL,
​ PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Orders (
​ OrderID int NOT NULL,
​ OrderNumber int NOT NULL,
​ PersonID int,
​ PRIMARY KEY (OrderID),
​ CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
​ REFERENCES Persons(PersonID)
);

SQL FOREIGN KEY on ALTER TABLE

To create a FOREIGN KEY constraint on the “PersonID” column when the “Orders” table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

DROP a FOREIGN KEY Constraint

To drop a FOREIGN KEY constraint, use the following SQL:

MySQL:

ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;

SQL Server / Oracle / MS Access:

ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;

SQL CHECK Constraint

The CHECK constraint is used to limit the value range that can be placed in a column.

SQL CHECK on CREATE TABLE

The following SQL creates a CHECK constraint on the “Age” column when the “Persons” table is created. The CHECK constraint ensures that you can not have any person below 18 years:

MySQL:

CREATE TABLE Persons (
​ ID int NOT NULL,
​ LastName varchar(255) NOT NULL,
​ FirstName varchar(255),
​ Age int,
​ CHECK (Age>=18)
);

SQL Server / Oracle / MS Access:

CREATE TABLE Persons (
​ ID int NOT NULL,
​ LastName varchar(255) NOT NULL,
​ FirstName varchar(255),
​ Age int CHECK (Age>=18)
);

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons (
​ ID int NOT NULL,
​ LastName varchar(255) NOT NULL,
​ FirstName varchar(255),
​ Age int,
​ City varchar(255),
​ CONSTRAINT CHK_Person CHECK (Age>=18 AND City=’Sandnes’)
);

SQL CHECK on ALTER TABLE

To create a CHECK constraint on the “Age” column when the table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD CHECK (Age>=18);

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City=’Sandnes’);

DROP a CHECK Constraint

To drop a CHECK constraint, use the following SQL:

SQL Server / Oracle / MS Access:

ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonAge;

MySQL:

ALTER TABLE Persons
DROP CHECK CHK_PersonAge;

SQL DEFAULT Constraint

The DEFAULT constraint is used to provide a default value for a column.

SQL DEFAULT on CREATE TABLE

The following SQL sets a DEFAULT value for the “City” column when the “Persons” table is created:

My SQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons (
​ ID int NOT NULL,
​ LastName varchar(255) NOT NULL,
​ FirstName varchar(255),
​ Age int,
​ City varchar(255) DEFAULT ‘Sandnes’
);

The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE():

CREATE TABLE Orders (
​ ID int NOT NULL,
​ OrderNumber int NOT NULL,
​ OrderDate date DEFAULT GETDATE()
);

SQL DEFAULT on ALTER TABLE

To create a DEFAULT constraint on the “City” column when the table is already created, use the following SQL:

MySQL:

ALTER TABLE Persons
ALTER City SET DEFAULT ‘Sandnes’;

SQL Server / MS Access:

ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT ‘Sandnes’;

Oracle:

ALTER TABLE Persons
MODIFY City DEFAULT ‘Sandnes’;

DROP a DEFAULT Constraint

To drop a DEFAULT constraint, use the following SQL:

MySQL:

ALTER TABLE Persons
ALTER City DROP DEFAULT;

SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;

SQL CREATE INDEX Statement

The CREATE INDEX statement is used to create indexes in tables.

Indexes are used to retrieve data from the database very fast. The users cannot see the indexes, they are just used to speed up searches/queries.

Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So, only create indexes on columns that will be frequently searched against.

CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name
ON table_name (column1, column2, …);

CREATE UNIQUE INDEX Syntax

Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, …);

CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name
ON table_name (column1, column2, …);

CREATE UNIQUE INDEX Syntax

Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, …);

DROP INDEX Statement

The DROP INDEX statement is used to delete an index in a table.

MS Access:

DROP INDEX index_name ON table_name;

SQL Server:

DROP INDEX table_name.index_name;

DB2/Oracle:

DROP INDEX index_name;

MySQL:

ALTER TABLE table_nameDROP INDEX index_name;

AUTO INCREMENT Field

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.

Syntax for MySQL

The following SQL statement defines the “ID” column to be an auto-increment primary key field in the “Persons” table:

CREATE TABLE Persons (
​ ID int NOT NULL AUTO_INCREMENT,
​ LastName varchar(255) NOT NULL,
​ FirstName varchar(255),
​ Age int,
​ PRIMARY KEY (ID)
);

MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature.

By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record.

To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement:

ALTER TABLE Persons AUTO_INCREMENT=100;

SQL Dates

SQL Date Data Types

MySQL comes with the following data types for storing a date or a date/time value in the database:

• DATE - format YYYY-MM-DD
• DATETIME - format: YYYY-MM-DD HH:MI:SS
• TIMESTAMP - format: YYYY-MM-DD HH:MI:SS
• YEAR - format YYYY or YY

SQL Operators

SQL Arithmetic Operators

SQL Bitwise Operators

SQL Comparison Operators

SQL Compound Operators

SQL Logical Operators

SQL Data Types for MySQL

Text data types:

Number data types:

*The integer types have an extra option called UNSIGNED. Normally, the integer goes from an negative to positive value. Adding the UNSIGNED attribute will move that range up so it starts at zero instead of a negative number.

Date data types: