软件测试--Mysql快速入门

文章目录

- 软件测试-mysql快速入门
- - sql主要划分
  - mysql常用的数据类型
  - sql基本操作
  - 常用字段的约束：
  - 连接查询
  - mysql内置函数
  - 存储过程
  - 视图
  - 事务
  - 索引

软件测试-mysql快速入门

sql主要划分

sql语言主要分为：

DQL：数据查询语言，用于对数据进行查询，select
DDL:数据定义语言，进行数据库，表的管理，create、drop
DML：数据操作语言，对数据进行增删改，insert，update，delete
TPL：事务处理语言，对事物进行处理，包括begin transaction，commit，rollback

mysql常用的数据类型

整数：int
小整数：tinyint 8位（-128，127）（0-255）
小数：decimal，如decimal（5，2）表示共存5位数，小数占2位，不能超过2为，整数3位不能超过三位
字符串：varchar ，一个中文和一个字母都占一个字符
日期时间：datetime, (1000-01-01 00:00:00~9999-12-31 23:59:59)

--书写格式
create table 表名(

	字段名  数据类型，

	字段名 数据类型

	...

);

sql基本操作

Insert 添加数据

sql语句中，字符串用单引号‘’或者双引号“”引起来，数字可以忽略引号

insert into 表名 values(…);-- 插入表所有字段

insert into 表名(…) values(…);-- 插入表中指定字段

insert into 表名 values(…),(…);-- 插入多条数据

update 修改数据

update 表名 set 字段1=值，字段2=值2… where 条件；

Delete删除表中的记录

delete from 表名 where 条件；

truncate 删除表中的记录

truncate table 表名；（删除表的所有数据，保留表结构）

delete 和truncate的区别？

在速度上，truncate》delete；
如果想删除部分数据用delete，注意带上where子句
如果想保留表而将所有数据删除，自增长字段恢复从1开始，用truncate

drop table 删除表

drop table 表名；

drop table if exists 表名；

常用字段的约束：

主键（primary key）：值不能重复，Auto_increment代表值自动增长
非空（not null）：字段不允许为空
唯一（unique）：字段的值不允许重复
默认值（default）：当不填写此值时会使用默认值

语法格式：

create table 表名(

	字段名 数据类型 约束，

	字段名 数据类型 约束

	...

);

带有 primary key（主键）的字段，值不能重复；
Auto_increment 为自增长

语法

create table 表名(
	字段名 数据类型 primary key auto_increment,
	字段名 数据类型 约束
	...
);

insert into d (name,age)values('李明',12),('李明2',13),('李明3',13);
# 主键并且自增长字段的表，insert语句插入数据，如果不指定id字段，id字段的值会自动增长

如果插入数据时，插入所有字段，但又没写自增长字段的值，insert语句会出错

解决方案
使用占位符，通常使用0或者null来占位。从而实现自增长。
非空
- 带有not null
唯一
- unique，字段值不能重复
default
- 设置字段默认值
别名 — as（也可以用空格代替）
去重 — distinct
比较运算符
- 等于 =
- 大于 >
- 小于 <
- 小于等于 <=
- 不等于 != 或者<>
逻辑运算符
- and
- or
- not

模糊查询

like
%表示任意多个字符
_表示一个任意字符

select * from students where name like '孙%'
select * from student where name like ‘孙_’

范围查询

in 在一个非连续的范围内

select * from student where home in ('北京','上海','天津')

between … and … 表示在一个连续的范围内

select * from student where age between 25 and 30;

空判断
- 注意：
  
  null 与 ‘’ 是不同的
  
  null 代表什么都没有
  
  ''代表长度为0的字符串
- 判断空 is null
- 非空 is not null
排序
- asc（默认，升序）
- desc （降序）
- ```
select * from 表名 order by 字段1 
```
聚合
- count() 计算总记录数
- max() 最大值
- min()最小值
- sum()求和
- avg()求平均

数据分组

分组

select 字段,聚合函数.. from 表名 group by 字段

分组后筛选

select 字段,聚合函数.. from 表名 group by 字段
having 字段

注意：

对比where与having
- where是对from后面指定的表进行数据筛选，属于对原始数据的筛选
- having是对group by的结果进行筛选
- having后面的条件中可以用聚合函数，where后面的条件不可以使用聚合函数

数据分页显示

获取部分行

select * from 表名 limit start,count;
从start开始，获取count条数据
select * from 表名 limit num
num表示获取数据的条数，默认从第一行开始

分页

select * from students limit(n-1)*m,m;
已知每页m条数据，查询第n页数据

连接查询

