SQL编程及高级查询

存储

        1. 表

                字段：一个事物的某一静态特征

                记录：字段的组合==>具体的事物

                表：记录的组合==>同一类事物的集合

                      字段是事物的属性、记录是事物本身、表是事物的集合

                      列==属性==字段；元组==表；

        2. create table

        3. 约束：对一个表中属性操作的权限叫做约束

                主键约束：避免数据冗余

                外键约束：引用完整性【事物与事物之间的关系】

                check约束：保证事物属性取值在合法的范围

                唯一约束（unique）：事物取值不能重复，但允许为空

                default约束：保证事物的属性一定会有一个值

                not null：非空

        4. 表与约束的关系

                表—事物的存储

                约束—事物取值的有效、合法

        5. 什么是关系？

                一对一

                一对多

                多对多

        6. 主键

                确定唯一事物的字段组合

        7. 外键

              指定表与表之间的关系

数据类型

        字符类型：

                char(length) :存储固定长度的字符串，小于用空间填空

                varchar2(length) ：可变的字符串

                nchar/nvarchar2(length) ：Unicode字符集类型

                long(length) ：可变长字符串

        数值类型:

                number(p,s) ：整数和浮点,p表示数字的最大位数，s表示小数位数

        日期:

                date : 时间和日期

                timestamp ： 日期秒值精确到秒后6位

        LOB类型:

                blob ：二进制对象

                clob ：字符格式的大型数据

                bfile ：二进制数据文件

SQL语言的分类

        数据定义语言DDL：create(创建)、alter(修改)、drop(删除)

        数据操纵语言DML:insert(插入)、update(更新)、delete(删除)、select(查询)

        事务控制语言TCL：commit(提交)、savepoint(保存点)、rollback(回滚)

        数据控制语言DCL：grant(授权)、revoke(撤销)

数据定义语言DDL

        创建表：表中数据默认存放在用户的默认表空间中，也可以通过指令指定表空间

--创建表
create table student(
       id number(10),
       name varchar2(10),
       bir date
)
--指定表空间
tablespace dataspace;
commit;
 
--删除表
drop table student;
commit;
 
 
--重命名表
alter table student rename to students;
commit;

        字段

--增加字段
alter table students add(address nvarchar2(10));
commit;
--删除字段
alter table students drop column address;
--删除字段
alter table students drop(address);
--修改字段
alter table students modify address nvarchar2(20);
commit;

        约束

                非空约束not null：限制必须为某字段赋值

--非空约束
alter table students modify address not null;
commit;

                默认约束defalut：取值的完整性

--默认约束
alter table students modify(bir date default to_date('1990-1-1','yyyy-mm-dd'));
commit;

                主键约束primary key：唯一标示

--主键约束
alter table students add constraint PK_ID primary key(id);
commit;

                唯一约束unique:不允许重复

--唯一约束
alter table students add constraint UN_NAME unique(name);
commit;

                检查约束check:保证取值的范围

--检查约束
alter table students add constraint CK_AGE check(age>15);
commit;

                外键约束foreign key: 表的关系

--外键约束
alter table students add constraint FK_CLS foreign key(cls) references classs(id);
commit;

                约束操作

--禁用约束
alter table students disable constraint UN_NAME;
commit;
--激活约束
alter table students enable constraint UN_NAME;
commit;
--删除约束
alter table students drop constraint FK_CLS;
commit;

 

数据操纵语言DML

        数据操作

--插入数据
insert into students values(1 ,'张三',18,'12-6月-1996','上海',2);
insert into students values(2,'李四',20,default,'北京',3);
insert into students(id,name) values(3,'micro');
insert into students select * from student;
commit;
 
--更新数据
update students set bir='01-5月-1998' where id=2;
commit;
 
--删除数据
delete from students where id=2;
commit;
 
--查询数据
select * from students;

        操作符

                算数运算:+  -  *  /  注:没有取余运算符

--算数运算
select empno ,ename,sal,sal*12+2000 from emp;

                关系运算符:等于  不等于<> !=  大于  小于

--关系运算
select * from emp where sal>1000 ;

                逻辑运算:and  or  not

--逻辑运算
select * from emp where sal>1500 or comm>100;

        序列:字段实现自动编号功能

