mysql 数据库基础查询操作命令

查询命令集合

- 数据库基础查询
-以单表数据查询所有数据
select * from table; //查询表中所有信息
select id from table;
select id ,name from table; // 查询表中指定的字段查询
- 数据库条件查询
-以某些数据作为条件进行查询符合条件的数据
select * from table where 条件字段=‘需要检索条件’；
select * from table where table.a is null;//查询条件为空的数据
select * from table where table.a > 100 or table.b >30;// 两者条件有一个满足即可
select * from table where table.a >100 and table.b>30;// 两者条件都要满足
- 数据库排序查询
-查询数据信息以什么条件进行排序，升or 降
select * from table where 条件字段=‘需要检索条件’ order by id desc/asc
- 数据库分组查询
select * from table where 条件字段=‘需要检索条件’ group by id
- 数据库分页查询
select * from table where id >10000 limit 1000,10
- 数据库连接查询
– 交叉连接
select * from table_a cross join table_b;
– 内连接
select * from table_a inner join table_b;
select * from table_a inner join table_b on b_id = id;
select * from table_a inner join table_b on table_a.b_id = table_b.id;
select * from table_a as s inner join table_b c on s.b_id = c.id;
select * from table_b c inner join table_a as s on s.b_id = c.id;
select * from table_b c inner join table_a as s where s.b_id = c.id;
select * from table_a as s left join table_b c on s.b_id = c.id;
select * from table_a as s right join table_b as c on s.b_id = c.id;
select * from table_b as c left join table_a as s on s.b_id = c.id;
– 自然内连接
select * from table_a natural join table_b;
– using关键字
select * from table_a left join table_b using(b_id);
- 数据库子查询

• select* from table_a id in(select id from table_b）// 子查询内的语句同样适用各种查询语句