菜单

mysql-柒 数据检索(伍),mysql-七数据检索

2019年5月4日 - MySQL

算算字段

mysql-柒 数据检索(伍),mysql-7数据检索

拼接字段

事例:vendors表包蕴供应商名和岗位音信。如若要生成一个供应商报表,供给在供应商的名字中服从name(location)那样的格式列出供应商音信。此报表须求单个值,而表中数据存款和储蓄在八个列vend_name和vend_country中,其余,供给用括号将vend_country括起来,这么些事物都尚未鲜明存储在多少表中,我们来看望哪些编写重返供应商名和地方的select语句

SELECT CONCAT(vend_name, ' (',vend_country,')') FROM vendors ORDER BY vend_name;

此间运用了concat函数,拼接串,即把多个串连接起来造成四个较长的串,concat须要3个依然多少个钦命的串,各类串之间用逗号分隔

manbetx网页手机登录版 1

SELECT CONCAT(RTRIM(vend_name),' (',RTRIM(vend_country),')') FROM vendors ORDER BY vend_name;

rtrim函数去掉值左侧的兼具空格,通过采纳rtrim(),各类列都进行了整理

manbetx网页手机登录版 2

联结(join)

SQL最有力的效能之①正是能在数据检索查询的执行中联合(join)表。联结正是选取SQL的SELECT能实践的最要害的操作。

事例:此例子包涵七个表,2个表是vendors ,
vendors表包蕴全数供应商新闻,每种供应商占1行,每一个供应商具有唯1的标记,此标志称为主键,可以是供应商ID或任何别的唯一值。

products表只存款和储蓄产品音信,它除了存款和储蓄供应商ID(vendors表的主键)外不存款和储蓄其余供应商音信,vendors表的主键又称为products的外键,它将vendors表与products表关联,利用供应商ID能从vnedors表中搜索相应供应商详细新闻。

SELECT * FROM products;

manbetx网页手机登录版 3

SELECT * FROM vendors;

manbetx网页手机登录版 4

SELECT * FROM orderitems;

manbetx网页手机登录版 5

行使别称

SELECT CONCAT(RTRIM(vend_id),' (',RTRIM(vend_country),')') AS vend_title FROM vendors ORDER BY vend_name;

manbetx网页手机登录版 6

 多少个表联结

SELECT vend_name , prod_name , prod_price FROM vendors , products WHERE vendors.`vend_id` = products.`vend_id` ORDER BY vend_name,prod_name;


SELECT vend_name , prod_name , prod_price FROM vendors INNER JOIN products ON vendors.`vend_id` = products.`vend_id`;

manbetx网页手机登录版 7

 

实践算数总结

SELECT prod_id ,quantity ,item_price FROM orderitems WHERE order_num = 20005;

manbetx网页手机登录版 8

SELECT prod_id ,quantity ,item_price, quantity*item_price AS expanded_price  FROM orderitems WHERE order_num = 20005;

manbetx网页手机登录版 9

多个表联结

SELECT prod_name , vend_name ,prod_price , quantity FROM orderitems , products , vendors WHERE products.`vend_id` = vendors.`vend_id` AND orderitems.`prod_id` = products.`prod_id` AND order_num = 20005;

manbetx网页手机登录版 10

 

选择数据管理函数

upper()函数

SELECT vend_name ,UPPER(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;

manbetx网页手机登录版 11

     函数                                     说明

   Left()                        再次来到串左侧的字符

   Length()                   再次回到串的尺寸

   Locate()                   寻觅串的多少个子串

   Lower()                    将串调换为题写

   LTrim()                     去掉串左侧的空格

   Right()                     再次回到串左边的字符

   RTrim()                    去掉串左侧的空格

   Soundex()               重临串的soundex值

   SubString()             重回子串的字符

   upper()                    将串转为大写

 

SELECT cust_name , cust_contact FROM customers WHERE SOUNDEX(cust_contact)=SOUNDEX('Y lie');

查究与lie发音相似的cust_contact的列

manbetx网页手机登录版 12

 使用表别称

SELECT CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country),')') AS vend_title FROM vendors ORDER BY vend_name;

manbetx网页手机登录版 13

 

 日期和时间管理函数

 manbetx网页手机登录版 14

SELECT cust_id , order_num FROM orders WHERE order_date = '2005-09-01';

SELECT cust_id , order_num FROM orders WHERE DATE(order_date) = '2005-09-01';

manbetx网页手机登录版 15

SELECT cust_id , order_num ,order_date FROM orders WHERE DATE(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

SELECT cust_id ,order_num,order_date FROM orders WHERE YEAR(order_date)= 2005 AND MONTH(order_date)= 9;

二月份的持有订单

manbetx网页手机登录版 16

SELECT CONCAT(vend_name, ' (',vend_country,')') FROM vendors ORDER BY vend_name;
SELECT CONCAT(RTRIM(vend_name),' (',RTRIM(vend_country),')') FROM vendors ORDER BY vend_name;
SELECT CONCAT(RTRIM(vend_id),' (',RTRIM(vend_country),')') AS vend_title FROM vendors ORDER BY vend_name;
SELECT prod_id ,quantity ,item_price FROM orderitems WHERE order_num = 20005;
SELECT prod_id ,quantity ,item_price, quantity*item_price AS expanded_price FROM orderitems WHERE order_num = 20005;
SELECT vend_name ,UPPER(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;
SELECT cust_name , cust_contact FROM customers WHERE SOUNDEX(cust_contact)=SOUNDEX('Y lie');
SELECT cust_name , cust_contact FROM customers;
SELECT cust_id , order_num FROM orders WHERE order_date = '2005-09-01';
SELECT cust_id , order_num FROM orders WHERE DATE(order_date) = '2005-09-01';
SELECT cust_id , order_num ,order_date FROM orders WHERE DATE(order_date) BETWEEN '2005-09-01' AND '2005-09-30';
SELECT cust_id ,order_num,order_date FROM orders WHERE YEAR(order_date)= 2005 AND MONTH(order_date)= 9;

 

制造高等联结,使用表外号

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';

manbetx网页手机登录版 17

自联结

SELECT prod_id,prod_name FROM products WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DTNTR');   自查询
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';  自联结

manbetx网页手机登录版 18

理当如此联合

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';

manbetx网页手机登录版 19

 

表面联结

SELECT customers.`cust_id`,orders.`order_num` FROM customers LEFT OUTER JOIN orders ON customers.`cust_id` = orders.`cust_id`;

  manbetx网页手机登录版 20

 

 

SELECT vend_name , prod_name , prod_price FROM vendors , products WHERE vendors.`vend_id` = products.`vend_id` ORDER BY vend_name,prod_name;
SELECT vend_name , prod_name , prod_price FROM vendors INNER JOIN products ON vendors.`vend_id` = products.`vend_id`;
SELECT prod_name , vend_name ,prod_price , quantity FROM orderitems , products , vendors WHERE products.`vend_id` = vendors.`vend_id` AND orderitems.`prod_id` = products.`prod_id` AND order_num = 20005;
SELECT CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country),')') AS vend_title FROM vendors ORDER BY vend_name;
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 prod_id,prod_name FROM products WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DTNTR');
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` FROM customers LEFT OUTER JOIN orders ON customers.`cust_id` = orders.`cust_id`;

 

http://www.bkjia.com/Mysql/1175554.htmlwww.bkjia.comtruehttp://www.bkjia.com/Mysql/1175554.htmlTechArticlemysql-7 数据检索(5),mysql-柒数据检索
联结(join)
SQL最庞大的效果之一就是能在数据检索查询的实行中集结(join)表。联结即是采用SQL的…

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图