Sql Server 基础知识

Sql(Structured Query Language) server基础

1.Sql server的组成： 

• 主要数据库文件：.mdf 特点：有且只有一个
• 次要数据库文件：.ndf 特点：任意个
• 日志数据库文件：.ldf 特点：至少一个

2.操作数据库： 

• 创建数据库：create databse 库名
• 查看所有数据库：exec sp_helpdb
• 查看当前数据库：exec sp_helpdb 库名
• 使用数据库：use 库名
• 删除数据库：drop database 库名 ps:正在使用的数据库无法删除

3.表的结构： 字段 数据类型(属性)

4.数据类型： 

• 整型
  • 微整型 tinying 1个字节
  • 小整型 smallint 2个字节
  • 整型 int 4个字节
  • 大整型 bigint 8个字节
• 浮点型
  • float 不能精确存储数值
  • real 不能精确存储数值
  • decimal(numeric ) 同义，用于精确存储数值
• 字符型
  • char 长度在1到8000之间 定长字符数据
  • varchar 长度在1到8000之间 变长字符数据
  • text 存储可变长度的非Unicode数据，最大长度为2^31-1(2,147,483,647)个字符
• 时间日期型：datetime 年月日时分秒毫秒
• 货币型：money 精确到货币单位的千分之十。存储大小为 8 个字节。存储形式为12345.67

sql server表

1.创建表的语法： 

create table 表名
(
    字段名称1 数据类型[属性],
    字段名称2 数据类型[属性],
    ...  
)

2.查看所有表语法： exec sp_help

3.查看当前表语法： exec sp_help 表名

4.修改表结构： 

• 增加一列语法：alter table 表名 add 字段名 数据类型
• 删除一列语法：alter table 表名 drop column 字段名
• 修改一列语法：alter table 表名 alter column 字段名 数据类型
• 删除表语法：drop table 表名

5.操作表数据： 

• 完全插入数据语法：insert into 表名(字段名1,字段名2...) values(值1,值2...)
• 省略插入数据语法：insert into 表名 values(值1,值2...)
• 部分插入数据语法：insert into 表名(字段名1,字段名2...) values(值1,值2...)
• 多行插入数据语法：insert into 表名 values(值1,值2...),(值1,值2...)
• 查看所有记录语法：select * from 表名
• 查看部分记录语法：select 字段名1,字段名2... from 表名
• 修改一条记录语法：update 表名 set 字段名=值 [where条件]
• 删除一条记录语法：delete from 表名 where 条件
• 清空表所有记录
• delete from 表名
• truncate from 表名
• SQL Server中truncate、delete和drop的异同点，相同点：
  truncate和不带where子句的delete，以及drop都会删除表内的数据
  不同点：
  1. truncate和delete只删除数据不删除表的结构(定义)
  drop语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index);依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。
  2. delete语句是数据库操作语言(dml)，这个操作会放到rollback segement中，事务提交之后才生效;如果有相应的trigger，执行的时候将被触发。
  truncate、drop是数据库定义语言(ddl)，操作立即生效，原数据不放到rollback segment中，不能回滚，操作不触发trigger。
  3.delete语句不影响表所占用的extent，高水线(high watermark)保持原位置不动。
  显然drop语句将表所占用的空间全部释放。
  truncate语句缺省情况下见空间释放到minextents个extent，除非使用reuse storage;truncate 会将高水线复位(回到最开始)。
  4.速度，一般来说: drop> truncate > delete
  5.安全性：小心使用 drop和truncate，尤其没有备份的时候.否则哭都来不及。
  使用上,想删除部分数据行用delete，注意带上where子句. 回滚段要足够大.
  想删除表,当然用drop
  想保留表而将所有数据删除，如果和事务无关，用truncate即可。如果和事务有关,或者想触发trigger,还是用delete。
  如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

6.标识列 identity(初值,增值)： 

• 定义：能唯一区分表中的每一条记录，且该属性是自动增长的
• 特点
  • 一个表中有且只有一个标识列
  • 标识列不能编辑，不能更新
  • 标识列数据类型只能是整型
  • 标识列不为null
  • 标识列不重复
• 作用：保证数据完整性

7.运算符： 

sql server的运算符与其他语言不同，这里列举三个

• &&-----and
• ||-----or
• !-----not

8.六大约束： 

• 检查约束 check 语法 check(条件)
• 默认约束 default 语法 default'默认语句'
• 非空约束 not null
• 唯一性约束 unique
• 主键 primary key
  • 主键的值不能重复
  • 一个表中有且只有一个主键
  • 外键与主键相对应
  • 主键类型可以是整型、字符型
  • 语法：字段名 数据类型 primary key
