MYSQL数据库、java面试

基础

三大范式：一）每个列都不可拆分 二）非主键完全依赖于主键 三）在二的基础上不依赖其他非主键

数据类型

整数类型：tinyint、smallint、mediumint、int、bigint

小数类型：float、double、decimal

日期类型：year、time、date、datetime、timestamp

文本、二进制类：char、varchar、tinyblob、blob、mediumblob、longblob、text、mediumtext、longtext、varbinary、binary

引擎

Innodb：提供数据库acid事务的支持。并且提供行级锁和外键的约束。设计目标就是处理大数据容量的数据库系统。

Myisam：不提供事务支持，不支持行级锁和外键。

区别：InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。

索引

索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。也是一种数据结构，实现通常是B树或者B+树。

索引覆盖：要查询的字段都建立过索引，引擎会直接在索引表中插叙文不会访问原始数据。

索引类型

主键索引：数据列不允许重复，不允许为NULL，一个表只有一个。

唯一索引：数据列不允许重复，允许为NULL，一个表可以有多个。数据列中的记录各不相同。

​ ALTER TABLE table_name ADD UNIQUE (column1,column2,…);

普通索引：基本的索引类型，没有唯一限制，允许为NULL。

​ ALTER TABLE table_name ADD INDEX index_name(column1,column2,…);

全文索引：ALTER TABLE table_name ADD FULTEXT(coolumn);

组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。

索引算法

BTree+算法：适用于查询=，>,<,<=,>=和between，还可以like

Hash算法：一次定位数据，检索效率远高于BTree索引。

对比：hash索引进行等值查询更快（一般），无法范围查询，不能进行模糊查询以及多列索引的最左前缀匹配，不够BTree+稳定，因为有hash冲突。

b树和b+树的区别：b树在内部节点和叶子节点都有值，随机查询快，但是I/O次数高，b+树更加的稳定。

聚簇索引：数据存储和索引放到一起，找到索引也就找到了数据

非聚簇索引：数据和索引分开存储，索引结构的叶子指向数据对应行。

联合索引：使用多个字段同时建立一个索引。

锁

行级锁

针对当前的行进行加锁。开销大，分为共享锁和排它锁。

表级锁

对当前操作的表进行加锁，资源消耗小。分为共享锁和排它锁。

页级锁

介于行级锁和表级锁之间。速度快，冲突多。

共享锁又叫读锁，排它锁又叫写锁。

悲观锁：查询完数据后将事务锁起来，直到提交后。

乐观锁：修改数据时将事务锁起来，version方式。

SQL优化

索引优化

垂直拆分：根据模块的耦合度，讲一个大的系统分为多个小的系统，即分布式系统；

水平拆分：数据量大的将同一个表的数据进行分块保存到不同的数据库中，这些数据库中的表结构完全相同。

读写分离

数据库优化

将字段很多的表分成多个表
增加中间表
增加冗余字段

基础命令

库级命令

• CREATE DATABASE 数据库名：创建数据库
• mysql -u root -p ：登录数据库
• drop database <数据库名>：删除数据库
• use 数据库名：使用数据库

表级命令

• CREATE TABLE table_name (column_name column_type)：创建表

• DROP TABLE table_name ：删除表

• INSERT INTO table_name ( field1, field2,…fieldN )
  VALUES
  ( value1, value2,…valueN )：插入数据

• select * from 表名：查询表数据

• SELECT * from 表名 WHERE 条件：根据条件查询数据

• UPDATE table_name SET field1=new-value1, field2=new-value2：更新表结构

• DELETE FROM table_name [WHERE Clause]：删除数据

• SELECT field1, field2,…fieldN
  FROM table_name
  WHERE field1 LIKE condition1 [AND [OR]] filed2 = ‘somevalue’：模糊查询

• SELECT expression1, expression2, … expression_n
  FROM tables
  [WHERE conditions]
  UNION [ALL | DISTINCT]
  SELECT expression1, expression2, … expression_n
  FROM tables
  [WHERE conditions]：查询两张表的数据集

• SELECT field1, field2,…fieldN FROM table_name1, table_name2…
  ORDER BY field1 [ASC [DESC][默认 ASC]], [field2…] [ASC [DESC][默认 ASC]]：排序

• SELECT column_name, function(column_name)
  FROM table_name
  WHERE column_name operator value
  GROUP BY column_name：分组

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
  LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
  RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

• IS NULL: 当列的值是 NULL,此运算符返回 true。
  IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
  <=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。

• mysql> SELECT name FROM person_tbl WHERE name REGEXP ‘^st’：查找name字段中以’st’为开头的所有数据

• mysql> SELECT name FROM person_tbl WHERE name REGEXP ‘ok$’：查找name字段中以’ok’为结尾的所有数据

• mysql> SELECT name FROM person_tbl WHERE name REGEXP ‘mar’：查找name字段中包含’mar’字符串的所有数据

• mysql> SELECT name FROM person_tbl WHERE name REGEXP ‘¹|ok$’：查找name字段中以元音字符开头或以’ok’字符串结尾的所有数据

• ALTER TABLE testalter_tbl DROP i：更改表

• CREATE INDEX indexName ON table_name (column_name)：创建索引

• ALTER table tableName ADD INDEX indexName(columnName)：添加索引

• DROP INDEX [indexName] ON mytable：删除索引

---

1. aeiou ↩︎