MySQL最新最全面试题和答案
MYSQL支持事务吗？
在缺省模式下，MYSQL是autocommit模式的，所有的数据库更新操作都会即时提交，所以在缺省情况下，mysql是不支持事务的。
但是如果你的MYSQL表类型是使用InnoDB Tables 或BDB tables的话，你的MYSQL就可以使用事务处理,使用SET AUTOCOMMIT=0就可以使MYSQL允许在非autocommit模式，
在非autocommit模式下，你必须使用COMMIT来提交你的更改，或者用ROLLBACK来回滚你的更改。
示例如下：
START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summmary=@A WHERE type=1;
COMMIT;
MYSQL相比于其他数据库有哪些特点？
MySQL是一个小型关系型数据库管理系统，开发者为瑞典MySQL AB公司，现在已经
被Sun公司收购，支持FreeBSD、Linux、MAC、Windows等多种操作系统
与其他的大型数据库例如Oracle、DB2、SQL Server等相比功能稍弱一些
1、可以处理拥有上千万条记录的大型数据
2、支持常见的SQL语句规范
3、可移植行高，安装简单小巧
4、良好的运行效率，有丰富信息的网络支持
5、调试、管理，优化简单（相对其他大型数据库）
介绍一下mysql的日期和时间函数
这里是一个使用日期函数的例子。下面的查询选择所有date_col 值在最后30 天内的记录。mysql> SELECT something FROM tbl_name
WHERE TO_DAYS(NOW()) – TO_DAYS(date_col) <= 30;
DAYOFWEEK(date)
返回date 的星期索引(1 = Sunday, 2 = Monday, ... 7 = Saturday)。索引值符合ODBC 的标准。
mysql> SELECT DAYOFWEEK(’1998-02-03′);
-> 3
WEEKDAY(date)
返回date 的星期索引(0 = Monday, 1 = Tuesday, … 6 = Sunday)：
mysql> SELECT WEEKDAY(’1998-02-03 22:23:00′);
-> 1
mysql> SELECT WEEKDAY(’1997-11-05′);
-> 2
DAYOFMONTH(date)
返回date 是一月中的第几天，范围为1 到31：
mysql> SELECT DAYOFMONTH(’1998-02-03′);
-> 3
DAYOFYEAR(date)
返回date 是一年中的第几天，范围为1 到366：
mysql> SELECT DAYOFYEAR(’1998-02-03′);
-> 34
MONTH(date)
返回date 中的月份，范围为 1 到12：
mysql> SELECT MONTH(’1998-02-03′);
-> 2
DAYNAME(date)
返回date 的星期名：
mysql> SELECT DAYNAME(”1998-02-05″);
-> …Thursday?
MONTHNAME(date)
返回date 的月份名：
mysql> SELECT MONTHNAME(”1998-02-05″);
-> …February?
QUARTER(date)
返回date 在一年中的季度，范围为 1 到4：
mysql> SELECT QUARTER(’98-04-01′);
-> 2
WEEK(date)
WEEK(date,first)
对于星期日是一周中的第一天的场合，如果函数只有一个参数调用，返回date 为一年的第几周，返回值范围为0 到53 (是的，可能有第53 周的开始)。两个参数形式的WEEK() 允许你指定一周是否以星期日或星期一开始，以及返回值为0-53 还是1-52。这里的一个表显示第二个参数是如何工作的：值含义
0 一周以星期日开始，返回值范围为0-53
1 一周以星期一开始，返回值范围为0-53
2 一周以星期日开始，返回值范围为1-53
3 一周以星期一开始，返回值范围为1-53 (ISO 8601)
mysql> SELECT WEEK(’1998-02-20′);
-> 7
mysql> SELECT WEEK(’1998-02-20′,0);
-> 7
mysql> SELECT WEEK(’1998-02-20′,1);
-> 8
mysql> SELECT WEEK(’1998-12-31′,1);
-> 53
注意，在版本4.0 中，WEEK(#,0) 被更改为匹配USA 历法。注意，如果一周是上一年的最后一周，当你没有使用2 或 3 做为可选参数时，MySQL 将返回0：
mysql> SELECT YEAR(’2000-01-01′), WEEK(’2000-01-01′,0);
-> 2000, 0
mysql> SELECT WEEK(’2000-01-01′,2);
-> 52
你可能会争辩说，当给定的日期值实际上是1999 年的第52 周的一部分时，MySQL 对WEEK() 函数应该返回52。我们决定返回0 ，是因为我们希望该函数返回“在指定年份中是第几周”。当与其它的提取日期值中的月日值的函数结合使用时，这使得WEEK() 函数的用法可靠。如果你更希望能得到恰当的年-周值，那么你应该使用参数 2 或 3 做为可选参数，或者使用函数YEARWEEK() ：
mysql> SELECT YEARWEEK(’2000-01-01′);
-> 199952
mysql> SELECT MID(YEARWEEK(’2000-01-01′),5,2);
-> 52
YEAR(date)
返回date 的年份，范围为1000 到9999：
mysql> SELECT YEAR(’98-02-03′);
-> 1998
YEARWEEK(date)
YEARWEEK(date,first)
返回一个日期值是的哪一年的哪一周。第二个参数的形式与作用完全与WEEK() 的第二个参数一致。注意，对于给定的日期参数是一年的第一周或最后一周的，返回的年份值可能与日期参数给出的年份不一致：
mysql> SELECT YEARWEEK(’1987-01-01′);
-> 198653
注意，对于可选参数0 或1，周值的返回值不同于WEEK() 函数所返回值(0)，WEEK() 根据给定的年语境返回周值。
HOUR(time)
返回time 的小时值，范围为0 到23：
mysql> SELECT HOUR(’10:05:03′);
-> 10
MINUTE(time)
返回time 的分钟值，范围为0 到59：
mysql> SELECT MINUTE(’98-02-03 10:05:03′);
-> 5
SECOND(time)
返回time 的秒值，范围为0 到59：
mysql> SELECT SECOND(’10:05:03′);
-> 3
PERIOD_ADD(P,N)
增加N 个月到时期P(格式为YYMM 或YYYYMM)中。以YYYYMM 格式返回值。注意，期间参数P 不是一个日期值：
mysql> SELECT PERIOD_ADD(9801,2);
-> 199803
PERIOD_DIFF(P1,P2)
返回时期P1 和P2 之间的月数。P1 和P2 应该以YYMM 或YYYYMM 指定。注意，时期参数P1 和P2 不是日期值：
mysql> SELECT PERIOD_DIFF(9802,199703);
-> 11
DA TE_ADD(date,INTERV AL expr type)
DA TE_SUB(date,INTERV AL expr type)
ADDDATE(date,INTERV AL expr type)
SUBDATE(date,INTERV AL expr type)
这些函数执行日期的算术运算。ADDDA TE() 和SUBDA TE() 分别是DATE_ADD() 和DA TE_SUB() 的同义词。在MySQL 3.23 中，如果表达式的右边是一个日期值或一个日期时间型字段，你可以使用+ 和–代替DATE_ADD() 和DATE_SUB()(示例如下)。参数date 是一个DATETIME 或DA TE 值，指定一个日期的开始。expr 是一个表达式，指定从开始日期上增加还是减去间隔值。expr 是一个字符串；它可以以一个“-”领头表示一个负的间隔值。type 是一个关键词，它标志着表达式以何格式被解释。下表显示type 和expr 参数是如何关联的：type 值expr 期望的格式
SECOND SECONDS
MINUTE MINUTES
HOUR HOURS
DAY DAYS
MONTH MONTHS
YEAR YEARS
MINUTE_SECOND “MINUTES:SECONDS”
HOUR_MINUTE “HOURS:MINUTES”
DAY_HOUR “DAYS HOURS”
YEAR_MONTH “YEARS-MONTHS”
HOUR_SECOND “HOURS:MINUTES:SECONDS”
DAY_MINUTE “DAYS HOURS:MINUTES”
DAY_SECOND “DAYS HOURS:MINUTES:SECONDS”
在expr 的格式中，MySQL 允许任何字符作为定界符。表中所显示的是建议的定界字符。如果date 参数是一个DA TE 值，并且计算的间隔仅仅有YEAR、MONTH 和DAY 部分(没有时间部分)，那么返回值也是一个DATE 值。否则返回值是一个DA TETIME 值：mysql> SELECT “1997-12-31 23:59:59″+ INTERV AL 1 SECOND;
-> 1998-01-01 00:00:00
mysql> SELECT INTERV AL 1 DAY + “1997-12-31″;
-> 1998-01-01
mysql> SELECT “1998-01-01″–INTERV AL 1 SECOND;
-> 1997-12-31 23:59:59
mysql> SELECT DA TE_ADD(”1997-12-31 23:59:59″,
-> INTERV AL 1 SECOND);
-> 1998-01-01 00:00:00
mysql> SELECT DA TE_ADD(”1997-12-31 23:59:59″,
-> INTERV AL 1 DAY);
-> 1998-01-01 23:59:59
mysql> SELECT DA TE_ADD(”1997-12-31 23:59:59″,
-> INTERV AL “1:1″MINUTE_SECOND);
-> 1998-01-01 00:01:00
mysql> SELECT DA TE_SUB(”1998-01-01 00:00:00″,
-> INTERV AL “1 1:1:1″DAY_SECOND);
-> 1997-12-30 22:58:59
mysql> SELECT DA TE_ADD(”1998-01-01 00:00:00″,
-> INTERV AL “-1 10″DAY_HOUR);
-> 1997-12-30 14:00:00
mysql> SELECT DA TE_SUB(”1998-01-02″, INTERV AL 31 DAY);
-> 1997-12-02
如果你指定了一个太短的间隔值(没有包括type 关键词所期望的所有间隔部分)，MySQL 假设你遗漏了间隔值的最左边部分。例如，如果指定一个type 为DAY_SECOND，那么expr 值被期望包含天、小时、分钟和秒部分。如果你象“1:10″样指定一个值，MySQL 假设天和小时部分被遗漏了，指定的值代表分钟和秒。换句话说，”1:10″DAY_SECOND 被解释为等价于“1:10″MINUTE_SECOND。这类似于MySQL 解释TIME 值为经过的时间而不是一天的时刻。注意，如果依着包含一个时间部分的间隔增加或减少一个日期值，该日期值将被自动地转换到一个日期时间值：
mysql> SELECT DA TE_ADD(”1999-01-01″, INTERV AL 1 DAY);
-> 1999-01-02
mysql> SELECT DA TE_ADD(”1999-01-01″, INTERV AL 1 HOUR);
-> 1999-01-01 01:00:00
如果你使用了确定不正确的日期，返回结果将是NULL。如果你增加MONTH、YEAR_MONTH 或YEAR，并且结果日期的天比新月份的最大天数还大，那么它将被调整到新月份的最大天数：
mysql> SELECT DA TE_ADD(’1998-01-30′, INTERV AL 1 MONTH);
-> 1998-02-28
注意，上面的例子中，单词INTERV AL 和关键词type 是不区分字母大小写的。EXTRACT(type FROM date)
EXTRACT() 函数使用与DA TE_ADD() 或DATE_SUB() 一致的间隔类型，但是它用于指定从日期中提取的部分，而不是进行日期算术运算。
mysql> SELECT EXTRACT(YEAR FROM “1999-07-02″);
-> 1999
mysql> SELECT EXTRACT(YEAR_MONTH FROM “1999-07-02 01:02:03″);
-> 199907
mysql> SELECT EXTRACT(DAY_MINUTE FROM “1999-07-02 01:02:03″);
-> 20102
TO_DAYS(date)
给出一个日期date，返回一个天数(从0 年开始的天数)：
mysql> SELECT TO_DAYS(950501);
-> 728779
mysql> SELECT TO_DAYS(’1997-10-07′);
-> 729669
TO_DAYS() 无意于使用先于格里高里历法(即现行的阳历)(1582)出现的值，因为它不考虑当历法改变时所遗失的天数。
FROM_DAYS(N)
给出一个天数N，返回一个DA TE 值：
mysql> SELECT FROM_DAYS(729669);
-> ‘1997-10-07′
FROM_DAYS() 无意于使用先于格里高里历法(1582)出现的值，因为它不考虑当历法改变时所遗失的天数。
DA TE_FORMA T(date,format)
依照format 字符串格式化date 值。下面的修饰符可被用于format 字符串中：修饰符含义
%M 月的名字(January..December)
%W 星期的名字(Sunday..Saturday)
%D 有英文后缀的某月的第几天(0th, 1st, 2nd, 3rd, etc.)
%Y 年份，数字的，4 位
%y 年份，数字的，2 位
%X 周值的年份，星期日是一个星期的第一天，数字的，4 位，与‘%V’一同使用
%x 周值的年份，星期一是一个星期的第一天，数字的，4 位，与‘%v’一同使用
%a 缩写的星期名(Sun..Sat)
%d 月份中的天数，数字的(00..31)
%e 月份中的天数，数字的(0..31)
%m 月，数字的(00..12)
%c 月，数字的(0..12)
%b 缩写的月份名(Jan..Dec)
%j 一年中的天数(001..366)
%H 小时(00..23)
%k 小时(0..23)
%h 小时(01..12)
%I 小时(01..12)
%l 小时(1..12)
%i 分钟，数字的(00..59)
%r 时间，12 小时(hh:mm:ss [AP]M)
%T 时间，24 小时(hh:mm:ss)
%S 秒(00..59)
%s 秒(00..59)
%p AM 或PM
%w 一周中的天数(0=Sunday..6=Saturday)
%U 星期(00..53)，星期日是一个星期的第一天
%u 星期(00..53)，星期一是一个星期的第一天
%V 星期(01..53)，星期日是一个星期的第一天。与‘%X’一起使用
%v 星期(01..53)，星期一是一个星期的第一天。与‘%x’一起使用
%% 一个字母“%”
所有其它的字符不经过解释，直接复制到结果中：
mysql> SELECT DA TE_FORMA T(’1997-10-04 22:23:00′, ‘%W %M %Y’);
-> ‘Saturday October 1997′
mysql> SELECT DA TE_FORMA T(’1997-10-04 22:23:00′, ‘%H:%i:%s’);
-> ‘22:23:00′
mysql> SELECT DA TE_FORMA T(’1997-10-04 22:23:00′,
‘%D %y %a %d %m %b %j?);
-> ‘4th 97 Sat 04 10 Oct 277′
mysql> SELECT DA TE_FORMA T(’1997-10-04 22:23:00′,
‘%H %k %I %r %T %S %w?);
-> ‘22 22 10 10:23:00 PM 22:23:00 00 6′
mysql> SELECT DA TE_FORMA T(’1999-01-01′, ‘%X %V’);
-> ‘1998 52′
在MySQL 3.23 中，在格式修饰符前需要字符`%’。在更早的MySQL 版本中，`%’是可选的。月份与天修饰符的范围从零开始的原因是，在MySQL 3.23 中，它允许存储不完善的日期值(例如‘2004-00-00′)。
TIME_FORMAT(time,format)
它的使用方法与上面的DATE_FORMA T() 函数相似，但是format 字符串只包含处理小时、分和秒的那些格式修饰符。使用其它的修饰符会产生一个NULL 值或0。
CURDA TE()
CURRENT_DATE
以‘YYYY-MM-DD’或YYYYMMDD 格式返回当前的日期值，返回的格式取决于该函数是用于字符串还是数字语境中：
mysql> SELECT CURDA TE();
-> ‘1997-12-15′
mysql> SELECT CURDA TE() + 0;
-> 19971215
CURTIME()
CURRENT_TIME
以‘HH:MM:SS’或HHMMSS 格式返回当前的时间值，返回的格式取决于该函数是用于字符串还是数字语境中：
mysql> SELECT CURTIME();
-> ‘23:50:26′
mysql> SELECT CURTIME() + 0;
-> 235026
NOW()
SYSDATE()
CURRENT_TIMESTAMP
以‘YYYY-MM-DD HH:MM:SS’或YYYYMMDDHHMMSS 格式返回当前的日期时间值，返回的格式取决于该函数是用于字符串还是数字语境中：
mysql> SELECT NOW();
-> ‘1997-12-15 23:50:26′
mysql> SELECT NOW() + 0;
-> 19971215235026
注意，函数NOW() 在每个查询中只计算一次，也就是在查询开始执行时。这就是说，如果在一个单独的查询中多次引用了NOW()，它只会给出值都是一个相同的时间。
UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)
如果调用时没有参数，以无符号的整数形式返回一个Unix 时间戳(从‘1970-01-01 00:00:00′GMT 开始的秒数)。如果以一个参数date 调用UNIX_TIMESTAMP()，它将返回该参数值从‘1970-01-01 00:00:00′GMT 开始经过的秒数值。date 可以是一个DATE 字符串，一个DATETIME 字符串，一个TIMESTAMP，或者以一个YYMMDD 或YYYYMMDD 显示的本地时间：
mysql> SELECT UNIX_TIMESTAMP();
-> 882226357
mysql> SELECT UNIX_TIMESTAMP(’1997-10-04 22:23:00′);
-> 875996580
当UNIX_TIMESTAMP 被用于一个TIMESTAMP 列时，函数直接返回一个内部的时间戳值，而不进行一个隐含地“string-to-unix-timestamp”转换。如果你传递一个超出范围的日期参数给UNIX_TIMESTAMP() ，它将返回0，但是请注意，MySQL 对其仅仅进行基本的检验(年范围1970-2037，月份01-12，日期01-31)。如果你希望减去UNIX_TIMESTAMP() 列，你应该需要将结果强制转换为一有符号整数。查看章节6.3.5 Cast 函数。
FROM_UNIXTIME(unix_timestamp [,format])
以‘YYYY-MM-DD HH:MM:SS’或YYYYMMDDHHMMSS 格式返回一个unix_timestamp 参数值，返回值的形式取决于该函数使用于字符串还是数字语境。如果format 给出，返回值依format 字符串被格式。format 可以包含与DA TE_FORMAT() 函数同样的修饰符。
mysql> SELECT FROM_UNIXTIME(875996580);
-> ‘1997-10-04 22:23:00′
mysql> SELECT FROM_UNIXTIME(875996580) + 0;
-> 19971004222300
mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),
‘%Y %D %M %h:%i:%s %x?);
-> ‘1997 23rd December 03:43:30 1997′
SEC_TO_TIME(seconds)
以‘HH:MM:SS’或HHMMSS 格式返回参数seconds 被转换到时分秒后的值，返回值的形式取决于该函数使用于字符串还是数字语境：
mysql> SELECT SEC_TO_TIME(2378);
-> ‘00:39:38′
mysql> SELECT SEC_TO_TIME(2378) + 0;
-> 3938
TIME_TO_SEC(time)
将参数time 转换为秒数后返回：
mysql> SELECT TIME_TO_SEC(’22:23:00′);
-> 80580
mysql> SELECT TIME_TO_SEC(’00:39:38′);
-> 2378
如何解决MYSQL数据库中文乱码问题？
在数据库安的时候指定字符集
如果在安完了以后可以更改以下文件：
C:\Program Files\MySQL\MySQL Server 5.0\my.ini
里的所有的default-character-set=gbk
C:\Program Files\MySQL\MySQL Server 5.0\data\depot_development\db.opt
default-character-set=gbk
default-collation=gbk_chinese_ci
建立数据库时候：指定字符集类型
CREATE DATABASE haichen
CHARACTER SET …gbk?
COLLATE …gbk_chinese_ci?;
2.建表的时候也指定字符集
CREATE TABLE student (
ID varchar(40) NOT NULL default ”,
UserID varchar(40) NOT NULL default ”,
) ENGINE=InnoDB DEFAULT CHARSET=gbk; ————————————————————————————————————-
1。创建数据库的时候：CREATE DA TABASE `database`
CHARACTER SET ‘utf8′
COLLATE …utf8_general_ci?;
2.建表的时候CREATE TABLE `database_user` (
`ID` varchar(40) NOT NULL default ”,
`UserID` varchar(40) NOT NULL default ”,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3.设置URL的时候
jdbc:mysql://localhost:3306/database?useUnicode=true&characterEncoding=UTF-8
如何提高MySql的安全性？
1.如果MYSQL客户端和服务器端的连接需要跨越并通过不可信任的网络，那么需要使用ssh 隧道来加密该连接的通信。
2.使用set password语句来修改用户的密码，先“mysql -u root”登陆数据库系统，然后“mysql> update https://www.360docs.net/doc/f22620193.html,er set password=password(’newpwd’)”，最后执行“flush privileges”就可以了。
3.Mysql需要提防的攻击有，防偷听、篡改、回放、拒绝服务等，不涉及可用性和容错方面。对所有的连接、查询、其他操作使用基于acl即访问控制列表的安全措施来完成。也有一些对ssl连接的支持。
4.设置除了root用户外的其他任何用户不允许访问mysql主数据库中的user表;
加密后存放在user表中的加密后的用户密码一旦泄露，其他人可以随意用该用户名/密码相应的数据库;
5.使用grant和revoke语句来进行用户访问控制的工作;
6.不要使用明文密码，而是使用md5()和sha1()等单向的哈系函数来设置密码;
7.不要选用字典中的字来做密码;
8.采用防火墙可以去掉50%的外部危险，让数据库系统躲在防火墙后面工作，或放置在dmz 区域中;
9.从因特网上用nmap来扫描3306端口，也可用telnet server_host 3306的方法测试，不允许从非信任网络中访问数据库服务器的3306号tcp端口，需要在防火墙或路由器上做设定; 10.为了防止被恶意传入非法参数，例如where id=234，别人却输入where id=234 or 1=1导致全部显示，所以在web的表单中使用”或”"来用字符串，在动态url中加入%22代表双引号、%23代表井号、%27代表单引号;传递未检查过的值给mysql数据库是非常危险的;
11.在传递数据给mysql时检查一下大小;
12.应用程序需要连接到数据库应该使用一般的用户帐号，开放少数必要的权限给该用户; $page_devide$
13.在各编程接口(c c++ php perl java jdbc等)中使用特定‘逃脱字符’函数;
在因特网上使用mysql数据库时一定少用传输明文的数据，而用ssl和ssh的加密方式数据来传输;
14.学会使用tcpdump和strings工具来查看传输数据的安全性，例如tcpdump -l -i eth0 -w -src or dst port 3306 strings。以普通用户来启动mysql数据库服务;
15.不使用到表的联结符号，选用的参数–skip-symbolic-links;
16.确信在mysql目录中只有启动数据库服务的用户才可以对文件有读和写的权限;
17.不许将process或super权限付给非管理用户，该mysqladmin processlist可以列举出当前执行的查询文本;super权限可用于切断客户端连接、改变服务器运行参数状态、控制拷贝复制数据库的服务器;
18.file权限不付给管理员以外的用户，防止出现load data ‘/etc/passwd’到表中再用select 显示出来的问题;
19.如果不相信dns服务公司的服务，可以在主机名称允许表中只设置ip数字地址;
20.使用max_user_connections变量来使mysqld服务进程，对一个指定帐户限定连接数;
21.grant语句也支持资源控制选项;
22.启动mysqld服务进程的安全选项开关，–local-infile=0或1 若是0则客户端程序就无法使用local load data了，赋权的一个例子grant insert(user) on https://www.360docs.net/doc/f22620193.html,er to ‘user_name’@'host_name’;若使用–skip-grant-tables系统将对任何用户的访问不做任何访问控制，但可以用mysqladmin flush-privileges或mysqladmin reload来开启访问控制;默认情况是show databases语句对所有用户开放，可以用–skip-show-databases来关闭掉。
23.碰到error 1045(28000) access denied for user ‘root’@'localhost’(using password:no)错误时，你需要重新设置密码，具体方法是:先用–skip-grant-tables参数启动mysqld，然后执行mysql -u root mysql,mysql>update user set password=password(’newpassword’) where user=’root’;mysql>flush privileges;，最后重新启动mysql就可以了。
MySQL面试题
1、MySQL取得当前时间的函数是?，格式化日期的函数是
2、写出SQL语句的格式: 插入，更新，删除
表名User
Name Tel Content Date
张三133******** 大专毕业2006-10-11
张三136******** 本科毕业2006-10-15
张四021-******** 中专毕业2006-10-15
(a) 有一新记录(小王132******** 高中毕业2007-05-06)请用SQL语句新增至表中
(b) 请用sql语句把张三的时间更新成为当前系统时间
(c) 请写出删除名为张四的全部记录
3、请写出数据类型(int char varchar datetime text)的意思; 请问varchar和char有什么区别
4、MySQL自增类型(通常为表ID字段)必需将其设为(?)字段
5、以下请用PHPMY ADMIN完成
(一)创建新闻发布系统，表名为message有如下字段
id 文章id
title 文章标题
content 文章内容
category_id 文章分类id
hits 点击量
(二)同样上述新闻发布系统：表comment记录用户回复内容，字段如下
comment_id 回复id
id 文章id，关联message表中的id
comment_content 回复内容
现通过查询数据库需要得到以下格式的文章标题列表,并按照回复数量排序，回复最高的排在最前面
文章id 文章标题点击量回复数量
用一个SQL语句完成上述查询，如果文章没有回复则回复数量显示为0
(三)上述内容管理系统，表category保存分类信息，字段如下
category_id int(4) not null auto_increment;
categroy_name varchar(40) not null;
用户输入文章时，通过选择下拉菜单选定文章分类
写出如何实现这个下拉菜单
MYSQL面试题：十一个高级MySql 面试题
1. Explain MySQL architecture. – The front layer takes care of network connections and security authentications, the middle layer does the SQL query parsing, and then the query is handled off to the storage engine. A storage engine could be either a default one supplied with MySQL (MyISAM) or a commercial one supplied by a third-party vendor (ScaleDB, InnoDB, etc.)
2. Explain MySQL locks. – Table-level locks allow the user to lock the entire table, page-level locks allow locking of certain portions of the tables (those portions are referred to as tables), row-level locks are the most granular and allow locking of specific rows.
3. Explain multi-version concurrency control in MySQL. – Each row has two additional columns associated with it – creation time and deletion time, but instead of storing timestamps, MySQL stores version numbers.
4. What are MySQL transactions? – A set of instructions/queries that should be executed or rolled back as a single atomic unit.
5. What?s ACID? – Automicity – transactions are atomic and should be treated as one in case of rollback. Consistency –the database should be in consistent state between multiple states in transaction. Isolation –no other queries can access the data modified by a running transaction. Durability – system crashes should not lose the data.
6. Which storage engines support transactions in MySQL? – Berkeley DB and InnoDB.
7. How do you convert to a different table type? – ALTER TABLE customers TYPE = InnoDB
8. How do you index just the first four bytes of the column? – ALTER TABLE customers ADD INDEX (business_name(4))
9. What?s the difference betw een PRIMARY KEY and UNIQUE in MyISAM? – PRIMARY KEY cannot be null, so essentially PRIMARY KEY is equivalent to UNIQUE NOT NULL.
10. How do you prevent MySQL from caching a query? –SELECT SQL_NO_CACHE …
11. What?s the difference between query_cache_type 1 and 2? – The second one is on-demand and can be retrieved via SELECT SQL_CACHE … If you?re worried about the SQL portability to other servers, you can use SELECT /* SQL_CACHE */ id FROM … – MySQL will interpret the code inside comments, while other servers will ignore it.
MYSQL面试题：简单叙述一下MYSQL的优化
1.数据库的设计
尽量把数据库设计的更小的占磁盘空间.
1).尽可能使用更小的整数类型.(mediumint就比int更合适).
2).尽可能的定义字段为not null,除非这个字段需要null.
3).如果没有用到变长字段的话比如varchar,那就采用固定大小的纪录格式比如char.
4).表的主索引应该尽可能的短.这样的话每条纪录都有名字标志且更高效.
5).只创建确实需要的索引。索引有利于检索记录，但是不利于快速保存记录。如果总是要在表的组合字段上做搜索，那么就在这些字段上创建索引。索引的第一部分必须是最常使用的字段.如果总是需要用到很多字段，首先就应该多复制这些字段，使索引更好的压缩。
6).所有数据都得在保存到数据库前进行处理。
7).所有字段都得有默认值。
8).在某些情况下,把一个频繁扫描的表分成两个速度会快好多。在对动态格式表扫描以取得相关记录时，它可能使用更小的静态格式表的情况下更是如此。
2.系统的用途
1).尽量使用长连接.
2).explain 复杂的SQL语句。
3).如果两个关联表要做比较话，做比较的字段必须类型和长度都一致.
4).LIMIT语句尽量要跟order by或者distinct.这样可以避免做一次full table scan.
5).如果想要清空表的所有纪录,建议用truncate table tablename而不是delete from tablename.
6).能使用STORE PROCEDURE 或者USER FUNCTION的时候.
7).在一条insert语句中采用多重纪录插入格式.而且使用load data infile来导入大量数据，这比单纯的indert快好多.
8).经常OPTIMIZE TABLE 来整理碎片.
9).还有就是date 类型的数据如果频繁要做比较的话尽量保存在unsigned int 类型比较快。
3.系统的瓶颈
1).磁盘搜索.
并行搜索,把数据分开存放到多个磁盘中，这样能加快搜索时间.
2).磁盘读写(IO)
可以从多个媒介中并行的读取数据。
3).CPU周期
数据存放在主内存中.这样就得增加CPU的个数来处理这些数据。
4).内存带宽
当CPU要将更多的数据存放到CPU的缓存中来的话,内存的带宽就成了瓶颈.
介绍一下如何优化MySql
一、在编译时优化MySQL
如果你从源代码分发安装MySQL，要注意，编译过程对以后的目标程序性能有重要的影响，不同的编译方式可能得到类似的目标文件，但性能可能相差很大，因此，在编译安装MySQL 适应仔细根据你的应用类型选择最可能好的编译选项。这种定制的MySQL可以为你的应用提供最佳性能。
技巧：选用较好的编译器和较好的编译器选项，这样应用可提高性能10-30%。（MySQL文档如是说）
1.1、使用pgcc（Pentium GCC)编译器
该编译器（https://www.360docs.net/doc/f22620193.html,/pcg/）针对运行在奔腾处理器系统上的程序进行优化，用pgcc 编译MySQL源代码，总体性能可提高10%。当然如果你的服务器不是用奔腾处理器，就不必用它了，因为它是专为奔腾系统设计的。
1.2、仅使用你想使用的字符集编译MySQL
MySQL目前提供多达24种不同的字符集，为全球用户以他们自己的语言插入或查看表中的数据。却省情况下，MySQL安装所有者这些字符集，热然而，最好的选择是指选择一种你需要的。如，禁止除Latin1字符集以外的所有其它字符集：——————————————————————————–
%>./configure -with-extra-charsets=none [--other-configuration-options] ——————————————————————————–
1.3、将mysqld编译成静态执行文件
将mysqld编译成静态执行文件而无需共享库也能获得更好的性能。通过在配置时指定下列选项，可静态编译mysqld。
——————————————————————————–
%>./configure -with-mysqld-ldflags=-all-static [--other-configuration-options] ——————————————————————————–
1.4、配置样本
下列配置命令常用于提高性能：——————————————————————————-
%>CFLAGS=”-O6 -mpentiumpro -fomit-frame-pointer” CXX=gcc CXXFLAGS=”-O6 -mpentiumpro -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti” ./configure –prefix=/usr/local –enable-assembler –with-mysqld-ldflags=-all-static –disable-shared
二、调整服务器
确保运用正确的编译固然重要，但这只是成功的第一步，配置众多的MySQL变量同样对服务器的正常运行起关键作用。你可以将这些变量的赋值存在一个配置文件中，以确保它们在每次启动MySQL时均起作用，这个配置文件就是https://www.360docs.net/doc/f22620193.html,f文件。
MySQL 已经提供了几个https://www.360docs.net/doc/f22620193.html,f文件的样本，可在/usr/local/mysqld/share/mysql/目录下找到。这些文件分别命名为my- https://www.360docs.net/doc/f22620193.html,f、https://www.360docs.net/doc/f22620193.html,f、https://www.360docs.net/doc/f22620193.html,f和https://www.360docs.net/doc/f22620193.html,f，规模说明可在描述配置文件适用的系统类型标题中找到。如果在只有相当少内存的系统上运行MySQL，而且只是偶尔的用一下，那么https://www.360docs.net/doc/f22620193.html,f会比较理想，因为它命令mysqld只使用最少的资源。类似地，如果你计划构建电子商务超市，而且系统拥有2G内存，那么你可
能要用到https://www.360docs.net/doc/f22620193.html,f文件了。
为了利用这些文件中的一个，你需要复制一个最适合需求的文件，改名为https://www.360docs.net/doc/f22620193.html,f。你可以选择使用配置文件三种作用范围的一种：
Global：将https://www.360docs.net/doc/f22620193.html,f文件复制到服务器的/etc目录下，这使得配置文件中的变量作用于全局，即对所有服务器上的MySQL数据库服务器有效。
Local：将https://www.360docs.net/doc/f22620193.html,f文件复制到[MYSQL-INSTALL-DIR]/var/目录下，使得https://www.360docs.net/doc/f22620193.html,f作用于特定的服务器。[MYSQL-INSTALL-DIR]表示MySQL安装目录。
User：你可以再限制作用于特定的用户，将https://www.360docs.net/doc/f22620193.html,f复制到用户的根目录下。
究竟如何设置https://www.360docs.net/doc/f22620193.html,f中的这些变量呢？更进一步说，你可以设置哪一个变量。虽然所用变量对MySQL服务器相对通用，每一个变量与MySQL的的某些组件有更特定的关系。如变量max_connects归在mysqld类别下。执行下列命令即可知道：——————————————————————————–
%>/usr/local/mysql/libexec/mysqld –help ——————————————————————————–
它显示大量的选项及与mysqld相关的变量。你可以很容易地在该行文字之下找出变量：——————————————————————————–
Possible variables for option –set-variable (-O) are ——————————————————————————–
然后你可以如下设置https://www.360docs.net/doc/f22620193.html,f中的那些变量：——————————————————————————–
set-variable = max_connections=100 ——————————————————————————–
它设置MySQL服务器的最大并发连接数为100。要确保在https://www.360docs.net/doc/f22620193.html,f文件中的[mysqld]标题下插入变量设置。
三、表类型
很多MySQL用户可能很惊讶，MySQL确实为用户提供5种不同的表类型，称为DBD、HEAP、ISAM、MERGE和MyIASM。DBD归为事务安全类，而其他为非事务安全类。
3.1、事务安全
DBD
Berkeley DB(DBD)表是支持事务处理的表，由Sleepycat软件公司(https://www.360docs.net/doc/f22620193.html,)开发。它提供MySQL用户期待已久的功能-事务控制。事务控制在任何数据库系统中都是一个极有价值的功能，因为它们确保一组命令能成功地执行。
3.2、非事务安全
HEAP
HEAP表是MySQL中存取数据最快的表。这是因为他们使用存储在动态内存中的一个哈希索引。另一个要点是如果MySQL或服务器崩溃，数据将丢失。
ISAM
ISAM表是早期MySQL版本的缺省表类型，直到MyIASM开发出来。建议不要再使用它。MERGE
MERGE是一个有趣的新类型，在3.23.25之后出现。一个MERGE表实际上是一个相同MyISAM表的集合，合并成一个表，主要是为了效率原因。这样可以提高速度、搜索效率、修复效率并节省磁盘空间。
MyIASM
这是MySQL的缺省表类型。它基于IASM代码，但有很多有用的扩展。MyIASM比较好的
原因：
MyIASM表小于IASM表，所以使用较少资源。
MyIASM表在不同的平台上二进制层可移植。
更大的键码尺寸，更大的键码上限。
3.3、指定表类型
你可在创建表时指定表的类型。下例创建一个HEAP表：——————————————————————————–
mysql>CREATE TABLE email_addresses TYPE=HEAP (
->email char(55) NOT NULL,
->name char(30) NOT NULL,
->PRIMARY KEY(email) );
——————————————————————————–
BDB表需要一些配置工作，参见https://www.360docs.net/doc/f22620193.html,/doc/B/D/BDB_overview.html。
3.4、更多的表类型
为了使MySQL管理工作更有趣，即将发布的MySQL 4.0将提供两种新的表类型，称为Innobase和Gemeni。
4、优化工具
MySQL服务器本身提供了几条内置命令用于帮助优化。
4.1、SHOW
你可能有兴趣知道MySQL服务器究竟更了什么，下列命令给出一个总结：——————————————————————————–
mysql>show status;
——————————————————————————–
它给出了一个相当长的状态变量及其值的列表。有些变量包含了异常终止客户的数量、异常终止连接的数量、连接尝试的次数、最大并发连接数和大量其他有用的信息。这些信息对找出系统问题和低效极具价值。
SHOW还能做更多的事情。它可以显示关于日志文件、特定数据库、表、索引、进程和权限表中有价值的信息。详见MySQL手册。
4.2、EXPLAIN
当你面对SELECT语句时，EXPLAIN解释SELECT命令如何被处理。这不仅对决定是否应该增加一个索引，而且对决定一个复杂的Join如何被MySQL处理都是有帮助的。
4.3、OPTIMIZE
OPTIMIZE语句允许你恢复空间和合并数据文件碎片，对包含变长行的表进行了大量更新和删除后，这样做特别重要。OPTIMIZE目前只工作于MyIASM和BDB表。
介绍一下Mysql的存储引擎
存储引擎是什么？
MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。
例如，如果你在研究大量的临时数据，你也许需要使用内存存储引擎。内存存储引擎能够在内存中存储所有的表格数据。又或者，你也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力)。
这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)。MySQL
默认配置了许多不同的存储引擎，可以预先设置或者在MySQL服务器中启用。你可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。
选择如何存储和检索你的数据的这种灵活性是MySQL为什么如此受欢迎的主要原因。其它数据库系统(包括大多数商业选择)仅支持一种类型的数据存储。遗憾的是，其它类型的数据库解决方案采取的“一个尺码满足一切需求”的方式意味着你要么就牺牲一些性能，要么你就用几个小时甚至几天的时间详细调整你的数据库。使用MySQL，我们仅需要修改我们使用的存储引擎就可以了。
在这篇文章中，我们不准备集中讨论不同的存储引擎的技术方面的问题(尽管我们不可避免地要研究这些因素的某些方面)，相反，我们将集中介绍这些不同的引擎分别最适应哪种需求和如何启用不同的存储引擎。为了实现这个目的，在介绍每一个存储引擎的具体情况之前，我们必须要了解一些基本的问题。
如何确定有哪些存储引擎可用
你可以在MySQL(假设是MySQL服务器4.1.2以上版本)中使用显示引擎的命令得到一个可用引擎的列表。
16 rows in set (0.01 sec) 这个表格显示了可用的数据库引擎的全部名单以及在当前的数据库服务器中是否支持这些引擎。
对于MySQL 4.1.2以前版本，可以使用mysql> show variables like “have_%”(显示类似
如果你在使用一个预先包装好的MySQL二进制发布版软件，那么，这个软件就包含了常用的引擎。然而，需要指出的是，如果你要使用某些不常用的引擎，特别是CSV、RCHIVE(存档)和BLACKHOLE(黑洞)引擎，你就需要手工重新编译MySQL源码。
使用一个指定的存储引擎
你可以使用很多方法指定一个要使用的存储引擎。最简单的方法是，如果你喜欢一种能满足你的大多数数据库需求的存储引擎，你可以在MySQL设置文件中设置一个默认的引擎类型（使用storage_engine 选项）或者在启动数据库服务器时在命令行后面加上–default-storage-engine或–default-table-type选项。
更灵活的方式是在随MySQL服务器发布同时提供的MySQL客户端时指定使用的存储引擎。最直接的方式是在创建表时指定存储引擎的类型，向下面这样:
你还可以改变现有的表使用的存储引擎，用以下语句:
字段类型或者表大小的一个类型进行修改可能使你丢失数据。如果你指定一个在你的当前的数据库中不存在的一个存储引擎，那么就会创建一个MyISAM(默认的)类型的表。
各存储引擎之间的区别
为了做出选择哪一个存储引擎的决定，我们首先需要考虑每一个存储引擎提供了哪些不同的核心功能。这种功能使我们能够把不同的存储引擎区别开来。我们一般把这些核心功能分为四类:支持的字段和数据类型、锁定类型、索引和处理。一些引擎具有能过促使你做出决定的独特的功能，我们一会儿再仔细研究这些具体问题。
字段和数据类型
虽然所有这些引擎都支持通用的数据类型，例如整型、实型和字符型等，但是，并不是所有的引擎都支持其它的字段类型，特别是BLOG（二进制大对象）或者TEXT文本类型。其它引擎也许仅支持有限的字符宽度和数据大小。
这些局限性可能直接影响到你可以存储的数据，同时也可能会对你实施的搜索的类型或者你
对那些信息创建的索引产生间接的影响。这些区别能够影响你的应用程序的性能和功能，因为你必须要根据你要存储的数据类型选择对需要的存储引擎的功能做出决策。
锁定
数据库引擎中的锁定功能决定了如何管理信息的访问和更新。当数据库中的一个对象为信息更新锁定了，在更新完成之前，其它处理不能修改这个数据(在某些情况下还不允许读这种数据)。
锁定不仅影响许多不同的应用程序如何更新数据库中的信息，而且还影响对那个数据的查询。这是因为查询可能要访问正在被修改或者更新的数据。总的来说，这种延迟是很小的。大多数锁定机制主要是为了防止多个处理更新同一个数据。由于向数据中插入信息和更新信息这两种情况都需要锁定，你可以想象，多个应用程序使用同一个数据库可能会有很大的影响。
不同的存储引擎在不同的对象级别支持锁定，而且这些级别将影响可以同时访问的信息。得到支持的级别有三种:表锁定、块锁定和行锁定。支持最多的是表锁定，这种锁定是在MyISAM中提供的。在数据更新时，它锁定了整个表。这就防止了许多应用程序同时更新一个具体的表。这对应用很多的多用户数据库有很大的影响，因为它延迟了更新的过程。页级锁定使用Berkeley DB引擎，并且根据上载的信息页(8KB)锁定数据。当在数据库的很多地方进行更新的时候，这种锁定不会出现什么问题。但是，由于增加几行信息就要锁定数据结构的最后8KB，当需要增加大量的行，也别是大量的小型数据，就会带来问题。
行级锁定提供了最佳的并行访问功能，一个表中只有一行数据被锁定。这就意味着很多应用程序能够更新同一个表中的不同行的数据，而不会引起锁定的问题。只有InnoDB存储引擎支持行级锁定。
建立索引
建立索引在搜索和恢复数据库中的数据的时候能够显著提高性能。不同的存储引擎提供不同的制作索引的技术。有些技术也许会更适合你存储的数据类型。
有些存储引擎根本就不支持索引，其原因可能是它们使用基本表索引(如MERGE引擎)或者是因为数据存储的方式不允许索引(例如FEDERA TED或者BLACKHOLE引擎)。
事务处理
事务处理功能通过提供在向表中更新和插入信息期间的可靠性。这种可靠性是通过如下方法实现的，它允许你更新表中的数据，但仅当应用的应用程序的所有相关操作完全完成后才接受你对表的更改。例如，在会计处理中每一笔会计分录处理将包括对借方科目和贷方科目数据的更改，你需要要使用事务处理功能保证对借方科目和贷方科目的数据更改都顺利完成，才接受所做的修改。如果任一项操作失败了，你都可以取消这个事务处理，这些修改就不存在了。如果这个事务处理过程完成了，我们可以通过允许这个修改来确认这个操作。
如何写出高质量、高性能的MySQL查询
下面就某些SQL语句的where子句编写中需要注意的问题作详细介绍。在这些where子句中，即使某些列存在索引，但是由于编写了劣质的SQL，系统在运行该SQL语句时也不能使用该索引，而同样使用全表扫描，这就造成了响应速度的极大降低。
1. IS NULL 与IS NOT NULL
不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。
任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。
2. 联接列
对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。我们一起来看一个例子，假定有一个职工表（employee），对于一个职工的姓和名分成两列存放（FIRST_NAME和LAST_NAME），现在要查询一个叫比尔.克林顿（Bill Cliton）的职工。下面是一个采用联接查询的SQL语句，
上面这条语句完全可以查询出是否有Bill Cliton这个员工，但是这里需要注意，系统优化器对基于last_name创建的索引没有使用。
当采用下面这种SQL语句的编写，Oracle系统就可以采用基于last_name创建的索引。
遇到下面这种情况又如何处理呢？如果一个变量（name）中存放着Bill Cliton这个员工的姓名，对于这种情况我们又如何避免全程遍历，使用索引呢？可以使用一个函数，将变量name 中的姓和名分开就可以了，但是有一点需要注意，这个函数是不能作用在索引列上。下面
3. 带通配符（%）的like语句
同样以上面的例子来看这种情况。目前的需求是这样的，要求在职工表中查询名字中包含
这里由于通配符（%）在搜寻词首出现，所以Oracle系统不使用last_name的索引。在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了使用：
4. Order by语句
ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中（象联接或者附加等）。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。
仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。
5. NOT
我们在查询时经常在where子句使用一些逻辑表达式，如大于、小于、等于以及不等于等等，也可以使用and（与）、or（或）以及not（非）。NOT可用来对任何逻辑运算符号取反。下面是一个NOT子句的例子：