MySQL复习——B站

简介

数据库用于存储数据，软件体系的核心

数据库分类：关系型和非关系型
关系：Mysql、Oracle、SqlServer、DB2、SQLlite
非关系：Redis、mongDB

关系型存储的是表，包含行和列
非关系型：存储对象，对象中的字段由自身的属性来决定，实时更新结构（适用于热搜等业务）

DBMS：数据库管理系统

使用语言：SQL

优点：体积小、速度快、学习成本低，可做集群

sql注释：单行：--，多行：/* */

数据库存储引擎：

1. InnoDB 存储引擎：它是 MySQL 5.5 版本之后默认的存储引擎，最大的特点是支持事务、行级锁定、外键约束等，不支持全文索引，表较大。
2. MyISAM 存储引擎：在 MySQL 5.5 版本之前是默认的存储引擎，不支持事务，也不支持外键，最大的特点是速度快，占用资源少，支持全文索引，表较小。

InnoDB和MyISAM区别：
表：

	InnoDB	MyISAM	补充
事务	支持，每条语句都会当作事务提交，所以速度较慢，对速度要求高建议取消自动提交，使用手动提交事务：begin和commit
外键	支持
索引类型	聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。	非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
是否保存总行数		有，count(*)速度很快，直接读取变量值即可，不能有where	InnoDB由于事务的原因，不同事务统计到的行数不一致，所以没必要建一个变量用于保存数据，如果需要知道大致行数可以使用：show table status
全文索引	5.7后支持	支持
压缩		可以进行查询操作
锁	支持表、行（默认）级	支持表级	前者行锁需要名中索引，否则退化为表锁
一定要有唯一索引	必须有，没有的话使用隐藏列来充当	可以没有
文件类型	frm：表定义文件；ibd：数据文件	frm：表定义文件；myd：数据文件；myi：索引文件

索引状况：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针


如何选择：

1. 如果支持事务，InnoDB
2. 如果查询绝大多：MyISAM
3. 系统崩溃后的恢复难度：InnoDB易，MyISAM难
4. 没啥需求：InnoDB

InnoDB推荐使用自增ID作为主键？
保证每次插入B+索引从右边扩展，避免B+树频繁合并和分裂，效率高。使用随机字符串则效率低。

InnoDB四大特性：
插入缓冲（insert buffer),二次写(double write),自适应哈希索引(ahi),预读(read ahead)

安装

尽量不要使用exe，协助会有残留已经注册表问题
使用zip包安装

1. 下载解压（这里用的5.7）

2. 配置mysql环境变量，bin目录

3. 新建mysql配置文件：my.ini

   [mysqld]
   basedir=D:\mysql\
   datadir=D:\mysql\data\
   port=3306
   skip-grant-tables
   

   注意：根据自身安装位置做相应的调整。最后一项是指明初次启动无需密码验证

4. 安装：启动管理员CMD，mysql bin目录下运行：mysqld -install

5. 初始化目录：mysql bin目录下运行：mysqld -initialize-insecure --user=mysql

6. 启动mysql服务：net start mysql

7. 启动mysql：mysql -u root -p，注意-p后不要有空格，然后直接两次回车

8. 设置mysql密码：update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';

9. 刷新权限：flush privileges

10. 注释掉ini文件中的最后一行，即可重启正常使用

net stop mysql
net start mysql
mysql -uroot -p123456

注意：如果服务有问题可以使用：sc delete mysql删除mysql服务，重新安装

数据库基础知识

数据库模型

三要素： 结构：数据库–>数据表–>字段

完整性：

• 域约束：规定字段允许值
• 主码约束：唯一标识，可组合，不能为NULL
• 外码约束：该表中非主码的外表主码，表间纽带

基本数据类型

整数

类型	字节	范围（无符号）	范围
tinyint	1	0-255	-128-127
smallint	2	0-65535	…
mediumint	3	0-2^24-1	…
int	4	0-2^32	…f
bigint	8	…	…

注意：int(n)中的n仅仅表示显式的宽度，不是能存的值的大小！

浮点

类型	字节	默认
float	4	0
double	8	0
decimal(m,d)	m+2	0

注意：M：数据总长度，D：小数位数，decimal是字符串形式的浮点数，用于金融计算
decimal(5,2)：123.45可以显示

日期与实践

