数据库中常用的SQL总结

创建表

以教师为例

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