这里写目录标题
第三章 使用MySQL
# 显示所有的数据库
show databases ;
# 使用指定数据库
use crashcourse;
# 查看所有表
show tables ;
# 查看表的所有列信息
show columns from customers;
# show columns from的快捷方式
describe customers;
# describe的缩写
desc customers;
# 显示mysql服务状态信息
show status ;
# 显示授予用户的安全权限
show grants ;
# 显示错误信息
show errors ;
# 显示警告信息
show warnings ;
第四章 检索数据
# 查看表的所有列信息
DESCRIBE products;
# 从products表中查询pro_name列的信息
SELECT prod_name FROM products;
# 从products表中查询pro_name,vend_id列的信息
SELECT prod_id, vend_id FROM products;
# 查询products表中的全部信息
SELECT * FROM products;
# 从products表中查询vend_id列后去重
SELECT DISTINCT vend_id FROM products;
# 从products表中查询vend_id列后去重(会应用于所有的列, 而不是只有第一列)
SELECT DISTINCT vend_id, prod_id FROM products;
# 从products表中查询prod_name的前5行(如果没有明确排序查询结果,则返回的数据没有特殊意义)
# 返回数据的顺序可能是数据被添加的顺序,也可能不是,只要返回相同数目的行,就是正常的
SELECT prod_name FROM products LIMIT 5;
# 从products表中查询prod_name的6-10行(从第5行开始,然后再返回5行)
SELECT prod_name FROM products LIMIT 5, 5;
# 同时返回两列的五行
SELECT prod_name, vend_id FROM products LIMIT 5, 5;
# 使用完全限定的名字(同时使用表名和列名)
SELECT products.prod_name FROM products;
SELECT products.prod_name FROM crashcourse.products;
第五章 排序检索数据
# 对prod_name列按字母序排序(默认升序)
SELECT prod_name FROM products ORDER BY prod_name;
# 先按价格排序, 如果价格相同则按名称排序
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name;
# 按价格降序
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC ;
# 先按价格降序,如果价格一样,按名称升序(升序是默认的)
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name;
# 按照价格降序后选出第一个,也就是价格最贵的
SELECT prod_ID, prod_price, prod_name FROM products ORDER BY prod_price DESC LIMIT 1;
第六章 过滤数据
# 查询价格为2.5的产品信息
SELECT prod_id, prod_price, prod_name FROM products WHERE prod_price=2.5;
# 查询名称为FUSES的产品信息
# 实际名称是Fuses,但是MYSQL不区分大小写
SELECT prod_id, prod_price, prod_name FROM products WHERE prod_name='FUSES';
# 查询产品id不是1003的产品信息
SELECT vend_id, prod_price, prod_name FROM products WHERE prod_id <> 1003 ;
# 查询产品id不是1003的产品信息,按vend_id降序输出
SELECT vend_id, prod_price, prod_name FROM products WHERE prod_id <> 1003 ORDER BY vend_id DESC;
第七章 数据过滤
# 查询价格为5-10的产品信息
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;
# 查询价格为5-10的产品信息,按vend_id降序输出
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10 ORDER BY vend_id DESC ;
# 查询价格为空产品信息 注意NULL就是为空,既不是空格符也不是0
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_price IS NULL ;
# 从customers中查询cust_email为空的人的编号
SELECT cust_id FROM customers WHERE cust_email IS NULL ;
# 查询vend_id=1003且prod_price<=10的产品信息
SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 1003 AND prod_price <= 10;
# 查询vend_id = 1002 或者 vend_id = 1003的产品信息
SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003;
# !!!本意是 查询prod_price >= 5 且 vend_id = 1001 和 prod_price >= 5 且 vend_id = 1的产品信息
# 但是返回结果出现了prod_price < 5 的产品,说明语句有问题
# 原因是AND的优先级高,mysql理解为vend_id = 1003 且 prod_price >= 10 或者所有 vend_id = 1003的产品
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_price >= 5 AND vend_id = 1001 OR vend_id=1003;
# 使用括号来限定优先级
SELECT vend_id, prod_name, prod_price FROM products WHERE (vend_id = 1002 OR vend_id=1003) AND prod_price >= 10 ;
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_price >= 10 AND (vend_id = 1002 OR vend_id=1003) ;
# IN 指定条件范围 查询vend_id的范围在1002, 1003的产品信息
SELECT vend_id, prod_name, prod_price FROM products WHERE vend_id IN (1002, 1003) ORDER BY prod_price;
# 使用OR和上面用IN的效果一样,但是IN更快
SELECT vend_id, prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 ORDER BY prod_price;
# NOT 否定后面跟的条件 查询vend_id的范围不在1002, 1003的产品信息
SELECT vend_id, prod_name, prod_price FROM products WHERE vend_id NOT IN (1002, 1003) ORDER BY prod_price DESC ;
第八章 用通配符过滤
# 通配符 % 表示任何字符出现任何次数(不区分大小写,我们可以配置区分大小写)
# 所有名字以jet开头的产品信息
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name LIKE 'jet%';
# 同上
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name LIKE 'JET%';
# 所有名字包含anvil的产品信息
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name LIKE '%anvil%';
# 所有名字以s开头,e结尾的产品信息
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name LIKE 's%e';
# 下划线 _ 匹配任意一个字符 查询_ ton anvil格式的产品信息 _ 可以是任意字符
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name LIKE '_ ton anvil';
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name LIKE '% ton anvil';
第九章 用正则表达式搜索
# 正则 REGEXP 告知后面跟的是正则表达式
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '1000';
# . 表示匹配任意一个字符
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '.000';
# like要求整个数据都要匹配,而REGEXP只需要部分匹配即可。
# 第一句只能匹配1000,第二句能匹配包含1000字符串的文本
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name LIKE '1000';
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '1000';
# 匹配名为JetPack .000的产品,BINARY表示区分大小写
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP BINARY 'JetPack .000';
# | 表示正则中的 或者
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '1000|2000';
# [123]表示匹配 1 Ton 或者 2 Ton 或者 3 Ton
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '[123] Ton';
# 错误,这么些只能表示 匹配1 或者 2 或者 3 Ton
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '1|2|3 Ton';
# ^ 否匹配
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '[^123] Ton';
# 匹配范围
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '[1-5] Ton';
# 匹配.
# 特殊字符匹配,需要转义,正则需要一个\,mysql需要一个\,所以是两个\
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '\\.';
# 匹配-
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '\\-';
# ? 表示?的前一位字符出现0次或者1次
# \\( 匹配( [0-9]匹配0-9 STICKS?匹配STICK或者STICKS \\)匹配)
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '\\([0-9] STICKS?\\)';
# 匹配字符类(预定义的字符集)
# 任意数字
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '[[:digit:]]';
# {4}要求它前面的字符(任意数字)出现4次,所以[[:digit:]]{4}匹配连在一起的任意4位数字
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '[[:digit:]]{4}';
# 任意字母
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '[[:alpha:]]';
# {9}要求它前面的字符(任意数字)出现9次,所以[[:alpha:]]{9}匹配连在一起的任意9位字母
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '[[:alpha:]]{9}';
# 任意小写字母
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '[[:lower:]]';
# 任意大写字母
SELECT vend_id, prod_name, prod_price FROM products WHERE prod_name REGEXP '[[:lower:]]';
# ^文本的开始, $文本的结束 ^在集合[]中表示否定,否则用来表示字符串的开始
# 使用定位符 匹配以数字或者.开关的所有产品
SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name;
第十章 创建计算字段
# 使用CONCAT()函数拼接列
SELECT CONCAT(vend_name, '(', vend_country, ')') FROM vendors ORDER BY vend_name;
# 使用RTRIM()函数来删除字段右边的空格
SELECT CONCAT(RTRIM(vend_name), '(', vend_country, ')') FROM vendors ORDER BY vend_name;
# 使用LTRIM()函数来删除字段左边的空格
SELECT CONCAT(LTRIM(vend_name),'(', vend_country, ')') FROM vendors ORDER BY vend_name;
# 使用TRIM()函数来删除字段两边的空格
SELECT CONCAT(TRIM(vend_name), '(', vend_country, ')') FROM vendors ORDER BY vend_name;
输出下面语句的结果
SELECT CONCAT(vend_name, '(', vend_country, ')') FROM vendors ORDER BY vend_name;
可以看到,拼接地址字段做得很好,但是这个新的列的名字是什么呢?是CONCAT(vend_name, '(', vend_country, ')')
嘛?实际上,它没有名字,它只是一个值。没有名字就不好应用,因为客户机没办法引用它,所以就有了别名(alias),别名是一个字段或者值的替换名。别名用AS关键字赋予。
# 使用别名 AS
SELECT CONCAT(TRIM(vend_name), '(', vend_country, ')') AS VEND_ID FROM vendors ORDER BY vend_name;
此时
此时列名就变成了我们指定的VEND_ID,就可以引用了。
别名的其他用途:表列明包含不符合规定的字符时重新命名它,在原来的名字容易被误解时扩充它,等待。
# 执行算术计算
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;
十一章 数据处理函数
函数 | 说明 |
---|---|
Left() | 返回串左边的字符 |
Length(str) | 返回串的长度 |
Lower() | 将串转换为小写 |
Ltrim() | 去掉串左边的空格 |
Right() | 返回串右边的字符 |
Rtrim() | 去掉串右边的空格 |
Soundex() | 返回串的SOUNDEX |
LOCATE(substr,str) | 找出串的第一个子串 |
另一种情况:Locate(substr,str,pos)。作用:返回子串 substr 在字符串 str 中的第 pos 位置后第一次出现的位置。如果 substr 不在 str 中返回 0。
SELECT vend_name, LOCATE('e', vend_name, 1) AS ven_name_substring FROM vendors ORDER BY vend_name;
Soundex()对字符串进行发音比较而不是字母比较
假设你只记得顾客的名字是Y. Lie,但是实际上顾客的名字是 Y Lee
SELECT cust_name, cust_contact FROM customers WHERE cust_contact = 'Y Lie';
会查找失败,因为WHERE限定条件很严格。现在试一下用Soundex()函数进行搜索
SELECT cust_name, cust_contact FROM customers WHERE SOUNDEX(cust_contact) = SOUNDEX('Y Lie');
# 查询日期为2005-09-01的信息
SELECT cust_id, order_num FROM orders WHERE order_date='2005-09-01';
此方法并不保险,因为时间中往往包含日期+时间,如 2005-09-01 19:30:05,此时用where就会查询失败。
# DATE()函数返回某时间的日期
SELECT cust_id, order_num FROM orders WHERE DATE(order_date)='2005-09-01';
如果需要的是日期,使用Date()是一种好习惯。
# 查询9月订单的两种方法
SELECT cust_id, order_num FROM orders WHERE DATE(order_date) BETWEEN '2005-09-01' AND '2005-09-30';
SELECT cust_id, order_num FROM orders WHERE YEAR(order_date)=2005 AND MONTH(order_date)=9;
方法一的缺点就是需要记得每个月有多少天,有时候还会出现闰月。
YEAR()函数和MONTH()函数返回一个时间的年份和月份,故方法二可以规避这些缺点。
十二章 汇总数据
汇总数据只是对表中数据(而不是实际数据本身)汇总,返回实际数据对时间和资源都是一种浪费.我们实际想要的是汇总信息.
# 查询产品的平均价格
SELECT AVG(prod_price) AS avg_price FROM products;
# 查询产品vend_id=1003的平均价格
SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id=1003;
获得多个列的平均值必须用多个AVG函数
SELECT AVG(prod_price) AS avg_price, AVG(vend_id) AS vend_id FROM products;
count()函数用来进行计数,有两种使用方式
1.count(*)对表中行的数目进行计数,包括NULL值
2.count(column)对特定列中具有值的进行计数,忽略NULL值
# 查询customers的总行数(包含空值,所以是)
SELECT COUNT(*) AS num_cust FROM customers;
# 查询有邮箱的客户数(忽略空值,所以是3)
SELECT COUNT(cust_email) AS num_cust FROM customers;
SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id=1003;
使用DISTINCT聚焦不同值
# DISTINCT
SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id=1003;
可以看到,使用了DISTINCT后,avg_price变大了,这是因为多个物品有相同的较低价格,被DISTINCT排除后,avg_price就变大了.
注意1.不允许使用COUNT(DISTINCT)
2.DISTINCT必须使用列名,不能用于计算或表达式.
# 组合聚焦函数
SELECT COUNT(*) AS num_items, MIN(prod_price) AS min_price,
MAX(prod_price) AS max_price,
AVG(prod_price) AS avg_price FROM products;