测试开发之路—MySQL数据库基础

CREATE TABLE `t_area`  (
  `area_id` int(12) ,
  `area_name` varchar(255),
  `priority` varchar(255) ,
  `create_time` datetime,
  `update_time` datetime,

);

1.2.3 插入数据

语法1：insert into 表名 values (值,..);

语法2：insert into 表名 (字段名,..) values (值,...);

--往表a中插入多条数据
insert into a values (1,16,张三),(2.19,李四);
--按字段往表a中插入多条数据
insert into a values (id,Name) values (3,赵云);

1.2.4 查询数据

语法：select 字段名（*表示全部字段） from 表名 () ;

--查询表 b 的id字段
select id from b;
--查询表 b 的id和age字段
SELECTid,age FROM b;

1.2.5 修改数据

语法：update 表名 set 字段=值,... where 条件,没有条件时，则修改所有字段

--修改表 b,
--id 为 3 的记录,
-- 姓名(name 字段)改为'狄仁杰'，年龄(age 字段)改为 20
UPDATE b SET b NAME ='狄仁杰”，age =20 WHERE id = 3;

1.2.6 删除数据

delete 删除表记录

语法：delete from 表名 where 条件

--刪除表b中id为3 的记录
DELETE FROM b WHERE id = 3;
--删除所有记录
DELETE from b;

truncate 删除表数据

语法：truncate table 表名;

drop 删除表

语法1：drop table 表名;

语法2：drop table if exists 表名;(如果表存在则删除，不存在则什么也不发生)

delete/truncate/drop三者的区别

delete:删表数据,保留主键记录和表结构
truncate: 删表数据和主键记录,保留表结构
drop: 表结构,主键记录, 表数据全部删除

1.3 字段约束

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

常用约束：

主键（primary key）：值不能重复，auto_increment代表自动增长

外键（foreign key）：外键约束经常和主键约束一起使用，用来确保数据的一致性，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值。

非空(not null)：此字段不能填空值

默认值（default）：不填写此值则使用默认值

唯一值（unique）：约束该字段的值具有唯一性

检查约束（check）：用来检查数据表中，字段值是否有效

1.4 条件查询

1.4.1 Where查询

语法：select * from 表名 where 条件;(也可以用在delete和update语句中)

-- 例 1:査询 student 表中 id 等于 1 的记录
SELECT *FROM student WHERE id= 1;
--例 2:査询 student 表中年龄 age 等于 30 的姓名 name,班级 class
SELECT name ,class FROM student WHERE age = 30;

1.4.2 比较运算符

1.4.3 逻辑运算符

and 与
or 或
not 非

-- 例 1:查询 age 年龄小于 30，并且 sex 性别为’女’的同学记录
SELECT * from student where age<30 and sex='女';
-- 例 2:查询 sex 性别为’女’或者 class 班级为'1 班'的学生记录
SELECT * from student where sex='女'or class ='1班';
-- 例 3:査询 position 职业非’辅助’的学生记录
SELECT * from student where not position =“辅助";

1.4.4 模糊查询

like实现模糊查询，%表示任意字符，_表示任意一个字符

-- 例 1:査询 name 姓名中以’狂’开头的学生记录
SELECT * from student where name like '狂%';
SELECT * from student where name like '狂_';
-- 例 2:査询  name 为任意姓，名叫’铁’的学生记录
SELECT * from student where name like '%铁';
SELECT * from student where name like '_铁';
-- 例 3:査询 name 姓名含有’周’的学生记录
SELECT * from student where name like '%周%';

1.4.5 范围查找

in：非连续范围查找

-- 例 1:查询 position 职业是’刺客’或’射手’或’辅助’的学生记录
SELECT * from student where position ="刺客"or position ="射手"or position =“辅助”;
SELECT * from student where position in (“刺客"，“射手"，“辅助”);

between：连续性范围查找，需要包含开始值和结束值

--例 2:查询 age 年龄为 25 至 30 的学生记录
SELECT * from student where age >= 25 and age <= 30;
SELECT * from student where age BETWEEN 25 and 30;

1.4.6 判断null

null 在 SQL 中代表空,不是0,也不是”",而是什么也没有
is null ---判断为空
is not null ---判断非空
nul 不能用比较运算符判断

