mysql总结

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]
and5>1 and 1>2false
or5>1 or 1>2true
not5 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 :关联查询的表不能超过三张表

  • 考虑到商业化的需求和目标,(成本,用户体验) 数据库的性能更加重要
  • 在规范性能的问题的时候,需要适当的考虑规范性
  • 故意给某些表增加一些冗余的字段(从多表查询中变为单表查询)
  • 故意增加一些计算列(从大数据量降低为小数据量的查询)
  • 0
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

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

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

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值