首页 > 代码库 > Mysql

Mysql

插入数据
标准语法:
insert into 表名[字段列表] values(值列表)

如何以最快的速度向一个数据表中插入100万条数据?
蠕虫复制
就是在已有的数据的基础之上,将原来的数据进行复制,插入到相对应的表中!
语法规则:
insert into 表名 select *|字段列表from 表名
注意:
当一个表中的数据复制到另一个表中的时候,需要注意数据的来源要与被插入的表的字段数量和类型要保持一致!

总结蠕虫复制的优点:
1,可以以最快的速度复制另外一张表的数据
2,在短期内产生大量的数据,以测试服务器的压力


主键重复
思考:
如果在进行数据更新的时候,某条记录的主键已经存在,但是又需要将最新的数据更新到该记录中,怎么办?
策略一:
可以使用以下的语法:
insert into 表名[字段列表] values(值列表) on duplicate key update 字段1=值1,字段2=值2……
也就是说,当主键不冲突的时候,相当于一条插入语句,当主键有冲突的时候,相当于一条更新语句!
策略二:
如果主键重复,直接删除原纪录再插入replace into 表名[字段列表] values(值列表);

在插入之前进行一次判断,判断有没有主键重复,如果没有,跟普通的插入语句没有区别,如果有主键冲突,先删除以前的记录,再插入新记录!


修改数据
标准语法:
update 表名 set 字段1=值1,字段2=值2……where条件;

还可以加上orderby子句和limit子句:

update 表名 set 字段1=值1,字段2=值2……[where条件][order by 字段名 asc|desc][limit 数据量]

 

删除数据
标准语法:
delete from 表名 [where条件] [order by 字段名 asc|desc][limit 数据量]

还有一个类似删除功能的语法:
truncate table 表名; 或 truncate 表名;
注意:该语句不属于DML,属于DDL

相当于做了两件事情:
1,先把原表drop掉!
2,再按以前的原表的结构重新创建一次!

 


查询数据
查询数据可以说是整个业务逻辑中使用的最多也是最复杂的!
以前的语法:
select * |字段列表 from 表名 [where子句];


一个比较完整的语法:

select [select选项] *|字段列表 [as 字段别名] from 数据源 [where子句][group by子句][having子句][order by子句][limit子句];

以上的语法一般只是单表查询,另外还有多表查询,多表查询又有联合查询、子查询、连接查询(左连接,右连接,内连接,外连接,自然连接)
注意:
1,from后面的子句往往称之为:五子句,也叫五子查询!
2,五子查询都可以没有,但是,如果要有,就必须按顺序写!

 

select选项和别名


select选项
含义:
就是系统在查询到相关数据之后,如何显示!
这里的select选项有两个值:
all:也是缺省值(默认值),保留所有的查询结果!
distinct:去重,去掉重复的查询结果!

 

别名
所谓的别名,就是给字段或其他表达式等标识符另起一个名字,基本语法如下:
字段|表达式|表|子查询 [as] 别名
这里的as可以省略,但是为了增加可读性,一般还是写上!

为什么要给字段起别名?
如果多表查询的时候,不同的表内可能出现相同的字段名,如果使用同名字段,PHP在提取的时候就会造成数据的丢失!
而mysql中的数据最终是有php来提取,php会把一条记录放到一个数组里面进行保存!而数组的下标就是这里查询结构的字段名!所以要给出现同名字段的字段起一个别名,以免造成数据的丢失(覆盖)
为什么给表起别名?
在真实的项目中,表名往往都比较长,懒得写!
当数据源是一个子查询的时候,必须给该子查询语句起一个别名!(后面学)


虚拟表

查询语句的比较完整的语法:
select [select选项] *|字段列表 [as 字段别名] from 数据源 [where子句][group by子句][having子句][order by子句][limit子句];
但是,一条真实的sql语句,有可能连字段没有!
典型的,select语句可以当计算器使用:
但是,理论上认为一条sql语句必须从一个数据源中去获取数据!
所以,为了保证sql语句语法的结构完整性,在Mysql中执行select语句的时候在适当的时候会自动创建一个虚拟表!这个虚拟表就是当数据源来使用!
虚拟表的名称:dual


