MySQL

最新推荐文章于 2023-07-22 18:14:59 发布

black210

最新推荐文章于 2023-07-22 18:14:59 发布

阅读量339

点赞数

分类专栏：学习笔记文章标签： mysql java 数据库

本文链接：https://blog.csdn.net/black210/article/details/105068634

版权

学习笔记专栏收录该内容

24 篇文章 1 订阅

订阅专栏

1.概述

数据库

DataBase ，简称： DB，用于存储和管理数据的仓库。

特点

持久化存储数据的。其实数据库就是一个文件系统
方便存储和管理数据
使用了统一的方式操作数据库

分类

关系数据库
非关系型数据库（NoSQL）
- redis

NoSQL和关系型数据库比较

优点：

成本：nosql数据库简单易部署，基本都是开源软件，不需要像使用oracle那样花费大量成本购买使用，相比关系型数据库价格便宜。
查询速度：nosql 数据库将数据存储于缓存之中，关系型数据库将数据存储在硬盘中，自然查询速度远不及nosql数据库。
存储数据的格式：nosql 的存储格式是 key,value 形式、文档形式、图片形式等等，所以可以存储基础类型以及对象或者是集合等各种格式，而数据库则只支持基础类型。
扩展性：关系型数据库有类似 join 这样的多表查询机制的限制导致扩展很艰难。

缺点：

维护的工具和资料有限，因为 nosql 是属于新的技术，不能和关系型数据库10几年的技术同日而语。
不提供对 sql 的支持，如果不支持 sql 这样的工业标准，将产生一定用户的学习和使用成本。
不提供关系型数据库对事务的处理。

各自的优势

非关系型数据库的优势

性能NOSQL是基于键值对的，可以想象成表中的主键和值的对应关系，而且不需要经过SQL层的解析，所以性能非常高。
可扩展性同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。

关系型数据库的优势

复杂查询可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。
事务支持使得对于安全性能很高的数据访问要求得以实现。对于这两类数据库，对方的优势就是自己的弱势，反之亦然。

总结：

关系型数据库与NoSQL数据库并非对立而是互补的关系，即通常情况下使用关系型数据库，在适合使用NoSQL的时候使用NoSQL数据库，让NoSQL数据库对关系型数据库的不足进行弥补。
一般会将数据存储在关系型数据库中，在nosql数据库中备份存储关系型数据库的数据

常见的关系数据库

MySQL：开源免费的数据库，小型的数据库，已经被 Oracle 收购了。MySQL6.x 版本也开始收费。后来 Sun公司收购了 MySQL ，而 Sun 公司又被 Oracle 收购
Oracle：收费的大型数据库，Oracle 公司的产品。
DB2 ：IBM公司的数据库产品,收费的。常应用在银行系统中。
SQL Server：MicroSoft 公司收费的中型的数据库。C#、.net等语言常使用。
SQLite: 嵌入式的小型数据库，应用在手机端，如：Android。

2. MySQL

Mysql

MySQL 是一种关系型数据库，在 Java 企业级开发中非常常用，因为 MySQL 是开源免费的，并且方便扩展。阿里巴巴数据库系统也大量用到了 MySQL，因此它的稳定性是有保障的。MySQL 是开放源代码的，因此任何人都可以在 GPL(General Public License) 的许可下下载并根据个性化的需要对其进行修改。MySQL的默认端口号是3306。
在这里插入图片描述

MySQL服务启动

手动。
cmd–> services.msc 打开服务的窗口
使用管理员打开cmd

net start mysql : 启动 mysql 的服务
net stop mysql：关闭 mysql 服务

登录

mysql -uroot -p密码
mysql -hip -uroot -p连接目标的密码
mysql --host=ip --user=root --password=连接目标的密码

退出

exit
quit

目录结构

MySQL安装目录：basedir="D:/develop/MySQL/"
- 配置文件 my.ini
- my.cnf 配置参数详解
MySQL数据目录：datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"

数据库：文件夹
表：文件
数据：数据

2.1.存储引擎

