嵌套查询:
select sname from t1 where t1id in(select t1id from t2 where t2id in(select t2id from t3 where color='黑'));
联结查询:
select t1.sname from t1,t2,t3 where t2.color='黑' and t3.pid=t2.pid and t1.sid=t3.sid;
内联结:
select t2.sid from t1 inner join t2 on t1.color='黑' and t2.pid=t1.pid;
自联结: 通过别名的方式将一张表变成两张表,然后进行多表查询。
select p2.pname from t1 as p1, t1 as p2 where p1.color='黑' and p2.pid=p1.pid;
外联结:
select t1.sname, t2.qty from t1 right join t2 on t1.sid=t2.sid;
联合查询:
select pname from product where color='白'
union
select pname from product where color='黑';
以上等价于
select pname from product where color='白' or color=’黑’;
这种查询会自动去重,如果想不去重,则使用union all
select pname from product where color='白'
union all
select pname from product where color='黑';
1)使用group by 进行分组
例子:
select id, name, count(sum) from t8 group by name;
select id, name, sum(sum) from t8 group by name;
2)、如何创建视图
使用create view 视图名字 as select……
例如将多张表查询,构建一张表(视图)
create view v1 as select jname, sname,pname,qty from supplier,product,project,psj where project.jid=psj.jid and product.pid=psj.pid and supplier.sid=psj.sid;
3)创建一个存储过程:
create procedure proc1()
begin
select curdate();
end;
调用:call proc1();
为了区分上面存储过程里的两个分号(意义不同),使用delimiter ##来替代最后的那个分号。
结果为:
create procedure proc1()
begin
select curdate();
end##
4)有参数过程
create procedure proc2(in id int)
begin
select sname from supplier where sid=id;
end##
5)输出参数过程
create procedure proc3(in id int, out name varchar(50) character set utf8)
begin
select sname into name from supplier where sid=id;
end##
6)在过程里的临时变量
declare name varchar(30) character set utf8 default ‘’;
declare i int; 如果没有default,初始化为null。
7)全局变量不需要提前定义,直接使用,在使用的时候用 @变量名。
mysql-联合查询和分组,存储过
最新推荐文章于 2024-04-02 16:44:41 发布