MySQL 常用操作

一．MySQL 创建用户以及授权

1、新建用户：

>CREATE USER name IDENTIFIED BY 'sxt';

 

2、增加新用户 

grant select on 数据库.* to 用户名@登录主机 identified by “密码”

3、查看当前用户

 select user();

 

4、更改密码：

1）更改环境变量

修改/etc/profile 文件，文件尾添加 mysql 的绝对路径 （查看绝对路径echo $PATH），修改环境变量（如果不修改则用/usr/local/mysql/bin/mysql -uroot -p）,修改完source /etc/profile使其生效

2）创建 MySQL 密码

mysqladmin -uroot password '123456'  //为 root 用户创建初始密码（' '加不加都行，最好加上）

注：可以忽略 warning 内容，指的是明码输入屏幕不安全。

mysql -uroot -p123456  //完成初始密码登录

3）密码重置

修改配置文件/etc/my.cnf，在 mysqld 配置段，增加字段 skip-grant

修改配置文件配置段

修改完成后，重启mysql服务：

/etc/init.d/mysqld restart

使用命令登入mysql（修改的配置段，是完成忽略授权的操作，可以直接登入，无需输入用户名密码），切换到 mysql 库，对 user 表进行更新操作（在mysql中use mysql切换到mysql库  ）

修改完成后，确认新密码登录有效。把/etc/my.cnf 改回原有状态，并重启 mysql

更新user表加password：

update user set password=password(‘linux’) where user=’root’(第二个password会在查看密码内容中加密密码）

4）连接 MySQL

Mysql不好使时，alias mysql=/usr/local/mysql/bin/mysql 
mysql -uroot -p123456
远程连接：mysql -uroot -p123456 -h127.0.0.1 -P3306.
适合本机连接：mysql -uroot -p123456 -S/tmp/mysql.sock （S指定文件路径）
连接的时候操作一些命令mysql  -uroot  -p123456  -e “show databases”  select * from user();

5、权限管理

1）进行授权 （授权后创造用户）

grant all on *.* to 'user1' identified by 'passwd';
grant all on *.* to 'user1' @127.0.0.1 identified by 'passwd';
默认socket登录：grant all on *.* to 'user1'@‘localhost’ identified by 'passwd';
对具体的:grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.133.1' identified by 'passwd'; （如果有两个IP地址，重新输入改一下ip）
对所有的ip进行授权：grant all on db1.* to 'user3'@'%' identified by 'passwd';
删除授权： 
mysql> revoke all privileges on *.* from root@”%”; 
mysql> delete from user where user=”root” and host=”%”; 
mysql> flush privileges; 

创建一个用户custom在特定客户端it363.com登录，可访问特定数据库fangchandb 

mysql >grant select, insert, update, delete, create,drop on fangchandb.* to custom@ it363.com identified by ‘ passwd’ 

2）查看授权表

>show grants;
>show grants for name;    //查看name用户权限
>show grants for user2@192.168.134.130;
>grant select on db_name.* to name;　　　　//给name用户db_name数据库的所有权限
>revoke select on db_name.* to name;　　　　//grant的反操作，去除权限；

二、数据库操作　

1、查看数据库：

>show databases;

 

2、创建数据库：

 >create database db_name;　　//db_name为数据库名

 

3、使用数据库：

 >use db_name;

 

4、删除数据库：  

  >drop database db_name;

5、刷新数据库    

  > flush privileges；

6、显示数据库mysql中所有的表  

 >show tables;

7、显示表mysql数据库中user表的列信息 

 >describe user;

8、查看当前使用的数据库 

>select database();

   查看密码内容 

>select passwd from user where user=’root’；

9、查看当前数据库版本

 >select version();

 

10、查看数据库状态 

>show status;

三、表

1、创建表：

>use db1; create table t1(`id` int(4), `name` char(40));
>default charset=utf8;　　//设置默认的编码，防止数据库中文乱码

 

2、复制表：

>create table tb_name2 select * from tb_name;
或者部分复制：
>create table tb_name2 select id,name from tb_name;

 

3、创建临时表：

　>create  temporary table tb_name(这里和创建普通表一样);

 

4、查看数据库中可用的表：

>show tables;

 

5、查看表里的字段：

 > desc tb_name;

 

6、删除表： 

>drop table tb_name;

7、表重命名：

>rename table name_old to name_new;或者

>alter table name_old rename name_new;

 

8、查看建表语句 

>show create table tb_name\G;

9、查看各参数 

>show variables; show variables like 'max_connect%';

10、修改参数 

>set global max_connect_errors=1000;

11、查看队列

 >show processlist; show full processlist;

 

12、修改表更改表结构  

>alter table tb_name add要更改的内容

四、MYSQL 常用 SQL 语句

1、查看表内行数 

>select count(*) from mysql.user;

2、查看 db 表内的内容 

>select * from mysql.db;

 

3、查看 db 表内含有 db 字段的内容 

>select db from mysql.db;

 

4、搜索查看多个字段 

>select db,user from mysql.db;

注：搜索多个字段时，字段中间要用“，”隔开

5、查询 host 为 127.0 的内容  

>select * from mysql.db where host like  '192.168.%';

6、向 db1.t1 中插入内容 

>insert into db1.t1 values (1, ‘abc’);

 

7、把 id=1 的字段内容更新成 

>aaa update db1.t1 set name='aaa' where  id=1;

 

8、清空 db1.t1 表内的内容 

>truncate table db1.t1;

 

注：清空后表的结构依然存在

9、删除 db1.t1 表内的内容 

>drop table db1.t1;

 

注：清空后连同表的结构一同删除

10、清空 db1.t1 数据库 

>drop database db1;

 

五、MySQL 数据库的备份与恢复

1、备份库

>mysqldump -uroot -p123456 mysql > /tmp/mysql.sql

2、恢复库

>mysql -uroot -plinux mysql < /tmp/mysql.sql

 

3、备份表

>mysqldump -uroot -plinux mysql user > /tmp/user.sql

4、恢复表

>mysql -uroot -plinux mysql < /tmp/user.sql

5、备份所有库

>mysqldump -uroot -p -A > /tmp/123.sql （all）

6、只备份表结构

>mysqldump -uroot -p123456 -d mysql > /tmp/mysql.sql

六、数据

1、插入数据：

>insert into tb_name(id,name,score)values(null,'张三',140),(null,'张四',178),(null,'张五',134);  (这里的插入多条数据直接在后边加上逗号，直接写入插入的数据即可；主键id是自增的列，可以不用写)

2、插入检索出来的数据：

>insert into tb_name(name,score) select name,score from tb_name2;

3、更新数据（指定更新数据）

>update tb_name set score=189 where id=2;
>update tablename set columnname=newvalue [where condition ]

4、删除数据

>delete from tb_name where id=3;