MySQL必知必会

2024-03-16 11:01| 来源: 网络整理| 查看: 265

PDF&源数据下载

链接：https://pan.baidu.com/s/1Ookw3cLDsrPOVJTVTrQ-kw 提取码：kfak

第二章连接mysql

在terminal中连接mysql，输入以下指令：

mysql -u root -p yourpassword 第三章连接数据库 USE crashhouses; SHOW DATABASES: SHOW TABLES SHOW COLUMNS FROM customers; SHOW STATUS 第四章检索数据

SELECT，DISTINCT，LIMIT语句

SELECT name FROM products; --选择单个列 SELECT name,id FROM products; --选择多个列 SELECT * FROM products; --选择所有列 SELECT DISTINCT ven_id FROM products --只返回不同的值 SELECT prod_name FROM produvts LIMIT 5; --限制返回不多于5行 SELECT prod_name FROM produvts LIMIT 5,5; --返回从行5开始的5行，也就是第6-10行 SELECT products.name FROM products; --使用完全限定的列名 SELECT products.name FROM crashcourse.products; --使用完全限定的列名和表名第五章排序检索数据

ORDER BY，DESC语句

select prod_name from products order by prod_name; --以prod_name的首字母来排序 select prod_id, prod_price, prod_name from products order by prod_price, prod_name; --以prod_price排序，然后相同的值再以prod_name来排序 select prod_id, prod_price, prod_name from products order by prod_price DESC; --加了DESC就成了降序 select prod_id, prod_price, prod_name from products order by prod_price DESC, prod_name; --DESC只应用到位于其前面的列名 --与DESC相反的是ASC（一般用不到，默认）第六章过滤数据

WHERE 子句

SELECT prod_name, prod_price FROM products WHERE prod_price = 2.5; --只返回prod_price值为2.50的行 SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;--选择商品价格在[5,10]区间的，包含端点 SELECT prod_name FROM products WHERE prod_price IS NULL;--选择prod_price=null的行一些关键点

WHERE子句的位置在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后，否则将会产生错误 NULL与不匹配：因为NULL未知具有特殊的含义，数据库不知道它们是否匹配，所以在匹配过滤以及不匹配过滤时都不返回它们。

第七章数据过滤

AND OR IN NOT

SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 1003 AND prod_price= 10;--OR语句 SELECT prod_name, vend_id, prod_price FROM products WHERE vend_id IN (1001,1003) ORDER BY prod_name;--选取vend_id是1001或者是1003的，并排序 SELECT prod_name, prod_price, vend_id FROM products WHERE vend_id NOT IN (1001,1003) ORDER BY prod_name;--vend_id不是1001和1003的一些关键点

AND的优先级高于OR MySQL支持使用NOT对IN 、BETWEEN 和EXISTS子句取反，但并不支持对所有条件进行取反

第八章用通配符进行过滤

LIKE ‘%’ ‘_’

SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%'; --找出所有以词jet起头的产品,%可以匹配任意字符任意长度 SELECT prod_id, prod_name from products WHERE prod_name LIKE '%anvil%'; SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil'; -- '_'可以匹配单个字符一些关键点搜索是区分大小写的%可以指代0个字符LIKE '%'也不能匹配用值NULL作为产品名的行。通配符处理起来比较耗时第九章用正则表达式进行搜索

REGEXP ‘.’ ‘|’ ‘[]’

SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name; --REGEXP代表正则表达式关键字，prod_name中含有1000的行 SELECT prod_name FROM products WHERE prod_name REGEXP '.000'; -- '.'可以匹配任一个字符 SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name; -- '|'代表或者的意思 SELECT prod_name FROM products WHERE prod_name REGEXP '[123] ton' ORDER BY prod_name; --匹配一个字符，匹配特定的字符 +-------------+ | prod_name | +-------------+ | 1 ton anvil | | 2 ton anvil | +-------------+ 正则表达式\\([0-9] sticks?\\) --匹配TNT (1 stick)和TNT (5 sticks) [[:digit:]]{4} --匹配连在一起的任意4位数字。亲测 '[:digit:]{4}' 和上面效果一样，[0-9][0-9][0-9][0-9]也一样 +--------------+ | prod_name | +--------------+ | JetPack 1000 | | JetPack 2000 | +--------------+ 一些关键点 LIKE '1000’和REGEXP '1000’是不一样的，前者匹配完整的列值，后者只要列值包含1000就ok正则表达式不区分大小写，如果想要区分，加上BINARY关键字，如WHERE prod_name REGEXP BINARY ‘JetPack .000’[123] ton也可以写成[1|2|3]字符集也可以被否定，[^123]匹配除这些字符外的任何东西。[12345] 也可以写成[1-5],[a-z]这种形式也可为了匹配特殊字符，必须用\为前导。\-表示查找-，\.表示查找. 。如果直接’.’，代笔的是匹配人一个字符。\也用来引用元字符，诸如\f(换页），\n（换行）等等。P57为了匹配反斜杠（\）字符本身，需要使用\\已定义的字符集[:alnum:]，[:alpha:]等等，在P58定位符，定义字符的位置P60^的双重用途： ^ 有两种用法。在集合中（用[和]定义），用它来否定该集合，否则，用来指串的开始处。1简单的正则表达式测试:如 SELECT ‘hello’ REGEXP ‘[0,9]’,返回1是匹配，返回0是不匹配。第十章创建计算字段

Concat()函数来拼接两个列 RTrim()函数去掉值右边的所有空格（LTrim()去掉左边，Trim()去掉串左右两边的空格）。

SELECT Concat(vend_name,'(',vend_country,')') from vendors ORDER BY vend_name; --供货商（国家）的形式，连接了两个列 SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')') AS vend_title FROM vendors ORDER BY vend_name; --用RTrim()去掉了右端的空格，然后又用AS给新拼接的列命名为vend_name,为了方便后面应用到客户机应用中 SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_price FROM orderitems WHERE order_num = 20005; --将quantity*item_price这两列相乘的值作为了新的列关键点测试计算：，SELECT3*2，将返回6；SELECT Trim(‘abc’)，将返回abc；而SELECT Now()利用Now()函数返回当前日期和时间。第11章使用数据处理函数

