PHP重点知识复习 – MySQL数据库层的优化

一、数据表数据类型优化

1. tinyint、smallint、bigint

考虑空间的问题,考虑范围的问题

2.char、varchar

存储字符串长度是否固定

3.enum

特定、固定的分类可以使用enum存储,效率更快

4.IP地址的存储

使用ip2long()函数,将IP地址转为整型

使用long2ip()函数,将整型转化为IP地址

二、索引的优化

索引的创建原则

索引不是越多越好,在合适的字段上创建合适的索引

复合索引的前缀原则:like查询%问题、全表扫描优化、or条件索引使用情况、字符串类型索引失效的问题

三、SQL语句的优化

1.优化查询过程中的数据访问

使用Limit、返回列不用*

2.优化长难句的查询语句

变复杂为简单、切分查询、分解关联查询、

四、优化特定类型的查询语句

优化count()

优化关联查询

优化子查询

优化Group by 和 distinct

优化limit 和 union

五、存储引擎的优化

尽量使用InnoDB存储引擎

六、数据表结构设计的优化

1.分区操作

通过特定的策略对数据表进行物理拆分

多用户透明

partition by

2.分库分表

水平拆分

垂直拆分

七、数据库架构的优化

主从复制

读写分离

双主热备

负载均衡

八、MySQL的负载均衡

通过LVS的三种基本模式实现负载均衡

MyCat数据库中间件实现负载均衡

 

PHP重点知识复习 – MySQL的安全性

一、SQL查询的安全方案

1.使用预处理语句防SQL注入,一般使用PDO处理

2.写入数据库的数据要进行特殊字符的转义

3.查询错误信息不要返回给用户,将错误记录到日志

注意:PHP端尽量使用PDO对数据库进行相关操作,PDO拥有对预处理语句很好的支持的方法,MySQLi也有,但是可扩展性不如PDO,效率略高于PDO,MySQL函数在新版本中已经趋向于淘汰,所以不建议使用,而且它没有很好的支持预处理的方法。

二、MySQL的其他安全设置

1.定期做数据备份

2.不给查询用户root权限,合理分配权限

3.关闭远程访问数据库权限

4.修改root口令,不用默认口令,使用较复杂的口令

5.删除多余的用户

6.改变root用户的名称

7.限制一般用户浏览其他库

8.限制用户对数据库文件的访问权限

解题办法:

通常情况下,SQL安全的考点都在防SQL注入的问题,因此只要遇到此类考点,优先考虑SQL注入的防护手段。

 

PHP重点知识复习 – MySQL的高扩展和高可用(待完善)

一、分区表的原理

1.工作原理

创建表时使用 partition by 子句定义每个分区存放的数据,执行查询时,优化器会根据分区定义过滤那些没有我们需要数据的分区,这样查询只需要查询所需数据在的分区即可。

分区的主要目的是将数据按照一个较粗的粒度分在不同的表中,这样可以将相关的数据存放在一起,而且如果想一次性删除整个分区的数据也很方便。

2.适用场景

(1)表非常大,无法全部存在内存,或者只在表的最后有热点数据,其他都是历史数据

(2)分区表的数据更易维护,可以对独立的分区进行独立的操作

(3)分区表的数据可以分布在不同的机器上,从而高效使用资源

(4)可以使用分区表来避免某些特殊的瓶颈

(5)可以备份和恢复独立的分区

3.限制

(1)一个表最多只能有1024个分区

(2)5.1版本中,分区表表达式必须是整数,5.5可以使用列分区

(3)分区字段中如果有主键和唯一索引列,那么主键列和唯一列都必须包含进来

(4)分区表中无法使用外键约束

(5)需要对现有表的结构进行修改

(6)所有分区都必须使用相同的存储引擎

(7)分区函数中可以使用的函数和表达式会有一些限制

(8)某些存储引擎不支持分区

(9)对于MyISAM的分区表,不能使用 load index into cache

(10)对于MyISAM表,使用分区表时需要打开更多的文件描述符

二、分库分表

1.工作原理

通过一些HASH算法或者工具实现将一张数据表垂直或者水平进行物理切分

2.适用场景

(1)单表记录条数达到百万到千万级别时

(2)解决表锁的问题

3.分表方式

水平分割

表很大,分割后可以降低在查询时需要读的数据和牵引的页数,同时也降低了索引的层数,提高查询速度

使用场景

(1)表中的数据本身就有独立性,例如表中分别记录各个地区的数据或者不同时期的数据,特别时有些数据常用,有些不常用

(2)需要把数据存放在多个介质上

水平分表缺点:

(1)给应用增加复杂度,通常查询时需要多个表名,查询所有数据都需要UNION操作

(2)在许多数据库应用中,这种复杂性会超过它带来的有点,查询时会增加读一个索引层的磁盘次数

