创建两个简单表A,B,两表中有相关联的列,并在关联列上建立索引
1.建立A,B表
SQL> CREATE TABLE A (
A_id NUMBER(10) PRIMARY KEY,
A_name VARCHAR2(12) NOT NULL ,
A_age NUMBER(10) NOT NULL);
SQL> CREATE TABLE B (
B_id NUMBER(10) NOT NULL PRIMARY KEY,
B_name VARCHAR(12) NOT NULL,
B_score NUMBER(3) NOT NULL
);
2.分别在A,B表中插入数据
INSERT INTO A (A_id, A_name, A_age)
VALUES (101, 'zhangsan', 18);
INSERT INTO A (A_id, A_name, A_age)
VALUES (102, 'lisi', 19);
INSERT INTO A (A_id, A_name, A_age)
VALUES (103, 'wangwu', 20);
INSERT INTO B (B_id, B_name, B_score)
VALUES (101, 'java', 95);
INSERT INTO B (B_id, B_name, B_score)
VALUES (102, 'python', 97);
INSERT INTO B (B_id, B_name, B_score)
VALUES (103, 'python', 88);
3.查看A,B表视图
4.建立关联索引
#查询选择了python课程的学生信息和成绩
select B_score,B_name,B_id,A_name,A_age from A,B where A_id=B_id and B_name='python';