CREATE USER ‘test‘@‘%‘ IDENTIFIED BY ‘123456‘;
如果新用户权限拒绝，编辑/etc/my.cnf：
[mysqld]
skip-grant-tables
grant all privileges on test.* to test@"%" identified by ‘1234‘;
Drop USER test;
mysql -u root -p -h localhost -P 3306
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‘;
RENAME TABLE my_table TO my_table_new,other_table TO other_table_new;
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;
| 类型名称 | 占用空间 | 可用默认值 | 说明 |
| 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 | 精度为秒 |
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 ‘年龄‘ ;
alert TABLE my_table DROP COLUMN my_field;
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;
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 总结
ALTER TABLE my_table CHANGE my_field my_field VARCHAR(30) NOT NULL DEFAULT ‘‘AFTER id;
使用change，保持原属性。
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())
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;//删除所有数据，上边的逐行删除，下边的更高效。
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);
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; //随机取值
ALTER TABLE t_keywords ADD INDEX idx_keyword (keyword); CREATE INDEX idx_keyword ON t_keywords (keyword);
ALTER TABLE t_keywords ADD INDEX idx_keyword_time (keyword,time);
ALTER TABLE t_keywords ADD INDEX idx_keyword (keyword(5));
6.4 扩展索引
ALTER TABLE t_keywords DROP INDEX idx_keyword ADD INDEX idx_keyword_create_time (keyword, create_time);
ALTER TABLE t_keywords DROP INDEX idx_keyword;
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;
原文：http://www.cnblogs.com/waterystone/p/5085825.html