MySQL必知必会2 淩亂°似流年 2023-08-17 16:17 77阅读 0赞 **使用数据处理函数** -------------------- **函数** 与其他大多数计算机语言一样,SQL支持利用函数来处理数据。函数一般是在数据上执行的,他给数据的转换和处理提供了方便,在前一章中用来去掉尾空格的RTrim()就是一个函数的例子 **文本处理函数** **输入:** SELECT vend\_name,Upper(vend\_name) AS vend\_name\_upcase FROM vendors ORDER BY vend\_name; **分析:** 将列 vend\_name\_upcase转换为大写 **示例:** mysql> SELECT NAME,UPPER(NAME) AS name_upcase FROM account ORDER BY NAME; +------+-------------+ | NAME | name_upcase | +------+-------------+ | aaa | AAA | | aest | AEST | | bbb | BBB | | ccc | CCC | | ccc | CCC | | ddd | DDD | | ddd | DDD | | t57L | T57L | | test | TEST | | tsdf | TSDF | +------+-------------+ 常用的文本处理函数 <table> <thead> <tr> <th>函数</th> <th>说明</th> </tr> </thead> <tbody> <tr> <td>Left()</td> <td>返回串左边的字符</td> </tr> <tr> <td>Length()</td> <td>返回串的长度</td> </tr> <tr> <td>Locate()</td> <td>找出串的一个子串</td> </tr> <tr> <td>Lower()</td> <td>将串转为小写</td> </tr> <tr> <td>LTrim()</td> <td>去掉串左边的空格</td> </tr> <tr> <td>Right()</td> <td>返回串右边的字符</td> </tr> <tr> <td>RTrim()</td> <td>去掉串右边的空格</td> </tr> <tr> <td>Soundex()</td> <td>返回串的SOUNDEX的值</td> </tr> <tr> <td>SubString()</td> <td>返回子串的字符</td> </tr> <tr> <td>Upper()</td> <td>将串转换为大写</td> </tr> </tbody> </table> **日期和时间处理函数** 日期和时间采用相应的数据类型和特殊的格式存储,以便于能快速和有效的排序或过滤,并且节省物理存储空间 **常用日期和时间处理函数** <table> <thead> <tr> <th>函数</th> <th>说明</th> </tr> </thead> <tbody> <tr> <td>AddDate()</td> <td>增加一个日期(天、周等)</td> </tr> <tr> <td>AddTime()</td> <td>增加一个时间(时、分等)</td> </tr> <tr> <td>CurDate()</td> <td>返回当前日期</td> </tr> <tr> <td>CurTime()</td> <td>返回当前时间</td> </tr> <tr> <td>Date()</td> <td>返回日期时间的日期部分</td> </tr> <tr> <td>DateDiff()</td> <td>计算两个日期之差</td> </tr> <tr> <td>Date_Add()</td> <td>高度灵活的日期运算函数</td> </tr> <tr> <td>Date_Format()</td> <td>返回一个格式化的日期或时间串</td> </tr> <tr> <td>Day()</td> <td>返回一个日期的天数部分</td> </tr> <tr> <td>DayOfWeek()</td> <td>对于一个日期、返回对应的星期几</td> </tr> <tr> <td>Hour()</td> <td>返回一个时间的小时部分</td> </tr> <tr> <td>Minute()</td> <td>返回一个时间的分钟部分</td> </tr> <tr> <td>Month()</td> <td>返回一个日期的月份部分</td> </tr> <tr> <td>Now()</td> <td>返回当前日期和时间</td> </tr> <tr> <td>Second()</td> <td>返回一个时间的秒部分</td> </tr> <tr> <td>Time()</td> <td>返回一个日期时间的时间部分</td> </tr> <tr> <td>Year()</td> <td>返回一个日期的年份部分</td> </tr> </tbody> </table> **汇总数据** -------------------- **聚集函数** 运行在行组上,计算和返回单个值的函数 **AVG函数** **输入:** SELECT AVG(prod\_price) AS avg\_price FROM products; **分析:** 此SELECT语句返回值avg\_price,它包含products表中所有产品的平均价格 **只用于单个列** AVG()只能用来确定特定数值的平均值,而且列名必须作为函数参数给出 **NULL值** AVG()函数忽略列值的NULL的行 **COUNT函数** **输入** SELECT COUNT(\*) AS num\_cust FROM customers; **分析** 利用COUNT对所有行计数,不管行中各列有什么值。包含NULL值 **输入** SELECT COUNT(cust\_email) AS num\_cust FROM customers; **分析** 使用COUNT对cust\_email列中有值的行进行计数,不包含NULL值 **聚集不同的值** **输入** SELECT AVG(DISTINCT prod\_price) AS avg\_price FROM products WHERE vend\_id = 1003; **分析** 排除掉相同的prod\_price的值 -------------------- **分组数据** -------------------- **创建分组** **输入** SELECT vend\_id,COUNT(\*) AS num\_prods FROM proucts GROUP BY vend\_id; **分析** 上面的SELECT语句指定了两个列,vend\_id包含产品供应商的ID,num\_prods为计算字段。GROUP BY 子句指示MySQL按vendid排序并分组数据。 **过滤分组** **输入:** SELECT cust_id,COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2; **分析** HAVING子句,他过滤 count(\*)>=2的那些分组 HAVING和WHERE的差别:HAVING用于分组后过滤,WHERE用于分组前过滤 **分组和排序** **输入** SELECT order_num,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >=50 ORDER BY ordertotal; **输出** mysql> SELECT order_num,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >=50 ORDER BY ordertotal; +-----------+------------+ | order_num | ordertotal | +-----------+------------+ | 20006 | 55.00 | | 20008 | 125.00 | | 20005 | 149.87 | | 20007 | 1000.00 | +-----------+------------+ **使用子查询** **利用子查询进行过滤** **输入** SELECT cust_name,cust_contact FROM customers WHERE cust_id IN(SELECT cust_id FROM orders WHERE order_num IN(SELECT order_num FROM orderitems WHERE prod_id = 'TNT2' ) ); **输出** +----------------+--------------+ | cust_name | cust_contact | +----------------+--------------+ | Coyote Inc. | Y Lee | | Yosemite Place | Y Sam | +----------------+--------------+ **分析** 为了执行上述SELECT语句;MySQL实际上必须执行3条SELECT语句,最里边的子查询返回订单号列表,此列表用于其外面的子查询的WHERE子句。外面的子查询返回客户ID列表,此客户ID列表用于最外层的WHERE子句,最外层的查询确实返回所需的数据 **连接表** **外键** 外键为某个表中的一列,他包含另一个表的主键值 **创建联结** **输入** SELECT vend_name,prod_name,prod_price FROM vendors,products WHERE vendors.vend_id=products.vend_id ORDER BY vend_name,prod_name; **分析** 最大的差别是在于所指定的两个列(prod\_name和prod\_price)在一个表中,二另一个列(vend\_name)在另一个表中 现在来看FROM子句。与以前的SELECT语句不一样,这条语句的FROM子句列出了两个表,分别是vendors表和products表。他们就是这条SELECT语句联结的两个表的名字。这两个表用WHERE子句正确联结,WHER子句指示MySQL匹配vendors表中的vend\_id和products表中的vend\_id \*\*内部联结\* **输入** SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id=products.vend_id; **分析** 此语句中的SELECT域前面的SELECT语句相同,但FROM子句不同,这里,两个表之间的关系是FROM子句的组成部分,以INNER JOIN指定,在使用这种语法时,联结条件用特定的ON子句而不是WHERE子句给出 **创建高级联结** **使用表别名** **输入** SELECT cust_name,cust_contact FROM customers AS c,orders AS o,orderitems AS oi WHERE c.cust_id=o.cust_id AND oi.order_num=o.order_num AND prod_id='TNT2'; **自联结** **输入** SELECT p1.prod_id,p1.prod_name FROM products AS p1,products AS p2 WHERE p1.vend_id=p2.vend_id AND p2.prod_id='DTNTR' **自然联结** 无论何时对表进行联结,应该至少有一个列出现在不止一个表中(被联结的列)。标准的联结返回所有数据,甚至相同的列多次出现。自然联结排除多次出现,使每个列只返回一次 **输入** SELECT c.*,o.order_num,o.order_date, oi.prod_id,oi.quantity,oi.item_price FROM customers AS c,orders AS o,orderitems AS OI WHERE c.cust_id=o.cust_id AND oi.order_num=o.order_num AND prod_id='FB'; **外部联结** **输入** SELECT customers.cust_id,orders.order_num FORM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id; -------------------- **组合查询** -------------------- 多数SQL查询都只包含从一个或多个表中返回数据的单条SELECT语句。MySQL也允许执行多个查询(多条SELECT语句),并将结果作为单个查询结果集返回 **创建组合查询** **使用UNION** UNION的使用很简单。所需做的只是给出每条SELECT语句,在各条语句之间加上关键字UNION **输入** 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指示MySQL执行两条语句,并把输出组合成单个查询结果集 -------------------- **全文本搜索** -------------------- 两个最常用的引擎为MyISAM和InnoDB,前者支持全文本搜索,后者不支持 使用全文本搜索,必须索引被搜索的列,而且要随着数据的改变不断的重新索引 在对表列进行适当的设计后,MySQL会自动进行所有索引和重新索引 在索引之后,SELECT可与Match()和Against()一起使用以实际执行搜索 **启用全文本搜索支持** 一般在创建时启用全文本搜索,CREATE TABLE语句接收FULLTEXT子句,他给出被索引的一个逗号分隔的列表。 创建表 CREATE TABLE productnotes( note_id INT NOT NULL AUTO_INCREMENT, prod_id CHAR(10) NOT NULL, note_date DATETIME NOT NULL, note_text TEXT NULL, PRIMARY KEY(note_id), FULLTEXT(note_text) )ENGINE=MYISAM; **分析** 这些列中有一个名为note\_text的列,为了进行全文本搜索,MySQL根据子句FULLTEXT(note\_text)的指示对他进行索引。这里的FULLTEXT索引单个列,如果需要也可以指定多个列 在定义之后MySQL自动维护该索引,在增加、更新、或删除行时,索引随之自动更新 **进行全文本搜索** 在索引之后,使用两个函数Match()和Against()执行全文本搜索,其中Match()指定被搜索的列,Against()指定要使用的搜索表达式 **输入** SELECT note_text FROM productnotes WHERE Match(note_text) Against("rabbit"); **分析** 此SELECT语句检索单个列note\_text。由于WHERE子句,一个全文本搜索被执行。Match(note\_text)指示MySQL针对指定的列进行搜索,Against('rabbit')指定词rabbit作文搜索文本。 **使用查询扩展** 查询扩展用来设法放宽所返回的全文本搜索结果的范围,考虑下面的情况。你想找出所有提到anvils的注释。只有一个注释包含词anvils,但你还想找出可能与你的搜索有关的其他行,即使他们不包含词anvils 这也是扩展的一项任务,在使用查询扩展时,MySQL对数据和索引进行两遍扫描来完成搜索: 首先,进行一个基于全文本的搜索,找出与搜索条件匹配的所有行 其次,MySQL检查这些匹配并选择所有有用的词 在其次,MySQL再次进行全文搜索,这次不仅使用原来的条件,而且还使用所有有用的词 利用查询扩展,能找出可能相关的结果,即使他们并不精确包含所查找的词 **使用查询扩展** SELECT note_text FROM productnotes WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION); **布尔文本搜索** MySQL支持全文本搜索的另一种形式,称为布尔方式,即使没有全文本搜索也可以使用,但这是一种非常缓慢的操作 **输入** SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE); **分析** 此全文本搜索检索包含词heavy的所有行,其中使用了关键字IN BOOLEAN MODE,但实际上没有指定布尔操作符,因此,其结果与没有指定布尔方式的结果相同 为了匹配包含heavy但不包含任意以rope开始的词的行可以使用以下查询 **输入** SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE); **全文本布尔操作符** <table> <thead> <tr> <th>布尔操作符</th> <th>说明</th> </tr> </thead> <tbody> <tr> <td>+</td> <td>包含,词必须存在</td> </tr> <tr> <td>-</td> <td>排除,词必须不出现</td> </tr> <tr> <td>></td> <td>包含,而且增加等级值</td> </tr> <tr> <td><</td> <td>包含,且减少等级值</td> </tr> <tr> <td>()</td> <td>把词组成子表达式(允许这些子表达式作为一个组被包含、排除、排列等)</td> </tr> <tr> <td>~</td> <td>取消一个词的排序值</td> </tr> <tr> <td>*</td> <td>词尾的通配符</td> </tr> <tr> <td>""</td> <td>定义一个短语(与单个词的列表不一样,他匹配整个短语以便包含或排除这个短语)</td> </tr> </tbody> </table> -------------------- **插入数据** -------------------- **插入完整的行** **输入** INSERT INTO customers(cust_name, cust_contact, cust_email, cust_address, cust_city, cust_state )VALUES('pEP e.lAPew', NULL, NULL, '100 Main Street', 'los Angeles', 'CA' ) **插入多个行** INSERT INTO customers( cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country )VALUES( 'Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA' ), ( 'M.Martian', '42 Galaxy Way', 'New Your', 'NY', '11213', 'USA' ); -------------------- **更新和删除数据** -------------------- **输入** UPDATE customers SET cust_email='elmer@fudd.com' WHERE cust_id=10005; **更新多个列** UPDATE customers SET cust_name='The Fudds', cust_email='elmer@fudd.com' WHERE cust_id=10005; **删除数据** DELETE FROM customers WHERE cust_id = 10006; 转载于:https://www.cnblogs.com/train99999/p/11241504.html
还没有评论,来说两句吧...