MySQL 当前默认的存储引擎是 InnoDB,并且在5.7版本所有的存储引擎中只有 InnoDB 是事务性存储引擎，也就是说只有InnoDB 支持事务。

show engines;：查看 MySQL 提供的所有存储引擎
show variables like '%storage_engine%';查看MySQL当前默认的存储引擎
show table status like "table_name" ;查看表的存储引擎

MyISAM 和 InnoDB 区别

MyISAM 是 MySQL 的默认数据库引擎（5.5版之前）。虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但 MyISAM 不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。不过，5.5版本之后，MySQL引入了 InnoDB（事务性数据库引擎），MySQL 5.5版本后默认的存储引擎为 InnoDB。

大多数时候我们使用的都是 InnoDB 存储引擎，但是在某些情况下使用 MyISAM 也是合适的比如读密集的情况下。（如果你不介意 MyISAM 崩溃恢复问题的话）。

是否支持行级锁 : MyISAM 只有表级锁(table-level locking)，而 InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
是否支持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原子性,其执行速度比InnoDB类型更快，但是不提供事务支持。但是InnoDB 提供事务支持事务，外部键等高级数据库功能。具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
是否支持外键： MyISAM 不支持，而 InnoDB 支持。
是否支持MVCC ：仅 InnoDB 支持。应对高并发事务, MVCC比单纯的加锁更高效;MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作;MVCC可以使用乐观(optimistic)锁和悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统一。推荐阅读：MySQL-InnoDB-MVCC多版本并发控制

2.2.索引

MySQL索引使用的数据结构主要有 BTree 索引 和 哈希索引 。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择 BTree 索引。

MySQL的 BTree 索引使用的是B树中的 B+Tree，但对于主要的两种存储引擎的实现方式是不同的。

MyISAM: B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。
InnoDB: 其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

CREAT INDEX 联合索引名称 ON 字段1，字段2

特点

根据主键，排序每一行数据；一页 16 kb
B+树：最大元素在根节点当中；只有叶子节点才会有data，其他都是索引；每个叶子节点都有一个指针，指向下一个数据，形成一个有序链表。
utf8：0~3字节，存不了表情（utf8mb4）

原则

选择唯一性索引
为经常需要排序、分组和联合操作的字段及常作为查询条件字段建立索引
限制索引的数目；删除不再使用或者很少使用的索引；尽量的扩展索引，不要新建索引。
尽量使用数据量少的索引；尽量使用前缀来索引；尽量选择区分度高的列作为索引
最左前缀匹配原则，非常重要的原则。
索引列不能参与计算，保持列“干净”：带函数的查询不参与索引。

B+树

每个索引都对应一棵B+树。用户记录都存储在B+树的叶子节点，所有目录记录都存储在非叶子节点。
InnoDB存储引擎会自动为主键建立聚簇索引（如果没有它会自动帮我们添加），聚簇索引的叶子节点包含完整的用户记录。
可以为指定的列建立二级索引，二级索引的叶子节点包含的用户记录由索引列 + 主键组成，所以如果想通过二级索引来查找完整的用户记录的话，需要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录。
B+树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表，而且每个页内的记录（不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引的话，则页面和记录先按照联合索引前边的列排序，如果该列值相同，再按照联合索引后边的列排序。
通过索引查找记录是从B+树的根节点开始，一层一层向下搜索。由于每个页面都按照索引列的值建立了页目录，所以在这些页面中的查找非常快

2.3.锁机制

MySQL 中锁的特性可以大致归纳如下：

存储引擎	行锁	表锁	页锁
MyISAM		√
BDB		√	√
InnoDB	√	√

页锁：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般
由于 BDB 已经被 InnoDB 所取代，我们只讨论 MyISAM 表锁和 InnoDB行锁的问题

MyISAM 和 InnoDB 存储引擎使用的锁：

MyISAM 采用表级锁(table-level locking)。
InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁

表级锁和行级锁对比：

表级锁： MySQL中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。
行级锁： MySQL中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。
表锁更适用于以查询为主，只有少量按索引条件更新数据的应用
行锁更适用于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用。

InnoDB存储引擎的锁的算法有三种：

