MySQL数据库系统.ppt

第9章 MySQL数据库系统 本章重点 (1) MySQL数据库的安装 (2)结构化查询语言（SQL） (3)MySQL中的用户管理 (4)phpMyadmin的使用,动态网站开发与设计,曲阜师大计算机科学学院,9.1 MySQL数据库简介,动态网站开发离不开数据存储，数据存储则离不开数据库。目前应用中流行的后台数据库有MySQL、 SQL Server、Oracle、 Sybase、 DB2、 PostgreSQL、 Informix等。PHP支持几乎全部当前主流的数据库。MySQL是当今“世界上最流行的开源数据库”。权威调查机构Evans数据公司预测，相比其他的开源数据库和闭源数据库，越来越多的开发者将继续选择MySQL。Evans的总裁John Andrews表示，用户对MySQL和其他开源数据库的评价正在赶上甚至超过很多专有商业数据库软件。,曲阜师大计算机科学学院,9.1.1 为什么选择MySQL,PHP和MySQL的搭配无论从性能上还是到易用性上都毫无疑问地成为了开发者的首选。业界普遍的声音认为：“MySQL是一个可靠的数据库系统，无论是在嵌入式或大型群集系统的部署中，还是在基于Web的应用程序领域，用户时常会发现其实自己并不是第一个选用MySQL数据库的先驱者” 。 在PHP中建立了完美的MySQL支持。在PHP中，用来操作MySQL的函数一直是PHP的标准内置函数。开发者只需要用PHP写下短短几行代码，就可以轻松连接到MySQL数据库。PHP还提供了大量的函数来对MySQL数据库进行操作。可以说，用PHP操作MySQL数据库极为简单和高效，这也使得PHPMySQL成为当今最为流行的Web开发语言与数据库搭配之一。,曲阜师大计算机科学学院,9.1.2 MySQL数据库简介,MySQL是开放源代码的，因此任何人都可以在General Public License的许可下下载并根据个性化的需要对其进行修改。MySQL因为其速度、可靠性和适应性而备受关注。MySQL关系型数据库于1998年1月发行第一个版本。它使用系统核心提供的多线程机制提供完全的多线程运行模式，提供了面向C、C+、Eiffel、Java、Perl、PHP、Python等编程语言的编程接口，支持多种字段类型并提供了完整的操作符。 2001年MySQL 4.0版本发布，标志着MySQL已经长大成人。在这个版本中提供了许多新的特性，如新的表定义文件格式、高性能的数据复制功能、更加强大的全文搜索功能等。到MySQL 5.1版本，开始支持存储过程、触发器和视图等功能，性能和效率方面都得到了更大的提升，能满足企业级用户的需求。目前MySQL的最新版本是5.5.17。现在的MySQL已经追赶上来了，相对于庞大的SQL Server它以优越的性能，灵活的易用性和跨平台等特点正在得到更多用户的认可。,曲阜师大计算机科学学院,9.2.1 获取MySQL安装包 9.2.2 安装并配置MySQL 9.2.3 进入MySQL控制台,9.2 MySQL的安装与初始化设置,曲阜师大计算机科学学院,9.3 MySQL中的数据类型,曲阜师大计算机科学学院,9.3.2 字段属性,字段除了必须声明类型之外，还可以有各种属性。如有的字段值不能为空，有的字段可以设成“key（键）”，有的字段可以设成“Auto_increment自增”，有的字段可以规定长度和设置默认值等。这就涉及到MySQL的字段属性。读者将在后面的学习中逐渐接触到不同的字段属性。,曲阜师大计算机科学学院,(1)查询数据。 (2)在表中插入、修改和删除记录。 (3)建立、修改和删除数据对象。 (4)控制对数据和数据对象的存取。 (5)保证数据库一致性和完整性。,9.4 操作MySQL数据库,曲阜师大计算机科学学院,9.4.2 操作数据库,1、创建数据库 CREATE语句可以用来创建新的数据库。根据本章前面讲过的方法，打开命令提示符界面，输入用户名密码登录到MySQL控制台。登录到控制台后光标前面显示“mysql”，在光标处可以直接输入SQL语句来创建一个名为student的数据库。输入以下命令并回车： mysql create database student; SQL语句可以用大写，也可以用小写，还可以大小写混合。本语句执行后会输出： Query OK, 1 row affected (0.08 sec) 这说明语句执行成功。一个名为student的数据库被创建成功。,曲阜师大计算机科学学院,2、显示数据库,创建完数据库后，可以用SHOW语句来查看数据库是否已经被创建。 mysql show databases; 输入命令后回车，列出当前所有数据库。 mysql show databases; +-+ | Database | +-+ | information_schema | | mysql | | student | | test | +-+ 4 rows in set (0.13 sec) 可以看到，student数据库已经创建成功（information_schema、mysql、test三个数据库均为MySQL安装时自动创建的原始数据库）。,曲阜师大计算机科学学院,3、选择数据库 USE语句用于选择一个数据库，使其成为当前数据库，语法如下： use 数据库名； 4、删除数据库 DROPDATABASE语句用于删除一个数据库，语法如下： drop database 数据库名； 如果是删除一个数据库，那么这个数据库下的所有表也将被删除。,曲阜师大计算机科学学院,9.4.3 操作数据表,1、创建数据表 CREATE语句不仅用于创建数据库，还用于在数据库中创建表。继续用CREATE语句在student数据库中创建一个表info。这个表用来存储学生基本信息，一共有3个字段，分别是姓名（name）、性别（sex）、年龄（age）。这3个字段对应的数据类型分别为CHAR、CHAR、TINYINT，长度分别限制在20字节、2字节、2字节以内。 在student数据库中创建表之前，需要首先打开这个数据库： mysql USE student; 此语句用USE命令选定一个要操作的数据库。执行后显示“Database changed”，表示数据库已经打开。 然后输入以下语句并回车： mysql create table info (name char(20), sex char(2), age tinyint(2); 注意每条SQL语句输入完毕后最后要输入“;”，表示输入完成。否则不论输入多少个回车此语句都不会执行，直到遇到分号结尾（也有极个别语句可以不加分号）。 语句执行完毕后显示“Query OK, 0 rows affected (0.14 sec)”。表示语句执行成功。这时候表info已经创建成功。,曲阜师大计算机科学学院,2、往表中插入数据,INSERT语句用来向表中插入新的数据记录。每次插入一条。如要向刚才创建的info表中插入一条各字段值分别为“张三”、“男”、“20”的记录，可以使用下面的语句： mysql insert into info values（“张三”，”男”，20）； 执行后显示“Query OK, 1 row affected (0.08 sec)”，表示语句执行成功。 值得注意的是，在插入数据时，字符串型值要用双引号或者单引号引起来，数值型不用引号（加引号就错了）。而且提供的数据也必须按照表的字段顺序排列，不能颠倒。 在下面的小节中将介绍如何从表中查询数据。在查询之前，先执行几次INSERT语句向表中插入几条信息，这样可以更加形象地说明查询语句的作用。不妨再插入“李四”、“王五”、“赵六”3条记录，这样表中共有4条记录。,曲阜师大计算机科学学院,3、查询数据表,1）查询全部记录全部字段 查询一个表中全部记录，可以用如下语句： mysql select * from info; 这里“*”表示所有字段。info为表名。程序执行后输出： +-+-+-+ | name | sex | age | +-+-+-+ | 张三 | 男 | 20 | | 李四 | 男 | 18 | | 王五 | 女 | 18 | | 赵六 | 女 | 17 | +-+-+-+ 4 rows in set (0.02 sec) 可见刚才插入的4条数据全部被查询出来了。,曲阜师大计算机科学学院,可以通过指定具体的字段和排序方式，来过滤掉不需要显示的字段。如要查询所有记录的姓名、年龄两个字段值，可以用如下语句： mysql select name,age from info; 执行后输出 +-+-+ | name | age | +-+-+ | 张三 | 20 | | 李四 | 18 | | 王五 | 18 | | 赵六 | 17 | +-+-+ 4 rows in set (0.00 sec),2）查询全部记录的部分字段值,曲阜师大计算机科学学院,通过SELECT语句的WHERE子句，可以查询某些满足指定条件的记录，这在查询中极为常用。如要查询所有年龄小于19的记录，可以用如下语句： mysql select * from info where age select * from infor where age19 and sex = “女”; 该语句执行后，将只输出满足条件的王五、赵六的两条记录。,3）查询满足某个条件的记录,曲阜师大计算机科学学院,4）查询某些记录，并对结果进行排序,使用SELECT语句的ORDER BY子句可以对查询结果进行排序。如查询所有性别为“女”的记录，并且将结果按照年龄从小到大排序。 mysql select * from info where sex= “女” order by age asc; 运行后输出结果如下： +-+-+-+ | name | sex | age | +-+-+-+ | 赵六 | 女 | 17 | | 王五 | 女 | 18 | +-+-+-+ 2 rows in set (0.01 sec) 如果要将从小到大改为从大到小，则将命令中的“asc”改为“desc”即可。,曲阜师大计算机科学学院,4、修改数据表,（1）ALTER语句用来修改一个表的定义，也就是说修改表自身。如修改表的名字，修改表中某个字段的名字、属性、类型等（也可以用于修改数据库的部分属性）。看下面的例子： mysql alter table info change name xingming char (20); 本语句将表info的name字段名修改为xingming，类型和长度不变。又如： mysql alter table info add addr char (50); 本语句在info表中又增加了一个名为addr，类型char，长度为50的新字段。 mysql alter table info drop addr; 本语句删除了表info中的addr字段。,曲阜师大计算机科学学院,（2）UPDATE语句修改表中现有记录,1）修改全部记录的某个字段的值 例如要将info表中全部记录的年龄都修改成25，可以使用下面的语句： mysql update info set age=25; 这时如果用SELECT语句查询此表，会看到所有记录的age字段都变成了25。（读者可以执行SELECT * FROM info；语句来查看表中的数据，SELECT语句的详细用法将在9.4.4节介绍）。 此外，还可以一次修改多个字段的值。如除了要将所有记录的age字段修改成25，还要将所有sex记录修改为“女”，可以用如下语句： mysql update info set age = 25, sex = “女”; 也就是说，多个字段之间用逗号隔开，可以一次修改多个字段的值。,曲阜师大计算机科学学院,2）修改满足某条件的记录,通过WHERE子句指定的条件，可以修改满足指定条件的记录的值。如要将姓名为“张三”的记录的年龄修改成23，可以用如下语句： mysql update info set age = 23 where name = “张三”; 执行之后再用SELECT语句查询此表，会发现“张三”的年龄为23，其他记录的年龄的均为25。 同样可以用逗号隔开的方法，修改满足指定条件的记录的多个字段。,曲阜师大计算机科学学院,（3）删除表中的记录,DELETE语句用来删除表中的记录。 可以一次删除全部记录，也可以删除满足指定条件的记录。 1）删除表中的全部记录。 如要删除表info中全部记录，可以用以下语句： mysqldelete from info; 该语句执行后表info中的全部记录都会被删除。可以看出该命令是比较危险的，不小心很容易造成误删，带来意想不到的后果。因此使用此命令时应尽量注意。 2）删除满足条件的记录 如果要删除表info中性别为“女”的记录，可以用如下命令： mysql delete form info where sex = “女”;,曲阜师大计算机科学学院,5、删除数据表,DROP语句用来删除一个数据表。例如： mysql drop table tbl1; 删除名为tbl1的表（删除前需要先打开数据库）。,曲阜师大计算机科学学院,9.5 MySQL数据库的管理,9.5.1 MySQL的用户管理 MySQL的用户管理，指的是哪个用户可以连接服务器，从哪里连接，连接后能做什么。新安装的MySQL，一般USER表中有两个用户，分别是root和匿名用户。匿名用户即不需要用户名和密码即可进入系统的用户。 MySQL用户包括普通用户和root用户。这两种用户的权限是不一样的。root用户是超级管理员，拥有所有的权限。root用户的权限包括创建用户、删除用户、修改普通用户的密码等管理权限。而普通用户只拥有创建该用户时赋予它的权限。用户管理包括管理用户的帐户、权限等。安装。 在MySQL数据库中，可以使用CREATE USER语句来创建新的用户，也可以直接在mysql.user表中添加用户。还可以使用GRANT语句来新建用户。,曲阜师大计算机科学学院,1、用CREATE USER语句来新建普通用户,语法如下： create user usernamehost identified by password; 说明：username - 你将创建的用户名, host - 指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost， 如果想让该用户可以从任意远程主机登陆，可以使用通配符%。 password - 该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器. 例子: create user doglocalhost identified by 123456; create user pig192.168.1.101_ idendified by 123456; create user pig% identified by 123456; create user pig% identified by ; create user pig%;,曲阜师大计算机科学学院,2、用INSERT语句来新建普通用户，,语法如下： mysqluse mysql； mysqlinsert into mysql.user(主机名称,用户名称,用户密码,权限1,权限2, ) values(host,user,password,select_priv,)； mysqlflush privileges; 例如： mysqluse mysql； mysql insert into user (host,user,password) values (localhost,jsjdpt,password(112233); mysqlflush privileges; /刷新系统权限表 这样就创建了一个名为：jsjdpt，密码为：112233的用户。,曲阜师大计算机科学学院,3、用GRANT语句来新建普通用户,最好用的方法是使用GRANT语句，因为这样更精确，错误少。从MySQL 3.22.11起提供了GRANT；它的主要用途是来给帐户授权的，但也可用来建立新帐户并同时授权。语法如下： grant priv_type (column_list) , priv_type (column_list) . on tbl_name | * | *.* | db_name.* to user_name identified by password , user_name identified by password . with grant option 这是完整的GRANT语句语法结构。看起来比较复杂。使用本命令可以一次创建多个MySQL账号。在实际应用中一般一次只创建一个用户，这样语法结构就可以简化为： grant priv_type (column_list) on tbl_name | * | *.* | db_name.* to user_name identified by password 而到了具体的语句中，还可以继续简化。如： mysql grant all on db1.* to “nie” identified by “123456”; 此语句执行之后创建用户nie，密码123456，该用户对数据库db1拥有全部权限。,曲阜师大计算机科学学院,grant语句的语法结构进行简要分析。 grant关键字，表示授权语句开始。 priv_type权限类型。可以是select/delete/update/create/drop/alter等任意一种。如果是全部权限，可以用all privileges，并且可以简写为all。 on tbl_name | * | *.* | db_name.*声明此用户可以操作哪些数据库以及哪些表。声明可以使用以下4种方法之一： tbl_name：直接指定表名，如info。 *：任意表。 *.*：任意数据库的任意表。 db_name.*：指定数据库的所有表，如db1。 to user_name：指定用户名。即要创建的账号的用户名，如上例中的“nie”。 identified by：此项目为可选。指定账号所对应的密码。应用引号引起来。密码提交后会自动被加密。,曲阜师大计算机科学学院,grant语句实例：,mysqlgrant select,insert,update,delete,create,drop on student.course to zhangsan219.218.22.187 identified by 1234; 给来自219.218.22.187的用户zhangsan分配可对数据库student的course表进行select,insert,update,delete,create,drop等操作的权限，并设定口令为1234。 mysqlgrant all privileges on student.* to zhangsan219.218.22.187 identified by 1234; 给来自219.218.22.187的用户zhangsan分配可对数据库student所有表进行所有操作的权限，并设定口令为1234。,曲阜师大计算机科学学院,9.5.2 删除用户和取消权限,数据库管理员不仅能创建用户并赋予权限，当然也能取消用户授权和删除用户。REVOKE语句用于取消某个用户的权限，语法如下： revoke privileges(columns) on what from user; 其中，privileges 是要取消的权限，user是要被取消权限的用户。例如： mysqlrevoke all on *.* from zhangsanlocalhost; query ok, 0 rows affected(0.00sec) revoke 语句只能取消用户权限，不能删除用户。即使取消了某个用户的所有权限，该用户依然可以连接到服务器。,曲阜师大计算机科学学院,要删除用户，需使用delete语句，将该用户的记录从mysql数据库的user表中删除。语法如下： delete from user where user=”user_name” and host=”host_name”; 例如： mysqluse mysql database changed mysqldelete from user where user=”zhangsan” and host=”localhost”; mysqlflush privileges; query ok, 1 row affected(0.02sec) 删除了本地机器上的用户“zhangsan”。,曲阜师大计算机科学学院,9.5.3 MySQL的密码管理,1、root用户修改自己的密码 root用户拥有很高的权限，因此必须保证root用户的密码的安全。root用户可以通过多种方式来修改密码。本小节将介绍几种root用户修改自己的密码的方法。 （1）使用mysqladmin命令来修改root用户的密码，步骤如下： 选择【开始】菜单中的“运行”命令，在弹出的对话框中输入“cmd”命令，单击“确定”按钮，进入命令行模式，输入： mysqladmin u用户名 p原密码 password 新密码 回车。注意， -u和用户名，-p和原密码之间的空格可以省略，但password和新密码之间的空格不能省略。,曲阜师大计算机科学学院,（2）修改mysql数据库下的user表,语法：mysqlupdate mysql.user set password=password(新密码) where user=“zhangsan“ and host=“localhost“; 实例：mysqlupdate user set password=password(54321) where user=root; mysqlflush privileges; /刷新系统权限表,曲阜师大计算机科学学院,（3）使用SET语句来修改root用户的密码,mysqlset password for usernamehost = password(新密码); 如果是当前登陆用户用set password = password(“新密码“); 实例： mysqlset password for rootlocalhost=password(); mysqlset password for name=password(new password); mysqlset password for pig% = password(“123456“);,曲阜师大计算机科学学院,2、root用户修改普通用户密码,root用户不仅可以修改自己的密码，还可以修改普通用户的密码。root用户登录MySQL服务器后，可以通过SET语句、修改user表和GRANT语句来修改普通用户的密码。本小节介绍root用户修改普通用户密码的方法。 （1）使用SET语句来修改普通用户的密码 mysqlset password for userlocalhost= password(“new_password”); （2）修改mysql数据库下的user表 mysqlupdate mysql.user set password=password(“new_password”) where user= and host=; mysqlflush privileges; （3）用GRANT语句来修改普通用户的密码 mysqlgrant priv_type on database.table to user identified by passwordpassword,useridentified by passwordpassword.,曲阜师大计算机科学学院,3、普通用户修改密码,普通用户也可以修改自己的密码。这样普通用户就不需要每次需要修改密码时都通知管理员。普通用户登录到MySQL服务器后，可以通过SET语句来设置自己的密码。SET语句的基本形式为： mysqlset password=password(new_password);,曲阜师大计算机科学学院,4、root用户密码丢失的解决办法,root用户密码丢失了，会给用户造成很大的麻烦。但是，可以通过某种特殊方法登录到root用户下。然后在root用户下设置新的密码。下面是解决root用户密码丢失的方法，执行步骤如下： （1） KILL掉系统里的MySQL进程： mysqlkillall -term mysqld （2）以不检查权限的方式启动MySQL，启动指令如下： mysqlsafe_mysqld -skip-grant-tables mysql quit 重新启动MySQL，就可以使用新密码登录了。,曲阜师大计算机科学学院,9.6 MySQL的数据管理,数据库中的数据对我们来说是如此重要，以至于我们不得不将有关数据保护的方法从数据库管理中摘出来，用专门的章节来讲解如何保护数据库中的数据。 备份是最简单的保护数据的方法，通过对数据库的备份，可以有效地保护数据，防止系统在遭到破坏时数据丢失，是数据库管理员必须掌握的技术。数据越是重要，数据的变化越频繁，备份越是需要经常进行。当数据库受到损坏时，我们可以用备份恢复原来的数据。,曲阜师大计算机科学学院,9.6.1 MySQL的备份,本节介绍2中最常用的方法。 1、使用mysqldump命令备份 mysqldump是逻辑备份。mysqldump命令能将整个数据库以sql语句的方式导出到一个.sql文本文件中。mysqldump不仅能备份表结构和数据，还可以同时支持myisam和innodb引擎数据库。mysqldump也可以备份单个表、单个库或所有库，还可以只导出表结构。最常见的mysqldump命令是制作整个数据库的一个备份： mysqldump opt databasebackup.sql 如备份student数据库，操作为： 选择“开始”“运行”，输入执行cmd命令，单击“确定”按钮，打开命令提示符窗口，在命令提示符下直接输入 mysqldump uroot p1234 opt studentd:ab.sql 按键回车即可。其中，-u和-p表示连接的用户名和密码，数据文件被备份到d盘的ab.sql中。,曲阜师大计算机科学学院,2、直接拷贝数据库文件,先关闭MySQL服务器，不允许其他用户进行任何更新操作，然后直接把*.frm 表的描述文件、*.MYD 表的数据文件、*.MYI 表的索引文件拷贝到备份的目录下。,曲阜师大计算机科学学院,9.6.2 MySQL的恢复,1、用mysqldump备份的数据库的恢复 选择“开始”“运行”，输入执行cmd命令，单击“确定”按钮，打开命令提示符窗口，在命令提示符下直接输入 mysql uroot p1234 student 进入到数据库中，然后在其后面