数据库入门

数据库： 保存有组织的数据的容器(通常是一个文件或一组文件).数据库软件应该称为 DBMS(DataBase Manage System) 数据库管理系统，数据库是指通过 DBMS 创建和操纵的容器，数据库可以是保存在硬盘上的文件，但也可以不是。你不能直接访问数据库，而是使用 DBMS 来访问，他替你访问数据库。

表: 某种特定类型数据的结构化清单。存储在表中的数据是一种类型的数据或一个清单，数据库中的每个表都有一个名字，用来标识自己，此名字是唯一的,这表示数据库中没有其他表具有相同的名字。

表具有一些特性，这些特定定义了数据在表中如何存储，如存储什么样的数据，数据如何分解，个部分信息如何命名等等，描述这组信息就是所谓的模式
模式: 关于数据库和表的布局及特性的信息.

列：表中的一个字段，所有的表都是由一个或多个列组成的。
数据类型: 所容许的数据的类型，每个表都有相对应的数据类型，他限制(容许)该列中存储的数据。数据类型限制可存储在列中的数据种类，数据类型还帮助正确的排序数据，并在优化磁盘使用方面起重要的作用，因此，在创建表时必须对数据类型给予特别的关注

行:表中的一个记录，
主键：一列(或一组列)，其值能够唯一区分表中的每个行。唯一标识表中每行的这个列称为主键，主键用来表示一个特定的行，没有主键，更新或删除表中特定行很困难，因为没有安全的方法保证只涉及相关的行。表中的任何列都可以作为主键，只要它满足以下条件:

• 任何两行都不具有相同的主键值
• 每个行都必须具有一个主键值(主键值不允许 NULL 值)。

SQL: 结构化查询语言(Structured Query Language),是一种专门用来与数据库通信的语言。

基础操作

了解数据库和表(SHOW)

1. SHOW DATABASES： 返回一个数据库列表，包含在这个列表中的可能是 MYSQL 内部使用的数据库.
2. SHOW TABLES: 返回当前选择的数据库内可用表的列表
3. SHOW COLUMS FROM customers: 要求给出一个表名，对每个字段返回一行。包含 该字段的信息。

创建和操纵表

创建表

MySQL 不仅用于表数据操作，而且还可以用来执行数据库和表的所有操作，包括表本身的创建和处理。

创建表的方法:
- 使用具有交互式创建和管理表的工具
- 使用 MySQL 语句操纵

1. 表创建基础(CREATE TABLE)
   • 新的表明名再关键字 CREATE TABLE 之后给出
   • 表列的名字和定义，用逗号分隔
2. 使用 NULL 值: Null 值就是没有值或者缺值，允许 NULL 值的列也允许再插入行时不给出该列的值，不允许 NULL 值的列不接受该列没有值的行，换句话说，在插入或更新行时该列必须有值。NULL 为默认值，如果不指定 NOT NULL, 则认为指定的是 NULL， NULL 值就是没有值，他不是空串，空串是一个有效的值。
3. 主键: 主键的值必须唯一，即，表中的每个行必须具有唯一的主键值，如果主键使用单个列，则他的值必须唯一，如果使用多个列，则这些列的组合值必须唯一。
4. 使用 AUTO_INCREMENT: 告诉 MySQL ，本列每当增加一行时自动增量，每次执行一个 INSERT 操作时， MySQL 会对该列增量，每个表只允许一个 AUTO_INCREMENT列，而且他必须被索引

更改表(ALTER TABLE)

1. 增加列: ALTER TABLE vendors ADD vend_phone CHAR(20)
2. 删除列: ALTER TABLE vendors DROP COLUMN vend_phone

删除表 (DROP TABLE)

重命名表 (RENAME TABLE)

插入数据

使用 INSERT 语句将数据插入表中， INSERT是用来插入(或添加)行到数据库表的，插入可以用集中方式使用

1. 插入完整行，使用 INSERT ，要求指定表明和被插入到新行的值,最好是写上列名，这样可以保证列明是正确的。
2. 插入多个行: INSERT 可以插入一行到一个表中，如果想一次性插入多行的话，就需要使用多个 INSERT 或者使用单条 INSERT 语句有多组值，每组值用一对圆括号括起来

