mysql常用操作

 

 

数据库登录

• 查看mysql版本

mysql -V

• 设置数据库编码方式

SET NAMES 'utf8';

　　

 

mysql -h host -u user -p

• 创建表

CREATE TABLE `table_name` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `number` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

• 创建数据库

CREATE DATABASE 数据库名;

• 切换数据库

use  `data_name`

• 显示所有表

 

show tables;

• 删除数据表

DROP TABLE table_name ;

 

• 插入数据

 

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

• 查询数据库

 

SELECT column_name, column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

 

• 更新数据库

 

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

• 删除数据记录

DELETE FROM table_name [WHERE Clause]

• like模糊查询

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

• 分组查询

SELECT name, COUNT(*) FROM   employee_tbl  GROUP BY name;