中级软件测试工程师之数据库基础知识

最新推荐文章于 2024-05-11 09:59:46 发布

萍_testing

最新推荐文章于 2024-05-11 09:59:46 发布

阅读量943

点赞数

分类专栏：软件测试中级文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_15630913/article/details/105540517

版权

软件测试中级专栏收录该内容

1 篇文章 0 订阅

订阅专栏

作为测试人员，尤其是中高级测试，随着测试的深入，对数据库的了解就不能仅仅限于基础的增删查改，只有对数据库有更深入的了解，才能更好的理解研发的设计，更早发现设计的漏洞，也能为后续学习性能测试，安全测试等打下一定的基础。

以下是本人在学习过程中，对mysql数据库原先比较陌生或者不了解的地方的一些整理，以下内容根据本人学习情况从不同的地方学习整理而来，个人觉得作为一个中级测试人员，了解以下数据库知识还是比较必要的，为以后学习更深入的测试技术也会有一定帮助，分享给大家。

一、数据库创建

1.启动/关闭数据库服务

打开cmd命令框

启动服务命令：net start MySQL80

关闭服务命令：net stop MySQL80

2.登录数据库

登录数据库命令：mysql -uroot -p(-u后面是用户名，-p后面是密码)

3.创建删除数据库

创建数据库：create DATABASE 数据库名

查看当前服务器下所有数据库：show databases；

设置数据库编码格式：

create DATABASE 数据库名 character set = utf8;

alter DATABASE 数据库名 character set = gbk;

查看数据库编码格式：

show create database 数据库名;

打开某一数据库：

user 数据库名;

删除数据库：drop database 数据库名;

二、数据类型

MySQL支持多种类型，大致可以分为三类：数值型（整型，浮点型）、日期/时间和字符串(字符)类型

存储范围越大，占用空间越大，需选择合适的数据类型

1.整型

数据类型	存储范围	占用字节
TINYINT	-128到127(-2^7到2^7-1) 0到255(0到2^8-1)	1
SMALLINT	-32768到32767(-2^15到2^15-1) 0到65535(0到2^16-1)	2
MEDIUMINT	-8388608到8388607(-2^23到2^23-1) 0到16777215(0到2^24-1)	3
INT	-2^31到2^31-1 0到4294967295(0到2^32-1)	4
BIGINT	-2^63到2^63-1 0到2^64-1	8

2.浮点型

数据类型	存储范围	占用字节
FLOAT[(M,D)]	单精度浮点数精确到大约7位小数	4
DOUBLE[(M,D)]	用得少	8
DECIMAL[(M,D)]	根据M,D值，定义货币要求精确度高的数据	M>D,则：M+2,否则：D+2

3.日期

数据类型	存储范围	占用字节
DATE	YYYY-MM-DD 1000-01-01到9999-12-31	3
TIME	HH:MM:SS '-838:59:59'/'838:59:59'	3
DATETIME	YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00到9999-12-31 23:59:59	8
YEAR	YYYY 1901到2155	1
TIMESTAMP	YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:00/2038-01-19 03:14:07	4

现在大都使用数值型来存储时间，时间戳形式存储

3.字符型

数据类型	存储范围
CHAR(M)	0-255字节定长字符串
VARCHAR(M)	0-65535 字节变长字符串
TINYTEXT	0-255字节短文本字符串
TEXT	0-65535字节长文本数据
MEDIUMTEXT	0-16777215字节中等长度文本数据
LONGTEXT	0-4294967295字节极大文本数据
TINYBLOB	不超过 255 个字符的二进制字符串
BLOB	二进制形式的长文本数据
MEDIUMBLOB	二进制形式的中等长度文本数据
LONGBLOB	二进制形式的极大文本数据

经常变化的字段用 varchar

知道固定长度的用 char

尽量用 varchar

超过 255 字符的只能用 varchar 或者 text

能用 varchar 的地方不用 text

三、索引

1.索引优点及缺点

优点：索引可以提高检索数据的速度

缺点：索引会降低更新表的速度

