数据库练习（一）

select * from table limit m,n （分页查询）
select * from employee group by sex; (分组查询)

连接查询:

create table customer(id int primary key auto_increment,name varchar(20),city varchar(20));

create table orders(id int primary key auto_increment,good_name varchar(20),price float(8,2),customer_id int);
insert into customer (name,city) values(‘李老师’,‘东北’);
insert into customer (name,city) values(‘崔老师’,‘山西’);
insert into customer (name,city) values(‘张老师’,‘内蒙’);
insert into customer (name,city) values(‘闫老师’,‘天津’);

insert into orders(good_name,price,customer_id) values(‘电脑’,59,1);
insert into orders(good_name,price,customer_id) values(‘笔记本’,88,2);
insert into orders(good_name,price,customer_id) values(‘吹风机’,99,1);
insert into orders(good_name,price,customer_id) values(‘香水’,300,3);
insert into orders(good_name,price,customer_id) values(‘牛奶’,100,6);

交叉查询

select * from customer,orders;
select * from customer cross join orders;

内连接查询:

select * from customer c inner join orders o on c.id=o.customer_id;
select * from customer,orders where customer.id=orders.customer_id;
select * from customer c,orders o where c.id=o.customer_id;

左外连接:

select * from customer c left join orders o on c.id=o.customer_id;

右外连接:

select * from customer c right join orders o on c.id=o.customer_id;

联合查询 :

select * from customer left join orders on customer.id=orders.customer_id
having price>20;