运维必会MySQL 30道面试题

老男孩教育运维班全体学员MySQL必会企业面试题

mysql
2016年9月10日
  本文转载老男孩教育,未经本人同意禁止转载!
原文:http://user.qzone.qq.com/49000448/blog/1427333863


1.登陆数据库

(1)单实例

  1. mysql -uroot -poldboy

(2)多实例

  1. mysql -uroot -poldboy -S /data/3306/mysql.sock

2.查看数据库版本及当前登录用户是什么

  1. mysql> select version(); 查看版本
  2. mysql> select user(); 查看用户

3.创建GBK字符集的数据库oldboy,并查看已建库的完整语句

  1. mysql> create database oldboy character set gbk collate gbk_chinese_ci;
  2. mysql> show create database oldboy\G

4.创建用户oldboy,使之可以管理数据库oldboy

第一种方法:

  1. mysql> grant all on oldboy.* to oldboy@localhost identified by 123456’;
  2. mysql> select user,host from mysql.user;

第二种方法:

  1. mysql> grant all on oldboy.* to oldboy@192.168.1.%/255.255.255.0 identified by 123456’;
  2. mysql> select user,host from mysql.user;

第三种方法:

  1. mysql> create user abc’@’localhost identified by 123456’;
  2. mysql> grant all on oldboy.* to abc’@’localhost’;

6.查看当前数据库里有哪些用户。

  1. mysql> select user,host from mysql.user;

7.进入oldboy数据库

  1. mysql> use oldboy

8.创建一innodb引擎字符集为GBK表test,字段为id和namevarchar(16),查看建表结构及SQL语句

  1. mysql> create table test(
  2. -> id int(4),
  3. -> name varchar(16)
  4. -> )ENGINE=innodb default charset=gbk;
  5. Query OK, 0 rows affected (0.02 sec)

第一种方法:

  1. mysql> desc test;
  2. +-------+-------------+------+-----+---------+-------+
  3. | Field | Type | Null | Key | Default | Extra |
  4. +-------+-------------+------+-----+---------+-------+
  5. | id | int(4) | YES | | NULL | |
  6. | name | varchar(16) | YES | | NULL | |
  7. +-------+-------------+------+-----+---------+-------+
  8. 2 rows in set (0.00 sec)

第二种方法:

  1. mysql> show columns from test;
  2. +-------+-------------+------+-----+---------+-------+
  3. | Field | Type | Null | Key | Default | Extra |
  4. +-------+-------------+------+-----+---------+-------+
  5. | id | int(4) | YES | | NULL | |
  6. | name | varchar(16) | YES | | NULL | |
  7. +-------+-------------+------+-----+---------+-------+
  8. 2 rows in set (0.00 sec)

查看表权限

  1. mysql> show create table test;

9.插入一条数据 1,oldboy

  1. mysql> insert into test values(1,'oldboy');
  2. mysql> select * from test;
  3. +------+--------+
  4. | id | name |
  5. +------+--------+
  6. | 1 | oldboy |
  7. +------+--------+
  8. 1 row in set (0.00 sec)

10.批量插入数据 2,老男孩,3,etiantian。要求中文不能乱码

  1. mysql> insert into test values(2,'老男孩'),(3,'etiantian');
  2. Query OK, 2 rows affected (0.07 sec)
  3. Records: 2 Duplicates: 0 Warnings: 0
  4. 查看创建情况
  5. mysql> select * from test;
  6. +------+-----------+
  7. | id | name |
  8. +------+-----------+
  9. | 1 | oldboy |
  10. | 2 | 老男孩 |
  11. | 3 | etiantian |
  12. +------+-----------+
  13. 3 rows in set (0.00 sec)

11.查询插入的所有记录,查询名字为oldboy的记录。查询id大于1的记录。

