mysql总结

最新推荐文章于 2023-02-06 15:45:10 发布
最新推荐文章于 2023-02-06 15:45:10 发布
阅读量733 收藏 5
点赞数
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/junjie886/article/details/104861725
版权

mysql

1、初识mysql

javaEE：企业级java开发 web

前端（页面，展示，数据）

后台（连接：连接数据库JDBC，连接前端（控制，控制视图跳转，给前端传递数据））

数据库（存数据）

1、只会写代码，学好数据库，基本混饭吃；
2、操作系统，数据结构与算法；当一个不错的程序员！
3、离散数学，数字电路，体系结构，编译原理+实战经验，高级程序员~优秀的程序员

1.1 为什么要学习数据库

1、岗位需求

2、现今为大数据时代，得数据者得天下

3、存数据（数据持久化）

4、数据库是所有软件体系中最核心的存在 DBA（数据库管理员Database Administrator）

1.2 什么是数据库

数据库（DB Database）

概念：数据仓库，是一款软件，安装在操作系统（windows ，linux…）之上

作用：存储数据，管理数据 。类似 Excel

1.3 数据库分类

关系型数据库：Excel（行、列）

  • MySQL , Oracle , Sql Server , DB2 , SQLlite
  • 通过表与表之间，行和列之间的关系进行数据的存储
  • SQL

非关系型数据库：（key：value）

  • Redis ， MongDB
  • 非关系型数据库，对象存储，通过对象的自身的属性来决定
  • NoSQL(NotOnly SQL)

1.4 DBMS

DBMS：数据库管理系统（Database Management System）

​ 一种操纵和管理数据库的大型软件，用于建立、使用、维护数据库…

1.5 MySQL简介

MySQL是一个关系型数据库管理系统

前世：瑞典MySQL AB公司

今生：属于Oracle旗下产业

  • MySQL：mysql是当前最流行的关系型数据库管理系统之一，为WEB应用方面，MySQL是最好的RDBMS(Relational Database Management System , 关系型数据库管理系统)应用软件之一
  • MySQL所使用的语言为SQL语言，用于访问数据库的最常用标准化语言
  • MySQL体积小，速度快，总体拥有成本低，并且开源，为中小型及较大、大型网站开发的选择标准
  • 集群开发

官网：https://www.mysql.com

1.6数据库语言

SQL语言分为五大类：

  • DDL（数据定义语言 Data Definition Language）：create、atlter、drop等语句自动提交，无需commit提交
  • DML（数据操作语言 Data Manipulation Language）：insert、update、delete等语句需要commit才能提交
  • DQL（数据查询语言 Data Query Language）：select 等查询语句不存在提交问题
  • DCL（数据控制语言 Data Control Languag ）：grant、revoke等授予权限与回收权限语句
  • DTL（数据事务语言 Data Transaction Language）：commit、rollback等事务提交与回滚语句。

锁有很多种，一般我们关注的都是DML操作产生的，比如insert，delete，update，select…for update都会同时触发表级锁和行级锁

**补充：对的，insert以后commit之前是锁表的状态，其他事务无法对该表进行操作。 **

2、安装MySQL

安装建议：

1、尽量不要使用exe安装，卸载麻烦

2、尽可能使用压缩包安装

教程：https://www.cnblogs.com/hellokuangshen/p/10242958.html

2.1 MySQL下载

mysql5.7 64位下载地址:

https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-winx64.zip

2.2 操作步骤

1、下载后得到zip压缩包

2、解压缩到自己的安装目录下

3、配置环境变量

​ 1、我的电脑 -> 属性 -> 高级系统设置 -> 环境变量

​ 2、选择path -> 添加 -> 路径为mysql根目录下的bin文件夹（E:\mysql-8.0.18-winx64\mysql-8.0.18- winx64\bin）

4、编辑my.ini文件，注意替换路径位置

[mysqld]
#指定MySQL的基础目录
basedir=E:\mysql-8.0.18-winx64\mysql-8.0.18-winx64\
#指定MySQL的数据库存储的目录
datadir=E:\mysql-8.0.18-winx64\mysql-8.0.18-winx64\data\
#指定MySQL的端口号
port=3306
#设置默认的字符集编码
character-set-server=utf8
#跳过密码登录
skip-grant-tables

5、启动管理员模式的CMD，并切换到MySQL的bin目录下，然后输入mysqld -install （安装MySQL）

#cmd命令
# 1、切换到MySQL的bin目录下
cd /d E:\mysql-8.0.18-winx64\mysql-8.0.18-winx64\bin
# 2、安装MySQL
mysqld -install

6、初始化数据库文件

mysqld --initialize-insecure --user=mysql

7、启动MySQL服务，登录mysql（切记不要输入密码）， 进入mysql管理界面

8、修改root密码，刷新权限

#修改root密码
update mysql.user set authentication_string=password('root123') where user='root' and Host='localhost';
#刷新权限
flush privileges;

9、 修改 my.ini文件删除最后一句skip-grant-tables

10、重启MySQL服务即可正常使用

net start mysql
net stop mysql

11、连接上测试出现以下结果为ok
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-G5kQL2VC-1584172249778)(image\1583079081465.png)]

3、字段的数据类型

3.1 数值类型

数值 类型

