sqlServer 基础入门

 

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) unique not null, --char(11)长度固定11 ,not null 初始为null, unique 表示唯一约束,
  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

--向 CourseCategory 表中添加 teacher 字段
alter table CourseCategory add teacher nvarchar(10) null


--删除 CourseCategory 表中 teacher 这一列数据
alter table CourseCategory drop column teacher

--添加unique约束
alter table Teacher add constraint IX_Teacher unique(phoneNumber)


--课程表
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)--外键约束
    ID int not null
)
go

--外键约束
alter table Course add constraint FK_Teacher foreign key(ID) references Teacher(ID)

--向表中插入数据
insert into Teacher(loginUser,loginPwd,userName,phoneNumber)
values('admin1','123456','李老师','18315000001'),
('admin2','123456','王老师','18315000002'),
('admin4','123456','高老师','18315000004')

insert into Teacher(loginUser,loginPwd,userName,phoneNumber)
select 'admin5','123456','付老师','18315000005'

select * from Teacher

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

运行结果