Upper() Date()

SELECT vend_name, Upper(vend_name) AS vend_name_upcase from vendors ORDER BY vend_name; --将vend_name全部大写关键点常用的文本处理函数：Left(),Length(),Locate(),Lower(),LTrim(),Right()等等。见P69有关Date()的各类操作函数在P71常用数值处理函数，Abs(),Cos(),Exp(),Mod(),Pi(),Rand(),Sin(),Sqrt(),Tan()，在P74 第12章汇总数据

聚集函数 AVG(),COUNT(),MAX(),MIN(),SUM()

SELECT AVG(prod_price) AS avg_price FROM products; --返回products表中所有产品的平均价格 SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003; --vend_id为1003的商品的平均价格 select count(*) AS num_cust FROM customers; --计算customers表中有多少行 select count(*) AS num_cust FROM customers; --只对具有email地址的客户计数 select SUM(item_price*quantity) AS total_price FROM orderitems WHERE order_num = 20005; --SUM()函数也可以对两列的积进行求和 SELECT AVG(DISTINCT prod_price)AS avg_price FROM products WHERE vend_id = 1003; --使用了DISTINCT参数，所以平均值只考虑各个不同的价格第13章分组数据

GROUP BY、HAVING、HAVING

select vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id; --根据vend_id进行分组 +---------+-----------+ | vend_id | num_prods | +---------+-----------+ | 1001 | 3 | | 1002 | 2 | | 1003 | 7 | | 1005 | 2 | +---------+-----------+ select vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*) >2;、 --利用HAVING对分组后进行过滤 +---------+-----------+ | vend_id | num_prods | +---------+-----------+ | 1001 | 3 | | 1003 | 7 | +---------+-----------+ SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >=2; --WHERE和HAVING共存 +---------+-----------+ | vend_id | num_prods | +---------+-----------+ | 1003 | 4 | | 1005 | 2 | +---------+-----------+ 关键点 GROUP BY子句可以包含任意数目的列。GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。SELECT子句及其顺序：SELECT、 FROM WHERE、 GROUP BY、 HAVING、 ORDER BY、 LIMIT 第14章使用子查询 select cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'); --列出订购了'TNT2'的用户的ID select cust_id,cust_name,cust_state, (select COUNT(*) from orders where orders.cust_id=customers.cust_id) AS orders FROM customers ORDER BY cust_name; --显示customers表中每个客户的订单总数。 P94 第15章联结表 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, products 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; --利用INNER JOIN，联结条件用ON来指定而不是WHERE，效果和上面的语句一样 SELECT order_num, 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; --联结三个表，前两个WHERE定义了两个联结条件关键点应该保证所有联结都有WHERE子句，否则MySQL将返回比想要的数据多得多的数据。等值联结（equijoin），它基于两个表之间的相等测试。这种联结也称为内部联结第16章创建高级联结

自联结：你发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。代码如下：

SELECT vend_id, 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 customers.cust_id, orders.order_num FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id; --这种方法不会显示没有订单的客户 +---------+-----------+ | cust_id | order_num | +---------+-----------+ | 10001 | 20005 | | 10001 | 20009 | | 10003 | 20006 | | 10004 | 20007 | | 10005 | 20008 | +---------+-----------+ 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 | +---------+-----------+

与内部联结关联两个表中的行不同的是，外部联结还包括没有关联行的行。在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）。上面的例子使用LEFT OUTER JOIN从FROM子句的左边表（customers表）中选择所有行。为了从右边的表中选择所有行，应该使用RIGHT OUTER JOIN。

使用带聚集函数的联结：与上面类似。

关键点表别名不返回客户机，列别名返回客户机应该总是提供联结条件，否则会得出笛卡儿积。第17章组合查询

UNION 有两种基本情况，其中需要使用组合查询：

在单个查询中从不同的表返回类似结构的数据；对单个表执行多个查询，按单个查询返回数据。 SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price

【本文地址】

公司简介

联系我们