Mysql入门到实战

Mysql 视频地址：狂神说
sql 实战: 图解SQL面试题：经典50题

初识数据库

数据库意义：数据存储、数据管理

数据库分类

关系型数据库：SQL

• Mysql、Oracle、SqlServer、DB2、SQlLite
• 通过表与表，行与行之间的关系进行数据存储

非关系型数据库：NoSQL（not only）

Redis、MongDB

对象存储，通过对象的自身属性来决定

命令行

操作数据库

数据库操作

create database if not exists westos	--增
drop database if exists westos	--删
-- 用反单引号 `` 包裹的，表示表名或者字段名是一个特殊字符
use `school`
show databases	--查

数据库的列类型

数值

• tinyint 1字节
• smallint 2字节
• mediumint 3字节
• int 4字节
• bigint 8字节
• float 4字节
• double 8字节
• decimal 字符串型的浮点数，用于金融计算

字符串

• char 固定大小 0~255字节
• varchar 可变字符串 0~65535字节 常用String
• tinytext 微型文本 2^8-1
• text 文本串 2^16-1=65535字节

时间日期

• date YYYY-MM-DD 日期格式
• time HH:mm:ss 时间格式
• datetime YYYY-MM-DD HH;mm:ss
• timestamp 时间戳 1970.1.1 到现在的毫秒数
• year 年份

NULL

• 没有值，未知
• 不可用null做运算，结果为null

数据库字段属性

1. Unsigned 无符号

• 无符号的整数
• 不可为负数

2. zerofill 填充零

• 不足的位数用0来填充，int(3)——005

3. 自增

• 一般用来设计主键，必须是整数型
• 可自定义起始值和步长

4. not null 非空
5. 默认值

表操作

建表

格式

create table `student` (
	`id` int(4) not null auto_increment comment '学号' primary key，
    ...
) engine=innodb default charset=utf8

查看操作

show create database school	 -- 查看创建的数据库语句
show create table student	 -- 查看数据表的定义语句
desc student	-- 显示表结构

设置数据库表的字符集编码

charset=utf8

不设置的话，会是mysql默认的字符集编码 Latin1，不支持中文

可以在my.ini中配置默认编码（不建议）

修改/删除

--修改表名：ALTER TABLE 旧表名 RENAMEAS 新表名
alter table teacher rename as teacher1
--增加表的字段：ALTER TABLE 表名 ADD 字段名 列属性
alter table teacher add age int(11)

--修改表的字段（重命名，修改约束！）
--ALTER TABLE 表名 MODIFY 字段名 列属性[]
alter table teacher modify age varchar(6)	      --修改约束
--ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
alter table teacher change age age1 int(11)		--字段重命名

--删除表的字段：ALTER TABLE表名 DROP 字段名
alter table teacher drop age1

modify和change的区别

• modify：用来修改字段类型和约束，不用来字段重命名
• change：用来字段重命名

数据库存储引擎

用于存储、处理和保护数据的核心服务，可控制访问权限并快速处理事务

InnoDB 默认使用

MyISAM 早年使用

MEMORY用到的很少，因为它是把数据存到内存中，如果内存出现异常就会影响数据，生命周期短

	InnoDB	MyISAM
事务支持	支持	不支持
锁的粒度	行级锁	表级锁
数据行锁定	支持	不支持
外键约束	支持	不支持
全文索引	5.7后支持英文索引	支持
表空间大小	较大，约为2倍	较小

Innodb：安全性高、支持并发控制、事务处理、多表多用户操作

MyISAM：节省空间、速度快

存放位置

本质还是文件的存储

所有的数据库文件都存放在 data 目录下C:\ProgramData\MySQL\MySQL Server 5.5\data

mysql引擎在物理文件上的区别

• InnoDB在数据库表中只有一个*.frm文件，以及上级目录下的 ibdata1 文件
• MYISAM对应文件
  • *.frm表结构的定义文件
  • *.MYD数据文件（data）
  • *.MYI索引文件（index）

Mysql数据管理

DML（数据操作）语言

Inset into…values

语法格式

insert into 表名([字段1，字段2,字段3])
values('值1'),('值2'),('值3'),(...)

注意事项

1. 字段与字段\值与值之间用 英文逗号 隔开
2. 字段可省略，但字段与值要一一对应（可单一省略自增字段）

Update …set…where

语法格式

update 表名 set column_name = value,[column_name = value,...] 
where [条件]

常见的操作符=、<>、<、>、>=、<=、and、or

结果为真，则返回 1；为假，则返回 0；比较结果不确定则返回 NULL。

操作符	含义	结果
[not] between…and…	在某个范围内	[2,5]
[not] in(value1,value2)	在集合中，逻辑上与or相同	age in(‘11’,‘12’)
[not] like	模糊匹配（ %[多个] 、 _[单一] ）	name like %lin%

注意事项

1. where不要省略，省略了会将整列的数据全都修改
2. value，是一个具体的值，也可以是一个变量（set birthday=CURRENT_TIME，更改为当前时间 ）

Delete from…where

