第四章 MySQL数据库.ppt

1、生物信息学数据库设计,第四章 MySQL数据库,学习内容,MySQL基础,2,3,数据库,数据库(Database,简称DB)的定义： “按照数据结构来组织、存储和管理数据的仓库” J.Martin给数据库下了一个比较完整的定义：数据库是存储在一起的相关数据的集合，这些数据是结构化的，无有害的或不必要的冗余，并为多种应用服务,4,数据库,数 据 表 存储过程 视 图 .,产品数据库,表,5,数据库的发展史,人工管理阶段 人力手工整理存储数据 文件系统阶段 使用磁盘文件来存储数据 数据库系统阶段 关系型数据库 高级数据库阶段 “关系-对象”型数据库,6,数据库的类型,纯文本数据库： 纯文本数据库

2、是只用空格符、制表符和换行符来分割信息的文本文件。适用于小型应用，对于大中型应用来说它存在诸多限制： 只能顺序访问，不能进行随机访问。 查找数据和数据关系或多用户同时访问进行写操作时非常困难。 关系数据库： 由于纯文本数据库存在诸多局限，所以人们开始研究数据模型，设计各种类型的使用方便的数据库。在数据库的发展史上，最具影响的数据库模型有：层次模型、网状模型和关系模型。其中，关系模型是目前应用最广泛和最有发展前途的一种数据模型，其数据结构简单，当前主流的数据库系统几乎都采用关系模型。,7,数据库的优点,数据按一定的数据模型组织、描述和储存 可为各种用户共享 冗余度较小，节省存储空间 易扩展，编写

3、有关数据库应用程序,8,数据库管理系统,数据库管理系统(Database Management System)是一种操纵和管理数据库的大型软件，是用于建立、使用和维护数据库，简称DBMS。 关系型数据库管理系统称为RDBMS，R指Relation DBMS的作用 它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。,9,DBMS的主要功能,数据定义功能： 提供数据定义语言(DDL) 定义数据库中的数据对象 数据操纵功能: 提供数据操纵语言(DML) 操纵数据实现对数据库的基本操作 (查询、插入、删除和修改),10,数据库的运行管理 保证数据的安全性、完整性 多用户对数据的并发使用 发

4、生故障后的系统恢复 数据库的建立和维护功能(实用程序) 数据库数据批量装载 数据库转储 介质故障恢复 数据库的重组织 性能监视等,DBA的主要职责,11,时下流行的DBMS,Oracle 应用广泛、功能强大，分布式数据库系统 “关系-对象”型数据库 MySQL 快捷、可靠 开源、免费、与PHP组成经典的LAMP组合 SQL Server 针对不同用户群体的五个特殊的版本 易用性好 DB2 应用于大型应用系统，具有较好的可伸缩性,12,数据库系统,数据库系统（Database System，简称DBS）是一个实际可运行的存储、维护和应用系统提供数据的软件系统 数据库系统构成 DBMS DB 应用

5、软件 数据库管理员 用户,13,数据库系统的使用,数据库服务器,响应和提供数据,应用程序,数据库,操作和查询,14,匈牙利命名法,匈牙利命名法由匈牙利程序员发明，他在微软工作了多年，此命名法就是通过微软的各种产品和文档传出来。多数有经验的程序员，不管他们用的是哪门语言，都或多或少在使用它。 基本原则：变量名 = 属性 类型 对象描述； 举例来说，表单的名称为form，那么在匈牙利命名法中可以简写为frm，则当表单变量名称为Switchboard时，变量全称应该为 frmSwitchboard。这样很容易从变量名看出Switchboard是一个表单，同样，如果此变量类型为标签，那么就应命名成 l

