mysql必知必会 学习笔记

于 2019-01-16 20:54:59 发布
阅读量184 收藏
点赞数
分类专栏： Mysql 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/shida_hu/article/details/86514666
版权

第四章 检索数据

对SQL关键字用大写，对所有的列和表名使用小写，这样代码更容易阅读和调试
1、检索单个列SELECT prod_name FROM products;
2、检索多个列 SELECT prod_id, prod_name, prod_price FROM products;
3、检索所有列 SELECT * FROM products;
4、检索所有的行 SELECT vend_id FROM products; SELECT DISTINCT vend_id FROM products;
5、限制检索结果不多于5行SELECT prod_name FROM products LIMIT 5;
返回从第5行开始的下一个5行SELECT prod_name FROM products LIMIT 5,5;
6、使用完全限定的表名 SELECT products.prod_name FROM crashcourse.products;

第五章 排序检查数据

1、对检索结果排序,按照字符顺序来排序 SELECT prod_name FROM products ORDER BY prod_name;
2、对两个列的结果进行排序，先按照价格，再按照名称SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name

对结果降序排列，通过关键字DESC，可以值对部分条件降序，默认是升序ASC(ASCENDING)。SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name

找出一列中最贵的商品的价格SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1;

第六章 过滤数据

1、从products表中检索两个列，但是不返回所有行，只返回prod_priced值为2.50的行 SELECT prod_name, prod_price FROM products WHERE prod_price = 2.50;
2、检查prod_name = ‘fuses’语句。MySQL执行匹配的时候默认不区分比大小写，所以fuses和Fuses匹配。SELECT prod_name, prod_price FROM products WHERE prod_name = 'fuses';
3、检索出价格小于等于10美元的所有商品 SELECT prod_name, prod_price FROM products WHERE prod_price <= 10;
4、检索出所有不是由供应商1003制造的所有产品 SELECT vend_id, prod_name FROM products WHERE vend_id <> 1003; 也可以用另一种方式 SELECT vend_id, prod_name FROM products WHERE vend_id != 1003;
5、使用BETWEEN操作符，检索价格在5美元和10美元之间的所有产品。SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;
6、返回没有价格的所有产品SELECT prod_name FROM products WHERE prod_price IS NULL;

第七章 数据过滤

1、对多个列进行过滤，使用AND操作符给WHERE附加条件。SELECT prod_id, prod_name, prod_price FROM products WHERE vend_id = 1003 AND prod_price <= 10;
2、用OR操作符，匹配多个条件中的任意一个就可以。SELECT prod_id, prod_name, prod_price FROM products WHERE vend_id = 1003 OR prod_price <= 10;
3、组合AND和OR，列出价格大于等于10美元且由1003或者1002制造的所有产品。SELECT prod_name, prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10; 注意，这里必须使用括号，因为AND的子啊计算顺序中优先级较高，如果不加顺序，程序执行顺序和我们想要的不一致
4、IN操作符可以指定条件范围，范围内的每一个条件都可以匹配。SELECT prod_name, prod_price FROM products WHERE vend_id IN(1002, 1003) ORDER BY prod_name;
OR也可以完成类似IN的功能，上面的语句可以写成SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 ORDER BY prod_name;
5、使用NOT操作符，列出除1002和1003之外的所有供应商制造的产品。SELECT prod_name, prod_price FROM products WHERE vend_id NOT IN (1002, 1003)ORDER BY prod_name;

第八章 使用通配符进行过滤

1、通配符%表示任何字符出现任意次数。找出所有以jet开头的产品SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%'
匹配任何位置出现文本anvil的值，不论它之前或者之后出现什么字符SELECT prod_id, prod_name FROM products WHERE vend_id LIKE '%anvil%'
找出以s开头以e结尾的所有产品SELECT prod_name FROM products WHERE prod_name LIKE 's%e';
注意%并不能匹配NULL
2、下划线通配符类似%，不过下划线通配符智能匹配单个字符，不能匹配多个，SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil';
注意：使用通配符的时候，尽量不要放在搜索模式的开始处，把通配符置于开始出，搜索起来比较慢

第十三章

1、创建分组
GROUP BY子句必须出现在WHERE之后，ORDER BY之前
例子：

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