类型	字节	范围	默认值
Year	1	1910-2155	Null
Data	4	1000-01-01,9999-12-31	Null
Time	3	-838:59:59,838,59,59	Null
Datetime	4	1000-01-01 00:00:00,9999-12-31	Null
Timestamp	4	同上	当前时间

注意：year类型支持两位数的整数插入，00-69 将转换为2000-2069之间，70-99 将转换为1970-1999之间

字符串

类型	说明
char	定长0-255
varchar	变长0-65535
tinytext	微型文本2^8-1
text	大文本2^16-1（tinytext,text,mediumtext,longtext）
enum	单元素
set	可取多元素

注意：字符串常量：ASCII（单字节），Unicode（两字节）

二进制

类型	说明
binary(m)	允许长度为0-m，字节长度位m
varbinary(m)	允许长度为0-m，字节数为m+1
bit(m)	允许长度0-m位，字节长度最多255
tinyblob	变长，最多255字节
blob	变长，最多2^16-1 ,64k
mediumblob	变长，最多2^24-1
longblob	变长，最多2^32-1 ,4G

二进制可以用来存储图片或者视频，但一般为了性能存储网络地址即可

常量类型

MySQL常量：

• 字符串常量：ASCII(单字节)&UNICODE(两字节)
• 数值常量
• 十六进制常量
• 日期时间常量
• 二进制常量
• NULL

数据库字段属性

unsigned：无符号整数，不能为符数
zerofill：不足使用0填充
auto_increment：自动加一
not null：非空（只是判断是否null）
default：默认值

每个表都必须存在以下五个字段

id：主键
version：乐观锁
is_delete：伪删除
gmt_create：创建时间
gmt_update：修改时间

数据库操作

创建数据库

create database [if not exists] <数据库名> [character set utf8] [collate utf8_bin]
注意：数据库后character为字符集，collate为校验规则，mysql默认编码不支持中文，所以一般都要设置编码格式
create database shop character set utf8 collate utf8_general_ci

查看数据库

show databases

查看数据库创建参数：show create databases <数据库名>

删除数据库

drop databases [if exists] <数据库名>

修改数据库

alter database <数据库名> 属性(character) set 值(gbk);

切换数据库

use <数据库名>

查看当前数据库：select database()

数据库表操作

创建表

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] <表名> ([<字段><数据类型>[<完整性约束>]]...[<表完整性约束>])

完整性约束：NULL | NOT NULL | PRIMARY KEY | UNIQUE

* 例：

create table Student(
id char(9) not null,
name char(12) not null,
primary key(id)
);`

create table Student(id char(9) not null,name char(12) not null,primary key(id,name));
# 在cmd中：
mysql> create table student 
    -> ( 
    -> id char(4) not null primary key comment '主键', 
    -> name char(5) not null, 
    -> major char(8) null, 
    -> sex tinyint(1) not null default 1 
    -> )engine=innodb default charset=utf8; 

注意：auto_increment必须加在主键上

删除表

drop [TEMPORARY] table [IF EXISTS] <表名1> <表名2> <表名3>
例：drop table Student;

查看表结构

describe(desc) <表名>;
查看建表语句：show create table 表名;

修改表结构

alter table <表名>...

alter [IGNORE] table <表名> 
    [alter column <字段><数据类型>] 
    [add column <字段><数据类型>[<完整性约束>]] 
    [drop column <字段>] 
    [drop constraint <约束>] 

注意：IGNORE：关键字，若修改后，出现重复关键字，则使用第一行，冲突行删除。
例如：

alter table xxx 
	  rename xxx; 修改表名字 
	  alter 字段 新类型
	  change 旧字段 新字段 新类型（修改字段名） 
	  modify 字段   新类型（可用来增加或删除约束） 
	  add    字段   类型   约束 [after 字段][first] 
	  drop   字段 
	  character set gbk; 
	  
alter table ooo modify yyy int auto_increament[=10];初始值设置从10开始 

alter talbe ooo modify yyy 类型 default zzz;修改yyy类型并给定默认值为zzz 

alert table xxx add constraint `FK_gradid` foreign key(`gradid`) references `grade`(`gradid`);为xxx建立外键关系

注意：外键关系删除时，需要先删除依赖该外键的从表

注意：数据库级别的外键称为物理外键，一般不建议，建议在程序级别上实现外键。（同样不建议使用级联）因为在delete操作或者update操作都需要考虑外键约束，开发会增加复杂度，并且测试也很不方便

修改表名

rename table xxx to yyy;

复制表

create [TEMPORARY] table [IF NOT EXISTS] 表名 [like old_tb_name ] | [as (select_statement)];

# 复制一个student的新表（空）：
CREATE TABLE student_1 LIKE student; 

# 复制一个student的新表，并复制内容：
CREATE TABLE student_2 AS  (SELECT * FROM student); 

数据操作

将媒体文件转换为二进制： LOAD_FILE(‘绝对路径’)

增

insert into <表名> [(字段1,字段2,...)] values(值1,值2,...)
注意：若不指定字段则则匹配全部字段，且只有当主键字段为自增时主键才能赋值null。

如果同时插入多个值：
insert into xxx(字段) values (值1),(值2);

删

delete from <表名> [where <条件>]
注意：删除操作比较危险，一般而言必须带上where条件，否则会删除所有数据
 例：

delete from Student where id='111';` 

