Mysql数据库----数据高级操作

新增数据：

基本语法：insert into 表名 【（字段列表）】values （值列表）；

在数据插入时，假设主键对应的值已经存在，插入一定会失败。

 

主键冲突（Duplicate key）

当主键存在冲突的时候，可以选择性的进行处理：更新和替换

 

主键冲突更新操作：

insert into 表名 【（字段列表：包含主键）】 values （值列表）on duplicate key update 字段=新值；

insert into my_class values ('PHP0810','B203');

-- 主键冲突：更新

insert into my_class values ('PHP0810','B203')

-- 冲突处理

on duplicate key update

-- 更新教室

room ='203';

 

主键冲突替换操作

replace into 表名 【（字段列表：包含主键）】 values （值列表）；

insert into my_class values ('PHP0710','A203');

-- 主键冲突：替换

replace into my_class values ('PHP0710','A205');

 

蠕虫复制

蠕虫复制：从已有的数据中去获取数据，然后将数据又进行新增操作，数据成倍的增加。

 

表创建高级操作：从已有表创建新表（复制表结构，不复制数据）

create table 表名 like 数据库.表名 ；

-- 复制创建表

create table my_copy like my_gbk;

蠕虫复制：先查出数据，然后将查出的数据新增一遍

insert into 表名【（字段列表）】select 字段列表 /* from 数据表名；

-- 蠕虫复制

insert into my_copy select * from my_collate_bin;

insert into my_copy select * from my_copy;

可以从别的表复制，也可以复制自己的表，复制自己的表数据成倍增加

 

蠕虫复制的意义：

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

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

 

更新数据

基本语法

update 表名 set 字段 = 值【where 条件】；

 

高级新增语法

update 表名 set 字段 = 值【where 条件】【limit 更新数量】；

-- 更新部分a变成c

update my_copy set name='c' where name='a' limit 3;

 

删除数据

与更新类似：可以通过limit来限制数量

select from 表名【where 条件】【limit 数量】；

-- 删除数据：限制记录数为10

delete from my_copy where name='b' limit 10;

删除：如果表中存在主键自增长，那么删除之后，自增长不会还原

 

思路： 数据的删除不会改变表结构，只能删除表后重建表

Truncate 表名； -- 先删除该表，后新增该表

 

查询数据

基本语法：select 字段列表 /* from 表名 【 where 条件】；

 

完整语法：

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

 

Select选项

select选项：select 对查出来的结果的处理方式、

All：默认的，保留所有的结果，

Distinct：去重，查出来的结果，将重复结果去除（所有字段都相同）（对整个字段查询，不能对部分字段）

-- select 选项

select * from my_copy;

select all * from my_copy;

select distinct * from my_copy; -- 去重

字段别名

字段别名：当数据进行查询的时候，有时候名字并不一定就满足需求（多表查询的时候，会有同名字段），需要对字段名进行重命名：别名

 

语法：字段名 【as】 别名；

-- 字段别名

select

id,

number as 学号,

name as 名字,

sex 性别

from my_student;

（as可以加，也可以不加）

数据源

数据源：数据的来源，关系型数据库的来源都是数据表，本质上只要保证数据类似二维表，最终都可以作为数据源

数据源分为多种：单表数据源，多表数据源，查询语句。

 

单表数据源：select * from 表名；

多表数据源：select * from 表名1，表名2，....

-- 多表数据源

select * from my_student,my_class;

从一张表中取出一条记录，去记录另外一张表中匹配所有记录，而且全部保留（记录数和字段数），将这种结果称之为笛卡尔积（交叉连接）；笛卡尔积没什么卵用，所以应该尽量避免，还耗费资源。

子查询：数据的来源是一条查询语句（查询语句的结果是二维表）

select * from （select 语句） as 表名；

 

where子句

where子句：用来判断数据，筛选数据

where子句返回结果：0或者1。0代表false；1代表true。

· select * from my_student where 1； -- 所欲条件都满足

 

判断条件：

比较运算符：> ，< ,>=,<=,!=,<>,= like,between..... and,in /not in.

逻辑运算符：&&（and） ，||（or),!(not)

 

where 原理：where是唯一一个直接从磁盘获取数据的时候就开始判断的条件，从磁盘取出一条记录，开始进行where判断：判断的结果如果成立保存到内存，如果失败直接放弃。

 

条件查询1：找出学生ID为1或者3或者5的学生

-- 找ID为1,3,5的学生

select * from my_student where id=1 || id=3 || id=5;

select * from my_student where id in(1,3,5);

条件查询2：查出区间落在170~180身高之间的学生

-- 找身高早170~180之间的学生

select * from my_student where height >=170 && height <=180;

select * from my_student where height between 170 and 180;

Between 本身是闭区间；between 左边的值必须小于或者等于右边的值

 

Groupby子句

groupby：分组的意思是，根据某个字段进行分组（相同的放到一组，，不同的分到不同的组）

 

基本语法：group by 字段名；

-- 根据性别分组；

select * from my_student group by sex;

（结果发现数据丢失了，what fuck）

 

分组的意义：是为了统计数据（按组统计：按分组字段进行数据统计）

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

Count（）：统计分组后的记录数，每一组有多少记录

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

Min（） ：统计最小值

Avg（）：统计平均值

Sum（）：统计和

-- 分组统计：身高高矮，年龄平均和总年龄

select sex,count(*),max(height),min(height),avg(age),sum(age) from my_student group by sex;

 

count（）函数：里面可以使用两种参数：*代表统计记录，字段名代表统计对应的字段（null代表不统计）

-- 分组统计：身高高矮，年龄平均和总年龄

select sex,count(*),max(height),min(height),avg(age),sum(age) from my_student group by sex;

select sex,count(*),count(age),max(height),min(height),avg(age),sum(age) from my_student group by sex;

（count（age）中如果有的age为空，则不统计）

 

分组会自动排序：根据分组字段：默认升序

Group by 字段 【asc|desc】； -- 对分组的结果然后合并之后的整个结果进行排序

 

 

多字段分组：先根据一个字段进行排序，然后对分组后的结果再次按照其他字段进行分组

-- 多字段分组：先班级，后男女

select c_id,sex,count(*) from my_student group by c_id,sex;

 

有一个函数：可以对分组的结果中的某个字段进行字符串连接（保留该组所有的某个字段）

-- 回溯统计

select c_id,count(*) from my_student group by c_id with rollup;

 

多字段回溯：考虑第一次分组会有一次回溯，第二次分组要看第一次分组的组数。组数是多少，回溯就是多少，然后加上第一层回溯即可。

-- 多字段分组回溯统计

select c_id,sex,count(*),group_concat(name) from my_student group by c_id,sex with rollup;

 

Having子句

Having 子句：与where子句一样：进行条件判断

 

where子句是针对磁盘数据进行判断：进入到内存之后，会进行分组操作，分组结果就需要having来处理。

 

Having几乎能做where能做的所有事情，但是where却不能做having能做的很多事情

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

-- 求出所有班级人数>=2的学生人数

select c_id,count(*) from my_student group by c_id having count(*) >=2;

2.having能够使用字段别名，where不能：where是从磁盘取数据，而别名是字段进入到内存后才有的

select c_id,count(*) as total from my_student group by c_id having total >=2; -- 这样是正确的

select c_id,count(*) as total from my_student where total>=2 group by c_id; -- 错误的，where是从磁盘读取数据

 

另：能用where还是要用where

 

Order by子句

Order by：排序，根据某个字段进行升序或者降序排序，依赖校对集

 

使用基本语法：

Order by 字段名 【asc|desc】； -- 默认asc升序

-- 排序

select * from my_student group by c_id; -- 分组

select * from my_student order by c_id; -- 排序

排序可以进行多字段排序：先根据某个字段进行排序，然后排序好的内部，再按照某个数据进行再次排序；

-- 多字段排序：先班级后性别

select * from my_student order by c_id,sex desc; -- 班级升序，性别降序

 

limit子句

limit子句：是一种限制结果的语句：限制数量

 

limit有两种使用方式

方案 1：值用来限制长度（数量）：limit 数据量；

-- 查询学生：前两个

select * from my_student limit 2;

方案2： 限制起始位置，限制数量：limit 起始位置 ，长度；

-- 查询学生：前两个

select * from my_student limit 0,2; -- 记录数是从0开始编号

select * from my_student limit 2,2;

 

limit方案2 主要用来实现数据的分页：为用户节省时间，提高服务器的响应效率，减少资源的浪费

对于用户来讲：可以点击的分页按钮：第1,2,3.....页

对于服务器来讲：根据用户选择的页码来获取不同的数据：limit offset ，length；

 

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

offset ：offset=（页码-1)*每页显示量