MySQL重点题

1、掌握 mysql、oracle、sqlserver三种产品的区别（各自使用场景）？

• MySQL

  • 特点：开源、轻量级、性能高、社区活跃。

  • 场景：中小型 Web 应用（如博客、电商）、读写分离架构、云服务（如 LAMP 栈）。

  • 限制：复杂事务处理能力较弱（但 InnoDB 已优化）。

• Oracle

  • 特点：商业数据库、功能全面、支持高并发、ACID 强一致性、分布式架构（如 RAC）。

  • 场景：金融、电信等大型企业核心系统、高可用性要求的场景。

  • 缺点：成本高、学习曲线陡峭。

• SQL Server

  • 特点：微软生态集成（如 .NET、Azure）、图形化管理工具完善。

  • 场景：企业内部系统（ERP、CRM）、Windows 服务器环境。

  • 限制：跨平台支持较弱（Linux 版本已推出但生态有限）。

2、sql语言的四大类 ddl dml dql dcl

• DDL（数据定义语言）

  数据定义语言 所有sql都与结构相关

  创建 create、修改 alter、删除 drop结构

  库 database、表 table、视图 view、存储过程 procedure、函数 function、触发器 trigger、索引 index等

• DML（数据操作语言）

  数据操作语言 所有sql都与数据相关

  新增数据 insert、修改数据 update、删除数据 delete

• DQL（数据查询语言） 查询数据：SELECT。 只负责查询数据

• DCL（数据控制语言） 权限管理：GRANT, REVOKE, DENY。

  数据控制语言

  用户、权限 grant revoke、事务 start transaction ; commit; rollback;

3、如何通过命令行模式连接到mysql数据库，查询所有库，指定操作的数据库，查询库中所有的表，如何实现远程连接？

• 连接数据库

  连接本机数据库: mysql  -uroot  -p密码 
  连接远程数据库: mysql -h 10.10.15.74 -uroot -proot
  mysql -h ip -u用户名 -p   
  查看用户权限：
  SELECT user, host FROM mysql.user;

• 常用命令

  SHOW DATABASES;          -- 查看所有库
  USE [库名];              -- 切换数据库
  SHOW TABLES;             -- 查看当前库所有表

4、如何判断一个mysql服务的状态？如何启动、停止mysql服务？

services.msc

cmd窗口：
sc query mysql     -- 查看状态（安装sc服务）
net start mysql80     -- 启动
net stop mysql80      -- 停止

• Windows 通过服务管理器（services.msc）操作 MySQL 服务。

5、创建数据库、表的语法结构？

-- 创建数据库
create database yan9;
create database yanfa9  DEFAULT CHARACTER SET utf8mb4; //在中文字符乱码时使用
​
-- 创建表
CREATE TABLE `表名`(
    列名1 数据类型1 [约束],
    列名2 数据类型2 [约束],
    ....
    列名n 数据类型n [约束]
);
-- 注意：最后一列，不需要加逗号

6、如何删除数据库、删除表？

drop database 库名;
drop table 表名;

7、修改数据表，新增列、修改列、删除列、修改列名、修改表名 对应的sql语法结构？

操作	语法示例
新增列	ALTER TABLE 表 ADD 列名 数据类型;
修改列类型	ALTER TABLE 表 MODIFY 列名 新数据类型;
删除列	ALTER TABLE 表 DROP COLUMN 列名;
修改列名	ALTER TABLE 表 CHANGE 旧列名 新列名 数据类型;
修改表名	RENAME TABLE 旧表名 TO 新表名;

1、修改表名
    rename
2、新增列
    add
3、修改列  
    1）更改约束和数据类型  modify
    2）更改列名  change
4、删除列  drop
​
-- 新增列
alter table student add address varchar(255) ;
-- 修改列
alter table student modify address char(255) ;
alter table student change name username char(16) not null
-- 删除列
alter table student drop address;
-- 查看表结构
desc student;
​
-- 更新student 为stu
alter table student rename to stu;
alter table stu rename student;

8、mysql中常见的数据类型有哪些？

整数型 int

tinyint ， smallint ， mediumint ， int ， bigint

浮点型 double

float ，double ，decimal(m,n)

日期时间 datetime

date ， time ，datetime ，timestamp(时间戳、精细到毫秒)

字符串

char(定长字符串 ) ， varchar（变长字符串）， text （）， longtext

char(5) 永远占5个字符存储空间

varchar(5) 根据存入的字符串计算占用的存储空间

9、创建表时，常见的约束有哪些？

