MySQL_SQL语法及其使用

MySQLSQL语法及其使用,MySQL支持的SQL语句的分类,数据库的选取、创建、丢弃和变更USECREATEDATABASEDROPDATABASEALTERDATABASE,数据表及索引的创建、变更和丢弃CREATETABLEDROPTABLECREATEINDEXDROPINDEXALTERTABLE,获得关于数据库和数据表的信息DESCRIBESHOW,从数据表中检索信息SELECTUNION,事务处理BEGINCOMMITROLLBACKSETAUTOCOMMIT,对数据表里的信息进行检索DELETEINSERTLOADDATAREPLACEUPDATE,管理性语句FLUSHGRANTREVOKE,考试计分项目,student数据表,score数据表,event数据表,absence数据表,3.1数据库的选定、创建、丢弃和变更,数据库的选定usedb_name;use语句选定一个数据库并将它当作MySQL指定服务器连接上的默认（当前）数据库。即使已经将某个数据库选定为当前的默认数据库，也可以通过db_name.tbl_name形式的名字去访问其它数据库中的数据表。select*fromdb_name.tbl_name;数据库的创建createdatabasedb_name,数据库的丢弃dropdatabasedb_name;数据库的变更alterdatabasedb_namedefaultcharactersetcharset;,3.2数据表的创建、丢弃、索引和变更数据表的创建CREATETABLEstudent(nameVARCHAR(20)NOTNULL,sexENUM(F,M)NOTNULL,student_idINTUNSIGNEDNOTNULLAUTO_INCREMENT,PRIMARYKEY(student_id);CREATETABLEscore(student_idINTUNSIGNEDNOTNULL,event_idINTUNSIGNEDNOTNULL,PRIMARYKEY(event_id,student_id),scoreINTNOTNULL);,CREATETABLEevent(dateDATENOTNULL,typeENUM(T,Q)NOTNULL,event_idINTUNSIGNEDNOTNULLAUTO_INCREMENT,PRIMARYKEY(event_id);CREATETABLEabsence(student_idINTUNSIGNEDNOTNULL,dateDATENOTNULL,PRIMARYKEY(student_id,date);,数据表的丢弃droptabletbl_name;数据表的索引MySQL能够创建以下几种类型的索引：1普通索引2唯一化索引3供全文本使用的FULLTEXT索引创建索引的方法1使用createtable语句在创建一个数据表的同时创建索引2使用createindex语句创建索引createindexindex_nameontbl_name(index_columns);createuniqueindexindex_nameontbl_name(index_columns);createfulltextindexindex_nameontbl_name(index_columns);3使用altertable语句将索引添加到现在的数据表上去altertabletbl_nameaddindexindex_name(index_columns);altertabletbl_nameadduniqueindex_name(index_columns);altertabletbl_nameaddpriamrykey(index_columns);altertabletbl_nameaddfulltext(index_columns);,索引的丢弃dropindexindex_nameontbl_name;altertabletbl_namedropindexindex_name;变更数据表的结构重命名数据表altertabletbl_namerenametonew_tbl_name;改变数据列类型altertabletbl_namemodifycolumns,3.3获取关于数据库和数据表的信息列出服务器管理着的数据库showdatabases;列出当前或指定数据库里的数据表；showtables;showtablesfromdb_name;显示有关数据表中的数据列或索引信息showcolumnsfromtbl_name;showindexfromtbl_name;显示关于当前数据库或指定数据库中数据表的描述信息showtablestatus;showtablestatusfromdb_name;显示与数据表的当前结构相对应的createtable语句showcreatetabletbl_name;,3.4插入、删除、修改数据记录,插入数据记录可以一次性列出全部的数据insertintotbl_namevalues(value1,value2,);可以用一条insert语句将多个数据行插入到数据表中去insertintotbl_namevalues(),(),;可以直接对数据列进行赋值，先给出数据列的名字，再列出它的值insertintotbl_name(col_name1,col_name2,)values(value1,value2,);删除数据记录deletefromtbl_namewherewhere_expr;修改数据记录updatetbl_namesetwhichcolumnstochangewherewhichrecordtoupdate;,3.5数据查询,SELECTselcet_optionsselect_listFROMtbl_listWHEREwhere_exprORDERBYcol_nameASC|DESCHAVINGwhere_exprORDERBYcol_nameASC|DESCLIMITskip_count,SHOW_COUNT注意：（1）可以生成统计信息（2）通配符号_匹配单个的任意字符%能匹配任何一个字符序列（包括空序列在内）。,涉及多个数据表的查询操作SELECT语句的基本语法SELECTselection_list#选择哪些数据列FROMtable_list从哪些数据表选择数据行WHEREprimary_constraint数据行必须满足哪些条件GROUPBYgrouping_colums如何对结果分组ORDERBYsorting_columns如何对结果进行排序HAVINGsecondary_constraint数据行必须满足的次要条件LIMITcount;对结果个数的限制正确写出一个查询的关键是怎样才能把个有关数据表按正确的顺序关联起来。,单关联查询只设计一个数据表mysqlSELECT*FROMstudent;全关联在SELECT语句的FROM子句给出了多个数据表的名字关联操作JOIN或“,”SELECTstudent_id,date,score,typeFROMeventjoinscoreWHEREdate=20020906ANDtype=QANDevent.event_id=score.event_id;结果示例SELECT,event.date,score.score,event.typeFROMevent,score,studentWHEREevent.date=20020916ANDevent.event_id=score.event_idANDscore.student_id=student.student_id;结果示例,课内练习：查询缺勤学生的姓名和缺勤次数SELECTstudent.student_id,,count(absence.date)asabsencesFROMstudent,absenceWHEREstudent.student_id=absence.student_idGROUPBYstudent.student_id;结果示例,左关联（右关联）LEFTJOIN的工作情况：首先，要指定一个或多个用来匹配的两个数据表里的数据行的数据列。当左数据表里的某个数据行与右数据表里的某个数据行相匹配时，这两个数据行的内容将被选取为一个输出行。当左数据表里的某个数据行在右数据表里没有找到任何匹配时，它也将被选取为一个输出行，但这时它将与右数据表里的一个“伪造”数据行（这个“伪造”数据行的所有数据列都将被设置为NULL）进行组合。DELETEFROMscoreWHEREstudent_id19;SELECTstudent.*,score.*FROMstudentLEFTJOINscoreONstudent.student_id=score.student_id;结果示例,左关联能够将左数据表有的，而在右数据表中没有匹配的数据行找出来SELECTstudent.*FROMstudentLEFTJOINscoreONstudent.student_id=score.student_idWHEREscore.scoreISNULL;结果示例,课堂练习：,找出考试缺勤的同学，以便安排补考SELECTstudent.student_id,,event.event_id,event.date,event.typeFROMstudent,eventLEFTJOINscoreONstudent.student_id=score.student_idANDevent.event_id=score.event_idWHEREscore.scoreISNULLORDERBYstudent.student_id,event.event_id;结果示例,使用子选择（从MySQL4.1版本才开始支持）允许将一个SELECT查询嵌套在另一个SELECT查询当中SELECT*FROMscoreWHEREevent_idIN(SELECTevent_idFROMeventWHEREtype=Q)；结果示例使用子选择来生成一个参考值由内层的SELECT语句来检索出一个数据值，然后把这个数据值用在外层SELECT语句的比较操作中。SELECT*FROMscoreWHEREevent_id=(SELECTevent_idFROMeventWHEREdate=20020923ANDtype=Q)；结果示例EXISTS和NOTEXISTSIN和NOTIN,EXISTS和NOTEXISTS子选择这两种形式的子选择将把由外层查询检索到的数据值传递给内部查询，看它们是否满足内层查询所给出的匹配条件。EXISTS和NOTEXISTS子选择非常适合用来检索某个数据表在另一个数据表里有或者没有匹配的记录。SELECT*FROMstudentWHEREEXISTS(SELECT*FROMscoreWHEREstudent.student_id=score.student_id);结果示例SELECT*FROMstudentWHERENOTEXISTS(SELECT*FROMscoreWHEREstudent.student_id=score.student_id);结果示例,IN和NOTIN子选择在IN和NOTIN子选择里，内层SELECT语句应该返回且只返回一个数据列，这个数据列的值将由外层SELECT语句中的比较操作进行求值。SELECT*FROMstudentWHEREstudent_idIN(SELECTstudent_idFROMscore);SELECT*FROMstudentWHEREstudent_idNOTIN(SELECTstudent_idFROMscore);可以将子选择改写为关联查询：（1）匹配型子选择查询的改写SELECT*FROMtable1WHEREcolumn1IN(SELECTcolumn2aFROMtable2WHEREcolumn2b=value)改写为SELECTtable1.*FROMtable1,table2WHEREtable1.column1=table2.column2aANDtable2.column2b=value);,SELECT*FROMscoreWHEREevent_idIN(SELECTevent_idFROMeventWHEREtype=T);结果示例改为SELECTscore.*FROMscore,eventWHEREscore.event_id=event.event_idANDevent.type=T;结果示例,（2）非匹配（即缺失）型子选择查询的改写SELECT*FROMtable1WHEREcolumn1NOTIN(SELECTcolumn2FROMtable2）;改写为SELECTtable1.*FROMtable1LEFTJOINtable2ONtable1.column1=table2.column2WHEREtable2.column2ISNULL;SELECT*FROMstudentWHEREstudent_idNOTIN(SELECTstudent_idFROMabsence);结果示例改写为SELECTstudent.*FROMstudentLEFTJOINabsenceONstudent.student_id=absence.student_idWHEREabsence.student_idISNULL;结果示例,涉及多个表的UNION查询依次从多个数据表选取记录SELECT*FROMstudentUNIONselect*FROMscore结果示例UNIONUNION语句的特点：（1）在UNION查询的结果集中，输出列的名字和类型将由UNION查询中的第一个SELECT所选取的数据列的名字和类型来决定。UNION查询的第二个以及以后的SELECT所选取的数据列的个数必须和第一个SELECT相同，但它们的名字和类型却不必相同（2）在默认的情况下，UNION会把结果集中的重复项去掉，如果想保留重复项，在第一个UNION的后面加上关键字ALL。（3）如果想对UNION结果进行排序，可以在最后一个SELECT语句的后面加上一个ORDERBY子句，这个ORDERBY子句将作用于UNION查询的最终结果。UNION查询中的各个SELECT语句也允许有它自己的ORDERBY子句。在这样做时必须把SELECT语句和它的ORDERBY子句括起来,（4）LIMIT语句也能用在UNION查询中，具体用法于ORDERBY差不多。（5）UNION查询可以利用不同的筛选条件去选取同一个数据表的不同子集。涉及多个数据表的删除和修改操作将所涉及的数据表列举在FORM子句里，再把用来找出待删除记录的匹配条件写在WHERE子句中。DELETEtable1FROMtable1,table2WHEREtable1.id=table2.id;涉及多个数据表的DELETE语句还允许用同一条语句从多个数据表里同时把有关的记录都删除掉。DELETEtable1,table2FROMtable1,table2WHEREtable1.id=table2.id;DELETEstudent,scoreFROMstudent,scoreWHEREstudent.student_id=score.student_idANDstudent.student_id=1;结果示例涉及多个数据表的删除操作还可以利用外键机制来实现。,事务处理事务是一组SQL语句，在执行时被视为一个不可中断的逻辑单元。事务处理的四个特征：（1）原子性。组成事务的语句形成一个逻辑单元，不能只执行其中的一部分。（2）一致性。在事务处理执行的前后，数据库是一致的。（3）隔离性。一个事务处理对另一个事务处理没有影响。（4）持续性。当事务处理成功执行结束后，其效果在数据库中被永久的记录下来。进行事务处理的步骤：先使用BEGIN语句来禁止自动提交模式，然后依次执行事务的各条语句，最后如果事务处理过程中没有发生错误，用COMMIT语句来提交事务并使修改生效，否则发出ROLLBACK语句来撤销那些修改。CREATETABLEbooks(idVARCHAR(8)PRIMARYKEY,titleVARCHAR(96),priceFLOAT,descriptionVARCHAR(30)type=innodb;示例,外键与引用完整性外键关系允许把某一个数据表里的一个索引声明为与另一个数据表里的一个索引有关联关系，还允许对外键所在的数据表设置一些操作处理方面的约束条件。数据库将根据外键关系所定义的规则来维护引用完整性。MySQL里的外键支持是由InnoDB数据表处理程序提供的。父表：是包含着原始键值的数据表。子表：是引用父表中键值的数据表，这种引用使它和父表构成依赖关系父表和子表是通过父表中的键值而关联在一起的，具体的说，就是子表中的索引将引用父表中的索引，它的值或者必须与父表中的某个值相匹配，或者将被设置为NULL，以表明父表中没有与之对应的记录。外键可以设置为不能为NULL的情况，此时，每一个键值都必须与父表中的某个值相匹配。语法FOREIGNKEYindex_name(index_columns)REFERENCEStbl_name(index_columns)ONDELETEaction,FOREIGNKEY后面的index_columns部分列出了构成子表中的这个索引的各有关数据列，它们必须和父表中的索引值相匹配REFERENCES子句中的tbl_name和index_columns分别给出了父表的名字和与子表中的外键相对应的父表索引数据列的名字。REFERENCES子句里的index_columns部分必须与关键字FOREIGNKEY后面的index_columns部分有相同数目的数据列。ONDELETE用来设定在父表记录被删除时子表将发生什么情况。ONDELETECASCADE级联删除ONDELETESETNULL设置为NULL注意事项：（1）构成外键关系的父表数据列和子表数据列在它们各自所在的数据表中都必须被索引。（2）父表索引和子表索引中相互对应的数据列必须是兼容的类型,管理MySQL用户帐户指定哪些用户可以连接到MySQL服务器？它们可以从哪些地方去连接MySQL服务器？连接上MySQL服务器后能进行怎样的操作？这些信息存放在mysql数据库中的各个权限表中。GRANT：创建MySQL帐户并设定它的权限。REVOKE：收回现有的MySQL帐户的权限。GRANT和REVOKE语句会影响到的四个权限表,（1）创建MySQL用户帐户并进行授权GRANTprivilegesprivileges(columns)ONwhatTOaccountIDENTIFIEDBYpasswod；privileges授予有关帐户的权限。,columns有关权限将作用于哪些数据列上（只有在需要设置数据列级权限时才必须给出这条语句）。what有关权限的级别。最高级别是全局级，即给定权限将作用于所有的数据库和所有的数据表；可以将全局级权限视为超级用户权限。有关权限还可以被设定为数据库级、数据表级或者数据列级（给出一条columns子句时）。,account被授有关权限的帐户。account值以user_namehost_name格式给出的一个用户名和一个主机名构成。passwod有关帐户的口令。示例从某一台主机访问GRANTALLONksjf.*TOheyanlocalhostIDENTIFIEDBY12345;GRANTALLONksjf.*TOheyanIDENTIFIEDBY12345;GRANTALLONksjf.*TOheyanIDENTIFIEDBY12345;从任意主机访问GRANTALLONksjf.*TOheyanIDENTIFIEDBY12345;从一组有限的主机访问GRANTALLONksjf.*TOheyanIDENTIFIEDBY12345;GRANTALLONksjf.*TOheyan192.168.128.%IDENTIFIEDBY12345;,示例1从某一台主机访问GRANTALLONksjf.*TOheyanlocalhostIDENTIFIEDBY12345;GRANTALLONksjf.*TOheyanIDENTIFIEDBY12345;GRANTALLONksjf.*TOheyanIDENTIFIEDBY12345;从任意主机访问GRANTALLONksjf.*TOheyanIDENTIFIEDBY12345;从一组有限的主机访问GRANTA