软件测试人员常用的sql语句（四）

MySQL数据库

1. MySQL内置函数

1.1 字符串函数

1.1.1 concat拼接字符串函数

• concat参数1,参数2,参数3,参数n)
  • 参数可以是数字，也可以是字符串
  • 把所有的参数连接成一个完整的字符串

例子1：把12，334，‘ab’拼接成一个字符串‘12334ab’
select concat(12, 334, 'ab');

1.1.2 length返回字符串字符的个数

• 一个utf8格式的汉字，length返回3

-- 例子1：计算字符串‘abc’的长度
select length('abc');
-- 例子2：计算字符串‘我和你’的长度
select length('我和你');
-- 例子3：计算字符串‘我和you’的长度
select length(‘我和you’);
-- 例子4： 查询表students中name长度等于9
--（三个utf8格式的汉字）的学生信息

1.1.3 mysql内置函数可以在where条件后面使用;

select * from students 
where length(name) = 9;

1.1.4 left从字符串的左侧截取指定数量字符

• left(字符串, n)
  • n代表字符串左侧截取n个字符

例子1：截取字符串‘我和你abc’左端3个字符
select left('我和你abc', 3);
例子2：截取字符串‘我和你abc’左端4个字符
select left('我和你abc', 4);
例子3：截取字符串‘abc我和你’左端4个字符
selectt left('abc我和你', 4);

1.1.5 left从字符串的右侧截取指定数量字符

• right(字符串, n)
  • n代表字符串右侧截取n个字符

例子1：截取字符串‘我和你abc’右端3个字符
select right('我和你abc', 3);
例子2：截取字符串‘我和你abc’右端4个字符
select right('我和你abc', 4);
例子3：截取字符串‘abc我和你’右端4个字符
selectt right('abc我和你', 4);

1.1.6 substring从字符串指定位置截取指定数量字符

• substring(字符串, 起始位置, n)
  • 起始位置从1开始
  • n代表截取的数量

例子1：截取字符串‘我和你abc’从第二个字符开始的3个字符
select right('我和你abc',2, 3);
例子2：截取字符串‘我和你abc’从左侧开始的3个字符
select right('我和你abc', 1, 3);
例子3：截取字符串‘abc我和你’从第4个字符开始的1个字符
selectt right('abc我和你', 4, 1);

1.1.7 内置函数可以用在select显示的字段名中

例子1：截取studets表中所有学生的姓
select left(name, 1) from students;
select substring(name. 1, 1) from studets;
例子2：查询students表的card字段，截取出生年月日，
显示李白的生日
select substring(card, 7, 8) 
from students
where name = '李白'; 
例子3：查询students表学生的所有信息，
将生日由大到小排列
select * from studetns 
order by substring(card, 7, 8);

1.1.8 ltrim去除字符串左侧的空格

• ltrim(带空格的字符串)

例子1：去除字符串‘ abcd’左侧空格
select ltrim('    abcd');

1.1.9 rtrim去除字符串右侧的空格

• rtrim(带空格的字符串)

例子1：去除字符串‘abcd      ’右侧空格
select rtrim('abcd      ');
select concat(rtrim('abcd      '), '测试字符')

1.1.10 trim去除字符串两侧空格

• trim(带空格的字符串)

例子1：去除字符串‘      abcd      ’右侧空格
select rtrim('      abcd      ');
例子2：
select concat(123, trim('  abcd  '), 'sdsds');

1.2 数学函数

1.2.1 round四舍五入

• round(数字, d)
  • d代表要保留的小数位，省略d默认为0

例子1：1.653四舍五入，保留整数位
select round(1.653);
例子2：1.653四舍五入，保留2位小数
select round(1.653, 2);
例子3：查询students中学生的平均年龄，并四舍五入，
保留两位小数
select round(avg(age), 2) from students;

1.2.3 rand随机数

• rand()
  • 每次运行会产生一个从0到1之间的浮点数
• 经常用rand()对一张表进行随机排序
  • order by rand()

select rand();
-- 小技巧：从学生表中随机抽取一个学生
select * from students order by rand() limit 1;

1.3 日期时间函数

1.3.1 current_date返回系统日期

• current_data()

1.3.2 current_time返回系统时间

• current_time()

1.3.3 返回系统日期和时间

• now()

例子：日期和时间函数
DROP TABLE IF EXISTS a;
CREATE TABLE a (
	id INT PRIMARY KEY NOT NULL,
	indate datetime
);

insert into a VALUES(1, '2021-6-25 14:16:01');
INSERT INTO a VALUES(3,now());

SELECT * FROM a;
id	indate
1	2021-06-25 00:33:08
2	2021-06-25 00:33:18
3	2021-06-25 00:33:26

2. 储存过程

2.1 定义

• 简单的说，就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法；
  ps:存储过程跟触发器有点类似，都是一组SQL集，但是存储过程是主动调用的，且功能比触发器更加强大，触发器是某件事触发后自动调用；

2.2 创建储存过程

