软件测试-MySQL语言基础-4

MySQL内置函数

1.字符串函数

1.1 拼接字符串 concat(字符串，字符串…)

把 12，34，‘ab’ 拼接为一个字符串’123456ab’

• select concat(12,34,’ab’);

• 

1.2 包含字符个数 length（str）

• 如果字符串中包含utf8 格式的汉字，一个汉字 length 返回 3.

计算字符串 ‘abc’ 的长度。

• select length(‘abc’)；

• 

计算 你好 的长度

• select length(‘你好’);

• 

查询表students中name长度等于9的学生信息。

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

• 

1.3 截取字符串

1.3.1 select left（str，len）；

• 返回字符串 str 的左端 len  个字符，中文与英文字母个数 len 一致。
• str：表示需要截取的字符串
• len：表示需要截取的字符个数。

截取字符串‘我和你abc‘的左端三个字符。

• select left(‘我和你abc’,3);

• 

1.3.2 select right （str，len）；

• 返回字符串str 右端 len 个字符，中文与英文字母 len 个数一致。

截取字符串’我和你abc‘的右端三个字符

• select right(‘我和你abc’,3);

1.3.3 select substring(str,pos,len);

• 返回字符串 str 的位置 pos 起 len 个字符。
• pos 从1 开始计数。

截取字符串 ’我和你abc‘ 从第二个字符开始的3个字符。

• select substring（’我和你abc’,2,3）;

• 

截取 students 表中所有学生的姓。

• select left(name,1) from students;

• 

— 练习

1. 查询students表的card字段，截取出生年月日，显示李白的生日。
   1. select name,substring(card,7,8) from students where name = ‘李白’;
   2. 
2. 查询students表中的所有学生信息，按照生日从大到小排序。
   1. select substring(card,7,8) from students order by substring(card,7,8) desc;

   2. 

2.去除空格

2.1 ltrim（str）

• 返回删除左侧空格的字符串 str。

去除字符串 ’    abc    ‘左侧空格。

• select ltrim(‘ abc ‘)；

• 

2.2 rtrim（str）

• 返回删除右侧空格的字符串 str。

去除字符串‘    abc    ’右侧空格。

• select rtrim(‘ abc ‘);

• 

2.3 trim（str）

• 返回删除左右两侧空格的字符串 str。

去除字符串 ‘    abc    ’左右空格

• select trim(‘ abc ‘);

• 

数学函数

1.求四舍五入值（round(n,d)）

• n：表示原数。
• d：表示小数位置。默认为0.

1.635 四舍五入，保留整数。

• select round(1.635);

• 

1.635四舍五入，保留小数点后2位。

• select round(1.635,2);

• 

查询students 表中学生的平均年龄，并四舍五入。

• select round(avg(age)) from students;

• 

练习

1. 查询students表中学生的平均年龄，并从小数点后2位开始四舍五入。
   1. select round(avg(age),2) from students;

   2. 

2.随机数 rand()

• 默认返回 值位0-1.0的浮点数。

返回一个从0到1.0的小数。

• select rand();

• 

小技巧：从学生表中随机抽出一个学生。

• select * from students order by rand() limit 1;

• 

3.日期时间函数

3.1 当前时间 current_date()

返回当前日期。

• select current_date();

• 

3.2 当前时间 current_time()

返回当前时间

• select current_time();

• 

3.3 当前日期时间 now()

返回当前的日期时间

• select now();

• 

存储过程

定义

存储过程 procedure，也翻译位存储程序，是一条或者多条sql语句的集合。

创建存储过程

• 语法格式
  • create procedure 存储过程名称(参数列表)
    begin 开始
    sql（语句）
    end 结束

比如：创建查询过程 stu() ,查询students 表所有学生的信息。

• create procedure stu()
  begin
      select * from students;
  end

使用存储过程

• 语法格式
  • call 存储过程（参数列表）

使用存储过程 stu()

• call stu()；

删除存储过程

• 语法格式
  • drop procedure 存储过程；
  • drop procedure if exists 存储过程；
• 删除的时候不需要写名字后面的括号。

删除存储过程 stu()

• drop procedure  if exists stu;

视图

定义

• 对于复杂的查询，在多个地方被占用，如果需求发生了改变，需要更改 sql 语句，则需要在多个地方进行修改，维护起来比较麻烦。
• 解决：定义视图
• 视图的本质就是对查询的封装

创建视图