2.索引使用场景

1）经常与其他表进行连接的表，在连接字段上应该建立索引；表的主键、外键必须有索引

2）数据量超过300的表应该有索引，少数据值的列不推荐增加索引；

3）定义为text, image和bit数据类型的列最好不增加索引；

4）修改性能远远大于检索性能时，不应该创建索引；

5）经常出现在Where子句中的字段，应该建立索引；

6）经常用到排序的列上推荐创建索引，因为索引已经排序。

3.索引分类

1）普通索引

由关键字KEY或INDEX定义的索引，为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（ORDERBYcolumn）中的数据列创建索引；允许被索引的数据列包含重复的值

2）唯一索引

由关键字UNIQUE定义的索引，唯一索引不仅可以提高访问速度，还能保证数据记录的唯一性；不允许被索引的数据列包含重复的值

3）主索引

由关键字PRIMARY定义的索引，为主键字段创建的索引

4）外键索引（全文索引）

由关键字FULLTEXT 定义的索引，为外键字段创建的索引

只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引

5）组合索引

当我们的where查询存在多个条件查询的时候，我们需要对查询的列创建组合索引

特点：最左匹配原则

假设我们创建（col1，col2，col3）这样的一个组合索引，那么只要查询条件中带有最左边的列，那么查询就会使用到索引，否则不会使用索引

4.索引创建

1）普通索引

CREATE INDEX 索引名 ON 表名(表列名);

ALTER TABLE 表名 ADD INDEX 索引名(表列名) ;

2）唯一索引

CREATE UNIQUE INDEX 索引名 ON 表名(表列名);

ALTER TABLE 表名 ADD UNIQUE INDEX 索引名(表列名) ;

3）主索引

ALTER TABLE 表名 ADD PRIMARY KEY (表列名);

4）外键索引（全文索引）

CREATE FULLTEXT INDEX 索引名 ON 表名(表列名);

ALTER TABLE 表名 ADD FULLTEXT INDEX 索引名(表列名);

5）组合索引

CREATE INDEX 索引名 ON 表名(列1，列2);

ALTER TABLE 表名 ADD INDEX 索引名(列1，列2) ;

5.索引删除

DROP INDEX 索引名 ON 表名;

ALTER TABLE 表名 DROP INDEX 索引名;

ALTER TABLE 表名 DROP PRIMARY KEY; //删除主索引

四、约束

1.什么是约束

对数据库字段值的限制

2.约束的作用

数据库的约束，是对数据的安全性，完整性的保证

3.约束的分类及作用

约束类型	定义	区别	支持约束类型
列级约束	对某一特定列的约束	列约束必须跟在列的定义后面，不可定义约束名称	主键、外键、唯一、检查、默认、非空/空
表级约束	对一个或多个列的约束	表约束不与列一起，而是单独定义，可定义约束名称	不支持非空/空，默认

4.约束的创建

1）主键约束（primary key）

特点：一张表只能有一个主键，数据唯一不能重复，不能为空值

2）外键约束（foreign key）

特点：保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系

父表子表必须拥有相同的存储引擎InnoDB

外键列和参照列必须有相似的数据类型

外键列和参照列必须创建索引

3）唯一约束（unique）

特点：一张表可以有多个唯一约束，保证数据唯一不重复，可以为空值

4）检查约束（check）---mysql不支持

特点：检查字段值所允许的范围

5）默认（default）

特点：未为字段赋值，则自动赋予默认值

6）非空约束（not null）

特点：限制字段不能为空

创建列级约束：

CREATE TABLE users1(

id INT(10) AUTO_INCREMENT PRIMARY KEY, ----主键约束，AUTO_INCREMENT自增，只能与主键一起使用

username VARCHAR(20) NOT NULL, ----非空约束

sex TINYINT UNSIGNED CHECK(sex=0 OR sex=1), ----检查约束，mysql中不生效

tel VARCHAR(255) UNIQUE KEY, ----唯一约束

role_id TINYINT UNSIGNED DEFAULT 0, ----默认约束，UNSIGNED无符号

role_id TINYINT UNSIGNED REFERENCES role表(id) ----外键约束，REFERENCES

);

