oracle怎么查修改前的fun,之前学习Oracle的笔记，记录一下

每次修改数据后使用 commit; 提交数据！！！

最好不要在操作界面关闭sql，而是exit退出，否则插入的数据再次登录会消失(回滚数据)

清空界面clear scr

创建表空间

create tablespace hg

datafile 'hg.dbf' size 10M

autoextend on;

查看默认和临时表空间

select default_tablespace,temporary_tablespace from user_users;

修改默认表空间

alter user username(用户名) default tablespace test(表空间名);

查看表空间里的所有表

select TABLE_NAME,TABLESPACE_NAME from dba_tables

where TABLESPACE_NAME='表空间名';

注意：表空间名要大写HG

表设置约束

alter table student

add constraint uq_stuid unique(id);唯一约束

alter table student

add constraint 6d foreign key(majorid) references majorinfo(majorid);

外键约束

foreign key 注意数据大小定义要一致

删除表

alter tablespace tablespace_name

drop table table_name;

删除表中数据(行)

delete from table_name

where id='0001';

修改表中数据

update student

set tel='13812345678';

where id='150001';

查询表中所有数据

select * from student;

查询表中无重复全部数据

select distinct * from student;

给列设置别名

select coursename AS 课程名称(别名),price AS 课程价格(别名) from courses;

对查询结果排序

查询courses表中的课程名称、打八折后的课程价格，并按课程价格降序排序，按课程名称升序排序

select coursename as 课程名称,price*0.8 as 折后课程价格 from courses

order by 折后课程价格 DESC，课程名称 ASC；

(若折后课程价格相同，则按课程名称排序)

使用case when语句查询(对结果进行判断以显示不同的值)

select coursename as 课程名称 ,case

when price>=200 then price*0.8

when price>=100 then price*0.9

end as 价格 from courses;

查询courses，如果typeid为1001，则显示'数据库'，如果typeid为1002，则显示'编程语言'

select coursename as 课程名称，case typeid

when 1001 then '数据库'

when 1002 then '编程语言'

end as 课程类型 from courses;

模糊查询 _代替一个字符，%代替0到多字符

查询课程名称中有'Java'的课程信息

select coursename,price from courses

where coursename LIKE '%Java%';

统计每类课程的平均价格，并要求平均价格高于200

select typeid,AVG(price), from courses

group by typeid

having AVG(price)>200;  //where中不能用聚合函数

统计所有价格不低于200的每类课程的平均价格

select typeid,AVG(price) from courses

where price>=200

group by typeid;

统计每类课程平均价格，并按平均价格降序排序

select typeid,AVG(price) from courses

group by typeid

order by AVG(price) desc;  //order by必须放在查询语句最后面

笛卡尔积(行数为两张表行数相乘，列数为两张表列数相加)

select * from table_name

内连接

查询课程信息表和课程类型信息表，显示课程名称和课程类型名称

select courses.coursename,typeinfo.typeid

from courses INNER JOIN typeinfo  //INNER JOIN表示内连接

ON courses.typeid=typeinfo.typeid;  //ON后面加条件

外连接

左外连接LEFT、右外连接RIGHT、全外连接FULL

使用右连接查询课程信息表和课程类型信息表的信息

select courses.coursename,typeinfo.typename

from courses right outer join typeinfo

on courses.typeid=typeinfo.typeid;

子查询(嵌套查询)

通常用在from和where字句中

查询课程价格高于平均价格的课程信息

select coursename,price

from courses

where price>(select AVG(price) from courses);

abs函数(绝对值)

select abs(-123),abs(123) from dual;

结果：abs(-123)  abs(123)

123        123

length函数(求字符长度)

将日期型转换为字符型

select to_char(sysdate,'YYYY-MM-DD DAY HH24:MI:SS ') from DUAL;

运行结果：

