SQL语法概要

SQL语法

DML数据操作语言

基础增删改查

Select查询

select          列名称 from 表名称;//选取指定列
select          *     from 表名称;//选取所有列
select distinct 列名称 from 表名称;//选取非重指定列
select          列名称 from 表名称 where 列 运算符 值;--条件选取指定列
                                                  --(运算符:=,<>,<,>,>=,<=,between,like)
                                                  --(值:文本单引,数值不用)
select          列名称 from 表名称 where 列 运算符 值 or  列 运算符 值;--两个或条件
select          列名称 from 表名称 where 列 运算符 值 and 列 运算符 值;--两个并条件
select          列名称 from 表名称                                 order by 列名称;--从小到大摆列
select          列名称 from 表名称                                 order by 列名称 desc;--从大到小排列
select          列名称 from 表名称                                 order by 列名称 desc, 列名称 asc;

Insert插入

insert into 表名称                         values (值1,值2,...);--插入
insert into 表名称 ( 列名称1, 列名称2, ... ) values ( 值1, 值2, ... );--指定项插入

Update更新

update 表名称 set 列名称 = 值 where 列名称 = 值;更新条件行
update 表名称 set 列名称 = 值,列名称 = 值,... where 列名称 = 值;--更新条件行的若干列

Delete删除

delete   from 表名称 where 列名称 = 值;--删除条件行
delete   from 表名称;//删除所有行
delete * from 表名称;//删除所有行

增删改成扩展

Top规定要返回的记录的数目

SQL Server: select top 数字/数字 percent 列名称 from 表名称;
MySQL     : select                      列名称 from 表名称 limit 数字;
Oracle    : select                      列名称 from 表名称 where ROWNUM <= 数字;
example:select top 50 percent name from Persons;

Like用于在where子句中搜索列中的指定模式

select 列名称 from 表名称 where 列名称     like pattern;
    //pattern通配符
    //          % 为替代一个或多个字符
    //          _ 仅替代一个字符
    // [charlist] 字符列中任意单一字符
    //[^charlist] 不在字符列中的任何单一字符
    //[!charlist] 不在字符列中的任何单一字符
select 列名称 from 表名称 where 列名称 not like pattern;
example:select name from Persons where city like '[!ALN]%';

In在where子句中规定多个值

select 列名称 from 表名称 where in ( 值1, 值2, ... );

Between在where子句中使用,作用是选取介于两个值之间的数据范围

select 列名称 from 表名称 where 列名称     between 值1 and 值2;--值1,值2根据数据库决定是否包括
select 列名称 from 表名称 where 列名称 not between 值1 and 值2;

As为列名称和表名称指定别名

select 列名称          from 表名称 as 表别名;
select 列名称 as 列别名 from 表名称;

JOIN用于根据两个或多个表中的列之间的关系

inner join/join在表中存在至少一个匹配时，返回行

select 列名称 from 表名称1 inner join 表名称2 on 表名称1.列名称 = 表名称2.列名称;

example:
Persons表

Id_P	LastName	FirstName
1	Adams	John
2	Bush	George
3	Carter	Thomas

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
inner 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

left join从左表那里返回所有的行

select 列名称 from 表名称1 left join 表名称2 on 表名称1.列名称 = 表名称2.列名称;

结果

LastName	FirstName	OrderNo
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678
		34764

right join从左表那里返回所有的行

select 列名称 from 表名称1 right join 表名称2 on 表名称1.列名称 = 表名称2.列名称;

full join从左表那里返回所有的行

select 列名称 from 表名称1 full join 表名称2 on 表名称1.列名称 = 表名称2.列名称;

结果

LastName	FirstName	OrderNo
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678
Bush	George
		34764

UNION用于合并两个或多个 SELECT 语句的结果集

select 列名称 from 表名称1 union     select 列名称 from 表名称2;--不可重复列名称
select 列名称 from 表名称1 union all select 列名称 from 表名称2;--可重复列名称

SELECT INTO从一个表中选取数据，然后把数据插入另一个表中

常用于创建表的备份复件或者用于对记录进行存档

select 列名称 into 新表名称 [in 外部数据库] from 表名称
exmple:
select Persons.LastName,Orders.OrderNo
into Persons_Order_Backup
from Persons
inner join Orders
on Persons.Id_P=Orders.Id_P;

DDL数据定义语言

Database库

Create Database创建数据库

Create Database 库名称;

Table表

Create Table 创建数据库中的表

create table 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
)
example:
create table Persons
(
Id_P      int,
LastName  varchar(255),
FirstName varchar(255),
Address   varchar(255),
City      varchar(255)
)

可使用INSERT INTO句向空表写入数据

约束

• NOT NULL

example:
create table Persons
(
Id_P      int not null,
LastName  varchar(255) not null,
FirstName varchar(255),
Address   varchar(255),
City      varchar(255)
)

• 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)
)

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 )--uc_PersonID约束名
)

MySQL/SQL Server/Oracle/MS Access:
--当表已被创建时，如需在 "Id_P" 列创建 UNIQUE 约束，请使用下列 SQL
alter table Persons
add unique(Id_P)
--命名 UNIQUE 约束
alter table Persons
add constraint uc_PersonID unique (Id_P,LastName)
--撤销unique约束
--MySQL:
alter table
drop index uc_PersonID

