mysql 右连接5.1_Mysql增删改查、内连接、左连接、右连接

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