数据库常用函数
/**接收地址栏参数***/
function GetQuery(key){
var search = location.search.slice(1);//得到get方式提交的查询字符串
var arr = search.split("&");
for(var i = 0;i < arr.length; i++){
var ar = arr[i].split("=");
if(ar[0] == key){
return ar[1];
}
}
}
function GetQuery(key){
var search = location.search.slice(1);//得到get方式提交的查询字符串
var arr = search.split("&");
for(var i = 0;i < arr.length; i++){
var ar = arr[i].split("=");
if(ar[0] == key){
return ar[1];
}
}
}
select * from table where c like '%h%'
sum 函数返回数值列的总数(总额)
select sum(column_name) from tabe_name 返回(column_name 数目)
group by 语句用于结合合计函数,根据一个或者多个列对结果集进行分组
select customer,sum(orderprice) from table group by customer 返回每一个总和
count()函数返回匹配指定条件的件数
select count(column_name from table_name 返回指定列的值的数目null不计入
agv 函数返回值列的平均值 null值不包在计算中
select agv(column_name) from table_name
select agv(orderprice) as orderaverage from orders 返回 orderprice 数字
查询orderprice值高于平均值的客户
select name from table_name where orderprice>(select avg(orderprice) from orders)
distinct用于返回唯一不同的值
select distinct 列名称 from 表名称
select name from table_name where a is null 查询为空的字段
is not null 查询不为空的字段
having
查找订单总金额小于2000的订单总金额
select name,sum(orderprice) from table_name group by name having sum(orderprice)<2000
查找订单金额超过1500的订单总金额
select name,sum(orderprice) from table_name group by name having sum(orderprice)>1500
ucase函数把字段的值转换为小写
lcase函数把字段的值转换成大写
mid提权字符串
select mid(name,1,2 ) as n from table_name 从name中提取三个字符
三表联查
-
第一种方法:select * from student,teacher,project where student.id=teacher.sid and student.id=project.sid;
-
第二种:select * from student inner join teacher on student.id=teacher.sid inner join project on student.id=project.sid;
-
每个分类的答题记录的和 (使用的是子查询,当然也可以用联接查询),正确数记录的和
(
select TID from 题目表 whereDID in
(select DID from 分类表 group by Dname)
)
A表的字段 Aid Bid Cid Aname
B表的字段 Bid Bname
C表的字段 Cid Cname
B表的字段 Bid Bname
C表的字段 Cid Cname
select Aid Bname Cname from A left join B on A.Bid = B.Bid inner join C on A.Cid = C.Cid where 其他条件