创建数据库、创建数据表(包括主外键约束以及用户自定义约束)、创建视图、等对象的SQL代码语句
时间: 2023-08-06 21:07:03 浏览: 103
以下是一个简单的示例,包括创建数据库、创建数据表、创建主外键约束、用户自定义约束和创建视图的 SQL 代码语句:
1. 创建数据库
```
CREATE DATABASE mydatabase;
```
2. 创建数据表
```
CREATE TABLE Students (
StudentID int PRIMARY KEY,
FirstName varchar(255) NOT NULL,
LastName varchar(255) NOT NULL,
Gender varchar(10) NOT NULL,
BirthDate date NOT NULL,
Email varchar(255) NOT NULL UNIQUE
);
CREATE TABLE Courses (
CourseID int PRIMARY KEY,
CourseName varchar(255) NOT NULL,
Credits int NOT NULL
);
```
3. 创建主外键约束
```
ALTER TABLE Enrollments
ADD CONSTRAINT PK_Enrollments PRIMARY KEY (StudentID, CourseID);
ALTER TABLE Enrollments
ADD CONSTRAINT FK_Enrollments_Students FOREIGN KEY (StudentID)
REFERENCES Students(StudentID);
ALTER TABLE Enrollments
ADD CONSTRAINT FK_Enrollments_Courses FOREIGN KEY (CourseID)
REFERENCES Courses(CourseID);
```
4. 创建用户自定义约束
```
ALTER TABLE Students
ADD CONSTRAINT CHK_Students_Gender CHECK (Gender IN ('M', 'F'));
ALTER TABLE Courses
ADD CONSTRAINT CHK_Courses_Credits CHECK (Credits >= 1 AND Credits <= 5);
```
5. 创建视图
```
CREATE VIEW StudentCourses AS
SELECT Students.FirstName, Students.LastName, Courses.CourseName, Courses.Credits
FROM Students
INNER JOIN Enrollments ON Students.StudentID = Enrollments.StudentID
INNER JOIN Courses ON Enrollments.CourseID = Courses.CourseID;
```
以上是一个简单的示例,根据具体情况,您需要根据具体需求进行适当的修改。
阅读全文