一、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
