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;