MySQL5学习笔记

Author:jarwang(王重东)QQ:[e-mail]:永中OfficeForLinuxOPEN-SOURCEDATABASE学习笔记--MySQL5.1作者:Jarwang建档时间:2007-09-27最后更新:文档号:版本:Author:jarwang(王重东)QQ:[e-mail]:永中OfficeForLinux文档控制更新履历3日期作者版本修改备注2007-9-27JarwangV1.1查核姓名职位分发NoNameLocation1234NoteToHolders:Ifyoureceiveanelectroniccopyofthisdocumentandprintitout,pleasewriteyournameontheequivalentofthecoverpage,fordocumentcontrolpurposes.Ifyoureceiveahardcopyofthisdocument,pleasewriteyournameonthefrontcover,fordocumentcontrolpurposes.Author:jarwang(王重东)QQ:[e-mail]:永中OfficeForLinux目录文档控制ii目录iiiMySQL基础1MySQL安装1下载安装MySQL1配置MySQL1下载安装mysql-gui-tools2数据库入门3MySQL-SQL基础3MySQL常用函数6MySQL数据引擎7MySQL应用9存储过程9MySQL存储过程之java调用9MySQL存储过程实现动态查询12MySQL应用总结12MySQL数据管理15数据管理15MySQL数据导出15MySQL数据还原15MySQL灾难性复制恢复总结16MySQL授权管理17未解决及已解决问题19未解决问题19已解决问题19Author:jarwang(王重东)QQ:[e-mail]:永中OfficeForLinux1MySQL基础开始接触MySQL，完全是因为预算费用的问题。^_^?。从最初的MySQL4不支持事务，到MySQL5支持事务提交，再到MySQL5.1的分区表。MySQL5.1是被SUN收购后，推出的最一个版本。本文原来是以MySQL5.0为基础，更新到5.1也同样适用。所以就以5.1为基础了。MySQL安装下载安装MySQL到下载，MySQL5.1是目前最新稳定版，并且区别社区版与企业版。学习的话完全可以使用community，企业应用的话推荐使用enterprise。选择相应的操作系统，一般来说下载Server、Client即可。rpm包很小26M，安装也相当简单。切换到root用户。su-rpm-ivhMySQL-server-community-5.1.33-0.rhel5.x86_64.rpmrpm-ivhMySQL-client-community-5.1.33-0.rhel5.x86_64.rpm当然也可以下载源码包，然后编译安装。总之和Oracle庞大相比，MySQL简直就是天差地别。配置MySQL在terminate中输入mysql，即可从本机登陆mysql。1、安装完后,要做第一件事,就是授权。授权root用户可以远程访问mysql。Author:jarwang(王重东)QQ:[e-mail]:永中OfficeForLinuxmysql的根用户root,类似sqlserver的sa。但root在默认情况是只允许从本机访问的，这一点不同于sa。初学者很容易就在这边浪费时间。再强调一点，默认状态下root只能从mysql自带的命令模式登录.既使是在本机用mysqladministrator或mysqlfront等等都是无法连接的。例:你想myuser使用mypassword从任何主机连接到mysql服务器的话。GRANTALLPRIVILEGESON*.*TO'myuser'@'%'IDENTIFIEDBY'mypassword'WITHGRANTOPTION;*.*表示有数所库。如果只允许访问一个数据库test，应写成test.*@'%'表所有主机。如果只允许只特定的主机访问，就写成@hostIP.例：如果你想仅允许用户myuser从ip为192.168.1.3的主机连接到mysql服务器，并使用mypassword作为密码。GRANTALLPRIVILEGESON*.*TO'myuser'@'192.168.1.3'IDENTIFIEDBY'mypassword'WITHGRANTOPTION;ALLPRIVILEGES的细项：主要有select\update\insert\delete等等。2、授权完后，就可以在远程连接MySQL服务器。mysql-hserverIP-uroot-p123说明-h与serverIP,可不加空格.其他参数以此类推.如果端口更改了,不是默认的3306.那么就要带上端口参数。mysql-hserverIp--port=3307-uroot-p1233、修改root密码为了便于管理及安全起见，应该修改root密码：mysqladmin-uroot-ppassward'newpassword'下载安装mysql-gui-tools尽管mysql-gui-tools还有很多BUG，但作为官方一种DBA的图形工具，还是相当好用的。到下载，安装：su-rpm-ivhmysql-gui-tools-5.0r12-1rhel4.i386.rpmrpm-ivhmysql-administrator-5.0r12-1rhel4.i386.rpmrpm-ivhmysql-migration-toolkit-5.0r12-1rhel4.i386.rpmrpm-ivhmysql-query-browser-5.0r12-1rhel4.i386.rpm安装完成后，会在Application菜单自动创建子菜单。DBA工具：Author:jarwang(王重东)QQ:[e-mail]:永中OfficeForLinux开发人工具：数据库入门MySQL-SQL基础1、创建表createtabletableNameCREATETABLEengineTest(idINT);2、更改表名altertableold_tableNamerenamenew_tableName3、增加列altertabletableNameAuthor:jarwang(王重东)QQ:[e-mail]:永中OfficeForLinuxaddtestColint4、修改列altertabletableNamemodifytestColvarchar(30)5、删除列altertabletableNamedropcolumntestCol6、删除表droptabletableName7、创建主从表createtableMainTable(intidprimarykey,namevarchar(40))createtableSlaveTable(intfId,sexvarchar(30),constraintfk_testforeignkey(fId)referencesMainTable(id))如果表是先创建,要添加主从关系.如下:altertableSlaveTableaddconstraintfk_testforeignkey(fId)referencesMainTable(id)值得一提的是:主键与外键,数据类型必须一致,否则报错#1005错误.8、mysql不支持top*的写法。但从mysql5开始可以使用select*fromtableNamelimitN9、limit+orderby语法跟mssql不一样，要先写orderby后加limit例：select*fromtableNameorderbycolumnlimit1010、把两列合并成一列Oracle的写法:selecta.column+b.columnfromtableAuthor:jarwang(王重东)QQ:[e-mail]:永中OfficeForLinuxMysql对非数值列不支持+的写法,正确的写法是：selectconcat(a.column+b.column)fromtable11、求笛卡尔积selecta.*,b.*fromnameasacrossjoinnameasb12、求至少选修两门课程的学生号（三个表s,c,sc）selecta.sno,c.snamefromSCasacrossjoinSCasbinnerjoinsasconb.sno=c.sno13、创建索引createindexindex_Nameontable(SNO)要说明的是Mysql删除索引dropindexindex_Nameontable不同于sql2000的语法:dropindextable.index_Name14、SQL流程控制语句（不包括Procedure）CASEvalueWHEN[compare-value]THENresult[WHEN[compare-value]THENresult...][ELSEresult]ENDCASEWHEN[condition]THENresult[WHEN[condition]THENresult...][ELSEresult]END分成两种形式：1、casevaluewhencompare-valuethenresult例：casetempwhen1then'ok'when2then'no'end;2、casewhencompare-valuethenresult1elseresult2例：casewhena.orderNumPart=''thenconcat(`a`.`OrderNum`,`a`.`OrderNumPart`)elseconcat(`a`.`OrderNum`,_gb2312'',`a`.`OrderNumPart`)end)这个语法跟SQL-SERVER是一样。15、mysqlinsert语法，主要指多个values的写法。例：insertintotableName(col1,col2)values(1,2)(3,4)(5,6)Author:jarwang(王重东)QQ:[e-mail]:永中OfficeForLinux要注意的是多个values之间要加“，”。这个跟标准的SQL语法相差很大。MySQL常用函数MySQL的一些常用函数与SQLSERVER的不同，在使用时应该特别注意。以下是我的一些收集，当然，MySQL还有很多功能强大的函数。但我的精力有限，只列举一些常用的函数。1、isNull()SQLSEVER语法：ISNULL(check_expression,replacement_value)用法：检查check_expression是否为NULL，并转换成replacement_value。例：selectisNull(col1,0)fromtable注：check_expression与replacement_value的类型必须相同。MySQL语法：isNull(check_expression)用法：检查check_expression是否为NULL。是返回1，否返回0。例：selectisNull(col1)fromtable注：由上可得出MySQL与SQLSERVER的IsNull()函数相差很大。MySQL要实现SQLSERVER上述例子，必须