=========================================安装mysql=========================================
从http://download.freelamp.com/LAMP/mysql-5.0.27.tar.gz下载(因为官方网站只有最新不稳定版的源码)
cd /usr/local/mysql
tar zxvf mysql-5.0.27.tar.gz
cd mysql-5.0.27
./configure -prefix=/usr/local/mysql -with-charset=utf8 -with-extra-charsets=all #这样就使character_set_database为utf8了
make               #mysql编译时间非常长
make install
cp support-files/my-medium.cnf /etc/my.cnf
cp support-files/mysql.server /etc/rc.d/init.d/mysql.server
cd ..
rm mysql-5.0.27.tar.gz
groupadd mysql
useradd -g mysql mysql
chown -R root /usr/local/mysql
chgrp -R mysql /usr/local/mysql
chown -R mysql /usr/local/mysql/var
/usr/local/mysql/bin/mysql_install_db --user=mysql &
/usr/local/mysql/bin/mysqld_safe --user=mysql #启动mysql
/usr/local/mysql/bin/mysqladmin -uroot password xxxxx //为root访问设置密码
/etc/rc.d/init.d/mysql.server stop #停止mysql,有时需要ps -e | grep mysqld查看id, 再kill该id
vi /etc/rc.local
添加/usr/local/mysql/bin/safe_mysqld --user=mysql & #让mysql随系统启动
/usr/local/mysql/bin/mysql -u root -p密码
show databases;
如果有多余的数据库, 用drop database database_name;
安装web程序时需要新建数据库:
create database db_app;    #分号不能掉了
grant all privileges on db_app.* to 用户名@localhost identified by '密码';
=========================================配置mysql=========================================
/etc/my.cnf的[mysqld]下加上default-character-set=utf8 #控制系统变量character_set_system和character_set_server
max_allowed_packet缺省为10M, 但是如果要往数据库里插入较长的文章可以加大到100M
=============================================================
"set names"命令控制mysql系统变量:character_set_client,character_set_connection,character_set_results
=============================================================
mysql4.0傻到只能在c区安装，在其它分区安装就启动不了
卸载mysql前最好关闭mysql服务，否则可能因占用服务名而导致重装失败
=============================================================
命令行安装和启动mysql:
mysqld-nt.exe --install mysql41 --defaults-file=my.ini
net start mysql41
=============================================================
装好mysql和mysqlcc后用mysql文件夹内的winmysqladmin启动mysql,然后用mysqlcc直接以用户名root,
密码空登录,进去后就可以修改登录用户名和密码了,再创建数据库和它的数据表
=============================================================
mysql数据库的所有字段都自动有默认值，比如字串的默认值是空串，数量的默认值是0，
使用sql语句往数据库添加数据时，未指定的字段都自动填充默认值
=============================================================
配置odbc连接数据库时，“服务器”一项应该用域名，而不用ip地址
========================================================
UNIX_TIMESTAMP(tb3.time_end)-UNIX_TIMESTAMP(tb3.time_start)计算时间差
UNIX_TIMESTAMP(tb3.time_end)-(UNIX_TIMESTAMP(NOW())
(版本4.1.1以上)DATEDIFF('1997-11-30 23:59:59','1997-12-31')=='-31'
计算月差不应该用DATEDIFF,而要用PERIOD_DIFF(并不要求版本4.1.1以上)
========================================================
UNIX_TIMESTAMP()为当前日期时间秒数, CURRENT_TIMESTAMP()为当前日期时间字串
'1997-12-31 18:19:50'==FROM_UNIXTIME(UNIX_TIMESTAMP('1997-12-31 10:19:50')+60*60*8)=='1997-12-31 10:19:50'+INTERVAL 60*60*8 SECOND
=='1997-12-31 10:19:50'+INTERVAL 8 HOUR=='1997-12-31 10:19:50'+INTERVAL '7:60' HOUR_MINUTE
DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')==FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y-%m-%d %H:%i:%s')
FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y-%m-%d %H:%i:%s')==CURRENT_TIMESTAMP()==NOW()=='YYYY-MM-DD HH:NN:SS'
注意:"INTERVAL n MONTH"是先加减月数, 然后再看日数是否合法, 不合法转成该月最大合法值
========================================================
日期字段查询效率优化:
可以优化的:
sql:                                                php:
select UNIX_TIMESTAMP(field)                        strtotime($field)
select DATE_FORMAT(field),MONTH(field),...          substr($field)
select FROM_UNIXTIME(field)                         date($field)
where UNIX_TIMESTAMP(field)>$item                   field>date($item)       #12万条数据(已建索引), 前者耗时600毫秒, 后者耗时300毫秒
where DATE_FORMAT(field, '%Y-%m-%d')=$item          field>=$item.' 00:00:00' and field<=$item.' 23:59:59'
where FROM_UNIXTIME(field, '%Y-%m-%d')=$item        field>=strtotime($item) and field<=strtotime($item.' 23:59:59') #150万条数据(已建索引), 前者耗时3.18秒, 后者耗时0.0003秒(没加索引时耗时0.3秒), 相差10000倍
where field>UNIX_TIMESTAMP($item)                   field>strtotime($item)
无法优化的:
group by UNIX_TIMESTAMP(field)或group by DATE_FORMAT(field)或group by FROM_UNIXTIME(field)
而在order by后则可以剥掉这些函数而不影响结果
一个日期存储技巧是存为形如'20071021'的整数, 既保持在phpmyadmin中查看的直观性, 又具备快速索引能力
========================================================
ELT(N,str1,str2,str3,...)该函数在数据统计分组时有用
如果N= 1，返回str1，如果N= 2，返回str2，等等。如果N小于1或大于参数个数，返回NULL。ELT()是FIELD()补运算
FIELD(str,str1,str2,str3,...)返回N
select ELT(FIND_IN_SET('b','a,b,c,d'), 'ej', 'Heja', 'hej', 'foo');得到'Heja'
这样就将'a,b,c,d'和'ej,Heja,hej,foo'对应了起来
还有CASE WHEN x>0 THEN "x>0" WHEN x>1 THEN "x>1" ELSE "false" END
或CASE x WHEN 0 THEN "x=0" WHEN 1 THEN "x=1" ELSE "false" END
简单判断用IF(x>0, "x>0", "x<=0")
还有INTERVAL(N,N1,N2,N3,...)
如果N<N1，返回0，如果N<N2，返回1等等，为了函数能正确地工作，要求N1<N2<N3< ...<Nn
=============================================================
SELECT语法：

