empinfo Oracle数据库,数据库原理学习笔记(oracle下练习)

数据库原理学习笔记(oracle下练习)：

SQL原理学习笔记

1) 向数据库中插入多条记录

INSERT INTO TABLE_NAME(colum1,colum2,colum3)

SELECT t.colum1,t.colum2,t.colum3

from TABLE_NAME2 t

where ...;

2) 向数据库中插入一条记录

INSERT INTO TABLE_NAME(id,name,sex)

VALUES(myId,myNAme,mySex)

3)修改数据库中的数据

UPDATE table

SET newvalue

WHERE criteria;

table 表名，该表包含了要修改的数据。

newvalue 表达式，该表达式确定将要插入到已更新记录的特定字段内的值。

criteria 表达式，用来确定将更新哪些记录。只有满足该表达式的记录才会被更新。

例如：update  student0136

set stdentsex = 'a'

where studentId ='1';

select * from  student0136

4)删除数据库中的记录

DELETE FROM 表名称 WHERE 条件

DELETE FROM table_name

WHERE column_name = some_value

可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的：

DELETE FROM table_name

或者：DELETE * FROM table_name

truncate table table_name

数据库查询语句

1) 查询出info_emp中所有信息

select * from info_emp

2) 查询info_emp中有效的员工信息

select * from info_emp where is_used=‘1’

3) 查询出用户编号、用户名、性别，并按照用户名升序、性别降序进行排列

select emp_id,emp_name,gender from info_emp

order by emp_name asc,gender desc

asc为升序，desc为降序，数据库默认的为升序排序

1) 给info_emp表命一个别名

select a.* from info_emp a

表示info_emp表的别名为 a

2) 给info_emp中的字段命一个别名

select a.emp_id emp_no, a.emp_name name

from info_emp a

此时，字段emp_id的别名为emp_no，字段emp_name的别名为name，info_emp表的别名为 a

另外，还可以有如下写法，中间价格as

select a.emp_id as emp_no, a.emp_name as name from info_emp a

多表关联操作

1)将info_emp和info_dept进行关联，查询出员工编号、员工名、所属部门(名称)的列表，并按照部门名称和员工姓名进行排序

select a.id,a.name,b.name form info_emp a,info_dept b

where a.deptId = b.deptId

order by b.name,a.name

左外连接

SELECT * FROM bb A

LEFT JOIN bbb C ON A.NAME1 = c.NAME

或

select * from bb a,bbb b

where a.NAME1=b.NAME(+)

右外连接

SELECT *

FROM bb A

right JOIN bbb C ON A.NAME1 = c.NAME

或

select * from bb a,bbb b

where a.NAME1(+)=b.NAME

全外连接

SELECT * FROM bb A

full JOIN bbb C ON A.NAME1 = c.NAME

其他sql

1) 返回前N行数据

select * from info_emp where rownum < 10

表示返回前9条的数据，rownum是数据库中的关键字

2) 去掉重复值：

select distinct dept_id from info_emp

表示去掉字段dept_id有重复的，只返回的结果中只保留一条记录。

3) 数据库模糊查询

select * from info_emp where emp_name like '张%'

集合函数的应用

1) 函数COUNT()用来统计一个表中有多少条记录。

例如：统计info_emp中员工的数量

select count(*) emp_nums from info_emp;

2) 函数AVG()可以返回一个字段中所有值的平均值。

注意：函数AVG()只能对数值型字段使用。这个函数在计算平均值时也忽略空值。

3) 计算字段值的和使用函数SUM()。

注意：函数SUM()的返回值代表某个字段中所有值的总和。

例如：统计某个部门员工的基本工资总额

select sum(a.salary_money) total_money

from info_salary a, info_emp b

where a.emp_id = b.emp_id and a.item_id ='00' and a.dept_id = '001' ;

4) 函数MAX()和函数MIN()分别用于返回最大值和最小值

注意：函数MIN()返回一个字段的所有值中的最小值。如果字段是空的，函数MIN()返回空值。

创建一张表

create table bbbb(

stuId varchar2(20) not null,

sex number,

constraint  PK_STU_ID primary key(stuId) constraint table_name primary key()

);

insert into bbbb

values('20044246','100')

删除一张表

drop table bbbb

修改表的名称

alter table result013 rename to result0136;

select * from result0136

向表格中增加一列

alter table result0136 add clollge varchar2(20);

UPDATE result0136

SET clollge= 'jsj'

select * from result0136

where clollge='jsj'

修改列的类型

alter table result0136 modify clollge  varchar2(32)；

