一篇文章让你Mysql入门

基础操作

对用户操作

    登陆mysqlmysql -uroot -p密码
    修改密码 update mysql.user set authentication_string=password('12346') where user='root' and Host = 'localhost';
    刷新权限 flush privileges;
    exit; 退出链接

---

常用数据类型

数值

• tinyinnt 十分小的数据 1字节

• smallint 较小的数据 2字节

• int 4字节

• bigint 大的数据 8 字节

• float 4字节

• double 8字节

• decimal 字符串形式的浮点数 精度更高。

---

日期类

• date YYYY-MM-DD
• time HH:mm:ss
• datetime YYYY-MM-DD HH:mm:ss最常用
• timestamp 时间戳 1970.1.1到现在的毫秒数
• year 年份表示

---

字符串

• char
• varchar
• tinytext 微型文本 2^8 - 1
• text 文本串 2^16-1

---

数据库的字段属性

Unsigned

• 无符号的整数
• 声明了该列不能声明为负数

zerofill

• 0填充的
• 不足的位数用0来填充 例如：int（3） 5 -> 005

自增

• 通常理解为自增，按顺序+1
• 设置唯一主键
• 可以自定义设计主键的起始值和自增的步长

非空 NULL not null

• 假设设置为 not null ，不给他赋值就报错
• NUll 不填写 默认就是 null

默认

• 设置默认的值
• sex 默认值为男，不指定该列的值，就是默认为男

