1、创建一个表
- 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;
其中，AUTO_INCREMENT表示该列是自动增长的，char存放的是固定长度的字符串，CHAR(50)表示存放的字符长度为50，PRIMARY KEY (cust_id)表示将cust_id设置成为主键。ENGINE=InnoDB，表示将存储引擎设为InnoDB。
关于InnoDB：
InnoDB 是 MySQL 上第一个提供外键约束的数据存储引擎，除了提供事务处理外，InnoDB 还支持行锁，提供和 Oracle 一样的一致性的不加锁读取，能增加并发读的用户数量并提高性能，不会增加锁的数量。InnoDB 的设计目标是处理大容量数据时最大化性能，它的 CPU 利用率是其他所有基于磁盘的关系数据库引擎中最有效率的。
InnoDB 是一套放在 MySQL 后台的完整数据库系统，InnoDB 有它自己的缓冲池，能缓冲数据和索引，InnoDB 还把数据和索引存放在表空间里面，可能包含好几个文件，这和 MyISAM 表完全不同，在 MyISAM 中，表被存放在单独的文件中，InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB。
同理可创建orderitems表（存放每个订单中的实际物品）、orders表（存放客户订单）、products表（存放产品目录，每行表示一个产品）、vendors表（存放生产产品的供应商）
- 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 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 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 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;
2、添加数据到customers表中，这里采用列=值的方式，这样可以保证当表中列的顺序改变时，插入数据也不会受到影响。
- 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');
同理，可以给orderitems表、orders表、products表、vendors表插入数据。
3、显示所有数据库
4、检索
（1）检索单列
如从products表中检索出一个名为prod_name的列。
- SELECT prod_name FROM products;
输出：
(2)检索多列
- SELECT prod_id,prod_name,prod_price FROM products;
输出
(3)检索所有列
(4)排序检索
- SELECT prod_name FROM products ORDER BY prod_name;
注意事项：
a. 在使用ORDER BY字句时，应保证它是SELECT语句的最后一条字句
b. 可以通过非选择的列来进行排序，如
SELECT prod_id FROM products ORDERBY prod_name;
Prod_id是选择的列，prod_name是非选择的列。
c. 可以按多个列进行排序，如
SELECTprod_id,prod_price,prod_name FROM products ORDER BY prod_price,prod_name;
d. 可以按照列的相对位置进行排序
SELECTprod_id,prod_price,prod_name FROM products ORDER BY 2,3;
此时，排序只能按照选择的列进行排序，而且增加了错用列名进行排序的可能性。
e. 默认排序为升序排序，降序排序需要指定DESC关键字，放在所选择的列后面。
SELECT prod_name FROM productsORDER BY prod_name DESC;
如果需要在多个列上进行降序排序，必须对每个列指定DESC。
(5) 过滤检索
a. 单条件过滤
WHERE通过特定的搜索条件对数据进行过滤，筛选出特定的行。
- SELECT prod_name,prod_price FROM products WHERE prod_price < 3.49;
WHERE字句支持=, <>, !=, <, <=, >, >=,BETWEEN,IS NULL操作
注意，在WHERE字句中使用字符串进行过滤时，要用单引号限定字符串。如：
- SELECT vend_id,prod_name FROM products WHERE prod_id != 'FB';
- SELECT vend_id,prod_name,prod_id FROM products WHERE prod_id != 'FB';
b. 多条件过滤（组合过滤）
检索出满足所有条件的行，各条件用AND连接，等同于&&
- SELECT prod_id,prod_price,prod_name,vend_id FROM products WHERE vend_id = 1001 AND prod_price < 10;
输出：
检索满足任一条件的行，各条件之间用OR来连接，等同于||
- SELECT prod_id,prod_price,prod_name,vend_id FROM products WHERE vend_id = 1001 OR prod_price < 10;
注意：当同时使用AND和OR语句时，应当使用括号来规定计算次序，因为默认AND的优先级比OR高，SQL会先处理AND语句。如检索出价格在10美元以下的，由厂商1001或1002生产的所有产品。
- SELECT prod_id,prod_price,prod_name,vend_id FROM products
- WHERE vend_id = 1001 OR vend_id = 1002 AND prod_price < 10;
输出：
在该结果中，出现了价格在10美元以上的产品，这是因为在没加括号的情况下，SQL先处理了AND导致的。更改为
- SELECT prod_id,prod_price,prod_name,vend_id FROM products
- WHERE (vend_id = 1001 OR vend_id = 1002) AND prod_price < 10;
即可得到正确的结果
另一种多条件过滤-操作符IN（功能相当于OR），查询出符合IN中任一条件的数据。
- SELECT prod_id,prod_price,prod_name,vend_id FROM products WHERE vend_id IN(1001,1002);
优点：
IN操作符一般比OR执行更快。
IN可以包含其他SELECT语句，使得能够更动态地创建WHERE字句。
否定查询-NOT
NOT可放在列名的前面，也可以放在列名的后面。如下：
- SELECT prod_id,prod_price,prod_name,vend_id FROM products WHERE vend_id NOT IN(1001,1002);
- SELECT prod_id,prod_price,prod_name,vend_id FROM products WHERE NOT vend_id IN(1001,1002);
使用LIKE进行模糊查询
“%”代表任意字符，”_”代表单个字符。
- SELECT prod_id,prod_price,prod_name,vend_id FROM products WHERE prod_id LIKE 'F%';
- SELECT prod_id,prod_price,prod_name,vend_id FROM products WHERE prod_id LIKE 'F_';
通配符搜索处理时间较长，所以能用其他操作符达到相同的目的，则不建议使用通配符。
去重复查询DISTINCT
- SELECT DISTINCT order_num FROM orderitems;
(7) 计算字段查询
1、拼接：将多个值联结到一起构成单个值
如将vend_name和vend_country拼接。
- SELECT CONCAT(vend_name,' (',vend_country,')') FROM vendors ORDER BY vend_name;
使用别名AS
- SELECT CONCAT(vend_name,' (',vend_country,')') AS vend_title FROM vendors ORDER BY vend_name;
使用别名的好处：1)给计算字段赋予别名，可以让客户机方便使用
2)使得列的含义更容易理解
(8) 使用函数进行查询
文本处理函数:
Left(str,len);
str为一个字符串，len表示从左边开始截取，截取字符串的长度。
Right(str,len)
同left(str,len)，只是从右边开始截取。
SUBSTRING(str,pos);
等同于SUBSTRING(str FROM pos)，str为一个字符串,pos为从哪个位置开始截取，直到末尾，如果pos为负数，则表示从倒数开始截取。
SUBSTRING(str,pos,len)；
等同于SUBSTRING(str FROM pos FOR len)表示从pos开始截取，截取长度为len。
SUBSTRING_INDEX(str,delim,count);
delim为标识字符，若count为正数，表示截取第count个标识符之前的字符串，若count为负数，表示截取倒数第count个字符之后的字符串。
Length();计算字符串长度
Lower();大写转小写
SOUNDEX();返回串的发音比较像的串。
如输入：
- SELECT vend_name,UPPER(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;
聚集函数:
AVG(),COUNT(),MAX(),MIN(),SUM()
如计算所有产品的价格的平均值：
- SELECT AVG(prod_price) AS avg_price FROM products;
(9)分组查询GROUP BY
查询按某个字段分组后，各组的数量，如
- SELECT vend_id,COUNT(*) AS num_prods FROM products GROUP BY vend_id;
查询出了产品中，每个厂商生产的产品数量。
输出：
与WITH ROLLUP一起使用
- SELECT vend_id,COUNT(*) AS num_prods FROM products GROUP BY vend_id WITH ROLLUP;
与HAVING一起使用，过滤分组
WHERE过滤行，HAVING过滤分组，如查询订单数大于等于2的顾客。
- SELECT cust_id,COUNT(*) AS '订单数' FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;
注意：1) Group by字句可以包含任意数目的列
2) 如果在Group by中使用了嵌套分组，则数据将在最后规定的分组上进行汇总
3) Group by字句中列出的列必须是select中的列或者是有效表达式，但不能是聚集函数。
4) Select语句中的每个列都必须在group by字句中给出
5) 如果分组列中具有null值，则null将作为一个分组返回，如果列中有多行null值，它们将被分为一组。
6) Group by字句必须出现在where字句之后，order by字句之前
946
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
