【数据库】03——初级开发需要掌握哪些SQL语句_数据库初级开发(1)
(1)创建关系创建一个关系,很简单。顺带一提,最后的分号是可选的。上面使用了主键约束,外键约束,非空约束not null。值得关注的是,包括Mysql(2)删除关系这个命令会删除r中所有元组,并且删除关系。下面的sql更弱点,会删除元组,但是保留关系。(3)修改关系结构给关系r增加属性A,其类型为D。删除关系中的属性。
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,涵盖了95%以上大数据知识点,真正体系化!
由于文件比较多,这里只是将部分目录截图出来,全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频,并且后续会持续更新
char
是定长的,如果存入的属性长度没有n
,会追加空格补全。因此char
和varchar
类型的数据可能无法比较,因为即使他们存的是相同的值,也可能返回false,建议始终使用varchar避免这样的问题。下表是一些char,varchar存储数据的实例对比。
SQL还提供nvarchar
类型来存放使用Unicode
表示的多语言数据。然而,很多数据库甚至允许在varchar
类型中存放Unicode
(采用utf-8
形式)。
2.2 基本模式定义
(1)创建关系
创建一个关系,很简单。顺带一提,最后的分号是可选的。
create table test
(
test_id int,
test_depat_name varchar(15),
test_price numeric(12,2) not null,
test_desc varchar(255),
primary key(test_id),
foreign key(test_dept_name) references department
);
上面使用了主键约束primary key
,外键约束foreign key
,非空约束 not null
。值得关注的是,包括Mysql
在内的数据库需要使用另一种外键约束的用法:
foreign key(test_dept_name) references department(dept_name)
(2)删除关系
drop table r;
这个命令会删除r中所有元组,并且删除关系。下面的sql更弱点,会删除元组,但是保留关系。
delete form r;
(3)修改关系结构
给关系r增加属性A,其类型为D。
alter table r add A D;
删除关系中的属性。
alter table r drop A;
3 SQL查询的基本结构
在讲解前,先放下后续会用到的一些表(除此外还有department,emp这种简单的表结构)。
3.1 单关系查询
查询并去重。
SELECT DISTINCT ename FROM emp;
SQL还允许显示指定不去重,但它是可以缺省的元素。
SELECT ALL ename FROM emp;
可以结合+
,-
,*
,/
运算符来使用select子句。
SELECT ename, salary \* 1.1 FROM emp;
where子句可以增加查询条件。
select \* from emp where sal between 2000 and 3000;
在SQL中,比较运算符>
,<
等等可以用来比较字符串、算数表达式以及特殊类型(如日期)。
3.2 多关系查询
我们可以在where子句中指定匹配条件,然后进行多关系查询。
select
e.empno,e.ename,d.dname,e.deptno,d.deptno
from
emp e,dept d
where e.deptno=d.deptno;
上面的实例给关系取了别名,这是区分不同关系中的同名属性的好办法。
事实上查询时,select
,from
,where
子句并不是顺序执行的。正确的理解如下。
1.为from所列出的关系产生笛卡尔积。
2.在1的结果上应用where子句中指定的谓词。
3.对步骤2的结果中的每个元组,输出select子句中指定的属性(或表达式的结果)。
可以料想到,where子句十分关键,否则结果会直接输出笛卡尔积,那可是相当大的数据量
4 附加的基本运算
4.1 更名运算
对属性可以使用更名运算进行更名。
SELECT emp_department AS edept FROM emp;
还可以用它来重命名关系,重命名关系的一个原因时把一个长的关系名替换成为短的。
select
e.empno,e.ename,d.dname,e.deptno,d.deptno
from
emp as e,dept as d
where e.deptno=d.deptno;
除此外,一个关系与自身进行笛卡尔积运算也需要使用重命名。比如找出至少比运维部门某一位员工工资更高的所有员工姓名。
select distinct T.name
from emp as T,emp as S
where T.sal > S.sal and S.deptname = '运维部';
4.2 字符串运算
在SQL标准中,字符串的相等运算是大小写敏感的。但是在一些数据库中(如Mysql和SQL Server),在匹配字符串时并不区分大小写。
字符串可以应用许多函数运算,比如连接字符串(||
),提取子串,去字符串后空格trim
等等。不同数据库系统提供的函数集是不同的,具体可以查阅数据库系统手册。
使用like
可以进行模糊匹配。有两个特殊的字符串可以用来描述模式。
- 百分号
%
:匹配任意字串 - 下划线
_
:匹配任意一个字符
模式是大小写敏感的(Mysql中除外,PostgreSQL使用ilike大小写也不敏感)。
另外,为了能够使模式包含特殊字符(%
和_
),SQL允许使用escape
关键字定义转义字符。如。
like 'ab\%cd% escape '\'
表示匹配以'ab%cd
开头的所有字符。
SQL标准还允许我们通过not like
比较运算符来搜索不匹配想。一些实现还提供了不区分大小写的变种。
一些SQL实现,特别是PostgreSQL,提供了similar to
运算。它具备比like
更强大的模式匹配能力,其模式定义语法类似UNIX
中使用的正则表达式。
4.3 排序
使用order by
关键字就可以实现排序了。desc
表示降序,asc
表示升序,缺省时默认升序。
select
empno, ename
from emp
where
hiredate between date '1980-01-01' and date '1981-01-01'
order by empno desc;
5 集合运算
SQL作用在关系上的union
、intersect
和except
运算对应数学集合论中的∪
、∩
、-
。
5.1 并运算
union会去重。
(select deptno from emp)
union
(select deptno from dept
order by deptno);
注意,上面使用()
是为了方便阅读,如果数据库不允许使用可以去掉。
不想去重则可以使用union all
5.2 交运算
这个过程是如此的枯燥。
(select deptno from emp)
intersect
(select deptno from dept
order by deptno);
同理,insect all
可以保留重复项。
5.3 差运算
(select deptno from emp)
except
(select deptno from dept
order by deptno);
同理,except all
可以保留重复项。
6 空值
空值给包括算数运算、比较运算和集合运算在内的关系运算带来了特殊的问题。
比如,如果算术表达式的任一输入值为空,则该算术表达式(如+,-,*,/)结果为空。
对比较运算,这也是一个问题。考虑比较运算1<null
的结果,这是true还是false呢?我们并不知道空值是什么,所以很难进行比较。因而SQL将任何设计空值的比较运算结果视为unknown
。
由于where子句中的谓词可以对比较结果进行and
,or
和not
的布尔运算,因此这些布尔运算的定义也被拓展成可以处理unknown值。
- and。true and unknown结果为unknown, false and unknown结果是unknown.而unknown and unknown的结果是unknown。
- or。true or unknown的结果是true,false or unknown的结果是unknown,而unknown or unknown的结果是unknown。
- not。not unknown的结果是unknown.
tips:尝试理解上面的规则。不用死记硬背。
可以使用is null
和is not null
来判空和判非空。还可以使用is unknown
和is not unknown
来判断一个比较运算的结果是否为unknown
,例如
select name
from instructor
where salary > 1000 is unknown;
注意,在使用select distinct
时。重复元素如果都是空,会被判为相同去重。这与谓词中的处理有区别,因为在谓词中null = null
会返回unknown
。
如果元组上所有属性上取值相等,那么他们会被当做相同的元组,即使某些值为空,这种方式还适用与集合的并、交、和差运算。
7 聚集函数
聚集函数是以值集为输入并返回单个值的函数,SQL标准提供了五个标准聚集函数。平均值avg
,最小值min
,最大值max
,总和sum
和计数count
。其中sum
和avg
必须输入数字集,但是其他运算符可以作用在非数字类型的集合上。
7.1 基本聚集
先求个平均工资
select avg(salary) as avg_salary
from instructor
where dept_name = 'Comp.Sci';
注意,在求平均值时,重复值是必要的,如果先对数据进行了去重操作,求出来的平均工资就不正确了。
如果确实需要在使用聚集函数前对数据去重,可以使用distinct
关键字。
select count(distinct ID)
from teachers
where semester = 'Spring' and year = 2018;
使用count(*)
可以统计一个关系中的元组数量。
7.2 分组聚集
有时候我们不仅希望将聚集函数作用在单个元组集上,而且希望将其作用在一组元组集上。在SQL上可以使用group by
实现。在group by
子句中可以给出一个或者多个属性用来构造分组。在分组(group by)子句中所有属性上取值相同的元组会被分在一个组内。
考虑一个示例,找出每个系的平均工资。
select deptname, avg(salary) as avg_salary
from instructor
group by dept_name;
使用聚集函数的一个重要的点是,任何没有出现在group by子句中的属性,如果出现在select子句中,它只能作为聚集函数的参数。比如,下面的查询是错误的,因为ID没有出现在group by子句中,但是出现在了select子句中,而且没有被聚集。
/\* 错误查询 \*/
select deptname, ID,avg(salary) as avg_salary
from instructor
group by dept_name;
总结来说就是,select子句中的属性只能是分组条件相关属性和聚集函数。这是因为一个特定分组的每个教师都可以有唯一的ID,但是每个分组只输出一个元组,那就无法确定要选择哪个元组值作为唯一输出。
7.3 having子句
有时候,对分组的限定条件比对元组的限定条件更有用。比如,我们只对教师的平均工资超过42000美元的那些系感兴趣,这个条件并不针对单个元组,而是针对group by
子句构成的每个分组。我们可以使用having
子句做到这些。SQL在形成分组以后才应用having
子句中的谓词,因此在having
子句中可以使用聚集函数。
select dept_name,avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary) > 42000;
与select子句类似,任何出现在having子句中,但是没有被聚集的属性必须出现在group by子句中。
7.4 对空值和布尔值的聚集
考虑如下查询:
select sum(salary)
from instructor;
如果有教师的薪资是null,SQL将忽略其值。在聚集函数中,除count(*)外的所有函数都会忽略输入集合中的空值。由于空值被忽略,聚集函数的输入值集合可能为空集,规定空集的count运算值为0,其它所有聚集运算会返回一个空值,在一些更加复杂的SQL结构中空值的影响会更加难以捉摸。
在SQL1999中引入了布尔数据类型,它可以取true,false和unknown三种值,聚集函数some
和every
可以应用于布尔值的集合,并分别计算这些值取(or)和取(and)的结构。
8 嵌套子查询
8.1 集合成员资格
使用in
可以测试集合成员资格,使用not in
可以测试集合资格的缺失,其实就是可以粗浅的理解为过滤。比如查询2017年秋季的课程,在看看他们是不是也是2018年的春季的课程集合中的成员,你当然可以使用交运算完成,但你也可以使用嵌套子查询实现。注意下面使用了distinct
。
select distinct course_id
from section
where semester = 'Fall' and year = 2017 and
course_id in
(select course_id
from section
where semester = 'Srping' and year = 2018);
8.2 集合比较
之前我们有一个查询时"找出工资至少比运维部某员工的工资要高的所有员工的姓名"。之前的做法是,
select distinct T.name
from emp as T,emp as S
where T.sal > S.sal and S.deptname = '运维部';
但是我们可以使用some
关键字,结合子查询实现同样的操作。
select name from emp where sal > some
(select sal
from emp where department = '运维部‘);
还有另外一个关键字all
代表所有也常常用于集合比较。练习找出平均工资最高的系。
select dept_name
from instructor
group by deptname
having avg(salary) >= all
(select avg(salary) from instructor group by dep_tname);
写起来好舒服的。
8.3 空关系测试
SQL中包含一个特性,测试一个子查询的结果是否存在元组,exist
结构在作为参数的子查询非空时返回true值。我们可以用它实现"找出2017年秋季学期和2018年春季学期都开课的所有课程"。
select course_id from section as S
where semester = 'Fall' and year = 2017 and
exist(select \*
from section as T
where semester = 'Spring' and year = 2018 and
S.course_id = T.course_id);
实际上它并不是特别好理解,注意比较与in
在写法上的区别。上面的查询还说明了SQL的一个特性:来自外层的查询相关名称(上述查询中的S)可以用在where
子句的子查询中。
使用了来自外层查询的相关名称的子查询被称为相关子查询。
在包含了子查询的查询中,在相关名称上可以应用作用域规则,根据此规则,在一个子查询中只能使用此子查询本身定义的,或者包含此子查询的任何查询中定义的相关名称,如果一个相关名称既在子查询中局部定义,有在包含该子查询的查询中全局定义,则局部定义有效。这有点类似编程语言中的局部变量的规则。
上面的相关子查询是不是觉得怪别扭的。怎么理解呢?我们来分析下它的执行步骤。
先看一个简单的例子。
select \* from emp e
where sal >(select avg(sal) from emp where job = e.job);
1.首先执行外查询 select * from emp e,
2.然后取出第一行数据,将数据中的JOB传递给内查询
内查询(select avg(sal) from emp where job = e.job) 根据外查询传递的JOB来查询平均工资,此时相当于select avg(sal) from emp where job = ‘CLERK’;
3.外查询取出的第一行数据比较sal是否大于内查询查出的平均工资,若大于,则保留改行作结果显示,反之则不保留
4.依次逐行查询、比较、是否保留;类似Java的for循环一样
在回过头看上面语句的执行步骤
select course_id from section as S
where semester = 'Fall' and year = 2017 and
exist(select \*
from section as T
where semester = 'Spring' and year = 2018 and
S.course_id = T.course_id);
执行步骤:
- 执行第一行,查询section表符合semester = 'Fall’以及 year = 2017的course_id值
2.然后取出外查询的第一行数据,此时S.course_id相当于明牌。找到符合semester = ‘Spring’ 和 year = 2018 两个条件,且course_id与外层查询相同的课程,有则返回true,没有则返回false。
3.内查询返回true时,则把该行数据结果保留,返回false时,则不保留
理解了吧。
自然,也存在not exist
。可以用它表达超集的概念,比如”关系A包含关系B“可以表示为
not exist (B except A)
,现在通过not exist
实现"找出选修了Biology系开设的所有课程的所有学生"。
是不是觉得一头雾水,别着急,我们先翻译下题意,题目换一种表达方式就是,找出选修的课程包含Biology的所有课程的学生。
发现A包含B结构了吗?学生选修的课程是A,Biology系开设的所有课程是B。
select S.ID,S.name
not exist
(select course_id from course
where dept_name = 'Biology')
except
(select T.course_id
from take as T
where S.ID = T.ID)
再来,我们前面提到了一个查询,“找出选修了ID为10101的教师所讲授的课程段的(不同)学生总数”。
回顾下我们之前怎么做的?我们使用了in
判断集合成员资格的形式进行了实现。核心套路是,大范围是否在小范围里。
select count(distinct ID)
from takes
where (course_id,sec_id,semester,year) in (select course_id,sec_id,semester,year
from teaches
where teaches.ID='10101');
现在用子查询来做看看。核心套路是,先查后筛,先查所有课程的参与学生数,再筛选器教师是否为10101,其时间段是否一致。
select count(distinct ID)
from takes
where exist (select course_id,sec_id,semester,year
from teaches
where teaches.ID='10101'
and takes.course_id = teaches.course_id
and takes.sec_id = teaches_sec_id
and takes.semester = teaches_semester
and takes.year = teaches.year
);
8.4 重复元组存在性测试
unique
可以检测结果是否存在重复元组。找出2017年最多开设一次的所有课程。
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,涵盖了95%以上大数据知识点,真正体系化!
由于文件比较多,这里只是将部分目录截图出来,全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频,并且后续会持续更新
elect course_id,sec_id,semester,year
from teaches
where teaches.ID=‘10101’
and takes.course_id = teaches.course_id
and takes.sec_id = teaches_sec_id
and takes.semester = teaches_semester
and takes.year = teaches.year
);
#### 8.4 重复元组存在性测试
`unique`可以检测结果是否存在重复元组。找出2017年最多开设一次的所有课程。
[外链图片转存中...(img-f8SDtxhN-1715004854117)]
[外链图片转存中...(img-atMpSnOT-1715004854117)]
[外链图片转存中...(img-Ka3lj0j6-1715004854117)]
**既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,涵盖了95%以上大数据知识点,真正体系化!**
**由于文件比较多,这里只是将部分目录截图出来,全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频,并且后续会持续更新**
**[需要这份系统化资料的朋友,可以戳这里获取](https://bbs.csdn.net/topics/618545628)**
更多推荐
所有评论(0)