mysql教案讲解.pdf

《mysql教案讲解.pdf》由会员分享，可在线阅读，更多相关《mysql教案讲解.pdf（22页珍藏版）》请在三一文库上搜索。

1、1、数据库概述 1.1 什么是数据库 数据库 DataBase 就是一个存储数据的仓库。 为了方便数据的存储和管理，它将数据按照特定的规律存储在磁盘上。通过数据库 管理系统，可有效地组织和管理存储在数据库中的数据。 1.2 文件与文件系统 所谓“文件”一般指存储在外部介质上数据的集合，是操作系统（确切地说是文件 系统）管理数据的基本单位。 1.3 文件系统和数据库系统之间的区别和联系 1.3.1 区别 (1) 文件系统用文件将数据长期保存在外存上，数据库系统用数据库统一存储数据； (2) 文件系统中的程序和数据有一定的联系，数据库系统中的程序和数据分离； (3) 文件系统用操作系统中的存取方法

2、对数据进行管理，数据库系统用DBMS 统一管理 和控制数据 (4) 文件系统实现以文件为单位的数据共享，数据库系统实现以记录和字段为单位的数 据共享。 1.3.2 联系 (1) 均为数据组织的管理技术； (2) 均由数据管理软件管理数据，程序与数据之间用存取方法进行转换； (3) 数据库系统是在文件系统的基础上发展而来 1.3.3 读写数据库与读写文件哪个更快 1.4 数据存储方式 学校需要存储学生和教师信息 公司需要存储员工信息和公司的资料 这些数据或者信息是如何存储的？数据存储方式经历了三个阶段：人工管理阶段、 文件系统阶段、数据库系统阶段（注：新兴数据存储系统-例如 google 的 b

3、igtable ）。 1.4.1 人工管理阶段 磁带、磁盘、手工记录 1.4.2 文件系统阶段 数据存储在计算机的磁盘上，数据以文件的形式出现，通过文件系统来管理这些文 件，文件系统通过路径和文件访问文件中的数据 1.4.3 数据库系统阶段 使用专门的数据库来管理数据。用户在数据库系统中建立数据库，然后在数据库中 建立表，最后将数据存储在这些表中-表是数据库存储数据的基本单位。 用户通过数据库管理系统来查询表中的数据。 1.5 常用的数据库 甲骨文的 Oracle：大型数据库， Linux/WINDOWS/Soloras/SUSE Sybase：大型数据库 Mysql：开源，中大型数据库，Li

4、nux/Windows/ 微软 SQL Server ：Windows Postgre SQL ：安全开源免费，中型， Linux Access： SQLITE：小型数据库， Linux/Windows ，嵌入式系统 1.6 数据库技术构成 数据库技术是解决数据存储、管理、统计、查询等处理而使用的一门广泛技术 数据 Data ：是数据库中存储的基本对象，是描述事物的符号， 如数字、文件、图形、 图像等 数据库 Database ，DB：是长期储存在计算机内、有组织的、可共享的数据集合。 数据库管理系统Database Management System ，DBMS：是位于用户与操作系统 之间的

5、管理数据库的软件（定义、管理和维护数据）。MySql 是一中 DBMS。 数据库系统Database System -DBS ：是指在计算机系统中引入数据库后的系统。 其组成如下：计算机系统、数据库、数据库管理系统及其开发工具、人员（数据库管理 员 DBA、终端用户） 通常情况下，把数据库系统简称为数据库。 SQL(Structed Query Language)语言：结构化查询语言，是数据库管理系统通过 SQL 语句来操作数据库中的数据。标准的SQL 命令，比如“ Select”、 “Insert ”、 “Update ”、 “Delete”、“Create ”和 “Drop ”常常被用于完

6、成绝大多数数据库 的操作。 数据库访问技术： ODBC（Open Database Connectivity开放式数据库连接） 是应用程序和数据库系统之间的中间件，用来在数据库管理系统中存取数据的标准 应用程序接口。 它通过使用相应应用平台上和所需数据库对应的驱动程序与应用程序的交互来实 现对数据库的操作，避免了在应用程序中直接调用与数据库相关的操作，从而提供了数 据库的独立性。 JDBC / JDO （Java Data Base Connectivity,java数据库连接） 是一种用于执行SQL 语句的 Java API，可以为多种关系数据库提供统一访问，它由 一组用 Java 语言编写