创建表级约束：

CREATE TABLE users2(

id INT(10) AUTO_INCREMENT,

username VARCHAR(20),

sex TINYINT UNSIGNED,

tel VARCHAR(255),

role TINYINT UNSIGNED,

CONSTRAINT PRIMARY KEY(id), ----关键字CONSTRAINT

CONSTRAINT CHECK(sex=0 OR sex=1),

CONSTRAINT UNIQUE KEY(tel),

CONSTRAINT FOREIGN KEY(role_id) REFERENCES role(id)

);

5.约束的修改

ALTER TABLE 表名 MODIFY COLUMN 列名数据类型约束

ALTER TABLE 表名 ADD CONSTRAIT 约束名约束(列名)

6.约束的删除

ALTER TABLE users3 DROP PRIMARY KEY ----删除主键约束

ALTER TABLE users3 DROP FOREIGN KEY 外键名 ----删除外键约束

ALTER TABLE 表名 ALTER COLUMN 列名 DROP DEFAULT ----删除默认约束

五、视图

1.什么是视图

视图是一个虚拟表，其内容由查询定义

2.视图的作用

简化了操作，把经常使用的数据定义为视图；

安全性，用户只能查询和修改能看到的数据；

逻辑上的独立性，屏蔽了真实表的结构带来的影响。

3.视图的用法

1）视图创建

CREATE VIEW 视图名(列1,列2,列3)

SELECT ... FROM ...

2）视图调用

使用方式与数据库表一样，视图是虚拟表

3）视图修改

CREATE OR REPLACE VIEW 视图名(列1,列2,列3)

SELECT ... FROM ... ;

ALTER VIEW 视图名(列1,列2,列3)

SELECT ... FROM ...;

4）视图删除

删除视图是指删除数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据。

DROP VIEW 视图名

六、数据库事务

1.什么是事务

事务是用户定义的一个数据库操作序列，事务主要用于处理操作量大，复杂度高的数据，只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

我们也可以理解为事务是由一个或多个SQL语句组成，如果其中有任何一条语句不能完成或者产生错误，那么这个单元里所有的sql语句都要放弃执行，所以只有事务中所有的语句都成功地执行了，才可以说这个事务被成功地执行！用于维护数据一致性。

2.事务特性

1）原子性：事务中的操作要么全做，要么全不做

2）一致性：事务的执行结果是将数据库从一个一致性状态变成另一个一致性状态

3）隔离性：一个事务的执行不能被其他事务干扰

4）永久性/持续性：事务一旦提交，其对数据库的改变是永久性的

3.事务隔离级别

隔离级别	定义	脏读	不可重复读	幻读
读未提交 read uncommitted	事务读取另一个未提交事务的数据	是	是	是
读已提交 read committed	一个事务要等另一个事务提交后才能读取数据	否	是	是
重复读 repeatable read	开始读取数据（事务开启）时，不再允许修改操作	否	否	是
顺序读 serializable	事务串行化顺序执行，可以避免脏读、不可重复读与幻读。这种事务效率低下，耗数据库性能	否	否	否

脏读：看到事务未提交前的数据

不可重复读：一个事务范围内两个相同的查询却返回了不同数据（针对UPDATE）

幻读：同样的事务操作，在前后两个时间段内执行对同一个数据项的读取，可能出现不一致的结果（针对INSERT）

七、数据库表

1.创建表

CREATE TABLE 表名(

列1 数据类型,

列2 数据类型,

……

)

2.修改表

添加单列：

ALTER TABLE 表名 ADD 字段名数据类型 [约束]

删除单列：

ALTER TABLE 表名 DROP 字段名

修改单列：

修改列定义：ALTER TABLE 表名 MODIFY 列名数据类型(新) [first/after 列名] //修改列的定义（数据类型/位置）

修改列：ALTER TABLE 表名 CHANGE 旧列名新列名新数据类型

修改数据表名：

ALTER TABLE 表名 RENAME 新表名

