-- number(38)

-- char(2000)

-- varchar(4000)

create table student(

sno    number(3) primary key,

sname  varchar2(40) default ('佚名'),

sex    char(2) check(sex in('男', '女', '中')),

age    number(2) check( age between 20 and 30 ),

birthday date,

sclass varchar2(10)

);

-- insert delete update

-- 事务: 一组相关的操作

-- commit   提交事务

-- rollback 回滚事务

-- 添加数据

-- 格式1: insert into 表 values(所有列的值);

insert into stuinsert into student values(1, 'jack', '男', 23, null, 'ab11');dent values(1, 'jack', '男', 23, null, 'ab11');

insert into student values(2, 'tom', '男', 21, null, 'ab12');

commit;

-- 格式2: insert into 表(指定列) values(指定列的值);

insert into student(sno, sname, birthday) values(3, 'mary', '7-7月-2017');

insert into student(sno, sname, birthday) values(4, 'mary', to_date('2017-06-10', 'yyyy-mm-dd'));

commit;

select * from student;

-- 修改数据 update

-- 格式: update 表 set 列=新值,... [where clause]

-- 把吴丹阳的年龄减小2岁，手机号改为13888888888

update studentinfo set s_age = s_age - 2, s_tel = '13888888888' where s_name = '吴丹阳';

commit;

select * from studentinfo s;

-- 删除数据 delete

-- 格式: delete from 表 [where clause]

-- 删除吴丹阳的学生信息

delete from studentinfo where s_name = '吴丹阳';

commit;

-- 查询数据 select

-- select 列 from 表 [where clause];

select * from classinfo c;

select * from studentinfo s;

select * from teacherinfo t;

-- 查询年龄大于23岁的男生的信息

select * from studentinfo s where s.s_age > 23 and s.s_sex = '男';

-- 查询家庭地址为空的学生信息

select * from studentinfo s where s.s_address is null;

-- 查询毕业学校不为空的学生信息

-- is null

-- is not null

select * from studentinfo s where s.s_school is not null;

select * from studentinfo s where not s.s_school is null;

-- 查询年龄大于24的本科生信息

select * from studentinfo s where s.s_age > 24 and s.s_xueli = '本科';

-- 查询年龄是19,24,26的学生信息

select *

  from studentinfo s

 where s.s_age = 19

    or s.s_age = 24

    or s.s_age = 26;

select * from studentinfo s where s.s_age in (19, 24, 26);

       -- = any() 等于括号里的任何一个值

select * from studentinfo s where s.s_age = any(19, 24, 26);

       -- > any() 大于最小值

select * from studentinfo s where s.s_age > any(22, 24, 26);

       -- < any() 小于最大值

select * from studentinfo s where s.s_age < any(22, 24, 26);

       -- > all() 大于最大值

select * from studentinfo s where s.s_age > all(22, 24, 26);

       -- < all() 小于最小值

select * from studentinfo s where s.s_age > all(22, 24, 26);

-- distinct 去除查询结果重复值

-- all 不去除查询结果重复值

-- 查询所有的学历

select distinct s.s_xueli from studentinfo s;

select all s.s_xueli from studentinfo s;

select distinct s.s_xueli, s.s_sex from studentinfo s;

-- order by 排序

-- order by 列 [asc(默认方式)|desc]

-- 查询所有学生的姓名和年龄,并按照年龄降序排序

select s.s_name, s.s_age from studentinfo s order by s.s_age desc;

-- 模糊查询

-- %  匹配任意多个字符

-- _  匹配一个字符

-- 查询以S字符开头的所有员工姓名

select e.ename from emp e where e.ename like 'S%';

-- 查询以S字符结尾的所有员工姓名

select e.ename from emp e where e.ename like '%S';

-- 查询包含S字符的所有员工姓名

select e.ename from emp e where e.ename like '%S%';

-- 查询名字包含5个字符的员工姓名

select e.ename from emp e where e.ename like '_____';

-- 查询名字以S字符开始且包含5个字符的员工姓名

select e.ename from emp e where e.ename like 'S____';

------------- 子查询 --------------

--emp 员工信息表(employee)

-- empno 员工编号

-- ename 员工姓名

-- job   职位

-- mgr   主管员工编号(manager)

-- hiredate 入职日期

-- sal   工资(salary)

-- comm  提成

-- deptno   部门编号(department number)

-- dept 部门信息表(department)

-- deptno 部门编号

-- dname 部门名字

-- loc    上班地点 (location)

select * from emp e;

select * from dept d;

--查询SMITH所在部门的名字

select d.dname

  from dept d

 where d.deptno = (select e.deptno from emp e where e.ename = 'SMITH');

-- 查询SMITH的主管名字

select a.ename

  from emp a

 where a.empno = (select b.mgr from emp b where b.ename = 'SMITH');

select * from emp e;

-- 查询工资高于1600的员工信息

select * from emp e where e.sal > 1600;

-- 查询工资高于ALLEN的员工信息

select * from emp e where e.sal > (select sal from emp where ename='ALLEN') ;

-- 查询工资高于30号部门所有人的员工信息

select *

  from emp e

 where sal > all(select sal from emp where deptno = 30);

-- 查询销售部(SALES)的员工姓名

select e.ename

  from emp e

 where e.deptno = (select d.deptno from dept d where d.dname = 'SALES');

-- 查询销售部的工资高于1300的员工姓名

select e.ename

  from emp e

 where e.sal > 1300

   and e.deptno = (select d.deptno from dept d where d.dname = 'SALES');

-- 伪列 rowid,rownum

-- rowid 行id,标示该行的物理存储位置

-- rownum 行编号

select rowid,rownum,e.* from emp e;

-- 分页查询

-- 查询员工表前三行

select * from emp e where rownum <= 3;

--查询员工表第三行到第五行

select a.*

  from (select rownum r, e.* from emp e) a

 where r >= 3

   and r <= 5;

-- 查询工资最高的三个员工信息

-- 先筛选数据，再对选出的数据(行)排序

--select * from emp e where rownum <= 3 order by e.sal desc;

select * from (select * from emp order by sal desc) e where rownum <= 3;

--按照工资降序排序，查询员工表第三行到第五行

select a.*

  from (select rownum r, e.* from (select * from emp order by sal desc) e) a

 where r >= 3

   and r <= 5;