MySQL数据库学习笔记

Mysql入门到精通
安装Mysql
    tar xxfv mysql-xx
    cd mysql-xx
    make
    make isntall
    ./script/mysql_install_db
启动和停止
    mysqladmin version
    mysqlshow
service mysql start
/etc/rc.d/init.d/mysqld start
mysqld_safe&
Mysql启动测试
locate safe_mysqld   locate在linux下找一个脚本
mysqladmin ping 检测是否在工作
mysqladmin version Mysql版本号
mysqlshow 查看有多少数据库
刚安装完后有两个数据库
Mysql用户管理
增加一个用户test_user密码为123,
grant select,insert,update,delete on *.* to ’test_user’@’localhost’ identikfied by ’123’;
use mysql
grant select,insert,update,delete on *.* to ’romote’@’%’ identikfied by ’remote’
grant(vt同意,)语句创建Mysql用户并指定其权限,而revoke(vt,撤回,废除,宣告无效)语句删除权限,grant和revoke影响两个表
user,db数据库权限,tables_priv表的权限,columns_priv列级权限
use mysql;
delete from User where User="";   //删除没有必要的用户
update User set Password=PASSWORD(’newpassword’) where User=’root’ //更改用户的密码
update User set Password=PASSWORD(’king’) where User=’remote’
如果要更改登陆终端,更新User表中相应用户的Host字段
平时不要用root用户管理mysql,Mysql的用户管理是通过User表来实现的，添加新用户常用有两个，一是在User表插入相应的数据行,同时设置权限;二是通过grant命令创建具有某种权限的用户.
grant的语法:
privileges(n特权,特别待遇,基本公民权利);
identified(vt识别,鉴别,把..和..看成一样v确定)
格式:grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码";
           (管理权限)
grant all on sailboy.* to NewUserName@HostName identified by "password";
grant all on *.* to postfix@localhost identified by "postfix";
grant usage on *.* to NewUserName@HostName identified by "password";   只允许登录--其它什么也不允许做。
grant select,insert,update on mydb.* to NewUserName@HostName identified by "password";
grant update,delete on mydb.TestTable to NewUserName
grant all on *.* to remote@localhost identified by "king";
REVOKE   ALL   on   sailboy.*   FROM   reomote@’%’;   
  REVOKE   grant   option  
on   dbaaa.*   FROM  
aaa@localhost;   
若要给次用户赋予他相应对象上的权限的管理能力,可以grant后面添加with grant option选项
要是添加用户密码要使用函数PASSWORD
回收权限可以更改User,或REVOKE
全局管理权限：
FILE: 在MySQL服务器上读写文件。
PROCESS: 显示或杀死属于其它用户的服务线程。
RELOAD: 重载访问控制表，刷新日志等。
SHUTDOWN: 关闭MySQL服务。
数据库/数据表/数据列权限：
ALTER: 修改已存在的数据表(例如增加/删除列)和索引。
CREATE: 建立新的数据库或数据表。
DELETE: 删除表的记录。
DROP: 删除数据表或数据库。
INDEX: 建立或删除索引。
INSERT: 增加表的记录。
SELECT: 显示/搜索表的记录。
UPDATE: 修改表中已存在的记录。
特别的权限：
ALL: 允许做任何事(和root一样)。
USAGE: 只允许登录--其它什么也不允许做。
连接的mysql     mysql -h主机 -u用户名 -p密码
show tables;  查看有多少表
show databases;  /*可以加入注释*/  要以分号结束
     #  单行注释
use mysql;
show talbes;
describe/desc 表名；   显示数据表的结构
    desc user;  查看用户的详细信息
desc user;用户表结构
exit    退出Mysql
MySQL  DCL入门   数据控制语言
每一张表有三个文件
create database school;
drop database school;
写的更严禁   
drop database if exists school;  # 如果
建表
use school;
create table teacher #建立教师情况表Teacher
(
    Id int(5) auto increment not null primary key,
    name char(10) not null,
    address varchar(50) default ’武汉大学’,
    year date
)
删除表
drop table teacher;
select * from jokes;
select id,jokedate form jokes;
select id,left(joketext,20),jokedate from jokes;  看joketext的前20字符
select count(*) from jokes;统计有多少条记录
PhpMyAdmin的使用
Mysql字符串和数字
ANSI字符串因该为’’引号,""在mysql兼容
＼n表示换行符
select 岩石引号和转意
select (100+Array8+ArrayArray)/3;  当计算器
select ’hello’,’"hello"’,""hello"",’hel"lo’,’＼’hello’;
数字
有效的浮点的例子
-32023.680Arraye+10
select hex(’cat’);  
数字类型
准确类型 integer(int),smallint,tinyint和bigint    近似数字类型
商品价格 888.88 price decimal(5,2) 表示总长度：5，小数位：2
Mysql函数
比较运算符 = != ,>,>=,
is null,is not null,isnull(expr) 是不是空
expr between min and max   
如果expr大于或等于min,并且expr小于或等于      
   max,between返回1,否则返回0,它等价与表达式(min  
is null,is not null,isnull(expr)
expr between num and max
如果expr大于或等于min,并且expr小于或等于max,between返回1，否则返回0。它等价于表达式(min0以及expr1null),那么if()返回  
expr2,否则返回expr3.if()返回一个数字或字符串，这取决于  
             它被使用的语境；
select if(1>2,2,3);        3
select if(1,,>
-,+
,/div,%,mod,
^
not,!
Mysql字符串函数;
select ASCII(’2’);
select ASCII(2);
select bin(12);   //二进制转换
    ort(12);    八
    hex(12)    十六
char()     以整数类型即使参数，返回这个整数所代表的ascii码值给出的字符组成的字符串，    null值将被忽略.
        select char(77,121,83,81,’76’);  //翻译成ACCEII码   MySQL
length(str)    返回字符串str的的字节数。   char_length(str)
        select length(’test’);    4
concat(str1,str2...)  
将参数连成字符串返回。如果有任何一个参数为null,返回值为null.可以有超过2个的参数。数字参  
数将被转换为相等价的字符串形式。 里面有一个是空，返回为空
select concat(’my’,’s’,’ql’);      
mysql
locate(子串substr,母串str)  返回子串substr在字符串str中第一次出现的位置，如果找不到，返回为0
        select locate(’bar’,’foobarbar’);  4  第一次出现的位置
        select locate(’xbar’,’foobar’);    0
instr(str,substr)        返回子串str中第一次出现的位置，和locate()相似，参数颠倒
        select instr(’foobarbar’,’bar’);   4
lpad(str,len,padstr)    用字符串padstr对str进行左边填补直至它的长度到达len个字符长度，然后返回str.如果str的长度长于len,那么它将被截除len个字符
        select lpad(’hi’,4,’??’);         // ’??hi’
rpad(str,len,padstr)      
用字符串padstr对str进行右边填补直至它的长度到达len个字符长度，然后返回str.如果str的长度长于len,那么它将被截除
len个字符
        select rpad(’hi’,5,’?’);          // ’hi???’
left(str,len)        返回字符串 str中最左边的len个字符
        select right
SELECT语法
数据选择   select 列1，列2，...from 表1，表2，...
select joketext form jokes where joketext like "%鞋%";  %表示任意字符串
select joketext form jokes where joketext like "_鞋_";  _表示任意一个字符串
分组函数
    员工表 emp(empno,name,tel,deptno,sal)
    部门表 dept(depton,dame,addr)
显示每个部门的最高工资
    select depton.max(sal) for emp group bydeptno;
Mysql数据定义语言DDL
创建数据库格式:   建库
    create database [if not exists] 数据库名
    create database if not exists test;
    create database question;
删除数据库个是：drop database [if exists] 数据库名
Mysql的表 一个表实际上对应三个文件 xx.MYD xx.MYI xx.frm  
create table 表名(
    列1 类型[选项],   
    列2 类型[选项],
    ....
)
数据库数据目录 lib/mysql/    *.frm 表格式定义文件,*.myd数据文件，*.myi索引文件
create database school;
create talbe test(
    a int,
    b int
)
例子:
Mysql数据库的数据存放的主体是表(table)
建表:   
    create database school;
    use school;
    create table teacher #建立教师情况表
    (
        Id int(5) auto_increment not null primary key;
        name char(10) not null,
        address varchar(50) default ’武汉大学’,
        year date
    );
    auto_increment每个记录自动加一,primary key主字段
    drop table teacher; 删除表
    修改表结构:
        alert table 表1 rename 表2;   //更改表名称
        alert talbe t2 modify a tinyint not null,change b c char(20);
        //为了改变列a,从integer改为tinyint not null(名字一样)，并且改变列b,从char(10)改为char(20),同时重命名，从b改为c;
        alert table t2 add d timestamp; //新增一个新timestamp列，名为d;
        alert table t2 add c int auto_increment,add index(c);
   //增加一个新的auto_inerement整数列，命名为e;我们用索引了c,因为auto_increment列必须被索引
        alert table t2 drop columm c; //删除列c
desc table名字
Mysql视图
    create table t(qty int,price int);人缘
    insert into t values(3,50);
    create view v as select qty,price,qty,price,qty*price as values from t;
    select * from v;
Mysql索引
添加/删除匿名索引
在列d上增加一个索引，列b是唯一索引，并且使列a为主键：
alert table t2 add index(d),add unique index(b),add primary key(a);
alert table t2 drop index d,drop index b,drop primary key;
添加/删除索引
create index idx_t2 on t2(d);
drop index inx_t2 on t2;
create unique index idx2_t2 on t2(b);
drop index idx2_t2 on t2;
三层模式
二层模式C/S(B/S)
三层模式C/A/S(B/A/S)  客湖层、逻辑层，数据层
灵活性和可扩展性，安全性，软件重用性
Mysql存储过程
use test;
delimiter //
create procedure simpleproc (out param1 int)
begin
    select count(*) into parma1 from t;
end
//
delimiter ;
call simpleproc(@a);
Mysql用户使用命令
mysql的use的用法
show databases;
use mysql; 使用mysql表
use db1;
select count(*) from members;
select * from mysql.members;更详细
use db1;
select * from test.emp;  //察看其他数据库的表,用"."
Mysql desc句法 表的结构
desc 表名[列名]
describe 提供有关一个表的列信息.列名可以是一个列名或是一个包含sql通配符字符"%"和"_"的字符串。没有必要用引号包围字符串。"%"表示任意的字符，
desc members 显示表的结构
desc members ’e%’;  members以"e"开头的列
Mysql事物与所定
start/commit/rollback
commit(提交)就是将sql语句交给数据库系统执行并生效,数据更改于磁盘文件.
缺省的，mysql运行在autocommit模式,执行完就更新存储磁盘
如果使用事务安全表，通过下面的命令,可以设置mysql为非autocommit模式
set autocommit=0;
在此之后，你必须使用commit来存储你的更改到磁盘上,或使用rollback(回滚)，
set autocommit=0;
可以用 select @@autocommit;来显示
rollback可以反悔
事务是一个不可分割的数据操作单元，事物中的操作要么都发生，要么都不发生。
事务可以用start transaction定义;
start transaction;
select @x=sum(发的工资) from emp where  deptOno=10;
update set a =@x;
commit;
自动体交语句
有些语句是无法回滚的，因为他们会自动提交，如：DDL语句(create/drop database,create/drop/alter table)
下面是自动的
alter table         begin             create index;
drop database     drop database
load master data
例子
start transaction;
    ...
    ...
    ...
    if @accoutn  school_2006_Array.sql      //将数据库中所有的表备份到文件
mysqldump --opt school teacher student > school_2006_Array.sql    //将数据库中taacher和student表备份到文件
mysqldump --databases school test > schoool_test_2006.sql    //备份多个数据库
进到数据库用source 以前的xx.sql备份
当用PHP5以下版本的PHP来连接MySQL5.0的数据库的时候，会出现下面的错误提示：
Client does not support authentication protocol requested by server; consider upgrading MySQL client
经过我在网上的一番查找终于找到问题的原因了，因为MySQL5.0的用户密码安全协议和以前的版本的不同了，为了让老版本的PHP连接数据库需要将MySQL的用户密码更换成老版本的。可以在mysql的命令行中执行下面的语句：
SET PASSWORD FOR 用户名@主机名 = OLD_PASSWORD(’密码’);
Mysql数据库同步
master/slave数据同步配置
A服务器设置
GRANT FILE ON *.* TO backup@1Array2.168.1.3 IDENTIFIED BY ‘1234’;
上面是Master开放一个账号backup密码1234给IP:1Array2.168.1.3有档案处理的权限
备份Master所有数据库..通常都用tar命令
在A服务器上修改/etc/my.cnf
在[mysqld]区段内加入参数
log-bin
server-id=1
sql-bin-update-same
binlog-do-db=vbb
从起A服务器mysql
此时因为由加入log-bin参数,因此开始有index产生了,在/var/lib/mysql目录下有.index当案记录数据库的异动log.
B服务器设置
设定/etc/my.cnf
在[mysqld]区段加入
master-host=A主服务器IP地址
master-user=backup
master-password=1234
master-port=3306
server-id=2
master-connect-retry=60
replicate-do-db=vbb 告诉slave只做vbb数据库的更新
log-slave-updates
将A上的mysql.tar copy到B上
用ftp传B的/tmp
Array
Mysql的新功能
索引分单列索引和组合索引
单列索引： 即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引，组合所印即一个索引包含多个列
普通话索引  三种方法创建
    创建索引,例如 create index  ON tablename (列的列表);
    修改表,例如 alter table tablename ADD INDEX [索引的名字] (列的列表);
    创建表的时候指定索引 CREATE TABLE table ([...],INDEX [索引的名字] (列的列表));
唯一性索引   唯一的区别:索引列的所有值都只能出现一次,即必须唯一.唯一性索引可以用以下几种创建
    创建索引,  CREATE UNIQUE INDEX  ON tablename (列的列表);
    修改表     ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);
    创建表的时候指定索引  CREATE TABLE tablename ([...],UNIQUE [索引的名字] [列的列表]);
主键
Mysql全文检索
MySQL全文检索简介
MySQL
模糊查询;匹配程度
Mysql3.23后支持全文检索
全文索引在mysql中是一个fulltext类型索引。fulltest索引可以在create table时或之后使用alter table或create index在char,varchar或text列上创建。
对于大的数据库，将数据装载到一个没有fulltext索引的表中，然后再使用alter table(或create index)创建索引，这将是非常快的。将数据装载到一个已经有fulltext索引的表中，将是非常慢的。
全文搜索通过match()函数完成。
create table articles(            #创建一个带索引的表
    id int unsigned auto_increment not null primary key,
    title varchar(200),
    body text,
    fulltext(title,body)      #需要在title,body进行全文索引
);
insert into articles (title,body)
values           #加入数据
(’mysl tudsf’,’ater you wnt throut a ...’),
(’msql tuadsf’,’fter you went dhrght a ...’),
(’mysql tuadsf’,’afer you wet throuasdht a ...’),
(’ysql tuadsf’,’fter you ent thrsadught a ...’),
(’mysql tuadsf’,’aftr you ent throught a ...’),
(’mysql tuadsf’,’after you went throught a ...’),
(’mysql tuadsf’,’afte you ent throught a ...’),
(’mysl tuf’,’aferr weu nt throught a ...’),
(’mysql tuadsdsf’,’after you went tasdfrought a ...’),
(’msql tuadsf’,’sdfeer you went throught a ...’),
(’myql tuadsf’,’aferer you went throught a ...’),
(’myql tuadsf’,’afsdfter you went throught a ...’),
(’myasdfql tudsfdsf’,’aftersdfou went throught a ...’),
模糊搜索
select * from articles where match(title,body) against(’mysql’);
函数match()对照一个文本集(包含在一个fulltext索引中的一个或多个列的列集)执行自然语言搜索一个字符串。搜索字符串做为against
()的参数被给定。搜索以忽略字母大小写的方式执行。对于表中每个记录行，match()返回一个相关性值。即，在搜索字符串与记录行在match()列
表中指定的列的文本之间的相似性尺度。
当match()被使用在一个where子句中,返回的记录行被自动地以相关性从高度的次序排序。相关性值是非负的浮点数字
。零相关性意味着不相似。相关性的计算是基于：词在记录行的数目，在行中唯一的数目、在集中词的全部树木和包含一个特殊词的文档（记录行）的数目。
前面的例子是函数match()使用的一写基本说明。记录行以相似性递减的顺序返回。
administrator
select