程序员韩顺平mysql从入门到精通

Mysql从入门到精通讲师：韩顺平学习内容 主讲:韩顺平(一)项目演示(二)php数据库编程-mysql 2.1mysql数据库-基础部分 2.2mysql数据库-加强部分1 --mysql表类型和存储引擎 --mysql中事务处理 2.3mysql数据库-加强部分2 --mysql表的基本查询加强 --mysql表的复杂查询 --维护数据的完整性—约束 --mysql内连接、外连接 2.4mysql数据库-加强部分3 --维护数据的完整性—约束 --mysql表自增 --mysql索引(三)数据库用户的管理PHP数据库编程-mysql 主讲:韩顺平1.数据库的基本概念2.mysql数据库的基本操作3.mysql的curd操作4.mysql常用函数5.php如何操作mysql数据库学习目标 主讲:韩顺平1.mysql基本操作2.mysql简单的crud操作3.php操作mysql解决之道 主讲:韩顺平数据库简介SQLServer、Oracle、MySQL、DB2、informix对当前主流数据库做一个比较介绍厂家、软件工程师、类型、特点MySQL数据库的安装和配置(带学生安装)使用命令行窗口连接MYSQL数据库mysql–h主机名-P端口–u用户名–p密码启动mysql数据库的常用方式：服务方式启动（界面）netstopmysql服务名netstartmysql服务名如何在任何目录下都可以连接到mysql数据库服务器、数据库和表的关系 主讲:韩顺平所谓安装数据库服务器，只是在机器上装了一个数据库管理程序，这个管理程序可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体的数据。数据库服务器、数据库和表的关系如图所示：MySQLdbmsDBDB数据对象(表)数据对象(表)数据对象(表)Client命令行PHP文件数据在数据库中的存储方式 主讲:韩顺平雇员表(employee)行(row)列(column)表的一行称之为一条记录表中一条记录对应一个php数组或对象的数据SQL语句分类 主讲:韩顺平DDL:数据定义语句DML:数据操作语句DQL:数据查询语句DCL:数据控制语句小面试题：mysql数据库的sql语句分几类?并举例说明.创建数据库 主讲:韩顺平CREATEDATABASE[IFNOTEXISTS]db_name

[create_specification[,create_specification]...]create_specification:

[DEFAULT]CHARACTERSETcharset_name

|[DEFAULT]COLLATEcollation_nameCHARACTERSET：指定数据库采用的字符集COLLATE：指定数据库字符集的比较方式(默认utf8_general_ci，一般不指定)练习：创建一个名称为db1的数据库。创建一个使用utf8字符集的db2数据库。创建一个使用utf8字符集，并带校对规则的mydb3数据库创建数据库-细节说明 主讲:韩顺平对字符集和排序规则的说明:CHARACTERSETcharset_nameCOLLATEcollation_nameshowcharacterset;[字符集]showcollation;[排序和校对规则]案例：分开测试ci是caseinsensitive,即"大小写不敏感",a和A会在字符判断中会被当做一样的;utf8_bin是二进制,a和A会别区别对待.例如你运行:SELECT*FROMtableWHEREtxt='a'那么在utf8_bin中你就找不到txt='A'的那一行,而utf8_general_ci则可以排序规则了解Mysql字符集和校验集 主讲:韩顺平collate:校对规则指:在对某个字段排序时，按照怎样的规则来排序这里我们先简单举一个案例，大家了解即可(不用深入研究).☞请查看Mysql关于校对规则手册数据库相关操作-查看、删除数据库主讲:韩顺平显示数据库语句：SHOWDATABASES显示数据库创建语句：SHOWCREATEDATABASEdb_name

数据库删除语句：DROPDATABASE[IFEXISTS]db_name

练习：查看当前数据库服务器中的所有数据库查看前面创建的db2数据库的定义信息删除前面创建的db1数据库查看当前mysql数据库连接进程情况修改、备份恢复数据库 (重点) 主讲:韩顺平ALTERDATABASE[IFEXISTS]db_name

[alter_specification[,alter_specification]...]alter_specification:

[DEFAULT]CHARACTERSETcharset_name