7、的类和接口组成。JDBC 提供了一种基准， 据此可以构建更高级的 工具和接口，使数据库开发人员能够编写数据库应用程序，同时，JDBC也是个商标名。 C API （C 语言连接数据库） MySQL 提供 C 语言使用的API. C 代码的 API 是随 MySQL 一起发布的 . 它包含在 mysqlclient库中, 可以使 C 程序来访问数据库. 综上所述，数据库技术主要包括：数据库系统、SQL 语句、数据库访问技术 1.7 MySQL逻辑架构 一张好的图片会是你更容易理解MySQL 各个组件的工作方式。 1、最上一层所包含的服务并不是MySQL 所独有的技术。 它们都是服务于C/S 程序

8、或者是这些程序所需要的：连接处理，身份验证，安全性等等。 2、第二层是我们所感兴趣的。这是 MySQL 的核心部分。 包括了查询的解析，分析， 优化，缓存以及所有内置的函数(如日期 ,时间,数学运算 ,加密 )。还有一些功能是涉及到 了存储引擎，如存储过程，触发器，视图等。 3、第三层包括了存储过程。它们负责存储和获取所有存储在MySQL 中的数据。就 像 Linux 众多的文件系统一样。每个存储引擎都有自己的优点和缺陷。服务器是通过存 储引擎 API 来与它们交互的。 这个接口隐藏了各个存储引擎不同的地方。对于查询层尽 可能的透明。这个API 包含了很多底层的操作。如开始一个事物，或者取出有

9、特定主键 的行。存储引擎不能解析SQL，互相之间也不能通信。 仅仅是简单的响应服务器的请求。 连接管理和安全 在服务器内部，每个client 连接都有自己的线程。这个连接的查询都在一个单独的 线程中执行。这个线程存在于一个核心或者CPU 中。服务器缓存了线程，因此不需要 为每个 client 连接单独创建和销毁线程。 当 clients （也就是应用程序）连接到了MySQL 服务器。服务器需要对它进行身份 验证。这个身份验证是基于用户名，主机，以及密码。也使用了 X.509 的 SSL连接。clients 一连接上，服务器就验证它的权限。如这个client 是否可以查询world 数据库下的

10、Country 表的数据。这部分将在独立的章节进行说明。 优化和执行 MySQL 的解析查询是创建了一个内部的结构（一个语法分析树） 。之后就进行了各 种各样的优化。这些优化包括了，查询语句的重写，读表的顺序，索引的选择等等。你 可以通过查询语句的关键词对优化器进行一定的提示。这样做会影响到它的优化方式。 你也可以让服务器对于各种优化进行说明。这样可以得知服务器的运行策略以及为了获 得更好的效率，可以做更多的优化工作，如查询优化等。优化器会在以后的教程中详细 介绍。 优化器是不会关心表所用到的存储引擎的类型，但是存储引擎会影响到服务器优化 查询的方式。 优化器需要知道存储引擎的一些特性，这些操

11、作的消耗以及表数据的一些 统计信息。例如，一些存储引擎支持索引，这对于查询是非常有用的。 在解析查询之前，要查询缓存，这个缓存只能保存查询信息以及结果数据。如果请 求一个查询在缓存中存在，就不需要解析，优化和执行查询了。直接返回缓存中所存放 的这个查询的结果。 MySQL 体系结构介绍： 存储引擎负责管理数据存储，以及MySQL 的索引管理。通过定义的API，MySQL 服务器能够与存储引擎进行通信。每个存储引擎均是1 个继承类，每个类实例作为处 理程序而被引用。 针对需要与特殊表一起工作的每个线程， 处理程序是在1 个处理程序的基础上实例化的。例如，如果3 个连接全都在相同的 表上工作，需要

12、创建3 个处理程序实例。 一旦创建了处理程序实例，MySQL 服务器将向处理程序发送命令，以便执行数据存 储和检索任务，如打开表、操纵行和管理索引等。 能够以累进方式创建定制存储引擎：开发人员能够以只读存储引擎启动，随后添加 对 INSERT、UPDATE 和 DELETE 操作的支持，甚至能够增加对索引功能、事务和其他 高级操作的支持 2. Linux平台下安装配置MySQL 3 MySQL语言 SQL 功能强大，但是概括起来，它可以分成以下几组： DDL（数据定义语言） ： 用于定义数据的结构，比如 创建、 修改或者删除数据库对象； DML（数据操作语言）：用于检索或者修改数据； DCL（

