本文共 6968 字，大约阅读时间需要 23 分钟。

mysql高级操作

一、数据新增
(一)批量插入：是一种优化数据逐条插入的方式

批量插入分两种
       全字段批量插入
              insert into 表名 values(值列表1),(值列表2),…;
       部分字段批量插入(注意字段默认值)
              insert into 表名 (字段列表) values (值列表1),(值列表2)…(值列表n);

示例1、批量插入学生成绩(t_30全字段)insert into t_30 values(null,'Tom','Computer',90),(null,'Lily','Computer',92);2、批量插入考生信息(t_30不包含成绩)insert into t_30 (stu_name,course) values('Tony','English'),('Ray','Math');

(二)蠕虫复制：从已有表中赋值数据直接插入到另一张表
蠕虫复制的目标是快速增加表中的数据
蠕虫赋值语法：insert into 表名 [(字段列表)] select 字段列表 from 表名;

示例1、创建一张表，将t_30表中的数据迁移过去create table t_35(	id int primary key auto_increment,	stu_name varchar(20) not null,	course varchar(20) not null,	score decimal(5,2))charset utf8;insert into t_35 select * from t_30;2、快速让t_35表中的数据达到超过100条(重复执行下面的指令)insert into t_35 (stu_name,course,score) select stu_nmae,course,score from t_35;

(三)主键冲突：在数据进行插入时包含主键指定，而主键在数据表已经存在

主键冲突解决方案
       忽略冲突：保留原始记录
       insert ignore into 表名 [(字段列表)] values(值列表);
       冲突更新：冲突后部分字段变成更新
       insert into 表名 [(字段列表)] value(值列表) on duplicate key update 字段=新值[,字段=新值];
       冲突替换：先删除原有记录，后新增记录
       replace into 表名 [(字段列表)] values(值列表);

示例1、用户名作为主键的用户注册(冲突不能覆盖)create table t_36(	username varchar(50) primary key,	password char(32) not null,	regtime int unsigned not null)charset utf8;insert into t_36('username','password',12345678);如果产生冲突报错insert ignore into t_36 values('username','21345678',12345678);冲突忽略，如果有冲突，本条忽略，不报错2、用户名作为主键的记录用户使用信息(不存在新增、存在则更新时间)create table t_37(	username varchar(50) primary key,	logintime int unsigned)charset utf8;insert into t_37 values('username',12345678) on duplicate key update logintime=unix_timestamp(); 3、用户名作为主键的记录用户使用信息(不存在新增、存在更新全部)create table t_38(	username varchar(50) primary key,	longintime int unsigned,	clientinfo varchar(255) not null)charset utf8;replace into t_38 values('usernmae',unix_timestamp(),'{phone:uc}');

二、数据查询
(一)查询选项：用于对查询结果进行简单数据筛选

查询选项是在select关键字之后，有两个互斥值
       all: 默认，表示保留所有记录
       distinct: 去重，重复的记录(所有字段都重复)

示例查看商品表中所有品类的商品信息：重复的商品值保留一次create table t_39(	id int primary key auto_increment,	goods_name varchar(50) not null,	goods_price decimal(10,2) default 0.00,	goods_color varchar(20),	goods_weight int unsigned comment '重量，单位克')charset utf8;insert into t_39 values(null,'mate10',5499.00,'blue',320),(null,'mate10',5499.00,'gray',320),(null,'nokia3301',1299,'black',420);考虑所有字段的去重(不含逻辑主键)select distinct goods_name,goods_price,goods_color,goods_weight form t_39;

(二)字段选择&别名

字段选择：根据实际需求选择的要获取数据的字段信息
根据实际需求，明确所需要的字段名字，使用逗号分隔
获取所有字段，使用星号统配所有字段
字段数据可以不一定来自数据源(select只要有结果即可)
       数据常量：select 1
       函数或者变量：select unix_timestamp(),@@version (@@是系统变量的前缀，后面跟变量名)

字段别名：给字段取的临时名字
字段别名使用as语法实现
       字段名 as 别名
       字段名 别名
字段别名的目的是通常为了保护数据
       字段冲突：多张表同时操作有同名字段(系统默认覆盖),想保留全部
       数据安全：对外提供数据不使用真实字段名字