|[DEFAULT]COLLATEcollation_name备份数据库表中的数据 mysqldump-u用户名-p数据库名>文件名.sql恢复数据库 Source文件名.sql练习1查看服务器中的数据库，并把其中某一个库的字符集修改为utf8;2备份test库中的数据，并恢复12修改、备份恢复数据库 主讲:韩顺平使用细节说明:mysqldump–u用户名–p密码数据库表1表2表3..>d:\\文件名.sqlmysqldump–u用户名–p密码-B数据库1数据库2..>d:\\文件名.sql单库,单库多表，多库安装Ecshop数据库 主讲:韩顺平课堂练习题:这是一个ecshop的数据库，包括ecshop所有的表，请导入到mysql数据库中将ecshop整个数据库备份到你的c:盘ecshop.bak将ecshop的表ecs_goods备份到c:盘ecshop.goods.bak将mysql的ecshop数据库删除,并通过备份的ecshop.bak恢复删除刚刚恢复的ecshop库中的ecs_goods表，然后使用备份的ecshop.goods.bak来恢复.使用php把ecs_goods表的商品名数据取出显示到网页(晚上一个练习题)表的创建(基本语句) 主讲:韩顺平CREATETABLEtable_name( field1datatype, field2datatype, field3datatype)characterset字符集collate校对规则engine存储引擎方式field：指定列名datatype：指定列类型(字段类型)【说明下】注意：创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型。例：user表(快速入门案例) id整形 name字符串 password字符串 birthday日期创建表(基本语句) 主讲:韩顺平细节说明在创建表的时候可以指定engine通常使用InnoDB或者MyISAM给说明一下创建的表对应的文件在什么地方(不同的表存储引擎对应的文件不同InnoDB对应一个文件,MyISAM对应三个文件*.frm表信息*.MYD存储数据*.MYI存储索引信息)Mysql数据类型(重点) 主讲:韩顺平VARCHAR、BLOB和TEXT类是变长类型。每个类型的存储需求取决于列值的实际长度。分类数据类型说明数值类型BIT(M)TINYINT[UNSIGNED]BOOL，BOOLEANSMALLINT[UNSIGNED]INT[UNSIGNED]BIGINT[UNSIGNED]FLOAT[(M,D)][UNSIGNED]DOUBLE[(M,D)][UNSIGNED]DECIMAL(M,D)[UNSIGNED]位类型。M指定位数，默认值1，范围1-64带符号的范围是-128到127。无符号0到255。默认是有符号使用0或1表示真或假带符号是负的2的15次方到2的15次方-1,无符号2的16方-1带符号是负的2的31次方到2的31次方-1,无符号2的32方-1带符号是负的2的63次方到2的63次方-1,无符号2的64方-1M指定显示长度，d指定小数位数,占用空间4个字节表示比float精度更大的小数,占用空间8个字节定点数M指定长度，D表示小数点的位数,文本、二进制类型CHAR(size)char(20)VARCHAR(size)varchar(20)BLOBLONGBLOBTEXTLONGTEXT固定长度字符串最大255可变长度字符串最大65532【在mysql5.0中最大21844】二进制数据大文本,不支持全文索引,不支持默认值,建议使用varchar时间日期DATE/DATETIME/TimeStamp日期类型(YYYY-MM-DD)(YYYY-MM-DDHH:MM:SS)，TimeStamp表示时间戳，它可用于自动记录insert、update操作的时间Mysql常用数据类型 主讲:韩顺平分类数据类型说明String类型ENUM类型（枚举）SET类型（集合）ENUM是一个字符串对象，其值来自表创建时在列规定中显式枚举的一列值。在某些情况下，ENUM值也可以为空字符串('')或NULL：SET是一个字符串对象，可以有零或多个值，其值来自表创建时规定的允许的一列值。指定包括多个SET成员的SET列值时各成员之间用逗号(‘,’)间隔开。这样SET成员值本身不能包含逗号。SET最多可以有64个不同的成员可以使用FIND_IN_SET()函数或LIKE操作符搜索SET值☞在表设计时，为提高效率和安全，最好notnulldefaul值的形式来控制Mysql常用数据类型 主讲:韩顺平数值型(整数)的基本使用Mysql常用数据类型 主讲:韩顺平数值型(整数)的使用细节如何定义一个无符号的整数?createtablet10(idintnotnulldefault0);//默认是有符号的createtablet11(idintunsignednotnulldefault0);有符号的2.解释一下整数范围的原因（深入）Mysql常用数据类型 主讲:韩顺平数值型(整数)的使用细节2.关于zerofill的说明在数据库定义是int(6)这个6到底表示什么意思?对于数值型(也可以是tinyint等)，我们这样定义时int(6)unsignedzerofill/*①zerofill只能和unsinged配合使用*//*②6表示当zerofill时，填充的宽度,就是如果数据不足6位，则这个字段将被0填充，如果不设置zerofill，则这个6没有什么含义*//*③int(6)zerofill/*只要指定zerofill则默认就是unsigned*//*④6并不是表示，存放的数不能超过6位，这点请注意哈*/测试案例:createtabletest(num1int,num2int(4)zerofill,num3int(6)unsignedzerofill);insertintotestvalues(345,345,345);//看结果就清楚insertintotestvalues(34578,34578,34578);//看结果就清楚insertintotestvalues(-345,-345,-345);//num2字段就存放不了，因为是unsigned的.Mysql常用数据类型 主讲:韩顺平数值型(bit)的使用基本使用mysql>createtabletest2(idint,abit(1));mysql>insertintotest2(1,3);mysql>insertintotest2values(2,65);细节说明bit字段在显示时，按ascii码对应的字符显示.查询的时候仍然可以用数值位类型。M指定位数，默认值1，范围1-64Mysql常用数据类型 主讲:韩顺平数值型(小数)的基本使用FLOAT[(M,D)][UNSIGNED]M指定显示长度，d指定小数位数,占用空间4个字节小数:float(4,2)表示的范围是-99.99~99.99float(4,2)unsigned表示的范围是0-99.99DECIMAL(M,D)[UNSIGNED]定点数M指定长度，D表示小数点的位数decimal(5,2)表示的范围是-999.99~999.99decaimal(5,2)unsigned表示的范围是0-999.99思考加入99.999会是什么结果?Mysql常用数据类型 主讲:韩顺平数值型(小数)的细节说明FLOAT[(M,D)][UNSIGNED]单精度浮点数精确到大约7位小数位。DECIMAL(M,D)[UNSIGNED]可以支持更加精确的小数位,压缩的“严格”定点数。M是小数位数(精度)的总数，D是小数点(标度)后面的位数。小数点和(负数)的‘-’符号不包括在M中。如果D是0，则值没有小数点或分数部分。DECIMAL整数最大位数(M)为65。支持的十进制数的最大位数(D)是30。如果D被省略，默认是0。如果M被省略，默认是10。建议：如果希望小数的精度高，推荐使用decimalMysql常用数据类型 主讲:韩顺平字符串的基本使用CHAR(size)固定长度字符串最大255字符VARCHAR(size)可变长度字符串最大65532字节【utf-8编码最大21844字符1-3个字节用于记录大小】Mysql常用数据类型 主讲:韩顺平字符串使用细节1char(4)//这个4表示字符数(绝对最大255)，不是字节数,不管是中文还是字母都是放四个,按字符计算.varchar(4)//这个4表示字符数，不管是字母还是中文都以定义好的表的编码来存放数据.不管是中文还是英文字母，都是最多存放2个，是按照字符来存放的.Mysql常用数据类型 主讲:韩顺平字符串使用细节2char(4)是定长，就是说，即使你插入'aa',也会占用分配的4个字符.varchar(4000)是变长，就是说，如果你插入了'aa',实际占用空间大小是L+1[注：同CHAR对比，VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)。]Mysql常用数据类型 主讲:韩顺平字符串使用细节3什么时候使用char,什么时候使用varchar答：如果数据是定长，推荐使用char，比如md5的密码,邮编，手机号，身份证号码等.如果一个字段的长度是不确定，我们使用varchar，比如留言,文章查询速度，char>varcharMysql常用数据类型 主讲:韩顺平字符串使用细节4char在存放空格时会丢失，一定要小心,varchar不会丢失比如在char中存放了'aaa'取出来就是'aaa’'了而varchar中存放了‘aaa’,取出来还是‘aaa’,后面的那个空格没有丢失为什么，因为char的存放规则是当数据放入不够时，后面全面补空格，这样就导致mysql数据库会误杀你希望的空格,小心通过php获取数据在页面来证明Mysql常用数据类型 主讲:韩顺平字符串使用细节5在存放文本时，也可以使用Text数据类型.可以将TEXT列视为VARCHAR列,注意Text不能有默认值.Mysql常用数据类型 主讲:韩顺平字符串使用细节6表的所有字段加起来不能超过65535个字节下面的能创建成功吗?Mysql常用数据类型 主讲:韩顺平日期类型的基本使用CREATETABLEbirthday(idINT,t1DATE,t2DATETIME,t3TimeStamp);timestamp时间戳mysql>INSERTINTObirthday(t1,t2)VALUES('2016-01-01','2016-01-0112:12:12');日期类型的细节说明TimeStamp在Insert和update时，自动联动Mysql常用数据类型 主讲:韩顺平Enum,SET类型基本使用看一个需求:有一个调查表votes，需求调查人的喜好，比如(苹果,西瓜，菠萝)中去选择（可以多选）。(男，女,保密)【单选】名字[非空]基本语法enum('值1','值2',...)set('值1','值2'，....)Mysql常用数据类型 主讲:韩顺平enum枚举类型基本使用枚举类型，其实就是“单选”类型，对应网页表单中的“单选项”的数据值；形式：enum(‘选项值1’，‘选项值2’，‘选项值3’，......)；该设定只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中的一个值；而且，出于效率的考虑，这些值实际存储的是“数字”，因为这些选项，每个选项值，依次对应如下数字：1，2，3，4，5，.......最多65535个；Mysql常用数据类型 主讲:韩顺平SET类型基本使用就是“多选”类型，对应网页表单中的“多选项”的数据值形式：set(‘选项值1’，‘选项值2’，‘选项值3’，......)；该设定只是提供了若干个选项的值，最终一个单元格中，实际可存储了任何其中的多个值（就是多选）；而且，出于效率的考虑，这些值实际存储的是“数字”，因为这些选项，每个选项值，依次对应如下数字：1，2，4，8，16，32，......最多64个；[偶数]1=Mysql常用数据类型 主讲:韩顺平Enum,SET类型基本使用添加数据INSERTINTOusersvalues('zs','男','足球');INSERTINTOusersvalues('zs',1,'足球');INSERTINTOusersvalues('zs',3,'足球');//失败INSERTINTOusersvalues('zs','中','足球');//失败INSERTINTOusersvalues('zs',2,'足球,篮球');//okINSERTINTOusersvalues('zs',2,'足球,大球');//失败，没有大球INSERTINTOusersvalues('zs',2,2);//okINSERTINTOusersvalues('zs',2,4);//okINSERTINTOusersvalues('zs',2,5);//ok5=1+4Mysql常用数据类型 主讲:韩顺平Enum,SET类型的查询以后只要是对set的查询,使用find_in_set()Mysql常用数据类型 主讲:韩顺平图片，电影，音频数据类型怎么存放?

