MYSQL下SQL的一些整理

应开发部门一个小MM的要求。整理了一份SQL的文档，内容很初级。但老手都知道，sql的命令不外乎那么几个，但却可以实现任何对数值的统计和排列。呵呵，专家往往都是钻研了一些看似很简单的东西，比如说ITPUB上newkid,还有几位版主。

如果有时间，再来整理一下MSYQL的引擎，备份恢复，参数说明文档吧

Mysql之sql总结

前言

本文档是对SQL语法的一些总结，如没做特别说明，此SQL仅适用于MYSQL较高版本数据库。总结包括以下几个部分。

SQL指令：SQL如何被用来存储，读取，以及处理存放数据库之中的资料

表格处理：SQL如何来处理数据库中的表格

SQL语法：提列出部分常用的SQL语法

SQL分类

首先了解一下SQL分类，根据SQL的作用不同，可以分为以下三类

Ø      数据库定义语言（DDL）

Create ,alter,drop，truncate

Ø      数据库操纵语言（DML）

Select ,insert ,update,delete

Ø      数据库控制语言（DCL）

Grant,revoke,commit,rollback

DDL

定义数据库，定义表结构，视图时使用，如

Create table ttt…..

Alter index …..

Drop view…….

Truncate table …..(注意，此操作本质是先drop掉，在重新建表的过程，会重置高水位线)

DML

网上有几种版本，有的把select归结为DQL语言，实际上，这是个很迷糊的概念。举个例子来解释吧，人本来只分男和女，可偏偏有了李宇春，曾轶可，姚小君这样不男不女的人…..呵呵

如果要从数据表中查询出需要的结果，可以用select xxx from  ttt where condition………子句。

插入数据：insert into tab_name (col1,col2…)  values (val1,val2…..)

修改数据：update table set col=val1….

删除数据：delete from table where ……

DCL

对用户授权，回收特权，以及对事务的操作，例如

Grantinsert,delete,update on db1.* to wokao@192.168.1.12identified by ‘aiyaya’;

 

 

一些常用的SQL语法

在讲解SQL语法之前，先创建一张temp表

Create table temp(

Id int,

Dep varchar(20)，

Name varchar(300)

)

select

select * from temp where condition

select version();----查询当前数据库版本

selectnow();------查询当前时间

select100/1000;-------select数值运算

select distinct

select distinct dep,name  from temp

distinct关键字对后面跟着的所有字段去重复

select where

select *  from  temp  where id>100

sql and or

select *  from temp  where ( id>1 and id<100 ) or (id>400and id <600)

--------查询出表tab_name中id在1到100和400到600之间的数据

in&exists(not in&not exists)

select * from tab1where id in (22,33,44)

select * from tab1 a where exists( select 1 from tab2 b wherea.id=b.id)

sql between

select * from temp where id between 1 and 100

sql like

select * from temp where dep like ‘%运维%’

select * from temp where dep like ‘运维%’

select * from temp where dep like ‘%运维’

sql order by & group by &having

select * from temporder by id desc (按照ID排序，默认为升序)

select * from tempgroup by dep -----按dep分组

selectid,dep,count(1)  from temp order by idgroup by dep having count(1)>1

having 分组过滤，在查找重复数据时很有用。

sql union& union all

select * from tempwhere id<100

union

select * from tempwhere id >1000

union 和 union all 的区别是unoin会去重复

sql case

select case dep

when ‘运维部’ then 1

when ‘市场部’ then 2

when ‘财务部’ then 3

else null

end dep_n

sql if &ifnull & nullif

Sql中IF实现的判断

SELECTIF(1>2,2,3);如果1>2为真，则为2，否则为3。这个语句返回的结果为3

SELECTIF(1<2,'yes ','no');同上

Sql中IFNULL实现的判断

SELECTIFNULL(1,0);如果1=0，则返回1，否则返回0.这个语句返回的结果为0

SELECTIFNULL(NULL,10);同上

SELECTIFNULL(1/0,'yes');同上

Sql中nullif实现的判断

SELECTNULLIF(1,2);如果1=2为真，则返回null，否则返回1，这个语句返回的结果为1

SELECTNULLIF(1,1);返回值为null

insert

inert into tempvalues(11,’运维部’,’myth’)

insert into tempselect 11,’运维部’,’myth’;

insert into tempvalues

(11,’运维部’,’Myth’),

(12,’市场部’,’Bob’)

(13,’财务部’,’Kate’)

update

Update temp setname=’What’ where id=11；

Update temp1,temp2set temp1.name=temp2.name

Wheretemp1.id=temp2.id；

delete

delete from tempwhere id=11；

delete from temp1,temp2where temp1.id=temp2.id

limit

select * from temporder by id limit 10

limit常作为分页的关键字

select into outfile

把数据导入到操作系统外部文件

Select * from tempinto outfile “/home/temp/temp.txt”

Load data infile

把外部文件中的数据导入到数据库，类似ORACLE的 sqlldr,MSSQL的DTS工具

Load data infile‘/home/temp/temp.txt’  into table temp(col1,col2,col3….)

……………..

一些常见的系统命令

show

show是mysql里一个非常有用的指令。它不可以归结为sql命令。它是mysqlDB系统命令。通过这个命令可以查看数据库当前的一些信息，如

showdatabase------------查看数据库列表

showtables---------查看当前数据下的表清单

show user;------查看当前连接用户

show variables;------查看系统变量

show processlist;-----查看当前链接信息

show status------查看系统当前信息

show grants forusername-----查看用户的权限

use

show databse

use db_name

analyse

当频繁的对大数据表进行操作后，可能需要对索引进行修复，此时可以使用analyse语句。

Analyse  table  table_name;

Repair

用于修复表，仅对myIsam和archive表引擎有作用

Repair table table_name

Check

数据库发生错误，索引没有同步更新，写入磁盘出错。可能需要对表进行检查

Check tabletable_name

Optimize

经常更新数据的磁盘需要整理碎片，数据库也是这样，Optimize Table语句对MyISAM和InnoDB类型的表都有效。

Optimize tabletable_name

SP,Funcation,Trigger语法

Store procedure

创建SP语法：

CREATE PROCEDURE sp_name([proc_parameter[,...]])

   [characteristic ...] routine_body

例如：

mysql>delimiter //

mysql> CREATEPROCEDURE simpleproc (OUT param1 INT)

   -> BEGIN

   ->   SELECT COUNT(*) INTO param1 FROM t;

   -> END

è //

mysql> CALLsimpleproc(@a);

Funcation

创建funcation语法

CREATE FUNCTIONsp_name ([func_parameter[,...]])

   RETURNS type

[characteristic...] routine_body

例如：

mysql>delimiter //

mysql> CREATEFUNCTION hello (s CHAR(20)) RETURNS CHAR(50)

   -> RETURN CONCAT('Hello, ',s,'!');

   -> //

Query OK, 0 rows

mysql> SELECThello('world');

Trigger

创建存储过程语法：

CREATE TRIGGERtrigger_name trigger_time trigger_event

ON tbl_name FOR EACH ROW trigger_stmt

例如：

DELIMITER

CREATE TRIGGERtestref BEFORE INSERT ON test1

  FOR EACHROW BEGIN

   INSERT INTO test2 SET a2 = NEW.a1;

   DELETE FROM test3 WHERE a3 = NEW.a1; 

    UPDATEtest4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;

  END

DELIMITER ;