mysql操作

1.启动:/etc/init.d/mysqld start 

2.停止:/etc/init.d/mysqld stop

3.重启:/etc/init.d/mysqld restart 

 

查看数据库

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| test |

+--------------------+

2 rows in set (0.00 sec)

选择数据库

mysql> use test;

Database changed

 

 

查看数据库属性

mysql> show create database test;

+----------+---------------------------------------------------------------+

| Database | Create Database |

+----------+---------------------------------------------------------------+

| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |

+----------+---------------------------------------------------------------+

1 row in set (0.00 sec)

 

查看表清单

mysql> show tables;

+---------------------+

| Tables_in_test |

+---------------------+

| auto_case |

| auto_case_copy |

| data_api_login |

| data_api_login_copy |

+---------------------+

4 rows in set (0.00 sec)

 

查看表结构


mysql> show create table data_api_login7;

+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| data_api_login7 | CREATE TABLE `data_api_login7` (

`id` mediumint(8) NOT NULL AUTO_INCREMENT,

`login_name` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

`login_passwd` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT '',

`method` varchar(8) CHARACTER SET utf8 NOT NULL DEFAULT '',

`remark` varchar(255) CHARACTER SET utf8 DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=gb2312 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC |

+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

 

查看字符集

mysql> show variables like 'char%';

+--------------------------+----------------------------+

| Variable_name | Value |

+--------------------------+----------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | latin1 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | latin1 |

| character_set_system | utf8 |

| character_sets_dir | /opt/lampp/share/charsets/ |

+--------------------------+----------------------------+

8 rows in set (0.00 sec)

更改字符集

mysql> set character_set_database = "utf8";

Query OK, 0 rows affected (0.00 sec)

 

mysql> set character_set_server = "utf8";

Query OK, 0 rows affected (0.00 sec)

 

更改库级的字符集--更改只会影响新增表，不会影响已有表

mysql> alter database test default character set = utf8;

Query OK, 1 row affected (0.00 sec)

更改某张表的字符集--更改只会影响新增，不会影响已有？？

mysql> alter table data_api_login default character set = utf8;

Query OK, 6 rows affected (0.03 sec)

Records: 6 Duplicates: 0 Warnings: 0

 

建表

CREATE TABLE `data_api_login11` (

`id` MEDIUMINT(8) NOT NULL AUTO_INCREMENT,

`login_name` VARCHAR(64) NOT NULL DEFAULT '',

`login_passwd` VARCHAR(64) NOT NULL DEFAULT '',

`method` VARCHAR(8) NOT NULL DEFAULT '',

`remark` VARCHAR(255) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC

插入记录

INSERT INTO test.data_api_login11

(id,

login_name,

login_passwd,

method,

remark

)

VALUES

('id',

'login_name',

'login_passwd',

'method',

'remark'

);

 

更新记录

UPDATE test.data_api_login11

SET

id = 'id' ,

login_name = 'login_name' ,

login_passwd = 'login_passwd' ,

method = 'method' ,

remark = 'remark'

 

WHERE

id = 'id' ;

 

清缓存查询数据

RESET QUERY CACHE;

SELECT * FROM data_api_login11

查看MySQL服务器配置信息

mysql> show variables;

查看MySQL服务器运行的各种状态值

mysql> show global status;

慢查询

mysql> show variables like '%slow%';

+------------------+-------+

| Variable_name | Value |

+------------------+-------+

| log_slow_queries | OFF |

| slow_launch_time | 2 |

+------------------+-------+

mysql> show global status like '%slow%';

+---------------------+-------+

| Variable_name | Value |

+---------------------+-------+

| Slow_launch_threads | 0 |

| Slow_queries | 279 |

+---------------------+-------+

配置中关闭了记录慢查询（最好是打开，方便优化），超过2秒即为慢查询，一共有279条慢查询


查看MySQL的配置参数

SHOW PROCESSLIST

 

或

 

SHOW VARIABLES LIKE 'Key%'

显示当前已经被打开的表列表

mysqladmin status

使用MySQL自带的mysqladmin 工具查看status，使用以下命令

mysqladmin -uroot –password=’password’ status

 

显示的结果如下：

Uptime: 87117 Threads: 1 Questions: 5481626 Slow queries: 16 Opens: 2211 Flush tables: 1 Open tables:

512 Queries per second avg: 62.923

 

另外可以添加 -i 5 参数，让其每五秒自动刷新之

mysqladmin -uroot –password=’password’ status -i 5

mysqladmin extended-status