创建表练习 主讲:韩顺平创建一个员工表(课堂练习/评讲),选用适当的数据类型,添加2条数据，然后通过php来网页中显示。字段属性Id整形name字符型sex枚举类型brithday日期型（date）entry_date日期型(date)job字符型Salary小数型resume文本型创建表练习 主讲:韩顺平显示表的细节问题:如何在显示表有较长中文字符时，可以对其显示的技巧先将控制台的宽度设置大一点比如800宽登录的时候这样登录mysql--default-character-set=latin1-uroot-pxxxx然后进入后再设置成setnamesgbk即可对其显示中文了.修改表(重点)-基本介绍 主讲:韩顺平使用ALTERTABLE语句追加,修改,或删除列的语法.ALTERTABLEtablenameADD (columndatatype[DEFAULTexpr] [,columndatatype]...);ALTERTABLEtablenameMODIFY (columndatatype[DEFAULTexpr] [,columndatatype]...);ALTERTABLEtablenameDROP (column);修改表的名称：Renametable表名to新表名修改表的字符集：altertablestudentcharactersetutf8;修改表-课堂练习 主讲:韩顺平练习在上面员工表的上增加一个image列(要求在resume后面)。修改job列，使其长度为60。删除sex列。表名改为employee。修改表的字符集为utf8列名name修改为user_namealtertableuserchangecolumnnameusernamevarchar(20);☞修改表的注意事项总结给5min学生练习帮助命令的使用 主讲:韩顺平在mysql使用中，我们可能忘记一个mysql命令的使用，我们可以通过帮助命令?来查看相关命令的使用方式1.？create方式2?createtable提示:控制台给出的都是英文说明，如果英文不好，还是用mysql中文手册吧!数据库CRUD语句主讲:韩顺平Insert语句(增加数据)Update语句(更新数据)Delete语句(删除数据)Select语句(查找数据)Insert语句 -基本介绍 主讲:韩顺平INSERTINTO tablename[(column[,column...])]VALUES (value[,value...]);使用INSERT语句向表中插入数据。快速入门案例:创建一张商品表(有idint,goods_namevarchar(50),pricefloat);//饮料添加2条记录Insert-课堂练习 主讲:韩顺平练习：使用insert语句向表中插入三个员工的信息。字段属性Id整形name字符型sex字符型brithday日期型Entry_date日期型job字符型Salary小数型resume文本型Insert语句 -使用细节 主讲:韩顺平☞细节说明(为讲解的比较清楚，创建几个小表来说明问题)插入的数据应与字段的数据类型相同。比如把'abc'添加到int类型会错误数据的大小应在字段的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。在values中列出的数据位置必须与被加入的列的排列位置相对应。字符和日期型数据应包含在单引号中。插入空值[前提是该字段允许为空]，不指定或insertintotablevalue(null)insertinto(),(),()形式添加多条记录如果是给表中的所有字段添加数据，可以不写前面的字段名称默认值的使用，当不给某个字段值时，如果有默认值就会添加，否则报错建议我们以后添加数据，都给我把''引起了.Insert语句课堂练习 主讲:韩顺平练习：写一个PHP,通过PHP来添加/显示/删除/修改学生表要求:加入js前端验证和php后台验证[可选]验证要求编号、语文、数学、英语是数字并且不为空，学生名字不能为空。删除修改删除修改删除修改删除修改删除修改删除修改1.分析程序框架图2.设计好表和数据3.关键技术提示(计算器)update语句 主讲:韩顺平UPDATE tbl_name

