第4章1 结构化查询语言SQL.docx
- 文档编号:18575786
- 上传时间:2023-08-19
- 格式:DOCX
- 页数:25
- 大小:26.10KB
第4章1 结构化查询语言SQL.docx
《第4章1 结构化查询语言SQL.docx》由会员分享,可在线阅读,更多相关《第4章1 结构化查询语言SQL.docx(25页珍藏版)》请在冰点文库上搜索。
第4章1结构化查询语言SQL
第4章结构化查询语言SQL
4.1SQL简介
SQL(StructuredQueryLanguage):
1986年提出,最终成为关系数据库的标准语言。
综合统一
数据定义语言DDL
CREATE、ALTER、DROP
数据操纵语言DML
SELECT,INSERT,
DELETE,UPDATE
数据控制语言DCL
GRANT,DENY,REVOKE
非过程化
只需提出“做什么”,不必指明“怎么做”
操作方式
面向集合
使用方法
既是自含式语言,又是嵌入式语言
Transact-SQL:
SQLServer中扩展的SQL实现。
增加了变量、运算符、函数、流程控制等语言元素。
4.2数据库的创建、删除
4.2.1创建数据库
CREATEDATABASEdatabase_name
[ON
[
[,
]
[LOGON{
CREATEDATABASE"Test"
CREATEDATABASESales
ON
(NAME=Sales_dat,
FILENAME='c:
\programfiles\microsoftsqlserver\mssql\data\saledat.mdf',
SIZE=10,
MAXSIZE=50,
FILEGROWTH=5)
LOGON
(NAME='Sales_log',
FILENAME='c:
\programfiles\microsoftsqlserver\mssql\data\salelog.ldf',
SIZE=5MB,
MAXSIZE=25MB,
FILEGROWTH=5MB)
GO
将GO之前的SQL语句作为一个批处理提交给SQLServer
SQLServer的内部流程:
①使用model数据库的复本初始化数据库及其元数据。
②SQLServer使用空页填充数据库的剩余部分,除了包含记录数据库中空间使用情况以外的内部数据页。
4.2.2设置当前数据库
use<数据库名>
use"Test1"
4.2.3删除数据库
dropDATABASE<数据库名>
dropDATABASE"Test1"
4.3表结构的建立、修改和删除
4.3.1建立表结构及表内约束
CREATETABLE[<数据库名>.<所有者名>.]<基本表名>
(<列定义>,……
[,<表级完整性约束>,……]
)
CREATETABLES
(SnoCHAR(8)Primarykey,
SnameCHAR(6)notnullUNIQUE,
SsexCHAR
(2)notnullcheck(Ssex='男'orSsex='女'),
SbirthdayDATETIMEcheck(Sbirthday<='1990-12-31'),
SdeptCHAR(10)DEFAULT'信科系',
SgradeINTcheck(Sgrade>=1andSgrade<=4)
);
Sbirthday<='1990-12-31':
在1990-12-31之前。
CREATETABLEC
(CnoCHAR(4)PRIMARYKEY,
CnameCHAR(10)notnullUNIQUE,
CcreditINTCheck(Ccredit>=1andCcredit<=6)
);
4.3.2建立表间约束(约束参照完整性)
CREATETABLESC
(SnoCHAR(8),
CnoCHAR(4),
SCscoreINTcheck(Scscore>=0andScscore<=100),
Primarykey(Sno,Cno),
foreignkey(Sno)referencesS(Sno),
foreignkey(Cno)referencesC(Cno)
);
4.3.3观察、理解表结构
理解查询分析器的对象浏览器中的层次结构:
表
列、约束、相关性、索引、触发器
列
包含类型定义、列级约束(null)
约束
包含主键约束(PK)、检查约束(CK)、
默认约束(DF)、外键约束(FK)
相关性
包含检查约束(CK)
操作细节:
命令执行后,效果需要刷新才能显示。
4.3.4修改基本表的结构
ALTERTABLE<表名>
[ADD<新列名><数据类型>[完整性约束]]
[DROP<完整性约束名>]
[ALTERCOLUMN<列名><数据类型>];
ALTERTABLESADDScomeDATETIME
/*注:
新增列一律为空值。
*/
ALTERTABLESADDScomeDATETIMEDEFAULT'2008-9-1'
/*注:
新增列一律为缺省值。
*/
ALTERTABLESDROPCOLUMNScome
ALTERTABLES2ALTERCOLUMNSdeptCHAR(20);
/*注:
涉及各种约束的列不能直接修改
注:
修改原有的列定义有可能会破坏已有数据*/
4.3.5基本表的删除
不能删除被FOREIGNKEY约束的表
错误流程
正确流程
DROPTABLES;
DROPTABLEC;
DROPTABLESC;
DROPTABLESC;
DROPTABLES;
DROPTABLEC;
4.4表数据的插入、修改和删除
4.4.1插入数据
INSERT[INTO]<基本表名>
(<列名>,……)VALUES(<列值>,……)
InsertintoS(Sno,Sname,Ssex,Sbirthday,Sdept,Sgrade)
values('95001','李勇','男','1990-1-1','信科系',1);
InsertintoS(Sno,Sname,Ssex,Sbirthday,Sdept,Sgrade)
values('95002','刘晨','男','1989-1-1','物理系',2);
InsertintoS(Sno,Sname,Ssex,Sbirthday,Sdept,Sgrade)
values('95003','王敏','女','1988-1-1','英语系',3);
InsertintoS(Sno,Sname,Ssex,Sbirthday,Sdept,Sgrade)
values('95004','张立','男','1987-1-1','信科系',4);
InsertintoCvalues('1','数据库',4);
InsertintoCvalues('2','数学',2);
InsertintoCvalues('3','信息系统',4);
InsertintoCvalues('4','操作系统',3);
InsertintoCvalues('5','数据结构',4);
InsertintoCvalues('6','数据处理',2);
InsertintoCvalues('7','PASCAL语言',4);
InsertintoSCvalues('95001','1',92);
InsertintoSCvalues('95001','2',85);
InsertintoSCvalues('95001','3',88);
InsertintoSCvalues('95002','2',90);
InsertintoSCvalues('95002','3',80);
4.4.2修改数据
UPDATE<表名>SET<列名>=<表达式>,……
[WHERE<逻辑表达式>]
UPDATESCSetSCscore=SCscore+2;
UPDATESCSetSCscore=99
whereSno='95001'andCno='1';
4.4.3删除数据
由于参照完整性约束,有些记录不能删除
DELETE<表名>[WHERE<逻辑表达式>]
deleteSwhereSbirthday>'1988-01-01';
deleteSwhereSno>='95003';
DELETES;
DELETESC;
DELETES;
数据被删除,但表结构保留。
4结构化查询语言SQL
4.5常用函数
4.5.1数学函数
RAND()
返回0到1之间的随机值。
例如:
Printrand()
declare@iFloat
set@i=rand()
print@i
PRINTCONVERT(INT,RAND()*26)--0to25
PRINTCONVERT(INT,RAND()*26)+1--1to26
规则:
PRINTCONVERT(INT,RAND()*(N-M+1))+M--MtoN(M ROUND(78.518,2) 按照指定精度四舍五入。 78.520 FLOOR(-123.45) 返回<=参数的最大整数。 -124 CEILING(-123.45) 返回>=参数的最小整数。 -123 PI() 3.14159 其他函数: 弧度、角度 DEGREES、RADIANS 指数函数 EXP、POWER 对数函数 LOG10、LOG 三角函数 COS、SIN、TAN、ATAN 4.5.2字符串函数 LEN('abcd') 计算串长 SPACE(10) 返回由重复的空格组成的字符串。 REPLICATE('abc',3) 返回由重复的字符串组成的字符串。 REVERSE('abcdef',3) 字符串逆序 declare@schar(100) set@s=REVERSE('abcdef') print@s declare@schar(100) set@s=REVERSE('abcdef') print@s RTRIM('abc') 缩减右空格 类型转换 ASCII('abc') 首字符转换为ASCII值。 CHAR(97) ASCII值转换为字符 STR(123.45,8,3) STR(float_expression[,length[,decimal]]) 数值转换为字符串 float_expression 带小数点的近似数字(float)数据类型的表达式。 length 总长度。 它包括小数点、符号、数字以及空格。 默认值为10。 decimal 小数点后的位数。 decimal必须小于或等于16。 如果decimal大于16,则会截断结果,使其保持为小数点后具有十六位。 CONVERT(real,'12.34') CONVERT(decimal(9,4),'12.34') 字符串转换为实数 (也可用于其他类型转换) 子串操作 LEFT('abcdefg',2) 取左子串 RIGHT('abcdefg',2) 取右子串 SUBSTRING('abcdefg',2,4) 取子串 REPLACE('oldisold! ','old','new') 子串替换 STUFF('abcdef',2,3,'12345') 位置替换 子串/模式匹配 CHARINDEX('Data','WhatisDataBase') 子串匹配 PATINDEX('%Data%','WhatisDataBase') 模式匹配 字符串相似度 SOUNDEX 返回由四个字符的代码 DIFFERENCE 返回[0,4],表示两串的相似性 SOUNDEX('hello') H120 SOUNDEX('what') 0000 SOUNDEX('water') 0000 DIFFERENCE('hello','what') 0 DIFFERENCE('what','water') 4 4.5.3日期函数 GETDATE() 返回当前时间 YEAR(GETDATE()) 取时间中的年 MONTH(GETDATE()) 取时间中的月 DAY(GETDATE()) 取时间中的日 DATENAME(year,GETDATE()) DATENAME(month,GETDATE()) DATENAME(day,GETDATE()) 取部分日期 (字符串) DATEPART(hour,GETDATE()) DATEPART(minute,GETDATE()) DATEPART(second,GETDATE()) 取部分日期 (整数) DATEADD(day,2,'2008-9-1') DATEADD(month,2,'2008-9-1') DATEADD(year,2,'2008-9-1') 日期加法 DATEDIFF(hour,'1990-1-1',GETDATE()) DATEDIFF(minute,'1990-1-1',GETDATE()) DATEDIFF(second,'1990-1-1',GETDATE()) 日期减法 4.6数据查询 SELECT语句有太多的命令选项(子句): SELECT子句 指定要显示的属性列(相当于投影运算) FROM子句 指定查询表 WHERE子句 指定查询条件(相当于选择运算) GROUPBY子句 对查询结果按指定列的值分组。 一般会在每组的若干记录上使用“集函数”。 HAVING短语 筛选出满足指定条件的组 ORDERBY子句 对查询结果按指定列值的排序 4.6.1单表查询 1、选择表中的若干列(投影) SELECTSname,Sno,SdeptFROMS; SELECT*FROMS; SELECTSnameas[姓名], year(GETDATE())-year(Sbirthday)as[年龄] FROMS; 2、选择满足条件的元组(选择) 查询1989-9-1以前出生的学生记录 SELECT*FROMSWHERESbirthday<'1989-9-1' 查询没有参加考试学生的信息 SELECTSno,Cno,*FROMSC WHERESCscoreISNULL 查询信科系大三的学生姓名 SELECTSnameFROMS WHERESdept='信科系'ANDSgrade=3; 查询大二、大三、大四的学生记录 SELECT*FROMS WHERESgradeBETWEEN2AND4; 查询大一的学生记录 SELECT*FROMS WHERESgradeNOTBETWEEN2AND4; 查询信科系和物理系的学生记录 SELECT*FROMS WHERESdeptIN('信科系','物理系'); SELECT*FROMS WHERESdept='信科系'ORSdept='物理系'; 字符串的模式匹配: % 代表任意长度的字符串 _ 代表任意单个字符 SELECT*FROMSWHERESnoLIKE'95%'; 查询所有的刘姓学生 SELECT*FROMSWHERESnameLIKE'刘%'; 查询所有的刘姓(双字)学生 SELECTSnameFROMSWHERESnameLIKE'刘_' 查询所有的姓名中第2个字是勇的学生 SELECTSname,SnoFROMSWHERESnameLIKE'_勇%' 出现重复行的情形: 查询所有选过课的学生学号(含重复行) SELECTSnoFROMSC; 查询所有选过课的学生学号(无重复行) SELECTDISTINCTSnoFROMSC; 3、对查询结果排序 所有记录按成绩降序排列 SELECT*FROMSC ORDERBYSCscoreDESC 3号课程的所有记录按成绩降序排列 SELECT*FROMSC WHERECno='3' ORDERBYSCscoreDESC 所有记录按学号升序、成绩降序排列 SELECT*FROMSC ORDERBYSnoASC,SCscoreDESC 4、使用集函数 计数 COUNT() 总和 SUM() 平均值 AVG() 最大值 MAX() 最小值 MIN() 方差 STDEV() 查询学生总数。 SELECTCOUNT(*)FROMS 查询选修了课程的学生人数。 (计算时取消重复值) SELECTCOUNT(DISTINCTSno)FROMSC 计算2号课程的学生平均成绩。 SELECTAVG(SCscore),MAX(SCscore),Min(SCscore) FROMSCWHERECno='2' 计算学生成绩的均值和方差。 SELECTAVG(SCscore),STDEV(SCscore)fromSC 4.6.2多表(连接)查询 1两表之间的连接查询 广义笛卡尔积 SELECTS.*,SC.*FROMS,SC 等值连接: 查询每个学生及其选修课程的情况。 SELECTS.*,SC.*FROMS,SC WHERES.Sno=SC.Sno 自然连接: 查询每个学生及其选修课程的情况。 SELECTS.Sno,Sname,Ssex,Sbirthday,Sdept,Sgrade,Cno,SCscore FROMS,SCWHERES.Sno=SC.Sno 或 SELECTS.*,Cno,SCscore FROMS,SCWHERES.Sno=SC.Sno 或 SELECTS.*,Cno,SCscoreFROMSINNERJOINSC ONS.Sno=SC.Sno 内连接INNERJOIN: 第一表与第二表在联接列上满足条件而连接。 更多内涵的连接: 查询选修1号课程且成绩大于90分所有的学生 SELECTS.*,Cno,SCscoreFROMS,SC WHERES.Sno=SC.SnoANDCno='1'ANDSCscore>90 或 SELECTS.*,Cno,SCscoreFROMSINNERJOINSC ONS.Sno=SC.Sno WHERECno='1'ANDSCscore>90 Cf: 查询每个学生每门课的姓名、课程名、成绩 Selects.sname,ame,scscorefromsc Innerjoinsonsc.sno=s.sno Innerjoinconsc.sno=o 2一个表的连接查询 自身连接: 一个表与其自己进行连接。 由于所有属性名都是同名属性,因此必须使用别名。 (/*例如一行查阅一位同学的几门课成绩*/) 自身连接 SELECTa.*,b.*FROMSCasa,SCasb WHEREa.sno=b.sno 查询选修了1号课程和3号课程的学生信息 SELECTa.*,b.*FROMSCasa,SCasb WHEREa.sno=b.snoANDo='1'ANDo='3' 3外连接查询 外连接: 以一个表为主体,将另一个表中“满足”和“不满足”连接条件的元组一起输出。 左外连接: 查询所有学生的选修情况(含未选修情况) SELECTS.*,SCscoreFROMS LEFTOUTERJOINSCONS.SNO=SC.SNO; 左外连接: 查询所有课程的成绩(含未考试科目) SELECTC.*,SCscoreFROMC FULLOUTERJOINSCONC.Cno=SC.Cno; 右外连接: SELECTS.*,SCscoreFROMS RIGHTOUTERJOINSCONS.SNO=SC.SNO; 外连接: SELECTS.*,SCscoreFROMS FULLOUTERJOINSCONS.SNO=SC.SNO; 4.6.3嵌套查询 查询块: SELECT-FROM-WHERE语句 嵌套查询: 将一个查询块嵌套在另一个查询块的WHERE子句中 1、不相关子查询 子查询的查询条件不依赖于父查询。 IN运算符 查询与“刘晨”在同一个系学习的学生 SELECT*FROMSWHERESdeptIN (SELECTSdeptFROMSWHERESname='刘晨') 同上(使用单表连接的方法) SELECTS1.*FROMSasS1,SasS2 WHERES1.Sdept=S2.SdeptANDS2.Sname='刘晨' 查询选择了2号课程的所有学生的姓名 SELECTSnameFROMS WHERESnoIN (SELECTSnoFROMSCWHERECno='2');--如果直接用连接查询? 查询选修了“数学”的学生学号和姓名 SELECT*FROMSWHERESnoIN (SELECTSnoFROMSCWHERECnoIN (SELECTCnoFROMCWHERECname='数学') ) 查询有一门课程分数高于90的学生姓名 SELECTSnameFROMS WHERESnoIN (SELECTdistinctSnoFROMSCWHERESCscore>90) 将信科系所有学生的成绩置100。 UPDATESCsetSCscore=100 whereSnoIN (SELECTSnoFROMSWHERESdept='信科系') 删除信科系所有学生的选课记录。 DELETEFROMSCWHERESnoIN (SELECTSnofromSWHERESdept='信科系') ANY或ALL运算符 查询有一门课程分数高于90的学生姓名 SELECTSna
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 第4章1 结构化查询语言SQL 结构 查询 语言 SQL