定义
- 当查询结果来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的结果返回；
- 连接查询可以通过连接运算符（连接条件）可以实现多个表查询
- 内连接：查询的结果只显示两个表中满足连接条件的部分
左连接：查询的结果为两个表匹配到的数据加左表特有的数据，对于右表中不存在的数据使用null填充
右连接：查询的结果为两个表匹配到的数据加右表特有的数据，对于左表中不存在的数据使用null填充

内连接

语法1

select * from 表1 inner join 表2 on 表1.字段=表2.字段

语法2

select * from 表1，表2 where 表1.字段=表2.字段

select * from 表1 inner join 表2 on 表1.字段=表2.字段
where 表1.字段=？

select * from 表1 inner join 表2 on 表1.字段=表2.字段
where 表1.字段1=？and 表1.字段2=？

select * from 表1 inner join 表2 on 表1.字段=表2.字段
inner join 表3 on 表3.字段=表2.字段
where 表1.字段=？

左连接
- select * from 表1 left join 表2 on 表1.字段=表2.字段
右连接
- select * from 表1 right join 表2 on 表1.字段=表2.字段
自关联：
- 设计省信息的表结构provinces
  - proid：省编码
  - pname：省名称
- 设计市信息的表结构citys
  - cityid：市编号
  - cname：市名称
  - proid：市所属的省编号
- citys表的proid表示城市所属的省，对应着provinces表的proid值
- 问题：能不能将两个表合成一张表呢？
  - 思考：两个表之间字段类型都是一样，就相差一个proid
  - 意义：存储的都是地区信息，而且每种信息数据都是数量有限的，没必要增加一个新表，或者将来还要新增区乡信息，加表会增大开销。
  - 答案：
    - 可以定义个areas表，结构如下：
      - id
      - name
      - pid
    - 表中的一条记录，可以记录省，也可以记录市
    - 记录省的时候，字段说明
      - id：代表省id
      - name：省名
      - pid：因为省没有所属省份，所以pid写为null
    - 记录为市时，字段说明
      - id 代表市
      - name 代表市名称
      - pid：代表市所属的省id
    - ==这就是自关联，表中某个字段，关联表中另一个子弹，但是他们的业务逻辑含义不一样，城市信息pid引用的是省信息的id
    - 表的结构不变，也可扩充县，镇，区信息

子查询

定义：在一个select语句中，嵌入了另一个select语句，那么被嵌入的select语句被称为子查询
主查询
- 外层第一条select语句为主查询

格式1

select avg(age) from stu;-- 结果只有一行一列，一个值
select * from stu where age>(select avg(age) from stu);
称为标量子查询

格式2

select studentNo from students where age=30；-- 一列（一列多行）

select score from sc where studentNo in (select studentNo from students where age=30);
称为列子查询

表级子查询：子查询返回的结果是多行多列（一个表），这种为表级子查询
select * from (select * from stu where sex='女') stu inner join sc on stu。studentNo = sc.studentNo

mysql内置函数

字符串函数
- 拼接字符串函数从concat(str1,str2,…)
- 返回字符个数 length(str)
  - 如果字符串中包含utf8格式汉字，一个汉字length返回3
  - select length(‘陈’)；-- 3
- 截取字符串 left(str,len);返回字符串str的左端len个字符，中文与英文字母个数len一致
- right(str,len)返回字符串str的右端len个字符，中文与英文字母个数len一致
- 去除空格 ltrim(str);删除左侧str中的空格
- 去除空格 rtrim(str);删除右侧str中的空格
- 去除空格 trim(str);删除左右侧str中的空格
- 四舍五入 round(n,d);n为原数，d为小数，默认为0
- 随机数 rand() 值为0-1.0之间的浮点数
- 日期时间函数
  - current_date()当前日期
  - 当前时间current_time()
  - 当前日期时间now()

存储过程

定义
- 存储过程procedure，也称为存储程序，是一条或者多条sql语句的集合
创建存储过程
- 语法
- create procedure 存储过程名称(参数列表)
  
  begin
  
  sql 语句
  
  end
  
  例1：创建查询过程stu(),查询students表所有学生信息
  
  第一步：设置分割符（Navicat中不需要）delimiter//
  
  第二步：创建存储过程 create procedure stu()
  
  begin
  
  select * from students;
  
  end
  
  第三步：还原分割符（Navicat中不需要）
  
  //
  
  delimiter
使用存储过程
- 语法
- call 存储过程（参数列表）；
  
  使用存储过程stu()
  
  call stu();
删除存储过程
- 语法
- drop procedure 存储过程
  
  drop procedure if exists 存储过程；
  
  例1：删除存储过程stu
  
  drop procedure stu;
  
  drop procedure if exists stu;

视图

