php面试题之五——MySQL数据库（基础部分）

mysql_num_rows() mysql_affected_rows() 这两个函数都作用于 mysql_query($query)操作的结果，mysql_num_rows() 返回结果集中行的数目。mysql_affected_rows() 取得前一次 MySQL 操作所影响的记录行数。 mysql_num_rows()仅对 SELECT 语句有效，要取得被 INSERT，UPDATE 或者 DELETE 查询所影响到的行的数目，用 mysql_affected_rows()。

相关题目：取得查询结果集总数的函数是?

mysql_num_rows()

2. sql 语句应该考虑哪些安全性？（新浪网技术部）

防止 Sql 注入，对特殊字符进行转义、过滤或者使用预编译的 sql 语句绑定变量。 最小权限原则，特别是不要用 root 账户，为不同的类型的动作或者组建使用不同的账户。 当 sql 运行出错时，不要把数据库返回的错误信息全部显示给用户，以防止泄露服务器和数据库相关信息。

3. 简单描述 mysql 中，索引，主键，唯一索引，联合索引的区别，对数据库的性能有什么影响（从读写两方面）（新浪网技术部）

索引是一种特殊的文件(InnoDB 数据表上的索引是表空间的一个组成部分)，它们包含对数据表里所有记录的引用指针。 普通索引(由关键字 KEY 或 INDEX 定义的索引)的唯一任务是加快对数据的访问速度。 普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字 UNIQUE 把它定义为一个唯一索引。 也就是说，唯一索引可以保证数据记录的唯一性。

主键，是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字 PRIMARY KEY 来创建。 索引可以覆盖多个数据列，如像 INDEX(columnA, columnB)索引，这就是联合索引。 索引可以极大的提高数据的查询速度，但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件。

4. 有一个留言板，用 mysql 做数据库，用户信息包括：用户名，密码，email，留言内容包括：留言 ID，标题，内容，发表时间，状态（审核，未审核）（新浪网技术部）

请实现下列需求： (1).数据库结构。无需写建表语句，用类似下面的表格，描述清楚即可,注意，要在索引栏中注明是否需要创建索引，以及要创建的索引的类型

表名 table_aaa 字段名 字段说明 字段类型 索引 name 姓名 varchar（64） 唯一索引 gender 性别 enum（‘M’，‘F’）

(2).用一个 sql 语句查询出发表留言数量大于 10 条的用户名及其留言数量，查询结果按文章数量降序排列

参考答案： 用户表结构如下：

表名 user 字段名 字段说明 字段类型 索引 user_id 用户编号 int unsigned 主键 name 用户名 varchar(30)   password 密码 char（32）   email 邮箱 varchar（50）  

留言表结构如下：

表名 message 字段名 字段说明 字段类型 索引 message_id 留言编号 int unsigned 主键 title 标题 varchr(100)   content 内容 text   user_id 用户 id int unsigned 普通索引 pubtime 发表时间 int unsigned   state 状态 tinyint 0 未审核 1 审核  

查询语句如下： SELECT u.name, COUNT(*) AS total FROM user AS u INNER JOIN message AS m ON u.user_id = m.user_id GROUP BY u.name HAVING total > 10 ORDER BY total DESC

5. 如何用命令把 mysql 里的数据备份出来（酷讯 PHP 工程师笔试题）

(1). 导出一张表 mysqldump -u 用户名 -p 密码 库名 表名 > 文件名(如 D:/a.sql) (2). 导出多张表 mysqldump -u 用户名 -p 密码 库名 表名 1 表名 2 表名 3 > 文件名(如 D:/a.sql) (3). 导出所有表 mysqldump -u 用户名 -p 密码 库名 > 文件名(如 D:/a.sql) (4). 导出一个库 mysqldump -u 用户名 -p 密码 -B 库名 > 文件名(如 D:/a.sql)

6. 两张表 city 表和 province 表。分别为城市与省份的关系表。

city:

id city provinceid 1 广州 1 2 深圳 1 3 惠州 1 4 长沙 2 5 武汉 3

province:

id province 1 广东 2 湖南 3 湖北

(1). 写一条 sql 语句关系两个表，实现：显示城市的基本信息。 显示字段：城市 id ，城市名， 所属省份 。如： id（城市 id） cityname(城市名) privence(所属省份) …… SELECT c.id AS id,c.city AS cityname,p.province FROM city c LEFT JOIN province p ON c.provinceid=p.id

