《SQLSERVER基础》PPT课件.ppt

SQLSERVER数据库设计与实现 主讲人 潘伟 西华师范大学计算机学院通信专业系列课程 第3章SQL基础 3 1SQLServer中的数据类型3 2数据表设计与创建3 3数据查询3 4修改表结构3 5插入 更新和删除表数据3 6约束 默认和规则3 7系统数据库3 8创建用户数据库 3 1SQLServer中的数据类型 3 1SQLServer中的数据类型 3 1SQLServer中的数据类型 3 1SQLServer中的数据类型 3 2数据表设计与创建 学生基本信息表 3 2数据表设计与创建 教师基本信息表 3 2数据表设计与创建 学生成绩表 3 2数据表设计与创建 课程信息表 3 2数据表设计与创建 课程信息表 课程安排表 3 2数据表设计与创建 3 2数据表设计与创建 院系代码表 专业代码表 教研室信息表 3 2数据表设计与创建 教室信息表 3 2数据表设计与创建 3 2数据表设计与创建 在企业管理其中创建SQL指令 CREATETABLE 所要定义的基本表的名字 库名 所有者 所有者 表名 组成该表的各个属性 列 涉及相应属性列的完整性约束条件 涉及一个或多个属性列的完整性约束条件 常用完整性约束主码约束 PRIMARYKEY唯一性约束 UNIQUE非空值约束 NOTNULL默认值约束 default检查约束 check参照完整性约束 foreignkey references PRIMARYKEY与UNIQUE的区别 3 2数据表设计与创建 学生基本信息表 举例 createtablestudent dbo student STUD IDchar 10 NOTNULLdefault 0401010101 NAMEnvarchar 4 NOTNULL BIRTHDAYdatetime GENDERnchar 1 check GENDER f orGENDER m ADDRESSnvarchar 20 TELCODEchar 12 ZIPCODEchar 6 MARKintdefault 560 primarykey STUD ID 3 3修改表结构 修改表 ALTERTABLE ADD 完整性约束 DROP ALTERCOLUMN 要修改的基本表ADD子句 增加新列和新的完整性约束条件DROP子句 删除指定的完整性约束条件DROPCOLUMN子句 删除指定的完整性约束条件ALTERCOLUMN子句 用于修改列名和数据类型 标准SQL中用altercolumn实现该功能 例 向Student表增加 入学时间 列 其数据类型为日期型 ALTERTABLEStudentADDScomedatetime 不论基本表中原来是否已有数据 新增加的列一律为空值 3 3修改表结构 修改表 删除属性列直接 间接删除把表中要保留的列及其内容复制到一个新表中删除原表再将新表重命名为原表名直接删除属性列 新 例 ALTERTABLEStudentDropcolumnScome 例 将出生日期的数据类型改为smalldatetime ALTERTABLEStudentaltercolumnBIRTHDAYSMALLDATETIME 注 修改原有的列定义有可能会破坏已有数据 3 4数据查询 客户程序 SQLSERVER SELECT FROMSALES 查询产生一个虚拟表 看到的是表形式显示的结果 但结果并不真正存储 每次执行查询只是现从数据表中提取数据 并按照表的形式显示出来 怎么查的 SELECTFROM WHERE ORDERBY ASC或DESC 数据查询 基础 数据查询 基础 查询全部的行和列 查询部分行 数据查询 基础 使用AS来命名列 使用 来命名列 数据查询 基础 查询空行 使用常量列 思考 非空行怎么查 数据查询 基础 限制固定行数 返回百分之多少行 数据查询 基础 升序排列 降序排列 数据查询 基础 按多列排序 思考 排序中的字段 可以使用表达式吗 如果不可以 请说明原因 如果可以 请举例说明 模糊查询 LIKE 查询时 字段中的内容并不一定与查询内容完全匹配 只要字段中含有这些内容 通配符 思考 身份证号码的约束表达式怎么写 模糊查询 ISNULL 把某一字段中内容为空的记录查询出来 模糊查询 BETWEEN 把某一字段中内容在特定范围内的记录查询出来 模糊查询 IN 把某一字段中内容与所列出的查询内容列表匹配的记录查询出来 聚合函数 SUM AVG 聚合函数 MAX MIN COUNT 分组汇总 分组查询 GROUPBY 分组查询 思考 执行以下的T SQL 结果如何 分组查询 多列分组 怎么样来统计每次的内部测试不同学员的成绩 分组查询 多列分组 分组查询 问题 在以上统计内部测试成绩的基础上 如果只想看补考的学员的成绩 怎么办 分组查询 再看看 增加条件 要求该学员的Cno在分组内出现过一次以上 分组查询 HAVING 分组查询 对比 WHERE子句从数据源中去掉不符合其搜索条件的数据GROUPBY子句搜集数据行到各个组中 统计函数为各个组计算统计值HAVING子句去掉不符合其组搜索条件的各组数据行 分组查询 思考 思考 分析以下T SQL的含义 COMPUTE子句 COMPUTE行聚合函数 BY列名集合 COMPUTE子句生成合计作为附加的汇总列出现在结果集的最后 当与BY一起使用时 COMPUTE子句在结果集内对指定列进行分类汇总 可在同一查询内指定COMPUTEBY和COMPUTE select fromstud infoorderbyleft address 3 computeavg mark byleft address 3 computeavg mark COMPUTE子句 多表联结查询 问题 学员内部测试成绩查询的每次显示的都是学员的编号信息 因为该表中只存储了学员的编号 实际上最好显示学员的姓名 而姓名存储在学员信息表 如何同时从这两个表中取得数据 连接查询 SQL中连接查询的主要类型广义笛卡尔积等值连接 含自然连接 非等值连接查询自身连接查询外连接查询复合条件连接查询 多表联结查询 分类 内联结 INNERJOIN 外联结 左外联结 LEFTJOIN 右外联结 RIGHTJOIN 完整外联结 FULLJOIN 多表联结查询 内联结 1 多表联结查询 内联结 2 多表联结查询 三表联结 多表联结查询 左外联结 多表联结查询 右外联结 SQLServer中的函数 SQLServer中的函数 SQLServer中的函数 SQLServer中的函数 SQLServer中的函数 3 5插入 更新和删除表数据 向表中插入数据就是将一条或多条记录添加到表尾 T SQL中使用INSERT命令完成数据插入 INSERT INTO 列名 VALUES 插入单行数据 注意事项1 每次插入一行数据 不可能只插入半行或者几列数据 因此 插入的数据是否有效将按照整行的完整性的要求来检验 3 5插入 更新和删除表数据 注意事项2 每个数据值的数据类型 精度和小数位数必须与相应的列匹配 插入单行数据 3 5插入 更新和删除表数据 注意事项3 不能为标识列指定值 因为它的数字是自动增长的 注意事项4 如果在设计表的时候就指定了某列不允许为空 则必须插入数据 属性列NAME不能为空 插入单行数据 3 5插入 更新和删除表数据 注意事项5 插入的数据项 要求符合检查约束的要求 注意事项6 具有缺省值的列 可以使用DEFAULT 缺省 关键字来代替插入的数值 插入单行数据 3 5插入 更新和删除表数据 插入多行数据 INSERTINTO 列名 SELECTFROM 方式1 3 5插入 更新和删除表数据 SELECT 列名 INTOFROM 猜一猜 该语句可以执行两次吗 插入多行数据 SELECTINTO插入多行数据的时候 如何插入新的标识列 SELECTIDENTITY 数据类型 标识种子 标识增长量 AS列名INTO新表FROM原始表 3 5插入 更新和删除表数据 插入多行数据 INSERTINTO 列名 SELECTUNIONSELECTUNION 插入多行数据 3 5插入 更新和删除表数据 UPDATESET WHERE 更新数据行 3 5插入 更新和删除表数据 删除数据行 3 5插入 更新和删除表数据 DELETEFROM WHERE 思考 以下的删除语句可以执行吗 删除数据行 3 5插入 更新和删除表数据 TRUNCATETABLE 删除数据行 3 5插入 更新和删除表数据 案例分析1 要求 某公司印了一批充值卡 卡的密码是随机生成的 现在出现这个问题 卡里面的 O和0 哦和零 i和1 哎和一 用户反映说看不清楚 公司决定 把存储在数据库中的密码中所有的 哦 都改成 零 把所有的 i 都改成 1 请编写SQL语句实现以上要求 数据库表名 Card 密码字段名 PassWord 案例分析1 分析 这是更新语句 需要使用UPDATE语句 因为牵涉到字符串的替换 需要使用到SQLServer中的函数Replace 案例分析1 T SQL 两行SQL语句 一行SQL语句 案例分析2 要求 在数据库表中有以下字符数据 如 13 1 13 2 13 3 13 10 13 100 13 108 13 18 13 11 13 15 14 1 14 2现在希望通过SQL语句进行排序 并且首先要按照前半部分的数字进行排序 然后再按照后半部分的数字进行排需 输出要排成这样 13 1 13 2 13 3 13 10 13 11 13 15 13 18 13 100 13 108 14 1 14 2数据库表名 SellRecord 字段名 ListNumber 案例分析2 分析 这是查询语句 需要使用SELECT语句 需要使用到ORDERBY进行排序 并且在ORDERBY的排序列中 也需要重新计算出排序的数字来 前半部分的数字 可以从先找到 符号的位置 然后 取其左半部分 最后再使用Convert函数将其转换为数字 Convert int Left ListNumber CharIndex ListNumber 1 后半部分的数字 可以先找到 符号的位置 然后把从第一个位置到该位置的全部字符替换为空格 最后再使用Convert函数将其转换为数字 Convert int Stuff ListNumber 1 Charindex ListNumber 案例分析2 T SQL 创建约束 ALTERTABLE ADDCONSTRAINT约束名CHECK 约束表达式 DEFAULT默认值FOR字段名 PRIMARYKEYCLUSTERED 列名集合 UNIQUE 列名集合 FOREIGNKEY 列名 REFERENCES主键表 列名 DROPCONSTRAINT约束名 3 6约束 默认和规则 例 ALTERTABLEStudentADDconstraintck studentCheck GENDER 女 ORGENDER 男 ALTERTABLEStudentADDconstraintDE studentDEFAULT 男 forGENDERALTERTABLEStudentADDconstraintPK studentPRIMARYKEY STUD ID ALTERTABLEStudentADDconstraintFK student DEPFOREIGNKEY DEPTCODE REFERENCESDEP DEPTCODE 默认默认是一种数据库对象 定义一次后 可以被一次或多次应用于任意表中的一列或多列 还可以用于用户定义的数据类型 创建和使用默认可以使用企业管理器和T SQL语句 CREATEDEFAULTdefault nameASconstraint expressionEXECsp bindefaultdefault name table name column name user datetype 3 6约束 默认和规则 例 createdefaultMR studentas 600 execsp bindefaultMR student student MARK 规则规则与CHECK约束的关系相对于默认与DEFAULT约束的关系一样 规则这种数据库对象的作用与CHECK约束一样 只不过规则不固定于那个列 而是创建好以后可以随便地绑定于表中地某个列上 创建规则的语句 CREATERULErule nameAScondition expression 3 6约束 默认和规则 例 createruleage studentas age 15and age 30execsp bindruleage student student age 3 7系统数据库 master数据库 master数据库是SQLServer2000中的总控数据库 它记录系统中所有系统级的信息 它对其他的数据库实施管理和控制的功能 同时 还保存了用于SQLServer管理的许多系统级别信息 master数据库记录所有的登录帐户和系统配置设置 记录所有其它的数据库 其中包括数据库文件的位置 还记录SQLServer的初始化信息 它始终有一个可用的最新master数据库备份 3 7系统数据库 model数据库 model数据库为用户新创建的数据库提供模板和原型 包含用户数据库中应该包含的所有系统表的结构 当用户创建数据库时 系统会自动地把model数据库中的内容复制到新建的用户数据库中 3 7系统数据库 msdb数据库 当很多用户在使用一个数据库时 经常会出现多个用户对同一数据的修改而造成数据不一致现象 或者是用户对某些数据和对象的非法操作等 为了防止上述现象的发生 SQLServer的代理程序能够按照系统管理员的设定来监控上述现象的发生 否则及时向系统管理员发出警报 那么当代理程序调度警报和作业 记录操作员的时候 系统要用到或实时产生许多相关信息 这些存储在msdb数据库里面 3 7系统数据库 tempdb数据库 tempdb数据库保存所有的临时表和临时存储过程 tempdb数据库是全局资源 所有连接到系统的用户的临时表和存储过程都被存储在该数据库中 SQLServer2000还提供了两个样板数据库 pubs和Northwind 其中pubs数据库记录了一个虚构的出版公司的数据信息 而Northwind数据库则保存了一个虚构的贸易公司的数据信息 3 8创建用户数据库 CREATEDATABASEdatabase nameON PRIMARY NAME logical file name FILENAME os file name SIZE size MAXSIZE max size UNLIMTED FILEGROWTH grow increment n LOGON NAME logical file name FILENAME os file name SIZE size MAXSIZE max size UNLIMTED FILEGROWTH grow increment n CREATEDATABASEstudentONPRIMARY NAME student data FILENAME c ProgramFiles MicrosoftSQLServer MSSQL Data student mdf SIZE 25 MAXSIZE 100 FILEGROWTH 15 LOGON NAME student log FILENAME c ProgramFiles MicrosoftSQLServer MSSQL Data student ldf SIZE 10 MAXSIZE 20 FILEGROWTH 2 3 8创建用户数据库