GROUP BY 关键字
多层嵌套分组
update 关键字
Mysql 根据一个表数据更新另外一个表
update 更新表 set 字段 = (select 参考数据 from 参考表 where 参考表.id = 更新表.id);
update table_2 m set m.column = (select column from table_1 mp where mp.id= m.id);
实例可以看 sql46题的联系
UPDATE sc,(SELECT sc.cno,AVG(score) AS av FROM sc,course c,teacher t WHERE sc.cno=c.cno AND c.tno=t.tno AND t.tname='谌燕' GROUP BY sc.cno) a
SET sc.score=a.av
WHERE sc.cno=a.cno
-- 把“SC”表中“谌燕”老师教的课的成绩都更改为此课程的平均成绩;
UPDATE SC SC,
-- 这个的逻辑是将查询出来的数据结果集放进临时表,update 关键字后面也是可以放几张表的
(SELECT A.cno, AVG(SC.SCORE) SCORE
FROM SC SC,
(
-- 这个的逻辑是先查出老师教了几门课
SELECT cno
FROM COURSE
WHERE tno IN (SELECT tno FROM TEACHER WHERE TNAME = "谌燕")
) A
WHERE SC.cno = A.cno
GROUP BY SC.cno
) AA
SET SC.SCORE = AA.SCORE
WHERE SC.cno = AA.cno;
关于 sql的 between … and 的边界问题
mysql:
select * from user where userId between 5 and 7;
查询userId为5、6,7的user,userId范围是包含边界值的。
另外 not between的范围是不包含边界值。
Oracle :
Oracle的BETWEEN..AND..前后都是闭区间,
表里面的 比较
-- 8、查询课程编号“c002”的成绩比课程编号 “c001”课程低的所有同学的学号、姓名;
-- 课程号、学号、分数都在一张表里面,属于自比型
SELECT * from student s
join sc a on a.sno = s.sno
join sc b on b.sno = s.sno
where a.cno = 'c001' and b.cno = 'c002' and a.score < b.score;
-- 1、查询“c001”课程比“c002”课程成绩高的所有学生的学号;
-- 自比较
-- 方法 一
SELECT a.*
from sc a
join sc b on a.sno = b.sno
where a.cno = 'c001' and b.cno = 'c002' and a.score > b.score;
-- 方法 二
select a.* from
(select * from sc a where a.cno='c001') a,
(select * from sc b where b.cno='c002') b
where a.sno=b.sno and a.score > b.score;
-- 方法 三
select * from sc a
where a.cno='c001'
and exists(select * from sc b where b.cno='c002' and a.score>b.score
and a.sno = b.sno)
报错
You can’t specify target table ‘sc’ for update in FROM clause
解决
也就是说将select出的结果再通过中间表select一遍,这样就规避了错误。注意,这个问题只出现于mysql,mssql和oracle不会出现此问题。
You can't specify target table for update in FROM clause含义:不能在同一表中查询的数据作为同一表的更新数据。
例如:
我想查询t_user_asset的余额加上50000作为更新字段f_cashAmount的值,这样写是不行的。
UPDATE t_user_asset SET f_cashAmount =
(
SELECT (ua.f_cashAmount+50000) cashAmount FROM t_user_asset ua WHERE ua.f_userId = 290
)
WHERE f_userId = 290
修改成以下写法就行,意思就是变个方向,在select外边套一层,让数据库认为你不是查同一表的数据作为同一表的更新数据:
UPDATE t_user_asset SET f_cashAmount =
(
SELECT ub.cashAmount FROM
(
SELECT (ua.f_cashAmount+50000) cashAmount FROM t_user_asset ua WHERE ua.f_userId = 290
) ub
)
WHERE f_userId = 290
以上问题只针对mysql数据库
复制表数据
SQL INSERT INTO SELECT 语句
通过 SQL,您可以从一个表复制信息到另一个表。
INSERT INTO SELECT 语句从一个表复制数据,然后把数据插入到一个已存在的表中。
SQL INSERT INTO SELECT 语句
INSERT INTO SELECT 语句从一个表复制数据,然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响。
SQL INSERT INTO SELECT 语法
我们可以从一个表中复制所有的列插入到另一个已存在的表中:
INSERT INTO table2
SELECT * FROM table1;
或者我们可以只复制希望的列插入到另一个已存在的表中:
INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;
分段统计
-- 统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
-- 在sc 表按 学科分组把每组的数据
select sc.cno ,c.cname,
sum(case when sc.score between 70 and 100 then 1 else 0 end) as "[70-100]"
from sc ,course c
where sc.cno = c.cno
group by sc.cno ,c.cname;
sql聚合函数与其他语句(关系,执行过程)
选择列表中的列 'SC.CNO' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。
元数据库为Stu_C,含有三个表,S、SC、C。执行的语句如下:
use Stu_C
select SNO,cno,sum(grade) sum_grade
from SC
where GRADE>=60
group by SC.SNO
having count(CNO)>=4
order by sum_grade desc
目的是检索选修4门课以上课程的学生总成绩(排除不及格的),并按总成绩的降序排列出来。根据提示我们容易知道,select语句中多了cno这个属性列。如果将其去掉执
行如下:
use Stu_C
select SNO,sum(grade) sum_grade
from SC
where GRADE>=60
group by SC.SNO
having count(CNO)>=4
order by sum_grade desc
[sql] view plain copy
当使用group by子句时则select中关于表的原始属性名只能是被"by"的属性,如上例中的SNO。
若想输出其他属性列,则应在select子句后加上相应的聚合函数(count(*),count(列名),sum(列名),avg(列名),max(列名),min(列名))。
其实这也很容易理解,当参照SNO被分组后,每一个SNO的具体实例与其他任意一个属性都变成一对一或一对多的关系,
只有通过聚合函数将每组其他想要的属性的多个实例算成一个值,即SNO与其形成一对一的关系后才能列出。
而且一旦SQL语句中使用了group by子句后聚合函数都变成分别对每组起作用。
一条SQL语句个子句的先后作用过程:
from→where→group by→select(含聚合函数)→order by
所以当select子句中含有sum(grade) sum_grade时,最后的排序子句可以是order by sum_grade desc。
like与 regexp的区别
REGEXP 是sql 正则表达式 函数
效率
在 like 的匹配语句的最左边使用统配符的前提下,与 regexp 没什么区别
字符串截取
这个不大懂
需求
数据里的域名可能是
http://www.baidu.com/abc.html?eee
http://www.baidu.com/abc.html#wedf
http://www.baidu.com/wer/hjk.html#whhhf
http://www.baidu.com/abc/ggg/
我需要截取成
http://www.baidu.com/abc.html
http://www.baidu.com/abc.html
http://www.baidu.com/wer/hjk.html
http://www.baidu.com/abc/ggg
sql
select left(talk_source,LEN(Talk_source)-CHARINDEX('?',REVERSE(Talk_source))) as Talk_source2,*
from Zoosnet_Data
where Project_ID=1 and (Zoosnet_Visit_Start_Time between '2013-1-1' and '2013-8-1')
and Zoosnet_Data.Talk_Source like '%http://www.baidu.com/abc.html?eee%'
改进
Project_ID=1, Visit_Start 和Talk_Source都必须加上索引!
如果Talk_Source都是以http://打头的,那么like表达式不要以%开头。以%开头会慢很多,而%在结尾的话可以利用到索引。
最上面的sql好像只能处理问号, 处理#,/可以这样写:
select replace(replace(left(talk_source, patindex('%[?#]%',
talk_source+'?')-1)+'$$','/$$',''),'$$','') as Talk_source2,*
from Zoosnet_Data
where Project_ID=1 and (Zoosnet_Visit_Start_Time between '2013-1-1' and '2013-8-1')
and Zoosnet_Data.Talk_Source like 'http://www.baidu.com/abc.html?eee%'
case when then else 的用法
-- 按各科平均成绩从低到高和及格率的百分数从高到低顺序
-- 这个是对sc 表进行一个排序
-- 及格率的百分数,及格的人数,除于总数,问题的 当一科成绩及格怎么加一
select sc.cno ,avg(sc.score) as 平均成绩 , sum(case when score>=60 then 1 else 0 end)/count(*)
as 及格率
from sc group by cno
order by 平均成绩 ,及格率 desc;