TO_CHAR(SYSDATE,'YYYY-MM-DDDAY

------------------------------

2019-10-01 星期二 15:08:14

自定义函数：

创建函数

create FUNCTION fun(price NUMBER)/*参数名 数据类型*/

return NUMBER/*返回数据类型*/

IS

BEGIN/*开始*/

return price*0.6;/*实现业务逻辑语句*/

END;/*结束*/

/  /*结束符*/

算术运算符： 加+ 减- 乘* 除/ 连接||

加减乘除用于数值型的值计算，连接用于字符型的值连接，如'123'||'456'结果是123456

比较运算符： > >= < <= !=或<> = 用于两个表达式之间比较

逻辑运算符：与AND 或OR 非NOT，优先级：非>与>或

优先顺序：算数运算符>比较运算符>逻辑运算符

定义常量

constant_name constant datatype;

例：class_name constant varchar2(20):='计算机一班';

定义变量

variable_name datatype[:=value];

例：age number(3):=20;

更改变量：age：=25;

set serverout on  /*要显示输出结果必须加这条语句，执行一次即可*/

DECLARE  /*声明*/

age number(3):=20;

name varchar2(20):='张三';

BEGIN

DBMS_OUTPUT.PUT_LINE('年龄='||age);

DBMS_OUTPUT.PUT_LINE('姓名='||name);

END;

/  /*必须加斜杆才开始执行上面的语句*/

IF语句

使用if判断，若姓名是‘张三’，则输出‘正确’，否则输出‘错误’

DECLARE

name varchar2(20):='张三';

BEGIN

IF name='张三' THEN

DBMS_OUTPUT.PUT_LINE('正确')；

ELSE

DBMS_OUTPUT.PUT_LINE('错误');

END IF;

END;

/

IS/AS区别

在存储过程(procedure)和函数(function)中没有区别

在视图(view)中只能用AS

在游标(cursor)中只能用IS

create table student

(

id varchar2(10),

name varchar2(20),

majorid varchar2(20),

classid varchar2(10),

sex varchar2(2),

nation varchar2(20),

entrancedate varchar2(20),

idcard varchar2(20),

tel varchar2(20),

email varchar2(20),

remarks varchar2(100)

);

create table majorinfo

(

majorid varchar2(20),

majorname varchar2(20)

);

create table classinfo

(

classid varchar2(10),

grade varchar2(10),

classname varchar2(20)

);

create table course

(

courseid varchar2(10),

coursename varchar2(20),

credit number(3,1),

remarks varchar2(100)

);

create table gradeinfo

(

studentid varchar2(10),

courseid varchar2(10),

grade number(4,1),

semester varchar2(16),

remarks varchar2(100)

);

insert into classinfo values('1401','2014级','计算机1班');

insert into classinfo values('1302','2013级','会计1班');

insert into classinfo values('1503','2015级','自动化1班');

insert into majorinfo values('0001','计算机');

insert into majorinfo values('0002','会计');

insert into majorinfo values('0003','自动化');

insert into course values('1001','计算机基础','0.5','无');

insert into course values('1002','会计电算化','1','无');

insert into course values('1003','电子技术','1','无');

insert into student values('150001','张小林','0001','1503','男','汉','2015.9','无','13112345678','无','无');

insert into student values ('140001','王铭','0002','1401','男','回','2014.9','无','13212345678','无','无');

insert into student values('130001','吴琦','0001','1302','女','汉','2013.9','无','13312345678','无','无');

insert into gradeinfo values('150001','1001','86','2015第一学期','无');

insert into gradeinfo values('140001','1002','90','2014第二学期','无');

insert into gradeinfo values('130001','1001','92','2014第一学期','无');

create view  v_student

as select student.name,majorinfo.majorname,classinfo.classname,student.entrancedate,student.tel

from student,classinfo,majorinfo

where student.majorid=majorinfo.majorid and student.classid=classinfo.classid;

select name,majorname,classname from v_student;

create index ix_stuname

on student(name);

create bitmap index ix_stumajor

on student(majorid);

select index_name,index_type from dba_indexes where table_name='STUDENT';

使用序列添加专业信息

create sequence seq_majorid

increment by 1

start with 1

maxvalue 9999999999

minvalue 1;

insert into majorinfo values(seq_majorid.NEXTVAL,'计算机');

insert into majorinfo values(seq_majorid.NEXTVAL,'会计');

insert into majorinfo values(seq_majorid.NEXTVAL,'自动化');

select * from majorinfo;

为学生信息表创建同义词

create SYNONYM stuinfo

for system.student;

select id,name from stuinfo;