Record lock：单个行记录上的锁
Gap lock：间隙锁，锁定一个范围，不包括记录本身
Next-key lock：record+gap 锁定一个范围，包含记录本身

相关知识点：

innodb 对于行的查询使用 next-key lock
Next-locking keying为了解决 Phantom Problem 幻读问题
当查询的索引含有唯一属性时，将next-key lock降级为 record key
Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生
有两种方式显式关闭 gap 锁：（除了外键约束和唯一性检查外，其余情况仅使用 record lock）
- 将事务隔离级别设置为 RC
- 将参数 innodb_locks_unsafe_for_binlog设置为1

3. SQL

什么是SQL？

Structured Query Language：结构化查询语言，其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方，称为“方言”。

通用语法

SQL 语句可以单行或多行书写，以分号结尾。
可使用空格和缩进来增强语句的可读性。
MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。
三种注释
- -- 注释内容
- # 注释内容（mysql 特有)
- /* 注释 */（多行）

分类

DDL(Data Definition Language)数据定义语言
- 用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等
DML(Data Manipulation Language)数据操作语言
- 用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等
DQL(Data Query Language)数据查询语言
- 用来查询数据库中表的记录(数据)。关键字：select, where 等
DCL(Data Control Language)数据控制语言(了解)
- 用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

3.1. DDL

3.1.1.操作数据库

C（Create）:创建

创建数据库：
- create database 数据库名称;
创建数据库，判断不存在，再创建：
- create database if not exists 数据库名称;
创建数据库，并指定字符集
- create database 数据库名称 character set 字符集名;
练习：创建 db4数据库，判断是否存在，并制定字符集为gbk
- create database if not exists db4 character set gbk;

R（Retrieve）：查询

查询所有数据库的名称:
- show databases;
查询某个数据库的字符集:查询某个数据库的创建语句
- show create database 数据库名称;

U（Update）:修改

修改数据库的字符集
- alter database 数据库名称 character set 字符集名称;

D（Delete）:删除

删除数据库
- drop database 数据库名称;
判断数据库存在，存在再删除
- drop database if exists 数据库名称;

使用数据库

查询当前正在使用的数据库名称
- select database();
使用数据库
- use 数据库名称;

3.1.2.数据类型

捡些常用的

int：整数类型
- age int,
- double:小数类型
  - score double(5,2)

date:日期，只包含年月日，yyyy-MM-dd

datetime:日期，包含年月日时分秒 yyyy-MM-dd HH:mm:ss
timestamp:时间错类型包含年月日时分秒 yyyy-MM-dd HH:mm:ss
- 如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
varchar：字符串
- name varchar(20):姓名最大20个字符
- zhangsan 8个字符张三 2个字符

3.1.3.操作表

C（Create）:创建

创建表

语法：最后一列，不需要加逗号（,）

create table 表名(
				列名1 数据类型1,
				列名2 数据类型2,
				....
				列名n 数据类型n
			);

create table student(
				id int,
				name varchar(32),
				age int ,
				score double(4,1),
				birthday date,
				insert_time timestamp
			);

复制表：

create table 表名 like 被复制的表名;

R（Retrieve）：查询

查询某个数据库中所有的表名称：show tables
查询表结构：desc 表名;

U（Update）:修改

修改表名
- alter table 表名 rename to 新的表名;
修改表的字符集
- alter table 表名 character set 字符集名称;
添加一列
- alter table 表名 add 列名数据类型;
修改列名称、类型
- alter table 表名 change 列名新列名新数据类型;
- alter table 表名 modify 列名新数据类型;
删除列
- alter table 表名 drop 列名;

D（Delete）:删除

drop table 表名;
drop table if exists 表名 ;

3.2. DML

添加数据

语法：

insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);

注意：

列名和值要一一对应。
如果表名后，不定义列名，则默认给所有列添加值
- insert into 表名 values(值1,值2,...值n);
除了数字类型，其他类型需要使用引号(单双都可以)引起来

删除数据

语法：

delete from 表名 [where 条件]

注意：

