• 15个 MySQL 基础面试题，DBA 们准备好了吗？

    此前我们已经有发表过Linux 面试基础问答之一、二和三共3篇文章，获得读者的好评，同时我们得到反馈，有些读者希望这种交互式学习方法能够做得更加灵活。心动不如行动，我们这就为您奉上 15个 MySQL 面试题

    问题1：你如何确定 MySQL 是否处于运行状态？

    答案： Debian 上运行命令 service mysql status，在RedHat 上运行命令 service mysqld status。然后看看输出即可。

    

    1. root@localhost:/home/avi# service mysql status
      2. 

      3. 

    3. /usr/bin/mysqladmin  Ver 8.42 Distrib 5.1.72, for debian-linux-gnu on i486
      4. 

    4. Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
      5. 

      6. 

    6. Oracle is a registered trademark of Oracle Corporation and/or its
      7. 

    7. affiliates. Other names may be trademarks of their respective
      8. 

    8. owners.
      9. 

      10. 

    10. Server version 5.1.72-2
      11. 

    11. Protocol version 10
      12. 

    12. Connection Localhost via UNIX socket
      13. 

    13. UNIX socket /var/run/mysqld/mysqld.sock
      14. 

    14. Uptime: 1 hour 22 min 49 sec
      15. 

      16. 

    16. Threads: 1  Questions: 112138  Slow queries: 1  Opens: 1485  Flush tables: 1  Open tables: 64  Queries per second avg: 22.567.
      17. 

    

    问题2：如何开启或停止 MySQL 服务？
    

    

    答案：运行命令 service mysqld start 开启服务；运行命令 service mysqld stop 停止服务。
    

    

    

    1. root@localhost:/home/avi# service mysql stop
      2. 

      3. 

    3. Stopping MySQL database server: mysqld.
      4. 

      5. 

    5. root@localhost:/home/avi# service mysql start
      6. 

      7. 

    7. Starting MySQL database server: mysqld.
      8. 

      9. 

    9. Checking for corrupt, not cleanly closed and upgrade needing tables..
      10. 

    

    问题3：如何通过 Shell 登入 MySQL？
    

    

    答案：运行命令 mysql -u root -p
    

    

    

    1. root@localhost:/home/avi# mysql -u root -p 
      2. 

    2. Enter password: 
      3. 

    3. Welcome to the MySQL monitor.  Commands end with ; or g. 
      4. 

    4. Your MySQL connection id is 207 
      5. 

    5. Server version: 5.1.72-2 (Debian) 
      6. 

      7. 

    7. Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. 
      8. 

      9. 

    9. Oracle is a registered trademark of Oracle Corporation and/or its 
      10. 

    10. affiliates. Other names may be trademarks of their respective 
      11. 

    11. owners. 
      12. 

      13. 

    13. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. 
      14. 

      15. 

    15. mysql>
      16. 

    

    问题4：如何列出所有数据库？
    

    

    答案：运行命令 show databases;
    

    

    

    1. mysql> show databases; 
      2. 

    2. +--------------------+ 
      3. 

    3. | Database           | 
      4. 

    4. +--------------------+ 
      5. 

    5. | information_schema | 
      6. 

    6. | a1                 | 
      7. 

    7. | cloud              | 
      8. 

    8. | mysql              | 
      9. 

    9. | phpmyadmin         | 
      10. 

    10. | playsms            | 
      11. 

    11. | sisso              | 
      12. 

    12. | test               | 
      13. 

    13. | ukolovnik          | 
      14. 

    14. | wordpress          | 
      15. 

    15. +--------------------+ 
      16. 

    16. 10 rows in set (0.14 sec)
      17. 

    

    问题5： 如何切换到某个数据库并在上面工作？
    

    

    答案：运行命令 use database_name; 进入名为 database_name 的数据库。
    

    

    

    1. mysql> use cloud; 
      2. 

    2. Reading table information for completion of table and column names 
      3. 

    3. You can turn off this feature to get a quicker startup with -A 
      4. 

      5. 

    5. Database changed 
      6. 

    6. mysql>
      7. 

    

    问题6：如何列出某个数据库内所有表？
    

    

    答案：在当前数据库运行命令 show tables;
    

    

    

    1. mysql> show tables; 
      2. 

    2. +----------------------------+ 
      3. 

    3. | Tables_in_cloud            | 
      4. 

    4. +----------------------------+ 
      5. 

    5. | oc_appconfig               | 
      6. 

    6. | oc_calendar_calendars      | 
      7. 

    7. | oc_calendar_objects        | 
      8. 

    8. | oc_calendar_repeat         | 
      9. 

    9. | oc_calendar_share_calendar | 
      10. 

    10. | oc_calendar_share_event    | 
      11. 

    11. | oc_contacts_addressbooks   | 
      12. 

    12. | oc_contacts_cards          | 
      13. 

    13. | oc_fscache                 | 
      14. 

    14. | oc_gallery_sharing         | 
      15. 

    15. +----------------------------+ 
      16. 

    16. 10 rows in set (0.00 sec)
      17. 

    

    问题7：如何获取表内所有 Field 对象的名称和类型？
    

    

    答案：运行命令 describe table_name;
    

    

    

    1. mysql> describe oc_users; 
      2. 

    2. +----------+--------------+------+-----+---------+-------+ 
      3. 

    3. | Field    | Type         | Null | Key | Default | Extra | 
      4. 

    4. +----------+--------------+------+-----+---------+-------+ 
      5. 

    5. | uid      | varchar(64)  | NO   | PRI |         |       | 
      6. 

    6. | password | varchar(255) | NO   |     |         |       | 
      7. 

    7. +----------+--------------+------+-----+---------+-------+ 
      8. 

    8. 2 rows in set (0.00 sec)
      9. 

    

    问题8：如何删除表？
    

    

    答案：运行命令 drop table table_name;
    

    

    

    1. mysql> drop table lookup; 
      2. 

      3. 

    3. Query OK, 0 rows affected (0.00 sec)
      4. 

    

    问题9：如何删除数据库？
    

    

    答案：运行命令 drop database database-name;
    

    

    

    1. mysql> drop database a1; 
      2. 

      3. 

    3. Query OK, 11 rows affected (0.07 sec)
      4. 

    

    问题10：如何查看表内所有数据？
    

    

    答案：运行命令 select * from table_name;
    

    

    

    1. mysql> select * from engines; 
      2. 

    2. +------------+---------+----------------------------------------------------------------+--------------+------+------------+ 
      3. 

    3. | ENGINE     | SUPPORT | COMMENT                                                        | TRANSACTIONS | XA   | SAVEPOINTS | 
      4. 

    4. +------------+---------+----------------------------------------------------------------+--------------+------+------------+ 
      5. 

    5. | InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        | 
      6. 

    6. | MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         | 
      7. 

    7. | BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         | 
      8. 

    8. | CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         | 
      9. 

    9. | MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         | 
      10. 

    10. | FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       | 
      11. 

    11. | ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         | 
      12. 

    12. | MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         | 
      13. 

    13. +------------+---------+----------------------------------------------------------------+--------------+------+------------+ 
      14. 

    14. 8 rows in set (0.00 sec)
      15. 

    

    问题11：如何从表（比如 oc_users ）中获取一个 field 对象（比如 uid）的所有数据？
    

    

    答案：运行命令 select uid from oc_users;
    

    

    

    1. mysql> select uid from oc_users; 
      2. 

    2. +-----+ 
      3. 

    3. | uid | 
      4. 

    4. +-----+ 
      5. 

    5. | avi | 
      6. 

    6. +-----+ 
      7. 

    7. 1 row in set (0.03 sec)
      8. 

    问题12：假设你有一个名为 ‘xyz’ 的表，它存在多个字段，如 ‘createtime’ 和 ‘engine’。名为 engine 的字段由 ‘Memoty’ 和 ‘MyIsam’ 两种数值组成。如何只列出 ‘createtime’ 和 ‘engine’ 这两列并且 engine 的值为 ‘MyIsam’？
    

    

    答案：运行命令 select create_time, engine from xyz where engine = ”MyIsam”;
    

    

    

    1. mysql> select create_time, engine from xyz where engine="MyIsam";
      2. 

      3. 

    3. +---------------------+--------+ 
      4. 

    4. | create_time         | engine | 
      5. 

    5. +---------------------+--------+ 
      6. 

    6. | 2013-12-15 13:43:27 | MyISAM | 
      7. 

    7. | 2013-12-15 13:43:27 | MyISAM | 
      8. 

    8. | 2013-12-15 13:43:27 | MyISAM | 
      9. 

    9. | 2013-12-15 13:43:27 | MyISAM | 
      10. 

    10. | 2013-12-15 13:43:27 | MyISAM | 
      11. 

    11. | 2013-12-15 13:43:27 | MyISAM | 
      12. 

    12. | 2013-12-15 13:43:27 | MyISAM | 
      13. 

    13. | 2013-12-15 13:43:27 | MyISAM | 
      14. 

    14. | 2013-10-23 14:56:38 | MyISAM | 
      15. 

    15. | 2013-10-23 14:56:38 | MyISAM | 
      16. 

    16. | 2013-10-23 14:56:38 | MyISAM | 
      17. 

    17. | 2013-10-23 14:56:38 | MyISAM | 
      18. 

    18. | 2013-10-23 14:56:38 | MyISAM | 
      19. 

    19. | 2013-10-23 14:56:38 | MyISAM | 
      20. 

    20. | 2013-10-23 14:56:38 | MyISAM | 
      21. 

    21. +---------------------+--------+ 
      22. 

    22. 132 rows in set (0.29 sec)
      23. 

    问题13：如何列出表 ‘xrt’ 内 name 域值为 ‘tecmint’，web_address 域值为 ‘tecmint.com’ 的所有数据？
    

    

    答案：运行命令 select * from xrt where name = “tecmint” and web_address = “tecmint.com”;
    

    

    

    1. mysql> select  * from xrt where name = "tecmint" and web_address = “tecmint.com”;
      2. 

    2. +---------------+---------------------+---------------+ 
      3. 

    3. | Id                  | name                   | web_address | 
      4. 

    4. +---------------+---------------------+----------------+ 
      5. 

    5. | 13                 |  tecmint               | tecmint.com  |
      6. 

    6. +---------------+---------------------+----------------+ 
      7. 

    7. | 41                 |  tecmint               | tecmint.com  |
      8. 

    8. +---------------+---------------------+----------------+
      9. 

    问题14：如何列出表 ‘xrt’ 内 name 域值不为 ‘tecmint’，web_address 域值为 ‘tecmint.com’ 的所有数据？
    

    

    答案：运行命令 select * from xrt where name != "tecmint" and web_address = "tecmint.com";
    

    

    

    1. mysql> select * from xrt where name != ”tecmint” and web_address = ”tecmint.com”;
      2. 

      3. 

    3. +---------------+---------------------+---------------+ 
      4. 

    4. | Id            | name                | web_address   | 
      5. 

    5. +---------------+---------------------+----------------+ 
      6. 

    6. | 1173          |  tecmint            | tecmint.com   |
      7. 

    7. +---------------+---------------------+----------------+
      8. 

    

    问题15：如何知道表内行数？
    

    

    答案：运行命令 select count(*) from table_name;
    

    

    

    1. mysql> select count(*) from Tables; 
      2. 

      3. 

    3. +----------+ 
      4. 

    4. | count(*) | 
      5. 

    5. +----------+ 
      6. 

    6. |      282 | 
      7. 

    7. +----------+ 
      8. 

    8. 1 row in set (0.01 sec)
      9. 

    以上是文章的全部内容。这篇‘Linux 面试题’对您有任何帮助吗？别忘了在下面留言，写出您的宝贵意见。
    

    原文发布时间为：2014-01-05
    

    本文来自云栖社区合作伙伴“Linux中国”
    

        
        
                
    
                                        

    
    
          
    • 

          
  • 

            相关阅读:
    
            
              跃迁方法论 Continuous practice
                
    EPI online zoom session 面试算法基础知识直播分享
                
    台州 OJ 2648 小希的迷宫
                
    洛谷 P1074 靶形数独
                
    洛谷 P1433 DP 状态压缩
                
    台州 OJ FatMouse and Cheese 深搜 记忆化搜索
                
    台州 OJ 2676 Tree of Tree 树状 DP
                
    台州 OJ 2537 Charlie's Change 多重背包 二进制优化 路径记录
                
    台州 OJ 2378 Tug of War
                
    台州 OJ 2850 Key Task  BFS
                
                    
          
    • 
          
  • 
            原文地址：https://www.cnblogs.com/twodog/p/12140712.html
          
    • 

        

      



      
      

        

        

      

    

  


  

  
Copyright © 2020-2023 
  润新知