T-SQL高级篇(一)

数据库设计

       定义

              数据库设计就是将数据库中的数据实体以及这些数据实体之间关系,进行规划和结构化的过程

在需求分析阶段,设计数据库的一般步骤

A.     收集相信

B.     标识实体

C.     标记每个实体需要存储的详细信息/属性

D.    标识实体之间的关系

在概要设计阶段和详细设计阶段数据库设计步骤:

1.      绘制E-R

2.      E-R图转化为数据库模型图

3.      应用三大范式规范化表设计

数据库E-R

       定义

              1.实体

                    现实世界中具有区分其他事物的特征或属性并与其他实体有联系的实体

              2.属性

                    实体的特征

              3.联系

                    两个或者多个实体之间的关联关系

              4.映射基数

                    表示通过联系与该实体关联的其他实体的个数

                    一对一:

                    X中的一个实体最多与Y中的一个实体关联,并且Y中的一个实体最多与X中的一个实体关联

                    一对多:

                                   X中的一个实体可以与Y中的任意数量的实体关联

                    多对一:

                                   X中的一个实体最多与Y中的一个实体关联

                    多对多:

                                   X中的一个实体可以与Y中的任意数量的实体关联

关系数据库模式

       定义

             一个关系描述为属性名的集合称为关系模式.

             关系数据库模式是对关系数据库结构的描述,或者说是对关系数据库框架的描述

规范设计

              第一范式(1NF,Normal Formate)

                    第一范式的目标是确保每列的原子性

              第二范式(2NF)

                    第二范式是在第一范式的基础上更进一层,其目标是确保表中的每列都和主键相关

              第三范式(3NF)

                      第三范式在第二范式的基础上更进一层.第三范式的目标是确保每列都和主键列直接相关,而不是间接相关

满足三大范式之俗语

              第一范式

                    细到不能再分

              第二范式

                    必须和主键相关

              第三范式

                    各列必须和主键直接相关,不能间接相关

数据库文件三大部分

              主数据文件:

                     *.mdf

              次要数据文件:

                     *.ndf

              日志文件:

                     *.ldf

              每个数据库至少要包含两个文件:一个数据库文件和一个日志文件

       经验

               为了保证数据的安全性,提高存储速度,我们应该将数据文件和日志文件分别放置在不同的驱动器上

创建数据库

       语法

                     CREATE DATABASE数据库名

                     ON PRIMARY                                   --该选项是一个关键字,指定主文件组中的文件

                     (

                            NAME=’数据库名’                    --主数据文件的逻辑名称

                            FILENAME=’数据库路径’         –主数据文件的物理名称

                            SIZE=数据库大小                      –主数据库文件的初始大小

                            MAXSIZE=数据库最大大小       –主数据文件增长的最大值

                                                 UNLIMITED         --不受限制,大小为盘符的最大值

                            FILEGROWTH=’大小增长率’    --主数据文件的增长率,当值为0时不增长

                     )

 

                     LOG ON

                     (

                            NAME=’’,

                            FILENAME=’’,

                            SIZE=,

                            FILEGROWTH=

                     )

                     GO                                                   --和后续的SQL语句分隔开

修改数据库

                     ALTER DATABASE数据库名

                     ADD FILE                                         --添加数据文件

                     (

                            …….

                     )

                     ADD LOG FILE                                 --添加日志文件

                     (

                            ……

                     )

创建多个数据文件和多个日志文件

                     CREATE DATABASE数据库名

                            ON PRIMARY

                     (

                            …..

                     ),

                     (

                            …..

                     )

                     LOG ON

                     (

                            …..

                     ),

                     (

                            …..

                     )

删除数据库

       语法

                     DROP DATABASE数据库名

创建数据库前先判定一下

                     USE master        --设置当前为master数据库,以便访问指定表

                     GO

                     IF EXISTS(SELECT * FROM sysdatabasesWHERE name=’指定表名’)

                     DROP DATABASE数据库名

                     CREATE DATABASE数据库名

                     ON

                     (

                            …..

                     )

                     LOG ON

                     (

                            ….

                     )

                     GO

                    

                     EXISTS(查询语句)监测某个查询是否存在

