use master go if exists(select * from sysdatabases where name = 'CourseManageDB') --查询是否存在这个库 drop database CourseManageDB --删除数据库(不可恢复) go --表示结束 create database CourseManageDB --创建数据库 on primary ( --数据库的逻辑文件名(就是系统用的,必须唯一) name='CourseManageDB_data', --数据库物理文件名(绝对路径) filename='E:\DB\CourseManageDB_data.mdf', --主数据文件名 --数据库初始文件大小(一定要根据你实际生产需求来定) size=10MB, --数据文件增值量(也要参考文件本身大小) filegrowth=1MB ), ( name='CourseManageDB_data1', filename='E:\DB\CourseManageDB_data1.ndf', size=10MB, filegrowth=1MB ) --日志文件 log on ( name='CourseManageDB_log', filename='E:\DB\CourseManageDB_log.ldf', size=10MB, filegrowth=1MB ) go --指定要操作的数据库 use CourseManageDB go --创建讲师表 if exists(select * from sysobjects where name='Teacher') --where查询条件 drop table Teacher go create table Teacher ( ID int identity(1000,1) primary key, --key主键 identity表示从1000开始每次增加1 loginUser varchar(50) not null, --varchar(50)最多50个,长度不固定 varchar(50) 表示可存放50个英文字符25个汉字 nvarchar(50)可存放50个汉字100个字符 loginPwd varchar(18) check(len(loginPwd)>=6 and len(loginPwd) <=18) not null, --check限制条件 loginPwd的长度要大于6且小于18 userName varchar(20) not null, phoneNumber char(11) not null, --char(11)长度固定11 ,not null 初始为null nowAddress varchar(100) default('地址不祥') --default 设置默认数据 ) go --课程分类表 if exists(select * from sysobjects where name='CourseCategory') drop table CourseCategory go create table CourseCategory ( Category_Id int identity(100,1) primary key, CategoryName varchar(20) not null ) go --课程表 if exists(select * from sysobjects where name = 'Course') drop table Course go create table Course ( CourseID int identity(1,1) primary key, CourseName varchar(20) not null, CourseContent nvarchar(500) not null, --nvarchar可存放500个汉子,1000个字符 ClassHour int not null,--课时 Credit int check(Credit >=1 and Credit <=30) not null, --check限制约束 Category_Id int references CourseCategory(Category_Id) not null, --外键约束 ID int references Teacher(ID)--外键约束 ) go --向表中插入数据 insert into Teacher(loginUser,loginPwd,userName,phoneNumber) values('admin1','123456','李老师','18315000001'), ('admin2','123456','王老师','18315000002'), ('admin4','123456','高老师','18315000004') insert into Teacher(loginUser,loginPwd,userName,phoneNumber,nowAddress) values('admin3','123456','付老师','18315000003','重庆沙坪坝区') --添加课程分类 insert into CourseCategory(CategoryName) values('前端开发'),('java开发'),('.net开发'),('c#开发') --添加课程信息 insert into Course(CourseName,CourseContent,ClassHour,Credit,Category_Id,ID) values('.net上位机开发','.net基础课程,net core/sql,asp.net',500,10,102,1002), ('c#开发','c#基础课程,net core/sql,asp.net',240,10,103,1003), ('web前端开发','javascript/css/html/vue/react',180,10,100,1001), ('java','java基础语法,java算法',321,10,101,1002) --删除id为1000的数据 delete from Teacher where ID =1000 --修改id为1003的数据 update Teacher set userName = 'mast王老师',phoneNumber='18315114070' where ID = 1003 --查询指定某张表 --select * from Teacher --select * from Course --select * from CourseCategory --关联查询 select CourseName,CourseContent,ClassHour,Credit,Course.Category_Id,CategoryName,userName from Course inner join CourseCategory on Course.Category_Id=CourseCategory.Category_Id inner join Teacher on Teacher.ID=Course.ID
运行结果
标签:入门,--,sqlServer,CourseManageDB,基础,50,go,数据库,name From: https://www.cnblogs.com/tlfe/p/18152658