MySQL 教程（超详细，零基础可学、第一篇）

目录一、MySQL数据库概述二、MySQL 连接1、使用 MySQL 二进制方式连接2、使用 PHP 脚本连接 MySQL三、MySQL 创建数据库1、使用 mysqladmin 创建数据库2、使用 PHP脚本 创建数据库四、MySQL 删除数据库1、使用 mysqladmin 删除数据库2、使用 PHP 脚本删除数据库五、MySQL 选择数据库1、从命令提示窗口中选择 MySQL 数据库2、使用 PHP 脚本选择 MySQL 数据库六、MySQL 数据类型1、数值类型2、日期和时间类型3、字符串类型4、枚举与集合类型Enumeration and Set Types5、空间数据类型Spatial Data Types七、MySQL 创建数据表1、通过命令提示符创建表2、使用 PHP 脚本创建数据表一、MySQL数据库概述MySQL 是一个关系型数据库管理系统由瑞典 MySQL AB 公司开发目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统关联数据库将数据保存在不同的表中而不是将所有数据放在一个大仓库内这样就增加了速度并提高了灵活性。MySQL 是开源的目前隶属于 Oracle 旗下产品。MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。MySQL 使用标准的 SQL 数据语言形式。MySQL 可以运行于多个系统上并且支持多种语言。这些编程语言包括 C、C、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。MySQL 对 PHP 有很好的支持PHP 是很适合用于 Web 程序开发。MySQL 支持大型数据库支持 5000 万条记录的数据仓库32 位系统表文件最大可支持 4GB64 位系统支持最大的表文件为8TB。MySQL 是可以定制的采用了 GPL 协议你可以修改源码来开发自己的 MySQL 系统。二、MySQL 连接1、使用 MySQL 二进制方式连接您可以使用 MySQL 二进制方式进入到 mysql 命令提示符下来连接 MySQL 数据库格式如下mysql -u root -p参数说明-u参数用于指定用户名。-p参数表示需要输入密码。root表示数据库默认下载安装的用户名如果未修改的话实例以下是从命令行中连接 mysql 服务器的简单实例[roothost]# mysql -u root -p Enter password:******按照提示输入密码默认密码root并按下 Enter 键。在登录成功后会出现mysql命令提示窗口你可以在上面执行任何 SQL 语句。以上命令执行后登录成功输出结果如下:Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2854760 to server version: 5.0.9 Type help; or h for help. Type c to clear the buffer.在以上实例中我们使用了 root 用户登录到 MySQL 服务器当然你也可以使用其他 MySQL 用户登录。如果用户权限足够任何用户都可以在 MySQL 的命令提示窗口中进行 SQL 操作。成功连接到 MySQL 后你可以在命令行中直接执行 SQL 查询。列出所有可用的数据库SHOW DATABASES;选择要使用的数据库USE your_database;列出所选数据库中的所有表SHOW TABLES;退出mysql命令提示窗口可以使用exit命令如下所示mysql EXIT; Bye或者使用mysql QUIT;或者按下Ctrl D在 Unix/Linux 系统中。2、使用 PHP 脚本连接 MySQLPHP 提供了 mysqli_connect() 函数来连接数据库。该函数有 6 个参数在成功链接到 MySQL 后返回连接标识失败返回 FALSE 。语法mysqli_connect(host, username, password, dbname,port, socket);参数说明参数描述host可选。规定主机名或 IP 地址。username可选。规定 MySQL 用户名。password可选。规定 MySQL 密码。dbname可选。规定默认使用的数据库。port可选。规定尝试连接到 MySQL 服务器的端口号。socket可选。规定 socket 或要使用的已命名 pipe。你可以使用 PHP 的 mysqli_close() 函数来断开与 MySQL 数据库的链接。该函数只有一个参数为 mysqli_connect() 函数创建连接成功后返回的 MySQL 连接标识符。语法bool mysqli_close ( mysqli $link )本函数关闭指定的连接标识所关联的到 MySQL 服务器的非持久连接。如果没有指定 link_identifier则关闭上一个打开的连接。**提示**通常不需要使用 mysqli_close()因为已打开的非持久连接会在脚本执行完毕后自动关闭。实例你可以尝试以下实例来连接到你的 MySQL 服务器:连接 MySQL?php $dbhost localhost; // mysql服务器主机地址 $dbuser root; // mysql用户名 $dbpass root; // mysql用户名密码 $conn mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die(Could not connect: . mysqli_error()); } echo 数据库连接成功; mysqli_close($conn); ?三、MySQL 创建数据库我们可以在登陆 MySQL 服务后使用create命令创建数据库语法如下:CREATE DATABASE 数据库名;以下命令简单的演示了创建数据库的过程数据名为 user:[roothost]# mysql -u root -p Enter password:****** # 登录后进入终端 mysql create DATABASE user;建数据库的基本语法如下CREATE DATABASE [IF NOT EXISTS] database_name [CHARACTER SET charset_name] [COLLATE collation_name];如果你希望在创建数据库时指定一些选项可以使用 CREATE DATABASE 语句的其他参数例如你可以指定字符集和排序规则实例CREATE?DATABASE?mydatabase ??CHARACTER?SET?utf8mb4 ??COLLATE?utf8mb4_general_ci;如果数据库已经存在执行 CREATE DATABASE 将导致错误。为了避免这种情况你可以在 CREATE DATABASE 语句中添加 IF NOT EXISTS 子句实例CREATE?DATABASE?IF?NOT?EXISTS?mydatabase;1、使用 mysqladmin 创建数据库mysqladmin 是 MySQL 提供的一个用于执行管理任务的命令行工具。通过 mysqladmin你可以执行各种数据库管理操作包括创建数据库。以下是使用 mysqladmin 创建数据库的基本语法mysqladmin -u your_username -p create your_database-u参数用于指定 MySQL 用户名。-p参数表示需要输入密码。create是执行的操作表示创建数据库。your_database是要创建的数据库的名称。使用普通用户你可能需要特定的权限来创建或者删除 MySQL 数据库。我们这边使用 root 用户登录root 用户拥有最高权限可以使用 mysqlmysqladmin命令来创建数据库。执行上述命令后系统会提示你输入密码输入密码后按 Enter 键即可。以下命令简单的演示了创建数据库的过程数据名为 user:[roothost]# mysqladmin -u root -p create user Enter password:******以上命令执行成功后会创建 MySQL 数据库 user。如果你希望在创建数据库时指定字符集和排序规则可以使用-default-character-set和-default-collation参数mysqladmin -u your_username -p create your_database–default-character-setutf8mb4–default-collationutf8mb4_general_ci以上代码创建一个使用 utf8mb4 字符集和 utf8mb4_general_ci 排序规则的数据库。请注意在执行这些命令时请确保 MySQL 服务器正在运行并且你有足够的权限执行这些操作。如果你希望使用 mysqladmin 连接到 MySQL 服务器执行其他管理任务例如查看服务器状态、重启服务器等可以使用以下形式的命令mysqladmin -u your_username -p your_command在这里your_command 是你希望执行的具体管理命令。例如要查看 MySQL 服务器的状态可以使用mysqladmin -u your_username -p status这将要求你输入密码并显示有关服务器状态的信息。2、使用 PHP脚本 创建数据库PHP 使用 mysqli_query 函数来创建或者删除 MySQL 数据库。该函数有两个参数在执行成功时返回 TRUE否则返回 FALSE。语法mysqli_query(connection,query,resultmode);参数描述connection必需。规定要使用的 MySQL 连接。query必需规定查询字符串。resultmode可选。一个常量。可以是下列值中的任意一个MYSQLI_USE_RESULT如果需要检索大量数据请使用这个MYSQLI_STORE_RESULT默认实例以下实例演示了使用 PHP 来创建一个数据库创建数据库?php $dbhost localhost; // mysql服务器主机地址 $dbuser root; // mysql用户名 $dbpass root; // mysql用户名密码 $conn mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die(连接错误: . mysqli_error($conn)); } echo 连接成功br /; $sql CREATE DATABASE RUNOOB; $retval mysqli_query($conn,$sql ); if(! $retval ) { die(创建数据库失败: . mysqli_error($conn)); } echo 数据库 RUNOOB 创建成功 ; mysqli_close($conn); ?四、MySQL 删除数据库使用普通用户登陆 MySQL 服务器你可能需要特定的权限来创建或者删除 MySQL 数据库所以我们这边使用 root 用户登录root 用户拥有最高权限。在删除数据库过程中务必要十分谨慎因为在执行删除命令后所有数据将会消失。drop 命令删除数据库drop 命令格式DROP DATABASE database_name; -- 直接删除数据库不检查是否存在 或 DROP DATABASE [IF EXISTS] database_name;参数说明IF EXISTS是一个可选的子句表示如果数据库存在才执行删除操作避免因为数据库不存在而引发错误。database_name是你要删除的数据库的名称。例如删除名为 RUNOOB 的数据库– 直接删除数据库不检查是否存在mysql DROP DATABASE RUNOOB;– 删除数据库如果存在的话DROP DATABASE IF EXISTS RUNOOB;**注意**在执行删除数据库操作前请确保你确实想要删除数据库及其所有数据因为该操作是不可逆的。为了避免误操作通常建议在执行删除之前备份数据库。1、使用 mysqladmin 删除数据库你也可以使用 MySQLmysqladmin命令在终端来执行删除命令。以下是使用 mysqladmin 删除数据库的命令mysqladmin -u your_username -p drop your_databaseyour_username是 MySQL 用户名your_database是要删除的数据库的名称。执行此命令后系统会提示输入密码输入密码后按 Enter 键即可删除数据库。以下实例删除数据库 RUNOOB(该数据库在前一章节已创建)[roothost]# mysqladmin -u root -p drop user Enter password:******执行以上删除数据库命令后会出现一个提示框来确认是否真的删除数据库Dropping the database is potentially a very bad thing to do. Any data stored in the database will be destroyed. Do you really want to drop the user database [y/N] y Database user dropped2、使用 PHP 脚本删除数据库PHP使用 mysqli_query 函数来创建或者删除 MySQL 数据库。该函数有两个参数在执行成功时返回 TRUE否则返回 FALSE。语法mysqli_query(connection,query,resultmode);参数描述connection必需。规定要使用的 MySQL 连接。query必需规定查询字符串。resultmode可选。一个常量。可以是下列值中的任意一个MYSQLI_USE_RESULT如果需要检索大量数据请使用这个MYSQLI_STORE_RESULT默认实例以下实例演示了使用PHP mysqli_query函数来删除数据库删除数据库?php $dbhost localhost; // mysql服务器主机地址 $dbuser root; // mysql用户名 $dbpass root; // mysql用户名密码 $conn mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die(连接失败: . mysqli_error($conn)); } echo 连接成功br /; $sql DROP DATABASE RUNOOB; $retval mysqli_query( $conn, $sql ); if(! $retval ) { die(删除数据库失败: . mysqli_error($conn)); } echo 数据库 RUNOOB 删除成功 ; mysqli_close($conn); ?五、MySQL 选择数据库在你连接到 MySQL 数据库后可能有多个可以操作的数据库所以你需要选择你要操作的数据库。1、从命令提示窗口中选择 MySQL 数据库在mysql提示窗口中可以很简单的选择特定的数据库。在 MySQL 中要选择要使用的数据库可以使用USE语句以下是基本的语法USE database_name;参数说明database_name是你要选择的数据库的名称。选择来数据库后你的后续 SQL 查询和操作在指定的数据库database_name上执行。实例以下实例选取了数据库 RUNOOB:[roothost]# mysql -u root -p Enter password:****** mysql use RUNOOB; Database changed mysql执行以上命令后你就已经成功选择了 RUNOOB 数据库在后续的操作中都会在 RUNOOB 数据库中执行。在命令行中你可以通过以下方式选择数据库mysql -u your_username -p -D your_database-D参数用于指定要选择的数据库。例如mysql -u root -p -D RUNOOB在输入密码后你将进入 MySQL 提示符并且任何后续的查询和操作都将在 RUNOOB 数据库上执行。请确保选择的数据库存在否则你将收到错误消息。你可以使用SHOW DATABASES;查询可用的数据库确保你要选择的数据库在列表中。2、使用 PHP 脚本选择 MySQL 数据库PHP 提供了函数 mysqli_select_db 来选取一个数据库。函数在执行成功后返回 TRUE 否则返回 FALSE 。语法mysqli_select_db(connection,dbname);参数描述connection必需。规定要使用的 MySQL 连接。dbname必需规定要使用的默认数据库。实例以下实例展示了如何使用 mysqli_select_db 函数来选取一个数据库选择数据库?php $dbhost localhost; // mysql服务器主机地址 $dbuser root; // mysql用户名 $dbpass 123456; // mysql用户名密码 $conn mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die(连接失败: . mysqli_error($conn)); } echo 连接成功; mysqli_select_db($conn, RUNOOB ); mysqli_close($conn); ?六、MySQL 数据类型MySQL 中定义数据字段的类型对你数据库的优化是非常重要的。MySQL 支持多种类型大致可以分为三类数值、日期/时间和字符串(字符)类型。1、数值类型MySQL 支持所有标准 SQL 数值数据类型。这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。关键字INT是INTEGER的同义词关键字DEC是DECIMAL的同义词。BIT数据类型保存位字段值并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。作为 SQL 标准的扩展MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。类型大小范围有符号范围无符号用途TINYINT1 Bytes(-128127)(0255)小整数值SMALLINT2 Bytes(-32 76832 767)(065 535)大整数值MEDIUMINT3 Bytes(-8 388 6088 388 607)(016 777 215)大整数值INT或INTEGER4 Bytes(-2 147 483 6482 147 483 647)(04 294 967 295)大整数值BIGINT8 Bytes(-9,223,372,036,854,775,8089 223 372 036 854 775 807)(018 446 744 073 709 551 615)极大整数值FLOAT4 Bytes(-3.402 823 466 E38-1.175 494 351 E-38)0(1.175 494 351 E-383.402 823 466 351 E38)0(1.175 494 351 E-383.402 823 466 E38)单精度浮点数值DOUBLE8 Bytes(-1.797 693 134 862 315 7 E308-2.225 073 858 507 201 4 E-308)0(2.225 073 858 507 201 4 E-3081.797 693 134 862 315 7 E308)0(2.225 073 858 507 201 4 E-3081.797 693 134 862 315 7 E308)双精度浮点数值DECIMAL对DECIMAL(M,D) 如果MD为M2否则为D2依赖于M和D的值依赖于M和D的值小数值2、日期和时间类型表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个零值当指定不合法的MySQL不能表示的值时使用零值。TIMESTAMP类型有专有的自动更新特性将在后面描述。类型大小( bytes)范围格式用途DATE31000-01-01/9999-12-31YYYY-MM-DD日期值TIME3‘-838:59:59’/‘838:59:59’HH:MM:SS时间值或持续时间YEAR11901/2155YYYY年份值DATETIME8‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’YYYY-MM-DD hh:mm:ss混合日期和时间值TIMESTAMP4‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC结束时间是第2147483647秒北京时间2038-1-19 11:14:07格林尼治时间 2038年1月19日 凌晨 03:14:07YYYY-MM-DD hh:mm:ss混合日期和时间值时间戳3、字符串类型字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。类型大小用途CHAR0-255 bytes定长字符串VARCHAR0-65535 bytes变长字符串TINYBLOB0-255 bytes不超过 255 个字符的二进制字符串TINYTEXT0-255 bytes短文本字符串BLOB0-65 535 bytes二进制形式的长文本数据TEXT0-65 535 bytes长文本数据MEDIUMBLOB0-16 777 215 bytes二进制形式的中等长度文本数据MEDIUMTEXT0-16 777 215 bytes中等长度文本数据LONGBLOB0-4 294 967 295 bytes二进制形式的极大文本数据LONGTEXT0-4 294 967 295 bytes极大文本数据注意char(n) 和 varchar(n) 中括号中 n 代表字符的个数并不代表字节个数比如 CHAR(30) 就可以存储 30 个字符。CHAR 和 VARCHAR 类型类似但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR不同的是它们包含二进制字符串而不要非二进制字符串。也就是说它们包含字节字符串而不是字符字符串。这说明它们没有字符集并且排序和比较基于列值字节的数值值。BLOB 是一个二进制大对象可以容纳可变数量的数据。有 4 种 BLOB 类型TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。有 4 种 TEXT 类型TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型可存储的最大长度不同可根据实际情况选择。4、枚举与集合类型Enumeration and Set TypesENUM: 枚举类型用于存储单一值可以选择一个预定义的集合。SET: 集合类型用于存储多个值可以选择多个预定义的集合。5、空间数据类型Spatial Data TypesGEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION: 用于存储空间数据地理信息、几何图形等。七、MySQL 创建数据表创建 MySQL 数据表需要以下信息表名表字段名定义每个表字段的数据类型语法以下为创建 MySQL 数据表的 SQL 通用语法CREATE TABLE table_name ( column1 datatype, column2 datatype, ... );参数说明table_name是你要创建的表的名称。column1,column2, … 是表中的列名。datatype是每个列的数据类型。以下是一个具体的实例创建一个用户表users实例CREATE?TABLE?users?( ? ? id?INT?AUTO_INCREMENT?PRIMARY?KEY, ? ? username?VARCHAR(50)?NOT?NULL, ? ? email?VARCHAR(100)?NOT?NULL, ? ? birthdate?DATE, ? ? is_active?BOOLEAN?DEFAULT?TRUE );实例解析id: 用户 id整数类型自增长作为主键。username: 用户名变长字符串不允许为空。email: 用户邮箱变长字符串不允许为空。birthdate: 用户的生日日期类型。is_active: 用户是否已经激活布尔类型默认值为 true。以上只是一个简单的实例用到了一些常见的数据类型包括 INT, VARCHAR, DATE, BOOLEAN你可以根据实际需要选择不同的数据类型。AUTO_INCREMENT 关键字用于创建一个自增长的列PRIMARY KEY 用于定义主键。如果你希望在创建表时指定数据引擎字符集和排序规则等可以使用CHARACTER SET和COLLATE子句实例CREATE?TABLE?mytable?( ? ? id?INT?PRIMARY?KEY, ? ? name?VARCHAR(50) )?CHARACTER?SET?utf8mb4?COLLATE?utf8mb4_general_ci;以上代码创建一个使用 utf8mb4 字符集和 utf8mb4_general_ci 排序规则的表。以下例子中我们将在 RUNOOB 数据库中创建数据表 runoob_tbl实例CREATE?TABLE?IF?NOT?EXISTS?runoob_tbl( ? ?runoob_id?INT?UNSIGNED?AUTO_INCREMENT, ? ?runoob_title?VARCHAR(100)?NOT?NULL, ? ?runoob_author?VARCHAR(40)?NOT?NULL, ? ?submission_date?DATE, ? ?PRIMARY?KEY?(?runoob_id?) )ENGINEInnoDB?DEFAULT?CHARSETutf8;实例解析如果你不想字段为空可以设置字段的属性为NOT NULL如上实例中的 runoob_title 与 runoob_author 字段 在操作数据库时如果输入该字段的数据为空就会报错。AUTO_INCREMENT定义列为自增的属性一般用于主键数值会自动加 1。PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键列间以逗号,分隔。ENGINE设置存储引擎CHARSET设置编码。1、通过命令提示符创建表通过mysql命令窗口可以很简单的创建 MySQL 数据表。你可以使用 SQL 语句CREATE TABLE来创建数据表。实例以下为创建数据表 runoob_tbl 实例:实例roothost# mysql?-u root?-p Enter password:******* mysql?USE?RUNOOB; DATABASE?changed mysql?CREATE?TABLE?runoob_tbl( ? ?-?runoob_id?INT?NOT?NULL?AUTO_INCREMENT, ? ?-?runoob_title?VARCHAR(100)?NOT?NULL, ? ?-?runoob_author?VARCHAR(40)?NOT?NULL, ? ?-?submission_date?DATE, ? ?-?PRIMARY?KEY?(?runoob_id?) ? ?-?)ENGINEInnoDB?DEFAULT?CHARSETutf8; Query OK,?0?ROWS?affected?(0.16?sec) mysql**注意**MySQL 命令终止符为分号;。注意-是换行符标识不要复制。2、使用 PHP 脚本创建数据表你可以使用 PHP 的mysqli_query()函数来创建已存在数据库的数据表。该函数有两个参数在执行成功时返回 TRUE否则返回 FALSE。语法mysqli_query(connection,query,resultmode);参数描述connection必需。规定要使用的 MySQL 连接。query必需规定查询字符串。resultmode可选。一个常量。可以是下列值中的任意一个MYSQLI_USE_RESULT如果需要检索大量数据请使用这个MYSQLI_STORE_RESULT默认实例以下实例使用了PHP脚本来创建数据表创建数据表?php $dbhost localhost; // mysql服务器主机地址 $dbuser root; // mysql用户名 $dbpass 123456; // mysql用户名密码 $conn mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die(连接失败: . mysqli_error($conn)); } echo 连接成功br /; $sql CREATE TABLE runoob_tbl( . runoob_id INT NOT NULL AUTO_INCREMENT, . runoob_title VARCHAR(100) NOT NULL, . runoob_author VARCHAR(40) NOT NULL, . submission_date DATE, . PRIMARY KEY ( runoob_id ))ENGINEInnoDB DEFAULT CHARSETutf8; ; mysqli_select_db( $conn, RUNOOB ); $retval mysqli_query( $conn, $sql ); if(! $retval ) { die(数据表创建失败: . mysqli_error($conn)); } echo 数据表创建成功 ; mysqli_close($conn); ?