SQL 基础操作

--************基础SQL**************------------------------------
--返回唯一不同的值
SELECT DISTINCT column1, column2 FROM table_name

--SQL ORDER BY 语法
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;


---SQL UPDATE 语法
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

--SQL DELETE 语句
DELETE FROM table_name
WHERE condition;

--SQL Server 
SELECT TOP number|percent column_name
FROM table_name;

--MySQL 语法
SELECT column_name(s)
FROM table_name
LIMIT number;

--Oracle 语法
SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

--使用 SQL % 通配符，替代 0 个或多个字符
SELECT * FROM Websites
WHERE url LIKE 'https%';

--使用 SQL _ 通配符。替代一个字符
SELECT * FROM Websites
WHERE name LIKE '_oogle';

--使用 SQL [charlist] 通配符
--MySQL 中使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式
--选取 name 以 "G"、"F" 或 "s" 开始的所有网站：
SELECT * FROM Websites
WHERE name REGEXP '^[GFs]';

--选取 name 以 A 到 H 字母开头的网站
SELECT * FROM Websites
WHERE name REGEXP '^[A-H]';

--选取 name 不以 A 到 H 字母开头的网站：
SELECT * FROM Websites
WHERE name REGEXP '^[^A-H]';

--SQL BETWEEN 操作符
SELECT column1, column2, ...
FROM table_name
WHERE column BETWEEN value1 AND value2;
-- NOT BETWEEN 
SELECT * FROM Websites
WHERE name NOT BETWEEN 'A' AND 'H';

--SQL 连接(JOIN)
--INNER JOIN：如果表中有至少一个匹配，则返回行
--LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
--RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
--FULL JOIN：只要其中一个表中存在匹配，则返回行
--SQL INNER JOIN
SELECT column1, column2, ...
FROM table1
JOIN table2 ON condition;


--SQL INNER JOIN 关键字
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

--SQL LEFT JOIN 语法，LEFT JOIN 称为 LEFT OUTER JOIN。
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

--SQL RIGHT JOIN 关键字，RIGHT JOIN 称为 RIGHT OUTER JOIN。
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

--SQL FULL OUTER JOIN 关键字
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

--UNION 操作符用于合并两个或多个 SELECT 语句的结果集。默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

--SQL UNION ALL 语法
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

--SQL SELECT INTO 语句，SELECT INTO 语句从一个表复制数据，然后把数据插入到另一个新表中。
CREATE TABLE 新表
AS
SELECT * FROM 旧表 

--SQL SELECT INTO 语法
SELECT *
INTO newtable [IN externaldb]
FROM table1;

--创建数据库。
CREATE DATABASE dbname;

---****************SQL约束---------------------------------------------------------
--添加 NOT NULL 约束
ALTER TABLE table_name ALTER COLUMN column_name data_type NOT NULL

--删除 NOT NULL 约束
ALTER TABLE table_name ALTER COLUMN column_name data_type  NULL

--SQL UNIQUE 约束
--UNIQUE 约束唯一标识数据库表中的每条记录。
--UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
--PRIMARY KEY 约束拥有自动定义的 UNIQUE 约束。
--请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。
--MySQL：
CREATE TABLE table_name
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)

--SQL Server / Oracle / MS Access：
CREATE TABLE table_name2
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

--如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束，请使用下面的 SQL 语法：
--MySQL / SQL Server / Oracle / MS Access：
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

--ALTER TABLE 时的 SQL UNIQUE 约束
--MySQL / SQL Server / Oracle / MS Access：
ALTER TABLE Persons ADD UNIQUE (P_Id)

--如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束
--MySQL / SQL Server / Oracle / MS Access：
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

--撤销 UNIQUE 约束
--MySQL：
ALTER TABLE Persons DROP INDEX uc_PersonID

--SQL Server / Oracle / MS Access：
ALTER TABLE Persons DROP CONSTRAINT uc_PersonID

--SQL PRIMARY KEY 约束
--PRIMARY KEY 约束唯一标识数据库表中的每条记录。
--主键必须包含唯一的值。
--主键列不能包含 NULL 值。
--每个表都应该有一个主键，并且每个表只能有一个主键。

--CREATE TABLE 时的 SQL PRIMARY KEY 约束
--MySQL：
CREATE TABLE table_name3
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
PRIMARY KEY (P_Id)
)

--SQL Server / Oracle / MS Access：
CREATE TABLE table_name4
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
)

--如需命名 PRIMARY KEY 约束，并定义多个列的 PRIMARY KEY 约束
CREATE TABLE table_name
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

