传智播客-韩顺平-Mysql数据库

1、北京传智播客教育 ,php数据库(mysql)编程,讲师：韩顺平,主讲 韩顺平,php 培训日志 从入门到精通,主讲：韩顺平,email：,本章节的讲解：参考视频jdbc详解关于mysql数据库部分!,主讲 韩顺平,php进阶-内容介绍,(一) 项目演示 (二) php数据库编程-mysql 2.1 mysql数据库-基础部分 2.2 mysql数据库-加强部分1 - mysql表类型和存储引擎 - mysql中事务处理 2.3 mysql数据库-加强部分2 - mysql表的基本查询加强 - mysql表的复杂查询 - 维护数据的完整性约束 - mysql内连接、外连接 2.4 mysql数

2、据库-加强部分3 - 维护数据的完整性约束 - mysql表自增 - mysql索引,主讲 韩顺平,php进阶-雇员管理系统演示,主讲 韩顺平,php进阶-oa(办公自动化)系统演示,主讲 韩顺平,php数据库编程-mysql,1. 数据库的基本概念 2. mysql数据库的基本操作 3. mysql的curd操作 4. mysql常用函数 5. php如何操作mysql数据库,主讲 韩顺平,学习目标,1. mysql基本操作 2. mysql简单的crud操作 3. php操作mysql,主讲 韩顺平,一个问题,淘宝网，天涯网,校友网, 雇员管理系统,留 言本.都有各自的功能,那么当我们关闭

3、系统的 时候，下次再访问这些网站时，为什么他们 各自的信息还存在? 再比如c/s的软件，比如网游、qq、他们又是 怎样保存数据的? a.游戏积分 b.qq聊天记录.,主讲 韩顺平, 解决之道-文件、数据库,我们刚学习过文件,大家可能回答用文件就可以保存数据嘛! 没有错，可以如果用文件保存数据存在几个缺点: (1)文件的安全性问题 (2)文件不利于查询和对数据的管理 (3)文件不利于存放海量数据 (4)文件在程序中控制不方便,主讲 韩顺平, 解决之道-文件、数据库,为了解决上述问题,专家们设计出更加利于管理数据的东东-数据库，它能更有效的管理数据。数据库是衡量一个程序员水平的重要指标。 举一个生

4、活化的案例说明 如果说 图书馆是保存书籍的,那么数据库就 是保存数据的。,主讲 韩顺平,数据库简介 SQL Server、Oracle、 MySQL、DB2、 SyBase * 对当前主流数据库做一个比较介绍 MySQL数据库的安装和配置 使用命令行窗口连接MYSQL数据库 mysql h 主机名 u用户名 p密码,启动mysql数据库的常用方式： 服务方式启动（界面） net stop mysql服务名 net start mysql服务名,主讲 韩顺平,数据库服务器、数据库和表的关系,MySQL dbms,DB,DB,数据对象(表),数据对象(表),数据对象(表),Client,所谓安装数

5、据库服务器，只是在机器上装了一个数据库管理程序，这个管理程序可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。 为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体的数据。 数据库服务器、数据库和表的关系如图所示：,主讲 韩顺平,数据在数据库中的存储方式,雇员表(employee),行(row),列(column),表的一行称之为一条记录 表中一条记录对应一个php对象的数据,主讲 韩顺平,SQL语句分类,DDL : 数据定义语句 (create alter drop) DML : 数据操作语句 (insert update delete) DQL : 数据查询语句

6、(select) DCL : 数据控制语句(grant revoke commit rollback),小面试题：mysql数据库的sql语句分几类?,主讲 韩顺平,创建数据库,CREATE DATABASE IF NOT EXISTS db_name create_specification , create_specification . create_specification: DEFAULT CHARACTER SET charset_name | DEFAULT COLLATE collation_name,CHARACTER SET：指定数据库采用的字符集 COLLATE：指定数

7、据库字符集的比较方式,练习： 创建一个名称为db1的数据库。 创建一个使用utf-8字符集的db2数据库。 创建一个使用utf-8字符集，并带校对规则的mydb3数据库,主讲 韩顺平,查看、删除数据库,显示数据库语句： SHOW DATABASES 显示数据库创建语句： SHOW CREATE DATABASE db_name 数据库删除语句： DROP DATABASE IF EXISTS db_name,练习： 查看当前数据库服务器中的所有数据库 查看前面创建的db2数据库的定义信息 删除前面创建的db1数据库,主讲 韩顺平,修改、备份、恢复数据库,ALTER DATABASE IF EX

