第1章 了解SQL
1.1 数据库基础
数据库:database(也可称 模式(schema))->表:table -> 列(column) -> 行(row)
主键(primary key):一列(或一组列),其值能够唯一区分表中的每个行!
- 任意两行都不具有相同的主键;
- 每个行都必须具有一个主键值(主键列不允许为NULL)
- 可以多列组合成主键,但组合值必须是唯一的。
1.2 什么是SQL
SQL:Structured Query Language 结构化查询语言;一种专门用来与数据库通信的语言。
第2章 MySQL简介
2.1 什么是MySQL
DBMA:数据库管理系统
为什么选择MySQL:
- 成本——开源,免费使用
- 性能——执行很快
- 可信赖——很多大公司也用MySQL
- 简单——MySQL容易安装和使用
2.1.1 客户机-服务器软件
DBMS分为两类:
- 一类为基于共享文件的DBMS;桌面用途。
- 另一类为基于客户机-服务器的DBMS:MySQL就是经典CS模型。
2.1.2 MySQL版本
登录本地数据库
mysql -u root -p
输入密码后 可以看到版本:
2.2 MySQL工具
MySQL需要客户机应用,这里介绍三种:
2.2.1 mysql 命令行实用程序
- 命令用 ; 或 \g 结束,换句话说,仅按Enter不执行命令;
- 输入 quit 和 exit 退出命令行实用程序
- 很重要 必须掌握!
2.2.2 MySQL Administrator
2.2.3 MySQL Query Browser
以上两种都是图形化工具,类似Navicat。
第3章 使用MySQL
3.1 连接
- 主机名(计算机名)——如果连接到本地MySQL服务器,为localhost;
- 端口 (如果使用默认端口3306之外的端口)
- 一个合法的用户名
- 用户口令(如果需要)
3.2 选择数据库
不能用关键字作为表名或列名
切换数据库
use crashcourse
展示所有的数据库
show databases;
数据库内表的列表
show tables;
显示表列
show columns from customers;
MySQL支持用describe作为“显示表列”的快捷方式
DESCRIBE customers;
其他show语句
第4章 检索数据
4.1 select语句
- 想选择什么
- 从什么地方选择
4.2 检索单个列
从products表中检索一个名为prod_name的列。
SELECT prod_name FROM products;
- 返回的数据可能是未排序的顺序
- sql语句不区分大小写;但习惯是关键字大写,列和表名小写。
4.3 检索多个列
多个列用逗号分隔,并且最后一个列名不能加逗号
SELECT prod_id,prod_name,prod_price FROM products;
4.4 检索所有列
SELECT * FROM products;
4.5 检索不同的行
关键字:DISTINCT;只返回不同的值
SELECT DISTINCT vend_id FROM products;
4.6 限制结果
LIMIT子句,返回前几行
SELECT prod_name FROM products LIMIT 5;
SELECT prod_name FROM products LIMIT 5,5;
4.7 使用完全限定的表名
SELECT products.prod_name FROM products;
SELECT products.prod_name FROM crashcourse.products;
在一些特定场景需要用完全限定名。
第5章 排序检索数据
5.1 排序数据
初始化的数据顺序为添加到表中的数据,但如果进行过更新或删除,会受到MYSQL重用回收存储空间的影响。
因此,如果不明确排序顺序,则不应该假定检索出的数据的顺序有意义。
为了排序,可使用ORDER BY子句;
SELECT prod_name
FROM products
ORDER BY prod_name;
5.2 按多个列排序
和前面的一样,按逗号分开
SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price,prod_name;
仅在多个行具有相同的prod_price值时才对产品按prod_name排序,如果prod_price列中所有的值都是唯一的,则不会按prod_name排序
5.3 指定排序方向
默认升序,可通过关键字DESC变为降序
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;
DESC关键字只应用到直接位于其前面的列名
使用ORDER BY 和 LIMIT 的组合,能够找出一个列中最高或最低的值
SELECT prod_price
FROM products
ORDER BY prod_price DESC
LIMIT 1;
第6章 过滤数据
6.1 使用WHERE 子句
用于检索特定的行,指定搜索条件也称“过滤条件”
SELECT prod_name,prod_price
FROM products
WHERE prod_price =2.50;
6.2 WHERE子句操作符
6.2.1 检查单个值
SELECT prod_name,prod_price
FROM products
WHERE prod_name = 'fuses';
6.2.2 不匹配检查
SELECT vend_id,prod_name
FROM products
WHERE vend_id <> 1003;
6.2.3 范围值检查
使用BETWEEN AND子句检索
SELECT prod_name,prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;
6.2.4 空值检查
NULL :无值 no value 它与字段包含0、空字符串或仅仅包含空格不同
SELECT prod_name
FROM products
WHERE prod_prices IS NULL;
第7章 数据过滤
本章讲述如何组合WHEER子句以建立功能更高级的搜索条件。并学习NOT和IN操作符。
7.1 组合WHEER子句
7.1.1 AND操作符
SELECT prod_id,prod_price,prod_name
FROM products
WHERE vend_id = 1003 AND prod_price<=10;
7.1.2 OR操作符
SELECT prod_name,prod_price
FROM products
WHERE vend_id = 1002 OR vend _id = 1003;
7.1.3 计算次序
默认是先AND 后 OR ,所以需要带括号,而且最好直接带括号,不要依赖默认的顺序。
select prod_name,prod_price
FROM products
where (vend_id =1002 or vend_id =1003) and prod_price>=10;
7.1.4 IN操作符
IN操作符用来指定条件范围,范围中的每个条件都可以进行匹配。IN取合法值的由逗号分隔的清单,全都括在圆括号中。
SELECT prod_name,prod_price
FROM products
WHERE vend_id IN (1002,1003)
ORDER BY prod_name;
IN 与 OR 完成的功能类似
7.3 NOT 操作符
否定后跟条件的关键字。
SELECT prod_name,prod_price
FROM products
WHERE vend_id NOT IN (1002,1003)
ORDER BY prod_name;
第八章 用通配符进行过滤
8.1 LIKE操作符
通配符(wildcard):用来匹配值的一部分特殊字符。
**搜索模式(search pattern)**由字面值、通配符或两者组合构成的搜索条件
想使用通配符,必须使用LIKE操作符,否则会是直接相等匹配。
8.1.1 百分号(%)通配符
% 表示任何字符出现的任意次数。
SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE 'jet%';
所以是可以配置区分大小写的!
SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE '%anvil%';
8.1.2 下划线(_)通配符
下划线的用途与%一样,但下划线只匹配单个字符而不是多个字符。
SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE '_ ton anvil';
8.2 使用通配符的技巧
通配符搜索的处理一般比前面讨论的其他搜索所花时间更长。
- 不要过度使用通配符。如果其他操作符能达到相同的目的,应该使用其他操作符。
- 在确实需要使用通配符时,除非绝对有必要,否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处,搜索起来是最慢的。
- 仔细注意通配符的位置。如果放错地方,可能不会返回想要的数据。
第九章 用正则表达式进行搜索
9.1 正则表达式介绍
9.2 使用MySQL正则表达式
9.2.1 基本字符匹配
检索列prod_name包含文本1000的所有行
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name;
SELECT prod_name
FROM products
WHERE prod_name REGEXP '.000'
ORDER BY prod_name;
9.2.2 进行OR匹配
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000|2000'
ORDER BY prod_name;
9.2.3 匹配几个字符之一
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[123] Ton'
ORDER BY prod_name;
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1|2|3 Ton'
ORDER BY prod_name;
9.2.4 匹配范围
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[1-5] Ton'
ORDER BY prod_name;
9.2.5 匹配特殊字符
SELECT vend_name
FROM vendors
WHERE vend_name REGEXP '.'
ORDER BY vend_name;
想检索带‘.’的字符,但是. 匹配任意字符,因此每行都被检索了出来。
需要转义
SELECT vend_name
FROM vendors
WHERE vend_name REGEXP '\\.'
ORDER BY vend_name;
9.2.6 匹配字符类
9.2.7 匹配多个实例
SELECT prod_name
FROM products
WHERE prod_name REGEXP '\\([0-9] sticks?\\)'
ORDER BY prod_name;
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[[:digit:]]{4}'
ORDER BY prod_name;
等价于
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[0-9][0-9][0-9][0-9]'
ORDER BY prod_name;
9.2.8 定位符
SELECT prod_name
FROM products
WHERE prod_name REGEXP '^[0-9\\.]'
ORDER BY prod_name;
第十章 创建计算字段
10.1 计算字段
就是有些数值不是拿出来就能用的,需要做一些转换。
虽然这个转换前端也能做,但是性能没有在数据库上做速度快。
10.2 拼接字段
拼接(concatenate):将值联结到一起构成单个值
SELECT Concat(vend_name,'(',vend_country,')')
FROM vendors
ORDER BY vend_name;
RTrim()删除数据右侧多余的空格
SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')')
FROM vendors
ORDER BY vend_name;
使用别名 用 AS关键字赋予,主要帮助客户机引用,也被称为 导出列
SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')') AS vend_title
FROM vendors
ORDER BY vend_name;
10.3 执行算术计算
这步让我对数据库刮目相看啊,还是非常强大的!
第十一章 使用数据处理函数
11.1 函数
11.2 使用函数
- 用于处理文本串(如删除或填充值,转换值为大写或小写)的文本函数
- 用于在数值数据上进行算术操作(如返回绝对值,进行代数运算)的数值函数
- 用于处理日期和时间值并从这些值中提取特定成分(例如,返回两个日期之差,检查日期有效性等)的日期和时间函数
- 返回DBMS正使用的特殊信息(如返回用户登录信息,检查版本细节)的系统函数
11.2.1 文本处理函数
SOUNNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节,使得能对串进行发音比较而不是字母比较。虽然SOUNDEX不是SQL的概念,但MySQL(就像多数DBMS一样)都提供对SOUNDEX的支持。
SELECT cust_name,cust_contact
FROM customers
WHERE cust_contact ='Y.Lie';
SELECT cust_name,cust_contact
FROM customers
WHERE Soundex(cust_contact)=Soundex('Y Lie');
是真的人性化,这种问题都能考虑到
11.2.2 日期和时间处理函数
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-09-01' AND '2005-09-30';
如果不知道一个月有多少天嘞?
SELECT cust_id,order_num
FROM orders
WHERE Year(order_date) = 2005 AND Month(order_date) = 9;
11.2.3 数值处理函数
第12章 汇总数据
12.1 聚集函数
一下的例子
- 确定表中行数(或者满足某个条件或包含某个特定值的行数)
- 获得表中行组的和
- 找出表列(或所有行或某些特定的行)的最大值、最小值和平均值
12.1.1 AVG()函数
返回平均值,也可以用来返回特定列或行的平均值。
SELECT AVG(prod_price) AS avg_price
FROM products;
也可以增加条件
SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;
12.1.2 COUNT()函数
可利用COUNT()确定表中行的数目或符合特定条件的行的数目。
COUNT()两种使用方式
- 使用COUNT(*)对表中行的数目进行计数,不管表列中包含的是空值(NULL)还是非空值
- 使用COUNT(column)对特定列中具有值的行进行计数,忽略NULL值
12.1.3 MAX()函数
SELECT MAX(prod_price) AS max_price
FROM products;
12.1.4 MIN()函数
与上述一致
12.1.5 SUM()函数
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;
12.2 聚集不同值
以上5个聚集函数都可以如下使用:
- 对所有的行执行计算,指定ALL参数或不给参数
- 只包含不同的值,指定DISTINCT参数
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id =1003;
12.3 组合聚集函数
SELECT COUNT(*) AS num_items,MIN(prod_price) AS price_min,MAX(prod_price) AS price_max,AVG(prod_price) AS price_avg
FROM products;
第13章 分组数据
13.1 数据分组
SELECT COUNT(*) AS num_prods
FROM products
WHERE vend_id =1003;
13.2 创建分组
SELECT vend_id,COUNT(*) AS num_prod
FROM products
GROUP BY vend_id;
- GROUP BY 子句可以包含任意数目的列。这使得能对分组进行嵌套,为数据分组提供更细致的控制。
- 如果在GROUP BY 子句中嵌套了分组,数据将在最后规定的分组上进行汇总。换句话说,在建立分组时,指定的所有列都一起计算(所以不能从个别的列取回数据)
- GROUP BY子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在SLECT 中使用表达式,则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
- 除聚集计算语句外,SELECT语句中的每个列都必须在GROUP BY 子句中给出。
- 如果分组列中具有NULL值,则NULL将作为一个分组返回,如果列中有多行NULL值,他们将分为一组。
- GROUP BY 子句必须出现在WHERE子句之后,ORDER BY 子句之前。
13.3 过滤分组
WHERE 过滤行,HAVING过滤分组
SELECT cust_id,COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >=2;
WHERE 和 HAVING 可以组合使用。
SELECT vend_id,COUNT(*) AS num_prods
FROM products
WHERE prod_price >=10
GROUP BY vend_id
HAVING COUNT(*) >=2;
SELECT vend_id,COUNT(*) AS num_prods
FROM products
GROUP BY vend_id
HAVING COUNT(*) >=2;
13.4 分组和排序
SELECT order_num,SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price)>=50;
13.5 SELECT子句顺序
第14章 使用子查询
14.1 子查询
上面所有的SELECT语句都是简单查询,即从单个数据库表中检索数据的单条语句,SQL还允许创建子查询,即嵌套在其他查询中的查询。
14.2 利用子查询进行过滤
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
WHERE cust_id IN(SELECT cust_id
FROM orders
WHERE order_num IN(SELECT order_num
FROM orderitems
WHERE prod_id='TNT2'));
14.3 作为计算字段使用子查询
SELECT cust_name.
cust_state
(SELECT COUNT(*)
FROM orders
WHERE orders,cust_id=customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;
第15章 联结表
15.1 联结
SQL最强大的功能之一就是能在数据检索查询的执行中联结(join)表。
15.1.1 关系表
相同数据出现多次决不是一件好事,此因素是关系数据库设计的基础。关系表的设计就是要保证把信息分解成多个表,一类数据库一个表。各表通过某些常用的值互相关联。
**外键(foreign key)**外键为某个表中的一列,它包含另一个表的主键值,定义了两个表之间的关系。
可伸缩性(scale) 能够适应不断增加的工作量而不失败,设计良好的数据库或应用程序称之为 可伸缩好。
15.1.2 为什么要使用联结
15.2 创建联结
SELECT vend_name,prod_name,prod_price
FROM vendors,products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name,prod_name;
15.2.1 WHERE子句的重要性
15.2.2 内部联结
目前为止所用的联结称为 等值联结(equijion),它基于两个表之间的相等测试。也被称为内部联结。
SELECT vend_name,prod_name,prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;
15.2.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 = 20005;
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';
第16章 创建高级联结
16.1 使用表别名
- 缩短SQL 语句
- 允许在单条SELECT语句中多次使用相同的表
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';
16.2 使用不同类型的联结
16.2.1 自联结
使用表被骂的主要原因之一是能在单天SELECT 语句中不止一次引用相同的表
SELECT prod_id,prod_name
FROM products
WHERE vend_id = (SELECT vend_id
FROM products
WHERE prod_id = 'DTNTR');