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

Feasibility Assessment Company

已于 2023-05-12 10:07:36 修改

阅读量348

点赞数

文章标签：数据库 sql sqlserver mysql

于 2023-05-11 20:53:55 首次发布

本文链接：https://blog.csdn.net/weixin_60328787/article/details/130630183

版权

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 Datatest
 create 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语言的案例。