mysql教案讲解

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的bigtable）。 1

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

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

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

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

7、数据库应用程序，同时，JDBC也是个商标名。 C API （C语言连接数据库） MySQL提供C语言使用的API. C代码的API是随MySQL一起发布的. 它包含在mysqlclient库中, 可以使C程序来访问数据库. 综上所述，数据库技术主要包括：数据库系统、SQL语句、数据库访问技术 1.7 MySQL逻辑架构 一张好的图片会是你更容易理解MySQL各个组件的工作方式。 1、最上一层所包含的服务并不是MySQL所独有的技术。它们都是服务于C/S程序或者是这些程序所需要的：连接处理，身份验证，安全性等等。 2、第二层是我们所感兴趣的。这是MySQL的核心部分。包括了查询的解析，分析，优化

8、，缓存以及所有内置的函数(如日期,时间,数学运算,加密)。还有一些功能是涉及到了存储引擎，如存储过程，触发器，视图等。 3、第三层包括了存储过程。它们负责存储和获取所有存储在MySQL中的数据。就像Linux众多的文件系统一样。每个存储引擎都有自己的优点和缺陷。服务器是通过存储引擎API来与它们交互的。这个接口隐藏了各个存储引擎不同的地方。对于查询层尽可能的透明。这个API包含了很多底层的操作。如开始一个事物，或者取出有特定主键的行。存储引擎不能解析SQL，互相之间也不能通信。仅仅是简单的响应服务器的请求。 连接管理和安全 在服务器内部，每个client连接都有自己的线程。这个连接的查询都在一

9、个单独的线程中执行。这个线程存在于一个核心或者CPU中。服务器缓存了线程，因此不需要为每个client连接单独创建和销毁线程。 当clients（也就是应用程序）连接到了MySQL服务器。服务器需要对它进行身份验证。这个身份验证是基于用户名，主机，以及密码。也使用了X.509的SSL连接。clients一连接上，服务器就验证它的权限。如这个client是否可以查询world数据库下的Country表的数据。这部分将在独立的章节进行说明。 优化和执行MySQL的解析查询是创建了一个内部的结构（一个语法分析树）。之后就进行了各种各样的优化。这些优化包括了，查询语句的重写，读表的顺序，索引的选择等等

10、。你可以通过查询语句的关键词对优化器进行一定的提示。这样做会影响到它的优化方式。你也可以让服务器对于各种优化进行说明。这样可以得知服务器的运行策略以及为了获得更好的效率，可以做更多的优化工作，如查询优化等。优化器会在以后的教程中详细介绍。 优化器是不会关心表所用到的存储引擎的类型，但是存储引擎会影响到服务器优化查询的方式。优化器需要知道存储引擎的一些特性，这些操作的消耗以及表数据的一些统计信息。例如，一些存储引擎支持索引，这对于查询是非常有用的。 在解析查询之前，要查询缓存，这个缓存只能保存查询信息以及结果数据。如果请求一个查询在缓存中存在，就不需要解析，优化和执行查询了。直接返回缓存中所存放

11、的这个查询的结果。 MySQL体系结构介绍： 存储引擎负责管理数据存储，以及MySQL的索引管理。通过定义的API，MySQL服务器能够与存储引擎进行通信。 每个存储引擎均是1个继承类，每个类实例作为处理程序而被引用。 针对需要与特殊表一起工作的每个线程，处理程序是在1个处理程序的基础上实例化的。例如，如果3个连接全都在相同的表上工作，需要创建3个处理程序实例。 一旦创建了处理程序实例，MySQL服务器将向处理程序发送命令，以便执行数据存储和检索任务，如打开表、操纵行和管理索引等。 能够以累进方式创建定制存储引擎：开发人员能够以只读存储引擎启动，随后添加对INSERT、UPDATE和DELET

12、E操作的支持，甚至能够增加对索引功能、事务和其他高级操作的支持 2. Linux平台下安装配置MySQL 3 MySQL语言 SQL功能强大，但是概括起来，它可以分成以下几组： DDL（数据定义语言）： 用于定义数据的结构，比如 创建、修改或者删除数据库对象； DML（数据操作语言）：用于检索或者修改数据； DCL（数据控制语言）：用于定义数据库用户的权限。 3.1 DDL 可以用于创建用户和重建数据库对象。下面是DDL命令： ：创建数据库表TABLE CREATE ALTER TABLE ：修改数据库表 DROP TABLE ：丢弃数据库表 CREATE INDEX ：创建索引 DROP I

13、NDEX：丢弃索引 3.2 DML 可以细分为以下的几个语句： SELECT：用于检索数据； INSERT：用于增加数据到数据库； UPDATE：用于从数据库中修改现存的数据 DELETE：用于从数据库中删除数据。 3.3 DCL 用于创建关系用户访问以及授权的对象。下面是几个DCL命令： ALTER PASSWORD ：修改访问授权密码 GRANT ：授权用户访问数据库表 REVOKE ：废除用户授权 CREATE SYNONYM： 3.4 表、字段、记录 在关系型数据库中，表就是一个数据的集合体，简单地理解就是一个二维（行与列有简单对应关系的）数据表格。 字段是指表格中的列，即具有相同属性

14、的数据集合，每个字段都必须有一个唯一的名称，称为字段名。例如，在表格中，如果用一列存放“性别”，“性别”就是一个字 段名。记录是指表格中的行，它由若干个字段值构成。例如，用于记录每个成员的表中，可以有昵称、年龄、性别、电子邮件等字段，添加进表中的每一个成员，都包含有昵称、年龄、性别、电子邮件这些数据，每个成员的这些数据构成一条记录。 下面给出一张学生表，表中id、name、gender、birth、class都是字段，而行中包含了这个表的一个记录，即每个学生的ID，名字，性别，生日等： 4. MySQL数据库基本操作 4.1 基本操作 4.1.1 连接登录MySql 语法：mysql -u用户