语句中，使用GROUP BY，对每一个vend_id计算一次num_prods，而不是对整个表计算
2、过滤分组 HAVING
WHERE 和HAVING 的区别，WHERE在分组之前过滤，而HAVING是在分组之后过滤。
例如：

SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

例子二，功能：列出具有2个（含）以上，价格为10（含）以上的产品的供应商：

SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2;

3、分组和排序
一般在使用GROUP BY子句的时候，也应该该出ORDER BY子句，这是保证数据正确排序的唯一方法。
例子：检索总计订单价格大于50的订单的订单号和总计订单价格：

SELECT order_num, SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50;
```]
如果想要按照总计订单价格排序输出，需要添加ORDER BY子句，如下所示：


### 第十四章 使用子查询
1、 对于prod_id未TNT2的所有订单物品，它检索其order_num列。`SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';`      
查询结果输出的订单号20005和20007，下一步查询具有订单20005和20007的客户ID。利用IN子句。`SELECT cust_id FROM orders WHERE order_num IN (20005, 20007);`        
现在把两个子查询组合起来。`SELECT cust_id FROM orders WHERE order_num IN(SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');`    
查询结果得到了客户id10001，和10004，下一步可以根据客户ID，检索客户信息`SELECT cust_name, cust_contact FROM customers WHERE cust_id IN (10001, 10004);`   
可以把WHERE的子句换成子查询。

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’)):

2、作为计算字段使用子查询
对客户id为10001的订单进行计数`SELECT COUNT(*) AS orders FROM orders WHERE cust_id = 10001;`    
为了对每一个客户进行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;


### 第十五章 联结表
1、创建联结。两个列prod_name, prod_price在同一个表中，而另一个列vend_name在另一个表中。

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

2、内部联结
使用内部联结INNER JOIN，使用内部联结的时候，条件用ON子句，而不是WHERE子句。

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

3、联结多个表

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 = 2005;

4、可以使用联结改进子查询语句
例如

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’)):

使用联结也可以完成相同的功能

SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num
AND prod_id = ‘TNT2’;


### 第十六章 创建高级联结
1、使用表别名引用被检索的表列

SELECT Contact(RTrim(vend_name), ‘(’, RTrim(vend_country), ‘)’ ) AS vend_tile
FROM vendors
ORDER BY vend_name;

用别名对之前的语句改造如下

SELECT cust_name, cust_contact
FROM customers AS c, order AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
AND oi.order_num = o.order_num
AND prod_id = ‘TNT2’;

2、自联结
找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的所有商品。

SELECT prod_id, prod_name
FROM products
WHERE vend_id = (SELECT vend_id
FROM products
WHERE prod_id = ‘DTNTR’);

可以使用联结完成同样功能的查询，其中products出现了两次，连个别名p1, p2。

SELECT p1.prod_id, p2.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id = ‘DTNTR’;

3、自然联结，使用通配符（SELECT*），通配符只对第一个表使用，其他所有列明确列出，没有重复的列被检索出来。

SELECT c.*, o.order_num, o.order_date
oi.prod_id, oi.quantity, oi.item_price
FROM customers AS c, order AS o, orderitem AS oi
WHERE c.cust_id = o.cust_id
AND oi.cust_id = o.cust_id
AND prod_id = ‘FB’;


4、内部联结
检索所有客户及其订单

SELECT customers.cust_id, orders.order_num
FORM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id;

5、外部联结
检索所有客户，包括哪些没有订单的客户，使用OUTER JOIN字段

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

6、使用带聚集函数的联结    
检索所有客户及每个客户所下的订单数，按照客户进行分组，使用GROUP BY。

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;

可以使用左外部联结来包含所有客户，甚至包含哪些没有下任何订单的客户

SELECT customers.cust_name,
customers.cust_id,
COUNT(orders.order_num) AS num_ord
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;


注意：   
一般我们会使用内部联结，但是有时候也会使用外部联结。   
应该总是提供联结条件。使用ON关键字，不是WHERE。

### 第十七章 组合查询
1、使用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指示mysql执行两个select语句，并把输出组合为单个查询结果集。
2、UNION ALL 
UNION会自动去除重复的行，如果想要输出所有匹配行，需要用UNION ALL
3、对组合查询结果进行排序
在使用UNION组合查询的时候，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT之后。不允许使用多个ORDER BY子句。

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)
ORDER BY vend_id, prod_price;
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值