如果不加条件，则删除表中所有记录。
如果要删除所有记录
- delete from 表名; – 不推荐使用。有多少条记录就会执行多少次删除操作
- TRUNCATE TABLE 表名; – 推荐使用，效率更高先删除表，然后再创建一张一样的表。

修改数据

语法：

update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];

注意：

如果不加任何条件，则会将表中所有记录全部修改。

3.3. DQL

select * from 表名;

语法

	select
		字段列表
	from
		表名列表
	where
		条件列表
	group by
		分组字段
	having
		分组之后的条件
	order by
		排序
	limit
		分页限定

3.3.1.基础查询

多个字段的查询：select 字段名1，字段名2... from 表名；
去除重复：distinct
计算列：
- 一般可以使用四则运算计算一些列的值。（一般只会进行数值型的计算）
- null 参与的运算，计算结果都为 null
- ifnull(表达式1,表达式2)：表达式1：哪个字段需要判断是否为null，表达式2为如果该字段为 null 后的替换值。
起别名：as；as 也可以省略

select *,(math+english) as 总成绩 from student;
SELECT IFNULL(NULL, "RUNOOB");

3.3.2.条件查询

where 条件列表

加一些运算符

			-- 查询年龄不等于20岁
			SELECT * FROM student WHERE age != 20;
			SELECT * FROM student WHERE age <> 20;
			-- 查询年龄22岁，18岁，25岁的信息
			SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25
			SELECT * FROM student WHERE age IN (22,18,25);
			-- 查询姓马的有哪些？ like
			SELECT * FROM student WHERE NAME LIKE '马%';
			-- 查询姓名第二个字是化的人
			SELECT * FROM student WHERE NAME LIKE "_化%";	
			-- 查询姓名是3个字的人
			SELECT * FROM student WHERE NAME LIKE '___';
			-- 查询姓名中包含德的人
			SELECT * FROM student WHERE NAME LIKE '%德%';

3.3.3.排序查询

语法：order by 子句

order by 排序字段1 排序方式1 ，  排序字段2 排序方式2...

SELECT * from runoob_tbl ORDER BY submission_date ASC;
--首先按`column_name1`字段排序，若`column_name1`相等，则按column_name2排序。
SELECT * FROM  table_name ORDER BY column_name1, column_name2 DESC;

排序方式：

ASC：升序，默认的。
DESC：降序。

如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。

3.3.4.聚合查询

聚合函数：将一列数据作为一个整体，进行纵向的计算。

SELECT COUNT(*) FROM students;
SELECT AVG(age) FROM students;

count：计算个数
- 一般选择非空的列：主键
- count(*)
- COUNT(*)和COUNT(id)实际上是一样的效果。
max：计算最大值
min：计算最小值
sum：计算和
avg：计算平均值
first(列名)：第一条记录
last(列名)：最后一条记录

MAX()和MIN()函数并不限于数值类型。如果是字符类型，MAX()和MIN()会返回排序最后和排序最前的字符。

聚合函数的计算，排除null值?

选择不包含非空的列进行计算
IFNULL函数

3.3.5.分组查询

语法：group by 分组字段；

分组之后查询的字段：分组字段、聚合函数

where 和 having 的区别？

where 在分组之前进行限定，如果不满足条件，则不参与分组。having 在分组之后进行限定，如果不满足结果，则不会被查询出来
where 后不可以跟聚合函数，having 可以进行聚合函数的判断。

--  按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组,分组之后。人数要大于2个人
		SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;
		
		SELECT sex , AVG(math),COUNT(id) 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2;

3.3.6.分页查询

语法：limit 开始的索引,每页查询的条数;

公式：开始的索引 = （当前的页码 - 1） * 每页显示的条数

limit 是一个MySQL"方言"

	-- 每页显示3条记录 
	SELECT * FROM student LIMIT 0,3; -- 第1页	
	SELECT * FROM student LIMIT 3,3; -- 第2页	
	SELECT * FROM student LIMIT 6,3; -- 第3页

3.3.7.运算符

算术运算符

