mysql 操纵数据语句

一、数据库方面。

1、新建数据库

     create database database-name（test）;

2、显示数据库

      show databases;

3、使用数据库

      use database-name（test）;

4、查看数据库表

      show tables;

二、表方面

1、添加表。

      create table table-name（field-name-1 field-type-1 modifiers，field-name-2 field-type-2 modifiers，...，field-name-n field-type-n modifiers）;

      例子：create table members(

                            member_id int(11) unsigned not null auto_increment,

                            name varchar(50) not null default '',

                            tel varchar(15) not null default ''.

                           email varchar(50)  not null default '',

                           primany key (member_id)

                      ) type=myisam;

2、删除表

      drop table table-name

      例子：drop table members;

3、添加表字段

      alert table table-name add new-field-name  new-field-type;

      例子：alert table members add groupid tinyint(3) unsigned not null default 0;

4、修改表字段

      alert table table-name modify old-field-name new-field-name new-field-type modifiers;

      例子：alert table members modify groupid groupid tinyint(3) unsigned not null default 0;

三、记录方面

1、insert 添加记录

      insert into table-name(field-name-1, field-name-2,...,field-name-n) values(value-1,value-2,....,value-n);

2、delete 删除记录

      delete from table-name OR delete from table-name where field-name-1 = value-1 and field-name-2=value2 ..... and field-name-n=value-n;

3、update 更新记录

      update table-name set field-name-1 = value-1 OR update table-name set field-name-1=value-1 where field-name-2 = value-2;

4、select 查询

      select  from members

      select distinct member_id from members

      select count(*) from (select distinct member_id from members) a