1018sql语言

    1. CREATE TABLE student
    2. (
    3.     sno char(20) primary key,
    4.     sname char(8),
    5.     ssex char(2),
    6.     sage int,
    7.     sdept char(2)
    8. );
    10. create table course
    11. (
    12.     cno int primary key,
    13.     cname char(30),
    14.     cpno int,
    15.     ccredit int
    16.     foreign key (cpno) references course(cno)
    17. );
    19. create table sc
    20. (
    21.     sno char(20),
    22.     cno int,
    23.     grade int,
    24.     primary key(sno,cno),
    25.     foreign key(sno) references student(sno),
    26.     foreign key(cno) references course(cno),
    27. );
    2. insert into student values ('201215121','李勇','男',20,'CS');
    3. insert into student values ('201215122','刘晨','女',19,'CS');
    4. insert into student values ('201215123','王敏','女',18,'MA');
    5. insert into student values ('201215125','张立','男',19,'IS');
    8. insert into sc values ('201215121', 1, 92);
    9. insert into sc values ('201213121', 2, 85);
    10. insert into sc values ('201216121', 3, 88);
    11. insert into sc values ('201215122', 2, 90);
    12. insert into sc values ('201214122', 3, 80);
    15. insert into course values (1,'数据库',5,4);
    16. insert into course values (2,'数学',null,4);
    17. insert into course values (3,'信息系统',1,4);
    18. insert into course values (4,'操作系统',6,3);
    19. insert into course values (5,'数据结构',7,4);
    20. insert into course values (6,'数据处理',null,2);
    21. insert into course values (7,'PASCAL语言',6,4);
    2. insert into student 
    3. (sno, sname, ssex,sage,sdept,TELE)
    4. values
    5. ('201215121','李勇','男',20,'CS','123456789');
    6. select * from student;
    1. alter table student add TELE varchar(20);
    2. /*
    3. (2)为student表以sdept建立一个名为sdeptin的索引；
    5. (3)为course表以cname建立一个名为cnamein的唯一索引；
    6. */
    7. create unique index sdeptin on student(sdept);
    9. create unique index cnamein on course(cname);
    1. select * from student;
    2. select * from sc;
    3. select * from course;
    4. select * from S_table;
    5. select * from P_table;
    6. select * from J_table;
    7. select * from SPJ;
    1. create table spj(
    2.     sno varchar(10),
    3.     pno varchar(10),
    4.     jno varchar(10),
    5. );
    1. create table S_table(
    2.     sno varchar(4) primary key,
    3.     sname varchar(10),
    4.     status int,
    5.     city varchar(10),
    6. );
    8. create table P_table(
    9.     pno varchar(4) primary key,
    10.     pname varchar(10),
    11.     color varchar(4),
    12.     weight int,
    13. );
    15. create table J_table(
    16.     jno varchar(20) primary key,
    17.     jname varchar(20),
    18.     city varchar(10),
    19. );