数据库基本概念-源动力

作者: 苦难_69e0 | 来源:发表于2021-07-15 17:01 被阅读0次

遇事不会查文档：

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个字节, 范围-2^15~215-1, int4个字节,范围-2^31~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

数据库基本概念-源动力

数据库发展历史与SQL Server

SQL Server数据类型

SQL Server数据类型特点

SQL Server对象

DDL

DML

TSQL块

赋值语句

流程控制语句

条件控制语句

循环控制语句

事务控制语句

实战

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读