MySQL数据库

目录

关系型数据库(RDBMS )

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。

每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。

我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。

所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

RDBMS 即关系数据库管理系统(Relational Database Management System)的特点：

1. 数据以表格的形式出现
2. 每行为各种记录名称
3. 每列为记录名称所对应的数据域
4. 许多的行和列组成一张表单
5. 若干的表单组成database

RDBMS 术语

• 数据库: 数据库是一些关联表的集合。

• 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。

• 列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。

• **行：**一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。

• 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。

• 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。

• **外键：**外键用于关联两个表。

• 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。

• 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。

• 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

MySql数据库

mysql是当前比较流行的关系型数据库管理系统,常用在web应用上.

现属于Oracle公司,替代产品 MariaDB.

MySql数据类型

数值类型

• MySQL支持所有标准SQL数值数据类型。

• 这些类型包括严格数值数据类型(integer、smallint、declmal和numeric)，以及近似数值数据类型(float、real和double)。

• 关键字int是integer的同义词，关键字dec是declmal的同义词。

• 作为SQL标准的扩展，MySQL也支持整数类型tinyint、mediumint和bigint。下面的表显示了需要的每个整数类型的存储和范围。

位bit 字节byte 1Byte=8bit

类型	byte	范围(有符号)	范围(无符号)	用途
tinyint	1	-2(8-1),2(8-1)-1	(2^(8-1))*2-1	小整数
smallint	2
mediumint	3
int/integer	4
bigint	8			超大整数
float	4			单精度
double	8			双精度

日期和时间类型

• 表示时间值的日期和时间类型为datetime、date、timestamp、time和year。

• 每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

• timestamp类型有专有的自动更新特性，将在后面描述。

类型	byte	范围	格式	用途
date	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
time	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值
year	1	1901/2155	YYYY	年份值
datetime	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	日期时间
timestamp	4	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	时间戳

字符串类型

字符串类型指char、varchar、binary、varbinary、blob、text、enum和set。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型	byte	用途
char	0-255	定长字符串
varchar	0-65535	变长字符串
tinyblob	0-255	不超过 255 个字符的二进制字符串
tinytext	0-255	短文本字符串
blob	0-65535	二进制形式的长文本数据
text	0-65535	长文本数据
mediumblob	0-16 777 215	二进制形式的中等长度文本数据
mediumtext	0-16 777 215	中等长度文本数据
longblob	0-4 294 967 295	二进制形式的极大文本数据
longtext	0-4 294 967 295	极大文本数据

字符串注意

• char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

• CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

• binary和 varbinary类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

• BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

• 有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

MySql事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
• 事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

• **原子性：**一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• **一致性：**在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• **隔离性：**数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
• **持久性：**事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

MySql索引

• 实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录.(用于快速查询)

• mysql默认的索引结构是B+树

  只有最底层的叶子节点（文件）保存数据。非叶子节点只保存索引，不保存实际的数据，所有的非叶子节点都可以看成是索引部分。

  • 根节点至少有两个子节点
  • 叶子节点都在同一高度
  • 非叶子节点有n个关键字,则它有n+1个子节点,且这n个结点递增排列
  • b+树的非叶子邛只存关键字,不存数据
  • b+树的叶子节点之间使用指针相连,是一个双向链表

优点:

大大提高查询速度

缺点:

降低表的增,删,改速度;因为更新表时,不仅要保存数据,还要保存索引文件.

• 索引分单列索引

  • 一个索引只包含单个列.
  • 一个表可以有多个单列索引,但这并不是组合索引.

• 组合索引

  • 一个索引包含多个列.

普通索引

1. 创建索引

   create index index_name on table_name (column_name);

   • char,varchar 类型,length 可以小于字段实际长度
   • blob,tect类型,必须指定length

2. 添加索引

   alter table table_name add index index_name(column_name);

3. 创建表的时候指定索引

   create teble teble_name(

   id int not null,

   username varchar(16) not null,

   index [index_name] (username(length))

   );

4. 删除索引

   drop index [index_name] on table_name;

唯一索引

与普通索引类似,但索引的列值必须唯一

允许有空值

1. 创建索引

   create unique index index_name on table_name(column_name(length))

2. 添加索引

   alter table table_name add unique [index_name] (column_name(length))

3. 创建表的时候指定

   create table table_name(

   id int not null,

   username varchar(16) not null,

   unique [index_name] (username(length))

   );

显示索引信息

show index from table_name; \G # \G用来格式化输出信息

数据库连接操作

1. net start mysql80 启动mysql服务
2. net stop mysql80 关闭mysql服务
3. mysql -h[hostip] -u[username] -p[password] 连接数据库(h代表数据库服务器的ip地址，如果是省略掉，代表本地ip)
4. exit 关闭数据库连接
5. 执行 .sql文件
   • mysql -uroot -p
   • set names utf8 (设置mysql控制台编码)
   • source 绝对路径 (执行sql文件)

用户管理操作

修改数据库用户密码

1. mysqladmin -uroot -p123456 password 123 （password后跟新的密码）（登陆之前操作）
2. • mysql5：set password for root@localhost=password (‘12345’);
   • mysql8：alter user root@localhost identified by ‘123’;

查看系统用户

select host,userfrom user;

赋予权限（创建用户）

1. create user rimi@localhost identified by ‘123456’;

2. grant all on . to rimi@localhost identified by ‘rimi’;

grant授予用户权限

all/all privileges 表示所有权限，mysql一共提供32种不同的操作权限，其中常用的包括all，select，alter，create，delete，drop，insert，index等。

on指定该权限对哪些表生效，*数据库和所有表代表所有。

To 指定该权限授予的用户

@ 指定该用户的权限应限于哪个IP地址登陆，无限制用%