垂直分表

把主键和一些列放在一个表,然后把主键和另外的列放在另一个表中

使用场景

(1)如果一个表中某些列常用,而另外一些列不常用

(2)可以使数据行变小,一个数据页能存储更多数据,查询时减少I/O次数

垂直分表缺点

管理冗余列,查询所有数据需要JOIN操作

3.分表缺点

有些分表的策略基于应用层的逻辑算法,一旦逻辑算法改变,整个分表逻辑都会改变,扩展性较差

对于应用层来说,逻辑算法无疑增加开发成本

三、主从热备和负载均衡

 

PHP重点知识复习 – MySQL的查询优化(待完善)

一、查找分析查询速度慢的原因

1.分析SQL查询慢的方法

记录慢查询日志

分析查询日志,不要直接打开慢查询日志进行分析,这样比较浪费时间和精力,可以使用pt-query-digest工具进行分析

2.使用 show profile

set profiling = 1; 开启服务器上执行的所有语句检测消耗的时间,存到临时表中

show profiles

show profile for query 临时表ID

3.使用 show status

show status 会返回一些计数器,show global status 查看服务器级别的所有计数

有时根据这些计数,可以猜测出哪些操作代价较高或者消耗时间多

4.使用 show processlist

观察是否右大量线程处于不正常的状态或特征

5.使用 explain 别名 desc

分析单条SQL语句

二、优化查询过程中的数据访问

1.访问数据太多导致查询性能下降

2.确定应用程序是否在检索大量超过需要的数据,可能是太多行或列

3.确认MySQL服务器是否在分析大量不必要的数据行

4.避免使用如下SQL语句

查询不需要的记录,使用 limit 解决

多表关联返回全部列,指定 A.id,A.name,B.age

总是取出全部列,SELECT * 会让优化器无法完成索引覆盖扫描的优化

重复查询相同的数据,可以缓存数据,下次直接读取缓存

5.是否在扫描额外的记录

使用 explain 来进行分析,如果发现查询需要扫描大量的数据但只返回少数的行,可以通过如下技巧去优化:

使用索引覆盖扫描,把所用的列都放到索引中,这样存储引擎不需要回表获取对应行就可以返回结果

改变数据库和表的结构,修改数据表范式

重写SQL语句,让优化器可以以更优的方式执行查询

三、优化长难的查询语句

一个复杂查询还是多个简单查询更好?

MySQL内部每秒能扫描内存中上百万行数据,相比之下,响应数据给客户端就要慢得多

使用尽可能少得查询是好的,但是有时将一个大的查询分解为多个小的查询是很有必要的

1.切分查询

将一个大的查询分为多个小的相同的查询

一次性删除1000万的数据要比一次删除1万,暂停一会的方案更加损耗服务器开销

2.分解关联查询

可以将一条关联语句分解成多条SQL来执行

让缓存的效率更高

执行单个查询可以减少锁的竞争

在应用层左关联可以更容易对数据库进行拆分

3.优化 conut() 查询

count(*)中的*会忽略所有的列,直接统计所有行数,因此不要使用count(列名)

4.优化关联查询

确定ON或者USING子句的列上有索引

确保 GROUP BY 和 ORDER BY 中只有一个表中的列,这样MySQL才有可能使用索引

5.优化子查询

尽可能使用关联查询来替代

6.优化 GROUP BY 和 DISTINCT

这两种查询均可使用索引来优化,是最有效的优化方法

关联查询中,使用标识列(主键列或auto_increment列)进行分组的效率会更高

如果不需要 ORDER BY,进行GROUP BY 时使用ORDER BY NULL,MySQL不会再进行文件排序

WITH ROLLUP超级聚合,尽量不使用,可以挪到应用程序处理

7.优化LIMIT 分页

LIMIT偏移量大的时候,查询效率较低

可以记录上次查询的最大ID,下次查询时直接根据该ID来查询

8.优化UNION查询

UNION ALL的效率高于UNION

解题方法

对于此类考题,先说明如何定位低效SQL语句,然后根据SQL语句可能低效的原因做排查,先从索引着手,如果索引没有问题,考虑以上几个方面,数据访问的问题,长难查询句的问题还是一些特定类型优化的问题,逐一回答。

 

PHP重点知识复习 – MySQL的SQL语句编写

一、MySQL的关联更新UPDATE语句

1.关联更新

真题测试:有A(id,sex,par,c1,c2),B(id,age,c1,c2)两张表,其中A.id与B.id关联,现在要求写出一条SQL语句,将B中age>50的记录的c1,c2更新到A表中统一记录中的c1,c2字段中

解答:

UPDATE A,B SET A.c1 = B.c1, A.c2 = B.c2 WHERE A.id = B.id AND B.age > 50;