8、ISTS db_name alter_specification , alter_specification . alter_specification: DEFAULT CHARACTER SET charset_name | DEFAULT COLLATE collation_name | ENGINE|TYPE = engine_name /表存储引擎再事务是再讲解,备份数据库表中的数据 mysqldump -u 用户名 -p 数据库名 文件名.sql 恢复数据库 Source 文件名.sql,练习 1 查看服务器中的数据库，并把其中某一个库的字符集修改为utf8; 2 备份test库中

9、的数据，并恢复,主讲 韩顺平,体验Mysql-安装ecshop,安装的时候，可能在创建管理员会报错误，解决方法如下: 重新设置 date.timezone = PRC ，需要重新启动apache才能生效. Date ; Defines the default timezone used by the date functions ; date.timezone = PRC,主讲 韩顺平,创建表(基本语句),CREATE TABLE table_name ( field1 datatype, field2 datatype, field3 datatype, )character set 字符集

10、 collate 校对规则 engine 存储引擎方式 field：指定列名datatype：指定列类型,注意：创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型。例：user表 id 整形 name 字符串 password 字符串 birthday 日期,主讲 韩顺平,mysql 常用的数据类型,VARCHAR、BLOB和TEXT类是变长类型。每个类型的存储需求取决于列值的实际长度。,主讲 韩顺平,mysql 常用的数据类型,主讲 韩顺平,mysql 字符集和校验规则,查询的乱码出现原因: character_set_clinet 设置和客户端实际发出的不一致. c

11、harset_set_results 设置和客户端实际需要的编码不一致.,主讲 韩顺平,mysql 字符集和校验规则,collate : 校对规则 指: 在对某个字段排序时，按照怎样的规则来排序 这里我们先简单举一个案例，大家了解即可. 请查看Mysql关于校对规则手册,主讲 韩顺平,创建表练习,创建一个员工表,主讲 韩顺平,修改表,使用 ALTER TABLE 语句追加, 修改, 或删除列的语法.,ALTER TABLE tablename ADD (column datatype DEFAULT expr , column datatype.);,ALTER TABLE tablename

12、 MODIFY (column datatype DEFAULT expr , column datatype.);,ALTER TABLE tablename DROP (column);,修改表的名称：Rename table 表名 to 新表名 修改表的字符集：alter table student character set utf8;,主讲 韩顺平,修改表,练习 在上面员工表的基本上增加一个image列。 修改job列，使其长度为60。 删除sex列。 表名改为user。 修改表的字符集为utf-8 列名name修改为username alter table user change

13、column name username varchar(20);,主讲 韩顺平,帮助命令的使用,在mysql使用中，我们可能忘记一个mysql命令的使用，我们可以通过 帮助命令 ? 来查看相关命令的使用 方式1. ？ create 方式2 ? create table,提示: 控制台给出的都是英文说明，如果英文不好，还是 用mysql中文手册吧!,主讲 韩顺平,数据库CRUD语句,Insert语句 (增加数据) Update语句 (更新数据) Delete语句 (删除数据) Select语句(查找数据),主讲 韩顺平,Insert语句,INSERT INTOtablename (column

14、 , column.) VALUES(value , value.);,使用 INSERT 语句向表中插入数据。, 注意事项 插入的数据应与字段的数据类型相同。 数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。 在values中列出的数据位置必须与被加入的列的排列位置相对应。 字符和日期型数据应包含在单引号中。 插入空值，不指定或insert into table value(null),主讲 韩顺平,Insert语句练习,注意：字符和日期要包含在单引号中。 show variables like character%; set character_se

15、t_results=gbk;,练习：使用insert语句向表中插入三个员工的信息。,主讲 韩顺平,update语句,UPDATE tbl_name SET col_name1=expr1 , col_name2=expr2 . WHERE where_definition,使用 update语句修改表中数据。, 注意事项: UPDATE语法可以用新值更新原有表行中的各列。 SET子句指示要修改哪些列和要给予哪些值。 WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。,主讲 韩顺平,update语句练习,练习：在上面创建的employee表中修改表中的纪录。,要求 将所有员工

