MySQL数据库技术教案

mysql基础课教案

教案

2011～2012学年第1学期

课程名称 MySQL数据库 系(院、部) 教研室(实验室) 授课班级

主讲教师 职

称

使用教材 MySQL实用教程 二○一一年八月

mysql基础课教案

教案

mysql基础课教案

Mysql数据库应用

mysql基础课教案

授课 内 容、 教具 与时 间分 配

1. MySQL 的数据类型 在 MySQL 中有如下几种数据类型： (1)数值型 MySQL 提供了五种整型： TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT,三种 浮点类型： FLOAT、DOUBLE 和 DECIMAL。 数值是诸如 32 或 153.4 这样的值。MySQL 支

持科学表示法，科学表示法由整数或浮点数 后跟“e”或“E” 、一个符号( “+”或“-” )和一个整数指数来表示。1.24E+12 和 23.47e-1 都是合法的科学表示法表示的数。而 1.24E12 不是合法的，因为指数前的符号未给出。 浮点数由整数部分、一个小数点和小数部分组成。整数部分和小数部分可以分别为空，但 不能同时为空。 数值前可放一个负号“-”以表示负值。 (2)字符(串)型 类型名 CHAR VARCHAR TINYBLOB BLOB 定长字符串 可变长字符串 非常小的 BLOB(二进制大对象) 小 BLOB 说明

MEDIUMBLOB 中等的 BLOB LONGBLOB TINYTEXT TEXT 大 BLOB 非常小的文本串 小文本串

MEDIUMTEXT 中等文本串 LONGTEXT ENUM SET (3)日期和时间型 类型名 DATE TIME 说明 “YYYY-MM-DD”格式表示的日期值 “hh:mm:ss”格式表示的时间值 大文本串 枚举；列可赋予某个枚举成员 集合；列可赋予多个集合成员

DATETIME “YYYY-MM-DD hh:mm:ss”格式

mysql基础课教案

Mysql数据库应用

课程名称 授课班级 授课重点、难点

1 创建数据库 2 删除数据库 3 数据库存储引擎

3.1 MySQL存储引擎简介 3.2 InnoDB存储引擎 3.3 MyISAM存储引擎 3.4 MEMORY存储引擎 . 3.5 存储引擎的选择

创建数据库（当然，数据库名\"asb\"不能是已经存在的） mysql> create database asb;

授课内容、教具与时

mysql基础课教案

间分配

选用数据库，使其成为当前操作的数据库 mysql> use asb;

成功选中后会有如下显示： Database changed

甚至可以在窗口命令登陆mysql数据库的时候直接选中要操作的数据库（当然前提是asb数据库存在）

bin>mysql asb -u uesername -p

Enter password:****** 查看当前所操作的数据库名称 mysql> select database(); 运行后结果如下： +------------+ | database() | +------------+ | asb | +------------+ 1 row in set (0.00 sec) 数据库存储引擎： ISAM

ISAM是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于，它不支持事务处理，也不能够容错：如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里，那就必须经常备份你所有的实时数据，通过其复制特性，MySQL能够支持这样的备份应用程序。

mysql基础课教案

MyISAM

MyISAM是MySQL的ISAM扩展格式和缺省的数据库引擎。除了提供ISAM里所没有的索引和字段管理的大量功能，MyISAM还使用一种表格锁定的机制，来优化多个并发的读写操作。其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。MyISAM还有一些有用的扩展，例如用来修复数据库文件的MyISAMChk工具和用来恢复浪费空间的MyISAMPack工具。

MyISAM强调了快速读取操作，这可能就是为什么MySQL受到了Web开发如此青睐的主要原因：在Web开发中你所进行的大量数据操作都是读取操作。所以，大多数虚拟主机提供商和Internet平台提供商(Internet Presence Provider，IPP)只允许使用MyISAM格式。 HEAP

HEAP允许只驻留在内存里的临时表格。驻留在内存里让HEAP要比ISAM和MyISAM都快，但是它所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那么所有的数据都会丢失。在数据行被删除的时候，HEAP也不会浪费大量的空间。HEAP表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。要记住，在用完表格之后就删除表格。让我再重复一遍：在你用完表格之后，不要忘记删除表格。 InnoDB和Berkley DB

