连接与查询

 +---------------------------------------------------------------------------------
 *    mysql_connect(); //函数打开非持久的 MySQL 连接。
 +---------------------------------------------------------------------------------
mysql_connect(); 
$con = mysql_connect($db['hostname'],$db['username'],$db['password']);
if (!$con){die('Could not connect: ' . mysql_error());}
else{echo '连接成功！';}



 +---------------------------------------------------------------------------------
 *    mysql_select_db(); //设置活动的 MySQL 数据库。
 +---------------------------------------------------------------------------------
$db_selected = mysql_select_db('btyou_masterdb_test', $con);



 +---------------------------------------------------------------------------------
 *    mysql_query(); //执行一条 MySQL 查询
 +---------------------------------------------------------------------------------
$sql = "SELECT * FROM tbl_statis_gamereport";
$result = mysql_query($sql,$con);
//var_dump(mysql_result($result,0,'TradeNo'));
var_dump($result); echo '<br/>';



 +---------------------------------------------------------------------------------
 *    mysql_fetch_assoc(); //从结果集中取得一行作为关联数组。
 +---------------------------------------------------------------------------------
 *	  mysql_fetch_array(); //从结果集中取得一行作为关联数组，或数字数组，或二者兼有。
 +---------------------------------------------------------------------------------
print_r(mysql_fetch_assoc($result));
print_r(mysql_fetch_assoc($result));
print_r(mysql_fetch_assoc($result));

#关闭连接
mysql_close($con);
/*END
+----------------------------------------------------------------------------------


 +---------------------------------------------------------------------------------
 *    现代样例    mysqli
 +---------------------------------------------------------------------------------
//require_once('config.php');
$link  = new mysqli(mysql_server,mysql_user,mysql_pass,mysql_dbname) or die( mysql_error() );
$link->query('set names utf8;');
$sql   = 'SELECT * FROM tbl_statis_gamereport';
$result = $query = $link->query($sql);
	while($row = $query->fetch_assoc()){
		$Infos[] = $row;
	}
	unset($query,$row,$sql);
var_dump($Infos);


//多表查询(三表 LEFT)
//本appID对应的礼包信息
$appArray = array();
$sql = "select b.LogoPic , a.* ,c.GiftID ,c.Code from (`tbl_gift_main` as a  LEFT JOIN `tbl_develop_app` as b ON a.GameID = b.AppID ) LEFT JOIN `tbl_gift_get_code` as c ON a.ID = c.GiftID where a.Status = 3 and a.ID in ($appIDstr)";
$query = $link->query($sql);
while($row = $query->fetch_assoc()){
	$appArray[$row['ID']] = array(
		'giftID' => $row['ID'],
		'logoUrl' => $row['LogoPic'],
		'giftName' => $row['GiftName'],   
		'content'=> $row['GiftDetail'],
		'useMethod' => $row['UseMethod'],
		'isGet' => '0',
		'giftCode'=> $row['Code'],
		'date'=> strtotime($row['EndTime'])*1000,
		'count'=> '',
		'allNum'=> ''
	);
}

登录

//登录数据库
cli  #mysql -u root -p 123456 


//多表查询 (内联查询 inner)
$sql = 'SELECT a.*,b.*,c.* FROM tb_demo3 AS c ,tb_demo2 AS b, tb_demo1 AS a WHERE a.id = b.id AND c.id = b.id';


union 对两个结果集进行并集操作，重复数据只显示一次
SELECT ...
UNION[ALL | DISTINCT]
SELECT ...
[UNION [ALL | DISTINCT]
SELECT ...]


//Truncate Table  重置一个表
mysql>TRUNCATE TABLE name
TRUNCATE `TABLE name`;
Truncate是一个能够快速清空资料表内所有资料的SQL语法。并且能针对具有自动递增值的字段，做计数重置归零重新计算的作用。


MariaDB [(none)]> show databases;  //展示所有的库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cactidb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)
use dbname;  //可以切换到你要访问的数据库。

MariaDB [test]> show tables; //查看当前库下的所有表
+----------------+
| Tables_in_test |
+----------------+
| employees      |
| t2             |
| t3             |
+----------------+

创建一个储存过程, 入参

//SELECT * FROM treenodes WHERE FIND_IN_SET(id, getChildList('3,5'));
//
CREATE DEFINER=`admin`@`%` FUNCTION `getChildList`(
`rootId` VARCHAR(100)
)
RETURNS varchar(1000) CHARSET utf8
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '查找所有子元素 //SELECT * FROM treenodes WHERE FIND_IN_SET(id, getChildList("3,5")); '
BEGIN
    DECLARE temp_ids varchar(1000);
    DECLARE temp_id varchar(1000);
    SET temp_ids = '$';
    SET temp_id =CAST(rootId as char);#转换的类型

    WHILE temp_id is not null DO
	SET temp_ids = concat(temp_ids,',',temp_id);  #函数用于将多个字符串连接成一个字符串
       SELECT group_concat(id) INTO temp_id FROM treenodes
       WHERE FIND_IN_SET(pid,temp_id)>0;
    END WHILE;
    RETURN temp_ids;
END