类型大小范围（有符号）范围（无符号）
tinyint1 字节(-128，127)(0，255)
smallint2 字节(-32 768，32 767)(0，65 535)
mediumint3 字节(-8 388 608，8 388 607)(0，16 777 215)
int4 字节(-2 147 483 648，2 147 483 647)(0，4 294 967 295)
bigint8 字节(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)(0，18 446 744 073 709 551 615)
float4 字节(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)0，(1.175 494 351 E-38，3.402 823 466 E+38)
double8 字节(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
decimal对于decimal(M,D) ，如果M>D，为M+2否则为D+2依赖于M和D的值依赖于M和D的值

注意：decimal数据类型运用与金融计算方面（精度问题）

3.2 时间日期类型

时间日期类型

类型大小范围格式
date3字节1000-01-01到9999-12-31YYYY-MM-DD
time3字节‘-838:59:59’到’838:59:59’HH:MM:SS
year1字节1901到2155YYYY
datetime8字节1000-01-01 00:00:00到9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS
timestamp4字节1970-01-01 00:00:00到2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07YYYYMMDD HHMMSS

timestamp：时间戳 ，1970.1.1到现在的毫秒数

3.3 字符类型

字符类型

类型大小用途
char0-255字节定长字符串
varchar0-65535 字节变长字符串
tinyblob0-255字节不超过 255 个字符的二进制字符串
tinytext0-255字节短文本字符串
blob0-65535字节二进制形式的长文本数据
text0-65535字节长文本数据

4、数据库字段的属性（重点）

4.1 主键

主键（primary key ）：主要的键，一张表只能有一个字段可以使用对应的键，用来唯一的约束该字段里面的数据，不能重复
或者可以由多个字段联合主键，但数据不能完全相同

4.2 自动增长

新增自增长（auto_increment ）：当对应的字段，不给值或者说给默认值，或者给null的时候，会自动的被系统触发，系统会从当前字段中已有的最大值再进行+1操作，得到一个新的在不同的字段

自增长特点：auto_increment

  • 任何一个字段都要做自增长必须前提是本身是一个索引（key一栏有值）
  • 自增长字段必须是数字（整型）
  • 一张表最多只能有一个自增长

4.3 唯一键

唯一键（unique）：一张表往往有很多字段需要具有唯一性，数据不能重复：但是一张表中只能有一个主键，唯一键就可以解决表中有多个字段需要唯一性约束的问题。

唯一键的本质和主键差不多：唯一键默认的允许自动为空，而且可以多个为空（空字段不参与唯一性比较）

4.4 非负

非负（Unsigned）：

  • 无符号整数
  • 声明unsigned的字段不能为负数

4.5 零填充

零填充（zerofill）：

  • 0填充的
  • 字段的数据类型必须为整型
  • 字段设置该属性时，不足的位数，使用0来填充
  • int（5） 00001

4.6 非空

非空（not null）：

  • 字段定义该属性时，插入数据时必须有数据，否则报错

4.7 默认值

默认值（default）：

  • 字段设置该属性时，插入数据时不指定数据，则默认给定数据

5、数据库引擎（重点）

  • InnoDB：节约空间，速度较快
  • Myisam：安全性较高，支持事务，支持多表多用用户操作
MYISAMINNODB
事务的支持不支持支持
数据行锁定不支持支持
外键约束不支持支持
全文索引支持不支持
表空间大小较小约为MYISAM两倍

在物理空间存在的位置

所有的数据库文件都存在data目录下

本质还是文件的存储

MySQL引擎在物理文件上的区别

  • InnoDB在数据库表中只有一个xxx.frm文件，以及上级目录下的ibdata1（所有InnoDB数据库表共享ibdata1）
  • MYISAM对应文件
    • xxx.frm : 表结构的定义文件
    • xxx.MYD : 数据文件（data）
    • xxx.MYI ：索引文件（index）

6、基本数据库命令操作

注释：

  • SQL 单行注释

    ​ – 注释…

  • SQL多行注释

    ​ /*

    ​ 注释…

    ​ */

6.1 基本操作

连接MySQL与退出

--连接数据库
-- mysql -u用户名 -p密码 
mysql -uroot -proot123

-- 退出
exit ;

修改密码

update mysql.user set authentication_string=password('新密码') where user = 'root' and Host='localhost'; --修改密码
flush privileges; --刷新权限

6.2 数据库操作

查看所有的数据库

show databases; --查看所有的数据库

切换使用数据库

-- use 数据库名
use `admin`;

创建数据库

--create database [if not exists] 数据库名 [character set 字符集] [collate 排列规则]
create database `study` character set utf8 collate utf8_general_ci;

查看创建数据库的语句

--show create database `数据库名`
show create database `admin`;

删除数据库

-- drop database [if exists] 数据名
drop database if exists `study`;

6.3 数据库表操作

查看所有的表

show tables; --查看单个数据库中所有的表

查看创建表的语句

--show create table `表名`
show create table `admin`;

查看表的结构

-- describe 表名 
describe `role` ;

创建表

/*
	create table [if not exists] `表名`(
		`字段名1` 数据类型 [属性] [索引] [注释],
		`字段名2` 数据类型 [属性] [索引] [注释],
		...
		`字段名n` 数据类型 [属性] [索引] [注释]
	)[表类型] [自增步长] [字符集] [注释]
*/
-- engine = innoDB 设置引擎
-- auto_increment=1 设置自增步长为 1
-- default charset=utf8 设置默认字符集为utf8
create table if not exists `student`(
	student_id int(10) not null auto_increment comment '学生ID', 
	student_name varchar(60) not null default '匿名' comment '学生姓名',
	student_age int(3) not null default 3 comment '学生年龄' ,
    student_sex int(2) not null default 0 comment '学生性别' ,
    birthday datetime not null comment '生日' ,
	primary key (student_id)
)engine=innoDB auto_increment=1 default charset = utf8 comment '学生信息表'

修改表

-- 修改表名
-- alter table `旧表名` rename as `新表名`
alter table `role` rename as `permission`;

--添加表字段(在某字段后添加)
--alter table `表名` add `新字段名` 数据类型 [属性] [索引] [注释] [after `某字段名`]
alter table `role` add `role_name` varchar(20) default '匿名' unique comment '角色名字' after "role_id" ;

--修改已存在表字段的属性、索引等
--alter table `表名` modify `字段名` 数据类型 [属性] [索引] [注释]
alter table `role` modify `role_name` varchar(200) default '匿名' comment '角色名字' ;

--重命名字段名
--alter table `表名` change `旧字段名` `新字段名` 数据类型 [属性] [索引] [注释]
alter table `role` change `role_name` `role_age` int(2) default 3 comment '角色名字' ;

--删除字段
--alter table `表名` drop `字段名`
alter table `role` drop `role_age` ;

modify不能重命名，只能修改约束，数据类型 change可重命名

添加表字段

-- alter table `表名` rename as `新表名`
alter table `role` rename as `permission`;

删除表

-- drop table [if exists] 表名
drop table `role` ;

7、MySQL数据管理

DML语言（Data Manipulation Language）

  • 添加数据
  • 修改数据
  • 删除数据

7.1 插入语句

insert语句

--插入一条数据
/*	
	insert into `表名`([字段名1],[字段名2],[字段名3], ... ,[字段名n]) 
	values ('值1','值2','值3', ... , '值n',)
*/
insert into `role` (`role_name`,`role_url`) 
values ("经理","D:/user/xxx/managerment.java")

--插入多条数据
/*
	insert into `表名`([字段名1],[字段名2],[字段名3], ... ,[字段名n]) 
	values  ('值1','值2','值3', ... , '值n',) , 
			('值1','值2','值3', ... , '值n',) ,
            	... ,
			('值1','值2','值3', ... , '值n',)
*/

insert into `role` (`role_name`,`role_url`) 
values ("经理","D:/user/xxx/managerment.java"),("总裁","D:/user/xxx/boss.java") ;

7.2 修改语句

update语句

/*
	update `表名` set 
	[`字段名1`='值1'],[`字段名2`='值2'], ... , [`字段名n`='值n'] [where 条件判断]
*/
update `role` set `role_name`='组长' where `role_id` = 1 ;

注意，没有加条件修改则修改所有数据

操作符含义范围结果
=等于5=6false
<> 或 !=不等于5=6true
>大于5>6false
<小于5<6true
>=大于或等于5>=6false
<=小于或等于5<=6true
between…and…在某个范围内[5,6]
and与5>1 and 1>2false
or或5>1 or 1>2true
not非5 not 5false

7.3 删除语句

delete语句

--delete from `表名` [where 条件判断]
delete from `role` where `role_id` between 1 and 2 ;

注意，没有加条件修改则修改所有数据

truncate语句

--truncate [table] `表名`
truncate `role`

作用：完全清空数据库表的所有数据，标得结构和索引约束不会变！

delete与truncate的区别

  • 相同点：都能删除数据，都不会删除表结构
  • 不同点：
    • truncate 重新设置自增字段 ，计数器会归零
    • truncate 不会影响事务

8、查询数据（最重点）

DQL语言(数据查询语言 Data Query Language)

  • 所有的查询操作都使用 select
  • 简单的查询，复杂的查询
  • 数据库中最核心的语言，最重要的语句
  • 使用频率最高的语句

8.1 select语句

查询通用

--select 表达式（函数 , 计算表达式 , 字段 , 变量 , null）


--select 函数 [as 别名] 
--查询版本号
select version() as '版本' ;

--select 计算表达式 [as 别名]
--查询5*8-1
select 5*8-1 as '结果' ;
select `role_id`+1 as 'id' from `role` ;

--select 变量 [as 别名]
--查询自增步长
select @@auto_increment_increment ;

查询所有的字段

--select * from 表名
select * from `role` ;

查询指定的字段

--select `字段1`,`字段2`,`字段3`, ... ,`字段n` from `表名`
select `role_name`,`role_age` from `role` ;

查询字段并以别名展示（as）

/*
select `字段1` as '别名1',`字段2` as '别名2',`字段3` as '别名3', ... ,`字段n` as '别名n' from `表名`
*/
select `role_name` as '角色名称',`role_age` as '角色年龄' from `role` as '角色表' ;

函数concat（字符拼接）

--concat( str1(字段1),str2(字段2),str3(字段3),... )可以拼接查询的字段和字符
select concat('姓名为：',`role_name`) as '姓名' from `role` ;
--结果为 姓名为：xxx

查询并去重复，字段值相同只显示一个（distinct）

--select distinct `字段1`,`字段2`,`字段3`, ... ,`字段n` from `表名`
select distinct `role_name` from `role` ;

注意：查询一个字段只要相同就去重只显示一个，查询两个或多个字段时，需所有字段完全相同才去重

8.2 where 条件字句

where关键字 作用：检索数据中 符合条件 的值

逻辑运算符

运算符语法描述
and 或 &&a and b 或 a && b逻辑与，两个为真，结果为真
or 或 ||a or b 或 a || b逻辑或，一个或两个为真，结果为真
not 或 ！not a 或 ! a逻辑非，a为真，结果为假

1、and

--查询学生，性别为女和年龄大于23岁
select * from `student` where `sex` = 0 and `age` > 23 ;

2、or

--查询学生，性别为女或者年龄大于23岁
select * from `student` where `sex` = 0 or `age` > 23 ;

3、not

--查询学生，年龄不等于23岁
select * from `student` where not `age` = 23 ;

模糊查询：比较运算符

运算符语法描述
is nulla is noll如果a为空，则结果为真
is not nulla is not null如果a不为空，则结果为真
between…anda between b and c若a在[b,c]之间，则结果为真
likea like bSQL匹配，匹配成功，则结果为真
ina in ( a1,a2,a3, … )a在a1，a2，… 中的某一个，则结果为真

1、is null

--查询年龄为空的学生
select * from `student` where `age` is null ;

2、is not null

--查询年龄不为空的学生
select * from `student` where `age` is not null ;

3、between…and…

--查询年龄在23到28的学生
select * from `student` where `age` between 23 and 28 ;

4、like

--查询姓'刘'的学生
select `student_name` from `student` where `student_name` like '刘%' ;

--查询姓名中带有'刘'字的学生
select `student_name` from `student` where `student_name` like '%刘%' ;

--查询姓名中第二个字带有'刘'字的学生 
select `student_name` from `student` where `student_name` like '_刘%' ;

like 结合 ‘’%’’ 和’’_ ‘’ 使用 。其中’’%’‘代表0到任意个字符，’’_’'代表一个字符

5、in

--查询在(1,2,3,4)的学生
select * from `student` where `student_id` in (1,2,3,4) ;

8.3 联表查询

多表查询时,一定要找到两个表中相互关联的字段,并且作为条件使用

  • 内连接查询（inner join…on…）: 只显示符合条件的数据
  • 左连接查询（left join…on…）: 左表中的数据优先全部显示
  • 右连接查询（right join…on…）: 右表中的数据优先全部显示
操作描述
left join返回的值两表都需要匹配
right join会从左表中返回所有的值，即使右表中没有匹配
inner join会从右表中返回所有的值，即使左表中没有匹配

1、inner join … on …

/*
	select 字段 from `表1` as '别名1' 
	inner join `表2` as '别名2' on 别名1.字段 = 别名2.字段
	inner join `表3` as '别名3' on 别名1.字段 = 别名3.字段(别名2.字段 = 别名3.字段)
	...
	inner join `表n` as '别名n' on 别名(1,2,3,...,n-1).字段 = 别名n.字段
	[where 条件判断]
*/
--查询参加考试的学生(学号，学生姓名,成绩)
select s.`number` , s.`name` , r.`result` from `student` as s 
inner join `student_result` as r 
on s.`number` = r.`number` ;

--查询参加考试的学生且成绩大于80分(学号，学生姓名,成绩)
select s.`number` , s.`name` , r.`result` from `student` as s
inner join `student_result` as r 
on s.`number` = r.`number` 
where r.`result` >80 ;

--多表查询
--查询参加java课程考试的学生且成绩大于80分(学号，学生姓名,成绩)
select s.`number` , s.`name` ,r.`result` from `student` as s
inner join `student_result` as r on s.`number` = r.`number` 
inner join `subject` as sub on r.`subject_id` = sub.`subject_id`
where r.`result` > 80 and sub.`sub_name` = 'java';

2、left join … on …

/*
	select 字段 from `表1` as '别名1' 
	left join `表2` as '别名2' on 别名1.字段 = 别名2.字段
	left join `表3` as '别名3' on 别名1.字段 = 别名3.字段(别名2.字段 = 别名3.字段)
	...
	left join `表n` as '别名n' on 别名(1,2,3,...,n-1).字段 = 别名n.字段
	[where 条件判断]
*/
--查询参加考试的学生(学号，学生姓名,成绩)
select s.`number` , s.`name` , r.`result` from `student` as s 
left join `student_result` as r 
on s.`number` = r.`number` ;

--查询参加考试的学生且成绩大于80分(学号，学生姓名,成绩)
select s.`number` , s.`name` , r.`result` from `student` as s
left join `student_result` as r 
on s.`number` = r.`number` 
where r.`result` >80 ;

--多表查询
--查询参加java课程考试的学生且成绩大于80分(学号，学生姓名,成绩)
select s.`number` , s.`name` ,r.`result` from `student` as s
left join `student_result` as r on s.`number` = r.`number` 
left join `subject` as sub on r.`subject_id` = sub.`subject_id`
where r.`result` > 80 and sub.`sub_name` = 'java' ;

3、right join … on …

/*
	select 字段 from `表1` as '别名1' 
	right join `表2` as '别名2' on 别名1.字段 = 别名2.字段
	right join `表3` as '别名3' on 别名1.字段 = 别名3.字段(别名2.字段 = 别名3.字段)
	...
	right join `表n` as '别名n' on 别名(1,2,3,...,n-1).字段 = 别名n.字段
	[where 条件判断]
*/
--查询参加考试的学生(学号，学生姓名,成绩)
select s.`number` , s.`name` , r.`result` from `student` as s 
right join `student_result` as r 
on s.`number` = r.`number` ;

--查询参加考试的学生且成绩大于80分(学号，学生姓名,成绩)
select s.`number` , s.`name` , r.`result` from `student` as s
right join `student_result` as r 
on s.`number` = r.`number` 
where r.`result` >80 ;

--多表查询
--查询参加java课程考试的学生且成绩大于80分(学号，学生姓名,成绩)
select s.`number` , s.`name` ,r.`result` from `student` as s
right join `student_result` as r on s.`number` = r.`number` 
right join `subject` as sub on r.`subject_id` = sub.`subject_id`
where r.`result` > 80 and sub.`sub_name` = 'java';

8.4 分页

分页（limit）：

语法: limit 开始的数据 , 分页大小

--select 字段 from 表名 limit 起始值 , 条数
--查询所有的学生并分页，起始值为第0个，每页2条数据
select * from `student` limit 0 , 2 ;

pageNum：页码 pageSize：条数 n：起始值

n = (pageNum-1)*pageSize

总页数=数据总数/条数

8.5 排序

排序（order by）：

  • 升序：asc
  • 降序：desc
--select 字段 from 表名 order by 字段 asc(desc)
--查询所有的学生根据成绩从高到底
select * from `student` order by `result` desc ;

8.6 子查询与嵌套查询

子查询就是嵌套在主查询中的查询 (查询由里及外执行)

子查询可以嵌套在主查询中所有位置，包括select、from、where、group by、having、orfer by

常用(select , where )

1、 select

--学生信息和班级名称位于不同的表中，要在同一张表中查出学生的学号、姓名、班级名称
select `number` , `name` , (select `class_name` from `class` 
                           where `class_id`= s.`class_id`)
from `student` as s ;

2、where

--要查出java成绩比李四高的学生的信息
select * from `student` 
where `subject` = 'java' 
and `score` > ( select `score` from `student` where `name`='李四' and 							`subject`='java' ) ;

8.7 MySQL函数

官网：https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

1、常用函数：

select abs(-89) --绝对值
select ceiling(90.2) --向上取整
select floor(90.2) --向下取整
select rand() --返回一个 0~1之间的随机数
select sign(-5) --判断一个数的符号 ，返回0或-1或1

2、字符串函数：

select char_length("xxxxxxx") --字符串长度
select concat('a','b','c', ...)  -- 字符串拼接
select lower("XXXXXXX") --小写
select upper("xxxxx")  --大写
select instr('junjie','n') --返回第一次出现的子字符的索引
select replace('junjie','j','m') --替换字符
select substr("junjie" , 2 , 5) --截取字符串 2为开始位置,5为截取的长度
select reverse('junjie') --反转字符串

3、日期函数：（c记住）

select current_date(); 	--当前日期
select now();			--当前时间
select localtime()		--本地时间
select sysdate()		--系统时间

select year(now());		--年份
select month(now()); 	--月份
select day(now());		--日
select hour(now())		--时
select minute(now())	--分
select 	second(now())	--秒

4、系统函数：

select system_user() --获取系统用户
select version() --获取版本

8.8 聚合函数

函数描述
count()计数
sum()求和
avg()平均值
max()最大值
min()最小值
…

聚合函数：

select count(*) from `stduent` --查询学生的总数

select sum(`result`) from `stduent` --查询学生的成绩的总和

select avg(`result`) from `stduent` --查询学生成绩的平均分

select max(`result`) from `student` --查询学生的成绩的最高分

select min(`result`) from `student` --查询学生的成绩的最低分

count(*)，count(1)，count(字段) 区别：

​ 执行效果上 ：

​ count(*) ：包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL

count(1)：包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL

​ count(字段)：只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是 表示null）的计数，即某个字段值为NULL时，不统计。

​

​ 执行效率上：

​ 1、字段为主键，count(字段) 会比 count(1) 快

​ 2、字段不为主键，count(1) 会比 count(字段) 快

​ 3、如果表多个字段并且没有主键，则 count（1） 的执行效率优于 count（*）

​ 4、如果有主键，则 count（主键）的执行效率是最优的

​ 5、如果表只有一个字段，则 count（*）最优。

8.9 分组与过滤

分组结合聚合函数使用

group by

/*
	select 表达式 from 表名 group by 字段 [having 条	件过滤]
*/
--查询不同课程的平均分,且平均分大于80分，最高分，最低分(根据不同的课程分组) 
select `sub_name` as '课程', avg(`result`) as '平均分' , max(`result`) as '最高分' , 			min(`result`) as '最低分' from `student` as '学生'
group by `sub_name` 
having '平均分' > 80 ;

8.10 数据库级别的MD5加密

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

MD5不可逆，具体的值的MD5相同

加密：

--插入时使用函数 MD5(密码) 即可加密
insert into  `user` ( `name` , `password`) values ('admin' , MD5("123456"))

--更新时加密
update `user` set `password`=MD5(`password`) where `name`='admin' ;

解密

--使用编程解密，查询时使用 MD5(密码) 即可解密
select * from `user` where `name`='admin' and `password`='MD5("123456")' ;

8.11 select 小结

语法：

select [all | distinct]
{* | 字段1 [as 别名1],字段2 [as 别名2], ... , 字段n [as 别名n] }
from `表1` [as 别名1]
[inner | left | right join `表2` as '别名2' on 别名1.字段 = 别名2.字段 ]
[inner | left | right join `表3` as '别名3' on 别名1.字段 = 别名3.字段(别名2.字段 = 别名3.字段]
	...
[inner | left | right join `表n` as '别名n' on 别名(1,2,3,...,n-1).字段 = 别名n.字段] --联合查询
[where 条件判断]	--制定结果满足的条件
[group by 字段]	--指定结果按照字段分组
[having 条件判断]  --过滤分组的记录必须满足的次要条件
[order by 字段 asc | desc ]  --指定查询记录排序
[limit 起始值,分页大小]        --指定查询记录分页

9、事务

事务： 一个事务有多个操作，这些操作要么全部执行，要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。

要不全部成功，要不全部失败

9.1 事务的原则

ACID原则：

  • 原子性（ Atomicity ）： 事务中所有操作被看成一个动作，所有的步骤要么全部完成，要么一个也不会完成
  • 一致性（ Consistency ）： 事务开始之前和事务结束以后 ， 数据库的完整性约束没有被破坏 （数据保持一致）
  • 隔离性（ Isolation ）：用于并发控制，通过隔离，一个未完成的事务不会影响另一个未完成的事务
  • 持久性（ Durability ）：事务提交后， 应该持久保存，不会因为与其他操作冲突而取消这个事务

隔离发生的问题：

  • 脏读：一个事务读取另一个事务未提交的数据
  • 不可重复读：一个事务读取了另一个事务已经提交的更新数据
  • 幻读：一个事务读取了另一个事务已经提交的新增的数据

不可重复读与幻读的区别：

前者是指读到了已经提交的事务的更改数据（修改或删除），后者是指读到了其他已经提交事务的新增数据。

1、脏读：

时间线A事务B事务
1开始事务
2开始事务
3查询余额为2000元
4取款1000元，余额被更改为1000元
5查询账户余额为1000元（产生脏读）
6取款操作发生未知错误，事务回滚，余额变更为2000元
7转入2000元，余额被更改为3000元（脏读的1000+2000）
8提交事务

备注： 按照正确逻辑，此时账户余额应该为4000元

2、不可重复读：

时间线A事务B事务
1开始事务
2开始事务
3统计总存款为10000元
4其中一个账号1000元更新为2000元
5提交事务
6再次统计总存款为11000元（不可重复读）

3、幻读

时间线A事务B事务
1开始事务
2开始事务
3统计总存款为10000元
4新增一个账号，存款为1000元
5提交事务
6再次统计总存款为11000元（不可重复读）

9.2 事务的执行

步骤：

  • 关闭自动提交

  • 开启事务

  • 提交

  • 回滚

  • 事务结束

    注意：MySQL是默认默认自动提交的

事务的步骤

set autocommit = 0 ; -- 关闭自动提交

start transaction -- 标记一个事务的开始

--执行操作(往下所有操作均为同一个事务)
insert into ....
update  ....
delete from ....
select ....

commit --提交数据

rollback --回滚数据，回到事务前

set autocommit = 1 ; --开启自动提交(结束事务)

--A用户给B用户转账200
SET autocommit = 0 ;
START TRANSACTION ;
UPDATE `account` SET `money`=`money`-200 WHERE `name` = 'A' ;
UPDATE `account` SET `money`=`money`+200 WHERE `name` = 'B' ;
COMMIT ;
ROLLBACK ;
SET autocommit = 1 ;

10、索引

什么是索引：

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构。

本质：索引是数据结构

10.1索引的分类

分类：

  • 主键索引 （primary key）:唯一的标识，主键字段的内容不可重复，一个表只能有一个字段为主键（或者联合主键）

  • 唯一索引 （unique）：唯一索引的字段的内容不可重复，一个表中可以有多个字段为唯一索引

  • 常规索引 （key 或 index）:使用index、key关键字来设置，可以重复

  • 全文索引 （fullText）:

    • 在特定的引擎中才有，MYISAM (8.0版本后，InnoDB也支持)
    • 快速定位数据

索引基础语法

--分析SQL执行的状况
--(非全文索引) explain select 表达式 
explain select * from `student` ;

--(全文索引) explain select 表达式 from 表名 where match( 字段 ) against ( '...' )
explain select * from `student` where match(`name`) against ('叶') ;


--显示所有的索引信息
--show index from 表名
show index from `student` ;

--增加索引
alter table 表名 add 索引名 ( 字段 )
alter table `student` add fulltext index studentName (`studentName`);

--创建索引
create 索引 索引名 on 表名 (字段)
create index id_student_name on `student` (`name`) ;

插入100万条数据

-- 在mysql数据库中执行以下语句才能执行函数 （临时生效，重启后失效）
SET GLOBAL log_bin_trust_function_creators=TRUE;

--自定义函数
delimiter $$ --函数头，必须写，标志
create function insert_data() --创建函数
returns int --定义返回的类型
begin -- 函数的开始
	declare num int default 1000000 ;
	declare i int default 0 ;
	--循环
	while i<num do
		--插入的语句
		insert into `student`(`name`,`age`) values ( 'xxx' , 3 ) ;
		set i=i+1 ;
	end while ;
	return i ;
end ; -- 函数的结束

--查询
select insert_data() ;

在100万条数据中查询

未添加索引:

​ select * from student where name= ‘用户555990’;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wFGvPSlv-1584172249780)(C:\Users\333\AppData\Roaming\Typora\typora-user-images\1583305150655.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hdkZHkUF-1584172249781)(C:\Users\333\AppData\Roaming\Typora\typora-user-images\1583305368809.png)]

