MySQL中定义fk语句_MySQL基础篇/第3篇：MySQL基本操作语句.md · qwqoo/MySQL-Review

本文链接：https://blog.csdn.net/weixin_29553791/article/details/113443075

### 第3篇：MySQL基本操作语句

- MySQL基础操作

#### 排序检索数据

- 之前的数据没有进行排序，其是按照默认在数据表中的数据返回的

- SELECT语句的ORDER BY 子句进行排序

```mysql

# 没有特定的顺序查询 (虽然结果看起来是有序的，是因为数据表中就是这个顺序)

SELECT username FROM test;

```

![1608379669271](images/1608379669271.png)

- **子句：** SQL语句由子句构成，有些子句是必须的，有些则是可选的。一个子句通常由一个关键字和所提供的数据组成。子句的例子有SELECT语句的FROM子句。

- 如果要明确排序检索出来的数据，则使用 ORDER BY 子句

- 例子如下

```mysql

# 默认是升序的省略了 asc 下面的等价

SELECT username FROM test ORDER BY username;

SELECT username FROM test ORDER BY username asc;

# 后面加上 desc 则是降序

SELECT username FROM test ORDER BY username desc;

```

![1608379832768](images/1608379832768.png)

![1608379895721](images/1608379895721.png)

- 也可以按照多个列进行排序

```mysql

# 先按照 username 降序，如果username一样，再按照id升序

SELECT id,username,password FROM test ORDER BY username desc,id asc;

# 先按照 username 升序，如果username一样，再按照id降序

SELECT id,username,password FROM test ORDER BY username asc,id asc;

# 在多个列上降序排序如果想在多个列上进行降序排序，必须对每个列指定DESC关键字。

```

![1608380043752](images/1608380043752.png)

- ORDER BY 和 LIMIT 的组合

```mysql

# 先按照 username 升序，如果username一样，再按照id降序，只取出一条结果

SELECT id,username,password FROM test ORDER BY username asc,id asc LIMIT 1;

```

![1608380202791](images/1608380202791.png)

#### 过滤数据

- 使用SELECT的WHERE子句搜索指定的搜索条件

```mysql

# 查询名字为 hello 的人

SELECT id,username,password FROM test WHERE username = 'hello';

```

![1608380385778](images/1608380385778.png)

- 数据的过滤也可以在应用中，如Java项目中，先拿到数据，然后在Java内存中进行数据的过滤。

- WHERE 子句的位置，在同时使用ORDER BY 和 WHERE子句的时候，应该让ORDER BY 在WHERE之后，否则会报错

```mysql

# 正确写法

SELECT id,username,password FROM test WHERE username = 'hello' ORDER by username asc;

# 错误写法

SELECT id,username,password FROM test ORDER by username asc WHERE username = 'hello';

```

![1608380754572](images/1608380754572.png)

- WHERE 子句的操作符

| 操作符 | 说明 |

| --------------- | ------------------ |

| = | 等于 |

| <> | 不等于 |

| ! | 不等于 |

| < | 小于 |

| <= | 小于等于 |

| > | 大于 |

| >= | 大于等于 |

| BETWEEN xxx AND | 在指定的两个值之间 |

- 测试SQL

```mysql

SELECT id,username,password FROM test WHERE id >= 2;

SELECT id,username,password FROM test WHERE id >= 2 AND id <=5;

SELECT id,username,password FROM test WHERE id BETWEEN 2 AND 5;

```

![1608381106463](images/1608381106463.png)

- 空值检查

```mysql

SELECT username,password FROM test WHERE username IS NOT NULL;

```

![1608381190402](images/1608381190402.png)

#### 数据过滤

- WHERE 子句和NOT、IN、AND 的操作

- WHERE子句中的NOT操作符有且只有一个功能，那就是否定它之后所跟的任何条件

```mysql

# AND操作符

SELECT id,username,password FROM test WHERE id >= 2 AND id <=5;

# OR 操作符

SELECT id,username,password FROM test WHERE id = 2 OR id =5;

```

![1608381596773](images/1608381596773.png)

- 组合AND和OR会带来一个问题。其会有一个计算的优先级，也就是说 AND优先于OR先执行

```mysql

# 从下面的查询结果来看，需要查询的数据 id > 1，但是等于 id=1 的结果也被查询出来，原因就是AND会优先于OR执行

SELECT id,username,password FROM test WHERE username = 'hello' OR username = 'hello1' AND id > 1;

# 合理的操作

SELECT id,username,password FROM test WHERE (username = 'hello' OR username = 'hello1' )AND id > 1;

```

