Oracle笔记（1）

crud以及一些基础操作

----DDL
-创建数据库
create table student(
id number primary key
)
– 添加
drop table student

– 添加字段
alter table student add (name varchar(20))
alter table student add (sex number(1))
alter table student add (test number(1))
– 修改字段类型
alter table student modify (sex varchar(2))
– 修改字段名字
alter table student rename column test to testhaha
– 删除表中字段
alter table student drop column testhaha

-----DML
–添加（增）
insert into student values(1,‘张三’,‘男’);
insert into student values(2,‘李四’,‘男’);
insert into student values(3,‘王五’,‘男’);
insert into student values(4,‘赵六’,‘男’);
insert into student values(5,‘王政’,‘男’);
insert into student values(6,‘李逵’,‘男’);
insert into student values(7,‘测试’,‘1’);
insert into student values(8,‘测试’,‘1’);
insert into student values(9,‘为空’,null);
–修改（改）
update student set sex=‘女’ where id=‘4’

–删除
delete from student where id =8
–删除 无from也可删除
delete student where id =7
----DQL
–查看表(查)
select * from student

–模糊查询
– 不限位数：%
select * from student where name like ‘王%’
– 站一个字位符：_
select * from student where name like ‘_六’

–排序 asc 升序 desc 降序
select * from student where sex =‘男’ order by name asc
select * from student where sex=‘男’ order by name desc
– 多重排序
select * from student where sex=‘男’ order by name desc, sex asc

–多表查询（连接查询）
–数据准备
create table TEST (
ID number(10),
CONTENT varchar(20)
);
insert into test values(1,‘这是1’);
insert into test values(2,‘这是2’);
insert into test values(3,‘这是3’);
insert into test values(4,‘这是4’);
insert into test values(5,‘这是5’);
insert into test values(8,‘这是8’);

– 笛卡尔积出现
select * from student,test
– 对应id处理
select * from student s,test t where s.id =t.id
– 连接查询
–内连接
select * from student s INNER JOIN test t on s.id =t.id where s.id =1

–外连接
–左连接
select * from student s LEFT JOIN test t on s.id = t.id
–右连接
select * from student s right JOIN test t on s.id = t.id

–函数
–计数
select count(1) from student where name like ‘王%’
–求和
select sum(id) from student
–平均数
select avg(id) from student
–四舍五入保留俩位数
select ROUND(AVG(id), 2) from student
–最大值
select max(id) from student
–最小值
select min(id) from student
–输出当前日期
select id,SYSDATE from student
–为空
select id,NVL(sex, ‘这压根就是空的’) from student
–拿到两一个筛选的结果
select * from (select count(id) s from student)
– if-else
select DECODE(sex, ‘男’,‘是的’,‘女’,‘不是’,‘啥都没有’) from test

– 分组(必须使用函数)
select COUNT(1),sex from student GROUP BY sex
–找出大于3的
select COUNT(1),sex from student where id>3 GROUP BY sex
–having 对分组后的数据进行筛选 （可使用聚合）；；； where 则是对 分组之前的数据进行筛选（不可使用聚合）
select COUNT(1),sex from student where id>3 GROUP BY sex having COUNT(1)>1

– 字查询 where 后面可以加
select * from student where id>(select ROUND(avg (id), 0) from student)