MYSQL重要知识点

MySQL是最著名的开源数据库，容易上手且功能强大，Yahoo!、BBCNews等著名站点都使用了MySQL数据库进行数据存储在Linux中可以通过/etc/my.cnf对MySQL服务器进行配置（在Windows中是一个叫my.ini的文件），有接近300个配置参数可以用来在启动MySQL服务器时控制其行为（包括：内存、日志、错误报告等等）在Linux中可以通过servicemysqldstart来启动MySQL服务，通过servicemysqldstop来停止MySQL服务（在Windows中可以通过netstartmysql来启动，通过netstopmysql来停止，也可以通过控制面板中的性能和维护中的管理工具中的服务来启动和停止MySQL服务）在服务器启动后，可以通过输入mysqladmin–uroot–pvariables命令来查看配置参数，可以通过在mysql客户端中输入showvariables;来查看MySQL支持多种存储引擎，可以通过showengines;来查看MySQL支持的所有存储引擎，或通过showvariableslike‘%storage_engine%’;来查看当前使用的存储引擎；如果需要修改所使用的存储引擎可以用altertablet_YYYtype=XXX;或altertablet_YYYengine=XXX;来修改指定的表所使用的存储引擎，也可以通过在my.cnf（my.ini）文件中加入default-storage-engine=XXX来指定默认的存储引擎，下面是对各种存储引擎的一个简单说明：MyISAM：默认的MySQL插件式存储引擎，不支持事务，但是在Web、数据仓储等大数据量应用环境下最常使用的存储引擎之一InnoDB：最常用的用于事务处理的存储引擎，具有众多特性，包括ACID事务支持，在Windows环境下是默认的存储引擎BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性，但在5.1之后的版本中不复存在Memory：将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问，但服务器关闭时不能持久存储MRG_MYISAM：允许MySQLDBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为1个对象引用它们，对于数据仓储等VLDB环境十分适合Archive：为大量很少引用的历史、归档、或安全审计信息的存储和检索提供了完美的解决方案Federated：能够将多个分离的MySQL服务器链接起来，从多个物理服务器创建一个逻辑数据库，适合于分布式环境或数据集群环境NDBCluster：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性MySQL支持丰富的数据类型：时间日期类：DATE/DATETIME/TIME/TIMESTAMP/YEAR数值类：BIGINT/BIT/DECIMAL/FLOAT/INT/MEDIUMINT/SMALLINT/TINYINT字符(串)类：BINARY/BLOB/LONGBLOB/MEDIUMBLOB/TINYBLOB/CHAR/ENUM/SET/TEXT/LONGTEXT/MEDIUMTEXT/TINYTEXT/VARBINARY/VARCHARMySQL图形化的客户端：PHPMyAdmin：MySQLAdministrator：SQLyog：常用命令和操作：1.登录MySQL服务器mysql–uusername–pdatabase_namemysql–hhostname–uusername–pdatabase_name2.修改字符集setcharsetencoding3.修改MySQL提示符promptXXX\c记录输入多少个命令的提示符\d提示当前数据库\D提示当前日期\h提示当前服务器主机\u提示用户名\U提示用户名@主机4.操作数据库a)创建数据库：createdatabasedb_name;或mysqladmin–uroot–pcreatedb_nameb)切换到某数据库：userdb_name;c)删除数据库：dropdatabasedb_name;5.操作表a)创建表：createtablet_name(col1,definition,col2,definition,…,coln,definition);b)显示表结构：desct_name;c)列出所有表：showtables;d)修改表结构i.增加字段：altertablet_nameaddcolumnxxxdefinition;ii.删除字段：altertablet_namedropcolumnxxx;iii.改变字段：altertabelt_namechangecolumnxxxyyydefinition;e)删除表：droptablet_name;f)重命名表：altertabelt_namerenamet_new_name;6.管理用户a)创建用户：createuserusernameidentifiedby‘userpassword’b)创建用户并授权：grantprivilege1,privilege2,…,privilegenondb_name.t_nameto‘username’@’host’identifiedby‘userpassword’;（说明：如果要将所有权限分配给用户可以用allprivileges代替授权项目；如果要将所有数据库的所有表授权给该用户，可以用*.*表示；可以用’%’表示所有站点；如果没有通过identifiedby设置用户口令，之后可以用setpasswordfor‘username’@’host’=password(‘userpassword’);或者通过mysqladmin–uusername–ppassworduserpassword来设置口令）c)召回用户权限：revokeprivilege1,…,privilegenfrom‘username’@’host’;（说明：可以用allprivileges代表召回所有权限）d)重命名用户：renameuser‘username1’@’host1’to‘username2’@’host2’7.存储函数（例子）mysqldelimiter$$mysqlcreatefunctioncalculate_bonus-(employee_idINTEGER)RETURNSDECIMAL(5,2)-BEGIN-DECLAREarticle_countINTEGER;-DECLAREbonusDECIMAL(10,2);-SELECTcount(id)ASarticle_countFROMarticles-WHEREauthor_id=employee_id;-setbonus=article_count*10;-RETURNbonus;-END;-$$mysqlDELIMITER;mysqlselectname,phone,calculate_bonus(id)fromauthors;mysqldropfunctioncalculate_bonus;8.视图（例子）mysqlcreateviewauthor_viewasselectname,e-mail,phonefromauthorsorderedbyemailASC;mysqlselect*fromauthor_view;mysqlalterviewauthor_viewasselectname,phoneFROMauthorsorderedbyphone;mysqldropviewauthor_view;9.触发器（例子）mysqlDELIMITER$$mysqlcreatetriggerarticle_counter-afterinsertonarticles-foreachrowbegin-updatecategoriessetcter=cter+1whereid=new.category_id;-END;-$$mysqlDELIMITER;10.事务a)查看事务隔离级别：select@@tx_isloation;b)设置事务隔离级别：set[global|session]transactionisolationlevel{READUNCOMMITTED|READCOMMITTED|REPEATABLEREAD|SERIALIZABLE}11.状态status12.客户端输出重定向a)开启：teefilenameb)关闭：notee13.备份a)导出表：select*intooutfile‘filename’fromt_name;b)导入表：loaddatainfile‘filename’intotablet_name;c)备份数据库：mysqldump–uroot–pdb_namefilenamemysqldump–uroot–p--all-databasesfilenamed)恢复数据库：mysql–uroot–pdb_namefilename