--创建序列
create sequence seq_id
--指定从0开始
start with 1
--每次增长1
increment by 1
--最大值200
maxvalue 200
--达到最大值不循环
nocycle
--每次生成10个序列
cache 10
--按照顺序递增
order;
commit;
 
 
--修改序列
alter sequence seq_id
nomaxvalue;
commit;
 
 
--删除序列
drop sequence seq_id;
commit;
 
--使用序列
insert into students values(seq_id.nextval,'micro',18,'23-7月-1994','广东',3);
insert into students values(seq_id.nextval,'myth',20,default,'newyork',3);
select seq_id.nextval from dual;
select seq_id.currval from dual;

        查询:scott

select * from emp;

               1. 计算列

  --计算列
 

select empno ,ename,sal,sal*12+2000 ysal from emp;

                2. distinct 不允许重复

--消除重复行
 
select distinct deptno from emp;

               3. between  and  查询指定范围包括边界

--between and
 
select * from emp where sal between 1000 and 2300;

                4. in 【属于若干个孤立的值】

--in 操作
 
select * from emp where deptno in(10,20);

                5. 条件查询where

--条件查询
 
select * from emp where sal>2000 or comm is not null;

               6. null 【空值】

                   null 可以参与 is、 is not；

--null值查询
 
select * from emp where comm is null;

               7. order by 【排序】 asc升序；desc降序

--排序
 
select * from emp order by sal asc;--升序,asc可省略
 
select * from emp order by sal desc;--降序

               8. 模糊查询

                  通配符：

                      %  表示任意0个或多个字符

                       _【下划线】  任意单个字符

                       [a-f]  a到f中任意单个字符

                       [a,f]  a或f

                       [^a-c]  非a-c的单个字符

--模糊查询
 
select * from emp where ename like '_A%';

                9. 聚合函数[多行函数，通常用于同于分组的信息]

                   单行—>>一行返回一个值

                  多行—>>多行返回一个值

                  聚合函数是多行函数

                       max( )；min( )；avg( )；count( )；sum( )

--聚合函数
 
select count(empno) from emp;--总数
 
select max(sal) from emp;--最大
 
select min(sal) from emp;--最小
 
select avg(sal) from emp;--平均数
 
select sum(sal) from emp;--求和

               10. group by [把表中记录按字段分别成不同的组]

--group by
 
select job ,count(job)  from emp group by job;

               11. having[对分组之后的信息过滤]

--having
 
select job,avg(sal) from emp group by job having avg(sal)>1500;

        12. 连接查询 join  on

                子查询

--子查询
 
select * from emp where deptno=(select deptno from emp where ename='SCOTT');

                   内连接

                       inner内连接: 只查询满足条件的部分

--内连接
 
select * from emp inner join dept on  emp.deptno=dept.deptno;

                   外连接

                       left: 查询左表所有部分,右边满足条件的部分

                       right: 查询右表所有部分,左边满足条件的部分

--左外连接
 
select * from emp left join dept on emp.deptno=dept.deptno;
 
--右外连接
 
select * from emp right join dept on emp.deptno=dept.deptno;
                   完全连接  full:左右均不受限制,全查询

--完全连接
 
select * from emp full join dept on emp.deptno=dept.deptno;

        13.伪列: 一个表列，但是它并没有存储在表中

--伪列:rowid  表中行的存储地址
 
select rowid ,empno,ename from emp;
 
--伪列:rownum 结果集中行的序号
 
select rownum ,empno,ename from emp;

         rownum为结果集中标识一个行号,并不会直接生成;所以rownum大于某一整数,不会返回结果

         rownum实现分页:   每页为3条记录,查询第三页

select * from
 
       (select rownum R,emp.* from emp) where
 
       R>(3-1)*3 and R<=3*3
 
       ;
 
/*
  内部查询得到rownum,并且用别名标记记录,供外层使用
*/

        集合运算

                交集:  intersect

--交集:前表与后表重复部分
 
select deptno from emp
 
intersect
 
select deptno from dept;

                并集:  union,不包含重复

                         union all,包含重复

--并集:不包含重复部分
 
select deptno from dept
 
union
 
select deptno from dept;

--并集:包含重复部分
 
select deptno from dept
 
union all
 
select deptno from dept;

                minus:补集

--补集:前表去掉后表的部分
 
select deptno from dept
 
minus
 
select deptno from emp;