系统存储过程扩展

       xp_cmdshellSQL Server扩展存储过程

              在操作系统的指定路径下创建文件夹

                     EXEC sp_configure ‘showadvanced options’,1  –在使用xp_cmdshell前先执行sp_configure

                     GO

                     RECONFIGURE                                             --重启RECONFIGURE

                     GO

                     EXEC sp_configure ‘xp_cmdshell’,1

                     GO

                     RECONFIGURE

                     GO

                     EXEC xp_cmdshell ‘MD D:\Student’           --用创建的xp_cmdshell来创建Student文件夹

                     GO

查询当前数据库中是否有表

       语法

                    SELECT * FROM SYSOBJECTS WHERE XTYPE='U'

创建表

       语法

                     CREATE TABLE   表名

                     (

                           1 数据类型列的特征

                           2 数据类型列的特征

                            ………

                     )

                    列的特征: 包括该列是否为空(NULL),是否是标识列(自动编号),是否有默认值/主键等

修改表

       语法

                     ALTER TABLE表名

                            ALTER COLUMN列名 varchar(20) not null

                     GO

                     ALTER TABLE表名

                            ADD列名类型                               –创建一个列

                     GO

删除表

       语法      

                     DROP TABLE表名

数据库表存储位置

                    当前数据库系统表:sysobjects

                    所有数据库信息表:sysdatabases

创建表的完整语法

                     USE数据库名

                     GO

                     IF EXISTS(SELECT * FROM sysobjectsWHERE name=’表名’)    //下面的3个表名必须一致

                            DROP TABLE表名

                     CREATE TABLE表名

                     (

                           列名类型是否允许为空约束名自动增长(种子,增长率)(IDENTITY(1,1))

                            ….

                     )

                     GO

       注意

                    使用DROP TABLE语句之前,首先要确认表中是否没有客户的业务数据了..

                    要养成随时备份数据库的习惯

使用SQL语句创建和删除约束

       数据完整性

                    指数据的正确性和相容性

       实体完整性

                    唯一确定表中一行记录

       域完整性

                    表中特定列数据的有效性,以确保不会输入无效的数值

       引用完整性

                    插入或删除记录时,维护表之间定义的关系

       自定义完整性

                    定义不属于以上三种完整性的特定业务规则

添加约束

       语法

                     ALTER TABLE表名

                     ADD CONSTRAINT约束名约束类型具体的约束说明

常用约束即用法

       主键约束(Primary Key Constraint)

                     ALTER TABLE表名

                     ADD CONSTRAINT约束名 PRIMARY KEY(列名)       推荐约束名:PK_**

       非空约束(Not NULL)

                     NOT NULL

       唯一约束(Unique Constraint)

                     ALTER TABLE表名

                     ADD CONSTRAINT约束名 UNIQUE (列名)                       推荐约束名:UQ_**

       检查约束(Check Constaint)

                     ALTER TABLE表名

                     ADD CONSTRAINT约束名 CHECK (条件)                        推荐约束名:CK_**

       默认约束(Default Constraint)

                     ALTER TABLE表名

                     ADD CONSTRAINT约束名DEFAULT (‘默认值’) FOR列名 推荐约束名:DF_**

       外键约束(Foreign Key Constraint)

                     ALTER TABLE表名

                     ADD CONSTRAINT约束名                                               推荐约束名:FK_**

                            FOREIGN KEY(列名) REFERENCES表名(列名)

                     GO

创建表的时候创建约束

                     CREATE TABLE表名

                     (

                           列名类型 NOT NULL 约束名(PRIMARY KEY)

                           列名类型 NOT NULL 约束名(UNIQUE)

                           列名类型 NOT NULL 约束名(DEFAULT) ‘默认值

                            …….

                     )

删除约束

       语法

                     ALTER TABLE表名

                            DROP CONSTRAINT约束名

在已有表中添加约束

       方法

                    方法一:                               --数据正确性要求比较高

1.      导出数据

2.      在空记录表中添加约束

3.      导入数据

                    方法二:                               --非金融行业

                                   ALTER TABLE表名 WITH NOCHECK 

                                          ADDCONSTRAINT约束名 CHECK (条件)