更新和删除数据

更新数据

为了更新(修改)表中的数据，可使用 UPDATE 语句，可采用两种方式使用 UPDATE:
使用 UPDATE 的时候一定要注意，不要省略 WHERE 子句，使用 UPDATE 语句总是以要更新的表的名字开始.要以 WHERE 子句(用来告诉 MySQL 更新哪一行)结束。在更新多个列时，只需要使用单个 SET 命令，每个 列=值对之间用逗号分隔(最后一列之后不用逗号);

• 更新表中特定行
• 更新表中所有行

删除数据

为了从一个表中删除(去掉)数据，使用 DELETE 语句，可依两种方式使用 DELETE

• 从表中删除特定的行 DELETE FROM products WHERE cust_id = 1006

更新和删除的指导原则

如果执行 UPDATE 而不带 WHERE 子句，则表中每行都将应用新值更新，类似的，如果执行 DELETE 语句而不带 WHERE 子句，表的所有数据都将被删除。

• 除非缺失打算更新和删除每一行，否则绝对不要使用不带 WHERE子句的 UPDATE或DELETE语句
• 保证每个表都有主键，尽可能像 WHERE子句那样使用它
• 在对 UPDATE 或 DELETE 语句使用 WHERE 子句前，最好先用 SELECT 进行测试，保证它过滤的是正确的记录，以防止编写的 WHERE 子句不正确
• 使用强制实施引用完整性的数据库，这样 MySQL 将不允许删除具有与其他表相关联的数据的行。

检索数据(SELECT)

从一个或多个表中检索信息，为了使用 SELECT 检索数据，必须至少给出两条数据 -- 项选择什么，以及从什么地方选择。

1. 检索单个列: SELECT prod_name FROM products

2. 检索多个列: SELECT prod_id,prof_name FROM product

3. 检索所有列: SELECT * FROM product

4. 检索不同的行: SELECT DISTINCT vend_id FROM products, DISTINCT 关键字必须直接方法在列名的前面

5. 限制结果: LIMIT :

   • SELECT prod_name FROM products LIMIT 5
   • SELECT prod_name FROM products LIMIT 5,5

6. 使用完全限定的表名: SELECT products.prod_name FROM crashcourse.products;

排序检索数据

将会讲授如何使用 SELECT 语句的 ORDER_BY 子句，根据需要排序检索出的数据.

子句(clause): SQL 语句由子句构成，有些子句是必需的，而有的是可选的，一个子句通常由一个关键字和所提供的数据组组成，子句的例子有 SELECT 语句的 FROM 子句。

1. 单列排序: SELECT prod_name FROM products ORDER BY prod_id
2. 多列排序: SELECT prod_name FROM products ORDER BY prod_price,prod_name, 排序完全按照所规定的顺序进行，也就是说仅在多行具有相同的 price 的时候才会根据 name 进行排序
3. 指定排序方向: 数据排序不限于升序顺序(A-Z),还可以使用 ORDER BY 子句以降序(Z-A)顺序排序，为了进行降序排序，必须指定 DESC 关键字. SELECT prod_id, prod_price,prod_name FROM products ORDER BY prod_price DESC. DESC 关键字只能应用到直接位于其前面的列名。如果想要对多个列上进行降序排列，必须对每个列指定DESC关键字。

过滤数据

使用 SELECT 语句的 WHERE子句指定搜索条件,只检索所需的数据需要指定搜索条件,搜索条件也称为过滤条件,在SELECT语句中，数据根据 WHERE 子句中指定的搜索条件进行过滤。WHERE 子句在表名 (FROM子句) 之后给出。

1. SELECT prod_name,prod_price FROM products WHERE prod_price=2.5;
   如果同时使用 ODRDER BY 子句和 WHERE 子句时，应该让 ORDER BY 位于 WHERE 之后。

2. WHERE 子句操作符

   • = 等于
   • <> 不等于
   • != 不等于
   • < 小于
   • <= 小于等于
   • > 大于
   • >= 大于等于
   • BETWEEN 在指定的两个值之间

