创建两个简单表A,B,两表中有相关联的列，并在关联列上建立索引

创建两个简单表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';