UPDATE A INNER JOIN B ON A.id = B.id SET A.c1 = B.c1, A.c2 = B.c2 WHERE B.age > 50;

二、MySQL的关联查询语句

六种关联查询

交叉连接(CROSS JOIN),内连接(INNER JOIN),外连接(LEFT JOIN/RIGHT JOIN),联合查询(UNION与UNION ALL),全连接(FULL JOIN)

1.交叉连接

SELECT * FROM A,B(,C); 或者

SELECT * FROM A CROSS JOIN B (CROSS JOIN C);

没有任何关联条件,结果是笛卡尔积,结果集会很大,没有意义,很少使用

2.内连接

SELECT * FROM A,B WHERE A.id = B.id; 或者

SELECT * FROM A INNER JOIN B ON A.id = B.id;

多表中同时符合某种条件的数据记录的集合

内连接分为三类

等值连接:ON A.id = B.id

不等值连接:ON A.id > B.id

自连接:SELECT * FROM A T1 INNER JOIN A T2 ON T1.id = T2.pid;

3.外连接

左外连接:LEFT OUTER JOIN,以左表为主,先查询出左表,按照ON后的关联条件匹配右表,没有匹配到的用NULL填充,可以简写成 LEFT JOIN

右外连接:RIGHT OUTER JOIN,以右表为主,先查询出右表,按照ON后的关联条件匹配左表,没有匹配到的用NULL填充,可以简写成 RIGHT JOIN

4.联合查询

SELECT * FROM A UNION SELECT * FROM B UNION……

就是把多个结果集集中在一起,UNION前的结果为基准,需要注意的是联合查询的列数要相等,相同的记录行会合并

5.嵌套查询

用一条SQL语句的结果作为另外一条SQL语句的条件

SELECT * FROM A WHERE id IN (SELECT id FROM B);

解题方法

根据考题要搞清楚表的结构和多表之间的关系,根据想要的结果思考使用哪种关联方式,通常把要查询的列先写出来,然后分析这些列都属于哪些表,才考虑使用关联查询。

真题测试

为了记录足球比赛的结果,设计表如下

其中,match赛程表中的hostTeamID与guestTeamID都和team表中的teamID关联,查询2002-6-1到2006-7-1之间举行的所有比赛,并且用以下形式列出:拜仁 2:0 不莱梅 2006-6-21

解答:

SELECT t1.teamName, m.matchResult, t2.teamName, m.matchTime FROM match as m LEFT JOIN team as t1 ON m.hostTeamID = t1.teamID, LEFT JOIN team as t2  ON m.guestTeamID = t2.teamID  WHERE m.matchTime BETTWEN “2006-6-1” AND “2006-7-1”;

 

此部分内容参考教程 《SQL语句设计》

PHP重点知识复习 – MySQL索引的基础和类型

一、MySQL索引的基础和类型

1.索引的基础

索引类似于书籍的目录,要想找到一本书的某个特定主题,需要先查找书的目录,定位对应的页码。

存储引擎使用类似的方式进行数据查询,先去索引当中找到对应的值,然后根据匹配的索引找到对应的数据行。

2.索引对性能的影响

优点:

大大减少服务器需要扫描的数据量

帮助服务器避免排序和临时表

将随机I/O变成顺序I/O

大大提高查询的速度

缺点:

降低写的速度、占用磁盘

3.索引的使用场景

对于非常小的表,大部分情况下全表扫描效率更高

中到大型表,索引非常有效

特大型的表,建立和使用索引的代价将随之增长,可以使用分区技术来解决

4.索引的类型

索引有很多中类型,都是实现在存储引擎层的

普通索引:最基本的索引,没有任何约束限制

唯一索引:与普通索引类似,但是具有唯一性约束

主键索引:特殊的唯一索引,不允许有空值

一个表只能有一个主键索引,可以有多个唯一索引

主键索引一定是唯一索引,唯一索引不是主键索引

主键可以与外键构成参照完整性约束,防止数据不一致

组合索引(复合索引):将多个列组合在一起创建索引,可以覆盖多个列

外键索引:只有InnoDB类型的表才可以使用外键索引,保证数据的一致性、完整性和实现级联操作

全文索引:MySQL自带的全文索引只能用于MyISAM,并且只能对英文进行全文索引

外键索引和全文索引不常用,一般外键索引的功能我们可以在程序逻辑中实现,全文索引可以使用ES等全文搜索引擎实现。

二、MySQL索引的创建原则

原则

1.最适合索引的列是出现在WHERE子句中的列,或连接字句中的列而不是出现在SELECT关键字后的列

比如 select id,email,tel from user where username = “xiaofan”;  这里我们应该选择 username 作为索引

2.索引列的基数越大,索引的效果越好

3.对字符串进行索引,应该制定一个前缀长度,可以节省大量的索引空间,这个就被称为前缀索引