primary key  主键   唯一且非空
foreign key 外键
auto_increment 配合主键   自动增长
comment '注释'
not null 非空
null 默认  允许为空
default  默认值
unique key 唯一键  唯一约束，要求值不能重复

注：反引号  ``   当表名或者列名为mysql中关键字时，需要使用反引号，转义为普通字符

10、char和varchar的区别？

CHAR	VARCHAR
定长，存储时填充空格	变长，按实际长度存储
适合长度固定的字段（如 MD5）	适合长度变化的字段（如评论）
最大 255 字符	最大 65535 字符（受行限制约）
读取速度更快	节省存储空间

11、sql语句中 insert 、update、delete 操作数据的语法结构

insert into 表名（列名1） values (值1);
insert into 表名（列名1,列名2） values (值1，null);
insert into 表名 values (值1，值2，值3);  --依次向表中所有的列赋值
insert into 表名（列名1） values (值1)，(值2)，(值3);  --依次向表中新增多行数据  mysql支持  oracle不支持
​
​
update 表名 set 列名=值 where 。。。;
delete from 表名 where 。。。。；

12、事务的四个特性

事务的目的是保障一个业务，要么都成功，要么都失败。转账业务。

原子性： 最小单位，要么都成功，要么都失败

一致性：从一个一致性状态到另外一个一致性状态。 转账前后 账户总额保持一致。

隔离性：事务提交之前对外部不可见。

持久性：一经提交，永久生效。

13、事务操作的基本步骤

1、设置自动提交模式关闭  set autocommit=false;
2、开启事务  start transaction;
执行多个dml语句或者dql语句;
不允许执行ddl语句（ddl语句会自动提交事务）
3、手动提交或者回滚事务
commit;|rollback;
4、设置自动提交模式到默认状态 set autocommit=true;

14、如何创建用户、赋权、回收权限

create user bbh identified by '123456';
alter user bbh identified by '0506';
​
-- 授权 赋予权限
grant all privileges on *.* to bbh;
grant select on yan9.account to bbh;
grant insert,update,delete on yan9.account to bbh;
​
-- 回收权限
revoke all privileges on *.* from bbh;

15、MySQL查询

