MySQL必知必会11-20

最新推荐文章于 2023-08-09 18:28:17 发布

kongytr

最新推荐文章于 2023-08-09 18:28:17 发布

阅读量152

点赞数

分类专栏：数据库学习

本文链接：https://blog.csdn.net/qq_34640691/article/details/109386000

版权

数据库学习专栏收录该内容

11 篇文章 0 订阅

订阅专栏

第十一章使用数据处理函数

Left()				返回串左边的字符
Length()			返回串的长度
Locate()			找出字符串的一个子串
Lower()				将串转换为小写
Ltrim()				去掉串左边的空格
Right()				返回串右边的字符
RTrim()				去除字符串右边的空格
Soundex()			返回串的SOUNDEX值
Substring()			返回子串的字符
Upper()				转换为大写

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_id, order_num from orders where order_date = '2005-09-01';
select cust_id, order_num from orders where DATE(order_date) BETWEEN '2005-0901' AND '2005-09-30';

在这里插入图片描述

常用的数值处理函数

EXP()			返回一个数的指数值
RAND()			返回一个小于1的正数
FLOOR()			取整

#以下是sql报错注入需要用到的一些语句
select exp(709);
select exp(710);
select rand();
select floor(rand()*2);

在这里插入图片描述

第十二章数据汇总

聚集函数运行在组上，计算和返回单个值的函数，
如AVG()返回某列的平均值；
COUNT()返回某列的行数；
MAX,MIN返回某列的最大最小值
SUM返回某列值之和。

select AVG(prod_price) AS avg_price from products;
select avg(prod_price) as avg_price from products where vend_id = 1003;

在这里插入图片描述

select count(*) as num_cust from customers;
select count(cust_email) as email_cust from customers;
select MAX(prod_price) as max_price from products;
select MIN(prod_price) as min_price from products;

在这里插入图片描述

select SUM(quantity) AS items_ordered from orderitems where order_num = 20005;
select SUM(quantity*item_price) AS items_ordered from orderitems where order_num = 20005;

在这里插入图片描述


select avg(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 1003;
mysql> select COUNT(*) AS num_items,
    -> MIN(prod_price) AS price_min,
    -> MAX(prod_price) AS prod_max,
    -> AVG(prod_price) AS prod_avg
    -> from products;

在这里插入图片描述

第十三章分组数据

分组是在SELECT语句的GROUP BY子句中建立的。如下所示

mysql> select vend_id, count(*) as num_prods from products group by vend_id;
#group by 子句指示Mysql按照vend_id排序并分组数据
#num_prods为计算字段，由count(*)函数建立
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+
4 rows in set
#group by子句必须出现在where子句之后，Order by子句之前。

HAVING子句与WHERE子句类似。目前学过的WHERE子句都可以用HAVING子句代替。唯一的差别在于，WHERE子句过滤行，HAVING子句过滤分组。

mysql> select cust_id, count(*) as orders from orders group by cust_id HAVING COUNT(*) >= 2;
+---------+--------+
| cust_id | orders |
+---------+--------+
|   10001 |      2 |
+---------+--------+
#检索count(*)>=2的分组

mysql> SELECT vend_id, count(*) as num_prods from products where prod_price >= 10 group by vend_id HAVING count(*) >= 2;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1003 |         4 |
|    1005 |         2 |
+---------+-----------+
2 rows in set
#检索具有两个以上，价格为10以上的供应商

排序和分组的区别

ORDER BY	GROUP BY
排序产生输出	分组行。但是输出可能不是分组的顺序
任意列都可以使用，甚至非选择的列也可以使用	只看使用选择列或表达式列，而且必须使用每个选择列表达式
不一定需要	如果与聚集函数一起使用列或者表达式，则必须使用

mysql> select order_num, SUM(quantity*item_price) AS ordertotal from orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >= 50;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20005 | 149.87     |
|     20006 | 55.00      |
|     20007 | 1000.00    |
|     20008 | 125.00     |
+-----------+------------+
4 rows in set
#检索订单价格大于等于50的订单号和总计订单价格

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    |
+-----------+------------+
4 rows in set
#order by子句对ordertotal排序

SELECT子句顺序

子句						说明							是否必要使用
SELECT 					要返回的列表或表达式			是
FROM					从中检索数据的表				仅在从表选择数据时使用
WHERE					行级过滤						否
GROUP					分组说明						仅在按组计算聚集时使用
HAVING					组级过滤						否
ORDER BY				输出排序顺序					否
LIMIT					要检索的行数					否

第十四章使用子查询

sql还允许创建子查询，即嵌套在其他查询中的查询。

（1）不使用嵌套查询
select order_num from orderitems WHERE prod_id = 'TNT2';
SELECT cust_id from orders where order_num in (20005,20007);
（2）使用嵌套查询
select cust_id from orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

列必须匹配：在where子句中使用子查询应该保证SELECT语句具有与WHERE子句中相同数目的列。

mysql> SELECT cust_name
    -> custe_state,
    -> (SELECT COUNT(*)
    -> FROM orders
    -> WHERE orders.cust_id = customers.cust_id) AS orders 
    -> FROM customers
    -> ORDER BY cust_name;
+----------------+--------+
| custe_state    | orders |
+----------------+--------+
| Coyote Inc.    |      2 |
| E Fudd         |      1 |
| Mouse House    |      0 |
| Wascals        |      1 |
| Yosemite Place |      1 |
+----------------+--------+
5 rows in set

select语句对customers表中每个客户返回三列:cust_name,cust_state和orders。orders是一个计算字段，由圆括号中的子查询建立的。
子查询对检索出的每个客户执行一次，在这个例子中，子查询执行了五次，因为检索了五个客户。

第十五章联结表

创建联结要规定联结的所有表以及它们如何关联。

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;

在这里插入图片描述
是

mysql> 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;
+----------------+-------------+------------+----------+
| prod_name      | vend_name   | prod_price | quantity |
+----------------+-------------+------------+----------+
| .5 ton anvil   | Anvils R Us | 5.99       |       10 |
| 1 ton anvil    | Anvils R Us | 9.99       |        3 |
| TNT (5 sticks) | ACME        | 10         |        5 |
| Bird seed      | ACME        | 10         |        1 |
+----------------+-------------+------------+----------+
4 rows in set
#联结多个表

第十六章创建高级联结

#16.1使用别名
mysql> 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';
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
2 rows in set


#16.2.1自联结
mysql> SELECT prod_id, prod_name
    -> from products
    -> where vend_id = (select vend_id from products where prod_id = 'DTNTR');
+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| DTNTR   | Detonator      |
| FB      | Bird seed      |
| FC      | Carrots        |
| SAFE    | Safe           |
| SLING   | Sling          |
| TNT1    | TNT (1 stick)  |
| TNT2    | TNT (5 sticks) |
+---------+----------------+
7 rows in set

mysql> 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';
+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| DTNTR   | Detonator      |
| FB      | Bird seed      |
| FC      | Carrots        |
| SAFE    | Safe           |
| SLING   | Sling          |
| TNT1    | TNT (1 stick)  |
| TNT2    | TNT (5 sticks) |
+---------+----------------+
7 rows in set

#16.2.3外部联结
mysql> select customers.cust_id, orders.order_num from customers LEFT outer join orders on customers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10002 | NULL      |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+
6 rows in set

#16.3使用聚集函数的联结
mysql> 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;
+----------------+---------+---------+
| cust_name      | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc.    |   10001 |       2 |
| Wascals        |   10003 |       1 |
| Yosemite Place |   10004 |       1 |
| E Fudd         |   10005 |       1 |
+----------------+---------+---------+
4 rows in set

第十七章组合查询

可用UNION操作符来组合数条SQL查询。使用UNION时默认重复的行会被自动取消，UNION ALL不取消重复的行

#17.2.1使用UNION
mysql> 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);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1003 | FC      | 2.5        |
|    1002 | FU1     | 3.42       |
|    1003 | SLING   | 4.49       |
|    1003 | TNT1    | 2.5        |
|    1001 | ANV01   | 5.99       |
|    1001 | ANV02   | 9.99       |
|    1001 | ANV03   | 14.99      |
|    1002 | OL1     | 8.99       |
+---------+---------+------------+
8 rows in set


