18.拼接字段
SELECT Concat(vender_name,’(‘,vender_country,’)’) FROM venders ORDER BY vender_name;
注：多数DBMS使用+或者||来实现拼接，MYSQL则使用Concat()函数来实现。
19.删除空格 RTrim() LTrim()
SELECT Concat(Trim(vender_name),’(‘,Trim(vender_country),’)’) FROM venders ORDER BY vender_name;
20.使用别名
SELECT Concat(Trim(vender_name),’(‘,Trim(vender_country),’)’) AS vender_title FROM venders ORDER BY vender_name;
21.执行算术计算
SELECT prod_id,quantity,item_price,quantity,item_price,quantity*item_price AS expanded_price FROM order items WHERE order_num = 20005;
注：如何测试计算：
SELECT 可以通过省略FROM子句以便简单的访问和处理表达式。例如：SELECT 3*2;将返回6；可以利用这点进行测试计算。
22.文本处理函数：
SELECT vender_name,Upper(vender_name) AS vender_name_upcase FROM venders ORDRER BY vender_name;
注意：常用的文本处理函数：
Left():返回串左边的字符；
Length():返回串的长度；
Locate():找出串的一个子串；
Lower():将串转换为小写；
LTrim():去掉串左边的空格；
Right():返回串右边的字符；
RTrim():去掉串右边的空格；
Soundex():返回串的SOUNDEX值； //匹配所有发音类似的
SubString():返回子串的字符；
Upper()：将串转换成大写；
23.日期和时间处理函数：
注意：Mysql中使用日期格式，无论什么时候指定一个日期，不算是插入或者更新表值还是用WHERE子句过滤，日期必须为格式yyyy-mm-dd.
SELECT cust_id,order_num FROM orders WHERE Date(order_date) = ‘2015-09-01’;
注意：WHERE order_date = ‘2015-09-01’靠谱么？答案是不，因为实际中很可能时间存储为’2015-09-01 11:30:05’,使用这样的办法将无法匹配到数据。解决的办法，就是使用Date(order_date),指示Mysql仅提取列的日期部分。
常用的日期和时间处理函数：
AddDate():增加一个日期（天，周等)；
AddTime():增加一个时间（时，分等)；
CurDate():返回当前日期；
CurTime():返回当前时间；
Date():返回日期时间的日期部分；
DateDiff():计算两个日期之差；
Date_Add():高度灵活的日期运算函数；
Date_Format():返回一个格式化的日期或时间串；
Day():返回一个日期的天数部分；
DayOfWeek():对于一个时间，返回对应的星期几；
Hour()/Minute()/Month()/Second()/Year()：返回一个时间的小时/分钟/月份/秒/年份部分；
Now()：返回当前时间和日期；
24.数值处理函数：
注意：常用的数值处理函数：
Abs():绝对值；
Cos():角度的余弦；
Exp()：指数值；
Mod():余数;
Pi():圆周率；
Rand()：随机数;
Sin()：正弦:
Sqrt()：平方根；
Tan()：正切；
25.聚集函数：
1>.AVG()
SELECT AVG(prod_price) AS avg_price FROM products;
注意：AVG()函数忽略列值为null的行。
2>.COUNT()
SELECT COUNT(*) AS num_cust FROM customers;
SELECT COUNT(cust_email) AS num_cust FROM customers;
注意：使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是null还是非null。使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。
3>.MAX()
SELECT MAX(prod_price) AS max_price FROM products;
注意：对非数值使用MAX()，如果数据按相应的列进行排序，则MAX（）返回最后一行。
4.MIN()
SELECT MIN(Prod_price) AS min_price FROM products;
注意：对非数值使用MAX()，如果数据按相应的列进行排序，则MAX（）返回最前面一行。
5.SUM()
SELECT SUM(quanytity) AS item_ordered FROM order items WHERE order_num = 20005;
26.分组
注意：GROUP BY子句指示MYSQL分组数据，然后对每个组而不是整个结果集进行聚集。在具体使用GROUP BY子句前，需要知道一些的规定：
1.GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
2.如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
3.GROUP BY子句中列出的每一个列都必须是检索列或者是有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
4.除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。
5.如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多个NULL，他们将分为一组。
6.GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。
7.使用WITH ROLLUP可以得到每个分组的汇总的值。
SELECT vender_id,COUNT(*) AS num_prods FROM products GROUP BY vend_id;
27.过滤分组
SELECT cust_id,COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >=2;
注意：WHERE在分组前进行过滤，HAVING在数据分组后进行过滤。
SELECT vend_id,COUNT(*) AS num_prods FROM products WHERE prod_price >=10 GOUP BY vend_id HAVINGS COUNT(*) >=2;
注意：先过滤后分组。
28.分组和排序
注意：ORDER BY是排序产生的输出。任意列都可以使用（甚至非选择的列也可以使用）。不一定需要。GROUP BY是分组行，但输出可能不是分组的顺序。只可能使用选择列或表达式列，而且必须选择每个选择列表达式。如果和聚集函数一起使用列（或者表达式），则必须使用。一般在使用GROUP BY子句时，应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法。千万不要仅仅依赖GROUP BY排序数据。
SELECT order_num,SUM(quantity*item_price) AS ordertotal FROM order items GROUP BY order_num HAVING SUM(quantity*item_price) >=50 ORDER BY ordertotal;
29.使用子查询
SELECT cust_id FROM orders WHERE order_num IN ( SELECT order_num FROM orderitems WHERE prod_id = ’TNT2’);
注意：子查询总是从内到外处理。对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。
SELECT cust_name,cust_state,(SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name;
30.联接表：等值联接/内部联接
SELECT vend_name.prod_name,prod_price FROM venders, products WHERE venders.vend_id = products.vend_id ORDER BY vend_name,prod_name;
注意：在一条SELECT语句中联接几个表时，相应的关系是在运行时构造的。在联结两个表时，实际上是将第一个表中的每一行和第二个表中的每一行配对。WHERE子句作为过滤条件，它只包含那些匹配给定条件（这里是联结条件）的行。没有WHERE语句，第一个表中的每一行将与第二个表中的每个行配对，而不管逻辑上是否可以配在一起。由没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目为第一个表中的行数*第二个表中的行数。
SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vender_id = products.vender_id;
31.联结多个表
SELECT prod_name,vender_name,prod_price,quantity FROM orderitems,products,venders WHERE products.vender_id = venders.vend_id AND orderitems.prod_id = products.prod_id AND order_num = 20005;
注意：不要联结不必要的表，联结的表越多，性能下降的越厉害。
32.使用表别名
SELECT cust_name,cust_concat FROM customers AS orders AS o,orderItems AS oi WHERE c.cust_id = o.cust_id AND oi.order_num = o.order_num AND prod_id = ’TNT2’;
33.联接表：自联结
SELECT prod_id,prod_name FROM products WHERE venders IN (SELECT vender_id FROM products WHERE prod_id = ‘DTNTR’;);
=>SELECT p1.prod_id,p1.prod_name FROM products AS p1,products AS p2 WHERE p1.vender_id = p2.vender_id AND p2.prod_id = ‘DTNTR’;
注意：自联结通常作为外部语句用来替换从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多。
34.自然联结
SELECT c.*,o.order_num,o.order_date,oi.prod_id,oi.quantity,oi.item_price FRPM 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’;
35.外部联结
SELECT customers.cust_id,orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;
SELECT sustomers.cust_id,orders.order_num FROM customers RIGHT OUTER JOIN orders ON orders.cust_id = customers.cust_id;
注意：与内部联接关联两个表中的行不同的是，外部联结还包括没有关联的行。一般我们使用内部联接，但是使用外部联结也是可以的。
36.组合查询
SELECT vender_id,prod_id,prod_price FROM products WHERE prod_price <=5
UNION SELECT vend_id,prod_id,prod_price FROM products WHERE vender_id IN (1001,1002);
注意：UNION是把两条SELECT语句的输出结果组合成单个查询结果集。使用UNION可能比使用WHERE子句更为复杂。但对于更复杂的过滤条件，或者从多个表（而不是单个表）中检索数据的情形，使用UNION可能会使处理更简单。使用时，有几点是需要注意的：
1.UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔。
2.UNION中的每个查询必须包含相同的列，表达式或聚集函数（不过各个列不需要以相同的次序列出）。
3.UNION会默认的去掉重复的行，如果不想去掉，想使用所有的行，可以用UNION ALL。
37.全文本搜索
注意：使用全文本搜索，MySQL不需要分别查看每个行，不需要分别分析和处理每个词。Mysql创建指定列的各词的一个索引，搜索可以针对这些词进行。为了进行全文本的搜索，必须索引被搜索的列，而且要随着数据的改变不断的重新索引。在索引之后，SELECT可与MATCH（）和AGAINST()可以一起使用。
一般在创建表时启用全文本搜索。CREATE TABLE语句接受FullText子句
CREATE TABLE productnotes
(
note_id int NOT NULL AUTO_INCREMENT,
prod_id char(10) NOT NULL,
note_text text NULL,
PRIMARY KEY(note_id),
FULLTEXT(note_text)
) ENGINE = MyISAM;
SELECT note_text FROM productnnotes WHERE Match(note_text) Against(‘rabbit’);
注意： 全文本搜索的一个重要的部分就是对结果排序。具有较高等级的行先返回（因为这些行很可能是真正需要的行。）
38.使用查询扩展
SELECT note_text FROM productionotes WHERE Match(note_text) Against (‘anvils’ WITH QUERY EXPANSION);
4.对数据库的插入
1.插入完整的行
INSERT INTO customers(cust_name,cust_address) VALUES (‘’,'’);
注意：1.如果表的定义允许，则可以在INSERT操作中省略某些列。省略列必须满足：该列定义为允许NULL值（无值或者空值）。在表定义中给出默认值。这表示如果不给出值，将使用默认值。2.INSERT 操作很可能很耗时（特别是有很多索引需要更新时），而且它可能降低等待处理的SELECT语句的性能。数据检索是最重要的，我们可以通过在INSERT和INTO之间添加关键字LOW_PRIORITY,只是mysql降低INSERT语句的优先级，INSERT LOW_PROIORITY INTO,也适用于UPDATE和DELETE。
2.插入多个行
INSERT INTO customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country) VALUES(‘Pep E. LaPEw’,’100’,’Los Angeles’,’CA’,’90046’,’USA’),(‘Pep E. LaPEw’,’100’,’Los Angeles’,’CA’,’90046’,’USA’);
注意：这样插入比多个INSERT INTO语句块，提高性能。
3.插入检索出的数据
INSERT INTO customers(cust_id,cust_contact,cust_email) SELECT cust_id,cust_concact,cust_email FROM sustneew;
注意：用INSERT SELECT从其他表中导入行。
5.对数据库的更新和删除
1.更新数据
UPDATE customers SET cust_email = ‘3dsjkds@dsdjsk.com’ WHERE cust_id = 10005;
UPDATE customers SET cust_name = ’The name’ ,cust_email = ‘dsds@123.com’ WHERE cust_id = 10005;
注意：如果用UPDATE更新多行，并且在更新这些行中的一行或者多行时出现一个错误，则整个UPDATE操作被取消（错误发生前更新的所有行被恢复到他们原来的值）。即使是发生了错误，也继续进行更新，可使用IGNORE关键字，UPDATE IGNORE customers…
2.删除数据
DELETE FROM customers WHERE cust_id = 10006;
注意：DELETE删除整行而不是删除列。如果想从表中删除所有行，可使用Truncate table语句，速度更快。Truncate table是删除原来的表并重新创建一个表，而不是逐行删除表里面的数据。
6.创建和操纵表
1.CREATE TABLE productnotes
(
note_id int NOT NULL AUTO_INCREMENT,
prod_id char(10) NOT NULL,
note_text text NULL,
PRIMARY KEY(note_id),
FULLTEXT(note_text)
) ENGINE = MyISAM;
注意：使用AUTO_INCREMENT主键的一个缺点，是不知道这些值都是谁。可使用last_insert_id()函数获得这个值。SELECT last_insert_id(),此语句返回最后一个AUTO_INCREMENT值。
2.更新表
ALTER TABLE venders ADD vend_phone CHAR(20);
ALTER TABLE venders DROP COLUMN vend_phone;
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREGN KEY (order_num) REFERENCES orders (order_num);
3.删除表
DROP TABLE customers2;
4.重命名表
RENAME TABLE customers2 TO customers;
7.视图
常用应用：
1>重用SQL语句。
2>简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节。
3>使用表的组成部分而不是整个表。
4>保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
5>更改数据格式和表示。视图可以返回与底层表的表示和格式不同的数据。
注意：
1>因为视图不包含数据，所以使用视图时，都必须处理查询执行时所需的任一检索。如果用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降的很厉害。
2>视图必须唯一命名。
3>可以创建的视图数目没有限制。
4>创建视图，必须有足够的访问权限。这些限制通常是数据库管理人员授予。
5>ORDER BY可以用在视图中，但如果从该视图检索数据的SELECT语句也含有OEDER BY，那么该视图的ORDE BY将被覆盖。
6>视图不能索引，也不能有关联的触发器或默认值。
7>一般，应该将视图用于检索，而不是用于更新。
语法：
1>CREATE VIEW
2>SHOW CREATE VIEW viewname——>查看创建视图的语句。
3>DROP VIEW viewname;
4>更新视图。可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW.如果要更新的视图不存在，则第二条更新语句会创建一个视图，如果要更新的视图存在，则第2条更新语句会替换原有视图。
5>利用视图简化复杂的联结
CREATE VIEW productcustomers AS SELECT cust_name,cust_concat,prod_id FROM customers, orders,orderitems WHERE costomers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num;
6>用视图重新格式化检索出的数据
CREATE VIEW vendorlocations AS SELECT Concat(RTrim(vend_name),’(‘,RTrim(vender_country),’)’) AS vend_title FROM venders ORDER BY vend_name;
8.存储过程 ：简单 安全 高性能
存储过程，是为以后的使用而保存的一条或者多条Mysql语句的集合。
使用存储过程的理由：
1>.通过把处理封装在容易使用的单元中，简化复杂的操作。
2>由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一（试验和测试）存储过程，则所使用的代码都是相同的。
3>简化对变动的管理。如果表名，列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。
4>提高性能。使用存储过程比使用单独的SQL语句要快。
5>存储过程的编写比SQL复杂，许多数据库管理员限制存储过程的创建权限，允许用户使用存储过程，但不允许创建存储过程。
注：执行名为productpricing的存储过程，计算并返回产品的最低，最高和平均价格。
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage
FROM products;
END;
CALL productpricing();
DROP PROCEDURE productpricing; // DROP PROCEDURE productpricing IF EXISTS;
CREATE PROCEDURE productpricing(
OUT p1 DECIMAL(8,2)；
OUT ph DECIMAL(8,2);
OUT pa DECIMAL(8,2);
)
BEGIN
SELECT Min(prod_price)
INTO p1
FROM products;
SELECT Max(prod_price)
INTO ph
FROM products;
SELECT Avg(prod_price)
INTO pa
FROM products
END;
CALL productpricing(@pricelow,@pricehigh,@priceaverage);
SELECT @priceaverage;
9.使用游标
游标是一个存储在Mysql服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。
Mysql游标只能用于存储过程（和函数）。
使用游标涉及几个明确的步骤：
1.在能够使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句。
2.一旦声明后，必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。
3.对于填有数据的游标，根据需要取出（检索）各行。
4.在结束游标使用时，必须关闭游标。
// 创建
CREATE PROCURDURE processorders()
BEGIN
DECLARE ordernumbersCURSOR
FOR
SELECT order_num FROM orders;
END;
// 打开
OPEN ordernumbers；
// get order number
FETCH ordernumbers INTO o;
// 关闭
CLOSE ordernumbers;
注意：在一个游标关闭后，如果没有重新打开，则不能使用它。但是，使用声明过的游标不需要再次声明，用OPEN语句打开它就可以了。其中FETCH用来检索当前行的order_num列（将自动从第一行开始）到一个名为o的局部声明的变量中。对检索出的数据不做任何处理。
9.触发器
MYSQL语句在需要时被执行，存储过程也是如此。他们都需要在某个表发生变化时自动处理，就是触发器。
创建触发器，需要给出4条信息：
1.唯一的触发器名；
2.触发器关联的表；
3.触发器应该响应的活动（DELETE，INSERT或UPDATE）；
4.触发器何时执行（处理之前或之后）
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT ‘Product added’;
注意：文本Product added将对每个插入的行显示一次。只有表才支持触发器，视图不支持。每个表每个事件每次只允许一个触发器。
DROP TRIGGER newproducts；
注意：触发器不能更新或覆盖。为了修改一个触发器，必须先删除它，然后再重新创建。
CREATE TRIGGER new order AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;
注意：此代码创建一个名为neworder的触发器，它按照AFTER INSERT ON orders执行，在插入一个新订单到orders表时，mysql生成一个新订单号并保存到order_num中，触发器从NEW.order_num取得这个值并且返回它。
网友评论