MySQL基础

本文介绍软件测试MySQL基础
本文参考以下内容 MySQL数据库基础

一 、使用场景

1.系统测试
界面上判断数据的正确性，是否需要查数据库
界面上有问题，无法判断是哪一类的问题，数据库可以做辅助定位
修改数据库数据，包括修改有效期时间，修改类型
2.接口测试
大部分时候没有界面辅助，需要数据库确认
3.制造测试数据
自动化测试 ——需要参数化数据
性能测试—— 成千上百个去压测
系统测试—— 分页测试 —— 每一页需要10-20条数据，数据库insert

二、语法基础

**SQL语句的分类**
DQL（数据查询语言）: 查询语句，凡是select语句都是DQL。
DML（数据操作语言）(Data Manipulation Language)：insert delete update，对表当中的数据进行增删改。
DDL（数据定义语言）：create drop alter，对表结构的增删改。
TCL（事务控制语言）：commit提交事务，rollback回滚事务。(TCL中的T是        Transaction)
DCL（数据控制语言）: grant授权、revoke撤销权限等。

2.1 常用操作

		1) mysql> select database(); 查看当前使用的是哪个数据库
​
        2) mysql> select version(); 查看mysql的版本号。
​
        3) \c  命令，结束一条语句。
​
        4) exit 命令，退出mysql。
        
        5)show tables;(显示当前数据库中的所有的表)
        
        6)create {database|schema} [if not exists] 数据库名//创建数据库
        
        7)show databases ;显示当前有的所有数据库
        
        8)use db_name 选择要使用的数据库名(该语句可以用来从一个数据库转移到另一个数据库，用create语句创	建的数据库不会自动成为当前数据库，需要用use语句指定当前的数据库)
        
        9)drop database [if exists ] sqlname;删库
        
        10)show create database sqlname 查看数据库的定义
        
        11)对于数据库的修改：alter database db_name default character set charset_name

2.2 MySQL数据表操作

2.1.1创建数据表

#新建表语句的语法格式： 
create table 表名(
​
字段名1 数据类型,
​
字段名2 数据类型,
​
字段名3 数据类型,
​
....
​
);

show create table 表名  #查看数据表的定义

create table 表名 as select 语句; #将查询结果当做表创建出来

2.2.2表的修改

#修改数据表的名称：
	1)alter table 旧表名 rename 新表名
#添加新的字段：
	2)alter table 表名 add 字段名 数据类型...;
#修改字段：
	3)alter table 表名 change 原字段名 新字段名 数据类型...；(修改字段)
	4)alter table 表名 modify 字段名 新数据类型
#删除字段：
	5)alter table 表名 drop 字段名（删除字段）
#在创建完表之后添加主键约束：	
	6)alter table 表名 add constraint 主键名 primary key 表名（主键字段）
#在创建完表之后进行外键约束：	
	7)alter table 表名 add constraint 外键名 foreign key（外键字段）references 关联表名（关联字段）
#查看表的结构	
	8)desc table_name
	  如：desc t_student;
        +-------+--------------+------+-----+---------+-------+
        | Field | Type         | Null | Key | Default | Extra |
        +-------+--------------+------+-----+---------+-------+
        | no    | int(11)      | YES  | PRI | NULL    |       |
        | sname | varchar(255) | YES  |     | NULL    |       |
        | age   | int(11)      | YES  |     | NULL    |       |
        | email | varchar(255) | YES  |     | NULL    |       |
        +-------+--------------+------+-----+---------+-------+
#删除数据表：
  	9)drop [temporary] table [id exists]tbl_name~~~可以一次删除多个表
  	基本表删除之后，数据，表上的视图，索引都将删除
#复制数据表：	
	10)create table new_tbl_name like old_tbl_name

2.3 MySQL数据操作语言 --增、删、改

insert 数据，可以进行一下操作：
指定字段增加
多条数据
一条数据

#插入新的记录：
insert [][][low_priority|delayed|high_priority] [ignore] [into] tbl_name
[(col_name,~~~)] values({expr|default},……) | set col_name=(expr|default)

