SQL Syntax - more about SQL

1. SQL SELECT INTO Statement

-- copy one table into a new table
SELECT *
INTO newtable [IN externaldb]
FROM table1

-- copy only columns into the new table
SELECT colName(s)
INTO newtable [IN externabdb]
FROM table1;

2. SQL INSERT INTO SELECT statement

-- copy data from one table and insert itinto an existing table
INSERT INTO table2
SELECT * FROM table1;
 
INSERT INTO table2 (colnames)
SELECT colnames

3. SQL CREATE DATABASE statement

-- Create a database
CREATE DATABASE dbname;

4. SQL CREATE TABLE statement

--Create a table in a database
CREATE TABLE table_name
(
colname1 data_type(size),
colname2 data_type(size),
....
);

5. SQL Constraints

--SQL constraint Syntax
CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
....
);

Constraints are followings:

 

NOT NULL - Indicates that a column cannot store NULL value
UNIQUE - Ensures that each row for a column must have a unique value
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly
FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table
CHECK - Ensures that the value in a column meets a specific condition
DEFAULT - Specifies a default value when specified none for this column

6. SQL CREATE INDEX Statement

Index: created in a table to search/query data more quickly and efficiently.  

CREATE INDEX (UNIQUE) index_name
ON table_name (colname)

7. DROP Statement

--DROP INDEX statement is used to delete an index in a table. 
--DROP index for SQL SERVER
DROP INDEX table_name.index_name
--DROP index for MySQL
ALTER TABLE table_name DROP INDEX index_name

-- DROP databaseDROP DATABASE database_name;

--DROP TABLE Statement
DROP TABLE table_name

-- only delete the data inside the table
TRUNCATE TABLE table_name

8. SQL ALTER TABLE Statement

--used to add, delete, or modify columns in an existing table.

--add column in a table
ALTER TABLE table_name
ADD column_name datatype

--delete a column
ALTER TABLE table_name
DROP COLUMN column_name

9. SQL AUTO INCREMENT Field

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