• 语法格式
  • create view 视图名称 as select 语句;

创建视图，名叫 stu_nan,查询所有男生信息。

• create view stu_nan as select * from students where sex = ‘男’;

使用视图

• 语法格式
  • select * from 视图名称；

使用视图 stu_nan

• select * from stu_nan;

在试图stu_nan 中查找年龄大于 25 岁的学生信息。

• select * from stu_nan where age > 25;

删除视图

• drop view 视图名称；
• drop view if exists 视图名称；

删除视图 stu_nan

• drop view if exists stu_nan;

事务

定义

• 事务广泛的运用于订单系统，银行系统等多种场景。
  • 例如：A用户和B用户是银行的储户，现在A用户要个B用户转账500元，那么需要做以下几件事。
    • 检查A的账余额>500元。
    • A账户中扣除500元
    • B账户中增加500元。
  • 正常的流程走下来，A账户扣了500，B账户加了500，皆大欢喜。那么A账户扣了钱，系统出现故障了呢？A白白损失了500，而B也没有收到本该属于他的500.
  • 以上案例中，隐藏着一个前提条件，A扣钱和B加钱，要么同时成功，要么同时失败，事务的需求就在于此。

什么是事务

所谓事务们就是一个操作序列，这些操作要么都执行，要么都不执行，他是一个不可分割的工作单位。

• 例如：银行转账工作，从一个账户扣款并使另一个账户赠款，这两个操作要么都执行，要么都不执行。
• 所以，应该把它们看作一个事务。

事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。

事务命令

• 开启事务

  • 

• 回滚事务

  • 

• 提交事务

  • 

开始事务，删除students表中 studentNo 为 001 的记录，同时删除 scores 表中 studentNo 为 001 的记录，回滚事务，两个表的删除同时放弃。

• begin;
  delete from students where studentNo = ‘001’;
  delete from scores where studentNo = ‘001’;
  rollback;

• 

开启事务，删除students表中 studentNo 为 001 的记录，同时删除 scores 表中 studentNo 为 001 的记录，回执行事务，两个表的删除同时生效。

• begin;
  delete from students where studentNo = ‘001’;
  delete from scores where studentNo = ‘001’;
  commit;

• 

索引

思考

看一本书，怎么快速知道要查看的内容在多少页？

• 给书建立一个目录；
• 通过目录的索引，快速找到内容对应的页。

当表中数据量很大时，查找数据会变的很慢。

可以给表建议一个类似书籍中的目录，从而加快数据查询效率，这在数据库中叫索引(index);

创建索引

• 语法格式
  • create index 索引名称 on 表名（字段名称（长度））
• 如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致。
• 字段类型如果不是字符串，可以不填写长度部分。

为表students 的 age 字段创建 索引，名为age_index

• create index age_index on students (age);

• 

为表students 的name 字段创建索引，名为name_index

• create index name_index on students(name(10));

• 

查询表中age 等于30 的学生

• select * from students where age = 30;

• 

• 案例中的select 语句 MySQL 会自动调用索引 age_index,从而提升查询效率。

查询表中 name 等于张飞 的学生

• select * from students where name = ‘张飞’;

• 

  案例中 的 select 语句 MySQL 会自动调用 索引 name_index，从而提升查询效率。

查询表中sex 等于 男 的学生。

• select * from students where sex = ‘男’;
• 案例中 的select 语句查询效率不会提升，因为没有为字段sex 建立任何索引。

查看索引

• 语法格式
  • show index from 表名；

查看students 表的所有索引。

• show index from students;

• 

删除索引

• 语法格式
  • drop index 索引名称 on 表名；

删除 students 表 的索引 age_index

• drop index age_name on students;

• 

索引优缺点

优点

• 索引大大提高了select 语句的查询速度。

缺点

• 虽然索引提高了查询速度，同时却会降低更新表的速度，例如对表进行insert、update、delete操作。因为更新表时，不仅要保存数据，还要保存索引文件。
• 在实际应用中那个，执行select语句的次数远远大于执行insert、update、delete语句的次数，甚至可以占到百分之八十以上，所以表建立索引是必要的。
• 在大量数据插入时，可以先删除索引，再批量插入数据，最后再添加索引，这样就可以提高数据插入的效率。

MySQL命令行

1. Windows cms命令窗口连接到MySQL

• 进入mysql.exe所在目录
• 输入以下命令 mysql -u[主机名] -p
  • 参数说明

  • 

