python語(yǔ)法基礎(chǔ)要求查詢結(jié)果需要添加字段result大于等于60分則顯示

CREATEDATABASEIFNOTEXISTStestingDEFAULTCHARACTERSETUSECREATETABLEIFNOTEXISTSscore(idINTPRIMARYKEYAUTO_INCREMENT,namevarchar(20),scoreINT)DEFAULTINSERTINTOscore(name,score)VALUES('zhangsan',72),('lisi',('wangwu',('zhaoliu',('maqi',result60pass60分則顯示SELECTname,score,if(score>=60,'pass','fail')ASFROMALTERTABLEADDCONSTRAINTpk_ExamStudentFOREIGNKEY(s_id)REFERENCESALTERTABLEADDCONSTRAINTpk_ExamStudentFOREIGNKEY(s_id)REFERENCESONUPDATEONDELETEs_idexamINSERTINTOexam(s_id,c_id,score,grade)(8265及格ALTERTABLEADDCONSTRAINTpk_ExamCourseFOREIGNKEY(c_id)REFERENCEScourse(c_id)ONUPDATECASCADEONDELETE嘗試使用不存在的c_idexamINSERTINTOexam(s_id,c_id,score,grade)(3591優(yōu)秀數(shù)據(jù)MySQLIsolation性：事務(wù)代碼單獨(dú)開(kāi)來(lái)。在事務(wù)結(jié)束前都不會(huì)被其他會(huì)話打MySQL5.5后，MySQL支持事務(wù)操作 MIT等關(guān)鍵字進(jìn)行事務(wù)操作使用BEGIN開(kāi)啟事務(wù)ROLLBACKCOMMITBEGINbook笑傲江湖hero MIT=0自動(dòng)提CREATEDATABASEIFCREATEDATABASEIFNOTEXISTStransactionDEFAULTCHARACTERSETuseCREATETABLEidINTPRIMARYKEYnamevarchar(20),ageINT)DEFAULTINSERTINTOtest(name,age)('zhangsan',INSERTINTOtest(name,age)('lisi',INSERTINTOtest(name,age)('wangwu',INSERTINTOtest(name,age)VALUES('zhaoliu',36);INSERTINTOtest(name,age)VALUES('maqi',27);pythontry...catch結(jié)合使用。bookhero中嘗試：book添加人物MyISAMMySQL5.5之前只支持MyISAMMySQL5.5InnoDB在MySQL5.0與MySQL5.7中執(zhí)行同樣的創(chuàng)建表格CREATETABLEidINTPRIMARYKEYnamevarchar(20),ageINT)DEFAULTSHOWCREATETABLEMySQL5.7MySQL5.0數(shù)據(jù)獨(dú)立：一旦視圖的結(jié)構(gòu)確定了，可以表結(jié)構(gòu)變化對(duì)用戶的影響，源表增加列對(duì)CREATE[ORCREATE[OR CEVIEWASSELECTWITHCHECKCREATEVIEWCREATEVIEWv_student(id,name,age)SELECT*FROMWITHCHECKDESCDESCCREATEORCEVIEW,)SELECTs_name,CREATEORCEVIEW,)SELECTs_name,s_ageFROMWITHCHECKDESCDESCSELECTSELECT*FROMDROPVIEWDROPVIEWCREATEVIEWv_score( 課程CREATEVIEWv_score( 課程分?jǐn)?shù)等級(jí))SELECTs_name,c_name,score,gradeFROMexamINNERJOINstudentONstudent.s_id=exam.s_idINNERJOINcourseONcourse.c_id=exam.c_idWITHCHECKSELECTSELECT*FROMDMLUPDATEv_student UPDATEv_student DMLWITHCHECKOPTION約束作用：對(duì)視圖所做的DML操作的結(jié)果，不能視圖的WHERE條件的限制。CREATEVIEWv_leader_1(編號(hào)學(xué)生編號(hào)CREATEVIEWv_leader_1(編號(hào)學(xué)生編號(hào)名稱(chēng))SELECTl_id,s_id,l_descFROMleaderWHEREs_id=WITHCHECKSELECTSELECT*FROMUPDATEUPDATEv_leader_1SET學(xué)生編號(hào)=2WHERE編號(hào)=1;因?yàn)榱艘晥D中的wheres_id=1子句，所以拋出異常WITHCHECKOPTION約束時(shí)，(CASCADED)CASCADEDLOCAL選項(xiàng)指定檢查的程度：CREATECREATE[ORRE[ALGORITHM={UNDEFINED|MERGE|TEMTE}][DEFINER={user|CURRENT_USER}][SQLSECURITY{DEFINER|INVOKERVIEWview_nameAS[WITH[CASCADE|LOCAL]CHECK索于快速找出在某個(gè)列中有一特定值的行不使用索引，MySQL必須從第一條記錄開(kāi)始例如：有一張2W2WPhone的字段記錄每個(gè)人的，現(xiàn)在想要查詢出為xxxx的人的信息。如果有了索引，那么會(huì)將Phone字段，通過(guò)一定的方法進(jìn)行，好讓查詢?cè)撟侄?WMySQL的類(lèi)型有兩種：BTREE、HASH。也就是用樹(shù)或者Hash值來(lái)該字段，更詳細(xì)的查找邏輯就需要會(huì)算法的知識(shí)了。（MyISAM和InnoDB引擎：只支持BTREE索引，也就是說(shuō)默認(rèn)使用BTREE，不能夠更換）1.SHOW2.SHOW1.SHOW2.SHOWVARIABLESLIKESETprofiling=SELECT*FROMphoneWHEREphonenum;5.SHOWCREATECREATETABLEidphonenumvarchar(11),CREATETABLEidphonenumcount=f=count=f=open('number.sql',f.write(b'INSERTINTOphone(id,phonenum)VALUES\n')foriinrange(1,count):numnum+value="('%d','%d'),\n"%(i,;SELECT;SELECT*FROMphone1WHEREphonenum;SELECT*FROMphoneWHEREphonenumSHOWSQLSHOW2索引的優(yōu)缺點(diǎn)據(jù)的速度。

、在一個(gè)列上(字段上)不同值較少的不要建立索引，比如在學(xué)生表的""字段全文索引：要求只有在MyISAMCHAR、VARCHAR、TEXT類(lèi)型所屬的記錄行，比"你是個(gè)大，二貨..."通過(guò)大，可能就可以找到該條記CREATETABLE表名CREATETABLE表名[[INDEX|KEY索引名字字段名CREATECREATETABLEidphonenumvarchar(11),SHOWSHOWCREATETABLE注意其中的EXINSELECT*FROMphone1WHEREphonenum 用 EXINSELECT*FROMphone1WHEREphonenum 通過(guò) possible_keyskeykeyphonenum。說(shuō)明使用CREATETABLECREATETABLEidphonenumvarchar(11),UNIQUEINDEXuniqId(id)SHOWSHOWCREATETABLEEXINEXINSELECT*FROMphone2WHEREid=EXEXINSELECT*FROMphone2WHEREid=idid值，則不會(huì)使用索引（possible_keys以及后面的內(nèi)容）CREATETABLECREATETABLEidphonenumvarchar(11),PRIMARYKEY(id)就是常見(jiàn)的PRIMARYSHOWSHOWCREATETABLE注意其中的PRIMARYEXINEXINSELECT*FROMphone3WHEREid=CREATETABLECREATETABLEidphonenumINDEXmultiIdx(id,SHOWSHOWCREATETABLE注意其中的KEYmultidxEXINEXINSELECT*FROMphone4WHEREid=possible_keysmultiIdx5創(chuàng)建全文索引CREATETABLEidphonenumCREATETABLEidphonenumFULLTEXTINDEX略CREATETABLECREATETABLEidgeoSPATIALINDEX其中GEOMETRY表示，另外還可以有POINT表示點(diǎn)。MySQL安裝PhpNow后，通過(guò)sql文件。pythonmysqlpythonMySQLpipinstallpipinstallpymysql-i /simple--trusted-import#通過(guò)connect()方法連接數(shù)據(jù)#參數(shù)1host數(shù)據(jù)庫(kù)主機(jī)名對(duì)應(yīng)-h參數(shù)localhost192.168.8.29#參數(shù)2user連接用戶比如root#參數(shù)3password連接比如#參數(shù)4database選擇的數(shù)據(jù)庫(kù)比如school#參數(shù)5port端比如3306#返回值返回連接的數(shù)據(jù)庫(kù)對(duì)db=pymysql.connect('localhost','root','1234','school2',#對(duì)數(shù)據(jù)庫(kù)進(jìn)行操#。。。#關(guān)閉數(shù)據(jù)import#通過(guò)connect()方法連接數(shù)據(jù)#參數(shù)1host數(shù)據(jù)庫(kù)主機(jī)名對(duì)應(yīng)-h參數(shù)localhost192.168.8.29#參數(shù)2user連接用戶比如root#參數(shù)3password連接比如#參數(shù)4database選擇的數(shù)據(jù)庫(kù)比如school#參數(shù)5port端比如3306#返回值返回連接的數(shù)據(jù)庫(kù)對(duì)db=pymysql.connect('localhost','root','1234','school2',#獲取一個(gè)游標(biāo)對(duì)cursor=#創(chuàng)建表的SQL語(yǔ)sql='''CREATETABLEIFNOTEXISTSstudent(s_idINTPRIMARYKEYs_nameVARCHAR(10),s_ageINT)DEFAULTCHARSET=utf8;#執(zhí)行SQL語(yǔ)#關(guān)閉數(shù)據(jù)import#通過(guò)connect()方法連接數(shù)據(jù)#參數(shù)1host數(shù)據(jù)庫(kù)主機(jī)名對(duì)應(yīng)-h參數(shù)localhost192.168.8.29#參數(shù)2user連接用戶比如root#參數(shù)3password連接比如#參數(shù)4database選擇的數(shù)據(jù)庫(kù)比如school#參數(shù)5port端比如3306#返回值返回連接的數(shù)據(jù)庫(kù)對(duì)db=pymysql.connect('localhost','root','1234','school2',#獲取一個(gè)游標(biāo)對(duì)cursor=#插入操作的sql='''INSERTINTOstudent(s_name,s_age)VALUES('maqi',37);'''sql2='''INSERTINTstudent(s_name,s_age)VALUES('niuba',34);'''exceptExceptionase:#關(guān)閉數(shù)據(jù)import#通過(guò)connect()方法連接數(shù)據(jù)#參數(shù)1host數(shù)據(jù)庫(kù)主機(jī)名對(duì)應(yīng)-h參數(shù)localhost192.168.8.29#參數(shù)2user連接用戶比如root#參數(shù)3password連接比如#參數(shù)4database選擇的數(shù)據(jù)庫(kù)比如school#參數(shù)5port端比如3306#返回值返回連接的數(shù)據(jù)庫(kù)對(duì)db=pymysql.connect('localhost','root','1234','school2',#獲取一個(gè)游標(biāo)對(duì)cursor=sql_param=input('請(qǐng)輸入要?jiǎng)h除的#刪除操作的sql='''DELETEFROMWHEREs_name="%s"'''%exceptExceptionase:#關(guān)閉數(shù)據(jù)import#通過(guò)connect()方法連接數(shù)據(jù)#參數(shù)1host數(shù)據(jù)庫(kù)主機(jī)名對(duì)應(yīng)-h參數(shù)localhost192.168.8.29#參數(shù)2user連接用戶比如root#參數(shù)3password連接比如#參數(shù)4database選擇的數(shù)據(jù)庫(kù)比如school#參數(shù)5port端比如3306#返回值返回連接的數(shù)據(jù)庫(kù)對(duì)db=pymysql.connect('localhost','root','1234','school2',#獲取一個(gè)游標(biāo)對(duì)cursor=#修改操作的sql='''UPDATESETs_age=WHEREs_name=exceptExceptionase:#關(guān)閉數(shù)據(jù)execute()之后，需要把結(jié)果展示出來(lái)import#通過(guò)connect()方法連接數(shù)據(jù)#參數(shù)1host數(shù)據(jù)庫(kù)主機(jī)名對(duì)應(yīng)-h參數(shù)localhost192.168.8.29#參數(shù)2user連接用戶比如root#參數(shù)3password連接比如#參數(shù)4database選擇的數(shù)據(jù)庫(kù)比如school#參數(shù)5port端比如3306#返回值返回連接的數(shù)據(jù)庫(kù)對(duì)db=pymysql.connect('localhost','root','1234','school2',#獲取一個(gè)游標(biāo)對(duì)cursor=sql='''SELECT*FROMstudent'''#執(zhí)行SQL句#獲取查詢的結(jié)果results=#遍歷查詢的結(jié)forrowinresults:name=row[1]age=row[2]print(':%s,:%d'%(name,exceptExceptionase:sql='''SELECTMAX(s_age)FROMstudent'''#執(zhí)行SQL語(yǔ)#獲取查詢的結(jié)results=cursor.fetchone()max_age=results[0]print('最大的是:%d'%exceptExceptionase:#關(guān)閉數(shù)據(jù)importclass始definit(self,host,user,password,db,port=3306):self.host=hostself.user=userself.password=passwordself.db=dbself.port=#連接數(shù)據(jù)def#連接數(shù)據(jù)self.conn=pymysql.connect(self.host,self.user,self.password,self.db,self.port)#獲取一個(gè)游標(biāo)對(duì)self.cursor=#關(guān)閉數(shù)據(jù)defclose(self):#參數(shù)sqlSQL字符#參數(shù)sql_paramSQL的參數(shù)格式為definsert(self,sql,sql_param