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
+------------+---------+--------------
| Engine
+------------+---------+--------------
| EXAMPLE
| CSV
| MyISAM
| BLACKHOLE | YES
| MRG_MYISAM | YES
| InnoDB
| ARCHIVE
| MEMORY
| FEDERATED | YES
+------------+---------+--------------
+-------+-----------------------------------------------------------------------------
| Table | 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 |
+-------+----------------------------------------------------------------------------
+---------+-------------------------------------------------------------------------
| 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 | //服务器默认的存储引擎等
+---------+--------------------------------------------------------------------------
+--------+---------------------------------------------------------------------------
| 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
讨人喜欢的 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) '上月最后一天'
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)