16、薪水修改为5000元。 将姓名为zs的员工薪水修改为3000元。 将wu的薪水在原有基础上增加1000元。,主讲 韩顺平,delete语句,delete from tbl_name WHERE where_definition,使用 delete语句删除表中数据。, 注意事项: 如果不使用where子句，将删除表中所有数据。 Delete语句不能删除某一列的值（可使用update） 使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。 同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记

17、这个潜在的问题。 删除表中数据也可使用TRUNCATE TABLE 语句，它和delete有所不同，参看mysql文档。,主讲 韩顺平,delete语句练习,删除表中名称为zs的记录。 删除表中所有记录。 使用truncate删除表中记录。,主讲 韩顺平,Select语句(1),SELECT DISTINCT *|column1, column2. column3. FROMtablename;, 注意事项 (需要创建新的学生表) Select 指定查询哪些列的数据。 column指定列名。 *号代表查询所有列。 From指定查询哪张表。 DISTINCT可选，指显示结果时，是否剔除重复数据,

18、基本select语句,主讲 韩顺平,Select语句(1),练习: 查询表中所有学生的信息。 查询表中所有学生的姓名和对应的英语成绩。 过滤表中重复数据。,主讲 韩顺平,Select语句(2),SELECT *|column1expression, column2expression，. FROMtablename;,在select语句中可使用表达式对查询的列进行运算,在select语句中可使用as语句,SELECT columnname as 别名 from 表名;,主讲 韩顺平,Select语句(2),练习 在所有学生分数上加10分特长分(即查询所有学生总分再加10分)。 统计每个学生的总

19、分。 使用别名表示学生分数。,主讲 韩顺平,Select语句(3),使用where子句，进行过滤查询。练习： 查询姓名为wu的学生成绩 查询英语成绩大于90分的同学 查询总分大于200分的所有同学,主讲 韩顺平,Select语句(4),在where子句中经常使用的运算符,Like语句中，% 代表零个或多个任意字符，_ 代表一个字符，例first_name like _a%;,主讲 韩顺平,Select语句(4),查询英语分数在 8090之间的同学。 查询数学分数为89,90,91的同学。 查询所有姓李的学生成绩。 查询数学分80，语文分80的同学。,主讲 韩顺平,Select语句(5),SEL

20、ECT column1, column2. column3. FROMtable; order by column asc|desc,使用order by 子句排序查询结果。,Order by 指定排序的列，排序的列即可是表中的列名，也可以是select 语句后指定的列名。 Asc 升序、Desc 降序 ORDER BY 子句应位于SELECT语句的结尾。 练习： 对数学成绩排序后输出。 对总分排序后输出，然后再按从高到低的顺序输出 对姓李的学生成绩排序输出,主讲 韩顺平,合计函数count,Select count(*)|count(列名) from tablename WHERE wher

21、e_definition,练习： 统计一个班级共有多少学生？ 统计数学成绩大于90的学生有多少个？ 统计总分大于250的人数有多少？,Count(列名)返回某一列，行的总数,主讲 韩顺平,合计函数sum,Select sum(列名),sum(列名) from tablename WHERE where_definition,练习： 统计一个班级数学总成绩？ 统计一个班级语文、英语、数学各科的总成绩 统计一个班级语文、英语、数学的成绩总和 统计一个班级语文成绩平均分 注意：sum仅对数值起作用，否则会报错。 注意：对多列求和，“，”号不能少。,Sum函数返回满足where条件的行的和,主讲 韩顺

22、平,合计函数avg,Select sum(列名),sum(列名) from tablename WHERE where_definition,练习： 求一个班级数学平均分？ 求一个班级总分平均分,AVG函数返回满足where条件的一列的平均值,主讲 韩顺平,合计函数max/min,Select max(列名)from tablename WHERE where_definition,练习： 求班级最高分和最低分（数值范围在统计中特别有用）,Max/min函数返回满足where条件的一列的最大/最小值,主讲 韩顺平,Select语句(6),SELECT column1, column2. col

