软件测试小白进阶之路---数据库MYSQL（2）

1. 数据库

MYSQL  小巧 效率高 免费

数据库的表结构

a.  数据分类

b. 数据关联

数据库设计评审点：

a. 数据存储是否有重复现象   不同表中是否存在相同字段

b. 是否符合范式要求    同一个表中存在的数据重复字段不要超过两个以上

c. 对于业务有频繁查询要求的数据表

d. 表间关系是否正确  是否按照业务要求进行了数据关联

e. 数据库字段以及表的设计是否充分  数据字段内容是否涵盖需求要求的所有数据  数据字段类型 及长度是否符合需求

 

2. 概念

表  基本的组成单位 与记录和字段组成

索引  建立在字段上  可以对字段进行排序  同一张表的索引

视图  建立在数据库上  封装查询命令 存储数据 方便查询调用

事务  a. 有两条以上的数据库命名组成的原子集合

    b. 优点 可以保证有关联的数据库操作所有对应的关联所需要的完整性质和统一性

   c. 缺点  不要将不相关的操作放在同一个事务中，否则会降低执行成功率

存储过程  a. 存储过程封装了多条SQL 命令，必须放在数据库服务器端

   b. 优点： 减少了网络传输SQL命令的压力  提高了访问的安全性 SQL 命令放在服务器执行效率高

   c. 缺点：需要编写和设计调试 他是一段程序 对大批量的数据较弱 避免分支和循环

触发器：自动执行 并且只能对一张表中进行触发  当某一张中发生操作时候需要同时对其表的数据进行操作可以考虑建立触发器

主键：用一标记该表中的记录， 不能重复， 不能为空

外键：通过外键和其他表中的数据进行关联  只能是以存在主键值

当前表的数据要被其他表使用时候 要有主键    有主键时候就要设置外键

 

3. 约束：

1. 主键约束

1. 主键约束 primary key

特点： 约束的字段数据，不能为空 不能重复 如果插入或者更新数据为空或者重复不允许操作

----语法：

create table students(

No int,

Name char(10)

datetime

constraint datetime primary key(No)

)

2. 删除主键约束

语法：alter table 表名 drop constraint 约束名

3. 改主键约束

语法：alter table 表名 alter column 字段 类型 not null

alter table student alter column No int not null;

 

2. 外键约束 FOREIGN KEY

新增外键关系

a. 命名的外键约束

create table grade(

No int,

grade float,

constraint foreign key(no) references student(no)

);

b. 删除外键约束

alter table grade drop constraint FK_02;

c. 改外键约束

alter table grade add constraint FK_02 foreign key (no) references student(no) on delete cascade;

当主表进行数据删除时候，从表一起删除

alter table grade add constraint FK_02 foreign key(no) references student(no) on update cascade;

当主表进行数据更新时候，从表一起更新

 

4. SQL 语句

创建数据库

语法：create database 数据库名；

开辟两块空间，一个是保存日志的，一个是保存表的

删除库 DROP DATABASE 库名；

单行注释 -- 多行注释/*    */

切换数据库

语法：use 数据库名；

备份数据库

BACKUP DATABASE 库名 TO DISK='文件名（路径）'；

恢复数据库

RESTORE DATABASE 库名 FROM DISK = '文件名（路径）'

 

建表

语法：create table 表名（字段名 字段类型 字段约束，字段名 字段类型 字段约束）；

create table tmp(

No int primarykey,

NAME char(10),

);

字段约束可以没有

删除表

语法：drop table 表名

注意外键约束

drop table grade

添加外键

a. 添加好的数据在添加外键

语法：alter table 表名 add constraint FK_ID foreign key(外键字段名) references 外表表名（对应表的主键字段名）

列：alter table tb_active add constraint FK_ID foreign key(user_id) references tb_user(id);

b. 建表时候直接添加外键约束

create tabel books(

bookid number(10) not null primary key,

categoryId number(10) not null references Category(id)  -- 外键约束

)

插入数据

语法：

a. 对表所有字段添加数据：insert into 表名 values(第一个字段值，第二个字段值...)

列：insert into students values(5,'张三'，'北京')；

b. 对表指定字段添加数据：insert into 表名(字段1，字段2) values(值1，值2)；

insert into student(id,name,sex) values(2,'张三','男'；)

 

删除数据

语法：

a. delete from 表名

注意：删除表的数据要考虑约束条件

b. delete from 表名[where 条件]

LIKE------模糊查询

 

更新数据

语法：update 表名 set 字段 = 值，字段2 = 值[where 条件]

注意：字段类型和值的类型要匹配，主键的可以修改

注意：为了保证数据的完整一致，最好在更新时候加WHERE条件

 

查询数据

语法：select 查询内容（字段1，字段2）from 表名 where 条件

列：查询所有姓‘张’的同学

SELECT * FROM student WHERE name LIKE'张%'；

