数据库原理学习笔记(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 )