cmd下操作mysql的常用命令

表名 user 字段 username , id ,password ,gid ,number ,sex ,age ,live, conidition 表名，commidity ，字段名gid ，gname ，price ,remamder

mysql> insert into user values(2,‘ruzhaocheng’,‘123456’,‘young’); //插入数据

mysql> insert into user(id,username,password,conidition)
values(3,‘zhangziqiang’,‘123456’,‘young’); //插入数据

mysql> insert into user
(id,username,password,conidition)values(6,‘laolizi’,‘2222’,‘old’),(7,‘laoli’,‘2222’,‘old’);//多条插入

mysql> delete from user where id =6; //删除，整条数据全部删除 //不加where将删除全部数据

mysql> update user set id =5 where id =7; //更改数据

mysql> update user set username =‘wwww’,password = ‘13579’,conidition = ‘child’ where id =4;//更改多项数据

mysql> select *from user; //显示数据 user是表名 查询所有信息；

mysql> select username from user; //查询指定数据

mysql> select username,password from user; //查询多个指定数据

mysql> select distinct password from user; //去除查询过程中的重复值

mysql> select * from user where id =1; //查询整行信息

mysql> select * from user where id >3; // 查询id大于2的整行信息

mysql> select * from user where age =12 or age =70;

mysql> select * from user where age between 20 and60;

mysql> select * from user where age !=50; //年龄不等于50

mysql> select * from user where age in(12,20,30); //选取 年龄为 12 ，20 ，30的数据

mysql> select * from user order by age; //按年龄升序排列

mysql> select * from user order by age desc; //年龄降序排列

mysql> select * from user order by age asc; //按年龄升序排列

mysql> select * from user limit 5 ,5; //从第5行开始查询，查询下面的5行数据

mysql> select * from user limit 5 ; //从第0行开始取5条数据

mysql> select * from user group by live; // 分组 按live字段排序，不会显示live的重复数据，去重

mysql> select count(*) from user; //查询有多少条数据

mysql> select username as name from user; //取别名

mysql> alter table 表名 add index 索引名 (字段名1[，字段名2 …]); //加索引

mysql> alter table 表名 add primary key (字段名); //加主关键字的索引

mysql> alter table 表名 add unique 索引名 (字段名); //加唯一限制条件的索引

mysql> alter table 表名 drop index 索引名; //删除某个索引

mysql> ALTER TABLE table_name ADD field_name field_type; //增加字段

mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type; //修改原字段名称及类型

MySQL ALTER TABLE table_name DROP field_name; //删除字段

//username为user（表名）的字段，gname为commidity（表名）的字段，gid 为两个表都有的字段
mysql> select username , gname from user inner join commidity on user.gid = commidity.gid; //查询两个内联表的数据

mysql> select user .username from user right/left join commidity on user.gid =commidity.gid; //左连，左表为基准。右联

mysql> select * from user where gid in(select gid from commidity); //嵌套查询 ==mysql> select * from user where gid in(1,2,3);