MySQL数据字典

随堂练习

先学习SQL

SELECT TABLE_SCHEMA,TABLE_NAME FROM information_schema.`TABLES` WHERE table_schema = 'xiaomi'

获取到一个MySQL服务器里的 所有库和表

SELECT * FROM information_schema.`COLUMNS` WHERE table_name='member' AND table_schema = 'xiaomi'

获取某一个库里某一张表的字段信息 比如：字段名 数据类型 是否主键 是否自增 字段描述 等等

样式参考

<!doctype html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Document</title>
    <style>
        body,td,th {font-family:"宋体"; font-size:12px;}
        table,h1,p{width:960px;margin:0px auto;}
        table{border-collapse:collapse;border:1px solid #CCC;background:#efefef;}
        table caption{text-align:left; background-color:#fff; line-height:2em; font-size:14px; font-weight:bold; }
        table th{text-align:left; font-weight:bold;height:26px; line-height:26px; font-size:12px; border:1px solid #CCC;padding-left:5px;}
        table td{height:20px; font-size:12px; border:1px solid #CCC;background-color:#fff;padding-left:5px;}
        .c1{ width: 150px;}
        .c2{ width: 150px;}
        .c3{ width: 80px;}
        .c4{ width: 100px;}
        .c5{ width: 100px;}
        .c6{ width: 300px;}
    </style>
</head>
<body>
    <h1 style="text-align:center;">数据字典</h1>
    <p style="text-align:center;margin:20px auto;">生成时间：<?=date('Y-m-d H:i:s');?></p>
    <?php
        # 数据表的个数
        $sql = "SELECT count(*) as num FROM information_schema.`TABLES` WHERE  table_schema = '".DB_NAME."'";
        $result = mysqli_query($link,$sql);
        $r = mysqli_fetch_assoc($result);

        #数据表字段
        $sql2 = "SELECT * FROM information_schema.`TABLES` WHERE table_schema = '".DB_NAME."'";
        $result = mysqli_query($link,$sql2);
        while(($row = mysqli_fetch_assoc($result)) != false){
            echo '<table border="1">';
            echo "<caption>表名:{$row['TABLE_NAME']}</caption>";
            echo '<tr>
                    <th>字段名</th>
                    <th>数据类型</th>
                    <th>默认值</th>
                    <th>允许非空</th>
                    <th>自动递增</th>
                    <th>主键</th>
                    <th>备注</th>
                </tr>';
            $sql = "SELECT * FROM information_schema.`COLUMNS` WHERE table_name='{$row['TABLE_NAME']}' and table_schema = '".DB_NAME."';";
            $_result = mysqli_query($link,$sql);
            while(($rows = mysqli_fetch_assoc($_result)) != false){
                echo '<tr>';
                echo "<td>{$rows['COLUMN_NAME']}</td>";
                echo "<td>{$rows['COLUMN_TYPE']}</td>";
                echo "<td>{$rows['COLUMN_DEFAULT']}</td>";
                echo "<td>{$rows['IS_NULLABLE']}</td>";
                echo "<td>{$rows['EXTRA']}</td>";
                echo "<td>{$rows['COLUMN_KEY']}</td>";
                echo "<td>{$rows['COLUMN_COMMENT']}</td>";
                echo '</tr>';
            }
            /*  */
            echo '</table>';
        }

    ?>
</body>
</html>