MySQL连接查询练习

1.查询已订购了商品的客户的公司名称、订商品编号和订购数量。

方法1：

SELECT customer.customerid ,customer.companyname, sell_order.productid, sell_order.sellordernumber

FROM customer INNER JOIN sell_order

ON customer. customerid= sell_order. customerid;

方法2：

SELECT customer.customerid ,customer.companyname, sell_order.productid, sell_order.sellordernumber

FROM customer,sell_order

WHERE customer. customerid= sell_order. customerid;

2、查询客户“国皓科技有限公司”订购的商品编号和数量信息。

SELECT customer.companyname, sell_order.productid,sell_order.sellordernumber

FROM customer INNER JOIN sell_order

ON customer. customerid= sell_order. customerid

WHERE customer.companyname='国皓科技有限公司';

3、查询客户“国皓科技有限公司”订购的商品信息，包括商品名称、商品价格和订购的数量

SELECT  product.productname,product.price, sell_order.sellordernumber

FROM   customer inner join sell_order 

ON customer.customerid= sell_order.customerid  /*两张表联接*/

JOIN  product

ON sell_order.productid=product.productid      /*联接第三张表*/

WHERE customer.companyname='国皓科技有限公司';

4.查询目前销售部没有接到订单的员工姓名。

SELECT  employee.employeename

FROM  employee  LEFT JOIN  department

 ON  employee.departmentid=department.departmentid

         LEFT  JOIN  sell_order

     ON  employee.employeeid=sell_order.employeeid

WHERE   sell_order.sellorderid  IS  NULL

        AND  department.departmentname='销售部';

5、查询人事部所有员工信息。

SELECT employee.employeeid, employeename, salary, department.departmentname

FROM  employee,department 

WHERE  employee.departmentid=department.departmentid 

AND  departmentname='人事部';

6、查询已经接收“打印纸”订单的员工的姓名和订购数量。

SELECT employeename,productname,sellordernumber

FROM  employee,sell_order,product

WHERE employee.employeeid=sell_order.employeeid  

       AND product.productid=sell_order.productid 

       AND  productname='打印纸';