数据库
常见数据库:
数据库管理系统(DBMS)是为管理数据库而设计的计算机软件系统,一般具有存储、截取、安全保障、备份等基础功能,目前主流的数据库管理系统有:Oracle、MySQL、SQL Server、DB2和Sybase。
一、Oracle
Oracle数据库是甲骨文公司推出的一款关系数据库管理系统,是当前数据库领域最有名、应用最广泛的数据库管理系统之一,Oracle产品覆盖了大、中、小型机等几十种机型。
Oracle数据库具有以下特点:
1、Oracle数据库可运行于大部分硬件平台与操作系统上。
2、Oracle能与多种通讯网络相连,支持多种网络协议。
3、Oracle的操作较为复杂,对数据库管理人员要求较高。
4、Oracle具有良好的兼容性、可移植性、可连接性和高生产率。
5、Oracle的安全性非常高,安全可靠。
二、MySQL
MySQL也是一款关系型数据库管理系统,由MySQL AB 公司开发,目前属于 Oracle 旗下产品,MySQL 是最流行的关系型数据库管理系统之一。MySQL也是一款开源的SQL数据库管理系统,是众多小型网站作为网站数据库的选择。
MySQL数据库具有以下特点:
1、MySQL是开源的,可供用户免费使用。
2、MySQL支持多线程,充分利用CPU资源。
3、MySQL对 PHP有很好的支持,PHP是比较流行的 Web 开发语言,搭配 PHP 和 Apache 可组成良好的开发环境。
4、MySQL提供TCP/IP、ODBC和JDBC等多种数据库连接途径。
三、Sybase
Sybase数据库是由美国Sybase公司推出的一种关系数据库系统,是一种典型的UNIX或WindowsNT平台上客户机/服务器环境下的大型数据库系统,由于基于客户机/服务器体系结构,Sybase支持共享资源且在多台设备间平衡负载。
Sybase数据库具有以下特点:
1、Sybase是基于客户/服务器体系结构的数据库,支持共享资源且在多台设备间平衡负载。
2、Sybase操作较为复杂,对数据库管理员的要求较高。
3、Sybase有非常好的开放性,能在几乎所有主流平台上运行。
4、Sybase是一款高性能、安全性非常高的数据库。
四、SQL Server
是美国微软公司推出的一款关系型数据库管理系统,是一款可扩展的、高性能的、为分布式客户机/服务器计算所设计的数据库管理系统,实现了与WindowsNT的有机结合,提供了基于事务的企业级信息管理系统方案。
SQL Server数据库具有以下特点:
1、SQL Server采用图形界面,操作简单,管理方便。
2、SQL Server开放性不足,只能在Windows平台上运行。
3、SQL Server可以用ADO,DAO,OLEDB,ODBC连接。
4、SQL Server是几大需要收费的主流数据库中收费最低的,维护费用也较低。
5、SQL Server具有强壮的事务处理功能,采用各种方法保证数据的完整性。
五、DB2
DB2是美国IBM公司开发的一款关系型数据库管理系统,主要应用于大型应用系统,具有较好的可伸缩性,可支持从大型机到单用户环境,应用于所有常见的服务器操作系统平台下。
DB2数据库具有以下特点:
1、DB2采用了数据分级技术,能够使大型机数据很方便地下载到LAN数据库服务器,使得客户机/服务器用户和基于LAN的应用程序可以访问大型机数据,并使数据库本地化及远程连接透明化。
2、DB2适用于数据仓库和在线事物处理,性能高。
3、DB2广泛应用于大型软件系统,向下兼容性较好。
4、DB2拥有一个非常完备的查询优化器,为外部连接改善了查询性能。
5、DB2具有很好的网络支持能力,可同时激活上千个活动线程。
数据库分类:
MySQL数据库体系结构
2017年11月12日 21:21:13 斯言甚善 阅读数 3322
版权声明:本文为博主LPeng原创文章,未经博主允许转载将负法律责任。 https://blog.csdn.net/qq_18312025/article/details/78515305
DDL:数据定义语言 data Definition language,
eg:create、drop、alter;
DML:数据操控语言 data Manipulation language,
eg:insert、update、delete;(要commit!)
DCL:数据控制语言 (管理用户权限)data Control language,
eg:grant、deny、revoke;
DQL:数据查询语言 data Query language,
eg:select。
innodb存储引擎内存结构
1.软件
安装:rpm;make install;yum localinstall rpm*
mysql 的软件存放的位置(每个人不一样),
软件存放的位置:show variables like ‘%base%’;
数据存放的位置:show variables like ‘%data%’;
2.通过这个软件可以管理很多数据:表
insert
delete
update
select
数据区的特点:
1.占用空间很大;
2.主要放的是表数据,索引数据
3.数据区由很多数据文件组成
4.数据文件特点:被格式化成一个个的数据块(数据页)(data page),默认16K
5.表数据放在数据页中,以行的形式存放
数据库启动,在ram中划出一块很大的内存区;
对数据行的访问:
1.数据行所在的数据页在内存中,直接产生内存读,将数据行从内存中读取出来,发送给用户;
2.数据行所在的数据页不在内存时,产生物理读(physical read),然后通过内存读读取数据。
用户线程:
- 每一个用户连接都会产生一个线程;
- 每一个用户线程会分配一个工作区。
数据缓冲区:(innodb_buffer_pool)
物理内存的:50%~80%
数据缓冲区是共享内存区
select * from t where id<5 order by ‘name’
执行SQL时:
1.建立一个用户连接、建立一个用户线程,分配一个用户空间(最经典的就是sort_buffer_size (256K))(每个用户线程都会分配一个sort_buffer)(256K指的是每个线程分配这么大);
2.执行sql,例select、insert、update、delete;
3.访问数据页,select、dml;在内存-》内存读;不在内存–》物理读+内存读。
物理读:
用户线程发出读请求,具体物理读这个动作是由专门的读线程执行的;
用户写线程负责将脏数据(已修改,但未提交的数据)写给磁盘,这个动作是由写线程来完成的,而不是用户线程来完成。
小知识:mysql里,增删改查都叫 query!!
SQL的执行时间:内存查找+物理读+内存读+…… 执行完成,留下一个脏数据块(修改了,但是没有写到磁盘上!)。
其中,物理写不计算在SQL执行时间里面,物理写也叫作后台写!
知道数据库的状态:(eg:读线程数、写线程数)
show engine innodb status \G (没有分号!)
数据文件
show variables like ‘%file%’;得到:
redo logfile:2个,每个50M
log buffer size:8M
dml:修改/增加/删除 数据行;
dml会产生redo log
redo的格式: 数据页的地址、数据行、具体动作、动作内容
redo log:日志本身
log buffer:日志缓存
log file:日志文件本身
进行DML操作,就会产生redo log(数据页的地址、数据行、具体动作、动作内容),redo log 就会往log buffer写,就算没提交,也会每隔一段时间不断的写,然后log buffer再写到磁盘的logfile里。
redo log会给人感觉修改了N多数据后,提交的时候,非常快!原因:其实只是把redo log写到了磁盘的logfile里,而大量的脏数据其实还没有写到磁盘里,而是在后台慢慢写,但我们并不用担心,因为redo log已经写到logfile里了!!!
(没有commit的数据是可以丢的,不违背mysql的承诺!!)
redo log 详细的记录了对每一个数据页里面的数据行的修改!
修改前的数据页+redo log–》可以构造出来脏页!
eg:delete from t where id<10000;
删除一万行数据,至少会产生1000个redo log。
1.可能会非常大,增长很快
2.可以构造脏块
mysql有一个承诺: 只要提交成功的sql,mysql保证这个sql修改的数据会被永久保存!
dml
commit;
commit;
mysql会做一件事情,将log buffer(内存里)里面的日志写入到 logfile里去!
(PS:数据库崩溃之后,都会有自己的崩溃机制!)
PS:
logfile是物理文件,是log存在的场所。一般数据库下的数据文件中有三个日志文件。
redo log重做日志,记录着数据库所发生的变化,进行一条DML语句时,如果提交会先写入重做日志里。如果数据库出问题了,也可以根据重做日志来进行恢复。
完整的描述sql工作过程中产生的
用户线程建立、工作区分配、
内存读、物理读、
commit、redo log(日志写)(用户空间)
物理写(为什么说是后台物理写)协同工作!
首先用户执行一条输入一条dml,之后产生脏页,redo日志,undo日志,redo日志首先存在于用户空间中,之后到logbuffer中,最后通过commit到磁盘的log.file中,这时候数据脏页还没有到磁盘之中,仅仅存在于内存里,可视redolog已经存在于写缓存中,这样系统就会告诉用户已经已经写成功了,但脏页依旧没有进入磁盘,但只要有redolog在,就可以构建脏块,所以不怕没写入的脏块丢失,之后脏块通过后台写线程慢慢的写入写缓存,之后进入磁盘,完成整个操作,但这个时间不计算在写时间,仅仅在后台进行。
【达到1/2的时候、每隔1s钟写一次、commit的时候,都会把sort_buffer写到log_buffer,log buffer往logfile写。】
【所以,log_buffer里,只有很小的数据,基本不会超过4M(一共8M)】
(sort_buffer虽然只有256K,但是是给每个线程分配256K,而线程很多时,就大了。)
存储中的闪存和缓存
sql执行速度要想更快:
1.内存要足够大(从内存读的速度是从磁盘读的几万倍);
2.物理读的速度要足够快(万一产生物理读,得能节省时间);
3.redo log 写入速度要足够快。
cache缓存:
读缓存:对读操作原理上是有性能的提升,但是对于数据库系统来说,性能提升不是很明显,特别是系统稳定以后;对于写操作没有性能的提升(写的时候还是直接写到存储中…)
写缓存:对写性能有显著提升;对读性能基本上和读缓存差不多。
(写缓存原理)
存储里面,主要是写缓存!
存储的写功能是很强大的,如果写缓存失效,则是因为电池导致写cache被关闭:
1.电池确实坏了;
2.电池生命周期结束(倒计时到了);
3.存储对电池有一个周期性的充放电,自动校正功能。
在充放电期间,存储关闭写缓存。(一般3个月一次)
为什么要充放电?
因为cache要知道电池能给自己充多长时间的电啊~
闪存
现代存储的工作机制:
读数据先到内存(128G)找–》到缓存(20G)找–》到闪存(4T)–》到磁盘,然后在磁盘找到时,直接读到内存。
网络上的方案:
在闪存里单独划出一块空间,给logfile。能够提升写缓存的速度。(错)
1.有个前提:写cache坏了,才能提升写缓存的速度;
2.而且,万一闪存坏了,那么logfile就没了,数据就没了。
1、创建数据库
CREATE DATABASE 数据库名;
Create database 数据库名 character set 编码
查看数据库
show databases;
show create database 数据库名;
2、删除数据库
drop database <数据库名>;
drop database if exists <数据库名>;
数据库的修改:
修改数据库的编码格式:
Alter database 库名 character set 编码;
表
3、选择数据库
user <数据库名>;
4、创建数据表
以下为创建MySQL数据表的SQL通用语法:
CREATE TABLE table_name (column_name column_type);
以下例子中我们将在 RUNOOB 数据库中创建数据表runoob_tbl:
CREATE TABLE IF NOT EXISTS runoob_tbl
(
runoob_id
INT UNSIGNED AUTO_INCREMENT,
runoob_title
VARCHAR(100) NOT NULL,
runoob_author
VARCHAR(40) NOT NULL,
submission_date
DATE,
PRIMARY KEY ( runoob_id
))ENGINE=InnoDB DEFAULT CHARSET=utf8;
5、删除数据表
以下实例删除了数据表runoob_tbl:
use RUNOOB_tbl;
DROP TABLE 表名;
DROP TABLE if exists 表名;
6、插入数据
mysql> INSERT INTO runoob_tbl
-> (runoob_title, runoob_author, submission_date)
-> VALUES
-> (“JAVA 教程”, “RUNOOB.COM”, ‘2016-05-06’);
1.登录数据库
mysql -u root -p 数据库名称
2.查询所有数据表
show tables;
3.查询表的字段信息
desc 表名称;
4.1添加表字段
alter table table1 add transactor varchar(10) not Null;
alter table table1 add id int unsigned not Null auto_increment primary key
4.2.修改某个表的字段类型及指定为空或非空
alter table 表名 change 字段名 字段名 字段类型 [是否允许非空];
alter table 表名 modify 字段名 字段类型 [是否允许非空];
alter table 表名 modify 字段名 字段类型 [是否允许非空];
4.3.修改某个表的字段名称及指定为空或非空
alter table 表名 change 字段原名称 字段新名称 字段类型 [是否允许非空
4.4如果要删除某一字段,可用命令:ALTER TABLE 表名 DROP 字段名;
7、查询数据
select * from runoob_tbl
Select * from 表名 where 条件;条件比如id=”1”;
show tables;
Desc 表名;
8、WHERE子句
SELECT * from runoob_tbl WHERE BINARY runoob_author=‘runoob.com’;
9、UPDATE更新
修改表名:
Alter table 表名 rename to 新表名;
UPDATE runoob_tbl SET runoob_title=“学习 Python” WHERE runoob_id=3’;
10、Delete
DELETE FROM runoob_tbl WHERE runoob_id=3;
11、Like
复制表结构:
Create table 表名 like 要复制的表名;
SELECT * from runoob_tbl WHERE runoob_author LIKE ‘%COM’;
12、union
条件、约束
1、条件表达式:
is :一般用于判断字段值是否为空;
not : 取反
2、模糊查询
Like
Select * from teacher where name like “ z_”; 表示单词字符的匹配
Select * from teacher where name like “ %z%”; 表示任意个字符的匹配
3、指定范围内的
Between 头部尾部都包含
In:在指定值中进行匹配
4、去重复
Distinct
5、起别名
As:可以省略
约束
非空约束 not null :
值已经是空的字段,不能设置为不空 alter table 表名 modify 列名 类型 not null;
讲非空字段设置为空字段:alter table 表名 modify 列名 类型 ;
唯一约束 unique:
被唯一约束的字段值不能重复,所以一般用于ID;
如果字段里面,已经有了一样的了,就不可以设置为唯一的,反之就可以
删除唯一约束:
Alter table 表名 drop index 列名;
主键约束 primary key
一般主键定义在字段列表的第一个
主键只能有一个,在一张表中;多表另算
Alter table 表名 modify 列名 类型 primary key ;
主键自增长是MYSQL独有的
外键约束 foreign key
三、聚合函数
聚合函数(null)空值不参与计算
主键(一般不为空)和count(*)不会为空
概念:将数据库表中的一列作为整体进行运算的函数
group by 子句, 分组子句
group by 字段/别名 [排序方式]
分组后会进行排序。升序:ASC,降序:DESC
以下[合计函数]需配合 group by 使用:
count 返回不同的非NULL值数目 count(*)、count(字段)
sum 求和
max 求最大值
min 求最小值
avg 求平均值
group_concat 返回带有来自一个组的连接的非NULL值的字符串结果。组内字符串连接
count()
sum();
max();
min();
avg();
group_concat()
– 其他常用函数
md5();
default();
四、查询语句
排序:
格式:
Order by 字段 asc ()升序(默认),desc降序
分组:
用于查询分组字段和聚合函数
比如查询部门的最高工资,平均工资、最低工资
查某个部门的平均工资
查询公司男女的平均工资
Where 和 having的区别:
1、Where 用于分组之前,满足条件了才进行分组;having 用于分组之后,满足分组的条件了才进行查询
2、Where 不能对聚合函数进行判断,having可以对聚合函数进行判断
以下的例子是错误的:
分页:
Limit 起始下标(0) , 每一页的数量;
起始下标: (页码-1)*每页显示数据的数目