mysql必知必会题目_MySQL必知必会面试题（一）

最新推荐文章于 2022-10-08 00:05:20 发布

赵泠

最新推荐文章于 2022-10-08 00:05:20 发布

阅读量179

点赞数

文章标签： mysql必知必会题目

本文链接：https://blog.csdn.net/weixin_26843605/article/details/113232957

版权

转载于老男孩教育

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’;

5、查看创建的用户oldboy拥有哪些权限mysql> show grants for abc@localhost;

mysql> show grants for oldboy@localhost;

mysql> show grants for oldboy@localhost\G

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;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int(4) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

第二种方法：mysql> show columns from test;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int(4) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

查看表权限mysql> show create table test;

9、插入一条数据 1，oldboymysql> 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更改为oldgirlmysql> 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;

+-------+-------------+------+-----+---------+-------+| id | int(4) | YES | | NULL | |

| age | tinyint(2) | 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

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安装就会出现)

多实例：/data/3306/mysql shutdown或者是/data/3306/mysql stop 这个需要我们自己写脚本。官方有参考

2、cp /etc/my.cnf /etc/my.cnf.bak,修改前做备份，这是个好习惯。修改my.cnfvi /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

赵泠

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql必知必会题目_MySQL必知必会面试题（一）

转载于老男孩教育1、登录数据库(1)、单实例mysql-uroot-poldboy(2)、多实例mysql-uroot-poldboy-S/data/3306/mysql.sock2、查看数据库版本及当前登录用户是什么mysql>selectversion();查看版本mysql>selectuser();查看用户3、创建GBK字符集的数据库oldboy，并查看...
复制链接

扫一扫