数据库入门教程--01建库建表插入数据以及数据的修改

if exists(select * from sysdatabases where name ='Datatest')
Drop database Datatest
go--判断是否已存在数据库Datatest，并删除
create database Datatest--创建数据库
on--创建数据文件
(name='Datatest',--数据库中文件名filename='D:\数据库练习文件夹\Datatest.mdf',--文件的储存地址size=5mb,--文件初始大小filegrowth=10%--文件增长速度
)
log on--创建日志文件
(name='Datatest_log',--日志文件的名字filename='D:\数据库练习文件夹\Datatest_log.ldf',--日志文件储存地址size=5mb,--日志文件大小filegrowth=10%--日志文件增长速度
)
go--每段代码的结束语句if exists(select * from sys.objects where name ='Department'and type='U') drop table Department--查看数据库中是否存在表并且删除
Drop database Datatestcreate table Department--创建一个表(-- 字段名 数据类型 是否为空或者主键 DepartmentId int primary key identity(1,1),--自动增长（初始值，增长步长）DepartmentName nvarchar(50) not null,DepartmentRemark text ) go --char(10)定长字符，里面最多可以储存10个字节，每次占用十个字节--varchar(10)不定字符可变。最多占用10个字节--text长文本--nvarchar,表示对中文友好，nvarchar(100)可以储存100汉字或者字节，varchar（100）100字节或者50汉字
create table [Rank]--创建职级表，因为Rank是数据库语言，所以价格括号(-- 字段名 数据类型 是否为空或者主键 RankId int primary key identity(1,1),--自动增长（初始值，增长步长）RankName nvarchar(50) not null,RankRemark text ) go create table People--创建职级表，因为Rank是数据库语言，所以价格括号(-- 字段名 数据类型 是否为空或者主键 PeopleId int primary key identity(1,1),--自动增长（初始值，增长步长）DepartmentId int references Department(DepartmentId) not null,--引入外键RankId int references [Rank](RankId) not null,--引入外键PeopleName nvarchar(50) not null,PeopleSex nvarchar(1) default('男')check(PeopleSex='男'or PeopleSex='女'),PeopleBirth datetime not null,PeopleSalary decimal(12,2)check(PeopleSalary>=1000and PeopleSalary<=1000000),PeoplePhone varchar(20) unique not null,PeopleAddress varchar(300),PeopleAddTime smalldatetime default (getdate())  --  PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime) --datetime带有时分秒--default默认值--check添加约束--unique唯一值--getdate()用于获取当前时间go --修改表结构--添加列--删除列--修改列--alter table 表名 add 新列名 数据类型alter table People add PeopleMail varchar(200)--alter table 表名 drop 新列名 数据类型
alter table People drop column PeopleMail
--alter table 表名 alter column 列名 数据类型
alter table People alter column PeopleAddress varchar(200)
--在修改表结构时要注意实际需求
--维护约束（删除，添加）
--删除约束
--alter table 表名 drop constraint 约束名
--删除一个月薪的操作
alter table People drop constraint CK__People__PeopleSa__2E1BDC42--删除约束
alter table People add  constraint CK__People__PeopleSa1
check(PeopleSalary>=1000and PeopleSalary<=1000000)--添加一个约束
--添加主键约束
--alter table People add constraint 约束名 primary key(列名)
--添加约束（唯一）
----alter table People add constraint 约束名 unique (列名)
--默认值约束
--alter table People add constraint 约束名 default 默认值 for 列名
--外键约束
--alter table People add constraint 约束名 foreign key(列名)
--references 管理表名(列名，一般是主键)
--向表格中插入数据
--insert into 表名 values ('','')--向部门表添加数据
insert into Department(DepartmentName,DepartmentRemark)
values('软件部','......')
insert into Department(DepartmentName,DepartmentRemark)
values('硬件部','......')
insert into Department(DepartmentName,DepartmentRemark)
values('市场部','......')
--向职级表中添加数据
insert into [Rank](RankName,RankRemark)
values('初级','辅助其他人完成任务')
insert into [Rank](RankName,RankRemark)
values('中级','具备上单中单能力')
insert into [Rank](RankName,RankRemark)
values('高级','可以带动全场节奏')
--向员工表中添加数据
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(1,2,'铠','男','1988-8-9',8900,'175583092060','安徽',getdate())
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(1,2,'兰陵王','男','1988-9-9',8100,'175583092061','安徽',getdate())
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(1,2,'鲁班七号','男','1988-8-1',8800,'175583092062','上海',getdate())insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(3,2,'李白','男','1988-2-9',8300,'175583092064','西安',getdate())insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(1,1,'刘备','男','1988-2-9',8200,'175583092065','太原',getdate())
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(1,2,'花木兰','男','1983-8-9',9000,'175583092066','北京',getdate())
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(3,2,'典韦','男','1988-8-9',8900,'175583092067','安徽',getdate())
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(3,1,'孙尚香','女','1988-8-9',8700,'175583092068','安徽',getdate())insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(1,1,'虞姬','男','1988-8-9',8990,'175583092069','苏州',getdate())insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(3,2,'宫本武藏','男','1988-8-9',8900,'175583092070','哈尔滨',getdate())
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(1,1,'马超','男','1988-8-10',8909,'175583092071','安徽',getdate())
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(2,2,'马可波罗','男','1988-8-1',8930,'175583092072','北京',getdate())
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(3,2,'小乔','女','1988-12-9',8980,'175583092073','武汉',getdate())insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(1,3,'大乔','女','1988-8-9',8800,'175583092074','南昌',getdate())insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(1,2,'王昭君','女','1988-10-9',8930,'175583092075','安徽',getdate())
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(3,2,'张飞','男','1988-4-9',8930,'175583092076','合肥',getdate())
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(2,2,'关羽','男','1988-3-9',8920,'175583092077','上海',getdate())
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(1,3,'吕布','男','1988-8-9',8930,'175583092078','合肥',getdate())insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(3,2,'白起','男','1988-8-9',8900,'175583092079','杭州',getdate())insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddress,PeopleAddTime)
values(2,2,'周瑜','男','1988-8-9',8900,'175583092080','苏州',getdate())

大家好，以上是本文的主要内容。内容涵盖数据库的建立、表的建立、向表中插入数据、表结构的修改、表数据的修改。目前随着ChatGPT的爆火，我们在学习数据库时，可以通过训练使用ChatGPT极大提高我们SQL语言的编写，但需要注意提前训练一下模型。一下是我用ChatGPT辅助我编写SQL语言的案例。