MySQL语句汇总

一、基础

1.数据库相关命令

a>.创建数据库

[sql]  view plain copy print ?

1. CREATE DATABASE 数据库名  

b>.创建数据库并设置默认字符集

[sql]  view plain copy print ?

1. CREATE DATABASE 数据库名 DEFAULT CHARACTER SET utf8;  

c>.删除数据库

[sql]  view plain copy print ?

1. DROP DATABASE 数据库名  

2.数据表相关命令

a>.删除表

[sql]  view plain copy print ?

1. DROP TABLE 表名;  

b>.新建表

[sql]  view plain copy print ?

1. DROP TABLE IF EXISTS 表名;  
2. CREATE TABLE 表名 (  
3. `id`  INT NOT NULL AUTO_INCREMENT,  
4. `username`  VARCHAR(60) NOT NULL DEFAULT '' COMMENT '登录名',  
5. `password`  CHAR(32) NOT NULL DEFAULT '' COMMENT '登录密码',  
6.  PRIMARY KEY (`id`),   -- 主键  
7.  INDEX (`username`)    -- 索引  
8. ) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='新建表';  

c>.修改表名

[sql]  view plain copy print ?

1. ALTER TABLE 表名1 RENAME TO 表名2;   

d>.添加列

[sql]  view plain copy print ?

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

e>.删除列

[sql]  view plain copy print ?

1. ALTER TABLE 表名 DROP COLUMN 列名;   

f>.几条简单的sql语句

[sql]  view plain copy print ?

1. 选择：SELECT * FROM 表名 WHERE 范围;  
2. 插入：INSERT INTO 表名(字段名1,字段名2) VALUES(字段值1,字段值2);  
3. 删除：DELETE FROM 表名 WHERE 范围;  
4. 更新：UPDATE 表名 SET 字段名=字段值 WHERE 范围;  
5. 查找：SELECT * FROM 表名 WHERE 字段名 LIKE ’% 字段值 %’ ;  
6. 排序：SELECT * FROM 表名 ORDER BY 字段名1,字段名2 [DESC];  
7. 总数：SELECT COUNT AS totalcount FROM 表名;  
8. 求和：SELECT SUM(字段值) AS sumvalue FROM 表名;  
9. 平均：SELECT AVG(字段名) AS avgvalue FROM 表名;  
10. 最大：SELECT MAX(字段名) AS maxvalue FROM 表名;  
11. 最小：SELECT MIN(字段名) AS minvalue FROM 表名;  

g>.赋予数据库用户权限（每个数据库都创建单独的用户）

[sql]  view plain copy print ?

1. CREATE USER '项目用户'@'localhost' IDENTIFIED BY '项目用户密码';    
2. GRANT ALL PRIVILEGES ON 数据库名称.* TO 项目用户@'localhost' ;   

或者

[sql]  view plain copy print ?

