mysql 链接查询

怼烎@ 2022-06-08 10:20 260阅读 0赞

use aaa;  
CREATE table IF NOT EXISTS son(  
sonId int UNSIGNED NOT NULL AUTO\_INCREMENT,  
SNAME VARCHAR(10),  
SAGE TINYINT,  
SGENDER ENUM('男','女','保密'),  
SADDRESS VARCHAR(100),  
SSCHOOL VARCHAR(50),  
SCLASS VARCHAR(20),  
PID INT UNSIGNED,  
PRIMARY KEY(SONID)  
)ENGINE=MYISAM AUTO\_INCREMENT=1 DEFAULT CHARSET=UTF8;  
  
  
INSERT INTO SON VALUES(NULL,'张三',11,'女','沈阳市','沈阳市第一中学','五年一班',1),  
(NULL,'张三',12,'女','沈阳市','沈阳市第一中学','五年二班',2),  
(NULL,'李四',10,'男','鞍山市','鞍山市第一中学','五年三班',3),  
(NULL,'王五',13,'女','沈阳市','沈阳市第一中学','五年四班',4);  
  
  
SELECT \* FROM SON;  
  
  
CREATE table IF NOT EXISTS PARENTS(  
PID INT UNSIGNED NOT NULL AUTO\_INCREMENT,  
PNAME VARCHAR(10),  
PAGE TINYINT,  
PGENDER ENUM('男','女','保密'),  
PADDRESS VARCHAR(100),  
IDCARD VARCHAR(18),  
PBIR DATE,  
PRIMARY KEY(PID)  
)ENGINE=MYISAM AUTO\_INCREMENT=1 DEFAULT CHARSET=UTF8;  
  
  
INSERT INTO PARENTS VALUES(NULL,'张三家长',45,'男','沈阳市','210381199002025317','1990-02-02');  
INSERT INTO PARENTS VALUES(NULL,'李四家长',45,'男','鞍山市','','1990-02-02');  
INSERT INTO PARENTS VALUES(NULL,'王五家长',45,'女','沈阳市','210381199412245317','1988-02-02');  
INSERT INTO PARENTS VALUES(NULL,'',45,'女','沈阳市','210381199412245317','1988-02-02');  
INSERT INTO PARENTS VALUES(NULL,'',55,'女','沈阳市','210381199412245317','1988-02-02');  
  
  
SELECT \* FROM PARENTS;  
DROP TABLE SON;  
  
  
\-- 多表链接查询  
\-- 查询两张表中共有的数据  
SELECT   
SON.SONID,PARENTS.PID,SON.SNAME,PARENTS.PNAME  
FROM SON,PARENTS  
WHERE SON.PID=PARENTS.PID;  
  
  
\-- 链接查询 JOIN ， LEFT JOIN   RIGHT JOIN  
SELECT   
PARENTS.PID,SON.SNAME,PARENTS.PNAME  
FROM SON LEFT JOIN PARENTS ON SON.PID = PARENTS.PID;  
  
  
\-- 员工表  
CREATE table job(  
JID INT UNSIGNED NOT NULL AUTO\_INCREMENT PRIMARY KEY,  
JNAME VARCHAR(20),  
JGENDER ENUM('男','女','保密'),  
JPJID INT  
)ENGINE=MYISAM AUTO\_INCREMENT=1 DEFAULT CHARSET=UTF8;  
  
  
DESC JOB;  
insert into job VALUES(NULL,'张三','男',NULL);  
insert into job VALUES(NULL,'李四','男',1);  
insert into job VALUES(NULL,'王五','女',1);  
insert into job VALUES(NULL,'赵六','男',3);  
  
  
SELECT \* FROM JOB;  
\-- 查询出每个员工的编号姓名和领导的名字  
  
  
\-- 自链接  
SELECT  
J1.JID,J1.JNAME,J2.JNAME  
FROM JOB J1,JOB J2  
WHERE J1.JPJID = J2.JID;