1、查看库里面有哪些库 mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || discuz || mysql || test |+--------------------+4 rows in set (0.01 sec)
2、进入某个库(切换库) 1
2
3
4
| mysql> use mysql
Database changed
mysql> use discuz
Database changed
|
3、查看当前所在库
mysql> select database();+------------+| database() |+------------+| discuz |+------------+1 row in set (0.00 sec)4、查看用户
mysql> select user();+----------------+| user() |+----------------+| root@localhost |+----------------+1 row in set (0.00 sec)5、查看数据库版本
mysql> select version();
+------------+
| version() |
+------------+
| 5.1.73-log |
+------------+
1 row in set (0.00 sec)
6、查看discuz个库有哪些表 1
2
3
4
5
6
7
8
9
10
11
| mysql> use discuz
Database changed
mysql> show tables;
+-----------------------------------+
| Tables_in_discuz |
+-----------------------------------+
| pre_common_admincp_cmenu |
| pre_common_admincp_group |
| pre_common_admincp_member |
+-----------------------------------+
297 rows in set (0.00 sec)
|
7、查看表中有哪些字段
mysql> desc pre_ucenter_vars;+-------+-----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-----------+------+-----+---------+-------+| name | char(32) | NO | PRI | | || value | char(255) | NO | | | |+-------+-----------+------+-----+---------+-------+2 rows in set (0.00 sec)8、查看表中某行的创建过程
mysql> show create table pre_ucenter_vars\G;*************************** 1. row *************************** Table: pre_ucenter_varsCreate Table: CREATE TABLE `pre_ucenter_vars` ( `name` char(32) NOT NULL DEFAULT '', `value` char(255) NOT NULL DEFAULT '', PRIMARY KEY (`name`)) ENGINE=MEMORY DEFAULT CHARSET=gbk1 row in set (0.00 sec)ERROR:No query specified
9、创建一个库实例 mysql> create database guozhen; 创建库
Query OK, 1 row affected (0.00 sec)
mysql> use guozhen; 进入库
Database changed
mysql> create table tb1 (`id` int(4), `name` char (40)) ENGINE=MYISAM DEFAULT CHARSET=gbk; 创建一个表
Query OK, 0 rows affected (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
| mysql> show tables; 查看tables库有哪些表
+-------------------+
| Tables_in_guozhen |
+-------------------+
| tb1 |
+-------------------+
1 row in set (0.00 sec)
mysql> desc tb1; 查看tb1表中有哪些字段
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| name | char(40) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> show create table tb1\G; 查看tb1创建过程
*************************** 1. row ***************************
Table: tb1
Create Table: CREATE TABLE `tb1` (
`id` int(4) DEFAULT NULL,
`name` char(40) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
ERROR:
No query specified
|
10、插入一行或者字段
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
| mysql> insert into tb1 values(1,'gz'); 插入一行
Query OK, 1 row affected (0.00 sec)
mysql> select * from tb1; 查看插入的行
+------+------+
| id | name |
+------+------+
| 1 | gz |
+------+------+
1 row in set (0.00 sec)
mysql> insert into tb1 (`id`) values(3); 只插入一个ID字段
Query OK, 1 row affected (0.00 sec)
mysql> select * from tb1;
+------+------+
| id | name |
+------+------+
| 1 | gz |
| 3 | NULL |
+------+------+
mysql> insert into tb1 (`name`) values('55'); 只插入一个name字段
Query OK, 1 row affected (0.00 sec)
mysql> select * from tb1;
+------+------+
| id | name |
+------+------+
| 1 | gz |
| 3 | NULL |
| NULL | 55 |
+------+------+
3 rows in set (0.00 sec)
mysql> insert into tb1 (`name`,`id`) values('linux3',5); 也可以字段颠倒顺序插入
Query OK, 1 row affected (0.00 sec)
mysql> select * from tb1;
+------+--------+
| id | name |
+------+--------+
| 1 | gz |
| 3 | NULL |
| NULL | 55 |
| 5 | linux3 |
+------+--------+
4 rows in set (0.00 sec)
|
11、更新某行(修改)
1
2
3
4
5
6
7
8
9
10
11
12
13
| mysql> update tb1 set id=5 where name='55'; 更新某行
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from tb1;
+------+--------+
| id | name |
+------+--------+
| 1 | gz |
| 3 | NULL |
| 5 | 55 |
| 5 | linux3 |
+------+--------+
4 rows in set (0.00 sec)
|
12、删除行、表、库 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
| mysql> delete from tb1 where name='55'; 删除指定的行
Query OK, 1 row affected (0.00 sec)
mysql> select * from tb1;
+------+--------+
| id | name |
+------+--------+
| 1 | gz |
| 3 | NULL |
| 5 | linux3 |
+------+--------+
3 rows in set (0.00 sec)
mysql> truncate table tb1; 清空某个表,该表还在。
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tb1; 查看已经空了
Empty set (0.00 sec)
mysql> drop table tb1; 删除某个表
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tb1; 查看时已然不存在
ERROR 1146 (42S02): Table 'guozhen.tb1' doesn't exist
mysql> drop database guozhen; 删除某个库
Query OK, 0 rows affected (0.01 sec)
mysql> show databases; 查看已然不存在
+--------------------+
| Database |
+--------------------+
| information_schema |
| discuz |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
|
13、查看队列
mysql> show processlist;+----+------+-----------+------+---------+------+-------+------------------+| Id | User | Host | db | Command | Time | State | Info |+----+------+-----------+------+---------+------+-------+------------------+| 28 | root | localhost | NULL | Query | 0 | NULL | show processlist |+----+------+-----------+------+---------+------+-------+------------------+1 row in set (0.00 sec)14、查看变量参数 mysql> show variables;更改某个变量值(例:max_connections原值为151) mysql> set global max_connections=200; //修改变量值
mysql> show variables like 'max_connections'; //过滤出该值
注:这样重启还是会恢复原值,所以我们要想永远改变,则要去/etc/my.cnf配置文件中修改该值。
15、查看状态 mysql> show status;
mysql> show status like '%running'; //过滤running的;%为万能通配,相当于shell下的*
16、修复表 mysql> repair table discuz.pre_forum_post; //discuz.pre_forum_post是你要修复的表名
|