比如,下面username长度比较长,如果创建完整的索引值,会影响索引效率。我们可以选择合适长度的前缀作为索引。下面选择username前5个字符作为索引

create table user(
  `id` int unsigned not null auto_increament primary key,
  `username` varchar(32) not null default '',
  KEY user_username(username(5))
);

4.根据情况创建复合索引,复合索引可以提交查询效率

比如,我们查找一本书的某一章下面的某一节内容,使用复合索引会大大提高查询效率

create table user(
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(32) NOT NULL DEFAULT '',
  `password` CHAR(32) NOT NULL DEFAULT '',
  UNIQUE user_username_password(`username`,`password`)
);

也可以使用 KEY user_username_password(username,password),但是由于上面的表中用户名和密码是有唯一关联性的所以用UNIQUE更合适,效率更高。

5.避免创建过多索引,索引会额外占用磁盘空间,降低写操作效率

6.主键尽可能选择较短的数据类型,可以有效减少索引的磁盘占用提高查询效率

三、MySQL索引的注意事项

注意

1.复合索引遵循前缀原则

比如

KEY(a,b,c)

WHERE a = 1 and b = 2 and c = 3

WHERE  a = 1 and b =2

WHERE a = 1

以上查询语句可以使用到定义的复合索引

WHERE b = 2 and c =3

WHERE a = 1 and c =3

以上这两个就是无法使用到复合索引

因为复合索引遵循前缀原则,只能从左到右依次查询

2.like查询,%不能在前,可以使用全文索引

比如

index user_username(username)

where name like “wang%” 这样可以使用索引

where name like “%wang%” 这样就不能使用索引了

3.column is null 可以使用索引

4.如果MySQL估计使用索引比全表扫描更慢,会放弃使用索引

5.如果 or 关键字前的条件中的列有索引,后面的没有,索引都不会被用到

6.列类型是字符串,查询时一定要给值加引号,否则索引失效

比如

name varchar(20)

where name = 100;  //这里可能有个name值是100,查询时不带引号,索引会失效

正确写法是 where name = “100”.

 

PHP重点知识复习 – MySQL数据库基础

一、MySQL数据类型

1.整数类型

TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT

属性:UNSIGNED

2.实数类型

FLOAT、DOUBLE、DECIMAL

DECIMAL存储比BIGINT更大的整数,可以存储精确的小数

FLOAT和DOUBLE类型支持使用标准的浮点进行近似计算

3.字符串类型

VARCHAR、CHAR、TEXT、BLOB

VARCHAR类型用于存储可变长度的字符串,它比CHAR定长类型更节省空间

VARCHAR类型会使用1或2个额外字节记录字符串的长度,列长度小于255个字节,使用1个字节表示,否则用2个

CHAR是定长的,根据定义的字符串长度分配足够的空间

CHAR会根据需要采用空格填充以方便比较

CHAR适合存储很短的字符串,或者所有值都接近同一长度

超出CHAR定义的长度,字符串会被截断

对于经常变更的数据,CHAR比VARCHAR更好,CHAR不易产生碎片

对于非常短的列,CHAR比VARCHAR在存储空间上更有效率

只分配真正需要的空间,更长的列会消耗更多的内存

尽量避免使用BLOB或TEXT,查询会使用临时表,导致严重的性能开销。

4.枚举类型

有时可以使用枚举代替常用的字符串类型

把不重复的集合存储成一个预定义的集合

非常紧凑,把列表值压缩到一个或两个字节

内存存储的是整数

尽量避免使用数字作为ENUM枚举的常量,易混乱

排序是按照内部存储的整数进行排序

枚举表会使表大小大大减小

5.日期和时间类型

尽量使用TIMESTAMP,比DATETIME空间效率高

用整数保存时间戳的格式通常不方便处理

如果需要存储微秒,可以使用BIGINT存储

6.列属性

auto_increment、default、not null、zerofill

二、MySQL数据表引擎

InnoDB表引擎

默认事务型引擎,最重要最广泛的存储引擎,性能非常优秀

数据存储在共享表空间,可以通过配置分开

对主键查询的性能高于其他类型的存储引擎

内部做了很多优化,从磁盘读取数据时自动在内存构建hash索引,插入数据时自动构建插入缓冲区

通过一些机制和工具支持真正的热备份

支持崩溃后的安全恢复

支持行级锁

支持外键

MyISAM表引擎

5.1版本前,MyISAM是默认的存储引擎

拥有全文索引、压缩、空间函数

不支持事务和行级锁,不支持崩溃后的安全恢复

表存储在两个文件,MYD和MYI

设计简单,某些场景下性能很好

其他表引擎

Archive、Blackhole、CSV、Memory

优先选择InnoDB

三、MySQL锁机制

1.基础概念