#1)一般插入语法如下:
insert into 表名(字段名1,字段名2,字段名3,....) values(值1,值2,值3,....);

#要求：字段的数量和值的数量相同，并且数据类型要对应相同。

#2)
insert into tab_name set 字段名1="value",字段名2="value";

#3）对于图片的储存
使用路径：LOAD_FILE('D:\images\picture.jpg')

update数据：
可以指定数据修改
可以修改表一列所有数据

#基础语法
update 表名 set 字段名1=值1,字段名2=值2... where 条件;
#注意当没有where条件时，会更新表中的所有数据

#更新表记录：
update [low_priprity] [ignore]  tbl_name set  clo_name1=expr1

更新多个表的数据：
update[low_priority][ignore] table_references set col_name=expr1[,col_name2=expr2[,...]]
[where where_definition]

不同约束条件的修改：
update table_name set 字段名 = case
		when 条件一                    	                            
			then 结果一                            
		else   结果二                          
		end

#删除表区分要点
delete		清除表里面的数据
drop 		删除库，也可以用来删除表数据和结构

#带条件的记录删除：
delete [low_priority][quick(快速删除)][ignore] from 表名 where 条件 [order by....(ASC||DESC)][limit row_count] ;

#删除表记录：delete [low_priority] [quick] [ignore] from tbl-name 

2.4 MySQL数据查询语言

2.4.1基础查询

select * from 表名  #(能够显示表中所有数据的信息，但一般不常用）

select 字段名1,字段名2,字段名3,.... from 表名;#（字段可以参与数学运算）

#给查询结果的列重命名:
select ename,sal * 12 as yearsal from emp;

select ename,sal * 12 （as可以省略） '年薪' from emp;

#条件查询：
select  字段,字段...  from  表名  where  条件;

select ename from emp where sal = 5000;

// between...and...是闭区间 [1100 ~ 3000]
select ename,sal from emp where sal between 1100 and 3000;


--在数据库当中NULL不是一个值，代表什么也没有，为空。

/*空不是一个值，不能用等号衡量。必须使用 is null或者is not null*/

select ename,sal,comm from emp where comm is null;

select ename,job from emp where sal in(800, 5000); 
# in后面的值不是区间，是具体的值。

ISLOWER(Sdept)将查询出来的结果用小写字母表示
例如：
SELECT Sname，‘Year of Birth: '，2000-Sage，ISLOWER(Sdept) FROM Student;

#Where字句是不能出现聚集函数的，因为where是对每一个元组进行过滤，而不是对每一个集合进行过滤

2.4.2 模糊查询

找出名字当中含有O的？（在模糊查询当中，必须掌握两个特殊的符号，一个是%，一个是_)
%代表任意多个字符，_代表任意1个字符。
可以使用 like 和 not like
select ename from emp where ename like '%O%';
select ename from emp where ename like '_O%';

2.4.3排序

select 字段名 from 表名 order by 字段;(默认是升序)

​select 字段名 from 表名 order by 字段 asc;（asc 表示升序）

select 字段名 from 表名 order by 字段 desc;（desc 表示降序）

select ename,sal from emp order by sal desc , ename asc;

注意：越靠前的字段越能起到主导作用。只有当前面的字段无法完成排序的时候，才会启用后面的字段。

2.4.4统计函数

对某一组数据进行操作（在where操作之后）：（又名多行处理函数）自动忽略null

distinct 关键字去除重复记录（只能出现在所有字段的最前面） 

count 计数 count（*）计数总记录的条数 
	count (字段名):表示统计字段名中不为null的数据总量
	count (1) ,统计总记录条数 ，括号中 1 表示一个常量

sum() 求和

avg() 平均值

max() 最大值

min() 最小值

2.4.5 group by 分组,having 过滤

groupby：按照某个字段或者某些字段进行分组

having：having是对分组之后的数据进行再次过滤

规则：
在SQL中，当你使用GROUP BY子句时，SELECT语句中出现的非聚合列必须包含在GROUP BY子句中。
这是因为GROUP BY子句用于对数据进行分组，并且SELECT语句中的列是在每个分组上进行的计算。

