mysql語句匯總，mysql語句

mysql語句匯總，mysql語句

前段時間和同學接的項目終於完工了，項目主要是做報表，涉及到了很多的sql語句，所以藉此總結下。

一、基礎1.資料庫相關命令

a>.建立資料庫

CREATE DATABASE 資料庫名

b>.建立資料庫並設定預設字元集

CREATE DATABASE 資料庫名 DEFAULT CHARACTER SET utf8;

c>.刪除資料庫

DROP DATABASE 資料庫名

2.資料表相關命令

a>.刪除表

DROP TABLE 表名;

b>.建立表

DROP TABLE IF EXISTS 表名;CREATE TABLE 表名 (`id`  INT NOT NULL AUTO_INCREMENT,`username`  VARCHAR(60) NOT NULL DEFAULT '' COMMENT '登入名稱',`password`  CHAR(32) NOT NULL DEFAULT '' COMMENT '登入密碼', PRIMARY KEY (`id`),   -- 主鍵 INDEX (`username`)    -- 索引) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='建立表';

c>.修改表名

ALTER TABLE 表名1 RENAME TO 表名2; 

d>.添加列

ALTER TABLE 表名 ADD COLUMN 列名 列類型（如：VARCHAR(30)); 

e>.刪除列

ALTER TABLE 表名 DROP COLUMN 列名; 

f>.幾條簡單的sql語句

選擇：SELECT * FROM 表名 WHERE 範圍;插入：INSERT INTO 表名(欄位名1,欄位名2) VALUES(欄位值1,欄位值2);刪除：DELETE FROM 表名 WHERE 範圍;更新：UPDATE 表名 SET 欄位名=欄位值 WHERE 範圍;尋找：SELECT * FROM 表名 WHERE 欄位名 LIKE ’% 欄位值 %’ ;排序：SELECT * FROM 表名 ORDER BY 欄位名1,欄位名2 [DESC];總數：SELECT COUNT AS totalcount FROM 表名;求和：SELECT SUM(欄位值) AS sumvalue FROM 表名;平均：SELECT AVG(欄位名) AS avgvalue FROM 表名;最大：SELECT MAX(欄位名) AS maxvalue FROM 表名;最小：SELECT MIN(欄位名) AS minvalue FROM 表名;

g>.賦予資料庫使用者權限（每個資料庫都建立單獨的使用者）

CREATE USER '項目使用者'@'localhost' IDENTIFIED BY '項目使用者密碼';  GRANT ALL PRIVILEGES ON 資料庫名稱.* TO 項目使用者@'localhost' ; 

或者

