MySql笔试题
数据库MySql基础篇
1.函数的分类？经常使用的函数有哪些？
lower
upper
substr
length
trim(去首尾空格，不会去除中间的空格)
str_to_date(%Y-%m-%d)
date_format
format(保留小数)round
rand()随机数
ifnull（如果为空，则替换为0）
聚合函数/分组函数
分组函数自动忽略空值
count
sum
avg
min
max
2.分组查询需要注意条件？
如果使用了order by，order by 必须放到group by后面。
在sql语句中，select语句后面只能跟分组函数+参与分组的字段。
如果想要对分组数据再进行过滤需要使用having子句。
- limit使用方法？
select * from emp limit m,n;
4.mysql常见数据类型？
char:定长字符串，适合做主键或者外键
varchar：可变长字符串
double/float
int/bigint
date
- 如何增加删除修改表结构
alter table 表名 add 字段名 数据类型(长度) --添加字段
alter table 表名 modify 字段名 数据类型(长度) --修改字段长度
alter table 表名 change 原字段名 现在字段名 数据类型(长度) --修改字段名称
alter table 表名 drop 字段名 --删除字段
6.如何开启MySQL服务,关闭My服务
开启服务：
service mysqld start
/init.d/mysqld start
safe_mysql &
关闭服务：
service mysqld stop
/etc/init.d/mysqld stop
mysqladmin -uroot -p123456 shutdown
7.检测端口是否运行
lsof -i:3306
netstat -tunlp|grep 3306
ss -tulnp|grep 3306
8.如何为MySQL设置密码或者修改密码。
方法一
mysqladmin -u root -p123456 password 'abc123' #比较常用
方法二（sql语句修改）
update mysql.user set password=password(123456) where user='root' and
host='localhost';
flush privileges;
方法三（sql语句修改）
set password=password('abc123');
9.如何登陆MySQL数据库。
单实例登陆
mysql -uroot -p123456
多实例登陆
mysql -uroot -p123456 -S /data/3306/mysql.sock
10.查看当前数据库的字符集
mysql> show variables like "%charac%";
11.如何查看当前数据库版本
# mysql -V
mysql> select version();
12.如何 查看当前登录的用户。
mysql> select user();
13.查看T1数据库中有哪儿些表
mysql> use T1;
mysql> show tables;
14.创建GBK字符集的数据库oldboy，并查看已建库完整语句
mysql> create database oldboy default character set gbk;
mysql> show create database oldboy;
15.创建用户oldboy，使之可以管理数据库oldboy
mysql> grant select,update,insert,delete,alter on oldboy.* to oldboy@'localhost'
identified by '123456';
16.查看创建的用户oldboy拥有哪些权限
mysql> show grants for oldboy@'localhost';
17.查看当前数据库里有哪些用户
mysql> select user,host from mysql.user;
18.如何进入oldboy数据库
mysql> use oldboy();
19.请写一个脚本： 创建一个innodb GBK表test，字段id int(4)和name varchar(16)
mysql> create table test (id int(4),name varchar(16)) engine=InnoDB default
charset=gbk;
20.查看建表结构及表结构的SQL语句
mysql> desc test;
mysql> show create table test\G
21.请使用脚本查询一条数据：插入一条数据“1,oldboy”
mysql> insert into test (id,name) values (1,'oldboy');
22.再批量插入2行数据 “2,老男孩”，“3,oldboyedu”
mysql> insert into test (id,name) values (2,'老男孩'),(3,'oldboyedu');
23.查询名字为oldboy的记录
mysql> select * from test where name='oldboy';
24.把数据id等于1的名字oldboy更改为oldgirl
mysql> update test set name='oldgirl' where id=1;
25.在字段name前插入age字段，类型tinyint(2)
mysql> alter table test add age tinyint(2) after id;
26.不退出数据库,完成备份oldboy数据库
mysql> system mysqldump -uroot -p123456 -B -x -F --events oldboy >/opt/bak.sql
27.删除test表中的所有数据，并查看
mysql> delete from test;
数据库MySql篇
1.说一下 MySQL 的行锁和表锁？
MyISAM 只支持表锁，InnoDB 支持表锁和行锁，默认为行锁。 表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低。 行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高。
2.MySQL的默认事务隔离级别是?
读未提交(RU): 一个事务还没提交时, 它做的变更就能被别的事务看到.
读提交(RC): 一个事务提交之后, 它做的变更才会被其他事务看到.
可重复读(RR): 一个事务执行过程中看到的数据, 总是跟这个事务在启动时看到的数据是一致的. 当然在可重复读隔离级别下, 未提交变更对其他事务也是不可见的.
串行化(S): 对于同一行记录, 读写都会加锁. 当出现读写锁冲突的时候, 后访问的事务必须等前一个事务执行完成才能继续执行.
3.Mysql数据库表类型有哪些？
MyISAM、InnoDB、HEAP、BOB,ARCHIVE,CSV等。
MyISAM：成熟、稳定、易于管理，快速读取。一些功能不支持（事务等），表级锁。
InnoDB：支持事务、外键等特性、数据行锁定。空间占用大，不支持全文索引等。
4.MySQL怎么恢复半个月前的数据？
通过整库备份+binlog进行恢复. 前提是要有定期整库备份且保存了binlog日志.
mysql> insert into test (id,name) values (2,'老男孩'),(3,'oldboyedu');
mysql> select * from test where name='oldboy';
mysql> update test set name='oldgirl' where id=1;
mysql> alter table test add age tinyint(2) after id;
mysql> system mysqldump -uroot -p123456 -B -x -F --events oldboy >/opt/bak.sql
mysql> delete from test;
5.一张自增表里面总共有 7 条数据，删除了最后 2 条数据，重启MySQL 数据库，又插入了一条数据，此时 id 是几？
表类型如果是 MyISAM ，那 id 就是 8。
表类型如果是 InnoDB，那 id 就是 6。
InnoDB 表只会把自增主键的最大 id 记录在内存中，所以重启之后会导致最大 id 丢失。
6.MySQL 的内连接、左连接、右连接有什么区别？
内连接关键字：inner join；左连接：left join；右连接：right join。 内连接是把匹配的关联数据显示
出来；左连接是左边的表全部显示出来，右边的表显示出符合条件的数据；右连接正好相反。
7.MySQL 问题排查都有哪些手段？
使用 show processlist 命令查看当前所有连接信息。 使用 explain 命令查询 SQL 语句执行计划。 开启慢查询日志，查看慢查询的 SQL。
8.如何做 MySQL 的性能优化？
为搜索字段创建索引。
避免使用 select *，列出需要查询的字段。
垂直分割分表。
选择正确的存储引擎。
读写分离
9.MySQL数据库作发布系统的存储，一天五万条以上的增量，预计运维三年,怎么优化？
（1）设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。
（2） 选择合适的表字段数据类型和存储引擎，适当的添加索引。
（3） 做mysql主从复制读写分离。
（4）对数据表进行分表，减少单表中的数据量提高查询速度。
（5）添加缓存机制，比如redis，memcached等。
（6）对不经常改动的页面，生成静态页面（比如做ob缓存）。
（7）书写高效率的SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM
TABLE.
10.MySQL由哪些部分组成, 分别用来做什么？
（1）Server
（2）连接器: 管理连接, 权限验证.
（3）分析器: 词法分析, 语法分析.
（4）优化器: 执行计划生成, 索引的选择.
（5）执行器: 操作存储引擎, 返回执行结果.
（6）存储引擎: 存储数据, 提供读写接口.
11.怎么验证 MySQL 的索引是否满足需求？
使用 explain 查看 SQL 是如何执行查询语句的，从而分析你的索引是否满足需求。 explain 语法：
explain select * from table where type=1。
12.请你介绍一下 mysql的主从复制？考察点：数据库
MySQL主从复制是其最重要的功能之一。主从复制是指一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器之中。对于多级复制，数据库服务器即可充当主机，也可充当从机。MySQL主从复制的基础是主服务器对数据库修改记录二进制日志，从服务器通过主服务器的二进制日志自动执行更新。
MySQL主从复制的两种情况：同步复制和异步复制，实际复制架构中大部分为异步复制。
复制的基本过程如下：
Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容。
Master接收到来自Slave的IO进程的请求后，负责复制的IO进程会根据请求信息读取日志指定位置之后的日志信息，返回给Slave的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置。
Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”。
Slave的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行。
13.请你介绍一下mysql的MVCC机制
MVCC是一种多版本并发控制机制，是MySQL的InnoDB存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。MVCC是通过保存数据在某个时间点的快照来实现该机制，其在每行记录后面保存两个隐藏的列，分别保存这个行的创建版本号和删除版本号，然后Innodb的MVCC 使用到的快照存储在Undo日志中，该日志通过回滚指针把一个数据行所有快照连接起来。
14.常用的Mysql复制架构有哪些？
（1）一主多从 在主库读取请求压力非常大的场景下, 可以通过配置一主多从复制架构实现读写分离, 把大量对实时性要求不是特别高的读请求通过负载均衡分布到多个从库上, 降低主库的读取压力，在主库出现异常宕机的情况下, 可以把一个从库切换为主库继续提供服务 。
（2）多级复制 一主多从的架构能够解决大部分读请求压力特别大的场景的需求, 考虑到 MysQL的复制是主库“推送” Binlog日志到从库,主库的 I/0压力和网络压力会随着从库的增加而增长(每个从库都会在主库上有一个独立的 Binlog Dump线程来发送事件), 而多级复制架构解决了一主多从场景下,主库额外的I/0和网络压力。
（3）双主复制/Dual Master 其实就是主库 Master和 Master2互为主从， client客户端的写请求都访问主库 Master,而读请求可以选择访问主库 Master或 Master2。
15.Mysql 的存储引擎,myisam和innodb的区别？
（1）InnoDB支持事务, MyISAM不支持.
（2）InnoDB支持行级锁, MyISAM支持表级锁.
（3）InnoDB支持多版本并发控制(MVVC), MyISAM不支持.
（4）InnoDB支持外键, MyISAM不支持.
（5）MyISAM支持全文索引, InnoDB不支持(但可以使用Sphinx插件)
16.请问MySQL的端口号是多少，如何修改这个端口号查看端口号：
使用命令show global variables like 'port';查看端口号 ，mysql的默认端口是3306。（补充：
sqlserver默认端口号为：1433；oracle默认端口号为：1521；DB2默认端口号为：5000；PostgreSQL
默认端口号为：5432）
修改端口号：
修改端口号：编辑/etc/my.cnf文件，早期版本有可能是my.conf文件名，增加端口参数，并且设定端
口，注意该端口未被使用，保存退出。
17.Mysql如何为表字段添加索引？
（1）添加PRIMARY KEY（主键索引）
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
（2）添加UNIQUE(唯一索引)
ALTER TABLE `table_name` ADD UNIQUE ( `column` )
（3）添加INDEX(普通索引)
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
（4）添加FULLTEXT(全文索引)
ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
（5）添加多列索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3`
)
18.说说自己对于 MySQL 常见的两种存储引擎：MyISAM与InnoDB 的理解？
InnoDB 引擎：InnoDB 引擎提供了对数据库 acid 事务的支持，并且还提供了行级锁和外键的约束，它
的设计的目标就是处理大数据容量的数据库系统。MySQL 运行的时候，InnoDB 会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎是不支持全文搜索，同时启动也比较的慢，它是不会保存表的行数的，所以当进行 select count() from table 指令的时候，需要进行扫描全表。由于锁的粒度小，写操作是不会锁定全表的,所以在并发度较高的场景下使用会提升效率的。
MyIASM 引擎：MySQL 的默认引擎，但不提供事务的支持，也不支持行级锁和外键。因此当执行插入和更新语句时，即执行写操作的时候需要锁定这个表，所以会导致效率会降低。不过和 InnoDB 不同的是，MyIASM 引擎是保存了表的行数，于是当进行 select count() from table 语句时，可以直接的读取已经保存的值而不需要进行扫描全表。所以，如果表的读操作远远多于写操作时，并且不需要事务的支持的，可以将 MyIASM 作为数据库引擎的首选。
