SQL入门笔记
SQL（Structured Query Language）:用于访问和处理数据库的标准的计算机语言
所有数据库的操作无非都是增删查改，所以以下的SQL命令也就只是增删查改这四方面。
SQL命令(SQL对大小写不敏感)：
SELECT : 提取数据
提取某列：SELECT column_name,column_name FROM table_name;
提取所有：SELECT * FROM table_name;
提取某列不重复：SELECT DISTINCT column_name,column_name
FROM table_name;
按条件提取某列：SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;
eg: SELECT * FROM Customers
WHERE CustomerID=1;
提取某列后排序：SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC;
SELECT TOP: SELECT TOP number|percent column_name(s)
FROM table_name;
Eg: SELECT TOP 2 * FROM Customers; //返回前两条数据
UPDATE : 更新数据
语法：UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
eg: UPDATE Customers
SET ContactName='Alfred Schmidt', City='Hamburg'
WHERE CustomerName='Alfreds Futterkiste';
tips: 如果省略了 WHERE 子句，所有的记录都将被更新！
DELETE ：删除数据
删除某条记录：DELETE FROM table_name
WHERE some_column=some_value;
删除表/所有：DELETE FROM table_name;
DELETE * FROM table_name;
INSERT INTO : 插入数据
语法：INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
CREATE DATABASE : 创建新数据库
语法：CREATE DATABASE dbname;
ALTER DATABASE : 修改数据库
CREATE TABLE : 创建数据库表
语法：CREATE TABLE table_name
(
column_name1
data_type(size),
column_name2
data_type(size),
column_name3
data_type(size),
....
);
ALTER TABLE : 修改数据库表
如需在表中添加列，请使用下面的语法:
ALTER TABLE table_name
ADD column_name datatype
如需删除表中的列，请使用下面的语法（请注意，某些数据库系统不允许这种在数据库表中删除列的方式）：
ALTER TABLE table_name
DROP COLUMN column_name
要改变表中列的数据类型，请使用下面的语法：
ALTER TABLE table_name
MODIFY
COLUMN column_name datatype
DROP TABLE : 删除表
语法：DROP TABLE table_name
Tips:如果我们仅仅需要删除表内的数据，但并不删除表本身,请使用TRUNCATE TABLE 语句：TRUNCATE TABLE table_name
CREATE INDEX : 创建索引
语法：CREATE UNIQUE INDEX index_name
ON
table_name (column_name)
DROP INDEX : 删除索引
操作符：LIKE、IN、BETWEEN 、JOIN、 UNION
通配符：通配符可用于替代字符串中的任何其他字符。如：%、-、[ ]、[! ]/[^ ]
LIKE: SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;
Eg:
SELECT * FROM Customers
WHERE
City LIKE 's%';
IN: SELECT column_name(s)
FROM table_name
WHERE column_name IN
(value1,value2,...);
eg: SELECT * FROM
Customers
WHERE
City IN ('Paris','London');
BETWEEN: SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Eg: SELECT * FROM Products
WHERE
(Price BETWEEN 10 AND 20)
AND NOT
CategoryID IN (1,2,3);
SELECT * FROM Orders
WHERE OrderDate
BETWEEN #07/04/1996# AND #07/09/1996#;
JOIN（连接）: SELECT column_name(s)
FROM table1
INNER
JOIN table2
ON table1.column_name=table2.column_name;
Eg:SELECT Orders.OrderID,Customers.CustomerName,Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;
UNION(合并): SELECT column_name(s) FROM table1
UNION（all）
SELECT column_name(s) FROM table2;
Eg:
SELECT City FROM Customers
UNION
SELECT
City FROM Suppliers
ORDER BY
City;
SELECT INTO(复制/备份): SELECT column_name(s)
INTO newtable [IN externaldb]
FROM table1;
Eg: SELECT *
INTO
CustomersBackup2013
FROM
Customers;
INSERT INTO SELECT(复制数据插入到已存在的表): INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;
Eg:
INSERT INTO Customers (CustomerName, Country)
SELECT
SupplierName, Country FROM Suppliers;
约束：
- NOT NULL - 指示某列不能存储 NULL 值。
- UNIQUE - 保证某列的每行必须有唯一的值。
- PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
- FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
- CHECK - 保证列中的值符合指定的条件。
- DEFAULT - 规定没有给列赋值时的默认值。
- AUTO_INCREMENT