3. 不匹检查. SELECT prod_name, prod_price FROM product WHERE prod_location <> 'japan', 注意，单引号用来限定字符串，如果将值与串类型的列来进行比较，则需要限定引号，用来与数值列进行比较的值不用引号。

4. 范围值检查: SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 5 AND 10 ,BETWEEN 是闭合查询，也就是意味着包含了起始和结束

5. 空值检查: 在创建表，表设计人员可以指定其中的列是否可以不包含值，在一个列不包含值时，称其为包含空值 NULL, SELECT 语句有一个特殊的 WHERE 子句，可用来检查具有 NULL值的列，这个 WHERE 子句就是 IS NULL 子句

数据过滤

组织 WHERE 子句建立功能更强的更高级的搜索条件，MYSQL 允许多个 WHERE 子句，这些子句可以以两种方式使用: 以 AND 子句的方式或者以 OR 子句的方式使用。操作符用来链接或者改变 WHERE 子句中的子句的关键字，也称为逻辑操作符

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

2. OR操作符: OR 操作符与 AND 操作符不同，它指示 MySQL 检索匹配任意条件SELECT prod_id FROM products WHERE vend_id=1002 OR prod_price=1003

3. 计算次序: SELECT prod_name FROM products WHERE (prod_id=1002 OR prod_id=1003) AND prod_price>10, AND 的计算次序要优先于 OR ，所以需要将 OR

4. IN操作符: IN 操作符用来指定条件范围，范围内的每个条件都可以进行匹配， IN 取合法值的由逗号分隔的清单，全都括在圆括号中。SELECT prod_name FROM products WHERE vend_id IN (1002,1003) ORDER BY prod_name 其实 IN 和 OR 的功能相同。那么为什么要使用 IN 操作符呢?

   • 在使用长的合法选项清单时，IN 操作符的语法更清楚且更直观
   • 在使用 IN 时，计算的次序更容易管理
   • IN 操作符一般比 OR 操作符清单执行更快
   • IN 的最大优点是可以包含其他 SELECT 语句，使得能够更动态地建立 WHERE 子句

5. NOT操作符:NOT 操作符用来否定它之后所跟的任何条件 SELECT prod_name FROM products WHERE vend_id NOT IN(1002,1003)

用通配符进行过滤

使用 LIKE 操作符来进行通配搜索，以便对数据进行复杂过滤

1. LIKE 操作符:利用通配符可创建比较特定数据的搜索模式，来匹配值的一部分的特殊字符，搜索模式: 由字面值，通配符或两者组合构成的搜索条件。为在搜索子句中使用通配符，必须使用 LIKE 操作符，LIKE 指示 MySQL，后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。

   • 百分号(%)通配符:表示任何字符出现的任意次数 SELECT prod_id,prod_name FROM products WHERE prod_name LIKE ‘jet%’
   • 下划线_通配符:另一个有用的通配符是下划线 _，下划线只匹配单个字符串而不是多个字符串。SELECT prod_id FROM products WHERE prod_name LIKE '_ton anvil';
   • 使用通配符的技巧:
     • 不要过渡使用通配符
     • 在确实需要的时候再使用通配符

用正则表达式来进行搜索

带学

创建计算字段

filed 可以与列的意思相同，经常互换使用，不过数据库的列称为一般列，而术语通常用在计算字段的连接上.

1. 拼接字段: concatenate:将值连接到一起构成单个值，解决办法是把两个列拼接起来，在 MySQL 的 SELECT 语句中，可使用 Concat() 函数来拼接两个列。Concat() 拼接串，即把多个串连接起来形成一个较长的串，需要一个或多个指定的串，各个串之间用逗号分隔。
2. 使用别名 SELECT Concat(...) AS newName FROM products
3. 执行算数计算: 计算字段的另一个常见用途是对检索出的数据进行算术计算，

使用数据处理函数

