SQLSERVER基础.pptVIP

SQL SERVER 数据库设计与实现,主讲人：潘伟,西华师范大学计算机学院通信专业系列课程,第3章 SQL 基础,3.1 SQL Server中的数据类型 3.2数据表设计与创建 3.3 数据查询 3.4修改表结构 3.5 插入、更新和删除表数据 3.6 约束、默认和规则 3.7 系统数据库 3.8 创建用户数据库,3.1 SQL Server中的数据类型,3.1 SQL Server中的数据类型,3.1 SQL Server中的数据类型,3.1 SQL Server中的数据类型,3.2 数据表设计与创建,学生基本信息表,3.2 数据表设计与创建,教师基本信息表,3.2 数据表设计与创建,学生成绩表,3.2 数据表设计与创建,课程信息表,3.2 数据表设计与创建,课程信息表,课程安排表,3.2 数据表设计与创建,3.2 数据表设计与创建,院系代码表,专业代码表,教研室信息表,3.2 数据表设计与创建,教室信息表,3.2 数据表设计与创建,3.2 数据表设计与创建,在企业管理其中创建 SQL指令,CREATE TABLE （ ， ， ）； ：所要定义的基本表的名字 库名. 所有者 . | 所有者. 表名 ：组成该表的各个属性（列） ：涉及相应属性列的完整性约束条件 ：涉及一个或多个属性列的完整性约束条件,常用完整性约束 主码约束： PRIMARY KEY 唯一性约束：UNIQUE 非空值约束：NOT NULL 默认值约束：default 检查约束：check 参照完整性约束:foreign keyreferences ？PRIMARY KEY与 UNIQUE的区别？,3.2 数据表设计与创建,学生基本信息表,举 例,create table student.dbo.student ( STUD_ID char(10) NOT NULL default(0401010101), NAME nvarchar(4) NOT NULL, BIRTHDAY datetime, GENDER nchar(1) check(GENDER=f or GENDER=m), ADDRESS nvarchar(20), TELCODE char(12), ZIPCODE char(6), MARK int default(560) primary key(STUD_ID) ),3.3 修改表结构,修改表,ALTER TABLE ADD 完整性约束 DROP ALTER COLUMN ； ：要修改的基本表 ADD子句：增加新列和新的完整性约束条件 DROP子句：删除指定的完整性约束条件 DROP COLUMN子句：删除指定的完整性约束条件 ALTER COLUMN子句：用于修改列名和数据类型。标准SQL中用alter column实现该功能,例 向Student表增加“入学时间”列，其数据类型为日期型。 ALTER TABLE Student ADD Scome datetime； 不论基本表中原来是否已有数据，新增加的列一律为空值。,3.3 修改表结构,修改表,删除属性列 直接/间接删除 把表中要保留的列及其内容复制到一个新表中 删除原表 再将新表重命名为原表名 直接删除属性列:(新) 例：ALTER TABLE Student Drop column Scome,例 将出生日期的数据类型改为smalldatetime。 ALTER TABLE Student alter column BIRTHDAY SMALLDATETIME； 注：修改原有的列定义有可能会破坏已有数据,3.4 数据查询,客户程序,SQL SERVER,SELECT * FROM SALES,查询产生一个虚拟表，看到的是表形式显示的结果，但结果并不真正存储，每次执行查询只是现从数据表中提取数据，并按照表的形式显示出来,怎么查的？,SELECT FROM WHERE ORDER BY ASC或DESC,数据查询-基础,数据查询-基础,查询全部的行和列,查询部分行,数据查询-基础,使用AS来命名列,使用=来命名列,数据查询-基础,查询空行,使用常量列,思考：非空行怎么查？,数据查询-基础,限制固定行数,返回百分之多少行,数据查询-基础,升序排列,降序排列,数据查询-基础,按多列排序,思考：排序中的字段，可以使用表达式吗？ 如果不可以，请说明原因； 如果可以，请举例说明；,模糊查询LIKE,查询时，字段中的内容并不一定与查询内容完全匹配，只要字段中含有这些内容,通配符,思考：身份证号码的约束表达式怎么写？,模糊查询IS NULL,把某一字段中内容为空的记录查询出来,模糊查询BETWEEN,把某一字段中内容在特定范围内的记录查询出来,模糊查询IN,把某一字段中内容与所列出的查询内容列表匹配的记录查询出来,聚合函数,SUM,AVG,聚合函数,MAX、MIN,COUNT,分组汇总,分组查询GROUP BY,分组查询,思考：执行以下的T-SQL：, 结果如何？,分组查询多列分组,怎么样来统计每次的内部测试不同学员的成绩？,分组查询多列分组,分组查询问题,在以上统计内部测试成绩的基础上， 如果只想看补考的学员的成绩，怎么办？,分组查询再看看,增加条件：要求该学员的Cno在分组内出现过一次以上,分组查询HAVING,分组查询对比,WHERE子句从数据源中去掉不符合其搜索条件的数据 GROUP BY子句搜集数据行到各个组中，统计函数为各个组计算统计值 HAVING子句去掉不符合其组搜索条件的各组数据行,分组查询思考,思考：分析以下T-SQL的含义,COMPUTE子句,COMPUTE 行聚合函数 BY 列名集合 COMPUTE子句生成合计作为附加的汇总列出现在结果集的最后。 当与BY一起使用时，COMPUTE子句在结果集内对指定列进行分类汇总。 可在同一查询内指定COMPUTE BY和COMPUTE。,select * from stud_info order by left(address,3) compute avg(mark) by left(address,3) compute avg(mark),COMPUTE子句,多表联结查询问题,学员内部测试成绩查询的每次显示的都是学员的编号信息， 因为该表中只存储了学员的编号； 实际上最好显示学员的姓名，而姓名存储在学员信息表； 如何同时从这两个表中取得数据？,连接查询,SQL中连接查询的主要类型 广义笛卡尔积 等值连接(含自然连接) 非等值连接查询 自身连接查询 外连接查询 复合条件连接查询,多表联结查询分类,内联结(INNER JOIN) 外联结 左外联结 (LEFT JOIN) 右外联结 (RIGHT JOIN) 完整外联结(FULL JOIN),多表联结查询内联结-1,多表联结查询内联结-2,多表联结查询三表联结,多表联结查询左外联结,多表联结查询右外联结,SQL Server中的函数,SQL Server中的函数,SQL Server中的函数,SQL Server中的函数,SQL Server中的函数,3.5插入、更新和删除表数据,向表中插入数据就是将一条或多条记录添加到表尾。T-SQL中使用INSERT命令完成数据插入。,INSERT INTO 列名 VALUES ,插入单行数据,注意事项1:每次插入一行数据，不可能只插入半行或者几列数据，因此，插入的数据是否有效将按照整行的完整性的要求来检验；,3.5 插入、更新和删除表数据,注意事项2:每个数据值的数据类型、精度和小数位数必须与相应的列匹配；,插入单行数据,3.5 插入、更新和删除表数据,注意事项3:不能为标识列指定值，因为它的数字是自动增长的；,注意事项4:如果在设计表的时候就指定了某列不允许为空，则必须插入数据；,属性列NAME不能为空,插入单行数据,3.5 插入、更新和删除表数据,注意事项5:插入的数据项，要求符合检查约束的要求,注意事项6:具有缺省值的列，可以使用DEFAULT（缺省）关键字来代替插入的数值,插入单行数据,3.5 插入、更新和删除表数据,插入多行数据,INSERT INTO (列名) SELECT FROM ,方式1：,3.5 插入、更新和删除表数据,SELECT (列名) INTO FROM ,猜一猜：该语句可以执行两次吗？,插入多行数据,? SELECT INTO插入多行数据的时候，如何插入新的标识列？,SELECT IDENTITY(数据类型,标识种子,标识增长量) AS 列名 INTO 新表 FROM 原始表,3.5 插入、更新和删除表数据,插入多行数据,INSERT INTO (列名) SELECT UNION SELECT UNION ,插入多行数据,3.5 插入、更新和删除表数据,UPDATE SET WHERE ,更新数据行,3.5 插入、更新和删除表数据,删除数据行,3.5 插入、更新和删除表数据,DELETE FROM WHERE ,思考：以下的删除语句可以执行吗？,删除数据行,3.5 插入、更新和删除表数据,TRUNCATE TABLE ,=,删除数据行,3.5 插入、更新和删除表数据,案例分析1要求,某公司印了一批充值卡，卡的密码是随机生成的，现在出现这个问题： 卡里面的“O和0”（哦和零）“i和1”（哎和一），用户反映说看不清楚，公司决定，把存储在数据库中的密码中所有的“哦”都改成“零”，把所有的“i”都改成“1”； 请编写SQL语句实现以上要求； 数据库表名：Card；密码字段名：PassWord；,案例分析1分析,这是更新语句，需要使用UPDATE语句； 因为牵涉到字符串的替换，需要使用到SQL Server中的函数Replace；,案例分析1T-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语句。 需要使用到ORDER BY进行排序，并且在ORDER BY的排序列中，也需要重新计算出排序的数字来。 前半部分的数字，可以从先找到“-”符号的位置，然后，取其左半部分，最后再使用Convert函数将其转换为数字： Convert(int, Left(ListNumber, CharIndex(-, ListNumber)-1) 后半部分的数字，可以先找到“-”符号的位置，然后把从第一个位置到该位置的全部字符替换为空格，最后再使用Convert函数将其转换为数字： Convert(int, Stuff(ListNumber,1, Charindex(-, ListNumber), ),案例分析2T-SQL,创建约束,ALTER TABLE ADD CONSTRAINT约束名 CHECK (约束表达式) |DEFAULT 默认值 FOR 字段名 |PRIMARY KEY CLUSTERED(列名集合) |UNIQUE (列名集合) |FOREIGN KEY(列名) REFERENCES 主键表(列名) DROP CONSTRAINT 约束名 ,3.6 约束、默认和规则,例 ALTER TABLE Student ADD constraint ck_student Check(GENDER=女 OR GENDER=男) ALTER TABLE Student ADD constraint DE_student DEFAULT 男 for GENDER ALTER TABLE Student ADD constraint PK_student PRIMARY KEY (STUD_ID) ALTER TABLE Student ADD constraint FK_student_DEP FOREIGN KEY (DEPTCODE) REFERENCES DEP(DEPTCODE),默认 默认是一种数据库对象，定义一次后，可以被一次或多次应用于任意表中的一列或多列，还可以用于用户定义的数据类型。创建和使用默认可以使用企业管理器和T-SQL语句。 CREATE DEFAULT default_name AS constraint_expression EXEC sp_bindefault default_name table_name.column_name,|user_datetype,3.6 约束、默认和规则,例 create default MR_student as 600 exec sp_bindefault MR_student,student.MARK,规则 规则与CHECK约束的关系相对于默认与DEFAULT约束的关系一样，规则这种数据库对象的作用与CHECK约束一样，只不过规则不固定于那个列，而是创建好以后可以随便地绑定于表中地某个列上。 创建规则的语句： CREATE RULE rule_name AS condition_expression,3.6 约束、默认和规则,例 create rule age_student as age15 and age30 exec sp_bindrule age_student,student.age,3.7 系统数据库,master数据库,master数据库是SQL Server 2000中的总控数据库，它记录系统中所有系统级的信息。它对其他的数据库实施管理和控制的功能，同时，还保存了用于SQL Server管理的许多系统级别信息。master数据库记录所有的登录帐户和系统配置设置；记录所有其它的数据库，其中包括数据库文件的位置；还记录SQL Server的初始化信息，它始终有一个可用的最新master数据库备份。,3.7 系统数据库,model数据库,model数据库为用户新创建的数据库提供模板和原型。包含用户数据库中应该包含的所有系统表的结构。当用户创建数据库时，系统会自动地把model数据库中的内容复制到新建的用户数据库中。,3.7 系统数据库,msdb数据库,当很多用户在使用一个数据库时，经常会出现多个用户对同一数据的修改而造成数据不一致现象，或者是用户对某些数据和对象的非法操作等。为了防止上述现象的发生，SQL Server的代理程序能够按照系统管理员的设定来监控上述现象的发生，否则及时向系统管理员发出警报。那么当代理程序调度警报和作业、记录操作员的时候，系统要用到或实时产生许多相关信息，这些存储在msdb数据库里面。,3.7 系统数据库,tempdb数据库,tempdb数据库保存所有的临时表和临时存储过程。 tempdb数据库是全局资源，所有连接到系统的用户的临时表和存储过程都被存储在该