数据库基础命令软件测试

–注释 只是为了给人来阅读,编译器会忽略的文本
create user lisi identified by 123456 account unlock;
–创建用户lisi,密码为123456,账户未锁定,代表可以登录. 这里会登录失败,因为没有权限
grant connect,resource to lisi;
–给lisi用户连接数据库,使用资源的权限.
/*
多行注释
多行注释
*/
alter user lisi identified by 666666;
–修改用户lisi的密码为6个6. 这个操作可以是system用户,也可以是lisi用户
alter user lisi account lock;
– 修改用户lisi,将账号锁定. 例如游戏公司锁账号
alter user lisi account unlock;
–解锁lisi用户
revoke connect,resource from lisi;
–从lisi用户回收连接数据库,使用资源权限
drop user lisi cascade;
–删除用户lisi. 并且删除它下面的所有表,视图等数据.

–练习
– 新建一个用户 tom，密码设置为 love
create user tom identified by love account unlock;
– 给用户 tom 授权登录，访问资源的权限
grant connect,resource to tom;
– 锁定 tom 用户的账户，使其不能登录
alter user tom account lock;
– 给 tom 用户解锁
alter user tom account unlock;
– 收回 tom 用户的登录、访问资源的权限
revoke connect,resource from tom;
– 删除用户 tom
drop user tom cascade;

–为什么使用数据库,而不是excel来存储数据?
–因为excel使用起来效率低下,所以使用数据库

–数据库存储数据,必须先建表,才能插入数据.
create table students(
sno number(4), --数字类型,长度为4 0000~9999
sname varchar2(9), --字符类型,长度为9 9个因为字符或3个中文字符
ssex varchar2(3),
sage number(3)
);

insert into students values(1001,‘张三’,‘男’,20);
–插入一条数据到学生表. 字符类型必须使用单引号
insert into students values(1002,‘李四’,‘女’,18);

select * from students;
– 从students表,查询所有字段

rename students to stu2;
–将students修改名字为stu2

alter table stu2 add ( score number(3), money number(4) );
–修改stu2表,添加score和money字段. 没有数据那么它的内容就是NULL

alter table stu2 drop column score;
–修改stu2表,删除score字段(列)

drop table stu2;
–删除stu2表

–约束:为了保证数据的有效性,完整性而添加的限制. 例如,不能存在两条完全相同的数据
–约束并不是互斥的,可以共存.
create table students(
sno number(4) primary key, --主键约束. 每个表只能有一个主键. 这个字段的值不能重复
name varchar2(10) not null, --非空约束. 这个字段必须有值.
sex varchar2(3) check(sex=‘男’ or sex=‘女’), --检查约束. 该字段只能取这个2个值
seat number(4) unique, --唯一约束. 这个唯一可以作用于多个字段
address varchar2(50) default ‘深圳市’ --默认约束. 如果该字段没有给值,那么就使用默认值
);

insert into students values(1001,‘张三’,‘男’,100,‘北京市’);
insert into students(sno,name,sex,seat) values(1002,‘李四’,‘女’,101);
–如果插入的字段值的数和表格字段的个数不等,则不能省略字段
select * from students;

–创建从表
create table scores(
sno number(4) not null references students(sno), --这个sno作为外键,引用自students表中的sno
course varchar2(10),
score number(3)
);
insert into scores values(1001,‘语文’,80);
insert into scores values(1001,‘数学’,85);
insert into scores values(1002,‘语文’,90);
insert into scores values(1002,‘数学’,77);
–1.如果值不存在与主键中,则不能添加到从表
insert into scores values(1003,‘数学’,77);
–2.如果值已经在从表中使用了,那么无法从主表中删除
delete from students where sno = 1001;

select * from scores;

–先有表,再添加约束
alter table scores add constraint ck_scores_score check( score>=0 and score<=100 );
– 修改scores表,添加约束.约束名为ck_scores_score, 内容是score值要大于等于0,小于等于100
alter table scores add constraint fk_scores_sno foreign key(sno) references students(sno);
–修改scores表,添加约束.约束名为fk_scores_sno, 内容是外键sno,引用自students的sno

–对于null 和 default约束就是修改状态
alter table scores modify score not null;
– 修改scores表,score字段不允许为空
alter table scores modify score default 0;
– 修改scores表,score字段默认值为0

alter table scores drop constraint ck_scores_score;
–修改表,移除名为ck_scores_score的约束.

