mysql数据库

SQL

Structured Query Language

结构化查询语言

在数据库中进行操作的语言，称为sql，结构化查询语言，当前关系型数据库都支持使用sql语言进行操作,也就是说可以通过 sql 操作 oracle,sql server,mysql,sqlite 等等所有的关系型的数据库

• sql语言主要分为：
  • DQL：数据查询语言，用于对数据进行查询，如select
  • DML：数据操作语言，对数据进行增加、修改、删除，如insert、udpate、delete
  • TPL：事务处理语言，对事务进行处理，包括begin transaction、commit、rollback
  • DCL：数据控制语言，进行授权与权限回收，如grant、revoke
  • DDL：数据定义语言，进行数据库、表的管理等，如create、drop
  • CCL：指针控制语言，通过控制指针完成表的操作，如declare cursor
• 对于测试工程师来讲，重点是数据的查询，需要熟练编写DQL，其它语言如TPL、DCL、CCL了解即可
• SQL 是一门特殊的语言,专门用来操作关系数据库
• 不区分大小写

数据表操作

• 创建表

create table 表名(
    字段名 类型 约束,
    字段名 类型 约束
    ...
)

• 删除表

格式一：drop table 表名

格式二：drop table if exists 表名

数据库

• 查看所有数据库

show databases;

• 使用数据库

use 数据库名;

• 查看当前使用的数据库

select database();

• 创建数据库

create database 数据库名 charset=utf8;
例：
create database ceshi charset=utf8;

• 删除数据库

drop database 数据库名;
例：
drop database ceshi;

数据表

• 查看当前数据库中所有表

show tables;

• 查看表结构

desc 表名;

查看表的创建语句

show create table 表名;
例：
show create table students;

备份

mysqldump –uroot –p 数据库名 > ceshi.sql

恢复

• 先创建新的数据库

mysql -uroot –p 新数据库名 < ceshi.sql

# 根据提示输入mysql密码

数据操作-增删改查

简单查询

select * from 表名
例：查询所有学生数据
select * from students

添加数据

添加一行数据

格式一：所有字段设置值，值的顺序与表中字段的顺序对应

• 说明：主键列是自动增长，插入时需要占位，通常使用0或者 default 或者 null 来占位，插入成功后以实际数据为准

insert into 表名 values(...)

格式二：部分字段设置值，值的顺序与给出的字段顺序对应

insert into 表名(字段1,...) values(值1,...)

修改

格式：update 表名 set 列1=值1,列2=值2... where 条件

例：修改id为5的学生数据，姓名改为 狄仁杰，年龄改为 20

update students set name='狄仁杰',age=20 where id=5

删除

格式：delete from 表名 where 条件

例：删除id为6的学生数据

delete from students where id=6

逻辑删除：对于重要的数据，不能轻易执行delete语句进行删除，一旦删除，数据无法恢复，这时可以进行逻辑删除。

1、给表添加字段，代表数据是否删除，一般起名isdelete，0代表未删除，1代表删除，默认值为0

2、当要删除某条数据时，只需要设置这条数据的isdelete字段为1

3、以后在查询数据时，只查询出isdelete为0的数据

数据操作-查询

• 查询所有字段

select * from 表名
例：
select * from students

• 查询指定字段
• 在select后面的列名部分，可以使用as为列起别名，这个别名出现在结果集中

消除重复行

• 在select后面列前使用distinct可以消除重复的行

select distinct 列1,... from 表名;
例：
select distinct sex from students;

条件

• 使用where子句对表中的数据筛选，符号条件的数据会出现在结果集中
• 语法如下：

select 字段1,字段2... from 表名 where 条件;
例：
select * from students where id=1;

• where后面支持多种运算符，进行条件的处理
  • 比较运算
  • 逻辑运算
  • 模糊查询
  • 范围查询
  • 空判断

比较运算符

• 等于: =
• 大于: >
• 大于等于: >=
• 小于: <
• 小于等于: <=
• 不等于: != 或 <>

逻辑运算符

• and
• or
• not

模糊查询

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

范围查询

• in表示在一个非连续的范围内

例1：查询家乡是北京或上海或广东的学生

select * from students where hometown in('北京','上海','广东')

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

例2：查询年龄为18至20的学生

select * from students where age between 18 and 20

空判断

• 注意：null与''是不同的
• 判空is null
• 判非空is not null

排序

• 为了方便查看数据，可以对数据进行排序
• 语法：

select * from 表名
order by 列1 asc|desc,列2 asc|desc,...

