MySQL数据库从入门到精通—数据库和表的基本操作 ...

又双叒罬

上一文，我们已经学习了初识数据库（MySQL数据库从入门到精通—初识数据库—数据库入门、MySQL在Windows系统中的安装与配置）本文详细讲解MySQL支持的数据类型，对数据库的基本操作（例如创建、查看、修改和删除库）做了介绍，最后讲解了数据表的基本操作。


MySQL支持的数据类型
² 数值类型
MySQL支持所有标准SQL数据类型，其中包括严格数据类型（例如INTEGER、SMALLINT、DECIMAL和NUMBERIC）、近似数值数据类型（例如FLOAT、REAL和DOUBLE PRESISION）。  作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。MySQL中不同的数值类型所应的字节大小和取值范围是不同的，具体如表2.1所示。
表2.1 MySQL数值类型

数据类型	字节数	无符号数的取值范围	有符号数的取值范围
TINYINT	1	0~255	-128~127
SMALLINT	2	0~65535	-32768~32767
MEDIUMINT	3	0~16777215	-8388608~8388607
INT / INTEGER	4	0~4294967295	-2147483648~2147483647
BIGINT	8	0~18446744073709551615	-9223372036854775808~9223372036854775807
FLOAT	4	0和1.175494351E-38~3.402823466E+38	-3.402823644E+38~-1.175494351E-38
DOUBLE	8	0和2.2250738585072014E-308~1.7976931348623157E+308	-1.7976931348623157E+308~2.2250738585072014E-308
DECIMAL(M,D)	变长，整数部分和小数部分分开计算	0和2.2250738585072014E-308~1.7976931348623157E+308	-1.7976931348623157E+308~2.2250738585072014E-308

在表2.1中，占用字节最小的是TINYINT，占用字节数最多的是BIGINT，DECIMAL类型的取值范围与DOUBLE类型相同。
MySQL支持的5种主要整数类型是TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT。这些类型在很大程度上是相同的，只是它们存储值的大小不相同。
MySQL支持的3种浮点类型是FLOAT、DOUBLE和DECIMAL类型。其中，FLOAT数值类型用于表示单精度浮点数值，而DOUBLE数值类型用于表示双精度浮点数值。
² 字符串类型
MySQL提供了8种基本的字符串类型，分别为CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和SET类型，可以存储的范围从简单的一个字符到巨大的文本块或二进制字符串数据，常见的字符串类型所对应的字节大小和取值范围如表2.2所示。
表2.2 MySQL字符串类型

数据类型	字节数	类型描述
CHAR	0~255	定长字符串
VARCHAR	0~65535	可变长字符串
TINYBLOB	0~255	不超过 255 个字符的二进制字符串
TINYTEXT	0~255	短文本字符串
BLOB	0~65535	二进制形式的长文本数据
TEXT	0~65535	长文本数据
MEDIUMBLOB	0~16777215	二进制形式的中等长度文本数据
MEDIUMTEXT	0~16777215	中等长度文本数据
LOGNGBLOB	0~4294967295	二进制形式的极大文本数据
LONGTEXT	0~4294967295	极大文本数据
VARBINARY(M)	0~M	允许长度0~M个字节的变长字节字符集
BINARY(M)	0~M	允许长度0~M个字节的定长字节字符集