SETcol_name1=expr1[,col_name2=expr2...]

[WHEREwhere_definition]

使用update语句修改表中数据☞基本使用练习：在上面创建的employee表中修改表中的纪录。要求将所有员工薪水修改为5000元。将姓名为'小妖怪’的员工薪水修改为3000元。将'老妖怪'的薪水在原有基础上增加1000元。 update语句 主讲:韩顺平☞使用细节:UPDATE语法可以用新值更新原有表行中的各列。SET子句指示要修改哪些列和要给予哪些值。WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。如果需要修改多个字段，可以通过set字段1=值1,字段2=值2....delete语句 -基本介绍 主讲:韩顺平deletefromtbl_name

[WHEREwhere_definition]

使用delete语句删除表中数据。快速入门案例(可以创建一些表来测试/可以复制一张表测试)删除表中名称为’zs’的记录。删除表中所有记录。使用truncate删除表中记录。delete语句 -使用细节 主讲:韩顺平使用delete语句删除表中数据。☞使用细节:如果不使用where子句，将删除表中所有数据。Delete语句不能删除某一列的值（可使用update设为null或者''）使用delete语句仅删除记录，不删除表本身。如要删除表，使用droptable语句。同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题。[外键基本介绍]delete语句 -使用细节 主讲:韩顺平使用delete语句删除表中数据。☞使用细节:删除表中数据也可使用TRUNCATETABLE语句，它和delete有所不同。如果要清空表中的所有记录，可以使用下面的两种方法：DELETEFROMtable1

TRUNCATETABLEtable1效果一样，TRUNCATE速度快DELETE可以带WHERE条件，删除更加灵活，truncate不能带whereDELETE可以返回被删除的记录数，而TRUNCATETABLE返回的是0推荐使用DELETE查询表select语句(1) 主讲:韩顺平SELECT[DISTINCT]*|{column1,column2.column3..} FROM tablename;☞注意事项(需要创建新的学生表)Select指定查询哪些列的数据。column指定列名。*号代表查询所有列。From指定查询哪张表。DISTINCT可选，指显示结果时，是否剔除重复数据基本select[重点，难点]语句select语句(1) 主讲:韩顺平课堂练习:查询表中所有学生的信息。查询表中所有学生的姓名和对应的英语成绩。过滤表中重复数据[distinct]。学员练习,再评讲select语句(2)-对列进行运算和别名操作 主讲:韩顺平SELECT*|{column1｜expression,column2｜expression，..} FROM tablename;在select语句中可使用表达式对查询的列进行运算在select语句中可使用as语句SELECTcolumnnameas别名from表名;三种形式select语句(2) 主讲:韩顺平练习在所有学生分数上加10分特长分(即查询所有学生总分再加10分)。统计每个学生的总分。使用别名表示学生分数。课堂练习在姓赵的学生分数上增加60%(即查询总分再加60%)。统计姓李的学生的总分。使用别名表示学生的数学分数。select语句(3)-使用where子句,进行过滤查询主讲:韩顺平使用where子句，进行过滤查询。练习：查询姓名为wu的学生成绩查询英语成绩大于90分的同学查询总分大于200分的所有同学使用where子句，课堂练习：查询姓名为赵并且(and)id大于90的学生成绩查询英语成绩大于语文成绩的同学查询总分大于200分并且数学成绩小于语文成绩,的姓宋的学生.select语句(4) 主讲:韩顺平在where子句中经常使用的运算符比较运算符><<=>==<>!=大于、小于、大于(小于)等于、不等于BETWEEN...AND...显示在某一区间的值IN(set)显示在in列表中的值，例：in(100,200)LIKE‘张pattern’NOTLIKE‘’模糊查询模糊查询ISNULL判断是否为空逻辑运算符and多个条件同时成立or多个条件任一成立not不成立，例：wherenot(salary>100);select语句(4) 主讲:韩顺平查询英语分数在80－90之间的同学。查询数学分数为89,90,91的同学。查询所有姓李的学生成绩。查询数学分>80，语文分>80的同学。课堂练习查询语文分数在70－80之间的同学。查询总分为189,190,191的同学。查询所有姓李或者姓宋的学生成绩。查询数学比语文多30分的同学。select语句(5) 主讲:韩顺平SELECTcolumn1,column2,column3.. FROM table orderbycolumnasc|desc，...使用orderby子句排序查询结果。Orderby指定排序的列，排序的列即可是表中的列名，也可以是select语句后指定的列名。Asc升序[默认]、Desc降序ORDERBY子句应位于SELECT语句的结尾。课堂练习：对数学成绩排序后输出【升序】。对总分排序后输出，然后再按从高到低的顺序输出对不姓李的学生成绩排序输出合计函数－count 主讲:韩顺平Selectcount(*)|count(列名)fromtablename [WHEREwhere_definition]

