《MySQL必知必会》笔记（前13章）

SHOW COLUMNS FROM categorys#查看表的字段结构DESCRIBE categorys#这是查看表结构的一种简写(和上面的一样)SHOW STATUS#显示服务器的信息SHOW GRANTS#用来显示权限的SHOW ERRORSSHOW WARNINGS#用来显示服务器的错误SHOW DATABASES;#显示所有的数据库USE test;#使用test数据库SHOW TABLES;#获得一个数据库内的表的列表SHOW COLUMNS FROM customers#获得一个表中字段的详细信息，对每个字段返回一行，行中包含字段名 数据类型 是不是允许为null等等#describe 语句 是SHOW COLUMNS FROM 的一种快捷方式# describe customers;与 SHOW COLUMNS FROM customers是一样的DESCRIBE customers;SHOW STATUS;#显示广泛的服务器状态信息SHOW GRANTS;#显示授权用户的安全权限##############  第四章:检索数据  #############一般对所有的SQL关键子使用大写，对所有的列和表使用小写SELECT prod_name FROM products;#从products表中检索一个名为prod_name的列SELECT prod_id,prod_name,prod_price FROM products;#从products表中检索多个列SELECT * FROM products;#最好不要用通配符(*),会降低检索和应用程序的性能SELECT vend_id FROM products;SELECT DISTINCT vend_id FROM products;#和上面不同的是，上面会检索出指定列的所有数据,下面的语句面使用DISTINCT关键字，告诉mysql只返回不同的值SELECT prod_name FROM products LIMIT 5;#LIMIT 5 指示mysql返回不多于5行，(这种参数总是从第一个数开始)，给出的数是返回的行数SELECT prod_name FROM products LIMIT 5,5;#指示返回从第五行开始  (不包括第五行) 的5行，第一个数为开始位置，第二个数为要检索的行数#行0 检索出来的是第一行 是行0 不是行1，所以limit1,1是指从第一行开始(不包括第一行) 起的一行，检索出来的就是行2#当检索的行数不够时，mysql返回它能返回的行数SELECT products.prod_name FROM products;#这里指定了一个完全限定的列名SELECT products.prod_name FROM crashcourse.products;#这里完全限定了表名，crashcourse.products是指crashcourse数据库中的products表##############  第五章:排序检索数据  ############SELECT prod_name FROM products ORDER BY prod_name# ORDER BY : 检索出来的数据按照prod_name的字母顺序进行排序SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price,prod_name;#顺序按照 prod_price 进行排序，在按照 prod_name 进行排序#对于上述的排序，仅在多个行具有相同的 prod_price 的值时才对产品按 prod_name 进行排序，#如果 prod_price 列中所有的值都是唯一的，那么就不会按 prod_name 排序#先满足第一个，在满足第二个SELECT prod_name FROM products ORDER BY prod_name DESC;#排序的方向: 默认是升序(A-Z)，使用 DESC 关键字是进行降序排序SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price DESC,prod_name;# DESC关键字只应用到直接位于其前面的列名，对prod_price进行降序排序，对prod_name进行升序排序 SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1# 找出了最贵的商品:prod_price DESC 保证了行是按照从最贵到最便宜进行排序，而LIMIT 1 告诉mysql仅返回一行#注意:order by 必须位于from子句的后面，limit必须位于order by 子句的后面##############  第六章:过滤数据  ############SELECT prod_name,prod_price FROM products WHERE prod_price = 2.50#只返回prod_price值为2.50 的行SELECT prod_name,prod_price FROM products WHERE prod_price = 2.50 ORDER BY prod_name#同时使用ORDER BY和WHERE时，ORDER BY 应位于WHERE的后面#WHERE子句的操作符 #=等于#<>不等于#!=不等于#<小于#>大于#<=小于等于#>=大于等于#BETWEEN AND 在指定的两个值之间SELECT prod_name,prod_price FROM products WHERE prod_name = 'fuses';SELECT prod_name,prod_price FROM products WHERE prod_price < 10;SELECT prod_name,prod_price FROM products WHERE prod_price <= 10SELECT vend_id,prod_name FROM products WHERE vend_id <> 1003;SELECT vend_id,prod_name FROM products WHERE vend_id != 1003SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;#检索出价格在5美元到10美元之间的所有产品 (也包括5和10美元)SELECT prod_name FROM products WHERE prod_price IS NULL;# IS NULL:检查prod_price为NULL值得列 NULL是无值，与字段包含0，空字符串，空格的含义是不一样的SELECT cust_id FROM customers WHERE cust_email IS NULL;# 检索出cust_email为NULL 的cust_id出来##############  第七章:数据过滤  ############SELECT prod_id,prod_price,prod_name FROM products WHERE vend_id = 1003 AND prod_price <= 10# AND 关键字，把两个条件联合起来查询 ，还可以添加多个过滤条件 。每添加条就要使用一个AND# OR 关键字，返回只要符合其中的一条条件就可以#如果需要列出价格为10美元以上。并且由供应商1002或1003制造的所有产品#错误SELECT prod_name,prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price > 10#因为AND 的优先级比 OR 高，所以检索出来的是供应商是1002并且价格为10元以上的 或者 供应商是1002的#正常SELECT prod_name,prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price > 10#添加一个括号，在任何时候是有具有AND和OR操作符的WHERE语句，都应该使用圆括号明确的分组操作符，不要过分依赖默认的计算次序，它能消除歧义SELECT prod_name,prod_price FROM products WHERE vend_id IN (1002,1003) ORDER BY prod_nameSELECT prod_name,prod_price FROM products WHERE vend_id =1002 OR vend_id=1003 ORDER BY prod_name#  IN关键字: 检索出供应商为1002和1003制造的所有产品，上面两条语句是一样的# 为什么要使用IN操作符:#1.在使用长的合法选项清单时，IN操作符的语法更加清楚 直观#2.在使用IN时，计算的次序更容易管理(因为使用的操作符更少)#3.IN操作符一般比OR操作符清单执行更快#4.IN的最大优点是可以包含其他哦SELECT语句，使得能够更动态的建立WHERE子句SELECT prod_name,prod_price FROM products WHERE vend_id IN NOT (1002,1003) ORDER BY prod_name# NOT关键字;列出出1002和1003之外的所有供应商制造的产品,NOT否定跟在它之后的条件#为什么使用NOT：#对于简单的WHERE子句，使用NOT确实没有什么优势，但是在更复杂的子句中,#NOT是非常有用的.例如，在与IN操作符联合使用时，NOT使找出与条件列表不匹配的非常简单##############  第八章:用通配符进行过滤  ############SELECT prod_name,prod_price FROM products WHERE prod_name LIKE 'jet%'# % 通配符: 检索任意以jet开头的词，%告诉Mysql接受jet之后的任意字符,不管它有多少的字符,不能匹配到NULL的行SELECT prod_name,prod_price FROM products WHERE prod_name LIKE '%anvil%'# 不管之前或者是之后出现什么字符SELECT prod_name,prod_price FROM products WHERE prod_name LIKE 's%e'# 不管中间出现什么字符，也可以是0个，SELECT prod_name,prod_price FROM products WHERE prod_name LIKE '_ ton anvil'# _ 通配符:一个下划线只匹配一个字符，就是只匹配ton anvil前面只有一个字符的#不要过度使用通配符,如果其他的操作符能到达相同的目的，应该使用其他的操作符##############  第九章:用正则表达式进行搜索  ############SELECT prod_name FROM products WHERE prod_name REGEXP '1000'# 返回prod_name中包含1000字符的所有行# REGEXP :告诉Mysql:REGEXP后所跟的东西作为正则表达式处理SELECT prod_name FROM products WHERE prod_name REGEXP '.000'# '.000'中的一点 匹配一个任意的字符#REGEXP与LIKE的重要差别SELECT prod_name FROM products WHERE prod_name LIKE '1000';SELECT prod_name FROM products WHERE prod_name REGEXP '1000'#执行第一条不会返回数据，而第二条返回一行#因为LIKE匹配的是列的整个值，不会进行列内匹配#而REGEXP匹配的是列值内进行匹配SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000'#'1000|2000'表示正则表达式的OR操作，匹配其中之一SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton'#使用正则表达式[123] Ton，[123]定义一组字符，它的意思是匹配1或2或3，因此存在1ton和2ton和3ton的话都会返回#[^123] 表示匹配除了这些字符外的任何东西#[0123456789] = [0-9]#如何匹配特殊的字符 例如; .[] ,使用\\进行匹配，\\-表示查询-字符SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.'#还有很多内容，需要了解更多看书吧##############  第十章:创建计算字段 #############拼接字段 : 使用 CONCAT() 关键字SELECT CONCAT(vend_name,'(',vend_country,')','(',vend_city,')') FROM vendors #Anvils R Us(USA)(Southfield)，ACME(USA)(Los Angeles)，就好像这种格式一样，在同一行中把不同列的字段拼接起来#使用别名 : 使用关键字 AS + 新的别名SELECT CONCAT(vend_name,'(',vend_country,')','(',vend_city,')') AS vend_title  FROM vendors #算术计算 : 注意看第二条 quantity*item_price AS expanded_priceSELECT prod_id,quantity,item_price  FROM orderitems WHERE order_num = 20005SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_price FROM orderitems WHERE order_num = 20005##############  第十一章:使用数据处理函数 #############转换成大写子母 : 关键字 UPPER()SELECT vend_name,UPPER(vend_name) AS vend_name_upcase FROM vendors #常用的文本处理函数#Left()返回串左边的字符#Length()返回串的长度#Locate()找出串的一个子串#Lower()转换成小写#LTrim()去掉串左边的空格#Rigth()返回串右边的字符#RTrim()去掉串右边的空格#Soundex()返回串的SOUNDEX值#SubString()返回子床串的字符#Upper()将串转换为大写#日期和时间的处理函数#Mysql的日期格式必须是:yyyy-mm-ddSELECT 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'#建议使用关键字 DATE() ，如果有一行的日期是 2005-09-01 11:30:05,使用第一条查询不会被检索出来# BETWEEN AND 关键字SELECT cust_id,order_num FROM orders WHERE DATE(order_date) BETWEEN '2005-09-01' AND '2005-09-30'#检索出2005年9月的所有订单#还有一种方法检索出2005年9月的订单，而不需要记住哪个月有多少天SELECT cust_id,order_num FROM orders WHERE YEAR(order_date) = 2005 AND MONTH(order_date) = 9#数值处理函数#Abs()返回一个数的绝对值#Cos()返回一个余弦#Exp()返回一个数的指定数#Mod()返回除操作的余数#Pi()返回圆周率#Rand()返回一个随机数#Sin()返回一个角度的正弦#Sqrt()返回一个数的平方根#Tan()返回一个角度的正切##############  第十二章:汇总数据 #############sql聚集函数#AVG()返回某列的平均值#COUNT()返回某列的行数(只是行数)#MAX()返回某列的最大值#MIN()返回最小值#SUM()返回某列值之和(计算每个行中的数值)SELECT AVG(prod_price) AS avg_price FROM products#返回products表红所有产品的平均价格SELECT COUNT(*) AS num_cust FROM customers#返回customers 表中客户的总数，利用COUNT(*)对所有的行计数，计数值在num_cust中返回SELECT COUNT(cust_email) AS num_cust FROM customers#只对具有电子邮件地址的客户计数SELECT MAX(prod_price) AS max_price FROM products#对products表中的 最贵物品的价格SELECT SUM(quantity) AS items_ordered FROM orderitems #订单orderitems中 所有物品的数量SELECT SUM(item_price*quantity) AS total_price FROM orderitems #合计每项物品的(金额*数量)，得出总的订单金额，SUM()函数忽略列值为NULL的行#聚合函数的 DISTINCT 使用SELECT AVG( DISTINCT prod_price) AS avg_price FROM products# DISTINCT 排除了一些较低的价格，再进行平均计算##############  第十三章:分组数据 ############# GROUP BY 子句SELECT vend_id,COUNT(*) AS num_prods FROM products GROUP BY vend_id#把 vend_id 相同为一组，分别计算每组由几个产品组成， GROUP BY  在 WHERE 之后，ORDER BY之前# HAVING 过滤分组 (WHERE) 只能过滤行,HAVING 的使用在WHERE 之后SELECT cust_id,COUNT(*) AS orders FROM orders  GROUP BY cust_id HAVING COUNT(*) >=2#HAVING COUNT(*) >=2 把 完成分组后的组进行过滤，只返回数量大于等于二的#HAVING 和WHERE的差别:WHERE在数据分组前过滤,HAVING在数据分组后进行过滤,WHERE排除的行不进行分组#GROUP BY 和 ORDER BY 的结合使用SELECT order_num,SUM(item_price*quantity) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(item_price*quantity>=50) ORDER BY ordertotal#根据order_num进行分组，在根据ordertotal的大小进行排序#SELECT子句及其顺序#子句说明是否必须使用#SELECT要返回的列或表达式是#FROM从中检索数据的表仅在从表选择数据是使用#WHERE行级过滤否#GROUP BY按条件进行分组否#HAVING组级过滤否#ORDER BY输出数据的排序方式否#LIMIT要检索的行数否