23、umn3. FROMtable group by column,练习：对订单表中商品归类后，显示每一类商品的总价,使用group by 子句对列进行分组,SELECT column1, column2. column3. FROMtable group by column having .,使用having 子句过滤,练习：查询购买了几类商品，并且每类总价大于100的商品,Having和where均可实现过滤，但在having可以使用合计函数,having通常跟在group by后，它作用于组。,主讲 韩顺平,时间日期相关函数,示例：select date_add(entry_date,INT

24、ERVAL 2 year) from student;增加两年,* 特别注意date_add() date_sub() datediff() timediff() 参与运算的可以是date/datetime/timestamp均可 * 查看 mysql 的帮助文档即可知道 d_type有哪些,主讲 韩顺平,字符串相关函数,主讲 韩顺平,数学相关函数,rand()返回一个随机浮点值 v ，范围在 0 到1 之间 (即, 其范围为 0 v 1.0)。若已指定一个整数参数 N ， 则它被用作种子值，用来产生重复序列。,主讲 韩顺平,流程控制函数,随机设计案例，比如emp表的案例.来想一想吧!(根据职

25、位不同来加薪的案例!),主讲 韩顺平,其它函数,主讲 韩顺平,mysql中文乱码,mysql有六处使用了字符集，分别为：client 、connection、database、results、server 、system。 client是客户端使用的字符集。 connection是连接数据库的字符集设置类型，如果程序没有指明连接数据库使用的字符集类型就按照服务器端默认的字符集设置。 database是数据库服务器中某个库使用的字符集设定，如果建库时没有指明，将使用服务器安装时指定的字符集设置。 results是数据库给客户端返回时使用的字符集设定，如果没有指明，使用服务器默认的字符集。 ser

26、ver是服务器安装时指定的默认字符集设定。 system是数据库系统使用的字符集设定。,主讲 韩顺平,php操作mysql数据库实例,/1.对mysql完成查询,得到连接 $conn=mysql_connect(localhost,root,root); /选择数据 mysql_select_db(hspdb1,$conn); /查询数据,返回结果 $res=mysql_query(select * from users,$conn); /得到查询到多少记录 $recs=mysql_num_rows($res); echo 共.$recs. 条记录; /取出记录. while($row=mys

27、ql_fetch_array($res) /打印 echo 编号:.$row0. 名称 .$row1. 邮件.$row2. ; /添加记录 / mysql_query(insert into users (name,email)values(abc1,beijing),$conn) /修改 /mysql_query(update users set email= where id=4,$conn); /删除 mysql_query(delete from users where id=4,$conn); echo 执行ok!;,主讲 韩顺平,php数据库编程(2)-mysql,1. mysql

28、表类型和存储引擎 2. mysql中事务处理,主讲 韩顺平,mysql表类型和存储引擎,介绍: MySQL的表类型由存储引擎（Storage Engines）决定，类型包括MyISAM、innoDB、BDB等。,MySQL 数据表主要支持六种类型 ，分别是：BDB、HEAP、ISAM、MERGE、MYISAM、InnoBDB。(参考mysql文档.) 这六种又分为两类，一类是”事务安全型”(transaction-safe)，包括BDB和InnoDB；其余都属于第二类，称为”非事务安全 型”(non-transaction-safe)。,显示当前数据库支持的存储引擎: show engines

29、;,主讲 韩顺平,mysql表类型和存储引擎,主讲 韩顺平,mysql表类型和存储引擎,我这里重点给大家介绍三种: MyISAM、InnoDB、MEMORY (Heap), MyISAM不支持事务、也不支持外键，但其访问速度快，对事 务完整性没有要求 InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事 务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。 MEMORY存储引擎使用存在内存中的内容来创建表。 每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH

30、索引。但是一旦服务关闭，表中的数据就会丢失掉 。,主讲 韩顺平,mysql表类型和存储引擎, 案例说明:,对前面我们提到的三种存储引擎，我们举例说明:, 如何选择表的存储引擎,如果你的应用是不需要事务，处理的只是基本的CRUD操作，那么MyISAM是不二选择 一般来说，如果需要事务支持，并且有较高的并发读写 频率，InnoDB是不错的选择。 Heap 存储引擎就是将数据存储在内存中，由于没有磁盘I./O的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。(经典用法 用户的在线状态.),主讲 韩顺平,mysql表类型和存储引擎, 修改存储引擎,ALTER TABLE 表

31、名 ENGINE = 储存引擎;,主讲 韩顺平,mysql中事务处理, 什么是事务 事务用于保证数据的一致性,它由一组相关的dml语句组成,该组的dml语句要么全部成功，要么全部失败。如:网上转账就是典型的要用事务来处理，用以保证数据的一致性。,当执使用commit语句可以提交事务.当执行了commit语句子后,会确认事务的变化、结束事务、删除保存点、释放锁，当使用commit语句结束事务子后，其它会话将可以查看到事务变化后的新数据, 提交事务,当执行事务操作时(dml语句）,mysql会在被作用的表上加锁,防止其它用户改表表的结构.这里对我们用户来讲是非常重要的。, 事务和锁,主讲 韩顺平,

32、mysql中事务处理, 回退事务 在介绍回退事务前，我们先介绍一下保存点(savepoint)的概念和作用.保存点是事务中的一点.用于取消部分事务，当结束事务时，会自动的删除该事务所定义的所有保存点. 当执行rollback时，通过指定保存点可以回退到指定的点,这里我们作图说明 mysql 数据库控制台事务的几个重要操作 0) start transaction /开始一个事务 1) savepoint 保存点名 /设置保存点 2) rollback to 保存点名 /取消部分事务 3) rollback /取消全部事务 4) commit /提交事务.,主讲 韩顺平,php程序中事务处理,

