数据库基本概念-源动力

2021-07-15  本文已影响0人  苦难_69e0

遇事不会查文档：

https://docs.microsoft.com/zh-cn/sql/sql-server/?view=sql-server-ver15

数据库发展历史与SQL Server

  • 1964年世界第一个数据库系统诞生于通用电气公司，为网状数据库
  • 1968年世界第一个层次数据库系统诞生于IBM公司
    -> 1970年,IBM 的研究员Edgar F.Codd发表了A Relational Model of Data forLarge Shared Data Banks 论文,提出了关系数据模型的概念
  • 1973年,加州大学伯克利分校的Michael Stonebraker和Eugene Wong利用IBM 公司已发布的信息,以及关系模型的理论,开始开发自己的关系数据库系统Ingres
  • 1974年IBM 的Ray Boyce和Don Chamberlin将Edgar F.Codd论述的关系数据库的12条准则的数学定义里程碑式地提出了SQL(Structured Query Language,结构化查询语言)。
  • 1978年Oracle诞生
  • 1988 SQL Server 由微软与Sybase共同开发。
  • 1993 SQL Server 4.2桌面数据库系统
  • 1994 Microsoft与Sybase在数据库开发方面的合作中止。
  • 1996 SQL Server 6.5发布
  • 1998 SQL Server 7.0发布。
  • 2000 SQL Server 2000
  • 2005 SQL Server 2005发布。
  • 2007 SQL Server 2008

SQL Server数据类型

日期：DATETIME（8字节） DATE（4字节）

字符串：CHAR(n)（定长） VARCHAR(n)（不定长） NCHAR(n)（Unicode字符集的所有、定长） NVARCHAR(n)（Unicode字符集的所有、不定长）

数值：float（浮点数，不精确建议不要用） decimal(p,s)（p是精度，s是小数位数） Smallmoney/money tinyint/smallint/int/bigint
blob：text image binary(n)/varbinary(n)
位型：bit (值可以为1,0,null)

GUID：uniqueidentifier（uniqueidentifier16字节的字符串代替int（主键、外键），使用newid生成）

时间戳：timestamp（timestap8位二进制不能转换成时间，每次修改会自动更新）

SQL Server数据类型特点

日期：DATETIME 8个字节, 范围1753-1-1 00:00:00~9999-12-31 12:59:59, 精确到3.33毫秒, DATE 4个字节 范围1900-1-1~2079-6-6. 日期函数getdate, dateadd, datediff

字符串：Char/varchar最大长度8000, nchar/nvarchar最大长度4000, 存储unicode, 默认不区分大小写, 使用+进行连接; 定长与变长的区别; 字符串函数trim, charindex, substring, left, right, replicate

数值：Tinyint1个字节, 范围0~255, smallint2个字节, 范围-215~215-1, int4个字节,范围-231~231-1, bigint8个字节, 范围-2^63~263-1. 浮点数float不精确, 计算快; decimal/numeric定点数精确, 计算慢; money货币型8字节, 范围-922,337,203,685,477.5808 to 922,337,203,685,477.5807, smallmoney4字节, 范围- 214,748.3648 to 214,748.3647

blob：Text/ntext/image已经被废除了, 建议使用varbinary(max), varchar(max), nvarchar(max)替换, 用来表示二进制大对象, 大文本对象, unicode大文本对象

位型：Bit表示1, 0, null

GUID：Uniqueidentifier16字节, 全局唯一类型, 6F9619FF-8B86-D011-B42D-00C04FC964FF, 使用newid产生无需GUID, 用newsequential产生有序GUID, 但是不能单独作为函数使用

时间戳：Timestamp8位二进制值, 数据库中唯一, 用于表示数据库内部时钟, 每次数据被修改和插入, 时间戳就会更新, 可以比较大小

SQL Server对象

函数
存储过程
触发器
视图
表
约束
键
索引
登录
用户
角色
连接服务器

DDL

Data Definition Language:数据定义语言

创建对象CREATE
修改对象ALTER
删除对象DROP

DML

Data Manupulation Language:数据操纵语言

SELECT(查询)
INSERT(插入)
UPDATE(修改)
DELETE(删除)

TSQL块

Declare @A int
Begin
    Set @A=100
    If exists(select id from T where id>@A)
        Print ‘Y’
    Else
        Print ‘N’
End

赋值语句

三种赋值语句

Declare @A int=0

Declare @B int
Set @B=0

Declare @C int
Select @C=Id from T where name=‘小明’

流程控制语句

条件控制语句

if 条件
...
else...

Declare @A int=0
If @A>0 
    Print ‘Y’
Else 
    Print ‘N’
