MySQL数据管理（一）

学习目标：

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

熟悉数据表的表类型

了解数据表的字符集

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

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

---

学习内容：

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

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

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

2.创建数据表

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

注意

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

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

3.列类型

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

列类型分为：

数值类型

字符串类型

日期和时间型数值类型

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

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

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

类型	说明	最大长度
CHAR[(M)]	固定长字符串，检索快但费空间， 0 <=  M  <=   255	M个字符
VARCHAR[(M)]	可变字符串   0 <=  M <= 65535	可变长度
TINYTEXT	微型文本串	0-255个字符
TEXT	文本串	0-65535个字符

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

类型	说明	取值范围
DATE	YYYY-MM-DD，日期格式	1000-01-01~ 9999-12-31
TIME	Hh:mm:ss ，时间格式	-838:59:59~838:59:59
DATETIME	YY-MM-DD hh:mm:ss	1000-01-01 00:00:00  至 9999-12-31 23:59:59
TIMESTAMP	YYYYMMDDhhmmss格式表示的时间戳	197010101000000 ~2038年的某个时刻
TEAR	YYYY格式的年份值	1901~2155

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

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

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

)  [  表类型 ] [ 表字符集 ] [注释] ;

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

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

语法

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

)  [  表类型 ] [ 表字符集 ] [注释] ;

示例

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%'

名称	MyISAM	InnoDB
事务处理	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大,约2倍

适用场合：  

使用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.修改表

(1)修改表名

ALTER TABLE 旧表名 RENAKE AS 新表名 

(2)添加字段

ALTER TABLE 表名 ADD 字段名 列类型 [属性]

(3)修改字段

ALTER TABLE 表名 MODIFY 字段名 列类型 [属性] #修改列的数据类型

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列类型[属性]#修改列的数据类型及名称

(4)删除字段

ALTER TABLE 表名 DROP 字段名