-- 1、 查询Student表中的所有记录的Sname、Ssex和Class列。
select Sname Ssex,Class from student;
​
-- 2、 查询教师所有的单位即不重复的Depart列。
select distinct Depart from teacher; -- distinct去重
​
-- 3、 查询Student表的所有记录。
select * from student;
​
-- 4、 查询Score表中成绩在60到80之间的所有记录。
-- between and 表示 >= and <=
select * from score where Degree BETWEEN 60 AND 80;
​
select * from score where Degree >=60 AND Degree<=80;
​
select * from score where Degree >=60 && Degree<=80;
​
-- 5、 查询Score表中成绩为85，86或88的记录。
select * from score where Degree in (85,86,88);
​
select * from score where Degree=85 or Degree=86 or Degree=88;
​
-- 6、 查询Student表中"95031"班或性别为"女"的同学记录。
select * from student where Class='95031' or Ssex='女';
​
-- 7、 以Class降序查询Student表的所有记录。
-- 排序 order by desc（降序）|asc（升序 默认升序）
select * from student ORDER BY Class DESC;
​
-- 8、 以Cno升序、Degree降序查询Score表的所有记录。
select * from score ORDER BY Cno ASC,Degree DESC;
​
-- 9、 查询"95031"班的学生人数。
select count(*) from student where Class='95031';
​
-- 10、 查询Score表中的最高分的学生学号和课程号。（子查询或者排序）
select Sno,Cno from score 
where Degree = (select max(Degree) from score);
​
-- 11、查询每门课的平均成绩。
-- 将cno值相同的分为一组 统计平均成绩avg()   sum()/count()
select Cno,avg(Degree) from score GROUP BY Cno;
​
-- 12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
-- count(sno)>5  统计函数筛选使用having
select Cno AVG(Degree) as AvgDegree 
from score
where Cno like '3%' 
GROUP BY Cno HAVING count(*) >=5;
​
-- 13、查询分数大于70，小于90的Sno列。
select DISTINCT Sno
from score 
where Degree > 70 and Degree>90;
​
-- 14、查询所有学生的Sname、Cno和Degree列。 
-- 笛卡尔积
-- select * from student，score; 
-- Sname as '姓名',Cno as '课程号',Degree as '成绩'
-- sname 姓名,cno 课程号,degree 成绩   二者表达内容相同
select sname 姓名,cno 课程号,degree 成绩
from student,score 
where student.sno=score.sno;
-- [inner]join 内连接 两张表匹配数据
select Sname as '姓名',Cno as '课程号',Degree as '成绩'
from student s 
join score sc on s.Sno=sc.Sno;
​
-- 15、查询所有学生的Sno、Cname和Degree列。
select sc.Sno,c.Cname,sc.Degree
from score sc join course c 
on sc.Cno=c.Cno;
​
-- 16、查询所有学生的Sname、Cname和Degree列。
select s.Sname,c.Cname,sc.Degree
from student s join score sc 
on s.Sno=sc.Sno 
join course c 
on sc.Cno=c.Cno;
​
-- 17、查询"95033"班学生的平均分。
select AVG(Degree) 
from score join student  
on score.Sno=student.Sno where Class='95033';
​
select score.* 
from score join student 
on score.Sno=student.Sno where Class='95033';
​
-- 18、 现查询所有同学的Sno、Cno和Degree列。
select Sno,Cno,Degree from score;
​
-- 19、查询所有选修"计算机导论"课程的"男"同学的成绩表。
select sc.* 
from student s
join score sc on sc.Sno=s.Sno
join course c on c.cno=sc.cno
where c.Cname='计算机导论' and s.Ssex = '男';
​
-- 20、查询成绩高于学号为"109"、课程号为"3-105"的成绩的所有记录。
select * from score where Degree>
(select Degree from score where Sno='109' and Cno='3-105');
​
-- 21、查询和学号为109的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
select Sno,Sname,Sbirthday from student 
where YEAR(Sbirthday)=(select YEAR(Sbirthday) from student where Sno='109');
​
-- 22、查询"张旭"教师任课的学生成绩。
select sc.* from score sc 
join course c on sc.Cno=c.Cno 
join teacher t on c.Tno=t.Tno
where t.Tname='张旭';
​
-- 23、查询选修某课程的同学人数多于5人的教师姓名。
select tname,course.cno,count(sno) from score
join course on course.cno = score.cno
join teacher on course.tno = teacher.tno
group by course.cno
having count(sno)>5
​
-- 查找选修人数大于5的课程号 根据课程号查找相关老师
select tname from teacher join course
on teacher.tno=course.tno
where cno in (select cno from score group by cno
having count(sno)>5)
​
-- 24、查询95033班和95031班全体学生的记录。
select * from student where Class in ('95033','95031');
​
-- 25、查询年龄小于50岁的所有老师记录。
select * from teacher 
where timestampdiff(year,Tbirthday,CURDATE())<50;
​
select teacher.*,year(now()=year(tbirthday)) 年龄 
from teacher where year(now())-year(tbirthday)<50;
​
-- 26、查询出"计算机系"教师所教课程的成绩表。
select sc.* from score sc
join course c on sc.Cno=c.Cno
join teacher t on c.Tno=t.Tno 
where t.Depart='计算机系';
​
-- 27、查询选修编号为"3-105"课程且成绩至少高于一个选修编号为"3-245"的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
select Cno,Sno,Degree from score 
where Cno='3-105' 
and Degree > ANY (select Degree from score where Cno='3-245') 
ORDER BY Degree DESC;
​
select Cno,Sno,Degree from score 
where Cno='3-105' 
and Degree > (select min(degree) from score where con = '3-245')
order by degree desc
​
-- 28、查询选修编号为"3-105"且成绩高于选修编号为"3-245"课程的同学的Cno、Sno
select Cno,Sno from score 
where Cno='3-105' 
and Degree>all(select Degree from score where Cno='3-245');
​
-- 31、查询所有教师和同学的name、sex和birthday。 
select Sname as name,Ssex as sex, Sbirthday as birthdayfrom student 
union -- 或union all
select Tname,Tsex ,Tbirthday  from teacher;
-- 只针对第一个表取别名就可以
​
-- 32、查询所有"女"教师和"女"同学的name、sex和birthday。   
-- union 可以筛选重复数据,  union all 不会筛选重复数据 
select Sname as name,Ssex as sex,Sbirthday as birthday from student where Ssex='女'
UNION
select Tname ,Tsex,Tbirthday from teacher where Tsex='女';
​
-- 33、查询成绩比该课程平均成绩低的同学的成绩表。 
-- 表的自连接查询
select sno,s1.cno,degree,s2.avg
from score s1 join (select cno,avg(degree) avg from score group by cno) s2
on s1.cno=s2.cno
where s1.degree < s2.avg
​
select sc.* from score sc 
where sc.Degree <(select AVG(Degree) from score where Cno=sc.Cno);
​
-- 34、查询所有任课教师的Tname和Depart。 
select distinct t.Tname,t.Depart from teacher t join course c on t.Tno=c.Tno;
​
-- 35、查询所有任课教师的Tsex和Tbirthday。 
select DISTINCT t.Tsex,t.Tbirthday from teacher t join course c on t.Tno=c.Tno;
​
-- 36、查询至少有2名男生的班号。
-- class ssex   student   group by ssec
select Class from student where Ssex='男' GROUP BY Class HAVING count(*)>=2;
​
-- 37、查询Student表中不姓"王"的同学记录。
select * from student where Sname not like '王%';
​
-- 38、查询Student表中每个学生的姓名和年龄。 
select Sname,timestampdiff(year,Sbirthday,CURDATE()) as age from student;
​
-- 40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
select * from student ORDER BY Class desc,sbirthday ASC;
​
-- 41、查询"男"教师及其所上的课程。
select t.*,c.Cname from teacher t 
join course c on t.Tno=c.Tno where t.Tsex = '男';
​
-- 42、查询最高分同学的Sno、Cno和Degree列。
select Sno,Cno,Degree from score
where Degree in (select max(Degree) from score);
​
-- 43、查询和"李军"同性别的所有同学的Sname。 自连接
select Sname from student 
where Ssex =(select Ssex from student where Sname='李军');
​
-- 44、查询和"李军"同性别并同班的同学Sname。 自连接
select Sname from student where 
Ssex=(select Ssex from student where Sname='李军') 
and Class=(select Class from student where Sname='李军') 
and Sname !='李军';
​
-- 45、根据成绩降序排序求前三名
select * from score order by degree desc limit 3;
-- 第5到第7名
select * from score order by degree desc limit 4,3; 
-- limit n    代表前n条数据
-- limit m,n  m代表偏移量  n代表查询条数