13、数据控制语言）：用于定义数据库用户的权限。 3.1 DDL 可以用于创建用户和重建数据库对象。下面是DDL 命令： CREATE TABLE：创建数据库表 ALTER TABLE ：修改数据库表 DROP TABLE ：丢弃数据库表 CREATE INDEX ：创建索引 DROP INDEX：丢弃索引 3.2 DML 可以细分为以下的几个语句： SELECT ：用于检索数据； INSERT：用于增加数据到数据库； UPDATE：用于从数据库中修改现存的数据 DELETE：用于从数据库中删除数据。 3.3 DCL 用于创建关系用户访问以及授权的对象。下面是几个DCL 命令： ALTER PASS

14、WORD ：修改访问授权密码 GRANT ：授权用户访问数据库表 REVOKE ：废除用户授权 CREATE SYNONYM： 3.4 表、字段、记录 在关系型数据库中，表就是一个数据的集合体，简单地理解就是一个二维（行与列 有简单对应关系的）数据表格。 字段是指表格中的列，即具有相同属性的数据集合，每个字段都必须有一个唯一的 名称，称为字段名。例如，在表格中，如果用一列存放“性别”，“性别”就是一个字 段名。 记录是指表格中的行，它由若干个字段值构成。例如，用于记录每个成员的表中， 可以有昵称、 年龄、性别、电子邮件等字段， 添加进表中的每一个成员，都包含有昵称、 年龄、性别、电子邮件这些数

15、据，每个成员的这些数据构成一条记录。 下面给出一张学生表，表中id、name 、gender 、birth 、class 都是字段，而行中包 含了这个表的一个记录，即每个学生的ID，名字，性别，生日等： 4. MySQL数据库基本操作 4.1 基本操作 4.1.1 连接登录 MySql 语法： mysql -u 用户名-p 密码 -h 服务器名称-P 端口 1）、连接到本机 mysql -u root -p Enter password: * - 2）、连接到远程主机上 mysql -h localhost -u root -p Enter password: * 4.1.2 退出 MySql

16、操作界面 mysql quit; mysqlexit; 4.1.3 修改密码 mysqladmin -u root psa(旧密码 ) password chx( 新密码 ) 4.1.4 显示当前存在的数据库 mysql show databases; 或者多行命令 mysql show - databases - ; 4.1.5 创建一个数据库 语法： create database if not exists 数据库名称default character set 字符集 mysql create database if not exists db1 default character se

17、t utf8; 4.1.6 删除一个数据库 语法： drop database if exists 数据库名称 mysql drop database db1; 4.1.7 选择数据库并显示当前选择的数据库 mysql use mysql; 4.1.8 显示当前数据库中存在的表 语法:show tables like 表达式 mysql show tables; 4.2 SQL 数据类型 smallint - 16 位元的整数。 interger 或 int - 32 位元的整数。 decimal(p,s) - p 精确值和 s 大小的十进位整数，精确值p 是指全部有几个数(digits) 大

18、小值， s 是指小数点後有几位数。如果没有特别指定，则系统会设为 p=5; s=0 。 float - 32 位元的实数。 Double - 64位元的实数。 char(n) - n 长度的字串， n 不能超过 254 。 varchar(n) - 长度不固定且其最大长度为 n 的字串， n 不能超过 4000 。 graphic(n) - 和 char(n) 一样，不过其单位是两个字元 double-bytes ， n 不能超过 127。 这个形态是为了支援两个字元长度的字体，例如中文字。 vargraphic(n) - 可变长度且其最大长度为 n 的双字元字串，n 不能超过 2000 。

19、date - 包含了 年份、月份、日期。 time - 包含了 小时、分钟、秒。 timestamp - 包含了 年、月、日、时、分、秒、千分之一秒。 4.3 主键、外键、索引 主键(primary key) 能够唯一标识表中某一行的属性或属性组。一个表只能有一个 主键，但可以有多个候选索引。主键常常与外键构成参照完整性约束，防止出现数据不 一致。主键可以保证记录的唯一和主键域非空,数据库管理系统对于主键自动生成唯一 索引，所以主键也是一个特殊的索引。 比如学生表里有学号和姓名，姓名可能有重名的，但学号确是唯一的，你要从学生 表中搜索一条纪录如查找一个人，就只能根据学号去查找，这才能找出唯一的

20、一个，这 就是主键 ;如：id int(10) not null primary key auto_increment ；自增长的类型 外键（foreign key ） 是用于建立和加强两个表数据之间的链接的一列或多列。外 键约束主要用来维护两个表之间数据的一致性。简言之，表的外键就是另一表的主键， 外键将两表联系起来。一般情况下，要删除一张表中的主键必须首先要确保其它表中的 没有相同外键（即该表中的主键没有一个外键和它相关联）。 索引(index) 是用来快速地寻找那些具有特定值的记录。主要是为了检索的方便， 是为了加快访问速度，按一定的规则创建的，一般起到排序作用。所谓唯一性索引， 这种索

