SQL--高级点的数据操作

1.新增数据

1.1、多数据插入

单数据插入：insert into 表名 [(字段列表)] values(值);
多数据插入：insert into 表名 [(字段列表)] values(值列表), (值列表)…;

1.2、主键冲突

在插入或者更新的时候，不知道这个表是不是主键的时候，为了避免冲突可以使用以下方法
先创建一张表

create table chongtu (
id varchar(10) primary key,
name varchar(50) 
);

--假设已近有的数据
insert into chongtu values('001','tom'),('002','jack');

• 主键冲突更新
  类似插入数据语法，如果插入的过程中主键冲突，那么采用更新方法。
  insert into 表名 [(字段列表)] values(值列表) on duplicate key update 字段 = 新值;

insert into chongtu values('002','jerry') on duplicate key update name='jerry';

• 主键冲突替换
  当主键冲突之后，干掉原来的数据，重新插入进去。
  replace into 表名 [(字段列表)] values(值列表);

replace into chongtu values('001','taifi');

1.3、蠕虫复制

蠕虫复制：一份为2，成倍增长，要注意主键冲突
基本语法：insert into 表名 [(字段列表)] select */字段列表 from 表;

insert into my_student (name) select name from my_student;

2、更新数据

update 表名 set 字段名 = 新值 [where 判断条件] limit 数量;
limit限制改变的数量

update my_student set name='cxk' where name='jacl' limit 1;

3、删除数据

之前也有提过，就带过就行了
delete from 表 [where 条件];

4、查询数据！！！

在数据库里面大部分的工作就是查询，所以这一点很重要
完整的查询指令：
select select选项 列表 from 表/数据源 where 条件 group by 分组 having 条件 order by 排序 limit 限制;

4.1、select选项

all：默认的，表示保存所有的记录，相当于*
distinct：去除重复，去除所有字段都重复的记录，只保留一条
别名用法：列表字段名 as 别名

比如有这么一张表：

--去除重复的，而且用了别名
select distinct name as 姓名 ,age as 年龄 from chongfu;

4.2、数据源

from是为前面的查询提供数据：数据源只要是一个符合二维表结构的数据即可。

4.3、动态数据

基本语法：from (select 字段列表 from 表) as 别名;
用于只查询一个表的一部分列
比如有个表：dongtai

select * from (select name,hobby from dongtai) as nameAndHobby;

4.4、where

就where后面接条件，进行筛选，复合条件保存在内存，不符合则丢弃

4.5、group by

分组统计，分组计算，group by将数据按照指定的字段分组之后，只会保留每组的第一条记录。
基本语法： group by 字段名;
利用一些统计函数：（聚合函数）
count()：统计每组中的数量，如果统计目标是字段，那么不统计为空NULL字段，如果为*那么代表统计记录，后面一定要接group by
avg()：求平均值
sum()：求和
max()：求最大值
min()：求最小值
有如右表所示：
代码：

select class as 班级,count(*) as 班级人数,avg(age) as 平均年龄 ,max(height) as 最高身高,min(height) as 最低身高 
from grouptest  
group by class;

4.6、group by的多分组

就是不止group by一个，可以分组多个的

select class as 班级 ,sex as 性别 ,count(*) as 人数 , group_concat(name) as 名字 from grouptest
group by class,sex;

group_concat()：为了将分组中指定的字段进行合并（字符串拼接）

4.7、回溯统计

当分组后，想把分组统计的结果整理起来，就用到回溯统计，比如班级有4男5女，那么回溯统计会回到总人数9人，对应的字段为NULL
基本语法：group by 字段 [asc|desc] with rollup;

select class as 班级 ,count(*) as 人数  from grouptest 
group by class with rollup;

4.8、having

和where差不多，但不一样的是，where 不可以用于聚合函数，但是having 可以
**注意：**having是在group之后，where是在group之前

--查询有2个以上男生的班级
select class ,count(*)  from grouptest 
where sex ='男' 
group by class
 having count(*)>=2;

4.9、order by

用来排序的
基本语法：order by 字段 [asc|desc]; //asc升序，默认的； desc 降序

--查询身高从高到低的男生
select * from grouptest 
order by height desc;

4.10、limit

用来限制的，
使用方法：limit 限制数量;

--只查看数据表中的前三个数据
select * from grouptest limit 3;