GO

循环控制语句

while 条件
begin
...
end

Declare @B int=0
While @B<100
Begin
    Insert into T(id) values(@B);
    Set @B=B+1
End
GO

事务控制语句

事务控制语句.png

实战

实战任务图.png 具体拥有的字段.png

示例：

USE [test]
GO

/****** Object:  Table [dbo].[student]    Script Date: 2021/7/12 14:58:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
--0清理表
if OBJECT_ID('Score','U') is not null
DROP table Score
go
if OBJECT_ID('ClassTeacherRelation','U') is not null
DROP table ClassTeacherRelation
go

if OBJECT_ID('Teacher','U') is not null
DROP table Teacher
go
if OBJECT_ID('Subject','U') is not null
DROP table Subject
go

if OBJECT_ID('ClassStudentRelation','U') is not null
DROP table ClassStudentRelation
go

if OBJECT_ID('Class','U') is not null
DROP TABLE Class
go

if OBJECT_ID('Student','U') is not null
DROP TABLE Student
go

--1学生表
CREATE TABLE [dbo].[Student](
    [id] [int] IDENTITY(1,1),
    [name] [nvarchar](50) not NULL,
    [gender] [varchar](10) not NULL default ('male')
    check (gender in ('male','female')),
    [birthday] [datetime] NULL,
    [code] [varchar](30) not NULL,
    constraint UK1Student unique (code),
    constraint PK_Student primary key(id)
) ;

--2班级表
CREATE TABLE [dbo].[Class](
    [id] [int] IDENTITY(1,1),
    [classname] [nvarchar](50) not NULL,
    constraint UK1Class unique (classname),
    [headmaster] [nvarchar](50) not NULL,
    constraint PK_Class primary key(id)
) ;

--3班级学生关系表
CREATE TABLE [dbo].[ClassStudentRelation](
    [id] [int] IDENTITY(1,1),
    [class_id] [int] not NULL,
    [student_id] [int] not NULL,
    constraint UK1ClassStudentRelation unique (class_id,student_id),
    constraint UK2ClassStudentRelation unique (student_id),
    constraint PK_ClassStudentRelation primary key(id)
) ;

--创建外键
alter table ClassStudentRelation 
add constraint FKClassStudentRelation_C
    foreign key (class_id)
    references Class(id);

alter table ClassStudentRelation 
add constraint FKClassStudentRelation_S
    foreign key (student_id)
    references Student(id);

--创建索引
create index XClassStudentRelation_C on ClassStudentRelation (class_id);
--create index XClassStudentRelation_S on ClassStudentRelation (student_id);



--4学科表
CREATE TABLE [dbo].[Subject](
    [id] [int] IDENTITY(1,1),
    [subjectname] [nvarchar](50) not NULL,
    constraint UK1Subject unique (subjectname),
    constraint PK_Subject primary key(id)
) ;

--5教师表
CREATE TABLE [dbo].[Teacher](
    [id] [int] IDENTITY(1,1),
    [name] [nvarchar](50) not NULL,
    [gender] [varchar](10) not NULL default ('male')
    check (gender in ('male','female')),
    [birthday] [datetime] NULL,
    constraint PK_Teacher primary key(id)
) ;

--6班级教师关系表
CREATE TABLE [dbo].[ClassTeacherRelation](
    [id] [int] IDENTITY(1,1),
    [class_id] int not NULL,
    [teacher_id] int not NULL,
    [subject_id] int not NULL,
    constraint UK1ClassTeacherRelation unique (class_id,teacher_id,subject_id),
    constraint PK_ClassTeacherRelation primary key(id)
) ;

--创建外键
alter table ClassTeacherRelation 
add constraint FKClassTeacherRelation_C
    foreign key (class_id)
    references Class(id);

alter table ClassTeacherRelation
add constraint FKClassTeacherRelation_T
    foreign key (teacher_id)
    references Teacher(id);

alter table ClassTeacherRelation
add constraint FKClassTeacherRelation_S
    foreign key (subject_id)
    references Subject(id);

--创建索引
create index XClassTeacherRelation_C on ClassTeacherRelation (class_id);
create index XClassTeacherRelation_T on ClassTeacherRelation (teacher_id);
create index XClassTeacherRelation_S on ClassTeacherRelation (subject_id);

--7考试成绩表
CREATE TABLE [dbo].[Score](
    [id] [int] IDENTITY(1,1),
    [subject_id] int not NULL,
    [exam_name] nvarchar(50) not null,
    [student_id] int not null,
    [score] int not null,
    constraint PK_Score primary key(id)
) ;
--创建外键
alter table Score
add constraint FKScore_ST
    foreign key (student_id)
    references Student(id);

alter table Score
add constraint FKScore_SU
    foreign key (subject_id)
    references Subject(id);

--创建索引
create index XScore_ST on Score (student_id);
create index XScore_SU on Score (subject_id);

--8写入数据
--8.1写入班级数据
insert into dbo.Class
(
    classname,headmaster
)
values
('一班','aaaa'),
('二班','bbbb');
go

--8.2写入学生数据
declare @Num INT=1
declare @MaxNumOfStudents INT=80 --40 students per class
while @Num <= @MaxNumOfStudents
begin
    insert into dbo.Student(
        name,
        gender,
        birthday,
        code)
        values(
        'Student ' + CAST(@Num AS VARCHAR(30)),
        (CASE WHEN @Num % 2 = 0 THEN 'male' ELSE 'female' END),
        DATEADD(DAY,@Num,CONVERT(DATETIME,'20200101',112)),
        @Num
        );
        set @Num=@Num+1
end
go
/**
another method
declare @MaxNumOfStudents INT=80 --40 students per class
with nums
as (select number
    from master..spt_values with (NOLOCK)
    where type = 'p')
select 'student' + CAST(number AS VARCHAR) name,
    case
        when CAST(number AS INT) % 2 = 0 then 'male'
        ELSE 'female'
    end gender,
    DATEADD(DAY,number,CONVERT(DATETIME,'20200101',112)) birthday,
    CAST(number AS INT)
    from nums
    where number < @MaxNumOfStudents;
**/