21、引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现 一次，即必须唯一。 总结： 主键一定是唯一性索引，唯一性索引并不一定就是主键。 一个表中可以有多个唯一性索引，但只能有一个主键。 主键列不允许空值，而唯一性索引列允许空值。 主键可以被其他字段作外键引用，而索引不能作为外键引用。 4.4 数据表操作 4.4.1 创建一个表 语法： create table if not exists 数据表名称(列定义 , ) 示例： mysql create table tb1( - id int auto_increment primary key, - name varchar(20

22、) - ); 列定义： 列名称数据类型null|not null primary keyuniqueauto_incrementdefault 默认值 4.4.2显示表 (db) 的内容 mysqlselect * from tb1; 4.4.3 查看表 tb1 的字段描述 语法： show columns from 数据表 语法： describe 数据表 mysql show columns from tb1; mysql describe tb1; mysql desc tb1; 4.4.4 修改数据表 语法如下： ALTER TABLE “table_name“ 【改变方式 】 改变方

23、式分： ADD：在表中增加一列，ADD 列名数据类型 CHANGE ：修改表中某一列的名字，CHANGE 列名 新列名 MODIFY ：改变某一列的数据类型，MODIFY 列名 数据类型 DROP ：丢弃某一列 1 添加列 语法 1：alter table 数据表名称add 列定义first 语法 2：alter table 数据表名称add 列定义after 列名称 语法 3：alter table 数据表名称add ( 列定义 ,.) 示例： mysql alter table tb1 add age int first; mysql alter table tb1 add age2 in

24、t default 10 after id; mysql alter table tb1 add sex tinyint default 0; 2 删除列 语法： alter table 数据表名称drop 列名称 示例： mysql alter table tb1 drop age 3 修改列定义 语法： alter table 数据表名称modify 列名称数据类型 示例： mysql alter table tb1 add memo varchar(100); mysql alter table tb1 modify memo char(1); mysql desc tb1; 4 修改列

25、名称及定义 语法： alter table 数据表名称change 旧列名称新的列名称列定义 示例： mysql alter table tb1 change memo memo1 varchar(200); 4.4.5 修改数据表名称 语法： alter table 数据表名称rename 新表名称 示例： mysql alter table tb1 rename user; 4.4.6 删除数据表 语法： drop table if exists 数据表名称 4.4.7 清空数据表 语法：TRUNCATE TABLE “表格名 “ 如清空 grade 表： TRUNCATE TABLE g

26、rade 4.4.8 插入记录 语法： INSERT INTO “表格名”(“列 1”, “列 2”, .) VALUES (“值 1”, “值 2”, .) ； 例如向 student 表中插入一条学生记录： INSERT INTO student (name, gender, birth,class) VALUES(Sun Nan , M, 1973-03-19 , 1); 注：因为 id 字段为自增长的，所以可以不用指定值，另，插入的时候如果指定列的 值为唯一属性的话则插入相同的值时会失败。比如说在student 表中，不能存在两条id 为 2 的记录，但可以存在两条name 为“Sun

27、 Nan”的记录。 4.4.9更新记录 格式：UPDATE “表格名” SET 列 1 = 新值 1, 列 2= 新值 2 WHERE 条件 ; 如我们修改“ Sun Nan ”同学的出生日期为“1972-08-29 ”，所在的班级id 为 2， 执行的语句为： UPDATE student SET birth= 1972-08-29 , class=2 WHERE name= Sun Nam ; 4.4.10 删除记录 格式： DELETE FROM “表名“ WHERE 条件 如从 grade 表中删除 student_id为 2 的记录： DELETE FROM grade WHERE

28、student_id=2; 如果要删除表中所有的数据： DELTE FROM grade; 5. 练习 创建学生表 student ： create table student ( id int auto_increment, name varchar(32), gender int default 1, birth varchar(20), class int, primary key (id) ); 创建班级表 class create table class ( id int not null, class_name varchar(32), begin_time varchar(20)

29、, teacher varchar(32), primary key (id) ); 创建成绩表 grade ： create table grade ( id int auto_increment, student_id int, class int, chinese int, math int, exam_time varchar(20), primary key (id) ); 6. select 语句 SELECT语句主要被用于查询数据库中的一个或多个表里的数据。 格式： SELECT FROM 例如： mysql select * from user; mysql select na

