当前位置：Gxlcms > 数据库问题 > （转）史上最全的MSSQL复习笔记

（转）史上最全的MSSQL复习笔记

时间：2021-07-01 10:21:17 帮助过：2人阅读

判断数据库文件是否已经存在 ：数据库的记录都存储在master库中的sysdatabases表中 --自动切换当前数据库 --使用代码开启外围应该配置器 exec sp_configure ‘show advanced options‘ ,1 RECONFIGURE exec sp_configure ‘xp_cmdshell‘,1 RECONFIGURE --自定义目录 xp_cmdshell可以创建出目录 ‘mkdir f:\project‘：指定创建目录 exec xp_cmdshell ‘mkdir f:\project‘ use master --exists 函数判断（）中的查询语句是否返回结果集，如果返回了结果集则得到true,否则得到false if exists( select * from sysdatabases where name=‘School‘) drop database School --删除当前指定名称的数据库 create database School on primary ( name=‘School_data‘,--逻辑名称.说明最多能够存储100mb数据，如果没有限制就可以将硬盘存储满 size=3mb,--初始大小 maxsize=100mb,--最大容量 filegrowth=10%,--文件增长一次增长10% filename=‘f:\project\School_data.mdf‘ ), --创建文件组 filegroup mygroup ( name=‘School_data1‘,--逻辑名称.说明最多能够存储100mb数据，如果没有限制就可以将硬盘存储满 size=3mb,--初始大小 maxsize=100mb,--最大容量 filegrowth=10%,--文件增长一次增长10% filename=‘F:\qiyi\School_data1.ndf‘ ) log on ( name=‘School_log‘,--逻辑名称 size=3mb,--初始大小 --maxsize=100mb,--最大容量 filegrowth=10%,--文件增长一次增长10% filename=‘f:\project\School_log.ldf‘ ), ( name=‘School_log1‘,--逻辑名称 size=3mb,--初始大小 --maxsize=100mb,--最大容量 filegrowth=10%,--文件增长一次增长10% filename=‘F:\qiyi\School_log1.ldf‘ ) View Code

3.创建数据表

