SQL学习资料总结

SQL语句：
Select-从数据库表中获取数据
Update-更新数据库表中的数据
Delete-从数据库表中删除数据
Insert into-向数据库表中插入数据

Create database-创建新数据库
Alter database-修改数据库
Create table-创建新表
Alter table-变更数据库表
Drop table-删除表
Create index-创建索引
Drop index-删除索引

Select distinct company from orders;//distinct去除重复值
Select company , OrderNumber from orders order by company desc , OrderNumber asc;降序、升序排列
Insert into Persons(LastName , Address) values(‘aa’ , ‘bb’);//插入数据
Update Person set Address = ‘aa’;//更新数据
Delete from Person where LastName=’aa’;//删除一行
Delete * from Person;//全部删除
Select TOP 2 * from Persons;//取前二条数据
等价于：select * from Persons limit 2;
ORACLE：select * from Persons where rownum <= 2;
Select TOP 50 PERCENT * from Persons;//从表中选取50%的记录
Select * from Persons where FirstName like ‘_eorge’;//查询第一个字符后是eorge的数据
Select * from Persons where city like ‘[ALN]%’;//查询居住的城市以A、L、或N开头的人(取反用’![ALN]%’)
Select * from Persons where LastName in (‘Adams’ , ‘Carter’);
Select p.LastName , o.OrderNo from Persons p inner join Orders o on p.Id_P = o.Id_p order by p.LastName;//内连接
Join: 如果表中有至少一个匹配，则返回行
Left join: 即使右表中没有匹配，也从左表返回所有的行
Right join: 即使左表中没有匹配，也从右表返回所有的行
Full join: 只要其中一个表中存在匹配，就返回行
Select * into Persons_backup from Persons;//把数据复制到另一个表
Select * into Persons in ‘Backup.mdb’ from Persons;//向另一个数据库中拷贝表
Select LastName,FirstName into Persons_backup from Persons;
Select p.LastName , o.OrderNo into Persons_Order_Backup from Persons as p inner join Orders as o on p.id_p = o.id_p;//从一个以上表中选取数据存入新表

Create database my_db;//创建数据库
Create table Persons{id_p int , LastName varchar(255) , FirstName varchar(255)}//创建表
Id_p int not null,//强制不能为NULL
Create table Persons{
Id_p int not null unique,//也可以写成这样
LastName varchar(255) not null,
Unique(id_p)//唯一约束
Constraint uc_PersonID unique(id_p , LastName)//为多个列定义unique约束
}
Alter table Persons add unique(id_p);//为已存在的列增加unique约束
Alter table Persons add constraint uc_personID unique(id_p , LastName);//为多列增加unique
MySQL:alter table Persons drop index uc_PersonID;
其他：alter table Persons drop constraint uc_PersonID;//撤销unique约束
Create table Persons{
Id_p int primary key,
LastName varchar(255) not null,
FirstName varchar(255),
Primary key(id_p)//也可以这样写
Constraint uc_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);//为多列增加主键
MySQL：Alter table Persons drop primary key;//撤销主键
其他：Alter table Persons drop constraint pk_PersonID;

外键 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) preferences Persons(id_p)
)
其他：
Create table Orders(
O_id int not primary key,
orderNo int not null,
id_p int foreign key preferences Persons(id_p)
constraints fk_PerOrders foreign key(id_p) references Persons(id_p)也可以这样写
)
增加外键：
Alter table Orders add foreign key(id_p) references Persons(id_p);
Alter table Orders add constraints fk_PerOrders foreign key(id_p) references Persons(id_p);增加多个外键
撤销foreign key约束：
MySQL：Alter table Orders drop foreign key fk_PerOrders;
其他：alter table Orders drop constraints fk_PerOrders;

Check约束：
Create table Persons(
Id_p int not null check(id_p > 0),//也可以这样写
LastName varchar(255) not null,
FirstName varchar(255),
City varchar(255),
Check(id_p > 0)
Constraint chk_Person check(id_p > 0 and city=’Sandnes’)定义多个列的check约束
);
增加check约束：
Alter table Persons add check(id_p > 0);
Alter table Persons add constraint chk_Person check(id_p > 0 and city=’Sandnes’);多个约束撤销约束：
其他：Alter table Persons drop constraint chk_Person;
MySQL：alter table Persons drop check chk_Person;