当多个查询同一时刻进行数据修改时,就会产生并发控制的问题

共享锁和排他锁,其实就是读锁和写锁

2.读锁

共享的,不堵塞,多个用户可以同时读一个资源,互不干扰

3.写锁

排他的,一个写锁会阻塞其他的写锁和读锁,这样可以只允许一个人进行写入,防止其他用户读取正在写入的资源。

4.锁粒度

表锁,系统性能开销最小,会锁定整张表,MyISAM使用表锁

行锁,最大程度地支持并发处理,但是也带来了最大的锁开销,InnoDB使用行级锁

四、MySQL事务处理

1.事务处理

MySQL提供事务处理的表引擎,InnoDB

服务器层不管理事务,由下层的引擎实现,所以同一个事务中,使用多种存储引擎不靠谱(也就是多张表需要都使用InnoDB引擎,才能进行正常的事务处理)

在非事务的表上执行事务操作MySQL不会发出提醒,也不会报错

因此,在进行事务处理时,需要首先检查下表是否使用的InnoDB引擎

2.存储过程

为以后的使用而保存的一条或多条MySQL语句的集合

存储过程就是有业务逻辑和流程的集合

可以在存储过程中创建表,更新数据,删除等等

3.使用场景

通过把处理封装在容易使用的单元中,简化复杂的操作

保证数据的一致性

简化对变动的管理

五、MySQL触发器

1.触发器

提供给程序员和数据分析员来保证数据完整性的一种方法,它时与表事件相关的特殊的存储过程

2.使用场景

可以通过数据库中的相关表实现级联更改

实时监控某张表中的某个字段的更改而需要做出相应的处理

某些业务编号的生成等

滥用会造成数据库及应用程序的维护困难

 

此部分内容还可以参考 《MySQL数据库》

MySQL数据库使用唯一索引避免重复插入数据

我们在MySQL数据库时,有时不希望存入重复的数据。比如用户表中想要每个用户的用户名不重复。除了,在插入数据时使用程序查询数据库判断,我们还可以使用MySQL数据库的唯一索引进行限制。以下说下具体操作方法:

1.在数据库表中为某个字段设置唯一索引,比如用户表中的 username 字段,设置唯一索引 UNIQUE

ALTER TABLE user ADD UNIQUE username_unique (username);

注意:在创建唯一索引时,有时会出现提示 ERROR 1062 (23000): Duplicate entry ‘***’ for key ‘username_unique’。这个问题的原因是该字段中已经存在重复的值,所以不能直接创建唯一索引。解决办法是删除重复的值,或清空表数据。

2.SQL插入语句修改,这里有三种方法插入数据

2.1 使用 ignore 关键字

INSERT IGNORE INTO user(username,password) values('admin','123456');

使用 ignore 关键字插入数据时,如果索引为唯一索引或主键索引的字段有重复的记录会忽略插入,执行返回0。

2.2 使用 repalce into 关键字

REPLACE INTO user(username,password) values('admin','123456');

使用replace into 插入数据,如果索引为唯一索引或主键索引的字段有重复的记录,它会先将字段重复的那条数据删除,再插入新数据。返回受影响的行数,这个数是删除与插入的行数总和。

2.3 使用 on duplicate key update 

INSERT INTO user(username,password) values('admin','123456') ON DUPLICATE KEY UPDATE password = 456789;

这句sql语句的含义是,当username的索引是唯一索引或主键索引时,插入的数据重复,就执行UPDATE语句。类似于下面的语句

UPDATE user SET password = 456789 WHERE username = 'admin';

 

小提示:善用MySQL的索引功能,可以大大提升数据库的性能。

 

PHP入门 – PHP连接并操作MySQL数据库

本节文章主要介绍使用mysql_connect()函数连接数据库,同时使用mysql_*相关函数操作数据库。

关于mysql_*函数以及对应的mysql扩展库有以下说明:

PHP5中使用mysql_connect()函数进行连接,但是从PHP5.5开始,MySQL就不推荐使用了,属于废弃函数。

PHP7中已经彻底不支持了,根据官网说明,取而代之的是如下两个:mysql扩展自 PHP 5.5.0 起已废弃,并在将来会被移除。应使用 MySQLi 或 PDO_MySQL 扩展来替换之。

具体用法和说明请参考: http://php.net/manual/zh/mysqlinfo.api.choosing.php

用以替代本函数的有:mysqli_connect()  PDO::__construct()

使用时,不要在使用mysql_connect了,可以换用mysqli_connect(),用法基本类似吧,据说是面向对象的库。

PHP连接MySQL数据库

1.连接MySQL数据库服务器

通过PHP脚本程序去管理MySQL服务器中的数据,也必须先建立连接,然后才能通过PHP中的函数向服务器中发送SQL查询语句。PHP可以通过MySQL功能模块去连接MySQL服务器,办法是调用mysql_connect()函数,和使用MySQL客户机程序连接MySQL服务器类似。

