mysql基础操作

mysql基础操作

一、数据操作

1、insert插入数据

-- 基本语法：
insert into 表名 [(字段列表)] values （值列表）；
-- 一次插入多条
insert into 表名 [(字段列表)] values (值列表),(值列表),(值列表),....;
insert into students values ('licu',20),('Arie',21);

主键冲突：

表中如果使用业务主键（有业务含义），在插入数据时，有不确定是否存在其主键，在插入数据时，如果有主键相同时，就会产生主键冲突。

主键冲突方案：

1、插入记录主键冲突，采用更新方法；

-- 解决方法语法：
insert into 表名 [（字段列表）] values （值列表）on duplicate key update 字段=新值;
-- 假如学生的stu0004主键被使用，插入的数据主键有使用了stu0004
insert into students values('stu0004','小婷') on duplicate key update stuname = '小婷'；
-- 将原来的主键为stu0004的stuname的值，替换成'小婷'

2、主键冲突替换 ：当主键冲突之后，干掉原来的数据，重新插入。

-- 基本语法：
replace into 表名([字段列表]) values (值列表);

2、蠕虫复制

蠕虫复制：一分为二，成倍增加。从已有的数据中获取数据，将获取的数据插入到表中

-- 基本语法
insert into 表名 [(字段列表)] select */字段列表 from 表;
-- 蠕虫复制注意主键冲突，蠕虫在业务上没什么意义，但可以在短时间内大量添加数据，进行测试

3、update更新数据

1、更新数据的时候，特别注意，通常是跟随条件；

-- 基本语法：
update 表名 set 字段名 = 新值 where 条件判断;

2、如果没有条件，是全表数据更新，可以使用limit 来限制数量 ;

-- 基本语法
update 表名 set 字段名 = 新值 [where条件判断] limit 数量;

4、delete删除数据

1、删除数据时，不全表删除，用where条件判断；

2、删除数据时，可以用limit限制数量；

-- 基本语法
delete from 表名 [where 条件];

Delete 删除数据时，无法重置auto_increment ；

MySQL中有一个重置表选项中自动增长语法：Truncate 表名； 它等于 drop 再create 。

5、select查询数据

-- 完整查询指令
 select (select选项) 字段列表 from 数据源 where 条件 group by 分组 having 条件 order by 排序 limit 限制;
 
 -- select选项：系统如何对待查询的结果；all保存所有记录，distinct 去重复；

1、where条件

对查询出来的数据进行条件筛选，where是通过运算符进行结果比较来判断。

2、group by字句

group by 表示分组的含义，根据指定的字段，将数据进行分组，分组的目的是进行统计。

-- 基本语法
group by 字段名;
select * from student where stuage>20 group by stuId;

group by 是为了分组后进行统计的，如果想看数据显示，group by没有意义；并且使用group by分组统计之后，只会保留每组的第一条数据（仅显示每组的第一条记录）。

group by分组的一些统计函数：

avg()：求平均值；

count()：统计每组的数量，如果统计目标是字段，是不统计null字段的；如果为“*”代表统计记录；

sum()：求和

max()：最大值

min()：最小值

group_concat()：将选定的字符串拼接

3、having字句

Having和where本质是相同，用来进行数据的条件筛选。

Having：在group by字句之后的，顺序不能乱的。可以针对分组数据进行筛选，where是不行的。

4、order by字句

将指定的字段进行排序

二、运算符

1、算术运算符

+、-、*、/、%：加减乘除求余
通常在不在条件中使用，而用于结果运算中。

2、比较运算符

> 、>=、<、<=、=、<>：大于、大于等于、小于、小于等于、等于、既大于又小于就不等于
用来条件中的限定

3、逻辑运算符

and、or、not：逻辑与、逻辑或、逻辑非
in：代替=，当结果不是一个值，而是结果集
基本语法：in(结果1，结果2，结果3....)；
select *from student where stuId in ('stu001','stu002','stu003');
is：专门用于判断字段是否为null的运算符；
基本语法：is null / is not null;
like：用于进行模糊匹配；
基本语法：like '匹配模式';
_:匹配单个对应字符
%：匹配多个字符

三、高级查询

1、联合查询

1、概述

联合查询是可合并多个相似的选择查询的结果集。等同于将一个表追加到另一张表，从而实现两张表的查询组合到一起，使用的谓词：union或union all。查询的字段合并是纵向合并，字段数不变，结果集合并到一起。

2、应用场景

1、将同一张表的不同结果（需要多条查询语句来实现），合并到一起显示数据。

​ 比如：学生表中，男生身高升序查询，女生身高降序查询。

