月份：2017年10月

MySQL笔记之函数查询的使用

参考表

未分类

count()用来统计记录的条数

mysql> select count(*) from score;
mysql> select count(*) from score where stu_id=901;

sum()求和函数

mysql> select sum(grade) from score;
mysql> select id,sum(grade) from score where stu_id=901;

avg()求平均值函数

mysql> select avg(grade) from score where c_name='计算机';
mysql> select c_name,avg(grade) from score group by c_name;

max()求最大值函数

mysql> select c_name,max(grade) from score where c_name='英语';
mysql> select c_name,max(grade) from score group by c_name;

min()求最小值函数

mysql> select c_name,min(grade) from score where c_name='中文';
mysql> select c_name,min(grade) from score group by c_name;

Concat拼接函数

mysql> select Concat(c_name, '(', stu_id, ')')
    -> from score order by stu_id;

mysql 将列值转变为列的方法

-- 创建库
CREATE TABLE `rate` (
 `uname` VARCHAR (300),
 `object` VARCHAR (300),
 `score` VARCHAR (300)
); 

-- 插入数据
INSERT INTO test.rate (uname, object, score)
 VALUES('aaa', 'chinese', '67'),
 ('aaa', 'math', '89'),
 ('aaa', 'physical', '89'),
 ('bbb', 'chinese', '67'),
 ('bbb', 'math', '75'),
 ('bbb', 'physical', '89');

-- 查询
SELECT DISTINCT uname AS '姓名',
SUM(CASE object
WHEN 'chinese' THEN score END)  AS '语文',
SUM(CASE object
WHEN 'math' THEN score END)  AS '数学',
SUM(CASE object
WHEN 'physical' THEN score END)  AS '物理' FROM rate GROUP BY uname;

如何通过SQL找出2个表里值不同的列的方法

以下有两个表，他们的结构完全相同，请通过SQL找出值不同的列。

Student_1

未分类

Student_2

未分类

方法一 — NOT EXISTS：

SELECT *
FROM Student_1 S1
WHERE NOT EXISTS
  (SELECT *
  FROM Student_2 S2
  WHERE S1.name = S2.name
  AND S1.age    = S2.age
  AND S1.score  = S2.score
  )
UNION ALL
SELECT *
FROM STUDENT_2 S2
WHERE NOT EXISTS
  (SELECT *
  FROM STUDENT_1 S1
  WHERE S1.name = S2.name
  AND S1.age    = S2.age
  AND S1.score  = S2.score
  );

方法二 — MINUS

(SELECT * FROM Student_1
MINUS
SELECT * FROM Student_2)
UNION ALL
(SELECT * FROM Student_2
MINUS
SELECT * FROM Student_1)

方法三 — HAVING GROUP BY

SELECT DISTINCT name, age, score FROM (
SELECT * FROM Student_1
UNION ALL
SELECT * FROM Student_2
)GROUP BY name, age, score HAVING COUNT(*)=1 ;

MySQL前缀索引导致的慢查询分析总结MySQL前缀索引导致的慢查询分析总结

前端时间跟一个DB相关的项目，alanc反馈有一个查询，使用索引比不使用索引慢很多倍，有点毁三观。所以跟进了一下，用explain，看了看2个查询不同的结果。

不用索引的查询的时候结果如下，实际查询中速度比较块。

mysql> explain select * from rosterusers limit 10000,3 ; 

+----+-------------+-------------+------+---------------+------+---------+------+---------+-------+ 

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 

+----+-------------+-------------+------+---------------+------+---------+------+---------+-------+ 

| 1 | SIMPLE | rosterusers | ALL | NULL | NULL | NULL | NULL | 2010066 | | 

+----+-------------+-------------+------+---------------+------+---------+------+---------+-------+

而使用索引order by的查询结果如下，速度反而慢的惊人。

mysql> explain select * from rosterusers order by username limit 10000,3 ; 

+----+-------------+-------------+------+---------------+------+---------+------+---------+----------------+ 

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 

+----+-------------+-------------+------+---------------+------+---------+------+---------+----------------+ 

| 1 | SIMPLE | rosterusers | ALL | NULL | NULL | NULL | NULL | 2010087 | Using filesort | 

+----+-------------+-------------+------+---------------+------+---------+------+---------+----------------+

区别在于，使用索引查询的Extra变成了，Using filesort。居然用了使用外部文件进行排序。这个当然慢了。

但数据表上在username，的确是有索引的。怎么会反而要Using filesort?

看了一下数据表定义。是一个开源聊天服务器ejabberd的一张表。初看以为主键i_rosteru_user_jid是username，和jid的联合索引，那么使用order by username时应该是可以使用到索引才对呀？

