数据库 2

use master
go

if exists(select * from sysdatabases where name=‘StudentManageDB’)
drop database StudentManageDB
go
–创建数据库
create database StudentManageDB
on primary
(
–数据库文件的逻辑名（数据库管理系统用的，必须唯一）
name=‘StudentManageDB_data’,
–数据库的物理文件名（绝对路径）
filename=‘D:\DB\StudentManageDB_data.mdf’,–主数据文件
–数据文件初始大小
size=20MB,–实际开发中，请根据需要设置合理的大小
–数据文件增长量 (建议不要指望这个增长)
filegrowth=5MB
)
,
(
name=‘StudentManageDB_data1’,
filename=‘D:\DB\StudentManageDB_data.ndf’,–次要数据文件
size=20MB,
filegrowth=5MB
)
log on
(
name=‘StudentManageDB_log’,
filename=‘D:\DB\StudentManageDB_log.ldf’,–主数据文件
size=20MB,
filegrowth=5MB
),
(
name=‘StudentManageDB_log1’,
filename=‘D:\DB\StudentManageDB_log1.ldf’,–主数据文件
size=20MB,
filegrowth=5MB
)
go
–创建数据表：建议，我们最好要给数据做统一的规范命名（建议大家使用Pascal命名法）
use StudentManageDB
go
–创建数据表
if exists(select * from sysobjects where name=‘StudentClass’)
drop table StudentClass
go
create table StudentClass
(
ClassId int primary key, --列的规范：列的名称 数据类型 各种约束
ClassName varchar(20) not null
)
go

if exists(select * from sysobjects where name=‘Students’)
drop table Students
go
create table Students
(
StudentId int identity(100000,1) primary key,
StudentName varchar(20) not null,
Gender char(2) check(Gender=‘男’ or Gender=‘女’), --check检查约束
DateOfBirth datetime not null ,–(Birthday)
StudentIdNo numeric(18,0) check(len(StudentIdNo)=18) unique, --身份证要求唯一，
–StudentIdNo char(18) check(len(StudentIdNo)=18) unique, --实际应用中，使用char(18)
Age int check(age>18 and age<35),
PhoneNumber varchar(50) ,
StudentAddress nvarchar(200) default(‘地址不详’), --default是默认约束
ClassId int references StudentClass(ClassId) --外键约束，要求：列的数据类型和长度和主键表对应字段必须一致
)
go
–创建管理员用户表
if exists(select * from sysobjects where name=‘Admins’)
drop table Admins
go
create table Admins
(
LoginId int identity(1000,1) primary key,
LoginPwd varchar(20) not null,
AdminName varchar(20) not null
)
go

if exists(select * from sysobjects where name=‘ScoreList’)
drop table ScoreList
go
create table ScoreList
(
Id int identity(1,1) primary key,
StudentId int references Students(StudentId),
CSharp int null,
SQLServerDB int null,
UpdateTime datetime default(getdate())
)
go

–print getdate()

–添加数据
use StudentManageDB
go

–insert into StudentClass(ClassId,ClassName) values(1,‘软件1班’)
–insert into StudentClass(ClassId,ClassName) values(2,‘软件2班’)
insert into StudentClass(ClassId,ClassName) values(1,‘软件1班’),(2,‘软件2班’),(3,‘计算机1班’),(4,‘计算机2班’)

select * from StudentClass

insert into Students ( StudentName, Gender, DateOfBirth, Age, StudentIdNo,PhoneNumber, StudentAddress, ClassId)
values(‘王小虎’,‘男’,‘1989-08-07’,22,120223198908071111,‘022-22222222’,‘天津市南开区红磡公寓5-5-102’,1)
,(‘贺小张’,‘女’,‘1989-05-06’,22,120223198905062426,‘022-33333333’,‘天津市河北区王串场58号’,2)
,(‘马小李’,‘男’,‘1990-02-07’,21,120223199002078915,‘022-44444444’,‘天津市红桥区丁字沽曙光路79号’,4)
,(‘冯小强’,‘女’,‘1987-05-12’,24,130223198705125167,‘022-55555555’,default,2)
,(‘杜小丽’,‘女’,‘1986-05-08’,25,130223198605081528,‘022-66666666’,‘河北衡水路北道69号’,1)
,(‘王俊桥’,‘男’,‘1987-07-18’,24,130223198707182235,‘022-77777777’,default,1)
,(‘张永利’,‘男’,‘1988-09-28’,24,130223198909282235,‘022-88888888’,‘河北保定市风华道12号’,3)
,(‘李铭’,‘男’,‘1987-01-18’,24,130223198701182257,‘022-99999999’,‘河北邢台市幸福路5号’,1)
,(‘宁俊燕’,‘女’,‘1987-06-15’,24,130223198706152211,‘022-11111111’,default,3)
,(‘刘玲玲’,‘女’,‘1989-08-19’,24,130223198908192235,‘022-11111222’,default,4)

	 select * from Students

insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100000,60,78)
,(100001,55,88),(100002,90,58),(100003,88,75),(100004,62,88),(100006,52,80),
(100007,91,66),(100009,78,35)

insert into ScoreList (StudentId,CSharp)values(100005,60)

select * from ScoreList

–插入管理员表
insert into Admins (LoginPwd,AdminName) values(123456,‘王晓军’),(123456,‘张明丽’)

—数据的基本操作

–【1】查询有null的数据
select * from ScoreList where SQLServerDB is null

–【2】取查询结果的前面几条数据（后面我们分页中会经常遇到）
select top 3 StudentId,SQLServerDB from ScoreList

select top 3 StudentId,SQLServerDB from ScoreList order by SQLServerDB ASC

select top 3 StudentId,SQLServerDB from ScoreList where SQLServerDB is not null order by SQLServerDB ASC

–请大家注意上面两条查询的区别，排序的时候，null是参与的

select top 20 percent StudentId,SQLServerDB from ScoreList order by SQLServerDB ASC

select 学号=StudentId, 总成绩=(SQLServerDB+CSharp)from ScoreList
select StudentId as 学号, (SQLServerDB+CSharp) as 总成绩 from ScoreList

select * from Students
select * from ScoreList
–链接查询的需求：当我们查询的结果在不同的表中时，我们通常需要将两个或多个表关联起来查询
select Students.StudentId,StudentName,Age,总成绩=(Csharp+SQLServerDB) from ScoreList
inner join Students on Students.StudentId=ScoreList.StudentId

–左外链接查询（优先保证左边的数据的表的所有数据，右边的如果没有对应的数据，则用null填充）
select Students.StudentId,StudentName,Age,总成绩=(Csharp+SQLServerDB) from Students
left join ScoreList on Students.StudentId=ScoreList.StudentId

–上面这个left join能查询没有参加考试的学员信息

select Students.StudentId,StudentName,Age,总成绩=(Csharp+SQLServerDB) from Students
right join ScoreList on Students.StudentId=ScoreList.StudentId

–like关键字 模糊查询 （%通配符 %张 张% %张% ）

–between 80 and 90

select StudentId,StudentName,Age from Students where StudentName like ‘%张%’

select Students.StudentId,StudentName,Age,总成绩=(Csharp+SQLServerDB) from ScoreList
inner join Students on Students.StudentId=ScoreList.StudentId
where (Csharp+SQLServerDB) between 160 and 190

–统计函数 avg max min sum
select avg(CSharp) as C#平均, 学生总数=count(*),SQLServer最高成绩=max(SQLServerDB)
from ScoreList