2011年（65）
分类： LINUX
2011-09-11 21:16:52
在介绍mysql之前我们先来介绍几个概念。
----------------------------------------------------------------------------------------------
1.什么是数据库？
数据库（DataBase，DB）是一个长期存储在计算机内的、有组织的、有共享的、统一管理的数据集合。它是一个按数据结构来存储和管理数据的计算机软件系统。
2.什么是DBMS？
数据库管理系统(database management system)是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称dbms。它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。用户通过dbms访问数据库中的数据，数据库管理员也通过dbms进行数据库的维护工作。它可使多个应用程序和用户用不同的方法在同时或不同时刻去建立，修改和询问数据库。
DBMS提供数据定义语言DDL（Data Definition Language）与数据操作语言DML（Data Manipulation Language），供用户定义数据库的模式结构与权限约束，实现对数据的追加、删除等操作。
3.数据组织模型有哪些？
数据模型有层次模型，网状模型，对象模型，关系模型等，用的比较多的是关系模型。
层次模型：是一种有根结点的定向有序树。（一个父节点可以有多个子节点，但每一个子节点只能有一个父节点）
网状模型：按照网状数据结构建立的数据库系统称为网状数据库系统。
关系数据模型：在关系数据库中，对数据的操作几乎全部建立在一个或多个关系表格上，通过对这些关系表格的分类、合并、连接或选取等运算来实现数据的管理。
4.什么是RDBMS？
RDBMS----ralational database management system关系数据库管理系统。
5.全球著名的大型RDBMS
Oracle, Sybase, Informix, DB2, EnterpriceDB
中型的RDBMS
SQL Server, Access
开源的RDBMS
MySQL, PostgreSQL
Memorydb, alibase
PostageSQL是兼容性最好的，而mysql适用于中小型企业，是开源界的老大。
---------------------------------------------------------------------------------------------
下面我们开始正式介绍mysql
------------------------------------------------------
1.mysql百科名片：
MySQL是一个小型关系型数据库管理系统，开发者为瑞典MySQL AB公司。在2008年1月16号被Sun公司收购。而2009年，SUN又被Oracle收购。目前MySQL被广泛地应用在Internet上的中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。
2.mysql的主要特性：
1. 同时访问数据库的用户数量不受限制；
2. 可以保存超过50,000,000条记录；
3. 是目前市场上现有产品中运行速度最快的数据库系统；
4. 用户权限设置简单、有效。
3.mysql逻辑结构：
第一层是服务于C/S程序或者是这些程序所需要的：连接处理，身份验证，安全性等等。
第二层是很重要，这是MySQL的核心部分。包括了查询的解析，分析，优化，缓存以及所有内置的函数(如日期,时间,数学运算,加密)。还有一些功能是涉及到了存储引擎，如存储过程，触发器，视图等
第三层包括了存储过程。它们负责存储和获取所有存储在MySQL中的数据。就像Linux众多的文件系统一样。每个存储引擎都有自己的优点和缺陷。服务器是通过存储引擎API来与它们交互的。这个接口隐藏了各个存储引擎不同的地方。对于查询层尽可能的透明。这个API包含了很多底层的操作。如开始一个事物，或者取出有特定主键的行。存储引擎不能解析SQL，互相之间也不能通信。仅仅是简单的响应服务器的请求
4.mysql相关软件：
Mysql Server
Mysql Cluster
Mysql Proxy
Mysql Adminstration
Mysql QueryBrowser
Mysql Workbench
Mysql Migration Toolkit
Mysql Embeded Server
Mysql Drivers and Connectes
5.mysql技术架构
Memory and Connection Management
Query Parsing and Execution
Data Storage
Connectirity
Standards Compliance
Transactions
Query Caching
Extensibility
Synectric Multiprocessing support
Security
Application Programming Interfaces
6. 安装mysql：
1.源码安装
yum install mysql-server
2.绿色软件安装
# tar -zxvf mysql-5.1.50-linux-i686-glibc23.tar.gz -C /usr/local
# cd /usr/local
# ln -s mysql[Tab] mysql
# cd mysql
# groupadd mysql
# useadd -g mysql mysql
# chown -R mysql:mysql .
# mkdir /data
# chown mysql:mysql /data/ -R
# ./scripts/mysql-install-db --datadir=/data --user=mysql
# chown -R root .
# ls /data/
# cd support-files/
#cp my-large.cnf /etc/my.cnf
#vim /etc/my.cnf
datadir = /data (添加)
# cd ..
# bin/mysql_safe --user=mysql &
# netstat -tnlp (查看3306端口)
# cp support_files/mysql.server /etc/init.d/mysqld
#vim etc/profile
PATH=$PATH:/usr/local/mysql/bin (添加)
在export RATH……之上
# . !$
#ln -sv /usr/local/nysql/include /usr/include/mysql
# ls /usr/include/mysql(发现以.h结尾的文件)
#Vim /etc/ld.so.conf.d/mysql.conf
/use/local/mysql/lib (添加)
#ldconfig -v
#vim /etc/man.config
MANPATH /usr/local/mysql/man
这样就可以以匿名的身份登录mysql了
#mysql
mysql> (这样就表示登陆进去了)
7.mysql启动时配置文件的寻找顺序：
/etc/my.cnf
/etc/mysql/my.cnf
$MYSQL_HOME/my.cnf
/path/to/file when defaults-extra-file=/path/to/file
~/.my.cnf
注意：查找顺序由上而下，当找到第一个时会接着找第二个，若内容不同时则以合集为准，若内容冲突则以最后一个文件为准。
8.让mysqld启动时读取额外的配置文件：
#vim /etc/init.d/mysqld
在$bindir /mysqld-safe --datadir=$datadir后 (添加 defaules-extranfile=/etc/mysql/my_instance.cnf) 就ok了。
9.设置密码：
为所有空密码的root用户设置密码，删除匿名账号
法一：
mysqladmin -u root password 'new-password'
mysqladmin -u root -h this host name password 'new-password'
eg:mysqladmin -uroot -hlocalhost password 'redhat'
mysql（此时登不进去了，只能提供密码登）
mysql -uroot -p(输入密码登陆）
法二:
UPDATE user-name SET Password=PASSWORD('redhat')
PASSWORD表示加密存储
FLUSH PRIVILEGES;(通知服务器将修改的密码加载至内存）
DROP USER
DROP USER
FLUSH PRIVILEGES;
SELECT Host,User,Password FROM user;：
10.mysql常见的客户端工具：
mysql
mysqladmin
mysqlshow
通用选项（大部分客户端软件都可使用）：
--user -u（重要）
--passwd -p（重要）
--host -h（重要）
此处指列举这三个最重要也是最常用的，别的想了解的话可以去查man文档。
11.mysql数据类型：
字符型
char（以字符形式存储）最多255字符
binary（以二进制形式存储）最多65535字符
varchar
varbinary
blob
clob
text
数值型
精确数值型
整型
int，tinyint，smallint，int，mediumint，bigint
十进制实数
近似数值型
单精度浮点
双精度浮点
日期时间型
date
time
datetime
timestamp
year
内建类型
enum枚举型
聚合型：聚合函数 COUNT(),MAX（），MIN(),AVG(),SUM()
------------------------------------------------------------------------
接下来我们来介绍一下SQL语句：
------------------------------------------------------------------------
1.创建表（table）：
CREATE TABLE table_name
一个表必须属于某个数据库。
举个简单的例子：
CREATE TABLE employee （
id INT NOT NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
UNIQUE (id）,
INDEX(last_name，first_name）
）；
此表包含id，last_name,first_name三列，并把id设置为唯一键，以last_name，first_name为索引。
创建表的三种方法：
创建表的方式1：
mysql>CREATE TABLE tb1 (
->id tinyint not null auto_increment primary key,
->name varchar(30));
SHOW TABLE STATUS LIKE 'tb1'\G(查看引擎）
一般不建议使用不同的引擎。
DESC users（相当于DESCRIBE users显示表结构）
创建表的方式2：
mysql> CREATE TABLE role2 SELECT * FROM role WHERE name LIKE '%use%';
mysql>SELECT * from role2
创建表的方式3：
mysql>CREATE TABLE role3 LIKE role;(使用LIKE复制另一个表的表结构，而不复制内容）
2.索引的创建删除：
直接创建索引的方法：
index_type:
USING {BTREE |HASH}
CREATE INDEX index-name ON table-name（col-name);
删除索引：
DROP INDEX index-name ON table-name;
例子：
>CREATE TABLE users2 (
->ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
->Name VARCHAR(30) NOT NULL,
->Age TINYINT UNSIGNED NOT NULL,
->Gender ENUM('F','M')NOT NULL DEFAULT 'M',
->Score FLOAT,
->INDEX index-score(score));(给score创建一个索引）
>SHOW INDEXES FROM users2;
3.视图的创建删除：
创建视图：
CREATE VIEW view-name AS SELECT clause(语句）
CREATE VIEW tb2 AS SELECT UID,Name，Age，Gender，CouseID FROM tb1 WHERE CouseID IS NOT NULL
创建视图其实是将AS后的SELECT 语句保存了下来，等执行SELECT * FROM tb2； 时其实是执行了两个select语句。
注意：向视图中插入数据的时候其实是向基表中插入数据，因为视图只是基表中的某些字段，所以插入的时候只是插了这几个字段，别的字段为空，但如果基表中不允许别的某个字段为空，此时是会出错的。
关键字 WITH CHECK OPTION 9(表示把基表的约束加到view里）
删除视图：
DROP VIEW view-name
4.设置存储引擎：
ENGINE [=] engine_name设置引擎
eg:alter table mytb engine='innodb';
AUTO_INCREMENT [=] value 设置自动增长值
查看引擎：
use BindDns（使用此数据库）
SHOW TABLE STATUS\G（查看表的状态，里面包含引擎）
SHOW TABLE STATUS LIKE ‘zones'\G
如何改变默认存储引擎：
1.启动服务时设定默认存储引擎：
vim /etc/my.cnf
default-storage-engine = MyISAM
service mysqld restart
2.SET GLOBAL storage_engine=engine_name;
eg:SET SESSION storage_engine = InnoDB;
5.select语句：
语法：SELECT column1，col2 FROM table-name
总结WHERE用到的关键字：
lIKE:
%（通配符，表示任意长度的任意字符）
- （任意单个字符）
BETWEEN AND
IN
IS NULL
IS NOT NULL
>
<
=
REGEXP(正则表达式）
+,-,*,/,%
举例：
首先得登录mysql，然后才能做下面的操作
mysql>use sampledb
mysql>SELECT rid，name FROM role；
mysql>SELECT name，rid FROM role；（命令中列出的字段顺序决定了最终显示的顺序）
mysql>SELECT * FROM role WHERE rid >= 2;
mysql>SELECT * FROM role WHERE weight != 0;
mysql>SELECT * FROM role WHERE name LIKE '%user%';
mysql>SELECT * FROM role WEHRE rid BETWEEN 1 AND 2;
mysql>SELECT * FROM role WHERE rid IN (1,2,4,5);
mysql>SELECT * FROM role WHERE rid + 1 >=3;
6.order：
检索出来的结果按某种规律排序
ORDER BY clause(以哪个字段排序）默认是升序
DESC(降序）
ASC（升序）
7.LIMIT：
LIMIT n （显示前n行）
SELECT * FROM role LIMIT 2;显示前2行
SELECT * FROM role LIMIT 2,1;跳过前两行，显示后面的一行
8.DISTINCT(消除重复的行）
SELECT DISTINCT zone from role LIMIT 100;
CREATE TABLE test SELECT zone from role LIMIT 100;
SELECT DISTINCT * FROM test；
9.GROUP BY (分组）
CREATE TABLE mytb SELECT zone from role LIMIT 1000;
SELECT zone，COUNT(*) FROM mytb；（此处的zone是表中的第一个自段的名字)
统计zone有多少行
SELECT zone，COUNT(*) FROM mytb GROUP BY zone；（以某字段为准，对表中的数据分组；用来做聚合，查看某个值有多少行）
SELECT zone,COUNT(*) AS numrecords FROM mydb GROUP BY zone HAVING numrecords >=10;
分组之后再对其进行过滤。（AS只是改变了显示时的字段值，不改变表中的内容）
10.子查询:
SELECT AVG(Age） FROM knight WHERE Age IS NOT NULL;
SELECT Name FROM knight WHERE Age > (SELECT AVG(Age) FROM knight WHERE Age IS NOT NULL);(年龄大于seclect子句列出来的平均年龄）
11.变量设置与引用：
SET @num=10；（设置变量）
SELECT zone,COUNT(*) AS nurecords FROM mydb GROUP BY zone HAVING numrecords >=@num;（引用变量）
变量设置和引用都使用@。
12.字段的添加删除与修改：
ADD语法：
ADD col_name(字段名） col_def（默认插到最后）
ADD FIRST|AFTER col_name（插到某个字段前或后）
MODIFY语法：
MODIFY col_name col_defination(只修改字段的内容和位置，不需要对字段本身重命名）
CHANGE old_name new_name col_defination（修改字段的内容的同时需要修改字段名）
DROP语法：
删除字段：
DROP col_name
删除键：
DROP PRIMARY KEY
删除表：
DROP TABLE [IF EXISTS] table_nameDROP {INDEX|KEY} index_name
13.数据的插入删除修改：
插入数据：
INSERT INTO table_name （col1,col2) VALUES (val1,val2)
INSERT INTO table_name SET col1=VAL1,col2=VAL2
REPLACE(插入时如有重复的将会覆盖）
修改数据：(一定要指定条件，不然就全部更新了）
UPDATE table_name SET col1=val1[,...][WHERE clause] LIMIT n;
删除数据：（一定要指定条件，不让就全删了）
DELETE FROM table [WHERE cluase];
TRUNCATE TABLE table_name(清空表信息）
14.连接的类型：
交叉连接
内连接
外连接
左外连接
右外连接
全外连接
UNION
自连接
各种连接举例：
交叉连接
例： SELECT * FROM table1，table2
内连接即对称连接
例： SELECT * FROM table1，table2 WHERE table1.CouseID=table2.CID
左外连接
（以左表为标准，左表中的都显示出来，对应的右表里有的显示，没的为空）
例：SELECT k.name,j.Cname FROM knight AS k LEFT JOIN juexue AS j ON j.Author=k.UID;
右外连接
例：SELECT k.name,j.Cname FROM knight AS k RIGHT JOIN juexue AS j ON j.Author=k.UID;
全外连接
左表右表的字段都显示出来。mysql不支持。
UNION
用于把两个表的查询结果连接起来
SELECT UID AS ID,Name AS NAME FROM knight；
UNION
SELECT CID AS ID,Cname AS NAME FROM juexue；
自连接
SELECT k1.Name，k2.Name AS Tutor FROM knight AS k1，knight AS k2 WHERE k1.Tutor=k2.UID;
15.SQL语句的综合应用例子：
CREATE DATABASE test;
use test;
>CREATE TABLE knight (
->UID INT UNSIGNED AUTO_INCREMENT NOT NULL UNIQUE,
->Name VARCHAR(10) NOT NULL,
->Alias VARCHAR(30),
->Age TINYINT,
->Gender ENUM('F','M'),
->CouseID TINYINT);
DESC knight;
ALTER TABLE knight ADD Master TINYINT UNSIGNED;
ALTER TABLE knight MODIFY Gender ENUM('F','M')NOT NULL DEFAULT 'M';
ALTER TABLE knight CHANGE Master Tutor TINYINT UNSIGNED AFTER Age;
DESC knight;
ALTER TABLE knight ADD INDEX (Age);
SHOW INDEXES FROM knight；
ALTER TABLE kinght RENAME TO knights；
INSERT INTO knight (Name) VALUE ('Guoer');
SELECT * FROM knight;
INSERT INTO knight (Gender) VALUE ('F');
SHOW WARNINGS;
DELETE FROM knight WHERE Gender='F';(删除之后此id号就不能用了，再插入时会掠过此id号）
INSERT INTO knight SET Name='Wei xiaobao';
ALTER TABLE knight AUTO_INCREMENT=2;(设定新插入的数据id从2开始）
INSERT INTO knight VALUE(3，'Hong qigong','jiuzhishengai',67,NULL,'M',NULL);
INSERT INTO knight (Name) VALUES ('WANG'),('LI'),('SUN');
UPDATE knight SET Age=21;
UPDATE knight SET Age=19 LIMIT 2;
DELETE FROM knight WHERE UID=5;
-------------------------------------------------------------------------
下面我们来解释事务和锁机制：
-------------------------------------------------------------------------
1.事务（Transaction）机制：
1）事务的四个特性
原子性：
--事务中的全部操作在数据库中是不可分割的，要么全部完成，要么均不执行
一致性：
--几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致
隔离性：
--事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明的
持久性：
--对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障
只有满足这四种特性才可以称作事务。
2）事务的隔离级别
读未提交（级别最低）--一个事务修改了数据，另一个事务马上会看到，而且会受影响。
读提交---一个事务修改了数据，直到事务完成并提交数据，另一个事务才会看到，并受到影响。
可重读---一个事务修改了数据，无论自己什么时候读，数据都不会变的，直到自己的事务完成，才会发现数据的改变。
串行化---一个事务改变了另一个事务中要用到的值，则另一个事务会停下来，等待它完成后才可以继续。（级别最高）
隔离级别是可以调整的 。
级别越高，安全性越高，并发性越差。MYSQL默认使用的是可重读级别。
SHOW VARIABLES LIKE 'tx%';查看当前的事务级别
3）事务机制用法：
START TRANSCATION启动事务
ROLLBACK;(撤销上一步操作，只要事务没提交，就可以回退）
update 对数据库进行查询添加修改删除操作
COMMIT（提交事务）
SELECT @@SESSION.tx_isolation；（查看事务级别）
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;(级别改为读未提交）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED；（级别改为读提交）
SET SESSION TRANSACTION ISOLATION LEVEL READ REPEATABLE READ;(级别改为可重读）
2.锁机制
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。
所级别：
表锁--开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行锁--开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高
页锁--开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
对mysql锁而言，不同的存储引擎支持不同的锁机制。请参考下图：
LOCK TABLES knight READ;(加一个读锁）
UNLOCK TABLES;解锁
LOCK TABLES knight WRITE;(加一个写锁）
INSERT INTO knight SET Name='wang'；(此时别的事务无法读也无法写）
UNLOCK TABLES;解锁
3.存储引擎：
常见的存储引擎及其区别见上表。
最常使用的2 种存储引擎:
1. Myisam是Mysql的默认存储引擎，当create创建新表时，未指定新表的存储引擎时，默认使用Myisam。
每个MyISAM在磁盘上存储成三个文件。文件名都和表名相同，扩展名分别是.frm
（存储表定义）、.MYD (MYData，存储数据)、.MYI (MYIndex，存储索引)。数据文件和
索引文件可以放置在不同的目录，平均分布io，获得更快的速度。
2. InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比Myisam的存储引擎，InnoDB 写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索
引。
-------------------------------------------------------------------------
最后我们一起来了解一下用户管理的相关知识：
-------------------------------------------------------------------------
1.mysql授权表共有5个表：user、db、host、tables_priv和columns_priv
user表：列出可以连接服务器的用户及其口令，并且它指定他们有哪种全局（超级
用户）权限。在user表启用的任何权限均是全局权限，并适用于所有数据
库。
db表：db表列出数据库，而用户有权限访问它们。在这里指定的权限适用于一个数
据库中的所有表。
tables_priv表：tables_priv表指定表级权限，在这里指定的一个权限适用于一个
表的所有列
columns_priv表：指定列级权限，在这里指定的一个权限只适用于某些列。
host表：不受create和revoke 的影响。
2.创建用户：
CREATE USER [IDENTIFIED BY 'password'];
CREATE USER IDENTIFIED BY '123456';（%表示任意长度的任意字符）
此时的用户仅有的权限就是连上数据库,这个权限叫usage。
3.删除用户：
DROP USER
4.给用户授权：
GRANT ALL PRIVILEGES ON mydb.mytb（privileges是权限的意思，将所有权限授给mydb数据库里的mytb表）
GRANT CREATE ON hellodb.* TO ;（hellodb.*表示hellodb数据库里的所有表）
FLUSH PRIVILEGES;
GRANT DROP ON hellodb.* TO ;
FLUSH PRIVILEGES;
SHOW GRANTS FOR ;
mysql -ujerry -p -h172.16.6.1（再连接一个虚拟机）
此时jerry就有了创建和删除表的权限。
5.收回用户权限：
REVOKE SELECT ON hellodb.* FROM ;
FLUSH PRIVILEGES;
mysql -ujerry -p -hlocalhost（重新登录mysql）
此时jerry就没有创建表的权限了。
6.WITH GRANT OPTION可以把获得的权限授予其他用户
设定其所使用的资源：
WITH MAX_QUERIES_PER_HOUR n;
WITH MAX_UPDATES_PER_HOUR n;
WITH MAX_CONNECTIONS_PER_HOUR n;
WITH MAX_USER_CONNECTIONS n;
修改完之后不会立即生效需重读资源限定的定义
flush user_resources;(重读资源限定的定义）
7.修改root密码的方法：
SET PASSWORD FOR );
FLUSH USER_RESOURCES;
SHUTDOWN(在mysql提示符下关闭服务器）
SUPER
8.字段级别权限：
GRANT SELECT(name） ON db.table TO
执行存储例程的权限：
GRANT EXECUTE ON
9.如何恢复管理员密码：
1.停止服务，以如下方式启动：
service mysqld stop
/usr/local/mysql/bin/mysqld_safe --skip-grant-tables --skip-networking &
2.mysql
mysql>USE mysql;
mysql>UPDATE user SET Password=PASSWORD('redhat') WHERE User='root' AND Host='localhost';
mysql>UPDATE user SET Password=PASSWORD('redhat') WHERE User='root' AND Host='127.0.0.1';
\q
3.重新正常启动mysql
killall mysqld
service mysqld start
10.将所有用户的权限恢复到初始状态
cd /usr/local/mysql
./scripts/mysql_install_db --datedir=/mydata/data --user=mysql
-------------------------------------------------------------------------
完结------