• 外键 foreign key references
• 与主键相对应
• 外键的值一定在主键范围之内
• 外键的值可以重复
• 语法：字段名 数据类型 foreign key references 主表名
• 注意事项：
• 在操作主表从表时：先删除从表，再删除主表，删除数据，也要先从后主
• 在建立表时：要先创建主表，再创建从表，插入数据，先主后从

sql server查询

1.查询符合条件的数据： select 字段名 from 表名 [where 条件]

2.之间的

• and ... or
• between ... and
• 例句：查询23岁到25岁之间的学生
  • select * from student where age>=23 and age<=25
  • select * from student where age between 23 and 25

3.不显示重复项：distinct select distinct 字段名 from 表名

4.前几项 top select top n * from 表名

5.排序 order by + 字段名 asc升序 desc降序(默认情况下为升序) select * from student where age>25 order by name desc

6. is not null/null select * from 表名 where 字段名 is null

7.列起别名as select id as 学生编号, name as 学生姓名 from student as a 注：实际语法中as可以省略

8.高级查询(模糊查询) like select 字段名 from 表名 where 字段名 like '通配符 值 通配符'

sql server 通配符

• % 任意字符
• []范围内任意字符
• [^]非范围内任意字符

9.联合查询 join

• 交叉查询：select 字段名 from 表1 cross join 表2 [where 条件]
• 内连接查询：select 字段名 from 表1 inner join 表2 on 联合条件 [where 条件]
• 外连接
  • 左外连接：select 字段名 from 表1 left join 表2 on 联合条件 [where 条件]
  • 右外连接：select 字段名 from 表1 right join 表2 on 联合条件 [where 条件]
• 全外连接：select 字段名 from 表1 full join 表2 on 联合条件 [where 条件]
• 多表连接：select 字段名 from 表1 inner join 表2 on 联合条件 inner join 表3 on 联合条件 [where 条件]

10.嵌套查询(嵌套查询都可以用联合查询完成)，显示的字段在一个表中，条件在另外一个表中

• in() 在...范围之内的
• not in() 不在...范围之内的
• exists 存在
• not exists 不存在

11. 分组 group by

系统函数

1.统计(聚合)函数

• sum() 求和
• avg() 平均值
• max() 最大值
• min() 最小值
• count() 数量

2.日期函数

• getDate()获取当前时间
• Dateadd() 增加时间

• datediff(datepart,startdate,enddate)

  startdate 和 enddate 参数是合法的日期表达式。

  datepart 参数可以是下列的值：

  datepart	缩写
  年	yy, yyyy
  季度	qq, q
  月	mm, m
  年中的日	dy, y
  日	dd, d
  周	wk, ww
  星期	dw, w
  小时	hh
  分钟	mi, n
  秒	ss, s
  毫秒	ms
  微妙	mcs
  纳秒	ns

• datepart(datepart,date)函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等,datepartd参数可以参见上表
• datename(datepart,date)傳回代表指定 date 之指定 datepart 的字元字串

3.数学函数

• abs()取绝对值
• round()四舍五入
• floor()函数返回小于或等于所给数字表达式的最大整数
• ceiling()函数返回大于或等于所给数字表达式的最小整数
• sqrt()开平方根
• 关于abs()/round()/...这些数学函数的demo可以在我的上篇博客中找到

4.字符串函数

• left()左截串
• right()右截串
• ltrim()去左空格
• rtrim()去右空格
• replace(字符串,旧字符串,新字符串) 替换
• substring(字符串,位置,长度) 截字符串 ps:sql中字符串下标从1开始
• reverse()反转
• len()长度
• upper()转大写
• lower()转小写

T-sql

1.声明变量语法： declare @变量名 数据类型

给变量赋值

• set @变量名=值
• select @变量名=值

输出变量 select @变量名

ps：若想输出变量、声明变量、变量赋值和变量输出三条语句需一起执行

2.编程语句

• begin...end
• if...else

视图

1.创建视图

create view 视图名称
as
sql中查询语句

2.使用视图 select * from 视图名

3.查看视图 exec sp_help

4.查看视图内容 exec sp_helptext 视图名

5.修改视图 alter view 视图名 as select * from 表名 [where条件]

6.删除视图 drop view 视图名

7.修改视图 update 视图名 set 字段名=值 [where条件]

存储过程/触发器/事务

1.sql server 存储过程

create proc | procedure pro_name
    [{@参数数据类型} [=默认值] [output],
     {@参数数据类型} [=默认值] [output],
     ....
    ]
as
        select ......

2.sql server 触发器

• insert触发器
• update触发器
• delete触发器

3.sql server 事务