数据库操作

1.Mysql查询表结构的sql介绍

SELECT
  COLUMN_NAME 列名,
  COLUMN_TYPE 数据类型,
  DATA_TYPE 字段类型,
  CHARACTER_MAXIMUM_LENGTH 长度,
  IS_NULLABLE 是否为空,
  COLUMN_DEFAULT 默认值,
  COLUMN_COMMENT 备注  
FROM
 INFORMATION_SCHEMA.COLUMNS
where
table_name  = '表名'

SELECT
    t.TABLE_NAME,
    c.COLUMN_NAME
FROM
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c,
    information_schema. TABLES AS ts
WHERE
    t.TABLE_NAME = c.TABLE_NAME
AND t.TABLE_SCHEMA = '库名'
AND t.CONSTRAINT_TYPE = 'PRIMARY KEY';

SELECT
    k.column_name
FROM
    information_schema.table_constraints t
JOIN information_schema.key_column_usage k USING (
    constraint_name,
    table_schema,
    table_name
)
WHERE
    t.constraint_type = 'PRIMARY KEY'
AND t.table_schema = '库名'
AND t.table_name = '表名'

/**
     * editTables
     *
     * 修改或查看数据库的datetime或者date类型
     *
     * @param string $type 【select 就会返回sql语句， query 就会直接修改数据库】
     */
    function editTables($type = 'select')
    {
        $datetime = array();

        $ifFunction = function($type = 'select', $str = '', &$datetime = array())
        {
            if($type == 'query')
            {
                if( ! $this ->db ->query($str))
                {
                    echo $str.' Failed to update !<br />';
                }
            }
            else
            {
                if(! in_array($str, $datetime))
                {
                    $datetime[] = $str;
                }
            }
        };

        foreach($this ->db ->query('show tables') ->result_array() as $key => $val)
        {
          foreach($this ->db ->query('SELECT COLUMN_NAME, DATA_TYPE,COLUMN_COMMENT FROM information_schema. COLUMNS WHERE table_name = \''.$val['Tables_in_'.$this ->db ->database].'\'') ->result_array() as $k => $v)
          {

              if(trim($v['DATA_TYPE']) === 'datetime')
              {
                  $str = 'alter table '.$val['Tables_in_'.$this ->db ->database].' modify '.$v['COLUMN_NAME'].' '.$v['DATA_TYPE'].' NOT NULL DEFAULT \'0000-00-00 00:00:00\' COMMENT \''.trim($v['COLUMN_COMMENT']).'\';';

                  $ifFunction($type, $str, $datetime);
              }
              elseif(trim($v['DATA_TYPE']) === 'date')
              {
                  $str = 'alter table '.$val['Tables_in_'.$this ->db ->database].' modify '.$v['COLUMN_NAME'].' '.$v['DATA_TYPE'].' NOT NULL DEFAULT \'0000-00-00\' COMMENT \''.trim($v['COLUMN_COMMENT']).'\';';

                  $ifFunction($type, $str, $datetime);
              }
          }
        }

        if($type == 'select')
        {
            echo (implode('<br>', $datetime));
        }
    }

例2.修改或查看数据库的不允许为空但是无默认值的字段

/**
     * fieldCheck
     *
     * 修改或查看数据库的不允许为空但是无默认值的字段
     *
     * @param string $type 【select 就会返回sql语句， query 就会直接修改数据库】
     */
    function fieldCheck($type = 'select')
    {
        $datetime = array();

        $ifFunction = function($type = 'select', $str = '', &$datetime = array())
        {
            if($type == 'query')
            {
                if( ! $this ->db ->query($str))
                {
                    echo $str.' Failed to update !<br />';
                }
            }
            else
            {
                if(! in_array($str, $datetime))
                {
                    $datetime[] = $str;
                }
            }
        };
        //查找主键
        $key =  $this->db->query("SELECT t.TABLE_NAME, c.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t, INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c, information_schema. TABLES AS ts WHERE t.TABLE_NAME = c.TABLE_NAME AND t.TABLE_SCHEMA = 'zfxm_gzgz_nianbao' AND t.CONSTRAINT_TYPE = 'PRIMARY KEY';")->result_array();
        // var_dump($key);exit;
        //查找不允许为空，并且没有默认值的子段
        foreach($this ->db ->query('show tables') ->result_array() as $key => $val)
        {
            $structureInfo =$this ->db ->query('SELECT COLUMN_NAME, COLUMN_TYPE,DATA_TYPE,IS_NULLABLE,COLUMN_DEFAULT,COLUMN_COMMENT FROM information_schema. COLUMNS WHERE table_name = \''.$val['Tables_in_'.$this ->db ->database].'\'') ->result_array();
            //查找主键
            $key = $this->db->query("SELECT k.column_name FROM information_schema.table_constraints t JOIN information_schema.key_column_usage k USING ( constraint_name, table_schema, table_name ) WHERE t.constraint_type = 'PRIMARY KEY' AND t.table_schema = 'tablename' AND t.table_name = '".$val['Tables_in_'.$this ->db ->database]."'")->row_array();
           $priKey = $key['column_name'];
                  foreach( $structureInfo as $k => $v)
                  {
                    //排除主键
                   if($priKey!=$v['COLUMN_NAME']){
                      if(trim($v['IS_NULLABLE']) === 'NO' && $v['COLUMN_DEFAULT']===NULL)
                      {
                        if($v['DATA_TYPE'] === 'varchar' ||$v['DATA_TYPE'] === 'char'){
                            $str = 'alter table `'.$val['Tables_in_'.$this ->db ->database].'` modify COLUMN `'.$v['COLUMN_NAME'].'` '.$v['COLUMN_TYPE'].'  NOT NULL DEFAULT \'\' COMMENT \''.trim($v['COLUMN_COMMENT']).'\';';
                        }elseif($v['DATA_TYPE'] === 'int' ||$v['DATA_TYPE'] === 'tinyint'){
                            $str = 'alter table `'.$val['Tables_in_'.$this ->db ->database].'` modify COLUMN `'.$v['COLUMN_NAME'].'` '.$v['COLUMN_TYPE'].'  NOT NULL DEFAULT 0 COMMENT \''.trim($v['COLUMN_COMMENT']).'\';';
                        }elseif($v['DATA_TYPE'] === 'text'){
                            $str = 'alter table `'.$val['Tables_in_'.$this ->db ->database].'` modify COLUMN `'.$v['COLUMN_NAME'].'` '.$v['COLUMN_TYPE'].' NULL COMMENT \''.trim($v['COLUMN_COMMENT']).'\';';
                        }
                          $ifFunction($type, $str, $datetime);
                      }

                }
                  }
            }
        if($type == 'select')
        {
            echo (implode('<br>', $datetime));
        }
            echo '<hr>';
    }