SQL Server 2016（基本概念和命令）

1、文件类型。

【1】主数据文件：数据库的启动信息。扩展名为".mdf"。

【2】次要（辅助）数据文件：主数据之外的数据都是次要数据文件。扩展名为".ndf"。

【3】事务日志文件：包含恢复数据库的所有事务日志信息。扩展名为".ldf"。

【4】文件流数据文件：存储文档、图片、音频、视频等。

2、表的基本感念。

行：每行成为一条"记录"或"元组"，用于描述一个对象的信息。

列：每列称为一个"字段"或"属性"，用于描述对象的一个属性。

3、主键、外键。

主键：键，即关键字。主键由一个或多个字段组成，其具有唯一性，不允许取空值（NULL）。一个表只能有一个主键。

外键：一个关系数据库通常包含多个表，通过外键可以使多个表关联起来。

4、常用数据类型。

精确数字：

        int：SQL Server中的主要数据类型。（整数型）

        bit：只有两种可以能值。只能是0、1或NULL（空值），Yes或No、True或False。（整数型）

        money：用来表示钱和货币值。

        

日期和时间：

        date：用于表示日期。

        time：用于表示一天中的某个时间。

        datetime：用于表示日期和时间。

        

字符串：

        char：存储固定长度。

        varchar：存储可变长度。

        

Unicode字符串：

        nchar：存储固定长Unicode字符型数据。

        nvarchar：存储变长Unicode字符型数据。

5、创建表。

创建表的基本语法：

create table 表名
(
    列名1 数据类型(大小)
    列名2 数据类型(大小)
    列名3 数据类型(大小)
    ......
)

identity(1,1)：表示该列为标识符，种子和增量值都为1。

not null：表示该列不允许空值。

primary key：表示该列为主键列，并且不为空。

check()：表示该列创建约束，括号内为约束条件。

命令参考：

##创建表employee
create table employee
(
编号 int identity(1,1) not null,
姓名 nvarchar(50) not null,
身份证号 varchar(18) primary key,
职务 nvarchar(50) not null,
出生日期 datetime not null,
基本工资 money not null check(基本工资>=0 and 基本工资<=100000)
)

##使用命令查看表employee的所有信息
select * from employee

6、删除表。语法：[ drop table 表名 ]

命令参考：

##删除表employee
drop table employee

7、修改表的结构。[ alter table 表名 ]

1、向表employee中添加一个"备注"列。 

添加表的基本语法：

alter table 表名
add 列名 数据类型(大小)

命令参考：

##向表中添加备注列
alter table employee
add 备注 nvarchar(2000)

8、修改列。 

修改列的数据类型：

alter table 表名

alter column 列名 数据类型(大小)

命令参考：

##修改备注列的长度为1000
alter table employee
alter column 备注 nvarchar(1000)

8、删除列。

删除列的基本语法：

alter table 表名

drop column 列名

命令参考：

##删除表中的备注列
alter table employee
drop column 备注

10、插入数据。语法：[ insert into 表名 (列名) values (列表值) ]

注意事项：

• 不能只插入半行或几列数据。

• 不能为标识符指定值，因为它的值是自动增长的。

• 插入的数据要符合check约束的要求。

• 如果指定不为空，则该列必须插入数据，否则将报错。

1、向表中插入一行数据。

命令参考：

 insert into employee (姓名,身份证号,职务,出生日期,基本工资)
values ('郭靖','111222333444555666','运维工程师','1995/1/1',8000)

11、 更新数据。语法：[ update 表名 set 列名=更新值 where 更新条件 ]

1、更新表中黄蓉的基本工资为11000；更新表中郭靖的工资为"9000"，备注为"勤奋好学"

命令参考：

update employee set 基本工资='11000' where 姓名='黄蓉'

update employee set 基本工资='9000',备注='勤奋好学' where 姓名='郭靖'

12、删除表中数据的两种方法。 （delete、truncate）

12.1、删除表中数据。语法：[ delete from 表名 where 删除条件 ] 

1、删除表中杨过的记录（行）；删除表中所有记录。

命令参考：

delete from employee where 姓名='杨过'  

delete from employee  

12.2、删除表中数据。[ truncate table 表名 ]（类似格式化表中数据）

【1】与delete删除数据不同的是，truncate类似格式化，删除所有记录标识符会重置为0。

【2】delete删除后可以通过事务日志恢复，而truncate不能。

1、删除表中的所有记录。

13、select查询。 

表达式：

        between：指定范围，使用"and"定义开始值和结束值。例：（1 and 10）

        in：是否在此范围内。例：in (10,20,30)

        like：模糊查询。例：like '66%'  //66开头的数据

        <>：不等于。

        !=：不等于，等同于<>。

通配符：

        '_'：表示单个字符。

        %：表示任意长度的字符串。

        [ ]：表示查找此范围内的数据。例：like '[C-P]'  //从C到P之间开头的数据

        [^]：表示不查找在此范围内的数据。例：like 'car[^afg]'  //查找以car开头但是后面的字母不为a、f、g的名字。