6、blSwitchboard。可以看出，匈牙利命名法非常便于记忆，而且使变量名非常清晰易懂，这样，增强了代码的可读性，方便各程序员之间相互交流代码。,数据库对象命名规范,遵循行业规范 当有相关国家/行业强制性数据结构标准规范存在时，用于存储某业务数据的业务表在表名命名上原则上应该遵从标准规定，其表中相关字段的中文名称（即数据项名称）若标准规范上有规定的应遵循规定。此外，若标准规范上对数据项的类型、长度有规定的，原则上也应当遵循或保证能直接兼容保存和访问。,15,字符范围原则 只能使用英文字母、下划线、数字进行命名，首位字符必须是英文字母。 分段命名原则 命名中多个单词间采用下划线分隔，以便阅读同

7、时方便某些工具对数据库对象的映射。如XXX_XXX_XXX，但不限于三段式。 勿用保留词 数据库对象命名不能直接使用数据库保留关键字，但分段中可以使用。如USER不能用于表名、列名等，但是USER_NAME可以用于列名，USER_INFO也可以用于表名。,16,简单命名原则 命名尽可能简单，避免太长的命名，尽量使用缩写形式，但是缩写也要能够表达命名的含义。数据库对象命名总长度不得超过30字节，以免超过数据库命名长度限制。建议每个单词分段长度不要超过6位。 富有含义原则 数据库对象命名通常用能表示其内容或者含义的英文单词或其缩写表示也可用其中文名称各字词的拼音首写字母或者拼音简写方式表示。数字应

8、尽量避免使用。,17,同义性原则 对于同一含义尽量使用相同的单词命名，不管使用英文单词、英文缩写还是拼音首字母，以免引起误解。如TELEPNHOE的A表中表示固定电话号码，在B表中就不应该用于表示移动电话号码。尽量避免同一单词表示多种含义的情况。 命名方式一致原则 在一个系统、一个项目中尽量采用一致的命名方式，都采用英文单词或者拼音首字母。尤其要避免在一个对象命名中同时采用英文单词和拼音首字母。,18,19,20,系统信息类表以T_SYS_开头、要素类表以T_IDX_开头、接口类表以T_INF_开头、临时表以T_TMP_开头、统计分析系统表以T_TJFX_开头等。 T_UserInfo、USE

9、R_INFO、UserInfo、T_用户信息、TB_USER_INFO、TBL_USER_INFO、T$USER$INFO、等都是违反本规范的，正确命名为T_USER_INFO,21,列命名,1. 正确命名：USER_NAME、AUDIT_TIME、AUDIT_USER 2. 错误命名：USERNAME、UserName、C_USER_NAME、人员姓名，违反规范。 3. 错误命名：COMMENT、AUDIT，违反保留字,22,23,MySQL概述,MySQL是一个高性能、多线程、多用户、建立在客户/服务器结构上的开源关系型数据库管理系统 MySQL是世界上最受欢迎的开放源代码数据库，当前全世

10、界有超过1000万的系统使用MySQL MySQL的官方网站为,24,MySQL数据库,什么是MySQL MySQL是一个小型关系型数据库管理系统，开发者为瑞典MySQL AB公司。目前MySQL被广泛地应用在Internet上的中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型 网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。,25,MySQl特征,使用C和C+编写，并使用了多种编译器进行测试，保证源代码的可移植性 支持AIX、FreeBSD、HP-UX、Linux、Mac OS、Novell Netware、OpenBSD、OS/2 Wra

11、p、Solaris、Windows等多种操作系统 为多种编程语言提供了API。这些编程语言包括C、C+、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。 支持多线程，充分利用CPU资源 优化的SQL查询算法，有效地提高查询速度,26,MySQl特征,既能够作为一个单独的应用程序应用在客户端服务器网络环境中，也能够作为一个库而嵌入到其他的软件中提供多语言支持，常见的编码如中文的GB 2312、BIG5，日文的Shift_JIS等都可以用作数据表名和数据列名 提供TCP/IP、ODBC和JDBC等多种数据库连接途径 提供用于管理、检查、优化数据库操作的管理工具 可以处理

