Mysql 一些实用方法笔记！

sql 注入：

#正常查询sql
$sql = "SELECT * FROM USER WHERE username = 'admin' and password = 'e10adc3949ba59abbe56e057f20f883e' ";


#只要在username字段输入”admin’;–”，这样就会被黑到，相应的SQL语句如下： 
$sql = "SELECT * FROM USER WHERE username='admin';-- and password = 'e10adc3949ba59abbe56e057f20f883e'";


#万能密码：xx' or 1='1   ;(只需知道用户名，or 后面 sql 不执行) 
$sql = "SELECT * FROM USER WHERE username = 'admin' OR 1='1' AND PASSWORD = 'e10adc3949ba59abbe56e057f20f883e' ";


#万能用户：xx’union select * from users;   ;  (不需要知道用户名，只需知道表名）
$sql = "SELECT * FROM USER WHERE username = 'xx' UNION SELECT * FROM USER  WHERE 1='1' and password = 'admin' "; 

sql 导入数据：

$sql='select id,title INTO `tmall`(`id`,`title`) from tj';//批量插入数据
$sql='INSERT INTO `tmall`(`id`,`title`) select id,title from tj' ; //批量插入数据

批量插入数据：

//result 为二维数组
foreach($result as $k=>$v){
    $data[]="(".$v['id'].",'".$v['title']."')";      
}
$sql = "insert into db_test (id,title) values ".implode(",",$data);

sql 关联更新多表数据：

$sql = "update a left join b on a.id=b.id set a.name='aName' ,b.name='bName' where a.id=1";
//根据 where 条件，更新a表，b表数据

$sql = "UPDATE `qpl_collocation` c left join qpl_user u on c.uid=u.id SET c.title='ddd111', u.qid=11 WHERE u.id=39”;

sql 关联删除多表数据：

$sql = "DELETE a.*,b.* FROM a,b WHERE a.id = 1 AND b.id =2";//根据 where 条件，删除a表，b表数据

#根据问卷ID关联删除问卷表,问卷题目表,问卷答案表
$sql = "DELETE p.*,s.*,a.* from db_paper p  
LEFT JOIN db_paper_subject s ON p.id=s.paper_id 
LEFT JOIN db_paper_answer a ON s.id=a.subject_id WHERE p.id=1 ";

mysql where in() 使用：

//整形拆分用 ，
$id_arr = array(1,2,3,4,5,6);//id数组
$id_str = implode(',',$id_arr);//数组拆分
 $sql = "select * from table where id in(".$id_str.")";
//打印结果：select * from table where id in(1,2,3,4,5,6)

//字符串拆分用  ‘“，”’
$str_arr = array('lucy','lily','jim','lilei','hanmeimei');//字符串数组
$str_str = implode('","',$str_arr);//数组拆分
 $sql = "select * from table where id in(".'"'.$str_str.'"'.")";
//打印结果：select * from table where id in("lucy","lily","jim","lilei","hanmeimei")

查看创建数据表创建的sql：

$sql = 'SHOW CREATE TABLE `db_article`';
#返回两个字段：
#TABLE （表名）     
#CREATE TABLE(创建数据表的 sql 语句)

mysql COUNT() 函数：

$count 为 * 查询最慢；
可以设为主键ID ；
没有主键ID，可以用0,1（可以填±整数，±小数等试试，能走通？为什么？具体谷歌百度。。。）
 SELECT COUNT($count) FROM  tableName 

mysql FIELD() 自定义排序函数：

#order by field(字段名,字段值1，字段值2，.... ,字段值N);
 
#其中字段名后面的字段值自定义，不限制个数 
# 将获取出来的数据根据字段值1，字段值2，.... ,字段值N的顺序排序！
 $sql = "SELECT id,username,status  FROM `db_user`   ORDER BY FIELD(status,0,3,1,2,5)";

mysql CONCAT() 连接字符串函数：

#拼接username，email进行模糊查询
$sql  ="SELECT * FROM table WHERE (   CONCAT(username,email) like '%{$keyword}%'  )  " ;

