欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

数据库中常用的SQL总结

程序员文章站 2022-11-10 21:57:40
创建表 以教师为例 create table teacher( id varchar(10), name varchar(20), department varchar(20), salary nu...

创建表

以教师为例

create table teacher(

id varchar(10),

name varchar(20),

department varchar(20),

salary numeric(8,2),

primary key(id)

);

插入

insert into teacher values(10215,’牟老师’,’计算机学院’,60000.00)

我们也可以在查询结果上进行插入,例如我们让计算机的老师的工资提高50%:

insert into teacher select id,name,department,salary*1.5 from teacher where department=‘计算机’

删除表中所有数据

delete from teacher

删除这个表

drop table teacher

删除部分符合条件的数据

delete from teacher where salary<10000.00

更新

update teacher set salary = salary*1.5 where department=‘计算机’

sql还提供了case结构,例如:

update teacher set salary =

case when salary<10000.00 then salary*1.5 else salary*1.1 end

为表添加新属性

以添加性别为例

alert table teacher add sex char(2)

删除属性

以性别为例

alert table teacher drop sex

单关系查询

select name from teacher

若果想去掉重复的结果可以在要查的属性前面加distinct关键字

select distinct name from teacher

select子句中还可以带运算符

select salary*2 from teacher

还可以查询满足某些条件的元组,条件在where子句中定义

select name from teacher where salary>50000.00 and department =‘计算机’

多关系查询

简单来说就是涉及到多个表的查询,为了演示方便我们现在再创建一个表department,用来表示各个学院都在那栋楼里,如下:

create table department(

name varchar(20),

building varchar(30),

primary key(name)

);

现在我们查询各个老师都在哪栋建筑里面

select t.name,d.building

from teacher as t,department as d

where t.department = d.name

这里面的t和d分别是表teacher和department的别名,实际查询中是把teacher和department做了一个笛卡尔积。

字符串运算

在查询过程中我们可以用like操作符对字符串进行匹配

百分号%:匹配任意子串 ‘example%’:匹配任一以example开头的字符串 ‘%example%’:匹配任意含有example的字符串 下划线_:匹配任意一个字符 ‘_ _ _’:匹配只包含三个字符的字符串 ‘_ _ _%’:匹配至少包含三个字符的字符串

在like比较运算中我们使用escape关键字来定义转义符,例如:

like ‘ab\%cd%’ escape ‘\’ :匹配所有以ab%cd开头的字符串,其中’\’就是转义符

排列查询结果的显示顺序

可以使用order by关键字来定义查询结果的顺序,例如:

select name from teacher order by name desc

desc表示降序,asc表示升序,默认是升序

集合运算

union、intersect、except分别代表u、∩、-运算。例如:

并运算:

(select name from teacher where department = ‘计算机’)

union

(select name from teacher where department =‘软件’)

交运算:

(select name from teacher where department=‘计算机’)

intersect

(select name from teacher where salary>40000.00)

差运算:

(select name from teacher where department=‘计算机’)

except

(select name from teacher where salary<40000.00)

聚集函数 平均数:avg 最大值:max 最小值:min 计数:count 总和:sum

基本聚集,例如:

select avg(salary)from teacher

分组聚集,例如:

select department,avg(salary)as avg_salary from teacher group by department

在group by子句中所有属性上取值相同的元组将会被分到一组中。我们还可以使用having对分组进行限定,例如:

select department,avg(salary)as avg_salary from teacher group by department having avg(salary)> 50000.00

注意having在形成分组后才发挥作用。

嵌套子句

连接词in或者not in测试元组是否是集合中的成员,例如:

select department from teacher where name in (select name from teacher where salary > 50000.00)

集合的比较

some和all关键字,>some表示:至少比某一个要大;>all表示:比所有都大。例如:

select name from teacher where salary>some(select salary from teacher where department=‘计算机’)

select name from teacher where salary>all(select salary from teacher where department=‘计算机’)

空关系测试

我们可以用exists来测试一个子查询的结果是否存在元组,例如:

select name where teacher where department = ‘计算机’ and exists (select name from teacher where salary>10000.00)

我们也可以用not exists来测试子查询的结果中是否不存在元组,我们可以将“关系a包含关系b”写成not exists(b except a),例如:

“找出选修了biology学院开设所有课程的同学”

select s.name from student as s where not exists (

(select courseid from course where department = ‘biology’)

except

(select courseid from takes as t where s.id = t.id)

)

重复元组测试

我们使用unique关键字来判断一个子查询的结果中是否含有重复元组,如下:

select name from teacher where unique (select name from teacher where department=‘计算机’)

from子句中的子查询

select department,avg_salary from (select department,avg(salary)as avg_salary from teacher group by department )where avg_salary>50000.00

我们也可以用lateral关键字作为前缀,以便访问from子句中在它前面的表,例如:

select name,salary,avg_salary from teacher t1,lateral (select avg(salary)as avg_salary from teacher t2 where t1.deparment = t2.department)

with子句

with子句能够定义临时关系,例如:

with avg_salary (department,value)as(select department,avg(salary)from teacher group by department)

select name from teacher,avg_salary where teacher.department = avg_salary.department and teacher.salary>avg_salary.value