MySQL常用基础命令

1. 创建表

create table 表明
(
id number(11) primary key,
goodsid number(12) not null,
vendorid number(12),
batchid number(12),
userid number(10),
approvestatus number(2),
jobstatus number(2)
)

2. 添加字段

alter table table_name add 字段 类型;

3. 添加数据

方法一：INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
方法二：insert into table_name values(值1, 值2,....)

4. 删除表

delete from table _name 

5. 修改字段值

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

6. 查找单表

select * from table_name

7. 模糊查询含有小的

select * from table_name where 字段 like'%小%'

8. 多字段模糊查询（多用户首页查询）

SELECT * FROM goods WHERE CONCAT (IFNULL(goods_name,''),IFNULL(goods_describe,'')) LIKE '%神奇%'

9. 查询某个字段为空所有值

select * from table_name where 字段 is null

10. 查询出来有哪些重复的

select * from order_info group by user_id having count(user_id)>1;

11. 查询出来重复的数据

select count(1) from order_info WHERE user_id in ( select user_id from order_info group by user_id having count(user_id)>1)

12. 查询出来某个字段出现的次数

select id,count(*) from goods GROUP BY id having count(*)>0 

13. 查询出5条按照id出现次数倒叙

select id,count(*)as 'num' from goods GROUP BY id ORDER BY num desc LIMIT 0,5

14. 两表联合查询查询商品和商品详情

SELECT g.*,d.* FROM goods g,goods_dtl d
WHERE g.`id`=d.`goods_id`