SQL 功能简单总结

SQL 简单介绍

数据定义功能

创建数据库

create database
create database sales

使用数据库

use
use sales

删除数据库

drop database
drop database sales

创建表

create table <表名> （<列名>，数据类型，列级完整性约束>，<列名>，数据类型，列级完整性约束,...,表级完整性约束）
create table sales(cid nvarchar(255) not null primary key,cname nvarchar(255),city nvarchar(255),[distinct] float, check ([distinct])>0,)
create table products(pid nvarchar(255) not null, pname nvarchar(255),city nvarchar(255),primary key (pid),foreign key(cid) references sales(cid),)

修改表

增加列

alter table sales
    add email nvarchar(255) not null

修改列

alter table sales
    alter column email nvarchar(255) not null primary key

删除列

alter table sales
    drop column email

删除表

drop table <表名>
drop table sales

数据查询功能

简单查询

选择表中的列

select * from sales                                         #查询全部记录
select pid, pname from sales where pid="001"                #查询条件值
select distinct pid from order where pname="Bob"          #消除重复值
select pid, quantity* price totalqty from products            #查询经过计算的值

条件查询

单个条件查询
select aid,aname from agents where city="new york"
注意对于布尔值，true 的否定为false，false的否定为true，但是unknown的否定仍然为unknown

确定范围查询
select aid, aname from agents where price between 5 and 10            
多重条件查询
select aid, aname from agents where price >5 and price <10

模糊查询
select aid, aname from agents where aname like "A%"
select aid, aname from agents where aname like "B"
select aid, aname from agents where aname like "[A-P]ers"
select aid, aname from agents where aname like "[^a]gents"

空值查询处理
select * from agents
    where aid is not null

聚合函数
select count (distinct cid) from customers
select sum(dollars) as totaldollars from orders
select avg(dollars) from orders     #只能用于数值列，忽略空值，不允许使用聚合函数和子查询
select max(dollars) from orders    #不能用于bit列，不允许使用聚合函数和子查询
select min(dollars) from orders    # 不能用于Bit 列，不允许使用聚合函数和子查询

查询结果排序
asc 升序排列，不指定默认升序
 select *  from customers
 order by cname (asc)

降序排列
select * from customers
order by cname desc

查询结果分组
select * from customers 
    groupby cname,cid

查询结果分组后加条件
select * from customers
    groupby cname ,cid 
having cid >"001"

连接查询

等值连接和非等值连接

selec distinct customer.cname agents.aname from customers, orders, agents
    where customers.cid=orders.cid and orders.aid=agents.aid

select c1.cid cid1 c2.cid cid2 from customers c1 customers c2 where c1.city=c2.city and c1.cid<c2.cid

内连接

select 列名1 列名2 from 表名1 inner join 表名2 on 连接条件1 inner join 表名2 on 连接条件2，其中inner 可以省略

select distinct c.cname a.aname from customers c join orders o on c.cid=o.cid join agents a on o.aid=a.aid

外连接

外连接为左外连接，右外连接，即 left outer join 和 right outer join ，左外连接以左边的表作为主表进行连接，其中outer可以省略
select c.cid c.cname o.pid from customers c left join order o on c.cid=o.cid

嵌套查询

in 谓词查询 和not in 谓词查询

select distinct cid from orders where aid in 
   

比较运算符的子查询

select pid from products p
    where 2<

量化比较谓词的子查询

select aid from agents
    where [percent] < all (select [percent] from agents)

exits 谓词的子查询(返回真或者假值)

select distinct cid from products
where pid="01" and exits 
（select * from orders o where cid =o.cid）

集合查询

并 (union 删除重复行，union all 保留重复行)

select city from customers
union
select city from agents

交

select cid from orders
intersect
select cid from orders

差

select cid from orders where pid>3
except
select cid from orders where pid>5

数据操纵功能

插入数据

插入单条数据

insert into agents(aid, aname,city)
values("a11","smith","dalas")

插入子查询结果

insert into cust1
select * from customers

更新数据

update sales
set [percent]=6
where aid="a01"

删除数据

delect from agents
where city="new york"

视图和索引功能

视图

视图的创建和使用

create view cust101 as
select c.cid, c.cname
from customers c orders o
where c.cid=o.cid

视图的更新

(注意with check options选项，并注意哪些情况下不允许更新视图)
update custs101 set distinct=distinct*4

视图的删除

drop view cust1

索引

索引的创建

create unique index aidx on agents(aid)

索引的删除

drop index agents.aidx

数据控制功能

权限

授予权限

(注意 with check option 选项)
grant insert on agents to wintest

收回权限

revoke insert on agents from wintest