课堂练习：统计一个班级共有多少学生？统计数学成绩大于90的学生有多少个？统计总分大于250的人数有多少？count(*)和count(列名)的区别[单独建表]Count(列名)返回某一列，行的总数合计函数－sum 主讲:韩顺平Selectsum(列名)｛,sum(列名)…｝fromtablename [WHEREwhere_definition]

课堂练习：统计一个班级数学总成绩？统计一个班级语文、英语、数学各科的总成绩统计一个班级语文、英语、数学的成绩总和统计一个班级语文成绩平均分注意1.：sum仅对数值起作用，否则会报错。注意2.：sum函数中，如果有null值，对结果有影响，举例说明(创建测试表)注意：对多列求和，“，”号不能少。Sum函数返回满足where条件的行的和合计函数－avg 主讲:韩顺平Selectavg(列名)｛,avg(列名)…｝fromtablename [WHEREwhere_definition]

练习：求一个班级数学平均分？求一个班级总分平均分AVG函数返回满足where条件的一列的平均值合计函数－Max/min 主讲:韩顺平Selectmax(列名)fromtablename [WHEREwhere_definition]

练习：求班级最高分和最低分（数值范围在统计中特别有用）Max/min函数返回满足where条件的一列的最大/最小值select语句(6) 主讲:韩顺平SELECTcolumn1,column2.column3..FROM table groupbycolumn使用groupby子句对列进行分组SELECTcolumn1,column2.column3.. FROM table groupbycolumnhaving...使用having子句过滤groupby用于对查询的结果分组统计,(画出分组示意图)having子句用于限制分组显示结果.?如何显示每个部门的平均工资和最高工资?显示每个部门的每种岗位的平均工资和最低工资?显示平均工资低于2000的部门号和它的平均工资[别名]Mysql函数-一览 主讲:韩顺平CURRENT_DATE(

)当前日期CURRENT_TIME(

)当前时间CURRENT_TIMESTAMP()当前时间戳DATE(datetime)返回datetime的日期部分DATE_ADD(date2,INTERVALd_valued_type)在date2中加上日期或时间DATE_SUB(date2,INTERVALd_valued_type)在date2上减去一个时间DATEDIFF(date1,date2)两个日期差(结果是天)TIMEDIFF(date1,date2)两个时间差(多少小时多少分钟多少秒)NOW(

)当前时间YEAR|Month|DATE(datetime)FROM_UNIXTIME()年月日时间日期相关函数 主讲:韩顺平CURRENT_DATE(

)当前日期CURRENT_TIME(

)当前时间CURRENT_TIMESTAMP()当前时间戳selectcurrent_date()fromdual;selectcurrent_time()fromdual;时间日期相关函数 主讲:韩顺平DATE(datetime)返回datetime的日期部分DATE_ADD(date2,INTERVALd_valued_type)在date2中加上日期或时间DATE_SUB(date2,INTERVALd_valued_type)在date2上减去一个时间DATEDIFF(date1,date2)两个日期差(结果是天)四个函数的基本使用，创建一张留言表：mysql>createtablemes(idint,contentvarchar(256),sendtimedatetime);添加一些数据mysql>insertintomessagevalues(3,'hello1',now());mysql>insertintomessagevalues(4,'hello1',now());查询显示所有留言信息，发布日期只显示日期，不用显示时间.请查询在10分钟内发布的帖子请在mysql的sql语句中求出2011-11-11和1990-1-1相差多少天请用mysql的sql语句求出你活了多少天?时间日期相关函数 主讲:韩顺平DATE(datetime)返回datetime的日期部分DATE_ADD(date2,INTERVALd_valued_type)在date2中加上日期或时间DATE_SUB(date2,INTERVALd_valued_type)在date2上减去一个时间DATEDIFF(date1,date2)两个日期差(结果是天)上面函数的细节说明：DATE_ADD()中的interval后面可以是yearminuteseconddayDATE_SUB()中的interval后面可以是yearminuteseconddayDATEDIFF(date1,date2)得到的是天数，而且是date1-date2的天数，因此可以取负数这四个函数的日期类型可以是date,datetime或者timestamp这几个函数经常在论坛留言表中使用.时间日期相关函数 主讲:韩顺平四个函数的基本使用，创建一张留言表：mysql>createtablemes(idint,contentvarchar(30),sendtimedatetime);添加一些数据mysql>insertintomessagevalues(10'第一条留言',now());mysql>insertintomessagevalues(20,'第二条留言',now());课堂练习查询：显示所有留言信息，发布日期只显示日期，不用显示时间.请查询在30秒以前发布的帖子select*frommeswheredate_add(sendtime,interval30second)<=now();请在mysql的sql语句中求出2009-11-11和1997-1-1相差多少天你活了多少年，取整数时间日期相关函数 主讲:韩顺平TIMEDIFF(date1,date2)两个时间差(多少小时多少分钟多少秒)NOW(

)当前时间YEAR|Month|DATE(datetime)FROM_UNIXTIME()unix_timestamp();年月日这里用的比较多的是now()另外函数演示一下即可mysql>selecttimediff(now()+10,now());mysql>selectyear(now());mysql>selectunix_timestamp();from_unixtime默认显示时间日期相关函数 主讲:韩顺平TIMEDIFF(date1,date2)两个时间差(多少小时多少分钟多少秒)NOW(

)当前时间YEAR|Month|DATE(datetime)FROM_UNIXTIME()unix_timestamp();年月日在实际开发中，我们也经常使用int来保存一个unix时间戳.请设计一张留言表，使用int保存时间,在显示时，可以按年-月-日时:分:秒的形式显示在控制台(控制台添加和显示)能够通过php程序添加时间，并且可以在php页面也安年-月-日时分秒形式显示在页面date_default_timezone_set(PHP添加显示)时间日期相关函数 主讲:韩顺平TIMEDIFF(date1,date2)两个时间差(多少小时多少分钟多少秒)NOW(

)当前时间YEAR|Month|DATE(datetime)FROM_UNIXTIME()unix_timestamp();年月日上机练习题，先练后讲.思考,如何查询在10秒/1天/2小时内发布的消息如何查询在10秒/1天/2小时前发布的消息字符串相关函数 主讲:韩顺平CHARSET(str)返回字串字符集CONCAT(string2

[,...])连接字串INSTR(string,substring)返回substring在string中出现的位置,没有返回0UCASE(string2)转换成大写LCASE(string2)转换成小写LEFT(string2,length)从string2中的左边起取length个字符LENGTH(string)string长度[按照字节]REPLACE(str,search_str,replace_str)在str中用replace_str替换search_strSTRCMP(string1,string2)逐字符比较两字串大小,SUBSTRING(str,position

[,length])从str的position开始,取length个字符LTRIM(string2)RTRIM(string2)trim去除前端空格或后端空格综合练习：以首字母小写的方式显示所有员工的姓名数学相关函数 主讲:韩顺平ABS(number2)绝对值BIN(decimal_number)十进制转二进制CEILING(number2)向上取整CONV(number2,from_base,to_base)进制转换FLOOR(number2)向下取整FORMAT(number,decimal_places)保留小数位数,格式化一把xx,xxx形式显示HEX(DecimalNumber)转十六进制LEAST(number,number2

[,..])求最小值MOD(numerator,denominator)求余RAND([seed])RAND([seed])其范围为0≤v≤1.0rand()返回一个随机浮点值v，范围在0到1之间(即,其范围为0≤v≤1.0)。若已指定一个整数参数N，则它被用作种子值，用来产生重复序列。