语法格式

delete from 表名 [where 条件] 

省略where会删除表数据，要避免用这个

truncate 删除表再重建

• DDL语句
• 表的结构和约束条件不会改变

truncate `teacher`

比较 delete 与 truncate

• 相同点：清空表，都不会改变表结构
• 不同点：
  • delete：可以回滚rockback
  • truncate：会重置自增、不影响事务

运算符

算术运算符

+ - * / %

在除法运算和模运算中，如果除数为0，将是非法除数，返回结果为NULL

比较运算符

比较结果为真，则返回 1，为假则返回 0，比较结果不确定则返回 NULL

=、<>、<、>、>=、<=、and、or

操作符	含义	结果
[not] between…and…	在某个范围内	[2,5]
[not] in(value1,value2)	在集合中，逻辑上与or相同	age in(‘11’,‘12’)
[not] like	模糊匹配（ %[多个] 、 _[单一] ）	name like %lin%
is [not] null	为空

逻辑运算符

如果表达式是真，结果返回 1。如果表达式是假，结果返回 0

运算符号	作用
NOT 或 !	逻辑非
AND	逻辑与
OR	逻辑或
XOR	逻辑异或

位运算符

位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。然后再将计算结果从二进制数变回十进制数

运算符号	作用
&	按位与
|	按位或
^	按位异或
!	取反
<<	左移
>>	右移

DQL查询 Select 数据（重点 ）

DQL（data query language）数据查询语言

语法

select [all | distinct]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
from table_name [as table_alias]
	[left | right | inner join table_name2]	-- 联合查询
	[where ..]		-- 指定结果需要满足的条件
	[group by ...] 	 -- 指定结果按照那个字段来分组
	[having] 	-- 过滤分组的记录必须按照哪个字节来分组
	[order by]	 -- 指定查询记录按照一个或多个条件排序 order by age asc正序/desc倒序
	[limit {[offset,]row_count | row_countOffSET offset}];	--指定查询的记录从哪条至哪条

• 前后顺序不可改变
• {}必须有，[]可选

指定查询字段

基础查询

SELECT 字段 [as 字段别名] from 表 [as 表别名]

查询指定字段的数据，并起别名

concat(a,b) 拼接

select concat(a,b) [as 字段别名] from 表

查询 字段b 中的数据，并将a和b拼接显示concat('姓名:',name)

distinct 去重

select distinct 字段 from 表

查询系统版本

select version()

加入表达式计算 + - * / %

select 表达式 from 表

select 100+1 as '结果' 
select `StuNum`+1 as '分数' from student

where条件子句

搜索的条件由一个或者多个表达式组成！结果布尔值

逻辑运算符

运算符	语法	描述
and &&	a and b	与
or ||	a or b	或
not !	not a	非

模糊查询，比较运算符

运算符	语法	描述
is [(]not] null	a is [not] null	为null[不为null]，结果为真
between	a between b and c	范围
like	a like b	结合% _
in	a in(a1,a2,…)	逻辑上类似 or

嵌套查询

在 where 内嵌套查询语句（套娃）

联表查询 XX join…on

• right join

• inner join

• left join

• 当条件放在on后面时，无论on条件的真假，都会返回左表的值；

  当条件放在where后面时，只有满足条件的记录会返回

select A.字段1，字段2,...
from 表1 as A 
inner[right/left] 表2 as B
on A.字段1 = B.字段2

（as 可省略）

操作	描述
inner join	获取两个表中字段匹配关系的记录（交集）
left join	左表中返回所有值，即使右表没有匹配
right join	右表中返回所有值，即使左表没有匹配

分页和排序 limit

limit 起始值(下标从0开始)，页长

SELECT * from teacher
order by age asc/desc
LIMIT 0,5

--第N页		(n-1)*pageSize,pageSize
--pageSize：页面大小
--(n-1)*pageSize：起始值
--n：当前页
--总页数 = 数据总数 / 页面大小 （向上取整）

分组过滤 group by…having

对查询的结果分组显示

where 后面不能接聚合函数，用having来代替 where

Mysql函数

函数大全

select 函数 from 表

常用函数

