SQL常用命令

一.表结构

1.1 登陆

mysql -u root -ppassword -h localhost -P 3306

1.2 查看创建完整结构

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';

1.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',
  `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;

注意：

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

1.3.1 数据类型

类型名称	占用空间	可用默认值	说明
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，但根据字符集一个字符可能占多个字节 （适用于定长或较短的串）
varchar			变长字符串 。适用于长度变化大且更新少（压缩存储，若经常变大，则可能需要分裂页的操作）
tinyblob/tinytext	0~255字符		小型长度
blob/text	0~65535字符		正常长度
mediumblob/mediumtext	0~16777215字符		中等长度
longblob/longtext	0-4294967295字符		极大长度
timestamp	4B	CURRENT_TIMESTAMP,1970~2038	保存1970-01-01 00:00:00以来的秒数，同unix时间戳。
datetime	8B	1001~9999	精度为秒，底层以整数存储，与时区无关。没有default，但可以在SQL语句中设为now()

1.4 重命名

RENAME TABLE my_table TO my_table_new,other_table TO other_table_new;

二.字段操作

2.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 '年龄' ;

2.2 删除字段

alert TABLE my_table DROP COLUMN my_field;

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;

2.3.1 ALTER

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

<pre name="code" class="sql">ALTER TABLE my_table ALTER COLUMN my_field SET DEFAULT 5;
ALTER TABLE my_table ALTER COLUMN my_filed DROP DEFAULT;

2.3.2 CHANGE

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

ALTER TABLE my_table CHANGE COLUMN my_field my_field_new VARCHAR(32) NOT NULL FIRST;
ALTER TABLE my_table CHANGE COLUMN my_field my_field_new VARCHAR(32) NOT NULL AFTER other_filed;

2.3.3 MODIFY

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

ALTER TABLE my_table MODIFY COLUMN my_field VARCHAR(32) NOT NULL AFTER other_field;

2.3.4 总结

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

2.4 调整字段位置

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

使用change，保持原属性。

三.数据操作

3.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())

3.2 删

DELETE FROM my_table WHERE id = 1;
DELETE FROM my_table WHERE create_time < date_sub(now(), INTERVAL 3 MONTH);

3.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);

3.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 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;  //随机取值

3.4.1 逻辑操作符

and、or (and优先级高于or)

3.4.2 关系操作符

=、!=、<>、<、<=、>、>=、BETWEEN、is、not、in

3.4.3 通配符

%(任意字符任意次)、_(任意字符单次)

四.索引

4.1 创建单个索引

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

4.2 创建联合索引

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

4.3 创建前缀索引

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

4.4 扩展索引

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

4.5 删除

ALTER TABLE t_keywords DROP INDEX idx_keyword;

五.外键

5.1 创建

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

5.2 删除

ALTER TABLE my_table DROP FOREIGN KEY fk_name;

六.用户

5.1 创建

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

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

[mysqld]
skip-grant-tables

5.2 授权

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

5.3 删除

Drop USER test;