4.11、分页

用limit 来限制获取指点区间的数据
使用方法：limit 偏移值 ,lenght；//偏移值就是从第几个开始，sql中以0为第一个；lenght就是获取多少条数据

--从第二个开始查询，查询2个
select * from grouptest limit 1,2;

5、运算符

5.1、算术运算符

5个：+ - * / %
通常不用于条件中，通常在select字段中

5.2、比较运算符

几个： >、>=、<、<=、=、<>、between
between 条件1 and 条件2;
其中，=相当于其他编程语言的==，<>是不等于
sql中没有bool值，1代表真，0代表假

--年纪在22和24之间的
select * from grouptest
where age between 22 and 24;

5.3、逻辑运算符

与、或、非：and 、or、not

5.4、is运算符

专门用于判断是否为空的，只有两种用法

• is null
• is not null

5.5、like运算符

用来进行模糊匹配
用法：like ‘匹配模式’;
_：匹配单个字符
%：匹配多个字符

select * from grouptest
where name like '西门%';

6、union联合查询

联合查询是可合并多个相似的选择查询的结果集。等同于将一个表追加到另一个表，从而实现将两个表的查询组合到一起，使用谓词为UNION或UNION ALL。

联合查询：将多个查询的结果合并到一起（纵向合并）：字段数不变，多个查询的记录数合并。

• 基本语法：(select 语句) union [union选项] (select 语句):
  其中，()可以去掉，但是如果要用到order的时候就不能去掉而且必须要用到limit

• union选项：
  distinct：去除重复（默认）
  all：保留所有结果

--简单的联合查询
select * from grouptest
union all
select * from grouptest;

--如果使用不当就会造成以下后果
select id,name from grouptest 
union all 
select name,id from grouptest;

--一定要注意加括号而且要用到limit
(select * from grouptest where sex ='男' order by age asc limit 99)
union
(select * from grouptest where sex ='女' order by age asc limit 99);

7、连接查询

7.1、交叉连接

• 原理
  1、 从第一张表依次取出每一条记录
  2、 取出每一条记录之后，与另外一张表的全部记录挨个匹配
  3、 没有任何匹配条件，所有的结果都会进行保留
  4、 记录数 = 第一张表记录数 * 第二张表记录数；字段数 = 第一张表字段数 + 第二张表字段数（笛卡尔积）
• 用法：表1 cross join 表2;

有两个表：grouptest：，chongfu：

select * from grouptest cross join chongfu;

• 意义：没什么卵用，只是用于数据库的压力测试而已

7.2、内连接

基本语法：表1 [inner] join 表2 on 匹配条件;
用一张表去匹配另外一张表，匹配成功则保留，失败则丢弃
如果内连接没有匹配条件就是交叉连接
有以下两张表：grouptest：class2：

--学生表内连接班级表
select * from grouptest as g inner join class2 as c on g.class=c.class_name;

• 上面是学生表内连接班级表的，但是如果是班级表内连接学生表的话又会不一样。即使班级表有物联网5班但是在学生表匹配不到所以不会显示物联网5班

--班级表内连接学生表
select * from class2 as c inner join grouptest as g  on c.class_name=g.class;

• 内连接中的on也可以用where来代替，一样的效果，但最好不要这样做

7.3、外连接

外链接：outer join，按照某一张表作为主表（主表中所有记录在最后都会保留），根据条件去连接另外一张表，从而得到目标数据。
外连接分为左连接和右连接，正如名字，左连接就是左边的表做主表，右连接同理

• 基本语法：
  左连接：主表 left join 从表 on 连接条件;
  右连接：从表 right join 主表 on连接条件;

select * from grouptest as g left join class2 as c on g.class=c.class_name;

select * from grouptest as g right join class2 as c on g.class=c.class_name;

• 在右连接可以看出，班级表右物联网3班但学生表没有物联网3班的同学，但主表还是会保留下来，从表数据变成NULL

7.4、using关键字

是在连接查询中用来代替对应的on关键字的，进行条件匹配。
基本语法：表1 [inner,left,right] join 表2 using(同名字段列表);
注意是同名字段！！！！

--假设同名字段列表吧。。。我懒得再弄一个表了
--emmmmm
select * from grouptest as g right join class2 as c using(class_name);