• 将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推
• 默认按照列值从小到大排列
• asc从小到大排列，即升序
• desc从大到小排序，即降序

聚合函数

• 为了快速得到统计数据，经常会用到如下5个聚合函数
• count(*)表示计算总行数，括号中写星与列名，结果是相同的
• 聚合函数不能在 where 中使用

• count(*)表示求所有列总数；
• max(列)表示求此列的最大值；
• min(列)表示求此列的最小值;
• sum(列)表示求此列的和;
• avg(列)表示求此列的平均值;

分组

• 按照字段分组，表示此字段相同的数据会被放到一个组中
• 分组后，分组的依据列会显示在结果集中，其他列不会显示在结果集中
• 可以对分组后的数据进行统计，做聚合运算
• 语法：

select 列1,列2,聚合... from 表名 group by 列1,列2...

例1：查询各种性别的人数

select sex,count(*) from students group by sex

分组后的数据筛选

• 语法：

select 列1,列2,聚合... from 表名
group by 列1,列2,列3...
having 列1,...聚合...

• having后面的条件运算符与where的相同

例1：查询男生总人数

方案一
select count(*) from students where sex='男' 
-----------------------------------
方案二：
select sex,count(*) from students group by sex having sex='男'

对比where与having

• where是对from后面指定的表进行数据筛选，属于对原始数据的筛选
• having是对group by的结果进行筛选

分页

• 当数据量过大时，在一页中查看数据是一件非常麻烦的事情
• 语法

select * from 表名
limit start,count

• 从start开始，获取count条数据
• start索引从0开始

分页

• 已知：每页显示m条数据，求：显示第n页的数据

select * from students limit (n-1)*m,m

• 求总页数
  • 查询总条数p1
  • 使用p1除以m得到p2
  • 如果整除则p2为总数页
  • 如果不整除则p2+1为总页数

连接查询

• 当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回

• 等值连接查询：查询的结果为两个表匹配到的数据

• 左连接查询：查询的结果为两个表匹配到的数据加左表特有的数据，对于右表中不存在的数据使用null填充

• 右连接查询：查询的结果为两个表匹配到的数据加右表特有的数据，对于左表中不存在的数据使用null填充

等值连接

方式一

select * from 表1,表2 where 表1.列=表2.列

方式二(又称内连接)

select * from 表1
inner join 表2 on 表1.列=表2.列

左连接

select * from 表1
left join 表2 on 表1.列=表2.列

例1：查询所有学生的成绩，包括没有成绩的学生

select
    *
from
    students stu
left join scores sc on stu.studentNo = sc.studentNo

例2：查询所有学生的成绩，包括没有成绩的学生，需要显示课程名

select
    *
from
    students stu
left join scores sc on stu.studentNo = sc.studentNo
left join courses cs on cs.courseNo = sc.courseNo

右连接

select * from 表1
right join 表2 on 表1.列=表2.列

自关联

• 答案：定义表areas，结构如下
  • id
  • atitle
  • pid
• 因为省没有所属的省份，所以可以填写为null
• 城市所属的省份pid，填写省所对应的编号id
• 这就是自关联，表中的某一列，关联了这个表中的另外一列，但是它们的业务逻辑含义是不一样的，城市信息的pid引用的是省信息的id
• 在这个表中，结构不变，可以添加区县、乡镇街道、村社区等信息

例3：查询河南省的所有区县

select 
    * 
from 
    areas as p
left join areas as c on c.pid=p.aid
left join areas as a on a.pid=c.aid
where 
    p.atitle='河南省'

子查询

• 在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句

主查询

• 主要查询的对象,第一条 select 语句

主查询和子查询的关系

• 子查询是嵌入到主查询中
• 子查询是辅助主查询的,要么充当条件,要么充当数据源
• 子查询是可以独立存在的语句,是一条完整的 select 语句

子查询分类

• 标量子查询: 子查询返回的结果是一个数据(一行一列)
• 列子查询: 返回的结果是一列(一列多行)
• 行子查询: 返回的结果是一行(一行多列)
• 表级子查询: 返回的结果是多行多列

标量子查询

例1：查询班级学生的平均年龄

查询班级学生平均年龄
select avg(age) from students

查询大于平均年龄的学生
select * from students where age > 21.4167

select * from students where age > (select avg(age) from students);

列级子查询

例3：查询18岁的学生的成绩，要求显示成绩

学生表中查询18岁的学生的学号
select studentNo from students where age=18