2、最常见，在大数据的情况下，会对表进行分表操作，需要对每张表进行部分数据显示，使用联合查询将数据显示在一起。

QQ1表在线数据，QQ2表在线数据

3、基本语法

-- 基本语法：
select 语句
union [union 选项]
select 语句;

-- union选项：1、distinct 去重；2、all 所有

-- 联合查询，使用到order by 字句时，需要键select语句括起来，同时要使用limit关键字来限制
(select 语句 order by 字段 limit 数量) union (select 语句 order by 字段 limit 数量);
-- 数量不确定，一般是一个比总记录数大就行。

2、连接查询

连接查询，就是将多张表连到一起进行查询（会导致记录数行和字段数列发生改变），它的意义：在关系型数据库中，实体与实体之间存在很多关联，可以通过连接查询来显示完整的数据。

连接查询分类：交叉连接、内连接、外连接（左连接和右连接）、自然连接

1、交叉连接：将两张表数据与另一张表彼此交叉，交叉连接产生的数据量很大，实际意义不大。

原理：

–1、从第一张表中依次取出每一条记录

–2、取出每条记录之后，与另一张表中的记录挨个进行匹配

–3、没有匹配条件，所有结果都会保留

–4、记录数 = 第一张表的记录数*第二张表的记录数；字段数 = 第一张表中的字段数+第二张中的字段数；

-- 基本语法
表1 cross join 表2;
select * from student cross join class;

2、内连接

内连接：inner join ，从一张表在中取出所有的记录去另一张表进行匹配，利用匹配条件进行匹配，匹配上的，就将结果保留下来；匹配不上的，放弃该结果。

-- 基本语法
表1 [inner] join 表2 on 匹配条件;  -- 匹配条件通常是等号，表1的某个字段值=表2对应的字段值
select * from student s inner join class c on s.classId = c.classId;
-- 查询学生以及他对应的班级

3、外连接

外连接：outer join ，以某一张表作为主表（表中的所有记录都会保留），根据条件去连接另一张表，而得到目标数据。

外连接分类：左外连接(left join)，右外连接(right join)；

左连接：左表为主表

右连接：右表为主表

-- 基本语法
左连接：主表 left join 从表 on 连接条件;
右连接：从表 right join 主表 on 连接条件;
select * from student s left join class c on s.classId = c.classId;
select * from class c right join student s on s.classId = c.classId;

3、using关键字

在连接查询中，用using来代替on关键字，进行条件匹配。

1、原理

–1、连接查询时，使用on的地方用using来代替；

–2、使用using的前提，对应两张表的字段名字要相同。

–3、如果使用using关键字，对应的同名字段，最终在结果中只会显示一个；

-- 基本语法
表1 [inner/left/right] join 表2 using(同名字段);

4、子查询

子查询是一种常用的计算机语言SELECT-SQL语言中嵌套查询下层的程序模块；当一个查询是另一个查询的条件时，称之为子查询。

子查询：指在一条select语句中，嵌入另一条select语句，那么被嵌入的select的语句被称之子查询语句。

子查询分类：

1、标量子查询：返回结果是一个数据源（一列一行 ）

2、列子查询：返回结果是一列（一列多行 ）

3、行子查询：返回结果是一行（一行多列）

4、表子查询：返回结果是多行多列

5、Exists 子查询：返回1 或 0（类似布尔型）

-- 标量子查询
---需求：知道一个学生名字：小猪，想知道他是哪个班级(班级名称)
---第一步：通过学生表获取他所在的班级id
---第二步：通过班级id，获取他的班级名称
-- 标量子查询语法：
select *from 数据源 where 条件判断 = 或 < >（大于或小于）（select 字段 from 数据源 where 条件判断);
selet *from my_class where class_id =(select class_id from my_student where stu_name='小猪');


-- 列子查询语法：
主查询 where 条件 in（列子查询）;

---列子查询
---获取已经有学生在班的班级名称
---第一步：找出学生表中的所有班级id
---第二步：找出班级表中的班级
select class_name from my_class where class_id in (select class_id from my_student);

-- 行子查询语法：
主查询 where 条件 [（构造一个行元素）] =子查询;
---行子查询
---想获取班级年龄最大、且身高最高的学生
---第一步：求出班级年龄最大的值
---第二步：求出班级身高最大的值
---第三步：求出对应的学生
selet *from my_student where (stu_age,stu_height)=(select max(stu_age),max(stu_height) from my_student);

-- 表子查询语法
select （字段表）from（表子查询）as 别名 [where] [group by] [ having] [order by] [limit];