测试人员需掌握的数据库知识小结

1、什么是数据库
存储数据的仓库。当前主流的包括关系型数据库和非关系型数据库
关系型数据库：和Excel工作簿一样，用由列和行构成的二维表来管理数据；利用SQL（Structured Query Language，结构化查询语言）对数据开展实际操作。
非关系型数据库：NOSQL数据库（特殊场景下高效率，Not only SQL）存储方式有以下：键值（Key-Value）储存数据库；列储存（Column-oriedted）数据库；面向文本文档（Document-Oriented）数据库；图型（Graph）数据库
2、数据库有哪些分类
小型数据库 access 1000条以下
中型数据库 mysql sqlserver 10万以下
大型数据库 oracle db2 dbase 10万以上
3、oracle数据库有哪些版本
oracle 9i (inter)网络技术
oracle 10g (grid)网格技术
oracle 11g (grid)网格技术
oracle 12c (cloud)云技术
4、oracle的两个服务
1)主服务
在Linux下切换到oracle用户权限
[oracle@localhost opt]$ sqlplus “/as sysdba”
SQL> startup
另外停止数据库的指令如下(不要执行)：
SQL> shutdown immediate
CTRL + D 退出当前命令
2)监听服务
启动监听服务 lsnrctl start
查看监听服务的状态 lsnrctl status
5、oracle 有的基本用户
1)sys 超级管理员 密码：安装时密码
特点：sys用户必须以sysdba、sysoper身份登录
2)system 系统管理员 密码：安装时密码
特点：主要管理其他用户的
3)scott 实例用户 密码：tiger
特点：初次不可以登录，账号被锁定，只有解锁才可以登录
6、常用的第三方工具pl sql安装和使用
解锁scott用户 alter user scott account unlock;
修改scott用户密码 alter user scott identified by scott;
7、sql语句
1、基本查询
1)查询部门列信息：select 列1,列2… from 表名
例：select * from 表名 (查询所有信息)
2)列上做运算：只有日期、数字类型的列才可做运算，如果列为空值，不可做运算
例：select empno,ename,sal,sal+1000,hiredate,hiredate+30 from emp
空值 null<>空格 空值null <>0 <>(不等于)
3)列上做连接 ||
例：select empno||ename from emp
4)列的别名 as 别名 或者 空格+别名 如果有特殊符号和空格一定用“”
5)表的别名 空格+别名
6)去重复 distinct
例：select distinct deptno from emp
2、where查询 where放在 from之后
1)语句结构
select 列1,列2… from 表名 where 条件(列/表达式 比较运算符 值)
普通比较运算符：
> < >= <= != <>(不等于)
逻辑运算符：
and 同时满足 or 满足其中一个即可
not 如果条件为假则返回真值? 一般跟between and 、in、like一起使用
其他比较运算符
1)between… and … 在两者之间(包括了边界值)
例：查询员工的基本工资在2000和3000之间
select * from emp where sal between 2000 and 3000
2)in在里面
例：查询员工编号是7369 7499 7844 7900的员工信息
select * from emp where empno in(7369,7499,7844,7900)
3)like 模糊查询 占位符 _ 两位占位符 __
例：查询员工名称包含S的员工信息
select * from emp where ename like ‘%S%’
例：查询员工的名称第二个字母是S的员工信息
select * from emp where ename like ‘_A%’
4)is null 是空
例：查询没有奖金的员工信息
select * from emp where comm is null or comm=0
5)条件之间的优先规则
1 所有的比较运算 (普通运算符、between and like in is null)
2 NOT
3 AND
4 OR
要想改变其优先顺序可以用C >0 and（A is null or B is not null）
例：查询岗位PRESIDENT并且工资大于1500或者是岗位是SALESMANS 的员工的名称，岗位，基本工资
select ename, job, sal from emp where job=‘SALESMAN’
or (job=‘PRESIDENT’ and sal>1500);
3、排序 order by 放在sql语句的最后
排序规则 从大到小 降序 desc
排序规则 从小到大 升序 asc 默认规则就是asc
例：查询员工的信息，并且工资从高到低进行排序
select * from emp order by sal desc
多个列进行排序 order by sal desc, age asc, 排序的列的先后顺序会影响结果集的顺序
4、单组函数
1)字符函数
lower 转换为小写
例：select emp., lower(ename) from emp（把名字大写转小写）
upper 转换为大写
例：select emp.,upper(ename) from emp（把名字小写转大写）
initcap 首字母大写
例：select emp., initcap(ename) from emp（把名字首字母大写）
concat(字符串1，字符串2) 连接函数
例：select emp.,concat(empno,ename) from emp（把工号和姓名合并为一列）
substr(字符串,截取的开始位置,截取的长度) 截取函数
length(字符串) 获取字符串的长度
instr(字符串1,字符串2) 在字符串1查找字符串2的内容在字符串1中的位置
rpad(字符串1,返回的长度,字符串2) 字符串2在字符串1的右边开始填充
lpad(字符串1,返回的长度,字符串2) 字符串2在字符串1的左边开始填充
trim(字符串) 前后去空格
replace(字符串1，要处理的内容，替换的新内容) 替换函数
2)数字函数
floor(x) 取小于或者等于x的最大整数
mod(被除数，除数) 取余数
round(x,小数位数) 四舍五入
trunc(x,小数位数) 不四舍五入
3)日期函数
select add_months(sysdate,-3) from dual
select add_months(sysdate,3) from dual
select hiredate, last_day(hiredate) from emp
select next_day(sysdate,‘星期一’) from dual
select sysdate from dual
4)转换函数
显示转换 to_date() to_number() to_char()
nvl2(参数1，参数2，参数3)
当参数1<>null返回参数2 当参数1=null 返回参数3
nvl (参数1，参数2)
当参数1<>null 返回参数1本身 当参数1=null 返回参数2
Decode(参数1, 值1, 翻译值1,值2,翻译值2,…值n,翻译值n,缺省值)
5)条件查询
case when 条件1 then ‘输出内容1’
when 条件2 then ‘输出内容2’
…
else ‘输出内容n’ end
6)分组函数
max() 求最大值
min() 求最小值
avg() 求平均值
sum() 求和
count() 求个数
注意事项： 1)空值不进行统计，要统计则先处理空值 nvl() nvl2()
2)如sql语句中没有group by，select 列除了分组函数，不能有其他列
7)group by 分组 放在where 后面 对多组数据进行统计
1)在select显示列除分组函数在group by 里面有的，在select显示列也可以有
2)多个列进行分组，先后顺序不影响结果集
3)where不可以使用分组函数
4)分组后过滤having
例：查询每一个领导下工资大于2000的人数和领导编号。
select mgr,count() from emp where sal>2000 and mgr is not null group by mgr
8)子查询 当出现未知条件的时候，可能用到子查询
1)单行子查询
例：查询跟MARTIN是同部门同岗位的员工信息
select * from emp where (deptno,job)=(select deptno,job from emp where ename=‘MARTIN’)
2)多行子查询
in 在某个范围里面
any 任意一个
all 同时满足
<any 小于最大值
>any 大于最小值
<all 小于最小值
>all 大于最大值
例：查询每个部门工资最高的员工的信息
select * from emp where (deptno,sal) in( select deptno, max(sal) from emp group by deptno)
3)分页查询 rownum 只支持<= 或者< 子查询作为表使用
例：查询员工表的第六行到第10行的记录
select * from ( select emp.,rownum rm from emp ) where rm between 6 and 10
9)1多表连接(99范式和92范式)
内连接 inner join on
通过一个关联字段，把两个表的数据连接到一起，得到所需要的数据

