MySQL 常用操作

最新推荐文章于 2024-07-23 19:15:50 发布

hjh00

最新推荐文章于 2024-07-23 19:15:50 发布

阅读量511

点赞数 1

分类专栏： MySQL 文章标签： mysql 数据备份数据库管理

本文链接：https://blog.csdn.net/hjh00/article/details/52857764

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1. 连接数据库

连接mysql需要4个参数用户名、密码、主机和端口，主机缺省是本机，端口缺省是3306

D:\>mysql -uroot -p -h127.0.0.1 -P3306
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.33 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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>

2.获取数据库的基本信息

1）版本信息

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.33    |
+-----------+
1 row in set (0.00 sec)

2）查看变量

show variables 输出所有变量，show variables like '' 可以输出符合匹配条件的变量。

mysql> show variables like 'version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 5.6.33                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
4 rows in set (0.00 sec)

3）查看数据状态

show status 输出所有变量，show status like ''输出符合匹配条件的变量。

mysql> show status like 'connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 11    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show status like 'log%';
Empty set (0.00 sec)

mysql> show status like '%log%';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| Binlog_cache_disk_use        | 0     |
| Binlog_cache_use             | 0     |
| Binlog_stmt_cache_disk_use   | 0     |
| Binlog_stmt_cache_use        | 0     |
| Com_binlog                   | 0     |
| Com_show_binlog_events       | 0     |
| Com_show_binlogs             | 0     |
| Com_show_engine_logs         | 0     |
| Com_show_relaylog_events     | 0     |
| Innodb_log_waits             | 0     |
| Innodb_log_write_requests    | 136   |
| Innodb_log_writes            | 35    |
| Innodb_os_log_fsyncs         | 41    |
| Innodb_os_log_pending_fsyncs | 0     |
| Innodb_os_log_pending_writes | 0     |
| Innodb_os_log_written        | 80384 |
| Innodb_available_undo_logs   | 128   |
| Tc_log_max_pages_used        | 0     |
| Tc_log_page_size             | 0     |
| Tc_log_page_waits            | 0     |
+------------------------------+-------+
20 rows in set (0.00 sec)

4）查看在运行的线程

show processlist

mysql> show full processlist;
+----+------+-----------------+-------+---------+------+-------+-----------------------+
| Id | User | Host            | db    | Command | Time | State | Info                  |
+----+------+-----------------+-------+---------+------+-------+-----------------------+
| 10 | root | localhost:62481 | mysql | Query   |    0 | init  | show full processlist |
| 11 | cc   | localhost:5071  | NULL  | Sleep   |    6 |       | NULL                  |
+----+------+-----------------+-------+---------+------+-------+-----------------------+
2 rows in set (0.00 sec)

5）数据库
show databases 查看所有数据库，use 切换数据库，select database() 查看当前数据库。

mysql>
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cc                 |
| monitor            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql> use mysql;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)

mysql>

数据库建库语句

mysql> show create database cc;
+----------+-------------------------------------------------------------+
| Database | Create Database                                             |
+----------+-------------------------------------------------------------+
| cc       | CREATE DATABASE `cc` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)

6）查看当前用户

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

查看用户授权信息

mysql> show grants for cc;
+---------------------------------------------------------------------------------------------------+
| Grants for cc@%                                                                                   |
+---------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'cc'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT ALL PRIVILEGES ON `cc`.* TO 'cc'@'%'                                                        |
+---------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

7）查看数据库的表

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.04 sec)

8）查看表的字段

mysql> desc 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.01 sec)

9）查看建表SQL语句

mysql> show create table db \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)

3.DDL操作

1）建数据库

在mysql 服务器的配置文件设置字符集和数据库引擎，用 create database <database name> 创建数据库。

[mysqld]
character-set-server=utf8
default-storage-engine=INNODB

2）删除数据库

drop database

3）建表、增加主键

create table

altet table add primary key

4)删除表

drop table

mysql> create database mytest;
Query OK, 1 row affected (0.00 sec)

mysql> use mytest;
Database changed

mysql> create table t1(id varchar(16), name varchar(32));
Query OK, 0 rows affected (0.45 sec)

mysql> alter table t1 add primary key pk_t1(id);
Query OK, 0 rows affected (0.36 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

4.数据操作

查询数据：select

增加数据：insert

更新数据：update

删除数据：delete

mysql> use mytest;
Database changed
mysql> create t1(id varchar(16), name varchar(32));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1(id varchar(16), name varchar(32))' at line 1
mysql> create table t1(id varchar(16), name varchar(32));
Query OK, 0 rows affected (0.45 sec)

mysql> alter table t1 add primary key pk_t1(id);
Query OK, 0 rows affected (0.36 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert t1 (id,name) values(1,'tom');
Query OK, 1 row affected (0.09 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 1  | tom  |
+----+------+
1 row in set (0.00 sec)

mysql> alter table t1 add column salary float(8,2);
Query OK, 0 rows affected (0.57 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> update t1 set salary=12000 where id=1;
Query OK, 1 row affected (0.11 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;
+----+------+----------+
| id | name | salary   |
+----+------+----------+
| 1  | tom  | 12000.00 |
+----+------+----------+
1 row in set (0.00 sec)

mysql>

5.数据备份

备份

D:\>mysqldump -uroot -p"123456" mytest > mytest.sql
Warning: Using a password on the command line interface can be insecure.

恢复

D:\>mysql -uroot -p"123456" mytest < mytest.sql
Warning: Using a password on the command line interface can be insecure.

6.创建用户和授权

mysql> grant all on mytest.* to 'mytest'@'%' identified by 'password';
Query OK, 0 rows affected (0.10 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.05 sec)

mysql>

7.修改密码

mysql> update mysql.user set password=password('123456') where user='mytest';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>