(1)第一种方法

  1. mysql> select * from test;
  2. +------+-----------+
  3. | id | name |
  4. +------+-----------+
  5. | 1 | oldboy |
  6. | 2 | 老男孩 |
  7. | 3 | etiantian |
  8. +------+-----------+
  9. 3 rows in set (0.00 sec)

(2)第二种方法

  1. mysql> select * from test where name='oldboy';
  2. +------+--------+
  3. | id | name |
  4. +------+--------+
  5. | 1 | oldboy |
  6. +------+--------+
  7. 1 row in set (0.00 sec)

(3)第三种方法

  1. mysql> select * from test where id>1;
  2. +------+-----------+
  3. | id | name |
  4. +------+-----------+
  5. | 2 | 老男孩 |
  6. | 3 | etiantian |
  7. +------+-----------+
  8. 2 rows in set (0.00 sec)

12.把数据id等于1的名字oldboy更改为oldgirl

  1. mysql> update test set name='oldgirl' where id=1;
  2. Query OK, 1 row affected (0.00 sec)
  3. Rows matched: 1 Changed: 1 Warnings: 0

查看修改情况

  1. mysql> select * from test;
  2. +------+-----------+
  3. | id | name |
  4. +------+-----------+
  5. | 1 | oldgirl |
  6. | 2 | 老男孩 |
  7. | 3 | etiantian |
  8. +------+-----------+
  9. 3 rows in set (0.00 sec)

13.在字段name前插入age字段,类型tinyint(2)

  1. mysql> alter table test add age tinyint(2) after id;
  2. Query OK, 3 rows affected (0.04 sec)
  3. Records: 3 Duplicates: 0 Warnings: 0
  4. mysql> desc test;
  5. +-------+-------------+------+-----+---------+-------+
  6. | Field | Type | Null | Key | Default | Extra |
  7. +-------+-------------+------+-----+---------+-------+
  8. | id | int(4) | YES | | NULL | |
  9. | age | tinyint(2) | YES | | NULL | |
  10. | name | varchar(16) | YES | | NULL | |
  11. +-------+-------------+------+-----+---------+-------+
  12. 3 rows in set (0.00 sec)

14.备份oldboy库及MySQL库

  1. mysqldump -uroot -poldboy -S /data/3306/mysql.sock --events -B oldboy mysql >/opt/mysql_back.sql
  2. cat /opt/mysql_back.sql

15.删除表中的所有数据,并查看

  1. 第一种mysql> delete from test;
  2. 第二种mysql> truncate table test;
  3. mysql> select * from test;
  4. Empty set (0.00 sec)

16.删除表test和oldboy数据库并查看

  1. mysql> drop table test; 删除表
  2. mysql> drop database test; 删除库

17.Linux命令行恢复以上删除的数据

  1. mysql -uroot -poldboy -S /data/3306/mysql.sock </opt/mysql_back.sql

18.把GBK字符集修改为UTF8(可选,注意,此题有陷阱)

  1. mysql> show variables like 'character_set_%';
  2. +--------------------------+-------------------------------------------+
  3. | Variable_name | Value |
  4. +--------------------------+-------------------------------------------+
  5. | character_set_client | utf8 |
  6. | character_set_connection | utf8 |
  7. | character_set_database | utf8 |
  8. | character_set_filesystem | binary |
  9. | character_set_results | utf8 |
  10. | character_set_server | utf8 |
  11. | character_set_system | utf8 |
  12. | character_sets_dir | /application/mysql-5.5.49/share/charsets/ |
  13. 由配置文件设置
  14. +--------------------------+-------------------------------------------+
  15. 8 rows in set (0.00 sec)

解决方法思想:

1、停止MySQL,单例可以使用/etc/init.d/mysqld(编译的话需要设置,yum安装就会出现)             多实例:/data/3306/mysql shutdown或者是/data/3306/mysql stop 这个需要我们自己写脚本。官方有参考2、cp /etc/my.cnf /etc/my.cnf.bak,修改前做备份,这是个好习惯。       修改my.cnf       vi /etc/my.cnf       在[client]下添加,client为控制客户端的,没试过,没有的可以不需要加。       default-character-set=utf8       在[mysqld]下添加,mysqld为控制服务器端的,改过了,OK。     default-character-set=utf8 3.重启:yum安装可以使用/etc/init.d/mysqld start 多实例要使用/data/3306/mysql restart(多实例详细介绍见下一篇文章) 4.show variables like ‘%char%’;查看

19.MySQL密码丢了,如何找回实战?

单实例

  1. /etc/init.d/mysqld stop
  2. mysqld_safe --skip-grant-tables --user=mysql &
  3. mysql
  4. 修改完密码重启
  5. /etc/init.d/mysqld restart

多实例

  1. /data/3306/mysql stop 无法停止
  2. killall mysqld
  3. mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-tables --user=mysql &
  4. update mysql.user set password=password('oldboy456') where user='root' and host='localhost';
  5. flush privileges;
  6. mysql 登录
  7. mysqladmin -uroot -poldboy shutdown
  8. /etc/init.d/mysqld start

MySQL内中文数据乱码的原理及如何防止乱码?(可选)

  1. 1. 网站程序字符集
  2. 2. 客户端的字符集
  3. 3. 服务器端字符集
  4. 4. linux客户端字符集
  5. 5. 以上都要统一,否则会出现中文乱码
  6. 如果编译的时候指定了特定的字符集,则以后创建对应字符集的数据库就不需要指定字符集
  7. -DDEFAULT_CHARSET=utf8 \
  8. -DDEFAULT_COLLATION=utf8_general_ci \
  9. -DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \
  10. 提示:二进制软件包,安装的数据库字符集默认latinl

21.在把id列设置为主键,在Name字段上创建普通索引

  1. mysql> alter table test add primary key(id);
  2. mysql> desc test;
  3. +-------+-------------+------+-----+---------+-------+
  4. | Field | Type | Null | Key | Default | Extra |
  5. +-------+-------------+------+-----+---------+-------+
  6. | id | int(4) | NO | PRI | 0 | |
  7. | age | tinyint(2) | YES | | NULL | |
  8. | name | varchar(16) | YES | | NULL | |
  9. +-------+-------------+------+-----+---------+-------+
  10. 3 rows in set (0.00 sec)

添加索引步骤

  1. mysql> alter table test add index index_name(name);
  2. create index index_name on test(name);

22.在字段name后插入手机号字段(shouji),类型char(11)

  1. mysql> alter table test add shouji char(11) after name;
  2. Query OK, 3 rows affected (0.05 sec)
  3. Records: 3 Duplicates: 0 Warnings: 0
  4. mysql> desc test;
  5. +--------+-------------+------+-----+---------+-------+
  6. | Field | Type | Null | Key | Default | Extra |
  7. +--------+-------------+------+-----+---------+-------+
  8. | id | int(4) | NO | PRI | 0 | |
  9. | age | tinyint(2) | YES | | NULL | |
  10. | name | varchar(16) | YES | MUL | NULL | |
  11. | shouji | char(11) | YES | | NULL | |
  12. +--------+-------------+------+-----+---------+-------+
  13. 4 rows in set (0.00 sec)

23.所有字段上插入2条记录(自行设定数据)

  1. mysql> insert into test values(4,24,'cyh','604419314'),(5,38,'oldboy','123456');
  2. Query OK, 2 rows affected (0.00 sec)
  3. Records: 2 Duplicates: 0 Warnings: 0
  4. mysql> select * from test;
  5. +----+------+-----------+-----------+
  6. | id | age | name | shouji |
  7. +----+------+-----------+-----------+
  8. | 1 | NULL | oldgirl | NULL |
  9. | 2 | NULL | 老男孩 | NULL |
  10. | 3 | NULL | etiantian | NULL |
  11. | 4 | 24 | cyh | 604419314 |
  12. | 5 | 38 | oldboy | 123456 |
  13. +----+------+-----------+-----------+
  14. 5 rows in set (0.00 sec)