30、me from user; 许多子句及它们的结合都可以用于SELECT语句中： FROM ：指定从哪些表中获取数据 DISTINCT ：消除重复的记录行 WHERE ：只返回满足特定条件（也叫过滤器）的记录行 ORDER BY ：按照表达式列表排序记录 LIMIT ：返回特定部分的数据，而不是所有的结果集 格式： SELECT DISTINCT FROM WHERE ORDER BY LIMIT 6.1 使用 FROM ?FROM 子句在 SELECT语句中是可选项，它出现在语句的表达式之后 ?FROM 子句指定语句要处理的表。 ?表的别名 在 SQL 语句里，在 FROM 子句里引用的表可以

31、给定一个临时的名称，该名称 叫做表的别名。 ?示例： mysql select id, name, sex, age from user where id = 1; mysql select u.id, u.name, u.sex, u.age from user u where id = 1; 6.2 使用 DISTINCT ?如果查询的结果中包含重复的记录，该重复记录要被删除以生成每行都是唯一 的结果集。为了达到这个目的，可在SELECT关键字后添加DISTINCT 关键字。 ?在指定的列里， DISTINCT 将所有的 NULL 值当作相同的值。 ?示例： mysql select di

32、stinct work from user; 6.3 使用 WHERE ?MySQL 支持许多不同类型的运算符，这依赖于操作数的数据类型，或操作数与 运算符处理的值的数据类型。 运算 比较 逻辑 ?示例： mysql select * from user where age select * from user where age 30 and (work=java or work=c+); mysql select * from user where age 30 and work in(java,c+); 6.4 关于 NULL 如果有一个操作数的结果是NULL，则大多数运算符的求值结果也

33、会是NULL。 当 NULL 值表达式在函数中被用于操作数时，运算符将产生NULL。 WHERE 子句也有相同的情况，WHERE 子句会丢弃那些条件不是TRUE 的记录。 示例： mysql select NULL+age from user; 6.5 在 SELECT语句里使用ORDER BY 如果有必要让输出记录按某一特定的顺序返回，可以使用ORDER BY子句以显示如 何排序结果集。 示例： mysql select * from user order by age; DESC：降序ASC：升序 示例： mysql select * from user order by age desc

34、; mysql select * from user order by age asc; 6.6 在 SELECT语句里使用LIMIT 当一个查询返回许多记录行时，有必要通过增加LIMIT 子句来限定只显示部分记录。 LIMIT 子句可以采用以下一个或两个参数： LIMIT row_count LIMIT skip_count, row_count 示例： mysql select * from user limit 5; mysql select * from user limit 5,5; 它常见的用处是获取包含某个特定列的最小或最大值的行 示例（查找user 表中年龄最大的）： mysq

35、l select * from user order by age desc limit 1; 6.7 聚集查询结果 有可能出现这种情况，结果集里的一行对应于底层基表里的一组记录行。这个过程叫聚 合，并且这样一个结果集被称为聚集。 计算一组值的摘要值的函数（例如AVG（）被称为“聚集”函数。 MIN（）求最小值 MAX（） 求最大值 SUM（）求和 AVG（）求平均数 COUNT（） 求条目数 示例： mysql select min(age) from user; mysql select max(age) from user; mysql select sum(age) from user

36、; mysql select avg(age) from user; 示例（使用count(*) 统计 user 表中用户的个数）： mysql select count(*) from user; 示例（指定统计memo 列，NULL 值不会被统计）： mysql select count(memo) from user; 6.8 用 SELECT与 GROUP BY分组 所有在 GROUP BY子句中指定的表达式里，具有相同结合值的记录行将按一组处理，在 结果集里以一行结束。 聚集函数可以与GROUP BY 一起使用将记录分组。 当带有 GROUP BY 子句时，聚集函数将为每个组统计值。

37、 示例（求每个工作员工的平均年龄）： mysql select avg(age), work from user group by work; 示例（求每个工作员工的总年龄）： mysql select count(*), sum(age), work from user group by work; 6.9 使用 UNION UNION 关键字能够将两个或多个SELECT语句的结果连接起来。其语句是： SELECT UNION SELECT 缺省地， UNION 消除结果集中的重复记录。为了获得所有的记录行，可用UNION ALL 取代每个 UNION 实例。 UNION 在下面情况下将很有用： 多个表中有相似的信息，并且需要从这些表中立刻获取记录。 需要从同一个表中获取几组记录，但是表现每组的条件并不容易写在一个单个的 WHERE 子句里。 示例： mysql select * from user where age union - select * from user where name = 张三;