现在的位置: 首页 > 综合 > 正文

linux下mysql详解

2012年07月04日 综合 ⁄ 共 9050字 ⁄ 字号 暂无评论

数据库是数据的集合,数据仓库。很早以前存储在文本文件中 有很大数据冗余 所以后来出现了数据库管理系统
早期数据库模型有层次模型、网状模型,使用很复杂,维护困难 后来出现了关系型数据库系统数据组成部分有两种:1元数据2 数据本身
RDBMS的特点:
1.数据以表格的形式出现
2.每行为各种记录名称
3.每列为记录名称所对应的数据域
4.许多的行和列组成一张表单
5.若干的表单组成database

数据库视角有逻辑模型和物理模型
逻辑模型的数据库对象有表(核心对象)、索引(快速查询操作,降低写操作)、视图(虚表,存储下来的select语句)、用户、存储过程、存储函数、触发器、事件调度器、游标

物理模型要有数据管理组件(元数据)、存储引擎、 物理文件
键有:主键(primary key)非空,不能相同(不能有相同值)一个表只能有一个。
外键(foreign key)表之间约束
唯一性约束(unique key)允许为空,如果不空 则值不能相同,一个表可以有多个
检查性约束(mysql不支持)
数据库管理系统要有:
1存储引擎
2安全管理
3管理元数据
4Managing transac事务管理(被看作一个整体的多个sql语句)
5一个事务完成之前不能另一个事务(隔离性)
6连接管理
7性能优化
8提供备份和还原的机制
9响应数据查询/修改请求

mysql是一种开放源代码的关系型数据库管理系统 。 mysql特征:速度快,单进程完全多线程 一个查询用一个独立的线程响应,有查询缓存的功能;高可靠性,伸缩性,容易安装部署,方便移植 兼容性,线程支持,国际化,广泛的编程应用,开源
mysql的存储引擎有MyISAM、InnoDB、NDB 集群专用、Archive 归档、Federated 联合存储引擎、Memory 、Merge 合并MyISAM 、Falcon
MyISAM:不支持事务机制;当创建一个数据表会产生三个文件:数据文件,索引文件和表结构定义文件
InnoDB:mysql 5.5以后版本之后默认使用的存储引擎,支持事务机制;当创建一个数据表只会产生一个文件:表空间文件
NDB:集群专用引擎
Archive:主要是将数据归档成一种格式,适合长期存放
Federrated:联合存储引擎
Memory:内存引擎;数据存储在内存中,但不能持久存储数据
Merge:此引擎允许你把许多结构相同的表合并为一个表

每个表都可以独立定义自己使用的存储引擎
不建议在同一个数据库当中使用不同的存储引擎
show engines 查看存储引擎
show table status 查看表的信息
show table status like 'user'\G 显示表的存储引擎
show global variables like '%engine%'; 查看默认存储引擎

mysql配置文件
/etc/my.cnf
/etc/mysql/my.cnf
$MYSQL_HOME/my.cnf
/path/to/file when defaults-exta-file=/path/to/file is specifed
~/.my.cnf 家目录下隐藏
查找顺序从上往下依次查找,越靠后找到的最后生效

mysql日志:
二进制日志
查询日志
错误日志
慢速查询日志
中继日志(复制中使用)
Mysql是单进程、多线程的

mysql支持命令行补全 ,命令历史
clear 取消命令的执行\c
delimiter \d 设定命令结束符 如:\d // 设定//为命令结束符
\q 退出
\g 不管结束符 直接把命令送到服务器端运行
\G 不管结束符 直接把命令送到服务器端运行并将结果竖排
-E 在连接之前用-E 显示结果将竖排
\! 不用退出mysql 直接执行shell命令 如:\! ls
\s 从服务器端获取状态信息
\.批处理模式
-H 输出html文档
-X 输出xml文档
--safe-updates 避免使用了不该用的命令导致数据库丢失

