mysql常用命令

命令记忆：ctrl+r

登陆mysql
mysql -h localhost -u root -p

创建新用户
grant 权限 on 数据库.表 to '用户名'@'登录主机' identified by '密码';
权限：all,select,insert,update,delete等
数据库.表：可以用*代替所有
登录主机：可以用%代替所有
grant all on *.* to 'try'@'%' identified by '123456';

修改用户密码
set password for 用户名=password('密码');
把用户try的密码修改为222222
set password for try=password('222222');

删除用户
drop user 用户名;
删除try
drop user try;

删除数据库
drop database 库名;

删除表
drop table 表名;

显示表创建时间
use 库名;
show table status;

显示数据库
show databases;

选择数据库
use 数据库名;

显示表
show tables;

显示字段
desc 表名;
show columns from 表名;

修改字段类型
alter table 表名称 modify 字段名称 字段类型 [是否允许非空];
alter table lab_goods modify goods_weight varchar(60) null;
alter table lab_goods modify goods_weight varchar(60) not null;
添加字段
alter table lab_user_rank add XXX tinyint(1) unsigned not null default '0';

添加字段类型
alter table 表名 add 字段 类型 其他;
alter table lab_goods add goods_other int(4) default '0';

修改字段数值
修改表lab_goods中的goods_thumb、goods_img、original_img三个字段的值为空，当字段goods_id等于1时
update lab_goods set goods_thumb=null,goods_img=null,original_img=null where goods_id=1;

 

修改折扣

update lab_member_price set user_price=75 where goods_id>=33706 and goods_id<=36670 and user_rank=31;

update lab_member_price set user_price=74 where goods_id>=33706 and goods_id<=36670 and user_rank=32;
update lab_member_price set user_price=73 where goods_id>=33706 and goods_id<=36670 and user_rank=33;

update lab_member_price set user_price=72 where goods_id>=33706 and goods_id<=36670 and user_rank=34;

删除记录
delete from lab_goods where keywords='AbD Serotec';

delete from lab_goods where goods_id>17088;

delete from lab_goods where goods_id<=17088 and goods_id>=9089;
同时再删除对应的折扣记录：
delete from lab_member_price where goods_id<=17088 and goods_id>=9089;

 

查找记录

查找表lab_goods中字段goods_sn中前三个字符是LAB的记录

select * from lab_goods where goods_sn like 'LAB%';

删除表内所有记录
delete from 表名;