示例1、明确需要查询的字段信息全部查询select * from t_39;需求为商品名字和价格select goods_name,goods_price from t_39;别名使用select goods_name as gn,goods_price gp from t_39;2、不需要数据源的数据获取：select的表达式本身能算出结果获取当前时间戳和版本号select unix_timestamp() as now,@@version as version,@@version;

(三)数据源：from关键字后，数据的来源。只要最终结果是一个二维表，都可以当做数据源

单表数据源：数据源就是一张表from 表名
多表数据源：数据来源是多张表(逗号分隔) from 表名1,表名2…表名n;
子查询数据源：数据来源是一个查询结果 from (select 字段列表 from 表名) as 别名
       数据源要求必须是一个表
       如果是查询结果必须给起一个表别名
数据表也可以是指定别名
       表名 as 别名
       表名 别名

示例1、单表数据源：最简单的数据源，直接从一个数据表获取select * from t_27;2、多表数据源：利用一张表的一条数据匹配另外一张表的所有记录，记录结果为:记录数=表1记录数*表2记录数;字段数=表1字段数+表2字段数select * from t_27,t_30;3、子查询数据源：数据来源是一个select对应的查询结果	查询语句需要使用括号包裹	查询结果需要指定别名select * from (select * from t_27,t_30) t;4、如果有时候名字较长或者使用不方便，可以利用表别名select * from t_30 as t;select t1.*, t2.stu_name from t_27 as t1, t_30 t2;

(四)where子句：跟在from数据源之后，对数据进行条件匹配

where是在磁盘读取后，进入内存之前进行筛选
       不符合条件的数据不会进入内存
where筛选的内容因为还没进如内存，所以数据是没有被加工过的
       字段别名不能使用在where中使用

示例查询t_35表中学生为lily的成绩信息select * from t_35 where stu_name='lily';

(五)运算符：用于进行运算的符号
比较运算符
>、<、=、>=、<=、<>(不等于)
between A and B(A和B之间，都包含，数值比较)
in(数据1,数据2…):在列举的数据中
like ‘pattern’ : 向上面样的，用于字符串比较
_单下划线，匹配对应位置的一个任意字符
%匹配当前位置(往后)任意数量任意字符
逻辑运算符
and、or、not
null运算符
is null、is not null

示例1、查询成绩不及格的所有学生信息select * from t_35 where score<60;2、查询成绩在60-90间的学生信息select * from t_35 where score between 60 ans 90;3、查询还没有成绩的学生select * from t_35 where score is null;

(六)group by子句
分组统计，根据某个字段将所有的结果进行分类，并进行数据统计分析
group by子句一定出现在where子句之后(如果同时存在)
group_concat():将组里的某个字段全部保留
any_value():不属于分组字段的任意一个组里的值
count():求对应分组的记录数量
    count(字段名)：统计某个字段值的数量(null不统计)
    count(*):统计整个记录的数量
sum():求对应分组中某个字段和
max()/min():对应分组中某个字段的最大、最少值
avg():求对应分组中某个字段的平均值

(七)回溯统计
在进行分组时(通常是多分组)，分一次结果的回溯都进行一次汇总统计
回溯统计语法：在统计之后使用with rollup

示例1、只统计每个班的男女同学数量，没有班级汇总select count(*),class_name,gender,group_concat(name) from t_40 group by clas_name,gender;2、汇总统计：回溯count(*),class_name,gender,group_concat(name) from t_40 group by clas_name,gender with rollup;

(八)分组排序：在分组后统计结果时可以根据分组字段进行升序或者降序显示数据
默认的系统就会自动对分组结果根据分组字段进行升序排序
可以设定分组结果的排序方式
    group by 字段名 [ASC]：升序排序(默认)
    group by 字段名 DESC：降序排序

(九)having子句：类似于where子句，是用来进行条件筛选数据的
having子句本身是针对分组统计结果进行条件筛选
having子句必须出现在group by子句后(如果同时存在)
having针对的数据是在内存里已经加载过的数据

示例获取班级人数小于3的班级select count(*) as `count`,class_name,group_concat(name) from t_40 group by class_name having `count`<3;