mysqladmin:
mysqladmin create mydb 创建数据库 不用连接mysql
drop 删除数据库
debug 输出显示调试信息
status 显示全局状态变量如 --sleep 3 隔3秒显示 --count 3 只显示3次
extended-status 显示所有的mysql系统变量
flush-hosts 清空dns缓存,内部错误信息
flush-logs 做日志滚动
flush-privileges
flush-tables 关闭表
flush-threads 重置线程缓冲池
kill 用于杀死客户端线程
processlist 线程列表
password 给用户设定密码
ping 用于查看服务器是否运行
reload =flush-privileges
refresh flush-hosts and flush-logs
shutdown 关闭mysql服务器
start-slave 启动从服务器
stop-slave 关闭从服务器
variables 显示全局系统变量

安装方式:1、rpm 2 源代码 3 绿色软件。解压直接使用
给mysql 加密码
mysqladmin -uroot -hlocalhost -p password '123456'
set password for root@localhost.localdomain=password('123456');
安装安装方式窗体底端
flush privileges;
添加用户并加密码
create user cacti@localhost identified by '123456';
删除用户drop user cacti@localhost;
给用户授权:
grant all privileges on testdb.* to cacti@localhost;

创建表:3种方式
1直接创建(通过定义字段等信息创建)
create table tb_name ();
create table classes ( CID SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL UNIQUE KEY, Name CHAR(20)) ENGINE=MyISAM DELAY_KEY_WRITE=1 COMMENT='dfjg';
2通过从其它表中检索数据后来创建表(检索过来的数据并不能附带原表中关于字段属性的定义)
create table tb_name () select clause
create table test select * from classes where CID < 3;
3以某表为模板创建表(不复制数据,只复制定义)
create table tb_name () like old_table_name
create table test2 like test

alter table 修改表
新增字段
alter table tb_name ADD Column_name column_defination
alter table tb_name ADD (column1 column_defi,column2,column2_defi);
alter table calsses add NumStu SMALLINT UNSIGNED;
alter table classes add (courseID1 smallint unsigned,courseID2 smallint unsigned);
修改字段 modify
alter table classes modify NumSTU smallint unsigned after courseID2 not null;修改位置
改变字段change
alter table classes change NumSTU NumOFSTU smallint unsigned not null;
删除字段drop
alter table classes drop courseID2;
改表名 rename alter table classes rename to class;
删除表 drop drop table classes if exists;

insert into 使用方法:
方法1:
insert into tb-name
insert into classes (Name,NumStu) values ('class2',50),('classes3',57);
方法2:
insert into tb——name set col1=val1,col2=val2,..
insert into classes set Name='class7',NumOfStu=77;
方法3:
create table test like classes;
insert into test select * from classes where CID <=5;

update 修改数据
update classes set CourseID=5 where CID=1;
select * from classes order by NumStu; 查询表 NumStu按升序排列
select * from classes order by NumStu DESC; 按降序排列
update classes set CourseID=6 order by NumStu limit 4; 按NumStu 升序排列 更新前4个 数据

delete 删除数据
delete from classes where NumStu < 50;
delete from classes order by NumStu DESC limit 2; 按NumStu降序排列删除2个数据

例:添加表
mysql> create table tb4 (
-> SID INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE KEY, AUTO_INCREMEN 自动增长
-> Name CHAR(40) BINARY NOT NULL,
-> Age TINYINT UNSIGNED NOT NULL,
-> Gender CHAR(1) NOT NULL DEFAULT 'M',
-> ClassID SMALLINT NOT NULL,
-> Description TINYTEXT ) ;

select
from table_name
where expression
group by 分组
having expression 实现group by 结果再做过滤
order by 排序

as 取别名
select Name as student,Age as NianLing from students where Age >=20;
distinct 去掉重复的 select distinct gender from students;
where 后跟表达式
select Name as student,Age as NianLing from student where Age-6 >=20;
select * from student where CID1 is null;
like:
select * from student where Name like 'H%';
select * from student where Name rlike '^H.*';
select * from student where Name rlike '^H.*F.*';
limit 限制
select * from student where Name not rlike '^H.*F.*' and Age >=20;

