sql总结

最新推荐文章于 2024-02-21 16:05:43 发布

十三佩奇

最新推荐文章于 2024-02-21 16:05:43 发布

阅读量690

点赞数

分类专栏： Oracle 文章标签： Oracle sql语句

本文链接：https://blog.csdn.net/weixin_42641234/article/details/81940962

版权

Oracle 专栏收录该内容

0 篇文章 0 订阅

订阅专栏

—用户–
创建用户 –DDL
create user java114 identified by java114
修改密码
ALTER USER java114 identified by 1
更改用户状态
–锁定用户
ALTER USER java114 ACCOUNT LOCK;
—解锁用户
ALTER USER java114 ACCOUNT UNLOCK;
删除用户
DROP USER java114;–删除用户
DROP USER java114 cascade;–同是删除用户及用户的所有数据(表/视图…)
–权限:对数据库可以执行的操作–
—新创建的用户没有任何的权限,必须由高权限用户给与

权限的分类
1.系统权限:登录数据库建表
2.对象权限:对某个具体的用户的数据的操作
常见系统权限角色:
connect:可以连接数据库
resource:可以创建表等
DBA:系统管理员
–赋予系统权限–
GRANT CONNECT TO java114;
GRANT CONNECT,resource TO java114;

–赋予对象权限(可以由表的拥有者或管理员赋予)–
GRANT SELECT ON scott.emp TO java114; —给与java114查询scott用户的emp表的权限,java114不能再给与其他用户
GRANT insert ON scott.emp TO java114; –插入记录
GRANT update ON scott.emp TO java114; – 修改
GRANT delete ON scott.emp TO java114; – 删除记录权限
GRANT all ON scott.emp TO java114;–给与增删改查权限

–权限的传递–
GRANT SELECT ON scott.emp TO java114 WITH GRANT option;

—收回权限(同时收回java114传递出去的权限)–
REVOKE SELECT ON scott.emp FROM java114;

用户口令管理
1.创建规则
Create profile lock_account limit failed_login_attempts 3 password_lock_time 2;

2.将规则赋予适用的用户
Alter user java114 profile lock_account;

create table test1(ID char(10));
select * from scott.emp;
conn system/sa as sysdba;
conn java114/java114;
create user java115 identified by java115

—-用户
sys:系统用户,拥有创建库及其他所有权限
system:系统用户,ORACLE数据库管理员权限(DBA),除了创建库
scott:默认用户,可以拥有数据库管理员权限(DBA),也可当成普通用户使用,默认密码为tiger

—-DBA—-
创建表空间—DDL
create tablespace mydb datafile ‘D:\space\mydb.dbf’size 500M
autoextend on next 20M maxsize 1000M;

修改表空间—DDL
alter database datafile ‘D:\space\mydb.dbf’ resize 100M;

删除表空间—DDL
1.删除表空间的引用
drop tablespace mydb;–实际文件不受影响

2.同时删除表空间的引用,以及与实际文件的关联
drop tablespace mydb INCLUDING CONTENTS AND DATAFILES;

—-查询所有表空间—从系统表中查询(数据字典) DQL
select TABLESPACE_NAME from sys.dba_tablespaces;

SYSTEM:系统表空间
temp:临时表空间
数据类型

1.字符型:”
char:SQL标准类型定长字符串最大长度2000
VARCHAR2:可变长字符串 0-4000 ORACLE专用类型

2.数字型
INT:短整型
number(7):整数 -9999999 -> 9999999
number(7,2):浮点型 -99999.99 ->99999.99

3.日期型
DATE:不包含时分秒
中文系统:’dd-mm月-yyyy’
英文系统:’dd-AUG-yyyy’

4.ROWID
表中某行记录在硬盘存储的物理编号

注意:1.ORACLE中基本不区分大小写,只有字符型的值区分
2.字符型和数字型区别不明显

数据库中的数据主要以表的形式存在

表维护
—-1.创建表结构—DDL
CREATE TABLE 自定义表名(
字段名1 类型(长度),
字段名2 类型(长度),
..
字段名N 类型(长度)—最后一个字段不能有,
)

dept
Name Type Nullable Default Comments

DEPTNO NUMBER(4) Y
DNAME VARCHAR2(20) Y
LOC VARCHAR2(20) Y

CREATE TABLE dept(
deptno NUMBER(4),
dname VARCHAR2(20),
loc VARCHAR2(20)
);

