还剩88页未读,继续阅读
本资源只提供10页预览,全部文档请下载后查看!喜欢就下载吧,查找使用更方便
文本内容:
MySQL教程Mysql是最流行的关系型数据库管理系统,在WEB应用方面MySQL是最好的RDBMSRelationalDatabaseManagementSystem关系数据库管理系统应用软件之一在本教程中,会让大家快速掌握Mysql的基本知识,并轻松使用Mysql数据库什么是数据库?数据库(Database)是按照数据结构来组织、存储和管理数据的仓库,每个数据库都有一个或多个不同的API用于创建,访问,管理,搜索和复制所保存的数据我们也可以将数据存储在文件中,但是在文件中读写数据速度相对较慢所以,现在我们使用关系型数据库管理系统(RDBMS)来存储和管理的大数据量所谓的关系型数据库,是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据RDBMS即关系数据库管理系统RelationalDatabaseManagementSystem的特点
1.数据以表格的形式出现
2.每行为各种记录名称
3.每列为记录名称所对应的数据域
4.许多的行和列组成一张表单
5.若干的表单组成databaseRDBMS术语在我们开始学习MySQL数据库前,让我们先了解下RDBMS的一些术语数据库:数据库是一些关联表的集合.数据表:表是数据的矩阵在一个数据库中的表看起来像一个简单的电子表格列:一列数据元素包含了相同的数据例如邮政编码的数据行一行(=元组,或记录)是一组相关的数据,例如一条用户订阅的数据冗余存储两倍数据,冗余降低了性能,但提高了数据的安全性主键主键是唯一的一个数据表中只能包含一个主键你可以使用主键来查询数据外键外键用于关联两个表复合键复合键(组合键)将多个列作为一个索引键,一般用于复合索引索引使用索引可快速访问数据库表中的特定信息索引是对数据库表中一列或多列的值进行排序的一种结构类似于书籍的目录参照完整性:参照的完整性要求关系中不允许引用不存在的实体与实体完整性是关系模型必须满足的完整性约束条件,目的是保证数据的一致性Mysql数据库MySQL是一个关系型数据库管理系统,由瑞典MySQLAB公司开发,目前属于Oracle公司MySQL是一种关联数据库管理系统,关联数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性Mysql是开源的,所以你不需要支付额外的费用Mysql支持大型的数据库可以处理拥有上千万条记录的大型数据库MySQL使用标准的SQL数据语言形式Mysql可以允许于多个系统上,并且支持多种语言这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等Mysql对PHP有很好的支持,PHP是目前最流行的Web开发语言MySQL支持大型数据库,支持5000万条记录的数据仓库,32位系统表文件最大可支持4GB,64位系统支持最大的表文件为8TBMysql是可以定制的,采用了GPL协议,你可以修改源码来开发自己的Mysql系统在开始学习本教程前你应该了解?在开始学习本教程前你应该了解PHP和HTML的基础知识,并能简单的应用本教程的很多例子都跟PHP语言有关,我们的实例基本上是采用PHP语言来演示如果你还不了解PHP,你可以通过本站的PHP教程来了解该语言MySQL安装所有平台的Mysql下载地址为MySQL下载.挑选你需要的MySQLCommunityServer版本及对应的平台Linux/UNIX上安装MysqlLinux平台上推荐使用RPM包来安装MysqlMySQLAB提供了以下RPM包的下载地址MySQL-MySQL服务器你需要该选项,除非你只想连接运行在另一台机器上的MySQL服务器MySQL-client-MySQL客户端程序,用于连接并操作Mysql服务器MySQL-devel-库和包含文件,如果你想要编译其它MySQL客户端,例如Perl模块,则需要安装该RPM包MySQL-shared-该软件包包含某些语言和应用程序需要动态装载的共享库libmysqlclient.so*,使用MySQLMySQL-bench-MySQL数据库服务器的基准和性能测试工具接下来我们在Centos系统下使用yum命令安装MySql检测系统是否自带安装mysql:rpm-qa|grepmysql如果你系统有安装,那可以选择进行卸载:rpm-emysql //普通删除模式rpm-e--nodepsmysql //强力删除模式,如果使用上面命令删除时,提示有依赖的其它文件,则用该命令可以对其进行强力删除安装mysql yuminstallmysqlyuminstallmysql-serveryuminstallmysql-devel启动mysql servicemysqldstart注意如果我们是第一次启动mysql服务,mysql服务器首先会进行初始化的配置如果是CentOS7版本,由于MySQL数据库已从默认的程序列表中移除,可以使用mariadb代替yuminstallmariadb-servermariadbmariadb数据库的相关命令是systemctlstartmariadb#启动MariaDBsystemctlstopmariadb#停止MariaDBsystemctlrestartmariadb#重启MariaDBsystemctlenablemariadb#设置开机启动验证Mysql安装在成功安装Mysql后,一些基础表会表初始化,在服务器启动后,你可以通过简单的测试来验证Mysql是否工作正常使用mysqladmin工具来获取服务器状态使用mysqladmin命令俩检查服务器的版本在linux上该二进制文件位于/usr/binonlinux,在window上该二进制文件位于C:\mysql\bin[root@host]#mysqladmin--versionlinux上该命令将输出以下结果,该结果基于你的系统信息mysqladminVer
8.23Distrib
5.
0.9-0forredhat-linux-gnuoni386如果以上命令执行后未输入任何信息,说明你的Mysql未安装成功使用MySQLClientMysql客户端执行简单的SQL命令你可以在MySQLClientMysql客户端使用mysql命令连接到Mysql服务器上,默认情况下Mysql服务器的密码为空,所以本实例不需要输入密码命令如下[root@host]#mysql以上命令执行后会输出mysql提示符,这说明你已经成功连接到Mysql服务器上,你可以在mysql提示符执行SQL命令mysqlSHOWDATABASES;+----------+|Database|+----------+|mysql||test|+----------+2rowsinset
0.13secMysql安装后需要做的Mysql安装成功后,默认的root用户密码为空,你可以使用以下命令来创建root用户的密码[root@host]#mysqladmin-urootpasswordnew_password;现在你可以通过以下命令来连接到Mysql服务器[root@host]#mysql-uroot-pEnterpassword:*******注意在输入密码时,密码是不会显示了,你正确输入即可Window上安装MysqlWindow上安装Mysql相对来说会较为简单,你只需要载MySQL下载中下载window版本的mysql安装包,并解压安装包双击setup.exe文件,接下来你只需要安装默认的配置点击next即可,默认情况下安装信息会在C:\mysql目录中接下来你可以通过开始=》在搜索框中输入cmd命令=》在命令提示符上切换到C:\mysql\bin目录,并输入一下命令mysqld.exe--console如果安装成功以上命令将输出一些mysql启动及InnoDB信息MySQL管理启动及关闭MySQL服务器首先,我们需要通过以下命令来检查MySQL服务器是否启动ps-ef|grepmysqld如果MySql已经启动,以上命令将输出mysql进程列表,如果mysql未启动,你可以使用以下命令来启动mysql服务器:root@host#cd/usr/bin./mysqld_safe如果你想关闭目前运行的MySQL服务器你可以执行以下命令:root@host#cd/usr/bin./mysqladmin-uroot-pshutdownEnterpassword:******MySQL用户设置如果你需要添加MySQL用户,你只需要在mysql数据库中的user表添加新用户即可以下为添加用户的的实例,用户名为guest,密码为guest123,并授权用户可进行SELECTINSERT和UPDATE操作权限root@host#mysql-uroot-pEnterpassword:*******mysqlusemysql;DatabasechangedmysqlINSERTINTOuserhostuserpasswordselect_privinsert_privupdate_privVALUESlocalhostguestPASSWORDguest123YYY;QueryOK1rowaffected
0.20secmysqlFLUSHPRIVILEGES;QueryOK1rowaffected
0.01secmysqlSELECThostuserpasswordFROMuserWHEREuser=guest;+-----------+---------+------------------+|host|user|password|+-----------+---------+------------------+|localhost|guest|6f8c114b58f2ce9e|+-----------+---------+------------------+1rowinset
0.00sec在添加用户时,请注意使用MySQL提供的PASSWORD函数来对密码进行加密你可以在以上实例看到用户密码加密后为6f8c114b58f2ce9e.注意在MySQL
5.7中user表的password已换成了authentication_string注意在注意需要执行FLUSHPRIVILEGES语句这个命令执行后会重新载入授权表如果你不使用该命令,你就无法使用新创建的用户来连接mysql服务器,除非你重启mysql服务器你可以在创建用户时,为用户指定权限,在对应的权限列中,在插入语句中设置为Y即可,用户权限列表如下Select_privInsert_privUpdate_privDelete_privCreate_privDrop_privReload_privShutdown_privProcess_privFile_privGrant_privReferences_privIndex_privAlter_priv另外一种添加用户的方法为通过SQL的GRANT命令,你下命令会给指定数据库TUTORIALS添加用户zara,密码为zara123root@host#mysql-uroot-ppassword;Enterpassword:*******mysqlusemysql;DatabasechangedmysqlGRANTSELECTINSERTUPDATEDELETECREATEDROP-ONTUTORIALS.*-TOzara@localhost-IDENTIFIEDBYzara123;以上命令会在mysql数据库中的user表创建一条用户信息记录注意:MySQL的SQL语句以分号;作为结束标识/etc/my.cnf文件配置一般情况下,你不需要修改该配置文件,该文件默认配置如下[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock[mysql.server]user=mysqlbasedir=/var/lib[safe_mysqld]err-log=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid在配置文件中,你可以指定不同的错误日志文件存放的目录,一般你不需要改动这些配置管理MySQL的命令以下列出了使用Mysql数据库过程中常用的命令USE数据库名:选择要操作的Mysql数据库,使用该命令后所有Mysql命令都只针对该数据库mysqluseRUNOOB;DatabasechangedSHOWDATABASES:列出MySQL数据库管理系统的数据库列表mysqlSHOWDATABASES;+--------------------+|Database|+--------------------+|information_schema||RUNOOB||cdcol||mysql||onethink||performance_schema||phpmyadmin||test||wecenter||wordpress|+--------------------+10rowsinset
0.02secSHOWTABLES:显示指定数据库的所有表,使用该命令前需要使用use命令来选择要操作的数据库mysqluseRUNOOB;DatabasechangedmysqlSHOWTABLES;+------------------+|Tables_in_runoob|+------------------+|employee_tbl||runoob_tbl||tcount_tbl|+------------------+3rowsinset
0.00secSHOWCOLUMNSFROM数据表:显示数据表的属性,属性类型,主键信息,是否为NULL,默认值等其他信息mysqlSHOWCOLUMNSFROMrunoob_tbl;+-----------------+--------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-----------------+--------------+------+-----+---------+-------+|runoob_id|int11|NO|PRI|NULL|||runoob_title|varchar255|YES||NULL|||runoob_author|varchar255|YES||NULL|||submission_date|date|YES||NULL||+-----------------+--------------+------+-----+---------+-------+4rowsinset
0.01secSHOWINDEXFROM数据表:显示数据表的详细索引信息,包括PRIMARYKEY(主键)mysqlSHOWINDEXFROMrunoob_tbl;+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment|+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+|runoob_tbl|0|PRIMARY|1|runoob_id|A|2|NULL|NULL||BTREE|||+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+1rowinset
0.00secSHOWTABLESTATUSLIKE[FROMdb_name][LIKEpattern]\G:该命令将输出Mysql数据库管理系统的性能及统计信息mysqlSHOWTABLESTATUSFROMRUNOOB;#显示数据库RUNOOB中所有表的信息mysqlSHOWTABLESTATUSfromRUNOOBLIKErunoob%;#表名以runoob开头的表的信息mysqlSHOWTABLESTATUSfromRUNOOBLIKErunoob%\G;#加上\G,查询结果按列打印Gif图演示MySQL安装MySQLPHP语法笔记列表
1. oocarain ooc***in@
163.com记录MySQL学习过程遇到的问题系统win32位MySQL版本
5.
7.17-logMySQL语法对大小写不敏感,但是大写更容易看出
一、启动关闭MySQL服务1【开始菜单】搜索services.msc打开windows【服务管理器】,可以在此开启关闭MySQL服务2在cmd中使用命令netstartmysql#启动mysql服务netstopmysql#关闭mysql服务遇到net命令无法识别,如下这是环境变量没有配置的原因,究竟是哪一个文件的环境变量没有配置呢?是C:\windows\system32\这个路径下的net.exe没有配置环境变量现切换到这个路径下试一下可不可以使用net命令在Powershell需要使用.\netstopmysql关闭服务在cmd中可以直接使用netstartmysql启动服务将c:\windows\system32添加到系统的Path中后成功!!!oocarain oocarain ooc***in@
163.com5个月前03-
062. 一条鱼 ili***yun@
163.com 参考地址用insert添加用户时,可能会报错:ERROR1364HY000:Fieldssl_cipherdoesnthaveadefaultvaluemy-default.ini中有一条语句指定了严格模式,为了安全,严格模式禁止通过insert这种形式直接修改mysql库中的user表进行添加新用户sql_mode=NO_ENGINE_SUBSTITUTIONSTRICT_TRANS_TABLES将STRICT_TRANS_TABLES删掉之后即可使用insert添加MySQL连接使用mysql二进制方式连接您可以使用MySQL二进制方式进入到mysql命令提示符下来连接MySQL数据库实例以下是从命令行中连接mysql服务器的简单实例[root@host]#mysql-uroot-pEnterpassword:******在登录成功后会出现mysql命令提示窗口,你可以在上面执行任何SQL语句以上命令执行后,登录成功输出结果如下:WelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis2854760toserverversion:
5.
0.9Typehelp;or\hforhelp.Type\ctoclearthebuffer.在以上实例中,我们使用了root用户登录到mysql服务器,当然你也可以使用其他mysql用户登录如果用户权限足够,任何用户都可以在mysql的命令提示窗口中进行SQL操作退出mysql命令提示窗口可以使用exit命令,如下所示mysqlexitBye使用PHP脚本连接MySQLPHP提供了mysqli_connect函数来连接数据库该函数有6个参数,在成功链接到MySQL后返回连接标识,失败返回FALSE语法mysqli_connecthostusernamepassworddbnameportsocket;参数说明参数描述host可选规定主机名或IP地址username可选规定MySQL用户名password可选规定MySQL密码dbname可选规定默认使用的数据库port可选规定尝试连接到MySQL服务器的端口号socket可选规定socket或要使用的已命名pipe你可以使用PHP的mysqli_close函数来断开与MySQL数据库的链接该函数只有一个参数为mysqli_connect函数创建连接成功后返回的MySQL连接标识符语法boolmysqli_closemysqli$link本函数关闭指定的连接标识所关联的到MySQL服务器的非持久连接如果没有指定link_identifier,则关闭上一个打开的连接提示通常不需要使用mysqli_close,因为已打开的非持久连接会在脚本执行完毕后自动关闭实例你可以尝试以下实例来连接到你的MySQL服务器:连接MySQLphp$dbhost=localhost:3306;//mysql服务器主机地址$dbuser=root;//mysql用户名$dbpass=123456;//mysql用户名密码$conn=mysqli_connect$dbhost$dbuser$dbpass;if!$conn{dieCouldnotconnect:.mysqli_error;}echo数据库连接成功!;mysqli_close$conn;MySQL创建数据库使用mysqladmin创建数据库使用普通用户,你可能需要特定的权限来创建或者删除MySQL数据库所以我们这边使用root用户登录,root用户拥有最高权限,可以使用mysqlmysqladmin命令来创建数据库实例以下命令简单的演示了创建数据库的过程,数据名为RUNOOB:[root@host]#mysqladmin-uroot-pcreateRUNOOBEnterpassword:******以上命令执行成功后会创建MySQL数据库RUNOOB使用PHP脚本创建数据库PHP使用mysqli_query函数来创建或者删除MySQL数据库该函数有两个参数,在执行成功时返回TRUE,否则返回FALSE语法mysqli_queryconnectionqueryresultmode;参数描述connection必需规定要使用的MySQL连接query必需,规定查询字符串resultmode可选一个常量可以是下列值中的任意一个MYSQLI_USE_RESULT(如果需要检索大量数据,请使用这个)MYSQLI_STORE_RESULT(默认)实例以下实例演示了使用PHP来创建一个数据库创建数据库php$dbhost=localhost:3306;//mysql服务器主机地址$dbuser=root;//mysql用户名$dbpass=123456;//mysql用户名密码$conn=mysqli_connect$dbhost$dbuser$dbpass;if!$conn{die连接错误:.mysqli_error$conn;}echo连接成功br/;$sql=CREATEDATABASERUNOOB;$retval=mysqli_query$conn$sql;if!$retval{die创建数据库失败:.mysqli_error$conn;}echo数据库RUNOOB创建成功\n;mysqli_close$conn;执行成功后,返回如下结果如果数据库已存在,执行后,返回如下结果MySQL删除数据库使用mysqladmin删除数据库使用普通用户登陆mysql服务器,你可能需要特定的权限来创建或者删除MySQL数据库所以我们这边使用root用户登录,root用户拥有最高权限,可以使用mysqlmysqladmin命令来创建数据库在删除数据库过程中,务必要十分谨慎,因为在执行删除命令后,所有数据将会消失以下实例删除数据库RUNOOB该数据库在前一章节已创建[root@host]#mysqladmin-uroot-pdropRUNOOBEnterpassword:******执行以上删除数据库命令后,会出现一个提示框,来确认是否真的删除数据库Droppingthedatabaseispotentiallyaverybadthingtodo.Anydatastoredinthedatabasewillbedestroyed.DoyoureallywanttodroptheRUNOOBdatabase[y/N]yDatabaseRUNOOBdropped使用PHP脚本删除数据库PHP使用mysqli_query函数来创建或者删除MySQL数据库该函数有两个参数,在执行成功时返回TRUE,否则返回FALSE语法mysqli_queryconnectionqueryresultmode;参数描述connection必需规定要使用的MySQL连接query必需,规定查询字符串resultmode可选一个常量可以是下列值中的任意一个MYSQLI_USE_RESULT(如果需要检索大量数据,请使用这个)MYSQLI_STORE_RESULT(默认)实例以下实例演示了使用PHPmysqli_query函数来删除数据库删除数据库php$dbhost=localhost:3306;//mysql服务器主机地址$dbuser=root;//mysql用户名$dbpass=123456;//mysql用户名密码$conn=mysqli_connect$dbhost$dbuser$dbpass;if!$conn{die连接失败:.mysqli_error$conn;}echo连接成功br/;$sql=DROPDATABASERUNOOB;$retval=mysqli_query$conn$sql;if!$retval{die删除数据库失败:.mysqli_error$conn;}echo数据库RUNOOB删除成功\n;mysqli_close$conn;执行成功后,数结果为注意在使用PHP脚本删除数据库时,不会出现确认是否删除信息,会直接删除指定数据库,所以你在删除数据库时要特别小心MySQL选择数据库在你连接到MySQL数据库后,可能有多个可以操作的数据库,所以你需要选择你要操作的数据库从命令提示窗口中选择MySQL数据库在mysql提示窗口中可以很简单的选择特定的数据库你可以使用SQL命令来选择指定的数据库实例以下实例选取了数据库RUNOOB:[root@host]#mysql-uroot-pEnterpassword:******mysqluseRUNOOB;Databasechangedmysql执行以上命令后,你就已经成功选择了RUNOOB数据库,在后续的操作中都会在RUNOOB数据库中执行注意:所有的数据库名,表名,表字段都是区分大小写的所以你在使用SQL命令时需要输入正确的名称使用PHP脚本选择MySQL数据库PHP提供了函数mysqli_select_db来选取一个数据库函数在执行成功后返回TRUE,否则返回FALSE语法mysqli_select_dbconnectiondbname;参数描述connection必需规定要使用的MySQL连接dbname必需,规定要使用的默认数据库实例以下实例展示了如何使用mysqli_select_db函数来选取一个数据库删除数据库php$dbhost=localhost:3306;//mysql服务器主机地址$dbuser=root;//mysql用户名$dbpass=123456;//mysql用户名密码$conn=mysqli_connect$dbhost$dbuser$dbpass;if!$conn{die连接失败:.mysqli_error$conn;}echo连接成功;mysqli_select_db$connRUNOOB;mysqli_close$conn;MySQL数据类型MySQL中定义数据字段的类型对你数据库的优化是非常重要的MySQL支持多种类型,大致可以分为三类数值、日期/时间和字符串字符类型数值类型MySQL支持所有标准SQL数值数据类型这些类型包括严格数值数据类型INTEGER、SMALLINT、DECIMAL和NUMERIC,以及近似数值数据类型FLOAT、REAL和DOUBLEPRECISION关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词BIT数据类型保存位字段值,并且支持MyISAM、MEMORY、InnoDB和BDB表作为SQL标准的扩展,MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT下面的表显示了需要的每个整数类型的存储和范围类型大小范围(有符号)范围(无符号)用途TINYINT1字节-128,1270,255小整数值SMALLINT2字节-32768,327670,65535大整数值MEDIUMINT3字节-8388608,83886070,16777215大整数值INT或INTEGER4字节-2147483648,21474836470,4294967295大整数值BIGINT8字节-9233372036854775808,92233720368547758070,18446744073709551615极大整数值FLOAT4字节-
3.402823466E+38,-
1.175494351E-38,0,
1.175494351E-38,
3.402823466351E+380,
1.175494351E-38,
3.402823466E+38单精度浮点数值DOUBLE8字节-
1.7976931348623157E+308,-
2.2250738585072014E-308,0,
2.2250738585072014E-308,
1.7976931348623157E+3080,
2.2250738585072014E-308,
1.7976931348623157E+308双精度浮点数值DECIMAL对DECIMALMD,如果MD,为M+2否则为D+2依赖于M和D的值依赖于M和D的值小数值日期和时间类型表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR每个时间类型有一个有效值范围和一个零值,当指定不合法的MySQL不能表示的值时使用零值TIMESTAMP类型有专有的自动更新特性,将在后面描述类型大小字节范围格式用途DATE31000-01-01/9999-12-31YYYY-MM-DD日期值TIME3-838:59:59/838:59:59HH:MM:SS时间值或持续时间YEAR11901/2155YYYY年份值DATETIME81000-01-0100:00:00/9999-12-3123:59:59YYYY-MM-DDHH:MM:SS混合日期和时间值TIMESTAMP41970-01-0100:00:00/2037年某时YYYYMMDDHHMMSS混合日期和时间值,时间戳字符串类型字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET该节描述了这些类型如何工作以及如何在查询中使用这些类型类型大小用途CHAR0-255字节定长字符串VARCHAR0-65535字节变长字符串TINYBLOB0-255字节不超过255个字符的二进制字符串TINYTEXT0-255字节短文本字符串BLOB0-65535字节二进制形式的长文本数据TEXT0-65535字节长文本数据MEDIUMBLOB0-16777215字节二进制形式的中等长度文本数据MEDIUMTEXT0-16777215字节中等长度文本数据LONGBLOB0-4294967295字节二进制形式的极大文本数据LONGTEXT0-4294967295字节极大文本数据CHAR和VARCHAR类型类似,但它们保存和检索的方式不同它们的最大长度和是否尾部空格被保留等方面也不同在存储或检索过程中不进行大小写转换BINARY和VARBINARY类类似于CHAR和VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串也就是说,它们包含字节字符串而不是字符字符串这说明它们没有字符集,并且排序和比较基于列值字节的数值值BLOB是一个二进制大对象,可以容纳可变数量的数据有4种BLOB类型TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB它们只是可容纳值的最大长度不同有4种TEXT类型TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT这些对应4种BLOB类型,有相同的最大长度和存储需求MySQL创建数据表创建MySQL数据表需要以下信息表名表字段名定义每个表字段语法以下为创建MySQL数据表的SQL通用语法CREATETABLEtable_namecolumn_namecolumn_type;以下例子中我们将在RUNOOB数据库中创建数据表runoob_tbl CREATETABLEIFNOTEXISTS`runoob_tbl``runoob_id`INTUNSIGNEDAUTO_INCREMENT`runoob_title`VARCHAR100NOTNULL`runoob_author`VARCHAR40NOTNULL`submission_date`DATEPRIMARYKEY`runoob_id`ENGINE=InnoDBDEFAULTCHARSET=utf8;实例解析如果你不想字段为NULL可以设置字段的属性为NOTNULL,在操作数据库时如果输入该字段的数据为NULL,就会报错AUTO_INCREMENT定义列为自增的属性,一般用于主键,数值会自动加1PRIMARYKEY关键字用于定义列为主键您可以使用多列来定义主键,列间以逗号分隔ENGINE设置存储引擎,CHARSET设置编码通过命令提示符创建表通过mysql命令窗口可以很简单的创建MySQL数据表你可以使用SQL语句CREATETABLE来创建数据表实例以下为创建数据表runoob_tbl实例:root@host#mysql-uroot-pEnterpassword:*******mysqluseRUNOOB;DatabasechangedmysqlCREATETABLErunoob_tbl-runoob_idINTNOTNULLAUTO_INCREMENT-runoob_titleVARCHAR100NOTNULL-runoob_authorVARCHAR40NOTNULL-submission_dateDATE-PRIMARYKEYrunoob_id-ENGINE=InnoDBDEFAULTCHARSET=utf8;QueryOK0rowsaffected
0.16secmysql注意MySQL命令终止符为分号;使用PHP脚本创建数据表你可以使用PHP的mysqli_query函数来创建已存在数据库的数据表该函数有两个参数,在执行成功时返回TRUE,否则返回FALSE语法mysqli_queryconnectionqueryresultmode;参数描述connection必需规定要使用的MySQL连接query必需,规定查询字符串resultmode可选一个常量可以是下列值中的任意一个MYSQLI_USE_RESULT(如果需要检索大量数据,请使用这个)MYSQLI_STORE_RESULT(默认)实例以下实例使用了PHP脚本来创建数据表创建数据表php$dbhost=localhost:3306;//mysql服务器主机地址$dbuser=root;//mysql用户名$dbpass=123456;//mysql用户名密码$conn=mysqli_connect$dbhost$dbuser$dbpass;if!$conn{die连接失败:.mysqli_error$conn;}echo连接成功br/;$sql=CREATETABLErunoob_tbl.runoob_idINTNOTNULLAUTO_INCREMENT.runoob_titleVARCHAR100NOTNULL.runoob_authorVARCHAR40NOTNULL.submission_dateDATE.PRIMARYKEYrunoob_idENGINE=InnoDBDEFAULTCHARSET=utf8;;mysqli_select_db$connRUNOOB;$retval=mysqli_query$conn$sql;if!$retval{die数据表创建失败:.mysqli_error$conn;}echo数据表创建成功\n;mysqli_close$conn;执行成功后,就可以通过命令行查看表结构MySQL删除数据表MySQL中删除数据表是非常容易操作的,但是你再进行删除表操作时要非常小心,因为执行删除命令后所有数据都会消失语法以下为删除MySQL数据表的通用语法DROPTABLEtable_name;在命令提示窗口中删除数据表在mysql命令提示窗口中删除数据表SQL语句为DROPTABLE实例以下实例删除了数据表runoob_tbl:root@host#mysql-uroot-pEnterpassword:*******mysqluseRUNOOB;DatabasechangedmysqlDROPTABLErunoob_tblQueryOK0rowsaffected
0.8secmysql使用PHP脚本删除数据表PHP使用mysqli_query函数来删除MySQL数据表该函数有两个参数,在执行成功时返回TRUE,否则返回FALSEh3语法mysqli_queryconnectionqueryresultmode;参数描述connection必需规定要使用的MySQL连接query必需,规定查询字符串resultmode可选一个常量可以是下列值中的任意一个MYSQLI_USE_RESULT(如果需要检索大量数据,请使用这个)MYSQLI_STORE_RESULT(默认)实例以下实例使用了PHP脚本删除数据表runoob_tbl:删除数据库php$dbhost=localhost:3306;//mysql服务器主机地址$dbuser=root;//mysql用户名$dbpass=123456;//mysql用户名密码$conn=mysqli_connect$dbhost$dbuser$dbpass;if!$conn{die连接失败:.mysqli_error$conn;}echo连接成功br/;$sql=DROPTABLErunoob_tbl;mysqli_select_db$connRUNOOB;$retval=mysqli_query$conn$sql;if!$retval{die数据表删除失败:.mysqli_error$conn;}echo数据表删除成功\n;mysqli_close$conn;执行成功后,我们使用以下命令,就看不到runoob_tbl表了mysqlshowtables;Emptyset
0.01secMySQL插入数据MySQL表中使用INSERTINTOSQL语句来插入数据你可以通过mysql命令提示窗口中向数据表中插入数据,或者通过PHP脚本来插入数据语法以下为向MySQL数据表插入数据通用的INSERTINTOSQL语法INSERTINTOtable_namefield1field
2...fieldNVALUESvalue1value
2...valueN;如果数据是字符型,必须使用单引号或者双引号,如value通过命令提示窗口插入数据以下我们将使用SQLINSERTINTO语句向MySQL数据表runoob_tbl插入数据实例以下实例中我们将向runoob_tbl表插入三条数据:root@host#mysql-uroot-ppassword;Enterpassword:*******mysqluseRUNOOB;DatabasechangedmysqlINSERTINTOrunoob_tbl-runoob_titlerunoob_authorsubmission_date-VALUES-学习PHP菜鸟教程NOW;QueryOK1rowsaffected1warnings
0.01secmysqlINSERTINTOrunoob_tbl-runoob_titlerunoob_authorsubmission_date-VALUES-学习MySQL菜鸟教程NOW;QueryOK1rowsaffected1warnings
0.01secmysqlINSERTINTOrunoob_tbl-runoob_titlerunoob_authorsubmission_date-VALUES-JAVA教程RUNOOB.COM2016-05-06;QueryOK1rowsaffected
0.00secmysql注意使用箭头标记-不是SQL语句的一部分,它仅仅表示一个新行,如果一条SQL语句太长,我们可以通过回车键来创建一个新行来编写SQL语句,SQL语句的命令结束符为分号;在以上实例中,我们并没有提供runoob_id的数据,因为该字段我们在创建表的时候已经设置它为AUTO_INCREMENT自动增加属性所以,该字段会自动递增而不需要我们去设置实例中NOW是一个MySQL函数,该函数返回日期和时间接下来我们可以通过以下语句查看数据表数据读取数据表select*fromrunoob_tbl;输出结果使用PHP脚本插入数据你可以使用PHP的mysqli_query函数来执行SQLINSERTINTO命令来插入数据该函数有两个参数,在执行成功时返回TRUE,否则返回FALSE语法mysqli_queryconnectionqueryresultmode;参数描述connection必需规定要使用的MySQL连接query必需,规定查询字符串resultmode可选一个常量可以是下列值中的任意一个MYSQLI_USE_RESULT(如果需要检索大量数据,请使用这个)MYSQLI_STORE_RESULT(默认)实例以下实例中程序接收用户输入的三个字段数据,并插入数据表中添加数据php$dbhost=localhost:3306;//mysql服务器主机地址$dbuser=root;//mysql用户名$dbpass=123456;//mysql用户名密码$conn=mysqli_connect$dbhost$dbuser$dbpass;if!$conn{die连接失败:.mysqli_error$conn;}echo连接成功br/;//设置编码,防止中文乱码mysqli_query$connsetnamesutf8;$runoob_title=学习Python;$runoob_author=RUNOOB.COM;$submission_date=2016-03-06;$sql=INSERTINTOrunoob_tbl.runoob_titlerunoob_authorsubmission_date.VALUES.$runoob_title$runoob_author$submission_date;mysqli_select_db$connRUNOOB;$retval=mysqli_query$conn$sql;if!$retval{die无法插入数据:.mysqli_error$conn;}echo数据插入成功\n;mysqli_close$conn;对于含有中文的数据插入,需要添加mysqli_query$connsetnamesutf8;语句接下来我们可以通过以下语句查看数据表数据读取数据表select*fromrunoob_tbl;输出结果MySQL查询数据MySQL数据库使用SQLSELECT语句来查询数据你可以通过mysql命令提示窗口中在数据库中查询数据,或者通过PHP脚本来查询数据语法以下为在MySQL数据库中查询数据通用的SELECT语法SELECTcolumn_namecolumn_nameFROMtable_name[WHEREClause][OFFSETM][LIMITN]查询语句中你可以使用一个或者多个表,表之间使用逗号分割,并使用WHERE语句来设定查询条件SELECT命令可以读取一条或者多条记录你可以使用星号(*)来代替其他字段,SELECT语句会返回表的所有字段数据你可以使用WHERE语句来包含任何条件你可以通过OFFSET指定SELECT语句开始查询的数据偏移量默认情况下偏移量为0你可以使用LIMIT属性来设定返回的记录数通过命令提示符获取数据以下实例我们将通过SQLSELECT命令来获取MySQL数据表runoob_tbl的数据实例以下实例将返回数据表runoob_tbl的所有记录:读取数据表select*fromrunoob_tbl;输出结果使用PHP脚本来获取数据使用PHP函数的mysqli_query及SQLSELECT命令来获取数据该函数用于执行SQL命令,然后通过PHP函数mysqli_fetch_array来使用或输出所有查询的数据mysqli_fetch_array函数从结果集中取得一行作为关联数组,或数字数组,或二者兼有返回根据从结果集取得的行生成的数组,如果没有更多行则返回false以下实例为从数据表runoob_tbl中读取所有记录实例尝试以下实例来显示数据表runoob_tbl的所有记录使用mysqli_fetch_arrayMYSQL_ASSOC参数获取数据php$dbhost=localhost:3306;//mysql服务器主机地址$dbuser=root;//mysql用户名$dbpass=123456;//mysql用户名密码$conn=mysqli_connect$dbhost$dbuser$dbpass;if!$conn{die连接失败:.mysqli_error$conn;}//设置编码,防止中文乱码mysqli_query$connsetnamesutf8;$sql=SELECTrunoob_idrunoob_titlerunoob_authorsubmission_dateFROMrunoob_tbl;mysqli_select_db$connRUNOOB;$retval=mysqli_query$conn$sql;if!$retval{die无法读取数据:.mysqli_error$conn;}echoh2菜鸟教程mysqli_fetch_array测试h2;echotableborder=1trtd教程ID/tdtd标题/tdtd作者/tdtd提交日期/td/tr;while$row=mysqli_fetch_array$retvalMYSQL_ASSOC{echotrtd{$row[runoob_id]}/td.td{$row[runoob_title]}/td.td{$row[runoob_author]}/td.td{$row[submission_date]}/td./tr;}echo/table;mysqli_close$conn;输出结果如下所示以上实例中,读取的每行记录赋值给变量$row,然后再打印出每个值注意记住如果你需要在字符串中使用变量,请将变量置于花括号在上面的例子中,PHPmysqli_fetch_array函数第二个参数为MYSQL_ASSOC,设置该参数查询结果返回关联数组,你可以使用字段名称来作为数组的索引PHP提供了另外一个函数mysqli_fetch_assoc该函数从结果集中取得一行作为关联数组返回根据从结果集取得的行生成的关联数组,如果没有更多行,则返回false实例尝试以下实例,该实例使用了mysqli_fetch_assoc函数来输出数据表runoob_tbl的所有记录使用mysqli_fetch_assoc获取数据php$dbhost=localhost:3306;//mysql服务器主机地址$dbuser=root;//mysql用户名$dbpass=123456;//mysql用户名密码$conn=mysqli_connect$dbhost$dbuser$dbpass;if!$conn{die连接失败:.mysqli_error$conn;}//设置编码,防止中文乱码mysqli_query$connsetnamesutf8;$sql=SELECTrunoob_idrunoob_titlerunoob_authorsubmission_dateFROMrunoob_tbl;mysqli_select_db$connRUNOOB;$retval=mysqli_query$conn$sql;if!$retval{die无法读取数据:.mysqli_error$conn;}echoh2菜鸟教程mysqli_fetch_assoc测试h2;echotableborder=1trtd教程ID/tdtd标题/tdtd作者/tdtd提交日期/td/tr;while$row=mysqli_fetch_assoc$retval{echotrtd{$row[runoob_id]}/td.td{$row[runoob_title]}/td.td{$row[runoob_author]}/td.td{$row[submission_date]}/td./tr;}echo/table;mysqli_close$conn;输出结果如下所示你也可以使用常量MYSQL_NUM作为PHPmysqli_fetch_array函数的第二个参数,返回数字数组实例以下实例使用MYSQL_NUM参数显示数据表runoob_tbl的所有记录:使用mysqli_fetch_arrayMYSQL_NUM参数获取数据php$dbhost=localhost:3306;//mysql服务器主机地址$dbuser=root;//mysql用户名$dbpass=123456;//mysql用户名密码$conn=mysqli_connect$dbhost$dbuser$dbpass;if!$conn{die连接失败:.mysqli_error$conn;}//设置编码,防止中文乱码mysqli_query$connsetnamesutf8;$sql=SELECTrunoob_idrunoob_titlerunoob_authorsubmission_dateFROMrunoob_tbl;mysqli_select_db$connRUNOOB;$retval=mysqli_query$conn$sql;if!$retval{die无法读取数据:.mysqli_error$conn;}echoh2菜鸟教程mysqli_fetch_array测试h2;echotableborder=1trtd教程ID/tdtd标题/tdtd作者/tdtd提交日期/td/tr;while$row=mysqli_fetch_array$retvalMYSQL_NUM{echotrtd{$row
[0]}/td.td{$row
[1]}/td.td{$row
[2]}/td.td{$row
[3]}/td./tr;}echo/table;mysqli_close$conn;输出结果如下所示以上三个实例输出结果都一样内存释放在我们执行完SELECT语句后,释放游标内存是一个很好的习惯可以通过PHP函数mysqli_free_result来实现内存的释放以下实例演示了该函数的使用方法实例尝试以下实例:使用mysqli_free_result释放内存php$dbhost=localhost:3306;//mysql服务器主机地址$dbuser=root;//mysql用户名$dbpass=123456;//mysql用户名密码$conn=mysqli_connect$dbhost$dbuser$dbpass;if!$conn{die连接失败:.mysqli_error$conn;}//设置编码,防止中文乱码mysqli_query$connsetnamesutf8;$sql=SELECTrunoob_idrunoob_titlerunoob_authorsubmission_dateFROMrunoob_tbl;mysqli_select_db$connRUNOOB;$retval=mysqli_query$conn$sql;if!$retval{die无法读取数据:.mysqli_error$conn;}echoh2菜鸟教程mysqli_fetch_array测试h2;echotableborder=1trtd教程ID/tdtd标题/tdtd作者/tdtd提交日期/td/tr;while$row=mysqli_fetch_array$retvalMYSQL_NUM{echotrtd{$row
[0]}/td.td{$row
[1]}/td.td{$row
[2]}/td.td{$row
[3]}/td./tr;}echo/table;//释放内存mysqli_free_result$retval;mysqli_close$conn;输出结果如下所示MySQLWHERE子句我们知道从MySQL表中使用SQLSELECT语句来读取数据如需有条件地从表中选取数据,可将WHERE子句添加到SELECT语句中语法以下是SQLSELECT语句使用WHERE子句从数据表中读取数据的通用语法SELECTfield1field
2...fieldNFROMtable_name1table_name
2...[WHEREcondition1[AND[OR]]condition
2.....查询语句中你可以使用一个或者多个表,表之间使用逗号分割,并使用WHERE语句来设定查询条件你可以在WHERE子句中指定任何条件你可以使用AND或者OR指定一个或多个条件WHERE子句也可以运用于SQL的DELETE或者UPDATE命令WHERE子句类似于程序语言中的if条件,根据MySQL表中的字段值来读取指定的数据以下为操作符列表,可用于WHERE子句中下表中实例假定A为10B为20操作符描述实例=等号,检测两个值是否相等,如果相等返回trueA=B返回false!=不等于,检测两个值是否相等,如果不相等返回trueA!=B返回true大于号,检测左边的值是否大于右边的值如果左边的值大于右边的值返回trueAB返回false小于号,检测左边的值是否小于右边的值如果左边的值小于右边的值返回trueAB返回true=大于等于号,检测左边的值是否大于或等于右边的值如果左边的值大于或等于右边的值返回trueA=B返回false=小于等于号,检测左边的值是否小于于或等于右边的值如果左边的值小于或等于右边的值返回trueA=B返回true如果我们想再MySQL数据表中读取指定的数据,WHERE子句是非常有用的使用主键来作为WHERE子句的条件查询是非常快速的如果给定的条件在表中没有任何匹配的记录,那么查询不会返回任何数据从命令提示符中读取数据我们将在SQLSELECT语句使用WHERE子句来读取MySQL数据表runoob_tbl中的数据实例以下实例将读取runoob_tbl表中runoob_author字段值为Sanjay的所有记录SQLSELECTWHERE子句SELECT*fromrunoob_tblWHERErunoob_author=菜鸟教程;输出结果MySQL的WHERE子句的字符串比较是不区分大小写的你可以使用BINARY关键字来设定WHERE子句的字符串比较是区分大小写的如下实例:BINARY关键字mysqlSELECT*fromrunoob_tblWHEREBINARYrunoob_author=runoob.com;Emptyset
0.01secmysqlSELECT*fromrunoob_tblWHEREBINARYrunoob_author=RUNOOB.COM;+-----------+---------------+---------------+-----------------+|runoob_id|runoob_title|runoob_author|submission_date|+-----------+---------------+---------------+-----------------+|3|JAVA教程|RUNOOB.COM|2016-05-06||4|学习Python|RUNOOB.COM|2016-03-06|+-----------+---------------+---------------+-----------------+2rowsinset
0.01sec实例中使用了BINARY关键字,是区分大小写的,所以runoob_author=runoob.com的查询条件是没有数据的使用PHP脚本读取数据你可以使用PHP函数的mysqli_query及相同的SQLSELECT带上WHERE子句的命令来获取数据该函数用于执行SQL命令,然后通过PHP函数mysqli_fetch_array来输出所有查询的数据实例以下实例将从runoob_tbl表中返回使用runoob_author字段值为RUNOOB.COM的记录MySQLWHERE子句测试php$dbhost=localhost:3306;//mysql服务器主机地址$dbuser=root;//mysql用户名$dbpass=123456;//mysql用户名密码$conn=mysqli_connect$dbhost$dbuser$dbpass;if!$conn{die连接失败:.mysqli_error$conn;}//设置编码,防止中文乱码mysqli_query$connsetnamesutf8;//读取runoob_author为RUNOOB.COM的数据$sql=SELECTrunoob_idrunoob_titlerunoob_authorsubmission_dateFROMrunoob_tblWHERErunoob_author=RUNOOB.COM;mysqli_select_db$connRUNOOB;$retval=mysqli_query$conn$sql;if!$retval{die无法读取数据:.mysqli_error$conn;}echoh2菜鸟教程MySQLWHERE子句测试h2;echotableborder=1trtd教程ID/tdtd标题/tdtd作者/tdtd提交日期/td/tr;while$row=mysqli_fetch_array$retvalMYSQL_ASSOC{echotrtd{$row[runoob_id]}/td.td{$row[runoob_title]}/td.td{$row[runoob_author]}/td.td{$row[submission_date]}/td./tr;}echo/table;//释放内存mysqli_free_result$retval;mysqli_close$conn;输出结果如下所示MySQLUPDATE查询如果我们需要修改或更新MySQL中的数据,我们可以使用SQLUPDATE命令来操作.语法以下是UPDATE命令修改MySQL数据表数据的通用SQL语法UPDATEtable_nameSETfield1=new-value1field2=new-value2[WHEREClause]你可以同时更新一个或多个字段你可以在WHERE子句中指定任何条件你可以在一个单独表中同时更新数据当你需要更新数据表中指定行的数据时WHERE子句是非常有用的通过命令提示符更新数据以下我们将在SQLUPDATE命令使用WHERE子句来更新runoob_tbl表中指定的数据实例以下实例将更新数据表中runoob_id为3的runoob_title字段值SQLUPDATE语句mysqlUPDATErunoob_tblSETrunoob_title=学习C++WHERErunoob_id=3;QueryOK1rowsaffected
0.01secmysqlSELECT*fromrunoob_tblWHERErunoob_id=3;+-----------+--------------+---------------+-----------------+|runoob_id|runoob_title|runoob_author|submission_date|+-----------+--------------+---------------+-----------------+|3|学习C++|RUNOOB.COM|2016-05-06|+-----------+--------------+---------------+-----------------+1rowsinset
0.01sec从结果上看,runoob_id为3的runoob_title已被修改使用PHP脚本更新数据PHP中使用函数mysqli_query来执行SQL语句,你可以在SQLUPDATE语句中使用或者不使用WHERE子句注意不使用WHERE子句将数据表的全部数据进行更新,所以要慎重该函数与在mysql命令提示符中执行SQL语句的效果是一样的实例以下实例将更新runoob_id为3的runoob_title字段的数据MySQLUPDATE语句测试php$dbhost=localhost:3306;//mysql服务器主机地址$dbuser=root;//mysql用户名$dbpass=123456;//mysql用户名密码$conn=mysqli_connect$dbhost$dbuser$dbpass;if!$conn{die连接失败:.mysqli_error$conn;}//设置编码,防止中文乱码mysqli_query$connsetnamesutf8;$sql=UPDATErunoob_tblSETrunoob_title=学习PythonWHERErunoob_id=3;mysqli_select_db$connRUNOOB;$retval=mysqli_query$conn$sql;if!$retval{die无法更新数据:.mysqli_error$conn;}echo数据更新成功!;mysqli_close$conn;MySQLWHERE子句MySQLDELETE语句笔记列表
1. General 769***870@qq.comupdate语句可用来修改表中的数据简单来说基本的使用形式为:update表名称set列名称=新值where更新条件;以下是在表students中的实例:将id为5的手机号改为默认的-:updatestudentssettel=defaultwhereid=5;将所有人的年龄增加1:updatestudentssetage=age+1;将手机号为13288097888的姓名改为小明年龄改为19:updatestudentssetname=小明age=19wheretel=13288097888;MySQLDELETE语句你可以使用SQL的DELETEFROM命令来删除MySQL数据表中的记录你可以在mysql命令提示符或PHP脚本中执行该命令语法以下是SQLDELETE语句从MySQL数据表中删除数据的通用语法DELETEFROMtable_name[WHEREClause]如果没有指定WHERE子句,MySQL表中的所有记录将被删除你可以在WHERE子句中指定任何条件您可以在单个表中一次性删除记录当你想删除数据表中指定的记录时WHERE子句是非常有用的从命令行中删除数据这里我们将在SQLDELETE命令中使用WHERE子句来删除MySQL数据表runoob_tbl所选的数据实例以下实例将删除runoob_tbl表中runoob_id为3的记录SQLUPDATE语句mysqluseRUNOOB;DatabasechangedmysqlDELETEFROMrunoob_tblWHERErunoob_id=3;QueryOK1rowaffected
0.23sec使用PHP脚本删除数据PHP使用mysqli_query函数来执行SQL语句,你可以在SQLDELETE命令中使用或不使用WHERE子句该函数与mysql命令符执行SQL命令的效果是一样的实例以下PHP实例将删除runoob_tbl表中runoob_id为3的记录:MySQLDELETE子句测试php$dbhost=localhost:3306;//mysql服务器主机地址$dbuser=root;//mysql用户名$dbpass=123456;//mysql用户名密码$conn=mysqli_connect$dbhost$dbuser$dbpass;if!$conn{die连接失败:.mysqli_error$conn;}//设置编码,防止中文乱码mysqli_query$connsetnamesutf8;$sql=DELETEFROMrunoob_tblWHERErunoob_id=3;mysqli_select_db$connRUNOOB;$retval=mysqli_query$conn$sql;if!$retval{die无法删除数据:.mysqli_error$conn;}echo数据删除成功!;mysqli_close$conn;MySQLUPDATE查询MySQLLIKE子句笔记列表
1. General 769***870@qq.comdelete语句用于删除表中的数据基本用法为:deletefrom表名称where删除条件;以下是在表students中的实例:删除id为3的行:deletefromstudentswhereid=3;删除所有年龄小于21岁的数据:deletefromstudentswhereage20;删除表中的所有数据:deletefromstudents;MySQLLIKE子句我们知道在MySQL中使用SQLSELECT命令来读取数据,同时我们可以在SELECT语句中使用WHERE子句来获取指定的记录WHERE子句中可以使用等号=来设定获取数据的条件,如runoob_author=RUNOOB.COM但是有时候我们需要获取runoob_author字段含有COM字符的所有记录,这时我们就需要在WHERE子句中使用SQLLIKE子句SQLLIKE子句中使用百分号%字符来表示任意字符,类似于UNIX或正则表达式中的星号*如果没有使用百分号%LIKE子句与等号=的效果是一样的语法以下是SQLSELECT语句使用LIKE子句从数据表中读取数据的通用语法SELECTfield1field
2...fieldNFROMtable_nameWHEREfield1LIKEcondition1[AND[OR]]filed2=somevalue你可以在WHERE子句中指定任何条件你可以在WHERE子句中使用LIKE子句你可以使用LIKE子句代替等号=LIKE通常与%一同使用,类似于一个元字符的搜索你可以使用AND或者OR指定一个或多个条件你可以在DELETE或UPDATE命令中使用WHERE...LIKE子句来指定条件在命令提示符中使用LIKE子句以下我们将在SQLSELECT命令中使用WHERE...LIKE子句来从MySQL数据表runoob_tbl中读取数据实例以下是我们将runoob_tbl表中获取runoob_author字段中以COM为结尾的的所有记录SQLUPDATE语句mysqluseRUNOOB;DatabasechangedmysqlSELECT*fromrunoob_tblWHERErunoob_authorLIKE%COM;+-----------+---------------+---------------+-----------------+|runoob_id|runoob_title|runoob_author|submission_date|+-----------+---------------+---------------+-----------------+|3|学习Java|RUNOOB.COM|2015-05-01||4|学习Python|RUNOOB.COM|2016-03-06|+-----------+---------------+---------------+-----------------+2rowsinset
0.01sec在PHP脚本中使用LIKE子句你可以使用PHP函数的mysqli_query及相同的SQLSELECT带上WHERE...LIKE子句的命令来获取数据该函数用于执行SQL命令,然后通过PHP函数mysqli_fetch_assoc来输出所有查询的数据但是如果是DELETE或者UPDATE中使用WHERE...LIKE子句的SQL语句,则无需使用mysqli_fetch_array函数实例以下是我们使用PHP脚本在runoob_tbl表中读取runoob_author字段中以COM为结尾的的所有记录MySQLDELETE子句测试php$dbhost=localhost:3306;//mysql服务器主机地址$dbuser=root;//mysql用户名$dbpass=123456;//mysql用户名密码$conn=mysqli_connect$dbhost$dbuser$dbpass;if!$conn{die连接失败:.mysqli_error$conn;}//设置编码,防止中文乱码mysqli_query$connsetnamesutf8;$sql=SELECTrunoob_idrunoob_titlerunoob_authorsubmission_dateFROMrunoob_tblWHERErunoob_authorLIKE%COM;mysqli_select_db$connRUNOOB;$retval=mysqli_query$conn$sql;if!$retval{die无法读取数据:.mysqli_error$conn;}echoh2菜鸟教程mysqli_fetch_array测试h2;echotableborder=1trtd教程ID/tdtd标题/tdtd作者/tdtd提交日期/td/tr;while$row=mysqli_fetch_array$retvalMYSQL_ASSOC{echotrtd{$row[runoob_id]}/td.td{$row[runoob_title]}/td.td{$row[runoob_author]}/td.td{$row[submission_date]}/td./tr;}echo/table;mysqli_close$conn;输出结果如下图所示MySQLUNION操作符本教程为大家介绍MySQLUNION操作符的语法和实例描述MySQLUNION操作符用于连接两个以上的SELECT语句的结果组合到一个结果集合中多个SELECT语句会删除重复的数据语法MySQLUNION操作符语法格式SELECTexpression1expression
2...expression_nFROMtables[WHEREconditions]UNION[ALL|DISTINCT]SELECTexpression1expression
2...expression_nFROMtables[WHEREconditions];参数expression1expression
2...expression_n:要检索的列tables:要检索的数据表WHEREconditions:可选,检索条件DISTINCT:可选,删除结果集中重复的数据默认情况下UNION操作符已经删除了重复数据,所以DISTINCT修饰符对结果没啥影响ALL:可选,返回所有结果集,包含重复数据演示数据库在本教程中,我们将使用RUNOOB样本数据库下面是选自Websites表的数据mysqlSELECT*FROMWebsites;+----+--------------+---------------------------+-------+---------+|id|name|url|alexa|country|+----+--------------+---------------------------+-------+---------+|1|Google|https://www.google.cm/|1|USA||2|淘宝|https://www.taobao.com/|13|CN||3|菜鸟教程|http://www.runoob.com/|4689|CN||4|微博|http://weibo.com/|20|CN||5|Facebook|https://www.facebook.com/|3|USA||7|stackoverflow|http://stackoverflow.com/|0|IND|+----+---------------+---------------------------+-------+---------+下面是appsAPP的数据mysqlSELECT*FROMapps;+----+------------+-------------------------+---------+|id|app_name|url|country|+----+------------+-------------------------+---------+|1|QQAPP|http://im.qq.com/|CN||2|微博APP|http://weibo.com/|CN||3|淘宝APP|https://www.taobao.com/|CN|+----+------------+-------------------------+---------+3rowsinset
0.00secSQLUNION实例下面的SQL语句从Websites和apps表中选取所有不同的country(只有不同的值)实例SELECTcountryFROMWebsitesUNIONSELECTcountryFROMappsORDERBYcountry;执行以上SQL输出结果如下注释UNION不能用于列出两个表中所有的country如果一些网站和APP来自同一个国家,每个国家只会列出一次UNION只会选取不同的值请使用UNIONALL来选取重复的值!SQLUNIONALL实例下面的SQL语句使用UNIONALL从Websites和apps表中选取所有的country(也有重复的值)实例SELECTcountryFROMWebsitesUNIONALLSELECTcountryFROMappsORDERBYcountry;执行以上SQL输出结果如下带有WHERE的SQLUNIONALL下面的SQL语句使用UNIONALL从Websites和apps表中选取所有的中国CN的数据(也有重复的值)实例SELECTcountrynameFROMWebsitesWHEREcountry=CNUNIONALLSELECTcountryapp_nameFROMappsWHEREcountry=CNORDERBYcountry;执行以上SQL输出结果如下MySQL排序我们知道从MySQL表中使用SQLSELECT语句来读取数据如果我们需要对读取的数据进行排序,我们就可以使用MySQL的ORDERBY子句来设定你想按哪个字段哪种方式来进行排序,再返回搜索结果语法以下是SQLSELECT语句使用ORDERBY子句将查询数据排序后再返回数据SELECTfield1field
2...fieldNtable_name1table_name
2...ORDERBYfield1[field
2...][ASC[DESC]]你可以使用任何字段来作为排序的条件,从而返回排序后的查询结果你可以设定多个字段来排序你可以使用ASC或DESC关键字来设置查询结果是按升序或降序排列默认情况下,它是按升序排列你可以添加WHERE...LIKE子句来设置条件在命令提示符中使用ORDERBY子句以下将在SQLSELECT语句中使用ORDERBY子句来读取MySQL数据表runoob_tbl中的数据实例尝试以下实例,结果将按升序及降序排列SQL排序mysqluseRUNOOB;DatabasechangedmysqlSELECT*fromrunoob_tblORDERBYsubmission_dateASC;+-----------+---------------+---------------+-----------------+|runoob_id|runoob_title|runoob_author|submission_date|+-----------+---------------+---------------+-----------------+|3|学习Java|RUNOOB.COM|2015-05-01||4|学习Python|RUNOOB.COM|2016-03-06||1|学习PHP|菜鸟教程|2017-04-12||2|学习MySQL|菜鸟教程|2017-04-12|+-----------+---------------+---------------+-----------------+4rowsinset
0.01secmysqlSELECT*fromrunoob_tblORDERBYsubmission_dateDESC;+-----------+---------------+---------------+-----------------+|runoob_id|runoob_title|runoob_author|submission_date|+-----------+---------------+---------------+-----------------+|1|学习PHP|菜鸟教程|2017-04-12||2|学习MySQL|菜鸟教程|2017-04-12||4|学习Python|RUNOOB.COM|2016-03-06||3|学习Java|RUNOOB.COM|2015-05-01|+-----------+---------------+---------------+-----------------+4rowsinset
0.01sec读取runoob_tbl表中所有数据并按submission_date字段的升序排列在PHP脚本中使用ORDERBY子句你可以使用PHP函数的mysqli_query及相同的SQLSELECT带上ORDERBY子句的命令来获取数据该函数用于执行SQL命令,然后通过PHP函数mysqli_fetch_array来输出所有查询的数据实例尝试以下实例,查询后的数据按submission_date字段的降序排列后返回MySQLORDERBY测试php$dbhost=localhost:3306;//mysql服务器主机地址$dbuser=root;//mysql用户名$dbpass=123456;//mysql用户名密码$conn=mysqli_connect$dbhost$dbuser$dbpass;if!$conn{die连接失败:.mysqli_error$conn;}//设置编码,防止中文乱码mysqli_query$connsetnamesutf8;$sql=SELECTrunoob_idrunoob_titlerunoob_authorsubmission_dateFROMrunoob_tblORDERBYsubmission_dateASC;mysqli_select_db$connRUNOOB;$retval=mysqli_query$conn$sql;if!$retval{die无法读取数据:.mysqli_error$conn;}echoh2菜鸟教程MySQLORDERBY测试h2;echotableborder=1trtd教程ID/tdtd标题/tdtd作者/tdtd提交日期/td/tr;while$row=mysqli_fetch_array$retvalMYSQL_ASSOC{echotrtd{$row[runoob_id]}/td.td{$row[runoob_title]}/td.td{$row[runoob_author]}/td.td{$row[submission_date]}/td./tr;}echo/table;mysqli_close$conn;输出结果如下图所示MySQLGROUPBY语句GROUPBY语句根据一个或多个列对结果集进行分组在分组的列上我们可以使用COUNTSUMAVG等函数GROUPBY语法SELECTcolumn_namefunctioncolumn_nameFROMtable_nameWHEREcolumn_nameoperatorvalueGROUPBYcolumn_name;实例演示本章节实例使用到了以下表结构及数据,使用前我们可以先将以下数据导入数据库中SETNAMESutf8;SETFOREIGN_KEY_CHECKS=0;--------------------------------Tablestructurefor`employee_tbl`------------------------------DROPTABLEIFEXISTS`employee_tbl`;CREATETABLE`employee_tbl``id`int11NOTNULL`name`char10NOTNULLDEFAULT`date`datetimeNOTNULL`singin`tinyint4NOTNULLDEFAULT0COMMENT登录次数PRIMARYKEY`id`ENGINE=InnoDBDEFAULTCHARSET=utf8;--------------------------------Recordsof`employee_tbl`------------------------------BEGIN;INSERTINTO`employee_tbl`VALUES1小明2016-04-2215:25:3312小王2016-04-2015:25:4733小丽2016-04-1915:26:0224小王2016-04-0715:26:1445小明2016-04-1115:26:4046小明2016-04-0415:26:542;COMMIT;SETFOREIGN_KEY_CHECKS=1;导入成功后,执行以下SQL语句mysqlsetnamesutf8;mysqlSELECT*FROMemployee_tbl;+----+--------+---------------------+--------+|id|name|date|singin|+----+--------+---------------------+--------+|1|小明|2016-04-2215:25:33|1||2|小王|2016-04-2015:25:47|3||3|小丽|2016-04-1915:26:02|2||4|小王|2016-04-0715:26:14|4||5|小明|2016-04-1115:26:40|4||6|小明|2016-04-0415:26:54|2|+----+--------+---------------------+--------+6rowsinset
0.00sec接下来我们使用GROUPBY语句将数据表按名字进行分组,并统计每个人有多少条记录mysqlSELECTnameCOUNT*FROMemployee_tblGROUPBYname;+--------+----------+|name|COUNT*|+--------+----------+|小丽|1||小明|3||小王|2|+--------+----------+3rowsinset
0.01sec使用WITHROLLUPWITHROLLUP可以实现在分组统计数据基础上再进行相同的统计(SUMAVGCOUNT…)例如我们将以上的数据表按名字进行分组,再统计每个人登录的次数mysqlSELECTnameSUMsinginassingin_countFROMemployee_tblGROUPBYnameWITHROLLUP;+--------+--------------+|name|singin_count|+--------+--------------+|小丽|2||小明|7||小王|7||NULL|16|+--------+--------------+4rowsinset
0.00sec其中记录NULL表示所有人的登录次数我们可以使用coalesce来设置一个可以取代NUll的名称,coalesce语法selectcoalesceabc;参数说明如果a==null则选择b;如果b==null则选择c;如果a!=null则选择a;如果abc都为null,则返回为null(没意义)以下实例中如果名字为空我们使用总数代替mysqlSELECTcoalescename总数SUMsinginassingin_countFROMemployee_tblGROUPBYnameWITHROLLUP;+--------------------------+--------------+|coalescename总数|singin_count|+--------------------------+--------------+|小丽|2||小明|7||小王|7||总数|16|+--------------------------+--------------+4rowsinset
0.01secMysql连接的使用在前几章节中,我们已经学会了如果在一张表中读取数据,这是相对简单的,但是在真正的应用中经常需要从多个数据表中读取数据本章节我们将向大家介绍如何使用MySQL的JOIN在两个或多个表中查询数据你可以在SELECTUPDATE和DELETE语句中使用Mysql的JOIN来联合多表查询JOIN按照功能大致分为如下三类INNERJOIN(内连接或等值连接)获取两个表中字段匹配关系的记录LEFTJOIN(左连接)获取左表所有记录,即使右表没有对应匹配的记录RIGHTJOIN(右连接)与LEFTJOIN相反,用于获取右表所有记录,即使左表没有对应匹配的记录本章节使用的数据库结构及数据下载runoob-mysql-join-test.sql在命令提示符中使用INNERJOIN我们在RUNOOB数据库中有两张表tcount_tbl和runoob_tbl两张数据表数据如下实例尝试以下实例测试实例数据mysqluseRUNOOB;DatabasechangedmysqlSELECT*FROMtcount_tbl;+---------------+--------------+|runoob_author|runoob_count|+---------------+--------------+|菜鸟教程|10||RUNOOB.COM|20||Google|22|+---------------+--------------+3rowsinset
0.01secmysqlSELECT*fromrunoob_tbl;+-----------+---------------+---------------+-----------------+|runoob_id|runoob_title|runoob_author|submission_date|+-----------+---------------+---------------+-----------------+|1|学习PHP|菜鸟教程|2017-04-12||2|学习MySQL|菜鸟教程|2017-04-12||3|学习Java|RUNOOB.COM|2015-05-01||4|学习Python|RUNOOB.COM|2016-03-06||5|学习C|FK|2017-04-05|+-----------+---------------+---------------+-----------------+5rowsinset
0.01sec接下来我们就使用MySQL的INNERJOIN也可以省略INNER使用JOIN,效果一样来连接以上两张表来读取runoob_tbl表中所有runoob_author字段在tcount_tbl表对应的runoob_count字段值INNERJOINmysqlSELECTa.runoob_ida.runoob_authorb.runoob_countFROMrunoob_tblaINNERJOINtcount_tblbONa.runoob_author=b.runoob_author;+-------------+-----------------+----------------+|a.runoob_id|a.runoob_author|b.runoob_count|+-------------+-----------------+----------------+|1|菜鸟教程|10||2|菜鸟教程|10||3|RUNOOB.COM|20||4|RUNOOB.COM|20|+-------------+-----------------+----------------+4rowsinset
0.00sec以上SQL语句等价于WHERE子句mysqlSELECTa.runoob_ida.runoob_authorb.runoob_countFROMrunoob_tblatcount_tblbWHEREa.runoob_author=b.runoob_author;+-------------+-----------------+----------------+|a.runoob_id|a.runoob_author|b.runoob_count|+-------------+-----------------+----------------+|1|菜鸟教程|10||2|菜鸟教程|10||3|RUNOOB.COM|20||4|RUNOOB.COM|20|+-------------+-----------------+----------------+4rowsinset
0.01secMySQLLEFTJOINMySQLleftjoin与join有所不同MySQLLEFTJOIN会读取左边数据表的全部数据,即便右边表无对应数据实例尝试以下实例,以runoob_tbl为左表,tcount_tbl为右表,理解MySQLLEFTJOIN的应用LEFTJOINmysqlSELECTa.runoob_ida.runoob_authorb.runoob_countFROMrunoob_tblaLEFTJOINtcount_tblbONa.runoob_author=b.runoob_author;+-------------+-----------------+----------------+|a.runoob_id|a.runoob_author|b.runoob_count|+-------------+-----------------+----------------+|1|菜鸟教程|10||2|菜鸟教程|10||3|RUNOOB.COM|20||4|RUNOOB.COM|20||5|FK|NULL|+-------------+-----------------+----------------+5rowsinset
0.01sec以上实例中使用了LEFTJOIN,该语句会读取左边的数据表runoob_tbl的所有选取的字段数据,即便在右侧表tcount_tbl中没有对应的runoob_author字段值MySQLRIGHTJOINMySQLRIGHTJOIN会读取右边数据表的全部数据,即便左边边表无对应数据实例尝试以下实例,以runoob_tbl为左表,tcount_tbl为右表,理解MySQLRIGHTJOIN的应用RIGHTJOINmysqlSELECTa.runoob_ida.runoob_authorb.runoob_countFROMrunoob_tblaRIGHTJOINtcount_tblbONa.runoob_author=b.runoob_author;+-------------+-----------------+----------------+|a.runoob_id|a.runoob_author|b.runoob_count|+-------------+-----------------+----------------+|1|菜鸟教程|10||2|菜鸟教程|10||3|RUNOOB.COM|20||4|RUNOOB.COM|20||NULL|NULL|22|+-------------+-----------------+----------------+5rowsinset
0.01sec以上实例中使用了RIGHTJOIN,该语句会读取右边的数据表tcount_tbl的所有选取的字段数据,即便在左侧表runoob_tbl中没有对应的runoob_author字段值在PHP脚本中使用JOINPHP中使用mysqli_query函数来执行SQL语句,你可以使用以上的相同的SQL语句作为mysqli_query函数的参数尝试如下实例:MySQLORDERBY测试php$dbhost=localhost:3306;//mysql服务器主机地址$dbuser=root;//mysql用户名$dbpass=123456;//mysql用户名密码$conn=mysqli_connect$dbhost$dbuser$dbpass;if!$conn{die连接失败:.mysqli_error$conn;}//设置编码,防止中文乱码mysqli_query$connsetnamesutf8;$sql=SELECTa.runoob_ida.runoob_authorb.runoob_countFROMrunoob_tblaINNERJOINtcount_tblbONa.runoob_author=b.runoob_author;mysqli_select_db$connRUNOOB;$retval=mysqli_query$conn$sql;if!$retval{die无法读取数据:.mysqli_error$conn;}echoh2菜鸟教程MySQLJOIN测试h2;echotableborder=1trtd教程ID/tdtd作者/tdtd登陆次数/td/tr;while$row=mysqli_fetch_array$retvalMYSQL_ASSOC{echotrtd{$row[runoob_id]}/td.td{$row[runoob_author]}/td.td{$row[runoob_count]}/td./tr;}echo/table;mysqli_close$conn;输出结果如下图所示MySQLNULL值处理我们已经知道MySQL使用SQLSELECT命令及WHERE子句来读取数据表中的数据但是当提供的查询条件字段为NULL时,该命令可能就无法正常工作为了处理这种情况,MySQL提供了三大运算符:ISNULL:当列的值是NULL此运算符返回trueISNOTNULL:当列的值不为NULL运算符返回true=: 比较操作符(不同于=运算符),当比较的的两个值为NULL时返回true关于NULL的条件比较运算是比较特殊的你不能使用=NULL或!=NULL在列中查找NULL值在MySQL中,NULL值与任何其它值的比较(即使是NULL)永远返回false,即NULL=NULL返回falseMySQL中处理NULL使用ISNULL和ISNOTNULL运算符在命令提示符中使用NULL值以下实例中假设数据库RUNOOB中的表runoob_test_tbl含有两列runoob_author和runoob_countrunoob_count中设置插入NULL值实例尝试以下实例:创建数据表runoob_test_tblroot@host#mysql-uroot-ppassword;Enterpassword:*******mysqluseRUNOOB;Databasechangedmysqlcreatetablerunoob_test_tbl--runoob_authorvarchar40NOTNULL-runoob_countINT-;QueryOK0rowsaffected
0.05secmysqlINSERTINTOrunoob_test_tblrunoob_authorrunoob_countvaluesRUNOOB20;mysqlINSERTINTOrunoob_test_tblrunoob_authorrunoob_countvalues菜鸟教程NULL;mysqlINSERTINTOrunoob_test_tblrunoob_authorrunoob_countvaluesGoogleNULL;mysqlINSERTINTOrunoob_test_tblrunoob_authorrunoob_countvaluesFK20;mysqlSELECT*fromrunoob_test_tbl;+---------------+--------------+|runoob_author|runoob_count|+---------------+--------------+|RUNOOB|20||菜鸟教程|NULL||Google|NULL||FK|20|+---------------+--------------+4rowsinset
0.01sec以下实例中你可以看到=和!=运算符是不起作用的mysqlSELECT*FROMrunoob_test_tblWHERErunoob_count=NULL;Emptyset
0.00secmysqlSELECT*FROMrunoob_test_tblWHERErunoob_count!=NULL;Emptyset
0.01sec查找数据表中runoob_test_tbl列是否为NULL,必须使用ISNULL和ISNOTNULL,如下实例mysqlSELECT*FROMrunoob_test_tblWHERErunoob_countISNULL;+---------------+--------------+|runoob_author|runoob_count|+---------------+--------------+|菜鸟教程|NULL||Google|NULL|+---------------+--------------+2rowsinset
0.01secmysqlSELECT*fromrunoob_test_tblWHERErunoob_countISNOTNULL;+---------------+--------------+|runoob_author|runoob_count|+---------------+--------------+|RUNOOB|20||FK|20|+---------------+--------------+2rowsinset
0.01sec使用PHP脚本处理NULL值PHP脚本中你可以在if...else语句来处理变量是否为空,并生成相应的条件语句以下实例中PHP设置了$runoob_count变量,然后使用该变量与数据表中的runoob_count字段进行比较MySQLORDERBY测试php$dbhost=localhost:3306;//mysql服务器主机地址$dbuser=root;//mysql用户名$dbpass=123456;//mysql用户名密码$conn=mysqli_connect$dbhost$dbuser$dbpass;if!$conn{die连接失败:.mysqli_error$conn;}//设置编码,防止中文乱码mysqli_query$connsetnamesutf8;ifisset$runoob_count{$sql=SELECTrunoob_authorrunoob_countFROMrunoob_test_tblWHERErunoob_count=$runoob_count;}else{$sql=SELECTrunoob_authorrunoob_countFROMrunoob_test_tblWHERErunoob_countISNULL;}mysqli_select_db$connRUNOOB;$retval=mysqli_query$conn$sql;if!$retval{die无法读取数据:.mysqli_error$conn;}echoh2菜鸟教程ISNULL测试h2;echotableborder=1trtd作者/tdtd登陆次数/td/tr;while$row=mysqli_fetch_array$retvalMYSQL_ASSOC{echotr.td{$row[runoob_author]}/td.td{$row[runoob_count]}/td./tr;}echo/table;mysqli_close$conn;输出结果如下图所示MySQL正则表达式在前面的章节我们已经了解到MySQL可以通过LIKE...%来进行模糊匹配MySQL同样也支持其他正则表达式的匹配,MySQL中使用REGEXP操作符来进行正则表达式匹配如果您了解PHP或Perl,那么操作起来就非常简单,因为MySQL的正则表达式匹配与这些脚本的类似下表中的正则模式可应用于REGEXP操作符中模式描述^匹配输入字符串的开始位置如果设置了RegExp对象的Multiline属性,^也匹配\n或\r之后的位置$匹配输入字符串的结束位置如果设置了RegExp对象的Multiline属性,$也匹配\n或\r之前的位置.匹配除\n之外的任何单个字符要匹配包括\n在内的任何字符,请使用象[.\n]的模式[...]字符集合匹配所包含的任意一个字符例如,[abc]可以匹配plain中的a[^...]负值字符集合匹配未包含的任意字符例如,[^abc]可以匹配plain中的pp1|p2|p3匹配p1或p2或p3例如,z|food能匹配z或foodz|food则匹配zood或food*匹配前面的子表达式零次或多次例如,zo*能匹配z以及zoo*等价于{0}+匹配前面的子表达式一次或多次例如,zo+能匹配zo以及zoo,但不能匹配z+等价于{1}{n}n是一个非负整数匹配确定的n次例如,o{2}不能匹配Bob中的o,但是能匹配food中的两个o{nm}m和n均为非负整数,其中n=m最少匹配n次且最多匹配m次实例了解以上的正则需求后,我们就可以更加自己的需求来编写带有正则表达式的SQL语句以下我们将列出几个小实例表名person_tbl来加深我们的理解查找name字段中以st为开头的所有数据mysqlSELECTnameFROMperson_tblWHEREnameREGEXP^st;查找name字段中以ok为结尾的所有数据mysqlSELECTnameFROMperson_tblWHEREnameREGEXPok$;查找name字段中包含mar字符串的所有数据mysqlSELECTnameFROMperson_tblWHEREnameREGEXPmar;查找name字段中以元音字符开头或以ok字符串结尾的所有数据mysqlSELECTnameFROMperson_tblWHEREnameREGEXP^[aeiou]|ok$;MySQL事务MySQL事务主要用于处理操作量大,复杂度高的数据比如说,在人员管理系统中,你删除一个人员,你即需要删除人员的基本资料,也要删除和该人员相关的信息,如信箱,文章等等,这样,这些数据库操作语句就构成一个事务!在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务事务处理可以用来维护数据库的完整性,保证成批的SQL语句要么全部执行,要么全部不执行事务用来管理insertupdatedelete语句一般来说,事务是必须满足4个条件(ACID)Atomicity(原子性)、Consistency(稳定性)、Isolation(隔离性)、Durability(可靠性)
1、事务的原子性一组事务,要么成功;要么撤回
2、稳定性有非法数据(外键约束之类),事务撤回
3、隔离性事务独立运行一个事务处理后的结果,影响了其他事务,那么其他事务会撤回事务的100%隔离,需要牺牲速度
4、可靠性软、硬件崩溃后,InnoDB数据表驱动会利用日志文件重构修改可靠性和高速度不可兼得,innodb_flush_log_at_trx_commit选项决定什么时候吧事务保存到日志里在MySQL命令行的默认设置下,事务都是自动提交的,即执行SQL语句后就会马上执行COMMIT操作因此要显式地开启一个事务务须使用命令BEGIN或STARTTRANSACTION,或者执行命令SETAUTOCOMMIT=0,用来禁止使用当前会话的自动提交事物控制语句BEGIN或STARTTRANSACTION;显式地开启一个事务;COMMIT;也可以使用COMMITWORK,不过二者是等价的COMMIT会提交事务,并使已对数据库进行的所有修改称为永久性的;ROLLBACK;有可以使用ROLLBACKWORK,不过二者是等价的回滚会结束用户的事务,并撤销正在进行的所有未提交的修改;SAVEPOINTidentifier;SAVEPOINT允许在事务中创建一个保存点,一个事务中可以有多个SAVEPOINT;RELEASESAVEPOINTidentifier;删除一个事务的保存点,当没有指定的保存点时,执行该语句会抛出一个异常;ROLLBACKTOidentifier;把事务回滚到标记点;SETTRANSACTION;用来设置事务的隔离级别InnoDB存储引擎提供事务的隔离级别有READUNCOMMITTED、READCOMMITTED、REPEATABLEREAD和SERIALIZABLEMYSQL事务处理主要有两种方法
1、用BEGINROLLBACKCOMMIT来实现BEGIN开始一个事务ROLLBACK事务回滚COMMIT事务确认
2、直接用SET来改变MySQL的自动提交模式:SETAUTOCOMMIT=0禁止自动提交SETAUTOCOMMIT=1开启自动提交事务测试mysqluseRUNOOB;DatabasechangedmysqlCREATETABLErunoob_transaction_testidint5engine=innodb;#创建数据表QueryOK0rowsaffected
0.04secmysqlselect*fromrunoob_transaction_test;Emptyset
0.01secmysqlbegin;#开始事务QueryOK0rowsaffected
0.00secmysqlinsertintorunoob_transaction_testvalue5;QueryOK1rowsaffected
0.01secmysqlinsertintorunoob_transaction_testvalue6;QueryOK1rowsaffected
0.00secmysqlcommit;#提交事务QueryOK0rowsaffected
0.01secmysqlselect*fromrunoob_transaction_test;+------+|id|+------+|5||6|+------+2rowsinset
0.01secmysqlbegin;#开始事务QueryOK0rowsaffected
0.00secmysqlinsertintorunoob_transaction_testvalues7;QueryOK1rowsaffected
0.00secmysqlrollback;#回滚QueryOK0rowsaffected
0.00secmysqlselect*fromrunoob_transaction_test;#因为回滚所以数据没有插入+------+|id|+------+|5||6|+------+2rowsinset
0.01secmysqlPHP中使用事务实例MySQLORDERBY测试php$dbhost=localhost:3306;//mysql服务器主机地址$dbuser=root;//mysql用户名$dbpass=123456;//mysql用户名密码$conn=mysqli_connect$dbhost$dbuser$dbpass;if!$conn{die连接失败:.mysqli_error$conn;}//设置编码,防止中文乱码mysqli_query$connsetnamesutf8;mysqli_select_db$connRUNOOB;mysqli_query$connSETAUTOCOMMIT=0;//设置为不自动提交,因为MYSQL默认立即执行mysqli_begin_transaction$conn;//开始事务定义if!mysqli_query$conninsertintorunoob_transaction_testidvalues8{mysqli_query$connROLLBACK;//判断当执行失败时回滚}if!mysqli_query$conninsertintorunoob_transaction_testidvalues9{mysqli_query$connROLLBACK;//判断执行失败时回滚}mysqli_commit$conn;//执行事务mysqli_close$conn;MySQLALTER命令当我们需要修改数据表名或者修改数据表字段时,就需要使用到MySQLALTER命令开始本章教程前让我们先创建一张表,表名为testalter_tblroot@host#mysql-uroot-ppassword;Enterpassword:*******mysqluseRUNOOB;Databasechangedmysqlcreatetabletestalter_tbl--iINT-cCHAR1-;QueryOK0rowsaffected
0.05secmysqlSHOWCOLUMNSFROMtestalter_tbl;+-------+---------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-------+---------+------+-----+---------+-------+|i|int11|YES||NULL|||c|char1|YES||NULL||+-------+---------+------+-----+---------+-------+2rowsinset
0.00sec删除,添加或修改表字段如下命令使用了ALTER命令及DROP子句来删除以上创建表的i字段mysqlALTERTABLEtestalter_tblDROPi;如果数据表中只剩余一个字段则无法使用DROP来删除字段MySQL中使用ADD子句来向数据表中添加列,如下实例在表testalter_tbl中添加i字段,并定义数据类型:mysqlALTERTABLEtestalter_tblADDiINT;执行以上命令后,i字段会自动添加到数据表字段的末尾mysqlSHOWCOLUMNSFROMtestalter_tbl;+-------+---------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-------+---------+------+-----+---------+-------+|c|char1|YES||NULL|||i|int11|YES||NULL||+-------+---------+------+-----+---------+-------+2rowsinset
0.00sec如果你需要指定新增字段的位置,可以使用MySQL提供的关键字FIRST设定位第一列,AFTER字段名(设定位于某个字段之后)尝试以下ALTERTABLE语句在执行成功后,使用SHOWCOLUMNS查看表结构的变化ALTERTABLEtestalter_tblDROPi;ALTERTABLEtestalter_tblADDiINTFIRST;ALTERTABLEtestalter_tblDROPi;ALTERTABLEtestalter_tblADDiINTAFTERc;FIRST和AFTER关键字只占用于ADD子句,所以如果你想重置数据表字段的位置就需要先使用DROP删除字段然后使用ADD来添加字段并设置位置修改字段类型及名称如果需要修改字段类型及名称你可以在ALTER命令中使用MODIFY或CHANGE子句例如,把字段c的类型从CHAR1改为CHAR10,可以执行以下命令:mysqlALTERTABLEtestalter_tblMODIFYcCHAR10;使用CHANGE子句语法有很大的不同在CHANGE关键字之后,紧跟着的是你要修改的字段名,然后指定新字段名及类型尝试如下实例mysqlALTERTABLEtestalter_tblCHANGEijBIGINT;=p=mysqlALTERTABLEtestalter_tblCHANGEjjINT;ALTERTABLE对Null值和默认值的影响当你修改字段时,你可以指定是否包含只或者是否设置默认值以下实例,指定字段j为NOTNULL且默认值为100mysqlALTERTABLEtestalter_tbl-MODIFYjBIGINTNOTNULLDEFAULT100;如果你不设置默认值,MySQL会自动设置该字段默认为NULL修改字段默认值你可以使用ALTER来修改字段的默认值,尝试以下实例mysqlALTERTABLEtestalter_tblALTERiSETDEFAULT1000;mysqlSHOWCOLUMNSFROMtestalter_tbl;+-------+---------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-------+---------+------+-----+---------+-------+|c|char1|YES||NULL|||i|int11|YES||1000||+-------+---------+------+-----+---------+-------+2rowsinset
0.00sec你也可以使用ALTER命令及DROP子句来删除字段的默认值,如下实例mysqlALTERTABLEtestalter_tblALTERiDROPDEFAULT;mysqlSHOWCOLUMNSFROMtestalter_tbl;+-------+---------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-------+---------+------+-----+---------+-------+|c|char1|YES||NULL|||i|int11|YES||NULL||+-------+---------+------+-----+---------+-------+2rowsinset
0.00secChangingaTableType:修改数据表类型,可以使用ALTER命令及TYPE子句来完成尝试以下实例,我们将表testalter_tbl的类型修改为MYISAM注意查看数据表类型可以使用SHOWTABLESTATUS语句mysqlALTERTABLEtestalter_tblENGINE=MYISAM;;mysqlSHOWTABLESTATUSLIKEtestalter_tbl\G***************************
1.row****************Name:testalter_tblType:MyISAMRow_format:FixedRows:0Avg_row_length:0Data_length:0Max_data_length:25769803775Index_length:1024Data_free:0Auto_increment:NULLCreate_time:2007-06-0308:04:36Update_time:2007-06-0308:04:36Check_time:NULLCreate_options:Comment:1rowinset
0.00sec修改表名如果需要修改数据表的名称,可以在ALTERTABLE语句中使用RENAME子句来实现尝试以下实例将数据表testalter_tbl重命名为alter_tbl mysqlALTERTABLEtestalter_tblRENAMETOalter_tbl;ALTER命令还可以用来创建及删除MySQL数据表的索引,该功能我们会在接下来的章节中介绍笔记列表
3.23版本中添加,如果你的MySQL版本低于
3.23版本就无法使用MySQL的临时表不过现在一般很少有再使用这么低版本的MySQL数据库服务了MySQL临时表只在当前连接可见,如果你使用PHP脚本来创建MySQL临时表,那没当PHP脚本执行完成后,该临时表也会自动销毁如果你使用了其他MySQL客户端程序连接MySQL数据库服务器来创建临时表,那么只有在关闭客户端程序时才会销毁临时表,当然你也可以手动销毁实例以下展示了使用MySQL临时表的简单实例,以下的SQL代码可以适用于PHP脚本的mysql_query函数mysqlCREATETEMPORARYTABLESalesSummary-product_nameVARCHAR50NOTNULL-total_salesDECIMAL122NOTNULLDEFAULT
0.00-avg_unit_priceDECIMAL72NOTNULLDEFAULT
0.00-total_units_soldINTUNSIGNEDNOTNULLDEFAULT0;QueryOK0rowsaffected
0.00secmysqlINSERTINTOSalesSummary-product_nametotal_salesavg_unit_pricetotal_units_sold-VALUES-cucumber
100.25902;mysqlSELECT*FROMSalesSummary;+--------------+-------------+----------------+------------------+|product_name|total_sales|avg_unit_price|total_units_sold|+--------------+-------------+----------------+------------------+|cucumber|
100.25|
90.00|2|+--------------+-------------+----------------+------------------+1rowinset
0.00sec当你使用SHOWTABLES命令显示数据表列表时,你将无法看到SalesSummary表如果你退出当前MySQL会话,再使用SELECT命令来读取原先创建的临时表数据,那你会发现数据库中没有该表的存在,因为在你退出时该临时表已经被销毁了删除MySQL临时表默认情况下,当你断开与数据库的连接后,临时表就会自动被销毁当然你也可以在当前MySQL会话使用DROPTABLE命令来手动删除临时表以下是手动删除临时表的实例mysqlCREATETEMPORARYTABLESalesSummary-product_nameVARCHAR50NOTNULL-total_salesDECIMAL122NOTNULLDEFAULT
0.00-avg_unit_priceDECIMAL72NOTNULLDEFAULT
0.00-total_units_soldINTUNSIGNEDNOTNULLDEFAULT0;QueryOK0rowsaffected
0.00secmysqlINSERTINTOSalesSummary-product_nametotal_salesavg_unit_pricetotal_units_sold-VALUES-cucumber
100.25902;mysqlSELECT*FROMSalesSummary;+--------------+-------------+----------------+------------------+|product_name|total_sales|avg_unit_price|total_units_sold|+--------------+-------------+----------------+------------------+|cucumber|
100.25|
90.00|2|+--------------+-------------+----------------+------------------+1rowinset
0.00secmysqlDROPTABLESalesSummary;mysqlSELECT*FROMSalesSummary;ERROR1146:TableRUNOOB.SalesSummarydoesntexistMySQL复制表如果我们需要完全的复制MySQL的数据表,包括表的结构,索引,默认值等如果仅仅使用CREATETABLE...SELECT命令,是无法实现的本章节将为大家介绍如何完整的复制MySQL数据表,步骤如下使用SHOWCREATETABLE命令获取创建数据表CREATETABLE语句,该语句包含了原数据表的结构,索引等复制以下命令显示的SQL语句,修改数据表名,并执行SQL语句,通过以上命令将完全的复制数据表结构如果你想复制表的内容,你就可以使用INSERTINTO...SELECT语句来实现实例尝试以下实例来复制表runoob_tbl步骤一获取数据表的完整结构mysqlSHOWCREATETABLErunoob_tbl\G;***************************
1.row***************************Table:runoob_tblCreateTable:CREATETABLE`runoob_tbl``runoob_id`int11NOTNULLauto_increment`runoob_title`varchar100NOTNULLdefault`runoob_author`varchar40NOTNULLdefault`submission_date`datedefaultNULLPRIMARYKEY`runoob_id`UNIQUEKEY`AUTHOR_INDEX``runoob_author`ENGINE=InnoDB1rowinset
0.00secERROR:Noqueryspecified步骤二修改SQL语句的数据表名,并执行SQL语句mysqlCREATETABLE`clone_tbl`-`runoob_id`int11NOTNULLauto_increment-`runoob_title`varchar100NOTNULLdefault-`runoob_author`varchar40NOTNULLdefault-`submission_date`datedefaultNULL-PRIMARYKEY`runoob_id`-UNIQUEKEY`AUTHOR_INDEX``runoob_author`-ENGINE=InnoDB;QueryOK0rowsaffected
1.80sec步骤三执行完第二步骤后,你将在数据库中创建新的克隆表clone_tbl如果你想拷贝数据表的数据你可以使用INSERTINTO...SELECT语句来实现mysqlINSERTINTOclone_tblrunoob_id-runoob_title-runoob_author-submission_date-SELECTrunoob_idrunoob_title-runoob_authorsubmission_date-FROMrunoob_tbl;QueryOK3rowsaffected
0.07secRecords:3Duplicates:0Warnings:0执行以上步骤后,你将完整的复制表,包括表结构及表数据MySQL元数据你可能想知道MySQL以下三种信息查询结果信息SELECTUPDATE或DELETE语句影响的记录数数据库和数据表的信息包含了数据库及数据表的结构信息MySQL服务器信息包含了数据库服务器的当前状态,版本号等在MySQL的命令提示符中,我们可以很容易的获取以上服务器信息但如果使用Perl或PHP等脚本语言,你就需要调用特定的接口函数来获取接下来我们会详细介绍获取查询语句影响的记录数PERL实例在DBI脚本中,语句影响的记录数通过函数do或execute返回#方法1#使用do执行$querymy$count=$dbh-do$query;#如果发生错误会输出0printf%d条数据被影响\ndefined$count$count:0;#方法2#使用prepare及execute执行$querymy$sth=$dbh-prepare$query;my$count=$sth-execute;printf%d条数据被影响\ndefined$count$count:0;PHP实例在PHP中,你可以使用mysqli_affected_rows函数来获取查询语句影响的记录数$result_id=mysqli_query$conn_id$query;#如果查询失败返回$count=$result_idmysqli_affected_rows$conn_id:0;print$count条数据被影响\n;数据库和数据表列表你可以很容易的在MySQL服务器中获取数据库和数据表列表如果你没有足够的权限,结果将返回null你也可以使用SHOWTABLES或SHOWDATABASES语句来获取数据库和数据表列表PERL实例#获取当前数据库中所有可用的表my@tables=$dbh-tables;foreach$table@tables{print表名$table\n;}PHP实例以下实例输出MySQL服务器上的所有数据库查看所有数据库php$dbhost=localhost:3306;//mysql服务器主机地址$dbuser=root;//mysql用户名$dbpass=123456;//mysql用户名密码$conn=mysqli_connect$dbhost$dbuser$dbpass;if!$conn{die连接失败:.mysqli_error$conn;}//设置编码,防止中文乱码$db_list=mysqli_query$connSHOWDATABASES;while$db=mysqli_fetch_object$db_list{echo$db-Database.br/;}mysqli_close$conn;获取服务器元数据以下命令语句可以在MySQL的命令提示符使用,也可以在脚本中使用,如PHP脚本命令描述SELECTVERSION服务器版本信息SELECTDATABASE当前数据库名或者返回空SELECTUSER当前用户名SHOWSTATUS服务器状态SHOWVARIABLES服务器配置变量MySQL序列使用MySQL序列是一组整数
123...,由于一张数据表只能有一个字段自增主键,如果你想实现其他字段也实现自动增加,就可以使用MySQL序列来实现本章我们将介绍如何使用MySQL的序列使用AUTO_INCREMENTMySQL中最简单使用序列的方法就是使用MySQLAUTO_INCREMENT来定义列实例以下实例中创建了数据表insect,insect中id无需指定值可实现自动增长mysqlCREATETABLEinsect--idINTUNSIGNEDNOTNULLAUTO_INCREMENT-PRIMARYKEYid-nameVARCHAR30NOTNULL#typeofinsect-dateDATENOTNULL#datecollected-originVARCHAR30NOTNULL#wherecollected;QueryOK0rowsaffected
0.02secmysqlINSERTINTOinsectidnamedateoriginVALUES-NULLhousefly2001-09-10kitchen-NULLmillipede2001-09-10driveway-NULLgrasshopper2001-09-10frontyard;QueryOK3rowsaffected
0.02secRecords:3Duplicates:0Warnings:0mysqlSELECT*FROMinsectORDERBYid;+----+-------------+------------+------------+|id|name|date|origin|+----+-------------+------------+------------+|1|housefly|2001-09-10|kitchen||2|millipede|2001-09-10|driveway||3|grasshopper|2001-09-10|frontyard|+----+-------------+------------+------------+3rowsinset
0.00sec获取AUTO_INCREMENT值在MySQL的客户端中你可以使用SQL中的LAST_INSERT_ID函数来获取最后的插入表中的自增列的值在PHP或PERL脚本中也提供了相应的函数来获取最后的插入表中的自增列的值PERL实例使用mysql_insertid属性来获取AUTO_INCREMENT的值实例如下$dbh-doINSERTINTOinsectnamedateoriginVALUESmoth2001-09-14windowsill;my$seq=$dbh-{mysql_insertid};PHP实例PHP通过mysql_insert_id函数来获取执行的插入SQL语句中AUTO_INCREMENT列的值mysql_queryINSERTINTOinsectnamedateoriginVALUESmoth2001-09-14windowsill$conn_id;$seq=mysql_insert_id$conn_id;重置序列如果你删除了数据表中的多条记录,并希望对剩下数据的AUTO_INCREMENT列进行重新排列,那么你可以通过删除自增的列,然后重新添加来实现不过该操作要非常小心,如果在删除的同时又有新记录添加,有可能会出现数据混乱操作如下所示mysqlALTERTABLEinsectDROPid;mysqlALTERTABLEinsect-ADDidINTUNSIGNEDNOTNULLAUTO_INCREMENTFIRST-ADDPRIMARYKEYid;设置序列的开始值一般情况下序列的开始值为1,但如果你需要指定一个开始值100,那我们可以通过以下语句来实现mysqlCREATETABLEinsect--idINTUNSIGNEDNOTNULLAUTO_INCREMENT-PRIMARYKEYid-nameVARCHAR30NOTNULL-dateDATENOTNULL-originVARCHAR30NOTNULLengine=innodbauto_increment=100charset=utf8;或者你也可以在表创建成功后,通过以下语句来实现mysqlALTERTABLEtAUTO_INCREMENT=100;MySQL处理重复数据有些MySQL数据表中可能存在重复的记录,有些情况我们允许重复数据的存在,但有时候我们也需要删除这些重复的数据本章节我们将为大家介绍如何防止数据表出现重复数据及如何删除数据表中的重复数据防止表中出现重复数据你可以在MySQL数据表中设置指定的字段为PRIMARYKEY(主键)或者UNIQUE(唯一)索引来保证数据的唯一性让我们尝试一个实例下表中无索引及主键,所以该表允许出现多条重复记录CREATETABLEperson_tblfirst_nameCHAR20last_nameCHAR20sexCHAR10;如果你想设置表中字段first_name,last_name数据不能重复,你可以设置双主键模式来设置数据的唯一性,如果你设置了双主键,那么那个键的默认值不能为NULL,可设置为NOTNULL如下所示CREATETABLEperson_tblfirst_nameCHAR20NOTNULLlast_nameCHAR20NOTNULLsexCHAR10PRIMARYKEYlast_namefirst_name;如果我们设置了唯一索引,那么在插入重复数据时,SQL语句将无法执行成功并抛出错INSERTIGNOREINTO与INSERTINTO的区别就是INSERTIGNORE会忽略数据库中已经存在的数据,如果数据库没有数据,就插入新的数据,如果有数据的话就跳过这条数据这样就可以保留数据库中已经存在数据,达到在间隙中插入数据的目的以下实例使用了INSERTIGNOREINTO,执行后不会出错,也不会向数据表中插入重复数据mysqlINSERTIGNOREINTOperson_tbllast_namefirst_name-VALUESJayThomas;QueryOK1rowaffected
0.00secmysqlINSERTIGNOREINTOperson_tbllast_namefirst_name-VALUESJayThomas;QueryOK0rowsaffected
0.00secINSERTIGNOREINTO当插入数据时,在设置了记录的唯一性后,如果插入重复数据,将不返回错误,只以警告形式返回而REPLACEINTOinto如果存在primary或unique相同的记录,则先删除掉再插入新记录另一种设置数据的唯一性方法是添加一个UNIQUE索引,如下所示CREATETABLEperson_tblfirst_nameCHAR20NOTNULLlast_nameCHAR20NOTNULLsexCHAR10UNIQUElast_namefirst_name;统计重复数据以下我们将统计表中first_name和last_name的重复记录数mysqlSELECTCOUNT*asrepetitionslast_namefirst_name-FROMperson_tbl-GROUPBYlast_namefirst_name-HAVINGrepetitions1;以上查询语句将返回person_tbl表中重复的记录数一般情况下,查询重复的值,请执行以下操作确定哪一列包含的值可能会重复在列选择列表使用COUNT*列出的那些列在GROUPBY子句中列出的列HAVING子句设置重复数大于1过滤重复数据如果你需要读取不重复的数据可以在SELECT语句中使用DISTINCT关键字来过滤重复数据mysqlSELECTDISTINCTlast_namefirst_name-FROMperson_tbl;你也可以使用GROUPBY来读取数据表中不重复的数据mysqlSELECTlast_namefirst_name-FROMperson_tbl-GROUPBYlast_namefirst_name;删除重复数据如果你想删除数据表中的重复数据,你可以使用以下的SQL语句mysqlCREATETABLEtmpSELECTlast_namefirst_namesex-FROMperson_tbl;-GROUPBYlast_namefirst_namesex;mysqlDROPTABLEperson_tbl;mysqlALTERTABLEtmpRENAMETOperson_tbl;当然你也可以在数据表中添加INDEX(索引)和PRIMAYKEY(主键)这种简单的方法来删除表中的重复记录方法如下mysqlALTERIGNORETABLEperson_tbl-ADDPRIMARYKEYlast_namefirst_name;MySQL及SQL注入如果您通过网页获取用户输入的数据并将其插入一个MySQL数据库,那么就有可能发生SQL注入安全的问题本章节将为大家介绍如何防止SQL注入,并通过脚本来过滤SQL中注入的字符所谓SQL注入,就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串,最终达到欺骗服务器执行恶意的SQL命令我们永远不要信任用户的输入,我们必须认定用户输入的数据都是不安全的,我们都需要对用户输入的数据进行过滤处理以下实例中,输入的用户名必须为字母、数字及下划线的组合,且用户名长度为8到20个字符之间ifpreg_match/^\w{820}$/$_GET[username]$matches{$result=mysqli_query$connSELECT*FROMusersWHEREusername=$matches
[0];}else{echousername输入异常;}让我们看下在没有过滤特殊字符时,出现的SQL情况//设定$name中插入了我们不需要的SQL语句$name=Qadir;DELETEFROMusers;;mysqli_query$connSELECT*FROMusersWHEREname={$name};以上的注入语句中,我们没有对$name的变量进行过滤,$name中插入了我们不需要的SQL语句,将删除users表中的所有数据在PHP中的mysqli_query是不允许执行多个SQL语句的,但是在SQLite和PostgreSQL是可以同时执行多条SQL语句的,所以我们对这些用户的数据需要进行严格的验证防止SQL注入,我们需要注意以下几个要点
1.永远不要信任用户的输入对用户的输入进行校验,可以通过正则表达式,或限制长度;对单引号和双-进行转换等
2.永远不要使用动态拼装sql,可以使用参数化的sql或者直接使用存储过程进行数据查询存取
3.永远不要使用管理员权限的数据库连接,为每个应用使用单独的权限有限的数据库连接
4.不要把机密信息直接存放,加密或者hash掉密码和敏感的信息
5.应用的异常信息应该给出尽可能少的提示,最好使用自定义的错误信息对原始错误信息进行包装
6.sql注入的检测方法一般采取辅助软件或网站平台来检测,软件一般采用sql注入检测工具jsky,网站平台就有亿思网站安全平台检测工具MDCSOFTSCAN等采用MDCSOFT-IPS可以有效的防御SQL注入,XSS攻击等防止SQL注入在脚本语言,如Perl和PHP你可以对用户输入的数据进行转义从而来防止SQL注入PHP的MySQL扩展提供了mysqli_real_escape_string函数来转义特殊的输入字符ifget_magic_quotes_gpc{$name=stripslashes$name;}$name=mysqli_real_escape_string$conn$name;mysqli_query$connSELECT*FROMusersWHEREname={$name};Like语句中的注入like查询时,如果用户输入的值有_和%,则会出现这种情况用户本来只是想查询abcd_,查询结果中却有abcd_、abcde、abcdf等等;用户要查询30%(注百分之三十)时也会出现问题在PHP脚本中我们可以使用addcslashes函数来处理以上情况,如下实例$sub=addcslashesmysqli_real_escape_string$conn%something_%_;//$sub==\%something\_mysqli_query$connSELECT*FROMmessagesWHEREsubjectLIKE{$sub}%;addcslashes函数在指定的字符前添加反斜杠语法格式:addcslashesstringcharacters参数描述string必需规定要检查的字符串characters可选规定受addcslashes影响的字符或字符范围具体应用可以查看PHPaddcslashes函数MySQL导出数据MySQL中你可以使用SELECT...INTOOUTFILE语句来简单的导出数据到文本文件上使用SELECT...INTOOUTFILE语句导出数据以下实例中我们将数据表runoob_tbl数据导出到/tmp/tutorials.txt文件中:mysqlSELECT*FROMrunoob_tbl-INTOOUTFILE/tmp/tutorials.txt;你可以通过命令选项来设置数据输出的指定格式,以下实例为导出CSV格式mysqlSELECT*FROMpasswdINTOOUTFILE/tmp/tutorials.txt-FIELDSTERMINATEDBYENCLOSEDBY-LINESTERMINATEDBY\r\n;在下面的例子中,生成一个文件,各值用逗号隔开这种格式可以被许多程序使用SELECTaba+bINTOOUTFILE/tmp/result.textFIELDSTERMINATEDBYOPTIONALLYENCLOSEDBYLINESTERMINATEDBY\nFROMtest_table;SELECT...INTOOUTFILE语句有以下属性:LOADDATAINFILE是SELECT...INTOOUTFILE的逆操作,SELECT句法为了将一个数据库的数据写入一个文件,使用SELECT...INTOOUTFILE,为了将文件读回数据库,使用LOADDATAINFILESELECT...INTOOUTFILEfile_name形式的SELECT可以把被选择的行写入一个文件中该文件被创建到服务器主机上,因此您必须拥有FILE权限,才能使用此语法输出不能是一个已存在的文件防止文件数据被篡改你需要有一个登陆服务器的账号来检索文件否则SELECT...INTOOUTFILE不会起任何作用在UNIX中,该文件被创建后是可读的,权限由MySQL服务器所拥有这意味着,虽然你就可以读取该文件,但可能无法将其删除导出表作为原始数据mysqldump是mysql用于转存储数据库的实用程序它主要产生一个SQL脚本,其中包含从头重新创建数据库所必需的命令CREATETABLEINSERT等使用mysqldump导出数据需要使用--tab选项来指定导出文件指定的目录,该目标必须是可写的以下实例将数据表runoob_tbl导出到/tmp目录中$mysqldump-uroot-p--no-create-info\--tab=/tmpRUNOOBrunoob_tblpassword******导出SQL格式的数据导出SQL格式的数据到指定文件,如下所示$mysqldump-uroot-pRUNOOBrunoob_tbldump.txtpassword******以上命令创建的文件内容如下--MySQLdump
8.23----Host:localhostDatabase:RUNOOB-----------------------------------------------------------Serverversion
3.
23.58----Tablestructurefortable`runoob_tbl`--CREATETABLErunoob_tblrunoob_idint11NOTNULLauto_incrementrunoob_titlevarchar100NOTNULLdefaultrunoob_authorvarchar40NOTNULLdefaultsubmission_datedatedefaultNULLPRIMARYKEYrunoob_idUNIQUEKEYAUTHOR_INDEXrunoob_authorTYPE=MyISAM;----Dumpingdatafortable`runoob_tbl`--INSERTINTOrunoob_tblVALUES1LearnPHPJohnPoul2007-05-24;INSERTINTOrunoob_tblVALUES2LearnMySQLAbdulS2007-05-24;INSERTINTOrunoob_tblVALUES3JAVATutorialSanjay2007-05-06;如果你需要导出整个数据库的数据,可以使用以下命令$mysqldump-uroot-pRUNOOBdatabase_dump.txtpassword******如果需要备份所有数据库,可以使用以下命令$mysqldump-uroot-p--all-databasesdatabase_dump.txtpassword******--all-databases选项在MySQL
3.
23.12及以后版本加入该方法可用于实现数据库的备份策略将数据表及数据库拷贝至其他主机如果你需要将数据拷贝至其他的MySQL服务器上你可以在mysqldump命令中指定数据库名及数据表在源主机上执行以下命令,将数据备份至dump.txt文件中:$mysqldump-uroot-pdatabase_nametable_namedump.txtpassword*****如果完整备份数据库,则无需使用特定的表名称如果你需要将备份的数据库导入到MySQL服务器中,可以使用以下命令,使用以下命令你需要确认数据库已经创建$mysql-uroot-pdatabase_namedump.txtpassword*****你也可以使用以下命令将导出的数据直接导入到远程的服务器上,但请确保两台服务器是相通的,是可以相互访问的/p$mysqldump-uroot-pdatabase_name\|mysql-hother-host.comdatabase_name以上命令中使用了管道来将导出的数据导入到指定的远程主机上MySQL导入数据MySQL中可以使用两种简单的方式来导入MySQL导出的数据使用LOADDATA导入数据MySQL中提供了LOADDATAINFILE语句来插入数据以下实例中将从当前目录中读取文件dump.txt,将该文件中的数据插入到当前数据库的mytbl表中mysqlLOADDATALOCALINFILEdump.txtINTOTABLEmytbl; 如果指定LOCAL关键词,则表明从客户主机上按路径读取文件如果没有指定,则文件在服务器上按路径读取文件你能明确地在LOADDATA语句中指出列值的分隔符和行尾标记,但是默认标记是定位符和换行符两个命令的FIELDS和LINES子句的语法是一样的两个子句都是可选的,但是如果两个同时被指定,FIELDS子句必须出现在LINES子句之前如果用户指定一个FIELDS子句,它的子句(TERMINATEDBY、[OPTIONALLY]ENCLOSEDBY和ESCAPEDBY也是可选的,不过,用户必须至少指定它们中的一个mysqlLOADDATALOCALINFILEdump.txtINTOTABLEmytbl-FIELDSTERMINATEDBY:-LINESTERMINATEDBY\r\n;LOADDATA默认情况下是按照数据文件中列的顺序插入数据的,如果数据文件中的列与插入表中的列不一致,则需要指定列的顺序如,在数据文件中的列顺序是abc,但在插入表的列顺序为bca,则数据导入语法如下mysqlLOADDATALOCALINFILEdump.txt-INTOTABLEmytblbca;使用mysqlimport导入数据mysqlimport客户端提供了LOADDATAINFILEQL语句的一个命令行接口mysqlimport的大多数选项直接对应LOADDATAINFILE子句从文件dump.txt中将数据导入到mytbl数据表中可以使用以下命令$mysqlimport-uroot-p--localdatabase_namedump.txtpassword*****mysqlimport命令可以指定选项来设置指定格式命令语句格式如下$mysqlimport-uroot-p--local--fields-terminated-by=:\--lines-terminated-by=\r\ndatabase_namedump.txtpassword*****mysqlimport语句中使用--columns选项来设置列的顺序$mysqlimport-uroot-p--local--columns=bca\database_namedump.txtpassword*****mysqlimport的常用选项介绍选项功能-dor--delete新数据导入数据表中之前删除数据数据表中的所有信息-for--force不管是否遇到错误,mysqlimport将强制继续插入数据-ior--ignoremysqlimport跳过或者忽略那些有相同唯一关键字的行,导入文件中的数据将被忽略-lor-lock-tables数据被插入之前锁住表,这样就防止了,你在更新数据库时,用户的查询和更新受到影响-ror-replace这个选项与-i选项的作用相反;此选项将替代表中有相同唯一关键字的记录--fields-enclosed-by=char指定文本文件中数据的记录时以什么括起的,很多情况下数据以双引号括起默认的情况下数据是没有被字符括起的--fields-terminated-by=char指定各个数据的值之间的分隔符,在句号分隔的文件中,分隔符是句号您可以用此选项指定数据之间的分隔符默认的分隔符是跳格符(Tab)--lines-terminated-by=str此选项指定文本文件中行与行之间数据的分隔字符串或者字符默认的情况下mysqlimport以newline为行分隔符您可以选择用一个字符串来替代一个单个的字符一个新行或者一个回车mysqlimport命令常用的选项还有-v显示版本(version),-p提示输入密码(password)等。