数据库的创建、数据表的创建

 创建数据库

use master --表示下面的操作是真的master数据库完成的
go
--判断当前数据库是否在master数据库中已经存在
if exists(select * from sysdatabases where name='MISDB')
drop database MISDB
go
--创建数据库
create database MISDB
on primary
(
    name='MISDB_mdata',
    filename='D:\DB\MISDB_mdata.mdf',
    size=20MB,--数据库文件初始大小
    filegrowth=1MB --当数据超过初始大小的自动增长量
),
--次要数据文件（可以有多个，也可以没有）
(
    name='MISDB_ndata',
    filename='D:\DB\MISDB_ndata.ndf',
    size=20MB,
    filegrowth=1MB
)
log on
(
    name = 'MISDB_log1',
    filename='D:\DB\MISDB_log1.ldf',
    size=10MB,
    filegrowth=1MB
),
(
    name = 'MISDB_log2',
    filename='D:\DB\MISDB_log2.ldf',
    size=10MB,
    filegrowth=1MB
)
go

  创建数据表

use MISDB
go
--添加部门表
if exists(select * from sysobjects where name='Department')
drop table Department
go

create table Department
(
    DepartmentId int identity(10,1) primary key,--主键，标识列
    DepartmentName varchar(50) not null
)
go
--添加职位表
if exists(select * from sysobjects where name='Post')
drop table Post
go

create table Post
(
    PostId int identity(10,1) primary key,
    PostName varchar(50)not null
)
go

if exists(select * from sysobjects where name='Employee')
drop table Employee
go

create table Employee
(
    EmployeeId int identity(1000,1) primary key,--主键约束
    EmployeeName varchar(50)not null,--非空约束
    Gender char(2) not null,
    NowAddress nvarchar(100)default('上海市嘉定区'),--默认约束
    IdNo char(18)not null check(len(IdNo)=18),--检查约束
    WeiXinNumber varchar(20)not null,
    PhoneNumber varchar(50)unique,--唯一约束
    OtherWork nvarchar(50)not null,
    EntryDate datetime not null,
    PostId int references Post(PostId),--外键约束（职位编号）
    DepartmentId int references Department(DepartmentId)--外键约束（部门编号）
)
go

 

数据库的数据类型

char：固定长度

varchar：可变长度

 

数据库的查询操作

--内连接查询
select 姓名=EmployeeName,年龄=EmployeeAge,性别=EmployeeGender,职位=PostName,部门=DepartmentName from Employee
inner join Post on Employee.PostId = Post.PostId
inner join Department on Employee.DepartmentId = Department.DepartmentId

--聚合查询
select count(*)as 员工总数 from Employee
select 最大编号=max(EmployeeId) from Employee
select 最小编号=min(EmployeeId) from Employee
select 平均编号=avg(EmployeeId) from Employee