插入数据
insert into dept values(50,’教学部’,’南京’);
insert into dept values(60,’市场部’,’南京’);
commit;

查询所有数据
select * from dept;

—2.删除表结构 DDL
drop table test1;

给表添加注释
comment on table dept is ‘部门信息表’;

给字段(列)添加注释
comment on column dept.deptno is ‘部门号’;
comment on column dept.dname is ‘部门名’;
comment on column dept.loc is ‘部门所在地’;

emp
Name Type Nullable Default Comments

EMPNO NUMBER(4)
ENAME VARCHAR2(10) Y
JOB VARCHAR2(9) Y
MGR NUMBER(4) Y
HIREDATE DATE Y
SAL NUMBER(7,2) Y
COMM NUMBER(7,2) Y
DEPTNO NUMBER(2) Y

create table EMP
(
empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
)

—-修改表结构
—-ORACLE不推荐使用 DDL
create table test1(sid varchar2(10));

1.添加新字段
alter table test1 add(sname varchar2(20));

insert into test1 values(‘1001’,’zs’);
insert into test1 values(‘10010002’,’ls’);

2.修改原字段
–如修改字段类型或减少字段的长度,必须保证本字段值为null;增加字段的长度,对原值没有影响
alter table test1 modify(sid number(4));

insert into test1 values(1001,’zs’);
alter table test1 modify(sid number(8));

3.修改字段名
alter table test1 rename column sid to empno;

4.删除字段(连同数据一块删除)
alter table testone drop column sname;

5.修改表名
rename test1 to testone;

删除记录
delete from test1;

列表内容
select * from dept;
select * from emp;

多表查询:
1.无条件多表查询—必须避免
会出现<<笛卡尔积>>:连接查询中的多个表没有where子句中指定的连接条件或连接条件无效,
将多张表的记录以乘积的方式连接在一起
select e.,d. from emp e,dept d

—多表查询的简单原则:
—1.先找出N张连接表的关联字段(一般是外键)
—2.N张表连接查询,至少有N-1个连接条件(关联字段)

2.等值多表查询(内连接):连接条件以”=”进行连接
—查询所有员工的所在部门的部门名
select e.empno,e.ename,e.hiredate,d.dname,d.loc
from emp e,dept d where e.deptno = d.deptno;

自连接:将同一张表根据不同的作用,当成N张表使用
—查询所有员工上司的名字
select e.ename 员工名,boss.ename 上司名 from emp e,emp boss where e.mgr = boss.empno;

–查询Smith上司的名字
select e.ename 员工名,boss.ename 上司名 from emp e,emp boss
where e.mgr = boss.empno and e.ename=’SMITH’;

1.查询所有学生的选课情况,要求显示 <<学生的名字,课程的名字,课程的成绩>>
select s.sname,c.cname,scc.source
from student s,course c,student_course_choose scc
where s.sid =scc.sid and c.cid = scc.cid;

2.查询Emp表中,每个员工的工资级别
select e.ename,e.sal,sg.grade from emp e,salgrade sg
where e.sal between sg.losal and sg.hisal;

–查询SCOTT下属的名字
select boss.ename 上司名 , e.ename 员工名 from emp e,emp boss where e.mgr = boss.empno and boss.ename=’KING’;

–查询选择了java的学生的姓名
select s.sname,c.cname,scc.source
from student s,course c,student_course_choose scc
where s.sid =scc.sid and c.cid = scc.cid AND c.cname=’JAVA’;

—-外连接—
外连接即是将目标表中不符合某个连接条件的记录也显示出来。

–左外连接:A left [outer] join B on 连接条件—A表为主要显示的表(不符合某个连接条件的记录也显示出来)

—显示所有员工所在的部门,包括没有未分配部门的员工
select e.empno,e.ename,e.hiredate,d.dname,d.loc
from emp e left join dept d
on e.deptno = d.deptno;

select e.empno,e.ename,e.hiredate,d.dname,d.loc
from emp e,dept d where e.deptno = d.deptno(+);

–右外连接:A right join B on 连接条件
—显示所有部门的员工信息,包括没有员工的部门
select d.deptno, d.dname,d.loc,e.empno,e.ename,e.hiredate
from emp e right join dept d
on e.deptno = d.deptno;

