常用脚本
时间类型default转换：
CREATE_DATE DATE default SYSDATE, 转换成
CREATE_DATE timestamp default now();
启动mysql
启动：net start mySql;
进入：mysql -u root -p/mysql -h localhost -u root -p databaseName; 列出数据库：show databases; 选择数据库：use databaseName; 列出表格：show tables；
显示表格列的属性：show columns from tableName； 建立数据库：source fileName.txt;
匹配字符：可以用通配符_代表任何一个字符，％代表任何字符串; 增加一个字段：alter table tabelName add column fieldName dateType;
增加多个字段：alter table tabelName add column fieldName1 dateType,add columns fieldName2 dateType;
多行命令输入:注意不能将单词断开;当插入或更改数据时，不能将字段的字符串展开到多行里，否则硬回车将被储存到数据中;
增加一个管理员帐户：grant all on *.* to user@localhost identified by \ 每条语句输入完毕后要在末尾填加分号';'，或者填加'\\g'也可以； 查询时间：select now(); 查询当前用户：select user();
查询数据库版本：select version();
查询当前使用的数据库：select database();
表分区处理：
--分区 对datetime，TIMESTAMP 处理
--对int类型 less than 即可。partition p0 values less than (10)； create table TW_PTL_ACCESSLOG (
LOGIN_ACCEPT VARCHAR(16) not null, USER_IP VARCHAR(128),
SESSIONID VARCHAR(128) not null,
USER_ID VARCHAR(500), LOGIN_TYPE INT(2),
LOGIN_NUM VARCHAR(20), URL VARCHAR(1000), SRC_URL VARCHAR(1000),
ACCESS_BEGIN_DATE TIMESTAMP not null, ACCESS_USED_TIME INT(6),
ACCESS_FLAG CHAR(1) default 'Y', CREATE_DATE timestamp default now(), CREATE_BY VARCHAR(20), NOTE VARCHAR(3000), DOMAIN VARCHAR(1000), DOMAIN_URL VARCHAR(1000), SUB_URL VARCHAR(1000), SYS_ID INT(3), TIME_RANGE INT(2)
) partition by range (UNIX_TIMESTAMP(ACCESS_BEGIN_DATE))( partition PART_ACCESSLOG_20140218 values (UNIX_TIMESTAMP('2014-02-18 00:00:00')), partition PART_ACCESSLOG_20140219 values (UNIX_TIMESTAMP('2014-02-19 00:00:00')), partition PART_ACCESSLOG_20140220 values (UNIX_TIMESTAMP('2014-02-20 00:00:00')), partition PART_ACCESSLOG_20140221 values (UNIX_TIMESTAMP('2014-02-21 00:00:00')), partition PART_ACCESSLOG_20140222 values (UNIX_TIMESTAMP('2014-02-22 00:00:00')), partition PART_ACCESSLOG_20140223 values (UNIX_TIMESTAMP('2014-02-23 00:00:00')), partition PART_ACCESSLOG_20140224 values (UNIX_TIMESTAMP('2014-02-24 00:00:00')), partition PART_ACCESSLOG_20140225 values (UNIX_TIMESTAMP('2014-02-25 00:00:00')), partition PART_ACCESSLOG_20140226 values (UNIX_TIMESTAMP('2014-02-26 00:00:00')), partition PART_ACCESSLOG_20140227 values (UNIX_TIMESTAMP('2014-02-27 00:00:00')), partition PART_ACCESSLOG_20140228 values (UNIX_TIMESTAMP('2014-02-28 00:00:00')) );
create table TW_PTL_INF_SERVINVOKE
less than less than less than less than less than less than less than less than less than less than less
than
(
INVOKE_ID VARCHAR(50) not null, INNER_REQUET_ID VARCHAR(20), SYSTEM_ID VARCHAR(10), OUTER_REQUET_ID VARCHAR(20), INVOKE_TYPE INT(2),
CONN_DATE DATETIME, USED_TIME INT(6), INVOKE_FLAG CHAR(2),
SERV_NAME VARCHAR(100), SERV_INPARA TEXT, SERV_OUTPARA TEXT,
RETCODE VARCHAR(50), RETMSG VARCHAR(4000), REMARK VARCHAR(1000), CLIENT_INPARA TEXT, CLIENT_OUTPARA TEXT
)partition by range COLUMNS(CONN_DATE)( partition PART_INF_INVOKELOG_20140218 values less than ('2014-02-18'), partition PART_INF_INVOKELOG_20140219 values less than ('2014-02-19'), partition PART_INF_INVOKELOG_20140220 values less than ('2014-02-20'), partition PART_INF_INVOKELOG_20140221 values less than ('2014-02-21'), partition PART_INF_INVOKELOG_20140222 values less than ('2014-02-22'), partition PART_INF_INVOKELOG_20140223 values less than ('2014-02-23'), partition PART_INF_INVOKELOG_20140224 values less than ('2014-02-24'), partition PART_INF_INVOKELOG_20140225 values less than ('2014-02-25'), partition PART_INF_INVOKELOG_20140226 values less than ('2014-02-26'), partition PART_INF_INVOKELOG_20140227 values less than ('2014-02-27'), partition PART_INF_INVOKELOG_20140228 values less than ('2014-02-28') );
修改主键：
alter table Statistic add Id int(11) primary key auto_increment;
mysql:表注释和字段注释
1 创建表的时候写注释 create table test1 (
field_name int comment '字段的注释' )comment='表的注释';
2 修改表的注释
alter table test1 comment '修改后的表的注释';
3 修改字段的注释
alter table test1 modify column field_name int comment '修改后的字段注释'; --注意：字段名和字段类型照写就行
4 查看表注释的方法
--在生成的SQL语句中看 show create table test1; --在元数据的表里面看 use information_schema;
select * from TABLES where TABLE_SCHEMA='my_db' and TABLE_NAME='test1'
5 查看字段注释的方法 --show
show full columns from test1; --在元数据的表里面看
select * from COLUMNS where TABLE_SCHEMA='my_db' and TABLE_NAME='test1'
建表时控制主键的起点【auto_increment=1001】：
create table emb_t_dictBusType (
emb_c_busTypeID int not null auto_increment, emb_c_busTypeEnName varchar(255) not null, emb_c_busTypeZhName varchar(255) not null, primary key(emb_c_busTypeID)
)engine=INNODB auto_increment=1001 default charset=gbk;
不控制主键的起点
create table emb_t_dictBusType (
emb_c_busTypeID int not null auto_increment, emb_c_busTypeEnName varchar(255) not null, emb_c_busTypeZhName varchar(255) not null, primary key(emb_c_busTypeID)
)engine=INNODB default charset=gbk;
显示表的结构：
DESCRIBE tablename;
创建临时表：(建立临时表zengchao)
create temporary table zengchao(name varchar(10));
从已经有的表中复制表的结构
create table table2 select * from table1 where 1<>1;
复制表
create table table2 select * from table1;
对表重新命名
alter table table1 rename as table2;
修改列的类型
alter table table1 modify id int unsigned;//修改列id的类型为int unsigned alter table table1 change id sid int unsigned;//修改列id的名字为sid，而且把属性修改为int unsigned
创建索引
alter table table1 add index ind_id (id); create index ind_id on table1 (id);
create unique index ind_id on table1 (id);//建立唯一性索引