![1608382303114](images/1608382303114.png)

![1608382422241](images/1608382422241.png)

- IN 操作符的使用

```mysql

SELECT id,username FROM test WHERE id IN (1,2,3);

SELECT id,username FROM test WHERE id NOT IN (1,2,3);

SELECT id,username FROM test WHERE id NOT IN (1,2,3) ORDER BY username;

SELECT id,username FROM test WHERE id NOT IN (1,2,3) ORDER BY username desc;

```

![1608382503368](images/1608382503368.png)

![1608382857893](images/1608382857893.png)

#### 使用通配符进行过滤

- LIKE 通配符 LIKE 是一个关键字

- **通配符(wildcard)** 用来匹配值的一部分的特殊字符。

- **搜索模式(search pattern)** 由字面值、通配符或者两者组合构成的搜索条件

- % 通配符，表示任意字符出现的任意次数

```mysql

SELECT id,username,password FROM test WHERE username LIKE 'he%';

SELECT id,username,password FROM test WHERE username LIKE '%o';

# 搜索内容不区分大小写，但是可以进行配置，是否区分大小写，这里涉及到数据库的排序方式

SELECT id,username,password FROM test WHERE username LIKE 'He%';

# 其他操作搜索 %e% 表示匹配任何包含e的文本的值，无论前面后面是什么

SELECT id,username,password FROM test WHERE username LIKE '%e%';

# 找出h开头，1结尾的内容

SELECT id,username,password FROM test WHERE username LIKE 'h%1';

```

![1608383403160](images/1608383403160.png)

![1608383714138](images/1608383714138.png)

- 下划线通配符 _ 其只能匹配一个字符

```mysql

SELECT id,username,password FROM test WHERE username LIKE '_ello';

```

![1608383862988](images/1608383862988.png)

- **建议：**

- 不要过度使用通配符

- 把搜索模式放在开头，也就是把 % 或者 _ 放在搜索条件开头，是最慢的，因为其不会走索引(之后会说)

- 需要注意通配符的位置，不要放错了

#### 用正则表达式进行搜索

- 正则表达式是用来匹配文本的特殊的串(字符集合)

```mysql

# 查询姓名包含 ello 的所有的行

SELECT id,username,password FROM test WHERE username REGEXP 'ello';

# 简单的正则表达式

SELECT id,username,password FROM test WHERE username REGEXP '.llo';

# 正则表达式的性能不稳定，有时候可能会更差，本人不推荐使用

# 正则表达式匹配不区分大小写，如果想区分大小写，可以这样写

SELECT id,username,password FROM test WHERE username REGEXP BINARY '.llo';

```

![1608384616352](images/1608384616352.png)

![1608384718801](images/1608384718801.png)

![1608384978948](images/1608384978948.png)

- 使用正则表达式进行OR匹配

```mysql

# 匹配 lo 或者 e

SELECT id,username,password FROM test WHERE username REGEXP BINARY 'lo|e';

```

![1608385169773](images/1608385169773.png)

- 匹配几个字符之一

```mysql

SELECT id,username,password FROM test WHERE username REGEXP '[llo] 1';

SELECT id,username,password FROM test WHERE username REGEXP '[llo]1';

# 上面的正则表达式等价于

# [llo] 1 => l 1、l 1、o 1

# [llo]1 => l1、l1、o1

# [] 是一种特殊形式的 OR

```

![1608385533826](images/1608385533826.png)

- 匹配范围

```mysql

SELECT id,username,password FROM test WHERE username REGEXP '[a-z]1';

SELECT id,username,password FROM test WHERE username REGEXP '[A-Z]1';

SELECT id,username,password FROM test WHERE username REGEXP BINARY '[A-Z]1';

```

![1608385724016](images/1608385724016.png)

- 匹配特殊字符，如果想匹配 .、[]、|和-等特市字符，怎么搜索？

```mysql

# 这个是通配符，不是我们想要的，所以需要转义

SELECT id,username,password FROM test WHERE username REGEXP '.';

# 在需要转义之前加上 \\

SELECT id,username,password FROM test WHERE username REGEXP '\\.';

```

![1608389403799](images/1608389403799.png)

| 类 | 说明 |

| ---------- | ------------------------------------------------- |

| [:alnum:] | 任意字母和数字(同[a-zA-Z0-9]) |

| [:alpha:] | 任意字符(同[a-zA-Z]) |