表2.2列出了常见的字符串类型，其中有些类型比较相似，接下来详细讲解其中一些容易混淆的类型。
1.CHAR和VARCHAR类型
CHAR 类型用于定长字符串，并且必须在圆括号内用一个大小修饰符来定义。这个大小修饰符的范围是0~255，比指定长度大的值将被截短，比指定长度小的值将会用空格作填补。
CHAR类型的一个变体是VARCHAR 类型。它是一种可变长度的字符串类型，并且也必须带有一个范围在0~65535之间的指示器。CHAR和VARCHGAR不同之处在于MySQL数据库处理这个指示器的方式，CHAR把这个大小视为值的大小，在长度不足的情况下就用空格补足，而VARCHAR类型把它视为最大值并且只使用存储字符串实际需要的长度（增加一个额外字节来存储字符串本身的长度）来存储值，所以短于指示器长度的VARCHAR类型不会被空格填补，但长于指示器的值仍然会被截短。
VARCHAR类型可以根据实际内容动态改变存储值的长度，因此在不能确定字段需要多少字符时使用VARCHAR类型可以大大地节约磁盘空间、提高存储效率。
2.TEXT和BLOB类型
对于字段长度超过255的情况下，MySQL提供了TEXT和BLOB两种类型。根据存储数据的大小，它们有不同的子类型。这些大型的数据用于存储文本块或图像、声音文件等二进制数据类型。
TEXT类型和BLOB类型的相同点具体如下。
（1）在TEXT或BLOB列的存储或检索过程中不存在大小写转换，当未运行在严格模式下时，如果为BLOB或TEXT列分配一个超过该列类型的最大长度值时，则值会被截取。如果截掉的字符不是空格，将会产生一条警告。
（2）BLOB和TEXT列都不能有默认值。
（3）在保存或检索BLOB和TEXT列的值时不删除尾部空格。
（4）对于BLOB和TEXT列的索引，必须指定索引前缀的长度。
TEXT类型和BLOB类型的不同点具体如下。
（1）TEXT值是大小写不敏感的，而BLOB是大小写敏感的。
（2）TEXT被视为非二进制字符串，而BLOB被视为二进制字符串。
（3）TEXT列有一个字符集，并且根据字符集的校对规则对值进行排序和比较，BLOB列没有字符集。
（5）可以将TEXT列视为VARCHAR列，在大多数情况下，可以将BLOB列视为足够大的VARBINARY列。
（6）BLOB可以储存图片，而TEXT不可以，TEXT只能储存纯文本文件。
² 日期和时间类型
在处理日期和时间类型的值时，MySQL带有不同的数据类型可供选择。它们可以被分成简单的日期和时间类型、混合的日期和时间类型。根据要求的精度，子类型在每个分类型中都可以使用，并且MySQL带有内置功能，可以将多样化的输入格式变为一个标准格式。日期和时间类型同样有对应的字节数和取值范围等，如表2.3所示。
表2.3 MySQL日期和时间类型

数据类型	字节数	取值范围	日期格式	零值
YEAR	1	1901~2155	YYYY	0000
DATE	4	1000-01-01~9999-12-3	YYYY-MM-DD	0000-00-00
TIME	3	-838:59:59~838:59:59	HH:MM:SS	00:00:00
DATETIME	8	1000-01-01 00:00:00~9999-12-31 23:59:59	YYYY-MM-DDHH:MM:SS	0000-00-00 00:00:00
TIMESTAMP	4	1970-01-01 00:00:01~2038-01-19 03:14:07	YYYY-MM-DDHH:MM:SS	0000-00-00 00:00:00