定义
- 对于复杂的查询，在多个地方被使用，如果需求发生了改变，需要更改sql语句，则需要在多个地方进行修改，维护起来非常麻烦；
- 解决：定义视图；
- 视图本质就是对查询的封装；
创建视图
- 语法
- create view 视图名称 as select语句；
  
  例1：创建视图，名叫stu_nan,查询所有男生信息
  
  create view stu_nan as select * from students where sex=‘男’;
使用视图
- 语法：
  
  select * from 视图名；
  
  例1：使用视图stu_nan
  
  select * from stu_nan;
  
  例2：在视图stu_nan 中查找年龄大于25岁的学生信息
  
  select * from stu_nan where age>25;
删除视图
- 语法
- drop view 视图名称；
  
  drop view if exists 视图名称；

事务

一、为什么要有事务
事务广泛的运用于订单系统、银行系统等多种场景;
例如:A用户和 B用户是银行的储户，现在A要给B转账500元，那么需要做以下几件事:
1、检查 A的账户余额>500 元;
2、A 账户中扣除 500 元;
3、B 账户中增加 500 元;
正常的流程走下来，A 账户扣了 500，B 账户加了 500，皆大欢喜。那如果 A账户扣了钱之后，系统出故障了呢?A白白损失了 500，而B也没有收到本该属于他的 500。以上的案例中，隐藏着一个前提条件:A扣钱和B加钱，要么同时成功，要么同时失败，事务的需求就在于此。
二、什么是事务
所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。
例如:银行转帐工作:从一个帐号扣款并使另一个帐号增款，这两个操作要么都执行，要么都不执行。所以，应该把他们看成一个事务
事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性
三、事务命令
- 开启事务
  - begin;
    说明：开启事务后执行修改 UPDATE 或删除 DELETE 记录语句，变更会写到缓存中而不会立刻生效。
- 回滚事务
  - rollback;
    
    说明：放弃修改。
- 提交事务
  - commit;
    
    说明：将修改的数据写入实际的表中。

例 1:开启事务,删除 students 表中 studentNo 为 001 的记录,同时删除 scores表中 studentNo 为 001 的记录，回滚事务，两个表的删除同时放弃
begin;
delete from students where studentNo='001',
delete from scores where studentNo = '001'.
rollback;

例 2:开启事务,删除 students 表中 studentNo 为 001 的记录,同时删除 scores表中 studentNo 为 001 的记录，提交事务，使两个表的删除同时生效
begin;
delete from students where studentNo ='001';
delete from scores where studentNo ='001';
commit;

索引

一、思考:
看一本书，怎么快速知道要查看的内容在多少页?给书建立一个目录;通过目录的索引，快速找到内容对应的页。
当表中数据量很大时，查找数据会变得很慢;可以给表建议一个类似书籍中的目录，从而加快数据查询效率，这在数据库中叫索引(index)
创建索引
- create index 索引名称 on 表名(字段名称(长度));
  
  如果指定字段是字符串，需要指定长度,建议长度与定义字段时的长度一致;
  字段类型如果不是字符串，可以不填写长度部分。
  
  例 1:为表 students 的 age 字段创建索引，名为 age index
  
  create index age_index on students(age);
  
  例 2:为表 students 的 name 字段创建索引，名为 name_index
  
  create index name index on students(name(10));
  
  例 3:查询表中 age 等于 30 的学生
  
  select * from students where age=30
- 例 3中的 SELECT 语句 mysql 会自动调用索引age_index,从而提升查询效率
  
  例 4:查询表中 name 等于’张飞’的学生
  select * from students where name=‘张k’;
  
  例4中的 SELECT 语句 mysql会自动调用索引name index,从而提升查询效率
  
  例 5:查询表中 sex 等于’男’的学生
  select* from students where sex=‘男’,
  
  例5 中的 SELECT 语句查询效率不会提升,因为没有为字段 sex 建立任何索引
三、查看索引
- show index from 表名;
- 例 1:查看 students 表的所有索引
  show index from students;
四、删除索引:
- drop index 索引名称 on 表名;
  例 1:删除 students 表的索引 age index
  drop index age_index on students;
五、索引优缺点

优点:
索引大大提高了 SELECT 语句的查询速度:
缺点:
虽然索引提高了查询速度，同时却会降低更新表的速度，例如对表进行INSERT、UPDATE 和 DELETE 操作。因为更新表时，不仅要保存数据，还要保存索引文件;
在实际应用中，执行 SELECT 语句的次数远远大于执行 INSERT、UPDATE 和DELETE 语句的次数，甚至可以占到 80%~90%,所以为表建立索引是必要的。在大量数据插入时，可以先删除索引,再批量插入数据,最后再添加索引,这样就可以提高数据插入的效率，