软件测试MYsql数据库

数据库

数据库是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、有共享的、统一管理的数据集合。

数据库是以一定方式储存在一起、能与多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合，可视为电子化的文件柜——存储电子信息场所，用户可以对数据库里的数据进行新增、查询、更新、删除等操作。

数据库是存放数据的仓库。它的存储空间很大，可以存放百万条、千万条、上亿条数据。但是数据库并不是随意地将数据进行存放，是有一定的规则的，否则查询的效率会很低。当今世界是一个充满着数据互联网世界，充斥着大量的数据。即这个互联网世界就是数据世界。数据的来源有很多，比如出行记录、消费记录、浏览的网页、发送的消息等等。除了文本类型的数据，图像、音乐、声音都是数据；

关系型数据库： MySQL  Oracle   Sqlserver  SQlite

优点：

1、易于维护：都是使用表结构，格式一致；

2、使用方便：SQL语言通用，可用于复杂查询；

3、复杂操作：支持SQL，可用于一个表以及多个表之间非常复杂的查询。

缺点：

1、读写性能比较差，尤其是海量数据的高效率读写；

2、固定的表结构，灵活度稍欠；

3、高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈

非关系型数据库：redis  

优点：

1、格式灵活：存储数据的格式可以是key,value形式、文档形式、图片形式等等，使用灵活，应用场景广泛，而关系型数据库则只支持基础类型。

2、高扩展性；（可用不同方式储存信息）

3、成本低：nosql数据库部署简单，基本都是开源软件。

缺点：1、不提供sql支持，学习和使用成本较高；

2、数据结构相对复杂，复杂查询方面稍欠。

Mysql与Oracle的区别

Mysql数据库：开源不收费

Oracle数据库：不开源收费

数据库语法：

连接数据库的命令：

Mysql  -u 用户名 -p

SQL语句

创建一个数据库

Create  database 数据库名；

删除一个数据库

drop  database  数据库名；

查看所有数据库

show  databases;

选择一个数据库

use 数据库名；

查看当前数据库内都有哪些表；

Show tables;

退出     Exit    Quit

创建一个表

Create table 表名(

列1名 列1类型  列1的默认值,

列2名 列2类型  列2的默认值,....

) engine myisam charset utf8;

可以声明：避免出现null 可以设置默认值

列1名 列1类型 not null ,

例子

创建一个chenyu111表，字段为id，int型，3个字节、name，char型，10个字节、class，字符串类型，10个字节；

create table chenyu111(

id int(3),

name char(10),

class char(10),

Time date,

Score float

)engine myisam charset utf8;

字符类型：

int(整数型)（有关计算的）

Char/varchar(字符串型)（有关于字符）

Float    double        有关于带小数的

Float 小数点前后7-8位     double  小数点前后20位

Date（有关于日期，时间的）

 

enum    set  有关选项的

enum 单选，枚举     set 多选

 

 Mysql5.0版本以下

一个字符=2字节    

一个数字=1字节

 Mysql5.0版本以上

一个字符=1字节    

一个数字=0.5字节

  

  

例子

create  table  A(name char(10) ,age int(2),class char(4)) engine myisam charset utf8;

create  table  A(name char(10),age int(3),class Char(4),result enum(‘优’,’差’)) engine myisam charset utf8;

创建一个chenyu_C表包含id字段为int型 3字节, result字段为单选类型，要求输入优、差、良，time为date类型;

Create table chenyu_C(

Id int(3) not null primary key,

Result enum(‘优’,’良’,’差’),

Time date

)engine myisam charset utf8;

创建表的时候添加主键

主键   primary key 

主键是不能重复的，不能为空，可以区分每一行的列。

外键 foreign key    

外键用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性。

创建外键

create table T(id int(10) not null primary key,E_id int(2) foreign key(E_id) references E(id))engine myisam charset utf8;

注！  创建外键时，“E_id”为想要把该表的E_id字段设置成外键；

    “id”为对应表的主键字段；

自增  auto_increment   （可与主键一块出现）

创建主键

create table A(id int(5)not null primary key,name chvar(20) ,salary float);

主键自增

create table A(id int(5) not null primary key auto_increment,name char(20) ,salary float);

主键不能为空，也不能相同，所以不能设置为默认值

添加默认值

create table D(id int(10) not null primary key auto_increment,name char(10) default ‘小明’,foreign key(id) references chenyu_D(id))engine myisam charset utf8;

