SELECT TOP子句:

SELECT TOP子句用于指定要返回的记录数量,在包含千条记录的大型表上很有用。

并不是所有的数据库系统都支持SELECT TOP子句。MYSQL支持LIMIT子句来选择有限数量的记录,而Oracle使用ROWNUM。

SQL server语法:

SELECT TOP number\percent column_name(s)

FROM table_name

WHERE condition

选择满足条件condition的table_name表中的一定数量的column_name(s)列;

MYSQL语法:

SELECT column_name(s)

FROM table_name

WHERE condition

LIMIT number

选择满足条件condition的table_name表中的一定数量的column_name(s)列;

Oracle语法:

SELECT column_name(s)

FROM table_name

WHERE condition AND ROWNUN<=number

选择满足条件condition的table_name表中的一定数量的column_name(s)列;

SQL LIKE运算符:

LIKE操作符用于在WHERE子句中搜索列中指定的模式;

有两个通配符与LIKE一起使用:

% -百分号表示零个、一个或多个字符;

_ -下划线表示单个字符;

MS ACCESS 使用问号(?)而不是下划线(_);

百分号可以和下划线组合使用。

SELECT column1,column2...

FROM table_name

WHERE columnN LIKE pattern;

选择table_name的columnN列中匹配pattern模式的column1,column2...

‘%’和‘_’通配符显示不同LIKE运算符的例子:

LIKE运算符

描述

WHERE CustomerName LIKE 'a%'

查找以‘a’开头的任何值

WHERE CustomerName LIKE '%a'

查找以‘a’结尾的任何值

WHERE CustomerName LIKE '%or%'

在任何位置查找具有“or”字符的任何值

WHERE CustomerName LIKE '_r%"

查找第二个字符是‘r’的任何值

WHERE CustomerName LIKE ‘a_%_%’

查找以‘a’开头且字符串至少是三个字符的值

WHERE CustomerName LIKE ‘a%o’

查找以‘a’开头‘o’结尾的任何值

SQL通配符:

通配符用于替换字符串中的其他任何字符,如LIKE语句中提到的“%”和“_”。

在MS Access和SQL Server中,也可以使用:

[charlist] -定义要匹配的字符的集合和范围;

[^charlist]或[!charlist] -定义不匹配字符的集合和范围。

如:

SELECT * FROM Customers

WHERE City LIKE '[bsp']%';

选择所有City以“b”,“s”或“p”开头的客户;

IN运算符:

IN运算符允许在WHERE子句中指定多个值;是多个OR条件的简写;

SELECT column_name(s)

FROM table_name

WHERE column_name IN(value1,value2...);

或者:

SECECT column_name(s)

FROM  table_name

WHERE column_name IN (SELECT statement)

 BETWEEN运算符:

BETWEEN运算符选择给定范围内的值,值可以是数字、文本或日期;

SELCET column_name(s)

FROM table_name

WHERE column_name BETWEEN value1 AND value 2;

从table_name处选择column_name(s)哪里的column_name的值范围在value1到value2之间;

显示示例范围外的值用NOT BETWEEN;

在不同的数据库中,BETWEEN操作符包含的范围不同,如一些数据库中包含value1和value2;一些数据库选择两个数据之间的值但不包括value1和value2;一些数据库只包括前者,即value1但不包括value2。因此,使用时需检测BETWEEN操作符的包含范围;

SQL通用数据类型:

数据类型 描述
CHARACTER(n) 字符/字符串。固定长度n
VARCHAR(n)或CHARACTER VARYING(n) 字符/字符串。可变长度,最大长度为n
BINARY(n) 二进制串。固定长度n
BOOLEAN 存储TRUE或FALSE值
VARBINARY(n)或BINARY VARING(n) 二进制串。可变长度,最大长度为n
INTEGER(p) 整数值(没有小数点)。精度p
SMALLINT 整数值(没有小数点)。精度5
INTEGER 整数值(没有小数点)。精度10
 BIGINT 整数值(没有小数点)。精度19 
 DECIMAL(p,s) 精确数值,精度p,小数点后位数s。 
 NUMERIC(p,s) 同DECIMAL(p,s) 
 FLOAT(p) 近似数值。尾数精度p 
 REAL 近似数值。尾数精度7 
 FLOAT 近似数值。尾数精度16 
 DOUBLE PRECISION 近似数值。尾数精度16 
 DATE 存储年月日的值 
 TIME 存储小时,分,秒的值 
 TIMESTAMP 存储年月日,小时,分,秒的值 
 INTERVAL 由一些整数数段组成,代表一段时间,取决于区间的类型 
 ARRAY 元素的固定长度的有序集合 
 MULTISET 元素的可变长度的无序集合 
 XML 存储XML数据 

不同数据库平台某些数据类型的通用数据名称:

