软件测试数据库基础

–注释 只是为了给人来阅读,编译器会忽略的文本
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 的雇员信息

– 显示年薪大于 30000，工作类别不是 MANAGER 的雇员信息
– 显示薪水在 1500 到 3000 之间，工作类别以“M”开头的雇员信息
– 显示奖金为空并且部门号为 20 或 30 的雇员信息
– 显示奖金不为空或者部门号为 20 的雇员信息，要求按照薪水降序排列
– 显示年薪大于 30000 工作类别不是 MANAGER，且部门号不是 10 和 40 的雇员信息，要求按照雇员姓名进行排序
– 选择在部门 30 中员工的所有信息
– 列出职位为（MANAGER）的员工的编号，姓名
– 找出部门 10 中的经理 (MANAGER) 和部门 20 中的普通员工 (CLERK)
– 找出部门 10 中既不是经理也不是普通员工，而且工资大于等于 2000 的员工
– 找出没有奖金或者奖金低于 500 的员工
– 显示雇员姓名，根据其服务年限，将最老的雇员排在最前面
– 找出有奖金的员工的不同工作
– 找出姓名中不带 R 这个字母的员工
– 显示所有员工，按工作降序排序，若相同，则按工资升序排序
– 查找出不属于任何部门的员工