字段注释

create table DDDDDDDDD(id int(10) not null primary key auto_increment comment '编号',name char(10) default 333 comment '名字',sal float comment '工资')engine myisam charset utf8;

 

什么是索引？

SQL索引有两种，聚集索引和非聚集索引，索引主要目的是提高了SQL Server系统的性能，加快数据的查询速度与减少系统的响应时间 

下面举两个简单的例子：

图书馆的例子：一个图书馆那么多书，怎么管理呢？建立一个字母开头的目录，例如：a开头的书，在第一排，b开头的在第二排，这样在找什么书就好说了，这个就是一个聚集索引，可是很多人借书找某某作者的，不知道书名怎么办？图书管理员在写一个目录，某某作者的书分别在第几排，第几排，这就是一个非聚集索引

字典的例子：字典前面的目录，可以按照拼音和部首去查询，我们想查询一个字，只需要根据拼音或者部首去查询，就可以快速的定位到这个汉字了，这个就是索引的好处，拼音查询法就是聚集索引，部首查询就是一个非聚集索引.

    看了上面的例子，下面的一句话大家就很容易理解了：聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续。就像字段，聚集索引是连续的，a后面肯定是b，非聚集索引就不连续了，就像图书馆的某个作者的书，有可能在第1个货架上和第10个货架上。还有一个小知识点就是：聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个。

语法：

创建普通索引

Create index 索引名字 on 表名（列名）

Create index chenyu_name on chenyu (tel,name,id)

创建唯一性索引

Create unique index 索引名字 on 表名(列名)

创建普通组合索引

Create index 索引名字 on 表名(列1,x列2)

索引的使用原则：经常在查询中用作条件的列应当添加索引，频繁进行排序或分组的列（即进行order by 或group by的列），应当为其添加索引；一个列的值域很大时应当为其添加索引。

修改表名

①rename  table  旧表名  to  新表明；

②alter  table  旧表名  rename  to  新表明；

删除一张表

drop  table 表名；

Drop table chenyu;

清空表数据

①delete  from 表名;

②truncate  table  表名称   （清空表里所有行，比delete更彻底）

查看表结构

desc 表名；（单独查看一张表）

查看建表语句

show create table 表名；

1.insert语句，往表中插入数据

insert into 表名称(列1，列2，列3，.....)  values  (值1，值2，值3，....);

Insert into a(id,name,age) values (5,’小明’,18);

单条记录插入

insert  into  A(id,name,age)  values  (5,’小李’,20);

插入部分列,插入所有列，插入全部列

insert into A (Name) values ('tim');

注意！enum数据类型的列插入的数据可以枚举的顺序来表示

create  table  A(id char(20),result enum(‘优’,’差’)) engine myisam charset utf8;

Insert  into  A(id,result)  values   (1,’优’),(2,’差’),(3,null);

Insert  into  A(id,result)  values   (1,1),(2,2),(3,6);

第三个数据插入的name为空；

 

插入一列

alter table 表名称 add 列名称 列类型;  --新增一列（列在新增时需要附带该列的类别）

Alter table D add name char(4);

Alter  table  A  add    class  char(5);

2.delete语句

  删除一行数据；

  delete from 表 where 列=值（条件）；

Delete from A where id = 1;

  删除A表中第二条数据

  Delete from  A  where  id = 2;

删除1,2,3，行数据

  Delete  from  A  where  id=1  or  id=2  or  id=3;

delete  from  A  where  id  in （1，2，3）；

删除一个列；

alter  table 表名称 drop  列名称；

Alter table chenyu drop

Alter  table 表名称 drop  column  列名称；

3.update语句

修改一列数据；

update 表名 set 列名=值（条件）；

Update A set name = ‘小明’;

修改某一个数据

Update  表名  set  列名=值（改的内容）  where  列=值（改的条件）；

Update A set name = ‘小红’ ,score = ‘60’ where id = 1;

update 表名 set 列名1=值,列名2=值... where 列=值；

  

将A 表里第一行的name改为二蛋，age改为22；

update A set name='二蛋'，age='22'    where id=1；

  

  

修改一个列的值（一行数据中的某一个值）；

  update 表名 set 列名列=值where限制条件；

update A set score= '80' where id=1；

修改一个列名；（列在修改时需要附带该列的类别）