select e.empno,e.ename,e.hiredate,d.dname,d.loc
from emp e,dept d where e.deptno(+) = d.deptno;

–全外连接:A full join B on 连接条件
select d.deptno, d.dname,d.loc,e.empno,e.ename,e.hiredate
from emp e full join dept d
on e.deptno = d.deptno;

—查询所有员工上司的名字,包括没有上司的员工
select e.ename,b.ename boss from emp e
left join emp b
on e.mgr = b.empno;
复合查询
————–分组函数(聚合函数)——
多行参数返回一个值

max();
select max(sal) from emp;
min();
select min(sal) from emp;
avg();
select avg(sal) from emp;
count()
select count(*) from emp;

sum()
select sum(sal) from emp;
select ename,max(sal) from emp;—–ORA-00937: 不是单组分组函数
1.分组函数不能和普通字段连用
2.分组函数不能使用在where字句后,作为条件
复杂查询

—-分组函数(聚合函数)—-
多行参数返回一个值

max():
select max(sal) from emp;

min()
select min(sal) from emp;

avg()
select avg(sal) from emp;

count()
select count(*) from emp

sum()
select sum(sal) from emp;

select ename, max(sal) from emp;—ORA-00937: 不是单组分组函数
select * from emp where sal = max(sal);—ORA-00934: 此处不允许使用分组函数

1.分组函数不能和普通字段连用;只有出现在group by后的分组字段才能和组函数连用
2.分组函数不能直接使用在where字句后,作为条件;
分组的条件使用having子句,having子句后的条件字段必须参与分组.
3.分组函数必须和分组查询连用

—分组查询—
group by 分组字段 having 条件
—-“各个”,”每个”,”相同”提示分组字段—

查询各部门最高/最低工资,部门人数
select deptno, max(sal),min(sal),count(*) from emp group by deptno

查询各部门中各工种的平均工资
select deptno,job,avg(sal) from emp group by deptno,job

求出每个部门的员工人数
select deptno,count(*) as “人数” from emp group by deptno;

求出每个部门中相同职位的员工人数
select deptno,job,count(*) as “人数” from emp group by deptno,job;

查询部门的员工人数大于五的部门编号
select deptno,count() from emp group by deptno having count()>5

—查询20号部门的最高最低,平均工资
select deptno,max(sal),min(sal) from emp group by deptno having deptno = 20

select deptno,max(sal),min(sal) from emp group by deptno having deptno = 20 and job=’CLERK’;—错误,job没有参与分组

1.查找平均工资大于2000的部门，并显示部门号和平均工资，并按平均工资降序排序
select deptno,avg(sal) 平均工资 from emp group by deptno having avg(sal)>2000 order by 平均工资

2.查询每个部门每个岗位的平均工资并按照部门号排序
select deptno,job,avg(sal) 平均工资 from emp group by deptno,job order by deptno

3.查询每个部门奖金总和，部门最高和最低奖金，并按照部门号降序排序
select deptno,sum(comm),max(comm),min(comm) from emp group by deptno order by deptno desc

4.显示出工作名称(job)中包含”MAN”的员工平均工资，最高工资，最低工资及工资的和
select job,sum(sal),max(sal),min(sal) from emp group by job having job like ‘%MAN%’;

查询SMITH上司的名字
1.查询SMITH上司的工号
select mgr from emp where ename=’SMITH’;–7902
2.查询上司工号对应的名字
select ename from emp where empno = 7902;

select ename from emp where empno =(select mgr from emp where ename=’SMITH’)

——子查询——
子查询:嵌套在其他SQL语句中的select语句

分类:
一.单行单列子查询:子查询返回一个结果
–查询smith所在部门的所有员工
1.先查询smith所在部门的部门号
select deptno from emp where ename=’SMITH’

2.再查询1结果的部门中的所有员工
select * from emp where deptno = (
select deptno from emp where ename=’SMITH’
)

–查询smith所在部门的部门名
1.先在emp表查询smith所在部门的部门号
select deptno from emp where ename=’SMITH’

2.再在dept表查询部门号对应的部门名
select * from dept where deptno = (select deptno from emp where ename=’SMITH’);

二.多行单列子查询
in:包含在
any:任意一个
all:所有

—-查询emp表中和10号部门工种相同的人员的信息
1.查询10号部门工种信息
select job from emp where deptno = 10