between:
select Name as student,Age as NianLing from student where Age between 18 and 25;
in 集合 范围比较操作符
select Name,Age from student where Age in (18,19,20,21);
group by 分组 通过聚合函数
select * from student group by Gender;
select count(*),Gender from student group by Gender;
select Gender,AVG(Age) from student group by Gender;
select Gender,AVG(Age),MAX(Age) from student group by Gender;
having 是group by的延伸 继续过滤
select Gender,AVG(Age) from student group by Gender having AVG(Age) >=20;
order by 排序select * from student ,courses where student.CID1=courses.CID;
select * from student order by Age DESC limit 2,3;
limit 限制显示的个数
select * from student order by Age DESC limit 2,3;

连接查询 :多表查询
笛卡尔乘积:两个表交叉连接
交叉连接:
应当尽可能避免交叉连接
select * from student, courses;

内连接(inner join):由=连接起来的
select * from student ,courses where student.CID1=courses.CID;
select student.Name,courses.Cname from student ,courses where student.CID1=courses.CID;
select s.Name,c.Cname from student as s,courses as c where s.CID1=c.CID;
select s.Name as Student,c.Cname as Course from student s ,courses as c where s.CID1=c.CID;
select c.Cname,t.Tname from courses as c, teachers as t where c.CID=t.TID;
select s.name, c.Cname, t.name from student as s, courses as c, techers as t where s.CID1=c.CID and c.TID=t.TID

外连接(outer join):不对称的连接
左外连接
select s.name, c.Cname from student as s left join course as c on s.CID1=c.CID;
右外连接select s.name, c.Cname from student as s right join course as c on s.CID1=c.CID;
完全外连接
自连接 selfjoin
select s1.name, s2.name from student as s1, student as s2 where s1.TID=s2.SID;
联合(union)
select name, age from studnet union select tname,age from teacher;
子查询:在一个查询基础上再一次查询
select avg(age)from student;
select name,age from student where age > (select avg(age)from student);
select count(*),name,age from student where age > (select avg(age)from student) group by gender;
select * from course;
select s.name, c.cname from (select name,age from student where age > (select avg(age)from student)) as s course as c where s.cid1=c.cid;
视图:
create view youngerstu as select Name,Age,CID1 from student where age < (select avg(age) from student);
show table status;
drop view viewname

存储引擎:
myisam 非事务 不支持外键 支持全文索引 没有数据缓存 支持索引缓存 支持hash和btree索引 表级别锁 快速读取新能 适用于数据仓库 支持数据压缩 不支持在线备份即热备(阻塞备份) 每张表最多支持64索引

InnoDb 支持基于多版本并发控制的事务 支持行级别的锁 支持外键 支持B-tree索引
支持可配置的用于索引和数据缓存的缓存大小 支持基于商业 在线非阻塞备份
存储数据在表空间中
memory不支持事务 表级锁 hash索引
show table status 显示表的存储引擎

xtraDB 作为innodb替代品

锁 死锁不可避免
读锁:共享锁 定义读多少之后能写
flush tables with read lock;读锁 不能写能读
unlock tables;释放读锁
写:独占锁,排它锁
lock table courses write;写锁 能读不能写
unlock tables;

锁被发起的方式来划分:
隐式锁:
显式锁:

锁粒度来划分:
表级锁
行级锁
页锁

锁实现级别:
服务器
存储引擎 隐式

事务;同时执行或不执行的sql语句
原子性
一致性
隔离性
持久性

支持事务要支持回滚(undo)
随机I/O 性能低下 事务日志记录详细操作步骤 将随机I/O 转换为线性I/O(连续的)
事务日志是日志文件,在磁盘上是连续的空间
通过undo回滚日志让未完成的事务退回到起点 redo日志实现重做,让未来得写入的日志重做
开启事务:start transaction
commit 提交
rollback 撤销
save point sp_name 保存点
rollback sp_name回到保存点