添加索引后：

​ create index id_student_name on student (name) ;

​ select * from student where name= ‘用户555990’;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PbrQNXk9-1584172249781)(C:\Users\333\AppData\Roaming\Typora\typora-user-images\1583305593305.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eHFMi3Q7-1584172249781)(C:\Users\333\AppData\Roaming\Typora\typora-user-images\1583305751600.png)]

10.2 索引的原则

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

索引的数据结构

Hash 类型的索引

Btree 类型的索引（InnoDB引擎默认的类型）

参考文章:

https://blog.codinglabs.org/articles/theory-of-mysql-index.html

11.用户管理

本质是对mysql.user表进行增删对查

用户管理

--创建用户
--create user 用户名 identifed by 密码 ;
create user 'junjie' identified by '123456' ;

--修改当前用户密码
--set password = password('密码')
set password = password('123456')

--修改制定用户密码
--set password from 用户名 =  password('密码')
set password from junjie = password('123456') ;

--重命名用户
---rename user 用户名 to 新用户名
rename user jun to jun1 ;

--用户授权 all privileges 全部的权限
--grant all privileges on *.* to 用户名 
grant all privileges on *.* to jun ;

--查看权限
--show grants for 用户名 
show grants for jun ;

--撤权
--revoke all privileges on *.* from 用户名
revoke all privileges on *.* from jun ;