MySQL 支持利用函数来处理数据，函数一般是在数据上执行的，它给数据的转换和处理提供了方便。函数没有 SQL 的可移植性强，多数的 SQL 语句是可移植的，在 SQL 实现之间有差异时，这些差异通常不那么难处理，而函数的可移植性却不强，

使用函数

大多数 SQL 实现支持以下类型的函数:

• 用于处理文本串(删除或填充值，转换值为大写或小写的文本函数)
• 用于在数值数据上进行算术操作(如绝对值，进行代数运算)的数值函数
• 用于处理日期和时间值，并且从这些址中提取特定成分
• 返回 DBMS 正使用的特殊信心(如返回用户登陆信息，检查版本细节)的系统函数

文本处理函数

• RTrim()函数来去除列值右边的空格。
• Upper()函数，将文本转换为大写。
• Left() 函数，返回串左边的字符。

时期和时间处理函数

时间和日期采用相应的数据类型和特殊的格式存储，以便能快速和有效排序或过滤，并且节省物理存储空间，一般，应用程序不使用用来存储日期和时间的格式，因此日期和时间函数被用来统计，读取和处理这些值。

• AddDate() 增加一个日期(天，周等)。
• AddTime() 增加一个时间(时，分等)。等,,,,。

数值处理函数

数值处理函数仅处理数值数据，这些函数一般主要用于代数，三角或几何运算.

• Abs(). 返回一个数的绝对值
• Cos()返回一个角度的余弦
• ...

汇总数据

MySQL 查询可用于检索数据，以便分析和报表生成，这种类型的检索例子有以下几种:

• 确定表中行数(或者满足某个条件或包含某个特定值的行数)
• 获得表中行组的和
• 找出表列(或所有行或某些特定的行)的最大值，最小值和平均值
  这些例子都需要对表中数据汇总。为了检索上述的类型， MySQL 给出了5个聚集函数。聚集函数运行在行组上，计算和返回单个值的函数。

AVG() 函数

AVG() 通过对表中行数计数并计算特定列值之和，求得该列的平均值，AVG()可用来返回所有列的平均值，也可以用来返回特定列或行的平均值。SELECT AVG(price) AS price_avg FROM products;

COUNT() 函数

COUNT() 函数进行计数，可利用 COUNT() 确定表中行的数目或符合特定条件的行的数目。COUNT()函数有两种使用方式

1. 使用 COUNT(*) 对表中行的数目进行计数，不管表列中包含的是空值(NULL)还是非空值。
2. 使用 COUNT(column) 对特定列中具有值的行进行计数，忽略 NULL 值。

MAX() 函数

MAX() 返回指定列中的最大值，MAX()要求指定列名，

MIN() 函数

MIN() 的功能正好与 MAX() 功能相反，他返回指定列的最小值，与 MAX() 一样，必须指定列名

SUM() 函数

SUM() 函数用来返回指定列值的和(总计)，SUM()也可以用来合计计算值

分组数据

使用分组来汇总表内容的子集，使用 SELECT的子句 GROUP BY 和 HAVING 子句。

创建分组

分组是在 SELECT 语句的 GROUP BY 子句中建立的。

SELECT country, COUNT(*) AS num_prod FROM products GROUP BY country;

使用了 GROUP BY子句时，需要知道一些重要的规定。

• GROUP BY 子句可以包含任意数目的列，这使得能对分组进行嵌套，为数据分组提供更细致的控制。

过滤分组

除了能使用 GROUP BY 分组数据外，MySQL 还允许过滤数组，规定包含哪些分组，排除哪些分组。使用 HAVING 过滤分组

SELECT price, COUNT(*) AS price_larger FROM products GROUP BY price HAVING price LIKE '6_000';

SELECT子句顺序:

• SELECT 要返回的列或表达式
• FROM 从中检索数据的表
• WHERE 行级过滤
• GROUP BY 分组说明
• HAVING 组级过滤
• ORDER BY 输出排序顺序
• LIMIT 限制检索的行数

使用子查询

SQL 允许创建子查询，即嵌套在其他查询中的查询

在 SELECT 语句中，子查询总是从内向外处理，在处理上面的 SELECT 语句时， MySQL 实际上执行了两个操作

