数据库创建表,插入数据,修改

--要求查询出工资比SMITH工资要高的全部雇员信息
select ename,sal from emp where sal>800;
--查询不是职位不是“CLERK”的员工信息（至少用2种方式查询）
select * from emp;
select * from emp where job not like'%CLERK%';
select empno as 雇员编号是 from emp;
select empno as 雇员编号是, ename as 姓名是, sal as 工资为, job as 职位是 from emp;
select sal from emp order by sal desc;
select empno,ename,job,nvl(count(job),0),nvl(avg(sal),0)from emp;
select job from emp group by job;--查询不重复的职位
--查询年薪，起别名,别名不要用单引号括起来
select ename,(sal+decode(comm,null,0,comm))*12 as 年薪 from emp;
--查询下员工姓名中有O和T的
select ename from emp where ename like'%OT%';
--查询显示工资大于各个部门工资的平均值的员工的年龄
--查询各个部门工资范围，按照1000~2000,2000~3000.。这样的格式显示人数
select deptno,count(deptno)as "1000以下",count(deptno)as "1000-2000",count(deptno)as "2000-3000",count(deptno)as "3000以上" from emp
where sal between 0 and 1000 or sal between 1000 and 2000 or sal between 2000 and 3000 or sal>3000
group by deptno
--数据控制
--插入数据
select * from bonus
insert into bonus (select ename,job,sal,comm from bonus)--插入子查询的列表与insert列名一致
insert into bonus(ename,job,comm)values ('tilei','jiaoshi',60)
insert into bonus values ('tom','work',23,1)
insert into bonus values ('jick','IT',25,13)
--更新数据
update bonus
set ename='tom',job='技术员',sal=100
where ename='tilei'
--删除数据
delete bonus
where ename='jick'
--创建表
create table stu1("姓名" varchar2(20),"班级" varchar2(20),"学号" number(20),"成绩" number(20))
select * from stu1
--给创建的表中插入数据
insert into stu1 values('东伯雪鹰','三年级',1001,10)
insert into stu1 values('萧炎','三年级',1002,20)
insert into stu1 values('罗峰','一年级',101,50)
insert into stu1 values('牧尘','二年级',1007,60)
drop table stu--删除表
--增加列
alter table stu
add ("学校" varchar2(30),"专业" varchar2(30),"学号" number(30))
select * from stu;
--修改列,修改列的数据类型，默认值，尺寸
alter table stu
modify(ename varchar2(30))
--删除列
alter table stu
drop column ename
insert into stu values (default,default,1009)--插入数据
--多表查询
--等值连接
select * from sc,student where sc.s#=student.s#
select * from student,teacher where student.s#=teacher.t#
select sname,tname,sage,score from sc,student,teacher where sc.s#=teacher.t#
--外连接

--使用子查询创建表
create table te
as select * from teacher
where t#=01
select * from te;

 

转载于:https://www.cnblogs.com/baichaofeng123/p/7229484.html