33、php程序中如何使用事务 在php操作数据库时，为了保证数据的一致性，比如转帐操作(图):(1)从一个帐户减掉10$(2)在另一个帐户上加入10$,我们看看如何使用事务?,主讲 韩顺平,mysql事务处理事务隔离级别,多个线程开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个线程在获取数据时的准确性。 如果不考虑隔离性，可能会引发如下问题： 1. 脏读 2. 不可重复读 3. 幻读(虚读),主讲 韩顺平,mysql事务处理事务隔离级别, 事务隔离级别 概念:隔离级别定义了事务与事务之间的隔离程度。 ANSI/ISO SQL92标准定义了一些数据库操作的隔离级别(这是国际标准化

34、组织定义的一个标准而已，不同的数据库在实现时有所不同)：,V 可能出现 x 不会出现,主讲 韩顺平,mysql事务处理事务隔离级别, 事务隔离级别,我们待会举例一个案例来说明mysql的事务隔离级别. 以对 account表的操作来说明上面的几种情况.,脏读（dirty read)：当一个事务读取另一个事务尚未提交的修改时，产生脏读。 不可重复读（nonrepeatable read)：同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生非重复读。 幻读（phantom read)：同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回

35、不同的结果集，此时发生幻读。,主讲 韩顺平,mysql事务处理事务隔离级别,1.查看当前会话隔离级别 select tx_isolation; 2.查看系统当前隔离级别 select global.tx_isolation; 3.设置当前会话隔离级别 set session transaction isolation level repeatable read; 4.设置系统当前隔离级别 set global transaction isolation level repeatable read; 5. mysql 默认的事务隔离级别是 repeatable read ,一般情况下，没有特殊要

36、求，没有必要修改（因为该级别可以满足绝大部分项目需求）,主讲 韩顺平,mysql事务处理事务隔离级别, mysql的事务隔离级-案例,我们举例一个案例来说明mysql的事务隔离级别. 以对account表 进行操作为例。,mysql控制台,mysql控制台,主讲 韩顺平,mysql事务处理事务隔离级别, mysql的事务隔离级在php程序中如何使用 没有完成测试: connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); 特别说明: java程序中Connection.XXXXXX有五个，但不是所有的数

37、据库对有对应的五个事务隔离级别实现。 在实际工作中，我们极少去修改各个数据库默认的隔离级别。,主讲 韩顺平,mysql中事务处理事务的acid特性, 原子性（Atomicity）原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。 一致性（Consistency）事务必须使数据库从一个一致性状态变换到另外一个一致性状态。 隔离性（Isolation）事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。 持久性（Durability）持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久

38、性的，接下来即使数据库发生故障也不应该对其有任何影响,主讲 韩顺平,php数据库编程(3)-mysql,1. mysql表的基本查询加强 2. mysql表的复杂查询 3. 维护数据的完整性约束,主讲 韩顺平,mysql表的基本查询-加强, 介绍,-公欲善其事，必先利其器,在前面我们讲过mysql表的基本查询，但是都是对一张表进行的查询，这在实际的软件开发中，还远远的不够。 下面我们讲解的过程中，将模拟一个简单的公司管理系统,创建 三张表(emp,dept,salgrade)为大家演示如何进行多表查询，select语句在软件编程中非常的有用，希望大家好好的掌握。,主讲 韩顺平, 介绍主键和外键