resource mysql_connect ( [string server [, string username [, string password [, bool new_link [, int client_flags]]]]] )

2.判断是否连接正确

我们也可以使用两个函数来判断,mysql_errno()和mysql_error(),这两个函数分别返回上次MySQL发生的错误号和错误信息,如果未发生任何错误,mysql_errno()函数将返回0.所以,我们可以使用判断来进行处理:

int mysql_errno ([ resource $link_identifier ] )

string mysql_error ([ resource $link_identifier ] )

if(mysql_errno()){ exit(‘数据库连接错误!’.mysql_error()); }

3.选择数据库

通常数据库的创建工作都是先由数据库管理员(DBA)建立,再由PHP程序员在脚本中使用。在使用PHP脚本建立起与MySQL服务器的连接之后,为了避免每次调用PHP的mysql扩展函数时都指定目标数据库,最好先用mysql_select_db()函数为后续操作选定一个默认数据库,这个函数和SQL命令“USE bookstore”功能相似。

bool mysql_select_db ( string $database_name [, resource $ link_identifier ] )

4.设置字符集

为了避免读取和写入数据时发生数据乱码,除了要将文件格式设置为utf-8无bom头格式,还要将数据库客户端字读集设置为utf8,所以我们需要在发送sql语句之前,使用mysql_set_charset()函数来完成数据库字符集的设定!

bool mysql_set_charset ( string $charset [, resource $link_identifier = NULL ] )

5.准备SQL语句

设置字符集完毕之后,我们需要为读取或写入数据设计SQL语句字符串,例如: $sql = “select username,password from user”;

6.发送SQL语句到MySQL服务器

设计好SQL语句之后,我们使用函数mysql_query()函数,将SQL语句发送到MySQL数据库服务器,由MySQL数据库服务器来执行此SQL语句,这里注意,我们之所以将SQL语句赋值给一个变量是为了出现错误的时候便于排错,我们可以直接输出$sql这个变量。

int/resource mysql_query ( string $query [, resource $link_identifier ] )

处理结果集

将SQL语句发送到MySQL服务器之后,MySQL服务器会将执行SQL语句之后的结果返回给PHP端,这里的结果分为两种:

1.布尔型

2.结果集资源类型

当我们执行的SQL语句是DML语句,也就是增,删,改三种语句,这时mysql_query()返回的类型是布尔类型,执行成功返回真,失败返回假,但有时SQL语句执行成功,但不一定有受影响行数,所以我们可以使用mysql_affected_rows()函数返回受影响行数;

当我们执行的是DQL语句,也就是查询语句,mysql_query()函数将返回的是结果集资源类型,我们可以使用mysql_num_rows($result)函数来获取结果集当中的记录条数,但是我们无法将结果集资源当中的数据得到,因此PHP为我们提供了4个函数,来解析此结果集资源:

1.mysql_fetch_array() 返回索引和关联的混合数组

2.mysql_fetch_assoc() 返回关联数组

3.mysql_fetch_row() 返回索引数组

4.mysql_fetch_object() 返回一个对象

如果没有特殊要求,尽量不要去使用mysql_fetch_array()方法。使用mysql_fetch_row()或mysql_fetch_assoc()函数实现相同的功能,效率会更高一些。上述四个函数每执行一次,结果集资源的指针都将向后移动一位,直到最后一位,将返回布尔类型的FALSE,因此,我们可以使用条件型循环while配合上述四个函数来使用,以mysql_fetch_assoc()函数为例,如下所示:

while($row=mysql_fetch_assoc($result)){

//$row为一条记录的数组

}

取得前一次 MySQL 操作所影响的记录行数

int mysql_affected_rows ([ resource $link_identifier ] )

取得上一步 INSERT 操作产生的 ID

int mysql_insert_id ([ resource $link_identifier ] )

取得结果集中字段的数目

int mysql_num_fields ( resource $result )

取得结果数据

mixed mysql_result ( resource $result , int $row [, mixed $field ] )

PHP会把结果数据表一直保存到PHP脚本执行结束,如果必须提前释放某次查询的结果数据表,就可使用mysql_free_result()函数提前释放它,最后将数据库连接关闭掉,我们使用mysql_close()函数来完成。

bool mysql_free_result ( resource $result )

bool mysql_close ([ resource $link_identifier ] )

以下是完整示例

//连接数据库
$host = 'localhost';
$datebase = 'phplearn';
$db_username = 'root';
$db_password = '';
$db = mysql_connect($host,$db_username,$db_password);
if(mysql_errno()){
	exit('数据库连接失败!'.mysql_error());
}
mysql_select_db($datebase, $db);
mysql_set_charset('utf8', $db);