运算符	作用
+	加法
-	减法
*	乘法
/ 或 DIV	除法
% 或 MOD	取余

比较运算符


符号	描述	备注
=	等于
<>, !=	不等于
>	大于
<	小于
<=	小于等于
>=	大于等于
BETWEEN	在两值之间	>=min&&<=max
NOT BETWEEN	不在两值之间
IN	在集合中
NOT IN	不在集合中
<=>	严格比较两个NULL值是否相等	两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0
LIKE	模糊匹配
REGEXP 或 RLIKE	正则式匹配
IS NULL	为空
IS NOT NULL	不为空

逻辑运算符

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

位运算符

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

3.4. DCL

DBA：数据库管理员
DCL：管理用户，授权

管理用户：

添加用户

语法：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

删除用户

语法：DROP USER '用户名'@'主机名';

修改用户密码

			UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
			UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';
			
			SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
			SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');

查询用户

切换到 mysql 数据库 USE myql;
查询user表SELECT * FROM USER;
通配符：%表示可以在任意主机使用用户登录数据库

权限管理：

查询权限

			-- 查询权限
			SHOW GRANTS FOR '用户名'@'主机名';
			SHOW GRANTS FOR 'lisi'@'%';

授予权限

			-- 授予权限
			grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
			-- 给张三用户授予所有权限，在任意数据库任意表上

撤销权限

			-- 撤销权限：
			revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
			REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';

3.5.数据库的备份和还原

命令行：

备份： mysqldump -u用户名 -p密码数据库名称 > 保存的路径
还原：
1. 登录数据库
2. 创建数据库
3. 使用数据库
4. 执行文件。source 文件路径

使用图形化工具

4.约束

概念：对表中的数据进行限定，保证数据的正确性、有效性和完整性。

分类

主键约束：primary key
非空约束：not null
唯一约束：unique
外键约束：foreign key

非空约束：not null，某一列的值不能为null

	--创建表时添加约束
		CREATE TABLE stu(
			id INT,
			NAME VARCHAR(20) NOT NULL -- name为非空
		);
	--创建表完后，添加非空约束
		ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

	--删除name的非空约束
		ALTER TABLE stu MODIFY NAME VARCHAR(20);

唯一约束：unique，某一列的值不能重复

唯一约束可以有NULL值，但是只能有一条记录为null

	--在创建表时，添加唯一约束
		CREATE TABLE stu(
			id INT,
			phone_number VARCHAR(20) UNIQUE -- 手机号
		);
	--删除唯一约束
		ALTER TABLE stu DROP INDEX phone_number;
	--在表创建完后，添加唯一约束
		ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

主键约束：primary key。

注意：

含义：非空且唯一
一张表只能有一个字段为主键
主键就是表中记录的唯一标识

	--在创建表时，添加主键约束
		create table stu(
			id int primary key,-- 给id添加主键约束
			name varchar(20)
		);

	--删除主键
		-- 错误 alter table stu modify id int ;
		ALTER TABLE stu DROP PRIMARY KEY;

	--创建完表后，添加主键
		ALTER TABLE stu MODIFY id INT PRIMARY KEY;
	--自动增长：如果某一列是数值类型的，使用 auto_increment 可以来完成值得自动增长
	--在创建表时，添加主键约束，并且完成主键自增长
        create table stu(
                id int primary key auto_increment,-- 给id添加主键约束
                name varchar(20)
            );
	--删除自动增长
		ALTER TABLE stu MODIFY id INT;
	--添加自动增长
		ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

外键约束：foreign key,让表于表产生关系，从而保证数据的正确性。

	--在创建表时，可以添加外键
			create table 表名(
				....
				外键列
				constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
			);
			CREATE TABLE tb_UserAndRole  --用户角色表
            (
              ID INT PRIMARY KEY IDENTITY(1,1),
              UserID INT NOT NULL,--用户ID
              RoleID INT NOT NULL,--角色ID
              foreign key(UserID) references tb_Users(ID)
                --tb_Users表的ID作为tb_UserAndRole表的外键
            )

	--删除外键
		ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

	--创建表之后，添加外键
		ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

级联操作

