MySQL常用操作

一、设置更改root密码

第一次进入数据库不需要密码:

[root@localhost ~]# /usr/local/mysql/bin/mysql -uroot       //-u 指定要登录的用户,后面有无空格都行;root为mysql自带的管理员账号,默认没有密码
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> quit           //退出时直接输入quit或exit即可
Bye

上面命令使用了绝对路径,不是很方便,为了更方便,可以修改/etc/profile把 /usr/local/mysql/bin加入到环境变量中:

[root@localhost ~]# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
[root@localhost ~]# source /etc/profile
[root@localhost ~]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

这样就可以不用每次使用绝对路径了。

  • 给root用户设定密码:
[root@localhost ~]# mysqladmin -uroot password '123456'     //设定密码为123456
Warning: Using a password on the command line interface can be insecure.      //这行为警告信息,意思是在命令行下面暴露了密码,不安全
[root@localhost ~]# mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)     //再次登录,提示错误,因为没有密码
  • 重新输入密码登录:
[root@localhost ~]# mysql -uroot -p      //-p 后面不可以有空格,可以直接跟密码,也可以不跟,不跟密码就是以交互形式输入密码
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
  • 更改root用户密码:
mysql> SET PASSWORD FOR 'root'@localhost =PASSWORD('1234567');       //之前密码为123456,现在改为1234567
Query OK, 0 rows affected (0.00 sec)
[root@localhost ~]# mysql -uroot -p
Enter password:                   
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)      //输入123456,提示错误
[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.     //输入1234567,成功登陆
  • 忘记root用户密码时修改密码:
[root@localhost ~]# vim /etc/my.cnf
skip-grant       //在[mysqld]下面增加这一行
[root@localhost ~]# /etc/init.d/mysqld restart         //重启服务
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!        
[root@localhost ~]# mysql -uroot        //进入mysql
mysql> use mysql;          //进入mysql库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>  update user set password = password ('1234567')   where user='root';         //修改root密码
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0
[root@localhost ~]# vim /etc/my.cnf          //去掉skip-grant这行
[root@localhost ~]# /etc/init.d/mysqld restart         //重启服务
[root@localhost ~]# mysql -uroot -p          //使用新密码登陆

二、连接MySQL

上面我们使用 mysql -uroot -p 命令来连接数据库,但是连接的只是本地数据库的localhost。而很多时候,我们需要连接网络中某一主机上的mysql。

[root@localhost ~]# mysql -uroot -p -h192.168.33.128 -P3306      //-h 指定远程主机的IP,-P(大写)用来指定远程主机mysql的绑定端口
Enter password: 

三、MySQL常用命令

在日常工作中,难免会遇到一些与mysql相关的操作,如建库、建表、查询mysql状态等,我们需要掌握关于这些常用的命令。

查询当前库

mysql> show databases;       //注意每条命令的最后面都需要跟一个分号作为结束符号
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)

查询某个库的表

  • 先要切换到某个库里:
mysql> use mysql;      //切换库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A          //提示会把当前库里的所有表的字段全部读一段,可以在启动mysql时加上-A关闭这个提示,不关闭也无影响

Database changed            
  • 然后再把表列出来:
mysql> show tables;        
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
28 rows in set (0.00 sec)

查看某个表的全部字段

mysql> desc db;         //查看db表的全部字段
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(16)      | NO   | PRI |         |       |
| Select_priv           | enum('N','Y') | NO   |     | N       |       |
| Insert_priv           | enum('N','Y') | NO   |     | N       |       |
| Update_priv           | enum('N','Y') | NO   |     | N       |       |
| Delete_priv           | enum('N','Y') | NO   |     | N       |       |
| Create_priv           | enum('N','Y') | NO   |     | N       |       |
| Drop_priv             | enum('N','Y') | NO   |     | N       |       |
| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
| References_priv       | enum('N','Y') | NO   |     | N       |       |
| Index_priv            | enum('N','Y') | NO   |     | N       |       |
| Alter_priv            | enum('N','Y') | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
| Event_priv            | enum('N','Y') | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)
  • 另外,还可以使用这条命令:
mysql> show create table db\G          //这个命令显示信息更详细,且会把建表语句全部列出来; \G让列出来的结果竖排显示,这样看起来更清晰,用了\G就不用加分号了。
*************************** 1. row ***************************
       Table: db
Create Table: CREATE TABLE `db` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Host`,`Db`,`User`),
  KEY `User` (`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileges'
1 row in set (0.00 sec)

查看当前是哪个用户

mysql> select user();       //查看当前用户
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

查看当前所使用的数据库

mysql> select database();        //查看当前数据库
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)

创建一个新库

mysql> create database db1;          //新建一个库db1
Query OK, 1 row affected (0.00 sec)

创建一个新表

mysql> use db1          //切换到库db1
Database changed
mysql> create table t1 (`id` int(4),`name` char(40));          //新建表t1,并且写入数据,字段名id和name用反引号括起来
Query OK, 0 rows affected (0.01 sec)

查看当前数据库的版本

mysql> select version();      //查看当前mysql版本
+-----------+
| version() |
+-----------+
| 5.6.36    |
+-----------+
1 row in set (0.00 sec)

查看MySql的当前状态

mysql> show status;          //查看当前mysql状态
+-----------------------------------------------+-------------+
| Variable_name                                 | Value       |
+-----------------------------------------------+-------------+
| Aborted_clients                               | 0           |
| Aborted_connects                              | 2           |
| Binlog_cache_disk_use                         | 0           |
| Binlog_cache_use                              | 0           |
| Binlog_stmt_cache_disk_use                    | 0           |
| Binlog_stmt_cache_use                         | 0           |
| Bytes_received                                | 1124        |
| Bytes_sent                                    | 25602       |
| Com_admin_commands                            | 0           |
| Com_assign_to_keycache                        | 0           |         
...

查看MySql的参数

mysql> show variables;           //查看mysql各参数
| innodb_stats_sample_pages                              | 8                                                                                                                                                                                                                                                                                                                                                |
| innodb_stats_transient_sample_pages                    | 8                                                                                                                                                                                                                                                                                                                                                |
| innodb_status_output                                   | OFF                                                                                                                                                                                                                                                                                                                                              |
| innodb_status_output_locks                             | OFF                                                                                                                                                                                                                                                                                                                                              |
| innodb_strict_mode                                     | OFF                                                                                                                                                                                                                                                                                                                                              |
| innodb_support_xa                                      | ON                                                                                                                                                                                                                                                                                                                                               |
| innodb_sync_array_size                                 | 1                                                                                                                                                                                                                                                                                                                                                |
| innodb_sync_spin_loops                                 | 30                                                                                                                                                                                                                                                                                                                                               |
| innodb_table_locks                                     | ON                                                                                                                                                                                                                                                                                                                                               |
| innodb_thread_concurrency                              | 0                                                                                                                                                                                                                                                                                                                                                |
| innodb_thread_sleep_delay                              | 10000                                                                                                                                                                                                                                                                                                                                            |
| innodb_tmpdir                                          |     
...

修改MySql的参数:

上面列出的很多参数都是可以在/etc/my.cnf中定义的。

  • 以参数 max_connect_errors为例,修改它:
mysql> show variables like 'max_connect%';      //mysql中,符号%类似于shell下的*,表示通配
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 100   |
| max_connections    | 151   |
+--------------------+-------+
2 rows in set (0.00 sec)

mysql> set global max_connect_errors = 1000;       //修改max_connect_errors的值为1000,set global可以临时修改一些参数,重启mysql服务失效,修改配置文件my.cnf才能永久生效
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'max_connect%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 1000  |            
| max_connections    | 151   |
+--------------------+-------+          //max_connect_errors的值为1000
2 rows in set (0.00 sec)

查看当前MySql服务器的队列

查看服务器队列在日常工作中用的最多,使用它可以查看mysql当前在干什么,也可以发现是否有锁表。 

  • 查看服务器队列:
mysql> show processlist;         //查看服务器队列
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 11 | root | localhost | db1  | Sleep   | 9373 |       | NULL             |
| 12 | root | localhost | NULL | Query   |    0 | init  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

四、MySQL用户管理

创建一个普通用户并授权

mysql> grant all on *.* to user1 identified by '123456';
Query OK, 0 rows affected (0.00 sec)


1. all表示所有的权限(如读、写、查询、删除等操作);
2. . 旁边有两个匹配条件,前面的*表示所有的数据库,后面的*表示所有的表; 
3. identified by后面跟密码,用单引号括起来,这里user1指的是localhost上的user1
  • 给网络上其他机器上的某用户授权:
mysql> grant all on db1.* to 'user2'@'192.168.33.128' identified by '111222';
Query OK, 0 rows affected (0.00 sec)



用户和主机的IP都用单引号括起来,两者之间有@符号,IP可以用%代替,表示所有主机

五、常用SQL语句

查询语句select

  • 第一种形式:
mysql> select count(*) from mysql.user;
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

//mysql.user 表示mysql库的user表,count(*)表示表中共有多少行
  • 第二种形式:
mysql> select * from mysql.db;
+----------------+---------+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host           | Db      | User  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+----------------+---------+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| %              | test    |       | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | N                  | N            | Y          | Y            |
| %              | test\_% |       | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | N                  | N            | Y          | Y            |
| 192.168.33.128 | db1     | user2 | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | Y                  | Y            | Y          | Y            |
+----------------+---------+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
3 rows in set (0.00 sec)

//上面表示查询mysql库的db表中的所有数据,谨慎使用该命令,因为需要耗费很大的cpu资源
  • 查询单个字段或多个字段:
mysql> select db from mysql.db      //查询单个字段
mysql> select db,user from mysql.db      //查询多个字段
  • 使用%查询:
mysql> select * from mysql.db where host like '192.168.%'     //查询IP为192.168网段的mysql库的db表中的所有数据

插入一行insert

插入操作在mysql中也很常见。 
- 插入:

mysql> insert into db1.t1 values (1,'abc');      //向db1库的t1表中插入1,abc
Query OK, 1 row affected (0.01 sec)
mysql> select * from db1.t1;
+------+------+
| id   | name |
+------+------+
|    1 | abc  |
+------+------+
1 row in set (0.00 sec)

更改表的某一行update

mysql表里存放的数据支持更改某个字段。 
- 更改:

mysql> update db1.t1 set name='aaa' where id=1;      //更改id=1的name为aaa
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from db1.t1;
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
+------+------+
1 row in set (0.00 sec)

清空某个表的数据truncate

有时候我们不想删除表,只想清空数据。 
- 清空:

mysql> truncate table db1.t1;         //清空db1库的t1表
Query OK, 0 rows affected (0.01 sec)
mysql> select * from db1.t1;
Empty set (0.00 sec)

删除表drop table

如果某个表不需要了,那就直接删除。 
- 删除表:

mysql> drop table db1.t1;      //删除db1库的t1表
Query OK, 0 rows affected (0.01 sec)

六、MySql数据库的备份与恢复

MySql备份mysqldump

[root@localhost ~]# mysqldump -uroot -p'123456' mysql > /tmp/mysql.sql
Warning: Using a password on the command line interface can be insecure.

//-u 和 -p 的作用和前面一样,后面的mysql指的是库名,然后重定向到一个文档里

MySql恢复

[root@localhost ~]# mysql -uroot -p'123456' mysql < /tmp/mysql.sql //恢复时使用的是mysql命令而不是mysqldump。
Warning: Using a password on the command line interface can be insecure.

扩展:

mysql5.7 root密码更改

myisam 和innodb引擎对比

mysql 配置详解

mysql调优

同学分享的亲身mysql调优经历

SQL语句教程

什么是事务?事务的特性有哪些

根据binlog恢复指定时间段的数据

mysql字符集调整​​​​​​​

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值