开篇说明:1、本文中的SQL脚本来自于:http://forta.com/books/0672327120/
2、本文为《MySQL必知必会》的读书笔记,内容基本上源于书中
目录
第1章:了解SQL
这一章节主要介绍了一些基本概念:
1、数据库(database):保存有组织的数据的容器。数据库是一个以某种有组织的方式存储的数据集合。理解数据库最简单的方法是将其想象为一个文件柜。此文件柜是一个存放数据的物理位置,不管数据是什么以及如何组织的。
2、表(table):某种特定类型数据的结构化清单。
3、列(column):表中的一个字段。所有表都是由一个或多个列组成的。
4、行(row):表中的一个记录。表中的数据是按行记录的,所保存的每个记录存储在自己的行内。
5、主键(primary key):一列(或一组列),其值能够唯一区分表中每个行。唯一标识表中的这个列(或这组列)称为主键。主键用来表示一个特定的行。没有主键,更新或删除表中特定行很困难,因为没有安全的方法保证只涉及相关的行。
6、SQL(Structured Query Language):结构化查询语言,是一种专门用来与数据库通信的语言。
第2章:MySQL简介
1、MySQL是一种DBMS,即它是一种数据库软件。数据的所有存储、检索、管理和处理实际上都是由数据库软件----DBMS(数据库管理系统)完成的。
2、DBMS分类:
(1)基于共享文件系统的DBMS,主要用于桌面用途;
(2)基于客户机--服务器的DBMS,MySQL、Oracle以及Microsoft SQL Server等数据库都是基于客户机-服务器的数据库。
3、MySQL工具:
(1)mysql命令行实用程序;
(2)MySQL Administrator;
(3)MySQL Query Browser。
第3章:使用MySQL
本章主要介绍了如何连接和登陆MySQL,如何用USE选择数据库,如何使用SHOW查看MySQL数据库、表和内部信息。
1、为了连接到MySQL,需要以下信息:主机名+端口+用户名+用户口令;
2、选择数据库:USE 数据库名;
3、了解数据库和表:
(1)SHOW DATABASES:返回可用数据库的一个列表。
(2)SHOW TABLES:返回当前选择的数据库内可用表的列表。
(3)SHOW COLUMNS FROM 表名:对表中的每一个字段返回一行,行中包含字段名、数据类型、是否允许null、键信息、默认值以及其他信息(如:auto_increment)。
(4)SHOW STATUS:用于显示广泛的服务器状态信息。
(5)SHOW CREATE DATABASE和SHOW CREATE TABLE:分别用来显示创建特定数据库或表的MySQL语句。
(6)SHOW GRANTS:用来显示授予用户的安全权限。
(7)SHOW ERRORS和SHOW WARNNINGS:用来显示服务器错误或者警告信息。
第4章:检索数据
本章学习了如何使用SQL的SELECT语句来检索单个表列、多个表列以及所有表列。
1、SELECT语句
为了使用SELECT检索表数据,必须至少给出两条信息:想选择什么以及从什么地方选择。
2、检索单个列
SELECT prod_name FROM products; 从products表中检索一个名为prod_name的列
3、检索多个列
SELECT prod_id, prod_name, prod_price FROM products;
4、检索所有列
SELECT * FROM products;
5、检索不同的行
DISTINCT关键字指示MySQL只返回不同的值。
SELECT DISTINCT vend_id FROM products;
6、限制结果
SELECT prod_name FROM products LIMIT 5; // 返回不多于5行
SELECT prod_name FROM products LIMIT 5, 5; // 从第5行开始,返回5行
7、使用完全限定表名
SELECT prod_name FROM products;
可用用下面的完全限定表名替换:
SELECT products.prod_name FROM products;
SELECT products.prod_name FROM mysql_study.products;
第5章:排序检索数据
本章主要讲如何使用SELECT语句的ORDER BY子句,根据排序检索出的数据。
1、排序数据:未进行排序时,检索出的数据并不是纯粹的随机顺序显示的。如果不排序,数据一般将以它在底层表中出现的顺序显示。这可以是数据添加到表中的顺序。但是,如果数据后来进行过更新或者删除,则此顺序将会受到MySQL重用回收存储空间的影响。因此,不明确控制的话,不能(也不应该)依赖该排序顺序。
2、按单个列排序
SELECT prod_name FROM products ORDER BY prod_name;
3、按多个列排序
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name;
说明:1、为了按多个列排序,只要指定列名,列名之间用逗号分开即可。
2、如上面的SQL语句所示:仅在多个行具有相同的prod_price值时才对产品按prod_name进行排序。如果prod_price值都是唯一的,则不会按prod_name排序。
4、按指定方向排序
默认的排序方式是升序(ASC),如果要倒序排列则需要在对应的列后面加上DESC;
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC;
说明:DESC只作用于直接位于其前面的列名,如下SQL语句中,只对prod_price列指定DESC,对prod_name列不指定。
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name;
5、ORDER BY 和LIMIT组合使用
找出一个列中最大或最小的值
SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1;
6、ORDER BY子句的位置
在给出ORDER BY子句时,应该保证它位于FROM子句之后。如果使用LIMIT,它必须位于ORDER BY之后。
第6章:过滤数据
本章将介绍如何使用SELECT语句的WHERE子句过滤返回的数据。
1、使用WHERE子句,WHERE后面的称为过滤条件
SELECT prod_name, prod_price FROM products WHERE prod_price = 2.50;
2、WHERE子句操作符
操作符 | 说明 |
---|---|
= | 等于 |
<> | 不等于 |
!= | 不等于 |
< | 小于 |
<= | 小于等于 |
> | 大于 |
>= | 大于等于 |
BETWEEN | 在指的ing的两个值之间 |
3、检查单个值
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 <= 10;
4、不匹配检查
SELECT vend_id, prod_name FROM products WHERE vend_id <> 1003;
SELECT vend_id, prod_name FROM products WHERE vend_id != 1003;
5、范围值检查
SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;
6、空值检查
SELECT prod_name FROM products WHERE prod_price IS NULL;
第7章:数据过滤
本章讲解如何组合WHERE子句以建立功能更强的更高级的搜索条件,以及学习如何使用NOT和IN操作符。
1、组合WHERE子句
MySQL允许给出多个WHERE子句。这些子句可以有两种方式的使用:以AND子句的方式或OR子句的方式使用。
2、AND操作符
操作符(operator):用来联结或改变WHERE子句中的子句关键字。也称为逻辑操作符。
SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 1003 AND prod_price <= 10;
AND:用在WHERE子句中的关键字,用来指示检索满足所有给的的条件。上诉SQL语句中是两个过滤条件,如果是多个则每添加一条就要使用一个AND。
3、OR操作符
SELECT prod_price, prod_name FROM products WHERE vend_id = 1002 OR vend_id = 1003;
OR:指示MySQL检索匹配任一条件的行。
4、计算次序
WHERE可以包含任意数目的AND和OR操作符,允许两者结合以进行复杂和高级的过滤。
下面写出这样一个业务需求的SQL语句:选择由供应商1002或1003制造的且价格都在10美元(含)以上的所有产品:
SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;
说明:运行结果里会发现查询的结果里有价格小于10美元的记录,显然是错误的,这是因为AND的执行次次序优先级高于OR,所以上面的这条SQL语句代表的结果是:由供应商1003制造的价格为10美元(含)以上的产品,或者由供应商1002制造的任何产品而不管其价格如何。
那么正确的写法是,要使用括号明确分组操作符:
SELECT prod_name, prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;
说明:任何时候使用具有AND和OR操作符的WHERE语句,都应该使用圆括号明确地分组操作符。不要过分依赖默认的计算次序,即使它确实是你想要的结果。使用圆括号没有什么坏处,它能消除歧义。
5、IN操作符
IN操作符用来指定条件范围,范围中的每个条件都可以进行匹配。它与OR的功能相同。
SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002, 1003) ORDER BY prod_name;
SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 ORDER BY prod_name;
说明:为什么要使用IN操作符?
(1)在使用长的合法选项清单时,IN操作符的语法更清楚且直观,在括号里逗号隔开即可;
(2)在使用IN时,计算的次序更容易管理,因为操作符更少;
(3)IN操作符一般比OR操作符清单执行更快;
(4)IN的最大优点是可以包含其他SELECT语句,使得能够更动态地建立WHERE语句。
6、NOT操作符
SELECT prod_name, prod_price FROM products WHERE vend_id NOT IN (1002, 1003) ORDER BY prod_name;
说明:WHERE子句中的NOT操作符有且只有一个功能:否定它之后所跟的任何条件。
第8章:用通配符进行过滤
本章介绍什么是通配符、如何使用通配符以及怎样使用LIKE操作符进行通配搜索,以便对数据进行复杂过滤。
通配符(wildcard):用来匹配值的一部分特殊字符。
搜索模式(search pattern):由字面值、通配符或两者组合成的搜索条件。
1、百分号(%)通配符
// 检索任意以jet开头的词,jet之后任意字符
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%';
// 通配符可以在搜索模式中的任意位置使用,并且可以使用多个通配符
// 匹配任何位置包含anvil的值,不管它之前或之后是什么
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '%anvil%';
// 以s开头,以e结尾
SELECT prod_name FROM products WHERE prod_name LIKE 's%e';
注意:%不能匹配NULL,如:WHERE prod_name LIKE ‘%’ 不能匹配出值为NULL的产品。
2、下划线(_)通配符
%可以匹配任意多个字符(含0个),而_只能匹配一个字符,不能多也不能少。
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil';
3、使用通配符的技巧:
(1)不要过度使用通配符。如果其他操作符能达到相同的目的,应该使用其他操作符;
(2)在确实需要通配符时,除非绝对有需要,否则不要把它们用在搜索模式的开始处,否则会很慢;
(3)注意通配符的位置。如果放错地方,则可能返回错误的数据。
第9章:用正则表达式进行搜索
1、正则表达式的介绍:正则表达式是用来匹配文本的特殊的串(字符集合)。
例如:如果你想从一个文本文件中提取电话号码,可以使用正则表达式;如果你想查找名字中间有数字的所有文件,可以使用正则表达式;如果你想在一个文本块中找到所有重复的单词,可以使用一个正则表达式......
2、基本字符匹配 REGEXP
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;
3、进行OR匹配
// | 表示匹配其中之一
SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000|3000' ORDER BY prod_name;
4、匹配几个字符之一
// 【123】定义一组字符,它的意思是匹配1或2或3
SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;
// [^123] 表示匹配除这些字符以外的任何东西
SELECT prod_name FROM products WHERE prod_name REGEXP '[^123] Ton' ORDER BY prod_name;
5、匹配范围
集合用来定义要匹配的一个或多个字符。
例如:[0123456789]可以用[0-9]替换,其他表示如:[a-z]
SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton' ORDER BY prod_name;
6、匹配特殊字符
说明:特殊字符需要用\\进行转义
SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.' ORDER BY vend_name;
7、匹配多个实例
元字符 | 说明 |
---|---|
* | 0个或多个匹配 |
+ | 1个或多个匹配(等于{1, }) |
? | 0个或1个匹配(等于{0, }) |
{n} | 指定数目的匹配 |
{n, } | 不少于指定数目的匹配 |
(n, m) | 匹配数目的范围(m不超过255) |
下面举几个例子:
// ?使s可选,出现0次或者1次
SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)' ORDER BY prod_name;
8、定位符的使用
元字符 | 说明 |
---|---|
^ | 文本的开始 |
$ | 文本的结束 |
[ [:<:] ] | 词的开始 |
[ [:>:] ] | 词的结束 |
// 找出以数字(包括小数点)开始的所有产品名
SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name;
9、关于LIKE和REGEXP的区别
LIKE匹配整个列。如果被匹配的文本仅在列值中出现,LIKE并不会找到它,相应的行也不会返回(当然,使用通配符除外)。
而REGEXP在列值内进行匹配,如果被匹配的匹配的文本在列值中出现,REGEXP将会找到它,相应的行将被返回,这时一个非常重要的差别(当然,如果适应定位符号^和$,可以实现REGEXP匹配整个列而不是列的子集)。
第10章:创建计算字段
本章介绍什么是计算字段,如何创建计算字段以及怎样从应用程序中使用别名引用它们。
1、计算字段:存储在表中的数据都不是应用程序所需要的,我们需要直接从数据库中检索出转换、计算或格式化的数据;而不是检索出数据,然后再在客户机应用程序中进行格式化。
2、拼接字段:Concat()函数
SELECT CONCAT(vend_name, '(', vend_country, '