16、delete、drop、truncate区别

联系：三者都可以删除

区别：

一、drop、truncate 属于ddl语言（不记录日志，无法使用事务撤销）、delete属于dml语言

二、drop 删除数据库，删除表，删除结果，包含所有数据。drop table 表名;

truncate 清空表数据，不删除结构（不能添加删除条件，实现部分删除）

delete 删除数据，可以指定where条件，删除部分数据。

三、truncate 会影响auto_increment 自增字段，让自增字段从默认值开始重新生成。

delete 无法影响自增字段。

17、myisam和innodb区别

特性	InnoDB	MyISAM
事务支持	支持(ACID)	不支持
锁机制	行级锁	表级锁
外键支持	支持	不支持
崩溃恢复	支持(通过事务日志)	不支持
全文索引	MySQL 5.6+ 支持	支持

18、索引的分类

• 基本分类

  • 单列索引：一个索引只包含单个列

  • 多列索引：也叫组合索引，在创建索引时所关联的字段不是一个字段，而是多个字段

• 功能分类

  • 普通索引： 最基本的索引，它没有任何限制。

  • 唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值组合必须唯一。

  • 主键索引：一种特殊的唯一索引，不允许有空值。一般在建表时同时创建主键索引。

  • 组合索引：顾名思义，就是将单列索引进行组合，在创建索引时所关联的字段不是一个字段，而是多个字段。

  • 外键索引：只有InnoDB引擎支持外键索引，用来保证数据的一致性、完整性和实现级联操作。

  • 全文索引：快速匹配全部文档的方式。InnoDB引擎5.6版本后才支持全文索引。MEMORY引擎不支持。

  • 隐藏索引：Mysql.8新特性，不可见索引，隐藏索引不会被优化器使用，但任然需要进行维护。

  • 降序索引：Mysql.8新特性，只有 innoDB 存储引擎支持降序索引，只支持BTREE降序索引。

  • 函数索引：Mysql.8新特性，在 MySQL8.0.13 开始支持在索引中使用函数（表达式）的值。

• 结构分类

  • B+Tree索引 ：MySQL使用最频繁的一个索引数据结构，是InnoDB和MyISAM存储引擎默认的索引类型。

  • Hash索引 : MySQL中Memory存储引擎默认支持的索引类型。

19、索引的创建、删除如何实现

