mysql基础学习（一）

新建2张空的表格

CREATE TABLE `job` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `deptName` varchar(50) DEFAULT NULL,

  `post` varchar(50) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE `message` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(12) DEFAULT NULL,

  `phone` varchar(12) DEFAULT NULL,

  `address` varchar(255) DEFAULT NULL,

  `jobId` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

执行插入语句

INSERT INTO message (NAME,phone,address,jobId) VALUES('Jam','12345534450','土星',1);

INSERT INTO message (NAME,phone,address,jobId) VALUES('Tom','12345543210','火星',1);

INSERT INTO message (NAME,phone,address,jobId) VALUES('Mary','12345554321','地球',2);

INSERT INTO job(deptName,post) VALUES('太空部','宇航员');

INSERT INTO job(deptName,post) VALUES('太空部','教授');

那么message表就有3条数据，job表有2条数据

执行查询语句

SELECT * FROM message;#结果1

SELECT * FROM job;#结果2

SELECT NAME,phone FROM message;#结果3

SELECT post FROM job;#结果4

SELECT NAME,post FROM message ,job

where message.jobId=job.id#结果5

就能把所需要的查询结果给查询出来

执行sql语句：UPDATE message SET address='水星' WHERE NAME='Jam'

就可以把土星改成水星了

执行sql语句：DELETE FROM message WHERE NAME='Mary'

就把第三条数据给删除了

left join 左外连接查找左表所以的数据及右表满足条件的数据，不满足的null补足

right join 右外连接查询右表所以的数据及左表满足条件的数据，不满足的null补足

inner join 内连接查询左右2表都同时满足的数据

SELECT message.*,job.* FROM message LEFT JOIN job ON message.jobId=job.id;#结果1

SELECT message.*,job.* FROM message RIGHT JOIN job ON message.jobId=job.id;#结果2

SELECT message.*,job.* FROM message INNER JOIN job ON message.jobId=job.id;#结果3