修改列的名称

alter table result0136 rename column clollge to college;

select * from  result0136

删除一列

alter table result0136 drop column college;

select * from  result0136

集合函数的使用

select b.techerid 老师编号,

sum(b.xueshi) 总学时,sum(b.xuefen) 总学分, min(b.xueshi) 最小学时, min(b.xuefen)最小学分,

max(b.xueshi)最大学时,max(b.xuefen) 最大学分

from couser0136 b

group by b.techerid

查找前2条数据

select * from couser0136

where rownum < 3

模糊查询

select *

from couser0136

where xueshi like '8%'

count函数的使用

select count(a.couserid)

from couser0136 a

where xueshi like '8%'

avg函数使用

select avg(a.xueshi) 平均学时

from couser0136 a

sum求和函数

select sum(a.xueshi) 总学时 from couser0136 a

min和max函数的使用

select min(a.xueshi) 最小学时,max(a.xueshi) 最大学时 from couser0136 a

case....when的使用

select couserid, cousername, xuefen ,

case

when xueshi >= 90 then 'a'

when xueshi >=80 then 'b'

when xueshi >=40 then 'c'

else 'd' end xueshi

from couser0136

其中( case when xueshi >= 90 then 'a' when xueshi >=80 then 'b' when xueshi >=40 then 'c'

else 'd' end )相当一个字段。

decode函数的使用

select couserid, cousername, xuefen ,

case

when xueshi >= 90 then 'a'

when xueshi >=80 then 'b'

when xueshi >=40 then 'c'

else 'd' end xueshi,decode(techerid,'10','100','1') techerid

from couser0136

如果techerid = 10，输出100，否则输出1，techerid为数据库中的字段。

select decode(a.techerid,10,'100',11,'101',12,'102',13,'103') techerid

from couser0136 a

如果techerid=10，输出100，techerid=11，输出101，techerid=12，输出102，techerid=13，输出103，

select decode(a.techerid,11,'100',12,'101',13,'102','103') techerid

from couser0136 a

如果techerid=11，输出100，techerid=12，输出101，techerid=13，输出102，其余的输出103

把表格中的列相加：

select nvl(aaa,0)+nvl(bbb,0)+nvl(ccc,0)+ nvl(ddd,0) from AAA

创建索引

create index idx_stuId on result0136 (studentid);

删除索引

drop index idx_stuId

分析函数row_number()的使用：

按dep_id分组，然后按emp_id给每组降序排序。

select * from (select dep_id,emp_id,phone,row_number() over(partition by dep_id order by emp_id desc) rw from info_emp0136 )

where rw=1;

分析函数rollup的使用：

select nvl(dep_id,'合计') dep_id,sum(phone) phone

from info_emp0136

group by rollup(dep_id)

select case

when a.dep_id is null then '合计'

else a.dep_id end dep_id,a.phone

from

( select dep_id,sum(phone) phone from info_emp0136

group by rollup(dep_id)) a

select dep_id,sum(phone) phone from info_emp0136

group by dep_id

union all

select '合计' dep_id,sum(phone) phone

from   info_emp0136

分析函数cube的使用：

select dep_id,degree_id,sum(phone) from info_emp0136

group by cube(dep_id,degree_id)

order by dep_id, degree_id nulls last

//从多维的角度考虑

lag和lead函数的使用

select emp_id,item_id,salary_money,

lag(salary_money,1,0) over(partition by emp_id order by item_id) a,

lead(salary_money,1,0) over (partition by emp_id order by item_id ) b

from info_salary_ly

first_value分析函数的使用

select bill_month,area_code,sum(local_fare) local_fare,

first_value(area_code)over (order by sum(local_fare) desc

rows unbounded preceding ) firstval,

first_value(area_code)over(order by sum(local_fare) asc

rows unbounded preceding ) lastval from t_t

group by bill_month,area_code

移动分析函数su,avg,max,min的使用

select area_code,bill_month, local_fare,

sum(local_fare) over ( partition by area_code

order by to_number(bill_month)

range between 1 preceding and 1 following ) "3month_sum",

avg(local_fare) over ( partition by area_code

order by to_number(bill_month)

range between 1 preceding and 1 following ) "3month_avg",

max(local_fare) over ( partition by area_code

order by to_number(bill_month)

range between 1 preceding and 1 following ) "3month_max",

min(local_fare) over ( partition by area_code

order by to_number(bill_month)

range between 1 preceding and 1 following ) "3month_min"

from ( select area_code,bill_month,sum(local_fare) local_fare

from t_t group by area_code,bill_month )