SELECT order_num FROM orderitems WHERE prod_id = 1;
SELECT cust_id FROM orders WHERE order_num = 1;
SELECT * FROM customers WHERE cust_id = 1;

SELECT * FROM customers WHERE cust_id IN (
  SELECT cust_id
  FROM orders
  WHERE order_num IN (
    SELECT order_num
    FROM orderitems
    WHERE prod_id = 1
  )
);

虽然子查询一般与 IN 操作符结合使用，但也可以用于测试等于(=),不等于(<>) 等。

作为计算字段使用子查询

使用子查询的另一方法是创建字段。

SELECT cust_name, cust_state,(SELECT COUNT(*) FROM orders WHERE cust_id = customers.cust_id) AS orders FROM customers;

联结表

SQL 最强大功能之一就是能在数据检索查询的执行中联结(join)表，联结是利用 SQL 的 SELECT 能执行的最重要的操作，很好地理解联结及其语法是学习 SQL 的一个极为重要的组成部分。

关系表

关系表的设计就是要保证把信息分解成多个表，一类数据一个表，各表通过某些常用的值(即关系设计中的关系)互相关联。

外键:外键为某个表中的一列，他包含另一个表的主键值，从而定义了两个表之间的关系。
可伸缩性:能够适应不断增加的工作量而不失败，

为什么要使用联结

分解数据为多个表中，更方便地处理，并且具有更大的可伸缩性。

使用联结，可以用单条 SELECT 语句检索出数据，联结是一种机制，用来在一条 SELECT 语句中关联表，因此称之为联结，

SELECT vend_name,prod_name,prod_id,prod_price FROM vendors,products WHERE vendors.vend_id = products.vend_id;

WHERE 子句的重要性

请记住:在一条 SELECT 语句中联结几个表时，相应的关系是在运行中构造的，在数据表的定义中不存在能指示 MySQL 如何对表进行联结的东西，你必须自己做这件事情，

笛卡尔积 由没有联结条件的表关系返回的结果为笛卡尔积，检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

内部联结

目前为止所用的联结称为等值联结,他基于两个表之间的相等测试，这种联结也称为内部联结，其实，对于这种联结可以使用稍微不同的语法来明确指定联结的类型，

联结多个表

SQL 对一条 SELECT 语句中可以联结的表的数目没有限制，创建联结的基本规则也相同，首先列出所有表，然后定义表之间的关系。

创建高级联结

组合查询

多数 SQL 查询都只包含一个或多个表中返回数据的单条 SELECT 语句，MySQL 也允许执行多个查询(多条查询)，并将结果作为单个查询结果集返回，这些组合查询通畅称为并 (union) 或复合查询(compound query).
有两种基本情况，其中需要使用组合查询:

• 再单个查询中从不同的表返回类似结构的数据
• 对单个表执行多个查询，按单个查询返回数据

创建组合查询

可用 UNION 操作符来组合数条 SQL 查询，利用 UNION, 可给出多条 SELECT 语句，将它们的结果组合成单个结果集。

使用 UNION : 用法，给出每条 SELECT 语句，在各条 语句之间放上关键字 UNION。
UNION 规则: UNION 必须由两条或两条以上的 SELECT语句组成，语句之间用关键字 UNION 分隔，UNION 中的每个查询必须包含相同的列，表达式或聚集函数。
包含或取消重复的行: UNION 查询时回自动去除重复的行，如果想要返回所有的匹配行，可使用 UNION ALL 而不是 UNION.
对组合查询结果排序: SELECT 语句的输出用 ORDER BY子句排序，在用 UNION组合查询时，只能使用一条 ORDER BY 子句，他必须出现在最后一条 SELECT 语句之后，对于结果集，不存在用一种方式排序一部分，用另一种反式排序另一部分。

全文本搜索

为了进行全文本搜索，必须索引被搜索的列，而且随着数据的改变不断的被重新索引，在对表列进行适当设计后，MySQL 会自动进行所有的索引和重新索引。在索引之后，SELECT 与 Match() 和 Against() 一起使用以时机执行搜索。