连接到本地的mysql，用户名root

• mysq -u root -p

连接到目标主机 [IP地址]的mysql，用户民root。

• mysql -h[ip地址] -u root -p

Windows cmd命令窗口 显示mysql 中utf8 格式乱码的解决方案

• Windows 的cmd 命令 窗口默认字符集为gbk，所以再cmd 中执行mysql命令连接到mysq后，需要执行如下命令；
  • set name gbk;
• 注意：
  • 后面一定要以分号结尾，
  • 如果通过Linux命令行连接mysql，默认字符集为utf8，不需要单独设置字符集。

2. MySQL命令行使用简介

• 连接到MySQL后，要操作表，一定要先选择表所在的数据库。
  • 语法格式
    • use [数据库名]

选择数据库 abc 

use abc

• 命令行中要执行sql语句，以;结尾。

在mysql命令行里，查询students 表。

select * from students;

• 退出 MySQL 命令行语法
  • 语法格式
    • exit

练习

通过mysql命令行，登录到MySQL，把students 表中 studentNo 为 003 的学生年龄修改为 25，执行查询语句，查看修改后的年龄是否正确，退出mysql命令行。

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

显示已有数据库

• 语法格式
  • show databases;

创建数据库

• 语法格式
  • create database 数据库名 default charset[默认字符集]；

建立一个数据库，名为 mytest ，默认字符集 为 utf8。

• create database mytest default charse utf8;

• 

删除数据库

• 命令格式
  • drop database 数据库名；

删除数据库 mytest

• drop database mytest;

练习

使用mysql命令执行如下操作：

1. 登录MySQL
2. 创建数据库mydb，默认字符集为utf8
3. 在mydb数据库中，创建表mytable，建表语句如下：
   1. create table mytable(fal varchar(20));

增加新用户

• 用root 身份登录mysql；
• 语法格式
  • grant all on 数据库名.表名 to 用户名@’登陆主机’ identified by ‘密码’ with grant option;
  • 在MySQL 8.0及以上版本中，创建用户和授权需要分开执行，不再支持旧版本中GRANT语句同时创建用户和设置密码的语法。以下是新版的标准操作步骤：
  • CREATE USER ‘用户名’@’登陆主机’ IDENTIFIED BY ‘密码’;
  • GRANT ALL PRIVILEGES ON 数据库名.表名 TO ‘用户名’@’登陆主机’ WITH GRANT OPTION; 
    • grant on：代表为用户赋权。
    • 数据名：可以是* ，代表所有数据库。
    • 表名：可以是* ，代表所有表，如：数据库.表名，写为*.* 代表可以对所有数据库和所有表进行操作。
    • to 用户名：指定要创建的用户的名称。
    • @’登陆主机’
      • @’localhost’：代表只能在本机登录，
      • @’%’：代表可以远程登录。
    • identified by ‘密码’：指定用户登录密码。
    • with grant option：该用户是否能把权限分配给其它用户。

建立一个用户，可以操作所有数据库和表，用户名test，该用户可以远程登录，密码123456，该用户可以为其它用户分配权限。

• CREATE USER ‘test’@’%’ IDENTIFIED BY ‘123456’;
• GRANT ALL PRIVILEGES ON *.* TO ‘test’@’%’ WITH GRANT OPTION;

建立一个用户，可以操作所有数据库和表，用户名test，该用户只能本机登录，密码123456，该用户可以为其它用户分配权限。

• CREATE USER ‘test’@’localhost’ IDENTIFIED BY ‘123456’;
• GRANT ALL PRIVILEGES ON *.* TO ‘test’@’%’ WITH GRANT OPTION;

修改用户密码

• 如果用户名不存在，grant 语句创建新用户，如果用户名存在，grant可以修改用户名密码以及用户权限等。
• 示例：
  • ALTER USER ‘test’@’%’ IDENTIFIED BY ‘新密码’;
    FLUSH PRIVILEGES; — 可选，确保权限立即生效

删除用户

第一步：使用root用户身份登录MySQL

• 语法：mysql -u root -p

第二步：选择mysql 数据库

• 语法：use mysql

第三步：回收用户权限

• 语法：
  • revoke all on *.* from 代表回收指定用户权限。
  • 语法：revoke all on *.* from 用户名@’登陆主机’;

第四步：删除用户

• 语法：delete from user where user = ‘用户名’;

第五步：刷新权限

• 语法：flush privileges;

删除用户test