15、名 -p密码 -h服务器名称 -P端口 1）、连接到本机 mysql -u root -p Enter password: * - 2 ）、连接到远程主机上 mysql -h localhost -u root -p Enter password: * 4.1.2 退出MySql操作界面 mysql quit; mysqlexit; 4.1.3 修改密码 mysqladmin -u root psa(旧密码) password chx(新密码) 4.1.4 显示当前存在的数据库 mysql show databases; 或者多行命令 mysql show - databases - ; 4.

16、1.5 创建一个数据库 语法：create database if not exists 数据库名称 default character set 字符集 mysql create database if not exists db1 default character set utf8; 4.1.6 删除一个数据库 语法：drop database if exists 数据库名称 mysql drop database db1; 4.1.7 选择数据库并显示当前选择的数据库 mysql use mysql; 4.1.8 显示当前数据库中存在的表 语法:show tables like 表达式

17、mysql show tables; 4.2 SQL数据类型 smallint - 16 位元的整数。 interger或int - 32 位元的整数。 decimal(p,s) - p 精确值和 s 大小的十进位整数，精确值p是指全部有几个数(digits)大小值，s是指小数点後有几位数。如果没有特别指定，则系统会设为 p=5; s=0 。 float - 32位元的实数。 Double - 64位元的实数。 char(n) - n 长度的字串，n不能超过 254。 varchar(n) - 长度不固定且其最大长度为 n 的字串，n不能超过 4000。 graphic(n) - 和 char

18、(n) 一样，不过其单位是两个字元 double-bytes， n不能超过127。这个形态是为了支援两个字元长度的字体，例如中文字。 vargraphic(n) - 可变长度且其最大长度为 n 的双字元字串，n不能超过 2000。 date - 包含了 年份、月份、日期。 time - 包含了 小时、分钟、秒。 timestamp - 包含了 年、月、日、时、分、秒、千分之一秒。 4.3 主键、外键、索引 主键(primary key) 能够唯一标识表中某一行的属性或属性组。一个表只能有一个主键，但可以有多个候选索引。主键常常与外键构成参照完整性约束，防止出现数据不一致。主键可以保证记录的唯一

19、和主键域非空,数据库管理系统对于主键自动生成唯一索引，所以主键也是一个特殊的索引。 比如学生表里有学号和姓名，姓名可能有重名的，但学号确是唯一的，你要从学生表中搜索一条纪录如查找一个人，就只能根据学号去查找，这才能找出唯一的一个，这就是主键;如：id int(10) not null primary key auto_increment ；自增长的类型 外键（foreign key） 是用于建立和加强两个表数据之间的链接的一列或多列。外键约束主要用来维护两个表之间数据的一致性。简言之，表的外键就是另一表的主键，外键将两表联系起来。一般情况下，要删除一张表中的主键必须首先要确保其它表中的 没有相

20、同外键（即该表中的主键没有一个外键和它相关联）。索引(index) 是用来快速地寻找那些具有特定值的记录。主要是为了检索的方便，是为了加快访问速度， 按一定的规则创建的，一般起到排序作用。所谓唯一性索引，这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。 总结： 主键一定是唯一性索引，唯一性索引并不一定就是主键。 一个表中可以有多个唯一性索引，但只能有一个主键。 主键列不允许空值，而唯一性索引列允许空值。 主键可以被其他字段作外键引用，而索引不能作为外键引用。 4.4 数据表操作 4.4.1 创建一个表 语法：create table if not

21、exists 数据表名称 (列定义,. ) 示例： mysql create table tb1( - id int auto_increment primary key, - name varchar(20) - ); 列定义： 列名称 数据类型 null|not null primary keyuniqueauto_incrementdefault 默认值 4.4.2显示表(db)的内容 from tb1; * mysqlselect 4.4.3 查看表tb1的字段描述 语法：show columns from 数据表 语法：describe 数据表 mysql show columns

22、from tb1; mysql describe tb1; mysql desc tb1; 4.4.4 修改数据表 语法如下： ALTER TABLE able_name“ 【改变方式】 改变方式分： ADD ：在表中增加一列，ADD 列名 数据类型 CHANGE：修改表中某一列的名字，CHANGE 列名 新列名 MODIFY：改变某一列的数据类型， MODIFY 列名 数据类型 DROP：丢弃某一列 1 添加列 语法1：alter table 数据表名称 add 列定义 first 语法2：alter table 数据表名称 add 列定义 after 列名称 语法3：alter table

23、 数据表名称 add (列定义,.) 示例： mysql alter table tb1 add age int first; mysql alter table tb1 add age2 int 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 alte

24、r table tb1 add memo varchar(100); mysql alter table tb1 modify memo char(1); mysql desc tb1; 4 修改列名称及定义 语法： 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

25、删除数据表 语法：drop table if exists 数据表名称 4.4.7 清空数据表 语法： 剔乕?吠?尠表格名 如清空grade表： TRUNCATE TABLE grade 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字段为自增长的，所以可以不用指定值，另，插

26、入的时候如果指定列的值为唯一属性的话则插入相同的值时会失败。比如说在student表中，不能存在两条id为2的记录，但可以存在两条name为“Sun 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 删除记录 格式：?佒?表名 WHERE 条件 如从grade表中删除

27、student_id为2的记录： DELETE FROM grade WHERE 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_n

28、ame varchar(32), begin_time varchar(20), 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

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

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

31、： ?mysql select distinct 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

32、，则大多数运算符的求值结果也会是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; 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;