- 例子1：创建储存过程stu(),查询students表所有学生
create procedure stu()
begin
	select * from students;
end

2.3 使用储存过程

• 调用储存过程：

call stu()

2.4 删除储存过程

• 删除储存过程时不用在名字后写()

drop procedure stu;
drop procedure if exists stu;

3. 视图

3.1 定义

• 视图就是对select语句的封装
• 视图可以理解为一张只读的表，针对视图只能用select，不能用delete和update

3.2 创建视图

CREATE VIEW stu_nan AS
SELECT * from students WHERE sex = '男';

3.3 使用视图

SELECT * from stu_nan;
SELECT * FROM stu_nan WHERE age > 30;

SELECT * FROM stu_nan INNER JOIN scores ON 
stu_nan.studentNo = scores.studentNo;

3.4 删除视图

DROP VIEW stu_nan;
DROP VIEW if EXISTS stu_nan;

4. 事务

4.1 为什么要有事务

• 为什么使用事务？
  举一个例子简单说明一下，当用户在网上买某本书时，它的数量有10本，每一本10元，用户账户总共只有90元，而用户要购买10本书，此时数量足够，而账户余额不够，如果不加事务在后台，数量会减少10本，而账户余额却不变，显然这不符合实际，因此要使书籍数量以及账户余额的变化同步就需要配置事务，以保证他们同时成功，同时失败。

4.2 什么是事务

• 所谓事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。
• 事务是多条更改数据操作的sql语句集合。
• begin — 开始事务
• rollback — 回滚事务，放弃对表的修改
• commit — 提交事务，对表的修改生效
• 没有写begin代表没有事务，没有事务的表操作都是实时生效
• 如果只写了begin，没有rollback，也没有commit，系统推出，结果是rollback

4.3 事务命令

• 回滚事务操作

-- 事务
-- 删除students表中为001的记录；
-- 同时删除scores表中为001的记录；
-- 回滚事务，两个删除同时放弃；

BEGIN;
DELETE FROM students WHERE studentNo = 1;
DELETE FROM scores WHERE studentNo = 1;
-- 回滚事务，放弃删除
ROLLBACK;
-- 查询回滚是否成功语句
SELECT * FROM students;
SELECT * FROM scores;

5. 索引

5.1 思考：

• 看一本书，怎么快速知道要查看的内容在多少页？
  - 给书建立一个目录；
  - 通过目录的索引，快速找到内容对应的页。
• 当表中的数据量很大时，查找数据会变得很慢；
• 可以给表建立一个类似书籍中的目录，从而加快数据查询效率，这在数据库中叫索引（index）；

5.2 创建索引

• 语句 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));

5.3 查看索引

-- WHERE 条件后面的字段，数据库系统自动会查找是否有索引
SELECT * from students WHERE age = 30;
SELECT * from students WHERE `name` = '李白';
-- 无索引，不调用
SELECT * FROM students WHERE sex = '女';

5.4 删除索引

DROP INDEX age_index ON students;
DROP INDEX name_index ON students;
-- 查询students表中的索引
show index from students;

5.5 索引优缺点

• 优点：
  - 大大提高了select语句的查询速度；
• 缺点：
  - 降低update、delete和insert语句的执行速度；
• 项目80%以上都是select，所以index必须的；
• 在实际工作中如果涉及到大量数据修改操作，修改之前可以将索引删除，修改完之后再把索引建立起来。

6. MySQL命令行

6.1 windows/linux cmd 命令窗口连接到mysql与退出mysql

6.2 MySQL命令行使用简介

7. 基于命令行的数据库管理操作

7.1 显示已有数据库

7.2 创建数据库

• create database 数据库名称 default charset utf8;
  

7.3 删除数据库

7.4 增加新用户

• grant all on 数据库名.表名 to @‘登录主机’ identified by ‘密码’ with grant option；
  - grant all on:代表为用户赋权；
  - 数据库名：可以是*，代表所有数据库；
  - 表名：可以是*，代表所有表，如数据库.表名 写为 . 代表可以对所有数据库和所有表操作；
  - to 用户名：指定要创建用户的名称；
  - @‘登录主机’：@‘localhost’：代表只能在本机登录，@’%’：代表可以远程登录；
  - with grant option：该用户是否能把权限分配给其他用户。

7.5 修改用户密码

• 本机登录密码修改（如果该用户存在就修改密码；如果该用户不存在就是创建用户）
  - grant all on 数据库名.表名 用户名 to @‘登录主机’ identified by ‘111111’ with grant option；
• 远程登录密码修改
  - grant all on 数据库名.表名 用户名 to @’%’ identified by ‘111111’ with grant option；

7.6 删除用户

• 第一步：用root身份登录mysql
  - mysql -u root -p
• 第二部：选择mysql数据库
  - use mysql;
• 第三步：回收用户test权限
  - revoke all on . from test@‘localhost’;
  - revoke all on . from test@’%’;
• 第四步：删除用户test
  - delete from user where user = ‘test’;
• 第五步：刷新权限
  - flush privilleges;
• 本章内容思维导图总结如下：