39、,mysql表的基本查询-加强,主讲 韩顺平, 查看表结构 查询所有列 查询指定列 如何取消重复行 ?查询SMITH 的薪水,工作,所在部门,mysql表的基本查询-加强,主讲 韩顺平, 使用算数表达式 ?显示每个雇员的年工资 使用列的别名 select ename 姓名,sal*12 as 年收入 from emp;,mysql表的基本查询-加强,主讲 韩顺平, 使用where子句 ?如何显示工资高于3000的员工 ?如何查找1982.1.1后入职的员工 ?如何显示工资在2000到2500的员工情况 如何使用like操作符 %: 表示0到多个字符 _: 表示单个字符 ?如何显示首字符为S的员

40、工姓名和工资 ?如何显示第三个字符为大写O的所有员工的姓名和工资 在where条件中使用in ?如何显示empno为 123,345,800.的雇员情况 使用is null的操作符 ?如何显示没有上级的雇员的情况,mysql表的基本查询-加强,主讲 韩顺平, 使用逻辑操作符号 ?查询工资高于500或是岗位为MANAGER的雇员，同时还要 满足他们的姓名首写字母为大写的J 使用order by字句 ?如何按照工资的从低到高的顺序显示雇员的信息 ?按照部门号升序而雇员的工资降序排列 使用列的别名排序 select ename,sal*12 年薪 from emp order by 年薪 asc;

41、别名需要使用“ 号圈中,mysql表的基本查询-加强,主讲 韩顺平, 分页查询 按雇员的id号升序取出,mysql表的基本查询-加强,主讲 韩顺平,mysql表的复杂查询, 说明 在实际应用中经常需要执行复杂的数据统计，经常需要显示 多张表的数据，现在我们给大家介绍较为复杂的select语句 数据分组-max,min,avg,sum,count ?如何显示所有员工中最高工资和最低工资 ?显示所有员工的平均工资和工资总和 ?计算共有多少员工 扩展要求： ?请显示工资最高的员工的名字，工作岗位 ?请显示工资高于平均工资的员工信息,主讲 韩顺平,mysql表的复杂查询, group by 和 hav

42、ing子句 group by用于对查询的结果分组统计, having子句用于限制分组显示结果. ?如何显示每个部门的平均工资和最高工资 ?显示每个部门的每种岗位的平均工资和最低工资 ?显示平均工资低于2000的部门号和它的平均工资 扩展要求：,主讲 韩顺平,mysql表的复杂查询, 对数据分组的总结,1. 分组函数只能出现在选择列表、having、order by子句种 2. 如果在select 语句种同时包含有group by ,having , order by 那么他们的顺序是group by , having , order by 3. 在选择列种如果有列、表达式、和分组函数,那么这些

43、列和表达式应当有一个出现在group by 子句中。 如select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)2000; 这里deptno就应当要出现在 group by 中,主讲 韩顺平,mysql表的复杂查询多表查询, 问题的引出,后面，要求大家能够完成!,主讲 韩顺平,mysql表的复杂查询多表查询, 说明,思考题: 显示雇员名,雇员工资及所在部门的名字,并按 部门排序.,多表查询是指基于两个和两个以上的表或是视图的查询.在实际应用中,查询单个表可能不能满足你的需求,（如显示sales部门位置和其员

44、工的姓名),这种情况下需要使用到(dept表和emp表) ?显示雇员名,雇员工资及所在部门的名字 【笛卡尔集】 规定:多表查询的条件是 至少不能少于 表的个数-1 ?如何显示部门号为10的部门名、员工名和工资 ?显示各个员工的姓名，工资，及其工资的级别,主讲 韩顺平,mysql表的复杂查询多表查询, 自连接 自连接是指在同一张表的连接查询。 ?显示某个员工的上级领导的姓名 比如显示FORD的上级. 思考题: 显示公司每个员工和它的上级的名字,主讲 韩顺平,mysql表的复杂查询子查询, 什么是子查询 子查询是指嵌入在其它sql语句中的select语句,也叫嵌套查询 单行子查询 单行子查询是指只

