课堂笔记2（乱）

select it.iname 商品名称,itc.cname 商品类别名称 from item it join itemcategory itc on
it.cid=itc.id;
it 为item的别名 ，提高运行速度，itc为itcategory的别名。
商品名称为iname的别名，列的别名，影响运行速度。
select table_name from user_tables;

select * from user_tables;

--select * from item;

--内连接

select * from item it join itemcategory itc on it.cid=itc.cid;
select * from item it，itemcategory itc where it.cid=itc.cid;

--外连接
----左外连接
----右外连接
----完全连接
---交叉连接
--给表和列起别名

select it.iname,itc.cname from item it join itemcategory itc on it.cid=itc.id;
--11
select count(*) from item;
--6
select count(*) from itemcategory;

--left join:先做内连接，再加上左表里剩余的记录
--11

select * from item it left join itemcategory itc on it.cid=itc.id;
select * from itemcategory itc left join item it on it.cid=itm.id;

--right join:先做内连接，再加上右表里剩余的记录

select * from item it right join itemcategory itc on it.cid=itc.id;
select * from itemcategory itc right join item it on it.cid=itm.id;

--cross join:笛卡尔积

select * from item cross join itemcategory;

--查询每一个员工选修的职位

select * from staff st join fund_non fu on st.cid=fu.id;
select sname,jname from staff st join staff_jobs sta on st.id=sta.sid join jobs jo on sta.cid=jo.id;