#增加环境变量 加入系统环境变量:
vim /etc/profile
export PATH=$PATH:/usr/local/mysql/bin/
##配置文件生效
source /etc/profile
设置更改root密码
##设置密码
mysqladmin -uroot password 'jiangshan'
再次登录:
mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
##更改密码
mysqladmin -uroot -p'jiangshan' password 'jiangshanlinux'
[root@adailinux ~]# mysql -uroot -p'jiangshanlinux'
Welcome to the MySQL monitor.
mysql>
##忘记密码
###编辑mysql配置文件:
vim /etc/my.cnf
[mysqld]
skip-grant //忽略授权
datadir=/data/mysql
socket=/tmp/mysql.sock
###重启mysql服务:
/etc/init.d/mysqld restart
Shutting down MySQL... SUCCESS!
Starting MySQL..................... SUCCESS!
###数据库更改密码
[root@adailinux ~]# mysql -uroot
Welcome to the MySQL monitor.
mysql> use mysql;
#切换mysql库
Database changed
mysql> select * from user\G;
查看用户的表信息,该表中存放的是用户相关信息(密码、授权…) G选项的作用是使输出信息有序显示,不加该选项,显示内容会很乱
mysql> select password from user;
#查看用户密码,显示结果Wie加密字符串!
mysql> update user set password=password('123456') where user='root';
Query OK, 4 rows affected (0.11 sec)
Rows matched: 4 Changed: 4 Warnings: 0
#将密码更改为‘123456’
mysql> quit
Bye
###删除授权
vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/tmp/mysql.sock
连接mysql
##远程连接:使用IP/port连接
mysql -uroot -p123456 -h127.0.0.1 -P3306
Welcome to the MySQL monitor.
mysql> quit
Bye
-h:=host,指定IP;-P:=port,指定端口。
##本地连接:使用socket连接
mysql -uroot -p123456 -S/tmp/mysql.sock
Welcome to the MySQL monitor.
mysql> quit
Bye
-S:=socket,指定socket。此方法只适用于本地连接,等同于“mysql -uroot -p123456”。
##显示所有数据库
mysql -uroot -p'123456' -e "show databases"
mysql常用命令
授权超级用户:
grant all on *.* to 'user1'@'127.0.0.1' identified by '123456';
- 创建user1用户并授予其所有权限“.”(通配符)
- 第一个表示db_name;第二个表示tb_name
- 同时指定其来源IP127.0.0.1(即,只可通过此IP登录)
- 此处可以使用通配符%,代表所有IP(一般不使用)
- 设定密码:identified by
查看库:
show databases;
查看都有哪些库
show databases;
查看某个库的表
use db; show tables \G;
查看表的字段
desc tb;
查看建表语句
show create table tb;
当前是哪个用户
select user();
当前库
select database();
创建库
create database db1;
创建表
create table t1 (id int, name char(40) adress varchar(30));
char(10) 'aaa '
varchar(10) 'aaa'
查看数据库版本
select version();
查看mysql状态
show status;
修改mysql参数
show variables like 'max_connect%'; set global max_connect_errors = 1000;
查看mysql队列
show processlist;
select * from information_schema.processlist where info is not null; sleep的可以忽略,qurey查询的才有 创建普通用户并授权
grant all on *.* to databases1.user1 identified by '123456';
grant all on db1.* to 'user2'@'10.0.2.100' identified by '111222';
grant all on db1.* to 'user3'@'%' identified by '231222';insert into tb1 (id,name) values(1,'aming');
更改密码
UPDATE mysql.user SET password=PASSWORD("newpwd") WHERE user='username' ;
查询
select count(*) from mysql.user; select * from mysql.db; select * from mysql.db where host like '10.0.%';
插入
update db1.t1 set name='aaa' where id=1;
清空表
truncate table db1.t1;
删除表
drop table db1.t1;
删除数据库
drop database db1;
修复表
repair table tb1 [use frm];
查看权限
show grants for root@'localhost';
echo "select user,host,password from mysql.user" |mysql -uroot -plingxiangxiang
mysql -uroot -p1234556 -e "select user,host,password into outfile '/home/mysql/1.txt' from mysql.user;"
;
增:
insert into test.test (id, name) values (123, 'ling');
insert into test.test values (value1_1, value2_2), (value2_1,value2_2), (value3_1, value3_2);
删:
delete from test.test where id in (123, 456);
alter table test drop column dt;删除字段,test表,dt字段
改:
update msyql.user set password = password('lingxiangxiang')
alter table employees add primary key (emp_no); 增加主键
alter table employees drop/add column salaries; 删除字段
create table blog_blogmodel as select * from book_blogmodel; 创建一样的新表
查:
select user, host, password from mysql.user where user = "root";
select * from msyql.user where conditions order by user [desc];
调整字段顺序:
1. ALTER TABLE `user_movement_log` CHANGE `GatewayId` `GatewayId` int not null default 0 AFTER RegionID
2. alter table test2 drop column cj, drop column goushi;
排序:select * from test.test order by id asc/desc;
SELECT * FROM usersWHERE email NOT REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+.[A-Z]{2,4}$'
show global variables like '%read_only%';
alter user root@localhost identified by '';
导出:
select * into outfile '/tmp/test/users.txt' fields terminated by '<[!]>' lines terminated by '<[end]>' from users;
导入:
load data infile '/tmp/test/users.txt' into table gamedb.users fields terminated by '<[!]>' lines terminated by '<[end]>';
create index idx_name on salaries(emp_no); 创建salaries表的emp_no字段的索引
show index from salaries\G; 查看索引
日期常用格式:
'year-month-day'