TRUNCATE TABLE tb_name;
或：TRUNCATE tb_name;

该方法会删除表中所有数据不走日志，故不可恢复，会重置自增列计数器，不会影响事务。

注意：delete删除的问题，重启数据库，现象：innodb自增从1重新开始（存在内存中，断电丢失），myisam会从上一个增量开始（存在文件中）

改

update <表名> set <字段> = <表达式> ...[where <条件>]
例：

update Student set name='xiaopi33' where id='111';` 

查

--顺序不能变
select [all|distinct] [top n [percent]] {*|<字段>|<表达式>} 
    [as <别名>]|[<字段> as <别名>]... 
    from <表名> 
    [where <条件>] 
    [group by <字段> having <分组条件>] 
    [order by <次序> [asc|desc]
    [limit 起始数据行,需要往下多少条]] 

all：包括重复，默认
distinct：不重复
where：查询条件，不能用于聚合函数
group by：按照<字段表名>值进行分组，值相同为一组，每组一个值。
having：子句限定条件，满足having才输出。若字段名有多组则先按第一个字段分组再按第二个。having子句中可以使用统计函数，where子句中不行！！where语句使用逻辑表达式
order by：排序
limit：分页，如果要求第N页：limit (n-1)*pageSize,pageSize

模糊查询运算符：

注意：为空判断条件：where xx='' or xx is null

Example

基本查询：
    select id as 学号,na as 姓名 from Student; 
    select concat('id:',id) as 学号,na as 姓名 from Student;
    select * from Student where na='111'; 

多重查询： 
    select * from Student where na='111' and age=11; 

模糊查询： 
    select * from Student where na like '11%'; 

统计查询： 
    select avg(grad) as 平均成绩 from Score; 

排序查询： 
    select id,na from Score where grade=100 order by id desc; 

分组查询： 
    select na,count(*) as 人数 from Score group by na; 

连接查询：
	select a,b,c,d
	from xxx as x
	left join yyy as y
	on x.m=y.n
	right join zzz as z
	on y.ooo=z.ooo

子查询：
	select xxx from
	(select yyy from zzz where ...)
	where ... 
其他：
	select version();
	select 100-1 as result;
	select @@auto_increment_increment;

连接查询：
左连接：left join，左表全保留
右连接：right join，右表全保留
内连接：inner join，两个表的交集

模糊查询通配符：
%   ：0或多个字符
_   ：一个字符
[ ]  ：某一范围
[^] ：非某一范围

逻辑运算符：特别注意：<> 不等于 同!=
between and：包头包尾，必须左小右大
and &&
or ||
not !

常用函数

abs() 绝对值
ceiling() 向上取整
floor() 向下取整
rand() 随机数0-1
sign() 返回符号，-1 0 1三者其中之一
char_length() 返回字符串长度
concat(,) 拼接字符串

聚合函数

count() 计数
sum() 求和
avg()
max()
min()

count(xxx) ：忽略null值
count(*)：不会忽略null，包括所有列
count(1)：不会忽略null，忽略所有列，用1代表所有行

如果列为索引，count(列)最快
如果没有索引，且只有一列，则count(*)最快
如果没有索引，有多列，则count(1)最快

事务

索引

primary key：主键索引，唯一标识，不可重复，只能由一个主键
unique key：唯一索引，不可重复
key：index索引，常规索引
fulltext：全文索引

-- 给xxx表的a字段添加全文索引，索引名为ft_a
alter table xxx add fulltext index ft_a(a);

-- 给表中字段加索引
create index id_表名_字段名 on 表(字段);

-- 显示所有索引信息
show index from xxx

-- explain 分析sql执行情况
-- 没有索引情况
explain select * from xxx;
-- 全文索引的情况match against用于全文索引
explain select * from xxx where match(a) against('b');

索引原则：

• 不是越多越好
• 不要对频繁变动的列加索引
• 小数据量不建议加
• 频繁查询建议加

可以参考文章：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

用户管理

相当于对mysql.user这张表进行增删改查

创建用户

create user 用户名 identified by '密码'

修改密码

-- 修改当前用户密码
set password = password('密码');

-- 修改指定用户密码
set password for 用户 = password('密码');

重命名用户

rename user 原名 to 新名

分配权限

-- 给用户赋予所有权限在所有库所有表上，除了给其他授权的权限外
grant all privileges on *.* to 用户名;

-- 赋予所有权限，包括授权权限
grant all privileges on *.* to 'root'@'localhost' with grant option

查看指定用户权限

-- 查看普通用户权限
show grants for 用户名;

-- 查看root用户权限
show grants for root@localhost

撤销指定用户权限

撤销在所有表上的所有权限：revoke all privileges on *.* from 用户名

删除用户

drop user 用户名

数据库备份

-- 导出
mysqldump -h主机名 -u用户 -p密码 数据库 表1 表2 表3 >sql文件全路径
mysqldump -h主机名 -u用户 -p密码 数据库 >sql文件全路径

-- 导入，建议先登录再导入，导入前要切换到目标数据库才行
source sql文件位置;
-- 或
mysql -u用户名 -p密码 数据库名< sql文件全路径

数据库设计

避免：数据冗余、外键

尽量不要使用驼峰命名，因为数据库不区分大小写，建议使用下划线区分

设计数据库步骤

三大范式

第一范式：每个字段不可再分
第二范式：上一条满足下，每张表只描述一件事情
第三范式：满足上两条，要求字段要与主键直接相关

规范性和性能问题：综合考虑规范和体验，要求性能更好，可以增加一些冗余字段和计算列（阿里要求关联表不得超过三张表）

JDBC

数据库驱动：应用程序需要先连到驱动，驱动去操作数据库

Java提供JDBC来统一管理数据库驱动

// 1 加载驱动，8.0驱动改了为：com.mysql.cj.jdbc.Driver
Class.forName("com.mysql.jdbc.Driver");
// 2 连接数据库（这三个参数死死的背下来：useUnicode=true&characterEncoding=utf8&useSSL=false）
String url="jdbc:mysql://localhost:3306/数据库?useUnicode=true&characterEncoding=utf8&useSSL=false";
String username="root";
String password="123456"
Connection connection = DriverManager.getConnection(url,username,password);
// 3 创建sql
Statement stat = connection.createStatement();
String sql = "select * from xxx";
// 4 执行sql
ResultSet result = stat.executeQuery(sql);
// 5 查看结果
while(resultSet.next()){
	System.out.println("id="+resultSet.getObject("id");
}
// 6 释放连接
resultSet.close();
stat.close();
connection.close();

statement可以有几种执行方式：

execute() 可以执行任何sql，效率低
executeQuery() 执行查询，返回ResultSet
executeUpdate() 执行更新、插入、删除，返回影响行数

ResultSet获取方式（获取当前指针处的行数据）：

resultSet.next() 移动到下一条数据
resultSet.getObject()
resultSet.getString()
…

防止SQL注入

使用PrepareStatement可以防止sql注入，预编译，效率更高，如果存在转义字符会被直接忽略

String sql = "insert into user values(?,?,?)";
st = conn.prepareStatement(sql);
st.setInt(1,1000);
st.setString(2,"pp");
st.setInt(3,18);
// 执行
int i = st.executeUpdate();

注意：编号起始为1。数据库中的date日期和java的日期格式不同：st.setDate(n,new java.sql.Date(new Date().getTime()));

事务

try{
	conn.setAutoCommit(false);
	// 执行sql...
	conn.commit();
}catch(Exception e){
	try{
		conn.rollback();
	}catch(...){...}
}finally{
	conn.setAutoCommit(true);
	//释放资源
}

连接池

数据库连接到释放非常消耗系统资源，所以需要池化技术

最小连接数、最大连接数、超时等待

dbcp：需要用到的包：commons-dbcp commons-pool
c3p：需要用到的包：c3p0 mchange-commons-java