拓展

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zaT8Gn5K-1596546740931)(http://img.zzzyuan.cn/image-20200801081503913.png)]

---

数据库的引擎 innoDB（默认使用） 和 MYISAM

	MYISAM	INNODB
事务支持	no	yes
外键约束	no	yes
数据行锁定	no	yes
全文索引	yes	no
表空间大小	较小	较大，约为2倍

• 常规使用操作：
  • MYISAM 节约空间，速度快
  • INNODB 安全性高，事务的处理，

在物理空间存在的位置

所有的数据库文件都存在data目录下，本质是文件存储。

MYSQL引擎在物理文件的区别

• innoDB 在数据库上只有一个 *.fem 文件，以及上级目录下的ibadta1文件
• MYISQM 对应文件
  • *.frm 表结构的定义文件
  • *.MYD 数文件 （data）
  • .MYI 索引文件（index）

设置数据库的字符集编码

CHARSET=utf8

• 不设置的话是mysql的默认的字符集编码Latin1（不支持中文）
• 可以在my.ini 中设置默认编码
• 创表的时候可以设置编码格式

---

约束条件

• 主键
  唯一确定一张表的一条记录，不重复不为空
  就是同一属性只有一个值，并且不可以为空

  create table user（
  属性名 类型 primary key
  ）    
  

• 联合主键 两个属性加起来不重复

  create table user2（
  属性名1 类型
  属性名2 类型
  primary key（属性名1，属性名2）
  ）
  

• 自增

  create table 表名（
  属性名 类型名 primary key auto_increase
  ）

• 后期添加主键

  alter table 表名 add primary key（属性）

• 删除主键

  alter table 表名 drop primary key （一个表中只有一个主键）

• 修改字段，添加约束

  alter table 表名 modify 属性 类型 primary key

• 唯一约束

修饰不可重复

 alter table 表名 add unique（属性）;
            
 create table 表名（
 属性 类型
 unique（属性）
 ）;

删除唯一约束

alter table 表名 drop index 属性

修改唯一

alter table 表名 modify 属性 类型 unique

• 默认约束
  不传值时默认

  create table 表名（
   属性 类型 default 默认值
  ）
  

• 外键约束

  • 物理外键

    方法一、
      create table 表名1（
      属性1 类型 primary key 
      ）
       create table 表名2（
      属性2 类型 primary key
      key 属性名3 (属性52)
      constraint 属性3 foreign key（属性2）references 表名1（属性1）  
      ） 
    方法二、
    alter table 表二 add constraint 属性名 foreign key（属性2）references 表名1（属性1）  
      添加属性2时必须是属性1中已有的值
      并且表1的属性不能任意删除修改如果已经被表二中属性绑定
      删表时先删从表，再删主表（如上表二为从表，表一为主表）
    

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AjOr69bi-1596546740935)(http://img.zzzyuan.cn/image-20200801091737036.png)]

---

数据库的设计范式

• 第一范式

数据库中的所有字段都是不可分割的原子值
设计的越详细不一定越好

• 第二范式

必须满足第一范式
除主键外的每一列都需要完全依赖主键
如果出现不完全依赖，那只能是联合主键情况

create table 表名（
人名 类型
人年龄 类型
产品 类型
primary key（人名，人年龄）
）

除主键以外的其他属性，只依赖主键部分字段

– 拆表

create table 信息（
人名 类型 primary key
人年龄 类型 
） 
create table 产品表（
产品 类型 primary key
）

• 第三范式

必须满足第二范式
除开主键列的其他列之间不能有传递依赖关系

---

查询总结

 select * form 表名 |查询表中的所有记录
 
 select 属性，属性 from 表名 |查询部分属性
 
 select distinct 属性 from 表名 |查询不重复的属性
 
 select * from 表名 where 属性 between 小 and 大 |查询一个范围内的属性
   
  select * from 表名 属性 >小 and 属性 < 大
  
  select * from 表名 where 属性 in（指定值，指定值） |查询某属性的指定数据
  
  select * from 表名 where 属性 ="x" or 属性 = "y" |查询某属性x 或y的具体数据
  
  select * from 表名 order by 属性 desc | 降序查询相关属性 
  
  select * from 表名 order by 属性 asc | 升序查询相关属性
  
  select * from 表名 order by 属性 asc ，属性2 desc| 升序查询属性1相关属性，如果相同则属性而降序排序
  
  select count（*） form 表名 where 属性 = "数据" |统计某一属性的某一数据量
  
  select 属性1，属性2 from 表名 where 属性3 = （select max（属性3）from 表名）|查找数据三最大值时属性2和1的值
  
  select 属性 limit
  
  ---avg（）求平均
  select  avg（属性）from 表名 where 属性2 |查询属性2的属性1的平均成绩 
  
  ---group by 分组
  select 属性1，avg（属性2）from 表名 group by 属性1

---

对数据库的操作

注意点：

1. `` 字段名推荐用 这个包裹

2. sql不分大小写，推荐小写

3. 所有的符号全英文

查询数据库 show databases

选择数据库 use xx

 查询select * from xxx
     select * from xxx where 具体的xxx
 
 退出数据库 exit
 
 创建数据库 create database ***
 
 查询表 show tables；
 
 创建表  create table 表名(属性名 类型(字节数));
 
 查看创建数据库的语句 show create database school
 
 查看student 数据表的定义语句 show create table student
 
 查看表的结构 desc 表名

​

### DML语言

insert

• 插入数据 insert into 表名(属性名，属性名) values（“数据”，“数据”）；

• 插入多组数据insert into 表名(属性名，属性名) values（“数据”，“数据”），（“数据”，“数据”），（“数据”，“数据”）；

• 省略属性插入数据insert into 表名values（“数据”，“数据”），（“数据”，“数据”），（“数据”，“数据”）；

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Gpy0UPCP-1596546740940)(http://img.zzzyuan.cn/image-20200801093922615.png)]

update

• 修改数据(不指定条件的情况修改所有属性) update 表名 set 属性名 = “具体的数据”;
• 修改数据(指定条件的情况修改指定条件的属性属性) update 表名 set 属性名 = “具体的数据” where 属性2 = ‘数据’;
• 修改多个属性 update 表名 set 属性名 = “具体的数据”，属性名 = “具体的数据” where 属性2 = '数据’

操作符	含义	范围
=	等于	5=6
<> 或者 !=	不等于
>
<
<=
>=
between … and …	在某个范围内	[…，…]
and	和	x > 1and x<5
or	或	x >1 or x < 5

delete

• 删除 delete from 表名 where 属性 = “删除的数据”

truncate

• 清空某一张表 truncate 表名。
• 完全清空一个数据库表，表的结构和索引约束不变

delete 和 truncate 区别

• 相同：都能删除表数据，不修改表结构

• 不同：

  • delete 不会影响自增变量 。

    删除后

    • innoDB 重启数据库后自增变量重置（保存在内存中，断电即失）
    • 而MYISAM继续从上一个自增量开始（保存在文件中，不会丢失）

  • truncate 会重置自增变量 ，不会影响事务

---

DQL查询数据

数据查询语言

• 所有的查询都用它 select
• 简单的查询，复杂的查询它都能做
• 数据库最核心的语言
• 使用频率最高

基本查询

select 字段 from 表

• select * from 表 查询所有的表中数据
• select 字段名 as 别名 from 表名 查询出来的数据显示别名，可以给字段或者表起别名
• 函数 concat（a,b）
  • select concat(“名字”，字段名) as 别名 from 表名 查询出来的结果都是名字 + 字段名（千万记得引号和斜引号的区别）

进阶查询

• select version() 查询系统版本
• **select @@auto_increment_increment **查询自增步长

数学计算

• **select 100*3-1 ** 计算结果

结果显示修改查看（+1分查看）

• select 字段 +1 from 表名

去重

• **select distinct（字段）from 表名 ** 发现重复数据，去重

where条件子句

运算符	语法	描述
and &&	a and b a && b	与
or ||	a or b a || b	或
Not ！		非

模糊查询

运算符	语法	描述
is null	a is null	如果操作符为null，结果为真
is not	a is not null	如果操作符不为null，结果为真
between	a between b and c	如果 a 在 b和 c之间 ，return true
like	a like b	sql匹配，如果a匹配b，return true
in	a in（a1,a2,a3）	如果a在a1,a2,a3值中，return true

like

• SELECT 字段1,字段2 FROM 表名 WHERE 字段1 LIKE ‘赵_’ 字段1以赵开头，且后面只有一个字，_ 下划线数量代表字数。
  • SELECT 字段1,字段2 FROM 表名 WHERE 字段1 LIKE ‘赵%’ 字段1以赵开头。
  • SELECT 字段1,字段2 FROM 表名 WHERE 字段1 LIKE ‘%其%’ 字段1 中有其字。

in

• SELECT 字段1,字段2 FROM 表名 WHERE 字段1 in (1,2,3) 查询字段1为1或2或3

关联查询

on后面的是连接条件，代表两个表建立关系所遵循的规则

where后面的可以看作是筛选条件，是对最终结果集进行过滤所遵循的规则

inner join

查出并集

• **select 别名1.字段1(字段1在表一表二中都有，因此要确定那个表的字段一，即用别名.字段1)，字段2，字段3**
  
   **from 表一 as 别名1 **
  
  **inner join 表二 as 别名2** 
  
   **on 别名1.字段一 = 别名2.字段一**  
  

right join

• **select 别名1.字段1(字段1在表一表二中都有，因此要确定那个表的字段一，即用别名.字段1)，字段2，字段3**
  
   **from 表一 as 别名1 **
  
  **right join 表二 as 别名2** 
  
   **on 别名1.字段一 = 别名2.字段一**  
  

lifft join

• **select 别名1.字段1(字段1在表一表二中都有，因此要确定那个表的字段一，	即用别名.字段1)，字段2，字段3**
  
   **from 表一 as 别名1 **
  
  **lift as 别名2** 
  
   **on 别名1.字段一 = 别名2.字段一**  
  

操作	描述
inner join	如果表中至少有一个匹配，就返回行
left join	会从左表中返回所有的值，即使右表中没有匹配
right join	会从右表中返回所有的值，即使左表中没有匹配

自连接

自己的表和自己的表连接，核心：一张表拆成两张一样的表即可

• 父类

categoryId	categoryName
2	信息技术
3	软件开发
5	美术设计

• 子类

categoryID	categoryName	pid
4	数据库	3
8	办公信息	2
6	web开发	3
7	ps技术	5

操作：父类对应的子类

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

select 父.字段 , 子.字段 
from 父表 as 别名 父 , 子类 as 别名 子
where 父.字段1 = 子.字段2

排序 和分页

降序（desc）和升序（asc）

select 字段
from 表名
inner join 表名 /....
on 条件
where 条件
order by 字段 desc / asc --严格按照以上顺序

分页

• limit 起始值 ，页面大小

• 第一页 limit 0,5 （每页显示5条数据）

• 第二页 limit 5,5

• 第三页 limit 10,5

• 第N页 limit （N-1）*5,5

• 总页数为 数据总数 + （每页数据量-1）/每页数据量

---

函数

常用函数

数学运算

abs(-8) --绝对值
ceiling(9,4) --向上取整
floor(9,4) -- 向下取整
rand() -- 返回一个 0-1之间的随机数
sign() -- 判断一个数的符号 

字符串

char_length() --返回字符串长度
concat() --拼接字符串
insert() --插入字符串，或者替换
lower() -- 转大写
upper() -- 转小写
instr() -- 返回第一次出现子串的位置
replace() -- 替换出现的字符串
substr() -- 返回指定的字符串
reverse() -- 翻转字符串

时间和日期函数

current_date() -- 获取当前日期
current() -- 获取当前日期
now() -- 获取当前的时间
localtime() -- 获取本地时间
sysdate() -- 获取系统时间

聚合函数

函数名称	描述
count（）	计数
sum（）	求和
avg（）	平均
max（） / min()	最大/最小

分组和过滤

• where中不能有聚合函数 可以使用having

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fONKVbwD-1596546740951)(http://img.zzzyuan.cn/image-20200801170028819.png)]

---

数据库级别的md5 加密

md5主要增强算法复杂度和不可逆性

md5不可逆，具体的值md5是一样的

CREATE TABLE `testmd5`(
	`id` INT(4) NOT NULL,
	`name` VARCHAR(20) NOT NULL,
	`pwd` VARCHAR(50) NOT NULL,
	PRIMARY KEY(`id`)
)DEFAULT CHARSET=utf8

INSERT INTO testmd5 VALUES(1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wanwu','123456');

UPDATE testmd5 SET pwd =MD5(pwd) --加密全部密码

INSERT INTO testmd5 VALUES(1,'zhangsan',MD5('123456')); --边插入边加密

---

事务

什么是事务

事务的原则性 （原子性，一致性，持久性，隔离性）

原子性

要么都成功，要么都失败

一致性

事务前后的数据保持一致

持久性

事务一旦提交就不可逆，被持久化到数据库中

隔离性

事务的隔离是多个用户并发访问数据库时，数据库为每一个用户开启的事物，不能被其他事物操作的数据所干扰，事物之间要互相隔离。

隔离导致的一些问题

• 脏读：指一个事物读取了另一个事物未提交的数据。
• 不可重复度：在一个事物内读取表中的某一行数据，多次读取的结果不同（这个不一定是错误，只是某些场合不对）
• 虚读（幻读）：是指一个事物内读取到了别的事物插入的数据，导致前后读取不一致。

事物的实现

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-f45wYClf-1596546740952)(http://img.zzzyuan.cn/image-20200801174003471.png)]

---

索引

mysql 官方对索引的定义为：索引是帮助mysql高效获取数据的数据结构。提取句子的主干，就可以得到索引的本质：索引是数据结构。

索引的分类

• 主键索引
  • 唯一标识，主键不可重复，只能有一个列作为主键。
• 唯一索引
  • 避免重复的列出现，唯一索引可以有多个，多个列都可以标识为唯一索引
• 常规索引
  • 默认的 index，key 关键字来设置
• 全文索引
  • 在特定的数据库引擎下才有，MYISAM
  • 快速定位数据

索引的使用

• 创建时指定索引
• 创建完毕后添加索引

显示所有索引

show index from 表名

增加一个全文索引

alter table 表名 add fulltext index 索引名(字段名) -- 一般两个名字相同

explain 分析sql执行的情况

explain select * from 表名 -- 非全文索引

explain select * from 表名 where match(字段名) against(特定条件 例如：字段名中有刘)

索引测试

CREATE TABLE `app_user`(
	`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) DEFAULT '' COMMENT '用户昵称',
	`email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',
	`phone` VARCHAR(20) DEFAULT '' COMMENT'手机号',
	`gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别（0男，1女）',
	`password` VARCHAR(100) NOT NULL COMMENT '密码',
	`age` TINYINT(4) DEFAULT '0' COMMENT '年龄',
	`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'




DELIMITER $$ --写函数前必写
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	
	WHILE i<num DO
	
	INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
	VALUES (CONCAT('用户',i),CONCAT('123456',i,'@qq.com'),CONCAT('18',FLOOR(RAND()*10000000)),
	FLOOR(RAND()*2),UUID(),FLOOR(RAND()*2));
	
	SET i = i+1;
		
	END WHILE;
	RETURN i;
	
END;

SELECT mock_data(); -- 执行函数


SELECT * FROM app_user WHERE `name` = '用户9999' -- 查询速度极慢0.7s

EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999' -- 查询了9700多条

-- id_表名_字段名
-- create index 索引名 on 表（字段）
CREATE INDEX id_app_name ON app_user(`name`); -- 添加索引

SELECT * FROM app_user WHERE `name` = '用户9999' -- 添加索引后查询数独为0.001s

EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999' -- 查询了一条

索引原则

• 索引不是越多越好
• 不要对经常变动的数据加索引
• 小数据量的表不需要加索引
• 索引一般加在常用来查询的字段上

索引的数据结构

• hash 类型的索引
• btree ：innoDB 的默认数据结构

---

权限管理与备份

权限管理

--创建用户
create user 用户名 identified by '密码'

-- 修改密码
	-- 当前用户密码
	set password = '密码'
	-- 修改指定用户密码
	set password for 用户名= password('密码')

-- 重命名
pename user 原名 to 新名

-- 用户授权
	--授予全部权限（除了给其他人权限）
	grant all privileges *.* to 用户名 

-- 查看权限
	--查看非管理员的权限
	show grants for 用户名 
	--查看管理员的权限
	show grants for roo@localhost

-- 撤销权限
	-- 撤销所有权限
	revoke all privileges on *.* from 用户名	
	
-- 删除用户
drop user 用户名
	

数据库备份

• 直接拷贝物理文件

• 在可视化工具中手动导出

• 使用命令行导出

  • mysqldump -h主机 -u用户名 -p密码 数据库 表名1 表名2 地址和文件名 （加表名导出表，不加表名导出数据库）

• 使用命令行导入

  • 登录账户
  • source 备份文件地址

---

规范数据库的设计

为什么需要设计

• 糟糕的数据库设计
  • 数据冗余，浪费空间
  • 数据库插入和删除都会麻烦，异常【屏蔽使用物理外键】
• 良好的数据库设计
  • 节省内存空间
  • 保证数据库的完整性
    • 方便我们开发系统

软件开发中，关于数据库的设计

• 分析需求：分析业务和需要处理的数据库的需求

  • 概要设计：设计关系图 E-R 图

  设计数据库的步骤

  • 收集信息，分析需求

  • 用户表（用户的登录注销，用户的个人信息，写博客，创建分类）

    • 分类表（文章的分类，作者）
    • 文章表（文章的信息）
    • 友链表

    标识信息