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;
数据导入导出
- 导入数据
- 登录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,产生了部分依赖
改为
学生编号(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 |
表的设计
一对多:两张表,多的表加外键 第三范式
多对多:三张表,关系表两个外键 第二范式
一对一:外键唯一,一张表字段太多时,要拆分表
数据库三范式可以避免表中数据的冗余,但实际中,为了满足客户的需求,有时会拿冗余换执行速度