//SQL查询语句
$sql = 'SELECT id,username,tel FROM users';
$result = mysql_query($sql, $db);
while($row = mysql_fetch_assoc($result)){
	echo $row['username'].'--'.$row['tel'].'<br>';
}
echo mysql_affected_rows($db).'<br>';

//SQL插入/修改/删除语句
$sql = "INSERT INTO users(username,tel) VALUES('bosheng',15189856231)";
$result = mysql_query($sql, $db);
if($result == true){
	echo '插入成功!'.'<br>';
}

echo mysql_insert_id($db);

mysql_free_result($result);
mysql_close($db);

 

PHP入门 – SQL语句设计/数据表增删改查

在我们创建好数据库和数据表后,我们就需要往数据表里插入数据了。这也是我们经常听到的增删改查。

操作数据表中的数据记录

1.使用INSERT语句向数据表中添加数据

格式:

INSERT INTO 表名 [(字段名1,…字段名n)] VALUES(‘值1’,…,’值n’);

INSERT INTO 表名 [(字段名1,…字段名n)] VALUES(‘值1’,…,‘值n’),(‘值1’,…,’值n’);

2.使用UPDATE语句更新数据表中已存在的数据

SQL语句可以使用UPDATE语句对表中的一列或多列数据进行修改,必须指定需要修改的字段,并且需要赋予的新值。还要给出必要的WHERE子句指定要更新的数据行。

格式:

UPDATE 表名 SET 字段名=表达式 [,…][WHERE 条件]

示例

UPDATE `users` SET `username`='xiaoming', `tel`='123456789' WHERE `id`=3;

3.使用DELETE语句删除数据表中不需要的数据记录

DELETE语句用来删除数据表中的一条或多条数据记录。

格式:

DELETE FROM 表名 [WHERE 条件]

小提示:删除数据是风险较高的操作,通常在编程中不会使用DELETE删除数据,而是使用更新某列数据的状态。

查询一张表中的数据

1.最简单的查询语句是使用SELECT语句检索记录的特定字段,多个字段可以用逗号分隔,例如:

SELECT username,password FROM user;

也可以使用*从表中检索出所有字段,使用“SELECT *”主要是针对用户的书写方便而言的。如果一张表当中的数据多大几百万,就意味着资源的浪费和漫长的查询等待,所以实际应用时要尽量避免使用它,而把查询的列名准确地列出来,也可以按自己指定的列顺序输出。

 

如果想为返回的列取一个新的标题,以及经过对字段的计算或总结之后,产生了一个新的值,希望把它放到一个新的列里显示,则用AS保留。例如:在上例的输出结果中使用中文字段名,可以在MySQL控制台中输入的命令如下所示:

SELECT username as ‘用户名’ FROM user;

定义别名时一定要使用单引号引起来,其中AS关键字是可选的,在原字段名和别名之间使用一个空格即可。

SELECT username ‘用户名’ FROM user;

2. 如果在使用SELECT语句返回的记录结果中包含重复的记录,可以使用DISTINCT关键字取消重复的数据,只返回一个。另外,要注意DISTINCT关键字的作用是整个查询的列表,而不是单独的一列。DISTINCT会消耗一定的服务器资源,如果不指定,会默认使用ALL关键字作为检索模式。

SELECT DISTINCT gid FROM user_group;

3. 在SELECT语句中,可以使用WHERE子句指定搜索条件,实现从数据表中检索出符合条件的记录。其中,搜索条件可以由一个或多个逻辑表达式组成,这些表达式指定关于某一记录是真或假的条件。在WHERE子句中,可以通过逻辑操作符和比较操作符指定基本的表达式条件。

逻辑操作符

图片1

比较运算符

图片2 图片3

4.空值只能定义在允许NULL字段中出现,NULL值是特殊的值,代表“无值”,与零值(0)和空字符串(”)都不相同。当在不支持默认值的字段中未输入值,或在字段中显式的设置为空,就会出现空值,但不能用处理已知值的方式来处理NULL。为了进行NULL值的搜索,必须采用特殊的语法。如果要检索NULL值,必须使用IS NULL和IS NOT NULL关键字。

5.使用SELECT语句获取数据表中的数据时,返回的记录一般是无规则排列的,有可能每次获取的查询记录截然不同。为了使用检索的结果方便阅读,可以在SELECT语句中使用ORDER BY子句,对检索的结果进行排序。

ORDER BY后面可以接一列或多列用于排序的字段,并且使用DESC或ASC关键字设计字段排序的方式。默认情况下按照升序排列,即使用ASC关键字。否则要按照降序排列,必须使用DESC关键字。ORDER BY子句可以和SELECT语句中的其他子句一起使用,但在子查询中不能有ORDER BY子句,因为ORDER BY子句只能对最终查询结果排序。

