SQL Server创建数据库及表；增，删，改，查方法介绍

1. 创建数据库的方法：

use master
go
--判断当前数据库是否存在
if exists(select * from sysdatabases where name = 'StudentManageDB')
drop database StudentManageDB
go

--创建数据库
create database StudentManageDB
ON primary
(
name  = 'StudentManagementDB_data',--数据库文件的逻辑名
filename = 'D:\Program File\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\StudentManagementDB_data.mdf',--数据库物理文件名（绝对路径）
size=10MB,--数据库文件初始大小
filegrowth=5MB--数据库扩充，数据文件增长量
),
(
name  = 'StudentManagementDB_data1',--数据库文件的逻辑名
filename = 'D:\Program File\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\StudentManagementDB_data1.ndf',--数据库物理文件名（绝对路径）
size=10MB,--数据库文件初始大小
filegrowth=5MB--数据库扩充，数据文件增长量
)
log on 
(NAME = 'StudentManagementDB_log',
filename='D:\Program File\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\StudentManagementDB_log.ldf',
size = 5MB,
filegrowth = 2MB
),
(NAME = 'StudentManagementDB_log1',
filename='D:\Program File\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\StudentManagementDB_log1.ldf',
size = 5MB,
filegrowth = 2MB
)
go

2. 创建表：

use StudentManageDB
go
if exists(select * from sysobjects where name = 'Students')
drop table Students
go
create table Students
(
	StudentId int identity(10000,1),--学号
	StudentName varchar(20) not null,--姓名
	Gender char(2) not null,--性别
	Birthday datetime not null,--出生日期
	StudentIdNo numeric(18,0) not null,--身份证号
	Age int not null,--年龄
	PhoneNumber varchar(50),
	StudentAddress varchar(500),
	ClassId int -- 班级外键
)
go
--创建班级表
if exists(select * from sysobjects where name = 'StudentClass')
drop table StudentClass
create table StudentClass
(
	ClassId int primary key,--班级编号
	ClassName varchar(20) not null,--班级姓名
)

--创建成绩表
if exists(select * from sysobjects where name = 'ScoreList')
drop table ScoreList
create table ScoreList
(
	Id int identity(1,1) primary key,--班级编号
	StudentId int not null,--班级姓名，好好外键
	CShar int null,
	SQLServe int null,
	UpdateTime datetime not null
)
--创建管理员表
if exists(select * from sysobjects where name = 'Admins')
drop table Admins
create table Admins
(
	LoginId int identity(1000,1) primary key,
	LoginPwd varchar(20) not null,--登录密码
	AdminName varchar(20) not null
)

3.增、 删、改、查方法

use StudentManageDB
go
insert into Students(StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
values('李小璐','女','1989-01-12','26',372425198608109075,'022-88997766','天津和憋屈',1)

insert into Students(StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
values('王璐','女','1980-01-12','40',372425198608109079,'022-88997767','山东',1)

select StudentName,Gender,Birthday from Students where Age >22

update Students set Gender = '女',Age=36,StudentAddress='天津河北区' where age > 22
select * from Students

delete from Students where StudentId = 10003

truncate table Students