2.查询1 中工种的人员信息
select * from emp where job in (select job from emp where deptno = 10);

—-查询高于20号部门最高工资的员工的信息(高于所有人)
1.查询20号部门的所有工资
select sal from emp where deptno=20
2.查询高于以上所有值的人员
select * from emp where sal >all(select sal from emp where deptno=20)

–更好的方法–
1.查询20号部门的最高工资
select max(sal) from emp where deptno =20
2.查询高于1工资
select * from emp where sal >(select max(sal) from emp where deptno =20)

—–查询比20号部门最低工资高的人
1.查询20号部门的所有工资
select sal from emp where deptno=20
2.高于以上任意一个值的员工
select * from emp where sal >any(select sal from emp where deptno=20)

select * from emp where sal >(select min(sal) from emp where deptno =20)

二.多行多列子查询
—-查询各部门最高工资的员工的信息—
1.查询各部门的最高工资
select deptno,max(sal) from emp group by deptno

2.查询各部门最高工资的员工的信息
select * from emp where (deptno,sal) in (
select deptno,max(sal) from emp group by deptno
)
编程
1.创建课程表
字段：课程id 主键，
课程名非空，
课时数大于0，
开课时间，
上课地点

插入记录
01， JAVA， 100 ，自定义，自定义
02， C， 200 ，自定义，自定义
03， C#, 200 ，自定义，自定义
04， PYTHON, 200 ，自定义，自定义

创建学生表
学号主键，
姓名非空
年龄 14-23岁默认18岁
是否已婚是或者否
性别男或者女
插入记录
01 ,ZS
02,LISI
03 WANGWU
04 WANGWR

二.设计关系，体现出一个学生可以选多门课，一门课可以被多个学生选择；每个学生选择的每门课都要有成绩。

ZS选择了JAVA 成绩100
C 成绩60
PYTHON 成绩70

LISI选择了JAVA 成绩98
C# 成绩70
PYTHON 成绩70

WANGWU 选择了C# 成绩60
PYTHON 成绩90

—-课程表
create table course(
cid number(4) primary key,
cname varchar2(20) not null,
ctime number(4),
opendate date,
adress varchar2(20)
)
insert into course(cid,cname) values(1,’JAVA’);
insert into course(cid,cname) values(2,’C’);
insert into course(cid,cname) values(3,’C#’);
insert into course(cid,cname) values(4,’PYTHON’);

—–学生表
create table student(
sid number(4) primary key,
sname varchar2(20) not null,
age number(2),
marry varchar2(2) ,
sex varchar2(2)
)

insert into student(sid,sname) values(101,’zs’);
insert into student(sid,sname) values(102,’ls’);
insert into student(sid,sname) values(103,’ww’);

—–选课表(成绩表)
create table student_course_choose(
scid number(10) primary key,—编号
sid number(4) references student(sid),
cid number(4) references course(cid),
source number(3) not null
)
insert into student_course_choose values(1,101,1,100);
insert into student_course_choose values(2,101,2,60);
insert into student_course_choose values(3,101,4,70);

insert into student_course_choose values(4,102,1,98);
insert into student_course_choose values(5,102,3,70);
insert into student_course_choose values(6,102,4,70);

insert into student_course_choose values(7,103,3,60);
insert into student_course_choose values(8,103,4,90);

—表与表之间的关系—
1. 一对一 :( 人与身份证丈夫和妻子), 两端都可以作为主表;在从表端放入主表的主键字段作为外键,并添加唯一约束即可
2. 一对多:(员工与部门),一般将一端作为主表,多端为从表,将主表的主键作为从表的外键即可(将关系在多端维护)
3.多对多(商品与顾客):A表和B表关系为多对多,一般新建一个关系表用于维护关系,关系表中以AB两表的主键为外键

——ORACLE中函数——-
dual:虚拟表,专门做测试,只有1列,不能drop

—-查询名字中含有S的员工,无视大小写
select * from emp where ename like ‘%S%’ or ename like ‘%s%’

select * from emp where upper(ename) like ‘%S%’
select * from emp where lower(ename) like ‘%s%’
select * from emp where lower(ename) like lower(‘%&ename%’)

select upper(‘aaa’) from dual;–将字符变为大写
select lower(‘AAAA’) from dual;–将字符变为小写

LPAD(C1,N,[C2]):如C1的长度>N,则截取N个相应的字符;如C1的长度