RENAME TABLE 表1 TO 新表1,表2 TO 新表2; //可修改多张表

3.删除表

DROP：DROP TABLE 表名（整张表被删除，要使用该表必须重新建）

4.其它（主键、外键）

	主键	外键
定义	唯一标识一条记录，不能有重复的，不允许为空	表的外键是另一表的主键, 外键可以有重复的, 可以是空值
作用	保证数据完整性，唯一性	和其他表建立联系
个数	一个表只能一个主键	一个表可以有多个外键

八、操作数据库表

1.插入数据（INSERT）

1）INSERT

插入一条数据：INSERT 表名 [列1，列2，……] VALUES(列1值，列2值，……)

插入多条数据：INSERT 表名 [列1，列2，……] VALUES(列1值，列2值，……),(列1值，列2值，……);

2）INSERT SET/SELECT（此方法可使用子查询，一次只能插入一条记录）

INSERT……SET……：INSERT 表名 SET 字段1 =字段值,字段2 =字段值;

INSERT……SELECT……：INSERT表名 [列1，列2，……] SELECT [列1，列2，……]……（将查询结果插入到指定表）

2.更新数据（UPDATE）

单表更新：UPDATE 表名 SET 字段1 = 字段值,字段2 = 字段值……[WHERE ……]; //加上where只更新符合条件的数据，否则更新整个表所有数据

多表更新：

参照别的表更新本表数据：UPDATE 表1 INNER JOIN/LEFT JOIN/RIGHT JOIN 表2 ON 表1.字段1 = 表2.字段2 SET 表1.字段 = 表2.字段;

建表同时将查询结果写入数据：CREATE TABLE 表1(列1，列2) SELECT 列1，列2 FROM 表2 ;

3.删除数据（DELETE）

单表删除：

DELETE FROM 表名 [WHERE ……];（删除数据表内容，不释放空间，即：下次插入表数据，id依然接着删除数据的id继续增加）

TRUNCATE：TRUNCATE TABLE 表名（清空表数据，释放空间，即：下次插入表数据，id从1重新开始）

多表删除：

DELETE tablename1 FROM tablename1 AS t1 INNER JOIN/LEFT JOIN/RIGHT JOIN tablename2 AS t2 ON t1.col1 = t2.col2;

4.查询数据（SELECT）

1）查询表达式

SELECT VERSION(); //查询当前数据库版本

SELECT user(); //查询当前登录账户

SELECT 1+2; //查询表达式结果

2）WHERE条件查询

SELECT 字段1，字段2 FROM 表名 WHERE 字段 = 字段值; //WHERE后接查询条件，查询符合条件的数据

3）GROUP BY分组查询

SELECT * FROM 表名 GROUP BY 字段1; //GROUP BY后接分组字段，将查询数据分组统计

SELECT * FROM 表名 GROUP BY 字段1 HAVING 字段 = 字段值; //HAVING后接查询条件，查询符合条件的分组数据，先分组后查询

4）查询结果排序（ORDER BY）

SELECT 字段1，字段2 FROM 表名 ORDER BY 字段 DESC/ASC; //DESC倒序排序，ASC升序排序（默认，可不写）

5）限制查询数量（LIMIT）

SELECT * FROM 表名 LIMIT n; //取前n条数据

SELECT * FROM 表名 LIMIT m,n; //取从第m+1列开始的前n条数据

6）子查询

嵌套在其它SQL语句中的SELECT语句

例1：SELECT * FROM users WHERE id = (SELECT create_id FROM articles);

例2:SELECT * FROM users WHERE id NOT IN (SELECT create_id FROM articles);

7）常用高级查询

union：UNION 操作符用于合并两个或多个 SELECT 语句的结果集。去重（查询列的数量一致，数据类型一致）

PS：UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

union all：同union，但不去重

except：包括所有在 TABLE1 中但不在 TABLE2 中的行，并消除所有重复行而派生出一个结果表。去重

except all：不去重

intersect：包括所有既在 TABLE1 中也在 TABLE2 中的行并消除所有重复行而派生出一个结果表。去重

