• 1. MySQL数据库姓 名:崔文华 电 话:58731616 Q Q:不告诉你 MAIL : cuiwenhua@gentek.com.cn
  • 2. MySQL数据库数据库概述 数据库基础知识 SQL语言 MySQL数据库基础操作
  • 3. 第一部分:数据库概述 数据库基本概念 什么是MySQL数据库 MySQL基本操作
  • 4. 数据库基本概念数据(Data) 数据库(Database) 数据库管理系统(DBMS) 数据库系统(DBS)
  • 5. 数据数据(Data)的定义: 对客观事物的符号表示,如图形符号、数字、字母等,数据是数据库中存储的基本对象。 在日常生活中,人们直接用语言来描述事物;在计算机中,为了存储和处理这些事物,就要将事物的特征抽象出来组成一个记录来描述。。 数据的种类 文字、图形、图象、声音 数据的特点 数据与其语义是不可分的
  • 6. 数据举例学生档案中的学生记录 (崔文华,男,1984,上海,计算机系,1990) 数据的形式不能完全表达其内容 数据的解释 语义:学生姓名、性别、出生年月、籍贯、所在系别、入学时间 解释:崔文华是个大学生,1984年出生,上海人,2003年考入计算机系
  • 7. 数据库数据库(Database,简称DB)的定义: “按照数据结构来组织、存储和管理数据的仓库” J.Martin给数据库下了一个比较完整的定义:数据库是存储在一起的相关数据的集合,这些数据是结构化的,无有害的或不必要的冗余,并为多种应用服务
  • 8. 数据库的发展史人工管理阶段 人力手工整理存储数据 文件系统阶段 使用磁盘文件来存储数据 数据库系统阶段 关系型数据库 高级数据库阶段 “关系-对象”型数据库
  • 9. 数据库的类型纯文本数据库: 纯文本数据库是只用空格符、制表符和换行符来分割信息的文本文件。适用于小型应用,对于大中型应用来说它存在诸多限制: 只能顺序访问,不能进行随机访问。 查找数据和数据关系或多用户同时访问进行写操作时非常困难。 关系数据库: 由于纯文本数据库存在诸多局限,所以人们开始研究数据模型,设计各种类型的使用方便的数据库。在数据库的发展史上,最具影响的数据库模型有:层次模型、网状模型和关系模型。其中,关系模型是目前应用最广泛和最有发展前途的一种数据模型,其数据结构简单,当前主流的数据库系统几乎都采用关系模型。
  • 10. 数据库的优点数据按一定的数据模型组织、描述和储存 可为各种用户共享 冗余度较小,节省存储空间 易扩展,编写有关数据库应用程序
  • 11. 数据库管理系统数据库管理系统(Database Management System)是一种操纵和管理数据库的大型软件,是用于建立、使用和维护数据库,简称DBMS。 关系型数据库管理系统称为RDBMS,R指Relation DBMS的作用 它对数据库进行统一的管理和控制,以保证数据库的安全性和完整性。
  • 12. DBMS的主要功能-For程序员数据定义功能: 提供数据定义语言(DDL) 定义数据库中的数据对象 数据操纵功能: 提供数据操纵语言(DML) 操纵数据实现对数据库的基本操作 (查询、插入、删除和修改)
  • 13. DBMS的主要功能-For数据库管理员数据库的运行管理 保证数据的安全性、完整性、 多用户对数据的并发使用 发生故障后的系统恢复 数据库的建立和维护功能(实用程序) 数据库数据批量装载 数据库转储 介质故障恢复 数据库的重组织 性能监视等
  • 14. 时下流行的DBMSOracle 应用广泛、功能强大,分布式数据库系统 “关系-对象”型数据库 MySQL 快捷、可靠 开源、免费、与PHP组成经典的LAMP组合 SQL Server 针对不同用户群体的五个特殊的版本 易用性好 DB2 应用于大型应用系统,具有较好的可伸缩性
  • 15. 数据库系统数据库系统(Database System,简称DBS)是一个实际可运行的存储、维护和应用系统提供数据的软件系统 数据库系统构成 DBMS DB 应用软件 数据库管理员 用户
  • 16. 数据库系统的使用数据库服务器响应和提供数据应用程序数据库操作和查询
  • 17. MySQL数据库什么是MySQL MySQL是一个小型关系型数据库管理系统,开发者为瑞典MySQL AB公司。目前MySQL被广泛地应用在Internet上的中小型网站中。由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型 网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。
  • 18. MySQl特征性能快捷、优化SQL语言 容易使用 多线程和可靠性 多用户支持 可移植性和开放源代码 遵循国际标准和国际化支持 为多种编程语言提供API
  • 19. MySQL5特性子查询 视图 存储过程 触发器 事务处理 热备份 二进制Bit类型 等等……
  • 20. MySQL不足不能直接处理XML数据 一些功能上支持的不够完善和成熟 不能提供任何OLAP(实时分析系统)功能 等等….
  • 21. MySQL应用MySQL的官方网站引述MySQL是“世界上最受欢迎的开放源代码数据库”。这不是狂妄之语,数字可以证明它:目前,有超过1000万份的MySQL被安装用于支付高负荷的网站和其他关键商业应用,包括像阿尔卡特、爱立信、朗讯、亚马逊、Google、纽约证券交易所、迪斯尼、Yahoo、美国宇航局等这样的产业领袖。在下述网页你还能查看到MySQL和它竞争对手进行了短兵相接的比较。 http://www.mysql.com/information/crash-me.php http://www.mysql.com/information/benchmarks.html
  • 22. MySQL基本操作mysql、mysqladmin和mysqldump MySQL管理工具和用户操作界面
  • 23. mysql命令mysql命令 mysql -h host_name -u user_name –ppassword h:当连接MySQL服务器不在同台主机时,填写主机名或IP地址 u:登录MySQL的用户名 p:登录MySQL的密码 注意:密码如果写在命令行的时候一定不能有空格。如果使用的系统为linux并且登陆用户名字与MySQL的用户名相同即可不用输入用户名密码,linux默认是以root登陆,windows默认用户是ODBC
  • 24. mysql程序常用命令选定默认数据库:use dbname; 显示所有数据库:show databases; 显示默认数据库中所有表:show tables; 放弃正在输入的命令:\c 显示命令清单:\h 退出mysql程序:\q 查看MySQL服务器状态信息:\s
  • 25. 处理SQL文件mysql命令 处理存放在文件里的SQL命令 格式:mysql [option] dbname < file.sql mysql –u root –p --default-character-set=latin1 dbname < backup.sql 注:latin1是MySQL4.0的默认字符集或者utf-8是MySQL5.0的默认字符集,如果不知道使用什么字符集的时候,可以选用这两个其中一个
  • 26. 使用的几点提示字符集 Windows命令窗口默认字符集为cp850或者gbk,因此存在许多字符显示不出来的问题。 可以在mysql命令加入--default-character-set=cp850或gbk选项,cp850字符集需要再追加一个 --character-sets-dir= C:/MySQL/share/characters选项
  • 27. mysqladmin命令mysqladmin命令 完成许多系统管理任务,如创建或删除一个数据库、修改密码等 格式:mysqladmin [option] admincommand 创建数据库 mysqladmin –u root –p create newdb 删除数据库 mysqladmin –u root –p drop newdb 更改密码 mysqladmin –u root –p password “newpasswd”
  • 28. mysqldump命令mysqldump命令 用于为数据库创建备份 格式:mysqldump [option] dbname > bak.sql mysqldump –u root –p tmpdb > backuptmp.sql 备份出来的是一个文本文件,默认为utf-8字符集,如果想使用另外一种字符集,必须使用 --default-character-set=gbk选项来设置
  • 29. 附加工具MySQL CC http://www.mysql.com/products/mysqlcc/index.html对于MySQL,它是一个极好的前端查询和数据库管理工具。现有Windows,UNIX和Linux版本可用,并且Mac OS X 的版本现在也有 DBTOOL专业管理器,来自http://www.dbtools.com.br,是一个用于管理MySQL数据库、表和索引的图形客户,其输入来自其他RDBMS的数据,该管理器提供一个用于查询和报告设计的接口。
  • 30. 总结MySQL是一种功能非常强大的关系型客户服务器数据库系统,它的安全性和稳定性足以满足许多应用程序的要求,而且有着非常高的性价比。
  • 31. 第二部分:数据库基础知识 数据库基础知识概述 SQL概述 SQL语言
  • 32. 数据库基础知识 数据库由一批数据构成的有序集合,这些数据被分门别类地存放在一些结构化的数据表(table)里,而数据表之间又往往存在交叉引用的关系,这种关系使数据库又被称为关系型数据库 档案柜=数据库 抽屉=表 文件=记录
  • 33. 数据库数 据 表 存储过程 视 图 ….…订单客户产品产品数据库表
  • 34. 数据表行(记录)列(字段)
  • 35. 数据在表中的存放编号姓名年龄民族部门1王涛33汉族人事管理部2李梅27汉族人事管理部1127李梅21133王涛1部门编码民族编码年龄姓名编号回族2汉族1民族民族编码市场营销部2人事管理部1部门部门编码 存在冗余为减少数据查找的麻烦,允许数据有一定的冗余
  • 36. 数据存储的完整性Roll NumberNameAddressBookTaken12 Alicia Ruth 12, Temple Street AC091 14 Jason Darren 123, Sunset Blvd. AC043 15 Mary Beth 32, Golden Avenue AC021 12 Alicia Ruth 12, Temple Street AC043 12 Alicia Ruth 12, Tmple Street AC011 15 Mary Beth 33, Golden Avenue AC011 不同的地址!存在不正确、不准确的数据,数据库“失去了完整性”姓名
  • 37. 数据的完整性数据完整性+=可靠性准确性
  • 38. 完整性分类数据实体完整性 字段完整性 引用完整性 自定义完整性
  • 39. 数据实体完整性河南新乡赵可以0010016河南新乡张丽鹃0010015江西南昌雷铜0010014湖南新田吴兰0010013山东定陶李山0010012….地址姓名学号江西南昌雷铜0010014×约束方法:唯一约束、主键约束、标识列
  • 40. 字段完整性河南新乡赵可以0010016河南新乡张丽鹃0010015江西南昌雷铜0010014湖南新田吴兰0010013山东定陶李山0010012….地址姓名学号湖北江门李亮8700000000×约束方法:限制数据类型、检查约束、外键约束、默认值、非空约束
  • 41. 引用完整性河南新乡赵可以0010016河南新乡张丽鹃0010015江西南昌雷铜0010014湖南新田吴兰0010013山东定陶李山0010012…地址姓名学号980010021数学×约束方法:外键约束科目学号分数…数学001001288数学001001374语文001001267语文001001381数学001001698
  • 42. 自定义完整性AV121322乔峰CV0016AV372133玄痛CV0015AV378291沙悟净AV0014AV378290猪悟能AV0013AV378289孙悟空AV0012….会员证用户姓名用户编号约束方法:规则、存储过程、触发器帐号姓名信用….00192孙悟空700288猪悟能612333段誉890111虚竹4093000岳不群-10×触发器:检查信用值
  • 43. 完整性包括…输入的类型是否正确? ——年龄必须是数字 输入的格式是否正确? ——身份证号码必须是18位 是否在允许的范围内? ——性别只能是”男”或者”女” 是否存在重复输入? ——学员信息输入了两次 是否符合其他特定要求? ——信誉值大于5的用户才能够加入会员列表 …… 列值要求(约束)整行要求(约束)
  • 44. 创建数据库表完毕!输入数据项,验证主键、主外键关系、检查约束……
  • 45. SQL概述什么是SQL? SQL是Structured Query Language(结构化查询语言)的缩写。SQL是专为数据库而建立的操作命令集,是一种功能齐全的数据库语言。在使用它时,只需要发出“做什么”的命令,“怎么做”是不用使用者考虑的。
  • 46. SQL语法组成DML( Data Manipulation Language数据操作语言) ——查询、插入、删除和修改数据库中的数据; ——SELECT、INSERT、 UPDATE 、DELETE等; DCL( Data Control Language数据控制语言) ——用来控制存取许可、存取权限等; ——GRANT、REVOKE 等; DDL( Data Definition Language数据定义语言) ——用来建立数据库、数据库对象和定义其列 ——CREATE TABLE 、DROP TABLE、ALTER TABLE 等 功能函数 ——日期函数、数学函数、字符函数、系统函数等
  • 47. MySQL中使用SQL语言几点说明属于一个SQL语句,使用分号(;)结尾,否则mysql认为语句没有输入完。 箭头(->)代表SQL语句没有输入完 取消SQL语句使用(\c) SQL语句关键字和函数名不区分大小写(Linux区分,Windows不区分) 使用函数时,函数名和后面的括号之间不能有空格
  • 48. MySQL数据类型数据类型种类 数值列类型 字符串列类型 日期和时间列类型
  • 49. 数据类型种类数值型 分为整型和浮点型 整型: 可以由十进制和十六进制表示 整数由数字序列组成,如:1,100。 由十六进制表示方法:0x且后面加1—9和 A—F 的任意数字或字母,并且0x中的X不 能大写。 浮点型: 浮点数由一个数字加一个小数点再加上一 个数字组成。两个数字序列不能同时为空。
  • 50. 字符串: 由单引号或者双引号括起来的字符或者数字。 如:”abc”,‘abc10’ 字符串中要用转义字符才能表示的特殊符号
  • 51. 日期和时间值 是存储如“2005 -1-1” 或者“12:00:00”这样的数值的值。在MySQL中日期是按”年-月-日”的顺序。 NULL值 是一种无类型的值,表示“空,什么也没有”。
  • 52. 数值列类型 MySQL为除了NULL值外的所有通用数据类型提供了列类型。列类型是一种手段,通过这种手段可以描述表的列可以包含什么样类型的值。 数值列类型 所有数值列类型的类型名及其说明和所占的字节数见下表:
  • 53. 数值列类型包括整型和浮点型 说明:TINYINT, SAMLLINT, MEDIUMINT, INT, BIGINT每个数据类型的取值范围不同,故分别可以表示不同的数值范围。在定义整型列时,可以指定可选的显示尺寸M(见上表),M是从1到255的值,它表示显示列中值的字符数。
  • 54. 例如:INT(4) 意思是指定了一个具有4个字符显示宽度的INT列。如果定义了一个没有明确宽度的整数列,则会分配缺省的宽度,缺省值为每种类型的最长值的长度。 对于每种浮点型,可指定一个最大的显示尺寸M和小数位数D,M的取值应该是0-30,但小于M-2。M和D对于DECIMAL是必须的。
  • 55. AUTO_INCREMENT 自动标识列,在需要产生唯一标志符号或者顺序值时候,可用此属性。值一般从1开始,每行增加1,在插入NULL到一个AUTO_INCREMENT列时,MySQL会插入一个比该列中当前最大值大1 的值,一个表中最多能有一个有此属性的列。对于想使用此属性的列应该定义为NOT NULL,并定义为PRIMARY KEY 或者定义为UNIQUE键。 举例: create table t(id int auto_increment not null primary key); UNSIGNED 无符号,此属性禁用负值,将数值的取值范围从零开始。 举例: create table t(num tinyint , num2 tinyint unsigned);数值列的完整性约束
  • 56. NULL和NOT NULL 默认为NULL,即插入值时没有在此字段插入值时自动填NULL,如果指定了NOT NULL,则必须在插入值时在此字段添入值,不允许插入NULL值。 DEFAULT 可以通过此属性来指定一个缺省值,即如果没有在此列添加值,那么默认添加DEFAULT后指定值。 ZEROFILL 前导零填充数值类型值以达到列的显示宽度。 举例: create table test2(num1 int default 1,num2 int zerofill);
  • 57. (本页无文本内容)
  • 58. 字符串列类型 所有字符串列类型及其说明和所占的字节数见下表:
  • 59. 说明: 对于可变长的字符串类型,其长度取决于实际存放在列中的值的长度。此长度在上表中用L来表示。附加若干额外字节,用来记录字符串长度,也就是说总长度等于L+n(n=1、2、3、4、8)
  • 60. CHAR和VARCHAR类型 CHAR类型和VARCHAR类型长度范围都是0~255之间的大小。他们之间的差别在于MySQL处理存储的方式: CHAR把这个大小视为值的准确大小(用空格填补比较短的值)。 VARCHAR类型把它视为最大值并且只使用了存储字符串实际上需要的字节数(增加了一个额外的字节记录长度)。因而,较短的值当被插入一个语句为VARCHAR类型的字段时,将不会用空格填补(然而,较长的值仍然被截短 )。
  • 61. (本页无文本内容)
  • 62. BLOB和TEXT类型 BLOB是二进制大对象,如果想存储二进制数BLOB将是最佳选择,而TEXT与之相同,只是BOLOB按照二进制编码排序(区分大小写),TEXT按照字符文本模式排序(不区分大小写)。 ENUM 和SET类型 是特殊的串类型,其列值必须从固定的串集中选择,二者差别为前者必须是只能选择其中的一个值,而后者可以多选。(不推荐使用)
  • 63. 字符串列类型的可选属性 BINARY 使用此属性可以使列值作为二进制串处理,即看成BLOB类型。 NULL 和NOT NULL 同数值型功能相同 DEFAULT 同数值型功能相同
  • 64. 日期和时间列类型 所有时间和日期列类型的类型名及其说明和所占的字节数见下表 :
  • 65. (本页无文本内容)
  • 66. 说明: 每个时间和日期列类型都有一个零值,当插入非法数值时就用零值来添加 表示日期时必须先按:年,月,日的顺序给出 DATE ,TIME ,DATETIME分别是存储日期,时间与日期和时间的组合,其格式为“YYYY-MM-DD”, “hh:mm:ss”和“YYYY-MM-DD hh:mm:ss”,对于DATETIME类型,日期和时间部分都需要 TIMESTAMP 时间戳列类型以YYYYMMDDhhmmss的格式来表示值,其取值范围是19700101000000到2037年的某个时间,主要用于记录更改或创建某个记录
  • 67. (本页无文本内容)
  • 68. (本页无文本内容)
  • 69. (本页无文本内容)
  • 70. 总结:常用数据类型分类备注和说明数据类型说明二进制数据类型存储非子符和文本的数据BLOB可用来存储图像文本数据类型字符数据包括任意字母、符号或数字字符的组合char固定长度的非 Unicode 字符数据varchar可变长度非 Unicode 数据text存储长文本信息日期和时间日期和时间在单引号内输入time时间date日期datetime 日期和时间数值型数据该数据仅包含数字,包括正数、负数以及浮点数int smallint整数float double浮点数货币数据类型用于财务数据decimal定点数Bit数据类型表示是/否的数据bit存储布尔数据类型
  • 71. 创建数据库建立数据库操作: 语法:create database 数据库名 叙述:创建一个具有指定名称的数据库。如果要创建的数据库已经存在,或者没有创建它的适当权限,则此语句失败。 例:建立一个student库。 mysql> create database student;
  • 72. 建立表操作: 语法:create table 表名( 列名1 列类型 [<列的完整性约束>], 列名2 列类型 [<列的完整性约束>], ... ... ); 叙 述:在当前数据库下新创建一个数据表。 列类型:表示该列的数据类型。 例: 建立一个表school,其由两列组成,第一列属性为非空,并做为主键,并自增 create table school( school_id int(10) not null auto_increment primary key, school_name varchar(20) );创建表
  • 73. 常见完整性约束: PRIMARY KEY 主码约束(主键) UNIQUE 唯一性约束 NOT NULL 非空值约束 AUTO_INCREMENT 用于整数列默认自增1 UNSIGNED 无符号整数 DEFAULT default_value 默认值约束 DEFAULT cur_timestamp 创建新记录时默认保存当前时间(仅适用timestamp数据列) ON UPDATE cur_timestamp 修改记录时默认保存当前时间(仅适用timestamp数据列) CHARACTER SET name 指定字符集(仅适用字符串)
  • 74. 数据表类型在创建一个新的MySQL数据表时,可以为它设置一个类型,其中最重要的3种类型是 MyISAM:成熟、稳定和易于管理 InnoDB:加入事物、数据行级锁定机制、外键约束条件、崩溃恢复等新功能 HEAP:只存在于内存中,可做临时表 create table tmp(…)ENGINE=MyISAM
  • 75. 主键与外键数据表之间的关联/引用关系是依靠具体的主键(primary key)和外键(foreign key)建立起来的。 主键:帮助MySQL以最快的速度把一条特点的数据记录的位置确定下来。 主键必须是唯一的 主键应该是紧凑的,因此整数类型比较适合 外键:引用另外一个数据表的某条记录。 外键列类型尽可能与主键列类型保持一致 外键列应该加上NOT NULL
  • 76. 主键 create table student( sid int not null auto_increment, name varchar(20) not null, primary key(sid) ); 外键(自动检查外键是否匹配,仅适用InnoDB) create table score( cid int not null auto_increment primary key, score int, sid int, foreign key(sid) references student(sid) );
  • 77. 主表和从表1、当主表中没有对应的记录时,不能将记录添加到子表 ——成绩表中不能出现在学员信息表中不存在的学号; 2、不能更改主表中的值而导致子表中的记录孤立 ——把学员信息表中的学号改变了,学员成绩表中的学号也应当随之改变; 3、子表存在与主表对应的记录,不能从主表中删除该行 ——不能把有成绩的学员删除了 4、删除主表前,先删子表 ——先删学员成绩表、后删除学员信息表
  • 78. 思考电话号码一般使用什么数据类型存储? 性别一般使用什么数据类型存储? 年龄信息一般使用什么数据类型存储? 照片信息一般使用什么数据类型存储? 薪水一般使用什么数据类型存储?
  • 79. 思考学员姓名允许为空吗? 家庭地址允许为空吗? 电子邮件信息允许为空吗? 考试成绩允许为空吗?
  • 80. 思考在主键列输入的数值,允许为空吗?不允许 一个表可以有多个主键吗?不可以 在一个学校数据库中,如果一个学校内允许重名的学员,但是一个班级内不允许学员重名,可以组合班级和姓名两个字段一起来作为主键吗?
  • 81. 选择主键的原则最少性 尽量选择单个键作为主键 稳定性 尽量选择数值更新少的列作为主键
  • 82. 思考标识列允许为字符数据类型吗?INT 如果标识列A的初始值为1,则输入三行数据以后,再删除两行,下次再输入数据行的时候,标识值从多少开始?四
  • 83. 例:建立一个学生信息表(student) 定义列sno学号,类型为5位定长字符串,非空,主键 定义列sname姓名,类型为8位定长字符串,非空 定义列ssex性别,取值1或0 定义列sage年龄,类型为短整型 定义列sdept系名,数据类型为20位变长字符串 例:建立一个课程信息表(course) 定义列courseid课程号,类型为整型,非空,自动增长,主键 定义列cname课程名,类型为16位变长字符串,非空 例:建立一个学生考试成绩信息表(sc) 定义列grade成绩,类型为整型 要求 把表字段补充完整,可以通过学号查看学生相关课程的成绩
  • 84. 显示表结构操作语法:describe 表名; desc 表名; 叙述:用于显示表的创建结构。
  • 85. 删除数据库或表操作删除表操作 语法:drop table [if exists] tab_name [,tab_name]... 叙述:从数据库中删除给定的表。如果给出if exists 子句,则删除不存在的表不会出错。 删除数据库操作 语法:drop database [if exists] db_name 叙述:删除给定的数据库。在删除一个数据库后,它就永远没有了,因此要特别小心。如果给出if exists 子句,则删除不存在的数据库不会出错。
  • 86. 更改表结构操作语法:alter table 表名 action; 说明:action 可以是如下语句: add 列名 建表语句 [first | after 列名] 可以为表添加一列,如果没指定first或者after,则在列尾添加一列,否则在指定列添加新列 add primary key (列名) 为表添加一个主键,如果主键已经存在,则出现错误 add foreign key(列名) references 表名(列名) 为表添加一个外键 alter 列名 set default 默认值 可以更改指定列默认值
  • 87. change 旧列名 新列名 <建表语句> [first | after 列名] 可以更改列类型和列名称,如果原列的名字和新列的名字相同 modify 列名 <建表语句> [first | after 列名] 和change的作用相同 drop 列名 //可以删除一列 drop primary key //可以删除主键 engine 类型名 //可以改变表类型 rename as 新表名 //可以将表名更改
  • 88. 举例1: 向people表中添加字段address2,类型为varchar,最大长度为100 alter table people add address2 varchar(100); 举例2: 将people表中的name列默认值改为100: alter table people alter name set default 100; 举例3: 向student表增加“入学时间”列,其数据类型为日期型。 alter table student add scome date; 注:无论基本表中原来是否已有数据,新增加的列一律为空值。 举例4: 将年龄的数据类型改为半字长整数。 alter table student modify sage smallint; 注:修改原有的列定义有可能会破坏已有数据
  • 89. MySQL运算符 算数运算符 比较运算符 逻辑运算符 位运算符
  • 90. 算数运算符
  • 91. 用字符串表示的数字在任何可能便于计算的地方都被自动地转换为数字。当执行转换时,MySQL遵循两个基本规则: 如果第一位是数字的字符串被用于一个算数运算中,那么它被转换为这个数字的值。 如果一个包含字符和数字混合的字符串不能被正确的转换为数字,那么它被转换成0。 字符串自动转换数字
  • 92. (本页无文本内容)
  • 93. (本页无文本内容)
  • 94. 比较运算符 比较运算符允许我们对表达式的左边和右边进行比较。一个比较运算符的结果总是1(真),0(假),或是为NULL(不能确定)。 比较运算符可以用于比较数字和字符串。数字作为浮点值比较,而字符串以不区分大小写的方式进行比较(除非使用特殊的BINARY二进制关键字)
  • 95. (本页无文本内容)
  • 96. 对于<>运算符,如果表达式两边不相等返回真值,相等返回假值。还可以比较字符串
  • 97. BETWEEN运算符 用于检验一个值(或者一个求值表达式)是否存在一个指定的范围内:
  • 98. IN运算符用于检验一个值(或者一个求值表达式)是否包含在一个指定的值集合中。
  • 99. 可以使用IS NULL或者IS NOT NULL运算符来测定是否为空。 可以使用特殊的<=>运算符,MySQL称为”NULL安全的等于”运算符。这意味着即使当包含在比较运算符中的表达式含有一个NULL值时,MySQL也会为比较运算符返回一个真值或假值。
  • 100. 如果想执行通配符数据搜索,应该使用LIKE运算符。它通过在表达式中允许使用专门的通配字符,可以找出与指定搜索字符串全部或部分匹配的记录。
  • 101. 默认情况下,比较是不区分大小写的方式执行的。然而,以前我们注意到,可以添加BINARY关键字让MySQL执行区分大小写的比较。
  • 102. MySQL数据库中的通配符“%” (百分号) 代表任意长度(长度可以为0)的字符串 举例: a%b表示以a开头,以b结尾的任意长度的字符串。如acb,addgb,ab 等都满足该匹配串 “_ ”(下横线) 代表任意单个字符 举例: a_b表示以a开头,以b结尾的长度为3的任意字符串。如acb,afb等都满足该匹配串
  • 103. REGEXP运算符 允许我们执行更复杂的字符串比较运算,在这里使用UNIX正则表达式。
  • 104. (本页无文本内容)
  • 105. 逻辑运算符
  • 106. (本页无文本内容)
  • 107. 位运算符如下表所示: “~” 表示按位取反。 “^” 表示异或。
  • 108. (本页无文本内容)
  • 109. (本页无文本内容)
  • 110. 插入记录操作语法: 叙述: 如果表名后面没写字段名,则默认是向所有的字段添加值,另外字符串值应该用‘ ’或“ ”引号括起来 举例1:向people表中添加一条记录: insert into people(name,age) values(“zhangsan”,20);INSERT [INTO] <表名> [列名] VALUES <值列表>
  • 111. 插入案例创建一张学生信息表,往表中插入数据 create table students( scode int not null auto_increment, sname varchar(20) not null, saddress varchar(20) default‘未知’, sgrade int, semail varchar(20), ssex bit, primary key(scode) );
  • 112. 插入数据行 1注意事项1:每次插入一行数据,不可能只插入半行或者几列数据,因此,插入的数据是否有效将按照整行的完整性的要求来检验;INSERT INTO Students (SName,SAddress,SGrade,SEmail,SSEX) VALUES (‘小明') ×
  • 113. 插入数据行 2注意事项2:每个数据值的数据类型、精度和小数位数必须与相应的列匹配;INSERT INTO Students (SName,SAddress,SGrade,SEmail,SSEX) VALUES (‘张青裁’,‘上海松江’,ZQC,‘ZQC@Sohu.com’, ‘男') ×
  • 114. 插入数据行3注意事项4:如果在设计表的时候就指定了某列不允许为空,则必须插入数据;INSERT INTO Students (SAddress,SGrade,SEmail,SSEX) VALUES ('上海松江',6,'ZQC@Sohu.com',0) ×
  • 115. 插入数据行4注意事项6:具有缺省值的列,可以使用DEFAULT(缺省)关键字来代替插入的数值INSERT INTO Students (SName,SAddress,SGrade,SEmail,SSEX) VALUES ('张青裁',DEFAULT,6,'ZQC@Sohu.com',0)
  • 116. 插入多行数据INSERT STUDENTS (SName,SGrade,SSex) VALUES('测试女生1',75,0), ('测试女生2',77,0), ('测试女生3',83,0), (‘测试男生1',81,1), ('测试女生4',90,0), (‘测试男生2',94,1), ('测试女生5',51,0), (‘测试男生3',53,1);INSERT INTO <表名>(列名) VALUES(<列名值>), (<列名值>), (<列名值>) ……
  • 117. 插入多行数据INSERT INTO TongXunLu ('姓名','地址','电子邮件') SELECT SName,SAddress,SEmail FROM StudentsINSERT INTO <表名>(列名) SELECT <列名> FROM <源表名>
  • 118. 学生、课程、成绩插入数据学生信息 '95001','洛燕妮','女',20,'信息系' '95002','欧阳炎','男',19,'计算机系' '95003','何东升','男',21,'美术系' '95004','刘晨','男',18,'计算机系' '95005','沙明华','女',21,'美术系' '95006','欧阳宝宝','男',19,'美术系’ 课程信息 1,'JAVA' 2,'C' 3,'.NET' 成绩信息 '95001',1,98 '95002',1,50 '95004',1,70 '95003',4,95 '95005',4,40 '95006',4,77 '95001',2,80 '95002',3,58 '95006',2,70
  • 119. 更改记录操作语法: 叙述: where 子句是判断语句,用来设定条件,限制只更新匹配的行,如果不带where子句,则更新所有行数据。 举例: 将student表中的所有学生名称为"Alex"的改为"Tom": update student set sname="Tom" where sname="Alex";UPDATE <表名> SET <列名 = 更新值> [WHERE <更新条件>]
  • 120. 更新数据行UPDATE Student SET SSEX = 0UPDATE Student SET Sdept ='家政系' WHERE Sdept = '刺绣系'UPDATE Sc SET Grade = Grade + 5 WHERE Grade <= 95
  • 121. 删除记录操作语法: 叙述:此语句删除表中的行,如果不带where子句,则删除整个表中的记录,但是表不被删除。 举例: 删除student表中的所有年龄小于18岁的记录: delete from student where sage<18;DELETE FROM <表名> [WHERE <删除条件>]
  • 122. 补充说明 删除学生编号为'95005'的学生信息包括成绩信息 delete from student,sc using student,sc where student.sno = sc.studentid and student.sno='95005'; 说明: 表和字段的引用方式有两种:绝对引用和相对引用 绝对引用:数据库名.表名(.字段名) 相对引用:表名.(字段名)
  • 123. 删除数据行河南新乡赵可以0010016河南新乡张丽鹃0010015江西南昌雷铜0010014湖南新田吴兰0010013山东定陶李山0010012….地址姓名学号980010016数学810010013语文670010012语文740010013数学880010012数学….分数学号科目DELETE FROM 学员信息表 WHERE 学号 ='0010012'
  • 124. 什么是查询?Application Logic客户程序查询请求查询结果集SQL SERVERA B C D E F GSELECT * FROM SALES查询产生一个虚拟表,看到的是表形式显示的结果,但结果并不真正存储,每次执行查询只是现从数据表中提取数据,并按照表的形式显示出来
  • 125. 怎么查的?学员编号学员姓名地址所在班级…….001张明全湖南长沙S201002李菲湖北宜昌S202003于寄谦甘肃天水S204004刘国正山东荷泽S201005周接轮台湾新竹S204006巩小妹香港龙湾S203007巩大妹香港龙湾S203008张明敏北京顺义S202009矛十八四川棉阳S204010罗林光陕西临潼S202011司马坡新疆喀什S201S202陕西临潼罗林光010…S202湖北宜昌李菲002S202北京顺义张明敏008SELECT * FROM Student WHERE 所在班级 = ‘S202’
  • 126. SQL语法SELECT [DISTINCT | DISTINCTROW | ALL] select_expression,... [FROM table_references [WHERE where_definition] [GROUP BY col_name,...] [HAVING where_definition] [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...] [LIMIT [offset,] rows] [PROCEDURE procedure_name] ]
  • 127. 查询记录操作from子句:指定查询数据的表 where子句:查询数据的过滤条件 group by子句:对匹配where子句的查询结果进行分组 having子句:对分组后的结果进行条件限制 order by子句:对查询结果结果进行排序,后面跟desc降序或asc升序(默认)。  limit子句:对查询的显示结果限制数目 procedure子句:查询存储过程返回的结果集数据
  • 128. 查询举例SELECT <列名> FROM <表名> [WHERE <查询条件表达式>] [ORDER BY <排序的列名>[ASC或DESC]] 列名称 SELECT SNo, SName, Sdept 表名 FROM Student过滤条件WHERE SSEX = 0排序条件ORDER BY SNo SELECT SNo,SName,Sdept FROM Students WHERE SSEX = 0 ORDER BY SNo
  • 129. 举例1 : 查询全体学生的学号与姓名。 select sno,sname from student; 举例2 : 查询全体学生的详细记录。 select sno,sname,ssex,sage,sdept from student; 或select * from student; 举例3 : 查全体学生的姓名及其出生年份。 select sname,2008-sage from student;  举例4 : 查询全体学生的姓名、出生年份和所有系,要求用小写字母表示所有系名。 select sname,'year of birth: ',2008-sage, islower(sdept) from student; 举例5 : 查询选修了课程的学生学号(去掉重复的记录) select distinct studentid from sc;
  • 130. 举例6 : 查询全体学生的学号与姓名,用中文显示列名。 select sno as ‘编号’,sname as ‘姓名’ from student; 举例7 : 给表设置别名。 select s.sno,s.sname from student as s; 举例8 : 查询年龄在20以下的学生的姓名。 select sname from student where sage<20;  举例9 : 查询全体学生的姓名、年龄,要求按照年龄降序排序。 select sname,sage from student order by sage desc; 举例10 : 查询年龄最大的前3个学生的姓名和年龄,或第4、5个学生 select sname,sage from student order by sage desc limit 3;或(limit 3,2)
  • 131. 使用集函数SQL提供的统计函数称为集函数. 主要的集函数: 记数函数: count(列名) 计算元素的个数 求和函数: sum(列名) 对某一列的值求和,但属性必须是整型 计算平均值:avg(列名)对某一列的值计算平均值 求最大值: max(列名) 找出某一列的最大值 求最小值: min(列名) 找出某一列的最小值
  • 132. 举例11 : 查询学生总数。 select count(*) from student; 举例12 : 查询选修了课程的学生人数。 select count(distinct studentid) from sc; 举例13 : 查询1号课程的学生平均成绩。 select avg(grade) from sc where courseid=1; 举例14 : 查询1号课程的学生最高分和最低分。 select max(grade) as ‘最高分’,min(grade) as ‘最低分’ from sc where courseid=1; 举例15 : 查询每个学生的平均成绩。 select studentid,avg(grade) as ‘平均成绩’ from sc group by studentid; 举例16 : 查询学生的平均成绩在70分以上的。 select studentid,avg(grade) as ‘平均成绩’ from sc group by studentid having avg(grade)>70;
  • 133. 在WHERE子句中使用谓词 : BETWEEN AND :在两数之间 NOT BETWEEN AND :不在两数之间 IN <值表> :是否在特定的集合里(枚举) NOT IN <值表> :与上面相反 LIKE :是否匹配于一个模式 IS NULL(为空的)或 IS NOT NULL(不为空的)REGEXP : 检查一个值是否匹配一个常规表达式。
  • 134. 举例17 : 查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄 。 select sname,sdept,sage from student where sage between 20 and 23; 举例18 : 查询年龄不在20~23岁之间的学生姓名、系别和年龄。 select sname,sdept,sage from student where sage not between 20 and 23; 举例19 : 查询'信息系'、'美术系'和'计算机系'学生的姓名和性别。 select sname,ssex from student where sdept in (‘信息系',‘美术系',‘计算机系');
  • 135. 举例20 : 查询学号为95001的学生的详细情况。 select * from student where sno like '95001'; 等价于:select * from student where sno='95001'; 举例21 : 查询所有姓刘学生的姓名、学号和性别。 select sname,sno,ssex from student where sname like ‘刘%'; 举例22 : 某些学生选修课程后没有参加考试,所以有选课记录,但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。 select studentid,courseid from sc where grade is null; 举例23 : 查所有有成绩的学生学号和课程号。 select studentid,courseid from sc where grade is not null; 举例24 : 查询计算机系年龄在20岁以下的学生姓名。 select sname from student where sdept=‘计算机系' and sage<20;
  • 136. 举例25 : 查询信息系、美术系和计算机系学生的姓名和性别 select sname,ssex from student where sdept in (‘信息系','美术','计算机系'); 可改写为: Select sname,ssex from student where sdept='信息系' or sdept='美术' or sdept='计算机系'; 举例26 : 查询选修了3号课程的学生的学号及其成绩,查询结果按分数降序排列。 select studentid,grade from sc where courseid=3 orber by grade desc; 举例27 : 查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列。 select * from student order by sdept,sage desc;
  • 137. 多表查询—问题学员内部测试成绩查询的每次显示的都是学员的编号信息, 因为该表中只存储了学员的编号; 实际上最好显示学员的姓名,而姓名存储在学员信息表; 如何同时从这两个表中取得数据?
  • 138. 多表查询—分类连接查询 同时涉及多个表的查询称为连接查询 用来连接两个表的条件称为连接条件 内连接(INNER JOIN) 外连接 ——左外联结 (LEFT JOIN) ——右外联结 (RIGHT JOIN) 外连接与普通连接的区别 普通连接操作只输出满足连接条件的元组 外连接操作以指定表为连接主体,将主体表中不满足连接条件的元组一并输出:
  • 139. SELECT S.SName,C.CourseID,C.Grade From Sc AS C INNER JOIN Students AS S ON C.StudentID = S.SNoScStudentsIDCourseIDGrade122300100100200297896776300381猜一猜:这样写,返回的查询结果是一样的吗? SELECT S.SName,C.CourseID,C.Grade From Students AS S INNER JOIN Sc AS C ON C.StudentID = S.SNo再猜一猜:以下返回多少行? SELECT S.SName,C.CourseID,C.Grade From Students AS S INNER JOIN Sc AS C ON C.StudentID <> S.SNo多表查询—内连接-1StundentsSName梅超风陈玄风陆乘风曲灵风SNo1234查询结果SName梅超风陈玄风陈玄风陆乘风CourseIDGrade00100100200297896776陆乘风00381
  • 140. 多表查询—内连接-2SELECT Students.SName, Sc.CourseID, Sc.Grade FROM Students,Sc WHERE Students.SNo = Sc.StudentID
  • 141. 多表查询—三表连接SELECT S.SName AS 姓名, CS.CName AS 课程, C.Grade AS 成绩 FROM Students AS S INNER JOIN Sc AS C ON (S.SCode = C.StudentID) INNER JOIN Course AS CS ON (CS.CourseID = C.CourseID)
  • 142. ScStudentsIDCourseIDGrade122300100100200297896776300381多表查询—左外连接StundentsSName梅超风陈玄风陆乘风曲灵风SNo1234查询结果SName梅超风陈玄风陈玄风陆乘风CourseIDGrade00100100200297896776陆乘风00381曲灵风NULLNULLSELECT S.SName,C.CourseID,C.Grade From Students AS S LEFT JOIN Sc AS C ON C.StudentID = S.SNo猜一猜:这样写,返回的查询结果是一样的吗? SELECT S.SName,C.CourseID,C.Grade From Sc AS C LEFT JOIN Students AS S ON C.StudentID = S.SNo
  • 143. 多表查询—右外连接SELECT Titles.Title_id, Titles.Title, Publishers.Pub_name FROM titles RIGHT OUTER JOIN Publishers ON Titles.Pub_id = Publishers.Pub_id
  • 144. 子查询: 将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为子查询。一个SELECT-FROM-WHERE语句称为一个查询块 例如: select sname from student where sno in (select studentid from sc where courseid=2);
  • 145. 例如: 查询选修了课程名为“Java”的学生学号和姓名 select sno,sname from student where sno in (select studentid from sc where cno = (select cno from course where cname=‘Java’) );
  • 146. 复制表我们来创建一个副本:
  • 147. 复制表可以通过在SELECT查询中指定字段来限制出现在附表中的字段
  • 148. 复制表通过使用SELECT 语句创建已存在表的空副本,并且返回一个空结果集。
  • 149. 数据库之路未完,待续…..