查询名字中有‘于’字同学的信息

SELECT*FROM student WHERE name LIKE‘%于%’；

查询姓‘杨’并且所在省份为‘河南省’的同学的学号

SELECT sid FROM student WHERE name LIKE'杨%' AND province = '河南'；

查询姓‘李’但是所在省份没有‘湖南’字样的同学省份身份证号码

SELECT card_id FROM student WHERE name LIKE'李%'AND province NOT LIKE'%湖南%'；

排序

对查询到的结果按照升序或者降序方式进行排序

语法：order by 字段 排序方式（dese, asc）

注意：order by 一定要写在查询sql 最前面

列：按照C2的升序排列

SELECT * FROM table ORDER BY C2 ASC;

按照C2 的降序排列

SELECT* FROM table ORDER BY C2 DESC;

 

聚合函数

聚合函数需要放在HAVING 里， 不能放在WHERE中

 

统计记录条数 COUNT

查多少名学生

SELECT  count(*) FROM student

查有多少个班级

SELECT count (DISTINCT class) FROM student;

查找入学时间在1990-1到2000-12的学生个数

SELECT count(enrollment) FROM student WHERE enrollment BETWEEN '1990-1' AND'2000-12';

分组查询 GROUP BU

对查询的内容进行分组，分类，然后对一个组的数据进行查询或者运算

SELECT 查询内容 FROM 表名 GROUP BY 字段名

分组前条件 在group by 之前用where 条件进行选数据

分组后条件 在group by 之后  + having 条件

 

RIGHT 函数

取身份证最后6位数

SELECT RIGHT（card_id,6）FROM student;

将身份证补齐20位，前面用0补

SELECT RIGHT('00'+card_id,20) FROM student;

SELECT '00'+card_id FROM student;

 

LEFT 函数

取身份证的前10位

SELECT left(card_id,10) FROM student;

将学号小于50的电话设置成NULL

UPDATE student SET tel = NULL WHERE sid < 50;

将电话为NULL 的学生信息查询出来

SELECT * FROM student WHERE tel IS NULL;

 

关联查询（等值连接）

内连接（等值连接）

外连接 左连，右连

1. 内连接 inner join

语法： select 内容 from表1 inner join 表2 on 等值条件

select * from student inner join grade on grade.stuid = student.stuid

2.  左连  select 内容 from 表1 left outer join 表2 on 条件

3.  右连 select 内容 from 表1 right outer join 表2 on 条件

 

视图/虚表  VIEW

创建视图  create view 视图名 as (SQL)

注意1 视图中不保存数据，不保存表的结构，不保存表，没有约束。只保存SQL语句

注意2 视图可以当表来使用 但是不建议做增删改的操作

注意3 删除视图时候，不会影响到原来的基础表，可以在视图中直接用SELECT

删除视图

DROP VIEW 视图名

视图优点：

a. 对一个使用SQL不熟练的来说，视图是一个简单的查询途径

b. 对经常重复的复杂SQL ，使用视图可以提高查询效率

c. 可以辅助用于权管理，提高数据安全性

d. 可以保护表和数据的完整一致性

视图缺点：

a. 降低查询效率

b. 增加保存空间

c. 无法对数据和表进行操作。使得操作测试不方便

视图在企业中如何应用

a. 对于测试而言，降低测试的复杂度。列：复杂SQL 写一次保存视图，以后可以调用

b. 对于测试而言，降低工作难度。列：新手使用上手快

c. 对于开发而言，减低服务器的工作。使用视图，服务器直接查询视图就可以满足查询功能

d. 对产品设计和业务人员而言，减低工作难度，

 

索引

索引就像目录，表中对一个字的目录结构，如果想操作表的数据，先在索引字段找到那一行，然后操作那一条数据

注意1. 建议索引设置在主键，外键，唯一约束，效率高

注意2. 索引是应用在查询条件的。经常查询的条件字段应该设置为索引字段

注意3. 一个表可以有多个索引，索引之间可以交叉字段，一个索引可以有多个字段

索引优点：

提高查询效率   提高排序的效率

索引缺点：

占用数据资源  降低了对数据库增，删，改的效率

索引在企业中的应用

a. 页面如果有很多的查询条件并且需要排序时候，建议一一创建索引

b. 检查是否主键和外键，唯一约束建立了索引关系

c. 在测试中应该检查，运行查询条件是否用了索引，频繁被更新的表，不要设置太多的索引

 建立索引

语法： create index 索引名 on 表（字段，字段）；

create index suoyin1 on studnet(stuname, family);

删除索引

语法：drop index 表名.索引名

drop index student.suoyin1

 

触发器 trigger

当满足触发条件时候，执行后面的触发SQL ,支持事务

语法：create trigger 触发器名 on 工作表名 for 触发方式 as sql 语句

触发条件  delete,update 触发条件因数据而异