12、MySQL备份

为什么要备份：

  • 保证重要的数据不丢失
  • 数据转移

MySQL数据备份的方式

  • 直接拷贝物理文件（data目录）
  • 在SQlyog等可视化工具中手动导出
  • 使用命令行导出 mysqldump 命令行使用

1、 命令行导出

#mysqldump -h 主机 -u用户名 -p密码  数据库 表名 > 物理盘位置
mysqldump -h localhost -uroot -proot123 client student > D:/a.sql

#导出多张表
#mysqldump -h 主机 -u用户名 -p密码  数据库 表名1 表名2 ... > 物理盘位置
mysqldump -h localhost -uroot -proot123 client student person > D:/a.sql

#导出数据库
#mysqldump -h 主机 -u用户名 -p密码  数据库 > 物理盘位置
mysqldump -h localhost -uroot -proot123 client > D:/a.sql

导出成功

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ko9d5PiF-1584172249782)(C:\Users\333\AppData\Roaming\Typora\typora-user-images\1583308640821.png)]

2、导入数据库

#mysql -u用户名 -p密码 库名 < 物理文件位置
mysql -uroot -proot123 client < D:/a.sql

#登录mysql 切换到该数据库 source 备份文件
source D:/db.sql

导入成功

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5jgIQ7Hp-1584172249782)(C:\Users\333\AppData\Roaming\Typora\typora-user-images\1583309615479.png)]