成绩表中根据学号查询成绩
select * from scores where studentNo in ('002','006')

select * from scores where studentNo in (select studentNo from students where age=18)

行级子查询

例4：查询男生中年龄最大的学生信息

select * from students where sex='男' and age=(select max(age) from students)

select * from students where (sex,age)=('男',30)

select * from students where (sex,age) = (select sex,age from students where sex='男' order by age desc limit 1)

表级子查询

例5：查询数据库和系统测试的课程成绩

select 
    * 
from 
    scores s
inner join 
    (select * from courses where name in ('数据库','系统测试')) c
on s.courseNo = c.courseNo

子查询中特定关键字使用

• in 范围

  • 格式: 主查询 where 条件 in (列子查询)

• any | some 任意一个

  • 格式: 主查询 where 列 = any (列子查询)
  • 在条件查询的结果中匹配任意一个即可,等价于 in

• all

  • 格式: 主查询 where 列 = all(列子查询) : 等于里面所有
  • 格式: 主查询 where 列 <>all(列子查询) : 不等一其中所有

select * from students where age in (select age from students where age between 18 and 20)

存储过程

存储过程，也翻译为存储程序，是一条或者多条SQL语句的集合

创建

• 语法如下

delimiter //
create procedure 存储过程名称(参数列表)
begin
sql语句
end
//
delimiter ;

• 说明：delimiter用于设置分割符，默认为分号
• 在“sql语句”部分编写的语句需要以分号结尾，此时回车会直接执行，所以要创建存储过程前需要指定其它符号作为分割符，此处使用//，也可以使用其它字符

示例

• 要求：创建查询过程，查询学生信息
• step1：设置分割符

delimiter //

• step2：创建存储过程

create procedure proc_stu()
begin
select * from students;
end
//

• step3：还原分割符

delimiter ;

调用

• 语法如下

call 存储过程(参数列表);

调用存储过程proc_stu
call proc_stu();

• 存储过程和函数都是为了可重复的执行操作数据库的 sql 语句的集合.
• 存储过程和函数都是一次编译,就会被缓存起来,下次使用就直接命中缓存中已经编译好的 sql, 不需要重复编译
• 减少网络交互,减少网络访问流量

视图

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

create view 视图名称 as select语句;

• 例：创建视图，查询学生对应的成绩信息

create view v_stu_score_course as 
select
    stu.*,cs.courseNo,cs.name courseName,sc.score
from
    students stu
inner join scores sc on stu.studentNo = sc.studentNo
inner join courses cs on cs.courseNo = sc.courseNo

• 查看视图：查看表会将所有的视图也列出来

show tables;

• 删除视图

drop view 视图名称;
例：
drop view v_stu_score_course;

• 使用：视图的用途就是查询

select * from v_stu_score_course;

事务

• 所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。例如，银行转帐工作：从一个帐号扣款并使另一个帐号增款，这两个操作要么都执行，要么都不执行。所以，应该把他们看成一个事务。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性

事务命令

• 要求：表的引擎类型必须是innodb类型才可以使用事务，这是mysql表的默认引擎
• 查看表的创建语句，可以看到engine=innodb

show create table students;

• 修改数据的命令会触发事务，包括insert、update、delete

• 开启事务，命令如下：

  • 开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中

begin;

• 提交事务，命令如下
  • 将缓存中的数据变更维护到物理表中

commit;

• 回滚事务，命令如下：
  • 放弃缓存中变更的数据

rollback;

索引

语法

• 查看索引

show index from 表名;

• 创建索引

方式一：建表时创建索引

create table create_index(
id int primary key,
name varchar(10) unique,
age int,
key (age)
);

方式二：对于已经存在的表，添加索引

如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
字段类型如果不是字符串，可以不填写长度部分

create index 索引名称 on 表名(字段名称(长度))
例：
create index age_index on create_index(age);
create index name_index on create_index(name(10));

• 删除索引：

drop index 索引名称 on 表名;

缺点

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件
• 但是,在互联网应用中,查询的语句远远大于增删改的语句,甚至可以占到80%~90%,所以也不要太在意,只是在大数据导入时,可以先删除索引,再批量插入数据,最后再添加索引

外键foreign key

• 如果一个实体的某个字段指向另一个实体的主键，就称为外键。被指向的实体，称之为主实体（主表），也叫父实体（父表）。负责指向的实体，称之为从实体（从表），也叫子实体（子表）

• 对关系字段进行约束，当为从表中的关系字段填写值时，会到关联的主表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并报错