sql基础语句01

sql基础语句笔记
1、将表中某一字段的最大值取出来的方式：

SELECT MAX(contract_no) FROM contract_c WHERE contract_no LIKE '%JK%'

2、在查看合同(contract_c)时，查看合同对应的货物(contract_product_c)数量和货物对应附件(ext_cproduct_c)的数量。
合同表的ID是货物表的外键，货物ID是附件的外键。是两层一对多的关系。

SELECT contract_no,(    SELECT COUNT(*) FROM contract_product_c    WHERE contract_id = c.CONTRACT_ID)cpnumFROM contract_c c WHERE contract_id='4028817a3357462e01336d3a0be10014'

SELECT COUNT(*) FROM  ext_cproduct_c WHERE contract_product_id IN(    SELECT contract_product_id FROM contract_product_c     WHERE contract_id ='4028817a3357462e01336d3a0be10014')

SELECT(    SELECT COUNT(*) FROM contract_product_c WHERE contract_id = c.contract_id) cpnum, (    SELECT COUNT(*) FROM ext_cproduct_c WHERE contract_product_id IN(        SELECT contract_product_id WHERE contract_id = c.contract_id    ) )epnum,c.*FROM contract_c c

3、查询合同(contract_c)对应的货物(contract_product_c)的数量(cnumber)和价格（price）的乘积

SELECT      c.contract_no,    cp.cpnum,    cp.cptotalFROM(SELECT * FROM contract_c)cLEFT JOIN(SELECT contract_id,COUNT(*) AS cpnum,SUM(cnumber*price) AS cptotal FROM contract_product_cGROUP BY contract_id)cpON c.contract_id = cp.contract_id

4、

SELECT c.contract_no,    t.factory_name,t.product_no,    t.ext_factory_name,t.ext_product_no    FROM(SELECT contract_id,contract_no FROM contract_c)cLEFT JOIN    (SELECT cp.contract_id,        cp.factory_name,cp.product_no,        ep.factory_name AS ext_factory_name,ep.product_no AS ext_product_no    FROM        (        SELECT             cp.contract_product_id,cp.contract_id,f.factory_name,cp.product_no            FROM        (SELECT contract_product_id,contract_id,factory_id,product_no FROM contract_product_c)cp        LEFT JOIN         (SELECT factory_id,factory_name FROM factory_c)f            ON cp.factory_id = f.factory_id        )cp        LEFT JOIN        (        SELECT ep.ext_cproduct_id,ep.contract_product_id,ep.product_no,f.factory_name        FROM        (SELECT ext_cproduct_id,factory_id,contract_product_id,product_no FROM ext_cproduct_c)ep        LEFT JOIN        (SELECT factory_id,factory_name FROM factory_c)f        ON ep.factory_id = f.factory_id        )ep    ON cp.contract_product_id = ep.ext_cproduct_id)t    ON c.contract_id = t.contract_id

SELECT c.*,    t.factory_name,t.product_no,    t.ext_factory_name,t.ext_product_no    FROM(SELECT * FROM contract_c)cLEFT JOIN    (SELECT cp.contract_id,        cp.factory_name,cp.product_no,        ep.factory_name AS ext_factory_name,ep.product_no AS ext_product_no    FROM        (        SELECT             cp.contract_product_id,cp.contract_id,f.factory_name,cp.product_no            FROM        (SELECT contract_product_id,contract_id,factory_id,product_no FROM contract_product_c)cp        LEFT JOIN         (SELECT factory_id,factory_name FROM factory_c)f            ON cp.factory_id = f.factory_id        )cp        LEFT JOIN        (        SELECT ep.ext_cproduct_id,ep.contract_product_id,ep.product_no,f.factory_name        FROM        (SELECT ext_cproduct_id,factory_id,contract_product_id,product_no FROM ext_cproduct_c)ep        LEFT JOIN        (SELECT factory_id,factory_name FROM factory_c)f        ON ep.factory_id = f.factory_id        )ep    ON cp.contract_product_id = ep.ext_cproduct_id)t    ON c.contract_id = t.contract_id

