一、基本语法
查询语句:SELECT LastName,FirstName FROM Persons
插入语句:INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')
更新语句:UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'
删除语句:DELETE FROM Person WHERE LastName = 'Wilson'
二、关键字
Top:
SELECT TOP 2
*FROMPersons (查询前多少条记录,可用于分页)
LIKE:SELECT*FROM Persons WHERE City NOT LIKE '%lon%' (模糊查询)
IN:SELECT*FROM Persons WHERE LastName IN ('Adams','Carter') (列出的值)
Between:SELECT *FROMPersons WHERE LastName BETWEEN
'Adams' AND
'Carter' (介于之间)
AS:SELECT LastNameAS Family
, FirstName ASName
FROM Persons (别名,表也可以有别名)
NULL:SELECT LastName,FirstName,AddressFROM Persons WHERE AddressIS NULL (是否为空)
DISTINCT:
SELECT DISTINCT
Company FROMOrders (去掉重复性)
ORDER BY:SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC(ASC)(排序)
三、联合查询
INNER JOIN:SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM PersonsINNER JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName (只有两边匹配上才列出)
LEFT JOIN:例子同上 (只要左边能匹配上,就列出)
RIGHT JOIN:例子同上 (只要右边能匹配上,就列出)
FULL JOIN:例子同上 (不能匹配也列出)
UNION:SELECT E_Name FROM Employees_China UNION SELECT E_Name FROM Employees_USA (列的类型必须一致对应)
四、创建语句
创建数据库:CREATE DATABASE my_db
创建表: CREATE TABLE Persons(Id_P int,LastName varchar(255),FirstName varchar(255),Address varchar(255),City varchar(255))
创建约束:NOT NULL(不为空) UNIQUE(值不重复) PRIMARY KEY(主键) FOREIGN KEY(辅键) CHECK(条件) DEFAULT(默认值)
创建索引:CREATE INDEX PersonIndex ON Person (LastName DESC) (为查询提高性能)
创建视图:CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition (简化sql语句)
删除语句:DROP
修改语句:ALTER
五、聚合
常用的聚合函数:sum(求和),count(总数),avg(平均值),min(最小值),max(最大值),stdev(偏差),var(方差),checksum_agg(校验和?)
需要配合Group by 分组使用,having 进行条件筛选