MySQL学习总结_MySQL

bitsCN.com 1.数据库概述 简而言之，数据库(DataBase)就是一个存储数据的仓库。为了方便数据的存储和管理，将数据按照特定的规律存储在磁盘上。通过数据库管理系统，可以有效的组织和管理存储在数据库中的数据。如今，已经存在的Oracle、SQLServer、MySQL等诸多优秀的数据库。 详解内容： 数据存储方式 数据库在开发中的作用 数据库访问技术 MySQL数据库的介绍 数据库泛型 SQL语言 常见数据库系统 如果学习数据库 1.1 数据库理论基础 数据库能够将数据按照特定的规律组织起来。那么，数据是如何存储的、数据库要遵守什么规则、数据库在什么地方使用，这些都是首先要了解的问题。 1.1.1 数据存储方式 如今数据库已经无处不在了。 一个网站需要有数据库来存储数据。 一个学校需要用数据库来存储学生和教师的信息。 一个公司需要用数据库来存储员工的信息和公司的资料。 要学习数据库，必须先了解数据库是如何存储数据的。 数据发展分为三个阶段： 1. 人工管理阶段 2. 文件系统阶段 3. 数据库系统阶段 1.1.2 数据库泛型 数据库泛型就是数据库应该遵循的规则。数据库泛型也称为范式。目前关系数据库最常用的四种范式分别是： 第一范式(1NF) 第二范式(2NF) 第三范式(3NF) BCN范式(BCNF) 在设计数据库时，数据库需要满足的最低要求的范式是第一范式。第一范式的要求表中不能有重复字段，并且每个字段不能再拆分。如果有一个数据库连第一范式都不满足的话，那就不能称之为关系型数据库了。只有满足了第一范式的要求，才可能在这个关系数据库中创建表。 在满足第一范式的基础上，可以将数据库中进行一步的细化。细化后可以使数据库满足第二范式的要求。依次进行细化，可以得到第三范式、BCN范式。 1.1.3 数据库在开发中作用 现在大部分的管理系统和软件都需要使用数据库来存储数据。在开发过程中，数据库已经成为必不可少的一部分。本小节将为读者简单介绍一下数据库在开发中的作用。 在软件开发过程中，经常需要使用数据库来存储数据。比如，一个学校的学生管理系统就需要数据库来存储学生的学籍信息、考试信息、教师的信息、课程信息等。再比如，银行的管理系统也需要数据库来存储信息。用户的银行帐户、存款量、存款和取款的记录等信息都是存储在数据库中的。当用户向自己的帐户里存款时，管理系统会在数据库中更新该用户的存款量。 1.2 数据库系统 1.2.1 数据库系统 很多读者认为数据库就是数据库系统(DataBase System, 简称为DBS)。其实，数据库系统的范围比数据库大很多。数据库系统不是一个单纯的数据库，而是由数据库、数据库管理系统、应用开发工具构成。很多时候，数据库管理员和用户可以当成数据库系统的一份子。 数据库就是存储数据的地方。 数据库管理系统(DataBase Management System, 简称为DBMS)是用来定义数据、管理和维护数据的软件。它是数据库系统的一个重要组成部分。应用系统是需要使用数据库的软件。比如学员管理系统就是一个应用系统，这个应用系统需要数据库管理它的数据。应用开发工具就是用来开发应用系统的。 1.2.2 SQL语言 SQL(Structured Query Language)语言的全称是结构化查询语言。数据库管理系统通过SQL语言来管理数据库中的数据。 SQL语言分为三个部分： 数据定义语言(Data Definition Language, 简称为DDL) 数据操作语言(Data Manipulation Language, 简称为DML) 数据控制语言(Data Control Language, 简称为DCL) 数据查询语言(Data Query Language, 简称DQL) 1.2.3 数据库访问技术 应用程序中，程序语言需要使用数据库访问技术来访问数据库。只有使用了数据库访问技术，程序中嵌入的SQL语句才会起作用。不同程序语言访问数据库的方式是不一样的。 JDBC是一种用于执行SQL语句的Java API。通过JDBC可以访问多种关系数据库。JDBC由一组用Java语言编写的类和接口组成。使用JDBC时，必须要安装驱动程序Connector/J。 PHP中为程序员提供了MySQL功能模块，PHP5以后开始提供mysqli接口。PHP可以通过MySQL功能模块和mysqli接口来访问MySQL数据库。 1.3 常见数据库 1.3.1 常见数据库系统 如今已经在很多优秀的商业数据库 如甲骨文(Oracle)公司的Oracle数据库 IBM公司的DB2数据库 微软公司的SQL Server数据库和Access数据库。 还有很多优秀的开源数据库，如MySQL数据库、PostgreSQL数据库等。 1. 甲骨文的Oracle 2. IBM的DB2 3. 微软的Access和SQL Server 4. 开源PostgreSQL 6. 开源MySQL 1.3.2 为什么要使用MySQL 如今很多大型网站已经选择MySQL数据库来存储数据。那么，MySQL到底有什么优势呢？MySQL数据库的使用非常广泛，尤其是在Web应用方面。由于MySQL数据库发展势头迅猛，SUN公司于2008年收购了MySQL数据库。这笔交易的收购价格高达10亿美元。这中以说明MySQL数据库价值。 MySQL数据库的优势: 1. 开源代码 2. 跨平台性 3. 价格优势 4. 功能强大且使用方便 1.3.3 MySQL版本和获取 MySQL数据库可以在Windows、UNIX、Linux和Mac OS等操作系统上运行。因此，MySQL有不同操作系统的版本。而且，根据发布的先后顺序，现在已经在开发MySQL的6.0版了。 根据MySQL的开发情况，可以将MySQL分为Alpha、Beta、Gamma和Generally Available(GA)等版本。 - Alpha(开发过程中) - Beta(测试版) - Gamma(比Beta版更高级) - Generally Available(GA)(稳定版) 1.4 如果学习数据库 数据库已经成为软件系统的一部分，那么学好数据库将是软件开发的一个必要条件。如何才能学好数据库，这个问题没有确切的答案。 学好数据库的方法： 1. 多上机实践 2. 多编写SQL语句 3. 通过Java或PHP程序语言操作数据库 4. 数据库理论知识不能丢 1.5 常见问题及解答 1. 如何选择数据库？ 2. 如何选择MySQL版本？ 1.6 本章习题 1. 数据存储的发展过程经历了哪几个阶段？ 2. 常用数据库系统有哪些？ 3. MySQL数据库如何分类？2.Windows平台下安装与配置MySQL 在Windows系列的操作系统下，MySQL数据库的安装包分为图形化界面安装和免安装(noinstall)这两种安装包。这两种安装包的安装方式不同，而且配置方式也不同。图形化界面安装包有完整的安装向导，安装和配置很方便。免安装的安装包直接解压即可使用，但是配置起来很不方便。 安装MySQL数据库 配置MySQL数据库 常用图形管理工具介绍 配置和使用免安装的MySQL数据库 2.1 安装与配置MySQL 2.1.1 安装MySQL MySQL图形化安装包有一个完整的安装向导，根据安装向导可以很方便的安装MySQL数据库。在Windows操作系统下，有两种MySQL图形化安装包。这两种安装包分别是"Windows Essentials"和"Windows MSI Installer"。前者包含了MySQL中最主要和最常用的功能，但是不包含一些不常用的功能。后者包含了MySQL全部功能，包括不常用的功能。 http://dev.mysql.com/downloads/mysql/#downloads 选择Microsoft Windows选项 mysql-5.1.40-win32.msi(图形界面安装) mysql-essential-5.1.40-win32.msi (图形界面安装) mysql-noinstall-5.1.40-wind32.zip (免安装) 安装步骤： 1. Next 2. Typical Complete Custom(O) 3. MySQL Server Client Programs C Includes Files / Lib Files(X) Change: ????? 4. Install Setup Type: Custom Destination Folder(程序安装路径): Data Folder(数据存储的路径): 5. Next->Next-> (O)Configure the MySQL Serer now (O)Register the MySQL Server now 6. Finish 2.1.2 配置MySQL 安装完成时，选上了Configure the MySQL Server now选项，图形化安装向导将进入MySQL配置欢迎界面。通过配置向导，可以设置MySQL数据库的各种参数。 1. Next 2. (O)Detailed Configuration (X)Standard Configuration 3. (O)Developer Machine(开发者) Server Machine(服务器) Dedicated MySQL Server Machine(专用的数据库服务器) 4. (O)Multifunctional Database(多功能型数据库) Transactional Database Only(事务型的数据库, InnoDB) Non-Transactional Database Only(只支持MyISaM类型数据库，非事务) 5. InnoDB Tablespace Settings (配置表空间) 6. 设置服务器的连接数 Decision Support(DSS) / OLAP Online Transaction Processing(OLTP) (O)Manual Setting Concurrent Connections: 10 7. MySQL服务的端口号： (O)Add firewall exception for this port(允许防火墙) (X)Enable Strict Mode(是区分大写) 8. 设置字符集 Standard Character Set Best Support for Multilingualism (O)Manual Selected Default Character Set / Collation utf-8 9. Install As Windws Service 服务选项 Service Name: MySQL (O)Lanch the MySQL Server automatically(服务是否自动启动) (O)Include Bin Directory in Windows PATH(添加到path路径当中) 10. Modify Security Setting: New root password: confirm: (X)Enable root access from remote machines(不否远程访问root) 11. Execute 12. Finish 2.2 启动服务并登录MySQL数据库 MySQL数据库分为服务器端(Server)和客户端(Client)两部分。只有服务器端的服务开户以后，才可以通过客户端来登录到MySQL数据库。 2.2.1 启动MySQL服务 开始->设置->控制面板->管理工具->服务 2.2.2 登录MySQL服务器 1. 开始->运行->cmd mysql -h localhost -u root -p mysql> 2. 开始->运行->mysql -h 127.0.0.1 -u root -p 2.2.3 配置path变量 如果MySQL的应用程序的目录没有添加到Windows系统的path环境变量中，可以手工的将MySQL的目录添加到path中。 将MySQL的应用程序的目录添加到Windows系统的path环境变量中，可以使以后的操作更加方便。例如，可以直接从运行对话框输入MySQL数据库命令。而且，以后在编程时也会更加方便。配置path路径很简单，只要将MySQL的应用程序的目录添加到系统的path环境变量中就可以了。 1. 右击【我的电脑】->【属性】 2. 在系统环境变量中选中path变量，然后单击编辑按钮进入编辑环境变量的对话框。 3. 把MySQL应用程序的目录添加到path环境变量名值的后面。已经存在的目录用分号隔开。 2.3 更改MySQL的配置 MySQL数据库安装好了以后，可能根据实际情况更改MySQL的某些配置。一般可以通过两种方式来更改。一种是通过配置向导来更改配置，另一种是手工来更改配置。 2.3.1 通过配置向导来更改配置 MySQL Server Instance Config Wizard与初始配置时 1.Reconfigure Instance (修改配置信息) Remove Instance (删除配置信息) 2.Detailed Configuration ...... ..... 2.3.2 手工更改配置 用户可以通过修改MySQL配置文件方式来进行配置。这种配置方式更加灵活，但是相对初学者来说比较困难。 在进行手工配置之前，需要对MySQL的文件有所了解。MySQL的文件安装在？?？？？ 数据库的数据文件安装在？？？？？ bin 可执行目录 data 数据目录 scripts share 字符集目录 mysql.ini 基本配置文件 my-huge.ini 配置大型数据库 my-large.ini .. my-innodb-heavy-4g 配置innodb my-medium.ini 中型数据库配置文件 my-small.ini 小型数据库配置文件 my-template.ini 配置文件模板 mysql.ini: port = 3306 # 端口号 default-character-set = gbk # 默认的字符集 basedir = .... # 安装文件夹 datadir = ..... # 数据库文件夹 default-storage-engine = INNODB # 默认的存储引擎 2.4 MySQL常用图形管理工具 MySQL图形管理工具可以在图形界面上操作MySQL数据库。在命令行中操作MySQL数据库时，需要使用很多的命令。而图形管理工具则只是使用鼠标点即可，这使MySQL数据库的操作更加简单。 MySQL的图形管理工具很多。常用的有MySQL GUI Toools、phpMyAdmin、Navicat等。通过这些图像管理工具，可以使MySQL的管理更加方便。每种图形管理工具各有特点。 1. MySQL GUI Tools 2. phpMyAdmin 3. Navicat 4. SQLyog 5. MySQLDumper 6. MySQL ODBC/Connector 2.5 使用免安装MySQL Windows操作系统下有免安装的MySQL软件包。用户直接解压这个软件包，进行简单的配置就可以使用了。免安装包省略了安装过程，使用起来也很方便。 1. 解压软件包 2. 创建my.ini文件 3. 修改my.ini文件 [mysqld] socket .... basedir = "c:/mysql/" datadir = "c:/mysql/data" [WindowsMySQLServer] Server = "c:/mysql/bin/mysqld.exe" * my.ini文件复制到C:/WINDOWS文件夹 4. 设置MySQL服务 c:/mysql/bing/mysqld.exe --install 5. 配置系统Path变量 6. 启动和关闭服务 运行服务：运行-> net start mysql 启动服务 net stop mysql 停止服务 2.6 上机操作 1. 通过图形化方式安装MySQL数据库 2. 配置免安装的MySQL 2.7 常见问题及解答 1. 如何选择字符集? 2. 如何删除MySQL数据库？ 2.8 习题 1. 练习使用图形化安装MySQL数据库。 2. 练习使用配置向导配置MySQL数据库。 3. 练习使用免安装的MySQL软件包安装。 4. 练习通过手工修改my.ini文件的方式更改配置。3.Linux平台下安装与配置MySQL 在Linux系列的操作系统下，一般都使用命令来安装MySQL数据库。因为Linux操作系统的发行版比较多，所以相应的MySQL版本也比较多。相同Linux发行版也有不同的MySQL软件包。根据自己的操作系统的版本来下载和安装不同的MySQL版本。 - Linux操作系统下的MySQL版本介绍 - 安装和配置MySQL的RPM包 - 安装和配置MYSQL的二进制包 - 安装和配置MySQL的源码包 3.1 Linux操作系统下的MySQL版本介绍 Linux操作系统的发行版很多，不同Linux发行版的MySQL版本是不同的。MySQL数据库主要支持Linux版本是Red Hat Enterprise Linux和SUSE Linux Enterprise Server。这两个版本下也有不同的安装包。 Linux操作系统的MySQL软件包一般分为三类，分别是RPM软件包，二进制软件包和源码包。 RPM软件包： 二进制包： 源码包： 1. Red Hat Enterprise Linux发行版 2. SUSE LInux Enterprise Server发行版 Linux版本 推荐安装的MySQL版本 Red Hat Enterprise Linux RPM软件包 SUSE Linux Enterprise Server RPM软件包 Fedora RPM软件包 Ubuntu 源码包 CentOS RPM软件包 3.2 安装和配置MySQL的RPM包 大部分Linux版本的MySQL软件是以RPM包的形式发布的。RPM包的安装和卸载都很方便，通过简单的命令就可以实现RPM包的安装与卸载。可能通过手工修改配置文件的方式来进行配置。 MySQL官方网站下载RPM包，http://dev/mysql.com/downloads/mysql/5.1.html。在该网站上有很多种类的RPM安装包，根据自己的操作系统和处理器类型来选择。 Red Hat Enterprise Linux 5 RPM(x86) 3.3 安装和配置MySQL的二进制包 Linux操作系统下有以二进制形式发布的MySQL软件包。这些二进制的MySqL软件包比RPM包要灵活，但是安装没有RPM包那么容易。 http://dev/mysql.com/downloads/mysql/5.1.html。 单击Linux(non RPM packages)链接，就可跳转到下载二进制包的位置。 3.4 安装和配置MySQL的源码包 Linux操作系统下有以源码的形式发布的MySQL软件包。这样软件包中是MySQL的源代码，需要用户自己进行编译。这种MySQL软件包的灵活性最高，用户可以根据自己的需要进行控制。而且， 感兴趣的用户可以查看MySQL的源代码。但是编译代码耗费的时候比较长。 http://dev.mysql.com/downloads/mysql/5.1.html 单击Source链接，就可以跳转到下载源码包的位置。 3.5 上机实践 1. 在Linux操作系统下用RPM包来安装MySQL数据库 2. 在Linux操作每户下用二进制包安装MySQL数据库 3. 在Linux操作系统下用源码包来安装MySQL数据库 3.6 常见问题及解答 1. 如何选择Linux操作系统下的MySQL数据库？ 2. Linux下如何卸载MySQL数据库？ 3.7 本意习题 1. 练习使用RPM软件包安装MySQL数据库。 2. 练习使用二进制软件包安装MySQL数据库。 3. 练习使用源码包安装MySQL数据库。 4. 练习手动配置MySQL的配置文件。4.MySQL数据类型 数据类型是数据的一种属性，其可以决定数据存储格式、有效范围和相应的限制。MySQL的数据类型包括整数类型、浮点数类型、定点数类型、日期和时间类型、字符串类型和二进制类型。 - 整数类型、浮点数类型和定点数类型 - 日期与时间类型 - 字符串类型 - 二进制类型 - 如何选择数据类型 4.1 MySQL数据类型介绍 MySQL数据库提供了多种数据类型。不同的类型有各自的特点，使用范围不相同。而且，存储方式也不一样。 4.1.1 整型 整数类型是数据库中最基本的数据类型。标准SQL中支持INTEGER和SMALLINT这两种整数类型。MySQL数据库除了支持这两种类型外，还扩展支持了TINYINT、MEDIUMINT和BIGInT。 各种整数类型的取值范围、存储的字节数、特点。 整型 字节数 无符号数的取值范围 有符号数的取值范围 TINYINT 1 0~255 -128~127 SMALLINT 2 0~65535 -32768~12767 MEDIUMINT 3 0~16777215 -8388608~8388607 INT 4 0~4294967295 -2147483648~2147483647 INTEGER 4 0~4294967295 -2147483648~2147483647 BIGINT 8 0~18446744073709551615 -9223372036954775808~9223372036854775807 MySQL命令不区分大小写，通常使用大写表示MySQL关键字，使用小写表示用户定义的值。 例如： # 设置密码 mysql -h localhost -u root -p USE `databasename`; DESC `tablename`; MySQ整数类型默认长度 1. tinyint(4) 2. smallint(6) 3. mediumint(9) 4. int(11) 5. bigint(20) Mysql注释支持以下几种: 1、# 2、-- 3、/* */ 实例一： # 如果数据库中不存在test数据库，则创建test数据库。 CREATE DATABASE IF NOT EXISTS `test`; -- 选择数据库 USE `test`; /* 如果test数据库中不存在int_tbl_1表，则创建int_tbl_1表 */ CREATE TABLE IF NOT EXISTS `test`.`int_tbl_1` ( `a` TINYINT, `b` SMALLINT, `c` MEDIUMINT, `d` INT, `e` BIGINT ); INSERT INTO `test`.`int_tbl_1` VALUES(1,1,1,1,1); INSERT INTO `test`.`int_tbl_1` VALUES(2,2,2,2,2); INSERT INTO `test`.`int_tbl_1` VALUES(3,3,3,3,3); INSERT INTO `test`.`int_tbl_1` VALUES(4,4,4,4,4); INSERT INTO `test`.`int_tbl_1` VALUES(5,5,5,5,5); SELECT * FROM `int_tbl_1`; 实例二： CREATE TABLE IF NOT EXISTS `test`.`int_tbl_2` ( `a` TINYINT UNSIGNED ZEROFILL NOT NULL , `b` SMALLINT UNSIGNED ZEROFILL NOT NULL , `c` MEDIUMINT UNSIGNED ZEROFILL NOT NULL , `d` INT UNSIGNED ZEROFILL NOT NULL , `e` BIGINT UNSIGNED ZEROFILL NOT NULL ); INSERT INTO `test`.`int_tbl_2` VALUES(1,1,1,1,1); INSERT INTO `test`.`int_tbl_2` VALUES(2,2,2,2,2); INSERT INTO `test`.`int_tbl_2` VALUES(3,3,3,3,3); INSERT INTO `test`.`int_tbl_2` VALUES(4,4,4,4,4); INSERT INTO `test`.`int_tbl_2` VALUES(5,5,5,5,5); SELECT * FROM `int_tbl_2`; 使用UNSIGNED或UNSIGNED ZEROFILL之后MySQ整数类型默认长度: 1. tinyint(3) 2. smallint(5) 3. mediumint(8) 4. int(10) 5. bigint(19) 实例三： CREATE TABLE IF NOT EXISTS `test`.`int_tbl_3` ( `a` INT(4) NOT NULL , `b` INT(11) NOT NULL ) ENGINE = INNODB; INSERT INTO `test`.`int_tbl_3` VALUES(111111,22222222); SELECT * FROM `int_tbl_3`; -- 数据111111成功插入到字段a中,不管字段a长度是设置了多少。因为INT类型的默认最大长度为11，因此即使插入的数据超出了指定长度的范围，也会插入到数据表中。 如何为字段选择整数类型？ 1. TINYINT : 如果字段的最大值不超过255，那选择TINYINT类型就足够了。 2. INT: 如果需要范围比较大的值，使用INT类型即可。如会员表中的编号字段等。 4.1.2 浮点数类型和定点数类型 MySQL使用浮点数类型和定点数类型来表示小数。浮点数类型包括单精度浮点数(FLOAT类型)和双精度浮点数(DOUBLE类型)。定点数类型就是DECIMAL型。 FLOAT型、DOUBLE型、DECIMAL型的取值范围、存储的字节数、特点。 小数类型 字节数 负数取值范围 无符号取值范围 FLOAT 4 -3.402823466E+38~ 0和1.175494351E-38~ -1.175494351E-38 3.402823466E+38 DOUBLE 8 1.7976931348623157E+308~ 0和2.2250738585072014E~ -2.2250738585072014E-308 1.7976931348623157E+308 DECIMAL(M,D) M+2 DOUBLE型 同DOUBLE型 或DEC(M,D) M:最大长度（包括小数部分，但不包括小数点） D:小数点后保留长度 FLOAT(6,2)表示数据长度为6个，小数点保留2位。1234.56符合要求。 如果插入值的精度高于实际定义的精度，系统会自动进行四舍五入处理，使值的精度达到要求。不同的是，FLOAT型和DOUBLE型在四舍五入时不会报错，而DECIMAL型会警告。 实例一： CREATE TABLE IF NOT EXISTS `test`.`decimal_tbl_1` ( `a` FLOAT(6,2), `b` DOUBLE(6,2), `c` DECIMAL(6,2) ) ENGINE = MyISAM; INSERT INTO `test`.`decimal_tbl_1` VALUES(3.143, 3.145, 3.1434); SHOW WARNINGS; -- 查看警告 # DECIMAL型有警告 SELECT * FROM `decimal_tbl_1`; 如果不指定精度，浮点数和定点数有其默认的精度。FLOAT型和DOUBLE型默认会保存实际精度，但这与操作系统和硬件的精度有关。DECIMAL型默认整数位为10，小位位为0，即默认为整数。 实例二： CREATE TABLE IF NOT EXISTS `test`.`decimal_tbl_2` ( `a` FLOAT, `b` DOUBLE, `c` DECIMAL ) ENGINE = MyISAM; INSERT INTO `test`.`decimal_tbl_2` VALUES(3.143, 3.145, 3.1434); SHOW WARNINGS; -- 查看警告 SELECT * FROM `decimal_tbl_2`; 在MySQL中，定点数以字符串形式存储。因此，其精度比浮点数要高。而且，浮点数会出现误差，这是浮点数一起存在的缺陷。如果要对数据的精度要求比较高，还是选择定点数(DECIMAL)比较安全。 不要使用"="做浮点数操作。 4.1.3 日期和时间类型 日期和时间类型是为了方便在数据库中存储日期和时间而设计的。MySQL中有多种表示日期和时间的数据类型。其中，YEAR类型表示年份；DATE类型表示日期；TIME类型表示时间；DATETIME和TIMESTAMP表示日期和时间。 日期和时间类型的存储字节数、取值范围和特点 1. YEAR类型 2. TIME类型 3. DATE类型 4. DATETIME类型 5. TIMESTAMP类型 4.1.3.1 YEAR类型 (1) 使用4位字符串或数字表示。 使用字节 取值范围 输入格式 超出范围 1 1901~2155 YYYY | 'YYYY' | "YYYY" 0000 实例一： CREATE TABLE IF NOT EXISTS `test`.`year_tbl_1`(`a` YEAR); INSERT INTO `test`.`year_tbl_1` VALUES(1997),('1998'),("1990"),(2200); SELECT * FROM `year_tbl_1`; (2) 使用2位整数或字符串表示。'00'到"69"转换为2000到2069，'70'到"99"转换为1970到1999。例如输入'35', YEAR值会转换为2035；输入'90', YEAR值会转换为1990。 '0'和"00"的效果是一样的。 实例二： INSERT INTO `test`.`year_tbl_1` VALUES('24'),("86"),("0"),('00'); SELECT * FROM `year_tbl_1`; INSERT INTO `test`.`year_tbl_1` VALUES(50),(0); 注意：插入0，转换后的YEAR值不是2000，而是0000。 因为YEAR类型只占一个字节。如果只需要记录年份，选择YEAR类型可以节约空间。 4.1.3.2 TIME类型 类型 字节 表示形式 取值范围 TIME 3 HH:MM:SS '-839:59:59'到'838:59:59' HH:时 MM:分(0~59) SS:秒(0~59) (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 23:50:50", '22:22:22', '11:11', '2 20:20', '2 20' 和 '30' CREATE TABLE IF NOT EXISTS `test`.`time_tbl_1`(`a` TIME); INSERT INTO `test`.`time_tbl_1` VALUES ("2 23:50:50"), ('22:22:22'), ('11:11'), ('2 20:20'), ('2 20'), ('30'); SELECT * FROM `time_tbl_1`; (2) 'HHMMSS'格式的字符串或者HHMMSS格式的数值表示。例如，输入'345454', TIME类型会转化为34:54:54,TIME类型会转化为34:54:54。如果输入0和'0'，那么TIME类型会转化为0000:00:00。 实例二： INSERT INTO `test`.`time_tbl_1` VALUES (121212), ('131313'), ('0'),(0); SELECT * FROM `time_tbl_1`; (3) 使用CURRENT_TIME或者NOW()输入当前系统时间。 实例三： INSERT INTO `test`.`time_tbl_1` VALUES (CURRENT_TIME),(NOW()); SELECT * FROM `time_tbl_1`; TIME类型专门用来存储时间数据，而且只占3个字节。如果只需要记录时间，选择TIME类型是最合适的。 4.1.3.3 DATE类型 类型 字节数 显示形式 取值范围 赋值方式 DATE 4 YYYY-MM-DD '1000-01-01' 'YYYY-MM-DD'或 到'9999-12-31' 'YYYYMMDD' (1) MySQL中支持一些不严格的语法格式，任何标点都可以用来做间隔符。如'YYYY/MM/DD'、'YYYY@MM@DD'、'YYYY.MM.DD'等分隔形式。 实例一: CREATE TABLE IF NOT EXISTS `test`.`date_tbl_1`(`a` DATE); INSERT INTO `test`.`date_tbl_1` VALUES ('1949-10-01'), ('1950#2#3'),('19514'),('19520101'); SELECT * FROM `date_tbl_1`; (2) '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。 MySQL也支持一些不严格的语法格式，如'YY/MM/DD'、'YY@MM@DD'、'YY.MM.DD'等分隔形式。例如，输入'898', DATE类型转换为1989-03-08。 实例二： INSERT INTO `test`.`date_tbl_1` VALUES ('53-01-01'), ('781'),('540101'),('790101'); SELECT * FROM `date_tbl_1`; (3) YYYYMMDD或YYMMDD格式的数字表示。其中'YY'的取值，'00'到'69'转换为2000到2069.'70'到'99'转换为1970到1999。例如，输入20080808,DATE类型转换为2008-08-08；输入790808，DATE类型将转换为1979-08-08。如果输入的值为0,那么DATE类型会转换为0000-00-00 实例三： INSERT INTO `test`.`date_tbl_1` VALUES (20080808), (800101), (090101), (0); SELECT * FROM `date_tbl_1`; (4) 使用CURRENT_DATE或者NOW()来输入函数系统日期。 INSERT INTO `test`.`date_tbl_1` VALUES (CURRENT_DATE),(NOW()); SELECT * FROM `date_tbl_1`; 4.1.3.4 DATETIME类型 类型 字节 显示形式 取值范围 DATETIME 8 YYYY-MM-DD HH:MM:SS '1000-01-01 00:00:00'到 '9999-12-31 23:59:59' (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；输入&＃8217;20080808080808&＃8216;， 同样转换为2008-08-08 08:08:08。 MySQL支持一些不严格的语法格式，任何标点都可以用来做间隔符。情况与DATE类型相同。而且，时间部分也可以使用任意分隔符隔开，这与TIME类型不同。TIME类型只能使用':'隔开。例如：输入'200808@ 08*08*08', 同样转换为2008-08-08 08:08:08。 实例一： CREATE TABLE IF NOT EXISTS `test`.`datetime_tbl_1`(`a` DATETIME); INSERT INTO `test`.`datetime_tbl_1` VALUES ('1949-10-01 11:11:11'),('1950#2#2 11+11+11'), ('19510101121212'); SELECT * FROM `datetime_tbl_1`; (2) 'YY-MM-DD HH:MM:SS'或者'YYMMDDHHMMSS'格式的字符串表示。其中'YY'的取值，'00'到'69'转换为2000到2069,'70'到'99'转换为1970到1999。与YEAR型和DATE型相同。 实例二： INSERT INTO `test`.`datetime_tbl_1` VALUES ('52-01-01 11:11:11'),('531 11*11*'), ('790101121212'); SELECT * FROM `datetime_tbl_1`; (3)YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的数字表示。例如，输入20080808080808,DATETIME类型转换为2008-08-08 08-08-08；输入080808080808，同样转换为2008-08-08 08:08:08。如果输入的值为0，那么DATETIME类型转换为0000-00-00 00:00:00。 实例三： INSERT INTO `test`.`datetime_tbl_1` VALUES (20080808080808),(090101080808), (790101080808),(0); SELECT * FROM `datetime_tbl_1`; (4) 使用NOW()来输入当前系统日期和时间 实例四： INSERT INTO `test`.`datetime_tbl_1` VALUES (NOW()); SELECT * FROM `datetime_tbl_1`; 4.1.3.5 TIMESTAMP类型 TIMESTAMP类型使用４个字节来表示日期和时间。TIMESTAMP类型的范围从1970-01-001 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 TABLE IF NOT EXISTS `test`.`timestamp_tbl_1`(`a` TIMESTAMP); INSERT INTO `test`.`timestamp_tbl_1` VALUES ('1970-10-01 11:11:11'),('1970#2#3 11+11+11'), ('19710101121212'),('28-01-01 11:11:11'), ('331 11*11*11'), ('790101121212'),('20080808080808'), ('090101080808'), (0),(NOW()); SELECT * FROM `timestamp_tbl_1`; TIMESTAMP类型的几种与DATETIME类型不同的形式。 (1) 使用CURRENT_TIMESTAMP来输入系统日期与时间 (2) 输入NULL时，系统会输入系统当前日期与时间 (3) 无任何输入时，系统会输入系统当前日期与时间 实例二: INSERT INTO `test`.`timestamp_tbl_1` VALUES(); INSERT INTO `test`.`timestamp_tbl_1` VALUES(''); INSERT INTO `test`.`timestamp_tbl_1` VALUES(CURRENT_TIMESTAMP); INSERT INTO `test`.`timestamp_tbl_1` VALUES(NOW()); INSERT INTO `test`.`timestamp_tbl_1` VALUES(NULL); TRUNCATE TABLE `timestamp_tbl_1`; -- 清空表 TIMESTAMP类型还有一个很大的特点，说是时间是根据时区来显示的。 需要显示日期与时间，TIMESTAMP类型能够根据不同地区的时区来转换时间。但是，TIMESTAMP类型的范围太小。其最大时间为2038-01-19 11:14:07 4.1.4 字符串类型 字符串类型是在数据库中存储字符串的数据类型。字符串类型包括CHAR、VARCHAR、BLOB、TEXT、ENUM、SET。 1. CHAR类型和VARCHAR类型 2. TEXT类型 3. ENUM类型 4. SET类型 4.1.4.1 CHAR类型和VARCHAR类型 CHAR类型和VARCHAR类型都是在创建表是指定最大长度，其基本形式如下： 字符串类型(M) 例如，CHAR(4)就是指数据类型为CHAR类型，其最大长度为4。 CHAR类型的长度是固定的，在创建表时就指定了。其长度可以是0~255的任意值。 例如，CHAR(100)就是指定CHAR类型的长度为100。 VARCHAR类型的长度是可变的，在创建时指定了最大长度。定义时，其最大值可以取0~65535之间的任意值。指定VARCHAR类型的最大值以后，其长度可以在0到最大长度之间。例如，VARCHAR(100)的最大长度是100。但是，不是每条记录都要占100个位置。而是在这个最大值范围内，使用多少分配多少。VARCHAR类型实际占用的空间为字符串的实际长度加1。这样，可以有效的节约系统的空间。 CHAR(5)与VARCHAR(5)对比 插入值 CHAR(5) 占用字节数 VARCHAR(5) 占用字节数 '' '' 5个字节 '' 1个字节 '1' '1' 5个字节 '1' 2个字节 '123' '1' 5个字节 '123' 4个字节 '123 ' '123' 5个字节 '123 ' 5个字节 '12345' '12345' 5个字节 '12345' 6个字节 CHAR(5)所占用的空间都是5个字节。这表示CHAR(5)的固定长度就是5个字节。而VARCHAR（5）所占的字节数是实际长度的基本上加一。因为字符串的结束标志符占用一个字节。从表的第三行可以看到，VARCHAR将字符串'123 '最后面空格依然保留着。为了确认空格是否保留，将所有数据后面加上'*'字符。结果显示如下： 实例一： CREATE TABLE IF NOT EXISTS `test`.`string_tbl_1`( `a` CHAR(5), `b` VARCHAR(5) ); INSERT INTO `test`.`string_tbl_1` VALUES('',''),('1', '1'),('123','123'),('123 ', '123 '),('12345', '12345'); SELECT * FROM `string_tbl_1`; char_part varchar_part * * 1* 1* 123* 123* 123* 123 * 12345* 12345* 由此可见，VARCHA类型将'123'最后面的空格保留着。而CHAR类型中将'123'后面的空格自动删除。 4.1.4.2 TEXT类型 TEXT类型是一种特殊的字符串类型。TEXT只能保存字符数据，如文章等。TEXT类型包含TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。 类型 允许的长度 存储空间 TINYTEXT 0~255字节 值的长度+2个字节 TEXT 0~65535字节 值的长度+2个字节 MEDIUMTEXT 0~167772150字节 值的长度+3个字节 LONGTEXT 0~4294967295字节 值的长度+4个字节 从表可以看出，各种TEXT类型的区别在于允许的长度和存储空间不同。因此在这几种TEXT中，根据需求选取既能满足需要以最节约空间的类型即可。 实例一： CREATE TABLE IF NOT EXISTS `test`.`string_tbl_2`( `a` CHAR(10), `b` VARCHAR(20), `c` TEXT ); INSERT INTO `test`.`string_tbl_2` VALUES('','',''),('1', '1','1'),('123','123','123'),('123 ', '123 ', '123 '),('12345', '12345','12345'); SELECT * FROM `string_tbl_2`; 4.1.4.3 ENUM类型(枚举类型) ENUM类型又称为枚举类型。在创建表时，ENUM类型的取值范围就以列表的形式指定了。 属性名 ENUM('值1', '值2',...., '值n'); 其中， '属性名'参数指定字段名称；'值n'参数表示列表中的第n个值，这些值末尾的空格将会被系统直接删除。 ENUm类型的值只能列表中的一个元素。其取值列表中最多能有65535个值。列表中的每个值都有一个顺序排序的编号，MySQL中存入的是这个编号，而不列表中的值。 如果ENUm类型加上了NOT NULL属性，其默认值为取值列表的第1个元素。如果不加NOT NULL属性，ENUm类型将允许插入NULL，而且NULL为默认值。 实例一： CREATE TABLE IF NOT EXISTS `test`.`enum_tbl`( `a` ENUM('male','female'), `b` ENUM('true','false') NOT NULL ); INSERT INTO `test`.`enum_tbl` VALUES('male', 'true'),(NULL, 'false'), (NULL, NULL),(20, 20); SELECT * FROM `enum_tbl`; 4.1.4.4 SET类型 基本形式如下： 属性名 set('值1','值2','值3'...'值n'); 其中，'属性名'参数指定字段名称；'值n'参数列表中的第n个值,这些值末尾的空格将会被系统直接删除。其基本形式与ENUM类型一样。 SET类型的值可以取列表中一个元素或者多个元素的组合。取多个元素时，不同元素之间用逗号隔开。SET类型的值最多只能是64个元素构成的组合。列表中的每一个值都有一个顺序排列的编号。MySQL中存入的是这个编号，而不是列表中的值。 插入记录时，SET字段里的元素顺序无关紧要。存入MySQL数据库后，数据库系统会自动按照定义时的顺序显示。 实例一： CREATE TABLE IF NOT EXISTS `test`.`set_tbl`( `a` SET('a','b','c','d','e','f','g') ); INSERT INTO `test`.`set_tbl` VALUES('f'),('a,b,c'),('d,e,a'); INSERT INTO `test`.`set_tbl` VALUES('h'); SELECT * FROM `set_tbl`; 4.1.5 二进制类型 二进制类型是在数据库中存储二进制数据的数据类型。二进制类型包括BINARY、VARBINARY、BIT、TINYBLOB、BLOG、MEDIUMBLOB和LONGBLOG。 1. BINARY和VARBINARY类型 2. BIT类型 3. BLOB类型 二进制类型 取值范围 BINARY(M) 字节数为M，允许长度为0~M的定长二进制字符串 VARBINARY(M) 允许长度为0~M的变长二进制字符串， 字节数为值的长度加一 BIT(M) M位二进制数据，M最大值为64 TINYBLOB 可变长二进制数据，最多255个字节 BLOB 可变长二进制数据，最多(2[16]-1)个字节 MEDIUMBLOB 可变长二进制数据，最多(2[24]-1)个字节 LONGBLOB 可变长二进制数据，最多(2[32]-1)个字节 4.1.5.1 BINARY和VARBINARY类型 BINARY和VARBINARY类型都是在创建表时指定了最大长度，其基本形式如下 ： 字符串类型(M) 这与CHAR类型和VARCHAR类型相似。 例如，BINARY(10)就是指数据类型为BINARY类型，其最大长度为10。 BINARY类型的长度是固定的，在创建表是就指定了。不足最大长度的空间由"/0"补全。例如，BINARY(50)就是指定BINARY类型的长度为50。 VARBINARY类型的长度是可变的，在创建表时指定了最大长度。指定好了VARBINARY类型的最大值以后，基长度可以在0到最大长度之间。例如，VARBINARY(50)的最大字节长度是50。但是，不是每条记录的字节长度都是50。在这个最大范围内，使用多少分配多少。VARBINARY类型实际占用的空间为实际长度加一。这样，可以有效的节约系统的空间。 实例一： CREATE TABLE IF NOT EXISTS `test`.`binary_tbl`( `a` BINARY(4), `b` VARBINARY(4) ); INSERT INTO `test`.`binary_tbl` VALUES('d','d'); SELECT length(a), length(b) FROM `test`.`binary_tbl`; SELECT a, a='d', a='d/0/0/0', b, b='d', b='b/0/0/0' FROM `test`.`binary_tbl`; 4.1.5.2 BIT类型 BIT类型也是创建表时指定了最大长度，其基本形式如下： BIT(M) 其中，'M'指定了该二进制的数的最大字节长度为M，M的最大值为64。例如，BIT(4)就是数据类型BIT类型，长度为4.若字段的类型BIT(4)，存储的数据是从0到15。因为，变成二进制以后，15的值为1111，其长度为4。如果插入的值为16，其二进制数为10000，长度为5，超过了最大长度。因此大于等于16的数是不能插入到BIT(4)类型的字段中的。在查询BIT类型的数据时，要用BIN(字段名+0)来将值转换为二进制显示。 向表中插入0、8、14等值后，查询其结果。 实例一： CREATE TABLE IF NOT EXISTS `test`.`bit_tbl`( `a` BIT(4) ); INSERT INTO `test`.`bit_tbl` VALUES(0),(8),(14); SELECT * FROM `test`.`bit_tbl`; SELECT bin(a+0) FROM `test`.`bit_tbl`; 4.1.5.3 BLOB类型 BLOB类型是一种特殊的二进制类型。BLOB可以用来保存数据量很大的二进制数据，如图片等。BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。这几种BLOB类型最大的区别就是能够保存的最大长度不同。LONGBLOB的长度最大，TINYBLOB的长度最小。 BLOB类型与TEXT类型很类似。不同点在于BLOB类型用于存储二进制数据，BLOB类型数据是根据其二进制编码进行比较和排序。而TEXT类型是文本模式进行比较和排序的。 实例一： CREATE TABLE IF NOT EXISTS `test`.`bit_tbl`( `a` BIT(4) ); INSERT INTO `test`.`bit_tbl` VALUES(0),(8),(14); SELECT * FROM `test`.`bit_tbl`; SELECT bin(a+0) FROM `test`.`bit_tbl`; 4.2 如何选择数据类型 在MySQ中创建表时，需要考虑为字段选择哪种数据类型是最合适的。选择合适的数据类型，会提高数据库的效率。 1. 整数类型和浮点数类型 2. 浮点数类型和定点数类型 3. CHAR类型和VARCHAR类型 4. 时间和日期类型 5. ENUM类型和SET类型 6. TEXT类型和BLOB类型 4.2.1 整数类型和浮点数类型 整数类型和浮点数类型最大的区别在于能否表达小数。整数类型不能表示小数，而浮点数类型可以表示小数。不同的整数类型的取值范围不同。TINYINT类型的取值范围是0~255。如果字段的最大值不超过255，那选择TINYINT类型就足够了。BIGINT类型的取值范围最大，最常用的整数类型是INT类型。 浮点型类型包括FLOAT类型和DOUBLE类型。DOUBLE类型的精度比FLOAT类型高。如果需要精确到小数点后10以上，就应该选择DOUBLE类型，而不应该选择FLOAT类型。 4.2.2 浮点类型和定点数类型 对于浮点数和定点数，当插入值的精度高于实际定义的精度，系统会自动进行四舍五入处理。其目的是为了使该值的精度达到要求。浮点数进行四舍五入不会报警，定点数出现警告。 在未指定精度的情况下，浮点数和定点数有其默认的精度。FLOAT类型和DOUBLE类型默认会保存实际精度。这个精度与操作系统和硬件的精度有关。DECIMAL类型默认整数位为10, 小数位为0，即默认为整数。 在MySQL中，定点数精度比浮点数要高。而且，浮点数会出现误差。如果要对数据的精度要求比较高，应该选择定点数。 4.2.3 CHAR类型和VARCHAR类型 CHAR类型的长度是固定的，而VARCHAR类型的长度是在范围内可变的。因此，VARCHAR类型占用的空间比CHAR类型小。而且，VARCHAR类型比CHAR类型灵活。 对于长度变化比较大的字符串类型，最好选择VARCHAR类型。 虽然，CHAR类型的占用空间比较大，但是CHAR类型的处理速度比VARCHAR快。因此，对于长度变化不大和查询速度要求较高的字符串类型，最好选择CHAR类型。 4.2.4 时间和日期类型 YEAR类型只表示年份。如果要记录年份，选择YEAR类型可以节约空间。TIME类型只表示时间。如果只需要记录时间，选择TIME类型最合适的。DATE类型只表示日期。如果只需要记录日期，选择DATE类型是最合适的。 如果需要记录日期和时间，可以选择DATETIME和TIMESTAMP类型。DATETIME类型表示的时间范围比TIMESTAMP类型大。因此，若需要的时候范围比较大，选择DATETIME类型比较合适。TIMESTAMP类型的时间是根据时区来显示的。如果需要显示的时间与时区对应，那就应该选择TIMESTAMP类型。 4.2.5 ENUM类型和SET类型 ENUm类型最多可以有65535个成员，而SET类型最多只能包含64个成员。两者的取值只能在成员列表中选取。ENUM类型只能从成员中选择一个，而SET类型可以选择多个。 因此，对于多个值中选取一个的，可以选择ENUM类型。例如，"性别"字段就可以定义成ENUM类型，因为只能在 "男"和"女"中选取其中一个。对于可以选取多个值的字段，可以选择SET类型。例如，"爱好"字段就可以选择SET类型，因为可能有多种爱好。 4.2.6 TEXT类型和BLOB类型 TEXT类型和BLOB类型很类似。TEXT类型只能存储字符数据。而BLOB类型可以存储二进制数据。如果要存储文章等纯文件的数据，应该选择TEXT类型。如果需要存储图片等二进制的数据，应该选择BLOB类型。 TEXT类型包括TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT类型允许的长度最大。BLOB类型也是如此。 4.3 常见问题及解答 1. MySQL中什么数据类型能够储存路径？ 答:MySQL中，CHAR、VARCHAR、TEXT等字符串类型都可以存储路径。但是，如果路径中使用"/"符号时，这个符号会被过滤。解决办法是，路径中用"/"或者"//"来代替"/"。这样MySQL就不会自动过滤路径分隔字符，可以完整的表示路径。 2. MySQL中如何使用布尔类型？ 答:在SQL标准中，存储BOOL和BOOLEAN类型。MySQL为了支持SQL标准，也是可以定义BOOL和BOOLEAN类型的。但是，BOOL和BOOLEAN类型最后换成是TINYINT(1)。也就是说，在MYSQL中，布尔类型等价于TINYINT(1)。因此，创建表时将一个字段定义成BOOL和BOOLEAN类型，数据库中真实定义的是TINYINT(1)。 CREATE TABLE IF NOT EXISTS `test`.`bool_tbl`( `a` BOOL, `b` BOOLEAN ); INSERT INTO `test`.`bool_tbl` VALUES(true, false), (false, false), (true, true); SELECT * FROM `test`.`bool_tbl`; 3. MySQL中如何存储JPG图片和MP3音乐？ 答：一般情况下，数据库中不直接存储图片和音乐文件。而是存储图片和文件的路径。如果实在需要在MySQL数据库中存储图片和音频文件，就选择BLOB类型。因为，BLOB类型可以用来存储二进制类型的文件。 4.4 帮助关键字 ? show ? select ? datatype ? int ? update ? select create table cats( id int not null auto_increment, pid int not null default 0, name varchar(60) not null default '', desn text not null default '', primary key(id), index name(name,pid) ) default charset=utf8; create table products( id int not null auto_increment, cid int not null default 0, name varchar(60) not null default '', price double(7,2) not null default 0.00, count int not null default 0, desn text, ptim int not null default 0, primary key(id), key pname(name,price) )default charset=utf8; # 插入数据 insert into cats values(null,0,'soft','this is soft'); insert into cats values(null,1,'java','this is java'); insert into cats values(null,1,'php','this is php'); insert into cats values(null,1,'C++','this is C++'); insert into cats values(null,0,'design','this is design'); insert into cats values(null,5,'ps','this is ps'); insert into cats values(null,5,'ai','this is ai'); # 删除数据 truncate cats; # 更新数据 # 查询数据 SELECT [ALL | DISTINCE] {*|TABLE.*|[table.]filed1[as alias1][,[table.]filed1[as alias1]][......]} FROM tableName [] [WHERE ...] [GROUP BY ...] [HAVING ...] [ORDER BY ...] [LIMIT count]5.操作数据库 数据库是指长期存储在计算机、有组织的、可共享的数据集合。简而言之，数据库就是一个存储数据的地方。只是，其存储方式有特定的规律。这样可以方便处理数据。数据库的操作包括创建数据库和删除数据库。这些操作都是数据库管理的基础。 - 创建数据库 - 删除数据库 - 数据库的存储引擎 - 如何选择存储引擎 5.1 创建数据库 创建数据库是指在数据库系统中划分一块空间，用来存储相应的数据。这是进行表操作的基础，也是进行数据库管理的基础。 创建数据库是通过SQL语言CREATE DATABASE命令实现的。 CREATE DATABASE 数据库名; -- MySQL命令以分号(;)或/g结束命令 SHOW DATABASES; -- 用列表显示所有数据库 CREATE DATABASE example; -- 创建数据库 CREATE DATABASE IF NOT EXISTS example; -- 如果没有example数据库则创建example数据库 5.2 删除数据库 删除数据库是指数据库系统中删除已经存在的数据库。删除数据库之后，原来分配的空间将被收回。值得注意的是，删除数据库会删除该数据库中所有的表和所有数据。 删除数据库是通过SQL语句DROP DATABASE命令实现的。 DROP DATABASE 数据库名 DROP DATABASE IF EXISTS example; -- 如果example数据库存在的话删除example数据库 5.3 数据库的存储引擎 MySQL中提到了存储引擎的概念。简而方之，存储引擎就是指表的类型。数据库存储引擎决定了表在计算机中的存储方式。 type=InnoDB engine=InnoDB MyISAM: 优点： 1. OPTIMIZE TABLE 表名; # 整理碎片 2. 强调快速读取操作 缺点：有一些功能不支持 InnoDB: 优点： 1. 支持多功能(事务、外键) 2. 强调快速读取操作 缺点：速度不如MyISAM,占用空间大 5.3.1 MySQL存储引擎简介 存储引擎的概念是MySQL的特点，而且是一种插入式的存储引擎概念。这决定了MySQL数据库中的表可以用不同的方式存储。用户可以根据自己的不同要求，选择不同的存储方式、是进行事务处理等。 使用SHOw ENGINES语句可以查看MySQL数据库支持的存储引擎类型。 SHOW ENGINES /G Engine : MyISAM Support: YES Comment: Default Engine at of MySQL 3.23 with great performance(描述说明) Transaction: NO (是否支持事务) XA: NO (是不否支持分布式XA规范) Savepoints: NO (是否支持保存点) SHOW VARIABLES LIKE 'have%'; SHWO VARIABLES LIKE 'storage_engine'; # 数据库默认引擎[storage:存储] 1. 可以在mysql.ini配置文件中修改数据库默认引擎 default-storage-engine = INNODB 5.3.2 InnoDB存储引擎 InnoDB是MySQL数据库的一个存储引擎。InnoDB给MySQL的表提供了事务、回滚、崩溃修复能力、多版本并发控制的事务安全。在MySQL从3.23.34a开始包含InnoDB存储引擎。InnoDB是MySQL上第一个提供外键约束的引擎。而且InnoDB对事务处理的能力，也是MySQL其他存储引擎所无法与之比拟的。 (1)InnoDB存储引擎中支持自动增长列(AUTO_INCREMET) 自动增长列的值不能为空，且值必须惟一。MySQL中规定自增列必须为主键。在插入值时，如果自动增长列不输入值，则插入的值为自动增长后的值；如果输入的值为0或者空(NULL)，则插入的值也为自动增长后的值；如果插入某个确定的值，且该值在前面没有出现过，则可以直接插入。 (2) InnoDB存储引擎中支持外键(FOREIGN KEY) (3) InnoDB存储引擎中，创建表的表结构存储在.frm文件中。数据和索引存储innodb_data_home_dir和innodb_data_file_path定义的表空间中。 (4) InnoDB存储引擎的优势在于提供良好的事务管理、崩溃修复能力和并发控制。 缺点是读写效率稍差，占用的数据空间相对比较大。 5.3.3 MyISAM存储引擎 MyISAM存储引擎是MySQL中常见的存储引擎，是以前MySQL的默认存储引擎。MyISAM存储引擎是基于ISAM存储引擎发展起来的。MyISAM增加了很多用的扩展。 MyISAM存储引擎的表存储成三个文件。文件的名字与表名相同。扩展名包括frm、MYD和MYI。其中frm为扩展名的文件存储表的结构;MYD为扩展名的文件存储数据,基是MYData的缩写；MYI为扩展名的文件存储索引，其是MyIndex的缩写。 MyISAM存储引擎的优势在于占用空间小、处理速度快。 缺点是不支持事务的完整性和并发性。 5.3.4 MEMORY存储引擎 MEMORY存储引擎是MySQL中的一个特殊的存储引擎。基使用存储在内存中内容来创建表，而且所有数据也放在内存中。这些特性都与InnoDB存储引擎、MyISAM存储引擎不同。 每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为frm类型。该文件的只存储表的结构。而其数据文件，都是存储在内存中。这样有利于对数据的快速的处理，提高整个表的处理效率。值得注意的是，服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要使用了，可以释放这些内存，甚至可以删除不需要的表。 MEMORY表的大小是受到限制的。表的大小主要取决于两个参数，分别是max_rows和max_heap_table_size。 5.3.5 存储引擎的选择 在实际工作中，选择一个合适的存储引擎是一个很复杂的问题。每种存储引擎都有各自的优势，不能笼统的说谁比谁好。 存储引擎的对比 特性 InnoDB MyISAM MEMORY 事务安全 支持 不支持 不支持 数据行锁定 支持 不支持 不支持 外键约束 支持 不支持 不支持 存储限制 64TB 有 有 空间占用 高 低 低 全文索引 不支持 支持 不支持 内存使用 高 低 高 插入数据的速度 低 高 高 对外键的支持 支持 无 无 5.4 MySQL字符集 gb2312-80(双字节6700个) 不推荐 gb13000(93年-23700多个) 不推荐 GBK 可以用，不推荐 GB18030(2000年) 数据库支持比较少 ANSI ISO-8859-1/latin1 UTF-32 USC-2 UTF-16 UTF-8(1-4字节) 强调推荐 GBK 2个字节 UTF8 3个字节 name varchar(12) 6个汉字 GBK name varchar(12) 4个汉字 UTF8 MySQL服务器，数据库，数据表和字段 show character set; 数据库中utf-8 => utf8 desc information_schema.character_sets; MySQL的字符集包括： 字符集：用来定义MySQL存储字符串的方式 36个 校对规则：比较字符串的关系 70 一对多的关系：1个字符集可以对应多个校对规则 show collation like 'gbk%'; show collation like 'utf%'; gbk_chines_ci # ci:不区分大小写 cs:区分大小写 gbk_bin # bin: 二进制比较 /s show variables like 'character_set_server'; show variables like 'collation_server'; # 保存数据是的字符集 create database demo default character set gbk collate gbk_chinese_ci; create table t(id int) engine=InnoDB default character set gbk collate gbk_chinese_ci; # 客户端与服务器端交互时： character_set_client # 客户端字符集 character_set_connection # 连接字符集 character_set_results # 结果字符集 set names 字符集 同时修改以上三个的值 set names gbk; alter database character set gbk; alter table t character set utf8; mysqldump -u root -p --default-character-set=gbk -d demo > c:/demo01.sql 5.5 本章实例 学校需要建立信息化的管理，必须要建立一个信息完备的数据库系统。这个数据库系统中存储着学校的老师、学生、课程安排、考试成绩等各种信息。建立一个名为school的数据库 1. 登录数据库系统 2. 查看已存在的数据库 3. 查看默认存储引擎 4. 创建数据库 5. 删除数据库 Start: 1. mysql -h localhost -u root -p 2. SHOW DATABASES; -- 查看所有数据库 3. SHOW ENGINES /g # 查看所有的存储引擎 SHOW VARIABLES LIKE 'storage_engine'; /* 查看默认的存储引擎 */ 4. CREATE DATABASE [IF NOT EXISTS] school; # 如果没有school数据库创建school数据库 5. SHOW DATABASES; 6. DROP DATABASE [IF EXISTS] school; -- 删除school数据库 5.6 上机实践 题目要求：登录数据库系统以后，创建一个student数据库和teacher数据库。然后再删除teacher数据库。然后查看数据库系统中还存在哪些数据库。 过程如下： (1) 登录数据库 (2) 查看数据库系统中已存在的数据库 (3) 查看该数据库系统支持的存储引擎类型 (4) 创建一个student数据库和teacher数据库 (5) 再次查看数据库系统中已经存在的数据库，确保student和teacher已经存在。 (6) 删除teacher数据库 (7) 再次查看数据库系统已经存在的数据库，确保teacher数据库已经删除。 (8) 删除student数据库 (9) 再次查看数据库系统已经存在的数据库，确保student数据库已经删除。 Start: 1. mysql -h localhost -u root -p 2. SHOW DATABASES; 3. SHOW ENGINES /G 4. CREATE DATABASE [] student /g 5. CREATE DATABASE [] teacher /g 6. SHOW DATABASES; 7. DROP DATABASE [IF EXISTS] teacher; 8. SHOW DATABASES; 7. DROP DATABASE [IF EXISTS] student; 8. SHOW DATABASES; 5.7 常见问题及解答 1. 如何修改默认存储引擎? (1) 使用配置向导，修改默认存储引擎。 (2) 修改配置文件my.ini，default_storage_engine=INNODB修改即可 2. 如何选择存储引擎？ CREATE TABLE `test`.`test` ( `a` INT NOT NULL ) ENGINE = MYISAM ; 5.8 本章习题 1. 练习在MySQL数据库系统中创建一个名为worker的数据库。创建成功后，删除该数据库。 2. 练习用三种不同的方法找出你所使用的MySQL数据库默认存储引擎。 3. 存储引擎InnoDB、MyISAM和MEMORY各有什么优缺点？6.创建、修改和删除表 表是数据库存储数据的基本单位、一个表包含若干个字段或记录、表的操作包括创建新表、修改表和删除表。这些操作都是数据库管理中最基本，也是最重要的操作。在这一章中将讲解如何在数据库中操作表。 - 创建表方法 - 表的完整性约束条件 - 查看表结构的方法 - 修改表的方法 - 删除表的方法 6.1 创建表方法 创建表是指在已存在的数据库中建立新表。这是建立数据库最重要的一步，是进行其他操作的基础。 6.1.1 创建表的语法形式 MySQL中，创建表是通过SQL语句CREATE TABLE实现的。 CREATE TABLE 表名( 属性名 数据类型 [完整性约束条件], 属性名 数据类型 [完整性约束条件], ...... 属性名 数据类型 [完整性约束条件], )[表类型] [表字符集]; SQL是不区分大小写 命名规范： 1. 命名富有意义(英文或英文组合) 2. 自定义名称使用小写 3. MySQL语句使用大写 1. mysql -h localhost -u root -p 2. SHOW DATABASES; 3. CREATE DATABASE IF NOT EXISTS example 4. CREATE TABLE IF NOT EXISTS text1 ( id INT, name VARCHAR(20); gender BOOLEAN, ) Engine = MyISAM; 5. DESCRIBE test1 /g -- 查看example表的结构 完整性约束条件表 PRIMARY KEY 标识该属性为该表的主键，可以唯一的标识对应的元组 FOREIGN KEY 标识该属性为该表的外键，是与之联系的某表的主键 NOT NULL 标识该属性不能为空 UNIQUE 标识该属性的值是唯一的 AUTO_INCREMENT 标识该属性的值自动增加，这是MySQL的SQL语句的特色(null,0) DEFAULT 标识该属性设置默认值(not null defualt 0,not null default 0.0,not null default '') id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMAKRY KEY, name VARCHAR(20) NOT NULL DEFUALT '', height DOUBLE(10,2) NOT NULL DEFAULT 0.00, age INT NOT NULL DEFUALT 0, sex CHAR(4) NOT NULL DEFAULT '男', 6.1.2 设置表的主键 主键是表的一个特殊字段。该字段能惟一地标识该表中的每条信息。主键和记录的关系，如同身份证和人的关系。主键用来标识每个记录，每个记录的主键值都不同。身份证是用来标明人的身份，每个人都具有惟一的身份证号。设置表的主键指在创建表时设置表的某个字段为该表的主键。 主键的主要目的是帮组MySQL以最快的速度查找到表中的某一条信息。 主键必须满足的条件： 1. 主键必须是唯一的，表中任意两条记录的主键字段的值不能相同； 2. 主键的值是非空值； 3. 主键可以是单一的字段，也可以是多个字段组合。 1. 单字段的主键： CREATE TABLE student1 ( stu_id INT PRIMARY KEY, stu_name VARCHAR(20) NOT NULL, stu_gender BOOLEAN ) Engine = InnoDB; SHOW TABLES; DESCRIBE student1; 2. 多字段主键: CREATE TABLE student2 ( stu_id INT, course_id INT, grade FLOAT, PRIMARY KEY(stu_id, course_id) )Engine = InnoDB; 6.1.3 设置表的外键 外键是表的一个特殊字段。如果字段sno是一个表A的属性，且依赖于表B的主键。那么，称表B为父表，表A为子表，sno为表A的外键。通过sno字段将父表B和子表A建立关联关系。设置表的外键指在创建表设置某个字段为外键。 设置外键的原则：必须依赖于数据库中已存在的父表的主键；外键可以为空值。 外键的作用: 是建立该表与其父表的关联关系。父表中删除某条信息时，子表中与之对应的信息也必须有相应的改变。例如，stu_id就student表的主键，stu_id是grade表的外键。当stu_id为'123'同学退学了，需要从student表中删除该学生的信息。那么，grade表中stu_id为'123'的所有信息也应该同时删除。 CONSTRAINT 外键别名 FOREIGN KEY (属性1.1, 属性1.2...属性1.n); REFERENCES 表名(属性2.1, 属性2.2,...,属性2.n) CREATE TABLE student3 ( id INT PRIMARY KEY, stu_id INT, course_id INT, # 设置外键 CONSTRAINT C_fk FOREIGN KEY(stu_id, course_id) REFERENCES student2(stu_id, course_id) ) Engine = InnoDB; 6.1.4 设置表的非空约束 非空性是指字段的值不能为空值(NULL)。非空约束将保证所有记录中该字段都有值。如果用户新插入的记录中，该字段为空值，则数据库系统会报错。例如，在id字段加上非空约束，id字段的值就不能为空。如果插入记录的id字段的值为空，该记录将不能插入。设置表的非空约束是指在创建表时为表的某些特殊字段加上NOT NULL约束条件。设置非空约束的基本语法规则如下： 属性名 数据类型 NOT NULL CREATE TABLE student4 ( id INT NOT NULL PRIMARY KEY, name VARCHAR(20) NOT NULL, stu_id INT, CONSTRAINT d_fk FOREIGN KEY(stu_id) REFERENCES student2(stu_id) ); 6.1.5 设置表的惟一性约束 惟一性是指所有记录中该字段的值不能重复出现。设置表的惟一性约束是指在创建表时为表的某些特殊字段加上UNIQUE约束条件。唯一性约束将保证所有记录中该字段的值不能重复出现。例如，在id字段加上惟一性约束，所以记录中id字段上不能出现相同的值。例如，在表的id字段加上惟一性约束，那么每条记录的id值都是惟一的，不能出现重复的情况。如果一条的记录的id值都是惟一的，不能出现重复的情况。如果一条的记录的id为'0001'，那么该表中就不能出现另一条记录的id为'0001'。设置惟一性约束的基本语法规则如下： 属性名 数据类型 UNIQUE CREATE TABLE student5 ( id INT NOT NULL PRIMARY KEY, stu_id INT UNIQUE, name VARCHAR(20) NOT NULL ); INSERT INTO `student5` values(1, 10, 'Sue'), (2, 11, 'Lucy'); INSERT INTO `student5` values(2, 10, 'Jack'); 6.1.6 设置表的属性值自动增加 AUTO_INCREMENT是MYSQL数据库中一个特殊的约束条件。其主要用于为表中插入的新记录自动生成惟一的ID。一个表只能有一个字段使用AUTO_INCREMENT约束，且该字段必须为主键的一部分。AUTO_INCREMENT约束的字段可以是任何整数类型(TINYINT、SMALLINT、INT、BIGINT等)。默认情况下，该字段的值是从1开始自增。 设置属性值字段增加的基本语法规则如下： 属性名 数据类型 AUTO_INCREMENT CREATE TABLE student6 ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, stu_id INT UNIQUE, name VARCHAR(20) NOT NULL ); INSERT INTO `student6` values('', 10, 'Sue'), ('', 11, 'Lucy'); INSERT INTO `student6` values('', 12, 'Jack'); 6.1.7 设置表的履的默认值 在创建表时可以指定表中字段的默认值。如果插入一条新的记录时没有为这个字段赋值，那么数据库系统会自动为穿上字段插入默认值。默认值通过DEFAULT关键字来设置的。 属性名 数据类型 DEFAULT 默认值 CREATE TABLE student7 ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, stu_id INT UNIQUE, name VARCHAR(20) NOT NULL DEFAULT 'WHAT', language VARCHAR(20) DEFAULT 'cn', english VARCHAR(20) DEFAULT 'ZERO', math FLOAT DEFAULT 0, computer FLOAT DEFAULT 0 ); INSERT INTO `student7` VALUES('',10,'Sue', 'kr', 'one','',''); INSERT INTO `student7` VALUES('',11,'Jack', '', '','',''); 6.2 查看表结构 查看表结构是指查看数据库中已存在的表的定义。查看表结构的语句包括DESCRIBE语句和SHOW CREATE TABLE语句。通过这两个语句，可以查看表的字段名、字段的数据类型、完整性约束条件等。 6.2.1 查看表基本结构语句DESCRIBE MySQL中，DESCRIBE语句可