12、拥有上千万条记录的大型数据库,27,MySQL5特性,子查询 视图 存储过程 触发器 事务处理 热备份 二进制Bit类型 等等,28,MySQL不足,不能直接处理XML数据 一些功能上支持的不够完善和成熟 不能提供任何OLAP（实时分析系统）功能 等等.,29,30,MySQL应用,与其他的大型数据库例如Oracle、DB2、SQL Server等相比，MySQL自有它的不足之处，如规模小、功能有限（MySQL Cluster的功能和效率都相对比较差）等，但是这丝毫也没有减少它受欢迎的程度。对于一般的个人使用者和中小型企业来说，MySQL提供的功能已经绰绰有余，而且由于MySQL是开放源码软件

13、，因此可以大大降低总体拥有成本。,31,MySQL应用,目前Internet上流行的网站构架方式是LAMP（Linux+Apache+MySQL+PHP），即使用Linux作为操作系统，Apache作为Web服务器，MySQL作为数据库，PHP作为服务器端脚本解释器。由于这四个软件都是自由或开放源码软件（FLOSS)，因此使用这种方式不用花一分钱就可以建立起一个稳定、免费的网站系统。,32,MySQL管理,可以使用命令行工具管理MySQL数据库（命令mysql 和 mysqladmin)，也可以从MySQL的网站下载图形管理工具MySQL Query Browser。,33,MySQL存储引擎

14、,MyISAM Mysql的默认数据库引擎，最为常用。拥有较高的插入，查询速度，但不支持事务 InnoDB 事务型数据库的首选引擎，支持ACID事务，支持行级锁定 BDB 源自Berkeley DB，事务型数据库的另一种选择，支持COMMIT和ROLLBACK等其他事务特性 Memory 所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在Mysql重新启动时丢失 Merge 将一定数量的MyISAM表联合而成一个整体，在超大规模数据存储时很有用,34,MySQL存储引擎,Archive 非常适合存储大量的独立的，作为历史记录的数据。因

15、为它们不经常被读取。Archive拥有高效的插入速度，但其对查询的支持相对较差 Federated 将不同的Mysql服务器联合起来，逻辑上组成一个完整的数据库。非常适合分布式应用 Cluster/NDB 高冗余的存储引擎，用多台数据机器联合提供服务以提高整体性能和安全性。适合数据量大，安全和性能要求高的应用 CSV 逻辑上由逗号分割数据的存储引擎 BlackHole 黑洞引擎，写入的任何数据都会消失，一般用于记录binlog做复制的中继 另外，Mysql的存储引擎接口定义良好。有兴趣的开发者通过阅读文档编写自己的存储引擎。,35,当创建一个新表的时候，你可以通过添加一个ENGINE 或TYP

16、E 选项到CREATE TABLE语句来告诉MySQL你要创建什么类型的表： CREATE TABLE t (i INT) ENGINE = INNODB; CREATE TABLE t (i INT) TYPE = MEMORY; 如果省略掉ENGINE或TYPE选项，默认的存储引擎被使用。一般的默认是MyISAM，但你可以用-default-storage-engine或-default-table-type服务器启动选项来改变它，或者通过设置storage_engine或table_type系统变量来改变。 当MySQL被用MySQL配置向导安装在Windows平台上，InnoDB存储引

17、擎替代MyISAM存储引擎作为替代.,Create table limbs(thing varchar(20),legs int,arms int);,37,事务安全表(TST) 比起非事务安全表 (NTST)有几大优势： 更安全。即使MySQL崩溃或遇到硬件问题，要么自动恢复，要么从备份加事务日志恢复，你可以取回数据。 你可以合并许多语句，并用COMMIT语句同时接受它们全部（如果autocommit被禁止掉）。 你可以执行ROLLBACK来忽略你的改变（如果autocommit被禁止掉）。 如果更新失败，你的所有改变都变回原来。（用非事务安全表，所有发生的改变都是永久的）。 事务安全存储引