45、返回一行数据的子查询语句 请思考：如何显示与SMITH同一部门的所有员工? 多行子查询 多行子查询指返回多行数据的子查询 请思考:如何查询和部门10的工作相同的雇员的名字、 岗位、工资、部门号,主讲 韩顺平,mysql表的复杂查询子查询, 上机练习题 查询ecshop中各个类别中，价格最高的商品.,主讲 韩顺平,mysql表的复杂查询子查询, 在多行子查询中使用all操作符 请思考:如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号 select ename,sal,deptno from emp where salall (select sal from emp where

46、deptno=30); 扩展要求：大家想想还有没有别的查询方法. Select ename,sal,deptno from emp where sal(select max(sal) from emp where deptno=30);,主讲 韩顺平,mysql表的复杂查询子查询, 在多行子查询中使用any操作符 请思考:如何显示工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号 select ename,sal,deptno from emp where sal any(select sal from emp where deptno=30) 扩展要求： 大家想想还有没有别的查询

47、方法. select ename,sal,deptno from emp where sal (select min(sal) from emp where deptno=30),主讲 韩顺平,mysql表的复杂查询子查询, 多列子查询 单行子查询是指子查询只返回单列、单行数据,多行子查询是指返回单列多行数据,都是针对单列而言的，而多列子查序则是指查询返回多个列数据的子查询语句 请思考如何查询与smith的部门和岗位完全相同的所有雇员,主讲 韩顺平,mysql表的复杂查询子查询,好好琢磨琢磨吧, 在from子句中使用子查询 请思考：如何显示高于自己部门平均工资的员工的信息 这里要用到数据查询的

48、小技巧，把一个子查询当作一个临时表使用 解法 select e1.*,e2.myavg from emp e1,(select avg(sal) myavg,deptno from emp group by deptno) e2 where e1.deptno=e2.deptno and e1.sale2.myavg 解法 select e1.* from emp e1 where e1.sal(select avg(sal) from emp where deptno=e1.deptno),主讲 韩顺平,mysql表的复杂查询子查询,好好琢磨琢磨吧, 在from子句中使用子查询 请思考：查找

49、每个部门工资最高的人的详细资料 解法 解法 思路:得到所有的员工，进行筛选，每拿到一个员工，判断该员工的工资是否是他们部门的最高工资。 select * from emp e where sal=(select max(sal) from emp where deptno=e.deptno);,主讲 韩顺平,mysql表的复杂查询子查询,好好琢磨琢磨吧, 在from子句中使用子查询课堂小练习 请思考：显示每个部门的信息和人员数量,我们一起完成.,主讲 韩顺平,mysql表的复杂查询子查询, 在from子句中使用子查询 这里需要说明的当在from子句中使用子查询时 ,该子查询会被作为一个视图来对

50、待,因此也叫 作内嵌视图，当在from子句中使用子查询时， 必须给子查询指定别名.,总结一下,主讲 韩顺平,mysql表的复杂查询子查询, 自我复制数据(蠕虫复制) 有时，为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据。 insert into mytable (id,name,sal,job,deptno) select empno,ename,sal,job,deptno from emp; 思考题：如何删除掉一张表重复记录 temp_table 是一张临时表 aa:是你要处理表（有重复行的.） select distinct * into temp_t

51、able from aa delete from aa insert into aa select * from temp_table drop table temp_table,主讲 韩顺平,mysql表的复杂查询合并查询, 合并查询 有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号 union , union all,intersect , minus 1)union 该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。 select ename,sal,job from emp where sal2500 union select en

52、ame,sal,job from emp where job=MANAGER;,主讲 韩顺平,mysql表的复杂查询合并查询,2)union all 该操作赋与union相似，但是它不会取消重复行,而且不会排序。 select ename,sal,job from emp where sal2500 union all select ename,sal,job from emp where job=manager; 3)intersectmysql测试没有成功! 使用该操作符用于取得两个结果集的交集。 select ename,sal,job from emp where sal2500 in

