mysql 链接查询

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;