一、基础
1. 对数据库和数据表的操作
1> 创建数据库
CREATE DATABASE databaseName
2> 删除数据库:DROP
drop database dbname
3> 备份sql server
--- 创建 备份数据的 device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
--- 开始 备份
BACKUP DATABASE pubs TO testBack
4> 创建数据表
create table tableName(fieldName1 type1 [not null] [primary key],fieldName2 type2 [not null][unique],..)
根据已有的表创建新表:
A:用关键字LIKE
create table tab_new like tab_old (使用旧表创建新表)
B:用关键字AS
create table tab_new as select col1,col2… from tab_old definition only
5> 删除数据表
drop table tabname
6> 增加一个列(字段)
Alter table tablename add column columnName
删除一个列(字段)
Alter table tableName drop column columnName
7> 创建索引:CREATE INDEX
在表中创建索引,可以方便快速高效地查询数据。用户无法看到索引,它们只能被用来加速搜索/查询。
注意:更新一个包含索引的表需要比更新一个没有索引的表更多的时间,这是由于索引本身也需要更新。因此,理想的做法是仅仅在常常被搜索的列(以及表)上面创建索引。
在表上创建一个简单的索引。
1. 允许使用重复的值:
语法:
CREATE INDEX index_name
ON table_name (column_name)
注释:"column_name" 规定需要索引的列。
2. 不允许有重复的值:
SQL CREATE UNIQUE INDEX 语法
在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。
语法:
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
如果需要创建降序索引,可以在列名称之后添加保留字 DESC:
CREATE INDEX PersonIndex ON Person (LastName DESC)
假如希望索引不止一个列,您可以在括号中列出这些列的名称,用逗号隔开:
CREATE INDEX PersonIndex
ON Person (LastName, FirstName)
删除索引:drop index indexName on tableName
注意:索引是不可更改的,想更改必须删除重新建。
8> 修改数据类型:
语法如下:
MySQL数据库:
alter table tableName modify columnName dataType; //修改列columnName列的数据类型为dataType
alter table tableName change oldColumnName newColumnName dataType; //修改列的名字为sid,而且把数据类型修改为dataType
SQL Server / Oracle / MS Access数据库:
ALTER TABLE table_name
ALTER COLUMN column_name datatype
9> 视图:VIEW
在 SQL 中,视图是基于 SQL 语句的结果集的可视化的表。
视图包含行和列,就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。
我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句,我们也可以提交数据,就像这些来自于某个单一的表。
创建视图:CREATE VIEW
语法:
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
查看视图内容跟查看数据表中内容的方法相同;
删除视图:
SQL DROP VIEW Syntax
DROP VIEW view_name
注意:MySQL不支持对视图的查询(支持创建和删除)
10> 创建表的备份复件:SELECT INTO
SELECT INTO 语句从一个表中选取数据,然后把数据插入另一个表中;
SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档。
数据库会创建一个名字为new_table_name的数据表,并将数据复制进去
语法:
把所有的列插入新表:
SELECT *
INTO new_table_name [IN externaldatabase] //IN externaldatabase用来标识目标表属于外部数据库
FROM old_tablename //源表
只把希望的列插入新表:
SELECT column_name(s)
INTO new_table_name [IN externaldatabase] //IN externaldatabase用来标识目标表属于外部数据库
FROM old_tablename
例子:
下例会创建一个名为 "Persons_Order_Backup" 的新表,其中包含了从 Persons 和 Orders 两个表中取得的信息:
SELECT Persons.LastName,Orders.OrderNo
INTO Persons_Order_Backup
FROM Persons
INNER JOIN Orders
ON Persons.Id_P=Orders.Id_P
在MySQL中,不支持SELECT INTO语句,只支持INSERT INTO ... SELECT ...
语法如下:
INSERT INTO tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
注意:在MySQL中,必须先手动创建目标表,然后才能INSERT,否则会提示出错
2. 创建表补充知识:约束条件
约束用于限制加入表的数据的类型。
可以在创建表时规定约束(通过 CREATE TABLE 语句),或者在表创建之后也可以(通过 ALTER TABLE 语句)。
命名UNIQUE约束(语法:CONSTRAINT 约束名称 约束条件)
下面介绍几种主要的约束条件:
A:NOT NULL
NOT NULL 约束强制列不接受 NULL 值,即约束强制字段始终包含值。
这意味着,如果不向字段添加值,就无法插入新纪录或者更新记录。
示例:
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
B:UNIQUE
UNIQUE 约束唯一标识数据库表中的每条记录。
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。(PRIMARY KEY 拥有自动定义的 UNIQUE 约束。)
请注意,每个表可以有多个 UNIQUE 约束,但是每个表只能有一个 PRIMARY KEY 约束。
示例:创建Persons表,其中Id_P字段有唯一性的限制
CREATE时增加 UNIQUE 约束:
MySQL数据库:
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (Id_P)
)
SQL Server / Oracle / MS Access数据库:
CREATE TABLE Persons
(
Id_P int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
命名 UNIQUE 约束,以及为多个列定义 UNIQUE 约束
MySQL / SQL Server / Oracle / MS Access数据库:
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
)
ALTER操作增加UNIQUE约束:
MySQL / SQL Server / Oracle / MS Access数据库:
ALTER TABLE Persons
ADD UNIQUE (P_Id)
命名 UNIQUE 约束,并定义多个列的 UNIQUE 约束
MySQL / SQL Server / Oracle / MS Access数据库:
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
删除UNIQUE约束:
MySQL数据库:
ALTER TABLE Persons DROP INDEX uc_PersonID
SQL Server / Oracle / MS Access数据库:
ALTER TABLE Persons DROP CONSTRAINT uc_PersonID
C:PRIMARY KEY
PRIMARY KEY 约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值(UNIQUE)。
主键列不能包含 NULL 值(NOT NULL)。
每个表都应该有一个主键,并且每个表只能有一个主键。(有且只有)
CREATE时增加主键约束:
MySQL数据库:
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (Id_P)
)
SQL Server / Oracle / MS Access数据库:
CREATE TABLE Persons
(
Id_P int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
命名 PRIMARY KEY 约束,以及为多个列定义 PRIMARY KEY 约束
MySQL / SQL Server / Oracle / MS Access数据库:
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID PRIMARY KEY (Id_P,LastName)
)
ALTER增加主键约束:
MySQL / SQL Server / Oracle / MS Access数据库:
ALTER TABLE Persons
ADD PRIMARY KEY (Id_P)
增加名为pk_PersonID的主键约束
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)
删除主键约束:
MySQL数据库:
ALTER TABLE Persons
DROP PRIMARY KEY
SQL Server / Oracle / MS Access数据库:
ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID
D:FOREIGN KEY
一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。
FOREIGN KEY 约束用于预防破坏表之间连接的动作。
FOREIGN KEY 约束也能防止非法数据插入外键列,因为它必须是它指向的那个表中的值之一。
语法:
FOREIGN KEY (fieldName1)REFERENCES (tableName.fieldName)
下例:创建Orders表,并添加外键约束:Id_P指向Person.Id_P
CREATE时增加 FOREIGN KEY 约束:
MySQL数据库:
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (O_Id),
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
)
SQL Server / Oracle / MS Access数据库:
CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
Id_P int FOREIGN KEY REFERENCES Persons(Id_P)
)
命名FOREIGN KEY约束
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
)
ALTER添加FOREIGN KEY约束:
MySQL / SQL Server / Oracle / MS Access数据库:ALTER TABLE Orders
ADD FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
命名 FOREIGN KEY 约束,以及为多个列定义 FOREIGN KEY 约束
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
删除 FOREIGN KEY 约束:
MySQL数据库:
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders
SQL Server / Oracle / MS Access数据库:
ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders
E:CHECK
CHECK 约束用于限制列中的值的范围。
如果对单个列定义 CHECK 约束,那么该列只允许特定的值。
如果对一个表定义 CHECK 约束,那么此约束会在特定的列中对值进行限制。
示例:
在Persons表创建时为 Id_P 列创建 CHECK 约束。CHECK 约束规定 "Id_P" 列必须只包含大于 0 的整数。
CREATE时增加 CHECK 约束:
My SQL数据库:
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (Id_P>0)
)
SQL Server / Oracle / MS Access数据库:
CREATE TABLE Persons
(
Id_P int NOT NULL CHECK (Id_P>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
命名 CHECK 约束,以及为多个列定义 CHECK 约束
MySQL / SQL Server / Oracle / MS Access数据库:
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')
)
ALTER 添加 CHECK 约束:
MySQL / SQL Server / Oracle / MS Access数据库:
ALTER TABLE Persons
ADD CHECK (Id_P>0)
命名 CHECK 约束,以及为多个列定义 CHECK 约束:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')
删除 CHECK 约束:
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT chk_Person
F:DEFAULT
DEFAULT 约束用于向列中插入默认值。
如果没有规定其他的值,那么会将默认值添加到所有的新纪录。
示例:
下面的 SQL 在 "Persons" 表创建时为 "City" 列创建 DEFAULT 约束:
CREATE时增加 DEFAULT 约束:
My SQL / SQL Server / Oracle / MS Access数据库:
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)
ALTER增加 DEFAULT 约束:
MySQL数据库:
ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'
SQL Server / Oracle / MS Access数据库:
ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES'
撤销 DEFAULT 约束:
MySQL数据库:
ALTER TABLE Persons
ALTER City DROP DEFAULT
SQL Server / Oracle / MS Access数据库:
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT
sql语句对大小写不敏感
1> 选择:select
SELECT 列名称 FROM 表名称
SELECT * FROM 表名称
选择不重复的结果:使用SELECT DISTINCT 语句
SELECT DISTINCT 列名称 FROM 表名称
2> 插入:insert
INSERT INTO 表名称 VALUES (值1, 值2,....)
3> 删除:delete
DELETE FROM 表名称 WHERE 列名称 = 值
4> 更新:update
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
5> 多选项查询条件:IN
语法:
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...) //所有column_name字段对应的值在in()列表中的,都会被选出(或不被选出 not时);
例子:从表中选取姓氏为 Adams 和 Carter 的人
SELECT * FROM Persons
WHERE LastName IN ('Adams','Carter')
6> 界值查询:BETWEEN
操作符 BETWEEN ... AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。
语法:
SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2
如需使用上面的例子显示范围之外的人,请使用 NOT 操作符:
例如:
SELECT * FROM Persons
WHERE LastName
NOT BETWEEN 'Adams' AND 'Carter'
注意:对于边界值,即value1和value2的处理,会根据数据库的不同而不相同(有些会包含边界值,有些则不会包含)
5> 查找匹配:like
select * from table1 where field1 like ’%value1%’
通配符:
A:% 替代一个或多个字符
例:
从Persons表中选择City以“Ne”开头的所有记录
SELECT * FROM Persons
WHERE City LIKE 'Ne%'
B:_仅替代一个字符
例:
从Persons表中选取名字的第一个字符之后是 "eorge" 的人
SELECT * FROM Persons
WHERE FirstName LIKE '_eorge'
C:[charlist] 字符列中的任何单一字符
例:
从Persons表中选取居住的城市以 "A" 或 "L" 或 "N" 开头的人
SELECT * FROM Persons
WHERE City LIKE '[ALN]%'
D:[^charlist]或者[!charlist]不在字符列中的任何单一字符
例:
从Persons表中选取居住的城市不以 "A" 或 "L" 或 "N" 开头的人
SELECT * FROM Persons
WHERE City LIKE '[!ALN]%'
6> 排序:order by
ORDER BY 语句默认按照升序对记录进行排序。
如果您希望按照降序对记录进行排序,可以使用 DESC 关键字。
例子:
以字母顺序显示公司名称(Company),并以数字顺序显示顺序号(OrderNumber):
SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber
7> 总数:count(column_name)函数返回匹配指定条件的行数
select count(*) from tableName
8> 求和:sum()
sum(column_name)函数函数返回数值列的总数(总额)
select sum(field1) as sumvalue from table1
9> 平均:AVG(column_name)函数返回数值列的平均值,其中NULL值不包括在计算中
select avg(field1) as avgvalue from table1
10> 最大:Max()
MAX(column_name)函数返回一列中的最大值。NULL 值不包括在计算中。
SELECT MAX(column_name) FROM table_name
11> 最小:Min()
MIN(column_name)函数返回一列中的最小值。NULL 值不包括在计算中。
SELECT MIN(column_name) FROM table_name
12> 分组:GROUP BY
合计函数 (比如 SUM) 常常需要添加 GROUP BY 语句。
语法如下:
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
例子:
先按Customer进行分组,然后对Customer的OrderPrice进行sum()操作(即对用户的所有OrderPrice合并在一个组中,方便sum()函数进行统计)
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
13> 对合计函数的结果进行筛选:HAVING
在 SQL 中增加 HAVING 子句原因是,WHERE 关键字无法与合计函数一起使用。
语法:
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value //对合计函数的结果进行筛选
例子:
找出Customer对应的OrderPrice的总和,且要求其合SUM(OrderPrice)小于2000
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000
14> 指定字段第一个记录:FIRST()
FIRST() 函数返回指定的字段中第一个记录的值
SELECT FIRST(column_name) FROM table_name
15> 指定字段最后一个记录:LAST()
SELECT LAST(column_name) FROM table_name
提示:可用order by关键字配合使用
16> 指定返回N条记录:TOP
TOP 子句用于规定要返回的记录的数目。
语法:
SELECT TOP number|percent column_name(s)
FROM table_name
注意:
mySQL中对应TOP关键字的是:LIMIT
例如:
返回前5个结果:
SELECT * FROM Persons order by Id_P LIMIT 5
返回从5到15个记录
SELECT * FROM Persons order by Id_P LIMIT 5,15
oracle 中对应TOP关键字的是:ROWNUM
语法:
SELECT column_name(s) FROM table_name WHERE ROWNUM <= number
二. 高级操作
1. 合并操作结果:UNION 运算符
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
注意:UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。
当ALL随UNION一起使用时(即UNION ALL),不消除重复行。
两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
例子:
列出在中国和美国的所有的雇员名字:
SELECT E_Name FROM Employees_China //选择中国的所有雇员名字
UNION ALL //对左右两个结果集进行合并(名字中难免会有重复值,需要用ALL关键字)
SELECT E_Name FROM Employees_USA //选择美国的所有雇员名字
2. EXCEPT 运算符
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。
注意:当ALL随EXCEPT一起使用时(EXCEPT ALL),不消除重复行。
3. INTERSECT 运算符
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。
当 ALL随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。
注:使用运算词的几个查询结果行必须是一致的。
4、关联查询:JOIN
有时为了得到完整的结果,我们需要从两个或更多的表中获取结果。
数据库中的表可通过键将彼此联系起来。这样可以在不重复每个表中的所有数据的情况下,把表间的数据交叉捆绑在一起。
下面列出了所有的JOIN类型,以及它们之间的差异。
JOIN(INNET JOIN): 如果表中有至少一个匹配,则返回行
LEFT (OUT)JOIN: 即使右表中没有匹配,也从左表返回所有的行
RIGHT(OUT)JOIN: 即使左表中没有匹配,也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配,就返回行
A、左外连接(左连接):LEFT JOIN
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行,即使在右表 (table_name2) 中没有匹配的行。
语法:
SELECT column_name(s) //输出的字段
FROM table_name1 //左表
LEFT JOIN table_name2 //右表
ON table_name1.column_name=table_name2.column_name //关联条件
使用时,左表按照关联条件与右表进行匹配,如果匹配,则按输出字段进行输出;如果不匹配,则只输出左表中对应的输出字段的值(右表对应的字段为空)
例题:
原始的表 (用在例子中的):
"Persons" 表:
Id_P LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
3 Carter Thomas Changan Street Beijing
"Orders" 表:
Id_O OrderNo Id_P
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 65
SQL语句:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons LEFT JOIN Orders //关联表
ON Persons.Id_P=Orders.Id_P //关联条件
ORDER BY Persons.LastName //结果排序
结果集:
LastName FirstName OrderNo
Adams John 22456
Adams John 24562
Carter Thomas 77895
Carter Thomas 44678
Bush George (空值) //对于左表(persons表)中的该项没有匹配值,故OrderNo为空
B:右外连接(右连接) RIGHT JOIN
RIGHT JOIN 关键字会从右表 (table_name2) 那里返回所有的行,即使在左表 (table_name1) 中没有匹配的行。
即,结果集既包括连接表的匹配连接行,也包括右连接表的所有行。
SELECT column_name(s) //输出的字段
FROM table_name1 RIGHT JOIN table_name2 //关联表
ON table_name1.column_name=table_name2.column_name //关联条件
例子:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons RIGHT JOIN Orders //关联表
ON Persons.Id_P=Orders.Id_P //关联条件
ORDER BY Persons.LastName //结果排序
输出结果:LastName FirstName OrderNo
Adams John 22456
Adams John 24562
Carter Thomas 77895
Carter Thomas 44678
34764 //对于右表(Orders表)中的该项没有匹配值,故LastName/FirstName为空
C:全外连接:FULL JOIN
只要其中某个表存在匹配,FULL JOIN 关键字就会返回行。
语法:
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
例子:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons FULL JOIN Orders //关联表
ON Persons.Id_P=Orders.Id_P //关联条件
ORDER BY Persons.LastName //结果排序
LastName FirstName OrderNo
Adams John 22456
Adams John 24562
Carter Thomas 77895
Carter Thomas 44678
Bush George
34764
D:内连接JOIN(INNER JOIN)
在表中存在至少一个匹配时,INNER JOIN 关键字返回行。
注意:JOIN默认为INNER JOIN
SELECT column_name(s) //输出的字段
FROM table_name1 //左表
INNER JOIN table_name2 //右表
ON table_name1.column_name=table_name2.column_name //关联条件
例子:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons JOIN Orders //关联表
ON Persons.Id_P=Orders.Id_P //关联条件
ORDER BY Persons.LastName //结果排序
输出结果:
LastName FirstName OrderNo
Adams John 22456
Adams John 24562
Carter Thomas 77895
Carter Thomas 44678
三. 补充
1> 指定别名(Alias):AS
通过使用 SQL,可以为列名称和表名称指定别名(Alias)。
表的 SQL Alias 语法:
SELECT column_name(s)
FROM table_name
AS alias_name
例子:
假设我们有两个表分别是:"Persons" 和 "Product_Orders"。我们分别为它们指定别名 "p" 和 "po"。现在,我们希望列出 "John" 的所有定单。我们可以使用下面的 SELECT 语句:
SELECT po.OrderID, p.LastName
FROM Persons AS p, Product_Orders AS po //指定别名
WHERE p.LastName='Adams'
列的 SQL Alias 语法:
SELECT column_name AS alias_name
FROM table_name
例子:
SELECT LastName AS Family, FirstName AS Name FROM Persons
输出结果:
Family Name
Adams John
Bush George
如果不用别名,则输出结果如下:
LastName FirstName
Adams John
Bush George
2> 日期函数
当我们处理日期时,最难的任务恐怕是确保所插入的日期的格式,与数据库中日期列的格式相匹配。
只要数据包含的只是日期部分(年/月/日),运行查询就不会出问题。但是,如果涉及时间(时/分/秒),情况就有点复杂了。
在讨论日期查询的复杂性之前,我们先来看看最重要的内建日期处理函数。
函数介绍:
下面的表格列出了 MySQL 中最重要的内建日期函数:
函数 描述
NOW() 返回当前的日期和时间
CURDATE() 返回当前的日期
CURTIME() 返回当前的时间
DATE() 提取日期或日期/时间表达式的日期部分
EXTRACT() 返回日期/时间按的单独部分
DATE_ADD() 给日期添加指定的时间间隔
DATE_SUB() 从日期减去指定的时间间隔
DATEDIFF() 返回两个日期之间的天数
DATE_FORMAT() 用不同的格式显示日期/时间
下面的表格列出了 SQL Server 中最重要的内建日期函数:
函数 描述
GETDATE() 返回当前日期和时间
DATEPART() 返回日期/时间的单独部分
DATEADD() 在日期中添加或减去指定的时间间隔
DATEDIFF() 返回两个日期之间的时间
CONVERT() 用不同的格式显示日期/时间
数据类型:
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 日期处理
如果数据库中使用的是时间,而用日期去比较的话,会得不到结果。这是由于该查询不含有时间部分的日期。
提示:如果您希望使查询简单且更易维护,那么请不要在日期中使用时间部分!
3> 大小写转换:UCASE() / LCASE()
UCASE():函数把字段的值转换为大写。
语法:
SELECT UCASE(column_name) FROM table_name
LCASE 函数把字段的值转换为小写。
语法:
SELECT LCASE(column_name) FROM table_name
注意:只对英文符号有用
4> 获取文本字段的长度:LEN() 函数
LEN 函数返回文本字段中值的长度。
语法:
SELECT LEN(column_name) FROM table_name
5> 提取字符:MID() 函数
MID 函数用于从文本字段中提取字符。
语法:
SELECT MID(column_name,start[,length]) FROM table_name
其中,column_name 是要提取字符的字段;
start规定提取的开始位置(起始值是 1);
length 可选。要返回的字符数。如果省略,则 MID() 函数返回剩余文本。
例子:
从Persons表中选取字段City中的前3位字符
SELECT MID(City,1,3) as SmallCity FROM Persons
三. 技能
1> 嵌套查询
select a.title,a.username,tempTable.adddate from
table a,(select max(adddate) adddate from table where table.title=a.title) tempTable //tempTable为(select ...)的别名
2> 在视图中查询(表名1:a )
select * from (SELECT a,b,c FROM a) T where t.a > 1;
3> 两张关联表,删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
4> 联表查询:
select * from a
left inner join b on a.a=b.b
right inner join c on a.a=c.c
inner join d on a.a=d.d
where .....
5> 1=1,1=2的使用
在SQL语句组合时用的较多:“where 1=1” 是表示选择全部;“where 1=2”全部不选
常识:
在SQL查询中:from后最多可以跟多少张表或视图:256
在SQL语句中出现 Order by 查询时,先排序,后取
在SQL中,一个字段的最大容量是8000,而对于nvarchar(4000),由于nvarchar是Unicode码。