Oracle(二)
create table score
(
SCONO NUMBER(4),
STUNO NUMBER(3),
COUNO number(4),
sco number(3),
SCONAME VARCHAR2(8)
)
ALTER TABLE SCORE ADD CONSTRAINT PK_SCONO PRIMARY KEY(SCONO)
ALTER TABLE SCORE ADD CONSTRAINT FK_STUNO FOREIGN KEY(STUNO) REFERENCES STUDENT(STUNO)
ALTER TABLE SCORE ADD CONSTRAINT FK_COUNO FOREIGN KEY(COUNO) REFERENCES COURSE(COUNO)
-------统计实体
select count(*) from score
select count(scono) from score
-------平均分
select avg(sco) from score
select avg(sco) as 平均分 from score
select avg(sco) as java平均分 from score where couno=1
--------IN
select * from score where sco in (56,36)
--------max min
select max(sco) from score ;
select max(sco) as 最大值 from score ;
select min(sco) as 最小值 from score
create table STUDENT
(
STUNO NUMBER(3),
STUNAME VARCHAR2(8),
STUSEX VARCHAR2(2),
STUAGE number(3),
STUADDRESS VARCHAR2(30),
STUTELE VARCHAR2(11)
)
-------查表
select * from student
select t.* from student t
------主键约束：
alter table student add constraint pk_stuNo primary key (stuNo)
------check约束：
alter table student add constraint ck_Stusex check (stusex='女' or stusex='男')
ALTER TABLE STUDENT ADD CONSTRAINT PK_STUNO PRIMARY KEY(STUNO)
ALTER TABLE STUDENT ADD CONSTRAINT CK_STUAGE CHECK(STUAGE>=12 AND STUAGE<=120)
ALTER TABLE STUDENT ADD CONSTRAINT CK_STUSEX CHECK(STUSEX='男' or STUSEX='女')
ALTER TABLE STUDENT MODIFY STUADDRESS DEFAULT('西安')
-------插入数据
INSERT INTO STUDENT (STUNO,STUNAME,STUSEX,STUAGE,STUTELE) VALUES(05,'小娃','男',52,'15029351330')
insert into student (stuno,stuname,stusex,stuage,stutele) values(06,'小宏','女',26,'15029351332')
------更新数据
UPDATE STUDENT SET STUNAME='娃' WHERE STUNO=5
------删除数据（不能删除）
DELETE FROM STUDENT WHERE STUNO=5
select * from student
create table course
(
couNO number(3),
COUNAME VARCHAR2(8)
)
ALTER TABLE COURSE ADD CONSTRAINT PK_COUNO PRIMARY KEY(COUNO )
alter table score add constraint pk_scono primary key (scono)
alter table student add constraint ck_stuage check (stuage>10 and stuage<=120)
alter table student add constraint ck_stusex check (stusex='男' or stusex='女')
alter table student add constraint ck_Stusex check (stusex='女'or stusex='男')
alter table score add constraint fk_stuno foreign key (stuno) references student(stuno)
------引用约束
alter table score constraint fk_stuno foreign key (stuno) references student(stuno)
------查表信息
select * from student
select t.*, t.rowid from student t
-------删除表
drop table Student
delete student
create table course
(
couNO number(4),
COUNAME VARCHAR2(8)
)
alter table student add constraint pk_stuno primary key (stuno)
alter table course add constraint pk_couno primary key (couno)
DROP TABLE SCORE
DROP TABLE COURSE
----复制
create table STUDENT_0002
(
STUNO NUMBER(3),
STUNAME VARCHAR2(8),
STUSEX VARCHAR2(2),
STUAGE NUMBER(3),
STUADDRESS VARCHAR2(30),
STUTELE VARCHAR2(11),
BIRTHDATE DATE
)
drop table student_0002
select * from student_0002;
---加入区别
insert into student (stuno) values(06)
insert into student_0002(stuno,stuname,stusex,stuage,stuaddress,stutele)select stuno,stuname,stusex,stuage,stuaddress,stutele from student
--------分组查询
select * from student
--不能用单独直接在select后加*
select stuaddress from student group by stuaddress;
select stuaddress,stuage from student group by stuaddress,stuage;
select count(*),stuaddress from student group by stuaddress;
select count(stuname),stuaddress from student group by stuaddress
--------order by 排序
select count(*),stuaddress from student group by stuaddress order by stuname desc/asc
--------having
--查询补考人员
select count(*) ,stuno,couno from score group by stuno, couno having count>=2
----中间数
select median(stuage) from student
----连接符||
----集合操作运算符union
select * from student union select * from student
--继续查询 2012/11/27
----查询显示比某个学员大的其他学院信息
-----
select * from student;
select * from score;
select stuage from student where stuno=1;
select * from student where stuage>(select stuage from student where stuno=1);
-----
select student.stuno,stuname,sco from student,score where student.stuno=score.stuno;
-----笛卡尔积结果
select student.stuno,stuname,sco from student,score
----查学号，姓名，学生科目，成绩
--方式一
select stuno,stuname,couname,sco from
(select student.stuno,stuname,sco,couno from student,score where student.stuno=score.stuno
) ss,course cou where ss.couno=cou.couno; --ss为虚拟表的别名，cou为course的别名
--方式二
select student.stuno,stuname,couname,sco from student,score,course where score.couno=course.couno and student.stuno=score.stuno
-----修改系统默认的日期格式
alter session set nls_date_format='yyyy-mm-dd';
alter session set nls_date_format='yyyy-mm-dd'
insert into student(birthdate) values ('2012-11-27') where stuno=1;
select * from student;
-----连接查询-----
-----内连接 查询参加考试的学员信息
select distinct student.stuno,stuname,stuaddress,stutele,stusex from student inner join score on student.stuno=score.stuno
select * from student;
select * from score;
select * from course;
select stuno,stuname,stusex,stuage,stuaddress from student where student.stuno in (select distinct stuno from score )--distinct 去掉重复项
---左外连接：查询没参加考试的学员信息
---左表数据全部显示，右表满足条件数据显示,
select student.stuno,stuname,stusex,stuage,stuaddress from student left join score on student.stuno=score.stuno where score.stuno is null
-----
select distinct student.stuno from student left join score on student.stuno=score.stuno where sco is null
-----右外连接
-----
-----oracle常用函数
-----字符串函数
--首字母变大写
select initcap('hello') from dual;
--lower('fjkdsf')
select lower('fjkdsf') from dual;
--ltrim(),rtrim()
select ltrim('123','1')from dual;
select rtrim('123','3')from dual;
--translate(,,)
select translate('121113','1','2')from dual;
--trim(' ')
select trim(' 12 ')from dual;
--lpad('hell',10,'x')
select lpad('hell',10,'x')from dual;
--rpad('hell',10,'x')
select rpad('hell',10,'x')from dual;
--replace('123'，'','000')
select replace('12产3'，'产','000') from dual;
--instr('hello admin','a')
select instr('hello admin','a')from dual;
--substr('hello oracle',4,6)
select substr('hello oracle',4,6)from dual;
--length('hello oracle')
select length('hello oracle')from dual;
--decode(1,2,3,4)
select decode(1,2,3,4)from dual;
--concat('hello ','oracle')
select concat('hello ','oracle')from dual;
select instr('hello admin','a')from dual;
----日期函数:
--add_months('','')
select add_months('2012-11-27',12) from dual;
--months_between('','')
select months_between('2012-11-27','1988-11-09') from dual;
--last_day('')当月最后一天
select last_day('2012-11-27') from dual;
--next_day('')
--sysdate系统日期
select sysdate from dual;
select next_day(sysdate，1) from dual
--trunc(122.2,2)
select trunc(2012.122,2) from dual
--extract(year from '2012-11-27')取到年月日的每一部分
select extract(year from sysdate) from dual
//select extract(year from '2012-11-28') from dual错误
----数字函数：
--abs()取绝对值
select abs(-120) from dual;
--ceil(12.12)大于12.12的最小整数
select ceil(12.12) from dual;
--floor()小于12.12的最大整数
select floor(12.12) from dual;
--mod(4,3)=1取余
select mod(4,3) from dual;
--sqrt()开方
select sqrt(4) from dual;
--round(10.276,2)=10.28
select from dual;
--
----转换函数：
--to_char(123345,'L9999,00')
select to_char(stuno,'L9,9999') from student;---L人民币
select to_char(stuno,'L9999.000000000') from student;
select to_char(122,'L9999.00') from dual;
select to_char(1211,'L999,00') from dual;
--to_date() 需要和日期一样位数
select to_date('20121227') from dual;
--to_number('123')
select to_number('123')+1 from dual;
-------
---nvl(stuaddress,'4545')如果没有则用4545填充
select nvl(stuaddress,'123') from student
---nvl2(EXPRESS1,EXPRESS2,EXPRESS3)如果1为null，取3，否则取2
select nvl2(stuaddress,'1不为空取此值2','1为空取此值3') from student
select * from student
----------数据库对象
--对象--表、视图、索引
-------同义词、序列、索引、视图
--同义词
--private 只能对象本身访问
create synonym show_student for admin.student;
select * from show_student
--public创建
create or replace public synonym show_student02 for admin.student;
--删除
drop synonym show_student;
--管理员授权用户创建公共访问
grant create public synonym to admin
select * from show_student02
--序列
create sequence inc_stuno
--初始值
start with 9
--每次增加多少
increment by 1
--最大值
maxvalue 999
minvalue 9
nocycle
cache 9
insert into student(stuno,stuname,stusex,stuage,stuaddress,stutele,birthdate)
values (inc_stuno.nextval,'test','男',25,'西安','112459399','2012-2-2')
----视图 view
--提供了另外一种级别的表安全性
--隐藏的数据的复杂性
--简化的用户的SQL命令隔离基表结构的改变
--通过重命名列，从另一个角度提供数据
create or replace view show_join_exam as select stuno,stuname,stusex,stuage,stuaddress from student where student.stuno in (select distinct stuno from score )
select * from show_join_exam