GRANT ALL PRIVILEGES ON 資料庫名稱.* TO 使用者名稱@'%' IDENTIFIED BY '使用者密碼';(用’%'表示從任何地址串連)

h>.添加主鍵

ALTER TABLE 表名 ADD PRIMARY KEY(列名);

i>.建立索引

CREATE INDEX 欄位名 ON 表名;

j>.刪除索引

DROP INDEX 欄位名;

k>.建立視圖

CREATE VIEW 視圖名 ASSELECT 語句;

l>.刪除視圖

DROP VIEW 視圖名;

m>.聯集查詢（這塊有很多內容，我這裡只提出基本的）

結果集列聯合：JOIN ，這裡我常用的是LEFT JOIN ，格式如下：

SELECT a.a, a.b, b.c, b.d FROM a LEFT OUT JOIN b ON a.a = b.c;

結果集行聯合：UNION或者UNION ALL，格式如下（注意列的名稱和個數一定要一致）：

SELECT a.a as col1, a.b as col2 from aUNION ALLSELECT b.c as col1, b.d as col2 from b;

n>.分組:GROUP BY 一般與count,sum,max,min,avg聯合使用

SELECT SUM(欄位名1) FROM 表名 GROUP BY 欄位名2;

o>.前10條記錄

SELECT * FROM 表名 LIMIT 0,9;(mysql沒有top文法，limit一般常用來分頁)

p>.排序（desc降序；asc升序；預設是升序）

SELECT * FROM 表名 ORDER BY 列名 DESC;

二、進階

a>.去重，一般常用 DISTINCT

SELECT DISTINCT * FROM 表名;SELECT DISTINCT(列名) FROM 表名;

b>.複製表

CREATE TABLE 表名2 AS   (     SELECT * FROM 表名1   )        

c>.查詢分析器:在sql前面加上EXPLAIN或者DESC，查看該語句執行情況

EXPLAIN  SELECT * FROM 表名;

三、貼一下做報表時寫的一些噁心的sql

1.根據查詢條件查詢出合格項目編號

SELECT DISTINCT(xp.Id)FROM xmgl_finance_plan xfp LEFT JOIN xmgl_project xp ON xfp.proId = xp.Id WHERE 1=1AND xp.proCategory IN ('ITER973')AND xp.Id IN(0,1,2);

2.-- 查詢專案計劃表裡對應的所有的預算科目對應的合計金額

SELECT  t.Pid,  xsb2.subjectName,  SUM(t.planTotalAmount)    planTotalAmount,  SUM(t.planCurrentAmount)    planCurrentAmount,  SUM(t.total_amount)    total_amount,  SUM(t.payedMoney)    payedMoney,  SUM(t.finalPayment)    finalPayment,  SUM(t.CurYearPayedMoney)    CurYearPayedMoneyFROM (SELECT        xsb.Id,        xsb.subjectName,        xsb.Pid,        SUM(xfp.planTotalAmount)    planTotalAmount,        SUM(xfp.planCurrentAmount)    planCurrentAmount,        SUM(xfp.total_amount)    total_amount,        SUM(xfp.payedMoney)    payedMoney,        SUM(xfp.finalPayment)    finalPayment,        SUM(xfp.CurYearPayedMoney)    CurYearPayedMoney      FROM xmgl_subject_budget xsb        LEFT JOIN xmgl_project xp          ON xsb.type = xp.subjectType        LEFT JOIN xmgl_finance_plan xfp          ON xsb.Id = xfp.subjectId      WHERE xp.Id = 1      GROUP BY xfp.subjectId,xsb.Id      ORDER BY xsb.Id) AS t  LEFT JOIN xmgl_subject_budget xsb2    ON t.Pid = xsb2.IdWHERE t.Pid <> 0GROUP BY t.Pid;

3.-- 查詢各個部門在特定科目下的控制額度及支出資訊(最佳化)

SELECT DISTINCT(xcs1.dept),xsc.subjectName,xcs2.amount,COALESCE(SUM(xfa.amount),0) payedMoney,xcs2.amount-SUM(xfa.amount) leftAmountFROM xmgl_control_subject xcs1LEFT JOIN xmgl_control_subject xcs2 ON xcs1.dept = xcs2.dept AND xcs2.detailId = 3LEFT JOIN xmgl_subject_control xsc ON xcs2.detailId = xsc.IdLEFT JOIN xmgl_finance_activity xfa ON xcs2.dept = xfa.department AND xfa.detailId IN('22','23')GROUP BY xcs1.dept,xcs1.detailId;

4.類似於QQ空間的訊息列表

-- 贊了我SELECT tf.id,1 AS typeId,c.avatar AS avatar, c.name,ct.catalog AS catalog, ct.id AS messageId, ct.content AS trendsContent,NULL AS replyContent, tf.create_time AS createTime,tf.read AS isReadFROM trends_fork tf LEFT JOIN cust_trends ct ON tf.trends_id = ct.id LEFT JOIN customer c ON c.id = tf.customer_idWHERE tf.master_id = 1UNION ALL-- 給我留言SELECT cb.id,2 AS typeId,c.avatar AS avatar,c.name, 3 AS catalog,cb.id AS messageId,NULL AS trendsContent,cb.content AS replyContent,cb.create_time AS createTime,cb.read AS isReadFROM cust_board cb LEFT JOIN customer c ON cb.customer_id = c.id WHERE cb.master_id = 1UNION ALL-- 評論我的動態SELECT  tr.id,3 AS typeId, c.avatar AS avatar, c.name, ct.catalog AS catalog, ct.id AS messageId, ct.content AS trendsContent, tr.content AS replyContent,tr.create_time AS createTime,tr.read AS isReadFROM trends_reply tr LEFT JOIN cust_trends ct ON tr.trends_id = ct.id LEFT JOIN customer c ON c.id = tr.customer_idWHERE tr.master_id = 1UNION ALL-- 回複了我評論的訊息（包括直接回複我的評論、在我的動態下評論別人的回複）SELECT tra.id,4 AS typeId,c.avatar AS avatar, c.name, ct.catalog AS catalog, tra.trends_id AS messageId,ct.content AS trendsContent, tra.content AS replyContent, tra.create_time AS createTime,tra.read AS isReadFROM trends_replyat tra LEFT JOIN  trends_reply tr ON tra.reply_id = tr.id LEFT JOIN customer c ON c.id = tra.customer_id LEFT JOIN cust_trends ct ON ct.id = tr.trends_idWHERE tra.at = 1 OR (tra.master_id = 1 AND tra.customer_id != 1)UNION ALL-- 留言板中回複我的訊息SELECT cbr.id,5 AS typeId,c.avatar AS avatar, c.name, 3 AS catalog, cb.id AS messageId,cb.content AS trendsContent,cbr.content AS replyContent, cbr.create_time AS createTime,cbr.read AS isReadFROM cust_board_reply cbr LEFT JOIN cust_board cb ON cbr.board_id = cb.id LEFT JOIN customer c ON cbr.customer_id = c.idWHERE (cbr.board_customer_id = 1 AND cbr.customer_id != 1 ) OR (cbr.master_id = 1 AND cbr.customer_id != 1) ORDER BY  createTime DESCLIMIT 2,10