常用MySQL命令整理

安装

<a href="http://www.jianshu.com/p/fd3aae701db9">http://www.jianshu.com/p/fd3aae701db9</a>

创建服务

mysqld --initialize --datadir=/users/ezio/database/mysql/data

启动

mysqld_safe --datadir=/users/ezio/database/mysql/data &amp;

登录

mysql -uroot -p123456

show variables like '%dir%';

create user 'abc'@'%' identified by 'abc123';

新建数据库

create database database-name;

选择数据库

use database-name;

新建表

create table table-name (

     name1 varchar(30),

     name2 varchar(40)

);

删除表

drop table tablename;

更改表

alter table table-name add name1 int not null auto_increment first,add peimary key (name1)

alter table table-name modify c char(10);

drop column name1;

change column name1 name2 int;

modify column date datetime <b>after age / first</b>;

插入表数据

insert into table-name (name1,name2) values (value1,value2)

add column name1;

删除表数据

delete from table-name where name1='value1' or name2='value2';

delete from tablename where id in (1,2,3,4);

delete from tablename where id between 1 and 254 and id!=10;

查询表数据

select name1 from table-name

排序

select * from dtuic_userprofile order by gmt_created desc limit 1\g

更新表数据

update myclass set name='mary' where id=1;

描述表

describe table-name;

查看索引

show index from dt_parser;

建立索引

alter table dt_parser add index name(status);

alter table table_name add index index_name (column_list)  #增加普通索引

alter table table_name add unique (column_list)                   #增加唯一索引

alter table table_name add primary key (column_list)           #增加主键索引

删除索引

drop index index_name on talbe_name

alter table table_name drop index index_name

alter table table_name drop primary key

联合查询

inner join

select a.runoob_id, a.runoob_author, b.runoob_count from runoob_tbl a inner join tcount_tbl b on a.runoob_author = b.runoob_author;

left join (该语句会读取左边的数据表runoob_tbl的所有选取的字段数据，即便在右侧表tcount_tbl中没有对应的runoob_author字段值)

select a.runoob_id, a.runoob_author, b.runoob_count from runoob_tbl a left join tcount_tbl b on a.runoob_author = b.runoob_author;

right join (该语句会读取右边的数据表 runoob_tbl 的所有选取的字段数据，即便在左侧表tcount_tbl中没有对应的runoob_author字段值)

select b.runoob_id, b.runoob_author, a.runoob_count from tcount_tbl a right join runoob_tbl b on a.runoob_author = b.runoob_author;