数据类型 Access SQLServer Oracle MySQL PostgreSQL
boolean Yes/No Bit Byte N/A Boolean
integer

Number(integer)

Int Number Int(Integer) Int(Integer)
float Number(single) Float(Real) Number Float Numeric
currency Currency Money N/A N/A Money
string(fixed) N/A Char Char Char Char
string(variable)

Text(<256)

Memo(65k+)

Varchar

Varchar

Varchar2

Varchar Varchar
binary object OLE Object Memo

Binary(fixed up to 8k)

Varbinary(<8k)

Image(<2GB)

Long

Raw

Blob

Text

Binary

Varbinary

SQL连接语句:

JOIN用于把两个或多个表的行结合起来,基于这些表中的共同字段;

JOIN类型:

INNER JOIN:如果表中有至少一个匹配,则返回行;

LEFT JOIN:即使右表中没有匹配,也从左表返回所有的行;

RIGHT JOIN:即使左表中没有匹配,也从右表返回所有行;

FULL JOIN:只要其中一个表存在匹配,则返回行;

INNER JOIN语法:

SELECT column_name(s)

FROM table1

INNER JOIN table2 ON table1.column_name = table2.column_name;

选择table1和table2中所有满足table1.column_name = table2.column_name的column_name(s)列的所有记录;

如果表中至少有一个匹配项,INNER JOIN关键字将会列出满足条件的所有行,如果不匹配,则不会列出行;

LEFT JOIN语法:

SELECT column_name(s)

FROM table1

LEFT JOIN table2

ON table1.column_name =table2.column_name;

或:

SELECT column_name(s)

FROM table1

LEFT OUTER JOIN table2

ON table1.column_name =table2.column_name;

SQL左连接LEFT JOIN关键字返回左表(table1)中所有行,即使在右表(table2)中没有匹配;当右表中无对应的匹配值时,对应的位置输出NULL。

SQL 右连接:

RIGHT JION返回右表table2的所有行,即使左表table1中没有匹配;当左表中无对应的匹配值时,对应的位置输出为NULL;

SELECT column_name(s)

FROM table_name

RIGHT JOIN table2

ON table1.column_name = table2.column_name

SQL  FULL OUTER JOIN关键字(完整外部连接)

当左表table1和右表table2表记录匹配时,FULL OUTER JOIN关键字将会返回所有记录;

SELECT column_name(s)

FROM table1

FULL OUTER JOIN table2

ON table1.column_name = table2.column_name;

 SQL自连接:

SELECT column_name(s)

FROM table1 T1,table1 T2

WHERE condition;

示例:

以下SQL语句匹配来自同一城市的客户:

SELECT A.CostomerName,B.CostomerName,A.City

FROM Customer A,Customer B

WHERE A.CustomerID <>B.CustomerID

AND A.City = B.City

ORDER BY A.City;

SQL UNION运算符:

UNION运算符用于组合两个或更多SELECT语句的结果集;

UNION中每个SELECT语句必须具有相同的列数,这些列也必须具有相似的数据类型,每个SELECT语句中的列也必须以相同的顺序排列;

SELECT column_name(s) FROM table1

UNION 

SELECT column_name(s) FROM table2;

默认情况下,UNION运算符选择一个不同的值,如果需要允许重复值,则需要使用UNOIN ALL;

SQL UNION ALL 语法:

SELECT column_name(s) FROM table1

UNION ALL

SELECT column_name(s) FROM table2;

UNION结果集中列名总是等于UNION中第一个SELECT语句中的列名;

SELECT INTO语句:

从一个表中复制数据,然后将数据插入到另一个新表中;

SELECT column_name(s)

INTO newtable [IN externaldb]

FROM table1;

从table1中复制column_name(s)列到新表newtable中,IN externaldb表示复制到另一个数据库中,不写表示复制与本数据库目录下;

将使用SELECT语句中定义的列名和类型创建新表,可以使用AS语句来定义一个新名称;

INSERT INTO SELECT语句:

从表中复制数据,并将数据插入到现有的表中;目标表中的任何现有行都不会受到影响;

INSERT INTO table2

(column_name(s))

SELECT column_name(s)

FROM table1;

撤销索引,表以及数据库:

DROP INDEX用于删除表中的索引;

不同数据库语法稍有差异:

MS Access DROP INDEX index_name ON table_name
MS SQL Server

DROP INDEX table_name.index_name

DB2/Oracle DROP INDEX index_name

MySQL

ALTER TABLE table_name DROP INDEX index_name

DROP TABLE语句:

DROP TABLE语句用于删除表;

DROP TABLE table_name

DROP DATABASE:

DROP DATABASE用于删除数据库;

DROP DATABASE database_name

TRUNCATE TABLE:

删除表中的数据,而非表本身;