13、规范数据库设计

当数据库比较复杂的时候，就需要规范设计数据库

糟糕的数据库设计：

  • 数据冗余，浪费空间
  • 数据插入和删除都会麻烦、异常（屏蔽使用物理外键）
  • 程序的性能差

良好的数据库设计：

  • 节省内存空间
  • 保证数据库的完整性
  • 方便开发系统

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

  • 分析需求，分析业务和需要处理的数据的需求
  • 概要设计，设计关系图E-R图

设计数据库的步骤：（个人博客）

  • 收集信息，分析需求
    • 用户表（用户登录注销，用户的个人信息，写博客）
    • 分类表（文章分类，创建者）
    • 文章表（文章的信息）
  • 标识实体（把需求落实到每个表及每个字段）
  • 标识实体之间的关系
    • 写博客：user --> blog
    • 创建分类：user --> category
    • user --> user

14、三大范式

14.1 为什么需要数据规范化？

  • 信息重复
  • 更新异常
  • 插入异常
  • 删除异常
  • 丢失有效的数据

14.2 第一范式

第一范式（1NF）：数据库表的每一个字段都是不可分割的原子性数据项

举例说明：

img

在上面的表中，“家庭信息”和“学校信息”列均不满足原子性的要求，故不满足第一范式，调整如下：

