SQL编程及高级查询

创建表空间、创建用户、创建表、对表添加约束、修改约束和删除约束属于数据定义语言（DDL）

用户授权、回收权限属于数据控制语言（DCL）

数据操控语言（DML）用于检索、插入和修改数据库信息。

 

--创建一张学生表
create table student(
stuid number(4) primary key,
stuname nvarchar2(20) not null,
stusex char(2) check(stusex='男' or stusex='女'),
stuage number(3) default 20,
stuphone char(11) not null
);

create table stu_backup(
stuname nvarchar2(20) not null,
stuage number(3) default 20,
stuphone char(11) not null
);

--把已经存在的表里的数据添加到另一张表里来
insert into stu_backup select s.stuname,s.stuage,s.stuphone from student s
select * from stu_backup;

--添加一列
alter table student add(stubirthday date);
--添加数据
insert into student(stuid,stuname,stusex,stuage,stuphone)
values(1000,'李慧坚','男',20,'13645698799');

insert into student(stuid,stuname,stusex,stuphone)
values(1001,'吴乾','男','13645698659');

insert into student(stuid,stuname,stusex,stuphone)
values(1002,'杨廉','女','13645698688');

insert into student values(1003,'邓展南','女',18,'15945687985',sysdate);
commit;

insert into student values(1004,'刘勇','女',28,'15945687989',sysdate);
commit;

insert into student values(1005,'罗毅','男',16,'15945687977',to_date('1998-11-11','yyyy-mm-dd'));
commit;

insert into student values(1006,'学霸','女',20,'15945686977',to_date('1994-11-11 12:23:23 ','yyyy-mm-dd hh:mi:ss'));
commit;

select * from student;

--修改数据
update student set stuage=25;
update student set stuage=25 where stuid=1000;
update student set stubirthday=to_date('1980-12-12 11:45:23','yyyy-mm-dd hh:mi:ss'),stuphone='13245678965'
where stuid=1000;

--删除数据
delete from student;
delete student;
delete student where stuid=1006;
--truncate
truncate table student;
--删除表的数据有两种方法
--1.是delete,用delete删除，可以加条件，而且可以回滚事务
--2.是truncate,用truncate删除，不能加条件，直接整表的数据都被永久的删除，自动提交事务，所以没办法回滚事务

--查询
--查询学生表所有的列的内容
select * from student for update;
--查询学号为1000的学生信息
select * from student where stuid=1000;
--查询学号为1000的学生的姓名和年龄
select student.stuname,student.stuage from student where student.stuid=1000;
--只想查这张表里的性别，去掉重复的
select distinct stusex from student;
--查询时可以给表起个别名，方便使用
select s.stuid  "stuno",s.stuname "stuname"  from student s;
--根据年龄来进行排序查询，可以升序(asc)也可以降序(desc),默认是升序
select * from student order by stuage desc;
--利用现有的学生表再创建一个新的表(相当于复制表的功能)
create table new_studnet as select * from student;
select * from new_studnet;

--查询名字的年龄是xx
select stuname||'的年龄是'||stuage as "学生的年龄" from student;

--员工表里有几个部门
--group by分组，如果你的查询语句里有了group by，那么select的后面
--只能查询group by后面的列，或者是聚合函数
select e.deptno from emp e group by e.deptno;

--分完组后再加条件,用having关键字，后面跟条件
--注意，分组前加条件用where,后组后加条件用having关键字
select e.deptno from emp e group by e.deptno having e.deptno=10;

--简单查询所用的一些语法
select from 表名 where group by having order by

--查询中也可以用到算术运算符(f,-,*,/)
select * from student;
--查询每个人年龄加了5岁之后的年龄
select s.stuage 没加5岁前, s.stuage+5 加了5岁后 from student s;
--%不是求余的意思
select mod(3,5) from dual; --用到一个数据函数

--还可以用到比较运算符(=,!=,>,<,>=,<=,<>),between ..and ,in .like,is null,is not null
--查询学生年龄大于20岁的学生信息
select * from student s where s.stuage>=20;
--查询年龄在15-20之间
select * from student s where s.stuage>=15 and s.stuage<=20;
select * from student s where s.stuage between 15 and 20;
--查询年龄是15,18
select * from student s where s.stuage in(15,18);
select * from student s where s.stuage=15 or s.stuage=18;
--查询没生日的学生信息
select * from student s where s.stubirthday is null;
select * from student s where s.stubirthday is not null;
--模糊查询,like配合%或者_
--%指任意长度的任何字符
--_指一个长度的任何字符
--查询学生表里名字是以 李 开头的
select * from student s where s.stuname like '李__';
--查询中的逻辑运算符(and ,or,not)
--查询年龄大于15并且生日不为空的学生信息
select * from student s where s.stuage>15 and s.stubirthday is not null;

select abs(100),abs(-100) from dual;
select sign(100),sign(-100),sign(0) from dual;

select ceil(-3.1) from dual;
select floor(3.1) from dual;
select round(5555.6666,2.5) from dual;

select sysdate  hz from dual;
select systimestamp  hz from dual;

--聚合函数avg(),max(),min(),sum(),count()
select * from student;
select max(stuage) from student;
select min(stuage) from student;
select avg(stuage) from student;
select sum(stuage) from student;
select count(*) from student;

--根据部门编号来分组并且求出每个部门有多少个人
select deptno, count(empno) from emp group by deptno
-- 先根据部门编号来分组，然后求出每个部门的工资的最大值，最小值，平均值
select e.deptno, max(e.sal),min(e.sal),avg(e.sal) from emp e group by e.deptno having avg(e.sal)>2000;
select * from emp where deptno=30;

--分析函数
--分析函数
select e.ename,e.deptno,e.sal,rank() over(partition by e.deptno order by e.sal) "rank"
 from emp e;
 
select e.ename,e.deptno,e.sal,dense_rank() over(partition by e.deptno order by e.sal) "dense_rank"
 from emp e;
 
 select e.ename,e.deptno,e.sal,row_number() over(partition by e.deptno order by e.sal) "row_number"
 from emp e;