添加级联操作

		ALTER TABLE 表名 ADD CONSTRAINT 外键名称 
					FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE 			CASCADE ON DELETE CASCADE  ;

分类：

级联更新：ON UPDATE CASCADE
级联删除：ON DELETE CASCADE

5.多表关系与三大范式

多表之间的关系分类

一对一(了解)：
- 如：人和身份证
- 分析：一个人只有一个身份证，一个身份证只能对应一个人
- 实现方式：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键。
一对多(多对一)：

如：部门和员工
分析：一个部门有多个员工，一个员工只能对应一个部门
实现方式：在多的一方建立外键，指向一的一方的主键。

多对多：

如：学生和课程
分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择
实现方式：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键

数据库设计的范式

概念：设计数据库时，需要遵循的一些规范。要遵循后边的范式要求，必须先遵循前边的所有范式要求

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。建议参考更多

谈一谈三个范式

第一范式（1NF）：每一列都是不可分割的原子数据项
第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于码（在1NF基础上消除非主属性对主码的部分函数依赖）
第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

几个概念

函数依赖：A–>B,如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于A
- 例如：学号–>姓名。（学号，课程名称） --> 分数
完全函数依赖：A–>B，如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值。
- 例如：（学号，课程名称） --> 分数
部分函数依赖：A–>B，如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值即可。
- 例如：（学号，课程名称） – > 姓名
传递函数依赖：A–>B, B – >C . 如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A
- 例如：学号–>系名，系名–>系主任
码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码
- 例如：该表中码为：（学号，课程名称）
- 主属性：码属性组中的所有属性
- 非主属性：除过码属性组的属性

图解三大范式

6.多表查询

查询语法：

	select
		列名列表
	from
		表名列表
	where....

笛卡尔积：

有两个集合A,B .取这两个集合的所有组成情况。
要完成多表查询，需要消除无用的数据

多表查询的分类

内连接查询：
- 隐式内连接
- 显式内连接
外链接查询：
- 左外连接
- 右外连接
子查询：
- 结果是单行单列
- 结果是多行单列
- 结果是多行多列

内连接查询

隐式内连接：使用 where 条件消除无用数据

		-- 查询员工表的名称，性别。部门表的名称
			SELECT 
				t1.name, -- 员工表的姓名
				t1.gender,-- 员工表的性别
				t2.name -- 部门表的名称
			FROM
				emp t1,
				dept t2
			WHERE 
				t1.`dept_id` = t2.`id`;

显式内连接：select 字段列表 from 表名1 [inner] join 表名2 on 条件

SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;	
SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`;

外链接查询

左外连接：select 字段列表 from 表1 left [outer] join 表2 on 条件
- 查询的是 左表所有数据以及其交集部分。
右外连接：select 字段列表 from 表1 right [outer] join 表2 on 条件；
- 查询的是 右表所有数据以及其交集部分。

-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
--左外连接
SELECT 	t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;
--右外连接
SELECT 	* FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`;

子查询

概念：查询中嵌套查询，称嵌套查询为子查询。

子查询的结果是单行单列的：子查询可以作为条件，使用运算符去判断。
- 运算符： > >= < <= =

-- 查询员工工资小于平均工资的人
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);

子查询的结果是多行单列的：子查询可以作为条件，使用运算符 in 来判断

-- 查询'财务部'和'市场部'所有的员工信息
SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';
SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
-- 子查询
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');

子查询的结果是多行多列的：子查询可以作为一张虚拟表参与查询

-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
-- 子查询
SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2
				WHERE t1.id = t2.dept_id;
				