流程控制函数[了解] 主讲:韩顺平IF(expr1,expr2,expr3)如果expr1为True,则返回expr2否则返回expr3IFNULL(expr1,expr2)如果expr1不为空NULL,则返回expr1,否则返回expr2SELECTCASEWHENexpr1THENexpr2WHENexpr1THENexpr3ELSEexpr4END;[也可以多重分支.]其它函数 主讲:韩顺平USER()查询用户DATABASE()数据库名称MD5(str)为字符串算出一个MD5128比特检查和,通常用于对应用程序使用到的表的某个字段(比如用户密码)加密PASSWORD(str)select*frommysql.user\G从原文密码str计算并返回密码字符串,通常用于对mysql数据库的用户密码加密基本使用mysql>createtableusers(idint,namevarchar(64)notnulldefault'',pwdchar(32)notnulldefault'');添加一个用户名.PHP对Mysql进行操作实例 主讲:韩顺平//1.对mysql完成查询,得到连接$conn=mysql_connect("localhost","root","root");//选择数据mysql_select_db("hspdb1",$conn); //查询数据,返回结果$res=mysql_query("select*fromusers",$conn);//得到查询到多少记录$recs=mysql_num_rows($res);echo"共".$recs."条记录<br/>";//取出记录.while($row=mysql_fetch_array($res)){ //打印 echo"编号:".$row[0]."名称".$row[1]."邮件".$row[2]."<br/>";}//添加记录//mysql_query("insertintousers(name,email)values('abc1','beijing')",$conn) //修改 //mysql_query("updateuserssetemail=''whereid=4",$conn);//删除mysql_query("deletefromuserswhereid=4",$conn);echo"<br/>执行ok!";课后练习题 主讲:韩顺平练习：写一个PHP,可以显示任意一张表的信息要求:编写一个php程序，显示一张表的全部信息要求，只需要给$sql="select*from表名"即可显示表的信息，表头是动态显示的。不需要指定.提示：使用mysql_num_fields()函数和mysql_field_name()就可以搞定php数据库编程(2)-mysql 主讲:韩顺平1.mysql表的基本查询加强2.mysql表的复杂查询3.维护数据的完整性—约束mysql表的基本查询--加强 主讲:韩顺平■介绍-公欲善其事，必先利其器在前面我们讲过mysql表的基本查询，但是都是对一张表进行的查询，这在实际的软件开发中，还远远的不够。下面我们讲解的过程中，将模拟一个简单的公司管理系统,创建三张表(emp,dept,salgrade)为大家演示如何进行多表查询，select语句在软件编程中非常的有用，希望大家好好的掌握。mysql表的基本查询--加强 主讲:韩顺平■查看表结构■查询所有列■查询指定列■如何取消重复行distinct?查询SMITH的薪水,工作,所在部门mysql表的基本查询--加强 主讲:韩顺平■使用算数表达式?显示每个雇员的年工资■使用列的别名selectename"姓名",sal*12as"年收入"fromemp;