逻辑表达式：

        not：取反的操作。

        and：和、并且的意思，需要同时满足两个条件。

        or： 或的意思，只需要满足两个条件其中一个即可。

13.1、案例。

//查询表中的所有信息
select * from employee//查询表中的"姓名,职务,基本工资"列
select 姓名,职务,基本工资 from employee//查询表中运维工程师的姓名
select 姓名 from employee
where 职务='运维工程师'//查询表中基本工资在8000到10000之间的所有员工信息
select * from employee
where 基本工资 between 8000 and 10000//查询表中基本工资小于10000或大于20000的所有员工信息
select * from employee 
where 基本工资<10000 or 基本工资>20000//查询表中基本工资为8000、9000、10000的所有员工信息
select * from employee 
where 基本工资 in (8000,9000,10000)//查询表中身份证号以66开头的员工所有信息
select * from employee 
where 身份证号 like '66%'//查询表中姓杨的运维工程师的信息。
select * from employee 
where 姓名 like '杨%' and 职务='运维工程师'//查询表中备注不为空的所有信息
select * from employee 
where 备注 is not null//查询表中前5行的数据
select top 5 * from employee//查询表中姓名和身份证号列的信息，姓名和身份证号以别名"name"、"idcard"显示
select 姓名 as name,身份证号 as idcard from employee//查询表中的基本工资，并以降序显示（高到低）
select * from employee 
order by 基本工资 desc//查询表中的有哪些职务（去重）
select distinct 职务 from employee//查询表中的姓名（name）、身份证号、职务、基本工资信息，条件要满足身份证号第三位为0并且职务不是CTO，在降序显示
select 姓名 as name,身份证号,职务,基本工资 from employee
where 身份证号 like '__0' and 职务!='CTO'
order by 基本工资 desc

14、使用select查询后加入新表或查询后创建新表。

不需要事先创建新表

//查询employee表中的姓名、身份证号、职务信息后创建然后保存到new01表中
select 姓名,身份证号,职务 into new01 from employee

需要事先创建新表再能将查询到的信息保存到新表

//查询employee表中的姓名、职务、出生日期并且基本工资大于等于15000的员工信息，保存new02表中
insert into new02 (姓名,职务, 出生日期)
select 姓名,职务,出生日期 from employee
where 基本工资>=15000//查询employee表中的姓名、职务、出生日期信息保存到new03中，然后向new03中加入信息
select into new03 (姓名,职务,出生日期)
select '欧阳锋','人事经理','1988-09-09' union
select '一灯','财务经理','1977-07-07' union
select 姓名,职务,出生日期 from employee

15、在查询中使用函数。

系统函数：

字符串函数：

日期函数：

15.1、案例。

//查询运维工程师的姓名和基本工资，以固定格式显示
select '运维工程师 ' +姓名+' 的基本工资是： '+cast(基本工资 as varchar(10))+'元'
from employee
where 职务='运维工程师'//查询当前系统时间10天之后的时间
select dateadd(DD,10,getdate())//查询表中的表中所有人的姓名和年龄
select 姓名,datediff(YY,出生日期,getdate()) as 年龄
from employee//查询表中90后的员工姓名和出生年份
select 姓名,datename(YEAR,出生日期) as 出生日期
from employee
where 出生日期 between '1990-01-01' and '1999-12-31'//查询表中基本工资的总和
select sum(基本工资) as 总工资 
from employee//查询表中基本工资的平均值
select avg(基本工资) as 平均工资 
from employee//查询表中最高工资和最低工资的员工信息
select max(基本工资) as 最高工资,min(基本工资) as 最低工资 
from employee//查询表中的总行数
select count(*) as 总行数 
from employee//查询表中90后的总人数
select count(出生日期) as '90后人数'
from employee
where 出生日期>='1990-01-01'//查询表中各个职务的平均工资
select 职务,avg(基本工资) as 职务平均工资 
from employee
group by 职务//查询表各个职务的平均工资小于10000的信息
select 职务,avg(基本工资) as 职务平均工资
from employee
group by 职务
having avg(基本工资)<10000//查询表中各个职务平均工资小于10000并且姓名不是段誉
select 职务,avg(基本工资) as 职务总工资 
from employee
where 姓名 !='段誉' 
group by 职务
having avg(基本工资) <10000

数字函数：

15.2、案例。

//查询表中的平均工资并取整
select ceiling(avg(基本工资)) as 平均工资
from employee

15.3、函数综合案例。

查询未满30岁的员工的生日和年龄，并且计算出距离30岁的天数，最后用字符串拼接显示结果。

select
'员工 '+姓名+
' 的生日是'+convert(varchar(10),出生日期,111)+
', 现在年龄是 '+cast(datediff(YY,出生日期,getdate()) as varchar(10))+'岁'+
', 距离30岁还有 '+
cast(datediff(DD,getdate(),dateadd(YY,30,出生日期)) as varchar(10))+'天'
from employee
where datediff(YY,出生日期,getdate())<=30
order by 出生日期