Heim >Datenbank >MySQL-Tutorial >mysql语句汇总_MySQL

mysql语句汇总_MySQL

WBOY
WBOYOriginal
2016-06-01 13:04:31977Durchsuche

前段时间和同学接的项目终于完工了,项目主要是做报表,涉及到了很多的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 视图名 AS
SELECT 语句;

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 a
UNION ALL
SELECT 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=1
AND 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)    CurYearPayedMoney
FROM (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.Id
WHERE t.Pid <> 0
GROUP BY t.Pid;

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

SELECT DISTINCT(xcs1.dept),xsc.subjectName,xcs2.amount,COALESCE(SUM(xfa.amount),0) payedMoney
,xcs2.amount-SUM(xfa.amount) leftAmount
FROM xmgl_control_subject xcs1
LEFT JOIN xmgl_control_subject xcs2 ON xcs1.dept = xcs2.dept AND xcs2.detailId = 3
LEFT JOIN xmgl_subject_control xsc ON xcs2.detailId = xsc.Id
LEFT JOIN xmgl_finance_activity xfa ON xcs2.dept = xfa.department AND xfa.detailId IN(&#39;22&#39;,&#39;23&#39;)
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 isRead
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
WHERE tf.master_id = 1
UNION 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 isRead
FROM cust_board cb LEFT JOIN customer c ON cb.customer_id = c.id WHERE cb.master_id = 1
UNION 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 isRead
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
WHERE tr.master_id = 1
UNION 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 isRead
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
WHERE 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 isRead
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
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
LIMIT 2,10


Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Vorheriger Artikel:mysql 5.5多实例部署_MySQLNächster Artikel:MySQL文件_MySQL