mysql三小时学习教案

1、mysql三小时学习教案Mysql 三小时教学杜飞 2006-11-11目的:1、在学生已经学习了SQL SERVER2000的基础上.利用一节课（三小时）的时间掌握mysql的安装,建库、建表、表的三种基本操作、查询、多表查询、数据库的备份与恢复2、MYSQL 服务器的简单维护3、mysql+apache+php的环境搭建一、如何安装mysql服务器:我们使用当前MY-SQL的比较新的版本MY SQL 5.0版。首先，如何得到MY SQL 5.0 我们可以从网上下载()/url。 我们来看一下如何进行安装MY SQL支持多平台，也就是说它可以工作在WINDWOS

2、平台上，也可以工作在LINUX平台上，在LINUX下的安装可能会复杂那么一点点，但在WINDOWS下的安装还是很简单的，和咱们平时安装软件没什么两样，下面咱们开始安装：我们拿到的是一个可执行文件，双击Setup.exe文件，开始mysql5.0的安装。先是等待，直到出现下面的欢迎窗口*安装过程略！*怎么检测你的mysql正确安装了。那你们是怎么检测 SQL SERVER是不是正确安装的？Netstat -an看有没有3306口吧！或者你就去看看你的服务有没有启动！二、登录到mysql服务器：两种方法：1、mysql hlocalhost uroot p口令2、直接利用开始菜单！开始-程序-my

