1 / 58

SQL 基础

SQL 基础. 西南财经大学经济信息工程学院. 西南财经大学数据库技术课程组 2011.02. 课程复习. 数据库基础 E-R 图， E-R 转换成关系 创建表，输入表的记录 软件： SQL DEVELOPER 表的基本结构（列名，列宽，小数位数） 表的约束 实体完整性约束（ PRIMARY KEY ） 参照完整性约束（ FOREIGN KEY) ON DELETE CASCADE ON DELETE RESTRICT ON DELETE SET NULL 域完整性 是否为空 默认值 CHECK 约束. 主表. 课程复习. 主键.

meriel
Download Presentation

SQL 基础

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL基础 西南财经大学经济信息工程学院 西南财经大学数据库技术课程组 2011.02

  2. 课程复习 • 数据库基础 • E-R图，E-R转换成关系 • 创建表，输入表的记录 • 软件：SQL DEVELOPER • 表的基本结构（列名，列宽，小数位数） • 表的约束 • 实体完整性约束（PRIMARY KEY） • 参照完整性约束（FOREIGN KEY) • ON DELETE CASCADE • ON DELETE RESTRICT • ON DELETE SET NULL • 域完整性 • 是否为空 • 默认值 • CHECK约束

  3. 主表 课程复习 主键 • ON DELETE CASCADE • ON DELETE RESTRICT • ON DELETE SET NULL 外键 子表

  4. 上次课外作业 • 创建下列3个表 • C表（cno,cname,ccredit) • Cno是主键 • S表(sno,sname,ssex,sage) • Sno是主键 SC表(sno,cno,grade) • Sno和S中的sno结构一致； cno和c中的cno结构一致 • Sno+cno是主键 • Sno是外码，参照s的主键 • Cno是外码，参照C的主键 • 注意：没有完成的同学请补充完成 • 软件下：http://liming.cai.swufe.edu.cn/sjkjs

  5. SQL基础 • SQL概述 • SELECT查询 • 投影查询 • 条件查询 • 统计查询 • 分组查询 • 排序查询 • 连接查询 • 数据定义 • 创建表 • 修改表 • 删除表 • 数据操纵功能 • 插入记录 • 删除记录 • 修改记录 • 实验练习 • 课外练习

  6. 系统注册的基本流程 • 用户填写登陆信息：用户号，姓名，密码（用户号必须唯一） • 000002 刘翔 000002 • 查询staff表中有无相同的用户号 • Select * from staff where staff_id='000002' ; • 如果有，提示错误。 • 如果没有，添加数据到表中。 • insert into staff values('000002','刘翔','000002');

  7. SQL 概述 • SQL是一种公共的关系型数据库数据查询和数据操纵语言 • 可以由人向数据库管理系统发出SQL语言命令 • 也可以由程序向数据库管理系统发出SQL语言命令 • SQL是一种脚本语言 • SQL语言语法非常严格，但又比较接近自然语言（英文）的表述方式

  8. 关系数据库标准语言SQL • SQL的数据查询功能 • SQL的数据定义功能 • SQL的数据操纵功能。

  9. SQL的数据查询功能 • 数据查询SELECT是数据库的核心操作。

  10. 投影查询 • 投影查询是指从表中查询全部列或部分列。 • 1．查询部分字段 • SELECT之后列出需要查询的字段名，字段名之间以英文逗号“，”分隔。 • 【例1】从表gcustomer中查询customer_id,。 • select customer_id,cust_name from gcustomer

  11. SQL DEVELOPER中输入和执行SQL 1 4 2 3

  12. 投影查询 • 2．查询全部字段 • “*”来表示表中所有字段。 • 【例2】查询表gcustomer的全部数据。 • SELECT * FROM gcustomer

  13. 投影查询 • 3．取消重复记录 • 在SELECT语句中，可以使用DISTINCT来取消查询结果中重复的记录。 • 【例3】查询表Account中有开户记录的客户代码。 • SELECT DISTINCT customer_id FROM gaccount • 理解：重复

  14. 投影查询 • 4．修改 查询的列名 • 在SELECT语句中，可以修改查询的列名。 • 【例4】查询客户信息。 • select customer_id as 客户号,cust_name as 客户姓名 from gcustomer • 说明：AS可以省略。

  15. 条件查询 若要在数据表中找出满足某些条件的行时，则需使用WHERE来指定查询条件。

  16. 条件查询 • 1．比较大小 • 【例5】从表gcustomer中查询”成都“的客户信息 • select * from gcustomer where cust_city='成都' • 【例6】从表Account中查询2010年的开户记录 • select * from gaccountwhere extract(year from build_date) =2010

  17. 条件查询 • 2．多重条件查询 • 符AND和OR将其连接成复合逻辑表达式，AND的运算优先级高于OR。用户可使用括号改变优先级。 • 【例7】从表gcustomer中查询”成都”的“教师”的信息 • select * from gcustomer where cust_city='成都' and cust_job='教师' • 【例8】从表Account中查询Banlance大于100000或小于20000的帐户信息 • select * from gaccountwhere balance<20000 or balance>1000 • 理解：AND 和OR

  18. 条件查询 • 3．确定范围 • 确定范围的是： • BETWEEN 下界表达式AND 上界表达式 • 其含义是“在下界表达式和上界表达式之间，且包含上界表达式的值和下界表达式的值”。 • 【例9】从表account中查询balance在10000元到10000元记录。 • select * from gaccount where balance between 20000 and 100000 • 等价于下列SELECT语句： • select * from gaccount where balance>=20000 and balance<= 100000

  19. 条件查询 • 4．确定集合 • 利用IN操作可以查询字段值属于指定集合的记录， • 【例10】从表gcustomer中查询cust_job是‘教师’‘工人’‘工程师’的记录。 • select * from gcustomer where cust_job in ('教师','工程师','工人')

  20. 条件查询 • 5．部分匹配查询 • LIKE定义的一般格式为：<字段名> LIKE <字符串常量> • %：表示任意长度的字符串。 • _：表示任意一个字符。 • 【例11】从表gcustomer查询cust_job中有’师’的记录 • select * from gcustomer where cust_job like '%师%'

  21. 条件查询 • 6．涉及空值查询 • 在SELECT语句中，使用IS NULL和IS NOT NULL来查询某个字段的值是否为空值。这里，IS不能用用等号“=”代替。 • 【例12】从表gcustomer中查询Cust_phone不为空的记录。 • select * from gcustomer where cust_phone is not null

  22. 统计查询

  23. 统计查询 • 【例13】计算表Account中所有帐户的最高Balance、最低balance • select max(balance) 最高余额 ,min(balance) 最低余额 from gaccount • 【例14】计算表gcustomer 成都的客户数。 • select count(*) from gcustomer where cust_city='成都' • 【例15】统计Account中有开户记录的客户数。 • selectcount(distinct customer_id) from g account • 思考：这里的DISTINCT为什么不能省略？

  24. 分组查询 • 1．分组查询 • 关键:识别分类字段,识别汇总字段,选定汇总函数 • GROUP BY类似于EXCEL中的分类汇总功能。 • 【例16】从Account中查询每个客户的帐户数。 • select customer_id,count(*) as 开户数 from gaccountgroup by customer_id

  25. 分组查询 • 【例17】从account中查询每个客户的帐户余额合计。 • select customer_id,sum(balance) as 余额合计 from gaccountgroup by customer_id

  26. 查询的排序 • 当用户需要对查询结果排序时，可用ORDER BY对查询结果按一个或多个查询列的升序（ASC）或降序（DESC）排列，默认值为ASC。ORDER BY之后可以是查询列，也可以是查询列的序号。 • 1．单列排序 • 使用ORDER BY可对查询结果按一个查询列进行排序。 • 【例18】gaccount表中查询账户信息，并按照customer_id降序排序。 • select * from gaccountorder by customer_id desc

  27. 查询的排序 • 【例19】从account中查询每个customer_id的余额合计，并按照余额合计降序排序。 • select customer_id,sum(balance) as 余额合计 from gaccount group by customer_id order by 余额合计 desc • 说明：这里“ORDER BY 余额合计”可以用“ORDER BY 2”代替（2表示查询列的列序号），也可以用“用ORDER BY sum(balance)”代替。

  28. 查询的排序 • 2．多列排序 • ORDER BY 列名1 [ASC|DESC][，列名2 [ASC|DESC]…] • 多列排序的含义是：将查询结果首先按<列名1>排序，在<列名1>的值相同的情况下，按<列名2>排序。 • 【例20】从表gcustomer查询结果按cust_city升序排序，cust_city相同的按照cust_job降序排序。 • select *from gcustomer order by cust_city,cust_job desc

  29. SQL书面作业1 • 创建下列3个表 • C表（cno,cname,ccredit) • S表(sno,sname,ssex,sage) • SC表(sno,cno,grade) • 使用SQL语句实现下列查询 • 在S表中查询学生所有的信息 • 在C表中查询学分在2-4范围内的课程的基本信息 • 查询C表中课程名包含“数据”的课程的编号和名称（提示：使用like) • 在SC中查询被学生选修的CNO（提示：DISTINCT） • 查询编号为“C01”、”C02”、”C03”、”C04”、“C05”，并且学分大于3的课程的基本信息 • 在SC表中查询所有的信息，并按照SNO升序排序，SNO相同的，按照GRADE降序排序 • 在SC表中查询成绩不及格的所有学生的学号、课程 • 在SC中查询“001”课程的课程的平均成绩 • 在SC中查询每门课程的平均成绩（提示：分组查询） • 在SC表中查询成绩为空的记录（IS NULL） • 注意：下周上机之前交

  30. 连接查询 • 连接查询是多个表中满足连接条件的记录才出现在结果表中的查询。在Oracle中，实现两个表的连接查询的格式有两种： • ①SELECT 查询列FROM 表1，表2 WHERE 连接条件AND 查询条件 • ②SELECT 查询列FROM 表1 [INNER] JOIN 表2 ON 连接条件WHERE 查询条件 • 说明：INNER可以省略。 • 常用的连接条件是：表1.公共字段=表2.公共字段。

  31. 连接查询的执行过程 • DBMS在执行连接查询的过程是：首先在表1中找到第1个记录，然后从表头开始扫描表2，逐一查找满足条件的记录，找到后，就将该记录和表1中的第1个记录进行拼接，形成查询结果中的一个记录；表2中的记录全部查找以后，再找表1中的第2个记录，然后再从头开始扫描表2，逐一查找满足连接条件的记录，找到后，将该记录和表1中的第2个记录进行拼接，形成查询结果中的一个记录。重复上述操作，直到表1中的记录全部处理完毕。

  32. 连接查询示例

  33. 连接查询 • 【例21】从表gcustomer和account中，查询cust_name,account_no,balance 。 • select cust_name,account_no,balance from gcustomer,gaccount where gcustomer.customer_id=gaccount.customer_id • 上述语句也可以写成下面2个语句 • select cust_name,account_no,balance from gcustomer inner join gaccount on gcustomer.customer_id=gaccount.customer_id • select cust_name ,account_no ,balance from gcustomer c ,gaccount a where c.customer_id=a.customer_id • 说明：这里C和A分别是gcustomer和account的别名 • 注意:在连接查询的时候，对于两个表都有的同名字段,必须用别名加以限制(无别名,就用表名)

  34. 连接查询 • 【例22】从表gcustomer和account中，查询成都地区的客户的cust_name,account_no,balance 。 • select cust_name,account_no,balance from gcustomer,gaccount where gcustomer.customer_id=gaccount.customer_id and cust_city='成都‘ • select cust_name,account_no,balance from gcustomer inner join gaccounton gcustomer.customer_id=gaccount.customer_id where cust_city='成都';

  35. SQL的数据定义功能 • 关系数据库系统支持三级模式结构，其模式、外模式和内模式中的基本对象有表、视图和索引。因此SQL的数据定义功能包括定义表、定义视图和定义索引

  36. 创建表 • 1．创建表的基本的命令 • 在SQL语言中，使用CREATE TABLE命令创建数据表。 • 【命令】CREATE TABLE <表名> (<字段名1> <类型>[(宽度 [,小数点位数])] ; • [,<字段名2> <类型>[(宽度 [,小数点位数] )]) • 【功能】创建一个以<表名>为表的名字、以指定的字段属性定义的数据表。 • 【说明】定义表的各个属性时，需要指明其数据类型及长度。常用数据类型说明见表

  37. 创建表

  38. 创建表 • 【例23】创建新表gcustomerbk ，其结构和表gcustomer相同。 • CREATE TABLE gcustomerbk ( customer_id CHAR(18) , cust_name varCHAR2(20), cust_city varchar2(30) , cust_phone varCHAR2(13), cust_gender CHAR(1) , cust_job varCHAR2(30)); • 执行CREATE TABLE语句后，新建表成为当前打开的表。

  39. 创建表 • 2．创建表的同时定义完整性规则 • 对于数据库表，在创建表的时候，可通过以下命令格式对表的完整性规则进行定义。 • 【命令】CREATE TABLE <表名>(<字段名1> <类型>[(宽度 [,小数点位数])] ; [CONSTRAINT 约束名 NOT NULL] [CONSTRAINT 约束名 PRIMARY KEY] [DEFAULT 表达式1] [CONSTRAINT 约束名 CHECK 逻辑表达式1] • 【功能】创建一个表。 • NOT NULL定义字段不能为空 • PRIMARY KEY定义表的主索引。 • DEFAULT定义字段的默认值。 • CHECK定义字段的有效性规则。 • 说明：DEFAULT定义的默认值的类型应和字段的类型相同。CHECK定义的有效性规则必须是一个逻辑表达式。

  40. 创建表 • 【例24】创建新表gcustomerbk ，其结构和表gcustomer相同。同时定义约束条件： customer_id为主键、 cust_name 为非空、 cust_gender默认值为‘M’，其取值范围是’F’或’M’ • CREATE TABLE gcustomerbk ( customer_id CHAR(18) CONSTRAINT custbk_pk PRIMARY KEY, cust_name varCHAR2(20) CONSTRAINT cust_name_nnull NOT NULL, cust_city varchar2(30) , cust_phone varCHAR2(13), cust_gender CHAR(1) DEFAULT 'M' CONSTRAINT cust_gender_mf CHECK (cust_gender='M' or cust_gender='F'), cust_job varCHAR2(30));

  41. 创建表 • 在插入或修改记录时，如果违反约束条件，系统会提示错误信息： • 第 * 行出现错误: ORA-02290: 违反检查约束条件 (约束名)

  42. 修改表的结构 • 在SQL语言中，使用ALTER TABLE命令修改表的结构，包括增加字段、删除字段、修改字段。对于数据库表，可以使用ALTER TABLE命令增加数据完整性规则、删除数据完整性规则和修改数据完整性规则。

  43. 修改表的结构 • 1．增加字段 • 【增加一个列命令】 ALTER TABLE <表名> ADD <字段名1> <类型>[(宽度 [,小数点位数])] • 【增加多个列命令】 ALTER TABLE<表名> ADD （ <字段名1> <类型>[(宽度 [,小数点位数])]， <字段名1> <类型>[(宽度 [,小数点位数])]……） • 【功能】在表中增加新字段，并定义字段的属性。 • 【例25】在gcustomer表中增加客户生日cust_birth字段，类型是Date • ALTER TABLE gcustomerbk ADD cust_birth Date; • 【例26】在gcustomer表中增加客户生日cust_birth字段，类型是Date，客户家庭地址cust_address，类型是varCHAR2(30) • ALTER TABLE gcustomerbk ADD ( cust_birth Date,cust_adress varCHAR2(30));

  44. 修改表的结构 • 2．修改字段 • 【修改一个列命令】 ALTER TABLE <表名> modify <字段名1> <类型>[(宽度 [,小数点位数])] • 【修改多个列命令】 ALTER TABLE<表名> modify （ <字段名1> <类型>[(宽度 [,小数点位数])]， <字段名1> <类型>[(宽度 [,小数点位数])]……） • 【功能】修改表中字段的属性。 • 【例27】将gcustomer表中cust_address字段类型修改为varCHAR2(40) • ALTER TABLE gcustomerbk modify cust_adress varCHAR2(40);

  45. 修改表的结构 • 3．删除字段 • 【删除改一个列命令】 ALTER TABLE <表名> drop column <字段名> • 【删除多个列命令】 ALTER TABLE <表名> drop ( <字段名1>, <字段名2>) • 【功能】删除表中指定的字段 • 【注意】删除表中一个字段有Column关键字，删除多个字段没有 • 【例28】删除表gcustomerbk中cust_birth和cust_adress字段。 • ALTER TABLE gcustomerbk drop (cust_bith,cust_adress) ;

  46. 修改表的结构 • 4．定义或修改数据完整性 • ALTER TABLE语句操作数据库表的数据完整性的命令格式主要有两 • 如果增加UNIQUE、PRIMARY KEY、FOREIGN KEY 和CKECK 必须使用ALTER TABLE语句的ADD子句; • ALTER TABLE 表名 ADD [CONSTRAINT 约束名]约束类型 约束定义 • 如果增加NOT NULL约束，那么必须使用ALTER TABLE语句的MODIFY子句， • ALTER TABLE 表名 MODIFY 列名 CONSTRAINT 约束名NOT NULL; • 【功能】在表中定义或修改字段的约束条件。

  47. 修改表的结构 • 【例29】在gcustomerbk中增加约束条件，设置cust_phone为非空 • ALTER TABLE gcustomer MODIFY cust_phone CONSTRAINT cust_phone_nnull NOT NULL; • 【例30】在gcustomerbk中增加约束条件，设置cust_phone的长度大于6位 • ALTER TABLE gcustomer ADD CONSTRAINT cust_phone_const CHECK (length(cust_phone) >= 6);

  48. 删除表 • 在SQL语言中，删除表的命令是DROP TABLE。 • 【命令】DROP TABLE 表名 • 【功能】删除表。 • 【例31】从磁盘上删除表gcustomerbk。 • DROP TABLE gcustomerbk

  49. SQL的数据操纵功能 • SQL语言的数据操纵也称为数据更新，主要包括插入数据、修改数据和删除数据3种语句。 • 数据操纵涉及另外两个命令： • ROLLBACK:回退命令，取消刚才执行的SQL语句 • COMMIT:提交命令，

  50. SQL的数据操纵功能 • SQL语言的数据操纵也称为数据更新，主要包括插入数据、修改数据和删除数据3种语句。 • 插入记录 • 插入数据是把新的记录插入到一个存在的表中。插入数据使用语句INSERT INTO。 • 【命令】INSERT INTO <表名>[(<字段名1>[,<字段名2>…])] VALUES(<值1>[,<值2>…])

More Related