Mysql的增刪改查sql語(yǔ)句總結(jié)

#創(chuàng)建數(shù)據(jù)庫(kù)?    CREATE?DATABASE?aa;    #刪除數(shù)據(jù)庫(kù)    DROP?DATABASE?aa;    表的操作  #創(chuàng)建表?create?table?表名    CREATE?TABLE?study(    id?INT?PRIMARY?KEY,    title?CHAR(10)?NOT?NULL,    sex?CHAR(4)?NOT?NULL    );    #刪除表(drop?table?表名)    DROP?TABLE?study    #在表格中插入數(shù)據(jù)（insert?into?表名（屬性）?values(對(duì)應(yīng)的值)）    INSERT?INTO?study(id,title,sex)?VALUES(1,"張三","男");    #更新表格數(shù)據(jù)（update?表名?set?鍵=值,鍵=值?where?條件）注意：修改多個(gè)屬性中間必須要用","隔開(kāi)，其他的符號(hào)無(wú)效,而set只能寫(xiě)一個(gè);  UPDATE?studySET?id=2?,?title="a"?WHERE?id=1    #刪除表中數(shù)據(jù)（delete?from?表名?where?條件）    DELETE?FROM?study?WHERE?id=0;    #查看表中數(shù)據(jù)（select?查詢的東西?from?表名?where?條件）    SELECT?*?FROM?study?WHERE?id=1    SELECT?*?FROM?study?ORDER?BY?age?DESC    #注意：order?by?字段名?asc?為升序，order?by?字段名?desc?為降序    #Mysql添加字段名(既列名)???（alter?table?表名?add?列名?字段類(lèi)型）    ALTER?TABLE?study??ADD?tell?CHAR(20)    ALTER?TABLE?study?ADD?address?CHAR(50)?AFTER?sex    #刪除列（alter?table?表名?drop?列名）    ALTER?TABLE?study?DROP?address    #修改列屬性（alter?table?表名?change?需要修改的列名?修改后的列名?新列名字段類(lèi)型）    ALTER?TABLE?study?CHANGE?telphone?tel?CHAR(20)?DEFAULT?"-"  ALTER?TABLE?study?CHANGE?tel  ALTER?TABLE?study?RENAME?stu簡(jiǎn)單函數(shù)    SELECT?SUM(age)?AS?"總年齡"?FROM??study  SELECT?AVG(age)?AS?"平均年齡"?FROM?study  SELECT?MAX(age)?AS?"最大年齡"?FROM?study  SELECT?COUNT(id)?AS?"人數(shù)"?FROM?study(統(tǒng)計(jì)人數(shù)選擇主鍵不然可為空的列會(huì)影響結(jié)果)

<br/><span style="color: rgb(0, 0, 0);">如果要生成下列結(jié)果, 該如何寫(xiě)sql語(yǔ)句?<br/><br/>勝 負(fù)2005-05-09  2  22005-05-10  1  2</span><span style="color: #800080"><br/><br/></span><span style="color: #000000">SELECT DATA AS " ",SUM(result='勝') AS "勝" ,SUM(result='負(fù)') AS "負(fù)" FROM test1 GROUP BY DATA <br/><br/>Case when(case 屬性=“” then “” end)　　　　　　</span>

<span style="color: #000000"><br/>寫(xiě)出由table1.table2得到table3的sql語(yǔ)句<span style="color: #ff9900"><br/></span><br/>@@##@@@@##@@<br/><br/>@@##@@</span><span style="color: #000000"> <br/></span>

)   AS = THEN 業(yè)績(jī)yj ELSE NULL END) AS = THEN 業(yè)績(jī)yj ELSE NULL END)     AS FROM table1 t1 LEFT JOIN table2 t2 ON t1.部門(mén)dep=

<span style="color: #000000">用一條SQL語(yǔ)句查詢出每門(mén)課都大于80分的學(xué)生姓名(表名為score)  　　Select distinct(去重) name from score where name not in(<br/><br/>　　　　Select name from score where fenshu<=80<br/>　　　　<br/>　　);</span><span style="color: #000000"><span style="color: #ff0000"><br/><br/></span><span style="font-size: 16px; color: rgb(0, 0, 0);"><strong>Union all、union</strong></span>（下分別為t1.t1）@@##@@@@##@@ </span>

SELECT?*?FROM?t1?UNION?ALL?SELECT?*?FROM?t2?(不去除重復(fù))===》t3    SELECT?*?FROM?t1?UNION??SELECT?*?FROM?t2?（去除重復(fù)）=====>t4