SELECT    c.*,    t.contract_product_id,t.factory_name,t.product_no,t.product_desc,t.cnumber,t.packing_unit,t.price,t.amount,    t.ext_cproduct_id,t.ext_factory_name,t.ext_product_no,t.ext_product_desc,t.ext_cnumber,t.ext_packing_unit,t.ext_price,t.ext_amountFROM(SELECT * FROM contract_c) cLEFT JOIN(    SELECT        cp.contract_id,        cp.contract_product_id,cp.factory_name,cp.product_no,cp.product_desc,cp.cnumber,cp.packing_unit,cp.price,cp.amount,        ep.ext_cproduct_id,ep.factory_name AS ext_factory_name,ep.product_no AS ext_product_no,ep.product_desc AS ext_product_desc,ep.cnumber AS ext_cnumber,ep.packing_unit AS ext_packing_unit,ep.price AS ext_price,ep.amount AS ext_amount    FROM    (        SELECT             cp.contract_product_id,cp.contract_id,f.factory_name,cp.product_no,            cp.product_desc,cp.cnumber,cp.packing_unit,cp.price,cp.amount        FROM        (SELECT contract_product_id,contract_id,factory_id,product_no, product_desc,cnumber,packing_unit, price,amount FROM contract_product_c) cp        LEFT JOIN         (SELECT factory_id,factory_name FROM factory_c) f        ON cp.factory_id = f.factory_id    ) cp    LEFT JOIN    (        SELECT             ep.ext_cproduct_id,ep.contract_product_id,f.factory_name,ep.product_no,            ep.product_desc,ep.cnumber,ep.packing_unit,ep.price,ep.amount        FROM        (SELECT ext_cproduct_id,contract_product_id,factory_id,product_no, product_desc,cnumber,packing_unit, price,amount FROM ext_cproduct_c) ep        LEFT JOIN        (SELECT factory_id,factory_name FROM factory_c) f        ON ep.factory_id=f.factory_id    ) ep    ON cp.contract_product_id=ep.contract_product_id) t ON c.contract_id=t.contract_id

5、查询出每门课都大于80分的学生姓名,

selec distinct name from scores where name not in(    select distinct name from scores where score<=80    )

6、每个月发生额比101科目多的科目

CREATE TABLE TestDB(id INT PRIMARY KEY AUTO_INCREMENT, AccID VARCHAR(20), Occmonth DATE,DebitOccur INT);INSERT INTO TestDB VALUES(NULL,'101','1997-1-1',100),(NULL,'101','1997-2-1',120),(NULL,'101','1997-3-1',140),(NULL,'101','1997-4-1',110);INSERT INTO TestDB VALUES(NULL,'102','1997-1-1',90),(NULL,'102','1997-2-1',120),(NULL,'102','1997-3-1',140),(NULL,'102','1997-4-1',110);INSERT INTO TestDB VALUES(NULL,'103','1997-1-1',130),(NULL,'103','1997-2-1',160),(NULL,'103','1997-3-1',170),(NULL,'103','1997-4-1',190);SELECT DISTINCT AccID FROM TestDBWHERE AccID NOT IN(SELECT TestDB.AccID FROM TestDB, (SELECT * FROM TestDB  WHERE accID = '101')AS db101 WHERE TestDB.Occmonth = db101.Occmonth AND TestDB.DebitOccur<= db101.DebitOccur );

7、统计每年每月的信息

 CREATE TABLE sales(id INT AUTO_INCREMENT PRIMARY KEY, years VARCHAR(10), months VARCHAR(10), amount FLOAT(2,1)); INSERT INTO sales VALUES (NULL,'1991','1',1.1),  (NULL,'1991','2',1.2),  (NULL,'1992','3',1.3), (NULL,'1992','4',1.4); SELECT sales.years, (SELECT t.amount FROM sales t WHERE t.months='1' AND t.years = sales.years)AS '1',(SELECT t.amount FROM sales t WHERE t.months='2' AND t.years = sales.years)AS '2',(SELECT t.amount FROM sales t WHERE t.months='3' AND t.years = sales.years)AS '3',(SELECT t.amount FROM sales t WHERE t.months='4' AND t.years = sales.years)AS '4'FROM sales GROUP BY years;