MySQL-30道面试题

1.登陆数据库

(1)单实例

mysql -uroot -poldboy

(2)多实例

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

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

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

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

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

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

第一种方法：

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

第二种方法：

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

第三种方法：

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

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

mysql> select user,host from mysql.user;

7.进入oldboy数据库

mysql> use oldboy

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

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

第一种方法：

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

第二种方法：

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

查看表权限

mysql> show create table test;

9.插入一条数据 1,oldboy

mysql> insert into test values(1,'oldboy');
mysql> select * from test;
+------+--------+
| id | name |
+------+--------+
| 1 | oldboy |
+------+--------+
1 row in set (0.00 sec)

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

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

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

（１）第一种方法

mysql> select * from test;
+------+-----------+
| id | name |
+------+-----------+
| 1 | oldboy |
| 2 | 老男孩 |
| 3 | etiantian |
+------+-----------+
3 rows in set (0.00 sec)

（２）第二种方法

mysql> select * from test where name='oldboy';
+------+--------+
| id | name |
+------+--------+
| 1 | oldboy |
+------+--------+
1 row in set (0.00 sec)

（３）第三种方法

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

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

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

查看修改情况

mysql> select * from test;
+------+-----------+
| id | name |
+------+-----------+
| 1 | oldgirl |
| 2 | 老男孩 |
| 3 | etiantian |
+------+-----------+
3 rows in set (0.00 sec)

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

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

14.备份oldboy库及MySQL库

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

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

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

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

mysql> drop table test; 删除表
mysql> drop database test; 删除库

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

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

18.把GBK字符集修改为UTF8（可选，注意，此题有陷阱）

mysql> show variables like 'character_set_%';
+--------------------------+-------------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /application/mysql-5.5.49/share/charsets/ |
由配置文件设置
+--------------------------+-------------------------------------------+
8 rows in set (0.00 sec)

解决方法思想：

1、停止MySQL，单例可以使用/etc/init.d/mysqld（编译的话需要设置，yum安装就会出现）             
2、多实例：/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密码丢了，如何找回实战？

单实例

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

多实例

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

MySQL内中文数据乱码的原理及如何防止乱码？（可选）

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

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

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

添加索引步骤

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

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

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

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

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

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

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

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

mysql> show index from test\G
*************************** 1. row ***************************
Table: test
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: test
Non_unique: 1
Key_name: index_name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: test
Non_unique: 1
Key_name: index_shouji
Seq_in_index: 1
Column_name: shouji
Collation: A
Cardinality: 5
Sub_part: 8
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
3 rows in set (0.00 sec)

26.删除Name，shouji列的索引

alter table test drop index index_name;
drop index index_shouji on test;
ysql> drop index index_shouji on test;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from test\G
*************************** 1. row ***************************
Table: test
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec

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

mysql> alter table test add index index_name_shouji(name(6),shouji(8));
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from test\G
*************************** 1. row ***************************
Table: test
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: test
Non_unique: 1
Key_name: index_name_shouji
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 5
Sub_part: 6
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: test
Non_unique: 1
Key_name: index_name_shouji
Seq_in_index: 2
Column_name: shouji
Collation: A
Cardinality: 5
Sub_part: 8
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
3 rows in set (0.00 sec)

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

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

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

mysql> explain select * from test where name='cyh' and shouji like '6044%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
type: range
possible_keys: index_name_shouji
key: index_name_shouji
key_len: 32
ref: NULL
rows: 1
Extra: Using where
1 row in set (0.00 sec)

30.把test表的引擎改成MyISAM

mysql> SHOW CREATE TABLE TEST\G
*************************** 1. row ***************************
Table: TEST
Create Table: CREATE TABLE `test` (
`id` int(4) NOT NULL DEFAULT '0',
`age` tinyint(2) DEFAULT NULL,
`name` varchar(16) DEFAULT NULL,
`shouji` char(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_name_shouji` (`name`(6),`shouji`(8))
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
修改后====================================
mysql> alter table test ENGINE=MYISAM;
Query OK, 5 rows affected (0.03 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE TEST\G
*************************** 1. row ***************************
Table: TEST
Create Table: CREATE TABLE `test` (
`id` int(4) NOT NULL DEFAULT '0',
`age` tinyint(2) DEFAULT NULL,
`name` varchar(16) DEFAULT NULL,
`shouji` char(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_name_shouji` (`name`(6),`shouji`(8))
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

posted @ 2017-12-17 18:38 活的潇洒80 阅读(164) 评论(0) 编辑收藏举报

刷新页面返回顶部

运维人生

MySQL-30道面试题

1.登陆数据库

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

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

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

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

7.进入oldboy数据库

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

9.插入一条数据 1,oldboy

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

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

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

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

14.备份oldboy库及MySQL库

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

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

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

18.把GBK字符集修改为UTF8（可选，注意，此题有陷阱）

19.MySQL密码丢了，如何找回实战？

MySQL内中文数据乱码的原理及如何防止乱码？（可选）

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

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

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

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

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

26.删除Name，shouji列的索引

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

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

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

30.把test表的引擎改成MyISAM

公告