mysql设置更改root密码、连接mysql、常用命令
查看有哪些数据库： mysql> show databases;+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
切换到mysql库：
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
Database changed
mysql>
查看库里的表：
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 user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
......
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
43 rows in set (0.00 sec)
查看表是怎么创建的
mysql> show create table user\G;
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
`Password` char(41) CHARACTER SET latin1 COLLATE latin1_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',
......
// \G 是让结果竖排显示；
查看当前登录的用户：
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;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use db1 //切换到db1库
Database changed
创建表：
mysql> use db1;
// 先切换到指定库下
Database changed
mysql> create table t1(`id` int(4),`name` char(40));
// 括号中是定义字段及字段格式，使用反引号引起来
Query OK, 0 rows affected (1.51 sec)
// drop table t1，可以删除表。
查看当前数据库的版本：
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.35 |
+-----------+
1 row in set (0.00 sec)
// 数据库版本：5.6.35
查看数据库状态：
mysql> show status;
+-----------------------------------------------+-------------+
| Variable_name | Value |
+-----------------------------------------------+-------------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
+-----------------------------------------------+-------------+
查看所有参数：
mysql> show variables\G;//查看所有参数
查看指定参数
mysql> show variables like 'max_connect%'\G;
// like表示匹配；%是通配符
更改参数：
mysql> set global max_connect_errors=110;
Query OK, 0 rows affected (0.04 sec)
#在此只是临时更改，如果要永久更改，需要编辑配置文件/etc/my.cnf
查看队列：
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
|> +----+------+-----------+------+---------+------+-------+------------------+
|5 | root | localhost | db1| Query | 0 | init| show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.01 sec)
完整显示：
mysql> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
|> +----+------+-----------+------+---------+------+-------+-----------------------+
|6 | root | localhost | db1| Query | 0 | init| show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)
页:
[1]