–练习
– 创建表 class_info
/*
字段名 类型 长度 可选值
c_id 字符串 20 主键
c_type 字符串 6 UI or 测试 or 开发
c_position 字符串 20 非空
start_time 日期(date)
c_status 字符串 6 在读 or 毕业
*/
create table class_info(
c_id varchar2(20) primary key,
c_type varchar2(6) check( c_type=‘UI’ or c_type=‘测试’ or c_type=‘开发’ ),
c_position varchar2(20) not null,
start_time date,
c_status varchar2(6) check(c_status=‘在读’ or c_status=‘毕业’)
);

select * from scott.emp;
– 查询scott用户下的emp表

–查询语法 select 字段,字段(或者*) from 表.
select * from emp;
–查询emp表的所有字段.
select ename,sal from emp;
–只查询姓名和薪水字段
select e.ename,e.sal from emp e;
–给emp表取别名为e. 那么前面的字段就可以使用这个别名
select ename 姓名,sal as 工资 from emp;
– 给ename和sal字段取别名.

–where 条件. 满足条件的记录会被查询出来
select * from emp where sal>=2000;

/*操作符
算数运算：+ - * /
关系运算：= > < <= >= != <>
逻辑运算：and or not
字符串连接操作符：||
/
select ename,sal2 from emp; --工资翻倍
select * from emp where job != ‘SALESMAN’; --查询工作不是SALESMAN的员工
select * from emp where sal>=1500 and sal<=3000; --查询工资在1500到3000的员工
select * from emp where not job = ‘SALESMAN’; --查询工作不是SALESMAN的员工

–order by 排序
select * from emp order by sal asc; --以工资排序.默认为升序
select * from emp order by sal desc; --以工资降序

select * from emp where sal>2000 order by sal desc;
– 先查询出2000以上工资的员工,再降序

select ‘hello’||ename from emp;
– 在每个ename前面连接一个hello字符

–distinct 消除重复行
select distinct job from emp;
–查询所有工作. 并且去重

–对null类型的判断,不能使用等于.
select * from emp where comm is null; --查询没有奖金的员工
select * from emp where comm is not null; --查询有奖金的员工

–oracle数据库,数据库名,表名,字段名都不区分大小写. 但是字段的值区分.
select * from emp where job = ‘SALESMAN’;
SELECT * FROM EMP WHERE JOB = ‘SALESMAN’;

–in 如果一个字段有多个取值,那么可以用in,替代or
select * from emp where job=‘CLERK’ or job = ‘SALESMAN’ or job = ‘MANAGER’;
select * from emp where job in (‘CLERK’,‘SALESMAN’,‘MANAGER’);
–查询工作为CLERK,SALESMAN,MANAGER的员工

select * from emp where job not in (‘CLERK’,‘SALESMAN’,‘MANAGER’);
–查询工作不为CLERK,SALESMAN,MANAGER的员工

–between and 在…之间. 用于简化区间. 包含两边边界
select * from emp where sal>=1500 and sal<=3000;
select * from emp where sal between 1500 and 3000;
–查询工资在1500到3000的员工

–like 模糊查询
–% 匹配任意个字符. 0~无穷大
–_ 匹配1个任意字符
–escape 后面会修饰一个符号. 跟在这个符号后面的统配符,不在拥有通配符效果

select * from emp where ename like ‘%S%’;
–查询名字包含S的员工
select * from emp where ename like ‘%S’;
–查询名字以S结尾的员工
select * from emp where ename like ‘S%’;
–查询名字以S开头的员工
select * from emp where ename like ‘_L%’;
–查询名字第2位为L的员工

select * from emp where ename like ‘%/%%’ escape ‘/’;
–查询名字中包含%的员工
select * from emp where ename like ‘%/_%’ escape ‘/’;
–查询名字中包含_的员工

– 显示薪水大于 2000，且工作类别是 MANAGER 的雇员信息
select * from emp where sal>2000 and job= ‘MANAGER’;
– 显示年薪大于 30000，工作类别不是 MANAGER 的雇员信息
select * from emp where sal12>30000 and job!= ‘MANAGER’;
– 显示薪水在 1500 到 3000 之间，工作类别以“M”开头的雇员信息
select * from emp where sal between 1500 and 3000 and job like ‘M%’;
– 显示奖金为空并且部门号为 20 或 30 的雇员信息
select * from emp where comm is null and deptno in(20,30);
– 显示奖金不为空或者部门号为 20 的雇员信息，要求按照薪水降序排列
select * from emp where comm is not null or deptno=20 order by sal desc;
– 显示年薪大于 30000 工作类别不是 MANAGER，且部门号不是 10 和 40 的雇员信息，要求按照雇员姓名进行排序
select * from emp where sal12>30000 and job != ‘MANAGER’ and deptno not in(10,40) order by ename;
– 选择在部门 30 中员工的所有信息
select * from emp where deptno = 30;
– 列出职位为（MANAGER）的员工的编号，姓名
select empno,ename from emp where job = ‘MANAGER’;
– 找出部门 10 中的经理 (MANAGER) 和部门 20 中的普通员工 (CLERK)
select * from emp where deptno=10 and job=‘MANAGER’ or deptno=20 and job=‘CLERK’;
– 找出部门 10 中既不是经理也不是普通员工，而且工资大于等于 2000 的员工
select * from emp where deptno=10 and job not in(‘MANAGER’,‘CLERK’) and sal>=2000;
– 找出没有奖金或者奖金低于 500 的员工
select * from emp where comm is null or comm<500;
– 显示雇员姓名，根据其服务年限，将最老的雇员排在最前面
select ename,hiredate from emp order by hiredate;
– 找出有奖金的员工的不同工作
select distinct job from emp where comm is not null;
– 找出姓名中不带 R 这个字母的员工
select * from emp where ename not like ‘%R%’;
– 显示所有员工，按工作降序排序，若相同，则按工资升序排序
select * from emp order by job desc,sal asc;
– 查找出不属于任何部门的员工
select * from emp where deptno is null;

–集合:可以把数据库的表当做集合,做交集,并集等运算
select deptno from dept --10 20 30 40
intersect
select deptno from emp; --10 20 30 null
–求交集

select deptno from dept --10 20 30 40
union
select deptno from emp; --10 20 30 null
–求并集

select deptno from dept --10 20 30 40
union all
select deptno from emp; --10 20 30 null
–求并集,不去重

select deptno from dept --10 20 30 40
minus
select deptno from emp; --10 20 30 null
–求补集. 减去相同,留下独有

select deptno from emp --10 20 30 null
minus
select deptno from dept; --10 20 30 40
–求补集

–多表连接查询
–交叉连接: 求笛卡尔积
/*
A(a,b,c) B(1,2,3)
a1 a2 a3
b1 b2 b3
c1 c2 c3
*/
select * from emp,dept;

