SQL server基础笔记(SQL Server foundation notes)

SQl server

第一章 数据库基础

1.1、 SQL Server软件的安装

(1)安装SQLServer前的准备工作:

扩展名为.iso的安装包文件,推荐使用虚拟光驱软件Daemon Tools进行安装(注意:安装Daemon

Tools时选择“免费许可”)。

(2)安装SQLServer时的注意事项:

功能选择“最小化安装”:数据库引擎服务(3个子项)、客户端连接工具、管理工具-基本(1个子项)。

1.2 、SQL Server软件的卸载

如何完全卸载SqlServer2012,参见以下网址:

https://jingyan.baidu.com/article/e4d08ffffd69b6330fd2f60d90.html

1.3、 数据库的分类

一般来说,数据库按照组织式分为两大类:关系型数据库和非关系型数据库。

当前主流的关系型数据库有Oracle、DB2、Microsoft SQL Server、MySQL、Microsoft Access等。

SqlServer:由Microsoft开发。

Oracle:由Oracle(甲骨文)开发 ,于1983年推出的世界上第一个开放式商品化关系型数据库管理系

统。

DB2:是美国IBM公司开发的一套关系型数据库管理系统,它主要应用于大型应用系统 。

MySQL:

由瑞典 MySQL AB 公司开发,目前属于 Oracle 旗下公司,一般用于中小型网站 的开发。

参见:

(1)https://www.php.cn/sql/421899.html

(2)https://www.zhihu.com/question/432781043/answer/1604375728

第二章 企业管理器

2.1 、数据库文件

数据库文件的扩展名:

主数据库文件: .mdf( 是 primary data fifile 的缩写

次数据库文件: .ndf(是Secondary data fifiles的缩写 )

事务日志文件: .ldf( 是Log data fifiles的缩写)

主要数据文件包含数据库的启动信息,并指向数据库中的其他文件,所有表、视图、存储和数据都

在这个文件。用户数据和对象可存储在此文件中,也可以存储在次要数据文件中。每个数据库有一个主 要数据文件。

次要数据文件是可选的,由用户定义并存储用户数据。通过将每个文件放在不同的磁盘驱动器上,次要文件可用于将数据分散到多个磁盘上。另外,如果数据库超过了 小,可以使用次要数据文件,这 样数据库就能继续增长。

事务日志文件保存用于恢复数据库的日志信息。每个数据库必须至少有一个日志文件。

2.2 、数据库的常用操作

1、脱机:

对数据库名右击–>任务–>脱机

2、联机:

它是脱机的反操作。

2、分离:

对数据库名右击——>任务——>分离,注意勾选”删除连接“。

附加:他是分离的反操作,对“数据库”右击——>附加。

3、删除数据库:

对数据库名右击–>删除

2.3、 SQL Server的数据类型

SqlServer常用数据类型参考:https://www.cnblogs.com/jojoword/p/10543763.html

2.3.1 、文本数据类型

char:固定长度的非unicode字符(一个汉字占两个字节)。

varchar:可变长度的非unicode字符(一个汉字占两个字节)。

nchar:固定长度的unicode字符(一个汉字占一个字节)。

nvarchar:可变长度的unicode字符(一个汉字占一个字节)。

示例:

char(6):可以存6个字母或数字,但只能存3个汉字。

nchar(6):可以存6个字母、数字或汉字。

长文本类型:

text:

存储可变长度的非Unicode数据,最大长度为2^31-1(2,147,483,647)个字符。

ntext:

存储可变长度的Unicode数据。

注意:

nchar、nvarchar、ntext这三种从名字上看比前面三种多了个“N”。它表示存储的是Unicode数据类型

的字符。我们知道字符中,英文字符只需要一个字节存储就足够了,但汉字需要两个字节存储,英文与

汉字同时存在时容易造成混乱,Unicode字符集就是为了解决字符集这种不兼容的问题而产生的,它所

有的字符都用两个字节表示,即英文字符也是用两个字节表示。nchar、nvarchar的长度是在1到4000

之间。和 char、varchar比较起来,nchar、nvarchar则最多存储4000个字符,不论是英文还是汉字;

而char、varchar最多能存储 8000个英文,或4000个汉字。可以看出使用nchar、nvarchar数据类型时不用担心输入的字符是英文还是汉字,较为方便,但在存储英文时数量上有些损失。 所以一般来说,

如果含有中文字符,用nchar/nvarchar,如果纯英文和数字,用char/varchar。

2.3.2、 整数数据类型

(1).bigint(相当于C#中的long)

每个bigint存储在8个字节中,其中一个二进制位表示符号位,其它63个二进制位表示长度和大

小,可以表示-2的63次方~2的63次方-1范围内的所有整数。

(2).int(相当于C#中的int)

int或者integer,每个int存储在4个字节中,其中一个二进制位表示符号位,其它31个二进制位表示长度

和大小,可以表示-2的31次方~2的31次方-1范围内的所有整数。

(3).smallint(相当于C#中的short)

每个smallint类型的数据占用了两个字节的存储空间,其中一个二进制位表示整数值的正负号,其它

15个二进制位表示长度和大小,,可以表示-2的15次方~2的15次方-1范围内的所有整数。

(4).tinyint(相当于C#中的byte)

每个tinyint类型的数据占用了一个字节的存储空间,可以表示0~255范围内的所有整数.

2.3.3、 浮点数据类型

浮点数据类型存储十进制小数,用于表示浮点数值数据的大致数值数据类型。浮点数据为近似值;浮点

数值的数据则Sql Server中采用了只入不舍的方式进行存储,即当且仅当要舍入的数是一个非零数时,

对其保留数字部分的最低有效位上加1,并进行必要的近位。

(1).real:相当于C#中的flfloat类型。

可以存储正的或者负的十进制数值,它的存储范围从-3.40E+38~-1.18E-38、0以及1.18E-

38~3.40E+38.每个real类型的数据占用4个字节的存储空间。

(2)flfloat[(n)]:相当于C#中的double类型。

其中为用于存储flfloat数值尾数的位数(以科学计数法表示),因此可以确定精度和存储大小。如果

指定了n它必须是介于1和53之间的某个值。n的默认值为53.

其范围从-1.79E+308-2.23E-308、0以及2.23E+3081.79E-308。如果不指定数据类型flfloat的长

度,它占用8个字节的存储空间。flfloat数据类型可以写成flfloat(n)的形式,n为指定flfloat数据的精度,n

为153之间的整数值。当n取124时,实际上定义了一个real类型的数据,系统用4个自己存储它。当n

取25~53时,系统认为其是flfloat类型,用8个字节存储它。

(3).decimal[(p[,s])]和numeric[(p[,s]):相当于C#中的decimal类型。

带固定精度和小数位数的数值数据类型。使用最大精度时,有效值从-1038+1~1038-1。

numeric在功能上等价于decimal。

p(精度)指定了最多可以存储十进制数字的总位数,包括小数点左边和右边的位数,该精度必须是从

1到最大精度38之间的值,默认精度为18.

s(小数位数)指定小数点右边可以存储的十进制数字的最大位数,小数位数必须是从0到p之间的值,

仅在指定精度后才可以指定小数的位数。默认小数位数是0;因此,0<=s<=p。最大存储大小基于精度

而变化。例如:decimal(10,5)表示共有10位数,其中整数5位,小数5位。

2.3.4、 日期时间类型

datetime:年月日时分秒.毫秒

smalldatetime:年月日时分秒

date:年月日2.3.5 位数据类型

bit: 称为位数据类型,只取0或1为值,长度1字节。bit值经常当作逻辑值用于判断true(1)或false(0),输

入非0值时系统将其替换为1。

第三章 查询分析器

select*from 表名--查询表中所有的信息
select*from 表名where条件表达式--带条件查询
select*(可以换成单独的表中某个表名列名)from 表名where条件表达式--带条件查询

3.1 、建库

语法:create database 数据库名

建库案例:

创建数据库StuInfo2的代码如下所示。

use master 

go

--若存在数据库StuInfo2,则删除该库 

if exists(select * from sysdatabases where name='StuInfo2') 

begin 

drop database StuInfo2 

end 

go

--创建数据库 

create database StuInfo2 

on primary --主数据库文件 

( 

name='StuInfo2', 

filename='E:\Database\SophomoreYearDB\2020级\6班\StuInfo2.mdf', 

size=5MB, 

maxsize=unlimited, 

filegrowth=1MB 

)

log on --日志文件 

( 

name='StuInfo2_log', 

filename='E:\Database\SophomoreYearDB\2020级\6班\StuInfo2_log.ldf', 

size=5MB, 

maxsize=unlimited, 

filegrowth=1MB 

)

go

3.2 、建表

语法:create table 表名

建表案例:

在上面的案例代码的基础上,继续编写如下代码:

–使用数据库StuInfo2来建表

use StuInfo2

create table StuBase 

( 

StuNo bigint not null, 

StuName nvarchar(50) not null, 

Gender bit not null, 

IdCard char(18) not null, 

Age int not null, 

Remark nvarchar(200) null 

)

go

create table Score 

(

StuNo bigint not null, 

CSharpScore float not null 

)

go

3.3 、约束

3.3.1 、SQL Server常见的7种约束

(1)主键约束:primary key(PK)

(2)唯一约束:unique(UQ),在表里可以存在多个唯一约束,但一个唯一约束里面不能存在同样的

值。

(3)检查约束:check(CK),自定义的约束。

(4)默认值约束:default(DF)

(5)外键约束:foreign key(FK)

(6)非空约束:not null

(7)标识列:identity ,系统自动填值进去,它是从哪个值开始,每次增加多少。 identity(1,1)前面表

示从1开始,后者表示每次增长1。

3.4、加五种键约束案例:

在上面的案例代码的基础上,继续编写如下代码:

3.4.1、添加主键约束

alter table StuBase add constraint PK_StuNo primary key(StuNo); 

3.4.2、添加默认约束

--alter table StuBase add constraint DF_Gender default(1) for Gender; 

alter table StuBase add constraint DF_Gender default 1 for Gender; 

3.4.3、添加唯一约束(表设计界面对任一列右击,选择“索引/键”可查看添加的约束)

alter table StuBase add constraint UQ_IdCard unique(IdCard); 

3.4.4、添加检查约束

alter table StuBase add constraint CK_Age check(Age>=18); 

3.4.5、添加外键约束

alter table Score add constraint FK_StuNo foreign key(StuNo) references 

StuBase(StuNo); 

3.5、删除约束

alter table 表名 drop constraint 约束名;

alter table StuBase drop constraint d_Gender;

第四单元

4.1、 T-SQL中的运算符

4.1.1、 比较运算符

运算符 含义

= 等于

> 大于

< 小于

>= 大于或等于

<= 小于或等于

<> 不等于

! 非

4.1.2 、逻辑运算符

逻辑表达式 说明 示例

AND 逻辑与 1 AND 1 = ; 1 AND 0 = 0; 0 AND 0 = 0;

OR 逻辑或 1 OR 1 = 1; 1OR 0 = 1; 0 OR 0 = 0;

NOT 逻辑非 NOT 1 = 0; NOT 0 = 1;

4.2 、简单查询数据

4.2.1、查表中所有记录:

select * from 表名 

4.2.2、带条件的查询:

select*from 表名 where 条件表达式 

4.3、删除表

语法:drop table 表名

4.4、插入数据

语法:insert  into 表名(列名列表)values(值列表)

注意:在插入数据时,若表中有自增列,则该列的值由系统自动分配,不需要程序员给定值。

4.5、更新数据行的语法:

update 表名 set 列名=新值 where 列名=旧值

4.6、删除数据行:

delete from 表名 where 行名

delete from team where name=2;

–Truncate table执行的速度更快,使用的系统资源和事务日志更少,不能删除主键外键关系的主键表中的数据,若要删除主键表中的数据,需要用delete来完成。

Truncate table Team。

Truncate table 表名。

Truncate table可以删除存在主外键关系的外键表中的所有数据。

Truncate table Player

4.7、一次性插入多条数据:

insert
select.(插入的数据).union

4.8、一次性多条数据行:

将一张表中的数据拷贝到另一张新的数据表中,使用SELECT…INTO语句
–将Team表中的数据行复制到Team2中.

select*into Team2 from Team;

4.9、查询学生的学号,姓名,性别的三种方法

–给查询的列取别名的三种方式

select '学号'=StuNo,Name '姓名',(case when Gender=1 then '男' else '女' end) as '性别'
from Baselnfo ;

select  '学号'=StuNo,'姓名'=Name ,'性别'=(case when Gender=1 then '男' else '女' end)  from Baselnfo;

select StuNo '学号',Name '姓名',(case when Gender=1 then '男' else '女' end) '性别' from Baselnfo;

select  StuNo as '学号',Name as '姓名',(case when Gender=1 then '男' else '女' end) as '性别'  from Baselnfo;

select Name+'-'+Idcard as'姓名-身份证号码'
from Baselnfo;

第五单元

5.1、关系运算符:

<>、!=

5.1.1、逻辑运算符:

and、or、not

5.2、过滤表中已有信息

5.2.1、过滤重复行(针对单列过滤)

select distinct Gender from  Baselnfo;

5.2.2、过滤重复行(针对多列过滤)

select distinct Gender,Name from  Baselnfo;

select top 3 *from Baselnfo

select top 3 *from Baselnfo where 列名

5.3 、使用TOP关键字仅显示前面若干条记录

top 整数:前多少条记录 

top 整数 percent:前百分之多少条记录

5.4、使用order by查询表中的正序排名:

select top 2 * from StuScore where Subject='C#'  order by Score desc ;

5.5、查询链接在一起显示

select cast(StuNo as varchar) +'-'+Name as'学号-姓名'
from Baselnfo;

5.6、查询除了这个之外所有的数据:

select*from  表名where 列名<> '列名中的数据'

select*from  表名where 列名!='列名中的数据'

5.7、查询多少到少之间的数据:

select*from StuScore where Subject='C#' and Score >= 40 and Score<=90;

5.5、对查询结果筛选列表运算符in

in:用于匹配值列表

not in

5.5.1、in用于跟多个具体的值做等值比较,满足任意一个即可

select*from Baselnfo where StuNo  in(20209130501);

5.5.2、not in:跟不等于这多个具体值的数据进行匹配

select*from Baselnfo where StuNo not in(20209130501,20209130503,20209130504);

5.6、模糊查询

语法:like结合通配符(%、_、[]、[^])使用

--查询姓‘哈’的学生信息
select*from Baselnfo where Name like '哈%';

select*from 表名 where 列名 like '查询的名字';

--查询不姓‘哈’的学生信息
select*from Baselnfo where Name not like '哈%';

select*from 表名where 列名 not like '查询的 字';

--查询姓名只有两个字,且第二个字不为‘哈’的同学
select*from Baselnfo where Name like'_[^哈]';

select*from 表名where 列名 not like '查询的 字';——下划线代表占一个字节

5.8、order by字句可以依据多个字段排序

–查询成绩信息,将C#成绩按降序排序,成绩相同的按学号升序排序

select  * from StuScore where Subject='SQlSever'  order by Score desc, StuNo asc;--降序
select  * from StuScore where Subject='SQlSever'  order by Score desc ;--降序

select  * from 表名 where 列名='列里面添加的数据'  order by 列名desc ;--降序

5.9、聚合函数

SUM():用于统计数值型字段的总和,它只能用于数值型字段,而且 NULL 值将被忽略。

AVG():用于计算一个数值型字段的平均值,该字段中的NULL 值在计算过程中将被忽略。

MAX():用于返回字段的最大值,在计算过程中遇到 NULL 值时予以忽略。

MIN():用于返回字段的最小值,在计算过程中遇到 NULL 值时予以忽略。

COUNT():统计记录行数。

5.9.1、求总分

select '总分'=SUM(Score) from StuScore;

select '总分'=SUM(列名) from 表名;

5.9.2、求每门课程的总分(各,每)

–在一个查询语句中可以使用任意多个字段对结果字段内的记录进行分组,字段列表中的每个输出字段必须在GROUP BY字句中出现或者用在某个聚合函数中。

select Subject '科目',SUM(Score)'总分' from StuScore group by Subject;

select 列名'科目',SUM(列名)'总分' from 表名group by 列名;

5.9.3、求各科平均分,并按降序排

select Subject '科目',AVG(Score)'平均分' from StuScore group by Subject order by AVG(Score) desc;

5.9.4、求各科最高分数

select Subject '科目',MAX(Score)'最高分' from StuScore group by Subject;

5.9.5、求各科最低分数

select Subject '科目',MIN(Score)'最低分' from StuScore group by Subject;

5.9.6、求各科参考人数

select Subject '科目',count(*)'实际参加考试的人数' from StuScore group by Subject;

5.10、HAVING子句:筛选

select Subject '科目',AVG(Score)'平均分>=70' from StuScore group by Subject having AVG(Score)>=70 order by AVG(Score) desc;

第六章

如果一个查询需要对多个表进行操作,就称为联接查询。联接查询的结果集或结果表,称为表之间的连

接。

联接查询分为内部联接查询,自联接查询,外部联接查询和复合条件联接查询等等。

6.1、内(部)链接

select bi.StuNo,Name,Gender,Age,Idcard,Subject,Score
from Baselnfo bi inner join StuScore ss on bi.StuNo= ss.StuNo

–两个表名中间使用逗号分隔,使用where子句写关联的字段

select bi.StuNo,Name,Gender,Age,IdCard,Subject,Score
from Baselnfo bi inner join StuScore ss
on bi.StuNo=ss.StuNo

6.2、外(部)联接

6.2.1、左外联接:left [outer] join

–特点:以left join左边的表为主表,右边的表为从表,,优先显示主表的所有记录的所有记录,若从表中没有相匹配的记录,则用“NULL”填充。

select bi.StuNo,Name,Gender,Age,IdCard,Subject,Score
from Baselnfo bi left join StuScore ss
on bi.StuNo=ss.StuNo

6.2.2、右外联接:right [outer] join

–特点:以right join右边的表为主表,左边的表为从表,,优先显示主表的所有记录的所有记录,若从表中没有相匹配的记录,则用“NULL”填充。

select bi.StuNo,Name,Gender,Age,IdCard,Subject,Score
from Baselnfo bi right join StuScore ss on bi.StuNo=ss.StuNo;

6.3、交叉联接:cross [outer] join

–特点:查询结果集的记录条数等于cross join左右两边表的记录条数的乘积。

select count(*)
from Baselnfo cross join StuScore

select count(*)from Baselnfo--9
select count(*) from StuScore--7

6.4、 自联接

联接不仅可以在表之间进行,也可以使一个表同其自身进行联接,这种联接称为自联接,相应的查询称

为自联接查询。

6.5 、更复杂的联接查询

SELECT 字段列表 FROM 表1 INNER JOIN 表2 ON 条件表达式1 INNER JOIN 表3 ON 条件表达式2 

6.6、集合运算

union和unionall–并集运算

intersect–交集运算

except–减集

6.6.1、 使用UNION和UNION ALL进行并集运算

UNION 和 UNION ALL都将两组查询进行了并集,唯一的区别在于,UNION不允许有重复列而UNION

ALL允许有重复的列。

UNION 返回的结果集的标题仅从第一个查询获得。

6.6.2、使用INTERSECT进行交集运算

交集同样的是对多个结果集合进行操作。交集将两个结果集中相同的记录取出来,形成一个新的集合。

6.6.3 、使用EXCEPT进行减集运算

减集是指比较2个结果集,将EXCEPT 关键字前的结果集除去交集部分而组成的新的集合。

注意:减集的最后获得的结果与2个结果集放的顺序有密切的关系。

Microsoft Visio 2010软件

————————

SQl server

Chapter I database foundation

1.1、 SQL Server软件的安装

(1) Preparations before installing SQL Server:

The extension is For the installation package file of ISO, it is recommended to use the virtual optical drive software daemon tools for installation (Note: install daemon)

Tools (select “free license”).

(2) Precautions when installing SQL Server:

Function selection “minimize installation”: Database Engine Service (3 sub items), client connection tool, management tool – Basic (1 sub item).

1.2 、SQL Server软件的卸载

To completely uninstall sqlserver2012, see the following website:

https://jingyan.baidu.com/article/e4d08ffffd69b6330fd2f60d90.html

1.3 classification of database

Generally speaking, databases are divided into two categories according to organization: relational databases and non relational databases.

At present, the mainstream relational databases include Oracle, DB2, Microsoft SQL server, mysql, Microsoft Access, etc.

SqlServer:由Microsoft开发。

Oracle: the world’s first open commercialized relational database management system developed by Oracle and launched in 1983

Unification.

DB2: a set of relational database management system developed by IBM, which is mainly used in large-scale application systems.

MySQL:

Developed by Swedish MySQL AB company, it is currently a company under Oracle and is generally used for the development of small and medium-sized websites.

See:

(1)https://www.php.cn/sql/421899.html

(2)https://www.zhihu.com/question/432781043/answer/1604375728

Chapter II Enterprise Manager

2.1. Database files

Extension of database file:

Master database file: MDF (abbreviation of < strong > primary data fifile < / strong > < strong >)

Secondary database file: NDF (short for secondary data files)

Transaction log file: LDF (short for log data files)

The main data file contains the startup information of the database and points to other files in the database. All tables, views, storage and data are saved

In this file. User data and objects can be stored in this file or in a secondary data file. Each database has a main data file.

Secondary data files are optional and are user-defined and store user data. By placing each file on a different disk drive, secondary files can be used to spread data across multiple disks. In addition, if the database is more than small, you can use secondary data files so that the database can continue to grow.

The transaction log file holds the log information used to recover the database. Each database must have at least one log file.

2.2 common operations of database

1. Offline:

Right click the database name — & gt; Task — & gt; offline

2. Online:

It is an offline anti operation.

2. Separation:

Right click the database name – & gt; Task – & gt; Separate and check “delete connection”.

Additional: it is the reverse operation of separation. Right click “database” – & gt; Additional.

3. Delete database:

Right click the database name — & gt; delete

2.3、 SQL Server的数据类型

SqlServer常用数据类型参考:https://www.cnblogs.com/jojoword/p/10543763.html

2.3.1 text data type

Char: non Unicode character of fixed length (one Chinese character takes up two bytes).

Varchar: variable length non Unicode characters (one Chinese character takes up two bytes).

Nchar: a Unicode character of fixed length (one Chinese character occupies one byte).

Nvarchar: Unicode characters of variable length (one Chinese character occupies one byte).

Example:

Char (6): you can save 6 letters or numbers, but only 3 Chinese characters.

Nchar (6): can save 6 letters, numbers or Chinese characters.

Long text type:

text:

Store variable length non Unicode data with a maximum length of 2 ^ 31-1 (2147483647) characters.

ntext:

Stores variable length Unicode data.

be careful:

Nchar, nvarchar and ntext have more “n” than the previous three in terms of names. It indicates that the stored data type is Unicode

The character of the. We know that in Chinese characters, English characters only need one byte to store, but Chinese characters need two bytes to store

When Chinese characters exist at the same time, it is easy to cause confusion. Unicode character set is produced to solve the incompatibility of character set

Some characters are represented by two bytes, that is, English characters are also represented by two bytes. Nchar and nvarchar are between 1 and 4000 in length

between. Compared with char and varchar, nchar and nvarchar can store up to 4000 characters, whether in English or Chinese;

Char and varchar can store up to 8000 English or 4000 Chinese characters. It can be seen that when using nchar and nvarchar data types, there is no need to worry about whether the input characters are English or Chinese. It is more convenient, but there is some loss in the quantity of storing English. So generally speaking,

If it contains Chinese characters, use nchar / nvarchar; if it is pure English and numbers, use char / varchar.

2.3.2 integer data type

(1). Bigint (equivalent to long in c#)

Each bigint is stored in 8 bytes, of which one binary bit represents the symbol bit and the other 63 binary bits represent the length and size

Small, which can represent all integers within the range of – 2 to the 63rd power ~ 2 to the 63rd power – 1.

(2). Int (equivalent to int in c#)

Int or integer. Each int is stored in 4 bytes. One binary bit represents the symbol bit and the other 31 binary bits represent the length

And size, which can represent all integers in the range from the 31st power of – 2 to the 31st power of – 1 of 2.

(3).smallint(相当于C#中的short)

Each smallint type of data takes up two bytes of storage space, in which one binary bit represents the sign of the integer value and the others

15 bits represent length and size, and can represent all integers within the range of – 2 to the 15th power ~ 2 to the 15th power – 1.

(4).tinyint(相当于C#中的byte)

Each tinyint type of data takes up one byte of storage space and can represent all integers in the range of 0 ~ 255

2.3.3 floating point data type

The floating-point data type stores decimal decimals and is used to represent the approximate numeric data type of floating-point numeric data. Floating point data is approximate; floating-point

The data of numerical value is stored in SQL Server in the way of rounding only, that is, if and only if the number to be rounded is a non-zero number,

Add 1 to the least significant bit of the reserved digital part, and make the necessary near bit.

(1). Real: equivalent to the flfloat type in C #.

It can store positive or negative decimal values. Its storage range is -3.40e + 38 ~ -1.18e-38, 0 and 1.18e-

38~3.40E+38. Each real type of data occupies 4 bytes of storage space.

(2) Flfloat [(n)]: equivalent to the double type in C #.

Where is the number of bits used to store the mantissa of flfloat value (expressed in scientific counting method), so the accuracy and storage size can be determined. If

N is specified. It must be a value between 1 and 53. The default value of n is 53

It ranges from -1.79e + 308-2.23e-308, 0 and 2.23e + 3081.79e-308. If you do not specify the length of the data type flfloat

Degree, which takes up 8 bytes of storage space. The flfloat data type can be written in the form of flfloat (n). N is the precision of the specified flfloat data, n

Is an integer value between 153. When n takes 124, a real type of data is actually defined, and the system uses four to store it. When n

When 25 ~ 53 is taken, the system considers it as flfloat type and stores it with 8 bytes.

(3). Decimal [(P [, S])] and numeric [(P [, S]): equivalent to the decimal type in C #.

Numeric data type with fixed precision and scale. When the maximum precision is used, the valid values range from – 1038 + 1 to 1038-1.

Numeric is functionally equivalent to decimal.

P (precision) specifies the total number of decimal digits that can be stored at most, including the number of digits to the left and right of the decimal point. The precision must be from

1 to the maximum precision of 38. The default precision is 18

S (scale) specifies the maximum number of decimal digits that can be stored to the right of the decimal point. The scale must be a value from 0 to P,

You can specify the number of decimal places only after specifying the precision. The default number of decimal places is 0; Therefore, 0 & lt= s<= p。 The maximum storage size is based on precision

And change. For example, decimal (10,5) means there are 10 digits in total, including 5 integers and 5 decimals.

2.3.4 date time type

Datetime: month, day, hour, minute and second millisecond

Smalldatetime: mm / DD / yyyy, H / min / S

Date: mm / DD / yy < strong > 2.3.5 < / strong > < strong > bit data type < / strong >

Bit: called bit data type, only 0 or 1 is taken as the value, and the length is 1 byte. It is used to judge whether the value is true or false

When a value other than 0 is entered, the system replaces it with 1.

Chapter III query analyzer

select*from 表名--查询表中所有的信息
select*from 表名where条件表达式--带条件查询
select*(可以换成单独的表中某个表名列名)from 表名where条件表达式--带条件查询

3.1. Database building

Syntax: create database database name

Database building case:

The code to create the database stuinfo2 is as follows.

use master 

go

--若存在数据库StuInfo2,则删除该库 

if exists(select * from sysdatabases where name='StuInfo2') 

begin 

drop database StuInfo2 

end 

go

--创建数据库 

create database StuInfo2 

on primary --主数据库文件 

( 

name='StuInfo2', 

filename='E:\Database\SophomoreYearDB\2020级\6班\StuInfo2.mdf', 

size=5MB, 

maxsize=unlimited, 

filegrowth=1MB 

)

log on --日志文件 

( 

name='StuInfo2_log', 

filename='E:\Database\SophomoreYearDB\2020级\6班\StuInfo2_log.ldf', 

size=5MB, 

maxsize=unlimited, 

filegrowth=1MB 

)

go

3.2. Table building

Syntax: create table table name

Table building cases:

On the basis of the above case code, continue to write the following code:

–Use database stuinfo2 to build tables

use StuInfo2

create table StuBase 

( 

StuNo bigint not null, 

StuName nvarchar(50) not null, 

Gender bit not null, 

IdCard char(18) not null, 

Age int not null, 

Remark nvarchar(200) null 

)

go

create table Score 

(

StuNo bigint not null, 

CSharpScore float not null 

)

go

3.3 constraints

3.3.1 seven common SQL Server constraints

(1) Primary key constraint: primary key (PK)

(2) Unique constraint: unique (UQ). Multiple unique constraints can exist in a table, but the same constraint cannot exist in a unique constraint

Value.

(3) Check constraint: check (CK), a custom constraint.

(4) Default value constraint: default (DF)

(5) Foreign key constraint: foreign key (FK)

(6) Non NULL constraint: not null

(7) The system automatically identifies which value to add each time. Previous table of identity (1,1)

It starts with 1, and the latter represents an increase of 1 each time.

3.4. Add five key constraint cases:

On the basis of the above case code, continue to write the following code:

3.4.1. Add primary key constraint

alter table StuBase add constraint PK_StuNo primary key(StuNo); 

3.4.2. Add default constraint

--alter table StuBase add constraint DF_Gender default(1) for Gender; 

alter table StuBase add constraint DF_Gender default 1 for Gender; 

3.4.3. Add unique constraint (right click any column in the table design interface and select “index / key” to view the added constraint)

alter table StuBase add constraint UQ_IdCard unique(IdCard); 

3.4.4. Add check constraint

alter table StuBase add constraint CK_Age check(Age>=18); 

3.4.5 adding foreign key constraints

alter table Score add constraint FK_StuNo foreign key(StuNo) references 

StuBase(StuNo); 

3.5. Delete constraint

alter table 表名 drop constraint 约束名;

alter table StuBase drop constraint d_Gender;

Unit 4

4.1 operators in T-SQL

4.1.1 comparison operator

< strong > operator < / strong > meaning

= be equal to

> greater than

< less than

>= Greater than or equal to

<= Less than or equal to

<& gt; Not equal to

! wrong

4.1.2 logical operators

< strong > logical expression < / strong > explain Examples

AND Logical and 1 and 1 =; 1 AND 0 = 0; 0 AND 0 = 0;

OR Logical or 1 or 1 = 1; 1OR 0 = 1; 0 OR 0 = 0;

NOT Logical non NOT 1 = 0; NOT 0 = 1;

4.2. Simple data query

4.2.1. Check all records in the table:

select * from 表名 

4.2.2 query with conditions:

select*from 表名 where 条件表达式 

4.3 delete table

语法:drop table 表名

4.4. Insert data

语法:insert  into 表名(列名列表)values(值列表)

Note: when inserting data, if there is a self incrementing column in the table, the value of this column will be automatically allocated by the system without the value given by the programmer.

4.5. Syntax of updating data line:

update 表名 set 列名=新值 where 列名=旧值

4.6. Delete data line:

delete from 表名 where 行名

delete from team where name=2;

–Truncate table executes faster and uses fewer system resources and transaction logs. You cannot delete the data in the primary key table of the primary key foreign key relationship. To delete the data in the primary key table, you need to use delete.

Truncate table Team。

Truncate table 表名。

Truncate table can delete all data in the foreign key table with primary foreign key relationship.

Truncate table Player

4.7. Insert multiple pieces of data at one time:

insert
select.(插入的数据).union

4.8. Multiple data lines at one time:

Copy the data in one table to another new data table, and use Select Into statement
–Copy the data rows in the team table to team2

select*into Team2 from Team;

4.9. Three methods to query students’ student number, name and gender

–Three ways to alias query columns

select '学号'=StuNo,Name '姓名',(case when Gender=1 then '男' else '女' end) as '性别'
from Baselnfo ;

select  '学号'=StuNo,'姓名'=Name ,'性别'=(case when Gender=1 then '男' else '女' end)  from Baselnfo;

select StuNo '学号',Name '姓名',(case when Gender=1 then '男' else '女' end) '性别' from Baselnfo;

select  StuNo as '学号',Name as '姓名',(case when Gender=1 then '男' else '女' end) as '性别'  from Baselnfo;

select Name+'-'+Idcard as'姓名-身份证号码'
from Baselnfo;

Unit 5

5.1. Relational operators:

<& gt;、!=

5.1.1. Logical operators:

and、or、not

5.2. Existing information in the filter table

5.2.1. Filter duplicate rows (for single column filtering)

select distinct Gender from  Baselnfo;

5.2.2. Filter duplicate rows (for multi column filtering)

select distinct Gender,Name from  Baselnfo;

select top 3 *from Baselnfo

select top 3 *from Baselnfo where 列名

5.3. Use the top keyword to display only the first several records

top 整数:前多少条记录 

top 整数 percent:前百分之多少条记录

5.4. Use the positive order ranking in the order by query table:

select top 2 * from StuScore where Subject='C#'  order by Score desc ;

5.5. Query links are displayed together

select cast(StuNo as varchar) +'-'+Name as'学号-姓名'
from Baselnfo;

5.6. Query all data except this:

select*from  表名where 列名<> '列名中的数据'

select*from  表名where 列名!='列名中的数据'

5.7. How much to less data to query:

select*from StuScore where Subject='C#' and Score >= 40 and Score<=90;

5.5. Filter list operators for query results

In: used to match the list of values

not in

5.5.1. In is used to make equivalent comparison with multiple specific values, and any one can be satisfied

select*from Baselnfo where StuNo  in(20209130501);

5.5.2. Not in: match with data not equal to these specific values

select*from Baselnfo where StuNo not in(20209130501,20209130503,20209130504);

5.6. Fuzzy query

Syntax: like is used with wildcards (%,, [], [^])

--查询姓‘哈’的学生信息
select*from Baselnfo where Name like '哈%';

select*from 表名 where 列名 like '查询的名字';

--查询不姓‘哈’的学生信息
select*from Baselnfo where Name not like '哈%';

select*from 表名where 列名 not like '查询的 字';

--查询姓名只有两个字,且第二个字不为‘哈’的同学
select*from Baselnfo where Name like'_[^哈]';

select*from 表名where 列名 not like '查询的 字';——下划线代表占一个字节

5.8. The order by sentence can be sorted according to multiple fields

–Query the score information, sort the c# scores in descending order, and sort the same scores in ascending order by student number

select  * from StuScore where Subject='SQlSever'  order by Score desc, StuNo asc;--降序
select  * from StuScore where Subject='SQlSever'  order by Score desc ;--降序

select  * from 表名 where 列名='列里面添加的数据'  order by 列名desc ;--降序

5.9 aggregation function

Sum(): used to count the sum of numeric fields. It can only be used for numeric fields, and null values will be ignored.

AVG (): used to calculate the average value of a numeric field. The null value in this field will be ignored in the calculation process.

Max(): used to return the maximum value of the field, which will be ignored when null value is encountered during calculation.

Min(): used to return the minimum value of the field, which will be ignored when null value is encountered during calculation.

Count(): counts the number of record lines.

5.9.1 total score

select '总分'=SUM(Score) from StuScore;

select '总分'=SUM(列名) from 表名;

5.9.2 calculate the total score of each course (each, each)

–In a query statement, you can use any number of fields to group the records in the result field. Each output field in the field list must appear in the group by sentence or be used in an aggregate function.

select Subject '科目',SUM(Score)'总分' from StuScore group by Subject;

select 列名'科目',SUM(列名)'总分' from 表名group by 列名;

5.9.3 calculate the average score of each section and arrange it in descending order

select Subject '科目',AVG(Score)'平均分' from StuScore group by Subject order by AVG(Score) desc;

5.9.4 seek the highest score of each subject

select Subject '科目',MAX(Score)'最高分' from StuScore group by Subject;

5.9.5 seek the lowest score of each subject

select Subject '科目',MIN(Score)'最低分' from StuScore group by Subject;

5.9.6 reference number of each section

select Subject '科目',count(*)'实际参加考试的人数' from StuScore group by Subject;

5.10. Having clause: filtering

select Subject '科目',AVG(Score)'平均分>=70' from StuScore group by Subject having AVG(Score)>=70 order by AVG(Score) desc;

Chapter VI

If a query needs to operate on multiple tables, it is called join query. The result set or result table of join query is called join between tables

Connect.

Join query is divided into internal join query, self join query, external join query and compound conditional join query.

6.1. Internal (internal) links

select bi.StuNo,Name,Gender,Age,Idcard,Subject,Score
from Baselnfo bi inner join StuScore ss on bi.StuNo= ss.StuNo

–The two table names are separated by commas, and the where clause is used to write the associated fields

select bi.StuNo,Name,Gender,Age,IdCard,Subject,Score
from Baselnfo bi inner join StuScore ss
on bi.StuNo=ss.StuNo

6.2. External connection

6.2.1、左外联接:left [outer] join

–Features: the table on the left of the left join is the master table, and the table on the right is the slave table. All records of all records in the master table are preferentially displayed. If there is no matching record in the slave table, it is filled with “null”.

select bi.StuNo,Name,Gender,Age,IdCard,Subject,Score
from Baselnfo bi left join StuScore ss
on bi.StuNo=ss.StuNo

6.2.2、右外联接:right [outer] join

–Features: the table on the right side of the right join is the master table, and the table on the left is the slave table. All records of all records in the master table are preferentially displayed. If there is no matching record in the slave table, it is filled with “null”.

select bi.StuNo,Name,Gender,Age,IdCard,Subject,Score
from Baselnfo bi right join StuScore ss on bi.StuNo=ss.StuNo;

6.3、交叉联接:cross [outer] join

–Features: the number of records in the query result set is equal to the product of the number of records in the left and right tables of the cross join.

select count(*)
from Baselnfo cross join StuScore

select count(*)from Baselnfo--9
select count(*) from StuScore--7

6.4. Self connection

Joins can be made not only between tables, but also between a table and itself. This kind of join is called self join, and the corresponding query is called self join

Is a self join query.

6.5. More complex join query

SELECT 字段列表 FROM 表1 INNER JOIN 表2 ON 条件表达式1 INNER JOIN 表3 ON 条件表达式2 

6.6 set operation

Union and union all — union operation

Intersect — intersection operation

Except — subtraction

6.6.1、 使用UNION和UNION ALL进行并集运算

Union and union all combine the two sets of queries. The only difference is that union does not allow duplicate columns

Duplicate columns are allowed for all.

The title of the result set returned by union is obtained only from the first query.

6.6.2. Use intersect for intersection operation

Intersection also operates on multiple result sets. Intersection takes out the same records in the two result sets to form a new set.

6.6.3. Use Excel to perform subtraction operation

Subtractive set refers to a new set formed by comparing two result sets and removing the intersection part of the result set before the excel keyword.

Note: the final result of subtraction is closely related to the order of the two result sets.

Microsoft Visio 2010软件