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:关系数据库管理系统
阅读(1107) | 评论(0) | 转发(2) |