一、sql增删改查、内连接、左连接、右连接

1、增：

INSERT INTO students(sid, sname, ssex, sage, sadress, sphone) VALUES (1,"aaa","男",50,"bbb","120")

2、删：

delete from students where sid=1;

3、改：

update students set sname="黎明",sage=20,sadress="广东虎门",sphone="114" where sid=2

4、查：

select * from students

5、左连接

SELECT
    MAX(v.number) AS number,
    SUM(v.casenum)AS casenum,
    SUM(v.casesum) AS casesum,
    MAX(d.name) AS name,
    MAX(d.unit) AS unit,
    MAX(d.csstype) AS csstype
FROM
    (SELECT id,name,unit,csstype FROM t_ca_case_detail_table ) AS d

LEFT JOIN (SELECT number,casenum,casesum FROM t_ca_case_value WHERE  month >= "202002" AND month <= "202007" AND xzqh in("4401") ) AS v 

ON v.number = d.id

GROUP BY d.id

ORDER BY d.id ASC

结果：

image.png

6、右连接

SELECT
    MAX(d.name) AS name,
    MAX(d.unit) AS unit,
    MAX(d.csstype) AS csstype,
    MAX(v.number) AS number,
    SUM(v.casenum)AS casenum,
    SUM(v.casesum) AS casesum
FROM
    (SELECT id,name,unit,csstype FROM t_ca_case_detail_table ) AS d

RIGHT JOIN (SELECT number,casenum,casesum FROM t_ca_case_value WHERE  month >= "202002" AND month <= "202007" AND xzqh in("4400") ) AS v 

ON v.number = d.id

GROUP BY d.id

ORDER BY d.id ASC

结果：

image.png

6、内连接

SELECT
    MAX(d.name) AS name,
    MAX(d.unit) AS unit,
    MAX(d.csstype) AS csstype,
    MAX(v.number) AS number,
    SUM(v.casenum)AS casenum,
    SUM(v.casesum) AS casesum
FROM
    (SELECT id,name,unit,csstype FROM t_ca_case_detail_table ) AS d

INNER JOIN (SELECT number,casenum,casesum FROM t_ca_case_value WHERE  month >= "202002" AND month <= "202007" AND xzqh in("4400") ) AS v 

ON v.number = d.id

GROUP BY d.id

ORDER BY d.id ASC

结果：

image.png