MySQL

下载地址

Download MySQL Community Server (Archived Versions)

学习视频

MySQL入门基础,mysql基础视频+数据库实战,老杜带你学_哔哩哔哩_bilibili

常用命令

启停服务

net start mysql
net stop mysql

退出

exit

查看 mysql 中数据库

show databases;

创建数据库

create database bjpowernode;

使用某数据库

use bjpowernode;

数据导入导出

导入数据

登录mysql数据库服务器
创建数据库：create database bjpowernode;
使用数据库：use bjpowernode
然后初始化数据库：source D:\bjpowernode.sql

导出数据

在windows的dos命令窗口中进行

导出数据库

mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p1111

导出数据库中某张表

mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p1111

查看某数据库中所包含表

show tables;

查看表结构

desc dept;

数据类型

varchar (最长 255)
char (最长 255)
int (最长 11)
bigint
float
double
date 短日期类型
datetime 长日期类型
clob 字符大对象（超过 255 个字符），最多可以存储 4 G 的字符串。如：存储一篇文章、一个说明。
blob 进制大对象，专门用来存储图片、声音、视频等流媒体数据。

约束

not null

create table t_vip(
	id int,
	name varchar(255) not null 
);

unique

NULL可以重复

create table t_vip(
	id int unique,
	name varchar(255),
	email varchar(255),
	unique(name,email)  // 两个字段联合起来唯一，为多个字段添加约束，要用表级约束
);

create table t_vip(
	id int,
	name varchar(255) not null unique // MySQL中自动变为主键，一般用主键实现
);

primary key

一行记录的唯一标识，只有一个，任何一张表都有主键，否则无效
主键特征：not null + unique
主键一般是定长，不建议用varchar

主键自增

create table t_vip(
	id int primary key auto_increment, // 从1开始
	name varchar(255)
);
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');

复合主键 #了解#

create table t_vip(
	id int,
	name varchar(255),
	email varchar(255),
	primary key(id,name)
);

foreign key

外键至少具有unique约束，可以为NULL

create table t_class(
	classno int primary key,
	classname varchar(255)
);
create table t_student(
	no int primary key auto_increment,
	name varchar(255),
	cno int,
	foreign key(cno) references t_class(classno)
);

日期

MySQL 的日期格式

%Y	%m	%d	%h	%i	%s
年	月	日	时	分	秒

date 默认格式：%Y-%m-%d

datetime 默认格式：%Y-%m-%d %h:%i:%s

str_to_date

create table t_user(
	id int,
	name varchar(32),
	birth date,
	create_time datetime
);
insert into t_user (id, name, birth) values (1, 'zhangsan', str_to_date ('01-10-1990','%d-%m-%Y'));
insert into t_user(id,name,birth) values(2, 'lisi', '1990-10-01'); // 使用默认格式，str_to_date函数就不需要

date_format

select id,name,date_format(birth, '%m/%d/%Y') as birth from t_user;
select id,name,birth from t_user; // 自动将date转换成varchar

date 和 datetime

date 年月日
datetime 年月日时分秒

insert into t_user(id,name,birth,create_time) values(1,'zhangsan','1990-10-01','2020-03-18 15:49:50');

str_to_date 和 date_format 也可用于 datatime 类型

now

获取系统当前时间（datetime）

insert into t_user(id,name,birth,create_time) values(2,'lisi','1991-10-01',now());

SQL 分类

%% SQL 不区分大小写，要以 ; 结尾 %%

DQL

数据查询语言
Select

DDL

数据定义语言 (增删改)，操作表结构

create 表名：建议以 t_ 或者 tbl_ 开始

create table 表名(
	字段名1 数据类型, 
	字段名2 数据类型, 
	字段名3 数据类型
);

create table t_student( // 括号里的只是建议长度
	no int(11),
	name varchar(32),
	sex char(1) default 'm', // 指定默认值
	age int(3),
	email varchar(255)
);

快速创建表：将一个查询结果当做一张表新建（包含表结构和数据） #了解#
```
create table emp2 as select * from emp;
```

drop

drop table t_student; // 表不存在时报错
drop table if exists t_student;

truncate：删除表中所有数据，物理删除，快速，不支持回滚
```
truncate table dept_bak;
```
alter：很少修改表结构 #了解#

DML

数据操作语言 (增删改)，操作表数据

insert

insert into 表名 (字段名 1, 字段名 2, 字段名 3…) values (值 1, 值 2, 值 3);

字段名和值要一一对应

insert into t_student(no,name,sex,age,email) values(1,'zhangsan','m',20,'zhangsan@123.com');
insert into t_student(email,name,sex,age,no) values('lisi@123.com','lisi','f',20,2);

字段名省略，值都要写上

insert into t_student values(2,'lisi','f',20,'lisi@123.com');

没有给其它字段指定值，默认值是 NULL
```
insert into t_student(no) values(3); 
```

一次插入多条记录：insert into t_user(字段名1,字段名2) values(),(),(),();

insert into t_user(id,name,birth,create_time) values
(1,'zs','1980-10-11',now()), 
(2,'lisi','1981-10-11',now()),
(3,'wangwu','1982-10-11',now());

delete

delete from 表名 where 条件;

效率低，支持回滚（对比truncate）

delete from t_user where id = 2;
delete from t_user; // 没有 where 条件，整张表的数据会全部删除

update

update 表名 set 字段名 1 = 值 1, 字段名 2 = 值 2 … where 条件;

update t_user set name = 'jack', birth = '2000-10-11' where id = 2;
update t_user set name = 'abc'; // 没有 where 条件，更新所有

TCL

