mysql常用语句（转）

原谅转自：http://www.cnblogs.com/waterystone/p/5085825.html

一. 规范

• 编程时一般关键字大写，其他小写；
• 尽量少用join查询；
• 选择小而简单的数据类型，尽量避免NULL(使得索引复杂，可用DEFAULT）；
• 除非有非常特别的原因需要其他存储引擎，否则应该优先考虑InnoDB引擎；
• 尽量使用整型定义主键。使用InnoDB应该尽可能按主键顺序插入数据，并尽可能使用单调增加的聚簇键。

二. 库相关

1.1 MySQL

1.1.1 版本

1	SELECT  VERSION();

　　

2.1 用户

2.1.1 创建

1	CREATE  USER  'test' @ '%'  IDENTIFIED  BY  '123456' ;

如果新用户权限拒绝，编辑/etc/my.cnf：

[mysqld]
skip-grant-tables

 

2.1.2 授权

1	grant  all  privileges  on  *.*  to  test@ "%"  identified  by  '1234' ;

 

2.1.3 删除

1	Drop  USER  test;

　　

　　

2.1.4 登陆

1 2	mysql -u root -p -h localhost -P 3306 /home/q/mysql/bin/mysql  -uroot -h127.0.0.1 -P3306

　　

三. 表结构

3.1 查看创建完整结构

1 2 3 4 5 6 7

DESC  my_table; SHOW  CREATE  TABLE  my_table; SHOW COLUMNS  FROM  my_table  LIKE  "my_field" ;   USE information_schema; SELECT  column_name  FROM  columns  WHERE  table_name= 'my_table' ; SELECT  ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_COMMENT  FROM  information_schema. COLUMNS  WHERE  table_schema =  'my_db_name'  AND  table_name =  'my_table_name' ;

　　

3.2 重命名

1	RENAME  TABLE  my_table  TO  my_table_new,other_table  TO  other_table_new;

　　

3.3 创建

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

CREATE  TABLE  `my_table` (    `id`  int (11)  NOT  NULL  AUTO_INCREMENT,    `nid`  bigint (20)  NOT  NULL  references  t_keywords(id),    `ugc_name`  varchar (20)   NOT  NULL  COMMENT  '名称' ,    `ugc_type` enum( 'one' , 'two' , 'three' )  NOT  NULL  DEFAULT  'one' ,    `total_count`  int (11) unsigned  NOT  NULL  DEFAULT  '0' ,    `data` json,    `create_date`  date  NOT  NULL ,    `insert_time` datetime  NOT  NULL  DEFAULT  '1970-01-01 00:00:00' ,    `updateTime`  timestamp  NULL  DEFAULT  CURRENT_TIMESTAMP  ON  UPDATE  CURRENT_TIMESTAMP ,    `is_finished` tinyint(3)  NOT  NULL  DEFAULT  '0' ,    PRIMARY  KEY  (`id`),    KEY  `idx_keyword_id_is_finished` (`keyword_id`,`is_finished`) ) ENGINE=InnoDB  DEFAULT  CHARSET=utf8  COLLATE =utf8_bin   #复制表结构 CREATE  TABLE  my_table_new  LIKE  my_table;   #将 select 的结果存成表（保留原字段的属性设置，但不复制索引） CREATE  TABLE  my_table_new ENGINE=INNODB  AS  SELECT  *  FROM  my_table;

　　

　

3.4 数据类型

类型名称	占用空间	可用默认值	说明
tinyint	1B	-128~127	小整数型
smallint	2B	-32768~32767	大整数型
mediumint	3B	-8388608~8388607	大整数型
int/integer	4B	-2147483648~2147483647	大整数型
bitint	8B	-9233372036854775808~9223372036854775807	极大整数型
float	4B	-3.402823466E+38~1.175494351E-38	单精度浮点数型
double	8B	1.7976931348623157E+308~2.2250738585072014E-308	双精度浮点数型
decimal(m,d)			对小数需要精确计算时用此类型，但在mysql处理时会转为double，所以能不用此类型尽量不用。
char			定长字符串，一个字符长度为1，但根据字符集一个字符可能占多个字节 （适用于定长或较短的串） 注：在多字节字符编码中，innodb将char视为非定长，按varchar存储
varchar	0~65535B	Latin1(1B/字符)：0~65532字符 utf-8(1~3B/字符)：21845字符 gbk(2B/字符)：32767字符	变长字符串 。适用于长度变化大且更新少（压缩存储，若经常变大，则可能需要分裂页的操作）。存储时，按实际存入数据存储。并有额外字段标记其长度，以便定位。 注：所有varchar列总长度之和不能超过65536B
tinyblob/tinytext	0~255字符		小型长度
blob/text	0~65535字符		正常长度
mediumblob/mediumtext	0~16777215字符		中等长度
longblob/longtext	0-4294967295字符		极大长度
json	受限于max_allowed_packet		需要5.7+版本
timestamp	4B	CURRENT_TIMESTAMP,1970~2038	保存1970-01-01 00:00:00以来的秒数，同unix时间戳。
datetime	8B	1001~9999	精度为秒

四. 字段操作

4.1 增

1 2 3

ALTER  TABLE  my_table  ADD  COLUMN  status tinyint(4)  NOT  NULL  DEFAULT  '0' ; ALTER  TABLE  my_table  ADD  COLUMN  my_field  VARCHAR (30)  NOT  NULL  DEFAULT  ''  AFTER  id; ALTER  TABLE  my_table  ADD  COLUMN  age  int (11) COMMENT  '年龄'  ;

 

4.2 删

1	alert  TABLE  my_table  DROP  COLUMN  my_field;

 

4.3 改

1 2 3

ALTER  TABLE  my_table CHANGE my_field my_field_new  VARCHAR  (50); ALTER  TABLE  my_table  MODIFY  COLUMN  my_field  INT (11)  NOT  NULL  DEFAULT  '0' ; ALTER  TABLE  my_table  ALTER  COLUMN  my_field  SET  DEFAULT  5;

 

4.3.1 ALTER　　

ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
设置或删除列的默认值。该操作会直接修改.frm文件而不涉及表数据。所以，这个操作非常快。

1 2	alter  table  film  alter  column  rental_duration  set  default  5; alter  table  film  alter  column  rental_duration  drop  default ;

 

4.3.2 CHANGE

CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
列的重命名、列类型的变更以及列位置的移动

1 2	ALTER  TABLE  MyTable CHANGE  COLUMN  foo bar  VARCHAR (32)  NOT  NULL  FIRST ; ALTER  TABLE  MyTable CHANGE  COLUMN  foo bar  VARCHAR (32)  NOT  NULL  AFTER  baz;

 

 

4.3.3 MODIFY

MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
除了列的重命名之外，他干的活和CHANGE COLUMN是一样的

1	ALTER  TABLE  MyTable  MODIFY  COLUMN  foo  VARCHAR (32)  NOT  NULL  AFTER  baz;

　　

4.3.4 总结

• change可以更改字段名，而modify/alter不能；
• 所有的MODIFY COLUMN/CHANGE COLUMN操作都将导致表重建；而ALTER COLUMN操作会修改.frm文件而不涉及表数据。

4.4 调整

1	ALTER  TABLE  my_table CHANGE my_field my_field  VARCHAR (30)  NOT  NULL  DEFAULT  '' AFTER  id;

使用change，保持原属性。

五. 基本操作

5.1 增

1 2 3 4 5 6 7

INSERT  INTO  my_table (id, keyword)  VALUES  (1,  'keyword' ); INSERT  INTO  my_table (field1,field2)  SELECT  field1,field2  FROM  other_table;   //批量插入时有字节限制，max_allowed_packet，一般为1MB INSERT  LOW_PRIORITY  INTO  t_keywords (id, keyword)  VALUES  (1,  'a' ), (2,  'b' ) INSERT  IGNORE  INTO  my_table (a,b,c)  VALUES  (1,2,3),(4,5,6); REPLACE  INTO  my_table (keyword, insert_time)  VALUES  ( 'a1' , now())//删除冲突的旧记录，增加新记录

　

5.2 删

1 2 3 4 5

DELETE  FROM  my_table  WHERE  id = 1; DELETE  FROM  my_table  WHERE  create_time < date_sub(now(), INTERVAL 3  MONTH );   DELETE  FROM  t_keywords; TRUNCATE  t_keywords;//删除所有数据，上边的逐行删除，下边的更高效，而且自增键会重置。

　　

5.3 改

1 2 3 4 5

UPDATE  t_keywords  SET  keyword =  'test'  WHERE  id = 5 UPDATE  t_keywords  SET  keyword =  'test' , name = "du"  WHERE  id = 5   INSERT  INTO  my_table (a,b,c)  VALUES  (1,2,3)  ON  DUPLICATE  KEY  UPDATE  b=2,c=3; INSERT  INTO  my_table (a,b,c)  VALUES  (1,2,3),(4,5,6)  ON  DUPLICATE  KEY  UPDATE  c=c+ VALUES (c);

　　

5.4 查

1 2 3 4 5 6 7

SELECT  id, keyword  FROM  t_keywords  WHERE  id = 5  AND  count  > 10 LIMIT 3, 5 SELECT  id, keyword  FROM  t_keywords  WHERE  id  BETWEEN  1  AND  5 SELECT  DISTINCT  id  FROM  t_keywords SELECT  DISTINCT  id  FROM  t_keywords  ORDER  BY  id,  count  DESC SELECT  SQL_NO_CACHE/SQL_CACHE *  FROM  t_keywords  WHERE  id = 5;//明确是否缓存 SELECT  *  FROM  my_table  WHERE  id >=( SELECT  floor(RAND() * ( SELECT  MAX (id)  FROM  my_table)) )  ORDER  BY  id LIMIT 5;  //随机取值 SELECT  *  FROM  my_table  WHERE  id >=( SELECT  floor(RAND() * (( SELECT  MAX (id)  FROM  my_table)-( SELECT  MIN (id)  FROM  my_table)) + ( SELECT  MIN (id)  FROM  my_table)))  ORDER  BY  id LIMIT 5;  //随机取值

　　

5.4.1 where子句

1. 逻辑操作符：and、or   (and优先级高于or)
2. 关系操作符：=、!=、<>、<、<=、>、>=、BETWEEN、is、not、in
3. 通配符(与like搭配使用)：%(任意字符任意次)、_(任意字符单次)

六. 索引

6.1 查看

1	SHOW  INDEX  FROM  my_table;

　　

6.2 创建单个索引

1 2	ALTER  TABLE  t_keywords  ADD  INDEX  idx_keyword (keyword); CREATE  INDEX  idx_keyword  ON  t_keywords (keyword);

　　

6.3 创建联合索引

1	ALTER  TABLE  t_keywords  ADD  INDEX  idx_keyword_time (keyword, time );

 

6.4 创建前缀索引

1	ALTER  TABLE  t_keywords  ADD  INDEX  idx_keyword (keyword(5));

 

6.5 扩展索引

1	ALTER  TABLE  t_keywords  DROP  INDEX  idx_keyword  ADD  INDEX  idx_keyword_create_time (keyword, create_time);

　　

6.6 删除

1	ALTER  TABLE  t_keywords  DROP  INDEX  idx_keyword;

　　

七. 外键

7.1 创建

1	ALTER  TABLE  my_table  ADD  CONSTRAINT  fk_userId  FOREIGN  KEY  (my_user_id)  REFERENCES  other_table (other_user_id);

　　

7.2 删除

1	ALTER  TABLE  my_table  DROP  FOREIGN  KEY  fk_name;

　

八、存储引擎

8.1 修改

1	ALTER  TABLE  my_table ENGINE=INNODB;