intersect all：不去重。

九、运算符和函数

1.常用字符函数

参考文档：https://www.runoob.com/mysql/mysql-functions.html

函数	作用	示例
CONCAT(s1,s2...sn)	字符串 s1,s2 等多个字符串合并为一个字符串	SELECT CONCAT('my','job');
CONCAT_WS(x, s1,s2...sn)	每个字符串之间通过分隔符x连接	SELECT CONCAT_WS('-','my','job');
FORMAT(x,n)	将数字 x 进行格式化 "#,###.##", 将 x 保留到小数点后 n 位，四舍五入（结果是字符）	SELECT FORMAT(18.22,1);
UPPER(s)	将字符串转换为大写	SELECT UPPER('my');
LOWER(s)	将字符串转换为小写	SELECT LOWER('MY');
LEFT(s,n)	返回字符串s的前n个字符	SELECT LEFT('MYSQL',2);
RIGHT(s,n)	返回字符串s的后n个字符	SELECT RIGHT('MYSQL',2);

2.数字函数

函数	作用	示例
CEIL(X)	向上取整	SELECT CEIL(12.22);
FLOOR(X)	向下取整	SELECT FLOOR(12.22);
ROUND(X)	四舍五入	SELECT ROUND(12.52);
TRUNCATE(x,y)	截取数据，返回x 保留到小数点后 y 位的值（不四舍五入）	SELECT TRUNCATE(12.221,1);
POWER(m,n)	显示m的n次方值	SELECT POWER(2,4);
DIV	取整数（两数相除，结果取整）	SELECT 2 DIV 3;
MOD	取余数（两数相除，结果取余数）	SELECT 2 MOD 3;

3.日期时间函数

函数	作用	示例
NOW()	当前日期和时间	SELECT NOW();
CURDATE()	当前日期	SELECT CURDATE();
CURTIME()	当前时间	SELECT CURTIME();
DATE(d)	从日期或日期时间表达式中提取日期值	SELECT DATE('2018-08-11 18:00:00');
ADDDATE(d,n)	计算起始日期 d 加上 n 天的日期	SELECT ADDDATE('2020-04-08 16:00:00',INTERVAL 5 hour);
SUBDATE(d,n)	日期 d 减去 n 天后的日期	SELECT SUBDATE('2020-04-08',INTERVAL 2 day);
DATEDIFF(d1,d2)	计算日期 d1->d2 之间相隔的天数	SELECT DATEDIFF('2020-04-06','2020-04-04');
DATE_FORMAT(d,f)	日期格式，按表达式 f的要求显示日期 d	SELECT DATE_FORMAT('2020-04-08','%m-%d-%Y %H:%i:%S');
DAY(d)	返回日期值 d 的日期部分	SELECT DAY('2018-08-11');
WEEK(d)	计算日期 d 是本年的第几个星期，范围是 0 到 53	SELECT WEEK('2020-01-05');
YEAR(d)	返回年份	SELECT YEAR('2020-01-01');
DAYOFWEEK(d)	日期 d 今天是星期几，1 星期日，2 星期一，以此类推	SELECT DAYOFWEEK('2020-04-08');
DAYOFMONTH(d)	计算日期 d 是本月的第几天	SELECT DAYOFMONTH('2020-04-08');
DAYOFYEAR(d)	计算日期 d 是本年的第几天	SELECT DAYOFYEAR('2020-04-08');

3.其它常用函数

函数	作用	示例
DATABASE()	返回当前数据库名称	SELECT DATABASE();
USER()	返回当前数据库登录用户	SELECT USER();
VERSION()	返回当前数据库版本	SELECT VERSION();
AVG()	返回平均值	SELECT AVG(number) FROM students;
COUNT()	返回总数	SELECT COUNT(id)(number) FROM students;
MAX()	返回最大值	SELECT MAX(number) FROM students;
MIN()	返回最小值	SELECT MIX(number) FROM students;
SUM()	返回求和结果	SELECT SUM(number) FROM students;