where子句

语法:where 表达式
功能:通过限定的表达式的条件对数据进行过滤,得到我们想要的结果
流程:逐一取出每一条记录,先通过当前记录来计算where后面表达式的值,如果计算的结果为真(非0),就返回来记录,如果计算的结构为假(0),则不返回记录!相当于对所有的记录做了一次遍历!
where子句后面往往配合MySQL运算符一起使用(做条件判断)
MySQL运算符
MySQL支持以下的运算符:
关系运算符
< >
<= >=
= !=(<>)
注意:这里的等于是一个等号


where子句后面往往配合MySQL运算符一起使用(做条件判断)
MySQL运算符
MySQL支持以下的运算符:
关系运算符
< >
<= >=
= !=(<>)
注意:这里的等于是一个等号

between and
做数值范围限定,相当于数学上的闭区间!
比如:
between A and B相当于 [A,B]
另外,between and 的前面还可以加上not,代表相反!

 

in和not in
语法形式:in|not in(集合)
表示某个值出现或没出现在一个集合之中!

逻辑运算符

&& and
|| or
! not


where子句的其他形式
空值查询
select *|字段列表 from 表名 where 字段名 is [not] null


模糊查询
也就是带有like关键字的查询,常见的语法形式是:
select *|字段列表from 表名 where 字段名 [not] like ‘通配符字符串’;

所谓的通配符字符串,就是含有通配符的字符串!
MySQL中的通配符有两个:
_ :代表任意的单个字符
% :代表任意的字符

查找表中含有特殊符号_或含有%的信息
由于%和_都具有特殊的含义,所以如果确实想查询某个字段中含有%或_的记录,需要对它们进行转义!

也就是查找 \_ 和 \%

 


group by子句(也叫作分组统计查询语句!)

语法
group by 字段1[,字段2]……
从形式上看,就是通过表内的某个或某些字段进行分组:
所以,分组之后,只会从每一个组内取出第一条记录,这种查询结果毫无意义!

因为分组统计查询的主要作用不是分组,而是统计!或者说分组的目的就是针对每一个分组进行相关的统计!
此时,就需要使用系统中的一些统计函数!

 

统计函数(聚合函数)

sum():求和,就是将某个分组内的某个字段的值全部相加
语法:select sum() as sum_() from 表 group by 字段

max():求某个组内某个字段的最大值
语法:select max() as max_() from 表 group by 字段
min():求某个组内某个字段的最小值
avg():求某个组内某个字段的平均值
count():统计某个组内非null记录的个数,通常就是用count(*)来表示!

注意:
统计函数都是可以单独的使用的!但是,只要使用统计函数,系统默认的就是需要分组,如果没有group by子句,默认的就是把整个表中的数据当成一组!


多字段分组
group by 字段1[,字段2]……
作用是:先根据字段1进行分组,然后再根据字段2进行分组!


回溯统计
回溯统计就是向上统计!
在进行分组统计的时候,往往需要做上级统计!

比如,先统计各个班的总人数,然后各个班的总人数再相加,就可以得到一个年级的总人数!

再比如,先统计各个班的最高分,然后各个班的最高分再进行比较,就可以得到一个年级的最高分!
在MySQL中,其实就是在语句的后面加上with rollup即可!

注意:
既然group by子句出现在where子句之后,说明了,我们可以先将整个数据源进行筛选,然后再进行分组统计!


having子句
having子句和where子句一样,也是用来筛选数据的,通常是对group by之后的统计结果再次进行筛选!
那么,having子句和where子句到底有什么区别呢?

二者的比较:

1,如果语句中只有having子句或只有where子句的时候,此时,它们的作用基本是一样的!
2,二者的本质区别是:where子句是把磁盘上的数据筛选到内存上,而having子句是把内存中的数据再次进行筛选!
3,where子句的后面不能使用统计函数,而having子句可以!因为只有在内存中的数据才可以进行运算统计!

 

order by子句