1. GRANT ALL PRIVILEGES ON 数据库名称.* TO 用户名@'%' IDENTIFIED BY '用户密码';(用’%'表示从任何地址连接)  

h>.添加主键

[sql]  view plain copy print ?

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

i>.创建索引

[sql]  view plain copy print ?

1. CREATE INDEX 字段名 ON 表名;  

j>.删除索引

[sql]  view plain copy print ?

1. DROP INDEX 字段名;  

k>.创建视图

[sql]  view plain copy print ?

1. CREATE VIEW 视图名 AS  
2. SELECT 语句;  

l>.删除视图

[sql]  view plain copy print ?

1. DROP VIEW 视图名;  

m>.联合查询（这块有很多内容，我这里只提出基本的）

结果集列联合：JOIN ，这里我常用的是LEFT JOIN ，格式如下：

[sql]  view plain copy print ?

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

结果集行联合：UNION或者UNION ALL，格式如下（注意列的名称和个数一定要一致）：

[sql]  view plain copy print ?

1. SELECT a.a as col1, a.b as col2 from a  
2. UNION ALL  
3. SELECT b.c as col1, b.d as col2 from b;  

n>.分组:GROUP BY 一般与count,sum,max,min,avg联合使用

[sql]  view plain copy print ?

1. SELECT SUM(字段名1) FROM 表名 GROUP BY 字段名2;  

o>.前10条记录

[sql]  view plain copy print ?

1. SELECT * FROM 表名 LIMIT 0,9;(mysql没有top语法，limit一般常用来分页)  

p>.排序（desc降序；asc升序；默认是升序）

[sql]  view plain copy print ?

1. SELECT * FROM 表名 ORDER BY 列名 DESC;  

二、进阶

a>.去重，一般常用 DISTINCT

[sql]  view plain copy print ?

1. SELECT DISTINCT * FROM 表名;  
2. SELECT DISTINCT(列名) FROM 表名;  

b>.复制表

[sql]  view plain copy print ?

1. CREATE TABLE 表名2 AS     
2. (     
3.   SELECT * FROM 表名1     
4. )     
5.        

c>.查询分析器:在sql前面加上EXPLAIN或者DESC，查看该语句执行情况

[sql]  view plain copy print ?

1. EXPLAIN  SELECT * FROM 表名;  

三、贴一下做报表时写的一些恶心的sql

1.根据查询条件查询出符合条件的项目编号

[sql]  view plain copy print ?

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

2.-- 查询项目计划表里对应的所有的预算科目对应的合计金额

[sql]  view plain copy print ?

1. SELECT  
2.   t.Pid,  
3.   xsb2.subjectName,  
4.   SUM(t.planTotalAmount)    planTotalAmount,  
5.   SUM(t.planCurrentAmount)    planCurrentAmount,  
6.   SUM(t.total_amount)    total_amount,  
7.   SUM(t.payedMoney)    payedMoney,  
8.   SUM(t.finalPayment)    finalPayment,  
9.   SUM(t.CurYearPayedMoney)    CurYearPayedMoney  
10. FROM (SELECT  
11.         xsb.Id,  
12.         xsb.subjectName,  
13.         xsb.Pid,  
14.         SUM(xfp.planTotalAmount)    planTotalAmount,  
15.         SUM(xfp.planCurrentAmount)    planCurrentAmount,  
16.         SUM(xfp.total_amount)    total_amount,  
17.         SUM(xfp.payedMoney)    payedMoney,  
18.         SUM(xfp.finalPayment)    finalPayment,  
19.         SUM(xfp.CurYearPayedMoney)    CurYearPayedMoney  
20.       FROM xmgl_subject_budget xsb  
21.         LEFT JOIN xmgl_project xp  
22.           ON xsb.type = xp.subjectType  
23.         LEFT JOIN xmgl_finance_plan xfp  
24.           ON xsb.Id = xfp.subjectId  
25.       WHERE xp.Id = 1  
26.       GROUP BY xfp.subjectId,xsb.Id  
27.       ORDER BY xsb.Id) AS t  
28.   LEFT JOIN xmgl_subject_budget xsb2  
29.     ON t.Pid = xsb2.Id  
30. WHERE t.Pid <> 0  
31. GROUP BY t.Pid;  

3.-- 查询各个部门在特定科目下的控制额度及支出信息(优化)

[sql]  view plain copy print ?

1. SELECT DISTINCT(xcs1.dept),xsc.subjectName,xcs2.amount,COALESCE(SUM(xfa.amount),0) payedMoney  
2. ,xcs2.amount-SUM(xfa.amount) leftAmount  
3. FROM xmgl_control_subject xcs1  
4. LEFT JOIN xmgl_control_subject xcs2 ON xcs1.dept = xcs2.dept AND xcs2.detailId = 3  
5. LEFT JOIN xmgl_subject_control xsc ON xcs2.detailId = xsc.Id  
6. LEFT JOIN xmgl_finance_activity xfa ON xcs2.dept = xfa.department AND xfa.detailId IN('22','23')  
7. GROUP BY xcs1.dept,xcs1.detailId;  

4.类似于QQ空间的消息列表

[sql]  view plain copy print ?

1. -- 赞了我  
2. 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 isRead  
3. FROM trends_fork tf LEFT JOIN cust_trends ct ON tf.trends_id = ct.id LEFT JOIN customer c ON c.id = tf.customer_id  
4. WHERE tf.master_id = 1  
5. UNION ALL  
6. -- 给我留言  
7. 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 isRead  
8. FROM cust_board cb LEFT JOIN customer c ON cb.customer_id = c.id WHERE cb.master_id = 1  
9. UNION ALL  
10. -- 评论我的动态  
11. 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 isRead  
12. FROM trends_reply tr LEFT JOIN cust_trends ct ON tr.trends_id = ct.id LEFT JOIN customer c ON c.id = tr.customer_id  
13. WHERE tr.master_id = 1  
14. UNION ALL  
15. -- 回复了我评论的消息（包括直接回复我的评论、在我的动态下评论别人的回复）  
16. 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 isRead  
17. FROM 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_id  
18. WHERE tra.at = 1 OR (tra.master_id = 1 AND tra.customer_id != 1)  
19. UNION ALL  
20. -- 留言板中回复我的消息  
21. 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 isRead  
22. FROM cust_board_reply cbr LEFT JOIN cust_board cb ON cbr.board_id = cb.id LEFT JOIN customer c ON cbr.customer_id = c.id  
23. WHERE (cbr.board_customer_id = 1 AND cbr.customer_id != 1 ) OR (cbr.master_id = 1 AND cbr.customer_id != 1) ORDER BY  createTime DESC  
24. LIMIT 2,10