MySql数据库面试题

1 用select语句找到当前服务器版本，当前数据库

mysql> SELECT VERSION(), DATABASE();

+-------------------------+------------+
| VERSION()               | DATABASE() |
+-------------------------+------------+
| 5.5.34-0ubuntu0.13.10.1 | NULL       |
+-------------------------+------------+
1 row in set (0.06 sec)

mysql> use Tecmint;
mysql> select VERSION(), DATABASE();

+-------------------------+------------+
| VERSION()               | DATABASE() |
+-------------------------+------------+
| 5.5.34-0ubuntu0.13.10.1 | tecmint    |
+-------------------------+------------+

2 使用not操作符（！），显示tecmint表，除用户sam的所有用户

mysql> SELECT * FROM Tecmint WHERE user !=SAM;

+---------------------+---------+---------+---------+---------+-------+ 
| date                | user    | host 	 | root     | local   | size  | 
+---------------------+---------+---------+---------+---------+-------+ 
| 2001-05-14 14:42:21 | Anthony | venus  | barb     | venus   | 98151 | 
| 2001-05-15 08:50:57 | TIM     | venus  | phil     | venus   | 978   | 
+---------------------+---------+---------+---------+---------+-------+

3 请简要说明ifnull()用法
ifnull 用来检查返回列的值（第一个参数），是否存在，如果存在返回，不存在返回第二个参数设定的值

mysql> SELECT name, IFNULL(id,'Unknown') AS 'id' FROM taxpayer;

+---------+---------+ 
| name 	  | id      | 
+---------+---------+ 
| bernina | 198-48  | 
| bertha  | Unknown | 
| ben     | Unknown | 
| bill    | 475-83  | 
+---------+---------+

4 返回指定的行数，从结果集的开始或末尾

mysql> SELECT * FROM profile LIMIT 5;

+----+------+------------+-------+-----------------------+------+ 
| id | name | birth      | color | foods                 | cats | 
+----+------+------------+-------+-----------------------+------+ 
| 1  | Fred | 1970-04-13 | black | lutefisk,fadge,pizza  | 0    | 
| 2  | Mort | 1969-09-30 | white | burrito,curry,eggroll | 3    | 
| 3  | Brit | 1957-12-01 | red   | burrito,curry,pizza   | 1    |   
| 4  | Carl | 1973-11-02 | red   | eggroll,pizza         | 4    | 
| 5  | Sean | 1963-07-04 | blue  | burrito,curry         | 5    | 
+----+------+------------+-------+-----------------------+------+

mysql> SELECT * FROM profile order by bither LIMIT 1;
+----+------+------------+-------+----------------+------+ 
| id | name | birth      | color | foods          | cats | 
+----+------+------------+-------+----------------+------+ 
| 9  | Dick | 1952-08-20 | green | lutefisk,fadge | 0    | 
+----+------+------------+-------+----------------+------+

5 oracle 和 mysql哪个更好一点，如何选
各有有点。但我喜欢mysql
mysql
1开源 2 可移动端运用 3支持gui和command 4支持querybrowse

6 获取mysql日期

mysql> SELECT CURRENT_DATE();

+----------------+
| CURRENT_DATE() |
+----------------+
| 2014-06-17     |
+----------------+

7 用mysql将指定表导出xml文件

mysql -u USER_NAME –xml -e 'SELECT * FROM table_name' > table_name.xml

8 简介MySQL_pconnect，他与MySQL_connect有什么不同
MySQL_pconnect是建立数据库连接并保持持续性，不能用mysql_close关闭，mysql_connect没有这个特性，所以可以用MySQL_close()关闭

9 检索一个user表的所有索引

mysql> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | 
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 
| user  |          0 | PRIMARY  |            1 | Host        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               | 
| user  |          0 | PRIMARY  |            2 | User        | A         |           4 |     NULL | NULL   |      | BTREE      |         |               | 
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 
2 rows in set (0.00 sec)

10 什么是csv表
csv是一个标准。通常使用逗号分割字段，用文本存储数据。通常一行一个记录。除了用逗号，也可以用其他指定符号分割字段。

11 mysql是否运行，并确认状态,如何启动并停止

# service mysql status | start | stop
# sytemctl status | start | stop  mysql

12 linuxshell 登录mysql

# mysql -u root -p 

13 检查服务器中的数据库，切换数据库， 显示数据库的所有表

mysql> show databases;
mysql> use cloud; 
mysql> show tables; 

14 查看某个数据表的结构

mysql> describe table1; 

15 删除数据库，删除数据表

mysql> drop database db1; 
mysql> drop table tb1; 

16 获的表的所有记录总数

mysql> select count(*) from Tables;