CREATE TABLE `rosterusers` ( 
`username` varchar(250) NOT NULL, 
`jid` varchar(250) NOT NULL, 
UNIQUE KEY `i_rosteru_user_jid` (`username`(75),`jid`(75)), 
KEY `i_rosteru_jid` (`jid`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

仔细检查突然发现其主键定义，不是定义的完整的主键名称，而跟了一个75的长度描述，稍稍一愣，原来用的是前缀索引，而不是整个字段都是索引。（我的记忆里面InnoDB还不支持这玩意，估计是4.0后什么版本加入的），前缀索引就是将数据字段中前面N个字节作为索引的一种方式。

发现了这个问题后，我们开始怀疑慢查询和这个索引有关，前缀索引的主要用途在于有时字段过程，而MySQL支持的很多索引长度是有限制的。

首先不带order by 的limit 这种查询，本质可能还是和主键相关的，因为MySQL 的INNODB的操作实际都是依靠主键的（即使你没有建立，系统也会有一个默认的），而limit这种查询，使用主键是可以加快速度，（explain返回的rows 应该是一个参考值），虽然我没有看见什么文档明确的说明过这个问题，但从不带order by 的limit 查询的返回结果基本可以证明这点。

但当我们使用order by username的时候，由于希望使用的是username的排序，而不是username（75）的排序，但实际索引是前缀索引，不是完整字段的索引。所以反而导致了order by的时候完全无法利用索引了。（我在SQL语句里面增加强制使用索引i_rosteru_user_jid也不起作用）。而其实使用中，表中的字段username 连75个都用不到，何况定义的250的长度。完全是自己折腾导致的麻烦。由于这是其他产品的表格，我们无法更改，暂时只能先将就用不不带排序的查询讲究。

总结：

  • 前缀索引，并不是一个万能药，他的确可以帮助我们对一个写过长的字段上建立索引。但也会导致排序（order by ，group by）查询上都是无法使用前缀索引的。

  • 任何时候，对于DB Schema定义，合理的规划自己的字段长度，字段类型都是首要的事情。

详解MYSQL的备份还原(PHP实现)

手把手教你实现MYSQL的备份还原

示例代码用我比较熟悉的PHP，当然你看完并理解了其中的思路，相信你也可以快速地用你熟悉的语言自己写出来。

一、新建dbBackup类，设置默认参数。

class dbBackup {
    public $host='localhost';    //数据库地址
    public $user='root';    //登录名
    public $pwd='';    //密码
    public $database;    //数据库名
    public $charset='utf8';    //数据库连接编码：mysql_set_charset
}

二、添加数据库连接function。

/**
     * 连接数据库 ...
     */
    function db() {        
        $con = mysql_connect($this->host,$this->user,$this->pwd);
        if (!$con){
            die('Could not connect');
        }

        $db_selected = mysql_select_db($this->database, $con);
        if (!$db_selected) {
            die('Can't use select db');
        }

        mysql_set_charset($this->charset);　　//设置编码

        return $con;
    }

三、查询数据库表集合

/**
     * 表集合 ...
     */
    function tblist() {
        $list=array();

        $rs=mysql_query("SHOW TABLES FROM $this->database");
        while ($temp=mysql_fetch_row($rs)) {
            $list[]=$temp[0];
        }

        return $list;
    }

四、查询表结构

/**
     * 表结构SQL ...
     */
    function sqlcreate() {
        $sql='';

        $tb=$this->tblist();        
        foreach ($tb as $v) {
            $rs=mysql_query("SHOW CREATE TABLE $v");
            $temp=mysql_fetch_row($rs);
            $sql.="-- 表的结构：{$temp[0]} --rn";
            $sql.="{$temp[1]}";
            $sql.=";-- <xjx> --rnrn";
        }
        return $sql;
    }

注：$sql.=”;– –rnrn”;每句SQL后面必须加上分号(;)分割，MYSQL导入才能识别。– — 是程序对SQL语句分割的标识，可以自定义但必须是注释语句，否则影响SQL语句。rn无实际意义用于文本美观

五、INSERT INTO语句

/**
     * 数据插入SQL ...
     */
    function sqlinsert() {
        $sql='';

        $tb=$this->tblist();        
        foreach ($tb as $v) {
            $rs=mysql_query("SELECT * FROM $v");
            if (!mysql_num_rows($rs)) {//无数据返回
                continue;
            }        
            $sql.="-- 表的数据：$v --rn";
            $sql.="INSERT INTO `$v` VALUESrn";        
            while ($temp=mysql_fetch_row($rs)) {
                $sql.='(';
                foreach ($temp as $v2) {
                    if ($v2===null) {
                        $sql.="NULL,";
                    }
                    else {
                        $v2=mysql_real_escape_string($v2);
                        $sql.="'$v2',";
                    }                    
                }
                $sql=mb_substr($sql, 0, -1);
                $sql.="),rn";
            }
            $sql=mb_substr($sql, 0, -3);
            $sql.=";-- <xjx> --rnrn";    
        }

        return $sql;
    }

注：

  1. 无数据返回时必须跳出本次循环，避免生成多余代码

  2. 当字段值为(NULL)时，插入字符为(NULL)而不是(‘NULL’)，没有单引号。3.$v2=mysql_real_escape_string($v2)，这是必要的转义

  3. mb_substr($sql, 0, -1)、mb_substr($sql, 0, -3)，必须去除最后一个逗号(,) 否则SQL语句出错5.$sql.=”;– –rnrn”，详见第四步注

六、备份操作

/**
     * 备份 ...
     * @param $filename 文件路径
     */
    function beifen($filename) {
        $this->db();    //连接数据库

        $sql=$this->sqlcreate();
        $sql2=$this->sqlinsert();        
        $data=$sql.$sql2;

        return file_put_contents($filename, $data);
    }

七、还原操作

/**
     * 还原 ...
     * @param $filename 文件路径
     */
    function huanyuan($filename) {
        $this->db();    //连接数据库

        //删除数据表
        $list=$this->tblist();
        $tb='';
        foreach ($list as $v) {
            $tb.="`$v`,";
        }
        $tb=mb_substr($tb, 0, -1);
        if ($tb) {
            $rs=mysql_query("DROP TABLE $tb");
            if ($rs===false) {
                return false;
            }
        }

        //执行SQL
        $str=file_get_contents($filename);
        $arr=explode('-- <xjx> --', $str);
        array_pop($arr);

        foreach ($arr as $v) {
            $rs=mysql_query($v);
            if ($rs===false) {
                return false;
            }
        }

        return true;
    }

备份示例：

$x=new dbBackup();
$x->database='test';
$rs=$x->beifen('db.sql');
var_dump($rs);

还原示例：

$x=new dbBackup();
$x->database='test';
$rs=$x->huanyuan('db.sql');
var_dump($rs);

完整代码：

class dbBackup {
    public $host='localhost';    //数据库地址
    public $user='root';    //登录名
    public $pwd='';    //密码
    public $database;    //数据库名
    public $charset='utf8';    //数据库连接编码：mysql_set_charset

    /**
     * 备份 ...
     * @param $filename 文件路径
     */
    function beifen($filename) {
        $this->db();    //连接数据库

        $sql=$this->sqlcreate();
        $sql2=$this->sqlinsert();        
        $data=$sql.$sql2;

        return file_put_contents($filename, $data);
    }

    /**
     * 还原 ...
     * @param $filename 文件路径
     */
    function huanyuan($filename) {
        $this->db();    //连接数据库

        //删除数据表
        $list=$this->tblist();
        $tb='';
        foreach ($list as $v) {
            $tb.="`$v`,";
        }
        $tb=mb_substr($tb, 0, -1);
        if ($tb) {
            $rs=mysql_query("DROP TABLE $tb");
            if ($rs===false) {
                return false;
            }
        }

        //执行SQL
        $str=file_get_contents($filename);
        $arr=explode('-- <xjx> --', $str);
        array_pop($arr);

        foreach ($arr as $v) {
            $rs=mysql_query($v);
            if ($rs===false) {
                return false;
            }
        }

        return true;
    }

    /**
     * 连接数据库 ...
     */
    function db() {        
        $con = mysql_connect($this->host,$this->user,$this->pwd);
        if (!$con){
            die('Could not connect');
        }

        $db_selected = mysql_select_db($this->database, $con);
        if (!$db_selected) {
            die('Can't use select db');
        }

        mysql_set_charset($this->charset);    //设置编码

        return $con;
    }

    /**
     * 表集合 ...
     */
    function tblist() {
        $list=array();

        $rs=mysql_query("SHOW TABLES FROM $this->database");
        while ($temp=mysql_fetch_row($rs)) {
            $list[]=$temp[0];
        }

        return $list;
    }

    /**
     * 表结构SQL ...
     */
    function sqlcreate() {
        $sql='';

        $tb=$this->tblist();        
        foreach ($tb as $v) {
            $rs=mysql_query("SHOW CREATE TABLE $v");
            $temp=mysql_fetch_row($rs);
            $sql.="-- 表的结构：{$temp[0]} --rn";
            $sql.="{$temp[1]}";
            $sql.=";-- <xjx> --rnrn";
        }
        return $sql;
    }

    /**
     * 数据插入SQL ...
     */
    function sqlinsert() {
        $sql='';

        $tb=$this->tblist();        
        foreach ($tb as $v) {
            $rs=mysql_query("SELECT * FROM $v");
            if (!mysql_num_rows($rs)) {//无数据返回
                continue;
            }        
            $sql.="-- 表的数据：$v --rn";
            $sql.="INSERT INTO `$v` VALUESrn";        
            while ($temp=mysql_fetch_row($rs)) {
                $sql.='(';
                foreach ($temp as $v2) {
                    if ($v2===null) {
                        $sql.="NULL,";
                    }
                    else {
                        $v2=mysql_real_escape_string($v2);
                        $sql.="'$v2',";
                    }                    
                }
                $sql=mb_substr($sql, 0, -1);
                $sql.="),rn";
            }
            $sql=mb_substr($sql, 0, -3);
            $sql.=";-- <xjx> --rnrn";    
        }

        return $sql;
    }
}
//备份
//$x=new dbBackup();
//$x->database='test';
//$rs=$x->beifen('db.sql');
//var_dump($rs);
//还原
//$x=new dbBackup();
//$x->database='test';
//$rs=$x->huanyuan('db.sql');
//var_dump($rs);

怎么重置mysql的自增列AUTO_INCREMENT初时值

重置 MySQL 自增列 AUTO_INCREMENT 初时值

注意, 使用以下任意方法都会将现有数据删除.

方法一:

delete from tb1; 

ALTER TABLE tbl AUTO_INCREMENT = 100;

（好处, 可以设置 AUTO_INCREMENT 为任意值开始）

提示:如果表列和数据很多, 速度会很慢, 如90多万条, 会在10分钟以上.

方法二:

truncate tb1;

（好处, 简单, AUTO_INCREMENT 值重新开始计数.）

怎么重置mysql的自增列

1、支持设置自增列的值

ALTER TABLE table_name AUTO_INCREMENT = 1;

不过这种方式自能设置大于当前使用的值，不能设置小于等于当前已经使用的自增列的值。myisam如果设置小于等于，则自增列的值会自动设置为

当前最大值加1。innodb则不会改变。

2、通过TRUNCATE把自增列设置为0，从MySQL 5.0.13开始TRUNCATE就能重置自增列为0.myisam和innode都是如此。

TRUNCATE TABLE table_name;

3、drop和create重建表方式重置自增列为0

DROP TABLE table_name; 

CREATE TABLE table_name { ... };

查询优化之EXPLAIN的使用分析

不同的SQL语句写法，往往会带来很大的性能差异，我们怎么才能知道执行SQL查询开销呢？MySQL为我们提供了EXPLAIN关键词，在你的select语句前加上EXPLAIN关键词，MySQL将解释它是如何处理的SELECT查询，提供有关表如何联接和联接的次序，所扫描的记录数等相关信息，你可以凭借这些信息，来优化你的SQL查询。

EXPLAIN select id, username from userinfo where username like '%peng%';

我们在查询语句前加上了EXPLAIN关键词，那么我们可以得到如下的报告。

id: 1
select_type: SIMPLE
table: userinfo
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
rel: NULL
rows: 6
Extra: Using where

大概解释下每一个数据项的含义：

  • id:SELECT识别符，SELECT的查询序列号；

  • select_type:SELECT类型，可以是SIMPLE（简单查询）、PRIMARY（最外面的select）等；

  • table:用到的表

  • type:联接类型

  • possible_keys:可用索引列

  • key:实际用到的索引列

  • key_len:键长度

  • rel:使用哪个列或常数与key一起从表中选择行

  • rows:检查的行数

  • Extra:该列包含MySQL解决查询的详细信息

更具体的解释请查阅MySQL手册，在这里，把手册的内容再抄一遍并没有什么意义，当你有了这些参考数据后，就能更准确的完成MySQL查询优化了。

基于Php mysql存储过程的详解

实例一：无参的存储过程

$conn = mysql_connect('localhost','root','root') or die ("数据连接错误!!!");
mysql_select_db('test',$conn);
$sql = "
create procedure myproce()
begin
INSERT INTO user (id, username, sex) VALUES (NULL, 's', '0');
end;
";
mysql_query($sql);//创建一个myproce的存储过程

$sql = "call test.myproce();";
mysql_query($sql);//调用myproce的存储过程，则数据库中将增加一条新记录。

实例二：传入参数的存储过程

$sql = "
create procedure myproce2(in score int)
begin
if score >= 60 then
select 'pass';
else
select 'no';
end if;
end;
";
mysql_query($sql);//创建一个myproce2的存储过程
$sql = "call test.myproce2(70);";
mysql_query($sql);//调用myproce2的存储过程,看不到效果，可以在cmd下看到结果。

实例三：传出参数的存储过程

$sql = "
create procedure myproce3(out score int)
begin
set score=100;
end;
";
mysql_query($sql);//创建一个myproce3的存储过程
$sql = "call test.myproce3(@score);";
mysql_query($sql);//调用myproce3的存储过程
$result = mysql_query('select @score;');
$array = mysql_fetch_array($result);
echo '<pre>';print_r($array);

实例四：传出参数的inout存储过程

$sql = "
create procedure myproce4(inout sexflag int)
begin
SELECT * FROM user WHERE sex = sexflag;
end;
";
mysql_query($sql);//创建一个myproce4的存储过程
$sql = "set @sexflag = 1";
mysql_query($sql);//设置性别参数为1
$sql = "call test.myproce4(@sexflag);";
mysql_query($sql);//调用myproce4的存储过程,在cmd下面看效果

实例五：使用变量的存储过程

$sql = "
create procedure myproce5(in a int,in b int)
begin
declare s int default 0;
set s=a+b;
select s;
end;
";
mysql_query($sql);//创建一个myproce5的存储过程
$sql = "call test.myproce5(4,6);";
mysql_query($sql);//调用myproce5的存储过程,在cmd下面看效果

实例六：case语法

$sql = "
create procedure myproce6(in score int)
begin
case score
when 60 then select '及格';
when 80 then select '及良好';
when 100 then select '优秀';
else select '未知分数';
end case;
end;
";
mysql_query($sql);//创建一个myproce6的存储过程
$sql = "call test.myproce6(100);";
mysql_query($sql);//调用myproce6的存储过程,在cmd下面看效果

实例七：循环语句

$sql = "
create procedure myproce7()
begin
declare i int default 0;
declare j int default 0;
while i<10 do
set j=j+i;
set i=i+1;
end while;
select j;
end;
";
mysql_query($sql);//创建一个myproce7的存储过程
$sql = "call test.myproce7();";
mysql_query($sql);//调用myproce7的存储过程,在cmd下面看效果

实例八：repeat语句

$sql = "
create procedure myproce8()
begin
declare i int default 0;
declare j int default 0;
repeat
set j=j+i;
set i=i+1;
until j>=10
end repeat;
select j;
end;
";
mysql_query($sql);//创建一个myproce8的存储过程
$sql = "call test.myproce8();";
mysql_query($sql);//调用myproce8的存储过程,在cmd下面看效果

实例九：loop语句

$sql = "
create procedure myproce9()
begin
declare i int default 0;
declare s int default 0;

loop_label:loop
set s=s+i;
set i=i+1;
if i>=5 then
leave loop_label;
end if;
end loop;
select s;
end;
";
mysql_query($sql);//创建一个myproce9的存储过程
$sql = "call test.myproce9();";
mysql_query($sql);//调用myproce9的存储过程,在cmd下面看效果

实例十：删除存储过程

mysql_query(“drop procedure if exists myproce”);//删除test的存储过程

实例十一：存储过程中的游标

总结：

  1. 存储过程可用于InnoDB或MyISAM类型的表

  2. show procedure status显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等<br>

  3. SHOW CREATE PROCEDURE myproce显示某一个存储过程的详细信息<br>

基于mysql时间处理函数的应用详解

DAYOFWEEK(date)

返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)。这些索引值对应于ODBC标准。

mysql> select DAYOFWEEK('1998-02-03');  

-> 3

WEEKDAY(date)

返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。

mysql> select WEEKDAY('1997-10-04 22:23:00');  

-> 5  

mysql> select WEEKDAY('1997-11-05');  

-> 2

DAYOFMONTH(date)

返回date的月份中日期，在1到31范围内。

mysql> select DAYOFMONTH('1998-02-03');  

-> 3

DAYOFYEAR(date)

返回date在一年中的日数, 在1到366范围内。

mysql> select DAYOFYEAR('1998-02-03');  

-> 34

MONTH(date)

返回date的月份，范围1到12。

mysql> select MONTH('1998-02-03');  

-> 2

DAYNAME(date)

返回date的星期名字。

mysql> select DAYNAME("1998-02-05");  

-> 'Thursday'

MONTHNAME(date)

返回date的月份名字。

mysql> select MONTHNAME("1998-02-05");  

-> 'February'

QUARTER(date)

返回date一年中的季度，范围1到4。

mysql> select QUARTER('98-04-01');  

-> 2

WEEK(date）

WEEK(date,first)

对于星期天是一周的第一天的地方，有一个单个参数，返回date的周数，范围在0到52。2个参数形式WEEK()允许

你指定星期是否开始于星期天或星期一。如果第二个参数是0，星期从星期天开始，如果第二个参数是1，

从星期一开始。

mysql> select WEEK('1998-02-20');  

-> 7  

mysql> select WEEK('1998-02-20',0);  

-> 7  

mysql> select WEEK('1998-02-20',1);  

-> 8

YEAR(date)

返回date的年份，范围在1000到9999。

mysql> select YEAR('98-02-03');  

-> 1998

HOUR(time)

返回time的小时，范围是0到23。

mysql> select HOUR('10:05:03');  

-> 10

MINUTE(time)

返回time的分钟，范围是0到59。

mysql> select MINUTE('98-02-03 10:05:03');  

-> 5

SECOND(time)

回来time的秒数，范围是0到59。

mysql> select SECOND('10:05:03');  

-> 3

PERIOD_ADD(P,N)

增加N个月到阶段P（以格式YYMM或YYYYMM)。以格式YYYYMM返回值。注意阶段参数P不是日期值。

mysql> select PERIOD_ADD(9801,2);  

-> 199803

PERIOD_DIFF(P1,P2)

返回在时期P1和P2之间月数，P1和P2应该以格式YYMM或YYYYMM。注意，时期参数P1和P2不是日期值。

mysql> select PERIOD_DIFF(9802,199703);  

-> 11  

DATE_ADD(date,INTERVAL expr type)

DATE_SUB(date,INTERVAL expr type)

ADDDATE(date,INTERVAL expr type)  

SUBDATE(date,INTERVAL expr type)

这些功能执行日期运算。对于MySQL 3.22，他们是新的。ADDDATE()和SUBDATE()是DATE_ADD()和DATE_SUB()的同义词。

在MySQL 3.23中，你可以使用+和-而不是DATE_ADD()和DATE_SUB()。（见例子）date是一个指定开始日期的

DATETIME或DATE值，expr是指定加到开始日期或从开始日期减去的间隔值一个表达式，expr是一个字符串；它可以以

一个“-”开始表示负间隔。type是一个关键词，指明表达式应该如何被解释。EXTRACT(type FROM date)函数从日期

中返回“type”间隔。下表显示了type和expr参数怎样被关联： type值 含义 期望的expr格式

  • SECOND 秒 SECONDS

  • MINUTE 分钟 MINUTES

  • HOUR 时间 HOURS

  • DAY 天 DAYS

  • MONTH 月 MONTHS

  • YEAR 年 YEARS

  • MINUTE_SECOND 分钟和秒 “MINUTES:SECONDS”

  • HOUR_MINUTE 小时和分钟 “HOURS:MINUTES”

  • DAY_HOUR 天和小时 “DAYS HOURS”

  • YEAR_MONTH 年和月 “YEARS-MONTHS”

  • HOUR_SECOND 小时, 分钟， “HOURS:MINUTES:SECONDS”

  • DAY_MINUTE 天, 小时, 分钟 “DAYS HOURS:MINUTES”

  • DAY_SECOND 天, 小时, 分钟, 秒 “DAYS HOURS:MINUTES:SECONDS”

MySQL在expr格式中允许任何标点分隔符。表示显示的是建议的分隔符。如果date参数是一个DATE值并且你的计算仅仅

包含YEAR、MONTH和DAY部分(即，没有时间部分)，结果是一个DATE值。否则结果是一个DATETIME值。

mysql> SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND;  

-> 1998-01-01 00:00:00  

mysql> SELECT INTERVAL 1 DAY + "1997-12-31";  

-> 1998-01-01  

mysql> SELECT "1998-01-01" - INTERVAL 1 SECOND;  

-> 1997-12-31 23:59:59  

mysql> SELECT DATE_ADD("1997-12-31 23:59:59",  

INTERVAL 1 SECOND);  

-> 1998-01-01 00:00:00  

mysql> SELECT DATE_ADD("1997-12-31 23:59:59",  

INTERVAL 1 DAY);  

-> 1998-01-01 23:59:59  

mysql> SELECT DATE_ADD("1997-12-31 23:59:59",  

INTERVAL "1:1" MINUTE_SECOND);  

-> 1998-01-01 00:01:00  

mysql> SELECT DATE_SUB("1998-01-01 00:00:00",  

INTERVAL "1 1:1:1" DAY_SECOND);  

-> 1997-12-30 22:58:59  

mysql> SELECT DATE_ADD("1998-01-01 00:00:00",  

INTERVAL "-1 10" DAY_HOUR);  

-> 1997-12-30 14:00:00  

mysql> SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY);  

-> 1997-12-02  

mysql> SELECT EXTRACT(YEAR FROM "1999-07-02");  

-> 1999  

mysql> SELECT EXTRACT(YEAR_MONTH FROM "1999-07-02 01:02:03");  

-> 199907  

mysql> SELECT EXTRACT(DAY_MINUTE FROM "1999-07-02 01:02:03");  

-> 20102

如果你指定太短的间隔值(不包括type关键词期望的间隔部分)，MySQL假设你省掉了间隔值的最左面部分。例如，

如果你指定一个type是DAY_SECOND，值expr被希望有天、小时、分钟和秒部分。如果你象”1:10″这样指定值，

MySQL假设日子和小时部分是丢失的并且值代表分钟和秒。换句话说，”1:10″ DAY_SECOND以它等价于”1:10″ MINUTE_SECOND

的方式解释，这对那MySQL解释TIME值表示经过的时间而非作为一天的时间的方式有二义性。如果你使用确实不正确的日期，

结果是NULL。如果你增加MONTH、YEAR_MONTH或YEAR并且结果日期大于新月份的最大值天数，日子在新月用最大的天调整。

mysql> select DATE_ADD('1998-01-30', Interval 1 month);  

-> 1998-02-28

注意，从前面的例子中词INTERVAL和type关键词不是区分大小写的。

TO_DAYS(date)

给出一个日期date，返回一个天数(从0年的天数)。

mysql> select TO_DAYS(950501);  

-> 728779  

mysql> select TO_DAYS('1997-10-07');  

-> 729669

TO_DAYS()不打算用于使用格列高里历(1582)出现前的值。

FROM_DAYS(N)

给出一个天数N，返回一个DATE值。

mysql> select FROM_DAYS(729669);  

-> '1997-10-07'

TO_DAYS()

不打算用于使用格列高里历(1582)出现前的值。

DATE_FORMAT(date,format)

根据format字符串格式化date值。下列修饰符可以被用在format字符串中： %M 月名字(January……December)

  • %W 星期名字(Sunday……Saturday)

  • %D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）

  • %Y 年, 数字, 4 位

  • %y 年, 数字, 2 位

  • %a 缩写的星期名字(Sun……Sat)

  • %d 月份中的天数, 数字(00……31)

  • %e 月份中的天数, 数字(0……31)

  • %m 月, 数字(01……12)

  • %c 月, 数字(1……12)

  • %b 缩写的月份名字(Jan……Dec)

  • %j 一年中的天数(001……366)

  • %H 小时(00……23)

  • %k 小时(0……23)

  • %h 小时(01……12)

  • %I 小时(01……12)

  • %l 小时(1……12)

  • %i 分钟, 数字(00……59)

  • %r 时间,12 小时(hh:mm:ss [AP]M)

  • %T 时间,24 小时(hh:mm:ss)

  • %S 秒(00……59)

  • %s 秒(00……59)

  • %p AM或PM

  • %w 一个星期中的天数(0=Sunday ……6=Saturday ）

  • %U 星期(0……52), 这里星期天是星期的第一天

  • %u 星期(0……52), 这里星期一是星期的第一天

  • %% 一个文字“%”。

所有的其他字符不做解释被复制到结果中。

mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');  

-> 'Saturday October 1997'  

mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');  

-> '22:23:00'  

mysql> select DATE_FORMAT('1997-10-04 22:23:00',  

'%D %y %a %d %m %b %j');  

-> '4th 97 Sat 04 10 Oct 277'  

mysql> select DATE_FORMAT('1997-10-04 22:23:00',  

'%H %k %I %r %T %S %w');  

-> '22 22 10 10:23:00 PM 22:23:00 00 6'

MySQL3.23中，在格式修饰符字符前需要%。在MySQL更早的版本中，%是可选的。

TIME_FORMAT(time,format)

这象上面的DATE_FORMAT()函数一样使用，但是format字符串只能包含处理小时、分钟和秒的那些格式修饰符。

其他修饰符产生一个NULL值或0。

CURDATE()

CURRENT_DATE

以’YYYY-MM-DD’或YYYYMMDD格式返回今天日期值，取决于函数是在一个字符串还是数字上下文被使用。

mysql> select CURDATE();  

-> '1997-12-15'  

mysql> select CURDATE() + 0;  

-> 19971215

CURTIME()

CURRENT_TIME

以’HH:MM:SS’或HHMMSS格式返回当前时间值，取决于函数是在一个字符串还是在数字的上下文被使用。

mysql> select CURTIME();  

-> '23:50:26'  

mysql> select CURTIME() + 0;  

-> 235026

NOW()

SYSDATE()

CURRENT_TIMESTAMP

以’YYYY-MM-DD HH:MM:SS’或YYYYMMDDHHMMSS格式返回当前的日期和时间，取决于函数是在一个字符串还是在数字的

上下文被使用。

mysql> select NOW();  

-> '1997-12-15 23:50:26'  

mysql> select NOW() + 0;  

-> 19971215235026

UNIX_TIMESTAMP(date)

如果没有参数调用，返回一个Unix时间戳记(从’1970-01-01 00:00:00’GMT开始的秒数)。如果UNIX_TIMESTAMP()用一

个date参数被调用，它返回从’1970-01-01 00:00:00′ GMT开始的秒数值。date可以是一个DATE字符串、一个DATETIME

字符串、一个TIMESTAMP或以YYMMDD或YYYYMMDD格式的本地时间的一个数字。

mysql> select UNIX_TIMESTAMP();  

-> 882226357  

mysql> select UNIX_TIMESTAMP('1997-10-04 22:23:00');  

-> 875996580

当UNIX_TIMESTAMP被用于一个TIMESTAMP列，函数将直接接受值，没有隐含的“string-to-unix-timestamp”变换。

FROM_UNIXTIME(unix_timestamp)

以’YYYY-MM-DD HH:MM:SS’或YYYYMMDDHHMMSS格式返回unix_timestamp参数所表示的值，取决于函数是在一个字符串

还是或数字上下文中被使用。

mysql> select FROM_UNIXTIME(875996580);  

-> '1997-10-04 22:23:00'  

mysql> select FROM_UNIXTIME(875996580) + 0;  

-> 19971004222300

FROM_UNIXTIME(unix_timestamp,format)

返回表示 Unix 时间标记的一个字符串，根据format字符串格式化。format可以包含与DATE_FORMAT()函数列出的条

目同样的修饰符。

mysql> select FROM_UNIXTIME(UNIX_TIMESTAMP(),  

'%Y %D %M %h:%i:%s %x');  

-> '1997 23rd December 03:43:30 x'

SEC_TO_TIME(seconds)

返回seconds参数，变换成小时、分钟和秒，值以’HH:MM:SS’或HHMMSS格式化，取决于函数是在一个字符串还是在数字

上下文中被使用。

mysql> select SEC_TO_TIME(2378);  

-> '00:39:38'  

mysql> select SEC_TO_TIME(2378) + 0;  

-> 3938

TIME_TO_SEC(time)

返回time参数，转换成秒。

mysql> select TIME_TO_SEC('22:23:00');  

-> 80580  

mysql> select TIME_TO_SEC('00:39:38');  

-> 2378

深入理解where 1=1的用处

where 1=1有什么用？在SQL语言中，写这么一句话就跟没写一样。

select * from table1 where 1=1与select * from table1完全没有区别，甚至还有其他许多写法，1<>2，’a’=’a’,’a'<>’b’，其目的就只有一个，where 的条件为永真，得到的结果就是未加约束条件的。

在SQL注入时会用到这个，例如select * from table1 where name=’lala’给强行加上select * from table1 where name=’lala’ or 1=1这就又变成了无约束的查询了。

最近发现的妙用在于，在不定数量查询条件情况下，1=1可以很方便的规范语句。例如一个查询可能有name,age,height,weight约束，也可能没有，那该如何处理呢？

String sql=select * from table1 where 1=1

为什么要写多余的1=1？马上就知道了。

if(!name.equals("")){
sql=sql+"name='"+name+"'";
}
if(!age.equals("")){
sql=sql+"age'"+age+"'";
}
if(!height.equals("")){
sql=sql+"height='"+height+"'";
}
if(!weight.equals("")){
sql=sql+"weight='"+weight+"'";
}

如果不写1=1呢，那么在每一个不为空的查询条件面前，都必须判断有没有where字句，否则要在第一个出现的地方加where

where 1=1的写法是为了检化程序中对条件的检测

打个比方有三个参数a, b, c

@sql=select * from tb'

这三个参数都可能为空

这时你要构造语句的话,一个个检测再写语句就麻烦

比如

if @a is not null

@sql=@sql + " where a=' + @a

if @b is not null

这里你怎么写?要不要加where 或直接用 and ?,你这里还要对@a是否为空进行检测

用上 where 1=1 之后,就不存在这样的问题, 条件是 and 就直接and ,是or就直接接 or

拷贝表

create   table_name   as   select   *   from   Source_table   where   1=1;

复制表结构

create   table_name   as   select   *   from   Source_table   where   1 <> 1;