3、sql5.0三、命令的简单使用：下面的操作一般就是使用标准SQL命令：也就是说你在SQL SERVER里面怎么用，这里就怎么用，但是有的命令也不是完全一样的。有一点点细微的差别！下面咱们就把这个命令简单的过一遍： my sql 命令的使用：以分号或g结束，c取消一行命令，h帮助1、显示数据库：show databases;2、建数据库：create database if not exists 数据库名;3、建数据表： create table if not exists 表名 (字段名1 类型。)create table student (编号 int auto_increment prim

4、ary key, 姓名 varchar(10);注意:设置了自动增长,就要定为主键,如果选择了BIT 类型,0不显示,非0显示为一个特殊符号!4、显示数据表:show tables;5、删除库: drop database if exists 库名; 6、删除表: drop table if exists 表名;7、显示表结构: desc 表名8、如何修改表结构:增长一个字段; alter table 表名 add 字段名 类型9、 删除一个字段: alter table 表名 drop 字段名10、 修改一个字段的属性: alter table 表名modify 字段 新属性11、修改主键:

5、 增加一个主键 （1）alter table 表名 add primary key; （2）alter table 表名 add id int；Alter table 表名 modify id int primary key;(3) alter table 表名 add id int primary key;12、 删除一个主键 (1)alter table 表名 drop primary key(字段名);(2)alter table 表名 drop 字段名；13、修改外键（1）alter table 表名 add foreign key(字段名) references 参照表(字段名)（2

6、）alter table 表名 drop foreign key 字段名或 先查出外键名 show create table 表名Alter table 表名 drop foreign key 外键名四、表的三种基本操作:插入记录、删除记录、更新记录、 这个和sql server是一样的，你在SQL SERVER里面怎么操作，在MYSQL里面就怎么操作！没什么大区别，当然小区别还是有的。比如；insert命令，SQL SERVER2000 一次只能插入一条记录，MYSQL可以插入多条记录。Insert into 表 (字段表列表) values（字段值），（字段值）。删除，更新和SQL SER

7、VER没有什么区别，不再累述！13、删除数据库：DROP DATABASE 数据库名14、删除表： DROP TABLE 表名15、表改名： RENAME TABLE 旧表中 TO 新表名 数据库不能改名，但也不是绝对不能改，但改不好会造成里面的数据无法正常读出，后果自负！16、库改名： 常用方法是把新库创建出来,然后把旧库里的数据导入进去.五、查询： 这应该是SQL中的一大块。SELECT 查询：也和SQL SERVER没有什么大差别：关键是大家要熟练运用各种运算符，数学运算符比较简单，重点在于字符型运算符LIKE、关系运算符和逻辑运算符如：查找姓王的记录：SELECT * FROM YUA

8、NGONG WHERE 姓名 like 王%;查找姓名中有王的记录：SELECT * FROM YUANGONG WHERE 姓名 like %王%;查找以王结尾的记录：SELECT * FROM YUANGONG WHERE 姓名 like %王;其中linit是SQL SERVER没有的。例如：我想显示第三条到第七条记录select * from 表名 limit 2,5;通配符描述示例%通配零个或多个任意字符_(下划线)通配任意一个字符不再含有 不同于SQL SERVER注意：如果用like发现结果不正确，有可能是编码的问题六、记录排序利用order by 对记录进行排序格式：selec

9、t 字段名列表 from 表名 where 条件 order by 排序字段1 asc desc 排序字段2如：按年龄对yuangong表进行升序排列！Select * from yuangong order by 年龄 asc 或 select * from yuangong order by 年龄如：按年龄对yuangong表进行降序排列！Select * from yuangong order by 年龄 desc对员工表先按性别升序排列，性别相同的再按年龄从大到小排序Select * from 员工表 order by 性别 asc,年龄 desc 七、聚集函数：最大值：max() 最

10、小值 min() 平均值avg() 求和: sum() 汇总： count ()如：求每个部门的基本工资平均值select 部门,avg(基本工资) as 部门基本工资 from 员工表 group by 部门显示平均基本工资大于3000的部门Select 部门,avg(基本工资) from 员工表 group 部门 where avg(基本工资)3000此句错误。SQL规定在分组中使用条件不能用 WHERE 而是用 havingSelect 部门,avg(基本工资) from 员工表 group by 部门 having avg(基本工资)3000八、多表查询：一个数据库中的多个表,存在一定

11、的联系,怎么样正常的显示这么表的信息?现在有三个表:ygName sex age 宋洋 男 20 马冬旭 女 40 GsName title date 单位宋洋 AD详解 2006-11-10 清华大学马冬旭 linux 2005-01-01 人民大学dz单位 地址清华大学 五道口人民大学 黄庄第一种方法称为:交叉连接,在SQL SERVER中又称为笛卡尔乘积但是要注意的默认生成的记录总数是两表记录之积select * from yg,gs;select * from yg,gs where =;这才是我们想要的记录第二种方法是用join连接:内连接select *

12、 from yg join gs on = 左外连接右外连接但没有全外连接九、联合：除了连接，mysql4。0以上的版本还支持UNION运算符,它用来把多个select查询号的输出连接成一个单独的结果集。大多数情况下，这个运算符用来把查询产生的结果集添加到不同的表，同时创建包括所有结果的一个单独表。比如面试的时候问你，有两个表，字段信息一样，让你用一条语句把两个表的信息组合为一个单独的表！为了说明UNION运算符的使用方法，我们举一个例子：现在有两个表，分别存放的是男同学信息和女同学信息，如果用一个语句将所有同学的信息显示出来！mysql select * from

13、 nan;+-+-+| name | score |+-+-+| 彭聪留 | 80 | 费优优 | 81 | 曲权 | 82 |+-+-+3 rows in set (0.00 sec)mysql select * from nv;+-+-+| name | score |+-+-+| 彭红 | 80 | 费红 | 81 | 曲红 | 82 |+-+-+3 rows in set (0.00 sec)mysql select * from nan union select * from nv;+-+-+| name | score |+-+-+| 彭聪留 | 80 | 费优优 | 81 | 曲

14、权 | 82 | 彭红 | 80 | 费红 | 81 | 曲红 | 82 |+-+-+6 rows in set (0.00 sec)那如果有三个表怎么办？也是一样的操作！但注意的是如果两个表的某条记录信息完全一致，则只显示为一条；如果想显示全部记录则在union后 加 allmysql select * from nan union all select * from nv;如果面试官又问你，如果想把显示的信息保存到一个表中怎么办？mysql create table 表名 select 语句；十、数据库备份与恢复备份:第一种方法: mysqldump备份一个表 mysqldump -h主机

15、名 u用户名 p口令 数据库 表名 文本文件 恢复: mysql -h 主机名 u用户名 p口令 数据库 文本文件 恢复的时候: mysql h 主机名 u用户名 p 口令 数据库文本文件名如:C:testmysqldump -uroot -p111111 net14 net14.txt删除数据库net14: drop database net14进行恢复:C:testmysql -u root -p111111 net14mysql -u root -p111111 net14文本文件名如: C:testmysqldump -uroot -p111111 -B net14 net28 net

16、1428.txt然后删除net14和net28再进行恢复但是要注意:必须一个一个的恢复,不能同时恢复两个:如: C:testmysql -uroot -p111111 -B net14 mysql -uroot -p111111 -B net28 select * from student into outfile c:abc1.txt;Query OK, 13 rows affected (0.00 sec)注意:1.路径中的盘符后是两个,其中第一个代表转义作用,第二个才是代表根目录.有时写成一个时不会报错,因此要注意检查备份的正确性;2.不允许重写文本文件;恢复方法:那么怎么恢复呢:用LO

17、AD DATA来恢复:格式: load data infile 路径及文件名 into table 表名如: load data infile c:student.txt into table student;注意:表必须存在.可用delete,清空其中的所有记录 或者用: truncate 表名,只删除记录,不删除结构!如果恢复出错怎么办?1.权限问题.2.分界符不匹配!3.路径和文件名不对!-数据的导入/导出：-如何与其他数据源之间进行数据的导入与导出!例如:如何将 SQL server 的数据导到mysql中来1.先在MS SQL 2000的导入导出工具将数据导出成*.txt文件格式注意

18、打开backup.txt观察其中的分隔字符 MS SQL 2000好像是用逗号分隔的2.在mysql中利用 load data infile 命令导入mysql load data infile c:sql.txt into table abc fields terminated by ,;Query OK, 5 rows affected (0.00 sec)Records: 5 Deleted: 0 Skipped: 0 Warnings: 0注意 目标表必须已经存在,并结构要与源表的结构相同!ACCESS 导到 MYSQL：1先建立一个access文件，保存成文本文件2打开文本文件，再转

19、换一下编码成ansi3建立数据库，导入到mysql中！mysql load data infile c:abc1.txt into table abc fields terminated by ,;Query OK, 3 rows affected (0.00 sec)EXCEL 导到mysql 步骤同上。只是要注意的是：excel默认是以TAB分隔的所以应用以下的语句：mysql load data infile c:book1.txt into table abc fields terminated by t;关于mysql 的具体内容我们就介绍这么多,下面我们来看一下php的搭建环境.因

20、为现在apache+mysql+php称为网站设计的经典组合.这是大家必须要掌握的.-修改字符集-在安装mysql5.0时可以设置好mysql的字符集，一般使用utf8的字符集1查看建立表“tablename”的sql语句Show create table tablename;/查看建立表“tablename”的sql语句| maos_mail_batch | CREATE TABLE maos_mail_batch (ID varchar(32) character set latin1 NOT NULL,BATCH_TIME varchar(30) character set latin1

21、 NOT NULL,BATCH_INTRODUCE varchar(2000) character set latin1 default NULL,BATCH_SUM int(11) default NULL,BATCH_STATE varchar(1) character set latin1 default NULL,PRIMARY KEY (ID) ENGINE=MyISAM DEFAULT CHARSET=utf8 |2修改数据库和表的字符集alter database maildb default character set utf8;/修改数据库的字符集alter table ma

22、iltable default character set utf8;/修改表的字符集如果您想要把表默认的字符集和所有字符列（CHAR, VARCHAR, TEXT）改为新的字符集，应使用如下语句：ALTER TABLEtbl_nameCONVERT TO CHARACTER SETcharset_name;警告：前面的操作转换了字符集之间的列类型。如果您有一列使用一种字符集（如latin1），但是存储的值实际上使用了其它的字符集（如utf8），这种情况不是您想要的。此时，您必须对这样的列进行以下操作。ALTER TABLE t1 CHANGE c1 c1 BLOB;ALTER TABLE t

23、1 CHANGE c1 c1 TEXT CHARACTER SET utf8;这种方法能够实现此功能的原因是，当您转换到BLOB列或从BLOB列转换过来时，并没有发生转换。3 mysql的主键问题：Mysql的两种主键。Primary key 和not null auto_incriment在建立mysql表时，给一个字段添加了主键primary key在insert数据时可以不用insert主键，mysql会自动添加0，但是在第二次insert时没有填写值mysql数据库还是默认添加0，会导致有重复的主键，这是不可以的。所有在定义了primary key时，在insert数据时要给主键填写值

24、。在建立mysql表时，给一个字段添加了主键not null auto_increment=1;这也是一个主键。时自增长的以1为开始。这个字段是可以不用填写值的，mysql数据库会自动给填写值，不会哦出现primary key的状况。Alter table tb add primary key(id);Alter table tb change id id int(10) not null auto_increment=1;4 删除自增长的主键id先删除自增长在删除主键Alter table tb change id id int(10);/删除自增长Alter table tb drop p

25、rimary key;/删除主建-有关外键的说明-1. 什么是参照完整性?参照完整性(完整性约束)是数据库设计中的一个重要概念，当数据库中的一个表与一个或多个表进行关联时都会涉及到参照完整性。比如下面这个例子：文章分类表 - categoriescategory_id name1 SQL Server2 Oracle3 PostgreSQL5 SQLite文章表 - articlesarticle_id category_id title1 1 aa2 2 bb3 4 cc可见以上两个表之间是通过category_id，其中categories表有4条记录，articles表有3条记录。然而可

26、能因为某种原因我们删掉了categories 表中category_id=4的记录，而articles表却还是有一条category_id=4的记录，很明显，category_id=4的这条记录不应该存在在articles表中，这样会很容易造成数据错乱。相反，外键关系(Foreign Key relationships)讨论的是父表(categories)与子表(articles)的关系，通过引入外键(Foreign Key)这个概念来保证参照完整性(Referential integrity)，将使会数据库变的非常简单。比如，要要做到删除categories表中category_id=4记录

27、的同时删除 articles 表中category_id=4的所有记录，如果没有引入外键的话，我们就必须执行2条SQL语句才行；如果有外键的话，可以很容易的用一条SQL语句就可以达到要求。2. 使用外键的条件MySQL只在v3.23.34版本以后才引入外键的，所以在这之前的版本就别想了:)，除此之外，还必须具备以下几个条件： 1) 在f配置文件中打开InnoDB引擎支持。 # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /var/db/mysql/ innodb_data_

