mysql入门之1小时学会MySQL基础
MySQL入门
mySQL(关系型数据库管理系统)
MySQL是一个关系型数据库管理系统,由瑞典MySQLAB公司开发,目前属于Oracle旗下产品。MySQL是最流行的关系型数据库管理系统之一,在WEB应用方面,MySQL是最好的RDBMS(RelationalDatabaseManagementSystem,关系数据库管理系统)应用软件。
MySQL是一种关系数据库管理系统,关系数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。
MySQL所使用的SQL语言是用于访问数据库的最常用标准化语言。MySQL软件采用了双授权政策,分为社区版和商业版,由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,一般中小型网站的开发都选择MySQL作为网站数据库。
由于其社区版的性能卓越,搭配PHP和Apache可组成良好的开发环境。
1.数据库(Database)是按照数据结构来组织、存储和管理数据的仓库,每个数据库都有一个或多个不同的API用于创建,访问,管理,搜索和复制所保存的数据。
2.使用关系型数据库管理系统(RDBMS)来存储和管理的大数据量。关系型数据库是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。
3.RDBMS特点:
1.数据以表格的形式出现
2.每行为各种记录名称
3.每列为记录名称所对应的数据域
4.许多的行和列组成一张表单
5.若干的表单组成database
4.RDBMS术语
冗余:存储两倍数据,冗余可以使系统速度更快。
主键:主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
外键:外键用于关联两个表。
复合键:复合键(组合键)将多个列作为一个索引键,一般用于复合索引。
索引:使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
参照完整性:参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件,目的是保证数据的一致性
5.MySQL是一种关联数据库管理系统,关联数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。
MySQL管理
6.启动及关闭MySQL服务器:
(1)检查MySQL服务器是否启动:
ps-ef|grepmysqld
(2)启动MySQL服务器:
root@host#cd/usr/bin
./safe_mysqld&
(3)关闭目前运行的MySQL服务器:
root@host#cd/usr/bin
./mysqladmin-uroot-pshutdown
Enterpassword:******
7.MySQL用户设置
在MySQL数据库中的user表添加新用户:
root@host#mysql-uroot–p//选择数据库 Enterpassword:******* mysql>usemysql; Databasechanged mysql>INSERTINTOuser (host,user,password, select_priv,insert_priv,update_priv)//设置权限Y VALUES('localhost','guest', PASSWORD('guest123'),'Y','Y','Y'); QueryOK,1rowaffected(0.20sec) mysql>FLUSHPRIVILEGES;
注意需要执行FLUSHPRIVILEGES语句。这个命令执行后会重新载入授权表。
另外一种添加用户的方法为通过SQL的GRANT命令
mysql>GRANTSELECT,INSERT,UPDATE,DELETE,CREATE,DROP ->ONTUTORIALS.* ->TO'zara'@'localhost' ->IDENTIFIEDBY'zara123';
8.管理MySQL的命令
USE数据库名:选择要操作的MySQL数据库:
mysql>useW3CSCHOOL;
Databasechanged
SHOWDATABASES:列出MySQL数据库管理系统的数据库列表:
mysql>SHOWDATABASES;
SHOWTABLES:显示指定数据库所有表,用该命令前需用use命令选择操作的数据库。
mysql>useW3CSCHOOL;
Databasechanged
mysql>SHOWTABLES;
SHOWCOLUMNSFROM数据表:显示数据表的属性,属性类型,主键信息,是否NULL,默认值等其他信息。
mysql>SHOWCOLUMNSFROMW3Cschool_tbl;
+-----------------+--------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+-----------------+--------------+------+-----+---------+-------+
SHOWINDEXFROM数据表:显示数据表的详细索引信息,包括PRIMARYKEY(主键)。
SHOWTABLESTATUSLIKE数据表\G:该命令将输出MySQL数据库管理系统的性能及统计信息。
mysql>SHOWTABLESTATUSFROMW3CSCHOOL;#显示数据库W3CSCHOOL中所有表的信息
mysql>SHOWTABLESTATUSfromW3CSCHOOLLIKE'W3Cschool%';#表名以W3Cschool开头的表的信息
mysql>SHOWTABLESTATUSfromW3CSCHOOLLIKE'W3Cschool%'\G;#加上\G,查询结果按列打印
9.PHPMySQL函数格式:mysql_function(value,value,...);
MySQL连接
10.使用MySQL二进制方式连接
[root@host]#mysql-uroot-p
Enterpassword:******
登录成功后会出现mysql>命令提示窗口,你可以在上面执行任何SQL语句。
退出mysql>命令提示窗口可以使用exit命令:mysql>exit
11.使用PHP脚本连接MySQL
PHP提供了mysql_connect()函数来连接数据库。
connectionmysql_connect(server,user,passwd,new_link,client_flag);5各参数均可选
使用PHP的mysql_close()函数来断开与MySQL数据库的链接。
boolmysql_close(resource$link_identifier);
通常不要用mysql_close(),因为已打开的非持久连接会在脚本执行完毕后自动关闭。
mysql_close()不会关闭由mysql_pconnect()建立的持久连接
$conn=mysql_connect($dbhost,$dbuser,$dbpass); if(!$conn) { die('Couldnotconnect:'.mysql_error()); } echo'Connectedsuccessfully'; mysql_close($conn);
MySQL创建/删除数据库
12.使用mysqladmin创建数据库
用root用户登录,root用户拥有最高权限,可以使用mysqlmysqladmin命令来创建数据库。
[root@host]#mysqladmin-uroot-pcreate/dropW3CSCHOOL
Enterpassword:*****
13.使用PHP脚本创建数据库
PHP使用mysql_query函数来创建或者删除MySQL数据库。
boolmysql_query(sql,connection); $conn=mysql_connect($dbhost,$dbuser,$dbpass); if(!$conn) { die('连接错误:'.mysql_error()); } echo'连接成功
'; $sql='CREATE/DROPDATABASEW3CSCHOOL'; $retval=mysql_query($sql,$conn); if(!$retval) { die('创建数据库失败:'.mysql_error()); } echo"数据库W3CSCHOOL创建成功\n"; mysql_close($conn);
MySQL选择数据库
14.使用PHP脚本选择MySQL数据库
PHP提供了函数mysql_select_db来选取一个数据库。
boolmysql_select_db(db_name,connection); $conn=mysql_connect($dbhost,$dbuser,$dbpass); if(!$conn) { die('连接失败:'.mysql_error()); } echo'连接成功'; mysql_select_db('W3CSCHOOL'); mysql_close($conn);
MySQL创建/删除数据表
15.MySQL创建数据表
创建MySQL数据表需要以下信息:
表名
表字段名
定义每个表字段
创建语法:CREATETABLEtable_name(column_namecolumn_type);
删除语法:DROPTABLEtable_name;
以下例子中我们将在W3CSCHOOL数据库中创建数据表w3cschool_tbl:
tutorials_tbl( tutorial_idINTNOTNULLAUTO_INCREMENT, tutorial_titleVARCHAR(100)NOTNULL, tutorial_authorVARCHAR(40)NOTNULL, submission_dateDATE, PRIMARYKEY(w3cschool_id) );
16.通过命令提示符创建表
使用SQL语句CREATETABLE来创建数据表。
mysql>CREATETABLEw3cschool_tbl( ->w3cschool_idINTNOTNULLAUTO_INCREMENT, ->w3cschool_titleVARCHAR(100)NOTNULL, ->w3cschool_authorVARCHAR(40)NOTNULL, ->submission_dateDATE, ->PRIMARYKEY(w3cschool_id) ->);
MySQL命令终止符为分号(;)。
17.使用PHP脚本创建/删除数据表或插入数据
语法:boolmysql_query(sql,connection);
$sql="CREATETABLEtutorials_tbl("创建 "tutorial_idINTNOTNULLAUTO_INCREMENT,". "tutorial_titleVARCHAR(100)NOTNULL,". "tutorial_authorVARCHAR(40)NOTNULL,". "submission_dateDATE,". "PRIMARYKEY(tutorial_id));" ; $sql="DROPTABLEw3cschool_tbl";删除 mysql_select_db('TUTORIALS'); $retval=mysql_query($sql,$conn);//判断是否成功而设置的参数; if(!$retval) { die('数据表创建失败:'.mysql_error()); } echo"数据表创建成功\n"; mysql_close($conn);
MySQL插入数据
18.向MySQL数据表插入数据通用的INSERTINTOSQL语法:
INSERTINTOtable_name(field1,field2,...fieldN)
VALUES
(value1,value2,...valueN);
如果数据是字符型,必须使用单引号或者双引号,如:"value"。
w3cschool_tbl表插入一条数据:
mysql>INSERTINTOw3cschool_tbl
->(w3cschool_title,w3cschool_author,submission_date)
->VALUES
->("LearnPHP","JohnPoul",NOW());
(->)不是SQL语句的一部分,它仅表示一个新行,如SQL语句太长,可通过回车键创建一个新行编写SQL语句,SQL语句的命令结束符为分号(;)。
19.使用PHP脚本插入数据
$sql="INSERTINTOw3cschool_tbl".
"(w3cschool_title,w3cschool_author,submission_date)".
"VALUES".
"('$w3cschool_title','$w3cschool_author','$submission_date')";
MySQL查询数据
20.为在MySQL数据库中查询数据通用的SELECT语法:
SELECTcolumn_name,column_name
FROMtable_name
[WHEREClause]
[OFFSETM][LIMITN]
可以通过SELECT命令读取一条或者多条记录。
可以通过OFFSET指定SELECT语句开始查询的数据偏移量默认情况下偏移量为0。
可以使用LIMIT属性来设定返回的记录数。
你可以使用星号(*)来代替其他字段,SELECT语句会返回表的所有字段数据
mysql>SELECT*fromw3cschool_tbl
21.使用PHP脚本来获取数据
使用PHP函数的mysql_query()及SQLSELECT命令来获取数据。
该函数用于执行SQL命令,然后通过PHP函数mysql_fetch_array()来使用或输出所有查询的数据。
尝试以下实例来显示数据表w3cschool_tbl的所有记录
$sql='SELECTw3cschool_id,w3cschool_title,
w3cschool_author,submission_date
FROMw3cschool_tbl';
mysql_select_db('W3CSCHOOL');
$retval=mysql_query($sql,$conn);
if(!$retval)
{
die('Couldnotgetdata:'.mysql_error());
}
while($row=mysql_fetch_array($retval,MYSQL_ASSOC))
while($row=mysql_fetch_assoc($retval))
用MYSQL_NUM参数显示数据表
while($row=mysql_fetch_array($retval,MYSQL_NUM))
用MYSQL_NUM参数显示数据表
{
echo"TutorialID:{$row['w3cschool_id']}
".
"Title:{$row['w3cschool_title']}
".
"Author:{$row['w3cschool_author']}
".
"SubmissionDate:{$row['submission_date']}
".
"--------------------------------
";
}
mysql_free_result($retval); 释放游标内存
echo"Fetcheddatasuccessfully\n";
mysql_close($conn);
MYSQL_ASSOC,设置该参数查询结果返回关联数组,你可以使用字段名称来作为数组的索引。
MySQL选择数据
22.从MySQL表中使用SQLSELECT语句来读取数据。
如需有条件地从表中选取数据,可将WHERE子句添加到SELECT语句中
以下是SQLSELECT语句使用WHERE子句从数据表中读取数据的通用语法:
SELECTfield1,field2,...fieldNFROMtable_name1,table_name2...
[WHEREcondition1[AND[OR]]condition2.....
WHERE子句也可以运用于SQL的DELETE或者UPDATE命令。
使用LIKE来比较字符串,否则MySQL的WHERE子句的字符串比较是不区分大小写的。你可以使用BINARY关键字来设定WHERE子句的字符串比较是区分大小写的。
23.使用PHP脚本读取数据
使用PHP函数的mysql_query()及相同的SQLSELECT带上WHERE子句的命令来获取数据。该函数用于执行SQL命令,然后通过mysql_fetch_array()来输出所有查询的数据。
$sql='SELECTw3cschool_id,w3cschool_title,
w3cschool_author,submission_date
FROMw3cschool_tbl
WHEREw3cschool_author="Sanjay"';
MySQLUPDATE
24.修改或更新MySQL中的数据,我们可以使用SQLUPDATE命令来操作。
通用SQL语法:
UPDATEtable_nameSETfield1=new-value1,field2=new-value2
[WHEREClause]
更新数据表中w3cschool_id为3的w3cschool_title字段值:
mysql>UPDATEw3cschool_tbl
->SETw3cschool_title='LearningJAVA'
- >WHEREw3cschool_id=3;
使用PHP脚本更新数据
$sql='UPDATEw3cschool_tbl
SETw3cschool_title="LearningJAVA"
WHEREw3cschool_id=3';
MySQLDELETE
25.DELETEFROMtable_name[WHEREClause
如果没有指定WHERE子句,MySQL表中的所有记录将被删除。
可以在WHERE子句中指定任何条件
删除w3cschool_tbl表中w3cschool_id为3的记录
mysql>DELETEFROMw3cschool_tblWHEREw3cschool_id=3;
用PHP脚本删除数据
$sql='DELETEFROMw3cschool_tbl
WHEREw3cschool_id=3';
MySQLLIKE子句
QLLIKE子句中使用百分号(%)字符来表示任意字符
没有使用百分号(%),LIKE子句与等号(=)的效果是一样的。
26.QLSELECT语句使用LIKE子句从数据表中读取数据的通用语法:
SELECTfield1,field2,...fieldNtable_name1,table_name2...
WHEREfield1LIKEcondition1[AND[OR]]filed2='somevalue'
LIKE通常与%一同使用,类似于一个元字符的搜索
在PHP脚本中使用LIKE子句
$sql='SELECTw3cschool_id,w3cschool_title,
w3cschool_author,submission_date
FROMw3cschool_tbl
WHEREw3cschool_authorLIKE"%jay%"';
MySQL排序
SELECTfield1,field2,...fieldNtable_name1,table_name2...
ORDERBYfield1,[field2...][ASC[DESC]]
ASC或DESC关键字来设置查询结果是按升序或降序排列。默认情况下,它是按升排列。
MySQL分组
SELECTcolumn_name,function(column_name)
FROMtable_name
WHEREcolumn_nameoperatorvalue
GROUPBYcolumn_name;
WITHROLLUP可以实现在分组统计数据基础上再进行相同的统计(SUM,AVG,COUNT…)。
coalesce来设置一个可以取代NUll的名称,coalesce语法:
selectcoalesce(a,b,c);
参数说明:如果a==null,则选择b;如果b==null,则选择c;如果a!=null,则选择a;如果abc都为null,则返回为null(没意义)。
mysql>SELECTcoalesce(name,'总数'),SUM(singin)assingin_countFROM employee_tblGROUPBYnameWITHROLLUP;
+--------------------------+--------------+
|coalesce(name,'总数')|singin_count|
+--------------------------+--------------+
|小丽 | 2|
|小明 | 7|
|小王 | 7|
|总数 | 16|
+--------------------------+--------------+
MySQL多表查询
27.在SELECT,UPDATE和DELETE语句中使用Mysql的JOIN来联合多表查询。
JOIN按照功能大致分为如下三类:
INNERJOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录。
LEFTJOIN(左连接):获取左表所有记录,即使右表没有对应匹配的记录。
RIGHTJOIN(右连接):与LEFTJOIN相反,用于获取右表所有记录,即使左表没有对应匹配的记录。
|w3cschool_author|w3cschool_count|
+-----------------+----------------+
|mahran | 20|
|mahnaz | NULL|
|Jen | NULL|
|Gill | 20|
|JohnPoul | 1|
|Sanjay | 1|
+-----------------+----------------+
mysql>SELECT*fromw3cschool_tbl;
+-------------+----------------+-----------------+-----------------+
|w3cschool_id|w3cschool_title|w3cschool_author|submission_date|
+-------------+----------------+-----------------+-----------------+
| 1|LearnPHP |JohnPoul |2007-05-24 |
| 2|LearnMySQL |AbdulS |2007-05-24 |
| 3|JAVATutorial |Sanjay |2007-05-06 |
连接以上两张表来读取w3cschool_tbl表中所有w3cschool_author字段在tcount_tbl表对应的w3cschool_count字段值:
mysql>SELECTa.w3cschool_id,a.w3cschool_author,b.w3cschool_countFROMw3cschool_tblaINNERJOINtcount_tblbONa.w3cschool_author=b.w3cschool_author;
+-----------+---------------+--------------+
|w3cschool_id|w3cschool_author|w3cschool_count|
+-----------+---------------+--------------+
| 1|JohnPoul | 1|
| 3|Sanjay | 1|
w3cschool_tbl为左表,tcount_tbl为右表,
mysql>SELECTa.w3cschool_id,a.w3cschool_author,b.w3cschool_countFROMw3cschool_tblaLEFTJOINtcount_tblbONa.w3cschool_author=b.w3cschool_author;
+-------------+-----------------+----------------+
|w3cschool_id|w3cschool_author|w3cschool_count|
+-------------+-----------------+----------------+
| 1|JohnPoul | 1|
| 2|AbdulS | NULL|
| 3|Sanjay | 1|
左边的数据表w3cschool_tbl的所有选取的字段数据,即便在右侧表tcount_tbl中没有对应的w3cschool_author字段值AbdulS。
MySQLNULL
ISNULL:当列的值是NULL,此运算符返回true。
ISNOTNULL:当列的值不为NULL,运算符返回true。
NULL值与任何其它值的比较(即使是NULL)永远返回false,
使用PHP脚本处理NULL值:
PHP脚本中你可以在if...else语句来处理变量是否为空,并生成相应的条件语句。
MySQL正则表达式
28.MySQL中使用REGEXP操作符来进行正则表达式匹配。
^ 匹配输入字符串的开始位置。如果设置了RegExp对象的Multiline属性,^也匹配'\n'或'\r'之后的位置。
$ 匹配输入字符串的结束位置。如果设置了RegExp对象的Multiline属性,$也匹配'\n'或'\r'之前的位置。
. 匹配除"\n"之外的任何单个字符。要匹配包括'\n'在内的任何字符,请使用象'[.\n]'的模式。
实例(表名:person_tbl)来加深我们的理解:
查找name字段中以'st'为开头的所有数据:
mysql>SELECTnameFROMperson_tblWHEREnameREGEXP'^st';
查找name字段中以'ok'为结尾的所有数据:
mysql>SELECTnameFROMperson_tblWHEREnameREGEXP'ok$';
查找name字段中包含'mar'字符串的所有数据:
mysql>SELECTnameFROMperson_tblWHEREnameREGEXP'mar';
查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据:
mysql>SELECTnameFROMperson_tblWHEREnameREGEXP'^[aeiou]|ok$';
MySQL事务
29.MySQL事务主要用于处理操作量大,复杂度高的数据。
在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
事务处理可以用来维护数据库的完整性,保证成批的SQL语句要么全部执行,要么全部不执行
事务用来管理insert,update,delete语句
事务必须满足4个条件(ACID):Atomicity(原子性)、Consistency(稳定性)、Isolation(隔离性)、Durability(可靠性)
1、事务的原子性:一组事务,要么成功;要么撤回。
2、稳定性:有非法数据(外键约束之类),事务撤回。
3、隔离性:事务独立运行。一个事务处理后的结果,影响了其他事务,那么其他事务会撤回。事务的100%隔离,需要牺牲速度。
4、可靠性:软、硬件崩溃后,InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得,innodb_flush_log_at_trx_commit选项决定什么时候吧事务保存到日志里。
在MySQL控制台使用事务来操作:
1,开始一个事务
starttransaction
2,做保存点
savepoint保存点名称
3,操作
4,可以回滚,可以提交,没有问题,就提交,有问题就回滚。
PHP中使用事务实例
mysql_query("SETAUTOCOMMIT=0");//设置为不自动提交,因为MYSQL默认立即执行mysql_query("BEGIN");//开始事务定义
if(!mysql_query("insertintotrans(id)values('2')")) { mysql_query("ROOLBACK");//判断当执行失败时回滚 } mysql_query("COMMIT");//执行事务 mysql_close($handler); MySQLALTER
30.修改数据表名或者修改数据表字段时,就需要使用到MySQLALTER命令。
使用了ALTER命令及DROP子句来删除以上创建表的i字段:
mysql>ALTERTABLEtestalter_tbl DROPi;
数据表中只剩余一个字段则无法使用DROP来删除字段。
ADD子句来想数据表中添加列,在表testalter_tbl中添加i字段,并定义数据类型:
mysql>ALTERTABLEtestalter_tblADDiINT;
以下ALTERTABLE语句,在执行成功后,使用SHOWCOLUMNS查看表结构的变化:
ALTERTABLEtestalter_tblDROPi;
ALTERTABLEtestalter_tblADDiINTFIRST;
ALTERTABLEtestalter_tblDROPi;
ALTERTABLEtestalter_tblADDiINTAFTERc;
FIRST和AFTER关键字只占用于ADD子句,所以如果你想重置数据表字段的位置就需要先使用DROP删除字段然后使用ADD来添加字段并设置位置。
修改字段类型及名称:
31.在ALTER命令中使用MODIFY或CHANGE子句。
把字段c的类型从CHAR(1)改为CHAR(10),可以执行以下命令:
mysql>ALTERTABLEtestalter_tblMODIFYcCHAR(10);
使用CHANGE子句,语法有很大的不同。在CHANGE关键字之后,紧跟着的是你要修改的字段名,然后指定新字段的类型及名称。尝试如下实例:
mysql>ALTERTABLEtestalter_tblCHANGEijBIGINT;
ALTER修改字段的默认值,mysql>ALTERTABLEtestalter_tblALTERiSETDEFAULT1000;
ALTER及DROP删除字段的默认值,ALTERTABLEtestalter_tblALTERiDROPDEFAULT;
ALTER及TYPE修改数据表类型,mysql>ALTERTABLEtestalter_tblTYPE=MYISAM;
ALTERTABLE使用RENAME修改数据表的名称,mysql>ALTERTABLEtestalter_tblRENAMETOalter_tbl;
MySQL索引
索引可以大大提高MySQL的检索速度
索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索包含多个列。
创建索引,确保该索引是应用在SQL查询语句的条件(一般作为WHERE子句的条件)。
索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。
缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
3方式创建普通索引
CREATEINDEXindexNameONmytable(username(length));
如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定length。
修改表结构
ALTERmytableADDINDEX[indexName]ON(username(length))
创建表的时候直接指定
CREATETABLEmytable( IDINTNOTNULL, usernameVARCHAR(16)NOTNULL, INDEX[indexName](username(length)) );
删除索引的语法
DROPINDEX[indexName]ONmytable;
唯一索引:前面加UNIQUE
使用ALTER命令添加和删除索引
有四种方式来添加数据表的索引:
ALTERTABLEtbl_nameADDPRIMARYKEY(column_list):该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。
ALTERTABLEtbl_nameADDUNIQUEindex_name(column_list):这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。
ALTERTABLEtbl_nameADDINDEXindex_name(column_list):添加普通索引,索引值可出现多次。
ALTERTABLEtbl_nameADDFULLTEXTindex_name(column_list):该语句指定了索引为FULLTEXT,用于全文索引。
使用ALTER命令添加和删除主键
主键只能作用于一个列上,添加主键索引时,你需要确保该主键默认不为空(NOTNULL)。实例如下:
mysql>ALTERTABLEtestalter_tblMODIFYiINTNOTNULL;
mysql>ALTERTABLEtestalter_tblADDPRIMARYKEY(i);
你也可以使用ALTER命令删除主键:
mysql>ALTERTABLEtestalter_tblDROPPRIMARYKEY;
SHOWINDEX命令列出表中的相关的索引信息。可以通过添加\G来格式化输出信息。
mysql>SHOWINDEXFROMtable_name\G
MySQL临时表
临时表只在当前连接可见,当关闭连接时,MySQL会自动删除表并释放所有空间。
mysql>CREATETEMPORARYTABLESalesSummary
mysql>DROPTABLESalesSummary;
MySQL复制表
创建新的克隆表clone_tbl。如果你想拷贝数据表的数据你可以使用INSERTINTO...SELECT语句来实现。
mysql>INSERTINTOclone_tbl(w3cschool_id,
-> w3cschool_title,
-> w3cschool_author,
-> submission_date)
->SELECTw3cschool_id,w3cschool_title,
-> w3cschool_author,submission_date
->FROMw3cschool_tbl;
MySQL元数据
想知道MySQL以下三种信息:
查询结果信息:SELECT,UPDATE或DELETE语句影响的记录数。
数据库和数据表的信息:包含了数据库及数据表的结构信息。
MySQL服务器信息:包含了数据库服务器的当前状态,版本号等。
(1)使用do()执行 $query
my$count=$dbh->do($query);
(2)使用prepare()及execute()执行 $query
my$sth=$dbh->prepare($query);
my$count=$sth->execute();
在PHP中,使用mysql_affected_rows()函数获取查询语句影响的记录数。
$result_id=mysql_query($query,$conn_id); #如果查询失败返回 $count=($result_id?mysql_affected_rows($conn_id):0); print("$countrowswereaffected\n");
数据库和数据表列表
PERL实例
#获取当前数据库中所有可用的表。
my@tables=$dbh->tables(); foreach$table(@tables){ print"TableName$table\n"; }
PHP实例:
$db_list=mysql_list_dbs($con); while($db=mysql_fetch_object($db_list)) { echo$db->Database."
"; }
MySQL序列
MySQL序列是一组整数:1,2,3,...,
用MySQLAUTO_INCREMENT来定义列。
mysql>CREATETABLEinsect
->(
->idINTUNSIGNEDNOTNULLAUTO_INCREMENT,
用SQL中的LAST_INSERT_ID()函数来获取最后的插入表中的自增列的值。
PERL实例
使用mysql_insertid属性来获取AUTO_INCREMENT的值。实例如下:
$dbh->do("INSERTINTOinsect(name,date,origin)
VALUES('moth','2001-09-14','windowsill')");
my$seq=$dbh->{mysql_insertid};
PHP实例
PHP通过mysql_insert_id()函数来获取执行的插入SQL语句中AUTO_INCREMENT列的值。
mysql_query("INSERTINTOinsect(name,date,origin) VALUES('moth','2001-09-14','windowsill')",$conn_id); $seq=mysql_insert_id($conn_id);
重置序列
删除了数据表中的多条记录,并对剩下数据的AUTO_INCREMENT列进行重新排列,那么你可以通过删除自增的列,然后重新添加来实现。
mysql>ALTERTABLEinsectDROPid;
mysql>ALTERTABLEinsect
->ADDidINTUNSIGNEDNOTNULLAUTO_INCREMENTFIRST,
->ADDPRIMARYKEY(id);
一般情况下序列的开始值为1,但如果你需要指定一个开始值100:
->idINTUNSIGNEDNOTNULLAUTO_INCREMENT=100,
或在表创建成功后,通过以下语句来实现:
mysql>ALTERTABLEtAUTO_INCREMENT=100;
MySQL处理重复数据
防止表中出现重复数据
在MySQL数据表中设置指定的字段为PRIMARYKEY(主键)或者UNIQUE(唯一)索引保证数据的唯一性。
设置表中字段first_name,last_name数据不能重复,你可以设置双主键模式来设置数据的唯一性,如果你设置了双主键,那么那个键的默认值不能为NULL,可设置为NOTNULL。如下所示:
CREATETABLEperson_tbl ( first_nameCHAR(20)NOTNULL, last_nameCHAR(20)NOTNULL, sexCHAR(10), PRIMARYKEY(last_name,first_name) );
INSERTIGNOREINTO与INSERTINTO的区别就是INSERTIGNORE会忽略数据库中已经存在的数据,如果数据库没有数据,就插入新的数据,如果有数据的话就跳过这条数据。这样可以保留数据库中已经存在数据,达到在间隙中插入数据的目的。
用了INSERTIGNOREINTO,执行后不会出错,也不会向数据表中插入重复数据:
mysql>INSERTIGNOREINTOperson_tbl(last_name,first_name)
->VALUES('Jay','Thomas');
QueryOK,1rowaffected(0.00sec)
REPLACEINTOinto如果存在primary或unique相同的记录,则先删除掉。再插入新记录。
UNIQUE(last_name,first_name)
查询重复记录
selectuser_name,count(*)ascountfromuser_tablegroupbyuser_namehavingcount>1;
select*frompeople
wherepeopleIdin(selectpeopleIdfrompeoplegroupbypeopleIdhavingcount(peopleId)>1)
统计重复数据
统计表中first_name和last_name的重复记录数:
mysql>SELECTCOUNT(*)asrepetitions,last_name,first_name ->FROMperson_tbl ->GROUPBYlast_name,first_name ->HAVINGrepetitions>1;
过滤重复数据
读取不重复的数据可以在SELECT语句中使用DISTINCT关键字来过滤重复数据。
mysql>SELECTDISTINCTlast_name,first_name
->FROMperson_tbl
->ORDERBYlast_name;
也可以使用GROUPBY来读取数据表中不重复的数据:
mysql>SELECTlast_name,first_name ->FROMperson_tbl ->GROUPBY(last_name,first_name);
删除重复数据
删除数据表中的重复数据,你可以使用以下的SQL语句:
mysql>CREATETABLEtmpSELECTlast_name,first_name,sex ->FROMperson_tbl; ->GROUPBY(last_name,first_name); mysql>DROPTABLEperson_tbl; mysql>ALTERTABLEtmpRENAMETOperson_tbl;
也可以在数据表中添加INDEX(索引)和PRIMAYKEY(主键)这种简单的方法来删除表中的重复记录。方法如下:
mysql>ALTERIGNORETABLEperson_tbl ->ADDPRIMARYKEY(last_name,first_name);
MySQL及SQL注入
没有过滤特殊字符时,出现的SQL情况:
//设定$name中插入了我们不需要的SQL语句
$name="Qadir';DELETEFROMusers;";
mysql_query("SELECT*FROMusersWHEREname='{$name}'");
以上的注入语句中,我们没有对$name的变量进行过滤,$name中插入了我们不需要的SQL语句,将删除users表中的所有数据。
防止SQL注入,注意以下几个要点:
1.永远不要信任用户的输入。对用户的输入进行校验,可以通过正则表达式,或限制长度;对单引号和双"-"进行转换等。
2.永远不要使用动态拼装sql,可以使用参数化的sql或者直接使用存储过程进行数据查询存取。
3.永远不要使用管理员权限的数据库连接,为每个应用使用单独的权限有限的数据库连接。
4.不要把机密信息直接存放,加密或者hash掉密码和敏感的信息。
5.应用的异常信息应该给出尽可能少的提示,最好使用自定义的错误信息对原始错误信息进行包装
6.sql注入的检测方法一般采取辅助软件或网站平台来检测,软件一般采用sql注入检测工具jsky,网站平台就有亿思网站安全平台检测工具。MDCSOFTSCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入,XSS攻击等。
在脚本语言,如Perl和PHP可以对用户输入的数据进行转义从而来防止SQL注入。
PHP的MySQL扩展提供了mysql_real_escape_string()函数来转义特殊的输入字符。
if(get_magic_quotes_gpc()) { $name=stripslashes($name); } $name=mysql_real_escape_string($name); mysql_query("SELECT*FROMusersWHEREname='{$name}'");
Like语句中的注入
like查询时,如用户输入的值有"_"和"%",则会出现这种情况:用户本只想查询"abcd_",查询结果中却有"abcd_"、"abcde"、"abcdf"等等;用户要查询"30%"(注:百分之三十)时也会出现问题。
在PHP脚本中我们可以使用addcslashes()函数来处理以上情况,如下实例:
$sub=addcslashes(mysql_real_escape_string("%something_"),"%_");
//$sub==\%something\_
mysql_query("SELECT*FROMmessagesWHEREsubjectLIKE'{$sub}%'");
addcslashes()函数在指定的字符前添加反斜杠。
语法格式:
addcslashes(string,characters)
MySQL导出数据
使用SELECT...INTOOUTFILE语句导出数据
将数据表w3cschool_tbl数据导出到/tmp/tutorials.txt文件中:
mysql>SELECT*FROMtutorials_tbl
->INTOOUTFILE'/tmp/tutorials.txt';
生成一个文件,各值用逗号隔开。这种格式可以被许多程序使用。
SELECTa,b,a+bINTOOUTFILE'/tmp/result.text' FIELDSTERMINATEDBY','OPTIONALLYENCLOSEDBY'"' LINESTERMINATEDBY'\n' FROMtest_table;
SELECT...INTOOUTFILE语句有以下属性:
LOADDATAINFILE是SELECT...INTOOUTFILE的逆操作,SELECT句法。为了将一个数据库的数据写入一个文件,使用SELECT...INTOOUTFILE,为了将文件读回数据库,使用LOADDATAINFILE。
SELECT...INTOOUTFILE'file_name'形式的SELECT可以把被选择的行写入一个文件中。该文件被创建到服务器主机上,因此您必须拥有FILE权限,才能使用此语法。
输出不能是一个已存在的文件。防止文件数据被篡改。
你需要有一个登陆服务器的账号来检索文件。否则SELECT...INTOOUTFILE不会起任何作用。
在UNIX中,该文件被创建后是可读的,权限由MySQL服务器所拥有。这意味着,虽然你就可以读取该文件,但可能无法将其删除
导出表作为原始数据
mysqldump是MySQL用于转存储数据库的实用程序。
将数据表tutorials_tbl导出到/tmp目录中:
$mysqldump-uroot-p--no-create-info\
--tab=/tmpW3CSCHOOLw3cschool_tbl
password******
导出整个数据库的数据,可以使用以下命令:
$mysqldump-uroot-pW3CSCHOOL>database_dump.txt
password******
备份所有数据库,可以使用以下命令:
$mysqldump-uroot-p--all-databases>database_dump.txt
password******
在mysqldump命令中指定数据库名及数据表。
在源主机上执行以下命令,将数据备份至dump.txt文件中:
$mysqldump-uroot-pdatabase_nametable_name>dump.txt
password*****
将备份的数据库导入到MySQL服务器中,可以使用以下命令,使用以下命令你需要确认数据库已经创建:
$mysql-uroot-pdatabase_name 以下命令将导出的数据直接导入到远程的服务器上,但请确保两台服务器是相通的,是可以相互访问的:
$mysqldump-uroot-pdatabase_name\
|mysql-hother-host.comdatabase_name
MySQL导入数据
从当前目录中读取文件dump.txt,将该文件中的数据插入到当前数据库的mytbl表中。
mysql>LOADDATALOCALINFILE'dump.txt'INTOTABLEmytbl;
如果用户指定一个FIELDS子句,它的子句(TERMINATEDBY、[OPTIONALLY]ENCLOSEDBY和ESCAPEDBY)也是可选的,不过,用户必须至少指定它们中的一个。
mysql>LOADDATALOCALINFILE'dump.txt'INTOTABLEmytbl
->FIELDSTERMINATEDBY':'
->LINESTERMINATEDBY'\r\n';
指定列的顺序。
如,在数据文件中的列顺序是a,b,c,但在插入表的列顺序为b,c,a,则数据导入语法如下:
mysql>LOADDATALOCALINFILE'dump.txt'
->INTOTABLEmytbl(b,c,a);
使用mysqlimport导入数据
mysqlimport客户端提供了LOADDATAINFILEQL语句的一个命令行接口。mysqlimport的大多数选项直接对应LOADDATAINFILE子句。
从文件dump.txt中将数据导入到mytbl数据表中,可以使用以下命令:
$mysqlimport-uroot-p--localdatabase_namedump.txt
password*****
mysqlimport命令可以指定选项来设置指定格式,命令语句格式如下:
$mysqlimport-uroot-p--local--fields-terminated-by=":"\
--lines-terminated-by="\r\n" database_namedump.txt
password*****
mysqlimport语句中使用--columns选项来设置列的顺序:
$mysqlimport-uroot-p--local--columns=b,c,a\
database_namedump.txt
password*****