mysql基础操作

一、数据库操作

创建数据库    create database name;
删除数据库    drop database name;
选择数据库    use name;

二、数据表操作

创建数据表    create table name(column_name column_type)
删除数据表    drop table name

三、数据操作

插入数据      insert into table (field1, field2, field3) values (values1, values2, values3)
查询数据      select * from table where id=1
删除数据      delete from table where id=1
更新数据      update table set field1=value1, field2=value2 where id=1

四、关键字

like         // 模糊查询     
select * from school where like '%com%';

union        // 唯一   
select * from table union [all] select * from school order by field desc

group by     // 分组    
select district_id, count(*) from school group by district_id;
select name, sum(sing) as count from  school group by name with rollup;

is null  、  is not null

五、多表查询

select a.id, a.author, b.count from table1 a, table2 b where a.author = b.author;
// insert join
select a.id, a.author, b.count from table1 a insert join table2 b on a.author = b.author;
// left join
select a.id, a.author, b.count from table1 a left     join table2 b on a.author = b.author;
// right join
select a.id, a.author, b.count from table1 a right   join table2 b on a.author = b.author;

六、ALTER 命令

删除字段    alter table table_name drop field1;
增加字段    alter table table_name add field1 int;
修改字段类型    alter table table_name modify field char(10);
修改字段名称    alter table table_name change field1 field2 int;
修改表名    alter table table_name rename to table_name;

七、索引

创建索引    create index indexName on table (column_name)
添加索引    alter table tableName add index indexName(column_name)
删除索引    drop index [indexName] on table_name;
查询索引    show index from table_name;

八、复制表

show create table school;
create table school_copy;
insert into school_copy (field1, field2) select field1, field2 from school;

九、处理重复数据

// 统计重复数据        
select count(*) as count, last_name, first_name from person group by last_name, first_name having count > 1; 

十、运算符

+    -    *    /    =    <>(!=)    <=>(严格比较相等)    <    >    <=    >=    

between    in    not in    is null    is not null    like
select 5 between 1 and 10;
select 5 in (2,3,4,5,6)
select '12345' like '%2345'