24.在手机字段上对前8个字符创建普通索引

  1. mysql> alter table test add index index_shouji(shouji(8));
  2. Query OK, 0 rows affected (0.03 sec)
  3. Records: 0 Duplicates: 0 Warnings: 0
  4. mysql> select * from test;
  5. +----+------+-----------+-----------+
  6. | id | age | name | shouji |
  7. +----+------+-----------+-----------+
  8. | 1 | NULL | oldgirl | NULL |
  9. | 2 | NULL | 老男孩 | NULL |
  10. | 3 | NULL | etiantian | NULL |
  11. | 4 | 24 | cyh | 604419314 |
  12. | 5 | 38 | oldboy | 123456 |
  13. +----+------+-----------+-----------+
  14. 5 rows in set (0.00 sec)

25.查看创建的索引及索引类型等信息

  1. mysql> show index from test\G
  2. *************************** 1. row ***************************
  3. Table: test
  4. Non_unique: 0
  5. Key_name: PRIMARY
  6. Seq_in_index: 1
  7. Column_name: id
  8. Collation: A
  9. Cardinality: 5
  10. Sub_part: NULL
  11. Packed: NULL
  12. Null:
  13. Index_type: BTREE
  14. Comment:
  15. Index_comment:
  16. *************************** 2. row ***************************
  17. Table: test
  18. Non_unique: 1
  19. Key_name: index_name
  20. Seq_in_index: 1
  21. Column_name: name
  22. Collation: A
  23. Cardinality: 5
  24. Sub_part: NULL
  25. Packed: NULL
  26. Null: YES
  27. Index_type: BTREE
  28. Comment:
  29. Index_comment:
  30. *************************** 3. row ***************************
  31. Table: test
  32. Non_unique: 1
  33. Key_name: index_shouji
  34. Seq_in_index: 1
  35. Column_name: shouji
  36. Collation: A
  37. Cardinality: 5
  38. Sub_part: 8
  39. Packed: NULL
  40. Null: YES
  41. Index_type: BTREE
  42. Comment:
  43. Index_comment:
  44. 3 rows in set (0.00 sec)