--8.3写入班级学生关系表
declare @MaxClassNum int;
select @MaxClassNum =count(*) from Class;
insert into dbo.ClassStudentRelation(
    class_id,student_id
)
select (s.id % @MaxClassNum) + 1 class_id,
    s.id student_id
from Student s;
go

--8.4写入学科表
insert into dbo.Subject
(
    subjectname
)
values
('C语言'),
('C++'),
('C#'),
('Java'),
('MySQL'),
('SQL Server');
go

--8.5写入教师表
DECLARE @Num INT=1
DECLARE @MaxNumOfTeachers INT=10
WHILE @Num<=@MaxNumOfTeachers
BEGIN
        INSERT INTO Teacher
        (
                name,
                gender,
                birthday
        )
        VALUES
        (
                (CASE WHEN @Num % 3=0 THEN '张'+CAST(@Num AS VARCHAR(2)) ELSE '李'+CAST(@Num AS VARCHAR(2)) END),
                (CASE WHEN @Num % 2=0 THEN 'male' ELSE 'female' END),
                DATEADD(MONTH, @Num, CONVERT(DATETIME,'19750101',112))
        );
        SET @Num=@Num+1
END
GO

--8.6写入班级教师关系表
DECLARE @MaxNumOfSubject INT;
DECLARE @MaxNumOfClass INT;
DECLARE @MaxNumOfTeacher INT;
DECLARE @Num INT=1;
DECLARE @Temp INT=1;
SELECT @MaxNumOfSubject = COUNT(*)
FROM Subject;
SELECT @MaxNumOfClass = COUNT(*)
FROM Class;
SELECT @MaxNumOfTeacher = COUNT(*)
FROM Teacher;

WHILE @Num<=@MaxNumOfClass
BEGIN
        WHILE @Temp<=@MaxNumOfSubject
        BEGIN
                INSERT INTO ClassTeacherRelation
                (
                        class_id,teacher_id,subject_id
                )
                VALUES
                (
                        @Num,
                        @MaxNumOfTeacher-@Num-@Temp,
                        @Temp
                );
                SET @Temp = @Temp + 1
        END
        SET @Temp = 1
        SET @Num = @Num + 1
END
GO

--8.7写入考试成绩表
DECLARE @Num INT = 1;
DECLARE @Person INT = 1;
DECLARE @MaxNumOfSubject INT;
SELECT @MaxNumOfSubject = COUNT(*)
FROM Subject;
DECLARE @MaxNumOfStudents INT;
SELECT @MaxNumOfStudents = COUNT(*)
FROM Student;
WHILE @Num <= @MaxNumOfSubject
BEGIN
        WHILE @Person <= @MaxNumOfStudents
        BEGIN
                INSERT INTO Score(
                        subject_id,exam_name,student_id,score
                )
                VALUES
                (
                        @Num,
                        '第'+CAST(@Num AS VARCHAR(1))+ '次考试',
                        @Person,
                        100-@Person
                );
                SET @Person = @Person + 1;
        END
        Set @Person = 1;
        Set @Num = @Num + 1;
END
GO
上一篇下一篇

猜你喜欢

热点阅读