MySQL数据管理（一）

￡神魔★判官ぃ 2024-04-29 11:06 51阅读 0赞

## 学习目标： ##

`熟练使用SQL语句创建数据表`

`熟悉数据表的表类型`

`了解数据表的字符集`

`熟练使用SQL语句修改和删除表`

`使用SQLyog图形化管理工具操作表`

--------------------

## 学习内容： ##

## 1.回顾关系型数据库管理系统 ##

关系型数据库管理系统（简称为RDBMS），是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据，以便于用户理解，关系型数据库这一系列的行和列被称为表，一组表组成了数据库。

关系模型可以简单理解为二维表格模型，而一个关系型数据库就是由二维表及其之间的关系组成的一个数据组织。

## 2.创建数据表 ##

CREATE TABLE IF NOT EXISTS `表名` (
    `字段名1` 列类型 [字段属性] [约束] [索引] [注释],
    `字段名2` 列类型 [字段属性] [约束] [索引] [注释],
    ......
    `字段名n` 列类型 [字段属性] [约束] [索引] [注释],
    )[表类型] [表字符集] [注释];

### 注意 ###

1：反引号用于区别MySQL保留字（全部大写）与普通字符而引入的。

2：多个字段（也称为列）之间使用逗号分隔，最后一个字段后无须逗号。

## 3.列类型 ##

列类型：规定数据库中该列存放的数据类型

列类型分为：

数值类型

字符串类型

日期和时间型数值类型

### 列类型分类3-1——数值类型 ###

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>&nbsp;类型</td> 
   <td>说明</td> 
   <td>取值范围</td> 
   <td>存储需求</td> 
  </tr> 
  <tr> 
   <td>TINYINT[(M)]</td> 
   <td>非常小的数据</td> 
   <td> <p>有符值： -27 ~ 27-1 &nbsp;</p> <p>无符号值：0 ~ 28-1 &nbsp;</p> </td> 
   <td>1字节</td> 
  </tr> 
  <tr> 
   <td>SMALLINT[(M)]</td> 
   <td>较小的数据</td> 
   <td>有符值： &nbsp;-215 ~ 215-1 &nbsp; 无符号值： &nbsp;0 ~ 216-1 &nbsp;&nbsp;</td> 
   <td>2字节</td> 
  </tr> 
  <tr> 
   <td>MEDIUMINT[(M)]</td> 
   <td>中等大小的整数</td> 
   <td>有符值： &nbsp;-223 ~ 223-1 &nbsp; 无符号值： &nbsp;0 ~ 224-1 &nbsp;&nbsp;</td> 
   <td>3字节</td> 
  </tr> 
  <tr> 
   <td>INT[(M)]</td> 
   <td>标准整数</td> 
   <td>有符值： -231 ~ 231-1 &nbsp; 无符号值：0 ~ 232-1 &nbsp;</td> 
   <td>4字节</td> 
  </tr> 
  <tr> 
   <td>BIGINT[(M)]</td> 
   <td>较大的整数</td> 
   <td>有符值： -263 ~263-1 无符号值：0 ~264-1&nbsp;</td> 
   <td>8字节</td> 
  </tr> 
  <tr> 
   <td>FLOAT[(M)]</td> 
   <td>单精度浮点数</td> 
   <td>±1.1754351e -38</td> 
   <td>4字节</td> 
  </tr> 
  <tr> 
   <td>DOUBLE[(M)]</td> 
   <td>双精度浮点数</td> 
   <td>±2.2250738585072014e -308</td> 
   <td>8字节</td> 
  </tr> 
  <tr> 
   <td>DECIMAL</td> 
   <td>字符串形式的浮点数</td> 
   <td>decimal(m, d)</td> 
   <td>m个字节</td> 
  </tr> 
 </tbody> 
</table>

### 列类型分类3-2——字符串类型 ###

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>&nbsp;类型</td> 
   <td>说明</td> 
   <td>最大长度</td> 
  </tr> 
  <tr> 
   <td>CHAR[(M)]</td> 
   <td>固定长字符串，检索快但费空间， 0 &lt;= &nbsp;M &nbsp;&lt;= &nbsp; 255</td> 
   <td>M个字符</td> 
  </tr> 
  <tr> 
   <td>VARCHAR[(M)]</td> 
   <td>可变字符串 &nbsp; 0 &lt;= &nbsp;M &lt;= 65535</td> 
   <td>可变长度</td> 
  </tr> 
  <tr> 
   <td>TINYTEXT</td> 
   <td>微型文本串</td> 
   <td>0-255个字符</td> 
  </tr> 
  <tr> 
   <td>TEXT</td> 
   <td>文本串</td> 
   <td>0-65535个字符</td> 
  </tr> 
 </tbody> 
</table>

### 列类型分类3-3——日期和时间类型 ###

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>类型</td> 
   <td>说明</td> 
   <td>取值范围</td> 
  </tr> 
  <tr> 
   <td>DATE</td> 
   <td>YYYY-MM-DD，日期格式</td> 
   <td>1000-01-01~ 9999-12-31</td> 
  </tr> 
  <tr> 
   <td>TIME</td> 
   <td>Hh:mm:ss ，时间格式</td> 
   <td>-838:59:59~838:59:59</td> 
  </tr> 
  <tr> 
   <td>DATETIME</td> 
   <td>YY-MM-DD hh:mm:ss&nbsp;</td> 
   <td>1000-01-01 00:00:00 &nbsp;至 9999-12-31 23:59:59</td> 
  </tr> 
  <tr> 
   <td>TIMESTAMP</td> 
   <td>YYYYMMDDhhmmss格式表示的时间戳</td> 
   <td>197010101000000 ~2038年的某个时刻</td> 
  </tr> 
  <tr> 
   <td>TEAR</td> 
   <td>YYYY格式的年份值</td> 
   <td>1901~2155</td> 
  </tr> 
 </tbody> 