4个隔离级别
隔离级别越高性能越低
查看隔离级别 show global variables like 'tx_%';
读未提交:一个事务在commit之前 也能看见
读提交 提交之后才能看到改变的
可重读 默认级别 看到的状态是自己看到的,提交了也看不到
完全化 效率极低
降低事务复杂度

设置隔离级别:
set global transaction isolation level read uncommitted; 设置读未提交 全局

set global transaction isolation level read committed; 设置读提交

set global transaction isolation level repeatable read 可重读

set global transaction isolation leve

用户管理

columns_priv字段授权
procs_priv 存储过程授权
tables_priv表级别授权
db 在数据库上授权
host
user
the grant 授权
revoke 收回权限
usage 远程连接权限
rename user 重命名

create user 创建用户
create user 'user'@'host' identified by 'password'; 创建的用户仅能连接 show

GRANT privilege_list on [table] db.tables to 'uer'@'host' [identified by 'password']

grant execute on db.user to 'cactiuser'@‘localhost';
requre ssl 需要ssl建立连接
with grant option
show grants for cactiuser@‘172.16.%。%’;查看用户获得了哪些权限

revoke privilege_list on db.tables from 'user'@'host'回收权限
revoke delect on jaiaowu。* from cactiuser@‘172.16.%。%’;

WITH
with_option:
GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count

root@locahost

[REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]

super 管理其他不便管理的权限

alter 修改表结构
alter routine

create routine 创建

索引:特殊的数据结构
innodb tablespace clustered btree
一个表可以有多个索引 大多数16个索引 (索引在存储引擎级别上实现)
btree
rtree
fulltext
hash
对mysql 大多数为btree索引 :primary,unique,fulltext

btree:最左前缀索引(prefix indexes)
使用的场景:做全值匹配 where name='stu11111';
匹配最左前缀 like 'string%'
做范围匹配: >,<, >=,<=,between age >=30
不使用场景:(如果查询没有从索引列的最左侧开始)条件无法匹配最左前缀
不能跳过索引中的列: textindex(name,age,birthdate),where name like 'stu__' and birthdate >= '1990-01-01 00:00:00';
btree索引可以实现覆盖索引;

hash索引:索引的数据是涉及字段中的所有数据:key-value 键-值
如Oreiwerjae879 stu1
Oreiwerjae879 pointer(stu1所在行的磁盘上的位置
hash对范围匹配没有用 仅适用于name=stu1 name in(stu1,stu2),<> 速度比btree快
memory引擎能用hash索引

Rtree索引 MyISAM引擎支持
fulltext MyISAM引擎支持

myisam 不支持hash索引 支持btree fulltext rtree
innodb 支持自适应的hash索引 支持btree
memory 默认使用hash索引 支持btree

创建索引:
简单索引 :索引单个字段
组合索引: 索引多个字段
create index

show index indexs keys 显示索引
FROM | in tb_name

如: show index from students;

drop index index_name 删除索引
如: drop index Extrainfo on students;

log
错误日志
二进制日志
慢查日志
查询日志
中继日志(从服务器上)

错误日志包含 服务器启动和关闭过程中的信息,服务器运行过程中的错误信息,事件调度运行一个事件时产生的信息,在从服务器上启动从服务器进程时产生的信息。

log_error 存放错误日志
log_warring 警告日志 =1 启动 一并记录到错误日志中

binary logs 做即时点恢复 复制
记录每一个潜在或存在的
记录 dml ddl 语句
记录的并非很准确
show variables like '%bin%';
binary log 最好不要跟数据文件放在同一磁盘上;
show master status;查看当前使用的二进制日志是哪个
二进制日志的格式:
基于语句的
基于行
混合mixed

查看二进制日志:
mysqlbinlog --start-position=‘10000’mysql-bin.0000002;

set sql_log_bin=0;
source /path/to/backup.sql
set sql_log_bin=1;

默认general-log为off

开启慢查询日志
slow_query_log
set global slow_query_log=‘on’;

flush logs 滚动日志 关闭老的日志 开启新的

purge binary logs 清除旧日志
如 purge binary logs to 'msyql-bin.00004';
purge binary logs

给我留言

留言无头像?