【澳门金沙vip】mysql语句汇总

mysql语句汇总

前段时间和同学接的项目终于完工了,项目主要是做报表,涉及到了很多的sql语句,所以借此总结下。

SQL基础

SQL:结构化查询语言 RDBMS:关系型数据库管理系统
SQL可以分为两个部分:数据操作语言(DML)和数据定义语言(DDL)。
查询和更新指令构成了 SQL 的 DML 部分:
SELECT – 从数据库表中获取数据
UPDATE – 更新数据库表中的数据
DELETE – 从数据库表中删除数据
INSERT INTO – 向数据库表中插入数据

一、基础

SQL 简介

SQL是用于访问和处理数据库的标准的计算机语言

SQL 中最重要的 DDL 语句:
CREATE DATABASE – 创建新数据库
ALTER DATABASE – 修改数据库
CREATE TABLE – 创建新表
ALTER TABLE – 变更(改变)数据库表
DROP TABLE – 删除表
CREATE INDEX – 创建索引(搜索键)
DROP INDEX – 删除索引

1.数据库相关命令

a>.创建数据库

CREATE DATABASE 数据库名

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

CREATE DATABASE 数据库名 DEFAULT CHARACTER SET utf8;

c>.删除数据库

DROP DATABASE 数据库名

SQL是什么?

  • SQL,指结构化查询语言,全称是 Structured Query Language。
  • SQL 让您可以访问和处理数据库。
  • SQL 是一种 ANSI(American National Standards Institute
    美国国家标准化组织)标准的计算机语言。

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;

SQL能做什么?

  • SQL 面向数据库执行查询
  • SQL 可从数据库取回数据
  • SQL 可在数据库中插入新的记录
  • SQL 可更新数据库中的数据
  • SQL 可从数据库删除记录
  • SQL 可创建新数据库
  • SQL 可在数据库中创建新表
  • SQL 可在数据库中创建存储过程
  • SQL 可在数据库中创建视图
  • SQL 可以设置表、存储过程和视图的权限

SQL:
SELECT DISTINCT : 去除重复的数据。
ORDER BY
:根据指定的列队结果集进行排序,默认为升序(ASC)排列,可以设置为降序(DESC)。
Top子句:用于规定要返回的记录的数目。
语法:
SQL Server:
SELECT TOP number | percent column_name(s) FROM table_name;
MySQL:
SELECT column_name(s) FROM table_name LIMIT number;
Oracle:
SELECT column_name(s) FROM table_name WHERE ROWNUM <= number;

二、进阶

a>.去重,一般常用 DISTINCT

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

b>.复制表

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

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

EXPLAIN  SELECT * FROM 表名;

常用的MySQL管理命令:

  • USE Databasename 用于在MySQL工作区内选择具体某个数据库。
  • SHOW DATABASES 列出 MySQL DBMS 所能访问的数据库。
  • SHOW TABLES 一旦数据库被 use 命令选中,显示数据库中的表。
  • SHOW COLUMNS FROM tablename 显示表的属性、属性类型、键信息、是否允许
    NULL 值,默认值,以及其他一些信息。
  • SHOW INDEX FROM tablename 显示表中所有索引的细节信息,包括PRIMARY
    KEY。
  • SHOW TABLE STATUS LIKE tablename\G 报告MySQL
    DBMS的性能及统计的细节信息。

通配符
SQL中的通配符必须与LIKE运算符一起使用:
描述

三、贴一下做报表时写的一些恶心的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('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 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

前段时间和同学接的项目终于完工了,项目主要是做报表,涉及到了很多的sql语句,所以借此总结下。
一、基础 1.数据库相关…

SQL SELECT

SELECT语句用于从数据库中选取数据,结果被存储在一个结果表中,称为结果集

  • %:替代一个或多个字符
  • _ :仅替代一个字符
  • [charlist]:字符列中的任何单一字符
  • [^charlist] 或者[!charlist]:不在字符列中的任何单一字符

SQL SELECT IDSTINCT

返回唯一不同的值(去除重复的值).


SQL WHERE

用于过滤记录.

BETWEEN 操作符:
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2

WHERE字句中的运算符

运算符 描述
= 等于
<> 不等于。注释:在 SQL 的一些版本中,该操作符可被写成 !=
> 大于
< 小于
>= 大于等于
<= 小于等于
BETWEEN 在某个范围内
LIKE 搜索某种模式
IN 指定针对某个列的多个可能值
  • 重要事项:不同的数据库对 BETWEEN…AND
    操作符的处理方式是有差异的。某些数据库会列出介于 “Adams” 和 “Carter”
    之间的人,但不包括 “Adams” 和 “Carter” ;某些数据库会列出介于
    “Adams” 和 “Carter” 之间并包括 “Adams” 和 “Carter”
    的人;而另一些数据库会列出介于 “Adams” 和 “Carter” 之间的人,包括
    “Adams” ,但不包括 “Carter” 。所以,请检查你的数据库是如何处理
    BETWEEN….AND 操作符的!

特殊条件

  1. 空值判断: is null
  2. between and (在 … 之间的值)
  3. in
  4. like

SQL AND & OR

Join:
下面列出了您可以使用的 JOIN 类型,以及它们之间的差异。

ORDER BY 用于对结果集进行排序

默认按照升序对记录进行排序.

  • DESC 升序
  • ASC 降序
  • JOIN: 如果表中有至少一个匹配,则返回行(INNER JOIN)
  • LEFT JOIN: 即使右表中没有匹配,也从左表返回所有的行
  • RIGHT JOIN: 即使左表中没有匹配,也从右表返回所有的行
  • FULL JOIN: 只要其中一个表中存在匹配,就返回行

SQL INSERT INTO

用于向表中插入新记录。

INSERT INTO 语句可以有两种编写形式。
第一种形式无需指定要插入数据的列名,只需提供被插入的值即可:

INSERT INTO table_name
VALUES (value1,value2,value3,...);

第二种形式需要指定列名及被插入的值:

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

INNER JOIN 关键字:
在表中存在至少一个匹配时,INNER JOIN 关键字返回行。
语法
SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name

SQL UPDATE

用于更新表中已经存在的记录

  • 注释:INNER JOIN 与 JOIN 是相同的。

SQL DELETE

用于删除表中已经存在的记录