Default约束：
Create table Persons(
Id_p int not null,
LastName varchar(255) not null,
FirstName varchar(255),
City varchar(255) default ‘Sandnes’
);
新增default约束：
MySQL：Alter table Persons alter city set default ‘sandnes’;
其他：alter table Persons alter column city set default ‘sandnes’;
撤销default约束：
MySQL：Alter table Persons alter city drop default;
其他：Alter table Persons alter column city drop default;

创建索引：
Create index PersonIndex on Person(LastName);
Create index PersonIndex on Person(LastName desc);
Create index PersonIndex on Person(LastName , FirstName);

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;
SQL：drop table table_name;
Drop database database_name;
Truncate table table_name;删除表内容，不删除表结构

Alter table Persons add Birthday date;增加一列
Alter table Persons alter column birthday year;修改列的数据类型
Alter table Person drop column birthday;删除一列

MySQL：Create table Persons(
P_id int not null auto_increment,//开始值是1，第条新记录递增1
LastName varchar(255) not null,
FirstName varchar(255),
Primary key(p_id)
);
Alter table Persons auto_increment = 100;//以100开始
SQL Server：
Create table Persons(
P_id int primary key identity,//开始值是1，第条新记录递增1
LastName varchar(255) not null,
FirstName varchar(255),
Primary key(p_id)
);
把identity改成identity(20 , 10)表示从20开始，每次递增10
Oracle：
Create sequence seq_person
Minvalue 1
Start with 1
Increment by 1
Cache 10//缓存为10个值，提高效率
Insert into Persons(p_id , FirstName , LastName) values(seq_person.nextval , ‘Lars’,’Monsen’);插入一条记录

SQL create view视图：基于SQL语句的结果集的可视化的表
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]; 选取 Products 表中所有单位价格高于平均单位价格的产品
Drop view view_name;删除视图
Select LastName , Address from Persons where Address is null;//选取Address列中null值的记录(也可以写成is not null)
SQL Server/MS Access：
Select ProductName,UnitPrice*(UnitsInStock + isnull(UnitsOnOrder , 0)) from products;//假如UnitsOnOrder为null值，则用返回0进行计算
Oracle：
Select ProductName,UnitPrice*(UnitsInStock + NVL(UnitsOnOrder , 0)) from products;
MySQL：
Select ProductName,UnitPrice*(UnitsInStock + ifnull(UnitsOnOrder , 0)) from products;//也可以用coalesce函数

SQL函数：
AVG(column);返回某列的平均值
COUNT(column);返回某列的行数
COUNT(*);返回被选行数
COUNT(DISTINCT column);返回相异结果的数目
FIRST(column);返回第一个记录的值
LAST(column);返回最后一个记录的值
MAX(column);返回某列的最高值
MIN(column);返回某列的最低值
SUM(column);返回某列的总和
UCASE(c);转换为大写
LCASE(c);转换为小写
MID(c , start[,end]);从某个文本域提取字符
LEN(c);返回某个文本域的长度
INSTR(c , char);返回在某个文本域中指定字符的数值位置
LEFT(c , number_of_char);返回某个被请求的文本域的左侧部分
RIGHT(c , number_of_char);返回某个被请求的文本域的右侧部分
ROUND(c , decimals);对某个数值域进行指定小数位数的四舍五入
MOD(x , y);返回除法操作的余数
NOW();返回当前的系统时间
FORMAT(c , format);改变某个域的显示方式
DATEDIFF(d , date1 , date2);用于执行日期计算

Select Customer,SUM(OrderPrice) from Orders group by Customer having SUM(OrderPrice) < 2000;// 查找订单总金额少于 2000 的客户
Select UCASE(LastName) as LastName , FirstName from Persons;// 选取LastName和 FirstName列的内容，然后把 "LastName" 列转换为大写
Select MID(City , 1 ,3) as SmallCity from Persons;// 从City列中提取前 3 个字符(从1开始)
Select LEN(City) as LengthOfCity from Persons;
Select ProductName , ROUND(UnitPrice , 0) as UnitPrice from Persons;//把价格舍入为最接近的整数
Select ProductName , UnitPrice , FORMAT(Now() , ‘YYYY-MM-DD’) as PerDate from Products;