1. select emp., dept. from dept,emp where dept.deptno=emp.deptno
   select emp.,dept. from emp inner join dept on emp.deptno= dept.deptno
   外连接
   左连接：left join…on 属从表(右表)的关联列名（+）
   以左表为准，左表的数据全有，通过一个关联条件，跟右表的数据相连接，如果有 与之匹配的记录则连接，否则返回为null
   右连接：right join…on 属从表（左表）的关联列名（+）
   跟左连接，恰恰相反，以右表为准，右表的数据全有，通过一个关联条件，跟左表 的数据相连接，如果有与之匹配的记录则连接，否则返回为null
   自连接
   例：查询每个员工的名称、编号及其领导的名称和编号
   select worker.ename,worker.empno,manager.ename,manager.empno from emp worker, emp manager where worker.mgr=manager.empno
   10 ) 查询表名里面的字段名称，字段类型，以及字段长度…
   select table_catalog,table_schema,table_name,column_name,column_default,is_nullable,data_type,character_maximum_length,
   column_type,column_key from information_schema.columns where table_name = ‘ac_admin’

11 ) python中连接Oracle的demo
import cx_Oracle
conn = cx_Oracle.connect(‘username/username@127.0.0.1:1521/ORCL’)
cursor = conn.cursor()
result=cursor.execute(‘Select * from emp’)
data=cursor.fetchone()
print (data)
conn.close()
分析函数（窗口函数）：用于数据收敛的范围
Eg:

12 ) python中连接Oracle的demo
import cx_Oracle
conn = cx_Oracle.connect(‘username/username@127.0.0.1:1521/ORCL’)
cursor = conn.cursor()
result=cursor.execute(‘Select * from emp’)
data=cursor.fetchone()
print (data)
conn.close()
13) sql中查询使用并行
Select /*partion 8 */ 字段名称 from 表名
14) 怎样查看SQL中执行计划？
15) SQL中正则表达式使用？
总结：sql语句的解题步骤
解题步骤
1、找出要查询这些列自哪些表
2、如果多个表，则多表连接，找到他们的关联条件
3、有没有where条件 分组前过滤
4、要不要group by
5、要不要having 分组后过滤
6、是否需要排序

5、管理表
1)创建表的语法
create table 表名(列名1 列 1类型 default 默认值 ,列名2 列2类型…)
2)修改表的结构
增加列 alter table 表名 add(列名 列的类型)
修改列的类型 alter table 表名 modify(列名 列的类型)
修改列的名称(重命名) alter tabl表名 rename column原列名 to新列名
删除表 drop table表名
修改表的名称 rename原表名称 to新表名称
给表加注释 comment on table 表名 is ‘注释的内容’
给列加注释 comment on column 表名.列 is ‘列的注释’
查看表的注释 select * from user_tab_comments
查看表中列的注释 select * from user_col_comments
3)数据操作
添加记录
对部分列进行插入 insert into 表名(列名1) values(列值1)
对所有列进行插入 insert into 表名 values(列值1,列值2，…)
修改记录
update 表名 set 列名=列值 where 条件
删除记录
delete from 表名 where 条件 删除指定的记录
delete 和 truncate的区别
1)truncate 删除的速度要比delete要快
2)delete删除可以回滚，而truncate不可以回滚
3)truncate 删除的时候，直接释放表的存储空间
6、约束
约束的类型
NOT NULL 非空约束
UNIQUE Key 唯一约束
PRIMARY KEY 主键约束
FOREIGN KEY 外键约束
CHECK 检查约束
创建约束
1)创建表的时候创建约束
2)添加约束
create table course (cid number ,cname varchar2(20),tid number ,
constraint courese_cid_pk primary key (cid));
create table teacher1(tid number,tname varchar(10),
constraint teacher_tid_pk primary key(tid));
alter table course add constraint course_tid_fk foreign key(tid)
references teacher1(tid);
alter table course add constraint course_tid_ck check(tid between 1 and 10)
alter table course add constraint course_cname_uk unique (cname);
3)删除约束
alter table 表名 drop constraint 约束名
4)失效约束
alter table 表名 disable constraint 约束名
5)生效约束
alter table 表名 enable constraint 约束名
6)查看约束
查看约束在哪些列上 select * from USER_CONS_COLUMNS
查看 USER_CONSTRAINTS 表可得到用户的所有约束. select * from USER_CONSTRAINTS
7、视图
create view 视图名称 as sql语句
select * from 视图名称
8、索引
CREATE INDEX 索引名称 ON 表名(列);
9、序列
CREATE SEQUENCE course_cid
INCREMENT BY 1 START WITH 10 MAXVALUE 100
insert into course values(course_cid.nextval,‘物理5’,1);
select course_cid.currval from dual
drop sequence course_cid
10、Oracle用户及权限管理
–创建用户–
–create user 用户名 identified by 密码–
例子：create user huangguangjing identified by huangguangjing
–赋予权限–
–grant 权限1,权限2 用户名–
例子：grant connect,resource to huangguangjing
–查看权限–
–select * from user_role_privs–
例子：select * from user_role_privs
–收回权限–
–revoke权限1,权限2 from 用户名–
例子：revoke connect,resource from huangguangjing
解锁scott用户
例子：alter user scott account unlock ;
8、MySQL常用语句

1. mysql -uroot -p 进入数据库
2. show databases; 显示所有数据库
3. use 数据库名称; 进入数据库
4. show tables; 显示所有表
5. service mysql status 查看mysql是否运行
6. mysql 默认密码为空
7. create database 表名; 创建表
   9、Oracle与MySQL增删改查语句对比：

10、数据库中delete、truncate、drop的区别
delete truncate drop
可以恢复删除的数据，同时会记录日志，删除速度慢，但是提交了没办法可以恢复， 删除所有数据，不会影响表机构，不会记录日志，数据不能恢复，删除很快 删除所有数据，包括表结构一并删除，不会记录日志，数据不能恢复，删除速度快
11、rownum和rowid的区别：
两者都是伪列，rownum是根据SQL查询的结果给每行分配一个逻辑编号，而rowid是物理结构上的，在每条记录新增到数据库时生成的唯一物理标记