python語法基礎(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ù)代碼單獨開來。在事務(wù)結(jié)束前都不會被其他會話打MySQL5.5后，MySQL支持事務(wù)操作 MIT等關(guān)鍵字進行事務(wù)操作使用BEGIN開啟事務(wù)ROLLBACKCOMMITBEGINbook笑傲江湖hero MIT=0自動提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ù)獨立：一旦視圖的結(jié)構(gòu)確定了，可以表結(jié)構(gòu)變化對用戶的影響，源表增加列對CREATE[ORCREATE[OR CEVIEWASSELECTWITHCHECKCREATEVIEWCREATEVIEWv_student(id,name,age)SELECT*FROMWITHCHECKDESCDESCCREATEORCEVIEW,)SELECTs_name,CREATEORCEVIEW,)SELECTs_name,s_ageFROMWITHCHECKDESCDESCSELECTSELECT*FROMDROPVIEWDROPVIEWCREATEVIEWv_score( 課程CREATEVIEWv_score( 課程分?jǐn)?shù)等級)SELECTs_name,c_name,score,gradeFROMexamINNERJOINstudentONstudent.s_id=exam.s_idINNERJOINcourseONcourse.c_id=exam.c_idWITHCHECKSELECTSELECT*FROMDMLUPDATEv_student UPDATEv_student DMLWITHCHECKOPTION約束作用：對視圖所做的DML操作的結(jié)果，不能視圖的WHERE條件的限制。CREATEVIEWv_leader_1(編號學(xué)生編號CREATEVIEWv_leader_1(編號學(xué)生編號名稱)SELECTl_id,s_id,l_descFROMleaderWHEREs_id=WITHCHECKSELECTSELECT*FROMUPDATEUPDATEv_leader_1SET學(xué)生編號=2WHERE編號=1;因為了視圖中的wheres_id=1子句，所以拋出異常WITHCHECKOPTION約束時，(CASCADED)CASCADEDLOCAL選項指定檢查的程度：CREATECREATE[ORRE[ALGORITHM={UNDEFINED|MERGE|TEMTE}][DEFINER={user|CURRENT_USER}][SQLSECURITY{DEFINER|INVOKERVIEWview_nameAS[WITH[CASCADE|LOCAL]CHECK索于快速找出在某個列中有一特定值的行不使用索引，MySQL必須從第一條記錄開始例如：有一張2W2WPhone的字段記錄每個人的，現(xiàn)在想要查詢出為xxxx的人的信息。如果有了索引，那么會將Phone字段，通過一定的方法進行，好讓查詢該字段2WMySQL的類型有兩種：BTREE、HASH。也就是用樹或者Hash值來該字段，更詳細(xì)的查找邏輯就需要會算法的知識了。（MyISAM和InnoDB引擎：只支持BTREE索引，也就是說默認(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)缺點據(jù)的速度。

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