28、file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /var/db/mysql/ innodb_log_arch_dir = /var/db/mysql/ 2) 相关联的表都必须采用InnoDB引擎。 3) 相关联的字段都必须建立所以。 MySQL v4.0版本以后，定义外键时会自动建立所以，所以在 v4.0 版本以前（含v4.0版本）必须手工定义索引。 4) 相关联的字段必须采用类似的数据类型，或者说可转换的数据类型，当然相同类型是最好不过了。 比如父表的字段是TINYINT类型，则子表只能采用TINYINT、SMAL

29、LINT、INT、BIGINT等几种类型。3. 外键语法参考可以通过 CREATE TABLE 或者 ALTER TABLE 来定义外键。CREATE TABLE 语法：CREATE TEMPORARY TABLE IF NOT EXISTS tbl_name (create_definition,)create_definition: column_definition| CONSTRAINT symbol FOREIGN KEY index_name (index_col_name,) reference_definitioncolumn_definition: col_name type

30、 NOT NULL | NULL DEFAULT default_value AUTO_INCREMENT UNIQUE KEY | PRIMARY KEY COMMENT string reference_definitionindex_col_name: col_name (length) ASC | DESCreference_definition: REFERENCES tbl_name (index_col_name,) MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ON DELETE reference_option ON UPDATE ref