--ALTER TABLE 时的 SQL PRIMARY KEY 约束
--MySQL / SQL Server / Oracle / MS Access：
ALTER TABLE Persons ADD PRIMARY KEY (P_Id)

--如需命名 PRIMARY KEY 约束，并定义多个列的 PRIMARY KEY 约束，
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

--撤销 PRIMARY KEY 约束
--MySQL
ALTER TABLE Persons
DROP PRIMARY KEY

--SQL Server / Oracle / MS Access：
ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID

--CREATE TABLE 时的 SQL FOREIGN KEY 约束
--MySQL：
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

--SQL Server / Oracle / MS Access：
CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)

--如需命名 FOREIGN KEY 约束，并定义多个列的 FOREIGN KEY 约束，
--MySQL / SQL Server / Oracle / MS Access：
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

--ALTER TABLE 时的 SQL FOREIGN KEY 约束
--MySQL / SQL Server / Oracle / MS Access：
ALTER TABLE Orders ADD FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)

--如需命名 FOREIGN KEY 约束，并定义多个列的 FOREIGN KEY 约束
--MySQL / SQL Server / Oracle / MS Access
ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)

--撤销 FOREIGN KEY 约束
--MySQL：
ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders
--SQL Server / Oracle / MS Access：
ALTER TABLE Orders DROP CONSTRAINT fk_PerOrders

---外键约束，级联删除
--在创建表的时候指定外键约束
CREATE TABLE table_name
(
    column1 datatype null,
    column2 datatype null,
    CONSTRAINT fk_name FOREIGN KEY  (column1,column2,column_n) 
    REFERENCES table_name2(column1,column2,column_n)
    ON DELETE CASCADE--级联删除
);
--在创建表后增加外键约束
ALTER TABLE table_name
    ADD CONSTRAINT fk_name
    FOREIGN KEY (column1, column2,column_n) 
    REFERENCES table_name2(column1,column2,column_n)
    ON DELETE CASCADE;--级联删除

--SQL CHECK 约束
--CHECK 约束用于限制列中的值的范围。
--如果对单个列定义 CHECK 约束，那么该列只允许特定的值。
--如果对一个表定义 CHECK 约束，那么此约束会基于行中其他列的值在特定的列中对值进行限制。

--CREATE TABLE 时的 SQL CHECK 约束
--MySQL：
CREATE TABLE table_name5
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
CHECK (P_Id>0)
)

--SQL Server / Oracle / MS Access：
CREATE TABLE table_name6
(
P_Id int NOT NULL CHECK (P_Id>0),
LastName varchar(255) NOT NULL,
)

--如需命名 CHECK 约束，并定义多个列的 CHECK 约
--MySQL / SQL Server / Oracle / MS Access
CREATE TABLE table_name7
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

--ALTER TABLE 时的 SQL CHECK 约束
--MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons ADD CHECK (P_Id>0)

--如需命名 CHECK 约束，并定义多个列的 CHECK 约束，
--MySQL / SQL Server / Oracle / MS Access：
ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

--撤销 CHECK 约束
--MySQL：
ALTER TABLE Persons DROP CHECK chk_Person
--SQL Server / Oracle / MS Access
ALTER TABLE Persons DROP CONSTRAINT chk_Person

--使用以下命令获取外键信息：


--SQL DEFAULT 约束
--CREATE TABLE 时的 SQL DEFAULT 约束
--My SQL / SQL Server / Oracle / MS Access：
CREATE TABLE table_name8
(
    P_Id int NOT NULL,
    City varchar(255) DEFAULT 'Sandnes'
)

--ALTER TABLE 时的 SQL DEFAULT 约束
--MySQL：
ALTER TABLE Persons ALTER City SET DEFAULT 'SANDNES'

--SQL Server / MS Access：
ALTER TABLE Persons ADD CONSTRAINT ab_c DEFAULT 'SANDNES' for City

--Oracle：
ALTER TABLE Persons MODIFY City DEFAULT 'SANDNES'

--撤销 DEFAULT 约束
--MySQL：
ALTER TABLE Persons ALTER City DROP DEFAULT

--SQL Server / Oracle / MS Access：
ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT

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

--在表上创建一个简单的索引
CREATE INDEX index_name ON table_name (column_name)

--SQL CREATE UNIQUE INDEX 语法
CREATE UNIQUE INDEX index_name ON table_name (column_name)

--希望索引不止一个列，您可以在括号中列出这些列的名称，用逗号隔开：
CREATE INDEX PIndex ON Persons (LastName, FirstName)

--SQL 撤销索引、撤销表以及撤销数据库
--DROP INDEX 语句用于删除表中的索引。
--MySQL 
ALTER TABLE table_name DROP INDEX index_name
--DB2/Oracle 
DROP INDEX index_name
--MS SQL Server
DROP INDEX table_name.index_name

