mysql基础知识，mysql面试必会100道题
大家好，今天给各位分享mysql基础知识的一些知识，其中也会对mysql面试必会100道题进行解释，文章篇幅可能偏长，如果能碰巧解决你现在面临的问题，别忘了关注本站，现在就马上开始吧！
数据库(mysql)关键知识
Mysql是目前互联网使用最广的关系数据库，关系数据库的本质是将问题分解为多个分类然后通过关系来查询。一个经典的问题是用户借书，三张表，一个用户，一个书，一个借书的关系表。当需要查询某个用户借书情况或者是书被那些人借了，就用关系查询来实现。
关系数据库范式
来自英文Normal form，简称NF。要想设计—个好的关系，必须使关系满足一定的约束条件，满足这些规范的数据库是简洁的、结构明晰的，同时，不会发生插入(insert)、删除(delete)和更新(update)操作异常。总共有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。
1NF是指数据库表的每一列都是不可分割的原子数据项。2NF必须满足1NF，要求数据库表中的每行记录必须可以被唯一地区分。3NF在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）。BCNF是在3NF基础上，任何非主属性不能对主键子集依赖（在3NF基础上消除对主码子集的依赖），满足BCNF不再会有任何由于函数依赖导致的异常，但是我们还可能会遇到由于多值依赖导致的异常。4NF的定义很简单：已经是BC范式，并且不包含多值依赖关系。5NF处理的是无损连接问题，这个范式基本没有实际意义，因为无损连接很少出现，而且难以察觉。而域键范式试图定义一个终极范式，该范式考虑所有的依赖和约束类型，但是实用价值也是最小的，只存在理论研究中。
Catalog和Schema
是数据库对象命名空间中的层次，主要用来解决命名冲突的问题。从概念上说，一个数据库系统包含多个Catalog，每个Catalog又包含多个Schema，而每个Schema又包含多个数据库对象（表、视图、字段等）。但是Mysql的数据库名就是Schema，不支持Catalog。
Mysql的数据库引擎主要有两种MyISAM和InnoDB，MyISAM支持全文检索，InnoDB支持事务。
SQL中的通配符‘%’代表任意字符出现任意次数。‘_’代表任意字符出现一次。SQL与正则表达式结合查询一般用在WHERE table_name REGEXP'^12.34'。子查询是从里到外执行。
数据库联结（join）涉及到外键，外键是指一个表的列是另一个表的主键，那么它就是外键。笛卡尔积联结（不指定联结条件时）生成的记录条目是单纯的第一个表的行乘以第二个表的列数。用得最多的是等值联结也叫内部联结。
高级联结还有自连接，是指查询中的两张表是同一张表，它通常作为外部语句用来代替从相同表中检索数据时使用的子查询。自然联结使每个列只返回一次。外部联结是指联结包含了那些在相关表中没有关联行的行。例如列出所有产品及其订购数量，包括没有人订购的产品。LEFT OUTER JOIN指选择左边表的所有行。
组合查询是指采用UNION等将两个查询结果取并集。
视图是查看存储在别处的数据的一种工具，它本身并不包含数据，因此表的数据修改了，视图返回的数据也将随之修改，因此如果使用了复杂或嵌套视图会对性能有较大的影响。视图的作用之一是隐藏复杂的SQL通常会涉及到联结查询。
存储过程类似于批处理，包含了一条或多条SQL语句。语法：
CREATE PROCEDURE name（）
BEGIN
SQL
END
-------------------------
CALL name（）//来调用存储过程
游标有DECLARE定义，游标与存储过程是绑定的，存储过程处理完成，游标就会消失。游标被打开后可以使用FETCH语句访问每一行。
触发器是在某个时间发生时自动执行某条SQL语句。语法：
CREATE TRIGGER name AFTER INSERT ON talbe_name FOR EACH ROW
事务处理可以维护数据库的完整性，保证批量的操作要么完全执行，要么完全不执行。包括事务、回退、提交、保留点几个关键术语。ROLLBACK只能在一个事务处理内使用。他不能回退CREATE和DROP操作。使用COMMIT保证事务提交。复杂的事务处理需要部分提交或回退，因此我们需要使用保留点SAVEPOINT。可以使用ROLLBACK TO savepoint_name。保留点越多越好。保留点在事务执行完成后自动释放。
mysql 常用命令手册
之前介绍了一系列的mysql基础知识，今天我们将MySQL常用的功能命令整理出来，供大家参考使用！
1.导出整个数据库
mysqldump-u用户名-p–default-character-set=latin1数据库名>导出的文件名(数据库默认编码是latin1)
mysqldump-u wcnc-p smgp_apps_wcnc> wcnc.sql
2.导出一个表
mysqldump-u用户名-p数据库名表名>导出的文件名
mysqldump-u wcnc-p smgp_apps_wcnc users> wcnc_users.sql
3.导出一个数据库结构
mysqldump-u wcnc-p-d–add-drop-table smgp_apps_wcnc>d:wcnc_db.sql
-d没有数据–add-drop-table在每个create语句之前增加一个drop table
4.导入数据库
A:常用source命令
进入mysql数据库控制台，
如mysql-u root-p
mysql>use数据库
然后使用source命令，后面参数为脚本文件(如这里用到的.sql)
mysql>source wcnc_db.sql
B:使用mysqldump命令
mysqldump-u username-p dbname< filename.sql
C:使用mysql命令
mysql-u username-p-D dbname< filename.sql
启动与退出
1、进入MySQL：启动MySQL Command Line Client（MySQL的DOS界面），直接输入安装时的密码即可。此时的提示符是：mysql>
2、退出MySQL：quit或exit
库操作
1、创建数据库
命令：create database<数据库名>
例如：建立一个名为sqlroad的数据库
mysql> create database sqlroad;
2、显示所有的数据库
命令：show databases（注意：最后有个s）
mysql> show databases;
3、删除数据库
命令：drop database<数据库名>
例如：删除名为 sqlroad的数据库
mysql> drop database sqlroad;
4、连接数据库
命令：use<数据库名>
例如：如果sqlroad数据库存在，尝试存取它：
mysql> use sqlroad;
屏幕提示：Database changed
5、查看当前使用的数据库
mysql> select database();
6、当前数据库包含的表信息：
mysql> show tables;（注意：最后有个s）
表操作，操作之前应连接某个数据库
1、建表
命令：create table<表名>(<字段名><类型> [,..<字段名n><类型n>]);
mysql> create table MyClass(
> id int(4) not null primary key auto_increment,
> name char(20) not null,
> sex int(4) not null default’′,
> degree double(16,2));
2、获取表结构
命令：desc表名，或者show columns from表名
mysql>DESCRIBE MyClass
mysql> desc MyClass;
mysql> show columns from MyClass;
3、删除表
命令：drop table<表名>
例如：删除表名为 MyClass的表
mysql> drop table MyClass;
4、插入数据
命令：insert into<表名> [(<字段名>[,..<字段名n> ])] values(值)[,(值n)]
例如，往表 MyClass中插入二条记录,这二条记录表示：编号为的名为Tom的成绩为.45,编号为的名为Joan的成绩为.99，编号为的名为Wang的成绩为.5.
mysql> insert into MyClass values(1,’Tom’,96.45),(2,’Joan’,82.99),(2,’Wang’, 96.59);
5、查询表中的数据
11)、查询所有行
命令：select<字段，字段，...> from<表名> where<表达式>
例如：查看表 MyClass中所有数据
mysql> select* from MyClass;
12）、查询前几行数据
例如：查看表 MyClass中前行数据
mysql> select* from MyClass order by id limit 0,2;
或者：
mysql> select* from MyClass limit 0,2;
6、删除表中数据
命令：delete from表名 where表达式
例如：删除表 MyClass中编号为的记录
mysql> delete from MyClass where id=1;
7、修改表中数据：
update表名 set字段=新值,…where条件
mysql> update MyClass set name=’Mary’where id=1;
8、在表中增加字段：
1命令：alter table表名 add字段类型其他;
3例如：在表MyClass中添加了一个字段passtest，类型为int(4)，默认值为
5mysql> alter table MyClass add passtest int(4) default’′
9、更改表名：
命令：rename table原表名 to新表名;
例如：在表MyClass名字更改为YouClass
mysql> rename table MyClass to YouClass;
更新字段内容
update表名 set字段名=新内容
update表名 set字段名= replace(字段名,’旧内容’,新内容’)
update article set content=concat(‘’,content);
字段类型和数据库操作
1．INT[(M)]型：正常大小整数类型
2．DOUBLE[(M,D)] [ZEROFILL]型：正常大小(双精密)浮点数字类型
3．DATE日期类型：支持的范围是-01-01到-12-31。MySQL以YYYY-MM-DD格式来显示DATE值，但是允许你使用字符串或数字把值赋给DATE列
4．CHAR(M)型：定长字符串类型，当存储时，总是是用空格填满右边到指定的长度
5．BLOB TEXT类型，最大长度为(2^16-1)个字符。
6．VARCHAR型：变长字符串类型
7.导入数据库表
创建.sql文件
先产生一个库如auction.c:mysqlbin>mysqladmin-u root-p creat auction，会提示输入密码，然后成功创建。
导入auction.sql文件
c:mysqlbin>mysql-u root-p auction< auction.sql。
通过以上操作，就可以创建了一个数据库auction以及其中的一个表auction。
8．修改数据库
在mysql的表中增加字段：
alter table dbname add column userid int(11) not null primary key auto_increment;
这样，就在表dbname中添加了一个字段userid，类型为int(11)。
9．mysql数据库的授权
mysql>grant select,insert,delete,create,drop
on*.*(或test.*/user.*/..)
to用户名@localhost
identified by‘密码’；
如：新建一个用户帐号以便可以访问数据库，需要进行如下操作：
mysql> grant usage
-> ON test.*
-> TO testuser@localhost;
Query OK, 0 rows affected(0.15 sec)
此后就创建了一个新用户叫：testuser，这个用户只能从localhost连接到数据库并可以连接到test数据库。下一步，我们必须指定testuser这个用户可以执行哪些操作：
mysql> GRANT select, insert, delete,update
-> ON test.*
-> TO testuser@localhost;
Query OK, 0 rows affected(0.00 sec)
此操作使testuser能够在每一个test数据库中的表执行SELECT，INSERT和DELETE以及UPDATE查询操作。现在我们结束操作并退出MySQL客户程序：
mysql> exit
DDL操作
1:使用SHOW语句找出在服务器上当前存在什么数据库：
mysql> SHOW DATABASES;
2、创建一个数据库MYSQLDATA
mysql> Create DATABASE MYSQLDATA;
3:选择你所创建的数据库
mysql> USE MYSQLDATA;(按回车键出现Database changed时说明操作成功！)
4:查看现在的数据库中存在什么表
mysql> SHOW TABLES;
5:创建一个数据库表
mysql> Create TABLE MYTABLE(name VARCHAR(20), sex CHAR(1));
6:显示表的结构：
mysql> DESCRIBE MYTABLE;
7:往表中加入记录
mysql> insert into MYTABLE values(“hyq”,”M”);
8:用文本方式将数据装入数据库表中（例如D:/mysql.txt）
mysql> LOAD DATA LOCAL INFILE“D:/mysql.txt”INTO TABLE MYTABLE;
9:导入.sql文件命令（例如D:/mysql.sql）
mysql>use database;
mysql>source d:/mysql.sql;
10:删除表
mysql>drop TABLE MYTABLE;
11:清空表
mysql>delete from MYTABLE;
12:更新表中数据
mysql>update MYTABLE set sex=”f”where name=’hyq’;
MySql 三大知识点——索引、锁、事务
MySQL的三大核心知识点——索引、锁、事务，是数据库性能优化和并发控制的关键，以下是详细解析：
一、索引定义：索引是数据库的“目录”，通过快速定位数据提升查询效率，类似书籍目录通过页码查找内容。
优点：
天生排序：数据按索引字段有序存储。
快速查找：通过B+树结构减少磁盘IO次数。
缺点：
占用空间：索引数据需额外存储。
降低写入速度：数据更新时需同步维护索引。
适用场景：
小表（全表扫描更快）、中大表（推荐使用）、超级大表（索引效果有限）。
索引类型：
实现方式：
聚集索引：数据按索引顺序物理存储（InnoDB主键默认聚集）。
辅助索引：非聚集索引，存储主键值而非数据本身。
功能分类：
普通索引：无约束，基础索引类型。
唯一索引：确保字段值唯一，允许空值（仅一个）。
主键索引：特殊的唯一索引，不允许空值。
复合索引：多列组合索引，遵循最左前缀原则。
外键索引：InnoDB特有，维护表间数据一致性。
全文索引：仅支持InnoDB/MyISAM，对英文文本分词检索（生产环境常用ES/Solr替代）。
B+树结构：
非叶子节点：仅存储索引和指针，不存数据，提高单节点索引容量。
叶子节点：存储数据和指向相邻节点的指针，形成链表结构。
性能优势：
3层B+树可存储约10亿索引，查询仅需2-3次磁盘IO。
支持范围查询和排序操作。
设计建议：
主键尽量短（如自增INT），避免UUID导致页分裂。
分库分表时，可保留自增主键，逻辑主键作为唯一索引。
B树：非叶子节点存储数据，索引容量受限
B+树：非叶子节点仅存索引，叶子节点存储数据并链表连接二、锁机制核心目标：保证并发事务下的数据一致性，通过锁的粒度和类型控制访问冲突。
锁类型：
按操作分：
共享锁（S锁）：读锁，允许多事务并发读取。
排他锁（X锁）：写锁，独占资源，禁止其他事务读写。
意向锁：表级锁，标记行锁存在，避免全表扫描检查。
按策略分：
悲观锁：通过SELECT... FOR UPDATE显式加锁。
乐观锁：通过版本号（如version字段）实现CAS机制。
锁粒度：
表锁：锁定整张表，并发度低（如MyISAM引擎）。
页锁：锁定数据页，中间粒度（BerkeleyDB引擎）。
行锁：锁定单行，高并发（InnoDB引擎）。
锁算法：
Record Lock：锁定单行记录。
Gap Lock：锁定索引间隙，防止幻读。
Next-Key Lock：Record Lock+ Gap Lock，锁定记录及间隙（InnoDB默认RR隔离级别使用）。
特殊场景：
默认读操作：使用MVCC（多版本并发控制）实现一致性非锁定读，不上锁。
手动加锁：SELECT... FOR UPDATE（X锁）或LOCK IN SHARE MODE（S锁）实现一致性锁定读。
SERIALIZABLE级别：通过串行化执行避免丢失更新（如转账场景）。
行锁失效场景：
查询未命中索引时，优化器可能降级为表锁。
SERIALIZABLE级别防止丢失更新三、事务ACID特性：
原子性（Atomicity）：通过Undo Log回滚未提交变更。
一致性（Consistency）：由原子性、隔离性、持久性共同保证。
隔离性（Isolation）：通过MVCC和锁实现，解决脏读、不可重复读、幻读。
持久性（Durability）：通过Redo Log保证事务提交后数据不丢失。
隔离级别：
未提交读（RU）：允许脏读（事务未提交的修改可见）。
已提交读（RC）：避免脏读，但允许不可重复读（每次读生成新Read View）。
可重复读（RR）：避免脏读和不可重复读（事务内使用同一Read View），MySQL通过MVCC和Next-Key Lock避免幻读。
串行化（SERIALIZABLE）：完全串行执行，避免所有并发问题（性能最低）。
MVCC实现：
隐藏字段：每行记录包含事务ID、回滚指针、删除标记等。
Read View：事务启动时生成，决定可见数据版本。
Undo Log：存储数据历史版本，形成版本链。
隔离级别与并发问题关系总结索引：优先使用短主键和复合索引，避免超级大表索引失效。锁：根据业务选择合适粒度，高并发场景慎用表锁。事务：合理设置隔离级别，RR级别通常能满足需求，SERIALIZABLE仅用于极端场景。通过理解这三大核心机制，可有效优化MySQL性能并避免并发问题。
好了，关于mysql基础知识和mysql面试必会100道题的问题到这里结束啦，希望可以解决您的问题哈！