img

可见，调整后的每一列都是不可再分的，因此满足第一范式（1NF）；

14.3 第二范式

第二范式（2NF）：需要确保数据库表中的每个字段都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）

前提：必须满足第一范式（1NF）

举例说明：

img

在上图所示的情况中，同一个订单中可能包含不同的产品，因此主键必须是“订单号”和“产品号”联合组成，

但可以发现，产品数量、产品折扣、产品价格与“订单号”和“产品号”都相关，但是订单金额和订单时间仅与“订单号”相关，与“产品号”无关，

这样就不满足第二范式的要求，调整如下，需分成两个表：

img img

14.4 第三范式

第三范式（3NF）：数据库表中的每一字段数据都和主键直接相关，而不能间接相关。

前提：必须满足第一，第二范式（1NF，2NF）

举例说明：

img

上表中，所有属性都完全依赖于学号，所以满足第二范式，但是“班主任性别”和“班主任年龄”直接依赖的是“班主任姓名”，

而不是主键“学号”，所以需做如下调整：

img img

这样以来，就满足了第三范式的要求。

规范性 和 性能 的问题

alibaba ：关联查询的表不能超过三张表

  • 考虑到商业化的需求和目标，（成本，用户体验） 数据库的性能更加重要
  • 在规范性能的问题的时候，需要适当的考虑规范性
  • 故意给某些表增加一些冗余的字段（从多表查询中变为单表查询）
  • 故意增加一些计算列（从大数据量降低为小数据量的查询）