SELECT * FROM users ORDER BY id DESC;

以上可以实现对数据表内容按照字段 id 的降序排列

6. 如果在数据表中的记录数非常多,一次从表中返回大量的记录不仅检索的速度慢,用户阅读也很不方便。所以在通过SELECT语句检索时,使用LIMIT子句一次取少量的记录,而用分页的方式继续阅读后面的数据。

LIMIT子句也可以和其他的SELECT子句一起使用,它可以指定两个参数,分别用以设置返回记录的起始位置,和返回记录的数量。

LIMIT子句也可以只使用一个参数,表示从开头位置,即偏移量为0的位置返回指定数量的记录,在上例中使用的“LIMIT 0, 5”等价于“LIMIT 5”。

SELECT * FROM users ORDER BY id DESC LIMIT 2,2;

以上可以实现对数据表内容按照 id 的倒序排列并只读取从第 3条数据的两条数据。

7. 在数据库系统中提供了一系列的内置统计函数,在SQL查询中使用这些统计函数可以更有效地处理数据。这些统计函数把存储在数据库中的数据,描述为一个整体而不是一行行孤立的记录。

图片4

这些函数通常用在SELECT子句中,作为结果数据集的字段返回的结果。在SELECT语句的SELECT子句中使用函数的语法如下:

SELECT 函数名(列名1 或*),…函数名(列名n) FROM 表名; #使用统计函数

SELECT COUNT(*) AS count FROM users;

以上可以统计数据表中有多少列数据。

SELECT COUNT(DISTINCT username) as count from users;

以上可以实现统计数据表中 字段 username 值不重复的列数。

SELECT sex,COUNT(*) AS count FROM users GROUP BY sex;

以上可以实现统计数据表中以字段 sex 分类的各类的列数

8.前面使用统计函数返回的是所有记录的统计结果,如果要对数据进行分组统计,就需要使用GROUP BY子句。这将可以允许用户在对数据进行分类的基础上,进行再查询。GROUP BY子句将表按列值分组,列的值相同的分为一组。如果GROUP BY后面有多个列名,则先按第一个列名分组,再在每组中按第二个列名分组。

需要注意的是,在GROUP BY子句中不支持对字段分配别名,也不支持任何使用了统计函数的集合列。

在完成数据结果的分组查询和统计后,还可以使用HAVING子句来对查询的结果,进行进一步的筛选。

在SELECT语句的子句中:WHERE子句选择所需要的行;GROUP BY子句进行了必要的分组整理;而HAVING子句对最后的分组结果进行了重新筛选。

查询多张表中的数据

1.多表查询(连接查询)

前提条件:假设有两个表,一个是学生表,一个是学生成绩表。

内连接 inner jion :

最常见的连接查询可能是这样,查出学生的名字和成绩:

select s.name,m.mark from student s,mark m where s.id=m.studentid

上面就是我们最常见的inner join,即内连接,把符合student.id=mark.studentid 条件的元组才选出来,也可以写成:

select s.name,m.score from student s inner join mark m on s.id=m.studentid

左连接-left join:

左连接是把左边的表的数据全部选出来:

select s.name,m.score from student s left join mark m on s.id=m.studentid

上面语句就是把左边的表,即student表中的元组全部选出,尽管有些分数表是没数据的,也选了出来

右连接-right join:

右连接就是把右边表的数据全部取出,不管左边的表是否有匹配的数据:

select s.name,m.score from student s right join mark m on s.id=m.studentid

上面的语句就是把mark分数表的数据全部取出来,不管student表中是否有数据匹配

全连接-full join(貌似mysql不支持)

把左右两个表的数据都取出来,不管是否匹配:

select s.name,m.score from student s full join mark m on s.id=m.studentid

2.嵌套查询(子查询)

where型子查询(把内层查询结果当作外层查询的比较条件)

取出每个栏目下最新的产品(goods_id唯一)

select cat_id,goods_id,goods_name from goods where goods_id in(select max(goods_id) from goods group by cat_id);

from型子查询 (把内层的查询结果供外层再次查询)

用子查询查出挂科两门及以上的同学的平均成绩

思路:

先查出哪些同学挂科两门以上

select name,count(*) as gk from stu where score < 60 having gk >=2;

以上查询结果,我们只要名字就可以了,所以再取一次名字

select name from (select name,count(*) as gk from stu having gk >=2) as t;

找出这些同学了,那么再计算他们的平均分

select name,avg(score) from stu where name in (select name from (select name,count(*) as gk from stu having gk >=2) as t) group by name;

exists型子查询(把外层查询结果拿到内层,看内层的查询是否成立)

查询哪些栏目下有商品,栏目表category,商品表goods

select cat_id,cat_name from category where exists(select * from goods where goods.cat_id = category.cat_id);