语法：
create table 表名
(
字段名称 字段类型 字段特征(是否为null,默认值 标识列 主键 唯一键 外键 check约束),
字段名称 字段类型 字段特征(是否为null,默认值 标识列 主键 唯一键 外键 check约束)
)
创建老师表Teacher :Id、Name、Gender、Age、Salary、Birthday

  1. <span style="color: #0000ff;">use</span><span style="color: #000000;"> School
  2. </span><span style="color: #0000ff;">if</span> <span style="color: #808080;">exists</span>(<span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> sysobjects <span style="color: #0000ff;">where</span> name<span style="color: #808080;">=</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Classes</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
  3.   </span><span style="color: #0000ff;">drop</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> Classes
  4. </span><span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> Classes
  5. (
  6.  Classid </span><span style="color: #0000ff;">int</span> <span style="color: #ff00ff;">identity</span>(<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">),
  7.  ClassName </span><span style="color: #0000ff;">nvarchar</span>(<span style="color: #800000; font-weight: bold;">50</span>) <span style="color: #808080;">not</span> <span style="color: #0000ff;">null</span><span style="color: #000000;"> 
  8. )
  9. </span><span style="color: #0000ff;">if</span> <span style="color: #808080;">exists</span>(<span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> sysobjects <span style="color: #0000ff;">where</span> name<span style="color: #808080;">=</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">teacher</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
  10.   </span><span style="color: #0000ff;">drop</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> teacher
  11. </span><span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> Teacher
  12. (
  13.  Id </span><span style="color: #0000ff;">int</span> <span style="color: #ff00ff;">identity</span>(<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #800000; font-weight: bold;">1</span>),<span style="color: #008080;">--</span><span style="color: #008080;">可以同时创建多个特征，用空格 分隔开。 identity是标识列，第一个参数是种子，第二个是增量</span>
  14. Name <span style="color: #0000ff;">nvarchar</span>(<span style="color: #800000; font-weight: bold;">50</span>)  <span style="color: #808080;">not</span> <span style="color: #0000ff;">null</span>,<span style="color: #008080;">--</span><span style="color: #008080;"> not null标记它的值不能为null--不能不填写</span>
  15. ClassId <span style="color: #0000ff;">int</span> <span style="color: #808080;">not</span> <span style="color: #0000ff;">null</span><span style="color: #000000;">,
  16.  Gender </span><span style="color: #0000ff;">bit</span> <span style="color: #808080;">not</span> <span style="color: #0000ff;">null</span><span style="color: #000000;">,
  17. Age </span><span style="color: #0000ff;">int</span><span style="color: #000000;">   ,
  18. Salary </span><span style="color: #0000ff;">money</span>, <span style="color: #008080;">--</span><span style="color: #008080;">如果不标记为 not null.那么就相当于标记了null</span>
  19. Birthday <span style="color: #0000ff;">datetime</span><span style="color: #000000;">  
  20. )</span>

4.数据完整性约束

实体完整性：实体就是指一条记录。这种完整性就是为了保证每一条记录不是重复记录。是有意义的
-- 主键：非空和唯一.一个表只有一个主键，但是一个主键可以是由多个字段组成的 组合键
-- 标识列：系统自动生成，永远不重复
-- 唯一键：唯一，但是可以为null,只能null一次

域完整性：域就是指字段，它是为了保证字段的值是准和有效，合理值
-- 类型 是否null，默认值，check约束，关系

自定义完整性：
-- check约束 ， 存储过程 触发器

引用完整性：一个表的某个字段的值是引用自另外一个表的某个字段的值。引用的表就是外键表，被引用的表就是主键表
-- 1.建立引用的字段类型必须一致
-- 2.建立引用的字段的意义一样
-- 3.建立主外键关系的时候选择 外键表 去建立主外键关系
-- 4.建立主外键关系的字段在主表中必须是主键或者唯一键
-- 5.对于操作的影响 ：
-- 1.在添加数据时，先添加主键表再添加外键表数据
-- 2.在删除的时候先外键表数据再删除主键表数据


-- 级联的操作：不建议使用：会破坏数据完整性
-- 不执行任何操作：该报错就报错，该删除就删除
-- 级联：删除主表记录，从表引用该值的记录也被删除
-- 设置null:删除主表记录，从表对应的字段值设置为null,前提是可以为null
-- 设置为default:删除主表记录，从表对应的字段值设置为default,前提是可以为default

主键约束（PK Primary key） 唯一键约束(UQ unique) 外键约束(FK foreign key) 默认值约束(DF default) check约束(CK check)

语法：
alter table 表名
add constraint 前缀_约束名称 约束类型 约束说明（字段 关系表达式 值)

  1. <span style="color: #0000ff;">use</span><span style="color: #000000;"> School
  2. </span><span style="color: #0000ff;">if</span> <span style="color: #808080;">exists</span>(<span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> sysobjects <span style="color: #0000ff;">where</span> name<span style="color: #808080;">=</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">PK_Classes_Classid</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
  3.  </span><span style="color: #0000ff;">alter</span> <span style="color: #0000ff;">table</span> classes  <span style="color: #0000ff;">drop</span> <span style="color: #0000ff;">constraint</span><span style="color: #000000;"> PK_Classes_Classid
  4. </span><span style="color: #0000ff;">alter</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> classes 
  5. </span><span style="color: #0000ff;">add</span> <span style="color: #0000ff;">constraint</span> PK_Classes_Classid <span style="color: #0000ff;">primary</span> <span style="color: #0000ff;">key</span><span style="color: #000000;">(classid)
  6. </span><span style="color: #008080;">--</span><span style="color: #008080;">为id添加主键</span>
  7. <span style="color: #0000ff;">alter</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> teacher 
  8. </span><span style="color: #0000ff;">add</span> <span style="color: #0000ff;">constraint</span> PK_teacher_id <span style="color: #0000ff;">primary</span> <span style="color: #0000ff;">key</span><span style="color: #000000;">(id)
  9. </span><span style="color: #008080;">--</span><span style="color: #008080;">为name添加唯一键</span>
  10. <span style="color: #0000ff;">alter</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> teacher
  11. </span><span style="color: #0000ff;">add</span> <span style="color: #0000ff;">constraint</span> UQ_Teacher_Name <span style="color: #0000ff;">unique</span><span style="color: #000000;">(Name)
  12. </span><span style="color: #008080;">--</span><span style="color: #008080;">同时创建salary的默认约束和age的check约束</span>
  13. <span style="color: #0000ff;">alter</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> teacher
  14. </span><span style="color: #0000ff;">add</span> <span style="color: #0000ff;">constraint</span> DF_Teacher_Salary <span style="color: #0000ff;">default</span>(<span style="color: #800000; font-weight: bold;">5000</span>) <span style="color: #0000ff;">for</span><span style="color: #000000;"> salary,
  15. </span><span style="color: #0000ff;">constraint</span> CK_Teacher_Age <span style="color: #0000ff;">check</span>(age<span style="color: #808080;">></span><span style="color: #800000; font-weight: bold;">0</span> <span style="color: #808080;">and</span> age<span style="color: #808080;"><=</span><span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">)
  16. </span><span style="color: #008080;">--</span><span style="color: #008080;">为teacher表的classid字段创建主外键</span>
  17. <span style="color: #0000ff;">if</span> <span style="color: #808080;">exists</span>(<span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> sysobjects <span style="color: #0000ff;">where</span> name<span style="color: #808080;">=</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">FK_Teacher_Classes_Classid</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
  18.  </span><span style="color: #0000ff;">alter</span> <span style="color: #0000ff;">table</span> teacher  <span style="color: #0000ff;">drop</span> <span style="color: #0000ff;">constraint</span><span style="color: #000000;"> FK_Teacher_Classes_Classid
  19. </span><span style="color: #0000ff;">alter</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> teacher
  20. </span><span style="color: #0000ff;">with</span> <span style="color: #0000ff;">nocheck</span> <span style="color: #008080;">--</span><span style="color: #008080;">不检查现有数据</span>
  21. <span style="color: #0000ff;">add</span> <span style="color: #0000ff;">constraint</span> FK_Teacher_Classes_Classid <span style="color: #0000ff;">foreign</span> <span style="color: #0000ff;">key</span>(classid) <span style="color: #0000ff;">references</span><span style="color: #000000;"> classes(classid)
  22. </span><span style="color: #008080;">--</span><span style="color: #008080;">on delete set default  级联操作</span><span style="color: #008080;">
  23. --</span><span style="color: #008080;">不执行任何操作：该报错就报错，该删除就删除  --no action --默认选择</span><span style="color: #008080;">
  24. --</span><span style="color: #008080;">级联：删除主表记录，从表引用该值的记录也被删除 --cascade</span><span style="color: #008080;">
  25. --</span><span style="color: #008080;">设置null:删除主表记录，从表对应的字段值设置为null,前提是可以为null   --set null</span><span style="color: #008080;">
  26. --</span><span style="color: #008080;">设置为default:删除主表记录，从表对应的字段值设置为default,前提是可以为default  --set default</span>

5.四中基本字符类型说明

  1. <span style="color: #008080;">--</span><span style="color: #008080;">len(参数) --获取指定参数内容的字符个数</span>
  2. <span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">LEN</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">abcd</span><span style="color: #ff0000;">‘</span>) 【<span style="color: #800000; font-weight: bold;">4</span><span style="color: #000000;">】运行结果
  3. </span><span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">LEN</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">中华人民共和国</span><span style="color: #ff0000;">‘</span>) 【<span style="color: #800000; font-weight: bold;">7</span><span style="color: #000000;">】<br>
  4. </span><span style="color: #008080;">--</span><span style="color: #008080;">DataLength(参数)：获取指定内占据的字节数--空间大小</span>
  5. <span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">DataLength</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">abcd</span><span style="color: #ff0000;">‘</span>) 【<span style="color: #800000; font-weight: bold;">4</span><span style="color: #000000;">】
  6. </span><span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">DataLength</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">中华人民共和国</span><span style="color: #ff0000;">‘</span>) 【<span style="color: #800000; font-weight: bold;">14</span><span style="color: #000000;">】<br>
  7. </span><span style="color: #008080;">--</span><span style="color: #008080;">char类型：当空间分配后，不会因为存储的内容比分配的空间小就回收分配的空间。但是如果存储的内容超出了指定的空间大小，就会报错，</span><span style="color: #008080;">当你存储的内容的长度变化区间不大的时候可以考虑使用char</span>
  8. <span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">LEN</span>(<span style="color: #0000ff;">char</span>) <span style="color: #0000ff;">from</span> CharTest 【<span style="color: #800000; font-weight: bold;">2</span><span style="color: #000000;">】
  9. </span><span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">DataLength</span>(<span style="color: #0000ff;">char</span>) <span style="color: #0000ff;">from</span> CharTest 【<span style="color: #800000; font-weight: bold;">10</span><span style="color: #000000;">】<br>
  10. </span><span style="color: #008080;">--</span><span style="color: #008080;">varchar  var--变化的：当你存储的内容小于分配的空间的时候，多余的空间会自动收缩。但是如果存储的内容超出了指定的空间大小，就会报错 当存储的内容波动区间比较大时候使用varchar</span>
  11. <span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">LEN</span>(<span style="color: #0000ff;">varchar</span>) <span style="color: #0000ff;">from</span> CharTest 【<span style="color: #800000; font-weight: bold;">2</span><span style="color: #000000;">】
  12. </span><span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">DataLength</span>(<span style="color: #0000ff;">varchar</span>) <span style="color: #0000ff;">from</span> CharTest 【<span style="color: #800000; font-weight: bold;">2</span><span style="color: #000000;">】<br>
  13. </span><span style="color: #008080;">--</span><span style="color: #008080;">nchar--  n代表它是一个unicode字符。规定不管什么样的字符都占据两个字节。  char:空间是固定的</span>
  14. <span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">LEN</span>(<span style="color: #0000ff;">nchar</span>) <span style="color: #0000ff;">from</span> CharTest 【<span style="color: #800000; font-weight: bold;">10</span><span style="color: #000000;">】
  15. </span><span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">DataLength</span>(<span style="color: #0000ff;">nchar</span>) <span style="color: #0000ff;">from</span> CharTest 【<span style="color: #800000; font-weight: bold;">20</span><span style="color: #000000;">】<br>
  16. </span><span style="color: #008080;">--</span><span style="color: #008080;">nvarchar  n  var  char </span>
  17. <span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">LEN</span>(<span style="color: #0000ff;">nvarchar</span>) <span style="color: #0000ff;">from</span> CharTest 【<span style="color: #800000; font-weight: bold;">2</span><span style="color: #000000;">】
  18. </span><span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">DataLength</span>(<span style="color: #0000ff;">nvarchar</span>) <span style="color: #0000ff;">from</span> CharTest 【<span style="color: #800000; font-weight: bold;">4</span>】

 技术分享

6.SQL基本语句

数据插入
调用方法 一 一对应原则：类型对应，数量对应，顺序对应
语法： 形参 实参
insert into 表名([字段列表]) values(值列表) --数据必须要符合数据完整性
插入操作是单个表的操作
插入操作insert一次只能插入一条记录

技术分享 
  1. <span style="color: #0000ff;">use</span><span style="color: #000000;"> School
  2. </span><span style="color: #008080;">--</span><span style="color: #008080;">插入teacher所有字段的数据.如果在表后没有指定需要插入的字段名称，那么就默认为所有字段添加值</span><span style="color: #008080;">
  3. --</span><span style="color: #008080;">但是一定需要注意的是：标识列永远不能自定义值--不能人为插入值</span><span style="color: #008080;">
  4. --</span><span style="color: #008080;">仅当使用了列列表并且 IDENTITY_INSERT 为 ON 时，才能为表‘Teacher‘中的标识列指定显式值。</span>
  5. <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> Teacher <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">张三</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">5</span>,<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #800000; font-weight: bold;">30</span>,<span style="color: #800000; font-weight: bold;">4000</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">1984-9-11</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
  6. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> Teacher(Name,ClassId,Gender,Age,Salary,Birthday) <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">张三</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">5</span>,<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #800000; font-weight: bold;">30</span>,<span style="color: #800000; font-weight: bold;">4000</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">1984-9-11</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
  7. </span><span style="color: #008080;">--</span><span style="color: #008080;">不为可以为null的字段插入值  :可以null的字段可以不赋值 </span><span style="color: #008080;">
  8. --</span><span style="color: #008080;">列名或所提供值的数目与表定义不匹配</span>
  9. <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> Teacher(Name,ClassId,Gender,Age,Salary) <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">李四</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">5</span>,<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #800000; font-weight: bold;">30</span>,<span style="color: #800000; font-weight: bold;">4000</span><span style="color: #000000;">)
  10. </span><span style="color: #008080;">--</span><span style="color: #008080;">非空字段一定需要赋值 ：不能将值 NULL 插入列 ‘Gender‘，表 ‘School.dbo.Teacher‘；列不允许有 Null 值。INSERT 失败</span>
  11. <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> Teacher(Name,ClassId,Age,Salary) <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">李四</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">5</span>,<span style="color: #800000; font-weight: bold;">30</span>,<span style="color: #800000; font-weight: bold;">4000</span><span style="color: #000000;">)
  12. </span><span style="color: #008080;">--</span><span style="color: #008080;">为有默认值的字段插入值：</span><span style="color: #008080;">
  13. --</span><span style="color: #008080;">1.不写这一列让系统自动赋值</span>
  14. <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> Teacher(Name,ClassId,Gender,Age) <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">王五</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">5</span>,<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #800000; font-weight: bold;">30</span><span style="color: #000000;">)
  15. </span><span style="color: #008080;">--</span><span style="color: #008080;">指定 null或者default</span>
  16. <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> Teacher(Name,ClassId,Gender,Age,Salary,Birthday) <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">赵六</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">5</span>,<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #800000; font-weight: bold;">30</span>,<span style="color: #0000ff;">default</span>,<span style="color: #0000ff;">null</span><span style="color: #000000;">)
  17. </span><span style="color: #008080;">--</span><span style="color: #008080;">数据必须完全符合表的完整性约束</span>
  18. <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> Teacher(Name,ClassId,Gender,Age,Salary,Birthday) <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">赵六1</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">5</span>,<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #800000; font-weight: bold;">300</span>,<span style="color: #0000ff;">default</span>,<span style="color: #0000ff;">null</span><span style="color: #000000;">)
  19. </span><span style="color: #008080;">--</span><span style="color: #008080;">任意类型的数据都可以包含在‘‘以内,     不包括关键字</span>
  20. <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> Teacher(Name,ClassId,Gender,Age,Salary,Birthday) <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">马鹏飞</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">5</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">0</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">15</span><span style="color: #ff0000;">‘</span>,<span style="color: #0000ff;">default</span>,<span style="color: #0000ff;">null</span><span style="color: #000000;">)
  21. </span><span style="color: #008080;">--</span><span style="color: #008080;">但是字符串值如果没有包含在‘‘以内.会报错   列名 ‘兰鹏‘ 无效。</span>
  22. <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> Teacher(Name,ClassId,Gender,Age,Salary,Birthday) <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">兰鹏</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">5</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">0</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">15</span><span style="color: #ff0000;">‘</span>,<span style="color: #0000ff;">default</span>,<span style="color: #0000ff;">null</span><span style="color: #000000;">)
  23. </span><span style="color: #008080;">--</span><span style="color: #008080;">但是数值组成的字符串可以不使用‘‘包含</span>
  24. <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> Teacher(Name,ClassId,Gender,Age,Salary,Birthday) <span style="color: #0000ff;">values</span>(<span style="color: #800000; font-weight: bold;">123</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">5</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">0</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">15</span><span style="color: #ff0000;">‘</span>,<span style="color: #0000ff;">default</span>,<span style="color: #0000ff;">null</span><span style="color: #000000;">)
  25. </span><span style="color: #008080;">--</span><span style="color: #008080;">日期值必须包含在’‘以内，否则就是默认值</span>
  26. <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> Teacher(Name,ClassId,Gender,Age,Salary,Birthday) <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">邹元标2</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">5</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">0</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">15</span><span style="color: #ff0000;">‘</span>,<span style="color: #0000ff;">default</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">1991-9-11</span><span style="color: #ff0000;">‘</span>)
View Code

数据删除
语法:
delete [from] 表名 where 条件

  1. <span style="color: #0000ff;">delete</span> <span style="color: #0000ff;">from</span> Teacher <span style="color: #0000ff;">where</span> Age<span style="color: #808080;"><</span><span style="color: #800000; font-weight: bold;">20</span>
  2. <span style="color: #008080;">--</span><span style="color: #008080;">特点:</span><span style="color: #008080;">
  3. --</span><span style="color: #008080;">1.删除是一条一条进行删除的</span><span style="color: #008080;">
  4. --</span><span style="color: #008080;">2.每一条记录的删除都需要将操作写入到日志文件中</span><span style="color: #008080;">
  5. --</span><span style="color: #008080;">3.标识列不会从种子值重新计算,以从上次最后一条标识列值往下计算</span><span style="color: #008080;">
  6. --</span><span style="color: #008080;">4.这种删除可以触发delete触发器</span>
  7. <span style="color: #008080;">--</span><span style="color: #008080;">truncate table 表名 --没有条件，它是一次性删除所有数据</span><span style="color: #008080;">
  8. --</span><span style="color: #008080;">特点：</span><span style="color: #008080;">
  9. --</span><span style="color: #008080;">1.一次性删除所有数据，没有条件，那么日志文件只以最小化的数据写入</span><span style="color: #008080;">
  10. --</span><span style="color: #008080;">2.它可以使用标识列从种子值重新计算</span><span style="color: #008080;">
  11. --</span><span style="color: #008080;">3.它不能触发delete触发器</span>
  12. <span style="color: #0000ff;">truncate</span> <span style="color: #0000ff;">table</span> teacher

数据更新(数据修改)：一定需要考虑是否有条件
语法:
update 表名 set 字段=值，字段=值 。。where 条件

  1. <span style="color: #0000ff;">update</span> Teacher <span style="color: #0000ff;">set</span> Gender<span style="color: #808080;">=</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">true</span><span style="color: #ff0000;">‘</span>
  2. <span style="color: #008080;">--</span><span style="color: #008080;">修改时添加条件</span>
  3. <span style="color: #0000ff;">update</span> Teacher <span style="color: #0000ff;">set</span> Gender<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">0</span> <span style="color: #0000ff;">where</span> Id<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">20</span>
  4. <span style="color: #008080;">--</span><span style="color: #008080;">多字段修改</span>
  5. <span style="color: #0000ff;">update</span> Teacher <span style="color: #0000ff;">set</span> ClassId<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">4</span>,Age<span style="color: #808080;">+=</span><span style="color: #800000; font-weight: bold;">5</span>,Salary<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">5000</span> <span style="color: #0000ff;">where</span> Id<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">22</span>
  6. <span style="color: #008080;">--</span><span style="color: #008080;">修改班级id=4，同时年龄》20岁的人员工资+500</span>
  7. <span style="color: #0000ff;">update</span> Teacher <span style="color: #0000ff;">set</span> Salary<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">Salary+500</span> <span style="color: #0000ff;">where</span> ClassId<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">4</span> <span style="color: #808080;">and</span> Age<span style="color: #808080;">></span><span style="color: #800000; font-weight: bold;">20</span>

数据检索--查询
语法： *代表所有字段
select */字段名称列表 from 表列表

  1. <span style="color: #0000ff;">select</span> StudentNo,StudentName,Sex,[Address] <span style="color: #0000ff;">from</span><span style="color: #000000;"> Student
  2. </span>--<span style="color: #000000;">可以为标题设置  别名，别名可以是中文别名
  3. </span><span style="color: #0000ff;">select</span> StudentNo <span style="color: #0000ff;">as</span> 学号,StudentName 姓名,性别=Sex,[Address] <span style="color: #0000ff;">from</span><span style="color: #000000;"> Student
  4. </span>--<span style="color: #000000;">添加常量列
  5. </span><span style="color: #0000ff;">select</span> StudentNo <span style="color: #0000ff;">as</span> 学号,StudentName 姓名,性别=Sex,[Address] ,国籍=<span style="color: #800000;">‘</span><span style="color: #800000;">中华人民共和国</span><span style="color: #800000;">‘</span> <span style="color: #0000ff;">from</span> Student
技术分享 
  1. --<span style="color: #000000;">select的作用
  2. </span>--<span style="color: #800080;">1</span><span style="color: #000000;">.查询
  3. </span>--<span style="color: #800080;">2</span><span style="color: #000000;">.输出
  4. </span><span style="color: #0000ff;">select</span> <span style="color: #800080;">1</span>+<span style="color: #800080;">1</span>
  5. --+<span style="color: #000000;">是运算符,系统会自动为你做类型转换
  6. </span><span style="color: #0000ff;">select</span> <span style="color: #800080;">1</span>+<span style="color: #800000;">‘</span><span style="color: #800000;">1</span><span style="color: #800000;">‘</span>
  7. <span style="color: #0000ff;">select</span> <span style="color: #800000;">‘</span><span style="color: #800000;">1</span><span style="color: #800000;">‘</span>+<span style="color: #800080;">1</span>
  8. --如果+<span style="color: #000000;">两边都是字符串,那么它就是一字符串连接符
  9. </span><span style="color: #0000ff;">select</span> <span style="color: #800000;">‘</span><span style="color: #800000;">1</span><span style="color: #800000;">‘</span>+<span style="color: #800000;">‘</span><span style="color: #800000;">1</span><span style="color: #800000;">‘</span>
  10. <span style="color: #0000ff;">select</span> <span style="color: #800000;">‘</span><span style="color: #800000;">a</span><span style="color: #800000;">‘</span>+<span style="color: #800080;">1</span>
  11. --<span style="color: #000000;">可以输出多列值
  12. </span><span style="color: #0000ff;">select</span> <span style="color: #800080;">1</span>,<span style="color: #800080;">2</span>,<span style="color: #800080;">34</span>,<span style="color: #800080;">3</span>,<span style="color: #800080;">545</span>,<span style="color: #800080;">67</span>,<span style="color: #800080;">567</span>,<span style="color: #800080;">6</span>,<span style="color: #800080;">7</span>
  13. --<span style="color: #000000;">Top、Distinct
  14. </span><span style="color: #0000ff;">select</span> * <span style="color: #0000ff;">from</span><span style="color: #000000;"> Student
  15. </span>--<span style="color: #000000;">top可以获取指定的记录数,值可以大于总记录数.但是不能是负值
  16. </span><span style="color: #0000ff;">select</span> top <span style="color: #800080;">100</span> * <span style="color: #0000ff;">from</span><span style="color: #000000;"> Student
  17. </span>--<span style="color: #000000;">百分比是取ceiling()
  18. </span><span style="color: #0000ff;">select</span> top <span style="color: #800080;">10</span> percent * <span style="color: #0000ff;">from</span><span style="color: #000000;"> Student
  19. </span>--重复记录与原始的数据表数据无关,只与你查询的结果集有关系 distinct可以去除结果集中的重复记录--<span style="color: #000000;">结果集中每一列的值都一样
  20. </span><span style="color: #0000ff;">select</span> distinct LoginPwd,Sex,Email <span style="color: #0000ff;">from</span><span style="color: #000000;"> Student
  21. </span><span style="color: #0000ff;">select</span> distinct Sex <span style="color: #0000ff;">from</span> Student
select的作用 技术分享 
  1. --<span style="color: #000000;">聚合函数：
  2. </span>--<span style="color: #800080;">1</span><span style="color: #000000;">.对null过滤
  3. </span>--<span style="color: #800080;">2</span><span style="color: #000000;">.都需要有一个参数
  4. </span>--<span style="color: #800080;">3</span><span style="color: #000000;">.都是返回一个数值
  5. </span>--<span style="color: #000000;">sum()：求和:只能对数值而言,对字符串和日期无效
  6. </span>--<span style="color: #000000;">avg()：求平均值
  7. </span>--<span style="color: #000000;">count()：计数：得到满足条件的记录数
  8. </span>--<span style="color: #000000;">max()：求最大值:可以对任意类型的数据进行聚合，如果是字符串就比较拼音字母进行排序
  9. </span>--<span style="color: #000000;">min()：求最小值
  10. </span>--<span style="color: #000000;">获取学员总人数
  11. </span><span style="color: #0000ff;">select</span> COUNT(*) <span style="color: #0000ff;">from</span><span style="color: #000000;"> Student
  12. </span>--<span style="color: #000000;">查询最大年龄值
  13. </span><span style="color: #0000ff;">select</span>  MIN(BornDate) <span style="color: #0000ff;">from</span><span style="color: #000000;"> Student
  14. </span><span style="color: #0000ff;">select</span>  max(BornDate) <span style="color: #0000ff;">from</span><span style="color: #000000;"> Student
  15. </span>--<span style="color: #000000;">查询总分
  16. </span><span style="color: #0000ff;">select</span> SUM(StudentResult) <span style="color: #0000ff;">from</span> Result <span style="color: #0000ff;">where</span> StudentNo=<span style="color: #800080;">2</span>
  17. --<span style="color: #000000;">平均分
  18. </span><span style="color: #0000ff;">select</span> avg(StudentResult) <span style="color: #0000ff;">from</span> Result <span style="color: #0000ff;">where</span> SubjectId=<span style="color: #800080;">1</span>
  19. --<span style="color: #000000;">注意细节：
  20. </span><span style="color: #0000ff;">select</span>  SUM(StudentName) <span style="color: #0000ff;">from</span><span style="color: #000000;"> Student
  21. </span><span style="color: #0000ff;">select</span>  SUM(BornDate) <span style="color: #0000ff;">from</span><span style="color: #000000;"> Student
  22. </span><span style="color: #0000ff;">select</span>  min(StudentName) <span style="color: #0000ff;">from</span><span style="color: #000000;"> Student
  23. </span><span style="color: #0000ff;">select</span>  max(StudentName) <span style="color: #0000ff;">from</span><span style="color: #000000;"> Student
  24. </span>--查询学号，姓名，性别，年龄，电话，地址 ---<span style="color: #000000;">查询女生
  25. </span><span style="color: #0000ff;">select</span> StudentNo,StudentName,Sex,BornDate,Address <span style="color: #0000ff;">from</span> Student <span style="color: #0000ff;">where</span> Sex=<span style="color: #800000;">‘</span><span style="color: #800000;">女</span><span style="color: #800000;">‘</span> and BornDate ><span style="color: #800000;">‘</span><span style="color: #800000;">1990-1-1</span><span style="color: #800000;">‘</span> and Address=<span style="color: #800000;">‘</span><span style="color: #800000;">广州传智播客</span><span style="color: #800000;">‘</span>
  26. --<span style="color: #000000;">指定区间范围
  27. </span><span style="color: #0000ff;">select</span> StudentNo,StudentName,Sex,BornDate,Address <span style="color: #0000ff;">from</span> Student <span style="color: #0000ff;">where</span>  BornDate >=<span style="color: #800000;">‘</span><span style="color: #800000;">1990-1-1</span><span style="color: #800000;">‘</span> and BornDate<=<span style="color: #800000;">‘</span><span style="color: #800000;">1993-1-1</span><span style="color: #800000;">‘</span>
  28. --between...and  >=  <=
  29. <span style="color: #0000ff;">select</span> StudentNo,StudentName,Sex,BornDate,Address <span style="color: #0000ff;">from</span> Student <span style="color: #0000ff;">where</span> BornDate  between <span style="color: #800000;">‘</span><span style="color: #800000;">1990-1-1</span><span style="color: #800000;">‘</span> and <span style="color: #800000;">‘</span><span style="color: #800000;">1993-1-1</span><span style="color: #800000;">‘</span>
  30. --查询班级id  <span style="color: #800080;">1</span>  <span style="color: #800080;">3</span> <span style="color: #800080;">5</span><span style="color: #000000;">  7的学员信息
  31. </span><span style="color: #0000ff;">select</span> * <span style="color: #0000ff;">from</span> Student <span style="color: #0000ff;">where</span> ClassId=<span style="color: #800080;">1</span> or ClassId=<span style="color: #800080;">3</span> or ClassId=<span style="color: #800080;">5</span> or ClassId=<span style="color: #800080;">7</span>
  32. --指定具体的取值范围--可以是任意类型的范围.值的类型需要一致--<span style="color: #000000;">可以相互转换
  33. </span><span style="color: #0000ff;">select</span> * <span style="color: #0000ff;">from</span> Student <span style="color: #0000ff;">where</span> ClassId <span style="color: #0000ff;">in</span>(<span style="color: #800080;">1</span>,<span style="color: #800080;">3</span>,<span style="color: #800000;">‘</span><span style="color: #800000;">5</span><span style="color: #800000;">‘</span>,<span style="color: #800080;">7</span><span style="color: #000000;">)
  34. </span><span style="color: #0000ff;">select</span> * <span style="color: #0000ff;">from</span> Student <span style="color: #0000ff;">where</span> ClassId not <span style="color: #0000ff;">in</span>(<span style="color: #800080;">1</span>,<span style="color: #800080;">3</span>,<span style="color: #800000;">‘</span><span style="color: #800000;">5</span><span style="color: #800000;">‘</span>,<span style="color: #800080;">7</span>)
聚合函数 技术分享 
  1. --带条件的查询-模糊查询--<span style="color: #000000;"> 只针对字符串而言
  2. </span>--<span style="color: #000000;">查询  姓 林 的女生信息
  3. </span>--=<span style="color: #000000;">是一种精确查询，需要完全匹配
  4. </span><span style="color: #0000ff;">select</span> * <span style="color: #0000ff;">from</span> Student <span style="color: #0000ff;">where</span> Sex=<span style="color: #800000;">‘</span><span style="color: #800000;">女</span><span style="color: #800000;">‘</span> and StudentName=<span style="color: #800000;">‘</span><span style="color: #800000;">林</span><span style="color: #800000;">‘</span>
  5. --通配符--<span style="color: #000000;">元字符
  6. </span>--%：任意个任意字段  window:*  正则表达式 ：.*
  7. --<span style="color: #000000;">_:任意的单个字符
  8. </span>--[]:代表一个指定的范围，范围可以是连续也可以是间断的。与正则表达式完全一样[<span style="color: #800080;">0</span>-9a-zA-<span style="color: #000000;">Z].可以从这个范围中取一个字符
  9. </span>--[^<span style="color: #000000;">]:取反值
  10. </span><span style="color: #0000ff;">select</span> * <span style="color: #0000ff;">from</span> Student <span style="color: #0000ff;">where</span> Sex=<span style="color: #800000;">‘</span><span style="color: #800000;">女</span><span style="color: #800000;">‘</span> and StudentName=<span style="color: #800000;">‘</span><span style="color: #800000;">林%</span><span style="color: #800000;">‘</span>
  11. --<span style="color: #000000;">通配符必须在模糊查询关键的中才可以做为通配符使用，否则就是普通字符
  12. </span>--<span style="color: #000000;">like   像 。。。。一样
  13. </span><span style="color: #0000ff;">select</span> * <span style="color: #0000ff;">from</span> Student <span style="color: #0000ff;">where</span> Sex=<span style="color: #800000;">‘</span><span style="color: #800000;">女</span><span style="color: #800000;">‘</span> and StudentName  like <span style="color: #800000;">‘</span><span style="color: #800000;">林%</span><span style="color: #800000;">‘</span>
  14. <span style="color: #0000ff;">select</span> * <span style="color: #0000ff;">from</span> Student <span style="color: #0000ff;">where</span> Sex=<span style="color: #800000;">‘</span><span style="color: #800000;">女</span><span style="color: #800000;">‘</span> and StudentName  like <span style="color: #800000;">‘</span><span style="color: #800000;">林_</span><span style="color: #800000;">‘</span>
  15. --[]的使用  学号在11~<span style="color: #000000;">15之间的学员信息
  16. </span><span style="color: #0000ff;">select</span> * <span style="color: #0000ff;">from</span> Student <span style="color: #0000ff;">where</span> StudentNo like <span style="color: #800000;">‘</span><span style="color: #800000;">[13579]</span><span style="color: #800000;">‘</span>
  17. ---<span style="color: #000000;">处理null值
  18. </span>--<span style="color: #0000ff;">null</span>:不是地址没有分配,而是不知道你需要存储什么值  所以null是指   不知道。但是=<span style="color: #000000;">只能匹配具体的值，而null根本就不是一个值
  19. </span><span style="color: #0000ff;">select</span> COUNT(email) <span style="color: #0000ff;">from</span> Student <span style="color: #0000ff;">where</span> Email !=<span style="color: #0000ff;">null</span>
  20. <span style="color: #0000ff;">select</span> COUNT(email) <span style="color: #0000ff;">from</span> Student <span style="color: #0000ff;">where</span> Email  <span style="color: #0000ff;">is</span> <span style="color: #0000ff;">null</span>
  21. <span style="color: #0000ff;">select</span> count(email) <span style="color: #0000ff;">from</span> Student <span style="color: #0000ff;">where</span> Email  <span style="color: #0000ff;">is</span> not <span style="color: #0000ff;">null</span>
  22. --<span style="color: #000000;">将null值替换为指定的字符串值
  23. </span><span style="color: #0000ff;">select</span> StudentName,ISNULL(Email,<span style="color: #800000;">‘</span><span style="color: #800000;">没有填写电子邮箱</span><span style="color: #800000;">‘</span>) <span style="color: #0000ff;">from</span> Student <span style="color: #0000ff;">where</span> ClassId=<span style="color: #800080;">2</span>
模糊查询 技术分享 
  1. --<span style="color: #000000;">当你看到  每一个，，各自，不同，，分别  需要考虑分组
  2. </span>--<span style="color: #000000;">查询每一个班级的男生人数
  3. </span>--<span style="color: #000000;">与聚合函数一起出现在查询中的列，要么也被聚合，要么被分组
  4. </span><span style="color: #0000ff;">select</span> classid,Sex,COUNT(*) <span style="color: #0000ff;">from</span> Student <span style="color: #0000ff;">where</span> Sex=<span style="color: #800000;">‘</span><span style="color: #800000;">男</span><span style="color: #800000;">‘</span><span style="color: #000000;"> group by ClassId,sex
  5. </span>--查询每一个班级的总人数,显示人数>=<span style="color: #000000;">2的信息
  6. </span>--<span style="color: #800080;">1</span>.聚合不应出现在 WHERE 子句中--<span style="color: #000000;">语法错误
  7. </span><span style="color: #0000ff;">select</span> ClassId ,COUNT(*) <span style="color: #0000ff;">as</span> num <span style="color: #0000ff;">from</span> Student <span style="color: #0000ff;">where</span> Email <span style="color: #0000ff;">is</span> not <span style="color: #0000ff;">null</span>   GROUP by ClassId having COUNT(*)>=<span style="color: #800080;">2</span><span style="color: #000000;"> order by num desc
  8. </span>--<span style="color: #000000;">完整的sql查询家庭
  9.  </span>--<span style="color: #800080;">5</span>                            <span style="color: #800080;">1</span>                      <span style="color: #800080;">2</span>                                 <span style="color: #800080;">3</span>                                     <span style="color: #800080;">4</span>                                           <span style="color: #800080;">6</span>                                                 
  10. --<span style="color: #0000ff;">select</span> 字段列表 <span style="color: #0000ff;">from</span> 表列表  <span style="color: #0000ff;">where</span><span style="color: #000000;"> 数据源做筛选 group by 分组字段列表 having 分组结果集做筛选 Order by  对结果集做记录重排
  11. </span><span style="color: #0000ff;">select</span> ClassId ,COUNT(*) <span style="color: #0000ff;">as</span> num <span style="color: #0000ff;">from</span> Student <span style="color: #0000ff;">where</span> Email <span style="color: #0000ff;">is</span> not <span style="color: #0000ff;">null</span><span style="color: #000000;">   GROUP by ClassId order by ClassId desc
  12. </span>--<span style="color: #000000;">关于top的执行顺序 排序之后再取top值
  13. </span><span style="color: #0000ff;">select</span> top <span style="color: #800080;">1</span> ClassId ,COUNT(*) <span style="color: #0000ff;">as</span> num <span style="color: #0000ff;">from</span> Student  GROUP by ClassId  order by num desc
分组统计

7.类型转换函数

  1. <span style="color: #008080;">--</span><span style="color: #008080;">select :输出为结果集--虚拟表</span><span style="color: #008080;">
  2. --</span><span style="color: #008080;">print：以文本形式输出  只能输出一个字符串值.</span>
  3. <span style="color: #0000ff;">print</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #808080;">+</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">a</span><span style="color: #ff0000;">‘</span>
  4. <span style="color: #0000ff;">select</span> <span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #800000; font-weight: bold;">2</span>
  5. <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> Student
  6. </span><span style="color: #008080;">--</span><span style="color: #008080;">类型转换</span><span style="color: #008080;">
  7. --</span><span style="color: #008080;">Convert(目标类型,源数据,[格式]) --日期有格式</span>
  8. <span style="color: #0000ff;">print</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">我的成绩是：</span><span style="color: #ff0000;">‘</span><span style="color: #808080;">+</span><span style="color: #ff00ff;">convert</span>(<span style="color: #0000ff;">char</span>(<span style="color: #800000; font-weight: bold;">3</span>),<span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">)
  9. </span><span style="color: #0000ff;">print</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">今天是个大日子：</span><span style="color: #ff0000;">‘</span><span style="color: #808080;">+</span><span style="color: #ff00ff;">convert</span>(<span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">30</span>),<span style="color: #ff00ff;">getdate</span>(),<span style="color: #800000; font-weight: bold;">120</span><span style="color: #000000;">)
  10. </span><span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">getdate</span><span style="color: #000000;">()
  11. </span><span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">len</span>(<span style="color: #ff00ff;">getdate</span><span style="color: #000000;">())
  12. </span><span style="color: #008080;">--</span><span style="color: #008080;">cast(源数据  as  目标类型)  它没有格式</span>
  13. <span style="color: #0000ff;">print</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">我的成绩是：</span><span style="color: #ff0000;">‘</span><span style="color: #808080;">+</span><span style="color: #ff00ff;">cast</span>(<span style="color: #800000; font-weight: bold;">100</span> <span style="color: #0000ff;">as</span> <span style="color: #0000ff;">char</span>(<span style="color: #800000; font-weight: bold;">3</span>))

8.日期函数

  1. <span style="color: #008080;">--</span><span style="color: #008080;">getdate():获取当前服务器日期</span>
  2. <span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">GETDATE</span><span style="color: #000000;">()
  3. </span><span style="color: #008080;">--</span><span style="color: #008080;">可以在源日期值是追加指定时间间隔的日期数</span>
  4. <span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">DATEADD</span>(dd,<span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">90</span>,<span style="color: #ff00ff;">GETDATE</span><span style="color: #000000;">())
  5. </span><span style="color: #008080;">--</span><span style="color: #008080;">dateDiff:找到两个日期之间指定格式的差异值</span>
  6. <span style="color: #0000ff;">select</span> StudentName,<span style="color: #ff00ff;">DATEDIFF</span>(yyyy,<span style="color: #ff00ff;">getdate</span>(),BornDate) <span style="color: #0000ff;">as</span> age <span style="color: #0000ff;">from</span> Student <span style="color: #0000ff;">order</span> <span style="color: #0000ff;">by</span><span style="color: #000000;">  age
  7. </span><span style="color: #008080;">--</span><span style="color: #008080;">DATENAME:可以获取日期的指定格式的字符串表现形式</span>
  8. <span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">DATENAME</span>(dw,<span style="color: #ff00ff;">getdate</span><span style="color: #000000;">())
  9. </span><span style="color: #008080;">--</span><span style="color: #008080;">DATEPART:可以获取指定的日期部分</span>
  10. <span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">cast</span>(<span style="color: #ff00ff;">DATEPART</span>(yyyy,<span style="color: #ff00ff;">getdate</span>()) <span style="color: #0000ff;">as</span> <span style="color: #0000ff;">CHAR</span>(<span style="color: #800000; font-weight: bold;">4</span>))<span style="color: #808080;">+</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">-</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">+</span><span style="color: #ff00ff;">cast</span>(<span style="color: #ff00ff;">DATEPART</span>(mm,<span style="color: #ff00ff;">getdate</span>()) <span style="color: #0000ff;">as</span> <span style="color: #0000ff;">CHAR</span>(<span style="color: #800000; font-weight: bold;">2</span>))<span style="color: #808080;">+</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">-</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">+</span><span style="color: #ff00ff;">cast</span>(<span style="color: #ff00ff;">DATEPART</span>(dd,<span style="color: #ff00ff;">getdate</span>()) <span style="color: #0000ff;">as</span> <span style="color: #0000ff;">CHAR</span>(<span style="color: #800000; font-weight: bold;">2</span>))