--DROP TABLE 语句用于删除表。
DROP TABLE table_name

--DROP DATABASE 语句
DROP DATABASE database_name

--TRUNCATE TABLE 语句
--如果我们仅仅需要删除表内的数据，但并不删除表本身，索引相关数据也删除
TRUNCATE TABLE table_name

--ALTER TABLE 语句
--如需在表中添加列，
ALTER TABLE table_name ADD column_name datatype

--如需删除表中的列，
ALTER TABLE table_name DROP COLUMN column_name

--要改变表中列的数据类型
--SQL Server / MS Access：
ALTER TABLE table_name ALTER COLUMN column_name datatype
--My SQL / Oracle：
ALTER TABLE table_name MODIFY COLUMN column_name datatype

--Oracle 10G
ALTER TABLE table_name MODIFY column_name datatype;

--SQL AUTO INCREMENT 字段
--用于 MySQL 的语法
CREATE TABLE table_name9
(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
PRIMARY KEY (ID)
)
--MySQL 使用 AUTO_INCREMENT 关键字来执行 auto-increment 任务。
--默认地，AUTO_INCREMENT 的开始值是 1，每条新记录递增 1。
--要让 AUTO_INCREMENT 序列以其他的值起始，请使用下面的 SQL 语法：
ALTER TABLE Persons AUTO_INCREMENT=100

--SQL Server
CREATE TABLE table_name10
(
ID int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
)
--MS SQL Server 使用 IDENTITY 关键字来执行 auto-increment 任务。
--在上面的实例中，IDENTITY 的开始值是 1，每条新记录递增 1。
--提示：要规定 "ID" 列以 10 起始且递增 5，请把 identity 改为 IDENTITY(10,5)。

--用于 Oracle 的语法
CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10
--上面的代码创建一个名为 seq_person 的 sequence 对象，它以 1 起始且以 1 递增。该对象缓存 10 个值以提高性能。cache 选项规定了为了提高访问速度要存储多少个序列值。
--必须使用 nextval 函数（该函数从 seq_person 序列中取回下一个值）：
INSERT INTO Persons (ID,FirstName,LastName)
VALUES (seq_person.nextval,'Lars','Monsen')

--------****视图*********————————————————————————————
 --SQL CREATE VIEW 语法
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

--SQL 更新视图
--SQL CREATE OR REPLACE VIEW 语法
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

--SQL 撤销视图
DROP VIEW view_name

--*************************SQL函数**************
--SQL Aggregate 函数
--AVG() - 返回平均值
SELECT AVG(column_name) FROM table_name
--COUNT() - 返回行数，COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）：
SELECT COUNT(column_name) FROM table_name;
SELECT COUNT(*) FROM table_name;
SELECT COUNT(DISTINCT column_name) FROM table_name;--函数返回指定列的不同值的数目：
--FIRST() - 返回第一个记录的值
--LAST() - 返回最后一个记录的值
--MAX() - 返回最大值
--MIN() - 返回最小值
--SUM() - 返回总和

--SQL Scalar 函数
--UCASE() - 将某个字段转换为大写
SELECT UPPER(column_name) FROM table_name;
--LCASE() - 将某个字段转换为小写
SELECT LOWER(column_name) FROM table_name;
--MID() - 从某个文本字段提取字符，MySql 中使用
SELECT MID(column_name[,start,length]) FROM table_name;
--SubString(字段，1，end) - 从某个文本字段提取字符
--LEN() - 返回某个文本字段的长度
SELECT LEN(column_name) FROM table_name;
--ROUND() - 对某个数值字段进行指定小数位数的四舍五入，ROUND() 函数用于把数值字段舍入为指定的小数位数。
SELECT ROUND(column_name,decimals) FROM TABLE_NAME;
--NOW() - 返回当前的系统日期和时间，NOW() 函数返回当前系统的日期和时间。
SELECT NOW() FROM table_name;
--FORMAT() - 格式化某个字段的显示方式
SELECT FORMAT(column_name,format) FROM table_name;

---GROUP BY 语句
--GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

--SQL HAVING 语法
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

--EXISTS 运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。
SELECT column_name(s)
FROM table_name
WHERE EXISTS(SELECT column_name FROM table_name WHERE condition);
--EXISTS 可以与 NOT 一同使用，查找出不符合查询语句的记录：
SELECT Websites.name, Websites.url 
FROM Websites 
WHERE NOT EXISTS (SELECT count FROM access_log WHERE Websites.id = access_log.site_id AND count > 200);