sql

-- 创建表
CREATE TABLE Student(
    sid VARCHAR(20),
    sname VARCHAR(50),
    cid VARCHAR(20),
    PRIMARY KEY(sid)
);

CREATE TABLE Course(
    cid VARCHAR(20),
    cname VARCHAR(50),
    PRIMARY KEY(cid)
);
select * from Student
INSERT INTO Student values('S001','张三','C100');
INSERT INTO Student values('S002','李四','C200');
INSERT INTO Student values('S003','王五','C300');

-- C000是Course表中没有的
INSERT INTO Student values('S004','赵六','C000');
select * from Course
INSERT INTO Course values('C100','操作系统');
INSERT INTO Course values('C200','编译原理');
INSERT INTO Course values('C300','网络安全');

-- C400是Student表中没有引用的
INSERT INTO Course values('C400','软件技术');

--
SELECT count(t.rowid) FROM Student t,Course t1;
select count(cid)as cid from student ,course;
select * from course;
-- 自然连接:NATURAL JOIN
--SELECT * FROM Student  JOIN Course;
SELECT * FROM Student NATURAL JOIN Course;
SELECT * FROM Course NATURAL JOIN  Student;

-- 内连接:INNER JOIN
SELECT * FROM Student s INNER JOIN Course c ON s.cid=c.cid;
SELECT * FROM Course c INNER JOIN  Student s ON c.cid=s.cid;

-- 与使用INNER JOIN结果类似，但包含重复列(cid)
SELECT * FROM Student s , Course c WHERE s.cid=c.cid;

-- 右外连接: RIGHT OUTER JOIN
SELECT * FROM Student s RIGHT JOIN Course c ON s.cid=c.cid;
SELECT * FROM Course c RIGHT JOIN Student s ON s.cid=c.cid;

SELECT * FROM Student s left JOIN Course c ON s.cid=c.cid;
SELECT * FROM Course c left JOIN Student s ON s.cid=c.cid;

-- FULL OUTER JOIN:用左右连接实现
SELECT * FROM Student  s LEFT JOIN Course  c ON s.cid=c.cid
UNION
SELECT * FROM Student s RIGHT JOIN Course c ON s.cid=c.cid;

SELECT * FROM Course  c LEFT JOIN Student  s ON s.cid=c.cid
UNION
SELECT * FROM Student s RIGHT JOIN Course c ON s.cid=c.cid;


SELECT * FROM Student s RIGHT JOIN Course c ON s.cid=c.cid;
UNION
SELECT * FROM Student  s LEFT JOIN Course  c ON s.cid=c.cid
--
insert into t_demo02(t_id02,t_pingpai02)
select  t_id,t_pingpai from t_demo

insert into t_demo02
select * from t_demo

select * from t_demo02

insert into tab_new select * from tab_old;或者SELECT vale1, value2 into Table2 from Table1
--

-- 交叉连接:CROSS JOIN
-- 与SELECT * FROM Student
SELECT * FROM Student CROSS JOIN Course;