18、擎可以给那些当前用读得到许多更新的表提供更好的部署。,38,InnoDB概述 InnoDB具有提交，回滚和崩溃恢复能力的事务安全存储引擎。 InnoDB锁定在行级。 InnoDB支持FOREIGN KEY强制。 InnoDB是为处理巨大数据量时的最大性能设计。它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。 InnoDB存储引擎被完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB存储它的表索引在一个表空间中，表空间可以包含数个文件（或原始磁盘分区）。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统

19、上。 InnoDB默认地被包含在MySQL二进制分发中。Windows Essentials installer使InnoDB成为Windows上MySQL的默认表。 InnoDB被用来在众多需要高性能的大型数据库站点上。著名的Internet新闻站点S运行在InnoDB上。Mytrix, Inc.在InnoDB上存储超过1TB的数据，还有一些其它站点在InnoDB上处理平均每秒800次插入/更新的负荷。,39,查看MySQL的当前存储引擎,查看MySQL提供的存储引擎: mysql show engines; 当前默认的存储引擎: mysql show variable

20、s like %storage_engine%; 表所用引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎): mysql show create table 表名; show create table limbs;,40,MySQL最常见的应用架构,单点（Single），适合小规模应用 复制（Replication），适合中小规模应用 集群（Cluster），适合大规模应用 MySQL新版本中增加了不少新的性能，包括对主键的更高速度的缓存，对子查询的更好的支持，以及应网络约会网站所要求的，基于地理信息的查询。 其同步开发的5.0版本则把目标对准了企业用户，独具以下特点：对外键

21、的良好支持；系统自动报错机制以及对存储过程的充分支持。,集群软件的分类：,集群软件根据侧重的方向和试图解决的问题，分为三大类：高性能集群（High performance cluster，HPC）、负载均衡集群（Load balance cluster， LBC），高可用性集群（High availability cluster，HAC）。,按照集群工作的层面，可分为：数据库服务器集群，应用服务器集群，交换机集群等 按照集群的架构，可分为：共享磁盘型，非共享磁盘型。 按照集群粒度不同：基于IO；基于操作系统；基于数据库实例的；基于每个数据库的等。,高性能集群（High performance

22、cluster，HPC），它是利用一个集群中的多台机器共同完成同一件任务，使得完成任务的速度和可靠性都远远高于单机运行的效果。弥补了单机性能上的不足。该集群在天气预报、环境监控等数据量大，计算复杂的环境中应用比较多；,什么是负载均衡集群 负载均衡集群（Load balance cluster， LBC），它是利用一个集群中的多台单机，完成许多并行的小的工作。一般情况下，如果一个应用使用的人多了，那么用户请求的响应时间就会增大，机器的性能也会受到影响，如果使用负载均衡集群，那么集群中任意一台机器都能响应用户的请求，这样集群就会在用户发出服务请求之后，选择当时负载最小，能够提供最好的服务的这台机器

23、来接受请求并相应，这样就可用用集群来增加系统的可用性和稳定性。这类集群在网站中使用较多；,什么是高可用性集群 高可用性集群（High availability cluster，HAC），它是利用集群中系统 的冗余，当系统中某台机器发生损坏的时候，其他后备的机器可以迅速的接替它来启动服务，等待故障机的维修和返回。最大限度的保证集群中服务的可用性。这类系统一般在银行，电信服务这类对系统可靠性有高的要求的领域有着广泛的应用。,46,MySQL基本操作,mysql、mysqladmin和mysqldump MySQL管理工具和用户操作界面,47,MySQL应用基础,客户端程序和实用工具介绍 myisa

24、mpack：压缩MyISAM表以产生更小的只读表的一个工具 mysql：MySQL命令行工具，通过交互方式输入SQL语句或从文件以批处理模式执行SQL语句。 mysqlaccess：检查访问权限的主机名、用户名和数据库组合的客户端工具。 mysqladmin：执行管理操作的客户端程序，用于创建或删除数据库、重载授权表、将表刷新到硬盘上及重新打开日志文件 mysqlbinlog：从二进制日志读取语句的工具。 mysqlcheck：表维护程序，用于检查、修复、分析及优化表。 mysqldump：将MySQL数据库转储到一个文件（如SQL语句或tab分隔符文本文件）。 mysqlhotcopy：数据

