你的位置:首页 > 数据库

[数据库]SQL查询语言练习

USE master GO IF EXISTS (SELECT * FROM sysdatabases WHERE name='MyStudentInfoManage')DROP DATABASE MyStudentInfoManage     --删除数据库GO CREATE DATABASE MyStudentInfoManage    --创建数据库GO USE MyStudentInfoManageGO --学生表IF EXISTS(SELECT * FROM sysobjects WHERE name='Student')DROP TABLE Student    --删除表GO CREATE TABLE Student(  -- Student(S#,Sname,Sage,Ssex) 学生表   S# INT NOT NULL PRIMARY KEY ,   --学号   Sname NVARCHAR(20) NOT NULL,   --姓名   Sage INT NOT NULL ,        --年龄   Ssex CHAR(2) NOT NULL       --性别               );--教师表IF EXISTS(SELECT * FROM sysobjects WHERE name='Teacher')DROP TABLE TeacherGO CREATE TABLE Teacher( --Teacher(T#,Tname) 教师表 T# INT NOT NULL PRIMARY KEY, Tname NVARCHAR(20) NOT NULL );--课程表IF EXISTS(SELECT * FROM sysobjects WHERE name='Course')DROP TABLE CourseGO CREATE TABLE Course(  --Course(C#,Cname,T#) 课程表  C# INT NOT NULL PRIMARY KEY ,  Cname NVARCHAR(20) NOT NULL ,  T# INT NOT NULL REFERENCES dbo.Teacher(T#) );--成绩表IF EXISTS(SELECT * FROM sysobjects WHERE name='SC')DROP TABLE SCGO CREATE TABLE SC(    --SC(S#,C#,score) 成绩表     S# INT NOT NULL REFERENCES dbo.Student(S#),    C# INT NOT NULL REFERENCES dbo.Course(C#),    Score INT NOT NULL,    CONSTRAINT FK_Student_Course PRIMARY KEY(S#,C#)  --学号和课程号同时座位      );----------------------------------------------------------------------插入测试数据到数据表(Student)中--------------------------------------------DELETE FROM dbo.Student;INSERT INTO dbo.Student    ( S#, Sname, Sage, Ssex )VALUES ( 001, -- S# - int     N'张三', -- Sname - nvarchar(20)     20, -- Sage - int     '男' -- Ssex - char(2)     )     INSERT INTO dbo.Student    ( S#, Sname, Sage, Ssex )VALUES ( 002, -- S# - int     N'李四', -- Sname - nvarchar(20)     21, -- Sage - int     '男' -- Ssex - char(2)     ) INSERT INTO dbo.Student    ( S#, Sname, Sage, Ssex )VALUES ( 003, -- S# - int     N'王五', -- Sname - nvarchar(20)     22, -- Sage - int     '女' -- Ssex - char(2)     ) INSERT INTO dbo.Student    ( S#, Sname, Sage, Ssex )VALUES ( 004, -- S# - int     N'周六', -- Sname - nvarchar(20)     18, -- Sage - int     '女' -- Ssex - char(2)     )             ------------------------------------------------------------插入数据到教师表(Teacher)中----------------------------------------------------------DELETE FROM dbo.Teacher;     INSERT INTO dbo.Teacher    ( T#, Tname )VALUES ( 01, -- T# - int     N'叶平' -- Tname - nvarchar(20)     )        ------------------------------------------------------------------插入数据到课程表中-------------------------------------------------------------DELETE FROM dbo.Course;INSERT INTO dbo.Course    ( C#, Cname, T# )VALUES ( 001, -- C# - int     N'语文', -- Cname - nvarchar(20)     01 -- T# - int     )       INSERT INTO dbo.Course    ( C#, Cname, T# )VALUES ( 002, -- C# - int     N'数学', -- Cname - nvarchar(20)     01 -- T# - int     )             INSERT INTO dbo.Course    ( C#, Cname, T# )VALUES ( 0003, -- C# - int     N'英语', -- Cname - nvarchar(20)     01 -- T# - int     )                      --------------------------插入数据到成绩表(SC)中----------------------------------------------------------------------------------------------- DELETE FROM dbo.SC;       INSERT INTO dbo.SC    ( S#, C#, Score )VALUES ( 001, -- S# - int     001, -- C# - int     90 -- Score - int     )