在表2.3中，每种日期和时间类型都有一个有效范围。如果插入的值超过这个范围，系统会报错，并将0值插入到数据库中，不同的日期和时间类型有不同的0值，在表2.3中已经详细列出。接下来详细讲解表2.3中的几种数据类型。
1.YEAR类型
YEAR类型使用1个字节来表示年份，在MySQL中以YYYY的形式来显示YEAR类型的值，为YEAR类型的字段赋值的表示方法如下。
（1）使用4位字符串和数字表示：其范围是1901~2155。输入格式为'YYYY'或YYYY。例如，输入‘2008’或者2008，可直接保存2008。如果超过了范围，就会插入0000。
（2）使用两位字符串表示：‘00’~‘69’转换为2000~2069，‘70’~‘99’转换为1970~1999。例如输入‘35’，YEAR值会转换成2035，输入‘90’，YEAR值会转换成1990。
（3）使用两位数字表示：1~69转换为2001~2069，70~99转换为1970~1999。
另外，在对YEAR类型的字段进行相关操作的时候，最好使用四位字符串或者数字表示，不要使用两位的字符串和数字。
有时可能会插入0或者‘0’，此处要严格区分0和‘0’。如果向YEAR类型的字段插入0，存入该字段的年份是0000；如果向YEAR类型的字段插入‘0’，存入的年份是2000。
2.TIME类型
TIME类型使用3个字节来表示时间。MySQL中以HH:MM:SS的形式显示TIME类型的值，其中，HH表示时（取值范围为0~23），MM表示分（取值范围为0~59），SS表示秒（取值范围是0~59）。
TIME类型的范围可以从‘-838：59：59’~‘838：59：59’。虽然小时的范围是0~23，但是为了表示某种特殊需要的时间间隔，将TIME类型的范围扩大了，而且还支持了负值。TIME类型的字段赋值表示方法如下。
（1）表示‘D HH:MM:SS’格式的字符串。其中，D表示天数（取值范围是0~34）。在保存时，小时的值等于D×24+HH。例如，输入‘2 11：30：50’，TIME类型会转换为59：30：50。当然，输入时可以不严格按照这个格式，可以是‘HH:MM:SS’、'HH:MM'、'D HH:MM'、'D HH'、'SS'等形式。例如，输入‘30’，TIME类型会自动转换为00：00：30。
（2）表示‘HHMMSS’格式的字符串或HHMMSS格式的数值，例如输入‘123456’，TIME类型会转换成12：34：56。如果输入0或者‘0’，那么TIME类型会转换为0000：00：00。
（3）使用current_time或者current_time()输入当前系统时间，它们属于MySQL的函数，将会在以后的章节中讲解。
读者还需注意的是，一个合法的TIME值，如果超出了TIME的范围，将被截取为范围最接近的端点。例如，‘880：00：00’将会被转换为838：59：59。另外，无效的TIME值，在命令行下无法被插入到表中。
3.DATE类型
DATE类型使用4个字节来表示日期。在MySQL中以YYYY-MM-DD的形式显示DATE类型的值，其中，YYYY表示年，MM表示月，DD表示日。DATE类型的字段赋值表示方法如下。
（1）表示‘YYYY-MM-DD’或‘YYYYMMDD’格式的字符串。例如输入‘4008-2-8’，DATE类型将转换为4008-02-08；输入‘40080308’，DATE类型将转换为4008-03-08。
（2）在MySQL中还支持一些不严格的语法格式，任何标点都可以用来做间隔符：如‘YYYY/MM/DD’、‘YYYY@MM@DD’和‘YYYY.MM.DD’等分隔形式。例如输入‘2011.3.8’，DATE类型将转换为2011-03-08。
（3）表示‘YY-MM-DD’或者‘YYMMDD’格式的字符串，其中‘YY’的取值，‘00’~‘69’转换为2000~2069，‘70’~‘99’转换为1970~1999，与YEAR类型类似。例如输入‘35-01-02’，DATE类型将转换为2035-01-02，输入‘800102’，DATE类型将转换为1980-01-02。
（4）使用current_date或current_date ()输入当前系统日期：这些属于MySQL的函数，将会在以后的章节中讲解。
在实际开发中，如果只需要记录日期，选择DATE类型是最合适的，因为DATE类型只占用4个字节。需要注意的是，虽然MySQL支持DATE类型的一些不严格的语法格式，但是在实际应用中最好选择标准形式。日期中使用“-”做分隔符，时间用“:”做分隔符，中间用空格隔开，格式如2016-03-17 16:27:55。当然如果有特殊需要，可以使用“@”，“*”等特殊字符做分隔符。
4.DATETIME类型
DATETIME类型使用8个字节来表示日期和时间。在MySQL中以‘YYYY-MM-DD HH:MM:SS’的形式来显示DATETIME类型的值。从其形式上可以看出，DATETIME类型可以直接用DATE类型和TIME类型组合而成。DATETIME类型的字段赋值表示方法如下。
（1）表示‘YYYY-MM-DD HH:MM:SS’或‘YYYYMMDDHHMMSS’格式的字符串。这种方式可以表达的范围是‘1000-01-01 00:00:00’~‘9999-12-31 23:59:59’。例如输入‘2008-08-08 08:08:08’，DATETIME类型会自动转换为2008-08-08 08:08:08，输入‘20080808080808’，同样转换为2008-08-08 08:08:08。
（2）DATETIME类型可以使用任何标点作为间隔符：这与TIME类型不同，TIME类型只能用“:’隔开。例如输入‘2008@08@08 08*08*08’，数据库中DATETIME类型统一转换为2008-08-08 08:08:08。
（3）表示‘YY-MM-DD HH:MM:SS’或‘YYMMDDHHMMSS’格式的字符串。其中‘YY’的取值，‘00’~‘69’转换为2000~2069，‘70’~‘99’转换为1970~1999。与YEAR类型和DATE类型相同。例如，输入‘69-01-01 11:11:11’，数据库中插入2069-01-01 11:11:11，输入‘70-01-01 11:11:11’，数据库中插入1970-01-01 11:11:11。
（4）使用now()来输入当前系统日期和时间，它属于MySQL的函数，将会在后面的章节中讲解。
DATETIME类型用来记录日期和时间，其作用等价于DATE类型和TIME类型的组合。一个DATETIME类型的字段可以用一个DATE类型的字段和一个TIME类型的字段代替。但是如果需要同时记录日期和时间，选择DATETIME类型是个不错的选择。
5.TIMESTAMP类型
TIMESTAMP类型使用4个字节来表示日期和时间。TIMESTAMP类型的范围是1970-01-01 08:00:01~2038-01-19 11:14:07。在MySQL中也是以‘YYYY-MM-DD HH:MM:SS’的形式显示TIMESTAMP类型的值。从其形式可以看出，TIMESTAMP类型与DATETIME类型的显示的格式是一样的。给TIMESTAMP类型的字段赋值的表示方法基本上与DATETIME类型相同。值得注意的是，TIMESTAMP类型范围比较小，没有DATETIME类型的范围大，因此输入值时需要保证在TIMESTAMP类型的有效范围内。
数据库的基本操作
² 创建和查看数据库
创建数据库就是在数据库系统中划分一块存储数据的空间，其语法格式如下。
CREATE DATABASE 数据库名称;
以上是创建数据库的语法，此处需要注意，数据库名称是唯一的，不能重复。
另外，用户还可以查看已经创建的数据库信息，语法格式如下所示。
SHOW CREATE DATABASE 数据库名称;
² 使用数据库
在创建数据库之后，如果想在此数据库中进行操作，则需要切换到该数据库，具体语法格式如下。
USE 数据库名;
另外，在使用数据库时，还可以查看当前使用的是哪个数据库。


