=========================================安装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南M" ================================================================== 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的操作里做 ==================================================================