–内连接
–等值连接. 将deptno值相同的合并,不同的就抛弃
select * from emp ,dept where emp.deptno = dept.deptno;
select ename,e.deptno,dname from emp e,dept d where e.deptno = d.deptno; --使用别名. 只获取想要的字段
select ename,e.deptno,dname from emp e join dept d on e.deptno = d.deptno; --专门用于连表的语法
–查询员工所在的部门名称

select ename,loc from emp e join dept d on e.deptno = d.deptno;
–练习: 查询员工所在的部门位置

–非等值连接: 一般就用于求等级. 求工资等级,学生成绩等级
select * from emp;
select * from salgrade;
select * from emp e join salgrade s on sal between losal and hisal; --如果字段没有出现在两个表中,则不需要写表名
–求员工的薪水等级

–自连接: 表格自己连自己.
select * from emp;
–员工的经理编号 = 经理的员工编号
select * from emp 员工表 join emp 领导表 on 员工表.mgr = 领导表.empno;
select 员工表.ename,领导表.ename from emp 员工表 join emp 领导表 on 员工表.mgr = 领导表.empno;
–查询员工的领导姓名

–外连接
–左外 left outer: 以左表为准.左表的内容需要全部显示,右表对应不上的数据,就表示为null
select * from emp e left join dept d on e.deptno = d.deptno;

–右外 right outer:以右表为准.右表的内容需要全部显示,左表对应不上的数据,就表示为null
select * from emp e right join dept d on e.deptno = d.deptno;

–满外 full outer: 两个表的数据都会全部显示,不会抛弃
select * from emp e full join dept d on e.deptno = d.deptno;

–子查询: 查询中还有查询
select * from emp;
select * from dept;
–查询出销售部（SALES）下面的员工信息
–1.使用连表完成
select * from emp e join dept d on e.deptno=d.deptno and dname=‘SALES’;
–2.使用子查询完成
–先找出销售部的部门编号
–再找这个部门编号下的员工
select * from emp where deptno =
(select deptno from dept where dname=‘SALES’);

