学习sql(提高篇)

Top的两种用法:

SELECT TOP 2 * FROM Persons
SELECT TOP 50 PERCENT * FROM Persons

like 和 not like:

SELECT * FROM Persons WHERE City LIKE '%g'  -- %:一个或多个字符，－：一个字符，
SELECT * FROM Persons WHERE City NOT LIKE '%lon%' -- [ABC]：其中一个，[^AB]或[!AB]:非其中一个


in:

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

between and not between:

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

as:

SELECT A AS B, C AS D FROM Table

join:

SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name

• INNER JOIN: 如果表中有至少一个匹配，则返回行
• LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
• FULL JOIN: 只要其中一个表中存在匹配，就返回行

union and union all:

SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2   -- union：列出不同的值，union all:列出所有的值，允许重复


select into:

SELECT column_name(s) INTO new_table_name [IN externaldatabase] FROM old_tablename  --把选取的列插入到创建的新的数据库表格中


create or alter:

CREATE DATABASE database_name
CREATE TABLE 表名称
(
列名称1 数据类型 NOT NULL unique，identify(自增),
列名称2 数据类型,DEFAULT ‘a’,
....
)

ALTER TABLE Persons ADD UNIQUE (P_Id)
ALTER TABLE Persons DROP INDEX uc_PersonID

ALTER TABLE Persons ADD PRIMARY KEY (Id_P)
ALTER TABLE Persons DROP PRIMARY KEY

ALTER TABLE Orders ADD FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders


ALTER TABLE Persons ADD CHECK (Id_P>0)
ALTER TABLE Persons DROP CONSTRAINT chk_Person    

ALTER TABLE Persons ALTER City SET DEFAULT 'SANDNES'

ALTER TABLE Persons ALTER City DROP DEFAULT

CREATE INDEX index_name ON table_name (column_name) --or unique index
ALTER TABLE table_name DROP INDEX index_name
DROP TABLE 表名称
DROP DATABASE 数据库名称
TRUNCATE TABLE 表名称  --删除表中的数据，不删除表格
ALTER TABLE table_name ADD column_name datatype
ALTER TABLE table_name DROP COLUMN column_name
ALTER TABLE table_name ALTER COLUMN column_name datatype
CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition

Date函数

null:

SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NULL

SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0)) FROM Products

DBMS:数据库管理系统，RDBMS：关系数据库管理系统