MySQL常用语句

一. 规范

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

二. 库相关

1.1 MySQL

1.1.1 版本

SELECT VERSION();

2.1 用户

2.1.1 创建

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

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

[mysqld]
skip-grant-tables

2.1.2 授权

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

2.1.3 删除

Drop USER test;

2.1.4 登陆

mysql -hlocalhost -P3306 -uroot -p123456
mysql -hlocalhost -P3306 -uroot -p123456 -DmyDB
mysql -hlocalhost -P3306 -uroot -p123456 -DmyDB -e "select * from user" > /home/work/data/result.xls

三. 表结构

3.1 查看创建完整结构

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';
SELECT * FROM information_schema. COLUMNS WHERE table_schema = 'my_db_name' AND table_name = 'my_table_name' \G;

3.2 重命名

RENAME TABLE my_table TO my_table_new,other_table TO other_table_new;

3.3 创建

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',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最近更新时间',
  `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=utf8mb4 COMMENT='任务配置表';

#复制表结构
CREATE TABLE my_table_new LIKE my_table;

#将select的结果存成表（保留原字段的属性设置，但不复制索引）
CREATE TABLE my_table_new ENGINE=INNODB AS SELECT * FROM my_table;

user demo表

DROP TABLE IF EXISTS user;
CREATE TABLE `user` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '英文名字',
  `cn_name` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '中文名字',
  `age` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' COMMENT '年龄',
  `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最近更新时间',

  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_name` (`name`),
  KEY `idx_cn_name` (`cn_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='用户表';

INSERT INTO user (name, cn_name, age) VALUES ('zhangsan', '张三', 18), ('lisi', '李四', 20), ('wangwu', '王五', 19);

注：

MySQL 5.5只能有一个字段timestamp使用CURRENT_TIMESTAMP，而5.6就允许多字段都使用CURRENT_TIMESTAMP了（https://my.oschina.net/BearCatYN/blog/911672）。

3.3.1 字符集

3.3.2 排序规则 - COLLATION

一个字符集有一个或多种collation，并且以_ci（大小写不敏感）、_cs（大小写敏感）或_bin（二元）结束。　

show variables like '%character%';
show variables like 'collation%';

#查看MYSQL所支持的字符集
show charset;

#查看表的字符集
show table status from db1 like 'user';

查看表中所有列的字符集
show full columns from user;

3.4 数据类型

类型名称	占用空间	可用默认值	说明
tinyint	1B	-128~127	小整数型
smallint	2B	-32768~32767	大整数型
mediumint	3B	-8388608~8388607	大整数型
int/integer	4B	-2147483648~2147483647	大整数型
bigint	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 增

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 删

alert TABLE my_table DROP COLUMN my_field;

4.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文件而不涉及表数据。所以，这个操作非常快。

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]
列的重命名、列类型的变更以及列位置的移动

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是一样的

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 调整

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

使用change，保持原属性。

五. 基本操作

5.1 增

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 删

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 改

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 查

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 * FROM user WHERE id IN (5, 20, 1, 10) ORDER BY FIELD (id, 5, 20, 1, 10); //排序
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子句

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

六. 索引

6.1 查看

SHOW INDEX FROM my_table;

6.2 创建单个索引

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

6.3 创建联合索引

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

6.4 创建前缀索引

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

6.5 扩展索引

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

6.6 删除

ALTER TABLE t_keywords DROP INDEX idx_keyword;

七. 外键

7.1 创建

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

7.2 删除

ALTER TABLE my_table DROP FOREIGN KEY fk_name;

八、存储引擎

8.1 修改

ALTER TABLE my_table ENGINE=INNODB;

posted @ 2015-12-29 15:17 waterystone 阅读(450) 评论(0) 编辑收藏举报

刷新页面返回顶部

waterystone

you can NEVER be too careful~

MySQL常用语句

一. 规范

二. 库相关

1.1 MySQL

1.1.1 版本

2.1 用户

2.1.1 创建

2.1.2 授权

2.1.3 删除

2.1.4 登陆

三. 表结构

3.1 查看创建完整结构

3.2 重命名

3.3 创建

3.3.1 字符集

3.3.2 排序规则 - COLLATION

3.4 数据类型

四. 字段操作

4.1 增

4.2 删

4.3 改

4.3.1 ALTER

4.4 调整

五. 基本操作

5.1 增

5.2 删

5.3 改

5.4 查

5.4.1 where子句

六. 索引

6.1 查看

6.2 创建单个索引

6.3 创建联合索引

6.4 创建前缀索引

6.5 扩展索引

6.6 删除

七. 外键

7.1 创建

7.2 删除

八、存储引擎

8.1 修改

公告

4.3.1 ALTER　　