25、库备份程序，可在MySQL服务器运行时快速备份MyISAM或ISAM表。 mysqlimport：数据导入程序，使用LOAD DATA INFILE将文本文件导入MySQL数据库的相关表中。 mysqlshow：显示数据库、表、列及索引相关信息的客户端程序。 perror：显示系统或MySQL错误代码含义的工具。 replace：更改文件中或标准输入中字符串的实用工具。,48,mysql命令,mysql命令 mysql -h host_name -u user_name ppassword h：当连接MySQL服务器不在同台主机时，填写主机名或IP地址 u：登录MySQL的用户名 p：登录My

26、SQL的密码 注意:密码如果写在命令行的时候一定不能有空格。如果使用的系统为linux并且登陆用户名字与MySQL的用户名相同即可不用输入用户名密码，linux默认是以root登陆，windows默认用户是ODBC,49,通用的连接方式语法,MySQL -h localhost -u root p MySQL -u root p MySQL -hlocalhost -uroot pxxx MySQL -uroot pxxx Mysql ucbuser pcbpass Show databases; Show tables;,50,mysql程序常用命令,选定默认数据库：use dbname;

27、显示所有数据库：show databases; 显示默认数据库中所有表：show tables; 放弃正在输入的命令：c 显示命令清单：h 退出mysql程序：q 查看MySQL服务器状态信息：status;,51,处理SQL文件,mysql命令 处理存放在文件里的SQL命令 格式：mysql option dbname file.sql mysql u root p -default-character-set=latin1 dbname backup.sql 注：latin1是MySQL4.0的默认字符集或者utf-8是MySQL5.0的默认字符集，如果不知道使用什么字符集的时候，可以选用

28、这两个其中一个,52,使用的几点提示,字符集 Windows命令窗口默认字符集为cp850或者gbk，因此存在许多字符显示不出来的问题。 可以在mysql命令加入-default-character-set=cp850或gbk选项，cp850字符集需要再追加一个 -character-sets-dir=C:/MySQL/share/characters选项,53,连接数据库,使用mysql命令登录MySQL数据库服务器 语法：mysql -h 主机名或IP地址 -u 用户名 -p 口令 例子：mysql -uroot -p 注意：root的密码默认为空,54,创建数据库（1）,使用show d

29、atabases能够查看MySQL系统中存在的数据库,55,创建数据库（2）,使用create database创建数据库 使用use命令选择数据库 删除数据库 MySQL drop database accounts;,56,创建表,使用create table命令创建表 删除表 mysql drop table acc;,57,创建表（2）,查看当前数据库中的所有表,58,创建表（3）,使用describe（可以缩写为desc）命令查看表的结构 desc limbs;,59,插入数据（1）,acc表的样例数据,60,插入数据（2）,使用insert命令插入数据 使用NULL值,61,查询数

30、据（1）,select命令的格式,62,查询数据 (2),63,查询数据 (3),64,查询数据 (4)select * from limbs where thing=fish;,65,查询数据：排序select thing as “事物”，legs,arms from limbs where thing like f%order by thingdesc;,66,程序数据：降序,67,查询数据：别名,68,查询数据：like（1）,69,查询数据：like（2）,70,查询数据,将表的数据输出到文本文件 Select * from limbs into outfile f:/int.out;

31、 Select count(*) from limbs; 行计数,71,更新数据,C:path C:MySQLMySQL Server 5.5bin C:mysql D cookbook -ucbuser -pcbpass f:limbs.sql Mysql D cookbook ucbuser pcbpass Select * from limbs; 一行输出所有内容 Mysql D cookbook ucbuser -pcbpass e “select * from limbs;select now();select count(*) from limbs”,生成HTML输出 Mysql

