数据库高级数据操作

1、主键冲突 

    1.1 主键冲突更新：on duplicate key update  非主键字段=新值

  例子：

ID(primary key)	Name	Room
1	张三	401
2	李四	407

 

此时如果插入insert into T values(2,王五,408),提示主键冲突

正确操作 insert into T values(2,王五,408) on duplicate key update name="王五", room="408”；

ID(primary key)	Name	Room
1	张三	401
2	王五	408

 

1.2 主键冲突 替换 replace 

  replece into T (字段列表）values(值列表)

2、蠕虫复制

理解：先查出数据。然后将查出的数据新增一遍，2倍新增。

insert into T 【字段列表】 selete * /字段列表 from T/B(表结构T、B 相同）

意义：

1、从已有数据表拷贝数据到新表中；

2、可以迅速的让表中的数据膨胀到一定数量级，用来测试表的压力以及效率。

3、高级更新:限制数量

update T  set 字段=值

【where 条件】

【limit 更新数量】

4、高级删除;limit限制数量

delete  from T

【where 条件】

【limit  数量】

注意：删除操作不会修改表的结构，如自增长不会变化，即按正常增长。

想删除数据后表的自增长变为0，可turncate T ,即先清除表，重置自增长。

5、高级查询

完整语法;

select [select 选项】 字段列表【字段别名】from 数据源 【where 条件子句】 【group by 子句】【having 子句】 【order by 子句】 【limit子句】；

select 选项:对查出来的处理方式

          all:默认，保留全部结果

          distinct：去重,去除重复数据

字段别名;字段名 【as】字段别名

数据源：from 数据源

        单表数据源 from T1

        多表数据源 from T1,T2   笛卡尔积

        子查询：数据是来自其他的表的数据。 select * from (select * from T ）as  表别名

where条件子句 ：where 条件

         条件：比较条件（> ,<...）、|| 、and  、or等

                    betwen and   、in 、not in等

Gropu by 子句：分组的意思，根据某个字段进行分组（相同的放一组，不同 的分到不同的租）  group by 字段

分组的意义：是为了统计数据（按组统计，按分组字段进行数据统计）， 一般和聚合函数一起使用才有意义

group by 字段 【asc|desc】对分组的结果然后合并之后整个结果排序，默认升序。

正常查找：

单字段分组  group by 单字段

SQL提供 了一系列统计函数：

      count() ：统计分组后的记录数，每组有多少记录，有两种参数，count(*)和count(字段),注意字段值为空的时候不统计。

      Max（）：统计每组中 的最大数

      Min（）：统计每组中 的最小数

      Avg（）：统计每组中 的最小数

     Sum（）：统计每组中的和

 

多字段分组：先按照一个字段进行分组，然后对分组后的数据按照其他字段进行分组；

单字段分组：

多字段分组;

注意：多字段分组前后顺序不同查询结果不同！！

group_concat(字段)

完成对分组的结果中的某个字段进行字符串连接(保留改组所有的某个字段,例如表中的username)

回溯统计:with rollup

语法：group by 字段..with rollup

理解：任何一个分组后都有一个小组，最后都向上级分组进行汇报统计，统计分组后数量的结果

单字段回溯统计：

多字段回溯统计：

注意:多组回溯统计次数，先考虑第一次分组会有一次回溯数A,第二次分组要看第一次分组的组数，组数是多少，第二次回溯次数就是多少B，总回溯次数A+B,总条数A+B+分组数

having 子句，与where子句一样，进行条件判断

区别：where子句是针对磁盘数据进行判断，进入内存后，会进行分组操作，分组结果就只能通过having子句来处理；

           having 能做where能做的任何判断，但是where却不能做having能做的事情

           1、分组统计的结果或者说统计函数 都只有having能够使用。

         2、having 能使用字段别名，而where不能。where是从磁盘取数据，而名字只能是字段名，别名是在字段进入内存后才会产生。

 

order by 子句：排序

根据某个字段进行ASC|DESC排序，依赖校对集（如大小写）

order by 字段名.. 【asc|desc】

limit 子句:限制结果（数量）语句

两种方式：1、只是用来限制长度（数据量） limit 数据量

2、限制起始位置，限制数据量：limit offset，length；用来实现数据的分页。

分页逻辑：

length：每页显示的数据量，基本不变。

offset:   offset=(页码-1）*length