# 数据准备
INSERT INTO student VALUES(13,"干将"，nu11,nu11，4，“法师");
-- 例 1:查询 sex 性别为 空 的学生记录
SELECT * from student where sex is null;
-- 例 2:查询 sex 性别 非空 的学生记录
SELECT * from student where sex is not null;

1.5 别名

表/字段的别名

-- 通过 as 给表 student 起一个别名
select *from student as s;
-- as 可以省略
select *from student s;

1.6 排序

语法：order by 字段名 desc/asc，asc表示升序（默认），desc表示降序

-- 例 1:查询所有学生记录，按 age 年龄从小到大排序
select *from student order by age asc;select *from student order by age;
-- 例 2:查询所有学生记录，按 age 年龄从大到小排序
select *from student order by age desc:
--例 3:查询所有学生记录，按 age 年龄从小到大排序年龄相同时，再按 id 从小到大排序
SELECT *from student ORDER BY age, id;

1.7 聚合函数

1.7.1 count计数

--查询学生总数(查询student表有多少记录)
select count(*) from student;
select count(id) from student;
--查询班级总数
select count(distinct cass) from student;
--查询女同学数量
SELECT count(*) from student where sex='女';SELECT count(id)from student where sex='女';

1.7.2 max最大值

--查询 student 中的最大年龄
SELECT max(age) from student;
--查询 student 中的女生最大年龄
SELECT max(age) from student where sex=女';

1.7.3 min最小值

--查询 student 中的最小年龄
SELECT min(age)from students;
-- 查询 student 中的女生最小年龄
SELECT min(age)from students where sex='女';

1.7.4 sum求和

--查询 student 中的年龄总和
SELECT sum(age)from student;
-- 查询 student 中的女生年龄总和
SELECT sum(age)from student where sex='女';

1.7.5 avg平均值

--查询 student 中的年龄的平均值
SELECT avg(age) from student;
--查询 student 中的女生年龄的平均值
SELECT avg(age) from student where sex=女';

1.8 分组

group by 字段名
select聚合函数 from 表名 group by 字段
select 聚合函数 from 表名 where 条件 group by 字段
select 分组字段,聚合函数 from 表名 where 条件 group by 字段
group by一般是配合聚合函数使用的

-- 分别查询3班不同性别的学生数量
SELECT sex,count(*) FROM student WHERE class = 3 GROUP BY sex;
-- 分别查询不同班级不同性别的学生数量
SELECT class,sex,count(*) FROM student GROUP BY class,sex;

1.9 分组后的数据筛选

group by having 条件

-- 查student表的男生总数
-- 用where查询男生总数
-- where先筛选复合条件的记录，然后在聚合统计
SELECT count(*) from student where sex = '男';
--用having查询男生总数-- having先分组聚合统计，在统计的结果中筛选
SELECT count(*) from student GROUP BY sex HAVING sex = '男';

having配合聚合函数的使用

--求班级人数大于3人的班级名字
SELECT
class
FROM
student GROUP BY class
HAVING count(*)>3;

having与where筛选的区别

where是对原始数据进行筛选
having是对group by之后已经分过组的数据进行筛选，having可以使用聚合函数,where不能用聚合函数

2.0 优先级

select 分组字段,聚合函数 from 表名 where 条件 group by 字段 having 条件 order by 字段

2.1 分页

limit 总是出现在 select 语句的最后
limit start, count
- 如,limit 0,3 表示从第1行开始,显示3行
- 如果是从第1行开始,start 可以省略,limit 0,3 可以写为 limit 3

-- 查询前三条记录
SELECT * from student limit 0,3;
SELECT * from student limit 3;
-- 查询从第4条记录开始的三条记录
SELECT * from student limit 3，3;
-- 每页显示4条记录，第2页的结果
select * from student limit 4,4;

2、连表查询

数据：student表（包含学生id，性别sex，年龄age，班级class），course表（课程id，课程名name），sc表（包含学生id，课程id，成绩）

2.1 内连接

语法：

select* from 表1 inner join 表2 on 表1.字段=表2.字段
内连接最重要的是,找对两张表要关联的字段

select * from a inner join b on a.id = b.id;

student表和scores表内连接查询结果

-- 没有关联字段的情况下
SELECT *from student INNER JOIN SC;
-- 关联字段正确的情况下
SELECT * from student INNER JOIN sc on student.id = sc.s_id;

内连接查询指定字段

-- student表与sc内连接,只显示name，cid，score
SELECT s.name,sc.c_id,,sC.score FROM student
INNER JOIN sc oN student.id= sc.s_id;

带有where的连接

语法：select * from 表1 inner join 表2 on 表1.字段=表2.字段 where 条件

--查询猪八戒的信息，要求只显示姓名、课程号、成绩
SELECT s.name，sc.c_id,SC.SCOre FROM student s
INNER JOIN sc on s.id = sc.s_id WHERE
s.name =“猪八戒”;

多表内连接

--查询学生信息和成绩以及成绩对应的课程名称
SELECT * FROM student s INNER JOIN sc oN s.id= sc.s_id 
INNER JOIN course on sc.c_id = c.id;

SQL语句三步法

1、搭框架
基本的select语句框架搭建起来,如果有多表,把相应的多表也联合进来

2、看条件
决定where后面的具体条件

3、显示的字段
select后面到底要显示什么字段

带有order by的联合查询

-- 查询成绩最高的男生信息，要求显示姓名、课程名、成绩
SELECT
s.name 姓名,
C.name 课程,
sc.score 成绩
FROM
student s INNER JOIN sC ON s.id = sc.s_id
INNER JOIN course on sc.c_id= c.id
WHERE
s.sex =“男”
ORDER BY
sc.score DESC
LIMIT 1;

2.2 左连接

语法：select * from 表1 left join 表2 on 表1.字段 = 表2.字段

--例 1:查询所有学生的信息以及成绩，包括没有成绩的学生
SELECT * FROM student s
INNER JOIN sc O s.id = sc.s_id;
SELECT * FROM student s
LEFT JOIN sc ON s.id = sc.s_id;

2.3 右连接

语法： select * from 表1 right join 表2 on 表1.字段 = 表2.字段

--例 1:查询所有课程的信息，包括没有成绩的课程
SELECT *
FROM sc
RIGHT JOIN course c on sc.c_id = c.id;

2.4 小结

内连接：把两张表相同的地方查出来

左连接：包含了内连接，同时查询左表特有的内容

右连接：包含了内连接，同时查询右表特有的内容

3、自关联

编号id	区域name	上级编号pid
1	广东省
2	浙江省
3	广州市	1
4	佛山市	1
5	绍兴市	2

查询一共有多少个省

select count(*) from a where pid = null

自关联是同一张表做连接查询
自关联下，一定找到同一张表可关联的不同字段

--查询广东省的所有城市
SELECT FROM  a a1
INNER JOIN a a2 on a1.id = a2.pid WHERE
a1.name ='广东省';

4、子查询

子查询是嵌套到主查询里面的
子查询作为主查询的数据源或者条件

--例 1:查询大于平均年龄的学生记录
SELECT avg(age) from student; --平均年龄28
select * from student where age >28;
--用子查询实现
select * from student where age > (SELECT avg(age) from student);

5、数据库存储过程

定义：存储在数据库服务端的SQL语句

--数据准备
create table user(
    id INT PRIMARY KEY auto_increment,
    name VARCHAR(20),
    phone_number BIGINT,
    password VARCHAR(20)
);

应用场景：

什么时候会用到存储过程？

做性能测试时，会使用存储过程帮助加海量用户数据
对功能做一些极限验证时，如测试海量商品时分页功能和分页跳转功能，需要加数据

1、批量处理

#创建存储过程d，录入10000条数据
create procedure d()
begin --存储过程的开始
    declare i int default 1; --定义变量i，初始值为1
    while i <= 10000
        --执行过程：录入一条数据
        do
            insert into user values(i,"张三",13000000000+i,"123456");
        set i = i+1;
    end while;

end --存储过程的结束

#调用存储过程
call d();

--如果想要名字也区分开来，可以使用字符串拼接concat("张三",i)
#删除存储过程
drop procedure d

2、重复使用

--删表数据和主键记录，保留表结构

TRUNCATE table user;
--再次调用
ca11 d();

删除存储过程