mysql基础学习（一）

1、新建2张空的表格CREATE TA幞洼踉残BLE `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;

2、执行插入语句INSERT INTO message (NAME,phone,ad蟠校盯昂dress,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条数据

3、执行查询语句SELECT * FROM message;#结果1SELECT * FROM job;#结果2SELECT NAME,phone FROM message;#结果3SELECT post FROM job;#结果4SELECT NAME,post FROM message ,jobwhere message.jobId=job.id#结果5就能把所需要的查询结果给查询出来

4、执行sql语句：UPDATE message SET address='水星' WHERE NAME='Jam'就可以把土星改成水星了

5、执行sql语句：DELETE FROM message WHERE NAME='Mary'就把第三条数据给删除了

6、left join 左外连接查找左表所以的数据及右表满足条件的数据，不满足的null补足right join 右外连接查询右表所以的数据及左表满足条件的数据，不满足的null补足inner join 内连接查询左右2表都同时满足的数据SELECT message.*,job.* FROM message LEFT JOIN job ON message.jobId=job.id;#结果1SELECT message.*,job.* FROM message RIGHT JOIN job ON message.jobId=job.id;#结果2SELECT message.*,job.* FROM message INNER JOIN job ON message.jobId=job.id;#结果3