32、D cookbook ucbuser -pcbpass H e “select * from limbs;select now();select count(*) from limbs” 禁止头部 Mysql D cookbook ucbuser -pcbpass skip-column-names H e “select * from limbs;select now()”,使长输出行具备可读性 show full columns from limbs; show full columns from limbsG 记录交互式的Mysql会话 T f:/limbs.out select now

33、(); t,使用自定义变量 select name:=thing from limbs where legs=0; select name; select * from limbs where thing=name; set name:=thing from limbs where legs=-1; select name; /? set total=4*7+2; select total;,将MYSQL作为计算器 select 67+6-9/3; select 1=2; select zhang=yang; select zhang=Zhang;,查询服务器版本和数据库名 select ve

34、rsion(),database(); 如何处理NULL值 DROP TABLE IF EXISTS stu_info; # _CREATE_TABLE_ CREATE TABLE stu_info ( idVARCHAR(20), PRIMARY KEY (id), nameVARCHAR(20), class VARCHAR(10) );,INSERT INTO stu_info (name,id,class) VALUES (杨振军,200801001,1); INSERT INTO stu_info (name,id,class) VALUES (吴楠,200801003,2); IN

35、SERT INTO stu_info (name,id,class) VALUES (白云,200801002,null); INSERT INTO stu_info (name,id) VALUES (杨超,200801004，null);,select * from stu_info where class is null; select * from stu_info where class is not null;,80,删除数据delete from stu_info where class is nul;创建视图create or replace view vw_stuinfo a

36、sselect id as 学号,name as 姓名from stu_info;show tables from cookbook;drop view vw_stuinfo;,事务控制,ROLLBACK-回滚; COMMIT- 提交。 提交数据类型： 显式提交、隐式提交及自动提交。 (1) 显式提交 用 COMMIT 命令直接完成的提交为显式提交。 (2) 隐式提交 用 SQL 命令间接完成的提交为隐式提交。这些命令是： ALTER ， AUDIT ， COMMENT ， CONNECT ， CREATE ， DISCONNECT ， DROP ， EXIT ， GRANT ， NOAUDI

37、T ， QUIT ， REVOKE ， RENAME 。,(3) 自动提交 若把 AUTOCOMMIT 设置为 ON ，则在插入、修改、删除语句执行后， 系统将自动进行提交，这就是自动提交。其格式为： SQLSET AUTOCOMMIT=1 ； 所有的 DML 语句都是要显式提交的，也就是说要在执行完DML语句之后，执行 COMMIT 。而其他的诸如 DDL 语句的，都是隐式提交的。也就是说，在运行那些非 DML 语句后，数据库已经进行了隐式提交，例如 CREATE TABLE，在运行脚本后，表已经建好了，并不在需要你再进行显式提交。 在提交事务（commit）之前可以用rollbacl回滚事

38、务。,set autocommit=off; show variables like “autocommit”;/查看自动提交状态 show columns from stu_info; insert into stu_info values(200801005,陈春艳,null);,修改提交方式也可在配置文件中进行 init-connect=set autocommit=off set autocommit=1; select * from stu_info; insert into stu_info values(200801006,邱景璇,3); select * from stu_in

39、fo; rollback; select * from stu_info;,显示修改表所用的引擎 show create table stu_infoG alter table stu_info engine=myisam; select * from stu_info; set autocommit=off; show variables like auto%; desc stu_info;,insert into stu_info values(200800107,彭东海,1); rollback; select * from stu_info; ?,alter table stu_inf

40、o engine=innodb; show variables like autocommit; select * from stu_info; insert into stu_info values(200801008,刘燕霞,2); rollback; commit; select * from stu_info; insert into stu_info values(200801009,饶建安,3);,88,重命名表delete from stu_info where id like %9;alter table stu_info rename stud_info;show table