junjie886
关注
  • 0
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL大总结
耀的博客
01-23 4万+
MySQL是程序员必须要掌握的，其实语法结构相比于其他的数据结构、Java、C++来说应该算是简单的，只要多多练习，拿下它应该不成问题。本文是将我学过的面试笔试重点的知识进行归纳总结，如果你认为写的不错的话，求点赞，求收藏，感谢！！！ 一、MySQL数据库基础 1，数据库的操作 1.1、显示当前数据库 show databases; 1.2 、创建数据库 语法： CREATE DATABASE [IF NOT EXISTS] db_name [create_specif..
mysql case语句吗_mysql case ... when语句的几个用法
weixin_29233333的博客
02-07 87
show create table 20130225t1;CREATE TABLE `20130225t1` (`id` tinyint(4) NOT NULL AUTO_INCREMENT,`b` char(1) NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDBselect * from 20130225t1;1甲2丁3乙4丙现在想b字段按照甲乙丙丁的顺序排...
MySQL知识总结
weixin_51568657的博客
03-09 914
文章目录MySQL总结数据类型1.整数类型2.浮点数类型和定点数类型3.字符串类型4.文本类型5.日期与时间类型5.1 YEAR类型5.2 TIME类型5.3 DATETIME类型5.4 TIMESTAMP类型6.二进制类型数据库表基本操作数据库基本操作创建数据库并查看数据库基本信息删除数据库命令修改数据库字符集（编码方式）切换数据库查看当前使用的数据库数据表基本操作（以创建学生表为例）创建数据表查看数据表查看当前数据表查看数据表基本信息查看数据表字段信息修改数据表修改数据表表名修改字段名修改字段类型增加字
史上最强MySQL总结大全
jjyybsm的博客
02-09 1607
目录 一、MySQL数据库基础 1，数据库的操作 1.1、显示当前数据库 1.2 、创建数据库 1.3、使用数据库 1.4、删除数据库 2、常用数据类型 2.1、数值类型 2.2、字符串类型 2.3、日期类型 3、表的操作 3.1、创建表 3.2、查看表结构 3.4、删除表 4、总结 4.1、操作数据库 4.2、常用数据类型 4.3、操作表 5、综合练习 二、MySQL表的增删改查基础 1、CRUD 2、新增（Create） 2.1、单行数据 + 全列插入 .
MySQL总结
Sober1894的博客
03-22 1957
mysql知识点总结
Mysql总结
学仔仔的博客
01-24 742
1.字段类型隐式转换 没加单引号（‘     ’）后果：索引失效/逻辑错误 2.sql顺序 FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1 ON: 对虚表VT1进行ON筛选，只有那些符合&lt;join-condition&gt;的行才会被记录在虚表VT2中。 JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表...
Mysql总结.png
08-04
该脑图专为入门学习mysql的小伙伴所准备的，包含【安装步骤、启动连接、数据库常常用的基本命令[查库、建库、删库、建表、删表]、数据库表详细的增删查改语句以及事例、表的基本操作、索引操作、外键约束、表关联、...
MySQL总结.xmind
06-22
用了将近一周的时间对mysql进行了初步的学习，总结成思维导图，主要内容包括有基本使用，查询，与python的交互，事务，视图，索引，账户管理，主从同步配置等内容。
mysql总结xmind版
05-02
关于mysql知识点的xmind总结，包含mysql的基础介绍，普通用法以及高级用法
mysql总结.docx
11-03
适合初中级小伙伴
mysql总结.txt
05-20
通过相关教程视频，对mysql数据库的知识点进行了相应的总结。知识点包括数据库结构、DQL、DDL、DML、常用函数、多表查询、视图、事务、存储过程等。
MySql总结.docx
05-06
非常适合小白来学习MySql数据库，里面有数据库的各大知识点及其内置函数的使用方法，有很多原理性的知识点。
mysql总结汇总
07-04
mysql增加，查询，修改，删除。包含种类齐全。请放心使用
mysql知识点总结（网安必备）
weixin_65049289的博客
01-04 1215
mysql知识点总结（网安必备）
MySQL数据库知识总结大全
最新发布
m0_37449634的博客
02-06 713
mysql 入门知识大全
mysql知识总结
杰特JET的博客
05-21 399
概述 MySQL 可以分为 Server 层和存储引擎层两部分。 Server 层包括连接器（和客户端交互、权限管理）、查询缓存（已经查询的请求会缓存在内存中，但是mysql经常会遇到缓存失效频繁的问题，例如table scan）、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。 而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、
MySQL常用命令和连接方式总结
MySQL是一种开源的关系型数据库管理系统，被广泛用于Web应用程序的后台数据存储。本文将详细介绍MySQL的一些常用命令和启动方式。 首先，MySQL的启动方式有两种。第一种方式是在cmd中运行services.msc命令，然后在...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值