(2). 如果要统计每个省份有多少个城市，请用 group by 查询出来。 显示字段：省份 id ，省份名，包含多少个城市。 SELECT p.id,p.province,count(c.id) AS num FROM province p LEFT JOIN city c ON p.id = c.provinceid GROUP BY p.id;

7. MySQL 数据库中的字段类型 varchar 和 char 的主要区别是什么？哪种字段的查找效率要高，为什么?

区别一，定长和变长 char 表示定长，长度固定，varchar表示变长，即长度可变。当所插入的字符串超出它们的长度时，视情况来处理，如果是严格模式，则会拒绝插入并提示错误信息，如果是宽松模式，则会截取然后插入。如果插入的字符串长度小于定义长度时，则会以不同的方式来处理，如char（10），表示存储的是10个字符，无论你插入的是多少，都是10个，如果少于10个，则用空格填满。而varchar（10），小于10个的话，则插入多少个字符就存多少个。 varchar怎么知道所存储字符串的长度呢？实际上，对于varchar字段来说，需要使用一个（如果字符串长度小于255）或两个字节（长度大于255）来存储字符串的长度。

区别之二，存储的容量不同 对 char 来说，最多能存放的字符个数 255，和编码无关。 而 varchar 呢，最多能存放 65532 个字符。VARCHAR 的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是 65,532字节

最大有效长度是 65532 字节，在 varchar 存字符串的时候，第一个字节是空的，不存任何的数据，然后还需要两个字节来存放字符串的长度。所以有效长度就是 65535 - 1 - 2= 65532

由字符集来确定，字符集分单字节和多字节 Latin1 一个字符占一个字节，最多能存放 65532 个字符 GBK 一个字符占两个字节， 最多能存 32766 个字符 UTF8 一个字符占三个字节， 最多能存 21844 个字符 注意，char 和 varchar 后面的长度表示的是字符的个数，而不是字节数。

两相比较，char 的效率高，没有碎片，尤其更新比较频繁的时候，方便数据文件指针的操作。但不够灵活，在实际使用时，应根据实际需求来选用合适的数据类型。

相关题目：若一个表定义为 create table t1(c int, c2 char(30), c3 varchar(N)) charset=utf8; 问N 的最大值又是多少？

（65535 - 1 - 2 - 4 - 30 * 3 ）/3

8. IP 该如何保存?

最简单的办法是使用字符串（varchar）来保存，如果从效率考虑的话，可以将 ip 保存为整型（unsigned int），使用 PHP 或 mysql 提供的函数将 ip 转换为整型，然后存储即可。 php 函数：long2ip()和 ip2long() MySQL 函数：inet_aton()和 inet_ntop

9. 设有成绩表如下所示，试查询两门及两门以上不及格同学的平均分。

编号 姓名 科目 分数 1 张三 数学 90 2 张三 语文 50 3 张三 地理 40 4 李四 语文 55 5 李四 政治 45 6 王五 政治 30

#创建一个成绩表 CREATE TABLE grade( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(10) NOT NULL, subject VARCHAR(10) NOT NULL, score TINYINT UNSIGNED NOT NULL );

#插入记录 INSERT INTO grade(name,subject,score) VALUES('张三','数学',90); INSERT INTO grade(name,subject,score) VALUES('张三','语文',50); INSERT INTO grade(name,subject,score) VALUES('张三','地理',40); INSERT INTO grade(name,subject,score) VALUES('李四','语文',55); INSERT INTO grade(name,subject,score) VALUES('李四','政治',45); INSERT INTO grade(name,subject,score) VALUES('王五','政治',30);

#查询语句 SELECT name,AVG(score),SUM(score<60) AS gk FROM grade GROUP BY name HAVING gk>=2;

10. 为了记录足球比赛的结果，设计表如下:

team：参赛队伍表

字段名称 类型 描述 teamID int 主键 teamname varchar(20) 队伍名称

match：赛程表

字段名称 类型 描述 matchID int 主键 hostTeamID int 主队的 ID gusetTeamID int 客队的 ID marchResult varchar(20) 比赛结果 matchTime date 比赛日期

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

