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.4.1 人工管理阶段磁带、

3、磁盘、手工记录1.4.2 文件系统阶段数据存储在计算机的磁盘上，数据以文件的形式出现，通过文件系统来管理这些文件，文件系统通过路径和文件访问文件中的数据1.4.3 数据库系统阶段使用专门的数据库来管理数据。用户在数据库系统中建立数据库，然后在数据库中建立表，最后将数据存储在这些表中-表是数据库存储数据的基本单位。用户通过数据库管理系统来查询表中的数据。1.5 常用的数据库甲骨文的 oracle：大型数据库， linux/windows/soloras/suse sybase：大型数据库mysql：开源，中大型数据库，linux/windows/微软 sql server ：windows po

4、stgre sql ：安全开源免费，中型， linux access：sqlite：小型数据库， linux/windows ，嵌入式系统1.6 数据库技术构成数据库技术是解决数据存储、管理、统计、查询等处理而使用的一门广泛技术数据 data ：是数据库中存储的基本对象，是描述事物的符号， 如数字、文件、图形、图像等数据库 database ，db：是长期储存在计算机内、有组织的、可共享的数据集合。数据库管理系统database management system ，dbms：是位于用户与操作系统之间的管理数据库的软件（定义、管理和维护数据）。mysql 是一中 dbms。数据库系统datab

5、ase system -dbs ：是指在计算机系统中引入数据库后的系统。其组成如下：计算机系统、数据库、数据库管理系统及其开发工具、人员（数据库管理员 dba、终端用户）通常情况下，把数据库系统简称为数据库。sql(structed query language)语言：结构化查询语言，是数据库管理系统通过sql 语句来操作数据库中的数据。标准的sql 命令，比如“ select”、 “insert ”、“update ”、 “delete”、“create ”和 “drop ”常常被用于完成绝大多数数据库的操作。数据库访问技术：odbc（open database connectivity开放

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

7、c也是个商标名。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 服务器将向处理程序发送命令，以便执行数据存储和检索任务，如打开表、操纵行和管理索引等。能够以累进方式创建定制存储引擎：开发人员能够以只读存储引擎启动，随后添

12、加对 insert、update 和 delete 操作的支持，甚至能够增加对索引功能、事务和其他高级操作的支持2. linux平台下安装配置mysql 3 mysql语言sql 功能强大，但是概括起来，它可以分成以下几组：ddl（数据定义语言） ： 用于定义数据的结构，比如 创建、 修改或者删除数据库对象；dml（数据操作语言）：用于检索或者修改数据；dcl（数据控制语言）：用于定义数据库用户的权限。3.1 ddl 可以用于创建用户和重建数据库对象。下面是ddl 命令：create table：创建数据库表alter table ：修改数据库表drop table ：丢弃数据库表create

13、 index ：创建索引drop index：丢弃索引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 语法：

15、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操作界面mysql quit; mysqlexit; 4.1.3 修改密码mysqladmin -u root psa(旧密码 ) password chx( 新密码 ) 4.1.4 显示当前存在的数据库mysql show databases; 或者多行命令mysql show - database

16、s - ;4.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)

18、- 和 char(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 ex

21、ists 数据表名称(列定义 ,. ) 示例：mysql create table tb1( - id int auto_increment primary key, - name varchar(20) - ); 列定义：列名称数据类型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

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

23、定义 ,.) 示例：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 alter table tb1 add memo v

24、archar(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 删除数据表语法： drop table if exists 数据表名

25、称4.4.7 清空数据表语法：truncate table 表格名 如清空 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 删除记录格式： delete from 表名 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, c

28、lass_name 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 例如：m

29、ysql 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 如果有一个操作数的结果是nul

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