Identified by 指定密码

查看用户权限

show grants for rimi@localhost;

回收用户权限

revoke create on . from rimi@localhost;

删除用户

drop user rimi@localhost;

数据库实例

1. show databases；查看所有数据库
2. create database xxx_xx；新建数据库
3. drop database xxx；删除数据库
4. use xxx；进入数据库
5. show tables；查看数据库中所有表

数据库表结构操作

1. create table table_name(

   id int 约束条件,

   name varchar(10) 约束条件

   );创建数据库表

   • Primary key 主键约束
   • Not null 非空约束
   • Auto_increment 自动增长的约束
   • Default 默认值
   • foreign key 外键约束

2. drop table table_name；删除表

3. create table table_name like table_name；复制一张表的格式

4. desc table_name；查看数据库表的描述

5. show create table table_name；查询建表语句

6. alter table table_name rename table_name；修改表名

7. alter table table_name change old_name new_name varchar(10);修改列

8. alter table table_name add column column_name data_type；添加列

9. alter table table_name drop column column_name；删除列

数据库表的数据操作

注意: 当insert/update数据时,若操作表的外键,需要确定 外键变更值 与 主表的 主键值 对应.

增

1. insert into table_name(x,x,x) values(x,‘x’,x); 添加一行数据

删

1. delete from table_name where id=‘xx’ ;删除一行数据
2. truncate table_name; 删除整个表格数据

改

1. update table_name set name=‘xx’ where id=‘xx’ ; 修改数据内容

查

1. select column_name,name2 from table_name; 查询指定列

2. select * from user;查询所有列

3. select column_name as name from table_name; 列起别名

4. select column_name from table_name as name; 表起别名

   eg: select u.name from user as u;

5. select distinct column_name from table_name;去重

常用函数

函数	用法
lower	小写
upper	大写
length	长度
substr	截取子串
concat	拼接
replace	替换
round/ceil/floor	四舍五入/向上取整/向下取整
uuid	生成一个唯一36位字符串

日期

函数	用法
now()	当前日期
last_day	某月最后一天
year、month、day
date_format	日期类型转换成字符串 ：%Y-%m-%d
str_to_date	把字符串转换成日期

特殊查询

去重复(distinct)

select DISTINCT class ,sname from students
-- class和sname拼接一起去重

条件查询(where 条件表达式)

条件表达式：

1. column_name >、<、!=
2. like + ‘带匹配符的字符串’(_ 匹配一个,**%**匹配多个);
3. column_name in(xx,xx) ;not in 不是指定数据中的一个
4. column_name between 1 and 3; 1到3之间（包含1、3）
5. 逻辑运算: and or not
6. is NULL 为空 、is not NULL 非空
7. IFNULL(column_name, x) 当列为null时用x替代

排序查询(order by 排序字段名 排序规则)

支持多个字段 order by xxx desc, xxx desc;先写的谁，就按照谁先排序

LIMIT 初始，记录数; 分页查询

1. select * from user limit 0,3

select max(column_name) from table_name ;聚合函数

1. count 求总
2. max 最大值
3. min 最小值
4. sum 求和
5. avg 平均值

group by 分组查询(常和聚合一起用)

select 字段名 from 表名 where 条件表达式 group by 分组名 having 分组搜索条件；eg：

select count(*),grade from rf_user

where id > 2

group by grade

having count(*) > 1

（1）在分组过程中，聚合函数会对每一组进行分别聚合。

（2）对于分组结果，可以进行二次筛选。使用having关键字，可以对分组出来的虚拟表，进行条件表达式的判断，将不符合条件的行排除掉。

（3）优先级：先where取出行，再GROUP BY分组，最后对分组结果进行having

having 条件表达式;（可筛选分组后的各组数据）

关联表

多表查询，只有一个字段，用in子查询串

select * from students 
where sno in 
(select sno from scores group by cno having MAX(degree))

外连接 union

连接两个select的结果，列数必须相同、列类型任意

select * from stu
union
select * from tea

一对一(用where就可以实现)

select e.no,e.name,x.address
from emp e, empext `x` 
where e.no = x.no

一对多

内连接：

-- inner join… on 条件表达式
select * from students s inner join classes c on s.cls_id = c.id;

左连接:

left join… on

以左表为基础，展示右表空值，左边放主表 （主表主键=从表外键）

1. 把左边的SQL写出来，括起来，别名 （主表）
2. 把右边边的SQL写出来，括起来，别名
3. left join
4. on 条件表达式
5. select from 条件表达式

SELECT d.dname,e.ename				-- 5
from 
(select deptno,dname from dept) d	-- 1
left join 							-- 3
(select ename,deptno from emp) e	-- 2
on d.deptno = e.deptno				-- 4 
-- 执行顺序

右连接:

right join … on (以右表为基础)

多对多

中间表

主表、从表、中间表（只有两个字段，分别对应主表和从表）

​ ps: 学生（主表） 课程（从表）

​ 中间表：学生表主键+课程表主键（本质就是两个外键）

思路

1. 多对多可以化成两个一对多
   • 一个学生可以有多门课程
   • 一门课程可以有多个同学进修

sql执行顺序

不是从头到尾执行，写时是一个字符串，

SQL引擎（mysql）拿到SQL语句不是先执行，而是先解析——树形结构，然后内部优化

8	SELECT distinct column[,...]		-- 选择，去重
6	agg_func(column or expression)[,...]-- 聚合函数
1	from left_table						-- 主表
3	left join right_table				-- 从表
2	on join_condition					-- 关联条件
4	where where_condition				-- 过滤条件
5	group by group_by_column			-- 分组
7	having having_condition				-- 分组过滤条件
9	order by order_by_column			-- 排序
10	limit count x,x;					-- 分页