TRUNCATE TABLE table_name;

 CREAT DATABASE语句:

CREAT DATABASE dbname;

创建数据库dbname;

CREAT TABLE语句:

CREAT TABEL table_name

(

column1_name data_type(size),

column2_name data_type(size),

...

);

ALTER TABLE语句:

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;

MySQL/Oracle:

ALTER TBALE table_name

MODIFY COLUMN column_name datatype;

AUTO INCREMENT 字段:

以下SQL语句将“Person”表中的“ID”字段定义为自动递增主键字段:

MySQL:

CREAT TABLE Person

(

  ID int NOT NULL AUTO_INCREMENT,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

PRIMAR KEY (ID)

);

默认情况下,AUTO_INCREMENT的起始值为1,每个记录增加1;

若要以其他值开始AUTO_INCREMENT序列,需对其赋初值,如:

ALTER TABLE Person AUTO_INCREMENT = 100

SQL Server为“Person”中“ID”字段设置自动递增语法:

CREAT TABLE

(

  ID int IDENTITY(1,1) PRIMARY KEY,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

);

SQL Server 中使用IDENTITY关键字执行自动增量;上面示例设置初始值为1,增量为1;若要设置初始值为10,增量为2,则改为IDENTITY(10,2);

Access设置为“person”表中的“ID”字段自增语法:

CREAT TABLE Persons

  ID Integer PRIMARY KEY AUTOINCREMENT,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

);

Access中使用AUTOINCREMENT完成自增,默认情况下初始值为1,每次自动增加1;若要设置初始值为10,每次增量为2,则改为AUTOINCREMENT(10,2);

Oracle语法:

CREAT SEQUENCE seq_person

MINVALUE 1

START WITH 1

INCREMENT BY 1

CACHE 10

上面的代码创建了一个名为“seq_person”的序列(sequence)对象,它以1开头,以1递增,次对象缓存10个值以提高性能;

若要向“Person”表中插入新记录,需用nextval函数,该函数从序列中检索下一个值:

INSERT INTO Persons(ID,FirstName,LastName)

Value(seq_person.nextval,'lars','Monsen')

SQL视图:

在SQL中,视图是基于SQL语句的结果集的可视化表;视图包括行和列,就像真正的表一样;视图中的字段是一个或多个数据库中真实表中的字段;视图中总是显示最新数据,每当用户查询视图时,数据库引擎就用视图的SQL语句重新构建数据;

CREAT VIEW语法:

CREAT VIEW view_name AS

SELECT column_name(s)

FROM table_name

WHERE condition;

更新视图:

CREAT OR REPLACE VIEW 语法:

CREAT OR REPLACE VIEW view_name AS 

SELECT column_name(s)

FROM table_name

WHERE condition

撤销视图:

DROP VIEW view_name

SQL Date函数:

MySQL中内置日期函数:

函数 描述
NOW() 返回当前的时间和日期
CURDATE() 返回当前的日期
CURTIME() 返回当前的时间
DATE() 提取日期或日期/时间表达式中的日期
EXTRACT() 返回日期/时间的单独部分
DATE_ADD() 向日期中添加指定的时间间隔
DATE_SUB() 从日期减去指定的时间间隔
DATEDIFF 返回两个日期间的天数
DATE_FORMAT() 用不同的格式显示日期/时间

SQL Server Date函数:

函数 描述
GETDATE() 返回当前的时间和日期
DATEPART() 返回日期/时间单独的部分
DATEADD() 在日期中添加或减去指定的时间间隔
DATEDIFF() 返回两个日期间的时间
CONVERT() 用不同的格式显示日期/时间

SQL Date数据类型:

MySQL:

DATE -格式:YYYY-MM-DD

DATETIME -格式:YYYY-MM-DD HH:MM:SS

TIMESTAMP -格式:YYYY-MM-DD HH:MM:SS

YEAR -格式:YYYY或YY

SQL Server:

DATE -格式:YYYY-MM-DD

DATETIME -格式:YYYY-MM-DD HH:MM:SS

SMALLDATETIME -格式:YYYY-MM-DD HH:MM:SS

TIMESTAMP -格式:唯一的数字

为了查询更加简单和易于维护,尽量不要使用日期中的时间部分;

SQL NULL值:

如果表中的列是可选的,那么我们可以插入一个新记录或现有的记录,而无须向里面添加一个值,则意味着该字段将存储为NULL;

NULL值的处理与其他值不同,为未知的或不适当值的占位符;无法比较NULL和0,因为他们是不等价的;

测试NULL值不能使用=,<或<>。必须使用IS NULL或IS NOT NULL操作符;

如:

SELECT * FROM Customers

WHERE Address IS NULL;

将会选择出Cutomers表中Address字段为空的值的列表,反正,IS NOT NULL则是选择表中值不为空的列表。