MYSQL必知必会 学习笔记一

什么是数据库

数据库（database）
保存有组织的数据的容器（通常是一个文件或一组文件），类似于一个文件柜

表（table）
某种特定类型数据的结构化清单
表可以保存顾客清单，产品目录，或者其他信息清单。

模式（schema）
关于数据库和表的布局及特性的信息

列（column）
表中的一个字段。所有表都是由一个或多个列组成的。
例如，城市，州，邮政编码应该总是独立的列。

数据类型（datatype）
所容许的数据的类型。每个表列都有相应的数据类型，它限制（或容许）该列中存储的数据。

行（row）
表中的一个记录
行数为记录的总数

主键（primary key）
一列（或一组列），其值能够唯一区分表中的每个行
表中的任何列都可以作为主键，只要它满足一下条件：
①任意两行不具有相同的主键值
②每个行都必须具有一个主键值（主键值不允许NULL值）

主键的最好习惯：
①不更新主键列中的值
②不重用主键列的值
③不在主键列中使用可能会更改的值

什么是SQL
结构化查询语言(Structured Query Language)简称SQL(发音sequel）。SQL是一种专用用来与数据库通信的语言。

什么是MySQL

MySQL是一种DBMS（Database Management System 数据库管理系统），即是一种数据库软件。

DMBS分为两类：一类为基于共享文件系统的DBMS，另一类为基于客户机-服务器的DBMS。

客户机-服务器软件
客户机-服务器应用分为两个不同的部分。
服务器部分是负责所有数据访问和吹了的一个软件。这个软件运行在称为数据库服务器的计算机上。
与数据文件打交道的只有服务器软件。

客户机是与用户打交道的软件。

使用MySQL

关键字(key word)
作为MySQL语言组成部分的一个保留字。

使用 create database 语句可完成对数据库的创建, 创建命令的格式如下:
create database 数据库名 [其他选项];
例如我们需要创建一个名为 samp_db 的数据库, 在命令行下执行以下命令:
create database samp_db character set gbk;

USE crashcourse
使用crashcourse数据库

SHOW DATABASES
返回可用数据库的一个列表

SHOW TABLES
返回当前选择的数据库内可用表的列表

SHOW COLUMNS FROM customers
要求给出一个表名（如customers），它对每个字段返回一行，行中包含字段名，数据类型，是否允许NULL，键信息，默认值以及其他信息。

自动增量
MySQL可以自动为每个行分配下一个可用编号，不用在添加一行时手动分配唯一值。

DESCRIBE
DESCRIBE作为SHOW COLUMNS FROM的一种快捷方式。
即SHOW COLUMNS FROM customers相当于DESCRIBE customers

SHOW STATUS
用于显示广泛的服务器状态信息

SHOW CREATE DATABASE
用于显示创建特定数据库

SHOW CREATE TABLE
用于显示创建特定表

SHOW GRANTS
grant（授予），用来显示授予用户（所有用户或特定用户）的安全权限。

SHOW ERRORS
用于显示服务器错误

SHOW WARNINGS
用于显示警告信息

检索数据

首先在命令行中执行 mysql -u root进入mysql 服务器
用create database crashcourse创建新的数据库

通过在命令行中执行mysql -D crashcourse -u root < create.sql
将pupulate.sql的数据导入crashcourse数据库中
通过在命令行中执行mysql -D crashcourse -u root < populate.sql
将pupulate.sql的数据导入crashcourse数据库中

SELECT语句

SELECT prod_name FROM products;
利用SELECT语句从products表中检索一个名为prod_name的列。

结束SQL语句
多条SQL语句必须以；分隔。

SQL语句不区分大小写，但要注意标识符

检索多个列

SELECT prod_id,prod_name,prod_price FROM products
在这个例子中，指定了三个列名，列名之间用逗号分隔。

检索所有列

SELECT * FROM products;

检索不同的行

SELECT vend_id FROM products;
返回所有行

如果要检索出不重复的值，使用DISTINCT(有区别的)关键字
SELECT DISTINCT vend_id FROM products;

限制结果

为了返回第一行或前几行，可使用LIMIT子句
SELECT prod_name FROM products LIMIT 5;
LIMIT 5指示MySQL返回不多于5行。

SELECT prod_name FROM products LIMIT 3,4;
指示MySQL返回从行3开始的4行。第一个数为开始位置，第二个数为要检索的行数。
（行的最开始下标为0，在行数不够时只返回有的行）

可以用SELECT prod_name FROM products LIMIT 4 OFFSET 3;代替

使用完全限定的表名

SELECT products.prod_name FROM products;
等价于
SELECT prod_name FROM products;
等价于
SELECT products.prod_name FROM crashcourse.products;

排序检索数据

排序数据

如果不排序，数据一般将以它在底层表中出现的顺序显示。

SQL语句由子句(clause)构成，如SELECT语句和FROM语句

为了排序，可使用ORDER BY子句。
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;

检索3个列，并按其中两个列对结果进行排序——首先按价格，然后再按名称排序

可以看出，仅在多个行具有相同的prod_price时才按prod_name进行排序。

指定排序方向

默认的排序顺序是升序，还可以用ORDER BY子句降序排序，需指定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关键字只应用到直接位于其前面的列名，因此，prod_price列降序排序
，而prod_name仍按升序。

与DESC相反的关键字ASC（ASCENDING）,但默认是升序的

使用ORDER BY和LIMIT的组合，找出一个列中最高或最低的值
SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1;

ORDER BY子句应保证在FROM子句之后，LIMIT子句必须位于ORDER BY之后。

过滤数据

使用WHERE子句

只检索所需数据需要指定搜索条件，搜索条件也称为过滤条件

SELECT prod_name,prod_price FROM products WHERE prod_price=2.50;
从products表中检索两个列，但只返回prod_price值为2.50的行；

在同时使用ORDER BY 和WHERE 子句时，ORDER BY位于WHERE 之后。

WHERE子句操作符

SELECT prod_name,prod_price FROM products WHERE prod_name=”fuses”;
MySQL在执行匹配时默认不区分大小写，所以Fuses会与fuses匹配。

SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;

空值检查
SELECT语句可用一个特殊的WHERE子句，IS NULL子句来检查具有NULL值的列。
SELECT prod_name FROM products WHERE prod_price IS NULL;
因为表中没有这样的行，所有不返回数据

SELECT cust_id FROM customers WHERE cust_email IS NULL;

数据过滤

组合WHERE子句

操作符（operator）用来联结或改变WHERE子句中的子句的关键字，也称为逻辑操作符。

AND操作符
SELECT prod_id,prod_price,prod_name FROM products WHERE vend_id=1003 AND prod_price<=10;

OR操作符
SELECT prod_name,prod_price FROM products WHERE vend_id=1002 OR vend_id=1003;

计算次序
AND和OR结合时，需注意次序

假如需要列出价格为10美元（含）以上且由1002或1003制造的所有产品
SELECT prod_name,prod_price FROM products WHERE vend_id=1002 OR vend_id=1003 AND prod_price>=10;

可以看到并未按预期过滤，原因在于计算的次序。
在处理OR之前，优先处理AND。
所以变成由1003制造的价格>=10的产品和1002制造的所有产品。

所以应使用圆括号明确分组。
SELECT prod_name,prod_price FROM products WHERE (vend_id=1002 OR vend_id=1003) AND prod_price>=10;

IN操作符

IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。
IN取合法值的由逗号分隔的清单。

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操作符的语法更清楚直观。
计算次数更容易管理。
比OR执行更快
可以包含其他SELECT语句，使得能够更快地动态建立WHERE子句。

NOT操作符

否定它之后的任何条件
SELECT prod_name,prod_price FROM products WHERE vend_id NOT IN(1002,1003) ORDER BY prod_name;

用通配符进行过滤

LIKE操作符

为在搜索子句中使用通配符，必须使用LIKE操作符
谓词(predicate)
从技术上说，LIKE是谓词而不是操作符，但最终结果是相同的。

%通配符
%表示任何字符出现任意次数（包括0）
SELECT prod_id,prod_name FROM products WHERE prod_name LIKE ‘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’;

用正则表达式进行搜索

使用MySQL正则表达式

MySQL用WHERE子句对正在表达式提供了初步的支持，允许你指定正则表达式，过滤SELECT检索出的数据。

基本字符匹配
下面语句检索列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;

这里使用了正则表达式.000. .是正则 表示式中的一个特殊的字符，表示匹配任意一个字符（类似_）

将前面的例子改为如下：
SELECT prod_name FROM products WHERE prod_name LIKE ‘1000’ ORDER BY prod_name;
不反返回数据，因为：
LIKE匹配整个列，如果被匹配的文本在列值中出现，LIKE将不会找到它，相应的行也不被返回（除非使用通配符）
而REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，将找到并返回。

为了区分大小写，可使用BINARY关键字，如WHERE prod_name REGEXP BINARY ‘JetPack .000’

进行OR匹配
为搜索两个串之一，使用|
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;

它的意思是匹配1或2或3，[]是另一种形式的OR语句
[^123]是匹配除这些字符外的任何字符。

匹配范围
[0-9]表示从0到9的集合
SELECT prod_name FROM products WHERE prod_name REGEXP ‘[1-5] Ton’ ORDER BY prod_name;

匹配特殊字符
要匹配如[],|,_这些已经有含义的特殊字符，需要使用转义，用\

匹配字符类
存在找出你经常使用的数字，所有字母字符或所有数字字母字符等匹配，可以使用预定于的字符集，称为字符类

匹配多个实例

SELECT prod_name FROM products WHERE prod_name REGEXP ‘\([0-9] sticks?\)’ ORDER BY prod_name;

s?表示s可选

SELECT prod_name FROM products WHERE prod_name REGEXP ‘[[:digit:]]{4}’ ORDER BY prod_name;

[:digit:]匹配任意数字,{4}要求前面的字符出现4次

定位符

如果你想找出以一个数(包括以小数点开始的数)开始的所有产品
SELECT prod_name FROM products WHERE prod_name REGEXP ‘^[0-9\.]’ ORDER BY prod_name;

^在集合中(即在[]中)用来否定该集合，否则用来指串的开始处。

创建计算字段

我们需要直接从数据库中检索处转换，计算或格式化过的数据。

这就需要计算字段了，计算字段并不实际存在与数据库中，计算字段是运行时在SELECT语句内创建的。

字段(field)
基本与列同义，经常互换使用，段通常用在计算字段的连接上。

拼接字段

拼接(concatenate) 将值联结到一起构成单个值

vendors表包含供应商名和位置信息，我们需要name(location)这样的格式。
方法是把两个列拼接起来，在SELECT语句中，可使用Concat()来拼接两个列。

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;

LTrim()去掉串左边的空格，而Trim()是去掉两边的空格。

使用别名

新计算列没有名字，只是一个值。
SQL支持列别名。别名(alias)是一个字段或值的替换名。别名有AS关键字赋予。

SELECT Concat(RTrim(vend_name),’ (‘,RTrim(vend_country),’)’) AS vend_titile FROM vendors ORDER BY vend_name;

任何客户机应用都可以按名引用这个列，就像是一个实际的表列一样。

别名有时也称为导出列(derived column)

执行算术计算

item_price列包含订单中每项物品的单价，如下汇总物品的价格(单价乘数量)

SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_price FROM orderitems WHERE order_num=20005;

客户机现在可以使用expanded_price，与其他列一样