| [:blank:] | 空格和制表(同[\\t]) |

| [:cntrl:] | ASCII控制字符(ASCII 0到31和127) |

| [:digit:] | 任意数字(同[0-9]) |

| [:graph:] | 与[:print:]相同，但不包括空格 |

| [:lower:] | 任意小写字母(同[a-z]) |

| [:print:] | 任意可打印字符 |

| [:punct:] | 既不在[:alnum:]又不在[:cntrl:]中的任意字符 |

| [:space:] | 包括空格在内的任意空白字符(同[\\f\\n\\r\\t\\v]) |

| [:upper:] | 任意大写字母(同[A-Z]) |

| [:xdigit:] | 任意十六进制数字(同[a-fA-F0-9]) |

| 元字符 | 说明 |

| ------ | ---------------------------- |

| * | 0个或多个匹配 |

| + | 1个或多个匹配(等于{1,}) |

| ? | 0个或1个匹配(等于{0,1}) |

| {n} | 指定数目的匹配 |

| {n,} | 不少于指定数目的匹配 |

| {n,m} | 匹配数目的范围(m不超过255) |

- 例子

```mysql

SELECT id,username,password FROM test WHERE username REGEXP '\$[0-9] hello?\$';

# 正则表达式\$[0-9] hello?\$需要解说一下。\$匹配)，[0-9]匹配任意数字，hello?匹配hello和hello(s后的?使s可选，因为?匹配它前面的任何字符的0次或1次出现)，\$匹配) 没有?，匹配hello和hellos会非常困难

```

- 例子

```mysql

# 匹配连载一起的4个数字

SELECT id,username,password FROM test WHERE username REGEXP '[:digit:]{4}';

```

| 元字符 | 说明 |

| ------- | ---------- |

| ^ | 文本的开始 |

| $ | 文本的结尾 |

