MySQL 常用操作

1. 连接数据库

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

D:\>mysql -uroot -p -h127.0.0.1 -P3306Enter password: ******Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 7Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 changedmysql> 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: dbCreate 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: 0mysql>

4.数据操作

查询数据：select

增加数据：insert

更新数据：update

删除数据：delete

mysql> use mytest;Database changedmysql> 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 1mysql> 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: 0mysql> 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: 0mysql> update t1 set salary=12000 where id=1;Query OK, 1 row affected (0.11 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> 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.sqlWarning: Using a password on the command line interface can be insecure.

恢复

D:\>mysql -uroot -p"123456" mytest < mytest.sqlWarning: 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: 0mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)mysql>