MySQL基础2
此篇为比较偶基础的复习时的练习(但是有部分丢失还有部门未看完)
数据库
大型数据库有:Oracle、Sybase、DB2、SQL server 小型数据库有:Access、MySQL、BD2等。
1、MySQL
MySQL是目前最受欢迎开源的SQL数据库管理系统,与其他的大型数据库Oracle、DB2、SQL Server等相比,MySQL虽然有它的不足之处,但丝毫也没有减少它受欢迎的程度。对于个人或中小型企业来说,MySQL的功能已经够用了, MySQ L又是开源软件,因此没有必要花大精力和大价钱去使用大型付费数据库管理系统了。
特点:
1、MySQL是开源免费的。
2、MySQL服务器是可靠的、易于使用的、快速的。
3、MySQL服务器工作在客户/服务器或嵌入系统中。
4、MySQL软件很多。
5、MySQL是一个关系数据库管理系统。
2、SQL Server
SQL Server是由微软公司开发的关系型数据库管理系统,一般用于Web上存储数据。SQL Server 提供了众多功能,如对XML和Internet标准的丰富支持,通过Web对数据轻松安全的访问,具有灵活的、安全的、和基于Web的应用程序管理等,及容易操作的操作界面,受到广大用户的喜爱。
3、Oracle
Oracle在数据库领域一直处于领先地位,由于有先进技术的不断更新,目前Oracle产品覆盖甚广,成为了世界上使用最广泛的关系数据系统之一。
完整的数据管理功能:
1、数据的大量性
2、数据的保存的持久性
4、数据的共享性
5、数据的可靠性
4、Sybase
Sybase美国Sybase公司研制的一种关系型数据库系统,是一种典型的UNIX或WindowsNT平台上客户机/服务器环境下的大型数据库系统。
特点:
1、客户/服务器体系结构
2、真正开放的
3、高性能的
5、DB2
DB2是美国IBM公司开发的一套关系型数据库管理系统,主要应用于大型应用系统,具有较好的可伸缩性。
数据分析
原则:为了验证假设的问题,需要提供必要的数据验证;为了挖掘更多的问题,并找出深层次的原因;不能为了做数据分析而做数据分析
步骤:数据探索、建立模型、模型验证
分析对象:关系型数据库、事物型数据库、面向对象型数据库;数据仓库/多维数据库;空间数据(如地图信息);工程数据;文本和多媒体数据;时间相关的数据;万维网
MySQL
数据库:一个以某种有组织的方式存储的数据集合(保存有组织的数据的容器)
表:某种特定类型数据的结构化清单
列:表中的一个字段
行:表中的数据按行存储
主键:一列(或一组列),其值能够唯一区分表中每个行;唯一标识表中每行的这个列(或这组列)称为主键
任意两行都不具有相同的主键
每行都必须具有一个主键值(主键类不允许null值)
SQL:结构化查询语言,专门与数据库通信的语言
MySQL:是一种数据库软件(DBMS)
DBMS:数据库软件,数据的存储、检索、管理和处理
基于共享文件系统的DBMS;基于客户机—服务器的DBMS(MySQL、Oracle和Microsoft SQL server)
连接到数据库
选择数据库 使用crashcourse 数据库 输入:use crashcourse; 输出:Database changed
SHOW显示表信息: SHOW DATABASES;
获得数据库中的表: SHOW TABLES;
显示表列: SHOW COLUMNS FROM customers; 或者DESCRIBE customers;
SHOW STATUS 显示广泛的服务器状态信息
SHOW CREATE DATABASE SHOW CREATE TABLE 显示创建特定数据库或表
SHOW GRANTS 显示授予用户的安全权限
SHOW ERRORS SHOW WARNINGS 显示服务器错误或警告消息
检索数据
检索单个列 SELECT PROD_NAME FROM PRODUCTS; 从PRODUCTS表中检索一个名为PROD_NAME的列
多条SQL语句必须以分号(;)分隔
SQL不区分大小写 惯例:对SQL关键字大写,随所有的列和表名小写(便于阅读和调试)
处理SQL语句时,其中空格都被忽略
检索多个列
从表products中选择数据,选定三列,每个列之间用逗号分隔 SELECT prod_id,pros_name,prod_price FROM products;
检索所有列 SELECT * FROM products; 通配符(*)
检索不同行
SELECT vend_id FROM products; 返回所有行
检索出有不同值的列表 SELECT DISTINCT vend_id FROM products; DISTINCT必须直接放在列名前面 ,DISTINCT应用于所有列而不仅是前置他的列。如果给出SELECT DISTINCT vend_id, prod_price, 除非指定的两个列都不同,否则所有行都将被检索出来。
限制结果
返回不多于5行 SELECT prod_name FROM products LIMIT 5;
指定要检索的开始行和行数 SELECT prod_name FROM products LIMIT 5,5;
注意:检索出来的第一行是行0 而不是行1 ,因此LIMIT 1,1是检索出第二行而不是第一行;行数不够时,LIMIT中指定要检索的行数为检索的最大行数,如果没有足够的行,MySQL讲只返回他能返回的那么多行
MySQL 5 的LIMIT语法 LIMIT 3,4 的含义是从行3开始的4行,容易混淆,可以用另一种替代语法LIMIT 4 OFFSET 3 ;
使用完全限定的表名(同时使用表名和列字)
SELECT products.prod_name FROM products;
排序检索数据 一般返回某个数据库表的单个列,他的返回值没有特定的顺序
排序数据 :ORDER BY(既可以用所检索的列来排序,也可以用非检索的列来排序数据)
SELECT prod_name FROM products ORDER BY prod_name;
指定排序方向 按价格以降序排序 SELECT prod_id ,prod_price,prod_name FROM products ORDER BY prod_price DESC;
多个列排序 先对价格排序,在对产品名排序 SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price DESC, prod_name; 这里对价格是降序排列
DESC 关键字之应用到直接位于其前面的列名,对后面的列不指定排序方向
与DESC相反的关键字是ASC(ASCENDING),在升序排序时可以指定他,一般默认是升序,所以没多大用处。
注意:区分大小写和排序顺序 在字典排序顺序中,A被视为与a相同,这是MySQL(和大多数数据库管理系统)的默认行为,有时候需要改变这种行为(如果数据库中包含大量的外语字符),当需要改变这种排序顺序是,用简单的ORDER BY子句做不到,必须请求数据库管理员的帮助。
使用ORDER BY和LIMIT组合,能够找出一个列中最高或最低的值。找出最昂贵的物品
SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1; (默认是由最昂贵到最便宜的,LIMIT 1 是仅返回一行)
注意:ORDER BY 字句应该位于FROM 字句后面,如果使用LIMIT,它必须位于 ORDERE BY 之后,使用子句的次序不对讲产生错误信息。
过滤数据:SEKECT prod_name ,prod_price FROM products WHERE prod_price = 2.5; 从products 表中检索两列,并返回prod_price 的值为2.5的列
数据的过滤也可以在应用层进行,但是在应用层过滤时,使用客户机(或者开发语言)处理数据库的工作会影响应用的性能,并且使所创建的应用完全不具备可伸缩性,如果在客户机上过滤数据,服务器不得的通过网络发送多余的数据,这将导致网络宽带的浪费。
注意:同时使用ORDER BY 和WHERE 时,应该让ORDER BY 处于 WHERE 后面。
WHERE子句操作符:
= 等于; <> 不等于; !=不等于; <小于; <=小于等于; >大于; >=大于等于;BETWEEN 两个值之间
检查单个值:SELECT prod_name,prod_price FROM products WHERE prod_name=’fuses’; 返回名字是fuses的行
列出价格小于10美元的产品 SELECT prod_name FROM products WHERE prod_price <10;
不匹配检查:列出供应商不是1003制造的产品 SELECT vend_id,prod_name FROM products WHERE vend_id <>1003;
WHERE 子句的引号使用:如果是值与字符串类型的列比较,需要加引号,而与数值列比较不需要引号,如上面的fuses。(字符串需要加引号)
范围值检查:检索5美元到10美元之间的所有产品 SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;
空值检查:SELECT prod_name WHERE prod_price IS NULL;
NULL与不匹配: 在过滤数据时,要验证返回数据中确实给出了被过滤列具有NULL的列。
数据过滤(多个限制条件)
AND标识符:SELECT prod_id, prod_name, prod_price FROM products WHERE vend_id =1003 AND prod_price<=10;
OR操作符: SELECT prod_name ,prod_price FRPM products WHERE vend_id=1002 OR vend_id=1003;
计算次序:SQL语句处理OR 操作符前,优先处理AND 操作符。如果有需要,可以用括号括起来消除歧义。
IN操作符:IN取合法值的由逗号分隔的清单,全部括在圆括号里面。
SELECT prod_name,prod_price in products WHERE vend_id IN(1002,1003) ORDER BY prod_name ; IN 和 OR 的功能一致。
NOT操作符:SELECT prod_name,prod_price FROM products where vend_id NOT IN (1002,1003) ORDER BY prod_name;
用速配符进行过滤:(找出名称包含某个字段的产品)
LIKE操作符:与通配符共同作用
通配符(wildcard)用来匹配值的一部分的特殊字符
百分号(%)通配符:表示任何字符出现的任意次数,找出所有词以jet起头的产品 SELECT prod_id ,prod_name FROM products WHERE prod_name LIKE ‘jet%’;
区分大小写,’jet%’ 与 JETPACK 1000 不匹配
SELECT prod_id,prod_name FROM products WHERE prod_name LIKE ‘%anvil%’; 搜索模式’%anvil%’ 表示匹配任何位置包含文本anvil 的值,不论他前或者后出现什么字符
通配符在中间的 SELECT prod_name FROM products WHERE prod_name LIKE ‘s%e’; 匹配以s开头以e结尾的产品
注意:尾空格可能会干扰通配符匹配。可以使用函数去掉首尾空格或者在搜索模式的最后附加一个%。 %通配符无法匹配出NULL,例如WHERE prod_name LIKE ‘%’ 无法匹配出用值NULL作为产品名的行。
下划线(_) 通配符 匹配单个字符 %匹配多个字符
SELECT prod_name,prod_id FROM products WHERE prod_name LIKE ‘_ton anvil’; 这个例子可以显示 ‘1 ton anvil’ ; 或者’2 ton anvil’ ; 但是像 ’ .5 ton anvil ‘ 就显示不了;如果吧下划线改成%就可以显示所有的;
使用通配符的技巧:不要过度使用通配符,不要把通配符用在搜索模式的开始处(降低搜索速度),注意通配符的位置。
使用正则表达式进行搜索
正则表达式以及应用:用来匹配文本的特殊的串(字符集合)。从一个文本中提取电话号码,查找名字中间有数字的所有文件,替换一个页面中所有URL为这些URL的实际HTML链接,在一个文本块中找到所有重复的词等等
使用MySQL正则表达式(MySQL仅仅支持多数正则表达式实现的一个很小的子集)
基本字符匹配
检索prod_name 包含文本1000的所有行: SELECT prod_name FROM products WHERE prod_name REGEXP ‘1000’ ORDER BY prod_name; REGEXP后跟的东西作为正则表达式(与文字正文1000匹配的一个正则表达式)处理。
使用正则表达式优点:SELECT prod_name FROM products WHERE prod_name REGEXP ‘.000’ order by prod_name; 可以匹配任意一个字符,1000和2000都可以匹配
LIKE和REGEXP区别:SELECT prod_name FROM products WHERE prod_name LIKE ‘1000’ ORDER BY prod_name; SELECT prod_name FROM products WHERE prod_name REGEXP ‘1000’ ORDER BY prod_name; 第一条语句不返回数据,而第二条语句返回一行,LIKE匹配整个列,如果被匹配的文本在列值中出现,LIKE讲不会找到他,相应的行也不会返回(除非使用通配符)。而REGEXP在列值内进行匹配,如果被匹配的文本在列值中出现,REGEXP将会找到他,相应的行被返回。
匹配不区分大小写 MySQL中的正则表达式匹配不区分大小写,如果需要区分,使用BINARY, 如WHERE prod_name REGEXP BINARY ‘JETPACK .000’ ;
进行OR匹配:SELECT prod_name FROM products WHERE prod_name REGEXP ‘1000|2000’ ORDER BY prod_name ; | 为正则表达式的OR操作符
可以给出两个以上的OR条件 ‘1000|2000|3000’ 将匹配1000或2000或3000
匹配几个字符之一: SELECT prod_name FROM products WHERE prod_name REGEXP ‘[123] Ton’ ORDER BY prod_name; 这里使用了正则表达式’[123] Ton’ 。[123]定义一组字符,他的意思是匹配1或2或3。因此,1 ton和2 ton都匹配且返回(没有3 ton)
[ ] 是另一种形式的OR语句。事实上,正则表达式 [123] Ton 为 [1|2|3]Ton的缩写,使用 [ ]来定义OR语句查找可以更好的理解 SELECT prod_name FROM products WHERE prod_name REGEXP ‘1|2|3 Ton’ ORDER BY prod_name; 会输出除了两个要求的行之外,还会检索出另外的三行,MySQL假定你的意思是’1’或者’2’或者’3 ton’。除非把字符 | 括在一个集合中,否则它将应用于整个串。
字符集合也可以被否定,即他们将匹配出除了指定字符之外的任何东西,为否定一个字符集,在集合的开始处防止一个^即可。因此,尽管[123]匹配字符1、2、3,但是[^123]却匹配这些字符外的任何东西。
匹配范围:SELECT prod_name FROM products WHERE prod_name REGEXP ‘[1-5] ton’ ORDER BY prod_name; [1-5]定义了一个范围,匹配1到5,因此返回3个匹配行2。由于5 ton 匹配,所以返回 .5 ton 。
匹配特殊字符:匹配’.’ SELECT vend_name FROM cendors WHERE vend_name REGEXP ‘.’ ORDER BY vend_name; 这种会检索出所有行,不是想要的结果,匹配特殊字符,必须用\\作为前导,\\. 表示查找. 。SELECT vend_name FROM vendors WHERE vend_name REGEXP ‘\\.’ ORDER BY cend_name;
\\也可以引用元字符(具有特殊含义的字符) \\f 换页; \\n 换行; \\r 回车; \\t 制表; \\v 纵向制表
匹配\ 匹配反斜杠(\)字符本身,需要使用\\\。
多数正则表达式实现使用单个反斜杠转义特殊字符,一遍能够使用这些字符本身,丹MySQL要求两个反斜杠(MySQL自己解释一个,正则表达式库解释另一个)
匹配字符类
匹配多个实例 重复元字符 *:0个或多个匹配; +:1个或多个匹配(等于{1,}) ?:0个或1个匹配(等于{0,1}) {n}:指定数目的匹配 {n,}:不少于指定数目的匹配 {n,m}:匹配数目的范围(m不超过255)
SELECT prod_name FROM products WHERE prod_name REGEXP ‘\\([0-9] sticks?\\)’ ORDER BY prod_name; 正则表达式\\([0-9] sticks?\\) \\(匹配),[0-9]匹配任何数字,sticks?匹配stick和sticks(s后的?使s可选,因为?匹配他前面的任何字符的0次或1次出现),\\)匹配)
匹配连载一起的任意4位数字SELECT prod_name FROM products WHERE prod_name REGEXP ‘[[:digit:]]{4}’ ORDER BY prod_name ; [:digit:]匹配任意数字,{4}确切的要求他前面的字符(任意数字)出现4次 或者 SELECT prod_name FROM products WHERE prod_name REGEXP ‘[0-9][0-9][0-9][0-9]’ ORDER BY prod_name ;
定位符: 定位元字符 ^ 文本的开始, $文本的结尾, [[:<:]] 词的开始, [[:>:]] 词的结尾
如果想找出一个数(包括以小数点开始的数)开始的所有产品,需要使用^定位符。
SELECT prod_name FROM products WHERE prod_name REGEXP ‘^[0-9\\.]’ ORDER BY prod_name; ^匹配串的开始,^[0-9\\.]只在.或者任意数字为串中第一个字符时才匹配他们。没有^,则还要多检索出4个别的行(那些中间有数字的行)。
^的双重用途,在集合中(用[和]定义),用它来否定该集合,否则,用来指串的开始处。
使REGEXP起类似LIKE的作用,利用定位符,用^开始每个表达式,用$结束每个表达式,可以使REGEXP作用和LIKE一样。
简单的正则表达式测试: 可以在不使用数据库的情况下用SELECT来测试正则表达式,REGEXP检查总是返回0(没有匹配)或1(匹配),可以用带文字串的REGEXP来测试表达式,并实验他们,相应语法为 SELECT ‘hello’ REGEXP ‘[0-9]’; 这个例子将返回0(因为hello中没有数字)
创建计算字段
计算字段:字段 :基本上与列(column)的意思相同,经常互换使用,不过数据库列一般称为列,而术语字段通常用在计算字段的连接上。注意,只有数据库知道SELECT语句中那些事实际的表列,那些列是计算字段。从客户机(如应用程序)的角度来看,计算字段的数据是以其他列的数据相同的方式返回的。
拼接字段:concatenate 在MySQL的SELECT语句中,使用Concat( ) 函数来拼接两个列
MySQL的不同之处 多数DBMS使用+或||来实现拼接,MySQL则使用Concat( ) 函数来实现,当把SQL语句转换成MySQL语句时需要铭记在心。
SELECT Concat(vend_name. ‘(‘. vend_country ‘)’) FROM vendors ORDER BY vend_name; Concat( ) 连接串,即把多个串连接起来形成一个较长的串。 Concat( ) 需要一个或多个指定的串,各个串之间用逗号分隔。上面的SELECT语句链接一下4个元素:1.存储在vend_name列中的名字;2包含一个空格和一个左圆括号的串;3存储在vend_country列中的国家;4包含一个右圆括号的串
通过删除数据右侧多余的空格来整理数据,可以使用MySQL的RTrim( ) 函数来完成 SELECT Concat(RTrim(vend_name), ’(‘, RTrim(vend_country),’)’) FROM vendors ORDER BY vend_name; RTrim( ) 函数去掉值右边的所有空格,通过使用RTrim( ) ,各个类都进行了整理。
Trim函数 MySQL除了支持RTrim( ) 去除右边的空格,还支持LTrim( ) 来去除左边的空格,以及Trim( ) 去除串左右两边的空格。
使用别名 SQL支持列别名。别名(alisa)是一个字段或值的替换名。别名用AS关键字赋予 SELECT Concat(RTrim(vend_name), ‘(‘ RTrim(vend_country), ‘)’) AS vend_title FROM vendors ORDER BY vend_name; 这里的语句计算字段之后跟了文本AS vend_title。他指示SQL创建一个包含指定计算的名为vend_title的计算字段。从输出中可以看到,结果与以前的相同,但现在列名为vend_title,任何客户机应用都可以按名引用这个列,就像他是一个世纪的表列一样。
执行算术计算:SELECT prod_id ,country,item price FROM orderitems WHERE order_nun=20005; item_price 列包含订单中每项物品的单价,如下汇总物品价格(单价乘以数量) SELECT prod_id, quantity item_price quantity*item_price AS expanded_price FROM orderitems WHERE order_num =20005; expanded_price 为一个新的计算字段,客户机应用现在可以使用这个新计算列和使用其他列一样。
MySQL支持以下基本算数操作符,此外,圆括号可以用来优先顺序,+ 加;- 减; *乘;/ 除
如何测试计算 SELECT通常用来从表中检索数据,但可以省略FROM子句以便简单地访问和处理表达式。例如,SELECT 3*2,将返回6,SELECT Trim(‘abc’),将返回abc,而SELECT Now() 利用Now() 函数将返回当前的日期和时间。
使用数据处理函数 函数:SQL支持利用函数来处理数据,函数一般在数据上执行的,他给数据的转换和处理提供了方便。
使用函数:大所述SQL实现支持一下类型的函数:1用于处理文本串(如删除或填充值,转换值为大写或小写)的文本函数;2用于在述职数据上进行算术操作(如返回绝对值,进行代数运算)的数值函数;3返回DBMS正使用的特殊信息(如返回用户登录信息,检查版本细节)的系统函数。
文本处理函数:Upper() 将文本转换为大写, SELECT vend_name, Upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;结果中每个供应商都列出两次,第一次为vendors 表中存储的值,第二次作为列vend_name_upcase转换为大写。
一些常用的文本处理函数: Left() 返回串左边的字符; Length() 返回串的长度; Locate() 提出串的一个字串; Lower() 将串转换为小写; LTrim() 去掉串左边的空格; Right() 返回串右边的字符; RTrim()去掉右边的空格; Sounsex() 返回串的SOUNDEX值; Upper() 将串转换为大写
SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节,是的能对串进行发音比较而不是字母比较。虽然SOUNDEX不是SQL概念,但MySQL(和多数DBMS一样)都提供对SOUNDEX的支持。
给出一个Soundex()的例子,customers表中有一个顾客Cayote Inc,其联系名为Y.Lee。但如果这是输入错误,此联系名实际应该是Y.Lie,显然按照正确的联系名搜索不会反悔数据,可以使用Soundex()函数进行搜索,匹配所有发音类似于T.Lie的联系名: SELECT cust_name, cust_contact FROM customers WHERE Soundex(Cust_contact=’Y.Lie); WHERE子句使用Soundex() 函数转换cust_contact列值和搜索串为他们的SOUNDEX值。因为Y.Lee和Y.Lie发音相似,所以他们的SOUNDEX值匹配,因此WHERE子句正确的过滤了所需的数据。
日期和时间处理函数: AddDate() 增加一个日期(天、周等); AddTime() 增加一个时间(时,分等); CurDate() 返回当前日期; CurTime() 返回当前时间; Date() 返回日期时间的日期部分; DateDiff() 计算两个日期之差; Date_Add() 高度灵活的日期运算函数; DateFormat() 返回一个格式化的日期或时间串; Day()返回一个日期的天数部分; DayOFWeek() 对于一个时间,返回对应的星期几; Hour() 返回一个时间的小时部分; Minute() 返回一个时间的分钟部分; Month()返回一个日期的月份部分; Now()返回当前的日期和时间; Second()返回一个日期时间的秒部分;Tine() 返回一个日期时间的时间部分; Year() 返回一个日期时间的年份部分 。
MySQL是用的日期格式,无论你什么时候指定一个日期,不管插入或更新表值还是用WHERE子句进行过滤,日期必须为格式yyyy-mm-dd。MySQL应该总是使用4位数字的年份。
如果想要的仅仅是日期,使用Date() 比较好,及时已知相应的列只包含日期也是如此,这样如果由于莫种原因表中以后有日期和时间值,SQL代码也不需要改变,同理如果只想要时间的话就使用Time()函数。 这两个函数都是在MySQL4.1.1中第一次引入的。
检索出2018年12月下的所有订单: SELECT cust_id,order_num FROM orders WHERE Date(order_data) BETWEEN ‘2018-12-01’ AND ‘2018-12-31’; 或者SELECT cust_id, order_num FROM orders WHERE year (order_date)=2018 AND Month(order_date)=12;
数值处理函数: 数值处理函数仅处理数值函数,一般主要用于代数。三角或几何运算,在主要的DBMS的函数中,数值函数是最一致和统一的函数,常用的数值处理函数: Abs() 返回一个数的绝对值; Cos() 返回一个角度的余弦值; Exp() 返回一个数的指数值; Mod() 返回除操作的余数; Pi() 返回圆周率; Rand() 返回一个随机数; Sin() 返回一个角度的正弦值; Sqrt() 返回一个数的平方根; Tan() 返回一个角度的正切。
汇总函数
聚集函数: 对表中数据(而不是实际数据本身)汇总。因此,返回实际表数据是对时间和处理资源的 一种浪费,比如 确定表中行数(或者满足某个条件或包含某个特定值的行数),获得表中行组的和, 找出表列(或所有行或某些特定的行)的最大值、最小值和平均值。 这种情况MySQL给出了5个聚集函数:AVG() 返回某列的平均值; COUNT() 返回某列的行数; MAX() 返回某列的最大值; MIN() 返回某列的最小值; SUM() 返回某列值之和
AVG()通过对对表中函数行数计数并计算特定列值之和,求该列的平均值, SELECT AVG(prod_price) AS avg_price FROM products’
AVG()也可以用来确定特定列或行的平均值, SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id=1003;
AVG()只能用来确定特定数值列的平均值,而且列命名必须作为函数参数给出,为了获得多格列的平均值,必须使用多个AVG() 函数,AVG()函数忽略值为NULL的行。
COUNT() 函数两种使用方式: 一是使用COUNT(*)对行的数目进行计数,不管表列中包含的是空值(NULL)还是非空值,二是使用COUNT(column)对特定列中具有值的行进行计数,忽略NULL值。
NULL值,如果指定列名,则是定列的值为空的行呗COUNT()函数忽略,但如果COUNT()函数中用的是星号(*),则不忽略。
MAX()函数 一般MAX()函数是用来找出最大的数值或日期,丹MySQL允许将它用来返回任意列中的最大值,包括返回文本列中的最大值,在用于文本数据时,如果数据按相应的列排序,则MAX()返回最后一行,MAX()函数忽略列值为NULL的行。MIN()函数同理。
聚集不同值: MySQL5 及后期版本,一下在MySQL4.x中不能正常运行。
以上5个聚集函数都可以如下使用,对所有行执行计算,指定ALL参数或不给参数(因为ALL是默认行为),只包含不同的值,指定DISTINCT函数。ALL参数不需要指定。
下面使用AVG()函数返回特定供应商提供的产品的平均价格。使用DISTINCT参数后,平均值考虑各个不同的价格; SELECT AVG(DISTINCT prod_price)AS avg_price FROM products WHERE vend_id=1003;
注意,如果指定列名,则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*),因此不允许使用COUNT(DISTINCT), 否则会产生错误,类似的DISTINCT必须使用列名,不能用于计算或表达式。
将DISTINCT用于 MIN()和MAX() 虽然DISTINCT 从技术上可以用于MIN()和MAX(),但这样做实际上没有价值,一个列中的最小值和最大值不管是否包含不同值都是相同的。
组合聚集函数: SELECT COUNT(*) as num_items,MIN(prod_price) AS price_min,MAX(prod_price) AS price_max, AVG(prod_price) AS price_avg FROM products; 这里单条的SELECT语句执行了4个聚集计算,返回 products表中的物品数目,产品价格最高、最低和平均值。
分组函数:
创建分组:按照分组计数 SELECT vend_id,COUNT(*) AS num_prods FROM products GROUP BY vend_id; 按照vend_id 排序并分组数据。
在具体使用GROUP BY 子句前,需要知道一些规定: GROUP BY 子句可以包含任意数目的列,这使得能对分组进行联套,为数据分组提供更细致的空值。 如果在GROUP BY 子句中嵌套了分组,数据将在最后规定的分组上进行汇总,换句话说,在建立分组时,指定的所有列都一起计算(所以不能从个别的列取回数据)。 GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数)。如果SELECT中使用表达式,则必须在GROUP BY 子句中指定相同的表达式,不能使用别名。 除了聚集计算语句之外,SELECT语句中每个列都必须在GROUP BY 子句中给出。 如果分组列中具有NULL值,则NULL 值将作为一个分组返回。如果列中有多行NULL值,他们将分为一组。 GROUP BY 子句必须要在出现WHERE 子句之后,ORDER BY 子句之前。
过滤分组: HAVING子句,HAVING类似于WHERE ,所有的WHERE 都可以用HAVING来代替,差别是WHERE 过滤行,HAVING过滤分组。
列出具有2个以上、价格为10 以上的产品的供应商: SELECT vend_id,COUNT(*) AS num_prods FROM products WHERE prod_price>=10 GROUP BY vend_id HAVING COUNT(*0) >=2;
分组与排序: GROUP BY 和 ORDER BY ,
ORDER BY |
GROUP BY |
排序产生的输出 |
分组行,但输出可能不是分组的顺序 |
任意列都可以使用(甚至非选择的列也可以使用) |
只可能使用选择列或表达式列,而且必须使用每个选择列表达式 |
不一定需要 |
如果与聚集函数一起使用列(或表达式),则必须使用 |
一般在使用GROUP BY 子句的同事,应该也给出ORDER BY 子句,这是保证数据正确排序的唯一方法。
检索总计订单价格大于60的订单号和总计订单价格,排序给出: SELECT order num,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM (quantity*item_price)>=60 ORDER BY ordertotal;
子查询(需要单独再看):应该学会嵌套,1检索出包含TNT2的所有订单的编号SELECT order_num FROM orderitems WHERE prod_id=’TNT2’; 2检索出具有订单2005和2007的客户ID SELECT cust_id FROM orders WHERE order_num IN(2005,2007); 3把两个组合嵌套起来 SELECT cust_id FROM orders WHERE order_num IN(SELECT order_num FROM orderitems WHERE prod_id=’TNT2’); 子查询总是从内向外处理,先执行括号里面的
在WHERE子句中使用子查询,应该保证SELECT语句具有与WHERE子句中相同数据的列,通常,子查询将返回单个列并且与单个列匹配,丹如果需要也可以使用多个列,子查询一般与IN操作符结合使用,但也可以用于测试等于(=)、不等于(<>)等。
作为计算字段使用子查询: 使用SELECT COUNT(*)对表中的行进行计数,并且通过提供一条WHERE 子句来过滤某个特定的客户ID。可仅对该客户的订单进行计数,对客户1001的订单进行计数 SELECT COUNT(*) AS orders FROM cust_id=1001; 为了对每个客户执行COUNT(*)计算,应该将COUNT(*)作为一个子查询 SELECT cust_name,cust_state,(SELECT COUNT(*) FROM orders WHERE orders ,cust_id=customers,cust_id) AS orders FROM customers ORDER BY cust_name;
联结表:
外键:外键作为某个表中的一列,包含另一个表的主键值,定义了两个表之间的关系
可伸缩性:能够适应不断增加的工作量而不失败,设计良好的数据库或应用程序称之为可伸缩性好。
在引用的列可能出现二义性时,必须使用完全限定列名(用一个点分隔的表名和列名)。如果引用一个没有用表名限制的具有二义性的列名,MySQL将返回错误。
笛卡尔积:由没有联结条件的表关系返回的结果为笛卡尔积,检索出的行的数目试讲第一个表中的行数乘以第二个表中的行数。
内部联结:SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id=products.vend_id;
联结多个表:SQL对一条SELECT语句中可以联络的表的数目没有限制,创建联结的基本规则也相同,首先列出所有表,然后定义表之间的关系,
创建高级联结: 使用表别名,直接在SELECT 后面的表名加AS加新名。 优点是缩短了SQL语句,允许在单条SELECT语句中多次使用相同的表。 注意表别名只在查询执行中使用,与列别名不一样,表别名不返回客户机。
自联结:假如某物品(ID为Apple)存在问题,需要找到Apple制造商的其他产品有没有问题。第一种为使用子查询先找到制造商,再找制造商的产品 SELECT prod_id,prod_name FROM products WHERE vend_id=(SELECT vend_id FROM products WHERE prod_id=’Apple’); 还有一种方法为使用联结 SELECT p1.prod_id,l1.prod_name FROM products AS p1,products AS p2 WHERE p1.vend_id=p2.vend_id AND p2.prod_id=’Apple’; 这个查询中需要的两个表是相同的表,因此products的第一次出现为p1,第二次出现为p2,避免了对products引用的二义性。
自然联结:无论何时对表进行联结,应该至少有一个列出现不止在一个表中(被联结的列),标准的联结返回所有数据,甚至相同的列多次出现,自然联结排除多次出现,且每个列只返回一次。
外部联结:许多联结将一个表中的行与另一个表中的行相关联,但有时候需要包含没有关联的那些行,例如,对每个客户下了多少订单进行计数,包括哪些至今尚未下订单的客户;列出所有产品以及订购数量,包括没有人订购的产品;计算平均销售规模,包括哪些至今没有下订单的客户。 内部联结,找出所有客户以及订单 SELECT customers.cust_id,orders.order_num FROM customers INNER JOIN orders ON customers.cust_id=orders.cust_id; 外部联结方法为 SELECT customers.cust_id,orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id=orders.order_id;
存在两种外部联结的形式,左外部联结和右外部联结,区别是所关联表的顺序不同,左外部联结可通过点到FROM或WHERE子句中表的顺序转换为右外部联结。
使用带聚集函数的联结: 检索出所有客户以及每个客户所下的订单数, SELECT customers.cust_name,customers.cust_id, COUNT(orders,order_num) AS num_ord FROM customers INNER JOIN orders ON customers.cust_id=orders.cust_id GROUP BY customers.cust_id;
使用联结和联结条件,注意所知用的联结类型,一般我们使用内部联结,但是应外部联结也是有效的;保证使用正确的联结条件,否则将返回不正确的数据;应该总是提供联结条件,否则会得出笛卡尔积;在一个联结中可以包含多个表,甚至对于每个联结可以采用不同的联结类型,这样是合法的,但是在一起测试他们之前,应该分贝测试每个联结,有利于排除故障。
组合查询:MySQL允许执行多个查询(多个SELECT语句),并将结果作为单个查询结果返回,这些组合查询通常称为并(union)或复合查询(compound query),两种情况需要使用组合查询: 在单个查询中从不同的表返回类似结构的数据;对于单个表执行多个查询,按单个查询返回数据。。
创建组合查询: 使用UNION操作符组合查询
搜索价格小于等于5的所有物品和包括供应商1001和1002生产的所有产品 SELECT vend_id,prod_id, prod_price FROM products WHERE prod_price>=5 UNION SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001,1002);
UNION 规则: UNION必须由两条或两条以上的SELECT语句组成,语句之间用关键字UNION分隔;UNION每个查询必须包含相同的列、表达式或聚集函数(不过各个列不需要以相同的次序列出); 列数据类型必须兼容,类型不必完全相同,但必须是DBMA可以隐含地转换的类型(例如,不同的数值类型或不同的日期类型)
包含或取消重复的行:UNION从查询结果集中去除了重复的行,和单条的SELECT语句中使用多个WHERE子句条件一样。如果想要返回所有匹配航,可使用UNION ALL.
最组合查询结果排序: ORDER BY 排序,在最后一条SELECT语句之后使用,不存在用一种方式排序一部分,而后又用另一种方式排序另一部分。
理解全文本搜索: 不是所有的引擎都支持全文本搜索,连个最常使用的引擎为MyISAM和InnoDB,前者支持全文搜索,后者不支持。
为了进行全文搜索,必须索引被搜索的列,而且要随着数据的改变不断地改变列,在对表列进行适当设计后,MySQL会自动进行所有的索引和重新索引,在索引之后,SELECT 可与Match()和Against()一起使用以实际执行搜索。
启用全文本搜索支持
进行全文本搜索: 在索引之后,使用两个函数Match()和Against()执行文本搜索,其中Match()指定被搜索的列,Against()指定要使用的搜索表达式。
使用完整的Match()说明,传递给Match()的值必须与FULLTEXT()定义中的相同,如果指定多个列,则必须列出他们(而且次序正确)
搜索不区分大小写,除非使用BINARY方式,否则全文本搜索不区分大小写。
使用查询拓展
布尔文本搜索
全文本搜索的使用说明
插入数据:INSERT INTO... VALUES 插入方式可以有插入完整的行,插入行的一部分,插入多行,插入某些查询的结果
更新和删除数据:UPDATE... SET 加需要改变的行,UPDATE 语句以需要更新表的名字开始,SET命令用来将心智赋予被更新的列,以WHERE子句结束。 在更新多个列时,只需要单个SET命令,每个“列=值”对之间用逗号分隔(最后一列之后不用逗号)。
IGNORE关键字 如果用UPDATE更新多个行、并且再更新这些行中的一行或多行出现错误,则整个UPDATE操作取消(错误发生前更新的所有航被恢复到他们原来的值),为了即使是发生错误,也继续进行更新,可以使用IGNORE关键字, UODTAE IGNORE customers...
为了删除某个列的值,可以设置他为NULL(加入定义允许NULL值) UPDATE customers SET cust_email=NULL WHERE cust_id=1005; 其中NULL用来去除cust_email列中的值。
删除数据: DELETE语句,使用DELETE时不要省略WHERE子句 DELETE FROM customers WHERE cust_id=1005;
DELETE 不需要列名或通配符,DELETE删除整行而不是删除列,为了删除指定的列,需要使用UPDATE语句。 DELETE 是从表中删除行,但是不是删除表的本身,如果想从表中删除所有航,不要使用DELETE,可以使用TRUNCATE TABLE 语句,他完成相同的工作,但速度更快(TRUNCATE实际上是删除原来的表并创建一个新的表,而不是删除表中的数据)
MySQL没有撤销按钮,小心使用UPDATE 和DELETE。
创建和操纵表: 表的创建一般有两种方法: 一是使用交互式创建和管理表的工具,而是直接用MySQL语句CREATE TABLE进行创建。在使用交互式工具时,实际上使用的是MySQL语句,但是这些语句不是用户编写的,界面工具会自动生成并执行相应的MySQL语句(更新现有表时也是这样)
表创建基础: 在使用CREATE TABLE 创建新表时,必须给出以下信息,一是新表的名字,在关键字CREATE TABLE之后给出,二是表列的名字和定义,用逗号分隔。在创建新表时,指定的表名必须不存在,否则将出错,如果要防止意外覆盖已有的表,SQL首先手工删除该表,然后再重建他,而不是简单的用创建表语句覆盖他。
如果想在一个表不存在时创建它,应该在表名后给出IF NOT EXISTS。这样做不检查已有表的模式相匹配,他指示查看表名是否存在,并仅在表名不存在时创建它。
使用NULL值,再更新或者插入行时,该列必须有值,如果试图插入没有值的列,将返回错误并且插入失败。
NULL和空串不一样,NULL是没有值,不是空串,如果指定’ ‘(两个单引号,其间没有字符),这在NOYT NULL 列中是允许的,空串是一个有效的值,不是无值,NULL值用关键字NULL而不是空串指定。
主键再介绍: 主键值必须唯一,表中的每个行必须由唯一的主键值,如果主键使用单个列,那么他的值必须唯一,如果主键只用多个列,那么这些列的组合值必须唯一。主键中只能使用不允许NULL值的列,允许NULL值的列不能作为唯一标识。
AUTO_INCREMENT: 每执行一次INSERT操作(增加一行时),MySQL自动对该列增量,给该列赋予下一个可用的值,这样给每个行分配一个而为一的cust_id。
指定默认值: DEFAULT关键字,在插入行时没有给出值。MySQL允许指定此时使用的默认值。与大多数DBMS不同,MySQL不允许使用函数作为默认值,他只支持常量。
引擎类型:与其他DBMS一样,MySQL有一个具体管理和处理数据的内部引擎,,但MySQL与其他DBMS不一样,它具有多种引擎,他打包多个引擎,这些引擎都隐藏在MySQL服务器内,全部都能执行CREAT TABLE 和SELECT 等命令。
几个需要知道的引擎: InnoDB是一个可靠的事务处理引擎,他不支持全文本搜索; MEMORY在功能等同于MyISAM,但由于数据存储在内存(不是磁盘)中,速度很快(特别适用于临时表);MyISAM是一个性能极高的引擎,支持全文本搜索,但不支持失误处理。
引擎类型可以混用,混用引擎类型有一个缺陷,外键(用于强制实施引用完整性)不能扩引擎,即使用一个个引擎的表不能引用其他具有不使用引擎的表的外键。
更新表: ALTER TABLE 语句, 加列ALTER TABLE vendors ADD vend_phone CHAR(20); 删除刚刚增加的列 ALTER TABLE vendors DROP COLUMN vend_phone; ALTER TABLE 一种常见的用于是定义外键 ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems orders FOREIGN KEY (order_num) REFERENCES orsers (order_num);
复杂的表结构更改一般需要手动删除过程,涉及以下步骤: 用新的列布局创建一个新表,使用INSERT SELECT语句,从旧表复制数据到新表,如果有必要,可使用转换函数和计算字段,检验包含所需数据的新表,重命名旧表(如果确定,可以删除它),用就表原来的名字重命名新表,根据需要,重新创建触发器、储存过程、索引和外键、
使用ALTER TABLE 时应该在进行改动前做一个完整的备份(模式和数据的备份),数据库表的更改不能撤销,如果增加了不需要的列,可能不能删除它们,类似的 ,如果删除了不该删除的列,也无法恢复。
删除表: DROP TABLE 删除的是整个表而不是其内容
重命名表: RENAME TABLE
使用视图: 视图是虚拟的表,与包含数据的表不一样,视图是包含使用时动态检索数据的查询。
视图的一些常见应用: 重用SQL语句,简化复杂的SQL操作,在编写后,可以方便的重用他而不必知道他的基本查询细节;使用表的组成部分而不是整个表;保护数据,可以给用户授予表的特定部分的访问权限而不是整个表的访问权限;更改数据格式和表示,视图可返回与底层表的表示和格式不同的数据。
视图本身不包含数据,每次使用视图时,都必须处理查询执行时所需的任一搜索,在部署使用了大量视图的应用前,应该进行测试。
视图的规则与限制: 与表一样,视图必须唯一命名(不能给视图取与别的视图或表相同的名字);对于可以创建的视图数目没有限制;为了创建视图,必须具有足够的访问权限,这些限制通常由数据库管理人员授予;视图可以嵌套,可以利用从其他视图中检索数据的查询来构造一个视图; ORDER BY 可以用在视图中,但如果从该视图检索数据SELECT 中也含有ORDER BY, 那么该视图中的ORDER BY 将被覆盖;视图不能索引,也不能有关联的触发器或默认值;视图可以和表一起使用,例如可以编写一条联结表和视图的SELECT 语句。
使用视图: 视图用CREATE VIEW语句来创建; 使用SHOW CREATE VIEW viewname;来查看创建视图的语句;用DROP删除视图,其语法DROP VIEW viewname; 更新视图时,可以先用DROP再用CREATE,也可以直接用CREATE OR REPLACE VIEW ,如果要更新的视图不存在,则第二条更新语句会创建一个视图,如果要更新的视图存在,则第二条语句会替换原有视图。
利用视图简化复杂的联结
用视图重新格式化检索出的数据
用视图过滤出不想要的数据
WHERE子句和WHERE子句: 如果从视图检索数据时使用了一条WHERE子句,则两组子句(一组在视图中,另一组是传递给视图的)将自动组合。
使用视图与计算字段
更新视图:通常视图的数据是可以更新的(即对他们使用INSERT,UPDATE和DELETE)。更新一个视图将更新其表(视图本身没有数据),如果你对视图增加或删除行,实际上是对其基表增加或删除行。
但是,不是所有的视图都是可以更新的,如果MySQL不能正确的确定被更新的基数据,则不允许更新(包括插入和删除)。这意味着,如果视图定义中有以下操作,则不能进行视图的更新。
分组(使用GROUP BY 和HAVING); 联结; 子查询; 并; 聚集函数(MIN()、COUNT()、SUM()等);DISTINCT; 导出(计算)列。
一般视图都是用于检索而不是用于更新。
使用存储过程:
为什么使用存储过程:通过把处理封装在容易使用的单元中,简化复杂的操作; 由于不要求反复建立一系列处理步骤,这保证数据的完整性。如果所有开发人员和应用程序都是用同一(试验和测试)存储过程,则所使用的代码都是相同的,可以防止错误,需要执行的步骤越多,出错的可能就越大,防止错误保证了数据的一致性; 简化对变动的管理,如果表名、列名或业务逻辑(或别的内容)有变化,只需要更改存储过程的代价,使用它的人员甚至不知道这些变化; 提高性能,因为使用存储过程比使用单独的SQL语句要快; 存在一些职能用在单个请求中的MySQL元素和特性,存储过程可以使用他们来编写功能更强更灵活的代码;你可能没有创建存储过程的安全访问权限,许多数据库管理员限制存储过程的创建权限,允许用户使用存储过程,但不允许他们创建过程。
使用存储过程:
执行存储过程,MySQL称存储过程的执行为调用,因此MySQL执行存储过程的语句为CALL。CALL接受存储过程的名字以及需要传递给它的任意参数。CALL productpricing(@pricelow, @pricehigh,@priceaverage); 其中,执行名为productpricing的存储过程,它计算并返回产品的最低、最高和平均价格。 存储过程可以显示结果,也可以不显示结果。
创建存储过程,返回产品平均价格的存储过程。 CERETE PROCEDURE productpricing() BEGIN SELECT Avg(prod_price) AS priceaverage FROM products; END; 此存储过程名为productpricing,用CREATE PROCEDURE productpricing()语句定义。如果存储过程接受参数,他们将在()中列举出来。此存储过程没有参数,但后跟的()仍然需要。BEGIN和END语句用来限定存储过程体,过程体本身仅是一个简单的SELECT语句。
在MySQL处理这段代码时,他创建一个新的存储过程productpricing。没有返回数据,因为这段代码并未调用存储过程,这里只是为以后实用文创建它。
删除存储过程,在存储过程创建之后,被保存在服务器上以供使用,直至被删除。删除命令从服务器中删除存储过程。未删除刚创建的存储过程,可使用一下语句:DROP PROCEDURE productrpricing; 这条语句删除刚创建的存储过程。注意没有使用横剖面的(),只给出存储过程名。
仅当存在时删除,如果一个指定的过程不存在,则DROP PROCEDURE 将产生一个错误,当过程存在想删除它时(如果过程出不存在也不产生错误)可使用DROP PROCEDURE IF EXIETS.
使用参数,productpricing只是一个简单的存储过程,他简单的显示SELECT语句的结果。一般,存储过程并不显示结果,而是把结果返回给你指定的变量。
变量内存中一个特定的位置,用来临时存储数据。以下是productpricing 的修改版本(如果不先删除此存储过程,则不能再次创建它);
CREATE PROCEDURE productpricing( OUT pl DECIMAL(8,2), OUT ph DECIMAL(8,2), OUT pa DECIMAL(8,2)) BEGIN SELECT Min (prod_price) INTO pl FROM products; SELECT Max(prod_price) INTO ph FROM products; SELECT Avg(prod_price) INTO pa FROM products; END;
此存储过程接受3个参数;pl存储产品最低价格,ph存储产品最高价格,pa存储产品平均价格。每个参数必须具有指定的类型,这里使用十进制值。关键字OUT制定相应的参数用来从存储过程传出一个值(返回给调用者)。MySQL支持IN(传递给存储过程)、OUT(从存储过程中传出)INOUT(对存储过程传入和传出)类型的参数。存储过程的代码位于BEGIN和END语句内,如前所见,他们是一系列SELECT语句,用来检索值,然后保存到相应的变量(通过指定INTO关键字)