41、;select * from stu_info;rollback;,为什么myisam中在autocommit=off时修改后直接提交而无法回滚？ 为什么在rename后，修改的数据直接提交？还有那些语句会造成这种情形？ 设置手动提交的条件是什么？,多表查询,创建表 DROP TABLE IF EXISTS stu_grade; create table stu_grade ( id varchar(20), subject varchar(20), primary key(id,subject), score decimal(4,2) );,Insert into stu_grade val

42、ues(200801001,R语言,91.5); Insert into stu_grade values(200801001,生物信息,92); Insert into stu_grade values(200801002,R语言,92.5); Insert into stu_grade values(200801002,生物信息,90.5); Insert into stu_grade values(200801012,生物信息,91);,内连接,只连接匹配的行 select as 姓名,b.subject as 课程,b.score as 成绩 from stu_info

43、a ,stu_grade b where a.id=b.id; select as 姓名,b.subject as 课程,b.score as 成绩 from stu_info a inner join stu_grade b on a.id=b.id;,左外连接,包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行 select as 姓名,b.subject as 课程,b.score as 成绩 from stu_info a left join stu_grade b on a.id=b.id;,右外连接,包含右边表的全部行（

44、不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行 select as 姓名,b.subject as 课程,b.score as 成绩 from stu_info a right join stu_grade b on a.id=b.id;,全外连接,包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。Mysql不支持 select as 姓名,b.subject as 课程,b.score as 成绩 from stu_info a full join stu_grade b;,交叉连接,生成笛卡尔积它不使用任何匹配或者选取条件，而

45、是直接将一个数据源中的每个行与另一个数据源的每个 行都一一匹配 select as 姓名,b.subject as 课程,b.score as 成绩 from stu_info a cross join stu_grade b; select as 姓名,b.subject as 课程,b.score as 成绩 from stud_info a, stu_grade b;,自己生成两个有关联的表，分别进行三种连接。,98,增加列alter table stu_info add birth_place int;alter table stud_info change

46、 birth_place birth_place varchar(15);alter table stud_info modifybirth_place varchar(50);alter table stud_info drop primary key;,99,改变列定义alter table stud_info add primary key(id);,在结果中选取部分行,select * from limbs limit 0,2; select * from limbs limit 3; select * from limbs limit 3,2; 处理重复值,用户权限管理,MySQL用

47、户包括普通用户和root用户。这两种用户的权限是不一样的。root用户是超级管理员，拥有所有的权限。root用户的权限包括创建用户、删除用户、修改普通用户的密码等管理权限。而普通用户只拥有创建该用户时赋予它的权限。用户管理包括管理用户的帐户、权限等。,102,权限表介绍 添加用户和权限 回收权限和删除用户 修改口令,1 权限表,安装MySQL时会自动安装一个名为mysql的数据库。mysql数据库下面存储的都是权限表。用户登录以后，MySQL数据库系统会根据这些权限表的内容为每个用户赋予相应的权限。这些权限表中最重要的是user表、db表和host表。除此之外，还有tables_priv表、c

48、olumns_priv表、proc_priv表等。本节将介绍这些表的内容。,user表,user表是MySQL中最重要的一个权限表。可以使用DESC语句来查看user表的基本结构。user表有39个字段。这些字段大致可以分为四类，分别是用户列、权限列、安全列和资源控制列。本小节将介绍这些字段含义。 1用户列 2权限列 3安全列 4资源控制列,db表和host表,db表和host表也是MySQL数据库中非常重要的权限表。db表中存储了某个用户对一个数据库的权限。db表比较常用，而host表很少会用到。可以使用DESC语句来查看这两个表的基本结构。这两个表的表结构差不多。db表和host表的字段大

49、致可以分为两类，分别是用户列和权限列。 1用户列 2权限列,tables_priv表和columns_priv表,tables_priv表可以对单个表进行权限设置。columns_priv表可以对单个数据列进行权限设置。可以使用DESC语句来查看这两个表的基本结构。本小节将介绍这两个表的内容。 tables_priv表包含8个字段，分别是Host、Db、User、Table_name、Table_priv、Column_priv、Timestamp和Grantor。前四个字段分别表示主机名、数据库名、用户名和表名。Table_priv表示对表进行操作的权限。这些权限包括Select、Inser