同时添加多个约束

       例子

                    ALTER TABLEStudent

              ADD CONSTRAINT PK_StudentPRIMARYKEY (StudentNo),

                  CONSTRAINT UQ_StudentUNIQUE (IdentityCard),

                  CONSTRAINT DF_StudentDEFAULT ('地址不详')FOR Address,

                  CONSTRAINT CK_StudentCHECK(BornDate>1980-01-01),

                  CONSTRAINT FK_StudentFOREIGN KEY (GradeID)REFERENCES Grade(GradeID)

           GO

--高级查询在数据库中用得是最频繁的,也是应用最广泛的。 Ø 基本常用查询 --select select * from student; --all 查询所有 select all sex from student; --distinct 过滤重复 select distinct sex from student; --count 统计 select count(*) from student; select count(sex) from student; select count(distinct sex) from student; --top 取前N条记录 select top 3 * from student; --alias column name 列重命名 select id as 编号, name '名称', sex 性别 from student; --alias table name 表重命名 select id, name, s.id, s.name from student s; --column 列运算 select (age + id) col from student; select s.name + '-' + c.name from classes c, student s where s.cid = c.id; --where 条件 select * from student where id = 2; select * from student where id > 7; select * from student where id < 3; select * from student where id <> 3; select * from student where id >= 3; select * from student where id <= 5; select * from student where id !> 3; select * from student where id !< 5; --and 并且 select * from student where id > 2 and sex = 1; --or 或者 select * from student where id = 2 or sex = 1; --between ... and ... 相当于并且 select * from student where id between 2 and 5; select * from student where id not between 2 and 5; --like 模糊查询 select * from student where name like '%a%'; select * from student where name like '%[a][o]%'; select * from student where name not like '%a%'; select * from student where name like 'ja%'; select * from student where name not like '%[j,n]%'; select * from student where name like '%[j,n,a]%'; select * from student where name like '%[^ja,as,on]%'; select * from student where name like '%[ja_on]%'; --in 子查询 select * from student where id in (1, 2); --not in 不在其中 select * from student where id not in (1, 2); --is null 是空 select * from student where age is null; --is not null 不为空 select * from student where age is not null; --order by 排序 select * from student order by name; select * from student order by name desc; select * from student order by name asc; --group by 分组 按照年龄进行分组统计 select count(age), age from student group by age; 按照性别进行分组统计 select count(*), sex from student group by sex; 按照年龄和性别组合分组统计,并排序 select count(*), sex from student group by sex, age order by age; 按照性别分组,并且是id大于2的记录最后按照性别排序 select count(*), sex from student where id > 2 group by sex order by sex; 查询id大于2的数据,并完成运算后的结果进行分组和排序 select count(*), (sex * id) new from student where id > 2 group by sex * id order by sex * id; --group by all 所有分组 按照年龄分组,是所有的年龄 select count(*), age from student group by all age; --having 分组过滤条件 按照年龄分组,过滤年龄为空的数据,并且统计分组的条数和现实年龄信息 select count(*), age from student group by age having age is not null; 按照年龄和cid组合分组,过滤条件是cid大于1的记录 select count(*), cid, sex from student group by cid, sex having cid > 1; 按照年龄分组,过滤条件是分组后的记录条数大于等于2 select count(*), age from student group by age having count(age) >= 2; 按照cid和性别组合分组,过滤条件是cid大于1,cid的最大值大于2 select count(*), cid, sex from student group by cid, sex having cid > 1 and max(cid) > 2; Ø 嵌套子查询 子查询是一个嵌套在select、insert、update或delete语句或其他子查询中的查询。任何允许使用表达式的地方都可以使用子查询。子查询也称为内部查询或内部选择,而包含子查询的语句也成为外部查询或外部选择。 # from (select … table)示例 将一个table的查询结果当做一个新表进行查询 select * from ( select id, name from student where sex = 1 ) t where t.id > 2; 上面括号中的语句,就是子查询语句(内部查询)。在外面的是外部查询,其中外部查询可以包含以下语句: 1、 包含常规选择列表组件的常规select查询 2、 包含一个或多个表或视图名称的常规from语句 3、 可选的where子句 4、 可选的group by子句 5、 可选的having子句 # 示例 查询班级信息,统计班级学生人生 select *, (select count(*) from student where cid = classes.id) as num from classes order by num; # in, not in子句查询示例 查询班级id大于小于的这些班级的学生信息 select * from student where cid in ( select id from classes where id > 2 and id < 4 ); 查询不是班的学生信息 select * from student where cid not in ( select id from classes where name = '2班' ) in、not in 后面的子句返回的结果必须是一列,这一列的结果将会作为查询条件对应前面的条件。如cid对应子句的id; # exists和not exists子句查询示例 查询存在班级id为的学生信息 select * from student where exists ( select * from classes where id = student.cid and id = 3 ); 查询没有分配班级的学生信息 select * from student where not exists ( select * from classes where id = student.cid ); exists和not exists查询需要内部查询和外部查询进行一个关联的条件,如果没有这个条件将是查询到的所有信息。如:id等于student.id; # some、any、all子句查询示例 查询班级的学生年龄大于班级的学生的年龄的信息 select * from student where cid = 5 and age > all ( select age from student where cid = 3 ); select * from student where cid = 5 and age > any ( select age from student where cid = 3 ); select * from student where cid = 5 and age > some ( select age from student where cid = 3 ); Ø 聚合查询 1、 distinct去掉重复数据 select distinct sex from student; select count(sex), count(distinct sex) from student; 2、 compute和compute by汇总查询 对年龄大于的进行汇总 select age from student where age > 20 order by age compute sum(age) by age; 对年龄大于的按照性别进行分组汇总年龄信息 select id, sex, age from student where age > 20 order by sex, age compute sum(age) by sex; 按照年龄分组汇总 select age from student where age > 20 order by age, id compute sum(age); 按照年龄分组,年龄汇总,id找最大值 select id, age from student where age > 20 order by age compute sum(age), max(id); compute进行汇总前面是查询的结果,后面一条结果集就是汇总的信息。compute子句中可以添加多个汇总表达式,可以添加的信息如下: a、 可选by关键字。它是每一列计算指定的行聚合 b、 行聚合函数名称。包括sum、avg、min、max、count等 c、 要对其执行聚合函数的列 compute by适合做先分组后汇总的业务。compute by后面的列一定要是order by中出现的列。 3、 cube汇总 cube汇总和compute效果类似,但语法较简洁,而且返回的是一个结果集。 select count(*), sex from student group by sex with cube; select count(*), age, sum(age) from student where age is not null group by age with cube; cube要结合group by语句完成分组汇总 Ø 排序函数 排序在很多地方需要用到,需要对查询结果进行排序并且给出序号。比如: 1、 对某张表进行排序,序号需要递增不重复的 2、 对学生的成绩进行排序,得出名次,名次可以并列,但名次的序号是连续递增的 3、 在某些排序的情况下,需要跳空序号,虽然是并列 基本语法 排序函数 over([分组语句] 排序子句[desc][asc]) 排序子句 order by 列名, 列名 分组子句 partition by 分组列, 分组列 # row_number函数 根据排序子句给出递增连续序号 按照名称排序的顺序递增 select s.id, s.name, cid, c.name, row_number() over(order by c.name) as number from student s, classes c where cid = c.id; # rank函数函数 根据排序子句给出递增的序号,但是存在并列并且跳空 顺序递增 select id, name, rank() over(order by cid) as rank from student; 跳过相同递增 select s.id, s.name, cid, c.name, rank() over(order by c.name) as rank from student s, classes c where cid = c.id; # dense_rank函数 根据排序子句给出递增的序号,但是存在并列不跳空 不跳过,直接递增 select s.id, s.name, cid, c.name, dense_rank() over(order by c.name) as dense from student s, classes c where cid = c.id; # partition by分组子句 可以完成对分组的数据进行增加排序,partition by可以与以上三个函数联合使用。 利用partition by按照班级名称分组,学生id排序 select s.id, s.name, cid, c.name, row_number() over(partition by c.name order by s.id) as rank from student s, classes c where cid = c.id; select s.id, s.name, cid, c.name, rank() over(partition by c.name order by s.id) as rank from student s, classes c where cid = c.id; select s.id, s.name, cid, c.name, dense_rank() over(partition by c.name order by s.id) as rank from student s, classes c where cid = c.id; # ntile平均排序函数 将要排序的数据进行平分,然后按照等分排序。ntile中的参数代表分成多少等分。 select s.id, s.name, cid, c.name, ntile(5) over(order by c.name) as ntile from student s, classes c where cid = c.id; Ø 集合运算 操作两组查询结果,进行交集、并集、减集运算 1、 union和union all进行并集运算 --union 并集、不重复 select id, name from student where name like 'ja%' union select id, name from student where id = 4; --并集、重复 select * from student where name like 'ja%' union all select * from student; 2、 intersect进行交集运算 --交集(相同部分) select * from student where name like 'ja%' intersect select * from student; 3、 except进行减集运算 --减集(除相同部分) select * from student where name like 'ja%' except select * from student where name like 'jas%'; Ø 公式表表达式 查询表的时候,有时候中间表需要重复使用,这些子查询被重复查询调用,不但效率低,而且可读性低,不利于理解。那么公式表表达式可以解决这个问题。 我们可以将公式表表达式(CET)视为临时结果集,在select、insert、update、delete或是create view语句的执行范围内进行定义。 --表达式 with statNum(id, num) as ( select cid, count(*) from student where id > 0 group by cid ) select id, num from statNum order by id; with statNum(id, num) as ( select cid, count(*) from student where id > 0 group by cid ) select max(id), avg(num) from statNum; Ø 连接查询 1、 简化连接查询 --简化联接查询 select s.id, s.name, c.id, c.name from student s, classes c where s.cid = c.id; 2、 left join左连接 --左连接 select s.id, s.name, c.id, c.name from student s left join classes c on s.cid = c.id; 3、 right join右连接 --右连接 select s.id, s.name, c.id, c.name from student s right join classes c on s.cid = c.id; 4、 inner join内连接 --内连接 select s.id, s.name, c.id, c.name from student s inner join classes c on s.cid = c.id; --inner可以省略 select s.id, s.name, c.id, c.name from student s join classes c on s.cid = c.id; 5、 cross join交叉连接 --交叉联接查询,结果是一个笛卡儿乘积 select s.id, s.name, c.id, c.name from student s cross join classes c --where s.cid = c.id; 6、 自连接(同一张表进行连接查询) --自连接 select distinct s.* from student s, student s1 where s.id <> s1.id and s.sex = s1.sex; Ø 函数 1、 聚合函数 max最大值、min最小值、count统计、avg平均值、sum求和、var求方差 select max(age) max_age, min(age) min_age, count(age) count_age, avg(age) avg_age, sum(age) sum_age, var(age) var_age from student; 2、 日期时间函数 select dateAdd(day, 3, getDate());--加天 select dateAdd(year, 3, getDate());--加年 select dateAdd(hour, 3, getDate());--加小时 --返回跨两个指定日期的日期边界数和时间边界数 select dateDiff(day, '2011-06-20', getDate()); --相差秒数 select dateDiff(second, '2011-06-22 11:00:00', getDate()); --相差小时数 select dateDiff(hour, '2011-06-22 10:00:00', getDate()); select dateName(month, getDate());--当前月份 select dateName(minute, getDate());--当前分钟 select dateName(weekday, getDate());--当前星期 select datePart(month, getDate());--当前月份 select datePart(weekday, getDate());--当前星期 select datePart(second, getDate());--当前秒数 select day(getDate());--返回当前日期天数 select day('2011-06-30');--返回当前日期天数 select month(getDate());--返回当前日期月份 select month('2011-11-10'); select year(getDate());--返回当前日期年份 select year('2010-11-10'); select getDate();--当前系统日期 select getUTCDate();--utc日期 3、 数学函数 select pi();--PI函数 select rand(100), rand(50), rand(), rand();--随机数 select round(rand(), 3), round(rand(100), 5);--精确小数位 --精确位数,负数表示小数点前 select round(123.456, 2), round(254.124, -2); select round(123.4567, 1, 2);
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值