CREATE TABLE C ( CNO CHAR(10), CNAME CHAR(10) UNIQUE, CCREDIT CHAR(10), CT CHAR(10), CPNO CHAR(10), PRIMARY KEY NONCLUSTERED(CNO), FOREIGN KEY(CPNO)REFERENCES C(CNO) ON DELETE NO ACTION ON UPDATE CASCADE )
时间: 2023-11-11 08:07:45 浏览: 88
这是一个SQL语句,用于创建一个名为C的表。其中包含CNO、CNAME、CCREDIT、CT、CPNO这五个字段。其中CNAME是唯一的,CNO是主键,CPNO是外键,关联到C表的CNO字段。ON DELETE NO ACTION表示在删除关联的记录时不做任何操作,ON UPDATE CASCADE表示在更新关联的记录时级联更新。
相关问题
将以下代码给为mysql代码:create table student(sno char(5) primary key, sname varchar(20), sage int, ssex char(2), sdept char(15)); create table course(cno char(3) primary key, cname varchar(20), cpno char(3), ccredit int, foreign key(cpno) references course); create table sc( sno char(5), cno char(3), grade int, primary key(sno,cno), foreign key(sno) references student, foreign key(cno) references course, check(grade>=0 and grade<=100));
以下是对应的 MySQL 代码:
```
-- Connect to the database
mysql -h hostname -u username -p password
-- Create the student table
CREATE TABLE student (
sno CHAR(5) PRIMARY KEY,
sname VARCHAR(20),
sage INT,
ssex CHAR(2),
sdept CHAR(15)
);
-- Create the course table
CREATE TABLE course (
cno CHAR(3) PRIMARY KEY,
cname VARCHAR(20),
cpno CHAR(3),
ccredit INT,
FOREIGN KEY (cpno) REFERENCES course (cno)
);
-- Create the sc table
CREATE TABLE sc (
sno CHAR(5),
cno CHAR(3),
grade INT,
PRIMARY KEY (sno, cno),
FOREIGN KEY (sno) REFERENCES student (sno),
FOREIGN KEY (cno) REFERENCES course (cno),
CHECK (grade >= 0 AND grade <= 100)
);
-- Close the connection
QUIT;
```
需要注意的是,在 MySQL 中,`CHECK` 约束不能使用函数,只能使用常量表达式。因此,`CHECK(grade >= 0 AND grade <= 100)` 不能写成 `CHECK(BETWEEN grade AND 0 AND 100)`。
CREATE DATABASE if NOT EXISTS xskc CHARSET UTF8 COLLATE UTF8_GENERAL_CI; use xskc; CREATE TABLE STUDENT ( Sno char(9)primary key, Sname char(20), Ssex char(2)check(Ssex in('男','女')) , Sage smallint check(Sage>=10 and Sage<=60), Sdept char(20) ); insert into STUDENT(Sno,Sname,Ssex,Sage,Sdept)values(201215121,'李勇','男',20,'CS'); insert into STUDENT(Sno,Sname,Ssex,Sage,Sdept)values(201215122,'刘晨','女',19,'IS'); insert into STUDENT(Sno,Sname,Ssex,Sage,Sdept)values(201215123,'王敏','男',18,'MA'); insert into STUDENT(Sno,Sname,Ssex,Sage,Sdept)values(201215124,'张立','男',19,'CS'); select * from STUDENT; CREATE TABLE COURSE ( Cno char(4)primary key, Cname char(40), Cpno char(4), foreign key(Cpno)references COURSE(Cno), Ccredit smallint ); insert into COURSE(Cno,Cname,Cpno,Ccredit)values(1,'数据库',NULL,4); insert into COURSE(Cno,Cname,Cpno,Ccredit)values(2,'数学',NULL,2); insert into COURSE(Cno,Cname,Cpno,Ccredit)values(3,'信息系统',1,4); insert into COURSE(Cno,Cname,Cpno,Ccredit)values(4,'操作系统',2,3); insert into COURSE(Cno,Cname,Cpno,Ccredit)values(5,'数据结构',3,4); insert into COURSE(Cno,Cname,Cpno,Ccredit)values(6,'数据分析',NULL,4); insert into COURSE(Cno,Cname,Cpno,Ccredit)values(7,'PASCAL语言',6,4); select * from COURSE; CREATE TABLE SC ( Sno char(9), Cno char(4), Grade smallint check(Grade>=10 and Grade<=100), primary key(Sno,Cno), foreign key(Sno) references STUDENT(Sno), foreign key(Cno) references COURSE(Cno) ); insert into SC(Sno,Cno,Grade)values(201215121,1,92); insert into SC(Sno,Cno,Grade)values(201215121,2,85); insert into SC(Sno,Cno,Grade)values(201215121,3,88); insert into SC(Sno,Cno,Grade)values(201215122,2,90); insert into SC(Sno,Cno,Grade)values(201215122,3,80);
好的,我看到您创建了三张表:`STUDENT`、`COURSE` 和 `SC`,并向它们插入了一些数据。表 `STUDENT` 存储了学生的基本信息,表 `COURSE` 存储了课程的信息,表 `SC` 存储了学生选课的成绩信息。接下来,您可以使用 SQL 语句来查询学生的成绩信息,如下:
1. 查询学生 `201215121` 的选课情况和成绩:
```
SELECT c.Cname, sc.Grade
FROM SC sc
JOIN COURSE c ON sc.Cno = c.Cno
WHERE sc.Sno = 201215121;
```
执行该 SQL 语句后,将会返回学号为 `201215121` 的学生选课的课程名称和成绩信息。
2. 查询课程 `数据库` 的选课学生情况和成绩:
```
SELECT s.Sname, sc.Grade
FROM SC sc
JOIN STUDENT s ON sc.Sno = s.Sno
JOIN COURSE c ON sc.Cno = c.Cno
WHERE c.Cname = '数据库';
```
执行该 SQL 语句后,将会返回选修了 `数据库` 课程的学生的姓名和成绩信息。
3. 查询所有学生的选课情况和成绩:
```
SELECT s.Sno, s.Sname, c.Cname, sc.Grade
FROM SC sc
JOIN STUDENT s ON sc.Sno = s.Sno
JOIN COURSE c ON sc.Cno = c.Cno;
```
执行该 SQL 语句后,将会返回所有学生的选课情况和成绩信息。
阅读全文