数据库设计与SQL.ppt

数据库设计与SQL,主要内容：逻辑设计与物理设计结构化查询语言（SQL）,逻辑设计,基本概念实体：对象的集合，如学生、课程、教师属性：描述实体的性质，如学号、成绩联系：实体间的关系，1-1，1-n，n-mE-R模型逻辑模型与具体的DBMS无关,学生,学习,课程,课程名称,物理设计（1）,ER模型转换规则规则1：实体表属性列规则2：一对多:用两张表，多方放一方的主键，如部门、雇员规则3：多对多，联系表，三张表例：学生、课程、成绩（学号、课程号、成绩）,物理设计（2）,主要设计内容表列：数据类型、可否为null、缺省值等主键外键索引其它：如视图、存储过程、触发器等设计工具：SQLServer企业管理器，PowerDesigner等,物理设计（3）,常见数据类型字符型：varchar(100)，一般不用char整型：int或bigint数字：numeric(10,2)布尔型：int日期型：datetime,物理设计（4）,主键设计：必须为每个表设计主键列选择选择有意义的列，如学号选择无意义的列，如专门作为主键的列id实现自增长：推荐使用GUID自管理,物理设计（5）,命名规范用有意义的英文，避免使用数据库的保留字或关键字表名、列名用有意义的英文单词组成，一般为名词，单词一般不缩写，单词的首字母大写,如有必要表名可以用模块名作为前缀注意：命名时不可用汉语作为表名、列名。,SQL,特点：非过程化语言标准ANSI/ISO：SQL-92、CoreSQL-99组成数据定义语言：如定义表、列数据操作语言：如增加、删除记录数据控制语言：如安全控制数据查询语言：SELECT,查询语句SELECT（1）,示例1：返回表author的所有行、所有列SELECT*FROMauthor;author:表名示例2：返回表author的指定列的所有行SELECTau_fname,au_lname,phoneASTelephone,city,stateFROMauthors注意Telephone是phone的别名,查询语句SELECT（2）,示例3：用WHERE子句指明查询条件SELECTau_fname,au_lname,phoneASTelephoneFROMauthorsWHEREstate=CAandau_lnameMcBadden返回居住在加利福尼亚州且不姓McBadden的作者列。注1：字符串，用单引号，CA注2：AND,OR,NOT注3：谓词：=,!=,LIKE,IN等,查询语句（3）,示例4：Like的使用，显示所有姓以M开头的的作者SELECTau_fname,au_lname,phoneASTelephoneFROMauthorsWHEREau_lnameLIKEM%:零个或任意多个字符的字符-:任意的单个字符,查询语句（4）,示例5：IN的使用SELECTau_lname,stateFROMauthorsWHEREstateIN(CA,IN,MD)该语句等同于：SELECTau_lname,stateFROMauthorsWHEREstate=CAORstate=INORstate=MD,查询语句（5）,示例6:DISTINCT避免重复SELECTDISTINCTau_idFROMauthorsORDERBYau_id示例7:ORDERBY子句用于排序SELECTau_fname,au_lname,phoneASTelephone,city,stateFROMauthorsORDERBYau_lnameASC,au_fnameASCASC：升序，DESC：降序,查询语句（6）,示例8:简单连接，内连接SELECTa.pub_name,SUBSTRING(b.title,1,30)ASTitleFROMpublishersa,titlesbWHEREa.pub_id=b.pub_idORDERBYa.pub_name此相当于内连接，即若pub_id在publishers中存在但不存在于titles,则相应的行不出现在查询中，与此相比，左外连接则将出现在查询中,查询语句（7）,示例9:外连接SELECTa.pub_name,SUBSTRING(b.title,1,30)ASTitleFROMpublishersaLEFTOUTERJOINtitlesbONa.pub_id=b.pub_idORDERBYa.pub_name,查询语句（8）,示例10：简单聚合函数SELECTtype,SUM(price),SUM(advance)FROMtitlesWHERE(typeLIKE%cook)GROUPBYtypeORDERBYtype其它常见聚合函数如：count,avg,max,min等,查询语句（9）,示例11:分页显示查询语句模版：SELECT*FROM(SELECTTOP15*FROMauthors（可加WHERE子句）ORDERBYau_id)aWHERE(au_idNOTIN(SELECTTOP10au_idFROMauthors（同样的WHERE子句）ORDERBYau_id)基本原理:在所查询的前n页条记录中，去掉前(n-1)条记录，即为第n页的查询记录,其它语句（1）,示例12，增加一条记录INSERTINTOdiscounts(discounttype,discount)VALUES(CustomerDiscount,6.0)示例13：修改一条记录UPDATEdiscountsS