SELECT [DISTINCT]
(column [{, column } ] )| *
FROM table [ { , table} ]
[ORDER BY column [ASC] | [DESC
[{ , column [ASC] | [DESC } ] ]
WHERE predicate [ { logical-connector predicate } ];

mysql:
SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [HIGH_PRIORITY]
    [DISTINCT | DISTINCTROW | ALL]
 select_expression,...
 [INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
 [FROM table_references
     [WHERE where_definition]
     [GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC], ...]
     [HAVING where_definition]
     [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
     [LIMIT [offset,] rows]
     [PROCEDURE procedure_name]
     [FOR UPDATE | LOCK IN SHARE MODE]]

mssql:
SELECT [DISTINCT] [TOP N [PERCENT]] column1 [AS column_heading]
       [, column2 [AS column_heading], ...]
 [INTO new_table_name]
 FROM table1 [ [AS] table_alias ]
 [ [INNER | { LEFT | RIGHT | FULL} [OUTER] ] JOIN table2 [ [AS] table_alias2 ]
       on ( join_conditions )] [...]
 [WHERE search_conditions]
 [GROUP BY aggregate_free_expression]
 [HAVING search_condition]
 [ORDER BY order_expression [ASC | DESC]]
 [ compute row_aggregate(column_name) [, ... ]
      [ by column_name [ , column_name ] ... ] ]
------------------------------------------------------

INSERT语法：

INSERT INTO table
[(column { ,column})]
valueS
(columnvalue [{,columnvalue}]);
------------------------------------------------------

UPDATE语法：

UPDATE table
SET column = value [{, column = value}]
[ WHERE predicate [ { logical-connector predicate}]];
------------------------------------------------------

DELETE语法：

DELETE FROM table
[WHERE predicate [ { logical-connector predicate} ] ];
------------------------------------------------------
--SJM_MeetingroomChargeList查看
  procedure prc_SJM_MRChargeListView(iID number,iMRChargeID number,oRS out rs) is
  begin
    open oRS for
    select id,itemcontent,itemamount,fee,notes
    from sjm_meetingroomchargelist a
    where meetingroomchargeid=iMRChargeID and iid is null and iMRChargeID is not null
       or id=iid and iid is not null and iMRChargeID is null
       or iid is null and iMRChargeID is null;
  end;
------------------------------------------------------
--SJM_MeetingroomChargeList编辑
  procedure prc_SJM_MRChargeListEdit(iID number,iMRChargeID number,iItemContent varchar2,iItemAmount number,iFee number,iNotes varchar2) is
  begin
  if iid is null then
    insert into sjm_meetingroomchargelist
    (id,meetingroomchargeid,itemcontent,itemamount,fee,notes)
    values
    (seq_sjm_chargelist.nextval,iMRChargeID,iItemContent,iItemAmount,ifee,inotes);
  else
    update sjm_meetingroomchargelist
    set meetingroomchargeid=imrchargeid,
        itemcontent=iitemcontent,
        itemamount=iitemamount,
        fee=ifee,
        notes=inotes
    where id=iid;
  end if;
  end;
------------------------------------------------------
--SJM_MeetingroomChargeList删除
  procedure prc_SJM_MRChargeListDel(iID number,iMRChargeID number) is
  begin
    delete from sjm_meetingroomchargelist
    where meetingroomchargeid=iMRChargeID and iid is null and iMRChargeID is not null
       or id=iid and iid is not null and iMRChargeID is null
       or iid is null and iMRChargeID is null;
  end;
============================================================================
SELECT * FROM d LEFT JOIN
        (SELECT * FROM a, b, c WHERE ...) AS ss      --select出来的结果作为一个表
        ON ...;
============================================================================
SELECT * FROM (a JOIN b) JOIN c WHERE a.id=b.id AND b.ref=c.id;
结果同于
SELECT * FROM a,b,c WHERE a.id=b.id AND b.ref=c.id;
但都没有以下快：
SELECT * FROM a JOIN (b JOIN c ON b.ref=c.id) ON a.id=b.id;       --似join出来的结果作为一个表,但并未另名
===============================================================================
mysql4.0.22中
SELECT * FROM a
              LEFT JOIN (b
                         LEFT JOIN c
                                ON b.ref=c.id
                        )
                     ON a.id=b.id;
被解析作了
SELECT * FROM a,b
              LEFT JOIN c
                     ON b.ref=c.id and a.id=b.id;
这就完全不符合本意了,只好写成
SELECT * FROM a
              LEFT JOIN b ON a.id=b.id
                        LEFT JOIN c ON b.ref=c.id
===============================================================================
mysql下'join'=='cross join', ','=='inner join ... on 1','left join'=='left outer join'
===============================================================================
使用group by时如果其它字段没有使用max(min)或sum等统计函数,则也只会传回该group第一行数据的对应字段的值,
因为不能在group by前order by, 比如选出合同审核历史表里每个合同的最后一次审核状态:
select id, verify_step, contract_id
from history_verify
where id in (select max(id)
             from history_verify
             group by contract_id
            )
不用子查询的做法:
create temporary table tmp_ids
select max(id) as id
from history_verify
group by contract_id;
select verify_step, contract_id
from history_verify
     left join tmp_ids.id=history_verify.id
如果是取第一次审核状态倒简单:
select id, verify_step, contract_id
from history_verify
group by contract_id
如果不是group而只取出一条则可:
select id, verify_step, contract_id
from history_verify
order by id desc
limit 1
===============================================================================
常用group函数:
COUNT(), COUNT(DISTINCT ...), AVG(), STD(), SUM(), MAX(), MIN(), and (>mysql4.1)GROUP_CONCAT()
==================================================================================
COUNT(*)对所有行进行计数, 而COUNT(col_name)只对非NULL值的行进行计数
==================================================================================
要计算同一用户对单一产品的订单数(即对同一产品的多次订单算一单):COUNT(DISTINCT user_id, product_id),
没有必要COUNT(DISTINCT concat(user_id, product_id)), 反而有错比如mysql 5.0.16-max会得到偏大的错误结果
====================================================================================
这样配合比用count(*)或php的mysql_num_rows()函数节约1/3的查询时间:
select sql_calc_found_rows id, name from tb1 where id>1000 limit 0,10;
select found_rows();
如果需要知道整张表的行数, 可以用show table status like 'tb1'来得到rows, 但是近似行数
====================================================================================
select * from
tb1
left join tb2
on tb2.qty>300 and tb2.id_tb1=tb1.id and tb1.qty<200
即left join的实质就是
"tb2中每行数据都往tb1中每行数据映射一次(可能复映射),符合条件的映射添加到结果集,
如果tb1中的一行数据没有任何符合条件的映射,就将一个对该行数据的空映射添加到结果集"
空映射保证了主表数据的全举性,如果再配合"group by主表主键"就能保证主表数据的唯一性
多重left join就是多次对left join的结果再left join
有时候需要将left join主从表的次序反过来, 但是又不想输出空行, 可以加条件where <原主表>.id is not null
====================================================================================
多库联查:
select *
from db1.tb1
     left join db2.tb1 on db1.tb1.id=db2.tb1.id
===================================================================================
inner join就是left join去掉空映射
===================================================================================
HAVING相当于GROUP BY下的WHERE，有时我们需要在group sum以后再过滤数据，比如
SELECT tb.num,tb.min_store,sum(tb1.qty)
FROM tb left join tb1 ...
GROUP BY tb.num
HAVING tb.min_store>sum(tb1.qty)
==========================================================================
建表sql脚本:
--客户
DROP TABLE IF EXISTS tb_customer;
CREATE TABLE tb_customer
(
  PRIMARY KEY (num),
  num varchar(50) default '' NOT NULL,
  name varchar(100) default '' NOT NULL,
  type_cd tinyint(4) default '0',
  country_cd varchar(50) default '',
  addr varchar(255) default '',
  zip varchar(32) default '',
  ph varchar(50) default '',
  fax varchar(50) default '',
  website varchar(100) default '',
  email varchar(100) default '',
  bank_name varchar(50) default '',
  bank_acnt_num varchar(255) default '',
  tax_num varchar(50) default '',
  tax_rate decimal(22,7) default '0.0000000',
  currency varchar(50) default '',
  `desc` text
)
COMMENT='客户';
----------------------------------------------
建表脚本范式：
--
DROP TABLE IF EXISTS ;
CREATE TABLE
(
  PRIMARY KEY (),

)
COMMENT='';
------------------------------------------------
varchar() default '',
tinyint(4) default '0',
decimal(22,7) default '0.0000000',
datetime default NULL,
int(11) default '0',
text,

NOT NULL
===============================================
sql字串处理(asp):
Function SqlStr(data)
SqlStr=Replace(data,"'","''")
End Function

conn.Execute("insert into columns(column,title,content) values('"&SqlStr(Request("area"))&"','"&SqlStr(Request.Form("title"))&"','"&SqlStr(Request.Form("content"))&"')")
================================================================
mysql中一次插入多行：insert into `tb_busi_scope` (`id',`name`)
                                         values  ('B01','化工'),
                                                 ('B31','运输'),
                                                 ('B42','金属/电器/模具');
mysql中比标准sql多replace:
(插入或更新,但更新条件不可控,实际上是删除所有符合primary或unique索引字段的行, 然后再插入新行,未提到的字段被改成了空,也不能用字段名来表示旧值)
replace into `tb_busi_scope` (`id`,`name`) values ('33','化工'),('34','运输'),('35','金属/电器/模具');
如果需要保持其它非unique索引字段的值, 可以:
insert into `tb_busi_scope` set `id`='33',`name`='化工' on duplicate key update `id`='33',`name`='化工';
insert into `tb_busi_scope` set `id`='34',`name`='运输' on duplicate key update `id`='34',`name`='运输';
insert into `tb_busi_scope` set `id`='35',`name`='金属/电器/模具' on duplicate key update `id`='35',`name`='金属/电器/模具';
如果要基于原有字段更新:
这样写无效(改用set方式也不行):
replace into turn_score (user_name,score)
                 values ('test',score+1)
这样写才有效:
replace into turn_score
select 'test',1
union
select user_name,score+1
from turn_score
where user_name='test'
================================================================
mysql4.1.1以前不能在update里用子查询根据tb1.fd1更新tb2.fd1(mysql4.1.1以上须不同表),可以这样:
replace into product_info
select product_info.`area_id`,product_info.`product_id`,tb.product_cent,product_info.`product_updown`
from product_info
     left join product_info tb on tb.product_id='429'
where product_info.product_id='1';
注意必须列出所有字段(在google里用"select all fields except"搜索不到选多排少的方法)
<<千万注意>>字段顺序要保持(tb.product_cent放在原product_info.product_cent的位置), 若改变须在第一行列出所有字段名来对应
同表选值如果能用子查询可以不用left join而用用户变量:
set @var1=(select product_cent from product_info where product_id='429');
================================================================
经常在统计时要建一个月份的临时表:
(临时表跟普通表用的不同存储空间, 即使重名也不会互相干扰, 但重名临时表比普通表有优先权)
(在ems sql里经tunnel执行会报错无tmp0表, 因tunnel里每条语句一次连接)
CREATE TEMPORARY TABLE tmp0 (mon varchar(2) default '');
INSERT INTO `tmp0` (`mon`) values ('01'), ('02'), ('03'), ('04'), ('05'), ('06'), ('07'), ('08'), ('09'), ('10'), ('11'), ('12');
也可以group by DATE_FORMAT(tb1.date_act,'%m')
================================================================
把选出的数据直接插入新建表(普通表和TEMPORARY表都行):
CREATE [TEMPORARY] TABLE tmp_ids
SELECT id FROM rss_content
WHERE MATCH(title,content) AGAINST('+对' IN BOOLEAN MODE);
================================================================
TEMPORARY表支持把选出的数据直接插入已建表(普通表和TEMPORARY表都行):
INSERT INTO tmp0
select id
from rss
where match(title,content) against('+对' IN BOOLEAN MODE);
================================================================
将sql执行的中间变量放到临时内存表里(既随sql会话而清除也在内存里运作):
一个变量:
CREATE TEMPORARY TABLE tmp_var_cnt TYPE=HEAP
select count(*) as value
from tb1;
...(select value from tmp_var_cnt)...
多个变量(注意utf8字段索引只能255字节即85个字符):
CREATE TEMPORARY TABLE tmp_vars (name varchar(85) NOT NULL,value varchar(255) NOT NULL,PRIMARY KEY (name)) TYPE=HEAP;
REPLACE INTO tmp_vars select 'cnt',count(*) from tb1;
REPLACE INTO tmp_vars select 'max',max(quantity) from tb1;
...(select value from tmp_vars where name='cnt')...
================================================================
数据库服务重启时, 内存表的结构不会丢失, 但数据会丢失, 而临时内存表的结构和数据都会丢失.
================================================================
如果使用保留字作为字段名，需要加上反引号，比如desc(降序)就要加反引号
==================================================================
mysql中sum内或加法式内出现一个null就会导致结果null,并没有自动将null转为零后在求和,
这种情况常见于对从表某个字段求和,而主表的某行关联的从表行数为零,
只好在求和、相加减等地方加个处理:
sum(CASE WHEN tb3.qty is null THEN 0 ELSE tb3.qty END);//不要使用"CASE tb3.qty WHEN null",等价tb3.qty=null语法错误
CASE WHEN tmp1.sum1 is null THEN 0 ELSE tmp1.sum1 END-CASE WHEN tmp2.sum2 is null THEN 0 ELSE tmp2.sum2 END
更简单用ifnull(tb3.qty,0)
==================================================================
SUBSTRING(str,pos[,len]),pos从1记起
==================================================================
RAND()返回0-1之间的浮点数
往数据库插一个随机数:substring(rand(),10,6), 要从10开始不要从11开始, 因为随机浮点数的最后一位为0时被忽略了
==================================================================
算出逗号分隔字段的元素个数(需要直接用sql排序时):
SELECT FIND_IN_SET(SUBSTRING_INDEX(list_field,',',-1),list_field) as cnt_list from table;
==================================================================
用group_concat串起来的字段用find_in_set()>0来作查询条件, 用in()则group_concat的时候要麻烦点
mysql子查询的两种用法(版本4.1.1以上):
(mysql引擎并不一定优化了子查询,可能检查每行数据时都执行了一遍,导致性能问题(而oracle知道自动将子查询放到临时表))
select title from rss_content where id=(select id from ids limit 0,1)
select title from rss_content where id in (select id from ids)
更新里用子查询:
update t1 set column2=(select max(column1) from t2);//注意必须是不同的表
为了性能或者mysql4.1.1之前可以用关联表(如创建临时表再关联)的方法实现(或用前述replace into ... select方法):
update tb1
       left join tb on tb.myid=tb1.myid
set tb1.content=tb.name
where tb.myid is not null
//注意:将需要更新的表作为主表, 否则似乎只能更新找到的第一条数据,
因为update里的left join跟select内不同, 由主表决定行数
==================================================================
where条件里多个条件的顺序优化:符合几率[小:and|大:or]/判断执行快的条件排前面
==================================================================
全文索引了的text字段的使用:
match(title,content) against('独 生 子 女')
所有关键字之间是或关系, 即只要有一个关键字被搜到, match值就大于0
要想所有关键字都存在用BOOLEAN MODE:
match (headline,story) against ('+Hurricane +Katrina' IN BOOLEAN MODE)
要想某些不存在,某些可有可无:
match (headline,story) against ('+Hurricane -Katrina jack' IN BOOLEAN MODE)
在select内时返回匹配程度值, where内时返回匹配度>0的行, 并且按匹配度降序排列
如果返回结果数量超过数据总数的一半或关键词是默认停用的常见词将搜索不到结果
==================================================================
在my.ini里加上ft_min_word_len = 1就可以将汉字当成单词的方式来使用全文索引字段
要注意的是编码格式必须用utf-8, 因为 mysql的match\like与 php的preg_match未开启u参数时 匹配gbk相似:
echo strtr('夏季出手的希望',array('值'=>'hell'));//将得到奇怪的"夏季出蔴ell南Ｍ"
==================================================================
unique索引的最大长度只能定到333(utf8表在mysql5), text字段建unique索引也只比较前333字符
fulltext建的是哈希表, 无顺序, 容量也未限制
==================================================================
搜索种类:
1.程序分词(汉字右侧非汉字时插空格(空格也插空格),汉字左侧插空格,即正则两遍)
2.保持用户分词(用match再and多个like binary或多个locate>0来搜索)
3.保持用户分词且保持近距顺序(用rlike限定多个关键字的距离)
==================================================================
where条件里不能使用select内定义的字段别名, 因为where执行顺序在select前面,
order by里就可以用别名了, 因为它在select后面执行.
==================================================================
数据库改名不能用一句命令实现而是先复制数据库然后再drop旧的数据库, phpmyadmin里写好了可以直接进行数据库改名操作
==================================================================
有时将一个字段从char/varchar类型修改为text等类型时报:
ERROR 1170: BLOB/TEXT column '...' used in key specification without a key length
那时因为已为该字段建了index索引, 而text字段不能建普通索引, 删除索引即可
==================================================================
删除数据后将自动索引号减小一些(只能减到仍存在数据的最大id):
ALTER TABLE `rss_content` AUTO_INCREMENT =1;
也可以在phpmyadmin的操作里做
==================================================================