SELECT CustomerID, SUM(TotalAmount) AS TotalSales  
FROM Orders  
GROUP BY CustomerID  
HAVING SUM(TotalAmount) > 1000;

2.4.6 语句执行顺序

select               

..

from                

..

where            

..

group by        

..

having               

..

order by        

...
limit {[offset,]row_count | row_count OFFSET offset}    

## 查询语句

2.4.7 连接查询

内连接：

1）等值连接

2）非等值连接

3）自连接

外连接：

1）左外连接

2）右外连接

全连接(很少用)

笛卡尔积现象：当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积

内连接:

select 字段名 from emp e (inner) join  dept d on e.deptno=d.deptno(连接条件);

select   e.ename,e.sal,s.grade  
from emp e inner join salgrade s 
on  e.sal between s.losal and s.hisal;

自连接：最大的特点是：一张表看做两张表。自己连接自己。

select 字段名 from emp e1 join emp e2 on 条件

外连接

内连接：

假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。

AB两张表没有主副之分，两张表是平等的。

外连接：

假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中

的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。

外连接的分类？

左外连接（左连接）：表示左边的这张表是主表。

右外连接（右连接）：表示右边的这张表是主表。

//左外连接
select 
	a.ename '员工', b.ename '领导'
from
	emp a
left outer join
	emp b
on
	a.mgr = b.empno;

//右外连接
select 
	a.ename '员工', b.ename '领导'
from
	emp b
right outer join
	emp a
on
	a.mgr = b.empno;

多表连接

select
	e.ename,d.dname,s.grade
from 
	emp e
inner join 
	dept d
on 
	e.deptno=d.deptno
inner join 
	salgrade s
on 
	d.depid = s.depid;
//emp表先和dept表连接，然后和salgrade表连接

内连接

左外连接

右外连接

全外连接（极少使用）

2.4.8 嵌套查询

select..from  (select) ;

   
SELECT
	t.*,
	s.grade 
FROM
	( SELECT deptno, avg( sal ) AS avgsal FROM emp GROUP BY deptno ) t
	JOIN salgrade s ON t.avgsal BETWEEN s.losal AND s.hisal;

 //语句中嵌入select子句
 

2.4.9 union

可以将查询结果集相加

#将两张表整合在一起
select ename from emp where 条件一
union 
select dname from dept where 条件二
//拼接的数据必须两两相对应。不能一张表是一个数据，另一张表是两个数据，这样无法拼接！

2.4.10 limit

#(取出结果集中的部分数据)

limit startindex length

startindex表示起始位置，从0开始，0表示第一条数据

length表示取几个

limit执行的顺序是最后一个

SELECT * FROM table_name LIMIT 5, 10; #从第5条数据开始，取后面10条数据

2.5 MySQL函数

1数学函数

abs（）绝对值函数
round（）取整函数
sqrt（）求平方根函数
rand（）随机数函数
floor（）取最大整数函数
PI() 返回圆周率的值
truncate（x，d）四舍五入函数 返回被舍去至小数点后d位的数字x
greatset（x1，x2，x3）返回参数中的最大值
least（）返回参数中最小值函数
bin（x）返回参数x的二进制值
otc（x）返回参数x的八进制值
hex（x）返回参数x的十六进制值

2聚合函数

sum（）求和函数
avg（）平均值函数
count（）计数函数
max（）用来计算表中满足条件的数的最大值
min（）用来计算表中满足条件的数的最小值

3字符串函数

char_length()
concat(str1,str2...)返回的是连接参数产生的字符串，如有一个值为null，则结果为null
repeat（str，count）返回一个由重复的字符串str组成的字符串
find_in_set(str,strlist)返回str在strlist中的位置值
locate(substr.str) 返回str中子字符串第一次出现的位置
instr（str，substr）返回字符串str中子字符串substr的第一个出现位置
left（str，len）返回字符串str最左侧len个字符
right（str，len）返回字符串str最右侧的len个字符
SUBSTR(string, start, length)：SUBSTR函数用于提取字符串的子串