函数	含义	结果
数学运算
ceiling()	向上取整	ceiling(9.1) = 10
floor()	向下取整	floor(9.1) = 9
abs()	绝对值	abs(-10) = 10
rand()	[0,1)的随机数
字符串函数
char_length()	字符串长度
concat()	拼接字符串
lower()	转小写
upper()	转大写
instr()	返回索引	instr(‘012345’,‘45’) = 4
replace()	替换出现的指定字符	replace('0123,‘01’,‘00’)=0023
substr()	截取指定字符串(下标从1开始)	substr(‘0123’,1,2) = 01
reverse()	反转
时间和日期函数
current_time()	获取当前时间
current_date()	获取当前日期
now()	获取当前日期和时间
year()	获取年	year(now())
系统函数
system_user()	系统用户
version()	版本

聚合函数

函数	含义	结果
count()	统计	count(字段) 忽略null
		count(1) 用1代表行，并统计，不会忽略null
		count(*) 统计行数，不会忽略null
sum()	求和
avg()	取平均
max()	最大
min()	最小

where 中不能使用聚合函数，一般分组用 group by...having..

数据库MD5加密 MD5()

• 主要用于
• 不可逆，具体值的MD5是一样的

事务 transaction

概念

要么都成功，要么都失败

事务原则 : ACID

• 原子性（Atomicity）：一起成功，或一起失败
• 一致性（Consistency）：数据库总是从一个一致性的状态转换到另一个一致性的状态。
• 隔离性（Isolation）：一个事务所做的修改在最终提交以前，对其他事务是不可见的。
• 持久性（Durability）：事务没有提交，恢复到原来状态；事务已经提交，持久化到数据库（提交则不可逆）

隔离所导致的问题

• 脏读：一个事务读取了另一个事务未提交的数据
• 不可重复读：在一个事务内，多次读取，会读取到不同的数据（事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致）
• 幻读（虚读）：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

不可重复读侧重于修改，幻读侧重于新增或删除

执行事务

mysql默认是开启事务自动提交

set autocommit = 0	--关闭 
set autocommit = 1	--开启（默认开启）

流程

• 关闭自动提交 set autocommit = 0;
• 事务开启start transaction 标记一个事务的开始，之后的sql都在同一个事务内
  • 在内部可以增删改查
• 提交：持久化（成功）commit;
• 回滚（失败）rollbak;
• 事务结束set autocommit = 1; 开启自动提交

保存点

savepoint 保存点名	-- 设置一个事务的保存点
rollback to savepoint 保存点名	-- 回滚到保存点
release savepoint 保存点名	-- 撤销保存点

隔离级别

• 读未提交（read uncommitted）、读已提交(read committed)、可重复读（repeatable read）、串行化（serializable）
• Mysql默认：可重复读

互联网项目中要用 读已提交

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
读已提交（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

索引

索引：帮助 mysql 高效获取数据的数据结构（B+tree）
索引常见面试题

索引的数据结构

B+树 https://blog.codinglabs.org/articles/theory-of-mysql-index.html

hash类型的索引：查询单条快，范围查询慢

b+tree类型的索引：数据有序,范围查询，层数越多，数据量指数级增长

分类

• 主键索引（primary key）
  • 唯一标识、主键不可重复、只能 有一个列作为主键
• 唯一索引（unique key）
  • 避免重复的列出现，可以重复，多个列都可以标识唯一索引
• 常规索引（key/index）
  • 默认，用index/key来标识
• 全文索引（fulltext）
  • 快速定位数据

测试索引

插入100万数据（了解）

-- 插入100万数据.
DELIMITER $$	-- 写函数之前必须要写，标志

CREATE FUNCTION mock_data ()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i<num DO
INSERT INTO `app_user`(`name`,`eamil`,`phone`,`gender`)VALUES(CONCAT('用户',i),'19224305@qq.com','123456789',FLOOR(RAND()*2));
SET i=i+1;
END WHILE;
RETURN i;
END;

SELECT mock_data() -- 执行此函数 生成一百万条数据

索引在小数据量的时候，用处不大，但是在大数据的时候，区别十分明显~

/*
#方法一：创建表时
    　　CREATE TABLE 表名 (
                字段名1  数据类型 [完整性约束条件…],
                字段名2  数据类型 [完整性约束条件…],
                [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
                [索引名]  (字段名[(长度)]  [ASC |DESC])
                );
#方法二：CREATE在已存在的表上创建索引
        CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名
                     ON 表名 (字段名[(长度)]  [ASC |DESC]) ;
#方法三：ALTER TABLE在已存在的表上创建索引
        ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                             索引名 (字段名[(长度)]  [ASC |DESC]) ;
                            
                            
#删除索引：DROP INDEX 索引名 ON 表名字;
#删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;
#显示索引信息: SHOW INDEX FROM student;
*/
 
/*增加全文索引*/
ALTER TABLE `school`,`student` ADD FULLTEXT INDEX `studentname` (`StudentName`);
 
/*EXPLAIN : 分析SQL语句执行性能*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';
 
/*使用全文索引*/
-- 全文搜索通过 MATCH() 函数完成。
-- 搜索字符串作为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行，MATCH() 返回一个相关性值。即，在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。
EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('love');
 
/*
开始之前，先说一下全文索引的版本、存储引擎、数据类型的支持情况
MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。
*/

索引准则

• 索引不是越多越好
• 不要对经常变动的数据加索引
• 小数据量的表建议不要加索引
• 索引一般应加在查找条件的字段

三大范式

第一范式 (1 NF)

原子性：保证每一列都不可再分

第二范式(2 NF)

• 满足第一范式
• 要求每个表只描述一件事情

第三范式(3 NF)

• 满足第一范式和第二范式
• 数据表中的每一列数据都和主键直接相关，而不能间接相关。

规范性和性能的关系

关联查询的表不超过三张表

JDBC

https://www.bilibili.com/video/BV1NJ411J79W?p=39

参考
数据库常见面试题