50、t、Update、Delete、Create、Drop、Grant、References、Index和Alter。Column_priv表示对表中的数据列进行操作的权限。这些权限包括Select、Insert、Update和References。Timestamp表示修改权限的时间。Grantor表示权限是谁设置的。,procs_priv表,procs_priv表可以存储过程和存储函数进行权限设置。可以使用DESC语句来查看procs_priv表的基本结构。本小节将介绍procs_priv表中各字段的含义。 procs_priv表包含8个字段，分别是Host、Db、User、Routine_n

51、ame、Routine_type、Proc_priv、Timestamp和Grantor。前三个字段分别表示主机名、数据库名和用户名。Routine_name字段表示存储过程或函数的名称。Routine_type字段表示类型。该字段有两个取值，分别是FUNCTION和PROCEDURE。FUNCTION表示这是一个存储函数。PROCEDURE表示这是一个存储过程。Proc_priv字段表示拥有的权限。权限分为3类，分别是Execute，Alter Routine和Grant。Timestamp字段存储更新的时间。Grantor字段存储权限是谁设置的。,2添加用户和权限,在MySQL数据库中，可

52、以使用CREATE USER语句来创建新的用户，也可以直接在mysql.user表中添加用户。还可以使用GRANT语句来新建用户。 1用CREATE USER语句来新建普通用户 2用INSERT语句来新建普通用户 3用GRANT语句来新建普通用户,109,添加用户 (1),使用grant命令添加用户 Grant select,insert,update,delete on cookbook .* to cbuserlocalhost identified by cbpass;,权限管理,权限管理主要是对登录到数据库的用户进行权限验证。所有用户的权限都存储在MySQL的权限表中。数据库管理员要对

53、权限进行管理。合理的权限管理能够保证数据库系统的安全。不当的权限设置可能会给数据库系统带来意想不到的危害。本节将介绍权限管理的内容。,111,权限管理,权限（ privileges） ALL 授予用户如下的所有权限 ALTER 改变表、列和索引 CREATE 创建数据库和表 DELETE 删除表中的记录 DROP 丢弃和删除表和数据库 FILE 读、写文件 INDEX 添加、删除索引 INSERT 向表中插入记录 PROCESS 查看和停止系统进程,112,添加用户 (3),REFERENCES 参考 RELOAD 使用FLUSH语句 SELECT 从表中查询记录 SHUTDOWN 关闭MyS

54、QL数据库 UPDATE 更新表中的记录 USAGE 连接到MySQL数据库，但是没有权限,113,添加用户：例子,例1：创建口令为 zhang的用户zzg，该用户在数据库cookbook的所有表都有select和insert权限，并且能够从任何主机连接到数据库服务器 mysql grant select, insert on cookbook.* to zzglocalhost identified by “zhang”;,114,添加用户：例子,例2：添加口令为45sdg11的用户jane， 该用户在数据库myCompany的表employees上具有ALL权限，并且只能从特定的主机上连接

55、数据库 mysql grant all on myCompany.employees to identified by “45sdg11”; 例3： mysql grant all on myCompany.employees to jane identified by “45sdg11”;,查看权限,show grants for current_user; show grants for rootlocalhost; show grants for zzglocalhost; use mysql show tables;,columns_priv db event f

56、unc general_log help_category help_keyword help_relation help_topic host ndb_binlog_index plugin proc procs_priv proxies_priv servers slow_log tables_priv time_zone time_zone_leap_second time_zone_name time_zone_transition time_zone_transition_type user,用户权限查询,select db,user from db; select user,select_priv,delete_priv, update_priv,insert_priv from user;,3回收权限和删除用户,在MySQL数据库中，可以使用DROP USER语句来删除普通用户，也可以直接在mysql.usr表中删除用户。 1用DROP USER语句来删除普通用户 2用