</table>

MySQL允许“不严格”语法：任何标点符号都可以用作日期部分之间的间隔符，如”16-06-16” ”16.06.16” ”16/06/16”都可以写入数据表

### 3.1创建数据表——字段属性 ###

CREATE   TABLE [ IF NOT EXISTS ]    `表名`   (
        
            `字段名1`   列类型 [ 字段属性 | 约束]  [ 索引 ] [注释] ,
            `字段名2`   列类型 [ 字段属性 | 约束]  [ 索引 ] [注释] , 
             … …    
             `字段名n`   列类型 [字段属性 | 约束]  [ 索引 ] [注释] 
    
    )  [  表类型 ] [ 表字符集 ] [注释] ;

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>&nbsp;字段属性</td> 
   <td>说明</td> 
  </tr> 
  <tr> 
   <td>UNSIGNED</td> 
   <td>无符号的，声明该数据列不允许负数</td> 
  </tr> 
  <tr> 
   <td>ZEROFILL</td> 
   <td> <p>1.0填充的，不足位数的用0来填充，如int（3），5则为005</p> <p>2.使用zerofill会默认加unsigned</p> </td> 
  </tr> 
  <tr> 
   <td>AUTO_INCREMENT</td> 
   <td> <p>1.自动增长的，每添加一条数据，自动在上一个记录数上加1；</p> <p>2.通常用于设置主键，且为整数类型</p> <p>3.可定义起始值和步长</p> </td> 
  </tr> 
  <tr> 
   <td>NULL和NOT NULL</td> 
   <td> <p>1.默认为NULL，即没有插入该列的数值</p> <p>2.如果设置为NOT NULL,则该列必须有值</p> </td> 
  </tr> 
  <tr> 
   <td>DEFAULT</td> 
   <td> <p>1.默认的 用于设置默认值</p> <p>2.例如 性别字段，默认为“男”，否则为“女” 若无指定该表的值，则默认为“男”的值</p> </td> 
  </tr> 
 </tbody> 
</table>

### 3.2创建数据表——字段注释 ###

语法

示例

CREATE TABLE IF NOT EXISTS `TEST`(
    `ID` INT(11) UNSIGNED COMMENT '编码号'
    )COMMENT='测试表';

## 4.查看表和查看表的定义 ##

创建表之后，如果需要查看一下该表是否存在，可以使用查看表的SQL命令。

SHOW TABLES;

创建表之后，如果需要查看一下该表的定义，可以使用如下的SQL命令。

DESCRIBE 表名或DESC表名

## 5.删除表 ##

如果不再需要student表，如何删除它 ？？？（危险动作，执行前先与DBA联系）

DROP TABLE IF EXISTS 表名

与创建数据库一样，如果当前数据库中已经存在student表，则再次创建时系统将提示出错。我们将创建学生表的语句改写成完整的删除并创建的语句。

DROP TABLE IF EXISTS `STUDENT`;
    CREATE TABLE `STUDENT`(......
    )

## 6.表类型 ##

### 表类型2-1 ###

MySQL属于关系型数据库管理系统，其他包括数据库以及用于数据库访问管理的接口系统。数据库负责存储数据，接口系统负责管理数据库。

由于不同用户对数据的容量、访问速度、数据安全性有不同的要求，为了满足不同用户的业务需求，MySQL数据库采用多种存储引擎进行数据存储。 表的存储引擎也称为表类型。

MySQL5.5支持的存储引擎有MyISAM、InnoDB 、HEAP、BOB、CSV等。

SHOW ENGINES；#查看系统所支持的存储引擎

MySQL中的常用的两种注释 单行注释：\#...... 多行注释：/\*……\*/

### 表类型2-2 ###

常用的存储引擎：MyISAM和InnoDB 。

MySQL5.5以上版本默认的存储引擎是InnoDB。

语法

SHOW VARIABLES LIKE 'STORAGE ENGINE%'

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>名称</td> 
   <td>MyISAM</td> 
   <td>InnoDB</td> 
  </tr> 
  <tr> 
   <td>事务处理</td> 
   <td>不支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>数据行锁定</td> 
   <td>不支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>外键约束</td> 
   <td>不支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>全文索引</td> 
   <td>支持</td> 
   <td>不支持</td> 
  </tr> 
  <tr> 
   <td>表空间大小</td> 
   <td>较小</td> 
   <td>较大,约2倍</td> 
  </tr> 
 </tbody> 
</table>

**适用场合：**

**使用MyISAM: 节约空间及相应速度，适合以访问为主的应用。 使用InnoDB: 安全性，事务处理及多用户操作数据表。**

### 设置表的表类型 ###

DREP TABLE IF EXISTS `STUDENT`;
    CREATE TABLE `STUDENT`(
    ......)ENGINE = MYISAM

DREP TABLE IF EXISTS `STUDENT`;
    CREATE TABLE `STUDENT`(
    ......)ENGINE = InnoDB

## 7.如何设置表字符集 ##

可为数据库、数据表、数据列设定不同的字符集。

方法1：创建时通过命令来设置。例如：

CREATE TABLE 表名(
    #省略一些代码
    )CHARSET = UTF8;

方法2：修改配置文件my.ini（Windows系统）中的参数。例如：

character-set-sever = utf8

8.修改表