CREATE TABLE t(

tid INT UNSIGNED NOT NULL AUTO_INCREMENT,

tname VARCHAR(30),

PRIMARY KEY (tid)

)ENGINE=MYISAM AUTO_INCREMENT=1 DEFAULT CHARSET =utf8;

INSERT INTO t VALUES(NULL,'李老师'),(NULL,'赵老师'),(NULL,'张老师');

SELECT * FROM t;

CREATE TABLE s(

sid INT UNSIGNED NOT NULL AUTO_INCREMENT,

sname VARCHAR(10)NOT NULL,

sgender ENUM ('男','女','人妖'),

sdept VARCHAR(20) NOT NULL DEFAULT '计算机科学',

sscore INT UNSIGNED,

tid INT UNSIGNED,

PRIMARY KEY(sid)

)ENGINE=MYISAM AUTO_INCREMENT=1601 DEFAULT CHARSET =utf8;

INSERT INTO s VALUES(NULL,'张三','男','美术',80,1),(NULL,'张三丰','人妖','英语',45,2);

INSERT INTO s VALUES(NULL,'李丽','女','美术',90,3),(NULL,'黄瑶','女','财经',99,3);

INSERT INTO s(sname,sgender,sscore) VALUES('赵磊','男',50),('周磊','人妖',68);

DROP TABLE job;

SELECT * FROM s;

-- 多表查询(不推荐使用)

SELECT s.`sid`,s.`sname`,t.`tname`

FROM s,t

WHERE s.tid = t.tid;

-- 连接查询 join left join right join

SELECT s.`sid` 学号,s.`sname` 姓名,s.`sdept` 专业,IF(t.`tname` IS NULL,'没安排老师',t.`tname`) 老师

FROM s LEFT JOIN t ON s.`tid`=t.`tid`;

 

CREATE TABLE job(

jid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

jname VARCHAR(20) NOT NULL,

jgender ENUM('男','女'),

jpjid INT

)ENGINE=MYISAM AUTO_INCREMENT=1 DEFAULT CHARSET =utf8;

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;

DESC job;

-- 查询每个员工的编号姓名及领导的名字

-- 自连接查询

SELECT j1.`jid` 编号,j1.`jname` 姓名,j2.`jname` 领导姓名

FROM job j1,job j2

WHERE j1.jpjid=j2.jid

SELECT j1.`jid` 编号,j1.`jname` 姓名,IF(j2.`jname` IS NULL,'总经理',j2.`jname`) 领导姓名

FROM job j1 LEFT JOIN job j2

ON j1.jpjid=j2.jid