InnoDB和Berkley DB(BDB)数据库引擎都是造就MySQL灵活性的技术的直接产品，这项技术就是MySQL++ API。在使用MySQL的时候，你所面对的每一个挑战几乎都源于ISAM和MyISAM数据库引擎不支持事务处理也不支持外来键。尽管要比ISAM和MyISAM引擎慢很多，但是InnoDB和BDB包括了对事务处理和外来键的支持，这两点都是前两个引擎所没有的。如前所述，如果你的设计需要这些特性中的一者或者两者，那你就要被迫使用后两个引擎中的一个了。

本次课程主要内容为 Mysql数据库操作 数据库存储引擎 复习题目：

1. 什么是存储引擎？如何选择？

小结 复习 思考

题 参考书

mysql基础课教案

2.mysql数据库操作方法

《mysql入门很简单》清华大学出版社

Mysql数据库应用

课程名称 授课班级 授课重点、难点

数据库表操作 授课教师

授课时数 4 授课方式 理论课

1. 创建表 2. 修改表 3. 删除表

授课内容、教具与时间分配

用CREATE TABLE 语句创建数据表

用 CREATE TABLE 语句创建表。此语句的完整语法是相当复杂的，因为存在那么多的可选子句，但在实际中此语句的应用相当简单。如我们在第 1 章中使用的所有 CREATE TABLE 语句都不那么复杂。

有意思的是，大多数复杂东西都是一些子句，这些子句MySQL在分析后扔掉。参阅附录 1 可以看到这些复杂的东西。 1、CREATE TABLE 语句的基本语法

用ALTER TABLE语句修改表的结构

有时你可能需要改变一下现有表的结构，那么Alter Table语句将是你的合适选择。 增加列

alter table tbl_name add col_name type

mysql基础课教案

例如,给表增加一列weight

mysql>alter table pet add weight int; 删除列

alter table tbl_name drop col_name 例如，删除列weight：

mysql>alter table pet drop weight; 改变列

alter table tbl_name modify col_name type 例如，改变weight的类型：

mysql> alter table pet modify weight samllint; 另一种方法是：

alter table tbl_name change old_col_name col_name type 例如：

mysql> alter table pet change weight weight samllint; 给列更名

mysql>alter table pet change weight wei; 给表更名

alter table tbl_name rename new_tbl 例如，把pet表更名为animal

mysql>alter table pet rename animal; 改变表的类型

另外，可以为列增加或删除索引等属性。 用DROP TABLE 语句删除数据表

mysql基础课教案

DROP TABLE [IF EXISTS] tbl_name [, tbl_name,...]

DROP TABLE删除一个或多个数据库表。所有表中的数据和表定义均被删除，故小心使用这个命令！

在MySQL 3.22或以后版本，你可以使用关键词IF EXISTS类避免不存在表的一个错误发生。 例如：

mysql>USE mytest;

mysql>DROP TABLE guestbook; 或者，也可以同时指定数据库和表： mysql>DROP TABLE mytest. guestbook;

本节讲述了有关表的大部分操作，现在将所述内容总结如下： MySQL的表的三种类型

小结

如何创建表、删除表

复习

如何改变表的结构、名字

思考

题 参考书

复习题目：

1. 数据表操作方法？ 教材：《mysql实用教程》

《mysql入门很简单》清华大学出版社

Mysql数据库应用

mysql基础课教案

授课班级

授课时数

2

授课方式

理论 课

授课重点、 难点

Mysql 索引

授课内容、 教具与时 间分配

索引是快速搜索的关键。MySQL 索引的建立对于 MySQL 的高效运行是很重要的。下面 介绍几种常见的 MySQL 索引类型。 MySQL 索引类型包括： (1)普通索引 这是最基本的索引，它没有任何限制。它有以下几种创建方式： ◆创建索引 1. CREATE INDEX indexName ON mytable(username(length));

如果是 CHAR,VARCHAR 类型，length 可以小于字段实际长度；如果是 BLOB 和 TEXT 类 型，必须指定 length,下同。 ◆修改表结构 1. ALTER mytable ADD INDEX [indexName] ON (username(length))

◆创建表的时候直接指定 1. 2. 3. 4. 5. 6. 7. 8. 9. CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );

删除索引的语法： 1. DROP INDEX [indexName] ON mytable;

(2)唯一索引

mysql基础课教案

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果 是

组合索引，则列值的组合必须唯一。它有以下几种创建方式： ◆创建索引 1. CREATE UNIQUE INDEX indexName ON mytable(username(length))

◆修改表结构 1. ALTER mytable ADD UNIQUE [indexName] ON (username(length))

◆创建表的时候直接指定 1. 2. 3. 4. 5. 6. 7. 8. 9. CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );

(3)主键索引 它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引： 1. 2. 3. 4. 5. 6. 7. 8. 9. CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );

当然也可以用 ALTER 命令。记住：一个表只能有一个主键。 (4)组合索引 为了形象地对比单列索引和组合索引，为表添加多个字段：

mysql基础课教案

1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11.

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL );

为了进一步榨取 MySQL 的效率，就要考虑建立组合索引。就是将 name, city, age 建 到一个索引里： 1. ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

建表时，usernname 长度为 16,这里用 10。这是因为一般情况下名字的长度不会超过 10,这样会加速索引查询速度，还会减少索引文件的大小，提高 INSERT 的更新速度。 如果分别在 usernname,city,age 上建立单列索引，让该表有 3 个单列索引，查询时 和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个 索引，但 MySQL 只能用到其中的那个它认为似乎是最有效率的单列索引。 建立这样的组合索引，其实是相当于分别建立了下面三组组合索引： 1. 2. 3. 4. 5. usernname,city,age usernname,city usernname

为什么没有 city,age 这样的组合索引呢？这是因为 MySQL 组合索引“最左前缀”的 结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用 到该组合索引，下面的几个 SQL 就会用到这个组合索引： 1. 2. 3. SELECT * FROM mytable WHREE username="admin" AND city=" 郑州" SELECT * FROM mytable WHREE username="admin"

而下面几个则不会用到：

mysql基础课教案

1. 2. 3.

SELECT * FROM mytable WHREE age=20 AND city="郑州" SELECT * FROM mytable WHREE city="郑州"

(5)建立索引的时机 到这里我们已经学会了建立索引，那么我们需要在什么情况下建立索引呢？一般来说， 在 WHERE 和 JOIN 中出现的列需要建立索引，但也不完全如此，因为 MySQL 只对<,<=, =,>,>=,BETWEEN,IN,以及某些时候的 LIKE 才会使用索引。例如： 1. 2. 3. 4. 5. SELECT FROM mytable t LEFT JOIN mytable m ON =ername WHERE m.age=20 AND m.city='郑州'

此时就需要对 city 和 age 建立索引，由于 mytable 表的 userame 也出现在了 JOIN 子 句

中，也有对它建立索引的必要。 刚才提到只有某些时候的 LIKE 才需建立索引。因为在以通配符%和_开头作查询时， MySQL 不会使用索引。例如下句会使用索引： 1. SELECT * FROM mytable WHERE username like'admin%'

而下句就不会使用： 1. SELECT * FROM mytable WHEREt Name like'%admin'

因此，在使用 LIKE 时应注意以上的区别。 (6)索引的不足之处 上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的 缺点： ◆虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、 UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件。 ◆建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一 个大表上创建了多种组合索引，索引文件的会膨胀很快。 索引只是提高效率的一个因素，如果你的 MySQL 有大数据量的表，就需要花时间研究 建立最优秀的索引，或优化查询语句。

mysql基础课教案

(7)使用索引的注意事项 使用索引时，有以下一些技巧和注意事项： ◆索引不会包含有 NULL 值的列 只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL 值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的 默认值为 NULL。 ◆使用短索引 对串列进行索引， 如果可能应该指定一个前缀长度。 例如， 如果有一个 CHAR(255)的列， 如果在前 10 个或 20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短 索引不仅可以提高查询速度而且可以节省磁盘空间和 I/O 操作。 ◆索引列排序 MySQL 查询只使用一个索引，因此如果 where 子句中已经使用了索引的话，那么 order by 中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排 序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。 ◆like 语句操作 一般情况下不鼓励使用 like 操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而 like “aaa%”可以使用索引。 ◆不要在列上进行运算 1. select * from users where YEAR(adddate)<2007;

