#創(chuàng)建數(shù)據(jù)庫(kù)CREATE DATABASE mytest CHARACTER SET gbk#刪除數(shù)據(jù)庫(kù)DROP DATABASE mytest表的操作#創(chuàng)建表（create table 表名（columns））CREATE TABLE students(id INT PRIMARY KEY,NAME CHAR(10) NOT NULL,sex CHAR(4) NOT NULL);#刪除表(drop table 表名)DROP TABLE students#在表格中插入數(shù)據(jù)（insert into 表名（屬性） values(對(duì)應(yīng)的值)）INSERT INTO students(id,NAME,sex) VALUES(1,"張三","男")#更新表格數(shù)據(jù)（update 表名 set 鍵=值,鍵=值 where 條件）中間要用“，”隔開(kāi)，其他的無(wú)效  set只需寫(xiě)一個(gè)UPDATE students SET id=2 , NAME="a" WHERE id=1#刪除表中數(shù)據(jù)（delete from 表名 where 條件）DELETE FROM students WHERE id=0#查看表中數(shù)據(jù)（select 查詢的東西 from 表名 where 條件）SELECT * FROM students WHERE id=1SELECT * FROM students ORDER BY age DESC(order by升序，order by 列名 desc降序)列#添加列（alter table 表名 add 列名 字段類(lèi)型）ALTER TABLE students ADD tel CHAR(20)ALTER TABLE students ADD address CHAR(50) AFTER sex#刪除列（alter table 表名 drop 列名）ALTER TABLE students DROP address#修改列屬性（alter table 表名 change 需要修改的列名 修改后的列名 新列名字段類(lèi)型）ALTER TABLE stu CHANGE telphone tel CHAR(20) DEFAULT "-"ALTER TABLE students CHANGE telALTER TABLE students RENAME stu簡(jiǎn)單函數(shù)SELECT SUM(age) AS "總年齡" FROM studentsSELECT AVG(age) AS "平均年齡" FROM studentsSELECT MAX(age) AS "最大年齡" FROM studentsSELECT COUNT(id) AS "人數(shù)" FROM students(統(tǒng)計(jì)人數(shù)選擇主鍵不然可為空的列會(huì)影響結(jié)果)分組表內(nèi)容：2005-05-09 勝2005-05-09 勝2005-05-09 負(fù)2005-05-09 負(fù)2005-05-10 勝2005-05-10 負(fù)2005-05-10 負(fù)    　　　　如果要生成下列結(jié)果, 該如何寫(xiě)sql語(yǔ)句?      勝 負(fù)2005-05-09 2 22005-05-10 1 2SELECT DATA AS " ",SUM(result='勝') AS "勝" ,SUM(result='負(fù)') AS "負(fù)" FROM test1 GROUP BY DATA Case when(case 屬性=“” then “” end)　　　　　　寫(xiě)出由table1.table2得到table3的sql語(yǔ)句 SELECT t1.部門(mén)dep,SUM(CASE WHEN 月份mon='一月份' THEN 業(yè)績(jī)yj ELSE NULL END) AS '一月份',SUM(CASE WHEN 月份mon='二月份' THEN 業(yè)績(jī)yj ELSE NULL END) AS '二月份',SUM(CASE WHEN 月份mon='三月份' THEN 業(yè)績(jī)yj ELSE NULL END) AS '三月份' FROM table1 t1 LEFT JOIN table2 t2 ON t1.部門(mén)dep=t2.部門(mén)dep GROUP BY 部門(mén)dep左連接與右連接(left join 表名 on 連接語(yǔ)句)Left join與right join區(qū)別就是：左連接是以主表為主，顯示所有內(nèi)容，若連接的表沒(méi)有與它對(duì)應(yīng)的值則不顯示或顯示為null，右連接同理。內(nèi)連接用一條SQL語(yǔ)句查詢出每門(mén)課都大于80分的學(xué)生姓名(表名為score)　　Select distinct(去重) name from score where name not in(　　　　Select name from score where fenshu<=80;　　);Union all、union（下分別為t1.t1）SELECT * FROM t1 UNION ALL SELECT * FROM t2 (不去除重復(fù))===》t3SELECT * FROM t1 UNION SELECT * FROM t2  （去除重復(fù)）=====>t4（上分別為t3.t4）