MySQL

MySQL

下载地址

Download MySQL Community Server (Archived Versions)

学习视频

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

常用命令

启停服务

net start mysql
net stop mysql

登录

mysql -uroot -p

退出

exit

查看 mysql 中数据库

show databases;

创建数据库

create database bjpowernode;

使用某数据库

use bjpowernode;

数据导入导出

• 导入数据

1. 登录mysql数据库服务器
2. 创建数据库：create database bjpowernode;
3. 使用数据库：use bjpowernode
4. 然后初始化数据库：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

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

1. 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());
  

2. delete

• delete from 表名 where 条件;

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

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

3. 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个隔离级别
• 持久性
  • 事务最终结束的一个保障。事务提交，相当于将没有保存到硬盘上的数据保存到硬盘上

隔离级别

1. 读未提交：read uncommitted
   • 事务A可以读取到事务B未提交的数据
   • 存在脏读现象
   • 一般是理论上的，大多数的数据库隔离级别都是二档起步
2. 读已提交：read committed
   • 事务A只能读取到事务B提交之后的数据
   • 解决了脏读现象，但不可重复读取数据
   • 每一次读到的数据是绝对的真实，oracle数据库默认的隔离级别
3. 可重复读：repeatable read
   • 事务A读取到的数据都是一致的，永远读取的都是刚开启事务时的数据。
   • 即使事务B将数据已经修改，并且提交，事务A读取到的数据还是没有发生改变，这就是可重复读。
   • 解决了不可重复读取数据，可能出现幻影读。每一次读取到的数据都是幻象，不够真实。
   • mysql数据库默认的隔离级别
4. 序列化：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，产生了部分依赖
改为

学生编号(pk)	学生名字
1001	张三
1002	李四
1003	王五

教师编号(pk)	教师姓名
001	王老师
002	赵老师

id(pk)	学生编号(fk)	教师编号(fk)
1	1001	001
2	1002	002
3	1003	001
4	1001	002

第三范式

在第二范式的基础上，所有非主键字段直接依赖主键，不要产生传递依赖。

学生编号	学生姓名	班级编号	班级名称
1001	张三	01	一年一班
1002	李四	02	一年二班
1003	王五	03	一年三班
1004	张三	03	一年三班

一年一班依赖01，01依赖1001，产生了传递依赖

改为

班级编号(pk)	班级名称
01	一年一班
02	一年二班
03	一年班

学生编号(pk)	学生名字	班级编号(fk)
1001	张三	01
1002	李四	02
1003	王五	03
1004	赵六	03

表的设计

一对多：两张表，多的表加外键 第三范式
多对多：三张表，关系表两个外键 第二范式
一对一：外键唯一，一张表字段太多时，要拆分表

数据库三范式可以避免表中数据的冗余，但实际中，为了满足客户的需求，有时会拿冗余换执行速度