#17.2.4对组合查询结果排序
mysql> 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;
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1001 | ANV01   | 5.99       |
|    1001 | ANV02   | 9.99       |
|    1001 | ANV03   | 14.99      |
|    1002 | FU1     | 3.42       |
|    1002 | OL1     | 8.99       |
|    1003 | TNT1    | 2.5        |
|    1003 | FC      | 2.5        |
|    1003 | SLING   | 4.49       |
+---------+---------+------------+
8 rows in set

第十八章全文本搜索

18.2.1启用全文本搜索支持
mysql> 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;

第十九章插入数据

INSERT是用来插入或添加行到数据库表的。一般不会产生输出

#19.2插入完整的行
mysql> INSERT INTO customers
    -> VALUES(NULL,
    -> 'Pep E. LaPew',
    -> '100 Main STREET',
    -> 'Los Angles',
    -> 'CA',
    -> '90046',
    -> 'USA',
    -> NULL,
    -> NULL);
Query OK, 1 row affected


#更安全的添加方式
mysql> INSERT INTO customers(cust_name,
    -> cust_address,
    -> cust_city,
    -> cust_state,
    -> cust_zip,
    -> cust_country,
    -> cust_contact,
    -> cust_email)
    -> VALUES('Pep E. LaPew',
    -> '100 Main STREET',
    -> 'Los ANGLES',
    -> 'CA',
    -> '90046',
    -> 'USA',
    -> NULL,
    -> NULL);
Query OK, 1 row affected

19.3插入多个行
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 27
mysql> 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. Martina',
    -> '42 Galaxy Way',
    -> 'New York',
    -> 'NY',
    -> '1121
    -> ',
    -> 'USA'
    -> );
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

第二十章更新和删除数据

update和delete语句

#客户10005现在有了电子邮件地址，它的记录需要更新
mysql> UPDATE customers SET cust_email = 'elmer@fudd.com' WHERE cust_id = 10005;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0
#更新单个列


mysql> UPDATE customers SET cust_name = 'THE Fudds', cust_email = 'elmer@fudd.com' WHERE cust_id = 10005;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0
#更新多个列
UPDATE IGNORE customers    忽略错误，继续进行更新
UPDATE中还可以使用子查询



DELETE语句
mysql> DELETE FROM customers WHERE cust_id = 10006;
Query OK, 0 rows affected