一些SQL命令（不断更新，我总记不住，哭）

List

• 建库建表
• 表的重命名(不区分大小写)
• 列的重命名
• 编码
• 修改结构
• 添加删除索引
• 大批量删除
• binlog相关
• select相关
• 数据库备份和恢复

建库建表

GBK: CREATE DATABASE `test_db1` DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

UTF-8: CREATE DATABASE `test_db2` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE `test_table` (

  `id` bigint(20) unsigned NOT NULL COMMENT '主键',

  `gmt_create` datetime NOT NULL COMMENT '创建时间',

  `content` varchar(20) DEFAULT NULL COMMENT '说明',

  PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='test create table'

表的重命名(不区分大小写)

rename table haha to contestInfo;  

列的重命名

mysql中：

alter table 数据表名 change 原列名 新列名 新列类型;

oracle中：

alter table 数据表名 rename 原列名 to 新列名; 

编码

修改数据库编码 ALTER DATABASE `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin

修改表编码 ALTER TABLE `category` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin

修改字段编码 ALTER TABLE `test` CHANGE `dd` `dd` VARCHAR( 45 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL

修改结构

    mysql>

    #表position增加列test

    alter table position add(test char(10));

    #表position修改列test

    alter table position modify test char(20) not null;

    #表position修改列test默认值

    alter table position alter test set default 'system';

    #表position去掉test默认值

    alter table position alter test drop default;

    #表position去掉列test

    alter table position drop column test;

    #表depart_pos删除主键

    alter table depart_pos drop primary key;

    #表depart_pos增加主键

    alter table depart_pos add primary key PK_depart_pos (department_id,position_id);

添加删除索引

show index from tblname;

ALTER TABLE table_name ADD INDEX index_name (column_list)

ALTER TABLE table_name ADD UNIQUE (column_list)

ALTER TABLE table_name ADD PRIMARY KEY (column_list)

CREATE INDEX index_name ON table_name (column_list)

CREATE UNIQUE INDEX index_name ON table_name (column_list)

DROP INDEX index_name ON talbe_name

ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE table_name DROP PRIMARY KEY

大批量删除

delete from contestinfo where `id`<3000;

select sleep(3);

delete from contestinfo where `id`<6000;

select sleep(3);

delete from contestinfo where `id`<9000;

...

binlog相关

备库上执行一定要考虑事务是否写binlog

set session SQL_LOG_BIN = 0; //关闭，不写binlog

set session SQL_LOG_BIN = 1; //开启，写binlog，default

mysql>show master status; 查看当前正在使用的二进制日志

mysql>show binlog events in'mysql-bin.000001'; 查看二进制日志记录的事件[from position]

mysql>flush logs; 二进制日志滚动

mysql>show binary logs; 查看所有二进制日志

mysql>purge binary logs to 'mysql-bin.000003'; 删除二进制日志

Master_Log_File & Read_Master_Log_Pos：下一个传输的主日志信息。

Relay_Master_Log_File & Exec_Master_Log_Pos：下一个执行的主日志信息。

Relay_Log_File & Relay_Log_Pos：下一个执行的中继日志信息。

中继日志出错：

mysql> CHANGE MASTER TO

       MASTER_LOG_FILE='<Relay_Master_Log_File>',

       MASTER_LOG_POS=<Exec_Master_Log_Pos>;

mysql> START SLAVE;

stop slave ;

reset slave [ALL] relay logs,file and post，ALL will clear ip,port,user,pass;

CHANGE MASTER TO

MASTER_HOST='xxx.xxx.xxx.xxx',

MASTER_PORT=xxxx, 切记int

MASTER_USER='xxxxx',

MASTER_PASSWORD='xxxxx',

MASTER_LOG_FILE='mysql-bin.xxxxxx',

MASTER_LOG_POS=xxxxx; 切记int

start slave;

select相关

查询时强制区分大小写

SELECT * FROM U WHERE binary name LIKE 'a%';  

行号

SELECT

    @row := @row + 1 as row,

    t.*

FROM some_table t, (SELECT @row := 0) r

截取

SELECT

    SUBSTRING_INDEX(phone,'-',2) AS PNumber,

    SUBSTRING_INDEX(phone,'-', -1) AS Ext,

    phone

FROM tb_user WHERE ucid=271338;

+--------------+------+-------------------+

| PNumber      |  Ext |       phone       |

+--------------+------+-------------------+

| 010-88888882 | 5612 | 010-88888882-5612 |

+--------------+------+-------------------+ 

拼接

UPDATE tb_user SET

phone=CONCAT( SUBSTRING_INDEX( phone,'-',2 ), '-', '1234' )

WHERE ucid = 271338;

+--------------+------+-------------------+

| PNumber      |  Ext |       phone       |

+--------------+------+-------------------+

| 010-88888882 | 1234 | 010-88888882-1234 |

+--------------+------+-------------------+ 

group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

select id,group_concat(name order by name desc Separator ';') from aa group by id;

+------+---------------------------------------+

| id| group_concat(name order by name desc) |

+------+---------------------------------------+

|1 | 20;20;10 |

|2 | 20|

|3 | 500;200|

+------+---------------------------------------+

3 rows in set (0.00 sec)

数据库备份和恢复

mysqldump -h127.0.0.1 -uroot -P$MYSQL_PORT --default-character-set=utf8 -q -e --single-transaction --master-data=2 --result_file=/u01/bak/dump_${DB_NAME}.sql -B $candidates

content:

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000050', MASTER_LOG_POS=349656998;

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test_db_1` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `test_db_1`;

-n, --no-create-db

-t, --no-create-info

-d, --no-data

-B, --databases

-e, --extended-insert Use multiple-row INSERT syntax that include several VALUES lists.

-q, --quick         Don't buffer query, dump directly to stdout.

--add-drop-database                 FALSE

--add-drop-table                    TRUE

--add-drop-trigger                  FALSE

--add-locks                         TRUE

--default-character-set=name        utf8

--tz-utc                            TRUE SET TIME_ZONE='+00:00' at top of dump

--result_file                       输出结果到

--single-transaction --master-data=2组合使用，保留位点

mysql -h localhost -P 6688 -u root -ptest  database< test_db.sql