一、MYSQL基础
1.安装
windows安装:https://www.cnblogs.com/tangyb/p/8971658.html
linux安装:https://blog.csdn.net/wwd0501/article/details/71171614
2.数据库介绍
2.1.什么是数据库?
存储数据的仓库,本质上就是存储数据的文件系统,方便我们管理数据。
2.2.实体(类)和表关系
一个实体对应一张表
一个对象(实例)对应一条记录
2.3.常见关系型数据库
MYSQL:开源免费的数据库,小型的数据库.已经被Oracle收购了.MySQL6.x版本也开始收费。
- Oracle:收费的大型数据库,Oracle公司的产品。Oracle收购SUN公司,收购MYSQL。
- DB2:IBM公司的数据库产品,收费的。常应用在银行系统中。
- SQLServer:MicroSoft 公司收费的中型的数据库。C#、.net等语言常使用。
- SyBase:已经淡出历史舞台。提供了一个非常专业数据建模的工具PowerDesigner。
- SQLite: 嵌入式的小型数据库,应用在手机端。
3.MYSQL基础操作❤重点❤
3.1.启动关闭
//启动mysql服务
mysqld --console
//关闭mysql服务
mysqladmin -uroot shutdown
1234
3.2.DDL(数据定义语言,这些语句定义不同的数据段、数据库、表、列、索引等数据库对象。常用语句关键字主要包括create,drop,alter等)
由于DDL涉及表的定义、结构的修改,所以DDL较多由数据库管理员(DBA)使用
3.2.1.操作数据库
创建
1. 直接创建数据库
语法:
create database 数据库名;
实例:
create database web12;
2. 创建库并指定字符集
语法:
create database 数据库名 charset=字符集;
实例:
create database web12_1 charset=gbk;
查询
1. 查看所有数据库
语法:
show databases;
2. 查看建库语句
语法:
show create database 数据库名;
实例:
show create database web12;
修改
1. 修改数据库的字符集
语法:
alter database 数据库名 charset=字符集;
实例:
alter database web12_1 charset=utf8;
删除
1. 直接删除数据库
语法:
drop database 数据库名;
实例:
drop database web12_1;
使用数据库
1. 查看我在哪个数据库中?
语法:
select database();
2. 使用(切换)数据库
语法:
use 数据库名;
实例:
use web12;
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849
3.2.2.操作表
创建
1. 创建表
语法:
create table 表名(
列名 数据类型,
列名 数据类型,
列名 数据类型
....
);
实例:
create table student(
id int,
name varchar(32),
birthday date
);
2. 常用数据类型
1)整型:
int
2)浮点型:
double
3)字符串:
varchar(长度)
长度范围:1~65535(中英文及特殊符号)
4)日期:
date(yyyy-MM-dd)
3. 快速创建(复制表)
语法:
create table 新表 like 旧表;
实例:
create table teacher like student;
查询
1. 查看库中所有表
语法:
show tables;
2. 查看建表语句
语法:
show create table 表名;
实例:
show create table teacher;
3. 查看表结构
语法:
desc 表名;
实体:
desc teacher;
修改
1. 添加一列
语法:
alter table 表名 add 列名 数据类型;
实例:
alter table teacher add jieshao varchar(66);
2. 修改列的数据类型
语法:
alter table 表名 modify 列名 新数据类型;
实例:
alter table teacher modify jieshao varchar(370);
3. 修改列名
语法:
alter table 表名 change 旧列名 新列名 新数据类型;
实例:
alter table teacher change jieshao intro varchar(888);
4. 删除一列
语法:
alter table 表名 drop 列名;
实例:
alter table teacher drop intro;
5. 修改表名
语法:
rename table 旧表名 to 新表名;
实例:
rename table teacher to tch;
alter table emp rename emp1;
6.修改字段排序
alter table emp add birth date after ename;
alter table emp modify age int(3) first;
删除
1. 直接删除表
语法:
drop table 表名;
实例:
drop table tch;
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596
3.3.DML(数据操作语句,用于添加、删除、更新和查询数据库记录,并检查数据完整性。常用语句关键字主要包括 insert,delete,update和select等)
添加记录
1. 语法:
insert into 表名(列1,列2....) values(值1,值2);
insert into 表名 values(所有的值);
2. 注意
要求列名与值一一对应
字符类型可以使用单双引,推荐单引号
字符串可以设置所有类型,mysql底层进行隐式转换
3. 练习
insert into student(id,name,birthday) values(1,'刘强东','1964-7-1');
insert into student(id,name) values('2','杰克马');
insert into student values(3,'王健林','1960-8-3');
insert into student values(4,'撒贝宁',null);
4. 补充
insert into student values(5,'tom',null),(6,'jerry',null);
主从复制
1. 将一张表的数据快速复制到另一张表,要求:二张表结构相同
2. 步骤
1)克隆表
create table stu like student;
2)主从复制
insert into stu select * from student;
2)修改记录
1. 语法:
update 表名 set 列名1=新值1,列名2=新值2 .... [where 条件];
2. 注意
[] 中括号的内容可以省略
3. 练习
update stu set birthday='1967-1-1';
update student set birthday='1967-1-1' where id = 2;
3)删除记录
1. 语法:
delete from 表名 [where 条件];
2. 注意
[] 中括号的内容可以省略
3. 练习
delete from stu;
delete from student where id = 4;
4. 摧毁表【了解】
特点:将已有表删除,在重建一个新表
语法:
truncate table 表名;
实例:
truncate table student;
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556
3.4.DQL单表查询❤重点❤
3.4.1.简单查询
语法:
1. 语法:
select * from 表名;
2. 对查询结果去重
select distinct 列名 from 表名;
注意:多字段去重要求内容完全一致
3. 在查询结果使用运算符,都会不影响表中原有的数据
4. null与其他数值进行运算,结果还是NULL
ifnull(列名,默认值) 函数
功能:判断如果该列有值就直接返回,如果为null就返回默认值
5. 别名
select 列名 [as] 列别名,.... from 表名 [as] 表别名;
12345678910111213141516
实例:
-- 查询表中所有学生的信息
select * from student1;
-- 查询表中所有学生的姓名和对应的语文成绩
select name,chinese from student1;
-- 查询表中学生姓名(去重)
select distinct name from student1;
select distinct name,chinese from student1;
-- 在所有学生数学分数上加10分特长分
select name,math+10 from student1;
-- 统计每个学生的总分
select name,chinese+english+math from student1;
select ifnull(english,0) from student1;
select name,chinese+ifnull(english,0)+math from student1;
-- 使用别名表示学生总分
select name as 姓名,(chinese+ifnull(english,0)+math) as 总分 from student1;
-- 表别名
select stu1.name from student1 as stu1;
12345678910111213141516171819202122232425
3.4.2.高级查询
语法(条件查询):
1. 语法:
select ... from 表名 where 条件;
2. 比较(关系)运算符
> < >= <= = !=
3. 逻辑运算符
and:条件同时满足
or:条件满足一个
not:条件取反
4. in关键字(集合)
select ... from 表名 where id in(1,3,5,7,9);
5. between关键字(范围)
select ... from 表名 where 列名 between 较小的值 and 较大的值;
6. null这哥们六亲不认
select ... from 表名 where 列名 is null; 查询为null的
select ... from 表名 where 列名 is not null;查询不为null的
7. like关键字(模糊)
select ... from 表名 where 列名 like '通配符字符串'
_ 单个任意字符
% 多个任意字符
12345678910111213141516171819202122232425
实例:
# 关系运算符
-- 查询math分数大于80分的学生
SELECT * FROM student WHERE math > 80;
-- 查询english分数小于或等于80分的学生
SELECT * FROM student WHERE english <=80;
-- 查询age等于20岁的学生
SELECT * FROM student WHERE age = 20;
-- 查询age不等于20岁的学生
SELECT * FROM student WHERE age != 20;
# 逻辑运算符
-- 查询age大于35且性别为男的学生(两个条件同时满足)
SELECT * FROM student WHERE age > 35 AND sex = '男';
-- 查询age大于35或性别为男的学生(两个条件其中一个满足)
SELECT * FROM student WHERE age >35 OR sex = '男';
-- 查询id是1或3或5的学生
SELECT * FROM student WHERE id = 1 OR id = 3 OR id = 5;
-- in关键字
-- 再次查询id是1或3或5的学生
SELECT * FROM student WHERE id IN (1,3,5,7,9);
-- 查询id不是1或3或5的学生
SELECT * FROM student WHERE id NOT IN (1,3,5,7,9);
-- 查询english成绩大于等于77,且小于等于87的学生
SELECT * FROM student WHERE english >=77 AND english <=87;
SELECT * FROM student WHERE english BETWEEN 77 AND 87;
-- 查询英语成绩为null的学生
SELECT * FROM student WHERE english IS NULL;
SELECT * FROM student WHERE english IS NOT NULL;
# like模糊匹配
-- 查询姓马的学生
SELECT * FROM student WHERE `name` LIKE '马%';
-- 查询姓名中包含'德'字的学生
SELECT * FROM student WHERE `name` LIKE '%德%';
-- 查询姓马,且姓名有三个字的学生
SELECT * FROM student WHERE `name` LIKE '马__';
1234567891011121314151617181920212223242526272829303132333435363738
3.4.3.排序
语法:
1. 语法:
select ... from 表名 order by 列名 [asc|desc],列名 [asc|desc];
asc:升序 (默认)
desc:降序
2. 注意:
后面排序字段在前面排序完成基础上在进行排序
1234567
实例:
# 排序
-- 查询所有数据,使用年龄降序排序
SELECT * FROM student ORDER BY age DESC;
-- 查询所有数据,在年龄降序排序的基础上,如果年龄相同再以数学成绩降序排序
SELECT * FROM student ORDER BY age DESC,math DESC;
123456
3.4.4.聚合查询
语法:
# 常用聚合函数
count(列名):统计个数
补充:对一列统计推荐使用 count(*)
sum(列名): 求和
avg(列名): 平均值
max(列名): 最大值
min(列名): 最小值
1234567
实例:
# 聚合函数
-- 查询学生总数(null值处理)
SELECT COUNT(id) FROM student;
SELECT COUNT(IFNULL(english,0)) FROM student;
SELECT COUNT(*) FROM student;
-- 查询年龄大于40的总数
SELECT * FROM student WHERE age > 40;
SELECT COUNT(*) FROM student WHERE age > 40;
-- 查询数学成绩总分
SELECT SUM(math) FROM student;
-- 查询数学成绩平均分
SELECT AVG(math) FROM student;
-- 查询数学成绩最高分
SELECT MAX(math) FROM student;
-- 查询数学成绩最低分
SELECT MIN(math) FROM student;
12345678910111213141516
3.4.5.分组
语法:
1. 语法:
select 分组列名 from 表名 group by 分组列名 having 分组后条件;
2. 区别:
where:在分组前进行条件过滤,不能使用聚合函数
having:在分组后进行条件过滤,可以使用聚合函数
123456
实例:
-- 查询男生和女各多少人
SELECT COUNT(*) FROM student WHERE sex = '男';
SELECT COUNT(*) FROM student WHERE sex = '女';
SELECT sex,COUNT(sex) FROM student GROUP BY sex;
-- 作业:按照地址分组,统计各个地区人数
SELECT address,COUNT(address) FROM student GROUP BY address;
-- 查询年龄大于25岁的人,按性别分组,统计每组的人数
-- 1.查询年龄大于25岁的人
SELECT * FROM student WHERE age > 25;
-- 2.按性别分组
SELECT sex FROM student WHERE age > 25 GROUP BY sex;
-- 3.统计每组的人数
SELECT sex,COUNT(sex) FROM student WHERE age > 25 GROUP BY sex;
-- 查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据
-- 错误 Invalid use of group function
SELECT sex,COUNT(sex) FROM student WHERE age > 25 AND COUNT(sex)>2 GROUP BY sex;
-- 正确
SELECT sex,COUNT(sex) FROM student WHERE age > 25 GROUP BY sex HAVING COUNT(sex)>2;
1234567891011121314151617181920212223
3.4.6.分页
语法:
1. 语法:
select ... from 表名 limit 开始索引,每页个数
2. 注意:
索引是从0开始的,默认值也为0
3. 索引公式:
索引= (当前页-1) × 每页个数
12345678
实例:
-- 查询学生表中数据,从第三条开始显示,显示6条
SELECT * FROM student LIMIT 2,6;
-- 查询学生表中数据,显示前6条
SELECT * FROM student LIMIT 0,6;
SELECT * FROM student LIMIT 6;
-- 模拟百度分页,一页显示5条
-- 第一页
SELECT * FROM student LIMIT 0,5;
-- 第二页
SELECT * FROM student LIMIT 5,5;
-- 第三页
SELECT * FROM student LIMIT 10,5;
123456789101112
3.5.MYSQL函数汇总
常用函数:
函数 | 功能 |
---|---|
CONCAT(s1,s2,s3…) | 连接s1到sn的字符串(任何字符串和null拼接都是null) |
insert(str,x,y,instr) | 将字符串str从x位置开始,y字符长的子串替换为字符串instr |
lower(str) | 将字符串str中所有字符变为小写 |
UPPER(str) | 大写 |
LEFT(str,x) | 返回字符串str最左边x个字符 |
RIGHT(str,x) | 返回字符串str最右边的x个字符 |
LPAD(str,n,pad) | 用字符串pad对str最左边进行填充,直到长度为n个字符串长度 |
PRPAD(str,n,pad) | 用字符串pad对str最右边进行填充,直到长度为n个字符串长度 |
LTRIM(str) | 去掉字符串str左侧的空格 |
RIGHT(str) | 去掉字符串str行尾的空格 |
REPEAT(str,x) | 返回str重复x次的结果 |
REPLACE(Str,a,b) | 用字符串b替换字符串str中所有出现的字符串a |
STRCMP(s1,s2) | 比较字符串s1和s2 |
TRIM(str) | 去掉行尾和行头的空格 |
SUBSTRING(str,x,y) | 返回字符串str x位置起y字符串长度的字串 |
数字函数:
函数 | 功能 |
---|---|
ABS(X) | 返回x的绝对值 |
CEIL(X) | 返回大于x的最小整数值 |
FLOOR(X) | 返回小于x的最大整数值 |
MOD(x,y) | 返回x/y的模 |
RAND() | 返回0-1内的随机值 |
ROUND(x,y) | 返回参数x的四舍五入的有y位小数的值 |
TRUNCATE(x,y) | 返回数值x截断为y位小树的结果 |
日期函数:
函数 | 功能 |
---|---|
CURDATE() | 返回当前日期 |
CURTIME() | 返回当前时间 |
NOW() | 返回当前的日期和时间 |
UNIX_TIMESTAMP(date) | 返回date的unix时间戳 |
FROM_UNIXTIME | 返回UNIX时间戳的日期值 |
WEEK(date) | 返回日期date为一年中的第几周 |
YEAR(date) | 返回日期date的年份 |
HOUR(time) | 返回time的小时值 |
MINUTE(time) | 返回time的分钟值 |
MONTHNAME(date) | 返回date的月份名 |
DATE_FROMATE(date,fmt) | 返回按字符串fmt格式化日期date值 |
DATE_ADD(date,interval expr type) | 返回一个日期或时间值加上一个时间间隔的时间值 |
DATEDIFF(expr,expr2) | 返回起始时间expr和结束时间expr2之间的天数 |
流程函数:
函数 | 功能 |
---|---|
IF(value,t f) | 如果value是真 返回 t;否则返回f |
IFNULL(value1,value2) | 如果value1不为空,返回value1,负责返回value2 |
CASE WHEN[value1] THEN[value2]…ELSE[default] END | 如果value1是真,返回result1否则返回defalut |
case [expr] WHEN[value1] THEN[value2]…ELSE[default] END | 如果expr等于value1,返回result1否则返回defalut |
其它函数:
函数 | 功能 |
---|---|
DATABASE() | 返回的确数据库库名 |
VERSION() | 返回当前数据库版本 |
USER() | 返回当前登录用户名 |
INET_ATON(IP) | 返回ip地址的数字表示 |
INET_NTOA(num) | 返回数字代表的ip地址 |
PASSWORD(str) | 返回字符串str加密版本 |
MD5() | 返回字符串的md5值 |
二、MYSQL高级进阶
1.join查询(七种情况)
1.1.inner join
注:A表示左表,B表示右表,下同
inner join:A、B共有,也就是交集
例子语句:
select * from tab1 a inner join tab2 b on a.id=b.id;
1
1.2.left join
A独有+AB共有(交集)
例子语句:
select * from tab1 a left join tab2 b on a.id=b.id;
1
1.3.right join
B独有+AB共有(交集)
例子语句:
select * from tab1 a right join tab2 b on a.id=b.id;
1
1.4.A独有
例子语句:
select * from tab1 a left join tab2 b on a.id=b.id where b.id is null;
1
1.5.B独有
例子语句:
select * from tab1 a right join tab2 b on a.id=b.id where a.id is null;
1
1.6.AB全有(并集)
由于mysql中不支持full outer join,所以这里通过union进行转换。AB并集:AB交集+A独有+B独有
例子语句:
select * from tab1 a left join tab2 b on a.id=b.id
union
select * from tab1 a right join tab2 b on a.id=b.id;
123
1.7.A、B独有并集
A、B独有并集,相当于A、B全有去掉AB的共有(交集)
例子语句:
select * from tab1 a left join tab2 b on a.id=b.id where b.id is null
union
select * from tab1 a right join tab2 b on a.id=b.id where a.id is null;
123
2.索引
2.1.索引是什么?
索引(Index)是帮助MySQL高效获取数据的数据结构。因此索引的本质就是数据结构。索引的目的在于提高查询效率,可类比字典、书籍的目录等这种形式。
可简单理解为“排好序的快速查找数据结构”。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
一般来说,索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上。
平常所说的索引,如果没有特别指明,都是B树索引。其中聚集索引、次要索引、覆盖索引、前缀索引、唯一索引默认都是用B树。
通过show index from tablename可以查看表的索引情况。
2.2.索引分类
①单值索引:一个索引只包含单个列,一个表可以有多个单值索引。
②唯一索引:索引列的值必须唯一,但允许有空值,主键就是唯一索引。
③复合索引:一个索引包含多个列
索引的结构:
①BTREE索引;②Hash索引;③Full-Text索引;④R-Tree索引
2.3.基本语法
①创建索引
create [unique] index indexname on tablename(columnname(length));
alter table tablename add index indexname (columnname(length));
12
注:如果是char、varchar类型的字段,length可以小于字段实际长度;如果是blob、text类型,必须指定length。
②删除索引
drop index indexname on tablename;
1
③查看索引
show index from tablename;
1
④其他创建索引的方式
1.添加主键索引
ALTER TABLE `table_name` ADD PRIMARY KEY (`column`)
2.添加唯一索引
ALTER TABLE `table_name` ADD UNIQUE (`column`)
3.添加全文索引
ALTER TABLE `table_name` ADD FULLTEXT (`column`)
4.添加普通索引
ALTER TABLE `table_name` ADD INDEX index_name (`column` )
5.添加组合索引
ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`, `column3`)
复制代码
123456789101112131415
3.索引优化
目的:索引优化的目的主要是让索引不失效
①全值匹配。
②最佳左前缀法则:带头大哥不能死,中间兄弟不能断;带头大哥可跑路,老二也可跟着跑,其余兄弟只能死。
③索引列上不计算。
④覆盖索引记住用。
⑤不等于、is null、is not null导致索引失效。
⑥like百分加右边,加左边导致索引失效,解决方法:使用覆盖索引。
⑦字符串不加单引号导致索引失效。
⑧少用or,用or导致索引失效。
4.大小写敏感
只要在创建表的时候指定collate为utf8_bin ,就可以实现大小写敏感,如果建表时未指定,则可修改字段的校对规则,也可以实现大小写敏感
5.ORDER BY优化
在使用order by时,经常出现Using filesort,因此对于此类sql语句需尽力优化,使其尽量使用Using index
①MySQL支持两种方式的排序filesort和index,Using index是指MySQL扫描索引本身完成排序。index效率高,filesort效率低。
②order by满足两种情况会使用Using index。1.order by语句使用索引最左前列。
2.使用where子句与order by子句条件列组合满足索引最左前列。
③尽量在索引列上完成排序,遵循索引建立(索引创建的顺序)时的最佳左前缀法则。
④如果order by的条件不在索引列上,就会产生Using filesort。1.filesort有两种排序算法:双路排序和单路排序。
双路排序:在MySQL4.1之前使用双路排序,就是两次磁盘扫描,得到最终数据。读取行指针和order by列,对他们进行排序,然后扫描已经排好序的列表,按照列表中的值重新从列表中读取对应的数据输出。即从磁盘读取排序字段,在buffer进行排序,再从磁盘取其他字段。
如果使用双路排序,取一批数据要对磁盘进行两次扫描,众所周知,I/O操作是很耗时的,因此在MySQL4.1以后,出现了改进的算法:单路排序。
单路排序:从磁盘中查询所需的列,按照order by列在buffer中对它们进行排序,然后扫描排序后的列表进行输出。它的效率更高一些,避免了第二次读取数据,并且把随机I/O变成了顺序I/O,但是会使用更多的空间,因为它把每一行都保存在内存中了。2.单路排序出现的问题。
当读取数据超过sort_buffer的容量时,就会导致多次读取数据,并创建临时表,最后多路合并,产生多次I/O,反而增加其I/O运算。
解决方式:
a.增加sort_buffer_size参数的设置。
b.增大max_length_for_sort_data参数的设置。
⑤提升order by速度的方式:1.在使用order by时,不要用select *,只查询所需的字段。
因为当查询字段过多时,会导致sort_buffer不够,从而使用多路排序或进行多次I/O操作。
2.尝试提高sort_buffer_size。
3.尝试提高max_length_for_sort_data。
⑦group by与order by很类似,其实质是先排序后分组,遵照索引创建顺序的最佳左前缀法则。当无法使用索引列的时候,也要对sort_buffer_size和max_length_for_sort_data参数进行调整。注意where高于having,能写在where中的限定条件就不要去having限定了
三、MYSQL常见面试题
3.1.什么是索引?
索引是对数据库中一或多个列值的排序,帮助数据库高效获取数据的数据结构。
假如我们用类比的方法,数据库中的索引就相当于书籍中的目录一样,当我们想找到书中的某个知识点,我们可以直接去目录中找而不是在书中的每页找,但是这也抛出了一个缺点,在对数据库修改的时候要修改索引导致花费时间增多。
几个基本的索引类型:普通索引 唯一索引 主键索引 全文索引
3.2.索引的优点和缺点?
索引优点:加快检索速度,唯一索引确保每行数据的唯一性,在使用索引的过程中可以优化隐蔽器,提供系统性能。
索引缺点:插入 删除 修改 维护速度下降,占用物理和数据空间。
3.3.drop delete truncate 的区别?
三者都是删除的意思,但是三个各有区别,delete和truncate只删除数据不删除表的结构,速度 drop>truncate>delete;
删除部分数据时,delete删除时要带上where语句,保留表而想删除所有的数据时用truncate
3.4.主键,外键的区别?
主键:数据库中表中唯一和完整标识的数据列或属性集合
外键:在一个表中存在另一个表的主键叫做外键
3.5.如何开启从库的binlog功能?
修改配置文件加上下面的配置:
log_bin=slave_bin
log_bin_index=slave-bin.index
需要重启服务
3.6.请解释全备、增备、冷备、热备概念及企业实践经验?
全备:数据库所有数据的一次完整备份,也就是备份当前数据库的所有数据
增备:就是在上次备份的基础上备份到现在所有新增的数据
冷备:停止服务的基础上进行备份操作
热备:实行在线进行备份操作,不影响数据库的正常运行
全备在企业中基本上是每周或天一次,其他时间是进行增量备份;热备使用的情况是有两台数据库在同时提供服务,针对归档模式的数据库;冷备使用情况在企业初期,数据量不大且服务器数量不多,可能会执行某些库、表结构等重大操作时。
3.7.开发有一堆数据发给DBA执行,DBA执行需要注意什么?
1.需要注意语句是否有格式上的错误,执行会出错导致过程中断
2.还需要注意语句的执行时间是否过长,是否会对服务器负载产生压力影响实际生产
3.8.如何更批量更改数据库表的引擎?
通过mysqldump备份出一个sql文件,在使用sed 命令替换 sed -i 's/GBK/UTF8/g'
3.9.网站打开慢,如果时数据库慢导致,如何排查并解决?
可以使用top free等命令分析系统性能等方面的问题,如果因为数据库的原因造成的,就需要查看慢查询日志去查找并分析问题所在。
3.10.如何调整生产线中myslq数据库的字符集?
1.首先导出库都表结构, -d 只导出表结构,然后批量替换。
2.导出库中的所有数据(在不生产新数据的前提下)
3.然后全局替换 set name = xxxxx
4.删除原有库与表,并新创建出来,在导入建库与建表语句与所有数据。
3.11.为mysql设置密码或者修改密码?
mysql -uroot -ppassword -e "set password for root = password('password')"
mysqladmin -uroot password "NEWPASSWORD"
12
3.12.创建一innodb GBK表test,字段 id int(4)和name varchar(16)?
create table test (id int(4),
name varchar(16)
)ENGINE=innodb DEFAULT CHARSET=gbk;
123
3.13.创建建表结构及表结构的sql语句?
dest test;
show create table test\G
12
3.14.插入一条数据“1,‘mingongge’”?
insert into test values (1,'mingongge');
1
3.15.删除test表中的所有数据,并查看?
delete from test;
select * from test;
12
3.16.把id列设置为主键,在name字段上创建普通索引?
alter table test add primary key(id);
create index mggindex on test(name(16));
12
3.17.查看创建的索引及索引的类型等信息?
show index from test;
show create table test\G
12
下面的命令也可以查看索引类型
show keys from test\G
1
3.18.删除name,shouji列的索引?
drop index SJ on test;
drop index mggindex on test;
12
3.19.查询手机号以137开头的,名字为zhao的记录(提前插入)?
select * from test where shuoji like '137%' and name = 'zhao';
1
3.20.收回mingongge库用户的select权限?
recoke select on mingongge.* from mingongge@localhost;
1
3.21.请解释关系型数据库的概念及主要特点?
关系型数据库模型是把复杂的数据结构归结为简单的二元关系,对数据的操作都是建立一个或多个关系表格,最大的特点就是二维的表格,通过sql结构查询语句存取数据,保持数据一致性方面很强大。
3.22.请说出关系型数据库的典型产品、特点及应用场景?
1.mysql 互联网企业常用
2.oracle 大型传统企业应用软件
3.如数据的备份、复杂连接查询、一致性数据存储等,还是使用mysql或其他传统的关系型数据库最合适
3.23.请详细描述sql语句分类及对应代表性关键字?
1.DDL 数据定义语言,用来定义数据库对象:库、表、列
代表性关键字:create drop alter
2.DML 数据库操作语言,用来定义数据库记录
代表性关键字:insert delete update
3.DCL 数据库控制语言,用来定义访问权限和安全级别
代表性关键字:grant deny revoke
4.DQL 数据库查询语言,用来查询记录数据
代表性关键字:select
3.24.请详细描述char(4)和varchar(4)的区别?
char长度是固定不可变得,varchar长度是可变的(在设定内)比如同写入cn字符,char类型对应的长度是4(cn+两个空格),但varchar对应的长度是2。
3.25.如何在线清理MySQL binlog?
MySQL中的binlog日志记录了数据中的数据变动,便于对数据的基于时间点和基于位置的恢复
但日志文件的大小会越来越大,点用大量的磁盘空间,因此需要定时清理一部分日志信息
手工删除:
首先查看主从库正在使用的binlog文件名称
show master(slave) status\G
1
删除之前一定要备份
purge master logs before'2017-09-01 00:00:00';
1
删除指定时间前的日志
purge master logs to'mysql-bin.000001';
1
删除指定的日志文件
自动删除:
通过设置binlog的过期时间让系统自动删除日志
show variables like 'expire_logs_days';
1
3.26.Binlog工作模式有哪些?各什么特点,企业如何选择?
1.Row(行模式);
日志中会记录成每一行数据被修改的形式,然后在slave端再对相同的数据进行修改
2.Statement(语句模式)
每一条修改的数据都会完整的记录到主库master的binlog里面,在slave上完整执行在master执行的sql语句
3.mixed(混合模式)
结合前面的两种模式,如果在工作中有使用函数 或者触发器等特殊功能需求的时候,使用混合模式
数据量达到比较高时候,它就会选择 statement模式,而不会选择Row Level行模式
3.27.误操作执行了一个drop库SQL语句,如何完整恢复?
1、停止主从复制,在主库上执行锁表并刷新binlog操作,接着恢复之前的全备文件(比如0点的全备)
2、将0点时的binlog文件与全备到故障期间的binlog文件合并导出成sql语句
mysqlbinlog --no-defaults mysql-bin.000011 mysql-bin.000012 >bin.sql
1
3、将导出的sql语句中drop语句删除,恢复到数据库中
mysql -uroot -pmysql123 < bin.sq
1
3.28.查看当前数据库的版本?
mysql -V
mysql -uroot -ppassword -e "use mysql;select version();"
12
3.29.查看当前数据库的字符集?
show create database DB_NAME;
1
3.30.MySQL如何实现双向互为主从复制,并说明应用场景?
双向同步主要应用于解决单一主库写的压力,具体配置如下
主库配置
[mysqld]
auto_increment_increment = 2 #起始ID
auto_increment_offset = 1 #ID自增间隔
log-slave-updates
从库配置
[mysqld]
auto_increment_increment = 2 #起始ID
auto_increment_offset = 2 #ID自增间隔
log-slave-updates 主从库服务器都需要重启mysql服务
123456789
3.31.mysql主从复制故障如何解决?
1.登陆从库,执行stop slave;
停止主从同步
然后 set global sql_slave_skip_counter =1;
跳过一步错误
最后执行 start slave;并查看主从同步状态
2.需要重新进行主从同步操作
进入主库,进行全备数据库并刷新binlog,查看主库此时的状态
回复全备文件到从库,然后执行change master
开启主从同步start slave
;并查看主从同步状态
3.32.如何监控主从监控是否故障?
mysql -uroot -ppassword -e "show slave statusG" | grep -E "Slave_IO_Running | Slave_SQL_Running" | awk 'print $2' | grep -c Yes
1
3.33.mysql数据库如何实现读写分离?
1.通过开发程序实现
2.通过其它工具实现(mmm,mycat等)
3.34.生产中一主多从从库宕机,如何手工恢复?
1.停止与主库的连接
2.修复好从库数据库
3.重新操作主从同步
3.35.生产中一主多从主库宕机,如何手工恢复?
1.登陆各个从库停止同步,并查看从库谁的数据最新,将它设置为新主库让其它从库同步其数据
2.修复好主库之后,重新操作主从同步的步骤就可以了
需要注意的新的主库如果之前是只读,需要关闭此功能让其可写
需要在新从库创建与之前主库相同的同步的用户与权限
其它从库执行change master to master_port=新主库的端口,start slave
3.36.工作中遇到过哪些数据库故障,请描述一两个例子?
1.开发使用root用户在从库上写入数据造成主从数据不一致,并且前端没有展示需要修改的内容(仍旧是老数据)(没有控制好用户权限和从库没有设置只读)
2.内网测试环境服务器突然断电造成主从同步
3.37.MySQL出现延迟有哪些原因,如何解决?
1.需要同步的从库数据太多
2.从库二点硬件资源较差,需要提升
3.网络问题,需要提升网络宽带
4.主库的数据写入量太大,需要优化配置和硬件资源
5.sql语句执行过长导致,需要优化
3.38.什么是数据库事务,事务有哪些特性?企业如何选择?
数据库事务是逻辑上的一组sql语句,组成这组操作的各个语句,执行时要么成功,要么失败。
特点:具有原子性、隔离性、持久性、一致性
3.39.给出企业生产大型mysql集群架构可行备份方案?
1.双主多从,主从同步的架构,然后实行某个从库专业做为备份服务器
2.编写脚本实行分库分表进行备份,并加入定时任务
3.最终将备份服务推送至内网专业服务器,数据库服务器本地保留一周
4.备份服务器根据实际情况来保留备份数据(一般30天)
3.40.企业生产mysql如何优化(请多角度描述)?
1.提升服务器硬件资源与网络带宽
2.优化mysql配置文件
3.开启慢查询日志然后分析问题所在
3.41.请描述MySQL里中文数据乱码的原理,如何防止乱码?
服务器系统、数据库、客户端三方字符集不一致导致,需要统一字符
3.42.请解释非关系型数据库概念及主要特点?
非关系型数据库也被称为Nosql,数据存储不需要有特有固定的表结构
特点:高性能,高并发,简单易安装
3.43.请说出非关系型数据库的典型产品、特点及应用场景?
1.memcaced 纯内存
2.redis 持久化缓存
3.mongodb 面向文档
如果需要短时间响应的查询操作,没有良好模式定义的数据存储,或者模式更改频繁的数据存储还是NoSQL
3.44.什么是MySQL多实例,如何配置mysql多实例?
mysql多实例就是在同一台服务器上启用多个mysql服务,它们监听不同的端口,运行多个服务进程,它们相互独立,互不影响的对外提供服务,便于节约服务器资源与后期架构扩展
多实例的配置方法有两种:
1、一个实例一个配置文件,不同端口
2、同一配置文件(my.cnf)下配置不同实例,基于mysqld_multi工具
3.45.如何加强MySQL安全,请给出可行的具体措施?
1、删除数据库不使用的默认用户
2、配置相应的权限(包括远程连接)
3、不可在命令行界面下输入数据库的密码
4、定期修改密码与加强密码的复杂度
3.46.MySQL Sleep线程过多如何解决?
1、可以杀掉sleep进程,kill PID
2、修改配置,重启服务
[mysqld]
wait_timeout = 600
interactive_timeout=30
(如果生产服务器不可随便重启可以使用下面的方法解决)
set global wait_timeout=600
set global interactive_timeout=30;
123456
3.47.详述MySQL主从复制原理
主从复制的原理如下:
主库开启binlog功能并授权从库连接主库,从库通过change master得到主库的相关同步信息,然后连接主库进行验证,主库IO线程根据从库slave线程的请求,从master.info开始记录的位置点向下开始取信息,同时把取到的位置点和最新的位置与binlog信息一同发给从库IO线程,从库将相关的sql语句存放在relay-log里面,最终从库的sql线程将relay-log里的sql语句应用到从库上,至此整个同步过程完成,之后将是无限重复上述过程。
3.48.MySQL如何实现双向互为主从复制,并说明应用场景?
双向同步主要应用于解决单一主库写的压力,具体配置如下
主库配置
[mysqld]
auto_increment_increment = 2 #起始ID
auto_increment_offset = 1 #ID自增间隔
log-slave-updates
从库配置
[mysqld]
auto_increment_increment = 2 #起始ID
auto_increment_offset = 2 #ID自增间隔
log-slave-updates
12345678910
主从库服务器都需要重启mysql服务
四、继续进阶文档(修仙)
4.1.MYSQL修仙之EXPLAIN用法和结果分析
4.2.MYSQL修仙之索引简介
4.3.MYSQL修仙之索引分析
4.4.MYSQL修仙之索引优化之索引失效
4.5.MYSQL修仙之查询截取分析
4.6.MYSQL修仙之锁机制
4.7.MYSQL修仙之主从复制及读写分离
总结
博主这里整理了MYSQL由基础到高级进阶 包括具体的例子以及一些常用的MYSQL方面的面试题 不过很多时候会问你随机写一个SQL语句 一般不要慌 都不会很难 看完博主的文章保你十拿十一稳 最后 感谢大家支持哈 三连三连~
————————————————
版权声明:本文为CSDN博主「白大锅」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/weixin_45735355/article/details/119672412