数据库
数据管理系统的发展过程分为两个阶段,即文件系统阶段和数据库系统阶段
(1)文件系统阶段:
此阶段管理数据的方法:把逻辑相关的一组数据组织成一个文件,每个文件有唯一的文件名标识存储在磁盘上,由文件系统负责实现按名存取。用户要访问哪个文件,就给出该文件名,由文件系统找到该文件并返回给用户,再由用户直接处理文件中的数据。
文件系统管理数据所存在的问题:
1)数据是离散的
2)数据是重复的
3)数据独立性差
4)数据难以按用户的需要表示。
(2)数据库系统阶段:
将所有的数据按数据模型进行集中存放,即存放在数据库中,由一个软件(DBMS)实行统一的数据管理,应用程序通过DBMS访问数据。
数据库系统相对于文件系统的优点:
1)数据是集成的,共享的
2)数据很少重复
3)数据独立性好(数据库系统的最大特点)
4)数据结构化,易于按用户的视图来表示
数据库的三级模式:内模式,概念模式,外模式
内模式:数据库中数据的存储结构,存储方法,存储策略的抽象描述
概念模式:数据库中数据逻辑结构的抽象描述。
外模式:单个用户用到的数据逻辑结构的抽象描述。
一个数据库中只能有一个内模式和一个概念模式,但可以有多个外模式。
------------------------------------------------------
一般用户 | 关系模型 | 程序员
表 | 关系 | 文件
行 | 实体 | 记录
列 | 属性 | 字段
-------------------------------------------------------
四种数据组织模型:
(1)层次模型的数据库系统:IBM
信息管理系统
数据库是整个信息管理系统的核心
我们所说的数据库一般有两种理解:一种是指数据管理软件,另一种是则是指数据
(2)网状模型
耦合度很高
(3)关系模型
(4)对象-关系模型
DBMS:数据库管理系统,只是管理数据库的软件。
RDBMS:关系型数据库管理系统
数据库管理系统由数据库(DB)、数据库管理系统(DBMS)、应用程序、用户和硬件组成
数据库管理系统应提供的功能:
管理数据存储
安全管理 ,数据库管理系统,操作系统,防火墙等
管理元数据,
事务管理,保证数据一致性的一个重要机制
数据检索
性能优化 ,检索效率最高的oracle,mysql
google,yahoo,阿里巴巴使用的都是mysql
备份恢复
数据检索和修改
对DataBase的选择有两个方向:
开发方向:DBA。主要实现数据库结构设计
在应用层次上的开发,开发一个存储过程,开发一个存储函数,写一个触发器,是通过SQL实现的
管理方向:DBA。 数据库软件的安装卸载升级等
数据的备份恢复
用户管理,权限管理
安全管理
架构设计
SQL:结构化查询语言
数据库结构是C/S模型的
我们需要把客户端的查询语句,送到服务器端,因此只需要在服务器端提供执行环境就行了。
查询语句:
select,update,delete
由于各个商家所开发出来的数据库查询语句可能不尽相同,于是ANSI 美国国家标准委员会提供了一种标准。
SQL-86
SQL-89
SQL-92
引入了三个兼容的层次:
Entry (最低的要求)
Intermediate
Full (最高的要求,完全按照标准设计)
SQL:1999,mysql
SQL:2003
Oracle:PL/SQL
SQL Server:T-SQL
MySQL:SQL
全球比较著名的RDBMS:
大型的:Oracle,sybase,Informix,DB2
中型的:SQL Server
开源的:Mysql,PostgreSQL-->EnterpriseDB
MySQL:全球最流行的
mysql被sun收购,sun又被Oracle收购,mysql的设计者Michael Widenius担心以后mysql会被商业化。于是Michael Widenius有继续维护mysql,并重新命名Maria DB(Michael Widenius)
mysql的版本:
Alpha,内测版
Beta,公测版
GA,公开发行版
社区版:公开下载使用,不提供任何服务
搜狐镜像
企业版:需要授权使用,提供企业监控套件
mysql的逻辑架构:
如图:
--------------------------
-------------------------
有以下mysql组件
客户端
连接/线程处理 在内存中生成一个新的线程,用于相应客户端请求
查询缓存,如果在查询缓存中没有,则交给分析器(语法分析器,词法分析器)
优化器,执行路径,从N中执行路径中选择最优路径
存储引擎
DML:数据操纵语言,插入(INSERT)、修改(UPDATE)、删除(DELETE)、查询(SELECT)
DDL:数据定义语言,CREATE、ALTER、DROP
MySQL架构
如图:
------------------------
-----------------------
(1)连接池
(2)SQL接口
DML 数据操作语言
DDL 数据定义语言
表,二维关系,表和表之间也可以建立关系
索引
视图
触发器
存储过程
存储函数
事件调度器
游标
用户
(3)分析器
查询,事务,对象权限,句法分析,词法分析
(4)优化器
访问路径,生成执行树,从里边选择最优路径。
(5)缓存和缓冲
(6)存储引擎(插件式的):
mysql提供插件式的存储引擎:(把表和数据对应起来)
MyisAM,InnoDB,NDB,Archive,Federated,Memory,Merge,Partner,Community,Custom
(7)文件系统
mysql的版本
alpha
beta
RC(Release Candidate)候选版
GA (General Availability)
-------------------------------------
mysql的RPM包
mysql 客户端工具
mysql-debuginfo 调试工具
mysql-devel 开发工具
mysql-embedded 嵌入式开发
mysql-ndb-management 集群管理
mysql-server 服务端工具
mysql-shared 共享库
mysql-shared-compat 提供向前兼容老版本的库
mysql-test mysql的测试组件
#yum install mysql mysql-server
------------------------------------
绿色安装;
bin
data 数据存放位置
include 头文件
lib 库文件
man 帮助文件
mysql-test
scripts 此目录中存放一些执行脚本的文件
share
sql-bench 提供mysql的压力测试
support-files 提供额外的配置文件
回顾一下mysql的二进制安装:
我们将存放数据的设备制作成逻辑卷。
创建分区
- #mkdir /mydata
-
#pvcreate /dev/sda5
-
#vgcreate myvg /dev/sda5
-
#lvcreate -L 2G -n mydata myvg
-
#mke2fs -j /dev/myvg/mydata
-
#mount /dev/myvg/mydata /mydata
-
#mkdir /mydata/data
创建用户和组,二进制编译安装
- #groupadd -g 3306 mysql
-
#useradd -u 3306 -g mysql -M -s /sbin/nologin mysql
-
#chown -R mysql:mysql /mydata
-
#tar xf ~/mysql-5.1.45-linux-i686-glibc23.tar.gz
-
#cd /usr/local
-
#ln -sv ~/mysql-5.1.45-linux-i686-glibc23 ./mysql
-
#cd mysql
-
#chown -R mysql:mysql ./
-
#scripts /mysql_install_db --user=mysql --datadir=/mydata/data
-
#chown -R root ./
-
#cp support-files/mysql.server /etc/init.d/mysqld
-
#chkconfig --add mysqld
-
#chkconfig mysqld on
-
#cp support-files/my-medium.cnf /etc/my.cnf
-
#vim /etc/my.cnf
-
添加:datadir=/mydata/data
-
-
库文件
-
#vim /etc/ld.so.conf.d/mysql.conf
-
添加:/usr/local/mysql/lib
-
重新加载模块
-
#ldconfig
-
-
头文件
-
#ln -sv /usr/local/mysql/include /usr/include/mysql
-
-
#service mysqld start
初始化mysql:
mysql的配置文件的查找次序:
/etc/my.cnf
/etc/mysql/my.cnf
$MYSQL_HOME/my.cnf
/path/to/file when defaults-extra-file=/path/to/file is specified
~/.my.cnf
如果在上述文件中的配置出现冲突,以最后找到的为准。
如果发现多个配置文件,Mysql会将所有的配置文件组合起来,则所有的配置都生效。
$MYSQL_HOME,安装mysql的家目录。如果没有指定,则默认会存放在数据目录中
在windows中的查找次序
%WINDIR%\my.ini,%WINDIR%\my.cnf
C:\my.ini,C:/my.cnf
%INSTALLDIR%\my.ini,%INSTALLDIR%\my.cnf
/path/to/file when defaults-extra-file=/path/to/file is specified
--------------------------------
安装完成后,通常会产生5个用户
3个系统账号。localhost,localhost.localdomain,127.0.0.1
2个匿名账号localhost,localhost.localdomain
在我这台主机上就是:localhost,stu11.example.com,127.0.0.1,localhost,stu11.example.com
#mysql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| test |
+--------------------+
use mysql;使数据库生效
show tables;查看表
mysql> use mysql;
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| func |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| proc |
| procs_priv |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
DESC 表名;查看表结构
mysql>desc user;
mysql> select Host,User,Password from user;
+-----------------------+------+------------------+
| Host | User | Password |
+-----------------------+------+------------------+
| localhost | root | |
| stu11.example.com | root | |
| 127.0.0.1 | root | |
| localhost | | |
| stu11.example.com | | |
+-----------------------+------+------------------+
mysql> select Host,User,Password from user\G;
*************************** 1. row ***************************
Host: localhost
User: root
Password:
*************************** 2. row ***************************
Host: stu11.example.com
User: root
Password:
*************************** 3. row ***************************
Host: 127.0.0.1
User: root
Password:
*************************** 4. row ***************************
.......
------------------------------------------------------------
设置密码,撤销匿名用户
设置密码有三种方式:
(一)
#mysqladmin -u root password 'redhat'
或者:
#mysqladmin -uroot -hlocalhost password 'redhat'
(二)
#mysql
mysql>set password for root@localhost=password('redhat');
mysql>flush privileges;
(三)
#mysql
mysql>update user set password=password('redhat') where User='root' and Host='127.0.0.1';
mysql>flush privileges;
-------------------
删除匿名用户
#mysql
mysql>drop user '' @localhost;
mysql>drop user '' @localhost.localdomain;
mysql>flush privileges;
---------------------------
mysql> select Host,User,Password from user;
+-------------------+------+------------------+
| Host | User | Password |
+-------------------+------+------------------+
| localhost | root | 27c30f0241a5b69f |
| stu11.example.com | root | |
| 127.0.0.1 | root | 27c30f0241a5b69f |
+-------------------+------+------------------+
-----------------------
此时你再访问mysql就得输入密码了。
#mysql -uroot -p ,不指-h 默认会在localhost主机登陆,输入密码,就可以进入了。
----------------------------------------
当有远程主机访问Mysql时,我们就要为远程客户端授权。
授权:mysql> grant all privileges on *.* to root@'%' identified by 'hanlenry';
mysql> select Host,User,Password from user;
+-------------------+------+------------------+
| Host | User | Password |
+-------------------+------+------------------+
| localhost | root | 27c30f0241a5b69f |
| stu11.example.com | root | |
| 127.0.0.1 | root | 27c30f0241a5b69f |
| % | root | 124260db45f0ebd0 |
+-------------------+------+------------------+
获得帮助:
#mysql -?
#mysql -I
#mysql --help
----------
Options for MySQL User Identification
--user 相当于-u
--password 相当于-p
----------------------
Options for Establishing a Connection
--protocol
tcp 客户端,服务器不在同一个主机
socket unix only
pipe windows only
memory windows only
--host 相当于-h ,服务器的地址
--port tcp/ip连接的端口号
--shared-memory-base-name
--socket
--------------------------------
设置默认数据库
-D
--database
#mysql -uroot -D mysql -p
显示当前默认数据库
mysql> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
mysql是基于命令行的客户端工具
和服务器端通信的方式:
交互式,就是我们上面使用mysql命令。
批处理
把mysql命令写到脚本中:
#vim my.sql
create database mydb;
让mysql执行批处理脚本文件
#mysql -uroot -p < my.sql
或者:source /root/my.sql
-------------
默认语句结束符是分号
只要不加分号就能执行的则是在客户端执行的
--------------------------
mysql>\h ,获得帮助信息
命令行编程
ctrl+w 删除光标之前的单词
ctrl+y 复制
ctrl+u 删除光标到行首
ctrl+k 删除光标到行尾
ctrl+e 移动到行尾
ctrl+a 移动到行首
手动打开命令补全功能,是对于新生成的命令
\#
写好了但又不想执行了,可以使用\c 取消
使用别的结束符,使用\d 重新定义结束符
mysql>\d //
\g 忽略默认的结束符,直接送往服务器
\G 竖排显示
想让显示的结果默认就是竖排显示的话,使用-E或--vertical
#mysql -uroot -E -p
mysql的客户端命令,提交到服务器的都称为SQL语句。
mysql> help keywords; 获得帮助信息
-e 不用登陆到服务器端,并将结果返回的选项
#mysql -uroot -p -e "show databases"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| test |
+--------------------+
想从某个数据库中的某个表中检索数据
#mysql -uroot -p -e "select user,host,password from mysql.user;"
+-------------------+------+------------------+
| Host | User | Password |
+-------------------+------+------------------+
| localhost | root | 27c30f0241a5b69f |
| stu11.example.com | root | |
| 127.0.0.1 | root | 27c30f0241a5b69f |
| % | root | 124260db45f0ebd0 |
+-------------------+------+------------------+
--------------------------------------------------
实现管理工作:
mysqladmin
创建数据库,删除数据库,显示服务配置,当前状态信息等
# mysqladmin -uroot -p ping 检测当前mysql 服务器是否工作正常
mysqladmin子命令:
create 创建数据库
#mysqladmin create mydata
drop 删除数据库
#mysqladmin drop mydata
debug 开启调试功能,以二进制编译为准,二进制编译时才会产生debug文件。
mysql>show variables like '%datadir%' 来显示mysql的数据文件的目录
mysql> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| datadir | /mydata/data |
+---------------+-----------------+
1 row in set (0.00 sec)
# cd /mydata/data
以.err结尾的文件就是debug调试日志所存放的位置
#mysqladmin debug
extended-status
#mysqladmin extended-status 记录mysql操作过程中的统计数据
Qcache_hits mysql的缓存命中率
和mysql> show status;效果一样;
flush-logs 手动实现日志文件的滚动
flush-privileges
flush-status 重新计数
password 修改密码
processlist 当前有多少用户连接进来,执行什么操作
reload
star-slave 启动从服务器
status 显示全局信息
#mysqladmin status --sleep 3 --count 2
stop-slave
variable
version 显示版本号,以及统计信息
图形化数据库管理工具:
SQLyog windows上基于图形界面连接远程Mysql进行数据库管理的工具
MySQL Front
phpMyAdmin
MySQL Query Browser 数据库查询浏览器
MySQL Administration
MySQL Workbench 数据库设计工具
-----------------------------------------------------------------------
行:记录(record)
列:字段(filed)
每一个字段都要定义数据类型。
数据类型决定了;
所能存储的范围
固定长度还是可变长度
对应的数据类型的排序机制
是否可以被索引
常见的数据类型:
字符型
二进制大对象类型
数值类型
布尔型
日期时间型
Interval类型
数值类型:精确型和浮点型
字符类型:BINARY 严格区分大小写
ENUM 枚举型
SET 集合性
定义字符串类型:
固定长度;
character(20)
char(20)
可变长度:
varchar
sql语句:
DML:update,delete
DDL:create,drop
create可以创建数据库,表,索引,视图
创建表:
mysql> CREATE DATABASE mydb;
mysql> show databases;
mysql> use mydb;
mysql> CREATE TABLE Students (
-> sno char(10) NOT NULL PRIMARY KEY,
-> sname char(20) NOT NULL,
-> s*** char(2) NOT NULL DEFAULT 'M',
-> sage int NOT NULL,
-> sdept char(20) DEFAULT 'computer');
Query OK, 0 rows affected (0.02 sec)
mysql> DESC Students;
+-------+----------+------+-----+----------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+----------+-------+
| sno | char(10) | NO | PRI | NULL | |
| sname | char(20) | NO | | NULL | |
| s*** | char(2) | NO | | M | |
| sage | int(11) | NO | | NULL | |
| sdept | char(20) | YES | | computer | |
+-------+----------+------+-----+----------+-------+
5 rows in set (0.02 sec)
char 最多可表示255个字符的长度
varchar 65535个字符
tinytext 255个字符
text 65535个字符
not null 不允许为空
null 可以为空
default 默认值,字符串加引号
character set 字符集
collation 字符集的排序方式
>show character set; 显示所有支持的字符集
>show collation; 所支持的所有排序方式
auto_increment 索引号自动加1,但必须为主键,使用NOT NULL 来修饰
float 4bytes
double 8bytes
(1)显示当前日期:
select current_date();
mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2011-08-31 |
+----------------+
1 row in set (0.02 sec)
(2)显示当前时间:
select current_time();
mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 00:58:19 |
+----------------+
1 row in set (0.02 sec)
创建表:
>create table users(
>Id int unsigned auto_increment not null primary key,
>name varchar(100) not null,
>*** enum ('F','M') not null default 'm',age tinyint unsigned);
插入数据:
>insert into users (name,age) values ('Jerry',18);
>select * from users;
工作模式:当我们定义了非空,但是如果你给了空值,则根据工作模式的不同,会有不同的结果。
>show variables like '%sql_mode%';
>set sql_mode='ansi';插入成功,但是会提示警告。
>set sql_mode='STRICT_ALL_TABLES';严格限定,如果你的姓名为空,则会无法插入
常用的工作模式:ANSI(字符串只能使用单引号)
IGNORE_SPACE 忽略内建函数的空白字符
STRICT_ALL_TABLES 拒绝违反规定的插入
TRANITIONAL 传统模式
mysqlde 的变量类型:
全局变量
对每一个新建立的回话都生效
回话变量
只对当前会话有效
show variables
show global variables
show session variables
使用like做模式匹配:
% 任意字符
? 一个字符
show global variables like 'time%'
如果你明确知道名字
只支持小写
select @@global.sql_mode;
select @@session.sql_mode
修改变量值:
set session sql_mode='traditional';
set global sql_mode='traditonal';不会立即生效。
如何区分大小写:
对于关键字和函数名来说是不区分大小写的,
对于数据库,表,视图名,则对应于你的操作系统,在linux上区分大小写,在windows上不区分大小写
存储过程和存储函数不区分大小写。但是触发器区分大小写。
别名:区分大小写
字符串:取决于数据类型
--------------------------------------
管理数据库:
安装完成mysql后,在数据目录中,每个目录就是一个数据库
>CREATE DATABASE IF NOT EXISTS mydb;
>CREATE DATABASE IF NOT EXISTS mydb2 CHARACTER SET 'gbk';
>CREATE DATABASE IF NOT EXISTS mydb2 CHARACTER SET 'gbk' COLLATE 'gbk_bin';
>SHOW CREATE DATABASE mydb2;
>DROP DATABASE IF EXISTS mydb2;
-------------------
改变
>ALTER DATABASE mydb2 COLLATE 'gbk_bin';
>ALTER DATABASE mydb2 CHARACTER SET 'gbk';
key (键),index
候选键:由一个属性名或多个属性名组成。表中任一行在候选键属性上的值是唯一的,并且不存在更小的真子集。
主键,primary key,从多个候选键中选择一个作为区分行的唯一标识符。
唯一键约束:它允许为空。unique key
外键:foreign key
mysql 支持哪些存储引擎
SHOW ENGINES;
存储引擎是表级别的概念
MyISAN 不支持事务
索引会降低数据库的响应效率