| [[:<:>

| [[:>:]] | 词的结尾 |

- 如果你想找出以一个数(包括以小数点开始的数)开始的所有产品，怎么办？简单搜索[0-9\\.](或[[:digit:]\\.])不行，因为它将在文本内任意位置查找匹配。解决办法是使用^定位符

```mysql

SELECT id,username,password FROM test WHERE username REGEXP '^[0-9\\.]';

# ^匹配串的开始。因此，^[0-9\\.]只在.或任意数字为串中第一个字符时才匹配它们

# ^的双重用途 ^有两种用法。在集合中(用[和]定义)，用它来否定该集合，否则，用来指串的开始处。

```

#### 创建计算字段

- 存储在表中的数据都不是应用程序所需要的。我们需要直接从数据库中检索出转换、计算或格式化过的数据；而不是检索出数据，然后再在客户机应用程序或报告程序中重新格式化。这就是计算字段的作用。

- **字段(field)** 基本上与列(column)的意思相同，经常互换使用，不过数据库列一般称为列，而术语字段通常用在计算字段的连接上。

- 性能问题：可在SQL语句内完成的许多转换和格式化工作都可以直接在客户机应用程序内完成。但一般来说，在数据库服务器上完成这些操作比在客户机中完成要快得多，因为DBMS是设计来快速有效地完成这种处理的。

- 拼接字段(concatenate)：将值联结在一起构成单个值。使用MySQL内置函数 Concat() 拼接两个列。

```mysql

SELECT Concat(id,'(',username,')') FROM test;

```

![1608425678843](images/1608425678843.png)

- 去除空格的函数 RTrim() 去除右侧的空格，LTrim() 去除左边的空格，Trim() 去除空格

``` mysql

SELECT Concat(RTrim(username)) FROM test;

```

![1608425797328](images/1608425797328.png)

- 使用别名 as 或者省略

```mysql

SELECT Concat(RTrim(username)) as name FROM test;

# as 可以省略，但是必须加上空格

SELECT Concat(RTrim(username)) name FROM test;

```

![1608426314342](images/1608426314342.png)

- 执行算术计算如 + 、- 、* 、/ MySQL 都是支持的

```mysql

# 注意，下面的计算结果毫无意义，只是为了演示语法，一般的，id不参与计算

SELECT id*10 10TimesId FROM test where id = 1;

```

![1608426473994](images/1608426473994.png)

#### 使用数据处理函数

- MySQL支持函数，SQL利用函数来处理数据

- SQL包括的函数

- 用于处理文本(删除、拼接、去除空格、转换值为大写或者小写)

- 用于在数值上进行算术操作

- 用于处理日期和时间值并且从这些值中提取特定成分的日期和时间函数

- 返回DBMS正使用的特殊信息的系统函数

- 文本处理函数

```mysql

SELECT username,Upper(username) FROM test;

```

![1608502197427](images/1608502197427.png)

| 函数 | 说明 |

| ----------- | ----------------- |

| Left() | 返回串左边的字符 |

| Length() | 返回串的长度 |

| Locate() | 找出串的一个子串 |

| Lower() | 将串转换为小写 |

| LTrim() | 去掉串左边的空格 |

| Right() | 返回串右边的字符 |

| RTrim() | 去掉串右边的空格 |

| Soundex() | 返回串的SOUNDEX值 |

| SubString() | 返回子串的字符 |

| Upper() | Upper() |

- 注意：SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节，使得能对串进行发音比较而不是字母比较。虽然SOUNDEX不是SQL概念。

- 日期和时间处理函数

| 函数 | 说明 |

| ------------- | ------------------------------ |

| AddDate() | 增加一个日期(天，周等) |

| AddTime() | 增加一个时间(时，分等) |

| CurDate() | 返回当前日期 |

| CurTime() | 返回当前时间 |

| Date() | 返回日期时间的日期部分 |

| DateDiff() | 计算两个日期之差 |

| Date_Add() | 高度灵活的日期运算函数 |

| Date_Format() | 返回一个格式化的日期或者字符串 |

| Day() | 返回一个日期的天数部分 |

| DayOfWeek() | 对于一个日期，返回对应的星期几 |

| Hour() | 返回一个时间的小时部分 |

| Minute() | 返回一个时间的分钟部分 |

| Month() | 返回一个日期的月份部分 |

| Now() | 返回当前日期和时间 |

| Second() | 返回一个时间的秒部分 |

| Time() | 返回一个日期时间的时间部分 |

| Year() | 返回一个日期的年份部分 |

- 案例

```mysql

SELECT id,username FROM test WHERE create_time = '2020-12-19 08:52:48';

```

![1608502960371](images/1608502960371.png)

- 使用部分日期进行比较得出结果

```mysql

SELECT id,username FROM test WHERE Date(create_time) = '2020-12-19';

```

![1608503058314](images/1608503058314.png)

- 如果想检索某年某月的数据

```mysql

# 第一种方法

SELECT id,username FROM test WHERE Date(create_time) BETWEEN '2020-12-01' AND '2020-12-31';

# 第二种方法

SELECT id,username FROM test WHERE Year(create_time) = 2020 AND Month(create_time) = 12;

```

![1608503502121](images/1608503502121.png)

- 数值处理函数

| 函数 | 说明 |

| ------ | ------------------ |

| Abs() | 返回一个数的绝对值 |

| Cos() | 返回一个角度的余弦 |

| Exp() | 返回一个数的指数值 |

| Mod() | 返回除操作的余数 |

| Pi() | 返回圆周率 |

| Rand() | 返回一个随机数 |

| Sin() | 返回一个角度的正弦 |

| Sqrt() | 返回一个数的平方根 |

| Tan() | 返回一个角度的正切 |

#### 聚集函数

- 聚集函数(aggregate function) 运行在行组上，计算和返回单个值的函数

| 函数 | 说明 |

| ------- | ---------------- |

| AVG() | 返回某列的平均值 |

| COUNT() | 返回某列的行数 |

| MAX() | 返回某列的最大值 |

| MIN() | 返回某列的最小值 |

| SUM() | 返回某列的和 |

- 注意点：AVG() 会忽略空值的行。COUNT(\*) 和 COUNT(某列)的区别 COUNT(\*)对表中行的数目计数，无论表列中包含的是不是空值。COUNT(某列)对列中具有值得行进行计数，忽略NULL值。MAX(某列) 忽略列值为NULL的行

- 以上的聚集函数都可以这样使用

- 对所有的行执行计算，指定ALL参数或者不给参数(因为ALL是默认行为)

- 只包含不同的值，使用 DISTINCT 参数

```mysql

# 只是以此为例

SELECT id,AVG(DISTINCT id) FROM test;

```

#### 分组数据

- 数据分组：关键语句 GROUP BY

```mysql

SELECT COUNT(username),username FROM test GROUP BY username;

```

![1608504413347](images/1608504413347.png)

- 规定

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

- 如果在GROUP BY 子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组的时候，指定的所有的列都是一起计算的(所以不能从个别的列取回数据)

- GROUP BY 子句中列出的每个列都必须是检索列或者是有效的表达式(但是不能是聚集函数)，如果SELECT 中使用表达式，则必须在GROUP BY 子句中指定相同的表达式，不能使用别名。

- 除了聚集计算语句外，SELECT 语句中的每个列都必须在GROUP BY 子句中给出。

- 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为1组。

- GROUP BY 子句必须出现在WHERE子句之后，ORDER BY 子句之前

- 使用ROLLUP 使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别(针对每个分组)的值

```mysql

SELECT COUNT(username),username FROM test GROUP BY username WITH ROLLUP;

```

![1608505306149](images/1608505306149.png)

- **过滤分组：** 规定包含哪些分组，不包含哪些分组，例如：得到至少有2个订单的所有的顾客

- WHERE子句的作用，过滤的是行而不是分组，WHERE 没有分组的概念

- HAVING 子句，HAVING子句非常类似于WHERE。事实上，目前学过的所有类型的WHERE子句都可以使用HAVING来替代，唯一的差别是WHERE过滤行，HAVING过滤分组。HAVING支持所有的的WHERE操作符。

```mysql

SELECT COUNT(username),username FROM test GROUP BY username WITH ROLLUP HAVING COUNT(username) >=2 ;

```

![1608505704425](images/1608505704425.png)

- HAVING和WHERE的差别这里有另一种理解方法，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要的区别，WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。

```mysql

SELECT COUNT(username),username FROM test WHERE id >2 GROUP BY username WITH ROLLUP HAVING COUNT(username) >=2 ;

```

![1608505777687](images/1608505777687.png)

- 分组和排序

| ORDER BY | GROUP BY |

| -------------------------------------------- | -------------------------------------------------------- |

| 排序产生的输出 | 分组行。但输出可能不是分组的顺序 |

| 任意列都可以使用(甚至非选择的列也可以使用) | 只可能使用选择列或表达式列，而且必须使用每个选择列表达式 |

| 不一定需要 | 如果与聚集函数一起使用列(或表达式)，则必须使用 |

- **SELECT 子句顺序**

| 子句 | 说明 | 是否必须使用 |

| -------- | -------------------- | ------------------------ |

| SELECT | 要返回的列或者表达式 | 是 |

| FROM | 从中检索数据的表 | 仅在从表选择数据时使用 |

| WHERE | 行级过滤 | 否 |

| GROUP BY | 分组说明 | 仅在按组计算聚集时候使用 |

| HAVING | 组级过滤 | 否 |

| ORDER BY | 输出排序顺序 | 否 |

| LIMIT | 要检索的行数 | 否 |

#### 使用子查询

- 查询(query)任何SQL语句都是查询。但此术语一般指SELECT语句。

- **创建数据表和数据(在文末案例SQL数据)**

- 订单存储在两个表中。对于包含订单号、客户ID、订单日期的每个订单，orders表存储一行。各订单的物品存储在相关的orderitems表中。orders表不存储客户信息。它只存储客户的ID。实际的客户信息存储在customers表中。

- 现在，假如需要列出订购物品TNT2的所有客户，应该怎样检索？下面列出具体的步骤。

- 检索包含物品 TNT2 的所有订单的编号

- 检索具有前一步骤列出的订单编号的所有的客户的ID

- 检索上一步骤返回的所有客户ID的客户信息

- 上述的每个步骤都可以单独使用一个查询来执行，可以把一条SELECT语句返回结果用于另一条SELECT的WHERE语句

- 也可以使用子查询语句把3个查询组合成一条语句

```mysql

# 第一个查询

SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';

# 第二个查询

SELECT cust_id FROM orders WHERE order_num IN (20005,20007);

# 合并之后

SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

```

![1608507511461](images/1608507511461.png)

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

- 首先，它执行下面的查询：`SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'` 此查询返回两个订单号：20005和20007。

- 然后，这两个值以IN操作符要求的逗号分隔的格式传递给外部查询的WHERE子句。外部查询变成：`SELECT cust_id FROM orders WHERE order_num IN (20005,20007);`

- 现在得到了订购物品TNT2的所有客户的ID。下一步是检索这些客户ID的客户信息。检索两列的SQL语句为：

```mysql

SELECT cust_name,cust_contact FROM customers WHERE cust_id IN (10001,10004);

```

![1608507684688](images/1608507684688.png)

- 合并SQL语句进行查询

```mysql

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'));

```

![1608507759376](images/1608507759376.png)

- 为了执行上述SELECT语句，MySQL实际上必须执行3条SELECT语句。最里边的子查询返回订单号列表，此列表用于其外面的子查询的WHERE子句。外面的子查询返回客户ID列表，此客户ID列表用于最外层查询的WHERE子句。最外层查询确实返回所需的数据。\

- **作为计算字段使用子查询**

- 假如需要显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中。

- 为了执行这个操作，遵循下面的步骤

- 从customers 表中检索客户列表

- 对于检索处的每个客户，统计其在orders表中的订单数目

```mysql

# 对客户10001的订单进行计数

SELECT COUNT(*) AS orders FROM orders WHERE cust_id = 10001;

# 对每个客户执行COUNT(*)计算，应该将COUNT(*)作为一个子查询

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

```

![1608508166577](images/1608508166577.png)

- 相关子查询(correlated subquery)涉及外部查询的子查询。这种类型的子查询称为相关子查询。任何时候只要列名可能有多义性，就必须使用这种语法(表名和列名由一个句点分隔)。这是为了确定某个数据确实是来自某张表，避免了笛卡尔积。

#### 案例SQL数据

```mysql

########################################

# MySQL Crash Course

# http://www.forta.com/books/0672327120/

# Example table creation scripts

########################################

########################

# Create customers table

########################

CREATE TABLE customers

(

cust_id int NOT NULL AUTO_INCREMENT,

cust_name char(50) NOT NULL ,

cust_address char(50) NULL ,

cust_city char(50) NULL ,

cust_state char(5) NULL ,

cust_zip char(10) NULL ,

cust_country char(50) NULL ,

cust_contact char(50) NULL ,

cust_email char(255) NULL ,

PRIMARY KEY (cust_id)

) ENGINE=InnoDB;

#########################

# Create orderitems table

#########################

CREATE TABLE orderitems

(

order_num int NOT NULL ,

order_item int NOT NULL ,

prod_id char(10) NOT NULL ,

quantity int NOT NULL ,

item_price decimal(8,2) NOT NULL ,

PRIMARY KEY (order_num, order_item)

) ENGINE=InnoDB;

#####################

# Create orders table

#####################

CREATE TABLE orders

(

order_num int NOT NULL AUTO_INCREMENT,

order_date datetime NOT NULL ,

cust_id int NOT NULL ,

PRIMARY KEY (order_num)

) ENGINE=InnoDB;

#######################

# Create products table

#######################

CREATE TABLE products

(

prod_id char(10) NOT NULL,

vend_id int NOT NULL ,

prod_name char(255) NOT NULL ,

prod_price decimal(8,2) NOT NULL ,

prod_desc text NULL ,

PRIMARY KEY(prod_id)

) ENGINE=InnoDB;

######################

# Create vendors table

######################

CREATE TABLE vendors

(

vend_id int NOT NULL AUTO_INCREMENT,

vend_name char(50) NOT NULL ,

vend_address char(50) NULL ,

vend_city char(50) NULL ,

vend_state char(5) NULL ,

vend_zip char(10) NULL ,

vend_country char(50) NULL ,

PRIMARY KEY (vend_id)

) ENGINE=InnoDB;

###########################

# Create productnotes table

###########################

CREATE TABLE productnotes

(

note_id int NOT NULL AUTO_INCREMENT,

prod_id char(10) NOT NULL,

note_date datetime NOT NULL,

note_text text NULL ,

PRIMARY KEY(note_id),

FULLTEXT(note_text)

) ENGINE=MyISAM;

#####################

# Define foreign keys

#####################

ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);

ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);

ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);

ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

```

```mysql

########################################

# MySQL Crash Course

# http://www.forta.com/books/0672327120/

# Example table population scripts

########################################

##########################

# Populate customers table

##########################

INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)

VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com');

INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)

VALUES(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse');

INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)

VALUES(10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'rabbit@wascally.com');

INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)

VALUES(10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', 'sam@yosemite.com');

INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)

VALUES(10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd');

########################

# Populate vendors table

########################

INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)

VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA');

INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)

VALUES(1002,'LT Supplies','500 Park Street','Anytown','OH','44333', 'USA');

INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)

VALUES(1003,'ACME','555 High Street','Los Angeles','CA','90046', 'USA');

INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)

VALUES(1004,'Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');

INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)

VALUES(1005,'Jet Set','42 Galaxy Road','London', NULL,'N16 6PS', 'England');

INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)

VALUES(1006,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'45678', 'France');

#########################

# Populate products table

#########################