² 修改数据库
前面讲解了如何创建和查看数据库，在数据库创建完成之后，编码也就确定了。若想修改数据库的编码，可以使用ALTER DATABASE语句实现，具体语法格式如下。


将数据库qianfeng的编码修改为gbk。


² 删除数据库
删除数据库就是将数据库系统中已经存在的数据库删除，在删除后，数据库中所有数据都会被清除，为数据库分配的空间也将被回收，删除数据库的语法格式如下。
DROP DATABASE 数据库名称;
将数据库qianfeng删除。


数据表的基本操作
² 创建数据表
在数据库创建成功之后，就可以在已经创建的数据库中创建数据表。在创建表之前，使用“USE 数据库名”切换到操作的数据库。创建数据表的语法格式如下。
CREATE table 表名(
字段名1 数据类型,
字段名2 数据类型，
……
字段名n 数据类型
);
在以上格式中，表名表示创建数据表的名称，字段名表示数据表的列名。
² 查看数据表
在创建完成数据表之后，可以通过SHOW CREATE TABLE语句查看数据表，语法格式如下。
SHOW CREATE TABLE 表名；
SHOW CREATE TABLE语句不仅可以查看表中的列，还可以查看表的字符编码等信息，但是显示的格式非常混乱，可以在查询语句后加上参数“\G”进行格式化。
mysql> SHOW CREATE TABLE stu\G;
*************************** 1. row ***************************
       Table: stu
Create Table: CREATE TABLE `stu` (
  `stu_id` int(10) DEFAULT NULL,
  `stu_name` varchar(50) DEFAULT NULL,
  `stu_age` int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
执行完后，显示的格式明显比之前整齐很多。
另外，如果只想查看表中列的相关信息，可以使用DESCRIBE语句，语法格式如下。
DESCRIBE 表名;
² 修改数据表
前面讲解了如何创建和查看数据表，在实际开发中，在数据表创建完成后可能会对数据表的表名、表中的字段名、字段的数据类型等进行修改，接下来对数据表的修改进行详细讲解。
1.修改表名  
在MySQL中，修改表名的语法格式如下所示。
ALTER TABLE 原表名 RENAME [TO] 新表名;
在以上格式中，关键字TO是可选的，是否写TO关键字都不会影响SQL语句的执行，一般忽略不写。
2.修改字段
数据表中的字段也时常有变更的需求，修改字段的语法格式如下。
ALTER TABLE 表名 CHANGE 原字段名 新字段名 新数据类型;
3.修改字段的数据类型
上面讲解了如何修改表中的字段，但有时并不需要修改字段，只需要修改字段的数据类型，修改表中字段数据类型的语法格式如下。
ALTER TABLE 表名 MODIFY 字段名 数据类型;
4.添加字段
在实际开发中，随着需求的扩展，表中可能需要添加字段，在MySQL中添加字段的语法格式如下。
ALTER TABLE 表名 ADD 新字段名 数据类型;
5.删除字段
删除表中的某一字段也是很可能出现的需求，在MySQL中删除字段的语法格式如下。
ALTER TABLE 表名 DROP 字段名;
6.修改字段的排列位置
在创建表时，表中字段的位置就已经确定，如果需要修改表中字段的位置，可以使用ALTER TABLE语句来修改，在MySQL中修改字段排列位置的语法格式如下。
ALTER TABLE 表名 MODIFY 字段名1 数据类型 FIRST|AFTER 字段名2;
在以上格式中，字段名1表示需要修改位置的字段，FIRST是可选参数，表示将字段1修改为表的第一个字段，“AFTER 字段名2”表示将字段1插入到字段2的后面。
² 删除数据表
删除数据表是从数据库将数据表删除，同时删除表中存储的数据。在MySQL中使用DROP TABLE语句删除数据表，语法格式如下。
DROP TABLE 表名;
小结：
本章详细讲解了MySQL支持的数据类型，对数据库的基本操作（例如创建、查看、修改和删除库）做了介绍，最后讲解了数据表的基本操作。

泡芙巧克力

已关注，慢慢看[爱]