MySQL-SQL语法及其使用

MySQLSQL语法及其使用MySQL支持的SQL语句的分类数据库的选取、创建、丢弃和变更USECREATEDATABASEDROPDATABASEALTERDATABASE数据表及索引的创建、变更和丢弃CREATETABLEDROPTABLECREATEINDEXDROPINDEXALTERTABLE获得关于数据库和数据表的信息DESCRIBESHOW从数据表中检索信息SELECTUNION事务处理BEGINCOMMITROLLBACKSETAUTOCOMMIT对数据表里的信息进行检索DELETEINSERTLOADDATAREPLACEUPDATE管理性语句FLUSHGRANTREVOKE考试计分项目namesexstudent_id李艳平M1刘言F2张昆M3王芳芳F4student_idevent_idscore12952277359145821699268236794680event_iddatetype12005-09-03Q22005-09-06Q32005-09-09T42005-09-16Q52005-09-23Q62005-10-01Tstudent数据表score数据表event数据表student_iddate22005-09-0242005-09-0622005-09-20absence数据表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数据查询SELECT[selcet_options]select_list[FROMtbl_list[WHEREwhere_expr][ORDERBY{col_name}[ASC|DESC]][HAVINGwhere_expr][ORDERBY{col_name}[ASC|DESC]][LIMIT[skip_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=‘Q’ANDevent.event_id=score.event_id;结果示例SELECTstudent.name,event.date,score.score,event.typeFROMevent,score,studentWHEREevent.date=20020916ANDevent.event_id=score.event_idANDscore.student_id=student.student_id;结果示例课内练习：查询缺勤学生的姓名和缺勤次数SELECTstudent.student_id,student.name,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,student.name,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和NOTEXISTSIN和NOTINEXISTS和NOTEXISTS子选择这两种形式的子选择将把由外层查询检索到的数据值传递给内部查询，看它们是否满足内层查询所给出的匹配条件。EXISTS和NOTEXISTS子选择非常适合用来检索某个数据表在另一个数据表里有或者没有匹配的记录。SELECT*FROMstudentWHEREEXISTS(SELECT*FROMscoreWHEREstudent.student_id=score.student_id);结果示例SELECT*FROMstudentWHERENOTEXISTS(SELECT*FROMscoreWHEREstudent.student_id=score.student_id);结