26.删除Name,shouji列的索引

  1. alter table test drop index index_name;
  2. drop index index_shouji on test;
  3. ysql> drop index index_shouji on test;
  4. Query OK, 0 rows affected (0.01 sec)
  5. Records: 0 Duplicates: 0 Warnings: 0
  6. mysql> show index from test\G
  7. *************************** 1. row ***************************
  8. Table: test
  9. Non_unique: 0
  10. Key_name: PRIMARY
  11. Seq_in_index: 1
  12. Column_name: id
  13. Collation: A
  14. Cardinality: 5
  15. Sub_part: NULL
  16. Packed: NULL
  17. Null:
  18. Index_type: BTREE
  19. Comment:
  20. Index_comment:
  21. 1 row in set (0.00 sec

27.对Name列的前6个字符以及手机列的前8个字符组建联合索引

  1. mysql> alter table test add index index_name_shouji(name(6),shouji(8));
  2. Query OK, 0 rows affected (0.02 sec)
  3. Records: 0 Duplicates: 0 Warnings: 0
  4. mysql> show index from test\G
  5. *************************** 1. row ***************************
  6. Table: test
  7. Non_unique: 0
  8. Key_name: PRIMARY
  9. Seq_in_index: 1
  10. Column_name: id
  11. Collation: A
  12. Cardinality: 5
  13. Sub_part: NULL
  14. Packed: NULL
  15. Null:
  16. Index_type: BTREE
  17. Comment:
  18. Index_comment:
  19. *************************** 2. row ***************************
  20. Table: test
  21. Non_unique: 1
  22. Key_name: index_name_shouji
  23. Seq_in_index: 1
  24. Column_name: name
  25. Collation: A
  26. Cardinality: 5
  27. Sub_part: 6
  28. Packed: NULL
  29. Null: YES
  30. Index_type: BTREE
  31. Comment:
  32. Index_comment:
  33. *************************** 3. row ***************************
  34. Table: test
  35. Non_unique: 1
  36. Key_name: index_name_shouji
  37. Seq_in_index: 2
  38. Column_name: shouji
  39. Collation: A
  40. Cardinality: 5
  41. Sub_part: 8
  42. Packed: NULL
  43. Null: YES
  44. Index_type: BTREE
  45. Comment:
  46. Index_comment:
  47. 3 rows in set (0.00 sec)

28.查询手机号以135开头的,名字为oldboy的记录(此记录要提前插入)

  1. mysql> select * from test where name='cyh' and shouji like '6044%';
  2. +----+------+------+-----------+
  3. | id | age | name | shouji |
  4. +----+------+------+-----------+
  5. | 4 | 24 | cyh | 604419314 |
  6. +----+------+------+-----------+
  7. 1 row in set (0.00 sec)

29.查询上述语句的执行计划(是否使用联合索引等)

  1. mysql> explain select * from test where name='cyh' and shouji like '6044%'\G
  2. *************************** 1. row ***************************
  3. id: 1
  4. select_type: SIMPLE
  5. table: test
  6. type: range
  7. possible_keys: index_name_shouji
  8. key: index_name_shouji
  9. key_len: 32
  10. ref: NULL
  11. rows: 1
  12. Extra: Using where
  13. 1 row in set (0.00 sec)

30.把test表的引擎改成MyISAM

  1. mysql> SHOW CREATE TABLE TEST\G
  2. *************************** 1. row ***************************
  3. Table: TEST
  4. Create Table: CREATE TABLE `test` (
  5. `id` int(4) NOT NULL DEFAULT '0',
  6. `age` tinyint(2) DEFAULT NULL,
  7. `name` varchar(16) DEFAULT NULL,
  8. `shouji` char(11) DEFAULT NULL,
  9. PRIMARY KEY (`id`),
  10. KEY `index_name_shouji` (`name`(6),`shouji`(8))
  11. ) ENGINE=InnoDB DEFAULT CHARSET=gbk
  12. 1 row in set (0.00 sec)
  13. 修改后====================================
  14. mysql> alter table test ENGINE=MYISAM;
  15. Query OK, 5 rows affected (0.03 sec)
  16. Records: 5 Duplicates: 0 Warnings: 0
  17. mysql> SHOW CREATE TABLE TEST\G
  18. *************************** 1. row ***************************
  19. Table: TEST
  20. Create Table: CREATE TABLE `test` (
  21. `id` int(4) NOT NULL DEFAULT '0',
  22. `age` tinyint(2) DEFAULT NULL,
  23. `name` varchar(16) DEFAULT NULL,
  24. `shouji` char(11) DEFAULT NULL,
  25. PRIMARY KEY (`id`),
  26. KEY `index_name_shouji` (`name`(6),`shouji`(8))
  27. ) ENGINE=MyISAM DEFAULT CHARSET=gbk
  28. 1 row in set (0.00 sec)


更多地址:http://user.qzone.qq.com/49000448?ptlang=2052
更多文章请关注:老男孩


历史上的今天:

  1. 2018:  Docker 导出多个镜像合并成一个tar(0)
  2. 2018:  Kubernetes kubectl 命令自动补全(0)
  3. 2017:  ZABBIX 忘记登录密码(1)
  4. 2017:  ZABBIX 3.2 监控服务器TCP连接状态(2)
    A+
发布日期:2016年07月27日  所属分类:MySQL
标签:
新闻联播老司机

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

目前评论:1   其中:访客  1   博主  0

  1. avatar 演员网 2
    无法停止怎么班