-- 普通内连接
SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` >  '2011-11-11'

7.事务

概念：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

操作

开启事务： start transaction;
回滚：rollback;
提交：commit;

MySQL 数据库中事务默认自动提交

事务提交的两种方式：

自动提交：
- mysql就是自动提交的
- 一条DML(增删改)语句会自动提交一次事务。
手动提交：
- Oracle 数据库默认是手动提交事务
- 需要先开启事务，再提交

修改事务的默认提交方式：

查看事务的默认提交方式：SELECT @@autocommit;– 1 代表自动提交 0 代表手动提交
修改默认提交方式： set @@autocommit = 0;

事务的四大特性 ACID

原子性（Atomicity）：是不可分割的最小操作单位，要么同时成功，要么同时失败。
持久性（Consistency）：当事务提交或回滚后，数据库会持久化的保存数据。
隔离性（Isolation）：多个事务之间。相互独立。
一致性（Durability）：事务操作前后，数据总量不变

隔离性会产生的3个问题

脏读（Dirty read）：一个事务，读取到另一个事务中没有提交的数据
不可重复读/虚读（Unrepeatableread）：在同一个事务中，两次读取到的数据不一样。
幻读（Phantom read）：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

不可重复度和幻读区别：

不可重复读的重点是修改，（同样的条件, 你读取过的数据, 再次读取出来发现值不一样了）
幻读的重点在于新增或者删除。（同样的条件, 第1次和第2次读出来的记录数不一样）

解决办法：四种隔离级别

read uncommitted：读未提交
- 产生的问题：脏读、不可重复读、幻读
read committed：读已提交 （Oracle）
- 产生的问题：不可重复读、幻读
repeatable read：可重复读 （MySQL默认）
- 产生的问题：幻读
- 解决虚读问题，自己提交才会改变数据
serializable：串行化
- 可以解决所有的问题
- 当前事务会等待另一个事务提交，才会生效修改，效率低。

8.函数

完整的函数请参考

8.1.数学函数

函数	描述
CEIL(x)	返回大于或等于 x 的最小整数，1.5返回2
FLOOR(x)	返回小于或等于 x 的最大整数，1.5返回1
PI()	返回圆周率(3.141593）
ROUND(x)	返回离 x 最近的整数
MOD(x,y)	返回 x 除以 y 以后的余数
EXP(x)	返回 e 的 x 次方
TRUNCATE(x,y)	返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）

8.2.字符串函数

函数	描述
UPPER(s)	将字符串转换为大写
CONCAT(s1,s2…sn)	字符串 s1,s2 等多个字符串合并为一个字符串
CONCAT_WS(x, s1,s2…sn)	同 CONCAT(s1,s2,…) 函数，但是每个字符串之间要加上 x，x 可以是分隔符
SUBSTR(s, start, length)	从字符串 s 的 start 位置截取长度为 length 的子字符串
REPLACE(s,s1,s2)	将字符串 s2 替代字符串 s 中的字符串 s1，比如将字符串 abc 中的字符 a 替换为字符 x
REPEAT(s,n)	将字符串 s 重复 n 次
FORMAT(x,n)	函数可以将数字 x 进行格式化 “#,###.##”, 将 x 保留到小数点后 n 位，最后一位四舍五入。
RIGHT(s,n)	返回字符串 s 的后 n 个字符
LEFT(s,n)	返回字符串 s 的前 n 个字符

8.3.时间函数

函数	描述
NOW()	返回当前日期和时间
SYSDATE()	返回当前日期和时间
DATE_FORMAT(d,f)	按表达式 f 的要求显示日期 d
TIME_FORMAT(t,f)	按表达式 f 的要求显示时间 t
STR_TO_DATE(string, format_mask)	将字符串转变为日期
MAKEDATE(year, day-of-year)	基于给定参数年份 year 和所在年中的天数序号 day-of-year 返回一个日期
MAKETIME(hour, minute, second)	组合时间，参数分别为小时、分钟、秒
SEC_TO_TIME(s)	将以秒为单位的时间 s 转换为时分秒的格式
TIME_TO_SEC(t)	将时间 t 转换为秒

black210

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
MySQL

1.概述数据库DataBase ，简称： DB，用于存储和管理数据的仓库。特点持久化存储数据的。其实数据库就是一个文件系统方便存储和管理数据3. 使用了统一的方式操作数据库分类关系数据库非关系型数据库（NoSQL）redisNoSQL和关系型数据库比较优点：成本：nosql数据库简单易部署，基本都是开源软件，不需要像使用oracl...
复制链接

扫一扫