sql语言基础

sql语言基础
1.概念
一般格式：
>>select [distinct/all]...
from...
where...
group by...having...
[union/intersect/minus]
order by...[asc/desc]
别名：select (price+0.15) retail或者select price+0.15 as retail
测试数据值是否为null只能用"is null"不能用"="。
字符运算符：like,%,_,||。
集合运算符：union(∩),intersect(∪),minus(−)。
sql可以组合常规列与聚合函数，要求有group by子句：
>>select pay sum(amount)
from checks
group by pay
where不能使用聚合函数，应使用having：
>>select team,avg(salary)
from orgchart
group by team
having arg(salary)<3800
连接表不创建物理表，而是建立虚拟表。
使用子查询时要确保子查询返回唯一值。
聚合函数count返回满足条件的行数。
创建数据库：
>>creat database A;
>>creat table A(
name char(30) not null
amount number);
创建数据表：
>>creat table A(name,amount)
as (
select * from B
where amount<50);
添加表列：
>>alter table A
add food char(40);
修改列：
>>alter table A
modify name char(40);
数据库完整性控制：
非空约束：not null
主键：primary key
唯一：unique
外键：foreign key A(name) references B(name);
检验：constraint A check (name in(Tom,Bill));
索引
SQL使用树状结构存储和检索索引的数据，以提高效率。
建立单列索引：
creat index id_index on bills(account_id);
建立多列索引：
creat index id_cmpd_index on bills(account_id,amount);
建立唯一索引：
creat unique index id_index on bills(account_id);
建立聚簇索引：（在主键上建立）
creat unique clustered index id_index on bills(account_id);
游标的作用选择一组数据，保存查询结果。
触发器：特殊的存储过程。

2.举例
查询列：
>>select stu_id,name,grade
from stu_info
>>select name as '姓名'
from stu_info
>>select top 5 stu_id,name
from stu_info
查询行：
通配符：%，_，[ ]，[^]
>>select stu_id,name
from stu_info
where sex='male' and mark>550
>>select *
from stu_info
where address is null
>>select *
from stu_info
where sdept in('信息学院'，'会计学院')
>>select *
from stu_info
where name like '张%'
>>select *
from stu_info
where name like '[张王]%'
排序：
>>select *
from stu_grade
where course_if='701'
order by grade desc
聚合函数：
函数名称：min,max,sum,avg,count
>>select max(grade),min(grade),avg(grade),sum(grade)
from stu_grade
where stu_id='2007170102'
分组汇总：
>>select stu_id,max(grade),min(grade),avg(grade),sum(grade)
from stu_grade
group by stu_id
>>select stu_id,max(grade),min(grade),avg(grade),sum(grade)
from stu_grade
group by stu_id
having avg(grade)>87
>>select *
from stu_info
compute count(stu_id)
连接查询：
>>select stu_info.stu_id,name,course_name,grade
from stu_info,stu_grade,course_info
where stu_info.stu_id=stu_grade.stu_id and course_info.course_id=stu_grade.course_id
>>select A.stu_id,name,course_name,grade
from stu_info as A,stu_grade as B,course_info as C
where A.stu_id=B.stu_id and B.course_id=C.course_id
>>select R1.stu_id,R1.name,R1.mark
from stu_info as R1,stu_info as R2
where R2.stu_id='2007070101' and R1.mark>R2.mark
>>select stu_info,stu_id,name,grade
from stu_info inner join tu_grade on stu_info.stu_id=stu_grade.stu_id
嵌套查询：
>>select *
from stu_grade
where course_id=
(select course_id
from course_info
where course_name='计算机基础')
>>select *
from stu_grade
where stu_id in
(select stu_id
from stu_id
from stu_info
where sdept='会计学院')
>>select *
from stu_grade
where exists
(select *
from course_info
where stu_grade.course_id=course_id)
集合查询：
集合运算符：union,except,intersect
>>select stu_id,name,mark,sdept
from stu_info
where sdept='信息学院'
union
select stu_id,name,mark,sdept
from stu_info
where mark>550