SQL

SQL

Select

SELECT column1, column2, ...
FROM table_name;

Select Distinct

select unique uint

SELECT DISTINCT column1, column2, ...
FROM table_name;

SELECT DISTINCT Country FROM Customers;

Where

SELECT column1, column2, ...
FROM table_name
WHERE condition;

SELECT * FROM Customers
WHERE Country='Mexico';
 
SELECT * FROM Customers
WHERE CustomerID=1;

Operators

Operator	Description
=	Equal
>	Greeter than
<	Less than
>=	Gretter than or equal
<=	Less than or equal
<>(!+)	Not equal;Note: In some versions of SQL this operator may be written as !=
Between	Between a certain range
LIKE	Search for a pattern
In	To specify multiple possible values for a column

AND,OR,NOT

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
 
SELECT * FROM Customers
WHERE Country='Germany' AND City='Berlin';

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;
 
SELECT * FROM Customers
WHERE City='Berlin' OR City='München';

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;
 
SELECT * FROM Customers
WHERE NOT Country='Germany';

SELECT * FROM Customers
WHERE Country='Germany' AND (City='Berlin' OR City='München');

Order By

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC(顺序)|DESC(反序);

SELECT * FROM Customers
ORDER BY Country;
 
SELECT * FROM Customers
ORDER BY Country DESC;

orders by Country, but if some rows have the same Country, it orders them by CustomerName:
 
SELECT * FROM Customers
ORDER BY Country, CustomerName;

Insert Into

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

NULL

SELECT column_names
FROM table_name
WHERE column_name IS NULL;
 
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;
 
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NOT NULL;

Update

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

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
 
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

Delete

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

DELETE FROM table_name WHERE condition;

DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

Delete All Record

Delete from table_name;

SELECT TOP

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

mysql
 
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;
 
SELECT TOP 3 * FROM Customers;

Min,Max

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

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

select MIN(column_name)
FROM table_name
WHERE condition;
 
SELECT MAX(column_name)
FROM table_name
WHERE condition;

COUNT,AVG,SUM

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

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

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

SELECT AVG(column_name)
FROM table_name
WHERE condition;

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

SELECT SUM(column_name)
FROM table_name
WHERE condition;

LIKE

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

Operator	Description
a%	start with a
%a	end with a
%or%	have or
_r%	str(second)==r
a_%	start with "a" and are at least 2 characters in length
a__%	start with "a" and are at least 3 characters in length
a%o	start with a,end with o

SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';

IN

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

The IN operator is a shorthand for multiple OR conditions.

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

BETWEEN

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

The BETWEEN operator is inclusive: begin and end values are included.

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

Aliases

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

Aliases are often used to make column names more readable.

An alias only exists for the duration of that query.

An alias is created with the AS keyword.

SELECT column_name AS alias_name
FROM table_name;
 
SELECT column_name(s)
FROM table_name AS alias_name;

JOIN

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

INNER JOIN

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

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

Left JOIN

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

Note: The LEFT JOIN keyword returns all records from the left table (Customers), even if there are no matches in the right table (Orders).

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

Right Join

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

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

Full join

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

Tip: FULL OUTER JOIN and FULL JOIN are the same.

Union

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

SELECT column_name(s) FROM table_name1
UNION (忽略重复)
SELECT column_name(s) FROM table_name2
 
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

SELECT INTO

SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中。

SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档。

SELECT column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_tablename

CREATE DB

CREATE DATABASE database_name

CREATE TABLE

CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
)

Constraints

NOT NULL

约束强制列不接受NULL值

UNIQUE

约束唯一标识数据库表的数据

PRIMARY KEY

约束唯一标识数据库表中的每条记录。

FOREIGN KEY

外键约束

 
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
)
 
新增约束
ALTER TABLE Orders
ADD FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
 
删除约束
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

CHECK

约束用于限制列中的值的范围。

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (Id_P>0)
)
 
新增
ALTER TABLE Persons
ADD CHECK (Id_P>0)
 
 
删除
ALTER TABLE Persons
DROP CHECK chk_Person

DEFAULT

约束默认值

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)
 
更新
 
ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'
 
删除
 
ALTER TABLE Persons
ALTER City DROP DEFAULT

索引

用户无法看到索引，它们只能被用来加速搜索/查询。

注释：更新一个包含索引的表需要比更新一个没有索引的表更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

CREATE INDEX index_name
ON table_name (column_name (DESC),column_name2,)
 
删除索引
ALTER TABLE table_name DROP INDEX index_name

DROP

drop 可以用了删除索引，表，库

ALTER TABLE table_name DROP INDEX index_name
 
DROP TABLE table_name;
 
DROP DATABASE db_name;
 
TRUNCATE TABLE table_name; #只清空值

ALTER

拥有向已有的表中添加，修改，删除列

添加列
ALTER TABLE table_name 
ADD column_name datatype;
 
删除列
ALTER TABLE table_name
drop column_name
 
修改列数据类型
ALTER TABLE table_name
ALTER COLUMN column_name datatype

AUTO INCREMENT

自增字段，开始值为1

设置起始值
ALTER TABLE table_name AUTO_INCREMENT=100

View

在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。

视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。

注释：数据库的设计和结构不会受到视图中的函数、where 或 join 语句的影响。

视图总是显示最近的数据。每当用户查询视图时，数据库引擎通过使用 SQL 语句来重建数据。

新建
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
 
CREATE VIEW [Device List] AS
SELECT DeviceID,DeviceName
FROM Device
 
 
修改视图
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
 
删除视图
 
DROP VIEW view_name