31、erence_optionreference_option: RESTRICT | CASCADE | SET NULL | NO ACTIONALTER TABLE 语法：ALTER IGNORE TABLE tbl_name alter_specification , alter_specification alter_specification:| ADD CONSTRAINT symbol PRIMARY KEY index_type (index_col_name,)| ADD CONSTRAINT symbol UNIQUE INDEX index_name index_type

32、(index_col_name,)| ADD CONSTRAINT symbol FOREIGN KEY index_name (index_col_name,) reference_definition| DROP FOREIGN KEY fk_symbol4. 定义外键mysqlCREATE TABLE categories ( - category_id tinyint(3) unsigned NOT NULL AUTO_INCREMENT, - name varchar(30) NOT NULL, - PRIMARY KEY(category_id) - ) ENGINE=INNODB

33、;Query OK, 0 rows affected (0.36 sec)mysqlINSERT INTO categories VALUES (1, SQL Server), (2, Oracle), (3, PostgreSQL), (4, MySQL), (5, SQLite);Query OK, 5 rows affected (0.48 sec)Records: 5 Duplicates: 0 Warnings: 0mysqlCREATE TABLE members ( - member_id INT(11) UNSIGNED NOT NULL, - name VARCHAR(20)

34、 NOT NULL, - PRIMARY KEY(member_id) - ) ENGINE=INNODB;Query OK, 0 rows affected (0.55 sec)mysqlINSERT INTO members VALUES (1, test), (2, admin);Query OK, 2 rows affected (0.44 sec)Records: 2 Duplicates: 0 Warnings: 0mysqlCREATE TABLE articles ( - article_id INT(11) unsigned NOT NULL AUTO_INCREMENT,

35、- title varchar(255) NOT NULL, - category_id tinyint(3) unsigned NOT NULL, - member_id int(11) unsigned NOT NULL, - INDEX (category_id), - FOREIGN KEY (category_id) REFERENCES categories (category_id), - CONSTRAINT fk_member FOREIGN KEY (member_id) REFERENCES members (member_id), - PRIMARY KEY(artic

36、le_id) - ) ENGINE=INNODB;Query OK, 0 rows affected (0.63 sec)注意：对于非InnoDB表，FOREIGN KEY子句会被忽略掉。如果遇到如下错误：ERROR 1005: Cant create table ./test/articles.frm (errno: 150)请仔细检查以下定义语句，常见的错误一般都是表类型不是INNODB、相关联的字段写错了、缺少索引等等。至此categories.category_id和articles.category_id、members.member_id和 articles.member_id已经

37、建立外键关系，只有 articles.category_id 的值存在与 categories.category_id 表中并且articles.member_id的值存在与members.member_id表中才会允许被插入或修改。例如：mysqlINSERT INTO articles (category_id, member_id, title) VALUES (6, 1, foo);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (test/articles, CO

38、NSTRAINT articles_ibfk_1 FOREIGN KEY (category_id)REFERENCES categories (id)mysqlINSERT INTO articles (category_id, member_id, title) VALUES (3, 3, foo);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (test/articles, CONSTRAINT fk_member FOREIGN KEY (member_id) REFERENCES members (member_id)可见上面两条语句都会出现错误，因为在categories表中并没有category_id=6、members表中