1.数据库介绍
连接到mysql服务的指令:mysql -h 主机IP -P 端口 -u 用户名 -p密码
登陆前保证mysql处于服务状态,如果没有写-h,默认是本机,如果没有-p,默认是3306,在实际工作中3306一般会被修改,因为3306容易被攻击
数据库的三层结构
-所谓mysql数据库,就是在主机安装一个数据库系统,就是在主机安装一个数据库管理系统DBMS ,这个管理程序可以管理多个数据库
-一个数据库中可以创建多个表,以保存数据
-数据库管理系统DBMS(mysqld ,在3306端口监听),数据库DB(data目录下的db01、db02….)和表(db01下面的文件)的关系如图所示
数据库的本质仍然是文件,后面会学到一些其它的数据库比如redis,可能会把数据存放到内存中,但是要实现数据的持久化,仍然要将其放入到磁盘中
数据库的表由行列构成,一行称之为一条记录,在java程序中往往使用一个对象来映射
SQL语句分类
-DDL:数据定义语句【create表,库…..】
-DML:数据操作语句【增加insert,修改update,删除delete】
-DQL:数据查询语句【select】
-DCL:数据控制语句【管路数据库:比如用户权限grant revoke】
Java程序操作数据库:JDBC后续内容
2.创建数据库
#使用指令创建数据库,创建一个名称为cs_db01的数据库
CREATE DATABASE db01;
#创建一个使用utf8字符集的cs_db02数据库
CREATE DATABASE cs_db02 CHARACTER SET utf8
#创建一个使用utf8字符集,并带校对规则的cs_03数据库
CREATE DATABASE cs_db03 CHARACTER SET utf8 COLLATE utf8_bin
#校对规则 utf_bin区分大小写 默认的utf8_general_ci 不区分大小写
3.查看、删除数据库
显示数据库语句:
SHOW DATABASES
显示数据库创建语句:
SHOW CREATE DATABASE db_name
数据库删除语句[慎用]:
DROP DATABASE [IF EXISTS] db_name
#查看当前数据库服务器中所有的数据库
SHOW DATABASE
#查看前面创建的cs_db01数据库的定义信息[在创建数据库表的时候,为了规避关键字,可以使用反引号解决]
SHOW CREATE DATABASE ‘cs_db01’
#删除当前创建的cs_db01数据库
DROP DATABASE cs_db01
4.备份恢复数据库
备份数据库的原理就是将数据库备份到文件系统中,如果数据库被黑客攻击,就可以从文件系统中恢复
备份数据库(注意:在DOS执行)命令行
mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > 文件名.sql
恢复数据库(注意:进入Mysql命令行执行)
sourse 文件名.sql
#备份,直接打开dos下执行mysqldump指令其实在mysql安装目录\bin
#这个备份文件就是对应的sql语句
mysqldump -u root -p -B cs_db01 cs_db02 > d:\\mysql\\back.sql
#恢复数据库(进入mysql命令行执行)
source d:\\mysql\\back.sql
ps:也可以直接将back.sql里面所有的内容复制到SQLyog查询编辑器里面执行,也可以恢复数据库(文件太大不建议这么做)
如果觉得数据库太大,不想备份整张数据库,可以只备份其中的几张表:
mysqldump -u 用户名 -p 密码 数据库 表1 表2 表n > d:\\文件名.sql
二、表
1.创建
CREATE TABLE table_name
(
field1 datatype,
field2 datatype,
field3 datatype
)character set 字符集 collate 校对规则 engine 引擎
说明:
-field:指定列名
-datatype:指定列类型(字段类型)
-character set: 字符集设置,如不指定则为所在数据库的字符集
-collate:校对规则设置,如果不指定则为所在数据库的校对规则
-engine:存储引擎(涉及内容多,后面有单独板块讲)
可以在SQLyog里面进行图形化的创建
但更多的还是使用上面的指令sql语句进行创建:
2.删除
删除数据库的语句很简单,但一般不用
3.修改
可以直接通过SQLyog的图形化操作界面来修改表的各种内容,比如名称,引擎,字符集等等
通过SQL语句修改:
alter的意思就是修改
这里 NOT NULL DEFAULT ‘ ’代表元素不为空,默认为‘ ’
三、Mysql数据类型
Mysql的数据类型就是针对每列的数据类型-列类型
Mysql数据类型:
-数值类型
int[4个]
double[双精度 8个] decimal[M,D][大小不确定]
-文本类型(字符串类型)
char 0-255
varchar 0-65535
text 0-65535(0-2^16-1)
-longtext 0-2^32-1
二进制数据类型
-blob[0-2^16-1]
-longblob[0-2^32-1]
日期类型
-year【年】
-data【日期年月日】
-time【时分秒】
datatime【存放年月日时分秒 YYYY-MM-DD HH:mm:ss】
timestamp[时间戳,自动更新按当前insert、select操作的时间]
Mysql数据类型看起来多,实际开发中用的没那么多,常用的就标红的这几种
整形的使用:
在实际开发中,讲究需求适当原则,如果整形只存放1-100就只需要int,用bigint就会影响查询效率,就比如age这种就用smallint就行
bit位类型的使用:
这里表中加入长度为8的bit,加入1显示‘1’,加入3显示‘11’
小数的使用:
-FLOAT/DOUBLE 单精度/双精度
-DECIMAL[M,D]:可以支持更加精确的小数位,M是小数位数的总数,D是小数点后面的位数,如果D是0则默认没有小数部分,M最大为65,D最大30,M被省略默认是10
字符串的基本使用
CHAR(size) 固定长度字符串 最大255字符,一个字符可以存放一个字母或者一个汉字
VARCHAR(size)0-65535
可变长度字符串最大65532字节,因为utf8编码最大21844字符,varchar的1-3个字节用于记录大小,如果是GBK编码则最多存32766字符,因为GBK是两个字节存一个字符
使用细节:
-char(4) varchar(4)都代表最多存放4个字符,对于char和varchar一个汉字或字母都是一个字符。
-char(4)是固定的大小,如果插入aa有两个空间没有使用,char的长度仍然是4, varchar(4)是可变长度大小,如果有空间没有使用,它的长度是实际存放数据长度,不会造成内存的浪费,所以varchar会有1-3个字节的预留空间记录长度
-但实际上有些场景还是会用char,比如存储固定长度的身份证号,邮编、手机号之类的,这样用varchar的查询速度比varchar快
-如果varchar不够用,可以考虑使用mediumtext或者longtext
-说明一点:mysql里面的字符串和java不同,是由单引号‘’圈起来
日期类型的使用:
黄字代表时间戳的配置,如果没有人为加时间进去,它就会自己根据当前时间进行加入
四、CRUD(create read update delete)
1.Insert(添加数据)
例子:
insert语句的注意事项:
-插入数据类型应与字段数据类型相同
-数据的长度应在列的规定范围内,比如:不能将一个长度为80的字符串加入到长度为40的列中
-在values中列出的数据位置必须与被加入的列的排列位置向对应
-字符和日期型数据应包含在单引号之中
-列可以插入空值[前提是该字段允许添加空值],insert into table value(null)
-insert into tab_name values (),(),()形式添加多条记录
-如果是给表中的所有字段添加数据,可以不写前面的字段名称
-默认值的使用,当不给某个字段值时,如果有默认值就会添加,否则报错
2.Update(更新数据)
如果不加where条件,会修改所有的记录,因此要小心
如果要修改多个字段就可以 SET 字段1 = 值1 , 字段2 = 值2……
3.Delete(修改数据)
使用起来与之前的UPDATE 差不多,如果不加where,会将表内所有数据删除
drop 表名会将表也删除
4.Select(查询数据)(单表、多表)
单表:
运算
在where过滤运算符子句中常用的运算符
这里使用了一个模糊查询的功能,LIKE‘韩%’表示把名字以韩开头的所有人拿出来
使用order by 子句排序查询的结果
asc是英文升序的缩写,desc是降序
where子句的加强:在mysql中 日期类型可以直接比较:查找1992.1.1后入职的员工:
要注意格式
模糊查询(like):
%表示0到多个字符,_表示单个任意字符
查询首个字母为S的所有员工:
查找第三个字符为大写o的所有员工的姓名和工资
显示没有上级雇员的情况,这里不能用=,而是用is
查询表结构:DESC
使用order by子句 按照工资从低到高显示雇员信息:
按照先雇员号升序而后薪资降序排列显示信息:
分页查询:
假设表有十万条数据,要求查询其中一项,这里就要用到分页查询
按雇员的id号升序取出,每页显示3条记录,分别显示第一页第二页和第三页
基本语法是:select …… limit start,rows //从第几行开始,取多少行
分组函数与子句的加强
显示每种岗位的雇员总数和平均工资:
表查询-加强:
统计每个部门的平均工资,并且只统计大于1000的,按从高到低排序
五、函数
1.统计函数
Count
COUNT(*) 和COUNT(列名)的区别:后者会排除null的情况
下面这种写法会排除null的行数,所以返回3
Sum
注意:sum仅对数值有用,对其它数据类型无意义会报错,对于多列求和逗号不能少
合计函数AVG 求平均值
合计函数-MAX/MIN
如果这里要查询最高分是谁,则要用到子查询,会比较复杂
分组统计语句 group by 和 having过滤查询
演示案例先建一个表
添加数据:
再建一个工资级别表
2.时间日期
时间日期常用相关函数:
例子:
在实际开发中,经常使用int保存unix的时间戳,然后使用from_unixtime()进行转换
3.字符串函数
字符串常用相关函数:
红色代表最常用
演示:
以首字母小写的方式显示所有员工emp表的姓名:
4.数学函数
数学相关的常用函数:
举例说明:
5.流程控制
常用流程控制语句:
例子:
6.加密和系统函数
常用加密和系统函数:
例子:
六、多表查询
内连接:内多表查询
问题引出:显示
雇员名,雇员工资以及所在 部门的名字(另一张表),并将它们按降序排列
如果不加任何条件直接查两张表,它的规则是:
1.从第一张表中取出一行,与第二张表的每一行进行组合,返回结果含有两张表的所有列
2.一共返回的记录数第一张表数*第二张表数
3.这样的默认多表查询结果,称为笛卡尔集
4.解决多表的关键就是写出正确的过滤条件where,需要程序员进行分析
自连接:将同一张表当作两张表用,需要给同一张表取别名,列名不明确,可以指定列的别名
子查询:子查询是指嵌入在其它sql语句中的select语句,也叫嵌套查询
-单行子查询:指返回一行数据的子查询语句
-多行子查询:指返回多行属性据的子查询 使用关键字in
表子查询:子查询当作临时表使用,这种查询可以解决很多场景
在多行子查询中使用all操作符:
案例:显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
这里把ALL改成ANY就可以查到比30号部门其中一个高的所有员工
多列子查询:指查询返回多个列数据的子查询语句
基本语法:(字段1 , 字段2) = (select 字段1 , 字段2 from…)
案例:查询与smith的部门和岗位完全相同的所有雇员(且不含他本人)
几个练习题:查找每个部门高于自己部门平均工资的人的员工号和工资
查找每个部门工资最高的人的详细资料
表复制
有时,为了对某个sql语句进行效率测试,我们需要海量数据时,可以使用此法为表创建海量数据
面试题:如何去掉一张表里的重复的记录
先构建一张具有重复记录的表:
去重思路:
-先创建一张临时表,利用like使该表结构与需要去重的表一样
-把原表的记录通过distinct关键字处理后复制到临时表
-清除掉原表的记录
-把临时表的记录复制到原表
-利用drop去掉临时表
合并查询 – 合并两条查询语句:
union all就是将两个表的查询结果合并,不会去重
只用union的话就会去重
外连接
-左外连接(左侧的表完全显示)
基本语法:select….from 表1 left join 表2 on [表1是左表]
-右外连接(右侧的表完全显示)
在实际开发中,绝大多数情况下使用的是内连接
八、约束
约束用于确保数据库的数据满足特定的商业规则
1.not null
如果在列上定义了not null,那么在插入数据事,必须为列提供数据
之前有演示
2.主键primary key
主键使用的许多细节:
-组件不能重复,也不能为null
-一张表中只能有一个主键,但可以是复合主键(将两个或多个元素相加视为主键)
-主键的指定方式有两种:直接在字段名后指定:字段名 primary key 或者 在表定义最后写primary key(列名)
-使用desc表名,可以看到主键的情况。
-在实际开发中,每一个表往往都会有一个主键,用来唯一标识
3.unique(唯一)
当定义了唯一约束后,该列值是不能重复的
注意:
-如果列没有指定not null,那么unique字段可以有多个null
-一张表可以有多个unique字段
4.foreign key(外键)
外键用于定义主表和从表之间的关系:外键约束要定义在从表上,主表则必须具有主键约束或者unique约束,当定义外键约束后,要求外键列数据必须在主表的主键列存在或是为null
这里class_id 与 id形成了外键约束,班级表为主表,学生表为外键,如果直接加一个300的class_id是加不进去的,应该先加id 300,同样在班级表里面直接删除id 200也是无法删除的,应该先删class_id 200
FOREIGN KEY(本表字段名) REFRENCES 主表名(或主键名或unique字段名)
案例:创建上述的学生表和班级表:
外键使用的注意事项:
-外键指向的表的字段,要求是primary key或者是unique
-表的类型是innodb,这样的表才支持外键
-外键字段的类型要和主键字段的类型一致(长度可以不同)
-外键字段的值,必须在主键字段中出现过,或者为null(前提是外键字段允许为null)
-一旦建立主外键关系,数据就不能随意删除了
5.check
用于强制使数据必须满足的条件,假设在sal列上定义了check约束,并要求sal列值在1000-2000之间如果不在1000-2000之间就会提示出错
Oracle和sql server均支持check,但是mysql5.7目前不支持check,只做语法校验,但不会生效
九、自增长
在某张表中存在一个id列(int),我们希望在添加数据的时候,该表的id从1开始自动增长,就会用到自增长技术
自增长的使用:
字段名 整形 primary key auto_increment
‘
注意:
-自增长一般配合主键或者unique使用
-自增长修饰的字段一般为整数型
-自增长默认从1开始,你也可以通过命令修改alter table 表名 auto_increment = xxx;
十、索引
说起提高数据库的性能,索引是最物美价廉的东西,不用加内存,不用改程序,不用调sql,查询速度就可能提高千百倍
比如,在一个含有8000000条数据的表中查询一条数据,要用4.55s才能查到,这里使用索引,速度就快很多
创建索引后,只对创建了索引的列有效
索引的代价:磁盘的额外占用、对dml(update , delete , insert)语句的效率有影响
1.索引的原理
如果没有索引的话,查询的时候程序会对全表进行扫描,如果查询的id是第一条记录,程序依然会对全表进行扫描,来看看是否有id还是1的数据,查询速度会非常慢
创建了索引的话,表会创建一个索引的二叉树(B树,B+树),查询的时候就利用二叉树来进行查询,效率会高非常多
对dml操作速度有影响的原因是因为修改这个树要进行额外的操作,对索引进行维护,保持其二叉树的性质
但是在实际项目中select比dml操作要多很多很多,所以一般还是需要利用索引对数据库表进行优化
2. 索引类型
主键索引
主键自动为一个索引,叫主索引(类型为Primary key)
唯一索引(unique)
普通索引(index)
全文索引[适用于MySAM]
Mysql自带全文索引,但一般开发不使用,性能太差,开发中考虑使用Solr和ElasticSearch
3.索引的使用
如果某列的值不会重复,优先考虑使用unique索引,否则使用普通索引
添加普通索引也可以这么写:
ALTER TABLE t25 ADD INDEX id_index(id)
添加主键索引:
在创建表变量时,直接在变量后面写PRIMARY KEY 也可以
删除索引:
删除主键索引
修改索引:先删除索引,再添加新的索引
查询索引:
3.创建索引的情况
哪些情况适合创建索引:
-较为频繁的作为拆线呢条件字段时应该创建索引
-唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件,比如性别
-更新频繁的字段不适合创建索引
-不会出现在WHERE子句的字段不该创建索引
十一、事务
1.事务的介绍以及操作
事务用于保证数据的一致性,它由一组相关的dml语句组成,该组的dml语句要么全部成功,要么全部失败。如:转账就要用事务来处理
事物和锁:当执行事物操作时(dml语句),mysql会在表上加锁,防止其它用户改表的数据,这对用户来讲是非常重要的
Mysql控制台的几个重要操作:transaction就是事务的意思
-start transaction –开始一个事务
-savepoint 保存点名 –设置保存点
-rollback to 保存点名 –回退事务
-rollback –回退全部事务
-commit –提交事务,所有的操作生效,不能回退
回退事务:
保存点(savepoint)保存点是事务中的点,用于取消部分事务,当结束事务时(commit),会自动删除改事务所定义的所有保存点,当执行回退事务时,通过指定保存点可以回退到指定的点
提交事务:
使用commit语句可以提交事务,当执行了commit语句后,会确认事务的变化、结束事务、删除保存点、释放锁,数据生效。当使用commit语句结束事务子后,其它会话将可以查看到事务变化后的新数据
上述例子一旦直接回退到a点就不能再回到b点
事务细节:
-如果不开始事务,默认情况下,dml操作是自动提交的,不能回滚
-如果开始一个事务,你没有创建保存点,你可以执行rollback,默认回到事务开始的一个状态(这也要开始事务,也就是start transction,如果没有,rollback也没有任何意义)
-你也可以在这个事务中(还没有提交时),创建多个保存点,比如savepoint aaa,执行dml,savepoint bbb;
-你可以在事务没有提交前,选择回退到哪个保存点
-mysql的事务机制需要InnoDB的存储引擎才可以使用,myisam不好使
-开始一个事务 start transaction ,set autocommit = off
2.隔离级别
事务隔离级别的介绍:
-多个连接开启各自事务操作数据库中数据时,数据库系统要负责隔离操作,以保证各个连接在获取数据时的准确性
-如果不考虑隔离性,可能会引发下列问题:脏读、不可重复读、幻读
脏读:当一个事务读取另一个事务尚未提交的修改时,产生脏读
不可重复读:同一查询在同一事务中多次进行,由于其它提交事务所做出的修改或删除,每次返回不同的结果集,此时发生不可重复读
幻读:同一查询在同一事务中多次进行,由于其它提交事务所做的插入操作,每次返回不同的结果集,此时发生幻读
查看当前的隔离级别:select @@tx_isolation
默认是可重复度级别
事务的隔离级别:
可串行化与可重复度级别的主要区别就是:可串行化会加锁,在一个事务尚未提交时,会卡在那个地方
具体来说就是:
一个用户对数据库的隔离级别是可重复读的话,数据会自动保存在该用户结束其自己的事务之前,其它用户修改数据库并且事务结束提交数据后,该用户查到的数据仍然是自己事务开始时的样子,不会受到其它用户修改数据的影响,只有自己的事务也提交过后才能看到数据库修改后的样子
该用户无法进行查询,或者说会卡在那个地方,因为锁知道此时有用户在对数据库进行操作,一旦其它用户的事务结束进行提交,该用户就能立即查询到修改的数据,但其他用户一致不提交,可能会出现超时不能查询的问题。
其它隔离操作:
这里的系统隔离级别是所有用户的默认隔离级别
十二、mysql表类型和存储引擎
基本介绍:
-mysql的表类型由存储引擎(storage engines)决定,主要包括MyISAM 、innoDB、 Memory
-Mysql数据表主要支持六种类型:CSV、Memory、ARCHIVE、MRG_MYISAM、MYISAM、InnoBDB
-这六种又分为两类,一类是“事务安全型“,比如InnoDB,它支持事务,其余属于第二类称为非事务安全型,它们都不支持事务
InnoDB:支持事务、行级锁定和外键,但效率不高,并且会占用额外的存储空间
MRG_MYISAM:收集myisam表,集合性质
Memory:存储在内存里面,每一个memory表对应一个磁盘文件,读写速度非常快,默认使用hash索引,一旦服务关闭,表中的数据就会丢失,但表的结构还存在
MYISAM:不支持事务、外键,但批量添加速度非常快,访问速度快,对事务完整性没有要求
-如果应用不需要事务,处理的只是最基本的CRUD操作,那么就用MyISAM
-如果需要支持事务,选择innoDB
-Memory存储引擎就是将数据存储在内存中,由于没有磁盘io的等待,速度极快,但关闭服务器或者数据库内存将丢失,经典应用是用户在线状态
修改存储引擎:
ALTER TABLE `表名` ENGINE = 存储引擎;
十三、视图(View)
视图需求应用实例:数据库中存储了用户很多信息,但用户自己查看的时候,我们选择性的只提供表的一部分信息展示,每个用户权限不一样,看到的内容也不一样。
视图的基本概念:
视图是一个虚拟表,其内容由查询定义,同真是的表一样,驶入包含列,其数据来自对应的真是表(基表)
视图和基表的关系图:
1.视图是根据基本表来创建的,是虚拟的表
2.视图爷有列,数据来自基表
3.提供视图可以修改基表的数据
4.基表的改变,也会影响到视图的数据
视图的基本使用:
-create view 视图名 as select语句
-alter view 视图名 as select语句
-SHOW CREATE VIEW 视图名
-drop view 视图名1,视图名2
视图的创建不会产生新数据,视图创建后磁盘对应视图只有一个视图结构文件.frm
针对视图的数据变化会影响到基表,基表的数据变化也会影响到视图
视图中可以再使用视图,也就是从视图中再做出新的视图,但根本的数据来源还是基表
视图的好处:
-安全:一些数据表有着重要的信息,有些字段是保密的,不能让用户看到,有视图就能让用户只看到他权限所能看到的信息
-性能:关系数据库的数据常常会分表存储,使用外键建立这些表之间的关系,这时,数据库查询通常会用到连接(join)这样做不但麻烦,而且效率也比较低,如果建立一个视图,将相关的表和字段组合再一起,就可以避免使用join查询数据
-灵活:提供系统中有一张旧表,这张表由于涉及的问题,即将被抛弃,然而,很多应用都是基于这张表,这时就可以建立一张视图,视图中的数据直接映射到新建的表,就可以少做很多改动,也达到了升级数据表的目的
一张视图也可以映射多张表:
十四、Mysql管理
原因:当我们做项目开发时,可以根据不同的开发人员,赋予他们相应的mysql操作权限。
Mysql的用户都存储在系统数据库mysql中user表中
其中user表的重要字段说明:
-host:允许登录的位置,localhost表示该用户只允许本机登录,也可以指定ip地址
-user: 用户名;
-authentication_string:密码,是通过mysql的password()函数加密之后的密码
创建用户
create user `用户名` @ `允许登录的位置` identified by `密码`
创建用户同时指定密码
删除用户:
drop user `用户名` @ `允许登录的位置`
Mysql权限管理细节:
给用户授权: