linux基础知识–服务配置–windows ISA Exchange服务配置

12_01_存储过程函数的应用

1、存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的性能的效率有好处；
2、存储过程与函数的区别在函数必须有返回值，而存储过程的参数可以使用in out inout类型，而函数的参数只能是in类型的。

mysql> ? create procedure;
mysql> delimiter // 将mysql的”;”结束符变为”//”作为结束符
mysql> create procedure pro_emp(out empcount int) 
begin
 select count(*) into empcount from emp;
end//
mysql> call pro_emp(@a)//

mysql> select @a//
+——+
| @a   |
+——+
|    4 |
+——+

mysql> create function constr1(str char(50)) returns char(50) return concat(‘hi’, str, ‘!’); 在mysql5.5上用此方法在服务器上无法创建函数.

继续阅读 12 存储过程、游标、函数的定义使用

11_01_视图的创建和不可更新性

1、视图是一种虚拟存在的表，对于使用视图的用户来说基本上是透明的。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且在使用试图时是动态生成的。
2、试图相对于普通的表的优势：简单（用户不需要关心后面对应表结构、关联、筛选条件），安全，数据独立。

创建修改试图的语法：
create or replace [algorithm={undefind | merge | temptable}] view view_name[(column_list)] as select_statement [with[cascaded |local] check option]

mysql> insert into video select 0,’zhang’,’200′;
mysql> insert into video select 0,’wang’,’1200′;
mysql> insert into video select 0,’li’,’100′;
mysql> insert into video select 0,’zhao’,’1100′;
mysql> create or replace view video_view as select * from video where price between 1000 and 2000;创建视图

mysql> select * from video_view;
+—-+——+———+
| id | name | price   |
+—-+——+———+
|  4 | zhao | 1100.00 |
|  2 | wang | 1200.00 |
+—-+——+———+
mysql> create or replace view video_view2 as select * from video_view; 在视图上再创建试图。

试图定义服从下述限制：
1、select语句不能包含from子句中的查询；
2、select语句不能引用系统或用户变量；
3、select语句不能引用预处理语句参数；
4、在存储子程序内，定义不能引用子程序参数或局部变量。
试图的不可更新性：使用下列的方法，视图更新后，基表不随之改变
聚合函数 sum(),min(),max(),count()
 distinct
 group by
 having
 union或union all
 位于选择列表中的字查询
 join
 from子句中的不可更新试图
 where子句中的子查询，引用from子句中的表

例如：
mysql> create view vcount as select count(id) from video;
mysql> delete from vcount;
ERROR 1288 (HY000): The target table vcount of the DELETE is not updatable 更新视图是不可以的

mysql> create view vpi as select 3.1415926;
mysql> insert into vpi select 2.1415926;
ERROR 1471 (HY000): The target table vpi of the INSERT is not insertable-into 不可以更新试图
mysql> create or replace view view1 as select(select id from video where id=2);

mysql> delete from view1;
ERROR 1288 (HY000): The target table view1 of the DELETE is not updatable 视图不可更新
11_02视图的With.Check.Option

WITH CHECK OPTION
1、WITH[CASCADE|LOCAL] CHECK OPTION决定了是否允许更新数据使记录不再满足视图条件;

2、Local只满足本视图的条件就更新；

3、CASCADED则是必须满足所有针对该视图的所有视图的条件才可以更新 。
mysql> create table t1(a int);
mysql> create view v1 as select * from t1 where a <2 with check option;
mysql> create view v2 as select * from v1 where a>0 with local  check option;
mysql> create view v3 as select * from v1 where a > 0 with cascaded check option;
mysql> insert into v1 select 2; 失败
ERROR 1369 (HY000): CHECK OPTION failed ‘test.v1′
mysql> insert into v2 select 4; 成功
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into v3 select 3; 失败
ERROR 1369 (HY000): CHECK OPTION failed ‘test.v3′

删除、查看view表
DROP view view_name;
查看视图：
show table status like ‘pattern’;
show table create view view_name \G
select * from views where table_name=’view_name’ \G

mysql> desc information_shema.views;
mysql> select table_name,check_option from information_schema.views where table_name=’v1′;

mysql> select table_name,check_option from information_schema.views where table_name=’v2′;

mysql官方下载地址
http://dev.mysql.com/downloads/

mysql5.5源码安装需要cmake，下面是官方下载地址：
http://www.cmake.org/cmake/resources/software.html
下载最新版本cmake-2.8.7-Linux-i386.tar.gz,解压缩后,bin目录的文件可以直接使用.
mysql5.5安装步骤官方帮助：
http://dev.mysql.com/doc/refman/5.5/en/installing-source-distribution.html

以下是源码安装mysql5.5的步骤：
[root@localhost pub]# groupadd mysql
[root@localhost pub]# useradd -g mysql mysql
[root@localhost pub]# rpm -ivh MySQL-5.5.19-1.rhel5.src.rpm
error: cannot create %sourcedir /usr/src/redhat/SOURCES
[root@localhost pub]# cd /usr/src/
[root@localhost src]# ls
[root@localhost src]# mkdir redhat
[root@localhost src]# rpm -ivh /var/ftp/pub/MySQL-5.5.19-1.rhel5.src.rpm
   1:MySQL                  warning: user pb2user does not exist – using root
########################################### [100%]
warning: user pb2user does not exist – using root
[root@localhost redhat]# cd SOURCES/
[root@localhost SOURCES]# ls
mysql-5.5.19.tar.gz
[root@localhost SOURCES]# tar zxf  mysql-5.5.19.tar.gz
[root@localhost SOURCES]# ll
total 23760
drwxr-xr-x 31 7161 wheel     4096 Nov 23 07:46 mysql-5.5.19
-rw-r–r–  1 root wheel 24296942 Nov 23 10:34 mysql-5.5.19.tar.gz
[root@localhost SOURCES]# cd mysql-5.5.19

[root@localhost mysql-5.5.19]# /var/ftp/pub/cmake-2.8.7-Linux-i386/bin/cmake .
使用cmake时，可能出现一下错误，按照错误提示安装需要的一些软件：
[root@localhost mysql-5.5.19]# yum -y install ncurses-devel
[root@localhost mysql-5.5.19]# yum -y install gcc-c++
直接使用解压缩后的cmake，如果其他必需软件没有安装，每执行一次此命令，还需要删除以下CMakeCache.txt，
[root@localhost mysql-5.5.19]# rm -f CMakeCache.txt
然后执行以下命令：
[root@localhost mysql-5.5.19]# make
[root@localhost mysql-5.5.19]# make install
[root@localhost mysql]# pwd
/usr/local/mysql
[root@localhost mysql]# chown -R mysql.mysql .
[root@localhost mysql]# scripts/mysql_install_db –user=mysql
以上设置完成，下面的操作可以不用：
[root@localhost mysql]# cp support-files/my-medium.cnf /etc/my.cnf

启动mysql
[root@localhost mysql]# bin/mysqld_safe –user=mysql &
将家目录下的.bash_profile添加mysql可执行文件的目录
PATH=$PATH:/usr/local/mysql/bin

以下第一条是将数据库创建语句导出，第二条是将数据库的数据导出！
[root@localhost home]# mysqldump -uroot -p -d test>/home/test.sql
[root@localhost home]# mysqldump -uroot -p test > /home/testdata.sql

索引概述：
1、索引是数据库中用来提高性能的最常用的工具。所有Mysql列类型都可以被索引，对相关列使用索引是提高查询操作性能的最佳途径。
2、根据存储引擎可以定义每个表的最大索引数和最大索引长度，每种存储引擎对每个表至少支持16个索引；
3、MyISAM和InnoDB存储引擎的表默认创建的都是BTREE索引；
4、MySQL中还支持全文索引，该索引用于全文搜索。5.0版本中只有MyISAAM支持全文索引，并只限于CHAR、VARCHAR和TEXT列；
创建索引：
mysql> use test;
mysql> desc employee;
+——–+———————-+——+—–+———+—————-+
| Field  | Type                 | Null | Key | Default | Extra          |
+——–+———————-+——+—–+———+—————-+
| id     | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| name   | varchar(50)          | YES  |     | NULL    |                |
| deptno | smallint(5) unsigned | YES  | MUL | NULL    |                |
+——–+———————-+——+—–+———+—————-+
mysql> create index ind_emp_name on employee(name);创建索引

创建前缀索引：
使用前5个作为索引，即为前缀索引
mysql> create index ind_emp_name1 on employee(name(5));

查询索引引用情况：
mysql> explain select * from employee where name=’zhang%’ \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employee
         type: ref
possible_keys: ind_emp_name,ind_emp_name1
          key: ind_emp_name 此处为索引
      key_len: 153
          ref: const
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

继续阅读 10_01_索引的使用

09_1_常用字符集介绍

字符集概述：在计算机中，只有二进制的数据，不管数据时在内存中，还是在外部存储设备中。对于我们所看到的字符，也是以二进制数据的形式存在的。不同字符对应二进制的规则，就是字符的编码。字符编码的集合称为字符集。

2、在早期的计算机系统中，使用的字符非常少，这些字符包括26个英文字母、数字符号和一些常用符号（包括控制字符），对这些字符进行编码，用一个字节就足够了（1个字节可以表示256个字符）。然而实际中，表示这些字符只使用了1个字节的7位，这就是ASCII编码，是基于英文字符的单字节编码系统；
3、一位ASCII编码中缺少许多和书写需要的字符，为此可以通过指定128以后的字符来扩展ASCII码。国际标准组织ISO定义了几个不同的字符集，他们是在ASCII码基础上增加了其他语言和地区需要的字符。其总常用的是ISO8859-1，通常叫做Latin-1，它包括了西方噢周语言不可缺少的附加字符，其中0-127的字符与ASCII相同。

继续阅读 9 字符集的介绍和使用

08_2_合成索引与前缀索引

mysql> create table t(id int, content blob, hash_value varchar(40));

mysql> insert into t select 1,repeat(‘pcedu’,100),md5(repeat(‘pcedu’,100));

对大的文本字段进行查询的时候要建立前缀索引,来提高查询效率.例如对content建立前缀索引:
mysql> create index index_content on t(content(10));
mysql> select * from t where content like ‘pcedu%’;

建立前缀索引后效率会提高.

继续阅读 8_2 前缀索引、定点数和浮点数的应用

1、选择合适的数据类型
在使用Mysql常见数据库表时，会遇到如何为字段选择合适数据类型的问题。例如：一张员工表用来记录员工的信息。对员工字段应该如何定义，如何挑选合适的数据类型是我们要考虑的问题。
下面学习字符、数值、日期数据类型的选择原则。
数据的类型，一定要掌握使用场合和存储需求。

CHAR 与 VARCHAR
1、都是用来存储字符串，但他们保存和检索的方式不同。
1-1）、CHAR属于固定长度的字符类型，所以处理速度要快。其缺点是浪费存储空间，需要程序对行尾空格处理。
1-2）、VARCHAR属于可变长度的字符类型。
2、两种类型在不同存储引擎上使用原则也不同：
2-1MYISAM存储引擎：建议使用固定长度的数据列
2-2MEMORY存储引擎：两者都作为CHAR类型处理。要考虑性能与速度
2-3INNODB存储引擎：建议使用VARCHAR类型。事务性，对事务要求准确性高

继续阅读 08_1 解决大文本数据空洞问题

1、Merge存储引擎是一组MyISAM表的组合,这些MyISAM标必须结果完全一样,MERGE表本身没有数据,对MERG表可以查询,更新,删除的操作,实际上是对MyISAM标的操作。
2、对于MERGE类型表的插入操作，是通过INSERT_METHOD子句定于插入的表，有三个不同的值：
（1）FIRST使插入操作作用在第一个表上
（2）LAST是插入操作作用在最后一个表上
（3）NO对MERGE不执行插入操作
3、MERGE在磁盘上有两个文件：frm文件和MRG包含组合表信息。

mysql> create table video1(id smallint unsigned auto_increment, name varchar(50),primary key(id)) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> create table video2(id smallint unsigned auto_increment, name varchar(50),primary key(id)) engine=myisam;
Query OK, 0 rows affected (0.00 sec)
mysql> create table allvideo(id smallint unsigned auto_increment, name varchar(50),primary key(id)) engine=merge union(video1,video2) insert_method=last;
建立联合
mysql> insert into video1 select 0,’java’;
mysql> insert into video2 select 1,’C++’;
mysql> select * from allvideo;
+—-+——+
| id | name |
+—-+——+
|  1 | java |
|  1 | C++  |
+—-+——+

继续阅读 07_5Merge存储引擎的使用和各种引擎的使用场合

1、InnoDB提供了具有提交，回滚和崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一点，并且会占用更多的磁盘空间以保留数据和索引
2、InnoDB不同于其他存储引擎的特点：
（1）自动增长列：
（1）、自动增长列必须是索引，如果是组合索引也必须是第一列
（2）、可以手工插入值
（3）、但插入的值为空或0，则实际插入的是自动增长后的值
（4）、可以通过alter table auto_table auto_increment=10；修改初值
（5）、可以使用last_insert_id()查询当前线程最后插入的记录使用的值
（2）外键约束 继续阅读 07_3 InnoDB存储引擎的使用

1、MyISAM是Mysql的默认存储引擎。不支持事务也不支持外键，其他优势就是访问速度快
2、对事务完整性没有要求，以查询、添加为主的应用基本上都可以选择使用这个引擎来创建表
3、每个MyISAM在磁盘上存储成3个文件，其文件名和表名相同。扩展名分别是：frm（存储表定义），MYD（MYData，存储数据），MYI（MYIndex，存储索引）。
4、数据文件和索引文件可以放置在不同的目录，平均分布IO，获得更可得速度。

[root@localhost test]# pwd
/var/lib/mysql/test
[root@localhost test]# ll
total 48
-rw-rw—- 1 mysql mysql 8614 Dec 20 05:09 employee.frm
-rw-rw—- 1 mysql mysql    0 Dec 20 05:09 employee.MYD
-rw-rw—- 1 mysql mysql 1024 Dec 20 05:09 employee.MYI
-rw-rw—- 1 mysql mysql 8592 Dec 25 02:45 sal.frm表定义文件
-rw-rw—- 1 mysql mysql   48 Dec 25 02:46 sal.MYD数据存储文件
-rw-rw—- 1 mysql mysql 1024 Dec 25 04:05 sal.MYI索引存储文件
-rw-rw—- 1 mysql mysql 8586 Dec 26 02:24 tab.frm只有表定义文件，还没有存储数据，也没有建立索引

5、在日常使用中可能会遇到MyISAM存储引擎的表损坏。一张损坏的表症状通常是查询意外中断，并看到如下错误：
（1）‘table_name.frm’被锁定不能更改
（2）不能找到文件’table_name.MYI’
（3）文件意外结束
（4）记录文件被损坏
（5）从表处理器得到错误nnn.

解决方法：
一、使用Mysql自带的myisamchk工具进行修复命令如下：
myisamchk -r tablename; // -r 代表 recover
或者
myisamchk -o tablename; // -o代表saft-recover

二、指用check table 和 repair table命令进行修复：
check table table_name[,table_name] …[option]
 option={QUICK|FAST|MEDIUM|EXTENDED|CHANGED}
REPAIR [LOCAL|NO_WRITE_TO_BINLOG] TABLE TABLE_NAME[,TABLE_NAME...] [QUICK] [EXTENDED] [USE_FRM]

check table 支队MyISAM和InnoDB有效

mysql> check table employee;
+—————+——-+———-+———-+
| Table         | Op    | Msg_type | Msg_text |
+—————+——-+———-+———-+
| test.employee | check | status   | OK       |
+—————+——-+———-+———-+
mysql> repair table employee;
+—————+——–+———-+———-+
| Table         | Op     | Msg_type | Msg_text |
+—————+——–+———-+———-+
| test.employee | repair | status   | OK       |
+—————+——–+———-+———-+

7存储引擎管理
表类型(存储引擎)的选择
mysql的存储引擎有:MyISAM,InnoDB,MEMORY,MERGE
插件式存储引擎是Mysql数据库最重要的特性之一，用户可以根据应用的需要选择如何存储和索引数据，是否使用事务等。Mysql默认支持多种存储引擎，以适用于不同领域的数据库应用需要。
Mysql5.0支持的存储引擎包括MyISAM，InnoDB，BDB，MEMORY，MERGE,EXAMPLE,NDB Cluster,ARCHIVE,CSV,BLACKHOLE,FEDERATED,FEDERATED等，其中 InnoDB和DBD提供事务安全表，其他都为非事务安全。

分析器和优化器好不好，事务型的，非事务性的。

关于Mysql存储引擎的概述在Mysql手册的第14章。

只有InnoDB支持外键，其他引擎都不支持外键。

mysql> show variables like ‘table_type’;
+—————+——–+
| Variable_name | Value  |
+—————+——–+
| table_type    | MyISAM |
+—————+——–+ 继续阅读 7存储引擎管理

数值函数：ABS(x) 返回x的绝对值
CEIL(x) 返回大于x的最小整数值
FLOOR(x) 返回小于x的最大整数值
MOD(x,y) 返回x/y的值
RAND() 返回0-1的随机值
ROUND(x,y) 返回参数x的四舍五入的有y位小数的值
TRUNCATE(x,y) 返回数字x截断为y位小数的结果 继续阅读 5 日期函数与数值函数的应用

1.算术运算符:+，-，*，/，%，mod
select 0.1+0.22,0.1-0.22,0.1*0.22,0.1/0.22,0.1 mod 0.22
2.比较运算符:=，!=或<>,<=>null的安全的等于，<,<=,>,>=,between,in,is null,is not null ,Like
mysql> select 1<=>1,1<=>0,null<=>null;
+——-+——-+————-+
| 1<=>1 | 1<=>0 | null<=>null |
+——-+——-+————-+
|     1 |     0 |           1 |
+——-+——-+————-+
mysql> select 123456 like ’123%’,123456 like ‘%234%’;
+——————–+———————+
| 123456 like ’123%’ | 123456 like ‘%234%’ |
+——————–+———————+
|                  1 |                   1 |
+——————–+———————+ 继续阅读 4MySQL运算符

今天得到一个txt格式的数据库文件,一直在想怎么导入到mysql数据库中，查了一些资料，mysql手册，终于找到方法了

mysql> LOAD DATA INFILE ‘data.txt’ INTO TABLE db2.my_table;

data.txt文件要在mysql的数据库目录下，比如/var/lib/mysql/bbk，将data.txt导入到bbk数据库中的user table：

mysql> create table user(username varchar(50), password varchar(20));

mysql> load data infile ‘/var/lib/mysql/bbk/data.txt’ into table bbk.user;

在这里我使用了绝对路径，虽然mysql默认使用相对路径，mysql会到默认的/var/lib/mysql/bbk目录下查找data.txt数据文本文件，但是总是报错，所以我直接使用了绝对路径，就不报错了。