(十)order by子句：排序，根据某个指定的字段进行升序或降序排序
排序的参照物是校对集
order by子句在having子句后(如果同时存在)
排序分为升序和降序：默认升序
    order by 字段 [ASC]:升序
    order by 字段 DESC:降序

示例多字段排序，先性别降序，然后年龄升序排序select * from t_40 order by gender desc, age asc;

(十一)limit子句：限制数据的获取数量(记录数)
limit子句必须在order by子句后(如果同时存在)
limit限制数量的方式有两种
    limit 数量：限制获取的数量
    limit 起始位置,数量：限制数据获取的位置以及数量(分页)

示例1、获取t_40表中前3条数据select * from t_40 limit 3;2、获取t_40表中第3条以后的3条数据select * from t_40 limit 3,3;

三、数据更新
限制更新：更新时对更新的记录数进行限制
限制更新通过limit来实现
限制更新其实是局部更新的一种手段，一般更多情况下是根据条件更新

四、数据删除
(一)限制删除：限制删除的记录数
使用limit限制删除数量

示例删除没有账户余额的一个用户delete from t_41 where account=0 limit 1;

(二)清空数据：将表中的所有数据清除，并将表的所有状态回到原始状态
清空数据的本质是先删除表，后创建表
清空语法：truncate 表名

示例清空数据表truncate t_41;

多表操作

一、联合查询
(一)联合查询：union，将多个查询结果合并成一个结果显示
联合查询是针对查询结果的合并(多条select语句合并)

联合查询语法
select 查询
       union 查询选项
select 查询
…

联合查询要求：联合查询是结果联合显示
       多个联合查询的字段结果数量一致
       联合查询的字段来源于第一个查询语句的字段
查询选项
       all：保留所有记录
       distinct：保留去重记录(默认)

示例select * from t_40union (all)select * from t_42;

(二)联合查询排序：针对联合查询的结果进行排序
order by本身是对内存结果进行排序，union的优先级高于order by

如果要对单独select的结果进行排序，需要两个步骤
       将需要排序的select指令进行括号包裹
       order by必须配合limit才能生效

示例将t_40和t_42表的结果使用年龄降序排序select * from t_40union allselect *from t_42order by age desc;

二、连接查询
join，将两张表依据某个条件进行数据拼接
join左右各一张表，join左边的表叫左表，右边的表叫右表
连接查询不限定表的数量，可以进行多表连接，只是表的链接需要一个一个的连(a join b join c…)
连接查询就是通过字段拼接，把两张表的记录变成一条记录：字段数量增加

(一)交叉连接：cross join，不需要连接条件的链接
交叉连接产生的结果就是笛卡尔积

示例交叉连接t_41和t_42表select * from t_41 cross join t_41;

(二)内连接：[inner] join：将两张表根据指定的条件连接起来，严格连接
内连接是将一张表的每一条记录与另外一张表根据条件匹配
       匹配成功：保留连接数据
       匹配不成功：都不保留

内连接语法：左表 join 右表 on 连接条件

(三)外连接：(outer) join，是一种不严格的连接方式
外连接分两种
       左外连接(左连接)：left join
       右外连接(右连接)：right join
外连接有主表和从表之分
       左连接：左表为主表
       右连接：右表为主表
外连接是将主表 的记录去匹配从表的记录

(四)自然连接：natural join,是一种自动寻找连接条件的链接查询

示例自然连接t_43和t_44表select * from t_43 natural join t_44；

(五)using关键字
连接查询时如果是同名字段作为连接条件，using可以代替on出现

三、子查询
子查询：sub query，通过select查询结果当做另外一条select查询的条件或者数据源

位置分类
       from子查询：子查询出现在from后做数据源
       where子查询：子查询出现在where后做数据条件
按子查询得到的结果分类
       标量子查询：子查询返回的结果是一行一列(一个数据)
       列子查询：子查询返回的结果是一列多行(一列数据)
       行子查询：子查询返回结果是一行多列
       表子查询：子查询返回结果是一个二维表
       exists子查询：子查询返回结果是布尔类型(验证型)
子查询都需要使用()进行包裹，必要时需要对子查询结果进行别名处理(from子查询)