语法
根据某个字段进行排序,有升序和降序!
语法形式为:
order by 字段1[asc|desc]
默认的是asc,也就是升序!如果要降序排序,需要加上desc!
思考:
假如现在有若干个学生的成绩score是一样的,怎么办?
此时,可以使用多字段排序!
多字段排序
order by 字段1[asc|desc],字段2[asc|desc]……
比如:order by score asc,age desc

也就是说,先按分数进行升序排序,如果分数一样的时候,再按年级进行降序排序!

 

limit子句

limit就是限制的意思,所以,limit子句的作用就是限制查询记录的条数!

语法
limit offset,length

其中,offset是指偏移量,默认为0,而length是指需要显示的记录数!
思考:
limit子句为什么排在最后?
因为前面所有的限制条件都处理完了,只剩下需要显示多少条记录的问题了!

思考:
假如现在想显示记录的第4条到第8条,limit子句应该怎么写?
limit 3,5;
注意:这里的偏移量offset可以省略的!缺省值就代表0!

分页原理
假如在项目中,需要使用分页的效果,就应该使用limit子句!

比如,每页显示10条记录:
第1页:limit 0,10
第2页:limit 10,10
第3页:limit 20,10
如果用$pageNum代表第多少页,用$rowsPerPage代表每页显示的长度

limit ($pageNum - 1)*$rowsPerPage, $rowsPerPage

 

联合查询


关键字:union

语法形式
select语句1
union[union选项]
select 语句2
union[union选项]
select 语句3
union[union选项]
……

作用:
所谓的联合查询,就是将多个查询结果进行纵向上的拼接,也就是select语句2的查询结果放在select语句1查询结果的后面。依次类推!

既然是纵向上的拼接,所以联合查询有一个最最基本的语法,就是各个select语句查询出来的结果,其中字段的个数必须相同,比如,不能一个是4列,一个是3列!并且从显示的结果上看,无论“拼接”了多少个select语句,字段名只显示第一条select语句的字段名!

其中,这里的union选项跟前面学习的select选择的值是一样的,只是默认值不一样:
all:也是缺省值,保留所有的查询结果!
distinct:去重(默认值),去掉重复的查询结果!

union的应用
union的主要应用有以下的两种情形:
第一,获得数据的条件,在同一个select语句中存在逻辑冲突,或者很难在同一个select语句中实现,这个时候,我们需要把这个业务逻辑进行拆分,也就是使用多个select语句单独的分别实现,然后再用union将各个select语句查询结果“拼接”到一起!

这里需要有几个主意的地方:
1,联合查询中如果要使用order by,那么就必须对这个select语句加上一对括号!
2,联合查询中的order by必须搭配上limit关键字才能生效!因为系统默认的联合查询的结果往往比较多,所以要加以限制,当然,如果想显示全部的数据,可以在limit子句后面加上一个很大的数,比如:999999


第二,如果一个项目的某张数据表的数据量特别大,往往会导致数据查询效率很低下,此时,我们需要对数据表进行优化处理,往往就是对该数据表进行“水平分割”,此时,这些拆分后的数据表,表结构都是一样的,只是存放的数据不一样!

比如:一个市区的手机号码机主信息,可以按不同网段放在不同的数据表中,现在,移动公司想查询平均每月消费在500元以上的客户,应该怎么查询?

回答:一个表一个表的查询,然后再通过union进行联合查询!

 


连接查询简介

概念
表与表之间是有联系的!
所谓的连接查询,就是指将两张或多张表按照某个指定的条件,进行横向上的连接并显示!

所以,从查询结果上看,字段数增加了!
比如:A表有3个字段,B表有4个字段,A表和B表进行连接查询可能有7个字段!


连接查询的操作有很多,根据连接查询的方式或性质不同,又可以将连接查询分成:
交叉连接、内连接、外连接和自然连接!

 


交叉连接

关键字:cross join

含义
就是从一张表的一条记录去连接另一张表中的所有记录,并且保存所有的记录,其中包括两个表的所有的字段!

从结果上看,就是对两张表做笛卡尔积!
笛卡尔积也就是两个表中所有可能的连接结果!如果第一张表有n1条记录,第二张表有n2条记录,那么笛卡尔积的结果有n1*n2条记录!

交叉连接语法:
select * | 字段列表 from 表1 cross join 表2

 

Mysql