TOP子句:
top子句用于规定要返回的记录的数目,对于数量庞大的table很有用,但不是所有的DBS都支持top子句。
SQL server 的语法:(" | "表示语法项选择其中一项)
select top number | percent column_name(s)
from table_name
MySQL和Oracle中的 SQL Server Top 是等价的
MySQL语法:
select column_name
from table_name
limit number
eg: select *
from persons
limit 5
Oracle语法:
select column_name
from table_name
where rownum<=nunmber
eg: select *
from persons
where rownum <= 5
eg: select top 2 * from persons 从persons表中选取头两条记录
select top 50 percent * from persons 从persons表中选取50%的记录
LIKE 操作符:
LIKE 操作符用于在where子句中搜索列中的指定模式
语法:
select column_name(s)
from table_name
where column_name like pattern
eg:select * from persons
where city like ‘N%' "%" 可用于定义通配符(模式中缺少的字母)
从persons 表中选取居住在以“N”开始的城市里的人
select * from persons
where city like ‘%g'
从persons 表中选取居住在以“g”结尾的城市里的人
select * from persons
where city like ‘%lon%'
从persons 表中选取居住在包含“lon”的城市里的人
select * from persons
where city not like ‘%lon%'
从persons 表中选取居住在不包含“lon”的城市里的人
SQL通配符:
通配符可以替代一个或者多个字符,且必须和like运算符一起使用
%——代替一个或者多个字符
_ ——仅代替一个字符
[charlist]——字符列中的任何单一字符
[^charlist]或者[!charlist]——不在字符列中的任何单一字符
eg: select * from persons
where city like ‘N%'
从persons 表中选取居住在以“N”开始的城市里的人
select * from persons
where city like ‘%lon%'
从persons 表中选取居住在包含“lon”的城市里的人
select * from persons
where firstname like ‘_eorge'
从persons 表中选取名字的第一个字符之后是eorge的人
select * from persons
where lastname like ‘C_e_er'
从persons 表中选取姓的第一个字符C,然后一个任意字符,接着是e,然后一个任意字符,接着是er的人
select * from persons
where city like '[ALN]%'
从Persons表中选取居住的城市以 "A" 或 "L" 或 "N" 开头的人
select * from persons
where city like '[!ALN]%'
从Persons表中选取居住的城市不以 "A" 或 "L" 或 "N" 开头的人
IN操作符:
in操作符允许我们在where子句中规定多个值
语法:
select column_name(s)
from table_name
where column_name in (value1,value2,……)
eg: select * from persons
where lastname in ‘adams,carter'
从persons 表中选取姓是adams和carter的人
BETWEEN操作符:
在where子句中使用,作用是选取介于两个值之间的数据范围
between…and选取介于两个值之间的数据范围,这些值可以是数值、文本或者日期
语法:
select column_name(s)
from table_name
where column_name
between value1 and value2
eg: select * from persons
where lastname
between ‘adams' and ‘carter'
以字母顺序显示介于 "Adams"(包括)和 "Carter"(不包括)之间的人
注意:不同的数据库对 BETWEEN...AND 操作符的处理方式是有差异的。某些数据库会列出介于 "Adams" 和 "Carter" 之间的人,但不包括 "Adams" 和 "Carter" ;某些数据库会列出介于 "Adams" 和 "Carter" 之间并包括 "Adams" 和 "Carter" 的人;而另一些数据库会列出介于 "Adams" 和 "Carter" 之间的人,包括 "Adams" ,但不包括 "Carter" 。
select * from persons
where lastname
not between ‘adams' and ‘carter'
以字母顺序显示不介于 "Adams"(包括)和 "Carter"(不包括)之间的人
SQL Alias(别名)
通过使用SQL,可以为列名称和表名称制定别名Alias。别名是查询程序更易阅读和书写
表的 SQL Alias 语法:
select column_name(s)
from table_name
as alias_name
列的 SQL Alias 语法:
select column_name(s) as alias_name
from table_name
eg:
使用表名称别名
persons 和 product_orders两个表,分别命名p,po
希望列出 john adams的所有订单
select po.order id, p.lastname, p.firstname
from persons as p, product_orders as po
where p.lastname='adams', p.firstname='john'
不使用别名
select product_orders.order id, persons.lastname, persons.firstname
from persons, product_orders
where persons.lastname='adams', persons.firstname='john'
使用一个列名别名
select lastname as family, firstname as name
from persons
JOIN:
join用于两个或多个表中列之间的关系,从这些表中查询数据
为了得到完整的结果,需要从两个或多个表中获取,就需要用到join
数据库中的表可以通过键来进行连接, primary key 主键是一个列,列中每一行的值都是唯一的
在表中,每个主键的值都是唯一的,目的是在不重复每个表中的所有数据的情况下,把表间的数据交叉整合在一起
eg:引用两个表,谁订购了产品,订购了什么产品
select persons.lastname, persons.firstname, orders.orderno
from persons, orders
where persons.id_p=orders.id_p
INNER JOIN 关键字:
表中至少存在一个匹配时,inner join 关键字返回行.inner join 和 join 是相同的
语法:
select column_name(s)
from table_name1
inner join table_name2
on table_name1.column_name=table_name2.column_name
eg:列出所有人的订购
select persons.lastname, persons.firstname, orders.orderno
from persons
inner join orders
on persons.id_p=orders.id_p
order by persons.lastname
INNER JOIN 关键字在表中存在至少一个匹配时返回行。如果 "Persons" 中的行在 "Orders" 中没有匹配,就不会列出这些行。
inner join 内连接
join:如果表中至少有一个匹配,则返回行
left join:即使右表中没有匹配,也从左表返回所有的行
right join:即使左表中没有匹配,也从右表返回所有的行
full join:只要其中一个表存在匹配,就返回行
left join 关键字:
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行,即使在右表 (table_name2) 中没有匹配的行。
语法:某些数据库里left join 称为 left outer join
select column_name(s)
from table_name1
left join table_name2
on table_name1.column_name=table_name2.column_name
eg:左链接实例。列出所有人及他们的订购,如果有的话
select persons.lastname, persons.firstname, orders.orderno
from persons
left join orders
on persons.id_p=orders.id_p
order by persons.lastname
LEFT JOIN 关键字会从左表 (Persons) 那里返回所有的行,即使在右表 (Orders) 中没有匹配的行,也会列出左表有的一行。
right join 关键字:
RIGHT JOIN 关键字会从右表 (table_name2) 那里返回所有的行,即使在左表 (table_name1) 中没有匹配的行。
语法:某些数据库里right join 称为 right outer join
select column_name(s)
from table_name1
right join table_name2
on table_name1.column_name=table_name2.column_name
eg:右链接实例。列出所有人订单及订购它们的人,如果有的话
select persons.lastname, persons.firstname, orders.orderno
from persons
right join orders
on persons.id_p=orders.id_p
order by persons.lastname
RIGHT JOIN 关键字会从右表 (Orders) 那里返回所有的行,即使在左表 (Persons) 中没有匹配的行,也会列出右表有的一行。
full join 关键字:
full JOIN 关键字只要其中表存在匹配,就会返回行。
语法:某些数据库里fulljoin 称为 fullouter join
select column_name(s)
from table_name1
full join table_name2
on table_name1.column_name=table_name2.column_name
eg:全链接实例。列出所有的人和他们的订单,以及所有人订单及订购它们的人,如果有的话
select persons.lastname, persons.firstname, orders.orderno
from persons
full join orders
on persons.id_p=orders.id_p
order by persons.lastname
FULL JOIN 关键字会从左表 (Persons) 和右表 (Orders) 那里返回所有的行。如果 "Persons" 中的行在表 "Orders" 中没有匹配,或者如果 "Orders" 中的行在表 "Persons" 中没有匹配,这些行同样会列出。
UNION操作符:
union用于合并两个或多个select语句的结果集
union内部的select语句必须拥有相同数量的列,列也必须拥有相似的数据类型,每条select语句中列的顺序必须相同
语法:默认union操作符选取不同的值,如果允许重复的值,用union all,结果集中的列名总是等于第一个select中的列名
select column_name(s) from table_name1
union
select column_name(s) from table_name2
eg:列出所有在美国和中国的不同的雇员名
select e_name from employees_china
union
select e_name from employees_usa
列出所有在美国和中国的所有雇员名
select e_name from employees_china
union all
select e_name from employees_usa
SELECT INTO 语句:
select into 语句可用于创建表的备份文件。
select into 语句从一个表中选取数据,然后把数据插入另一个表中
select into 语句用于创建表的备份复件或者用于对记录进行存档
语法:
把所有列插入新表
select *
into new_table_name [in externaldatabase]
from old table_name
只把希望的列插入新表
select column_name(s)
into new_table_name [in externaldatabase]
from old table_name
eg:
制作备份复件
select *
into persons_backup
from persons
向另一个数据库中拷贝表
select *
into persons_backup in ‘backup.mdb’
from persons
如果我们希望拷贝某些域,可以在 SELECT 语句后列出这些域:
select lastname,firstname
into persons_backup
from persons
带有where子句:
通过从 "Persons" 表中提取居住在 "Beijing" 的人的信息,创建了一个带有两个列的名为 "Persons_backup" 的表
select lastname,firstname
into persons_backup
from persons
where city=‘beijing’
被连接 的表:
创建一个名为 "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
CREATE DATABASE 语句:
用于创建数据库
语法:create database database_name
CREATE TABLE 语句:
用于创建数据库中的表
语法:create table 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
……
)
数据类型:
integer(size),int(size),smallint(size),tinyint(size):仅容纳整数,括号内为规定数字的最大位数
decimal(size,d), numeric(size,d): 仅容纳带有小数的数字,size规定数字的最大位数,d规定小数点右侧的最大位数
char(size):容纳固定长度的字符串(可容纳字母、数字以及特殊字符)。在括号中规定字符串的长度。
varchar(size):容纳可变长度的字符串(可容纳字母、数字以及特殊字符)。在括号中规定字符串的最大长度。
eg:
create table persons
(
id_p int,
lastname varchar(255),
firstname varchar(255),
address varchar(255),
city varchar(255)
)
SQL 约束 (constraints):
约束用于限制加入表的数据类型
可以在创建表时规定约束(通过 CREATE TABLE 语句),或者在表创建之后也可以(通过 ALTER TABLE 语句)。
我们将主要探讨以下几种约束:
- NOT NULL
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
- CHECK
- DEFAULT
NOT NULL约束:
限制列不接受null值,强制字段始终包含值。意味着如果不向字段添加值,就不能插入新纪录或者更新记录
eg:
create table persons
(
id_p int not null,
lastname varchar(255) not null,
firstname varchar(255),
address varchar(255),
city varchar(255)
)
UNIQUE约束:
唯一标识数据库表中的每条记录
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
PRIMARY KEY 拥有自动定义的 UNIQUE 约束。
请注意,每个表可以有多个 UNIQUE 约束,但是每个表只能有一个 PRIMARY KEY 约束。
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)
)
当表已经被创建,需要在id_P列创建unique约束:
MYSQL / SQL SERVER / Oracle / MS access:
alter table persons
add unique (id_p)
如需命名unique约束,并定义多个列的unique约束:
MYSQL / SQL SERVER / Oracle / MS access:
alter table persons
add constraint uc_personID unique(id_p, lastname)
撤销unique约束:
MYSQL:
alter table persons
drop index uc_personID
SQL SERVER / Oracle / MS access:
alter table persons
drop constraint uc_personID
PRIMARY KEY 约束:
PRIMARY KEY 约束唯一标识数据库中的每条记录
主键必须包含唯一的值,不能有null值,每个表有且仅有一个主键
在 "Persons" 表创建时在 "Id_P" 列创建 PRIMARY KEY 约束:
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 pk_personID primary key (id_p, lastname)
)
当表已经被创建,需要在id_P列创建primary key约束:
MYSQL / SQL SERVER / Oracle / MS access:
alter table persons
add primary key (id_p)
如需命名primary key约束,并定义多个列的primary key约束:
MYSQL / SQL SERVER / Oracle / MS access:
alter table persons
add constraint pk_personID primary key(id_p, lastname)
注释:如果使用 ALTER TABLE 语句添加主键,必须把主键列声明为不包含 NULL 值(在表首次创建时)。
撤销primary key约束:
MYSQL:
alter table persons
drop primary key
SQL SERVER / Oracle / MS access:
alter table persons
drop constraint pk_personID
FOREIGN KEY 约束:
一个表中的foreign key 指向另一个表中的primary key
用于防治破坏表之间的连接,防治非法数据插入外键列,因为它必须是它指向的那个表中的值之一
在 "Persons" 表创建时在 "Id_P" 列创建 foreign key 约束:
MYSQL:
create table orders
(
id_0 int not null ,
orderno int not null,
id_p int,
primary key (id_o),
foreign key (id_p) references persons(id_p)
)
SQL SERVER / Oracle / MS access:
create table orders
(
id_0 int not null primary key,
orderno int not null,
id_p int foreign key references persons(id_p)
)
命名foreign key 约束,以及为多个列定义foreign key 约束:
MYSQL / SQL SERVER / Oracle / MS access:
create table orders
(
id_0 int not null ,
orderno int not null,
id_p int,
primary key (id_o),
constraint fk_perorders foreign key (id_p)
references persons(id_p)
)
当表已经被创建,需要在id_P列创建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)
撤销primary key约束:
MYSQL:
alter table persons
drop foreign key fk_perorders
SQL SERVER / Oracle / MS access:
alter table persons
drop constraint fk_perprders
CHECK约束:
用于限制列中的值的范围
如果对单个列定义check约束,那么该列只允许特定的值
如果对一个表定义check约束,那么这个约束会在特定的列中对值进行限制
在persons表创建时给id_p列创建check约束,规定该列必须包含只大于0的整数
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约束
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),
constraint chk_persons check (id_p>0 and city ='sandnes'
)
在表已经存在的情况下,为id_p列创建check约束:
MY SQL / SQL SERVER / ORACLE / MS ACCESS:
alter table persons
add check (id_p>0)
需要命名check列约束,以及为多个列命名check约束:
MY SQL / SQL SERVER / ORACLE / MS ACCESS:
alter table persons
add constraint chk_person check(id_p>0 and city ='sandnes'
撤销check约束:
MY SQL:
drop check chk_person
SQL SERVER / ORACLE / MS ACCESS:
alter table persons
drop constraint chk_person
DEFAULT约束:
用于向列中插入默认值,如果没有规定其他的值,会将默认值添加到所有的新纪录
在persons表创建时为city列创建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'
)
通过使用类似getdate()这样的函数,default约束也可以用于插入系统值:
create table orders
(
id_0 int not null ,
orderno int not null,
id_p int ,
orderdate date default getdate()
)
在表已经存在的情况下,为city列创建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 SERBER / ORACLE / MS ACCESS:
alter table persons
alter column city drop default
CREATE INDEX语句:
用于在表中创建索引,在不读取整个表的情况下,索引可以更快的查找数据
用户无法看到索引,它们只能被用来加速搜索/查询
更新一个包含索引的表需要比更新一个没有索引的表更多的时间,这是因为索引本身也需要更新,理想的做法是在常常被搜索的列(以及表)上面创建索引。
语法:
在表上创建一个简单的索引,允许使用重复的值
create index index_name
on table_name (column_name)
注:column_name 规定需要索引的列
在表上创建一个唯一索引,唯一的索引意味着两个行不能拥有相同的索引值
create unique index index_name
on table_name (column_name)
eg:
创建一个personindex索引,在person表的lastname列
create index personindex
on person(lastname)
希望以降序索引某个列中的值,可以在列名称之后添加保留字DESC
create index personindex
on person(lastname DESC)
希望索引不止一个列,可以在括号中列出这些列的名称
create index personindex
on person(lastname,firstname)
DROP语句:
删除索引、表和数据库
drop index 删除表格中的索引:
用于 MicrosoftSQLJet(以及Microsoft Access)的语法:
drop index index_name on table_name
用于MS SQL server的语法:
drop index table_name.index_name
用于IBM DB2和Oracle语法:
drop index index_name
用于MYSQL的语法:
alter table table_name drop index index_name
drop table 用于删除表(表的结构、属性以及索引也会被删除):
drop table 表名称
drop database 用于删除数据库:
drop database 数据库名称
truncate table仅删除表格中的数据,不删除表本身:
truncate table 表名称
alter table语句:
用于在已有的表中添加、修改或者删除列
添加列:
alter table table_name
add column_name datatype
删除列:
alter table table_name
drop column column_name
注:某些数据库不允许上面删除列的方式 用下面的
alter table table_name
drop column column_name datatype
eg:
添加列
alter table persons
add birthday date 新列类型是日期date
改变列的数据类型
alter table persons
alter column birthday year 可以存放2位或4位格式的年份
删除列
alter table persons
drop column birthday
AUTO INCREMENT 字段
auto-increment会在新纪录插入表时生成一个唯一的数字
通常希望在每次插入新纪录时,自动的创建主键字段的值
可以在表中创建一个auto-increment字段
MYSQL:
把persons表中的p_id列定义为auto-increment主键:
create table persons
(
p_id int not null auto-increment,
lastname varchar(255) not null,
firstnamr varchar(255),
address varchar(255),
primary key (p_id)
)
默认的auto-increment开始值是1,每条新纪录递增1
从其他的值开始递增:
alter table persons auto-increment=100
在persons表中插入新纪录,不必为p_id列规定值(会自动添加一个唯一的值)
insert into persons (firstname, lastname)
values('bill', 'gates') firstname会被设置为bill , lastname列会被设置为gates
SQL SERVER:
把persons表中的p_id列定义为auto-increment主键:
create table persons
(
p_id int primary key identity,
lastname varchar(255) not null,
firstnamr varchar(255),
address varchar(255)
)
MS SQL 使用identity关键字来执行auto-increment任务
默认identity的开始值是1,每条记录递增1
要规定p_id列以20开始依次递增10 ,把identity改为identity(20,10)
在persons表中插入新纪录,不必为p_id列规定值(会自动添加一个唯一的值)
insert into persons (firstname, lastname)
values('bill', 'gates') firstname会被设置为bill , lastname列会被设置为gates
Access语法:
把persons表中的p_id列定义为auto-increment主键:
create table persons
(
p_id int primary key autoincrement,
lastname varchar(255) not null,
firstnamr varchar(255),
address varchar(255)
)
MS ACCESS使用autoincrement关键字来执行auto-increment任务
默认identity的开始值是1,每条记录递增1
要规定p_id列以20开始依次递增10 ,把autoincrement改为autoincrement(20,10)
在persons表中插入新纪录,不必为p_id列规定值(会自动添加一个唯一的值)
insert into persons (firstname, lastname)
values('bill', 'gates') firstname会被设置为bill , lastname列会被设置为gates
Oracle语法:
必须通过sequence对创建auto-increment字段(该对象生成数字序列)
create sequence语法:
create sequence seq_person
minvalue 1
start with 1
increment by 1
cache 10
上面的代码创建名为seq_person的序列对象,以1开始以1递增。该对象缓存10个值以提高性能。cache选项规定了为了提高访问速度需要存储多少个序列值。
要在persons表中插入新纪录,我们必须使用nextval函数(该函数从seq_person序列中取回下一个值):
insert into persons(p_id, firstname,lastname)
values (seq_person.nextval, 'lars', 'monsen') firstname设置为lars,lastname设置为monsen
VIEW视图:
视图是基于sql语句的结果集的可视化的表
视图包含行和列,就像一个真实的表。视图中的字段就是来自一个或者多个数据库中的真实的表中的字段。我们可以向视图添加sql函数,where以及join语句,也可以提交数据,就像这些来自于某个单一的表
注:数据库的设计和结构不会受到视图中的函数、where、join语句的影响。
create view 语法:
create view view_name as
select column_name(s)
from table_name
where condition
注:视图总是显示最近的数据,每当用户查询视图时,数据库引擎通过使用sql语句来重建数据。
eg:
样本数据库northwind拥有一些被默认安装的视图。视图current product list 会从products表列出所有正在使用的产品
create view [current product list] as
select productID,produetname
from products
where discontinued=no
我们可以查询上面这个视图:
select * from [current product list]
northwind样本数据库的另一个视图会选取products表中所有单位价格高于平均单位价格的产品
create view [products above average price] as
select productname, unitprice
from products
where unitprice>(select avg(unitprice) from products)
我们可以查询上面这个视图:
select * from [products above average price]
另一个视图实例会计算在1997年每个种类的销售总数。这个视图会从另一个名为“product sales for 1997”的视图哪里选取数据:
create view [category sales for 1997] as
select distinct categoryname,sum(productsales) as categorysales
from [product sales for 1997]
group by categoryname
查询上面这个视图:
select * from [category sales for 1997]
若仅查看beverages类的全部销量;
select * from [category sales for 1997]
where categoryname=‘beverages’
SQL更新视图:
sql create or replace view syntax
create or replace view_name as
select column_name(s)
from table_name
where condition
向current product list 视图添加category列
create view [current product list] as
select productID,productname,category
from products
where discontinued=no
SQL撤销视图:
sql drop view syntax
drop view view_name
DATE函数:
在处理日期时,主要是要确保所插入的日期的格式,与数据库中日期列额的格式相匹配
只要数据包含的只是日期部分,运行查询就不会出问题。但是,如果涉及时间,情况就有点复杂了。
MYSQL中最重要的内建日期函数:
NOW():返回当前的日期和时间
CURDATE():返回当前的日期
CURTIME():返回当前的时间
DATE():提取日期或日期/时间表达式的日期部分
EXRACT():返回日期/时间的单独部分
DATE_ADD():给日期添加指定的时间间隔
DATE_SUB():给日期减去指定的时间间隔
DATEDIFF():返回两个日期之间的天数
DATE_FORMAT():用不同的格式显示日期/时间
SQL Server Date中最重要的内建日期函数:
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
SAMLLDATATIME - 格式:YYYY-MM-DD HH:MM:SS
YEAR - 格式:唯一的数字
SQL日期处理:
eg:从orders表中选取orderdate为2018-12-26的记录(表中没有时间,只有日期)
select * from orders where orderdate=‘2018-12-26’
该查询不能查含有时间部分的日期,如果希望查询简单且容易维护,就不要在日期中使用时间部分!
NULL值
NULL值是遗漏的未知数据,默认表的列可以存放null值
如果表中某个列是可选的,那么我们可以在不向该列添加值的情况下,插入新纪录或者更新已有的记录。这意味着该字段将以null值保存
null值处理方式与其他值不同,null用作未知的或不适用的值的占位符
无法比较null和0,它们是不等价的
eg:
仅仅选取address列中带有null的值,不能用比较运算符=,<,<>来测试null,需要用 is null 操作符
select lastname ,firstname ,address from persons
where address is null
我们始终用is null 来查找 null 值
仅仅选取address列中不带有null的值,必须用is not null
select lastname ,firstname ,address from persons
where address is not null
NULL函数:
P_Id | ProductName | UnitPrice | UnitsInStock | UnitsOnOrder |
---|---|---|---|---|
1 | computer | 699 | 25 | 15 |
2 | printer | 365 | 36 | |
3 | telephone | 280 | 159 | 57 |
假如 "UnitsOnOrder" 是可选的,而且可以包含 NULL 值。
我们使用如下 SELECT 语句:
select productname , unitprice*(unitsinstock+unitsonorder)
from products
在上面的例子中,如果有 "UnitsOnOrder" 值是 NULL,那么结果是 NULL。
微软的 ISNULL() 函数用于规定如何处理 NULL 值。
NVL(), IFNULL() 和 COALESCE() 函数也可以达到相同的结果。
在这里,我们希望 NULL 值为 0。
下面,如果 "UnitsOnOrder" 是 NULL,则不利于计算,因此如果值是 NULL 则 ISNULL() 返回 0
SQL server / ms access:
select productname , unitprice*(unitsinstock+isnull(unitsonorder,0))
from products
oracle:
oracle没有isnull()函数,用nvl()函数
select productname , unitprice*(unitsinstock+nvl(unitsonorder,0))
from products
MY SQL:
select productname , unitprice*(unitsinstock+ifnull(unitsonorder,0))
from products
select productname , unitprice*(unitsinstock+coalesce(unitsonorder,0))
from products
SQL数据类型:
Microsoft Access 数据类型
数据类型 | 描述 | 存储 |
---|---|---|
Text | 用于文本或文本与数字的组合。最多 255 个字符。 | |
Memo | Memo 用于更大数量的文本。最多存储 65,536 个字符。 注释:无法对 memo 字段进行排序。不过它们是可搜索的。 | |
Byte | 允许 0 到 255 的数字。 | 1 字节 |
Integer | 允许介于 -32,768 到 32,767 之间的数字。 | 2 字节 |
Long | 允许介于 -2,147,483,648 与 2,147,483,647 之间的全部数字 | 4 字节 |
Single | 单精度浮点。处理大多数小数。 | 4 字节 |
Double | 双精度浮点。处理大多数小数。 | 8 字节 |
Currency | 用于货币。支持 15 位的元,外加 4 位小数。 提示:您可以选择使用哪个国家的货币。 | 8 字节 |
AutoNumber | AutoNumber 字段自动为每条记录分配数字,通常从 1 开始。 | 4 字节 |
Date/Time | 用于日期和时间 | 8 字节 |
Yes/No | 逻辑字段,可以显示为 Yes/No、True/False 或 On/Off。 在代码中,使用常量 True 和 False (等价于 1 和 0) 注释:Yes/No 字段中不允许 Null 值 | 1 比特 |
Ole Object | 可以存储图片、音频、视频或其他 BLOBs (Binary Large OBjects) | 最多 1GB |
Hyperlink | 包含指向其他文件的链接,包括网页。 | |
Lookup Wizard | 允许你创建一个可从下列列表中进行选择的选项列表。 | 4 字节 |
MySQL 数据类型
在 MySQL 中,有三种主要的类型:文本、数字和日期/时间类型。
Text 类型:
数据类型 | 描述 |
---|---|
CHAR(size) | 保存固定长度的字符串(可包含字母、数字以及特殊字符)。在括号中指定字符串的长度。最多 255 个字符。 |
VARCHAR(size) | 保存可变长度的字符串(可包含字母、数字以及特殊字符)。在括号中指定字符串的最大长度。最多 255 个字符。 注释:如果值的长度大于 255,则被转换为 TEXT 类型。 |
TINYTEXT | 存放最大长度为 255 个字符的字符串。 |
TEXT | 存放最大长度为 65,535 个字符的字符串。 |
BLOB | 用于 BLOBs (Binary Large OBjects)。存放最多 65,535 字节的数据。 |
MEDIUMTEXT | 存放最大长度为 16,777,215 个字符的字符串。 |
MEDIUMBLOB | 用于 BLOBs (Binary Large OBjects)。存放最多 16,777,215 字节的数据。 |
LONGTEXT | 存放最大长度为 4,294,967,295 个字符的字符串。 |
LONGBLOB | 用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。 |
ENUM(x,y,z,etc.) | 允许你输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值,则插入空值。 注释:这些值是按照你输入的顺序存储的。 可以按照此格式输入可能的值:ENUM('X','Y','Z') |
SET | 与 ENUM 类似,SET 最多只能包含 64 个列表项,不过 SET 可存储一个以上的值。 |
Number 类型:
数据类型 | 描述 |
---|---|
TINYINT(size) | -128 到 127 常规。0 到 255 无符号*。在括号中规定最大位数。 |
SMALLINT(size) | -32768 到 32767 常规。0 到 65535 无符号*。在括号中规定最大位数。 |
MEDIUMINT(size) | -8388608 到 8388607 普通。0 to 16777215 无符号*。在括号中规定最大位数。 |
INT(size) | -2147483648 到 2147483647 常规。0 到 4294967295 无符号*。在括号中规定最大位数。 |
BIGINT(size) | -9223372036854775808 到 9223372036854775807 常规。0 到 18446744073709551615 无符号*。在括号中规定最大位数。 |
FLOAT(size,d) | 带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。 |
DOUBLE(size,d) | 带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。 |
DECIMAL(size,d) | 作为字符串存储的 DOUBLE 类型,允许固定的小数点。 |
* 这些整数类型拥有额外的选项 UNSIGNED。通常,整数可以是负数或正数。如果添加 UNSIGNED 属性,那么范围将从 0 开始,而不是某个负数。
Date 类型:
数据类型 | 描述 |
---|---|
DATE() | 日期。格式:YYYY-MM-DD 注释:支持的范围是从 '1000-01-01' 到 '9999-12-31' |
DATETIME() | *日期和时间的组合。格式:YYYY-MM-DD HH:MM:SS 注释:支持的范围是从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59' |
TIMESTAMP() | *时间戳。TIMESTAMP 值使用 Unix 纪元('1970-01-01 00:00:00' UTC) 至今的描述来存储。格式:YYYY-MM-DD HH:MM:SS 注释:支持的范围是从 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC |
TIME() | 时间。格式:HH:MM:SS 注释:支持的范围是从 '-838:59:59' 到 '838:59:59' |
YEAR() | 2 位或 4 位格式的年。 注释:4 位格式所允许的值:1901 到 2155。2 位格式所允许的值:70 到 69,表示从 1970 到 2069。 |
* 即便 DATETIME 和 TIMESTAMP 返回相同的格式,它们的工作方式很不同。在 INSERT 或 UPDATE 查询中,TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式,比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。
SQL Server 数据类型
Character 字符串:
数据类型 | 描述 | 存储 |
---|---|---|
char(n) | 固定长度的字符串。最多 8,000 个字符。 | n |
varchar(n) | 可变长度的字符串。最多 8,000 个字符。 | |
varchar(max) | 可变长度的字符串。最多 1,073,741,824 个字符。 | |
text | 可变长度的字符串。最多 2GB 字符数据。 |
Unicode 字符串:
数据类型 | 描述 | 存储 |
---|---|---|
nchar(n) | 固定长度的 Unicode 数据。最多 4,000 个字符。 | |
nvarchar(n) | 可变长度的 Unicode 数据。最多 4,000 个字符。 | |
nvarchar(max) | 可变长度的 Unicode 数据。最多 536,870,912 个字符。 | |
ntext | 可变长度的 Unicode 数据。最多 2GB 字符数据。 |
Binary 类型:
数据类型 | 描述 | 存储 |
---|---|---|
bit | 允许 0、1 或 NULL | |
binary(n) | 固定长度的二进制数据。最多 8,000 字节。 | |
varbinary(n) | 可变长度的二进制数据。最多 8,000 字节。 | |
varbinary(max) | 可变长度的二进制数据。最多 2GB 字节。 | |
image | 可变长度的二进制数据。最多 2GB。 |
Number 类型:
数据类型 | 描述 | 存储 |
---|---|---|
tinyint | 允许从 0 到 255 的所有数字。 | 1 字节 |
smallint | 允许从 -32,768 到 32,767 的所有数字。 | 2 字节 |
int | 允许从 -2,147,483,648 到 2,147,483,647 的所有数字。 | 4 字节 |
bigint | 允许介于 -9,223,372,036,854,775,808 和 9,223,372,036,854,775,807 之间的所有数字。 | 8 字节 |
decimal(p,s) | 固定精度和比例的数字。允许从 -10^38 +1 到 10^38 -1 之间的数字。 p 参数指示可以存储的最大位数(小数点左侧和右侧)。p 必须是 1 到 38 之间的值。默认是 18。 s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。 | 5-17 字节 |
numeric(p,s) | 固定精度和比例的数字。允许从 -10^38 +1 到 10^38 -1 之间的数字。 p 参数指示可以存储的最大位数(小数点左侧和右侧)。p 必须是 1 到 38 之间的值。默认是 18。 s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。 | 5-17 字节 |
smallmoney | 介于 -214,748.3648 和 214,748.3647 之间的货币数据。 | 4 字节 |
money | 介于 -922,337,203,685,477.5808 和 922,337,203,685,477.5807 之间的货币数据。 | 8 字节 |
float(n) | 从 -1.79E + 308 到 1.79E + 308 的浮动精度数字数据。 参数 n 指示该字段保存 4 字节还是 8 字节。float(24) 保存 4 字节,而 float(53) 保存 8 字节。n 的默认值是 53。 | 4 或 8 字节 |
real | 从 -3.40E + 38 到 3.40E + 38 的浮动精度数字数据。 | 4 字节 |
Date 类型:
数据类型 | 描述 | 存储 |
---|---|---|
datetime | 从 1753 年 1 月 1 日 到 9999 年 12 月 31 日,精度为 3.33 毫秒。 | 8 bytes |
datetime2 | 从 1753 年 1 月 1 日 到 9999 年 12 月 31 日,精度为 100 纳秒。 | 6-8 bytes |
smalldatetime | 从 1900 年 1 月 1 日 到 2079 年 6 月 6 日,精度为 1 分钟。 | 4 bytes |
date | 仅存储日期。从 0001 年 1 月 1 日 到 9999 年 12 月 31 日。 | 3 bytes |
time | 仅存储时间。精度为 100 纳秒。 | 3-5 bytes |
datetimeoffset | 与 datetime2 相同,外加时区偏移。 | 8-10 bytes |
timestamp | 存储唯一的数字,每当创建或修改某行时,该数字会更新。timestamp 基于内部时钟,不对应真实时间。每个表只能有一个 timestamp 变量。 |
其他数据类型:
数据类型 | 描述 |
---|---|
sql_variant | 存储最多 8,000 字节不同数据类型的数据,除了 text、ntext 以及 timestamp。 |
uniqueidentifier | 存储全局标识符 (GUID)。 |
xml | 存储 XML 格式化数据。最多 2GB。 |
cursor | 存储对用于数据库操作的指针的引用。 |
table | 存储结果集,供稍后处理。 |
SQL服务器-RDBMS
现代的 SQL 服务器构建在 RDBMS 之上。
DBMS - 数据库管理系统(Database Management System)
数据库管理系统是一种可以访问数据库中数据的计算机程序。
DBMS 使我们有能力在数据库中提取、修改或者存贮信息。
不同的 DBMS 提供不同的函数供查询、提交以及修改数据。
RDBMS - 关系数据库管理系统(Relational Database Management System)
关系数据库管理系统 (RDBMS) 也是一种数据库管理系统,其数据库是根据数据间的关系来组织和访问数据的。
20 世纪 70 年代初,IBM 公司发明了 RDBMS。
RDBMS 是 SQL 的基础,也是所有现代数据库系统诸如 Oracle、SQL Server、IBM DB2、Sybase、MySQL 以及
Microsoft Access 的基础。