#创建参赛队伍表 CREATE TABLE team( teamID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, teamName VARCHAR(30) NOT NULL ); #向参赛队伍表中插入记录 INSERT INTO team(teamName) VALUES('拜仁'); INSERT INTO team(teamName) VALUES('不莱梅'); INSERT INTO team(teamName) VALUES('皇家马德里'); INSERT INTO team(teamName) VALUES('巴塞罗那'); INSERT INTO team(teamName) VALUES('切尔西'); INSERT INTO team(teamName) VALUES('曼联'); INSERT INTO team(teamName) VALUES('AC 米兰'); INSERT INTO team(teamName) VALUES('国际米兰'); #创建 match 赛程表 CREATE TABLE mat( matchID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEy, hostTeamID INT UNSIGNED NOT NULL, guestTeamID INT UNSIGNED NOT NULL, matchResult VARCHAR(20) NOT NULL, matchTime DATE NOT NULL ); #向赛程表中插入几条记录 INSERT INTO mat(hostTeamID,guestTeamID,matchResult,matchTime) VALUES(1,2,'3:1','2006-6-15'); INSERT INTO mat(hostTeamID,guestTeamID,matchResult,matchTime) VALUES(3,4,'2:2','2006-6-28'); INSERT INTO mat(hostTeamID,guestTeamID,matchResult,matchTime) VALUES(5,6,'0:2','2006-7-10'); INSERT INTO mat(hostTeamID,guestTeamID,matchResult,matchTime) VALUES(7,8,'5:3','2006-5-30'); #查询语句 SELECT t1.teamName,matchResult,t2.teamName,matchTime FROM mat LEFT JOIN team AS t1 ON hostTeamID = t1.teamID LEFT JOIN team AS t2 ON guestTeamID = t2.teamID WHERE matchTime BETWEEN '2006-6-1' AND '2006-7-1';

11. 有如下两张表 a 和 b，请写出得到结果表的查询语句

表a：

id num a 5 b 10 c 15 d 10

表b：

id num b 5 c 15 d 20 e 99

结果表：

id sum(num) a 5 b 15 c 30 d 30 e 99 #创建表 a CREATE TABLE t1_uni( id CHAR(1), num TINYINT )engine=MyISAM charset=utf8; #创建表 b CREATE TABLE t2_uni( id CHAR(1), num TINYINT )engine=MyISAM charset=utf8; #向 a 表中插入记录 INSERT INTO t1_uni VALUES('a',5),('b',10),('c',15),('d',10); #向 b 表中插入记录 INSERT INTO t2_uni VALUES('b',5),('c',15),('d',20),('e',99); #查询语句 SELECT id,SUM(num) FROM( SELECT id,num FROM t1_uni UNION ALL SELECT id,num FROM t2_uni) AS tmp GROUP BY id;

12. MYSQL 取得当前时间的函数是?格式化日期的函数是？

current_time()用于取得当前时间 date_format(datetime, format)用于格式化日期，如：select date_format(now(),'%Y%m%d');

13. 写出发贴数最多的十个人名字的 SQL，利用下表：members(id,username,posts,pass,email)

SELECT members.username FROM members ORDER BY posts DESC LIMIT 10;

[!!]14. 请简述项目中优化 sql 语句执行效率的方法,从哪些方面,sql 语句性能如何分析?

尽量选择较小的列将where中用的比较频繁的字段建立索引select子句中避免使用‘*’避免在索引列上使用计算、not in 和<>等操作当只需要一行数据的时候使用limit 1保证单表数据不超过200W，适时分割表。

针对查询较慢的语句，可以使用explain 来分析该语句具体的执行情况。