mysql REGEXP() 正则匹配函数：

需求：给今天生日的会员发送祝福短信
sql解析：
1.取4位月份和日期：身份证如果是15位，从第九位取；如果是18位，从11位取。
2.REGEXP正则匹配手机号，筛选正确的手机号
3.REGEP正则不匹配身份证以字母开头（一般带字母的身份证都是最后一位是X）
4.REGEP正则匹配身份证位数，必须是15位或18位
5.REGEP正则匹配身份证生日是否当前日期
6.从用户表查询匹配然后发送短信。。。。。

date_default_timezone_set('Asia/Shanghai');//设置时区
$birthday = date('m').date('d'); //当前月份和日期，例： 1212 
 
$sql  ="SELECT mobile,username,email, 
IF(CHAR_LENGTH(id_card) = 15,SUBSTRING(id_card,9,4),SUBSTRING(id_card,11,4) ) birthday 
FROM USER WHERE   id_card not regexp '[a-z]+' 
AND  mobile REGEXP '^((13)|(15)|(18))([0-9]{9})$'  
AND id_card REGEXP '^(([0-9]{14}[x0-9]{1})|([0-9]{17}[x0-9]{1}))$'
AND id_card REGEXP '^(([0-9]{10}".$birthday."[x0-9]{4})|([0-9]{8}".$birthday."[x0-9]{3}))$'  " ;

mysql GROUP BY COUNT（） 和 DISTINCT区别：

一、未使用GROUP BY 和 DISTINCT：

#查询数量
SELECT  count(0)
FROM ipos_spkcb a LEFT JOIN com_base_kehu b ON a.zd_id = b.Id 
WHERE ( b.khdm = 120605 AND a.sl > 0 AND a.spdm LIKE '%13%' ) ;

#查询数据
SELECT   b.id 'B表ID',a.zd_id 'A表ID',b.khdm '客户代码',a.spdm '商品代码',a.sl '商品数量' 
FROM ipos_spkcb a LEFT JOIN com_base_kehu b ON a.zd_id = b.Id 
WHERE ( b.khdm = 120605 AND a.sl > 0 AND a.spdm LIKE '%13%' ) ;

Paste_Image.png Paste_Image.png

二、使用 GROUP BY 和 DISTINCT的区别：

#查询分组的数量

SELECT  count(0) c
FROM ipos_spkcb a LEFT JOIN com_base_kehu b ON a.zd_id = b.Id 
WHERE ( b.khdm = 120605 AND a.sl > 0 AND a.spdm LIKE '%13%' ) 
group by a.spdm;

#查询分组后的数据
SELECT  a.spdm ,a.zd_id
FROM ipos_spkcb a LEFT JOIN com_base_kehu b ON a.zd_id = b.Id 
WHERE ( b.khdm = 120605 AND a.sl > 0 AND a.spdm LIKE '%13%' ) 
group by a.spdm;

1.使用Group by 分组获取数据：count共15

Paste_Image.png

2.使用Group by 分组获取符合条件的数据

Paste_Image.png

#查询数量


SELECT  count(distinct a.spdm) c
FROM ipos_spkcb a LEFT JOIN com_base_kehu b ON a.zd_id = b.Id 
WHERE ( b.khdm = 120605 AND a.sl > 0 AND a.spdm LIKE '%13%' ) 
 

#查询数据
SELECT   distinct a.spdm,a.zd_id
FROM ipos_spkcb a LEFT JOIN com_base_kehu b ON a.zd_id = b.Id 
WHERE ( b.khdm = 120605 AND a.sl > 0 AND a.spdm LIKE '%13%' ) 
 

11.使用DISTINCT获取count数据15

Paste_Image.png

22.使用DISTINCT获取符合条件的数据

Paste_Image.png

mysql DATE_FORMAT函数：

当数据库存储的日期时间为date('Y-m-d H:i:s')格式时，使用date_format('create_time,'%Y')可以获取年。date_format('create_time,'%m')可以获取月。
date_format('create_time,'%d')可以获取日。
。。。。。。

Paste_Image.png