mysql表的基本查询--加强 主讲:韩顺平■使用where子句 ?如何显示工资高于3000的员工 ?如何查找1982.1.1后入职的员工//日期类型是可以比较 ?如何显示工资在2000到2500的员工情况//比较运算符between■如何使用like操作符 %:表示任意0到多个字符_:表示任意单个字符 ?如何显示首字符为S的员工姓名和工资 ?如何显示第三个字符为大写O的所有员工的姓名和工资■在where条件中使用in?如何显示empno为123,345,800...的雇员情况■?如何显示没有上级的雇员的情况mysql表的基本查询--加强 主讲:韩顺平■使用逻辑操作符号?查询工资高于500或是岗位为MANAGER的雇员，同时还要满足他们的姓名首写字母为大写的J■使用orderby字句（示意图）?如何按照工资的从低到高的顺序显示雇员的信息?按照部门号升序而雇员的工资降序排列■使用列的别名排序[津贴]对所有员工的年工资进行排序.

mysql表的基本查询--加强 主讲:韩顺平■分页查询按雇员的id号升序取出，每页显示3条记录。请分别显示第一页，第二页，第三页

课堂练习题:按雇员的id号降序取出，每页显示4条记录。请分别显示第3页，第5页对应的sql语句mysql表的基本查询--加强 主讲:韩顺平■说明在实际应用中经常需要执行复杂的数据统计，经常需要显示多张表的数据，现在我们给大家介绍较为复杂的select语句■数据分组-max,min,avg,sum,count?如何显示所有员工中最高工资和最低工资?显示所有员工的平均工资和工资总和?计算共有多少员工?请统计部门编号为10的平均工资.扩展要求：[上机练习,同学们先做]?请显示工资最高的员工的名字，工作岗位?请显示工资高于平均工资的员工信息mysql表的基本查询--加强 主讲:韩顺平■groupby和having子句groupby用于对查询的结果分组统计,having子句用于限制分组显示结果.同学们先思考?如何显示每个部门的平均工资和最高工资?显示每个部门的每种岗位的平均工资和最低工资?显示平均工资低于2000的部门号和它的平均工资[别名可用]扩展要求：mysql表的基本查询--加强主讲:韩顺平课堂练习(后面评讲):使用分组函数和数据分组子句(1)显示所有雇员的平均工资、总计工资、最高工资、最低工资。(2)显示每种岗位的雇员总数、平均工资。(3)显示雇员总数，以及获得补助的雇员数。(4)显示管理者的总人数。(5)显示雇员工资的最大差额。(6)显示每个部门每个岗位的平均工资[1]、每个部门的平均工资[2]、每个岗位的平均工资[3]。mysql表的基本查询--加强主讲:韩顺平■对数据分组的总结1.如果在select语句种同时包含有groupby,having,orderby那么他们的顺序是groupby,having,orderby请统计各个部门的平均工资，并且是大于1000的，并且按照平均工资从高到低排序wheresalin('xxx');mysql表的复杂查询 主讲:韩顺平■多表查询问题的引出(重点，难点)后面，要求大家能够完成!mysql表的复杂查询-多表查询主讲:韩顺平■说明课堂练习:显示雇员名,雇员工资及所在部门的名字,并按部门排序[降序排].思考题:请查询出雇员的名字，薪水，工作,部门的名称，工资的级别多表查询是指基于两个和两个以上的表或是视图的查询.在实际应用中,查询单个表可能不能满足你的需求,（如显示sales部门位置和其员工的姓名),这种情况下需要使用到(dept表和emp表)?显示雇员名,雇员工资及所在部门的名字【笛卡尔集】规定:多表查询的条件是至少不能少于表的个数-1?如何显示部门号为10的部门名、员工名和工资?显示各个员工的姓名，工资，及其工资的级别mysql表的复杂查询-自连接主讲:韩顺平■自连接自连接是指在同一张表的连接查询。?显示某个员工的上级领导的姓名比如显示’FORD’的上级. 课堂练习题[同学们先做!]思考题:显示公司每个员工名字和他的上级的名字mysql表的复杂查询-子查询主讲:韩顺平■什么是子查询子查询是指嵌入在其它sql查询语句中的select语句,也叫嵌套查询■单行子查询单行子查询是指只返回一行数据的子查询语句请思考：如何显示与SMITH同一部门的所有员工?■多行子查询多行子查询指返回多行数据的子查询使用关键字in课堂练习:如何查询和10号部门的工作相同的雇员的名字、岗位、工资、部门号,但是不含10号部门的雇员.mysql表的复杂查询- 主讲:韩顺平☞上机练习题查询ecshop中各个类别中，价格最高的商品.提示，会使用到:ecs_goods的字段goods_idcat_idgoods_nameshop_pricegroupby会默认返回第一行.mysql表的复杂查询- 主讲:韩顺平■在多行子查询中使用all操作符请思考:如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号selectename,sal,deptnofromempwheresal>all(selectsalfromempwheredeptno=30);

