mysql

关于mysql的应用

group by 应用

select uid  ,sum(uid) as uidsum from table_name group by uid

sql脚本

create database test1;
use test1;
create table tp_test
(
       id int(11) not null auto_increment,
       name varchar(12) not null ,
       nickname varchar(255) not null,
       PRIMARY KEY (id)
);
insert into tp_test
(name,nickname)
values
('hahuduhf','hsuhdu');

ps:如果设置了主键，如果主键从在，则报错，不存在则创建。不设置则在当前最大值基础上怎加

保存数据库配置信息

   $str = '<?php   return ' . var_export($webconfig, true) . ';';
    if (!file_put_contents('../rt/database.php', $str)) {
        msg_box('配置信息保存失败，请重试！');
    }
//创建数据库
    $mysqlmode = function_exists("mysql_connect") ? 'mysql' : 'mysqli';
    $link = ($mysqlmode == 'mysql') ? @mysql_connect($webconfig['DB_HOST'], $webconfig['DB_USER'], $webconfig['DB_PWD']) : new mysqli($webconfig['DB_HOST'], $webconfig['DB_USER'], $webconfig['DB_PWD']);
    if (!$link) {
        $errno = ($mysqlmode == 'mysql') ? mysql_errno($link) : $link->errno;
        $error = ($mysqlmode == 'mysql') ? mysql_error($link) : $link->error;
        if ($errno == 1045) {
            msg_box('database_errno_1045', $error, 0);
        } elseif ($errno == 2003) {
            msg_box('database_errno_2003', $error, 0);
        } else {
            msg_box('database_connect_error', $error, 0);
        }
    }

    $dbname = $webconfig['DB_NAME'];
    $mysql_version = ($mysqlmode == 'mysql') ? mysql_get_server_info() : $link->server_info;
    if ($mysql_version > '4.1') {
        if ($mysqlmode == 'mysql') {
            mysql_query("CREATE DATABASE IF NOT EXISTS `$dbname` DEFAULT CHARACTER SET " . DBCHARSET, $link);
        } else {
            $link->query("CREATE DATABASE IF NOT EXISTS `$dbname` DEFAULT CHARACTER SET " . DBCHARSET);
        }
    } else {
        if ($mysqlmode == 'mysql') {
            mysql_query("CREATE DATABASE IF NOT EXISTS `$dbname`", $link);
        } else {
            $link->query("CREATE DATABASE IF NOT EXISTS `$dbname`");
        }
    }

    if (($mysqlmode == 'mysql') ? mysql_errno($link) : $link->errno) {
        msg_box('database_errno_1044', ($mysqlmode == 'mysql') ? mysql_error($link) : $link->error, 0);
    }
    if ($mysqlmode == 'mysql') {
        mysql_close($link);
    } else {
        $link->close();
    }
    //数据库创建完毕

创建数据表

$db = new dbstuff();
    $db->connect($webconfig['DB_HOST'], $webconfig['DB_USER'], $webconfig['DB_PWD'], $webconfig['DB_NAME'], DBCHARSET);
    $tablepre = $webconfig['DB_PREFIX'];

    $file_dir = dirname(__FILE__);
    $file_name = "./data/sql.sql";

    $sql = file_get_contents($file_name, $file_dir);
    $sql = str_replace("\r\n", "\n", $sql);

    show_header();
    show_install();
    show_footer();

    runquery($sql);

    $file_name = "./data/area.sql";
    $sql = file_get_contents($file_name, $file_dir);
    $sql = str_replace("\r\n", "\n", $sql);
    runquery($sql);
    
    $db->query("INSERT INTO {$tablepre}admin (username, password,status, create_time) VALUES ('{$admin}', '{$password}',1, '".time()."')");
    $db->close();
    
    $url = "./success.html";
    echo "<script language='javascript' type='text/javascript'>";
    echo "window.location.href='$url'";
    echo "</script>";

创建数据表结束

sql文件格式

-- ----------------------------
-- Table structure for jrb_auth_item
-- ----------------------------
DROP TABLE IF EXISTS `jrb_auth_item`;
CREATE TABLE `jrb_auth_item` (
  `id` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
  `auth_name` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '验证名称 如房产证',
  `score` smallint(6) DEFAULT NULL COMMENT '信用分数',
  `status` tinyint(2) unsigned DEFAULT '1' COMMENT '是否启用该验证 1-不启用  2-启用',
  `must` tinyint(2) unsigned DEFAULT '1' COMMENT '是否必须认证 1-否  2-是',
  `type` tinyint(2) unsigned DEFAULT '1' COMMENT '验证类型  1-个人认证信息 -- 2-机构认证信息',
  `create_time` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='验证类型表';

-- ----------------------------
-- Records of jrb_auth_item
-- ----------------------------
INSERT INTO `jrb_auth_item` VALUES ('1', '身份认证', '10', '2', '2', '1', '1546987987'),
('2', '工作认证', '10', '2', '2', '1', '1386814799'),
('3', '银行信用报告认证', '10', '2', '2', '1', null),
('4', '收入认证', '10', '2', '1', '1', null),
('5', '房产认证', '10', '2', '1', '1', null),
('6', '技术职称认证', '10', '2', '1', '1', null),
('7', '购车证明', '10', '2', '1', '1', null),
('8', '结婚认证', '10', '2', '1', '1', null),
('9', '居住地证明', '10', '2', '1', '1', null),
('10', '视频认证', '10', '2', '1', '1', null),
('50', '其他认证', '0', '2', '1', '1', '1394489523');