数据库
1 查询
Select * From table
select host,user,password from MySQL.user where user='ybb' and host='%'; 查询用户
- select user,host,password from MySQL.user;(查看用户, IP, 密码对应表)
- select user,host,db from MySQL.db;(查看用户, IP, 数据库对应表)
- select distinct user from MySQL.user;(查看所有用户)
2 删除
truncate + 表名 删数据柳表结构
delete from t_learner
3 事务运行命令
- /opt/server/MySQL/bin/MySQL
- begin
- commit
4 修改 root 密码
方法 1
- MySQL -hlocalhost -uroot -p
- MySQL>update MySQL.user set password=password('新密码') where User="root"andHost="localhost";
方法 2: 用 mysqladmin
格式: mysqladmin -u 用户名 -p 旧密码 password 新密码
例子: mysqladmin -uroot -p123456 password 123
5 赋权
grant select,insert,update,delete,create on wlc.* to wlc@localhost identified by 'wlc123'; 赋权增删改查
grant all privileges on wlcpay.* to wlcpay@"%" identified by 'wlcpay123'; 赋权所有权限
flush privileges; 刷新权限列表
show grants for authuser; 查看用户权限
6 添加索引
1). 添加 PRIMARY KEY(主键索引)
MySQL>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
2). 添加 UNIQUE(唯一索引)
- MySQL>ALTER TABLE `table_name` ADD UNIQUE (
- `column`
- )
3) 添加 INDEX(普通索引)
- CREATE INDEX idx_tlearner_learnerUserID ON vocedudb.t_learner(LearnerUserID);
- MySQL>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
4). 添加 FULLTEXT(全文索引)
MySQL>ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
5). 添加多列索引
MySQL>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
7 查看 MySQL 数据库中所有用户
MySQL> SELECT DISTINCT CONCAT('User:''',user,'''@''',host,''';') AS query FROM MySQL.user;
查看数据库中具体某个用户的权限
MySQL> show grants for 'cactiuser'@'%';
备份数据库里表 mysqldump -hip oil_statistics wl_daily_email -uwanglibao_yunwei -p> wl_daily_email2017.11.9
恢复
MySQL -hhost -uroot -p user<user_back.sql
8 查看指定数据库的表的大小,
MySQL> SELECT CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024),2),'MB') AS DATA FROM information_schema.TABLES WHERE table_schema='MySQL' and table_name='user';
来源: http://www.bubuko.com/infodetail-2785011.html