随笔
来源:互联网 发布:倪匡蔡澜直播的软件 编辑:程序博客网 时间:2024/06/03 11:18
<?php
//封装curl函数
//支持http、https协议,支持get和post请求方式
function request($url,$https=true,$method='get',$data=null){
//1.初始化curl
$ch = curl_init($url);
//2.设置相关请求参数
//设置不直接输出
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
//判断是否为https请求
if($https === true){
//支持https协议
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
}
//判断是否为post请求
if($method === 'post'){
//开启post
curl_setopt($ch, CURLOPT_POST, true);
//发送post数据
curl_setopt($ch, CURLOPT_POSTFIELDS, $data);
}
//3.发送请求
$content = curl_exec($ch);
//4.关闭链接
curl_close($ch);
return $content;
}
//邮件发送测试方法
function sendMail($subject,$msghtml,$sendAddress){
//引入发送类phpmailer.php
require './PHPMailer/class.phpmailer.php';
//实列化对象
$mail = new PHPMailer();
/*服务器相关信息*/
$mail->IsSMTP(); //设置使用SMTP服务器发送
$mail->SMTPAuth = true; //开启SMTP认证
$mail->Host = 'smtp.163.com'; //设置 SMTP 服务器,自己注册邮箱服务器地址
$mail->Username = 'zhangch16'; //发信人的邮箱用户名
$mail->Password = 'zhangchunhao123'; //新注册邮箱,客户端授权码
/*内容信息*/
$mail->IsHTML(true); //指定邮件内容格式为:html
$mail->CharSet ="UTF-8"; //编码
$mail->From = 'zhangch16@163.com'; //发件人完整的邮箱名称
$mail->FromName ="php55开发组"; //发信人署名
$mail->Subject = $subject; //信的标题
$mail->MsgHTML($msghtml); //发信主体内容
// $mail->AddAttachment("fish.jpg"); //附件
/*发送邮件*/
$mail->AddAddress($sendAddress); //收件人地址
//使用send函数进行发送
if($mail->Send()) {
//发送成功返回真
return true;
// echo '您的邮件已经发送成功!!!';
} else {
return $mail->ErrorInfo;//如果发送失败,则返回错误提示
}
}
// ----------------------------------------------------------------------
// 遍历文件夹
$path_info="./ThinkPHP";
function read_all_file($path,$level=0){
is_dir($path) or die('不是目录');
$res=opendir($path);
if (!$res) {
die('打开目录失败!');
}
while (false!==($file=readdir($res))) {
if ($file=='.'||$file=='..') {
continue;
}
$str=" ";
$offset=str_repeat($str,$level*6);
echo $offset.$file."<br>";
$current_path=$path."/".$file;
if (is_dir($current_path)) {
read_all_file($current_path,$level+1);
}
}
closedir($res);
}
read_all_file($path_info);
// 递归删除目录
$path_info="./ThinkPHP";
function delete_dir($path){
$res=opendir($path);
while (false!==($file=readdir($res))) {
if ($file=='.'||$file=='..') {
continue;
}
$current_path=$path."/".$file;
if (is_file($current_path)) {
unlink($current_path);
}else{
delete_dir($current_path);
}
}
closedir($res);
rmdir($path);
}
// ----------------------------------------------------------------------------------
// 无限极分类
function categoryList($arrs,$level=0,$pid=0)
{
//使用静态变量,保存结果数组
static $categorys = array();
//循环读取原始分类数据,把符合条件的按顺序存入$categorys中
foreach($arrs as $arr){
//找出$arr[pid]==$pdi的数据
if($arr['pid']==$pid){
$arr['level'] = $level;
$categorys[] = $arr;
//递归调用
categoryList($arrs,$level+1,$arr['id']);
}
}
return $categorys;
}
//调用函数
dump(categoryList($arrs));
//文件锁-----确保多个进程同时写入文件成功-----------------------------------------------------
$fp = fopen("lock.txt","w+");
if(flock($fp,LOCK_EX)){
//获得写锁,写数据
fwrite($p,"write something");
//解除锁定
flock($fp,LOCK_UN);
}else{
echo"file is locking....";
}
fclose($fp);
// 用php实现一个双向队列
//---------------------------------------------------------------------------------------
// sphinx php操作
// indexer.exe +sphinx.conf路径 + 源文件(movie)
include_once('sphinxapi.php');
$sphinx=new sphinxclient();
$sphinx->setserver('lcalhost',9312);
$res=$sphinx->query($word);
$matches=$res['matches'];
// 得到关键字所在的id数组
$res=array_keys($matches);
$keys='';
foreach($res as $key){
$keys.=$key.',';
}
$keys=rtrim($keys,',');
// 设置关键字样式
// $style=array('before_match'=>'<font color="red">','after_match'=>'</font>')
// row=$sphinx->buildexcerpts($row,$index,$word,$style);
//----------------------------------------------------------------------------------------------------
// php操作 memcache
$mem=new Memcache();
$mem->connect('localhost',11211);
$mem->set('name','zs');
$mem->get('name');
//----------------------------------------------------------------------------------------------------
// php操作 mongodb
$mem=new MongoClient();
$mem->connect('localhost',6317);
$mem->set('name','zs');
$mem->get('name');
// ----------------------------------------------------
// 防XSS攻击
function filterXSS($string){
//相对index.php入口文件,引入HTMLPurifier.auto.php核心文件
require_once './Public/Common/htmlpurifier/HTMLPurifier.auto.php';
// 生成配置对象
$cfg = HTMLPurifier_Config::createDefault();
// 以下就是配置:
$cfg -> set('Core.Encoding', 'UTF-8');
// 设置允许使用的HTML标签
$cfg -> set('HTML.Allowed','div,b,strong,i,em,a[href|title],ul,ol,li,br,p[style],span[style],img[width|height|alt|src]');
// 设置允许出现的CSS样式属性
$cfg -> set('CSS.AllowedProperties', 'font,font-size,font-weight,font-style,font-family,text-decoration,padding-left,color,background-color,text-align');
// 设置a标签上是否允许使用target="_blank"
$cfg -> set('HTML.TargetBlank', TRUE);
// 使用配置生成过滤用的对象
$obj = new HTMLPurifier($cfg);
// 过滤字符串
return $obj -> purify($string);
}
// 加密密码
function getPwd(){
$key=substr(sha1('bjcz55'),9,10);
$password=substr(sha1($password),19,10);
return sha1($key.$password);
}
// 读取文件
$len=1024;
$path='./file.txt';
$res=fopen($path,'r');
while($str=fread($path,$len)){
$str=str_replace('\r\n','<br>',$str);
echo $str;
}
// 反转字符串 strrev();
$str='abcd';
$arr=str_split($str);
$s='';
for($i=count($arr)-1;$i>=0;$i--){
$s.=$arr[$i];
}
echo $s;
//单例模式
final class Db{
private static $obj=null;
private function __construct(){}
private function __clone(){}
public static function getInstance(){
if(!self::$obj instanceof self){
self::$obj=new self();
}
return self::$obj;
}
}
$obj1=Db::getInstance();
//单例工厂
final class fanctory{
private static $obj=null;
private function __construct(){}
private function __clone(){}
public static function getInstance($classname){
if(!isset(self::$obj[$classname])){
self::$obj[$classname]=new $classname();
}
return self::$obj[$classname];
}
}
$obj1=Db::getInstance();
// 100以内素数个数
$n='';
for($i=1;$i<=100;$i++){
$s=0;
for($j=1;$j<=$i;$j++){
if($i%$j==0){$s++;}
}
if($s==2){$n++;echo $i,'<br>';}
}
echo '<br>'.$n;
//踢猴子
function king($n,$m){
$arr=range(1,$n);
$i=0;
while(count($arr)>1){
$i+=1;
$tmp=array_shift($arr);
if($i%$m!=0){
array_push($arr,$tmp);
};
}
return $arr[0];
}
echo king(10,7);
// 冒泡排序
for($i=0;$i<count($arr)-1;$i++){
for($j=0;$j<count($arr)-1-$i;$j++){
if($arr[$j]>$arr[$j+1]){
$temp=$arr[$j];
$arr[$j]=$arr[$j+1];
$arr[$j+1]=$temp;
}
}
}
//用一个循环将一个数组里的不同元素个数统计出来;
$arr=[1,2,3,1,44,3];
$arr1='';
foreach($arr as $k=>$v){
if(!in_array($v,$arr1)){
$arr1[$v]=1;
}else{
$arr1[$v]=$arr1[$v]+1;
}
}
dump($arr1);
//防止表单重复提交
<input type="submit" onclick="this.disabled=true; this.value='登录中...'; this.form.submit();" value="登录" />
// 数据表去重
delete from qs_users where id not in (
select temp.id from (
select id from qs_users
group by username
) temp
)
//php调用同级其他类的方法
use Home\Controller\IndexController;
IndexController::logOut();
//tp中引入其他类时 实例化需要加反斜杠
$index=new \IndexController();
如果调用server层直接用D('index','service');
// 子查询先组队在排序(多对多表之间的连接,必须连接的student_id是不重复的)
SELECT
*
FROM
(
SELECT
e.*, a.startdateime AS startTime,
a.enddatetime AS endTime,
a.examarrangement_id
FROM
examinationstudent AS e
LEFT JOIN examarrangement AS a ON a.student_id = e.student_id
AND a.examination_id = '15c092ca83e711e78611e03f499fe56f'
WHERE
e.examination_id = '15c092ca83e711e78611e03f499fe56f'
) AS t
GROUP BY
t.student_id
ORDER BY
t.startTime
//where or where 连用时 要将or where 用括号括起来 组成一个条件
where (a=1 or b=2) and c=3;
//php 实现多线程 同时执行两个程序 (既前面的程序不用执行完 后面的程序 同时进行)
// 可以通过ajax 来实现多线程 (async: false 同步执行 在success没有返回值之前是不执行后面的程序的)
// async: true 异步执行 不待success返回值 同时执行后面的程序
// $.ajax 的global属性
// 生成中文乱码 iconve(); 可以将中文转码
行列转换
select username,
sum(if(cource='chinese',score,0)) as chinese,
sum(if(cource='math',score,0)) as math,
sum(if(cource='english',score,0)) as english from grade group by username;
-简单Case函数
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
巧妙地sql语句可以省去一次循环
SELECT firm_id,firm_name,phonenumber,createtime,
case
when firm_type=0 then '制造商'
when firm_type=1 then '供应商'
when firm_type=2 then '供应商&制造商'
END firm_type,
(SELECT COUNT(*) from device WHERE (firm.firm_id = device.supplier_id or firm.firm_id = device.manufacturer_id) )
+ (SELECT COALESCE(SUM(total),0) from consumable WHERE (firm.firm_id = consumable.supplier_id or firm.firm_id = consumable.manufacturer_id))
+ (SELECT COALESCE(SUM(total_number),0) from instrument WHERE (firm.firm_id = instrument.supplier or firm.firm_id = instrument.manufacturer))
as consumable_count
FROM firm;
mysql 函数:COALESCE(SUM(total),0) 返回参数第一个不为null的值;
//一次循环将数据相同的组合到一起 并将这个组合取名字 并算出每个组合的个数
foreach($list as $k=>$v){
$result[$v['typebasic_id']]['sub'][] = $v;
$result[$v['typebasic_id']]['name'] =$v['typebasic_name'];
$result[$v['typebasic_id']]['num'] =count($result[$v['typebasic_id']]['sub']);
}
可以将数据组合成一个字符串;
select GROUP_CONCAT(typebasic_name) as a,count(typebasic_name) from typebasic where typebasic_id='000FD8D27D04D1F30B846D969B454DCD';
select GROUP_CONCAT(typebasic_name,',',is_delete,',','$') as a,count(typebasic_name) from typebasic 将字符串设置一个标志位$;
//将time类型转化为秒数
//将秒数转化为time类型
mysql> select time_to_sec(120) from dual; 00:02:00
mysql> select sec_to_time(‘00:02:01’) from dual; 121
行列转换 加 两表联合起来(union all)
SELECT cname AS "姓名",
SUM(IF(cource="语文",score,0)) AS "语文",
SUM(IF(cource="数学",score,0)) AS "数学",
SUM(IF(cource="物理",score,0)) AS "物理",
SUM(score) AS "总成绩",
ROUND(AVG(score),2) AS "平均成绩"
FROM tb
GROUP BY cname
UNION ALL
SELECT
"总成绩平均数",
ROUND(AVG(`语文`),2) , ROUND(AVG(`数学`),2), ROUND(AVG(`物理`),2), ROUND(AVG(`总成绩`),2), ROUND(AVG(`平均成绩`),2)
FROM(
SELECT "all",cname AS "姓名",
SUM(IF(cource="语文",score,0)) AS "语文",
SUM(IF(cource="数学",score,0)) AS "数学",
SUM(IF(cource="物理",score,0)) AS "物理",
SUM(score) AS "总成绩",
AVG(score) AS "平均成绩"
FROM tb
GROUP BY cname
)tb2
GROUP BY tb2.all;
//封装curl函数
//支持http、https协议,支持get和post请求方式
function request($url,$https=true,$method='get',$data=null){
//1.初始化curl
$ch = curl_init($url);
//2.设置相关请求参数
//设置不直接输出
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
//判断是否为https请求
if($https === true){
//支持https协议
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
}
//判断是否为post请求
if($method === 'post'){
//开启post
curl_setopt($ch, CURLOPT_POST, true);
//发送post数据
curl_setopt($ch, CURLOPT_POSTFIELDS, $data);
}
//3.发送请求
$content = curl_exec($ch);
//4.关闭链接
curl_close($ch);
return $content;
}
//邮件发送测试方法
function sendMail($subject,$msghtml,$sendAddress){
//引入发送类phpmailer.php
require './PHPMailer/class.phpmailer.php';
//实列化对象
$mail = new PHPMailer();
/*服务器相关信息*/
$mail->IsSMTP(); //设置使用SMTP服务器发送
$mail->SMTPAuth = true; //开启SMTP认证
$mail->Host = 'smtp.163.com'; //设置 SMTP 服务器,自己注册邮箱服务器地址
$mail->Username = 'zhangch16'; //发信人的邮箱用户名
$mail->Password = 'zhangchunhao123'; //新注册邮箱,客户端授权码
/*内容信息*/
$mail->IsHTML(true); //指定邮件内容格式为:html
$mail->CharSet ="UTF-8"; //编码
$mail->From = 'zhangch16@163.com'; //发件人完整的邮箱名称
$mail->FromName ="php55开发组"; //发信人署名
$mail->Subject = $subject; //信的标题
$mail->MsgHTML($msghtml); //发信主体内容
// $mail->AddAttachment("fish.jpg"); //附件
/*发送邮件*/
$mail->AddAddress($sendAddress); //收件人地址
//使用send函数进行发送
if($mail->Send()) {
//发送成功返回真
return true;
// echo '您的邮件已经发送成功!!!';
} else {
return $mail->ErrorInfo;//如果发送失败,则返回错误提示
}
}
// ----------------------------------------------------------------------
// 遍历文件夹
$path_info="./ThinkPHP";
function read_all_file($path,$level=0){
is_dir($path) or die('不是目录');
$res=opendir($path);
if (!$res) {
die('打开目录失败!');
}
while (false!==($file=readdir($res))) {
if ($file=='.'||$file=='..') {
continue;
}
$str=" ";
$offset=str_repeat($str,$level*6);
echo $offset.$file."<br>";
$current_path=$path."/".$file;
if (is_dir($current_path)) {
read_all_file($current_path,$level+1);
}
}
closedir($res);
}
read_all_file($path_info);
// 递归删除目录
$path_info="./ThinkPHP";
function delete_dir($path){
$res=opendir($path);
while (false!==($file=readdir($res))) {
if ($file=='.'||$file=='..') {
continue;
}
$current_path=$path."/".$file;
if (is_file($current_path)) {
unlink($current_path);
}else{
delete_dir($current_path);
}
}
closedir($res);
rmdir($path);
}
// ----------------------------------------------------------------------------------
// 无限极分类
function categoryList($arrs,$level=0,$pid=0)
{
//使用静态变量,保存结果数组
static $categorys = array();
//循环读取原始分类数据,把符合条件的按顺序存入$categorys中
foreach($arrs as $arr){
//找出$arr[pid]==$pdi的数据
if($arr['pid']==$pid){
$arr['level'] = $level;
$categorys[] = $arr;
//递归调用
categoryList($arrs,$level+1,$arr['id']);
}
}
return $categorys;
}
//调用函数
dump(categoryList($arrs));
//文件锁-----确保多个进程同时写入文件成功-----------------------------------------------------
$fp = fopen("lock.txt","w+");
if(flock($fp,LOCK_EX)){
//获得写锁,写数据
fwrite($p,"write something");
//解除锁定
flock($fp,LOCK_UN);
}else{
echo"file is locking....";
}
fclose($fp);
// 用php实现一个双向队列
//---------------------------------------------------------------------------------------
// sphinx php操作
// indexer.exe +sphinx.conf路径 + 源文件(movie)
include_once('sphinxapi.php');
$sphinx=new sphinxclient();
$sphinx->setserver('lcalhost',9312);
$res=$sphinx->query($word);
$matches=$res['matches'];
// 得到关键字所在的id数组
$res=array_keys($matches);
$keys='';
foreach($res as $key){
$keys.=$key.',';
}
$keys=rtrim($keys,',');
// 设置关键字样式
// $style=array('before_match'=>'<font color="red">','after_match'=>'</font>')
// row=$sphinx->buildexcerpts($row,$index,$word,$style);
//----------------------------------------------------------------------------------------------------
// php操作 memcache
$mem=new Memcache();
$mem->connect('localhost',11211);
$mem->set('name','zs');
$mem->get('name');
//----------------------------------------------------------------------------------------------------
// php操作 mongodb
$mem=new MongoClient();
$mem->connect('localhost',6317);
$mem->set('name','zs');
$mem->get('name');
// ----------------------------------------------------
// 防XSS攻击
function filterXSS($string){
//相对index.php入口文件,引入HTMLPurifier.auto.php核心文件
require_once './Public/Common/htmlpurifier/HTMLPurifier.auto.php';
// 生成配置对象
$cfg = HTMLPurifier_Config::createDefault();
// 以下就是配置:
$cfg -> set('Core.Encoding', 'UTF-8');
// 设置允许使用的HTML标签
$cfg -> set('HTML.Allowed','div,b,strong,i,em,a[href|title],ul,ol,li,br,p[style],span[style],img[width|height|alt|src]');
// 设置允许出现的CSS样式属性
$cfg -> set('CSS.AllowedProperties', 'font,font-size,font-weight,font-style,font-family,text-decoration,padding-left,color,background-color,text-align');
// 设置a标签上是否允许使用target="_blank"
$cfg -> set('HTML.TargetBlank', TRUE);
// 使用配置生成过滤用的对象
$obj = new HTMLPurifier($cfg);
// 过滤字符串
return $obj -> purify($string);
}
// 加密密码
function getPwd(){
$key=substr(sha1('bjcz55'),9,10);
$password=substr(sha1($password),19,10);
return sha1($key.$password);
}
// 读取文件
$len=1024;
$path='./file.txt';
$res=fopen($path,'r');
while($str=fread($path,$len)){
$str=str_replace('\r\n','<br>',$str);
echo $str;
}
// 反转字符串 strrev();
$str='abcd';
$arr=str_split($str);
$s='';
for($i=count($arr)-1;$i>=0;$i--){
$s.=$arr[$i];
}
echo $s;
//单例模式
final class Db{
private static $obj=null;
private function __construct(){}
private function __clone(){}
public static function getInstance(){
if(!self::$obj instanceof self){
self::$obj=new self();
}
return self::$obj;
}
}
$obj1=Db::getInstance();
//单例工厂
final class fanctory{
private static $obj=null;
private function __construct(){}
private function __clone(){}
public static function getInstance($classname){
if(!isset(self::$obj[$classname])){
self::$obj[$classname]=new $classname();
}
return self::$obj[$classname];
}
}
$obj1=Db::getInstance();
// 100以内素数个数
$n='';
for($i=1;$i<=100;$i++){
$s=0;
for($j=1;$j<=$i;$j++){
if($i%$j==0){$s++;}
}
if($s==2){$n++;echo $i,'<br>';}
}
echo '<br>'.$n;
//踢猴子
function king($n,$m){
$arr=range(1,$n);
$i=0;
while(count($arr)>1){
$i+=1;
$tmp=array_shift($arr);
if($i%$m!=0){
array_push($arr,$tmp);
};
}
return $arr[0];
}
echo king(10,7);
// 冒泡排序
for($i=0;$i<count($arr)-1;$i++){
for($j=0;$j<count($arr)-1-$i;$j++){
if($arr[$j]>$arr[$j+1]){
$temp=$arr[$j];
$arr[$j]=$arr[$j+1];
$arr[$j+1]=$temp;
}
}
}
//用一个循环将一个数组里的不同元素个数统计出来;
$arr=[1,2,3,1,44,3];
$arr1='';
foreach($arr as $k=>$v){
if(!in_array($v,$arr1)){
$arr1[$v]=1;
}else{
$arr1[$v]=$arr1[$v]+1;
}
}
dump($arr1);
//防止表单重复提交
<input type="submit" onclick="this.disabled=true; this.value='登录中...'; this.form.submit();" value="登录" />
// 数据表去重
delete from qs_users where id not in (
select temp.id from (
select id from qs_users
group by username
) temp
)
//php调用同级其他类的方法
use Home\Controller\IndexController;
IndexController::logOut();
//tp中引入其他类时 实例化需要加反斜杠
$index=new \IndexController();
如果调用server层直接用D('index','service');
// 子查询先组队在排序(多对多表之间的连接,必须连接的student_id是不重复的)
SELECT
*
FROM
(
SELECT
e.*, a.startdateime AS startTime,
a.enddatetime AS endTime,
a.examarrangement_id
FROM
examinationstudent AS e
LEFT JOIN examarrangement AS a ON a.student_id = e.student_id
AND a.examination_id = '15c092ca83e711e78611e03f499fe56f'
WHERE
e.examination_id = '15c092ca83e711e78611e03f499fe56f'
) AS t
GROUP BY
t.student_id
ORDER BY
t.startTime
//where or where 连用时 要将or where 用括号括起来 组成一个条件
where (a=1 or b=2) and c=3;
//php 实现多线程 同时执行两个程序 (既前面的程序不用执行完 后面的程序 同时进行)
// 可以通过ajax 来实现多线程 (async: false 同步执行 在success没有返回值之前是不执行后面的程序的)
// async: true 异步执行 不待success返回值 同时执行后面的程序
// $.ajax 的global属性
// 生成中文乱码 iconve(); 可以将中文转码
行列转换
select username,
sum(if(cource='chinese',score,0)) as chinese,
sum(if(cource='math',score,0)) as math,
sum(if(cource='english',score,0)) as english from grade group by username;
-简单Case函数
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
巧妙地sql语句可以省去一次循环
SELECT firm_id,firm_name,phonenumber,createtime,
case
when firm_type=0 then '制造商'
when firm_type=1 then '供应商'
when firm_type=2 then '供应商&制造商'
END firm_type,
(SELECT COUNT(*) from device WHERE (firm.firm_id = device.supplier_id or firm.firm_id = device.manufacturer_id) )
+ (SELECT COALESCE(SUM(total),0) from consumable WHERE (firm.firm_id = consumable.supplier_id or firm.firm_id = consumable.manufacturer_id))
+ (SELECT COALESCE(SUM(total_number),0) from instrument WHERE (firm.firm_id = instrument.supplier or firm.firm_id = instrument.manufacturer))
as consumable_count
FROM firm;
mysql 函数:COALESCE(SUM(total),0) 返回参数第一个不为null的值;
//一次循环将数据相同的组合到一起 并将这个组合取名字 并算出每个组合的个数
foreach($list as $k=>$v){
$result[$v['typebasic_id']]['sub'][] = $v;
$result[$v['typebasic_id']]['name'] =$v['typebasic_name'];
$result[$v['typebasic_id']]['num'] =count($result[$v['typebasic_id']]['sub']);
}
可以将数据组合成一个字符串;
select GROUP_CONCAT(typebasic_name) as a,count(typebasic_name) from typebasic where typebasic_id='000FD8D27D04D1F30B846D969B454DCD';
select GROUP_CONCAT(typebasic_name,',',is_delete,',','$') as a,count(typebasic_name) from typebasic 将字符串设置一个标志位$;
//将time类型转化为秒数
//将秒数转化为time类型
mysql> select time_to_sec(120) from dual; 00:02:00
mysql> select sec_to_time(‘00:02:01’) from dual; 121
行列转换 加 两表联合起来(union all)
SELECT cname AS "姓名",
SUM(IF(cource="语文",score,0)) AS "语文",
SUM(IF(cource="数学",score,0)) AS "数学",
SUM(IF(cource="物理",score,0)) AS "物理",
SUM(score) AS "总成绩",
ROUND(AVG(score),2) AS "平均成绩"
FROM tb
GROUP BY cname
UNION ALL
SELECT
"总成绩平均数",
ROUND(AVG(`语文`),2) , ROUND(AVG(`数学`),2), ROUND(AVG(`物理`),2), ROUND(AVG(`总成绩`),2), ROUND(AVG(`平均成绩`),2)
FROM(
SELECT "all",cname AS "姓名",
SUM(IF(cource="语文",score,0)) AS "语文",
SUM(IF(cource="数学",score,0)) AS "数学",
SUM(IF(cource="物理",score,0)) AS "物理",
SUM(score) AS "总成绩",
AVG(score) AS "平均成绩"
FROM tb
GROUP BY cname
)tb2
GROUP BY tb2.all;
//注意 mkysql 中的 `` 这个符号 很重要; 在中文做作为字段是;
计算age与最新数据的间隔(启发:在计算用户充钱或者其他数据间隔的时候使用)
select *,(age-(select max(age) as age from test1 where a.name=name group by name)) from test1 as a where name in (select name from test1 group by name) ;
计算某组数据的前两条数据
select a.* from test1 a left join test1 b on a.name=b.name and a.age<b.age group by a.id,a.name,a.age having count(b.id)<2 order by a.name,a.age desc
阅读全文
0 0
- 随笔
- 随笔
- 随笔
- 随笔
- 随笔
- 随笔
- 随笔
- 随笔
- 随笔
- 随笔
- 随笔
- 随笔
- 随笔
- 随笔
- 随笔
- 随笔
- 随笔
- 随笔
- 递归法求最大公约数
- 7个无用的测试指标
- ora-12705:无法访问NLS数据文件,或者指定的环境无效
- 单机版软件DATETIME incompatible with the field type
- 深度学习中的激活函数
- 随笔
- Tomcat部署
- 注册码破解神器--OllyDbg
- 如何利用客户端缓存对网站进行优化? If-Modified-Since & If-None-Match
- Java 数字的相关处理工具类
- No executable code found at line。。。
- 每次遇到浏览器主页被篡改的问题,就特别气愤加头疼
- Java 使用POI 导出 百万级别的数据量的 Excel
- 【编程】编程中的“元”