Alter  table  表名  change    原列名  新列名  类型（字符类型（位数））

Alter  table  表名  change   column   原列名  新列名  类型（字符类型（位数））

Alter  table  A  change    ID  ID1  int(5)；

Alter  table  A  change  column  ID  ID1  int(5)；

1.修改A_scl表中id列为sid列，规定为整数型，位数可以输入3位；

Alter  table  A_scl  change   id  sid  int(3)；

修改一个列属性；

Alter   table  表名  modify   列名  列属性;

alter table A  modify name char(50);

数据库查询：

A表

	id	Name（姓名）	Age（年龄）	Score（分数）
1	1	王华	18	60.5
2	2	张三	22
3	3	刘强东	23	70
4	4	张二麻子	22	0

Select  查询内容  From  表名；

Select  查询内容 From  表名  Where  列=值（条件）；

查询A表的所有信息；

Select  *  from  A;

查询名字叫王华人的所有的信息；

Select   *   from A  where  name=’王华’;

查询名字叫王华的人的年龄；

Select   age  from A  where  name=’王华’;

查询名字叫王华人的年龄,分数；

Select   age，score  from A where name=’王华’;

限制条件的格式：

运算符：

<    小于          

>    大于         

>=   大于等于   

<=    小于等于    

<>   !=     不等于   

in  在数值范围之内      （对于值而言）

Not in   不在数值范围之内     （对于值而言）

Between...and...      在.......之间

（ score between 70 and 100             分数在70和100之间  ）

not Between...and...      不在.......之间

（ score not between 70 and 100             分数不在70和100之间  ）

Name  is  null      

查询年龄为空的人的名字;

Select  name  from  A  where  age  is  null;

连接符号or（或）,

and（且、和）   

Where id = 1  or  id =2;

Where id in(1,2) ;

Where id = 1  and class =’2班’;

Where id =1  or   class=’2班’;

 

注意！！

and和or连用的时候，会先运算and，然后运算or

1. 查询分数在70和90的人的名字；

Select name from A where score between 70 and 90;

1. 查询分数不在70和90的人的名字；

Select name from A where score not between 70 and 90;

1. 查询名字为王华，分数为60.5或者名字为张小龙，分数为100的人的信息；

Select  *  from  A  where  name=’王华’  and  score=60.5  or name=’张小龙’  and  score=100；

先运算所有and条件组合，然后运算or组合；

（王华        （100

Or      and    or

张小龙 ）       60.5）

1. 查询名字叫王华或者叫张小龙的人，并且分数为60.5或者分数为100的人的信息；

select * from a where （name=’王华‘ or name=’张小龙‘） and （score=60.5 or score=100）；

（）or或  and和   （or）

在有括号的情况下，会先计算括号里面的。

Select  所查内容  from 表名  where  列名称  运算符号  值

查询分数大于80的人的所有信息；

Select   *  from   A  where  score >= 80;

模糊查询

Select  查询内容 From  表名  Where  列名  like  ‘%a’;   

Select  查询内容 From  表名  Where  列名  not  like  ‘%a’;

‘%a’     以a结尾的数据

‘a%’     以a开头的数据

‘%a%’   中间带有a的数据

‘_a_ ’    三位且中间是a的数据

‘_a’     两位且结尾是a的数据

‘a_’     两位且开头是a的数据

‘a_ _b’  四位以a开头且以b结尾的

查询张姓开头的人的分数；

Select  score  From  A  Where  name  like  ‘张%’;

查询名字有三位数，且中间一个字为强的人的id；

Select  id  From  A  Where  name  like  ‘_强_’;

查询不以张姓开头的人的分数；

Select  score  From  A  Where  name  not  like  ‘张%’;

取别名：as  (对一个数据库表字段)

Select ename ‘名字’

Select  查询内容 as 别名  From  表 ;

Select  查询内容   别名  From  表 ;

Select  sal   ‘薪资’  from  persnoal；

Select  sal  as  ‘薪资’  from  persnoal；

单表查询最终格式：（顺序从上到下）

Select   查询内容

From    表,区分大小写

Where     限制条件  

Group by   列名                # 分组

Having     限制条件 （等同于where）

Order by    列名  desc/asc        #排序

Limit     行数         #查询前/后几行

 

几个关键字的使用顺序为

Select、from、

