数据库中常用的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
上一篇: 感情骗子的表现
下一篇: 恋爱的四个阶段:你们走到哪一段了?
推荐阅读
-
C#操作数据库中存取图片文件的方法
-
在php中设置session用memcache来存储的方法总结
-
关于ajax对象一些常用属性、事件和方法大小写比较常见的问题总结
-
C#访问SQL Server数据库的实现方法
-
Mybatis中的动态SQL语句解析
-
sql2005 数据库转为sql2000数据库的方法(数据导出导入)
-
在sqlserver2005中安装sql server 2000的示例数据库northwind的方法
-
SQL 2005 ERROR:3145 解决办法(备份集中的数据库备份与现有的数据库不同)
-
SQL Server 2005 中做全文检索的方法分享
-
sql server 2005中使用with实现递归的方法