--1、表空间和权限
/**
create tablespace xx;
sys : 超级管理员 change_on_install
system : 普通管理员 manager
scott : 标准用户 tiger
connect resource dba
*/
--创建表空间
create tablespace yc_ts
datafile ''
size 100M;
--临时表空间
create temporary tablespace yc_tts
tempfile ''
size 50M;
--创建用户
create user yc identified by yc
default tablespace yc_ts
temporary tablespace yc_tts;
--授权
grant resource, connect to yc;
--撤销权限
revoke resource, connect from yc;
--锁账号
alter user yc account lock;
--解账号
alter user yc account unlock;
--2、表的创建和约束
/**
create table table_name(
column_name column_type,
...
)
emp, dept, bonus, salgrade
*/
--创建一个与emp结构相同的表,不要数据
conn scott/a;
grant select scott.emp on yc;
conn yc/yc;
create table emp1
as
select * from scott.emp where 1 = 0;
--查看表结构
desc table_name;
--删除表结构
drop table table_name;
--修改表结构(添加字段)
alter table table_name
add email varchar2(20);
--修改表结构(修改字段)
alter table table_name
modify email varchar(10);
--修改表结构(删除字段)
alter table table_name
drop column email;
--约束
/**
1、主键约束: pramry key
2、默认值约束: default
3、检查约束: check
*/
alter table table_name
add constraint constraint_name constraint_type;
/**
4、唯一约束: unique
5、非空约束: not null
*/
alter table table_name
modify column_name constraint_type;
/**
6、外键约束: foreign key
子表:含有外键的表
主表:外键与主键对应的表
约束建立在子表中:
*/
alter table sub_table_name
add constraint constraint_name foreign key(foreignKey_name)
references main_table_name(primarykey_name);
--3、数据操作
/**
insert: insert into table_name(column_name,...) values(column_value,...);
--向emp1表中插入emp表中deptno=20部门的员工信息
*/
insert into emp1 select * from scott.emp where deptno=20;
/**
delete: delete from table_name where ...
update: update tabel_name set column_name=column_value, ... where ...;
*/
create table stuInfo(
id number,
name varchar(20),
sex varchar(4),
age number
);
insert into stuInfo
select 1,'xw','男',18 from dual union
select 2,'mc','女',16 from dual union
select 3,'ww','男',21 from dual union
select 4,'xw','男',18 from dual;
--删除除了学号(自动编号)字段以外,其它字段都相同的冗余记录!
delete from stuInfo where id not in(
select min(id) from stuInfo group by name, sex, age);
--4、基础操作
/**
select: select column_name, ... from table_name
where ... group by ... having ... order by ...;
查询所有列用:*;
where是筛选分组前的数据
having是筛选分组后的数据
查询语句的执行先后顺序:
-- from --> where --> group by --> having --> select --> order by
筛选数值要比字符速度快
*/
--面试题:比较效率(第一条比第二条快)
select * from emp where deptno = 10 and ename like '%A%';
select * from emp where ename like '%A%' and deptno = 10;
--5、高级查询
conn scott/a;
grant select scott.dept on yc;
conn yc/yc;
/**
连接查询:
内连接查询:( [inner] join ) emp, dept
select ename, loc from emp e join dept d on e.deptno = d.deptno;
外连接查询: ( [outer] join ) 驱动表 和 匹配表
左外连接:left join 左边的表为驱动表,右边的表为匹配表
select * from emp e left join dept d on e.deptno = d.deptno;
右外连接:right join 右边的表为驱动表,左边的表为匹配表
select * from emp e right join dept d on e.deptno = d.deptno;
全连接查询:( full join )
先左外连接 再右外连接,然后 union(并集)结果
select * from emp e full join dept d on e.deptno = d.deptno;
交叉查询:( cross join )笛卡尔集
select * from emp, dept;
自连接查询:(自己操作自己)
--查询每个员工的名字和经理的名字
select e1.ename, e2.ename from emp e1 join emp e2 on e1.mgr = e2.empno;
*/
--查询没有员工的部门名字
select dname from dept d left join emp e on d.deptno = e.deptno where e.deptno is null;
select dname from dept d left join emp e on d.deptno = e.deptno where e.deptno is null;--(错)
/** where 是连接后筛选,and 是边筛选边筛选 */
--不准用组合函数,求薪水的最高值
select max(sal) from emp;
select distinct sal from emp where sal not in(
select distinct e1.sal from emp e1 join emp e2 on e1.sal < e2.sal);
/**
create tablespace xx;
sys : 超级管理员 change_on_install
system : 普通管理员 manager
scott : 标准用户 tiger
connect resource dba
*/
--创建表空间
create tablespace yc_ts
datafile ''
size 100M;
--临时表空间
create temporary tablespace yc_tts
tempfile ''
size 50M;
--创建用户
create user yc identified by yc
default tablespace yc_ts
temporary tablespace yc_tts;
--授权
grant resource, connect to yc;
--撤销权限
revoke resource, connect from yc;
--锁账号
alter user yc account lock;
--解账号
alter user yc account unlock;
--2、表的创建和约束
/**
create table table_name(
column_name column_type,
...
)
emp, dept, bonus, salgrade
*/
--创建一个与emp结构相同的表,不要数据
conn scott/a;
grant select scott.emp on yc;
conn yc/yc;
create table emp1
as
select * from scott.emp where 1 = 0;
--查看表结构
desc table_name;
--删除表结构
drop table table_name;
--修改表结构(添加字段)
alter table table_name
add email varchar2(20);
--修改表结构(修改字段)
alter table table_name
modify email varchar(10);
--修改表结构(删除字段)
alter table table_name
drop column email;
--约束
/**
1、主键约束: pramry key
2、默认值约束: default
3、检查约束: check
*/
alter table table_name
add constraint constraint_name constraint_type;
/**
4、唯一约束: unique
5、非空约束: not null
*/
alter table table_name
modify column_name constraint_type;
/**
6、外键约束: foreign key
子表:含有外键的表
主表:外键与主键对应的表
约束建立在子表中:
*/
alter table sub_table_name
add constraint constraint_name foreign key(foreignKey_name)
references main_table_name(primarykey_name);
--3、数据操作
/**
insert: insert into table_name(column_name,...) values(column_value,...);
--向emp1表中插入emp表中deptno=20部门的员工信息
*/
insert into emp1 select * from scott.emp where deptno=20;
/**
delete: delete from table_name where ...
update: update tabel_name set column_name=column_value, ... where ...;
*/
create table stuInfo(
id number,
name varchar(20),
sex varchar(4),
age number
);
insert into stuInfo
select 1,'xw','男',18 from dual union
select 2,'mc','女',16 from dual union
select 3,'ww','男',21 from dual union
select 4,'xw','男',18 from dual;
--删除除了学号(自动编号)字段以外,其它字段都相同的冗余记录!
delete from stuInfo where id not in(
select min(id) from stuInfo group by name, sex, age);
--4、基础操作
/**
select: select column_name, ... from table_name
where ... group by ... having ... order by ...;
查询所有列用:*;
where是筛选分组前的数据
having是筛选分组后的数据
查询语句的执行先后顺序:
-- from --> where --> group by --> having --> select --> order by
筛选数值要比字符速度快
*/
--面试题:比较效率(第一条比第二条快)
select * from emp where deptno = 10 and ename like '%A%';
select * from emp where ename like '%A%' and deptno = 10;
--5、高级查询
conn scott/a;
grant select scott.dept on yc;
conn yc/yc;
/**
连接查询:
内连接查询:( [inner] join ) emp, dept
select ename, loc from emp e join dept d on e.deptno = d.deptno;
外连接查询: ( [outer] join ) 驱动表 和 匹配表
左外连接:left join 左边的表为驱动表,右边的表为匹配表
select * from emp e left join dept d on e.deptno = d.deptno;
右外连接:right join 右边的表为驱动表,左边的表为匹配表
select * from emp e right join dept d on e.deptno = d.deptno;
全连接查询:( full join )
先左外连接 再右外连接,然后 union(并集)结果
select * from emp e full join dept d on e.deptno = d.deptno;
交叉查询:( cross join )笛卡尔集
select * from emp, dept;
自连接查询:(自己操作自己)
--查询每个员工的名字和经理的名字
select e1.ename, e2.ename from emp e1 join emp e2 on e1.mgr = e2.empno;
*/
--查询没有员工的部门名字
select dname from dept d left join emp e on d.deptno = e.deptno where e.deptno is null;
select dname from dept d left join emp e on d.deptno = e.deptno where e.deptno is null;--(错)
/** where 是连接后筛选,and 是边筛选边筛选 */
--不准用组合函数,求薪水的最高值
select max(sal) from emp;
select distinct sal from emp where sal not in(
select distinct e1.sal from emp e1 join emp e2 on e1.sal < e2.sal);