事务控制语言
事务提交 commit
事务回滚 rollback

DCL

数据控制语言
授权 grant
撤销权限 revoke

存储引擎

存储引擎是MySQL中特有的术语，表存储数据的方式

建表时指定存储引擎

create table t_product(
	id int primary key,
	name varchar(255)
)engine=InnoDB default charset=gbk;

MySQL默认存储引擎：InnoDB，默认字符编码方式：utf8

查看MySQL版本

select version();

查看MySQL支持的存储引擎

show engines \G

MyISAM

结构
- 格式文件 — 存储表结构的定义（mytable.frm）
- 数据文件 — 存储表行的内容（mytable.MYD）
- 索引文件 — 存储表上索引（mytable.MYI）
支持转换为压缩、只读表来节省空间
不支持事务机制，安全性低

InnoDB

结构
- 表结构定义 .frm
- 表空间 tablespace 用于存储表的内容（表空间是一个逻辑名称，表空间存储数据+索引）
非常安全，支持事务，支持数据库崩溃后自动恢复机制
效率不是很高，并且也不能压缩，不能转换为只读，不能很好的节省存储空间

MEMORY

结构
- 表结构定义 .frm
- 表数据及索引存储在内存中
- 不能包含 TEXT 或 BLOB 字段。
数据存储在内存中，且行的长度固定
查询效率最高，不安全，关机之后数据消失

事务

事务只与DML语句有关，其它语句和事务无关
实现多条DML语句同时成功和同时失败
- InnoDB存储引擎：提供一组用来记录事务性活动的日志文件
- 在事务的执行过程中，每一条DML的操作都会记录到事务性活动的日志文件中。

提交事务

commit; 默认每执行一条DML语句，提交一次

start transaction; // 相当于关闭自动提交机制
insert into dept_bak values(20,'abc', 'tj');
commit;
select * from dept_bak;
rollback;
select * from dept_bak;

回滚事务

rollback; 回滚到上一次的commit

start transaction; 
insert into dept_bak values(10,'abc', 'tj');
select * from dept_bak;
rollback;
select * from dept_bak;

事务特性

原子性
- 一个完整的业务逻辑，最小的工作单元，不可再分。
一致性
- 在同一个事务当中，所有操作必须同时成功，或者同时失败，保证数据的一致性。
隔离性
- 4个隔离级别
持久性
- 事务最终结束的一个保障。事务提交，相当于将没有保存到硬盘上的数据保存到硬盘上

隔离级别

读未提交：read uncommitted
- 事务A可以读取到事务B未提交的数据
- 存在脏读现象
- 一般是理论上的，大多数的数据库隔离级别都是二档起步
读已提交：read committed
- 事务A只能读取到事务B提交之后的数据
- 解决了脏读现象，但不可重复读取数据
- 每一次读到的数据是绝对的真实，oracle数据库默认的隔离级别
可重复读：repeatable read
- 事务A读取到的数据都是一致的，永远读取的都是刚开启事务时的数据。
- 即使事务B将数据已经修改，并且提交，事务A读取到的数据还是没有发生改变，这就是可重复读。
- 解决了不可重复读取数据，可能出现幻影读。每一次读取到的数据都是幻象，不够真实。
- mysql数据库默认的隔离级别
序列化：serializable
- 表示事务排队，不能并发，每一次读取到的数据都是最真实的
- 最高隔离级别，效率最低，解决了所有的问题。

查看隔离级别

select @@tx_isolation;

修改隔离级别

set global transaction isolation level read committed;

视图

将复杂的SQL语句以视图对象的形式新建，简化开发，利于维护
使用视图的时候可以像使用table一样，对视图进行增删改查
对视图对象增删改查，会导致原表被操作

创建视图对象

create view 
	emp_dept_view
as // 必须是DQL语句
	select 
		e.ename,e.sal,d.dname
	from
		emp e
	join
		dept d
	on
		e.deptno = d.deptno;

删除视图对象

drop view dept2_view;

索引

为字段添加索引，缩小扫描范围，提高查询效率
任何数据库,主键上自动添加索引对象。在mysql中，有unique约束的字段，即会自动创建索引对象
任何数据库，一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。
在mysql当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在，索引存储结构：自平衡二叉树

创建索引

mysql> create index emp_ename_index on emp(ename);

删除索引

mysql> drop index emp_ename_index on emp;

查看SQL语句是否使用索引进行检索

explain select * from emp where ename = 'KING'; // 若type=ALL，说明没有使用索引

索引失效

模糊匹配中以“%”开头
使用or，or两边的条件字段都要有索引
使用复合索引%% 两个字段，或者更多的字段联合起来添加一个索引 %%时，没有使用左侧的列查找
where当中索引列参加了运算或使用了函数

数据库设计三范式

第一范式

表必须有主键，每一个字段原子性不可再分。

没有主键
联系方式可以分为邮箱地址和电话

学生编号	学生姓名	联系方式
1001	张三	zs@gmail.com,1359999999
1002	李四	ls@gmail.com,13699999999
1001	王五	ww@163.net,13488888888
改为

学生编号(pk)	学生姓名	邮箱地址	联系电话
1001	张三	zs@gmail.com	1359999999
1002	李四	ls@gmail.com	13699999999
1003	王五	ww@163.net	13488888888

第二范式

在第一范式的基础上，所有非主键字段完全依赖主键，不要产生部分依赖。

学生编号	学生姓名	教师编号	教师姓名
1001	张三	001	王老师
1002	李四	002	赵老师
1003	王五	001	王老师
1001	张三	002	赵老师
张三依赖1001，王老师依赖001，产生了部分依赖
改为