–查询工作地点(LOC)在NEW YORK工作的所有员工
select * from emp e join dept d on e.deptno=d.deptno and loc =‘NEW YORK’;

select * from emp where deptno =
(select deptno from dept where loc = ‘NEW YORK’);

–any 任何一个
– <any 10 20 30 小于最高 可以确定,一定比30小
– >any 10 20 30 大于最小 可以确定,一定比10大
– =any 10 20 30 等于任意一个 可以确定他的值是10或20或30
–ex:查询emp表中比销售员（SALESMAN）最高工资低的员工信息
–先找出所有销售的工资
–然后在比最高的低
select * from emp where sal<any
(select sal from emp where job = ‘SALESMAN’);

–求工资等于scott的所有员工
select * from emp where sal =any
(select sal from emp where ename =‘SCOTT’);

select * from emp where sal in
(select sal from emp where ename =‘SCOTT’);
–总结: 如果子查询只返回一个结果,那么使用=. 如果返回多个结果,那么可以使用in或者 =any

–all 所有
– >all 大于最大 10 20 30 这个数一定比30大
– <all 小于最小 10 20 30 这个数一定比10小
–ex:查询比所有销售员薪水都高的员工信息
select * from emp where sal>all
(select sal from emp where job = ‘SALESMAN’);

– 创建一查询，显示与 BLAKE 在同一部门工作的雇员的姓名和受雇日期、部门编号，但是 BLAKE 不包含在内。
select ename,hiredate,deptno from emp where deptno =
(select deptno from emp where ename =‘BLAKE’)
and ename !=‘BLAKE’;
– 显示位置在 DALLAS 的部门内的雇员姓名、工作。
select ename,job from emp where deptno =
(select deptno from dept where loc=‘DALLAS’);
– 显示被 KING 直接管理的雇员的姓名以及薪水。
select ename,sal from emp where mgr=
(select empno from emp where ename=‘KING’);
– 创建一查询，显示能获得与 SCOTT 一样薪水的其他雇员的姓名、受雇日期以及薪水。
select * from emp where sal in
(select sal from emp where ename=‘SCOTT’)
and ename !=‘SCOTT’;
– 查找出工资等级不为 4 级的员工的员工名字，部门名字，部门位置
select * from emp e join salgrade on sal between losal and hisal
join dept d on e.deptno = d.deptno
where grade != 4;

–总结:查询语句的书写
– select 字段 from 表1 join 表2 on 表1连接表2条件
–join 表3 on 连表条件
–join 表4 on 连表条件
–where 筛选条件
–oder by 排序字段

–对于数据就4个操作. 增删改查

– 快速创建表. 通过查询语句,可以快速创建表
create table emp2 as select * from emp where sal>2000;
select * from emp2;

–复制表结构(表头). 通过永远不成立的条件,返回空表. 然后通过这个空表,创建emp3
create table emp3 as select * from emp where 1=2;
select * from emp3;

select * from dept;
–增加 insert
–1.插入时,不写字段. 则意味着值的个数,全等于表中字段个数
insert into dept values(50,‘国防部’,‘北京市’);
–2.插入时,写字段. 前面有几个字段,后面就给几个值
insert into dept(deptno,dname) values(60,‘教育部’);
–3.插入时,后面使用的是查询语句. 需要保证emp和emp3字段完全相同
select * from emp3;
insert into emp3 select * from emp where sal >=3000;

–修改 update (更新)
update emp3 set sal = 8000,comm=2000 where ename=‘KING’; --where代表条件
update emp3 set sal = 8000,comm=2000; --修改的时候,如果没有条件,则代表修改整个表

–删除 delete. 直接删除整条记录
select * from emp2;
delete from emp2 where ename=‘CLARK’; --delete后面没有字段!!
delete from emp2; --删除所有记录

–truncate 彻底删除,不能恢复
truncate table emp2;

–给销售部的所有员工加薪200块 sal = sal+200
drop table emp2;
create table emp2 as select * from emp;
select * from emp2;

update emp2 set sal = sal+200 where deptno =
( select deptno from dept where dname = ‘SALES’ );

–删除销售部所有部门信息.包含部门表和员工表
–先删除从表
delete from emp where deptno = (
select deptno from dept where dname = ‘SALES’
);
–在删主表
delete from dept where dname = ‘SALES’;

rollback;
–将 EMP 表中工资大于 2000 的员工数据，插入到 BONUS 表中
select * from BONUS;
insert into BONUS select ename,job,sal,comm from emp where sal>2000;