SQL 中 LEFT JOIN 的含义是________,如果 tbl_user 记录了学生的姓名(name)和学号(ID)，tbl_score 记录了学生（有的学生考试以后被开除了，没有其记录）的学号(ID)和考试成绩(score)以及考试科目(subject)，要想打印出各个学生姓名及对应的的各科总成绩，则可以用 SQL 语句_________。 left join 表示左外连接，以左表为准，左表中的记录都会出现在查询结果中，如果对应的记录在右表中没有匹配的记录，则右表的字段值以 NULL 填充。 #创建表 tbl_user CREATE TABLE tbl_user( id INT NOT NULL, name VARCHAR(50) NOT NULL, PRIMARY KEY (id) ); #创建表 tbl_socre CREATE TABLE tbl_score( id INT NOT NULL, score DEC(6,2) NOT NULL, subject VARCHAR(20) NOT NULL ); #插入记录 INSERT INTO tbl_user (id, name) VALUES (1, 'beimu'); INSERT INTO tbl_user (id, name) VALUES (2, 'aihui'); INSERT INTO tbl_score (id, score, subject) VALUES (1, 90, '语文'); INSERT INTO tbl_score (id, score, subject) VALUES (1, 80, '数学'); INSERT INTO tbl_score (id, score, subject) VALUES (2, 86, '数学'); INSERT INTO tbl_score (id, score, subject) VALUES (2, 96, '语文'); #查询语句 SELECT a.id,SUM(b.score) AS sumscore FROM tbl_user a LEFT JOIN tbl_score b ON a.id=b.id GROUP BY a.id;

16. 使用 php 写一段简单查询，查出所有姓名为“张三”的内容并打印出来

表 user

name tel content date 张三 13333663366 大专毕业 2006-10-11 张三 13612312331 本科毕业 2006-10-15 张四 021-55665566 中专毕业 2006-10-15

请根据上面的题目完成代码：

$mysql_db=mysql_connect("local","root","pass"); mysql_select_db("DB",$mysql_db); $result=mysql_query("select * from user where name='张三'"); while($row=mysql_fetch_array($result)){ echo $row['name']. $row['tel']. $row['content']. $row['date']; echo "<br>"; }

17. 写出 SQL 语句的格式 : 插入 ，更新 ，删除（卓望）

表名 user

name tel content date 张三 13333663366 大专毕业 2006-10-11 张三 13612312331 本科毕业 2006-10-15 张四 021-55665566 中专毕业 2006-10-15

(a).有一新记录(小王 13254748547 高中毕业 2007-05-06)请用 SQL 语句新增至表中 (b).请用 sql 语句把张三的时间更新成为当前系统时间 (c).请写出删除名为张四的全部记录

INSERT INTO user(name,tel,content,date)VALUES('小王','13254748547','高中毕业','2007-05-06'); UPDATE user SET date = date_format(now(),'%Y-%m-%d') WHERE name = '张三' DELETE FROM user WHERE name = '张四'

8. MySQL 自增类型(通常为表 ID 字段)必需将其设为()

整型，并设置为 AUTO_INCREMENT

19. 数据库中的事务是什么?

事务（transaction）是作为一个单元的一组有序的数据库操作。如果组中的所有操作都成功，则认为事务成功，即使只有一个操作失败，事务也不成功。如果所有操作完成，事务则提交，其修改将作用于所有其他数据库进程。如果一个操作失败，则事务将回滚，该事务所有操作的影响都将取消。 ACID 四大特性,原子性、隔离性、一致性、持久性。

20. What's the difference between mysql_fetch_row() and mysql_fetch_array()? （Yahoo）

mysql_fetch_row() 从和指定的结果标识关联的结果集中取得一行数据并作为数组返回。每个结果的列储存在一个数组的单元中，偏移量从 0 开始。 mysql_fetch_array() 是 mysql_fetch_row() 的扩展版本。除了将数据以数字索引方式储存在数组中之外，还可以将数据作为关联索引储存，用字段名作为键名。 mysql_fetch_array() 中可选的第二个参数 result_type 是一个常量，可以接受以下值： MYSQL_ASSOC，MYSQL_NUM 和 MYSQL_BOTH。其默认值是 MYSQL_BOTH。 如 果 用 了 MYSQL_BOTH ， 将 得 到 一 个 同 时 包 含 关 联 和 数 字 索 引 的 数 组 。 用MYSQL_ASSOC 只得到关联索引（如同 mysql_fetch_assoc() 那样），用 MYSQL_NUM 只得到数字索引（如同 mysql_fetch_row() 那样）。

21. 请写出 php 连 mysql 连接中，获取下一个自增长 id 值的方法，可以写多个（酷讯）

方法一，使用 show table status ，然后获取 auto_increment 的值 方法二，使用 select max(id) + 1 from table 方法三，如果是刚插入记录，可以使用 last_insert_id() + 1 获得

22. 从表 login 中选出 name 字段包含 admin 的前 10 条结果所有信息的 sql 语句 （酷讯）

SELECT * FROM login WHERE name LIKE ‘