MySQL 技巧

MySQL添加索引命令

1.PRIMARY  KEY（主键索引）
mysql>ALTER  TABLE  `table_name`  ADD  PRIMARY  KEY (  `column`  ) 
2.UNIQUE(唯一索引)
        mysql>ALTER  TABLE  `table_name`  ADD  UNIQUE (
`column` ) 
3.INDEX(普通索引)
mysql>ALTER  TABLE  `table_name`  ADD  INDEX index_name (  `column`  )
4.FULLTEXT(全文索引)
mysql>ALTER  TABLE  `table_name`  ADD  FULLTEXT ( `column` )
5.多列索引
mysql>ALTER  TABLE  `table_name`  ADD  INDEX index_name (  `column1`,  `column2`,  `column3`  )

MySQL复制表的常用的两种方法

MySQL服务的默认引擎是 InnoDB

mysql> show engines;
+------------+---------+--------------
| Engine     | Support | Comment
+------------+---------+--------------
| EXAMPLE    | YES     | Example stora
| CSV        | YES     | CSV storage e
| MyISAM     | YES     | Default engin
| BLACKHOLE | YES     | /dev/null sto
| MRG_MYISAM | YES     | Collection of
| InnoDB     | DEFAULT | Supports tran
| ARCHIVE    | YES     | Archive stora
| MEMORY     | YES     | Hash based, s
| FEDERATED | YES     | Federated MyS
+------------+---------+--------------

原表的结构：

mysql> show create table cs;
+-------+-----------------------------------------------------------------------------
| Table | Create Table
+-------+-----------------------------------------------------------------------------
| cs    | CREATE TABLE `cs` (
`cs_id` int(8) NOT NULL AUTO_INCREMENT COMMENT 'id of the student''s course.',
`stu_id` int(8) NOT NULL,
`cs_name` varchar(50) DEFAULT 'vkebb',
PRIMARY KEY (`cs_id`),
KEY `stu_id` (`stu_id`),
CONSTRAINT `cs_ibfk_1` FOREIGN KEY (`stu_id`) REFERENCES `stu` (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------

（1）说明

我通过下面这条SQL语句快速的建立一个备份表,注意这种做法表的存储引擎也会采用服务器默认的存储引擎而不是源表的存储引擎，此种复制方法把表的内容也一起复制过来了。

注：as 与（）可以忽略，但建议使用，因为很其他的SQL产要求使用它们。

mysql> create table cs_bak1 as
    -> (select *
    -> from cs)
    -> ;

mysql> show create table cs_bak1;
+---------+-------------------------------------------------------------------------
| Table   | Create Table
+---------+--------------------------------------------------------------------------
| cs_bak1 | CREATE TABLE `cs_bak1` (
`cs_id` int(8) NOT NULL DEFAULT '0' COMMENT 'id of the student''s course.',
`stu_id` int(8) NOT NULL,
`cs_name` varchar(50) DEFAULT 'vkebb'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 | //服务器默认的存储引擎等
+---------+--------------------------------------------------------------------------

（2）说明

使用和cs_bak表相同的结构来创建一个新表，列名、数据类型、空指和索引也将复制，但是表的内容不会被复制。外键和专用的权限也没有被复制。

mysql> create table cs_bak
    -> like cs;

mysql> show create table cs_bak;
+--------+---------------------------------------------------------------------------
| Table | Create Table
+--------+---------------------------------------------------------------------------
| cs_bak | CREATE TABLE `cs_bak` (
`cs_id` int(8) NOT NULL AUTO_INCREMENT COMMENT 'id of the student''s course.',
`stu_id` int(8) NOT NULL,
`cs_name` varchar(50) DEFAULT 'vkebb',
PRIMARY KEY (`cs_id`),
KEY `stu_id` (`stu_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+--------+---------------------------------------------------------------------------

http://hi.baidu.com/jcs0611/blog/item/47858a50f4bc01898c54308d.html

讨人喜欢的 MySQL replace into 用法（insert into 的增强版）

在向表中插入数据的时候，经常遇到这样的情况：1. 首先判断数据是否存在； 2. 如果不存在，则插入；3.如果存在，则更新。

在 SQL Server 中可以这样处理：

if not exists (select 1 from t where id = 1) insert into t(id, update_time) values(1, getdate()) else update t set update_time = getdate() where id = 1

那么 MySQL 中如何实现这样的逻辑呢？别着急！MySQL 中有更简单的方法： replace into

replace into t(id, update_time) values(1, now());

或

replace into t(id, update_time) select 1, now();

replace into 跟 insert 功能类似，不同点在于：replace into 首先尝试插入数据到表中， 1. 如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。 2. 否则，直接插入新数据。

要注意的是：插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。

MySQL replace into 有三种形式：

1. replace into tbl_name(col_name, ...) values(...) 2. replace into tbl_name(col_name, ...) select ... 3. replace into tbl_name set col_name=value, ...

前两种形式用的多些。其中 “into” 关键字可以省略，不过最好加上 “into”，这样意思更加直观。另外，对于那些没有给予值的列，MySQL 将自动为这些列赋上默认值。

根据指定日期取不超过指定日期的周一至周日

 

 

select 

date_add('2011-04-10', interval - ( 12 + dayofweek('2011-04-10')) day)  '不超过指定日期所在上周周一',

date_add('2011-04-10', interval - ( 6 + dayofweek('2011-04-10')) day) '不超过指定日期所在上周日', 

date_add('2011-04-10', interval - ( 5 + dayofweek('2011-04-10')) day) '不超过指定日期所在周一',

date_add('2011-04-10', interval - ( -1 + dayofweek('2011-04-10')) day) '不超过指定日期所在周日'

select date_add( '2012-04-09',interval - (dayofweek('2012-04-09' ) + 5 ) % 7  day )  '本周一';

 

 

根据指定日期取上一月第一天和最后一天

 

select

date_sub(date_sub('2011-04-10',interval extract(day from '2011-04-10')-1 day),interval 1 month) '上月第一天',

date_sub(date_sub('2011-04-10',interval extract(day from '2011-03-10') day),interval 0 month) '上月最后一天'

查看mysql版本的四种方法

1：在终端下：mysql -V。 以下是代码片段：

　　[shengting@login ~]$ mysql -V
　　mysql Ver 14.7 Distrib 4.1.10a, for redhat-linux-gnu (i686)

　　2：在mysql中：mysql> status;
　　以下是代码片段：
　　mysql> status;
　　--------------
　　mysql Ver 14.7 Distrib 4.1.10a, for redhat-linux-gnu (i686)
　　Connection id:          416
　　SSL:                    Not in use
　　Current pager:          stdout
　　Using outfile:          ''
　　Using delimiter:        ;
　　Server version:         3.23.56-log
　　Protocol version:       10
　　Connection:             Localhost via UNIX socket
　　Client characterset:    latin1
　　Server characterset:    latin1
　　UNIX socket:            /tmp/mysql_3311.sock
　　Uptime:                 62 days 21 hours 21 min 57 sec
　 　Threads: 1 Questions: 584402560 Slow queries: 424 Opens: 59664208 Flush tables: 1 Open tables: 64 Queries per second avg: 107.551

　　3：在help里面查找
　　以下是代码片段：
　　[shengting@login ~]$ mysql --help | grep Distrib
　　mysql Ver 14.7 Distrib 4.1.10a, for redhat-linux-gnu (i686)

　　4：使用mysql的函数
　　以下是代码片段：
　　mysql> select version();
　　+-------------+
　　| version()   |
　　+-------------+
　　| 3.23.56-log |
　　+-------------+
　　1 row in set (0.00 sec)