mysql基础操作

/**建表*/DROP TABLE IF EXISTS usersTable;CREATE TABLE usersTable (  ID          BIGINT(20)     UNSIGNED   NOT NULL           AUTO_INCREMENT,  NAME        VARCHAR(255)   NOT NULL   DEFAULT 'myName',  COMMENT '名称',  AGE         INT(3)                    DEFAULT '0',  COMMENT '名称',  SEX         tinyint(1)                DEFAULT '1',  COMMENT '男(1)，女(0)',  BIRTHDAY    datetime,  PRIMARY KEY (ID)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;CREATE INDEX INDEX_NAME_BIRTHDAY  ON usersTable(NAME, BIRTHDAY);/**表字段修改*/ALTER TABLE usersTable ADD COLUMN ip varchar(15) DEFAULT '127.0.0.1' COMMENT '登录IP' AFTER ID;  ALTER TABLE usersTable CHANGE COLUMN ip reg_ip varchar(15) DEFAULT 'localhost';ALTER TABLE usersTable DROP COLUMN reg_ip;/**数据修改*/INSERT INTO usersTable(username, pass, email) VALUES('user1', '123', '[e-mail]');UPDATE usersTable set pass='123456', email='[e-mail]' WHERE username='user1';DELETE FROM usersTable WHERE username='user1';update usersTable set title =  replace(title,'oldWord','newWord') where title like '%oldWord%';/**清空数据*/TRUNCATE TABLE usersTable;/**查看表结构**/describe usersTable;/**查看数据库大小*/use information_schema;select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data  from TABLES where table_schema='usersTable';