将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成 1. select * from users where adddate<‘2007-01-01’;

◆不使用 NOT IN 和<>操作 以上，就对其中 MySQL 索引类型进行了介绍。 小结 复习 思考题 教材：《mysql 实用教程》 《mysql 入门很简单》清华大学出版社

mysql基础课教案

Mysql数据库应用

课程名称 授课班级 授课重点、难点 视图 授课教师

授课时数 4 授课方式 理论课

创建视图、修改视图和删除视图

mysql基础课教案

将覆盖原视图。select_statement 是一个查询语句，这个查询语句可从表或其它的视图中 查询。视图属于数据库，因此需要指定数据库的名称，若未指定时，表示在当前的数据库 创建新视图。

表和数据库共享数据库中相同的名称空间，因此，数据库不能包含相同名称的表和视 图，并且，视图的列名也不能重复。

3. 注意事项

创建视图存在如下注意事项：

(1) 运行创建视图的语句需要用户具有创建视图(CRATE VIEW)的权限，若加了 [OR REPLACE]时，还需要用户具有删除视图(DROP VIEW)的权限；

(2) SELECT 语句不能包含 FROM 子句中的子查询；

(3) SELECT 语句不能引用系统或用户变量；

(4) SELECT 语句不能引用预处理语句参数；

(5) 在存储子程序内，定义不能引用子程序参数或局部变量；

(6) 在定义中引用的表或视图必须存在。但是，创建了视图后，能够舍弃定义引用 的表或视图。要想检查视图定义是否存在这类问题，可使用 CHECK TABLE 语句；

(7) 在定义中不能引用 TEMPORARY 表，不能创建 TEMPORARY 视图；

(8) 在视图定义中命名的表必须已存在；

(9) 不能将触发程序与视图关联在一起；

(10) 在视图定义中允许使用 ORDER BY,但是，如果从特定视图进行了选择，而

mysql基础课教案

该视图使用了具有自己 ORDER BY 的语句，它将被忽略。

三. 修改视图——ALTER VIEW

1. 语法

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]

该语句用于更改已有视图的定义。其语法与 CREATE VIEW 类似。

四. 删除视图——DROP VIEW

1. 语法

DROP VIEW [IF EXISTS]

view_name [, view_name] ...[RESTRICT | CASCADE]

该语句用户删除视图，可一次删除多个视图。[IF EXISTS]选项确保语句正确运行。 若没有该子句，当指定的视图不存在时，将发生错误。

小结 复习 思考 题 参考 书

本文讲述 MySql5.x 中视图的使用，包括创建视图、修改视图和删除视图的相关知识，包 括命令的语法、使用举例以及注意事项。

复习题目： 1. 视图与表的区别和联系？

mysql基础课教案

Mysql数据库应用

mysql基础课教案

Mysql数据库应用

课程名称 授课班级

SQL查询语句 授课教师

授课时数 8 授课方式 理论课

mysql基础课教案

授课重点、难点

基本查询语句 使用集合函数查询 连接查询

授课内容、教具与时间分配

SELECT被用来检索从一个或多个表中精选的行。select_expression指出你想要检索的列。SELECT也可以用

来检索不引用任何表的计算行 1、select*from table1;

2、select field1,field2 from table1; 3、select count(*)from table2;

4、select table1.filed1,table2.filed2 from table1,table2 where table1.field1='valed1'; 5、select *from table1 where field1='valed1' and fiels2='valed2' orded by field1; 6、select*from table1 where filed1='valed1' order byfiled1 desc linit n; 7、select *from table2 where name like '%$lin%';(模糊) 8、sub_selects(高级)

select *from table1 where id In(select id from table2......); 9、select ...into table...(高级)

(可用select...into outfile...;insert...selkect...替代)

10、SELECT MAX(field1) AS field1 FROM table1 列的最大值 其中：表：table \字段：field 集合函数查询

对一个表中的记录进行数据统计。例如，如果你想统计存储在表中的一次民意测验的投票结果。或者你想知道一个访问者在你的站点上平均花费了多少时间。要对表中的任何类型的数据进行统计，都需要使用集合函数。你可以统计记录数目，平均值，最小值，最大值，

本文来源：https://www.bwwdw.com/article/18l4.html