where 、group by/分组 、having/筛选、order by/排序 、limit

Select name as ’名字’ from xuesheng  where age=15 group by class having avg(score)>88 order by score desc limit 1;

group by 分组

group by语法可以根据给定数据列的每个成员对查询结果进行分组统计，最终得到一个分组汇总表。

SELECT子句中的列名必须为分组列或列函数。列函数对于GROUP BY子句定义的每个组各返回一个结果。

Group by 只取各分组中的第一个数据

Group by可以对多个列进行分组

 

Having筛选

having语句通常与group by语句联合使用，用来过滤由group by语句返回的记录集。

having语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足，当存在聚合函数为条件的sql中，需要把where换成having

查询（每个班级）平均分大于60的班级名字

Select class from A group by class having avg(score)>60;

Order by score；

Desc  代表’倒序’        

asc代表’正序 ‘   （不填写的话默认为正序）

Limit

放在sql句尾，用来决定展示前多少条数据

查看第10行到第35行

Limit

假设条件里的第一个行数为X

假设条件里的第二个行数为Y

第一个数字就是X减1

第二个数字就是Y减（X减1）

limit x,x;

limit x；

只看第五行

limit 4,1；

单独一个x代表展示前x行

第一个x代表从多少行开始

第二个x代表展示多少行

注意！若查询一个范围的行，比如2-7行，

第一个x填写的是  2减1

第二个x填写的是  7减第一个x

Limit 1,6

查询前三行的数据

Select * from student limit 3;

查询从第3行开始往后5行的数据

Select * from student limit 2,5;

查询数据库的前10行在Sqlserver，Mysql，Oracle的形式：

Sql server:

Select top 10 * from A;

Mysql:

Select * from A limit 10;

Oracle:

Select * from A where rownum <=10;

去重 distinct

SELECT DISTINCT 列名称 FROM 表名称

单列去重

查询学生表里的学生字段并去重

Select distinct name from xuesheng;

多列去重

查询学生表里的学生，班级，分数字段并对其去重

select distinct name,class,score from xuesheng ;

注意！！

多列去重必须得是查询出来的所有信息有重复的数据，才能够进行多列去重；

Union

UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）

UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）

使用形式如下：

SELECT 列名称 FROM 表名称 UNION SELECT 列名称 FROM 表名称 ；

SELECT 列名称 FROM 表名称 UNION ALL SELECT 列名称 FROM 表名称 ；

Union必须满足取出的列数相同，列名称未必一致，默认以第一条sql的列名称为准

A表:

Id	Name(姓名)	Age(年龄)
1	小一	18
2	小亮	15
3	小花	21
4	小一	20
5	小亮	22

Select  *

From  A

Group  by  name;

分组效果图：

Id	Name(姓名)	Age(年龄)
1	小一	18
2	小亮	15
3	小花	21

Select  *

From  A

Order by id;

降序排列效果图：

Id	Name(姓名)	Age(年龄)
5	小亮	15
4	小一	18
3	小一	20
2	小花	21
1	小亮	22

1. 查询A表下年龄在15岁以上的人的所有信息并按姓名进行分组；

Select * from A where age>15 group by name;

1. 查询A表下前5行的数据并按照年龄进行降序排列；

Select * from A order by age desc limit 5 ；

Select * from (select * from A limit 5) as A order by age desc;

聚合函数：

统计函数关键字：count（列名）

求和关键字：sum（列名）

求平均数：avg（列名）

求最大值：max（列名）

求最小值：min（列名）

统计班级的人的个数

Select count（id）， from  A；

求班级分数的总和；

Select sum（score） from  A

求班级所有人的平均年龄

Select avg（age） from  A

求班级所有人的最大年龄；

Select max（age） from  A

求班级所有人的最小年龄；

Select min（age） from  A

平均分大于60的人的分数

正确示例

Select score from A having avg(score)>60;

错误示例

Select score from A where avg(score)>60;

规律：

1. 查询规律（先找表，后找查询内容，最后找限制条件）。
2. 当查询内容存在多个时，此时每个查询内容之间用“，”隔开；
3. 一般最后一个“的”后面就是要查询的内容，最后一个条件之前的所有形容词即是条件；
4. 当限制条件存在多个时，每个限制条件之间用and（且）或者or（或）连接；
5. 如果多个条件之间是并列关系(多个条件使用的同一个列名)，此时使用关联符号“or” ,如果多个条件(多个条件使用的不同列名)是串联或者并列关系，此时用关联符号“and”;
6. 一

