数据库基础培训

1、数据库基础培训2016-6-6培训内容第三部分：考核说明史l关系数据库、范式、主流数据库lSQL环境l常用数据库物理结构、逻辑结构、系统组成 “能够完成一般项目能够完成一般项目对数据库的要求：对数据库的要求：基本操作、简单设基本操作、简单设计、计、SQL编程编程 ”据库设计原则v熟练掌握SQL开发v精通常用SQL语句 “能够完成一般项目能够完成一般项目对数据库的要求：对数据库的要求：基本操作、简单设基本操作、简单设计、计、SQL编程编程 ”图、临时表、数据类型v查询数据（聚合和分组、多表查询）、修改数据v存储过程、触发器、游标、函数、包、临时表v事务和并发v常用技巧（常用函数、系统表）v数据库

2、设计v结构l内模式、概念模式、外模式v工作流程l见图v发展历史l第一代（网状、层次）、第二代（关系）、第三代系数据库l主流数据库，大中项目，商用较广Oracle、DB2、Informix、Sybase、Ms SQLl目前项目Oracle、Informix、DB2较常用v数据库的SQL环境l目录(Catalog)、模式(Schema)、模式对象ormix结构概述vDB2结构概述QL*Plus、PLSQL DeveloperlInformix：dbaccess、Informix客户端lDB2：DB2客户端、Quest CentrallMSSQL：提供易用的客户端工具rtment(did integ

3、er,dname varchar(40),primary key (did)/表名避免使用保留字，应有明显含义，注意长度/在Informix中不建议建表时同时建主键外键e table employe(sid integer,sname varchar(20) not null,sex varchar(4) default m,did integer,primary key (sid),foreign key (did) references department (did)请考虑复合主键和复合外键的建立table employerename to employe_newlrename empl

4、oye to employe_newvInformix修改表名lrename table employe to employe_newrtmentldrop table employe_newmploye add birthday datelalter table employe add(birthday date,salary char(10)rmixlalter table employe add birthday date before didlalter table employe add(birthday date before did,salary char(10) before

5、did)oye modify birthday char(10)lalter table employe modify(birthday char(10),salary int)table employe rename column birthday to birdayvInformix修改列名lrename column employe. birthday to birdayable employe drop column birdaylalter table employe drop(birthday,salary)er table employe drop birdaylalter ta

6、ble employe drop(birthday,salary)ter table employe add primary key (sid)/Informixalter table employe add constraint primary key (sid)请考虑复合主键和复合外键的建立e可从系统表获得alter table employe drop constraint constraint_name/oraclealter table employe drop primary key/Informixalter table employe modify sid integer单字段

7、主键，在对列执行alter操作时可删除该主键和索引；单字段索引没有这个问题X_employe_sname on employe (sname asc)v删除索引drop index IDX_employe_sname/constraint_name可从系统表获得请考虑复合索引的建立le employeadd foreign key (did) references department (did) on delete cascadevInformix建外键alter table employe add constraintforeign key (did) references departm

8、ent (did)请考虑复合外键的建立le employe drop constraint constraint_name/ constraint_name可从系统表获取制l文本和图形l自定义（事务级别）l全局临时表（会话级别）/语法与普通建表类似/Oracle临时表为数据级别，数据字典一直有/Informix临时表为内存表制数据l合并分隔数据l安全性v缺点l性能、更新限制mploye_viewasselect *from employev删除视图drop view employe_view请考虑如何定制视图，都有哪些情况nct|all *|column1 as a, column2 as b

9、, from table_name1, table_name2where search_conditiongroup by group_by_expressionhaving search_conditionorder by order_by_expression asc|descland、or、not、in、like（%、_、）lBetween a and bl连接符（+、|）l数值运算l函数lIs null/is not nullmin()、avg()、count()l聚合的重值处理lgroup bylhaving请验证count(*)和count(column)的区别a.dname, c

10、ount(sid) as employe_numfrom department a, employe bwhere a.did = b.didgroup by a.did, a.dnamehaving count(sid) 2请验证count(*)和count(column)的区别a.sid, a.sname, b.sid, b.snamefrom employe a, employe bwhere a.age = b.age + 10did, a.dname, b.sid, b.snamefrom department a inner join employe b on a.did = b.

11、did请验证连接条件绝对真或绝对假的情况，下面连接考虑同样问题外连接select a.did, a.dname, b.sid, b.snamefrom department a type join employe b on a.did = b.did/type=left、right、fulla.did, a.dname, b.sid, b.snamefrom department a cross join employe bwhere a.did 1很少直接采用交叉连接，应避免笛卡尔积ino all带有重复结果select sname, sex from employe where sname

12、 = aunion allselect sname, sex from employe where sname = bion去掉重复结果select sname, sex from employe where sname = aunionselect sname, sex from employe where sname = b样可以实现并集查询select sname, sex from employe where sname = 1 or sname = 2l使用distinct去除重复结果select distinct sname, sex from employe where snam

13、e = 1 or sname = 2子查询lexists子查询/可实现交集、差集查询ect *from employewhere did = (select didfrom department where dname = A)ect *from employewhere did in (select didfrom department where dname like A%)lect *from employewhere exists (select didfrom department where dname like A%)l插入多行数据l使用select语句插入：insert select select into 更新lTruncate table table