9.数学函数

  1. <span style="color: #008080;">--</span><span style="color: #008080;">rand:随机数:返回0到1之间的数，理论上说可以返回0但是不能返回1<br></span><span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">RAND()</span><span style="color: #008080;">
  2. --</span><span style="color: #008080;">abs:absolute:取绝对值</span>
  3. <span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">ABS</span>(<span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">)
  4. </span><span style="color: #008080;">--</span><span style="color: #008080;">ceiling:获取比当前数大的最小整数</span>
  5. <span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">CEILING</span>(<span style="color: #800000; font-weight: bold;">1.00</span><span style="color: #000000;">)
  6. </span><span style="color: #008080;">--</span><span style="color: #008080;">floor:获取比当前数小的最大整数</span>
  7. <span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">floor</span>(<span style="color: #800000; font-weight: bold;">1.99999</span><span style="color: #000000;">)
  8. </span><span style="color: #ff00ff;">power</span><span style="color: #000000;">:
  9. </span><span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">POWER</span>(<span style="color: #800000; font-weight: bold;">3</span>,<span style="color: #800000; font-weight: bold;">4</span><span style="color: #000000;">)
  10. </span><span style="color: #008080;">--</span><span style="color: #008080;">round():四舍五入.只关注指定位数后一位</span>
  11. <span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">ROUND</span>(<span style="color: #800000; font-weight: bold;">1.549</span>,<span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">)
  12. </span><span style="color: #008080;">--</span><span style="color: #008080;">sign:正数==1  负数 ==-1  0=0</span>
  13. <span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">SIGN</span>(<span style="color: #808080;">-</span><span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">)
  14. </span><span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">ceiling</span>(<span style="color: #800000; font-weight: bold;">17</span><span style="color: #808080;">*</span><span style="color: #800000; font-weight: bold;">1.0</span><span style="color: #808080;">/</span><span style="color: #800000; font-weight: bold;">5</span>)

10.字符串函<

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！