编辑代码

CREATE DATABASE test;
use test;
CREATE TABLE student (
	id int,
	name varchar(255),
	address varchar(255),
	city varchar(255),
    sal double,
    tid int
);

CREATE TABLE teacher (
	id int(11),
	name varchar(255),
    sal double
);

INSERT INTO student VALUES (1, '刘一', '郑十家隔壁', '河南', 123.1,1);
INSERT INTO student VALUES (2, '陈二',  '李四家隔壁', '安徽',12,2);
INSERT INTO student VALUES (3, '张三',  '白娘子家隔壁', '杭州',124,3);
INSERT INTO student VALUES (4, '李四',  '许仙家隔壁', '杭州',1,3);
INSERT INTO student VALUES (5, '王五',  '李四家隔壁', '杭州',3,3);
INSERT INTO student VALUES (6, '赵六',  '赵六家隔壁', '杭州',124,4);
INSERT INTO student VALUES (7, '孙七',  '张三家隔壁隔壁', '杭州',123.33,3);
INSERT INTO student VALUES (8, '周八',  '雷峰塔附近', '杭州',1253,3);
INSERT INTO student VALUES (9, '吴九',  '孙七家隔壁', '杭州',null,5);
INSERT INTO student VALUES (10, '郑十',  '周八家隔壁', '杭州',12.13,3);
 

INSERT INTO teacher VALUES (1,'策划一',123.1);
INSERT INTO teacher VALUES (2,'法撒旦二',12);
INSERT INTO teacher VALUES (3,'发动反攻',124);
INSERT INTO teacher VALUES (4,'过分',1);
INSERT INTO teacher VALUES (5,'华国锋',3);
INSERT INTO teacher VALUES (6,'国锋',1555);


update student set address = null where id = 2;
-- select * from student limit 2,4;
-- select id, name, address, city, sal from student;
-- select * from student where city <> '河南';
-- select * from student where address is null;
-- select * from student where address is not null;
-- select * from student where sal > 0 and sal <= 100;
-- select * from student where sal between 0 and 100;
-- select * from student where sal < 5 or sal is null;
-- select * from student order by sal asc;
-- select * from student order by sal desc;
-- select * from student where address like '%隔壁%';

-- select * from student where address like '_四%';
-- select * from student where city in ('杭州','安徽');
-- select avg(sal) avg ,city from student where sal is not null group by city having avg > 5;

-- select * from student s, teacher t where s.tid = t.id and s.sal > 100;

-- select * from student s join teacher t
-- on s.tid = t.id
-- where s.sal > 100;

select * from student s left join teacher t
on s.tid = t.id


-- select * from student s right join teacher t
-- on s.tid = t.id