-- 创建索引
CREATE [UNIQUE|FULLTEXT] INDEX 索引名称
[USING 索引类型]
ON 表名(列名...);
​
-- 索引的删除
DROP INDEX 索引名称 ON 表名; 
ALTER TABLE 表名 DROP INDEX 索引名称;
-- 创建普通索引
CREATE INDEX idx_name ON user(name);
​
-- 创建唯一索引
CREATE UNIQUE INDEX idx_id ON user(id);
​
-- 主键索引：表在创建时设置了主键，那主键列自带主键索引
​
-- 创建组合索引
CREATE INDEX idx_sex_age ON user(sex,age);
​
-- 外键索引：表在创建时设置了外键，那外键列自带外键索引
​
-- 全文索引
CREATE FULLTEXT INDEX idex_full_phone ON user(phone);
​
-- 隐藏索引，主键不可以设置为隐藏索
-- 引应用场景：软删除、灰度发布。
--     软删除：不确定当前索引是否需要删除的时候，软删除，不会彻底删除，可以恢复索引，不需要重新创建，但需要维护。
--     灰度发布：测试索引，对当前数据库不会产生太多影响，确认有效后，可以取消隐藏，改变为正常索引。
CREATE INDEX idx_name ON user(name) invisible;
​
-- 降序索引,降序索引能够实现以order by设置的顺序对索引进行扫描，且不会损耗性能
-- SELECT * FROM user where id > 56789 ORDER BY age DESC; 不加降序索引和加上降序索引执行效率是不同的
CREATE INDEX idx_desc ON user(age desc);
​
-- 函数索引,当使用函数表达式作为过滤条件时，不会损耗性能
-- SELECT * from user where SUBSTR(name,1,2)='牛建'; 不加降序索引和加上降序索引执行效率是不同的
CREATE index idx_fun ON user((SUBSTR(name,1,2)));
​
-- 删除索引
DROP INDEX idx_name ON user;
ALTER TABLE user DROP INDEX idex_full_phone;

20、索引的失效场景

 1. 不符合最左前缀原则（针对联合索引） 
-- 假设有联合索引 INDEX idx_name (col1, col2, col3)
SELECT * FROM table WHERE col2 = 'value'; -- 索引失效
SELECT * FROM table WHERE col3 = 'value'; -- 索引失效 
​
2. 在索引列上使用函数或运算 
-- 假设 username 有索引
SELECT * FROM user WHERE LENGTH(username) = 5; -- 索引失效
SELECT * FROM products WHERE price + 10 > 100; -- 索引失效 
​
3. 使用不等于(!= 或 <>)判断 
SELECT * FROM user WHERE status != 1; -- 即使status有索引也可能失效 
​
4. 使用 IS NULL 或 IS NOT NULL 
SELECT * FROM user WHERE phone IS NULL; -- 可能导致索引失效
​
5. 使用 LIKE 以通配符开头 
SELECT * FROM user WHERE username LIKE '%john%'; -- 索引失效
SELECT * FROM user WHERE username LIKE 'john%'; -- 可以使用索引 
​
6. 类型转换（隐式类型转换）
-- 假设 phone 是 varchar 类型但有索引
SELECT * FROM user WHERE phone = 13800138000; -- 索引失效(数字与字符串比较) 
​
7. 使用 OR 条件不当
-- 假设 name 有索引而 age 没有
SELECT * FROM user WHERE name = 'John' OR age = 25; -- 整个查询索引失效 
​
8. 索引列参与计算 
SELECT * FROM orders WHERE YEAR(create_time) = 2023; -- 索引失效
-- 应改为范围查询
SELECT * FROM orders WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';
​
9. 使用 NOT IN 或 NOT EXISTS
SELECT * FROM user WHERE id NOT IN (1, 2, 3); -- 索引可能失效 
​
10. 数据量过小 当表中数据量非常少时，MySQL 可能选择全表扫描而非使用索引。 
​
11. 索引选择性过低 当索引列的值重复率过高（如性别字段只有男/女），MySQL 可能不使用索引。 
​
12. 使用了 ORDER BY 与索引顺序不一致 
-- 假设有索引 (col1, col2)
SELECT * FROM table ORDER BY col2, col1; -- 无法利用索引排序 检查索引使用情况 可以使用 EXPLAIN 分析查询是否使用了索引：  EXPLAIN SELECT * FROM user WHERE username LIKE 'john%'; 在结果中查看 possible_keys 和 key 字段，确认是否使用了预期的索引。

21、sql优化方案

1）mysql服务器优化

运行内存（8G、64G） 网络带宽

2）体系结构优化

优化服务器 总共运行内存 16G mysql运行过程中，服务器可用内存还有6G（资源没有得到最大化使用）

可能原因：安装mysql时，没有注意使用的内存参数，使用默认参数。

优化my.ini配置文件，调整mysql各种可用参数

3）查找慢sql

4）优化sql 提高查询效率

（1）分库分表 （oracle表分区） 数据库量极大超过千万级

（2）添加索引

（3）查找是否索引失效