MySQL教程
Mysql是最流行的关系型数据库管理系统,在WEB应用方面MySQL是最好的RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一。下面分享的MySQL教程主要是面向想学习MySQL的初学者,在通过本教程的学习后,能够对MySQL有一个初步的认识并能比较熟练地操作MySQL数据库。
一、MySQL安装(Window7 安装)
步骤1、在任何版本的 Windows 默认安装是现在比以前要容易得多,MySQL巧妙地打包安装程序。只需下载安装包,随地把它解压缩,并运行 mysql.exe.
这里点击:DOWNLOAD 链接,进入一下页面:
步骤2、这里点击:DOWNLOAD 链接,进入一下页面:
步骤3、这里要求注册网站帐号,但可以直接通过。如上点击 "No thanks, just start my download",继续下一步:
提示下载保存文件,下载完成后(本教程中下载完成的文件是:mysql-5.6.25-winx64.zip)解压文件放到目录:D:\software 下,这是一个免安装包,这里不需要安装步骤。
在本教程中MySQL执行文件在:D:\software\mysql-5.6.25-winx64\bin, 它通过从命令提示符第一次进入测试MySQL服务器。转到mysqld服务器的位置,这里进入 D:\software\mysql-5.6.25-winx64\bin,然后输入mysqld.exe,
注:或直接双击文件:mysqld.exe 启动MySQL服务,如果不启动数据库,以下的步骤无法操作;
如果一切顺利,会看到有关启动和InnoDB一些消息。如果没有看到,可能有权限问题。请确保保存数据的目录可以访问任何根据用户(可能是MySQL)运行数据库进程。
MySQL不会自己添加到开始菜单,并没有特别漂亮的GUI的方式来停止服务器。 因此,如果倾向于通过双击可执行 mysqld 启动服务器,应该记住通过并用 mysqladmin,任务列表,任务管理器或其他 Windows 特定方式来执行。
二、验证MySQL安装
MySQL已经被成功安装后,基础表已经被初始化,并且服务器已经启动,可以通过一些简单的测试验证。
三、使用中mysqladmin工具程序来获取服务器状态
使用 mysqladmin 来检查服务器版本。在目录 D:\software\mysql-5.6.25-winx64\bin
D:\software\mysql-5.6.25-winx64\bin>mysqladmin --version
看到结果如下:
mysqladmin Ver 8.42 Distrib 5.6.25, for Win64 on x86_64
如果看到这样的消息,则有可能是在安装了一些问题,需要一些帮助解决它。
四、使用MySQL客户端执行简单的SQL命令
可以使用MySQL客户端使用 mysql 命令连接到 MySQL 服务器。此时,不需要给任何密码,因为默认情况下它被设置为空。
所以,只需要使用下面的命令:
D:\software\mysql-5.6.25-winx64\bin>mysql
应该出现一个mysql>提示符。 现在,已连接到MySQL服务器,可以在MySQL的命令>提示符下执行所有SQL,如下:
mysql>SHOW DATABASES;+----------+| Database |+----------+| mysql || test |+----------+2 rows in set (0.13 sec)
五、安装后的步骤:
MySQL附带了一个空密码有的root用户。成功后安装了数据库和客户端,需要进行如下设置root密码:
D:\software\mysql-5.6.25-winx64\bin>mysqladmin -u root password "123456";
注:
1. 关闭正在运行的MySQL服务。 2. 打开DOS窗口,转到 D:\software\mysql-5.6.25-winx64\bin 目录。 3. 输入mysqld --skip-grant-tables 回车。--skip-grant-tables 的意思是启动MySQL服务的时候跳过权限表认证。 4. 再开一个DOS窗口(因为刚才那个DOS窗口已经不能动了),转到mysql\bin目录。 5. 输入mysql回车,如果成功,将出现MySQL提示符 >。 6. 连接权限数据库: use mysql; 。 6. 改密码:update user set password=password("123456") where user="root";(别忘了最后加分号) 。 7. 刷新权限(必须步骤):flush privileges; 8. 退出 quit。 9. 注销系统,再进入,使用用户名root和刚才设置的新密码 123456 登录。
现在使MySQL服务器的连接,那么使用下面的命令:
D:\software\mysql-5.6.25-winx64\bin>mysql -u root -p Enter password: ******
六、运行和关闭MySQL服务器
首先检查MySQL服务器正在运行与否。在资源管理器查看有没有mysqld的进程,如果MySQL正在运行,那么会看到列出来的 mysqld 进程。如果服务器没有运行,那么可以使用下面的命令来启动它:
D:\software\mysql-5.6.25-winx64\bin>mysqld 2015-07-30 22:59:20 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2015-07-30 22:59:20 0 [Note] mysqld (mysqld 5.6.25) starting as process 3028 ...
现在,如果想关闭已经运行的 MySQL 服务器,那么可以使用下面的命令做到这一点:
D:\software\mysql-5.6.25-winx64\bin>mysqladmin -u root -p shutdown Enter password: ******
七、设置MySQL用户帐户
要添加一个新用户到 MySQL,只需要在数据库中的新记录添加到用户表:mysql.user
下面是添加新用户:yiibai 的例子,给定 SELECT, INSERT 和 UPDATE权限并使用密码:yiibai123; SQL 查询是:
D:\software\mysql-5.6.25-winx64\bin>mysql -u root -p Enter password: mysql>use mysql; Database changed mysql>INSERT INTO user (host, user, password, select_priv, insert_priv, update_priv) VALUES ('localhost', 'yiibai', PASSWORD('123456'), 'Y', 'Y', 'Y'); Query OK, 1 row affected (0.20 sec) mysql>FLUSH PRIVILEGES; Query OK, 1 row affected (0.01 sec) mysql>SELECT host, user, password FROM user WHERE user = 'yiibai'; +-----------+---------+------------------+ | host | user | password | +-----------+---------+------------------+ | localhost | yiibai | *59A8740AAC5DBCB2907F38891BE42957F699CB77 | +-----------+---------+------------------+ 1 row in set (0.00 sec)
当要添加一个新用户,记得要PASSWORD()函数加密MySQL新使用的密码。正如在上面的例子中看到密码为mypass,进行加密后为:*59A8740AAC5DBCB2907F38891BE42957F699CB77
注意最后要使用 FLUSH PRIVILEGES 语句。这告诉服务器重新加载授权表。如果不使用它,那么将无法使用新的用户帐户连接到MySQL服务器,至少在服务器重新启动后才可以。
也可以通过在用户设置表以下几列的值为'Y',指定给新用户的权限,在执行INSERT查询后,也可以在以后使用UPDATE查询更新它们:
Select_priv
Insert_priv
Update_priv
Delete_priv
Create_priv
Drop_priv
Reload_priv
Shutdown_priv
Process_priv
File_priv
Grant_priv
References_priv
Index_priv
Alter_priv
下面我们来创建一个数据库:tutorials,使用以下命令:
D:\software\mysql-5.6.25-winx64\bin>mysql -u root -p password; Enter password:mysql>create database tutorials default character set utf8 collate utf8_general_ci;
添加用户帐户的另一种方式是通过使用GRANT SQL命令; 下面的例子将增加用户zara 并使用密码zara123 为特定数据库:tutorials
D:\software\mysql-5.6.25-winx64\bin>mysql -u root -p password; Enter password: mysql>use mysql; Database changed mysql>GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ->ON tutorials.* ->TO 'yiibai'@'localhost' ->IDENTIFIED BY '123456';
这也将创建一条记录在MySQL数据库中的表: user
注意: MySQL不会终止命令,直到给一个分号(;)在SQL命令的结尾。
八、
my.ini 文件配置
大多数情况下,不需要去修改这个文件。默认情况下,它会具有以下项:
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
在这里,可以指定错误日志为其它的目录,否则不应该更改此文件中的任何内容。
九、管理 mysql 命令
这里是重要的MySQL命令,经常在MySQL数据库的管理或工作中使用:
USE Databasename : 用于选择在MySQL工作区指定的数据库。
SHOW DATABASES: 列出了MySQL数据库管理系统中的所有可访问的数据库。
SHOW TABLES: 显示已经选择数据库中的表的命令。
SHOW COLUMNS FROM tablename: 显示属性,属性类型,关键信息,NULL是否被允许,默认值和其它的表信息。
SHOW INDEX FROM tablename: 提供所有指标的详细信息表,其中包括PRIMARY KEY.
SHOW TABLE STATUS LIKE tablename\G: 报告MySQL的数据库管理系统的性能和统计数据的详细信息。
十、使用MySQL二进制连接MySQL
可以使用MySQL二进制在命令提示符下建立MySQL数据库的连接。
【MySQL教程示例】
下面是一个简单的例子,从命令提示符连接MySQL服务器:
D:\software\mysql-5.6.25-winx64\bin>mysql -u root -p Enter password:
注意,这里密码为空,直接回车就就进入mysql>命令提示符下,能够执行任何SQL命令。以下是上述命令的结果:
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.25 MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
在上面的例子中,我们使用 root 用户,但可以使用任何其他用户。任何用户将能够执行所有的SQL操作(前提这个用户有对应执行SQL权限)。
任何时候使用exit命令在mysql>提示符下,从MySQL数据库断开。
mysql>exitBye
十一、MySQL创建数据库
需要特殊权限创建或删除一个MySQL数据库。因此,假如有权使用 root 用户,就可以用mysql中的mysqladmin来创建数据库。
MySQL教程示例:
下面是一个简单的例子,创建名为 tutorials 的数据库。
D:\software\mysql-5.6.25-winx64\bin>mysqladmin -u root -p create tutorials Enter password:<回车>
或
D:\software\mysql-5.6.25-winx64\bin>mysql -u root -p Enter password: mysql>create database yiibai_tutorials1; mysql>create database yiibai_tutorials2;
这将创建一个MySQL数据库:yiibai_tutorials1 和 yiibai_tutorials2,使用下面命令查看结果:
mysql>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | yiibai_tutorials1 | | yiibai_tutorials2 | +--------------------+ 6 rows in set (0.00 sec)
十二、MySQL删除数据库
需要特殊权限来创建或删除一个MySQL数据库。因此,假如你有机会获得root用户来登录,可以用mysql中mysqladmin二进制来创建任何数据库。
在删除任何数据库时要注意,因为删除数据库时所有的数据在数据库中。
下面是一个例子,用来删除前面的章节中所创建的数据库:
D:\software\mysql-5.6.25-winx64\bin>mysqladmin -u root -p drop yiibai_tutorials1 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 'yiibai_tutorials1' database [y/N] y Database "yiibai_tutorials1" dropped或使用:
mysql>drop database yiibai_tutorials1; Query OK, 0 rows affected (0.01 sec)
使用下面命令看删除后,数据库的列表情况:
mysql>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | yiibai | +--------------------+ 6 rows in set (0.00 sec)
连接到MySQL服务器后,则需要选择特定的数据库的来工作。这是因为可能有多个数据库可使用在MySQL服务器上。
十三、从命令提示符选择MySQL数据库
这是非常简单的,从MySQL>提示符下选择一个特定的数据库。可以使用SQL命令的 user 来选择一个特定的数据库。
MySQL教程示例:
下面是一个例子,选择数据库为 test :
D:\software\mysql-5.6.25-winx64\bin>mysql -u root -p Enter password: mysql>use test; Database changed mysql>
现在,我们已经选择 test 数据库,后续所有操作将在 test 数据库上执行。
注意: 所有的数据库名,表名,表中的字段名称是区分大小写的。所以,我们必须使用适当的名称,在给定任何SQL命令。
十四、MySQL表字段类型
正确地定义的表中的字段在数据库的整体优化是非常重要的。我们应该只使用真正需要使用类型和字段的大小; 如果知道只使用2个字符,就不使用10个字符宽定义一个字段。这些类型的字段(或列),也被称为数据类型,数据存储这些字段之中。
MySQL使用许多不同的数据类型,总体上分为三类:数字,日期,时间和字符串类型。
数字数据类型
MySQL使用所有标准的ANSI SQL数字数据类型,所以,如果在学习MySQL之前,有接触过其它不同的数据库系统,那么这些定义看起来很熟悉。下面列出了常见的数字数据类型及其说明:
INT - 正常大小的整数,可以带符号。如果是有符号的,它允许的范围是从-2147483648到2147483647。如果是无符号,允许的范围是从0到4294967295。 可以指定多达11位的宽度。
TINYINT - 一个非常小的整数,可以带符号。如果是有符号,它允许的范围是从-128到127。如果是无符号,允许的范围是从0到255,可以指定多达4位数的宽度。
SMALLINT - 一个小的整数,可以带符号。如果有符号,允许范围为-32768至32767。如果无符号,允许的范围是从0到65535,可以指定最多5位的宽度。
MEDIUMINT - 一个中等大小的整数,可以带符号。如果有符号,允许范围为-8388608至8388607。 如果无符号,允许的范围是从0到16777215,可以指定最多9位的宽度。
BIGINT - 一个大的整数,可以带符号。如果有符号,允许范围为-9223372036854775808到9223372036854775807。如果无符号,允许的范围是从0到18446744073709551615. 可以指定最多20位的宽度。
FLOAT(M,D) - 不能使用无符号的浮点数字。可以定义显示长度(M)和小数位数(D)。这不是必需的,并且默认为10,2。其中2是小数的位数,10是数字(包括小数)的总数。小数精度可以到24个浮点。
DOUBLE(M,D) - 不能使用无符号的双精度浮点数。可以定义显示长度(M)和小数位数(D)。 这不是必需的,默认为16,4,其中4是小数的位数。小数精度可以达到53位的DOUBLE。 REAL是DOUBLE同义词。
DECIMAL(M,D) - 非压缩浮点数不能是无符号的。在解包小数,每个小数对应于一个字节。定义显示长度(M)和小数(D)的数量是必需的。 NUMERIC是DECIMAL的同义词。
日期和时间类型
MySQL的日期和时间数据类型包括:
DATE - 以YYYY-MM-DD格式的日期,在1000-01-01和9999-12-31之间。 例如,1973年12月30日将被存储为1973-12-30。
DATETIME - 日期和时间组合以YYYY-MM-DD HH:MM:SS格式,在1000-01-01 00:00:00 到9999-12-31 23:59:59之间。例如,1973年12月30日下午3:30,会被存储为1973-12-30 15:30:00。
TIMESTAMP - 1970年1月1日午夜之间的时间戳,到2037的某个时候。这看起来像前面的DATETIME格式,无需只是数字之间的连字符; 1973年12月30日下午3点30分将被存储为19731230153000(YYYYMMDDHHMMSS)。
TIME - 存储时间在HH:MM:SS格式。
YEAR(M) - 以2位或4位数字格式来存储年份。如果长度指定为2(例如YEAR(2)),年份就可以为1970至2069(70〜69)。如果长度指定为4,年份范围是1901-2155,默认长度为4。
字符串类型
虽然数字和日期类型比较有意思,但存储大多数数据都可能是字符串格式。 下面列出了在MySQL中常见的字符串数据类型。
CHAR(M) - 固定长度的字符串是以长度为1到255之间个字符长度(例如:CHAR(5)),存储右空格填充到指定的长度。 限定长度不是必需的,它会默认为1。
VARCHAR(M) - 可变长度的字符串是以长度为1到255之间字符数(高版本的MySQL超过255); 例如: VARCHAR(25). 创建VARCHAR类型字段时,必须定义长度。
BLOB or TEXT - 字段的最大长度是65535个字符。 BLOB是“二进制大对象”,并用来存储大的二进制数据,如图像或其他类型的文件。定义为TEXT文本字段还持有大量的数据; 两者之间的区别是,排序和比较上存储的数据,BLOB大小写敏感,而TEXT字段不区分大小写。不用指定BLOB或TEXT的长度。
TINYBLOB 或 TINYTEXT - BLOB或TEXT列用255个字符的最大长度。不指定TINYBLOB或TINYTEXT的长度。
MEDIUMBLOB or MEDIUMTEXT - BLOB或TEXT列具有16777215字符的最大长度。不指定MEDIUMBLOB或MEDIUMTEXT的长度。
LONGBLOB 或 LONGTEXT - BLOB或TEXT列具有4294967295字符的最大长度。不指定LONGBLOB或LONGTEXT的长度。
ENUM - 枚举,这是一个奇特的术语列表。当定义一个ENUM,要创建它的值的列表,这些是必须用于选择的项(也可以是NULL)。例如,如果想要字段包含“A”或“B”或“C”,那么可以定义为ENUM为 ENUM(“A”,“B”,“C”)也只有这些值(或NULL)才能用来填充这个字段。
十五、MySQL创建表
表的创建命令需要:
表的名称
字段名称
定义每个字段(类型、长度等)
语法
下面是通用的SQL语法用来创建MySQL表:
CREATE TABLE table_name (column_name column_type);
现在,我们将在 test 数据库中创建以下表。
create table tutorials_tbl( tutorial_id INT NOT NULL AUTO_INCREMENT, tutorial_title VARCHAR(100) NOT NULL, tutorial_author VARCHAR(40) NOT NULL, submission_date DATE, PRIMARY KEY ( tutorial_id ) );
在这里,一些数据项需要解释:
字段使用NOT NULL属性,是因为我们不希望这个字段的值为NULL。 因此,如果用户将尝试创建具有NULL值的记录,那么MySQL会产生错误。
字段的AUTO_INCREMENT属性告诉MySQL自动增加id字段下一个可用编号。
关键字PRIMARY KEY用于定义此列作为主键。可以使用逗号分隔多个列来定义主键。
通过命令提示符来创建表
在mysql>提示符下,创建一个MySQL表这是很容易的。使用 SQL 命令 CREATE TABLE 来创建表。
下面是一个例子,创建一个表: tutorials_tbl:
D:\software\mysql-5.6.25-winx64\bin>mysql -u root -p Enter password: mysql>use tutorials; Database changed mysql>CREATE TABLE tutorials_tbl( ->tutorial_id INT NOT NULL AUTO_INCREMENT, ->tutorial_title VARCHAR(100) NOT NULL, ->tutorial_author VARCHAR(40) NOT NULL, ->submission_date DATE, ->PRIMARY KEY ( tutorial_id ) ->); Query OK, 0 rows affected (0.16 sec) mysql>
注: MySQL不会终止命令,直到给一个分号(;)表示SQL命令结束。
十六、MySQL删除表
删除现有MySQL表这是很容易的,但必须非常小心,当删除表后丢失的数据将不能恢复。
语法
这是用来删除MySQL表的通用SQL语法:
DROP TABLE table_name ;
从命令行提示符删除表
这需要只是在 MySQL>提示符下执行DROP TABLE SQL命令。
下面是一个例子,它将删除表:tutorials_tbl:
root@host# mysql -u root -p Enter password: mysql>use test; Database changed mysql>DROP TABLE tutorials_tbl; Query OK, 0 rows affected (0.8 sec) mysql>
十七、MySQL插入数据
想要将数据插入到MySQL表,需要使用SQL INSERT INTO命令。 可以通过用mysql>提示符或通过使用像PHP脚本将任何数据插入到MySQL表。
语法
这里是INSERT INTO命令将数据插入到MySQL表的通用SQL语法:
INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );
要插入字符串类型数据,则需要双或单引号保留到所有的值,例如:- "value".
这将使用SQL 的INSERT INTO命令将数据插入到MySQL表:tutorials_tbl
MySQL教程示例
下面的例子将创建3条记录到表:tutorials_tbl
root@host# mysql -u root -p password; Enter password: mysql>use use; Database changed mysql>INSERT INTO tutorials_tbl (tutorial_title, tutorial_author, submission_date) VALUES ("Learn PHP", "Paul", NOW()); Query OK, 1 row affected (0.01 sec) mysql>INSERT INTO tutorials_tbl ->(tutorial_title, tutorial_author, submission_date) ->VALUES ->("Learn MySQL", "Saya", NOW()); Query OK, 1 row affected (0.01 sec) mysql>INSERT INTO tutorials_tbl ->(tutorial_title, tutorial_author, submission_date) ->VALUES ->("JAVA Tutorial", "yiibai", '2015-05-06'); Query OK, 1 row affected (0.01 sec) mysql>
注意: 请注意,所有的箭头符号(->)不是SQL命令的一部分; 它表示一个新行,是由MySQL提示符按下回车键没有给出一个分号,命令自动创建在行尾。
在上面的例子中,未提供 tutorial_id 对应的值,因为在创建表时它会自动创建,这个字段我们给了AUTO_INCREMENT选项。因此MySQL会自动分配插入ID的值。 这里, NOW() 是MySQL函数,返回当前的日期和时间。
十八、MySQL SELECT查询
SQL SELECT命令用于从MySQL数据库获取数据。可以在MySQL>提示符使用这个命令,以及任何像PHP的脚本和语言等。
语法
下面是通用的SQL的SELECT命令语法,从MySQL表获取数据:
SELECT field1, field2,...fieldN table_name1, table_name2...[WHERE Clause][OFFSET M ][LIMIT N]
可以使用分隔的一个或多个逗号从多个表,以及使用WHERE子句包括各种条件,但WHERE子句是SELECT命令的可选部分
可以在一个SELECT命令指定读取一个或多个字段
可以指定星号(*)代替选择的字段。在这种情况下,将返回所有字段
可以指定任意的条件在 WHERE 子句后面
可以使用OFFSET指定一个偏移量,SELECT从那里开始返回记录。默认情况下 offset 的值是 0
可以使用LIMIT属性限制返回的数量
这将使用SQL SELECT命令从MySQL 表 tutorials_tbl 读取数据
MySQL教程示例
下面的例子将从 tutorials_tbl 表返回所有记录:
root@host# mysql -u root -p password; Enter password: mysql>use test; Database changed mysql>SELECT * from tutorials_tbl; +-------------+----------------+-----------------+-----------------+ | tutorial_id | tutorial_title | tutorial_author | submission_date | +-------------+----------------+-----------------+-----------------+ | 1 | Learn PHP | Paul | 2015-07-17 | | 2 | Learn MySQL | Saya | 2015-07-17 | | 3 | JAVA Tutorial | yiibai | 2007-05-06 | +-------------+----------------+-----------------+-----------------+ 3 rows in set (0.25 sec) mysql>