扩展要求：大家想想还有没有别的查询方法.Selectename,sal,deptnofromempwheresal>(selectmax(sal)fromempwheredeptno=30);mysql表的复杂查询- 主讲:韩顺平■在多行子查询中使用any操作符请思考:如何显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号selectename,sal,deptnofromempwheresal>any(selectsalfromempwheredeptno=30)扩展要求：大家想想还有没有别的查询方法.selectename,sal,deptnofromempwheresal>(selectmin(sal)fromempwheredeptno=30)mysql表的复杂查询 主讲:韩顺平■多列子查询单行子查询是指子查询只返回单列、单行数据,多行子查询是指返回单列多行数据,都是针对单列而言的，而多列子查序则是指查询返回多个列数据的子查询语句请思考如何查询与smith的部门和岗位完全相同的所有雇员(并且不含smith本人)(字段1，字段2...)=(select字段1，字段2from。。。。)请查询和宋江数学，英语，语文完全相同的学生[查作弊学生]mysql表的复杂查询 主讲:韩顺平好好琢磨琢磨吧■在from子句中使用子查询请思考：如何显示高于自己部门平均工资的员工的信息（名字，薪水，和该部门的平均薪水）这里要用到数据查询的小技巧，把一个子查询当作一个临时表使用解法①【课堂练习】selecte1.*,e2.myavgfromempe1,(selectavg(sal)myavg,deptnofromempgroupbydeptno)e2wheree1.deptno=e2.deptnoande1.sal>e2.myavg解法②selecte1.*fromempe1wheree1.sal>(selectavg(sal)fromempe2wheree2.deptno=e1.deptno)mysql表的复杂查询 主讲:韩顺平好好琢磨琢磨吧■在from子句中使用子查询请思考：查找每个部门工资最高的人的详细资料(雇员们，薪水，部门编号，最高工资)解法①解法②思路:得到所有的员工，进行筛选，每拿到一个员工，判断该员工的工资是否是他们部门的最高工资。select*fromempase1wheree1.sal=(selectmax(sal)fromempase2wheree2.deptno=e1.deptno);mysql表的复杂查询 主讲:韩顺平好好琢磨琢磨吧■在from子句中使用子查询—课堂小练习先练，再讲：显示每个部门的信息(部门名,编号,地址)和人员数量,我们一起完成。思路:1.先将人员信息和部门信息关联显示2.然后统计mysql表的复杂查询 主讲:韩顺平■在from子句中使用子查询这里需要说明的当在from子句中使用子查询时,为了查询方便，需要给子查询指定别名.总结一下mysql表的复杂查询 主讲:韩顺平■自我复制数据(蠕虫复制)有时，为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据。insertintomytable(id,name,sal,job,deptno)selectempno,ename,sal,job,deptnofromemp;

思考题：如何删除掉一张表重复记录temp_table是一张临时表[使用createtable表名likexxx]aa:是你要处理表（有重复行的.）insertintotemp_tableselectdistinct*fromaadeletefromaainsertintoaaselect*fromtemp_tabledroptabletemp_tablemysql表的复杂查询-合并查询主讲:韩顺平■合并查询【了解】有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号union,unionall1)union该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。selectename,sal,jobfromempwheresal>2000unionselectename,sal,jobfromempwherejob='MANAGER'; mysql表的复杂查询-合并查询主讲:韩顺平2)unionall该操作赋与union相似，但是它不会取消重复行,而且不会排序。selectename,sal,jobfromempwheresal>2000unionallselectename,sal,jobfromempwherejob='manager';mysql表的内连接和外连接主讲:韩顺平■概述表连接分为内连接和外连接。■内连接内连接实际上就是利用where子句对两张(多表)表形成的笛卡尔积进行筛选,我们前面学习的查询都是内连接,也是在开发过程中用的最多的连接查询。基本语法:select字段from表1innerjoin表2on连接条件AND其它条件;请显示雇员的名字，工作，和部门的名称??mysql表的内连接和外连接主讲:韩顺平■外连接①左外连接 (如果左侧的表完全显示我们就说是左外连接）②右外连接 (如果右侧的表完全显示我们就说是右外连接)为了讲清楚，我们举例说明。--表stuidname1,Jack2,Tom3,Kity4,nono--表examidgrade1,562,7611,8mysql表的内连接和外连接主讲:韩顺平■外连接1)左连接（显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id号,成绩显示为空）selectstu.id,,exam.gradefromstuleftjoinexamonstu.id=exam.idmysql表的内连接和外连接主讲:韩顺平■外连接2)右连接（显示所有成绩，如果没有名字匹配，显示空）思考题，实现学生表(stu)和考试成绩(exam)的完全显示效果?mysql表的内连接和外连接主讲:韩顺平■一个小练习(课堂练习)为加深大家对外连接的理解，我们做一个小练习列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。要求两种方式使用左外连接实现使用右外连接实现php数据库编程(3)-mysql主讲:韩顺平1.维护数据的完整性—约束2.mysql表自增3.mysql索引mysql维护数据的完整性-约束主讲:韩顺平■约束约束用于确保数据库数据满足特定的商业规则。在mysql中，约束包括:notnull、unique,primarykey,foreignkey,和check五种.mysql维护数据的完整性-约束主讲:韩顺平■primarykey(主键)-基本使用用于唯一标示表行的数据,当定义主键约束后，该列不能重复,不能null字段名字段类型primarykeyuser表字段:id name emailmysql维护数据的完整性-约束主讲:韩顺平■primarykey(主键)-细节说明primarykey不但不能重复而且不能为null。一张表最多只能有一个主键,但可以是复合主键一般来说一张表总有primarykey,而且是整数类型的(ecs_vote、ecs_users、ecs_goods等等)主键的指定方式有两种直接在字段名后指定：字段名primakrykey在表定义最后写primarykey(列名);如果一个字段设置成notnull并unique，从使用效果上非常像primarykey使用desc表名，可以看到primarykey的情况.mysql维护数据的完整性-约束主讲:韩顺平■notnull(非空)如果在列上定义了notnull,那么当插入数据时，必须为列提供数据。■unique(唯一)当定义了唯一约束后，该列值是不能重复的.。字段名字段类型notnull字段名字段类型uniqueunique细节(注意):指定notnull和不指定notnull,会怎样?一张表可以有几个unique?mysql维护数据的完整性-约束主讲:韩顺平■foreignkey(外键)-基本使用用于定义主表和从表之间的关系:外键约束要定义在从表上，主表则必须具有主键约束或是unique约束.,当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null(学生/班级图示)InnoDBFOREIGNKEY(本表字段名)REFERENCES主键表名(主键名或unique字段名)学生表(从表) 班级表(主表)idnameclass_id id class_nameintroducestu_nummysql维护数据的完整性-约束主讲