一、 数据库的出现
1. 数据库是什么: 数据库简单来说,就是存储数据的地方(废话),对于用户认证这个过程来说,当用户登录服务器时, 系统需要把用户的输入的用户认证信息和存储的用户认证信息进行比对,这一过程就需要事先把所有用户的信息存储在一个数据库中,然后逐条进行比对。早起最传统的数据库当然就是文本文档了,每个用户认证时,需要把整个文本文档凑头到尾比对一边,效率极其低下。随着数据爆炸式的增长,以及用户并发访问量的增加,这样的文本数据库逐渐不能满足互联网时代的需要。于是产生了各种各样的其他专门的数据库程序来管理数据。
另外文本数据库拥有几个严重的缺陷:
1) 数据冗余和不一致: 同一个数据可能存放多次
2) 数据访问困难
3) 数据孤立性:大文件拆分后,很难建立联系
4) 数据完整性问题
5) 原子性问题:保证数据库间交互式,能够同步
6) 并发访问
7) 安全性问题:不能只设置部分内容的权限 , 因此来说,把所有数据类操作交给前端程序,太过于粗糙。
2. 数据库软件:
为了更加精细的操作数据的存取,在应用程序和数据之间建立一个中间层,专门用来负载数据存取和管理,就产生了数据库管理系统(DBMS)。
数据库管理系统,包含前端API接口与前端应用程序交互,也包含shell命令行接口与程序员直接交互。同时负责完成数据存取。这时,前端程序将不再直接面对数据,而有数据库管理系统代理完成更加精细管理工作。包括权限,以及如何按照比较高效的方式分配数据。
3. 数据库模型:
1)层次模型
2)网状模型
3)关系模型:1975年又IBM研究院E.F.Coded提出并发表了最开始论述关系型数据库的模型论文,有拉里.克瑞森和其程序员朋友将其实现,后来承接了美国军方一个名叫oracle的项目创建了oracle公司
4)NoSQL:No only SQL , 很多产品,各有侧重点。 由于海量数据是由分布式存贮, 所以需要CAP测试,NoSQL 产品通常只能满足两个。
CAP测试: Consistent Avilable Partition tolerence 一致性, 可用性,容错性
4. 开源数据库:sqliteMySQL, PostgreSQL (EnterpriseDB),MariaDB,MySQL
二、 关系型数据库管理器模型
1. 存储的文件:
1) 数据:保存的数据
2) 索引:保存数据的索引文件
3) 事物文件:操作事物文件
2. 磁盘空间管理器: 用于管理磁盘上数据的存取,建立索引等工作
3. 缓冲区管理器: 为了解决频繁I/O的低效,一部分检索结果会存在缓冲区中内存中,缓冲区管理器用于管理存于内存中
4. 存取方法接口:管理数据的存取方法,于sql语句作用
5. 事物管理器:当以个存取操作完成后,并不是直接写入数据文件,而且写入事物日志中,如果此时数据库崩溃,数据库存取数据将会存入事务日志中。写入数据库文件为随机I/O,写入日志文件为顺序I/O 所以后者比较快。
事物:满足ACID测试
A:原子性
C: 一致性
I:隔离性
D:持久性
6. 锁管理器:当某一数据在被用户访问时,它将不能再被其他用户访问,会被锁管理器锁住。
7. 恢复管理器:存取操作写入事务日志但没写入数据中时,数据库崩溃,在第二次重新启时候,事务日志中内容将通过恢复管理器写入到数据文件中
8. SQL查询引擎:
1). SQL Struct Query Language : 结构化查询语言, 包含以下三类命令
(1) DCL: Data Control Language: 数据控制语言,主要是授予或取消某些访问权限
GRANT/REVORK
(2) DDL: Data Definition Language:数据定义语言,建立表,创建索引
CTREAT/ALTER/DROP
(3) DML : Data Manipulation Language:数据需改语言
SELECT/INSERT/DELETE/UPDATE
2). SQL查询引擎包含四个部分
(1) 分析器,进行SQL语言的语法分析
(2) 查询计划,对查询对象路径进行优化,有可能统一对象存在多条索引
(3) 优化器, 对于SQL语言查询方式进行优化
(4) 求解器, 管理返回结果等
(5) 线程池, 当mysql服务器接收多个并发请求时,分配线程响应多用户
9. SQL命令行接口: SQL的客户端程序,使用SQL命令查询SQL服务器
10. 应用程序命令API:不同编程语言有不同的SQL命令输出接口,可以通过API驱动把SQL语句输送给SQL服务器,本质上也是SQL客户端
11. ODBC: 底层关系型数据库查询标准
12. SQL索语言的标准 ANSI: SQL-86, SQL-89, SQL-92, SQL-99, SQL-2003
三、 mysql 服务器安装和基本使用
1. mysql程序来源: 官方网站 mysql.com
1) vendor 系统自带的rpm包
2) MySQL官方rpm包 : 不建议使用,可能会打破依赖关系
3) 通用二进制格式 : 建议
4) 源码编译 : 建议
2. 使用通用二进制文件安装
1. 下载官方二进制文件tar包,解压缩,并且复制其到/usr/local/
# wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.44-linux2.6-x86_64.tar.gz
# tar -xf mysql-5.5.44-linux2.6-x86_64.tar.gz
# cp -r mysql-5.5.44-linux2.6-x86_64 /usr/local/
# cd /usr/local/
# ln -sv mysql-5.5.44-linux2.6-x86_64 mysql
2. 创建mysql系统用户用户组 -r 选项创建系统用户用户组
# groupadd -r mysql
# useradd -g mysql -r -s /sbin/nologin mysql
# id mysql
uid=498(mysql) gid=498(mysql) groups=498(mysql)
3. 修改文件的属主属组
# cd /local/usr/mysql
# chown -R root:mysql *
4. 数据库服务器初始化, 运行./script/mysql_install_db 创建系统库
创建额外的LVM来存储数据库文件
# fdisk /dev/sdb
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-13054, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-13054, default 13054): +10G
Command (m for help): t
Hex code (type L to list codes): 8e
Command (m for help): w
The partition table has been altered!
# pvcreate /etc/sdb1
Physical volume "/dev/sdb1" successfully created
# vgcreate mydata /dev/sdb1
Physical volume "/dev/sdb1" successfully created
# lvcreate -L 6G -n mysqlData mydata
Logical volume "mysqlData" created
# mke2fs -t ext4 /dev/mydata/mysqlData
# vim /etc/fstabl 中添加一行,设置开机自动挂
/dev/mydata/mysqlData /data ext4 defaults,noatime 0 0
# mkdir /data
# mount -a
# mkdir /data/mydata
# chown -R mysql:mysql /data/mydata 由于数据库操作是由mysql用户执行的,所以要修改这个目录权限
5. 运行数据库初始化脚本
# ./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mydata --user=mysql
Installing MySQL system tables...
150602 19:08:09 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.5.44) starting as process 3913 ...
OK
Filling help tables...
150602 19:08:10 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.5.44) starting as process 3920 ...
OK
数据库初始化脚本的几个选项
--basedir=/usr/local/mysql 设置mysql顶层目录位置
--datadir=/data/mydata 数据库数据存放位置
--user=mysql 负责运行mysql数据库的用户
# ls /data/mydata 每一个数据库为一个文件夹
mysql performance_schema test
5. 预备服务脚本和配置文件
# cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld
# chkconfig --add mysqld
# chkconfig --list | grep mysql
chkconfig --list | grep mysql
# cp /usr/local/mysql/support-files/my-large.cnf /etc/my.cnf
修改配置文的个别选项,在[mysqld]项下面加上
datadir = /data/mydata
# service mysqld start
Starting MySQL.. [ OK ]
# 添加PATH换机变量
# vim /etc/profile.d/mysql.sh 添加
export PATH=/usr/local/mysql/bin:$PATH
# chmod +x /etc/profile.d/mysql.sh
# source /etc/profile.d/mysql.sh
# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.44-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
3. 启动后产生的文件
# cd /data/mydata/ && ls
ibdata1 ib_logfile0 ib_logfile1 mysql mysql-bin.000001 mysql-bin.index performance_schema test www.playground.com.err www.playground.com.pid
错误日志
www.playground.com.pid pid文件
ibdata1 inodb存储引擎的表空间文件
ib_logfle0 inodb事物日志
mysql-bin.* 二进制日志
每个目录相当于一个数据库
插件式的存储引擎,表类型, 底层的存储引擎可以按需自行修改
MyISAM
InoDB
XtraDB(percona开源组织) InoDB的改良版
4. 需改管理员密码
# mysql
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('root');
Query OK, 0 rows affected (0.00 sec)
mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('root');
Query OK, 0 rows affected (0.00 sec)
演示效果
[root@www ~]# mysql -u root -h localhost -proot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.5.44-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
5. 删除匿名用户
# mysql -uroot -hlocalhost -proot
mysql> use mysql ## 系统内建有mysql系统库, 里面user表,mysql服务器的用户名和密码都存放在里面
Database changed
mysql> DESC user
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
mysql> SELECT User,Host,Password FROM user;
+------+--------------------+-------------------------------------------+
| User | Host | Password |
+------+--------------------+-------------------------------------------+
| root | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| root | www.playground.com | |
| root | 127.0.0.1 | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| root | ::1 | |
| | localhost | |
| | www.playground.com | |
+------+--------------------+-------------------------------------------+
6 rows in set (0.01 sec)
## 此时我们看见有两个匿名用户
mysql> DROP USER 'root'@'::1'; ## 删除ipv6地址的root用户
Query OK, 0 rows affected (0.00 sec)
mysql> DROP USER ''@'localhost'; ## 删除匿名用户
Query OK, 0 rows affected (0.00 sec)
mysql> DROP USER ''@'www.playground.com'; ## 删除另一个匿名用户
Query OK, 0 rows affected (0.00 sec)
mysql> SET PASSWORD FOR 'root'@'www.playground.com' = PASSWORD('root'); ## 为另一个空密码root用户设置密码
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT User,Host,Password FROM user;
+------+--------------------+-------------------------------------------+
| User | Host | Password |
+------+--------------------+-------------------------------------------+
| root | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| root | www.playground.com | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| root | 127.0.0.1 | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+------+--------------------+-------------------------------------------+
3 rows in set (0.00 sec)
7. 导出头文件,库文件和man文档
# ln -sv /usr/local/mysql/include/ /usr/include/mysql
`/usr/include/mysql' -> `/usr/local/mysql/include/'
# vim /etc/ld.so.conf.d/mysql.conf 添加
/usr/local/mysql/lib/
# ldconfig -p | grep mysql
libmysqlclient.so.18 (libc6,x86-64) => /usr/local/mysql/lib/libmysqlclient.so.18
# vim /etc/man.config 中添加
/usr/local/mysql/man
# man mysqld
AUTHOR
Oracle Corporation (http://dev.mysql.com/).
MySQL 5.5 05/02/2015 MYSQLD(8)
(END)
说明已经是新版本了
四、 mysql客户端以及命令的基本使用
1. 配置文件基本个是/etc/my.cnf,服务器配置文件和客户端配置文件写在一起,通常包含三个部分
1) [mysql] :mysql客户端配置部分,只是用来配置mysql自己的客户端
2) [mysqld]:mysql客户端服务器配置段
3) [client]:mysql的所有客户端,包括其他的客户端
2. mysql客户端用户登录
mysql
-u 用户名,不指定默认为root用户
-h 主机名或IP,不指定为localhost
-p 密码, 不指定为空密码
3. mysql的命令 :
命令不去分大小写,命令参数区分大小写,与文件相关的区分大小写
1) 客户端命令 : 在客户端可以自动执行的命令
例如: help, status, quit,不需要语句终止符默认为“;”
使用help可以获取命令帮助符, help KEYWORD
\g : 把命令送去服务器端,如果结束符被改了的情况下可以在sql命令后面加这个
mysql> SELECT User,host,Password FROM user\g
+------+--------------------+-------------------------------------------+
| User | host | Password |
+------+--------------------+-------------------------------------------+
| root | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| root | www.playground.com | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| root | 127.0.0.1 | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+------+--------------------+-------------------------------------------+
\G : 把命令送去服务器, 显示时候一行一行显示
mysql> SELECT User,host,Password FROM user\G
*************************** 1. row ***************************
User: root
host: localhost
Password: *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B
*************************** 2. row ***************************
User: root
host: www.playground.com
Password: *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B
*************************** 3. row ***************************
User: root
host: 127.0.0.1
Password: *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B
3 rows in set (0.00 sec)
2) 服务器端命令
SELECT
SELECT col1, col2, ... FROM tb1, tb2, ... WHERE clause;
WHERE clause:
WHERE col1 > 30 ; 同城这里为判断语句
SHOW DATABASES; 显示所所有库
USE database_name; 显示数据库
SHOW TABLES; 显示某库所有表
CREATE DATABASE database_name; 创建数据库
DROP DATABASE database_name; 删除数据库
3) mysql 命令接收的两种模式
交互式模式: 直接在mysql shell中输入命令
批处理模式: 使用输入重定向,mysql < test.sql 实现
非登录执行命令:mysql -e "sql_command"
五、 mysql 服务器的几个重要概念
1. 约束,constraint : 某一个字段取值的范围
1) 主键约束 : 只要某些字段被定为主键,则填入数据时不能重复,另外不能为NULL 值
2) 外键约束:通过另一张表的取值范围来约束当前表
3) 唯一键约束:与主键相同,但是可以为空 ,主键只能有一个,唯一键只能多个
4) 检查式约束: 用户自定义的取值范围,通常为布尔表达式,符合条件才能填
5) 非空约束 : 不能为空
2. 键(key)
1)主键:能惟一标识表中每一个记录的字段或字段的组合;
2)候选键 : 可以作为主键使用的字段或字段组合,不一定只有一种
3) 唯一键 : 唯一标示,但可以为空,数据不能重复出现
4) 外键 3. 表创建需要定义
1)需要定义的内容:字段名, 约束,字段类型
2)字符类型:
char(#): 不区分大小写
varchar(#):区分大小写
binary(#):不区分大小写
varbinary(#):区分大小写,
text(不区分大小写):存储指针,指向对象
blob(区分大小写): 二进制大对象,只存储指针,指向对象
3)数值型:
精确数值 :
int:tinyint(1byt), smallint(2byt),mediumint(4byt),int(4byt),bigint(8byt)
decimal :十进制小树
近似值: float, double
日期时间型:date, time, datatime, timestamp
布尔型:
内置类型: ENUM 限定特定的取值范围, SET