53、tersect select ename,sal,job from emp where job=manager;,主讲 韩顺平,mysql表的复杂查询合并查询,4) minusmysql测试没有成功 使用该操作符用于取得两个结果集的差集，它只会显示存在第一个集合中，而不存在第二个集合中的数据。 select ename,sal,job from emp where sal2500 minus select ename,sal,job from emp where job=manager;,主讲 韩顺平,mysql表的内连接和外连接, 概述 表连接分为内连接和外连接。 内连接 内连接实际上就是利

54、用 where 子句对两张表形成的笛卡尔积进行筛选,我们前面学习的查询都是内连接,也是在开发过程中用的最多的连接查询。,主讲 韩顺平,mysql表的内连接和外连接, 外连接 左外连接 (如果左侧的表完全显示我们就说是左外连接） 右外连接 (如果右侧的表完全显示我们就说是右外连接) 完全外连接(完全显示两个表，没有匹配的记录置为空?) 为了讲清楚，我们举例说明。,-表stu id name 1, Jack 2, Tom 3, Kity 4, nono,-表exam id grade 1, 56 2, 76 11, 8,主讲 韩顺平,mysql表的内连接和外连接, 外连接 1)内连接案例（显示两表

55、id匹配的） 2)左连接（显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id号,成绩显示为空） select stu.id,, exam.grade from stu left join exam on stu.id=exam.id 有的程序员喜欢这样写左外连接 select stu.id,, exam.grade from stu , exam where stu.id=exam.id(+)mysql 是否能这样写，初步测试没有成功!,基本语法: select 字段1,字段2 表名1 inner join 表名2 on 条件,主讲 韩顺平,mysql表的

56、内连接和外连接, 外连接 3)右连接（显示所有成绩，如果没有名字匹配，显示空） 4)完全外连接（显示所有成绩和所有人的名字，如果相应的匹配值，则显示空）mysql 中目前不支持外连接,可以使用union!,主讲 韩顺平,mysql表的内连接和外连接, 一个小练习 为加深大家对外连接的理解，我们做一个小练习,列出部门名称和这些部门的员工信息，同时列出那些没有员工的部 门。,主讲 韩顺平,php数据库编程(4)-mysql,1. 维护数据的完整性约束 2. mysql表自增 3. mysql索引,主讲 韩顺平,mysql维护数据的完整性, 介绍,数据的完整性用于确保数据库数据遵从一定的 商业的逻辑

57、规则。在oracle中，数据完整性可 以使用约束、触发器、过程三种方法来实现， 在这三种方法中，因为约束易于维护，并且具 有最好的性能，所以作为维护数据完整性的首选。,主讲 韩顺平,mysql维护数据的完整性约束, 约束,约束用于确保数据库数据满足特定的商业规则。 在mysql中，约束包括: not null、unique, primary key,foreign key,和check 五种.,主讲 韩顺平,mysql维护数据的完整性约束, not null(非空) 如果在列上定义了not null,那么当插入数据时，必须为列提供数据。 unique(唯一) 当定义了唯一约束后，该列值是不能重

58、复的.但是可以为null。 primary key(主键) 用于唯一的标示表行的数据,当定义主键约束后，该列不但不能重复而且不能为null。需要说明的是:一张表最多只能有一个主键,但是可以有多个unqiue约束。,字段名 字段类型 not null,字段名 字段类型 unique,主讲 韩顺平,mysql维护数据的完整性约束, primary key(主键) 用于唯一的标示表行的数据,当定义主键约束后，该列不但不能重复而且不能为null。 需要说明的是:一张表最多只能有一个主键,但是可以有多个unqiue约束。,字段名 字段类型 primary key,主讲 韩顺平,mysql维护数据的完整性

59、约束, foreign key(外键) 用于定义主表和从表之间的关系: 外键约束要定义在从表上，主表则必须具有主键约束或是unique约束.,当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null (学生/班级 图示),FOREIGN KEY (本表字段名) REFERENCES 主键表名(主键名或unique字段名),本表字段名 references 主键表名(主键名或unique字段名), 不能使用下面的方法定义外键:,主讲 韩顺平,mysql维护数据的完整性约束, foreign key(外键)细节讨论,外键指向的表的字段，必须是primary key 或者是 unique 表的类型是innodb, 这样的表才支持外键 外键字段的类型要和主键字段的类型一致(长度可以不同) 外键字段的值，必须在主键字段中出现过，或者为nu