mysql常用命令

1.设置root密码

./mysqladmin -uroot -password 123456

2. 添加用户并授权

insert into mysql.user (Host,User,Password) values('localhost','guest','123456');
grant select on *(数据库名).*(表名) to guest@127.0.0.1 identified by '123456';
flush privileges;

3.创建数据库和表

DROP DATABASE IF EXISTS data;
create database data;
use data;
DROP TABLE if exists basic_data;
create table basic_data(
    Date date NOT NULL,
        userID int unsigned NOT NULL,
        userName varchar(50) NOT NULL,
        1_industry varchar(30) NOT NULL COMMENT '一级行业',
        2_industry varchar(30) NOT NULL COMMENT '二级行业',
        allConsume decimal(8,2) NOT NULL,
        allClicks int unsigned NOT NULL,
    	primary key(userID),
        key(userName,Date,1_industry,2_industry)
)ENGINE=Innodb default CHARSET=utf8;

4. 表的修改

<span style="font-family:Hiragino Sans GB W3, Hiragino Sans GB, Arial, Helvetica, simsun, u5b8bu4f53;"><pre name="code" class="sql">show keys from aaa; //查看表aaa的key
show index from aaa; //查看表aaa的索引
alter table aaa rename to bbb;//修改表aaa的名字为bbb
alter table aaa drop primary key; //删除表aaa的主键
alter table aaa add primary key(date,ID);//给表aaa添加联合主键data和ID
alter table aaa add key(departmentID);//给表aaa的departmentID列添加键
alter table aaa add INDEX(Date);//给aaa的Date列添加索引
drop index 索引名 on 表名； //删除表索引
alter table aaa change Date Date date NOT NULL first;//将表aaa的Date列（其属性为date NOT NULL）调整为第一列
alter table aaa modify column dayRatio float(8,4); //将表aaa的dayRatio列修改属性为float(8,4)
alter table aaa add column users int unsigned NOT NULL; //给表aaa添加列，列名users，属性int unsigned NOT NULL
alter table aaa modify UserID int unsigned NOT NULL after Date; //将UserID 列移动到Date列后面，int unsigned NOT NULL是UserID的属性</span>
<span style="font-family: 'Hiragino Sans GB W3', 'Hiragino Sans GB', Arial, Helvetica, simsun, u5b8bu4f53;">alter table aaa drop paidUsers; //删除表aaa的paidUsers列</span>