--Oracle/SQL Server/MS Access:
alter table Persons
drop constraint uc_PersonID

• PRIMARY KEY约束唯一标识数据库表中的每条记录(唯一,不为null)

MySQL:
create table Person
(
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 Person
(
Id_P int not null primary key,
LastName varchar(255) not null,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

MySQL / SQL Server / Oracle / MS Access:
create table Person
(
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 )
)

alter table Persons
add primary key(Id_P)

alter table Persons
add constraint pk_PersonID primary key(Id_P,LastName)

alter table Persons 
drop primary key

alter table Persons
drop contraint pk_PersonID

• FOREIGN KEY

create table Orders
(
foreign key(Id_P) references Persons(Id_P)
)

• CHECK

create table Persons
(
check(Id_P>0)
)

• DEFAULT

create table Persons
(
City varchar(255) default 'Sandnes'
)

Create Index创建索引

create        index index_name on table_name( column_name )
create unique index index_name on table_name( column_name )      --唯一索引
create        index index_name on table_name( column_name desc ) --降序
create        index index_name on table_name( column_name1, column_name2 )

Drop 删除索引、表和数据库

alter table table_name drop index index_name--MySQL

drop table 表名称

drop database 数据库名称

truncate table 表名称--删除数据

Alter 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

Auto Increment生成唯一数字

MySQL:
create table Persons
(
P_Id int not null auto_increment,
primary key(P_Id)
)
alter table Persons auto_increment = 100
SQL server:
create table Persons
(
P_Id int primary key identity
)
create table Persons
(
P_Id int primary key identity(20,10)--起始20,递增10
)
Access:
create table Persons
(
P_Id int primary key autoincrement
)
create table Persons
(
P_Id int primary key autoincrement(20,10)--起始20,递增10
)
Oracle:
create sequence seq_person minvalue 1 start with 1 increment by 1 cache 10--起始1,递增1,缓存10

Create View基于SQL语句的结果集的可视化表

创建:
create view [view_name] as select column_names from table_name where condition
使用:
select * from [view_name]
更新:
create  view [view_name] as select column_names from table_name where condition
replace view [view_name] as select column_names from table_name where condition
删除:
drop view [view_name]

Date函数

MySQL Date:

函数	说明	例子
now()	返回当前日期和时间	now()
curdate()	返回当前日期	curdate()
curtime()	返回当前时间	curtime()
date()	提取日期或者日期/时间表达式的日期部分	date(date_name)
extract()	返回日期/时间的单独部分	extract( year unit from date_name )
date_add()	给日期添加指定的时间间隔	date_add(date_name, interval 2 day)
date_sub()	从日期减去指定的时间间隔	date_sub(date_name, interval 2 day)
datediff()	返回两个日期之间的天数	datediff(date_name1,date_name2)
date_format()	用不同的格式显示日期/时间	date_format(date_name, ‘%Y-%m-%d’)

NULL值

select LastName, FirstName, Address from Persons where Address is null
select LastName, FirstName, Address from Persons where Address is not null

select ProductName, UnitPrice*(UnitsInStock+isnull(UnitsOnOrder,0)) from Products--SQL server/MS Access
select ProductName, UnitPrice*(UnitsInStock+nvl(UnitsOnOrder,0)) from Products--Oracle
select ProductName, UnitPrice*(UnitsInstock+ifnull(UnitsOnOrder,0)) from Products--MySQL
select ProductName, UnitPrice*(UnitsInstock+coalesce(UnitsOnOrder,0)) from Products--MySQL

SQL函数

合计函数

函数	描述
avg(column)	返回某列的平均值
count(column)	返回某列的行数(不包含NULL)
count(*)	返回被选行数
first(column)	返回在指定的域中第一个记录的值
last(column)	返回在指定的域中最后一个记录的值
max(column)	返回某列的最高值
min(column)	返回某列的最低值
sum(column)	返回某列的总和

Scalar函数

函数	描述
ucase(column)	将某个域转化为大写
lcase(column)	将某个域转化为小写
mid(c,start,length)	从某个文本域提取字符
len(c)	返回某个文本域的长度
instr(c,char)	返回在某个文本域在指定字符的数值位置
left(c)	返回某个被请求的文本域的左侧部分
right(c,number)	返回某个被请求的文本域的右侧部分
round(c,decimals)	对某个数值域进行指定小数位数的四舍五入
mod(x,y)	返回除法操作的余数
now()	返回当前的系统日期
format(c,format)	改变某个域的显示方式
datediff(d,date1,date2)	用于执行日期计算

group by 和having

group by 用于结合合计函数，根据一个或多个列对结果集进行分组

select column_name, aggregate_function(column_name)
from table_name
where column_name operator value
group by column_name;

example:
select Custormer, sum( OrderPrice ) from Orders group by customer

having 用于WHERE 关键字与合计函数一起使用

select column_name, aggregate_function(colum_name)
from table_name
where column_name operator value
group by column_name
having aggregate_function( column_name ) operator value

example:
select Customer, sum(OrderPrice) from Orders group by Customer having sum( OrderPrice ) < 2000;