SQL2005培训精编课程.doc

1 33 1 1 MICROSOMICROSO SQLSQL SERVERSERVER 简介 是微软推出的一款数据库产品简介 是微软推出的一款数据库产品 4 1 1 表 TABLE 区分不同的实体 4 1 2 列 COLUMN 常称字段 FIELD 4 1 3 数据类型 DATATYPE 4 1 4 记录 RECORD 记录有可以被称为行 ROW 5 1 5 主键 PRIMARYKEY ID 5 1 6 索引 5 2 2 数据库的语言数据库的语言 SQL SQL 全称是全称是 STRUCTUREDSTRUCTURED QUERYQUERY LANGUAGE LANGUAGE 7 2 1 查询数据 关键字 SELECT FROM 7 2 2 带条件查询数据 关键字 WHERE 8 2 3 带条件查询数据 关键字 LIKE 模糊查询 9 2 4 带条件查询数据 关键字 IN 模糊查询 9 2 5 插入数据表记录 11 2 6 修改数据 12 2 7 删除数据 12 3 3 数据表建表的规则数据表建表的规则 SQL SQL 三范式三范式 16 3 1 第一范式 1NF 无重复的列 16 3 2 第二范式 2NF 属性完全依赖于主键 消除部分子函数依赖 16 3 3 第三范式 3NF 属性不依赖于其它非主属性 消除传递依赖 17 4 4 SQLSQL 联合查询数据 多表查询数据联合查询数据 多表查询数据 18 4 1 LEFT JOIN ON 18 4 2 JOIN ON 18 4 3 GROUP BY 分组 18 4 4 HAVING 条件 ORDER BY排序 DESC 和 ASC 18 5 5 SQLSQL 函数运用函数运用 20 5 1 COUNT 1 计数函数 20 5 2 SUM 求和函数 20 5 3 SUBSTRING 字段 189 CN 20 5 4 MAX 字段 MIN 字段 20 5 5 REPLACE 替换字符 20 6 6 高级编程高级编程 21 6 1 存储过程创建 删除 21 6 2 存储过程修改 测试 22 6 3 多参数存储过程操作 22 6 4 触发器 添加删除 修改 测试 22 6 5 触发器虚标DELETED INSERTED 表的作用 24 6 6 时间类型 25 6 7 取消重复关键字 27 7 7 数据的导入与导出数据的导入与导出 EXCEL TXTEXCEL TXT 附件数据库 附件数据库 30 2 33 7 1 导入 EXCEL数据到数据库中 30 7 2 导入TXT数据到数据库中 30 7 3 附件数据库 30 7 4 分离数据库 31 7 5 数据库自动备份方案 31 7 6 数据库练习 31 8 8 附件学习参考资料附件学习参考资料 32 8 1 SYBASE数据库建模 32 8 2 数据表高级查询参考 33 8 3 子表结合查询 36 8 4 数据库日志查看 36 8 5 数据库作业 37 9 9 项目验收项目验收 38 9 1 SHALIWY数据库的建立和表的建立 38 3 33 授课人 张木和 4 33 1 Microso SQL Server 简介 是微软推出的一款数据库产品 1 1 表 Table 区分不同的实体 如学生信息表 学校信息表 表包含以下的列 字段 行 数据类型 姓名年龄日生成绩班级 张三 21 010 9 785 5 李四 23 王五 26 表名 男袜男袜 字段类型长度备注 IDInt 4 自增编号 NameNvarchar 50 50 名称 名称 尺寸 价格 会员价格 市场价格 入库 数量 抽象出实体中有用的信息 描述字段 1 2 列 Column 常称字段 Field 如下图的 员工姓名 部门 入职时间 1 3 数据类型 DataType 下面我们为员工表规定了 姓名 部门 和 入职时间 三个列 这样我们往列 里面填写数据就可以啦 但是我们改填写什么数据 如填写数据的下面格式 入职 时间一定要是 Datetime 时间 类型 员工姓名一定要是字符 汉字等数字组成 类型 DataType 数据类型 说明 Int 数据类型 用于整数 如 1 2 3 4 4555 Nvarchar 50 汉字类型 需要加长度 超过长度将会被截断 Varchar 50 汉字类型 需要加长度 超过长度将会被截断 Char 50 汉字类型 需要加长度 超过长度将会被截断 Float 浮点类型 如 1 2 13 4 0 56 小数点 Datetime 时间类型 如 2010 11 27 2010 11 27 Text 文本类型 如日志 保存 2m 文本数据 Bit 只保存是否 0 1 True False 5 33 1 4 记录 Record 记录有可以被称为行 Row 一个表里可以记录 40 亿条数据信息 也就是说改员工信息表可以记录 40 亿个用户 信息 容量 1 5 主键 PrimaryKey ID 定义 唯一性 代码 定义 唯一性 代码 IDID 值 自动增长 值 自动增长 员工表中的每一行记录代表了一个员工 一般员工的名字就能唯一标识这一个员工 但是名字也是有可能重复的 这时我们就要为每一名员工分配一个唯一的工号 ProductInfo IDNameProNumColorRule 1 厚竹纤维中统男 袜 4154 006 4154 006 蓝色10 寸 2 厚竹纤维中统男 袜 4154 006 4154 006 蓝色10 寸 3 厚竹纤维中统男 袜 4154 006 4154 006 蓝色10 寸 产品表 ProductInfo 在保存数据的时候 id 是不要赋值的 1 6 索引 可以提高数据查询的速度 但是任何事物都是双刃剑 它也有一些缺点索引占据一 定磁盘空间 就像有按笔画查找的目录的书会比没有这种目录的书页数要多一些 索引减慢了数据插入和删除的速度 因为每次插入和删除的时候都需要更新索引 表拥有的索引越多 则写操作的平均性能下降就越大 实例 以某公司员工信息为列子 表明 staffInfo 命名尽量用英语的 规范的要 求 工号员工姓名员工部门入职时间 1 马小虎市场部 2010 9 7 2 王二小客服部 2010 8 7 3 白展堂开发部 2010 8 7 4 李达最后勤部 2010 8 3 数据库建立表 staffInfo IDstaffNamedepartmentRegTime 1 马小虎市场部 2010 9 7 2 王二小客服部 2010 8 7 3 白展堂开发部 2010 8 7 4 李达最后勤部 2010 8 3 6 33 学习作业 自己以文本的形式描述一个详细的个人信息表 UserInfo 表 想 下需要哪些字段 越详细越好 参照以上列子列好之后发送到 提出疑问和难 点 共同进步 softmaster softmaster 谢谢合作 7 33 2 数据库的语言 SQL 全称是 Structured Query Language SQL 具有下面 4 个主要的功能 创建数据库并定义表的结构 查询需要的数据 更新 或者删除指定的数据 控制数据库的安全 使用 SQL 我们可以完成和 DBMS 的几乎所 有交互任务 2 1 查询数据 关键字 select from 比如我们要查员工信息 语句 select from staffInfo 表示查出全部用户数据 格式 select 字段 名 from 表名 表示全部的字段 列 Select 员工姓名 from staffInfo 查出员工姓名列 Select 员工姓名 员工部门 from staffInfo 查出员工姓名列 多列用逗号隔 开 详细代码SQL2 1 v2010 sql 2 2 带条件查询数据 关键字 where 比如我们要查某一具体员工信息 语句 select from staffInfo where 员工姓名 王二小 表示查出具体用户所有数据 格式 select 字段 名 from 表名 表示全部的字段列 2 3 带条件查询数据 关键字 like 模糊查询 比如我们要查姓王的员工信息 语句 select from staffInfo where 员工姓名 like 王 表示查出所有的姓王的员工 8 33 2 4 带条件查询数据 关键字 in 模糊查询 比如我们要查姓王的员工信息 语句 select from staffInfo where 员工姓名 in 王二 小 马小虎 表示查出王二小和马小虎的员工信息 学习作业 A 查询姓李的员工信息 B 查询 9 月入职的员工 C 查出后勤 部门和客服部门的员工信息 参照以上列子列好之后发送到 提出疑问和难点 共同进步 softmaster softmaster 谢谢合作 9 33 2 5 插入数据表记录 比如 新增一个用户记录 对应字段值 INSERT INTO dbo StaffInfo StaffName Department RegTime VALUES 毛艾 N 后勤部门 2010 9 4 16 40 19 625 1 VALUES 值个数对应字段的个数 2 数据类对应字段的类型 操作实例 把查询结果插入到数据表中 INSERT INTO StaffInfo StaffName DepartmentID RegTime Age SELECT XXX StaffName DepartmentID GETDATE Age FROM StaffInfo 10 33 2 6 修改数据 修改员工信息表 UPDATE StaffInfo SET StaffName 王楠 WHERE ID 5 2 7 删除数据 删除员工王楠的用户信息 DELETE StaffInfo WHERE StaffName 王楠 执行之后 11 33 学习作业 1 新增 10 个员工信息 员工姓名部门入职时间 李纨客服部 2010 9 3 张好客服部 2010 9 3 张二客服部 2010 9 3 李四客服部 2010 9 3 李六人力资源部 2010 9 3 李六人力资源部 2010 9 3 李四人力资源部 2010 9 3 张纨后勤部 2010 9 3 张二后勤部 2010 9 3 2 修改员工张好于李纨的入职时间为 2010 9 4 3 删除员工张好于李纨的用户信息 参照以上列子列好之后发送到 提出疑问和难点 共同进步 softmaster softmaster 谢谢合作 12 33 项目参考 13 33 3 数据表建表的规则 SQL 三范式 3 1 第一范式 1NF 无重复的列 错误设计 编号姓名姓名 2 1张飞张飞 2关羽关羽 正确设计 编号姓名年龄 1张飞32 2关羽27 3 2 第二范式 2NF 属性完全依赖于主键 消除部分子函数依赖 错误设计 编号姓名年龄 1张飞32 2关羽27 2李达31 正确设计 编号姓名年龄 1张飞32 2关羽27 3李达31 文字解释 第二范式 2NF 要求数据库表中的每个实例或行必须可以被惟一地区分 为实现区分通 常需要为表加上一个列 以存储各个实例的惟一标识 例如员工信息表中加上了员工编号 emp id 列 因为每个员工的员工编号是惟一的 因此每个员工可以被惟一区分 这个 惟一属性列被称为主关键字或主键 主码 第二范式 2NF 要求实体的属性完全依赖于主关键字 所谓完全依赖是指不能存 在仅依赖主关键字一部分的属性 如果存在 那么这个属性和主关键字的这一部分应该分 离出来形成一个新的实体 新实体与原实体之间是一对多的关系 为实现区分通常需要为 表加上一个列 以存储各个实例的惟一标识 简而言之 第二范式就是属性完全依赖于主 键 14 33 3 3 第三范式 3NF 属性不依赖于其它非主属性 消除传递依赖 错误设计 编号姓名生日年龄 1张飞1978 10 132 2关羽1982 10 128 2李达197931 正确设计 编号姓名年龄 1张飞32 2关羽27 3李达31 满足第三范式 3NF 必须先满足第二范式 2NF 简而言之 第三范式 3NF 要求一个数据库表中不包含已在其它表中已包含的非主关键字信息 例如 存在一个部门 信息表 其中每个部门有部门编号 dept id 部门名称 部门简介等信息 那么在的员 工信息表中列出部门编号后就不能再将部门名称 部门简介等与部门有关的信息再加入员 工信息表中 如果不存在部门信息表 则根据第三范式 3NF 也应该构建它 否则就会有 大量的数据冗余 简而言之 第三范式就是属性不依赖于其它非主属性 学习作业 自己以文本的形式描述一个详细的个人信息表 UserInfo 表 想 下需要哪些字段 是否满足以上的条件 参照以上列子列好之后发送到 提出疑问和难点 共同进步 softmaster softmaster 谢谢合作 15 33 4 SQL 联合查询数据 多表查询数据 4 1 left join on 4 2 join on 4 3 group by 分组 a 语文 b 语文 c 数学 语文 a b 数学 c 4 4 having 条件 order by 排序 DESC 和 ASC having 作用和意义同 where 一致 但是 Having 只能与 GROUP By 使用 学习作业 问题 1 向表 User Info 添加 2 条数据 用户 UserID 为 zmh 27 和 tom002 16 33 问题 2 分组查询用户字段 查看有多少个独立的用户 问题 3 查出用户表中重复出现 2 次的用户账号 和次数 问题 4 查询 dbo Book Order 中的 2 个字段 OrderNumber TotalMoney 查找出价格最高的用户 和价格 问题 5 查询 dbo Book Order 中的 2 个字段 OrderNumber TotalMoney 查找出价格最低的用户 和价格 17 33 5 SQL 函数运用 5 1 count 1 计数函数 5 2 sum 求和函数 SUM COUNT 多字段使用的时候要进行分组 GROUP BY SUM 5 3 substring 字段 5 4 max 字段 min 字段 5 5 REPLACE 替换字符 18 33 6 高级编程 6 1 存储过程创建 删除 a 优点 1 安全性好 2 性能好 检索数据速度快 b 怎么用存储过程 创建存储过程的语法 CREATE PROC Proc Book Order List AS BEGIN sql语句块 END 修改存储过程的语法 ALTER PROC Proc Book Order List AS BEGIN sql语句块 END 删除存储过程 DROP PROC Proc Book Order List 运行存储过程 EXEC Proc Book Order List 作业 1 创建存储过程 查询用户分组中大于1的用户查询出来 2 创建存储过程 查出价格最好的价格和用户 订单号 表 Book Order 3 修改储过程 查询全部字段 4 执行存储过程 5 删除存储过程 19 33 6 2 存储过程修改 测试 6 3 多参数存储过程操作 6 4 触发器 添加删除 修改 测试 关键字 TRIGGER FOR 关键字的类型有 INSERT update delete 1 触发器放在SQL里面直接运行 无需于代码结合使用 2 存储过程 一般都是与这个 C C java 等语言结合使用 20 33 6 5 触发器虚标 deleted inserted 表的作用 21 33 22 33 6 6 时间类型 23 33 6 7 取消重复关键字 UIDCIDIsOpen 24 33 a1是是 a2是是 a3是是 a4否否 a5否否 a6否否 a7是是 a8是是 a9是是 a10否否 字段中带有字表列 25 33 7 数据的导入与导出 Excel txt 附件数据库 7 1 导入 Excel 数据到数据库中 7 2 导入 txt 数据到数据库中 7 3 附件数据库 26 33 7 4 分离数据库 7 5 数据库自动备份方案 7 6 数据库练习 27 33 8 附件学习参考资料 8 1 sybase 数据库建模 FK STAFFINF REFERENCE DEPARTME FK STAFF SA REFERENCE STAFFINF Staffinfo ID StaffName DepartmentID RegTime int varchar 50 int datetime Department Classify ID Name int nvarchar 30 Staff Salary ID StaffID Salary RegTime int int float datetime 28 33 8 2 数据表高级查询参考 CREATE PROCEDURE dbo sp Shop LuckyItemGet userID int bonusID int Resultnvarchar 100 OUTPUT ProductNum nvarchar 50 OUTPUT AS declare randNum int declare plevel int declare ProductName Nvarchar 50 declare pcount INT DECLARE limitday INT DECLARE limitcount INT DECLARE nowcount INT DECLARE bindcount int DECLARE lastTime DATETIME 最近获奖时间 DECLARE Timespan INT 获奖时间间隔 小时 Exec Gdcn ShopRateGet randNum OUTPUT PRINT CAST randNum AS varchar select ProductNum ProductNum bindcount productcount pcount pcount plevel plevel limitday limitday limitcount limitcount from ShopCjItem where randNum pmin and randNum 0 and pcount 0 AND pcount bindcount begin PRINT CAST ProductNum AS varchar 判断物品限制 SELECT nowcount COUNT FROM A ShopCjLog WHERE plevel plev