但是having一般都是和group by一起用;

 查询（每个班级）平均分大于60的班级名字

Select class from A group by class having avg(score)>60;

当聚合函数为查询内容时，此时聚合函数为真聚合函数,此时可以大胆使用即可；

（只描述一个字段）

查询A表中分数的最大值；

查询A表中最大的分数；

Select max(score) from A;

1. 当聚合函数为查询内容时，此时聚合函数为真聚合函数,此时可以大胆使用即可；当聚合函数为限制条件时，此时聚合函数可能为假聚合函数，若为假聚合函数可以进行倒序/正序取第一个来达到效果；当聚合函数为限制条件时，此时聚合函数若为真聚合函数时，此时把改部分表示成条件，然后放在having后；

错误示例：

（描述两个字段）

查询A表中分数最大的人名

select name from A where score =(select max(score) from A);

（这样的题描述了两个字段，就不能用到上面的总结了！！！！一般这样的题为嵌套题型）

当聚合函数为限制条件时，此时聚合函数可能为假聚合函数，若为假聚合函数可以进行倒序/正序取第一个来达到效果；

查询A表分数列中的第一条信息；

Select score from A  limit 1;

查询A表分数列中的最后一条信息；

Select score from A  order by id desc limit 1;

查询A表中id为最大的一条信息

Select  score  from A order by id desc limit 1;

当聚合函数为限制条件，且此时聚合函数也为真聚合函数时，需要把聚合函数表示成条件，然后放在having后，且这样的题一般都是要跟group by一起出现的；（这样的题出的不多！）

 查询（每个班级）最高分大于60的班级名字

Select class from a group by class having max(score)>60;

1. 当遇到”各个”或者”每个”字眼，或需要把某一列下的内容进行划分，此时需要进行分组；

多表查询：

多表查询格式：

内连接：inner join...on...(查询两张表A,B中所有符合where条件限制的结果，如果这两张表中存在至少一个匹配项，就返回该匹配项的结果)

左连接：left join...on...(以左表为主，查询左表中的所有数据以及左表符合where条件的结果，即使右表中没有匹配项，也从左表中返回所有的数据行。)

右连接：right join...on...（以右表为主，查询右表中所有数据以及右表中符合where条件的结果，即使左表中没有匹配项，也从右表中返回所有的数据行。）

外连接（全连接）：full join...on...（查询左连接和右连接的所有结果，只要其中有一个表中有匹配的行，就返回数据）

内连接:

Select  内容 from A inner join B  on  A.主键=B.外键 where 条件；

Select A.name,B.age from A inner join B on A.kid = B.kid where A.score=60;

左连接:

Select  内容 from A left join B  on  A.主键=B.外键 where 条件；

Select  内容 from A left join B on A.主键=B.外键 left join C on C.主键=A.主键；

右连接:

Select  内容 from A right join B  on  A.主键=B.外键 where 条件；

格式一：

Select   内容  from   表1，表2   where  主键列=外键列  and   列=值（条件） ;

查询名字为小明的人的年龄；

Select B.age from A inner join B on A.kid = B.kid where A.name = ‘小明’;

格式二：

Select  内容  from  表1  inner join  表2  on  主键列=外键列   where   列=值（条件）;

查询名字为小明的人的年龄；

Select B.age from A inner join B on A.id=B.id where A.name=’小明’;

Select...(查询的内容)from...（表名一）inner join ...（表名二）on ...(表1.表1表2共同部分=表2.表1表2共同部分) where...(限制条件)；

创建视图：create view 视图名 as 后面跟select语句；

Create  view  A  as  select  *  from  C;

视图的定义为

对视图进行增加删除和修改的操作会影响到基础的物理表，创建视图可以保证原表数据的安全性；

多表类型

Create view A as select A.name as name1,B.name as name2 from A,B where A.id=B.id;

与多表联查不同的是，创建视图不会把第二个表相同的字段默认加‘（）’，所以得起别名保证字段不重复，多表联查创建的视图也可以任意操作，原来表也会发生改变；

 

规律：

1.在多表查询时，此时先找查询内容，再看跟哪几个表，最后再找限制条件（查询内容和限制条件在哪几个表里面，就需要把这几个表关联到一起）；