SQL Summary

[QuXiangjie]

SQL Select

Select
From
Where
Group by
Having
Order by

Table Operations

Table Creation

Attribution name ,Type name ,(Not Null),(Primary Key)

CREATE TABLE Employee
(
EmpID Integer Not Null primary key,
Last_Name Varchar(15),
Address Varchar(30)
)

Table Changing and Removing Tables

ALTER TABLE table_name
ADD column_name datatype;

--以下是例子
ALTER TABLE Customers
ADD Email varchar(255);

--------------------------

ALTER TABLE table_name
DROP COLUMN column_name;

--以下是例子
ALTER TABLE Customers
DROP COLUMN Email;

--------------------------

--Name changes
ALTER TABLE table_name
RENAME COLUMN old_name to new_name;

-- Data type changes
ALTER TABLE table_name
ALTER COLUMN column_name datatype;  (SQL Server / MS Access)

DROP TABLE table_name;

Data row operation

Delete Statement

--Delete certain rows

Delete from tablename 
where state='HI'

--Delete all rows
Delete from customer_T

Update Statement

Update Tablename
Set PositionNumber=44
where SID=400;

Select * from part
Update part
Set Price=24.95
where aprt_num='AT94'

Insert Statement

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

If you are adding all the colunmns, you can use this statement

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

SQL Comparison Operatiors

Operator is used in a WHERE clause to search for a specified pattern in a column.

Like

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

Others

Build in Function

Join

Subquery

The operator must be a standard comparison operator (=,<>,!=,>,>=,< or <=)

Select column name
from table name
where column name operator ANY
  (Select....)

Transaction Intergrity

Functions: Routines that return values and take input parameters Procedures: Routines that do not return values and can take input or output parmeters. Triggers: Routines that execute in response to a database event(Insert, update or delete)

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;