/*顾客表*/ 

        CREATE TABLE customers 

        ( 

        c_id int NOT NULL AUTO_INCREMENT, 

        c_name char(50) NOT NULL, 

        c_address char(50) NULL, 

        c_city char(50) NULL, 

        c_zip char(10) NULL, 

        c_contact char(50) NULL, 

        c_email char(255) NULL, 

        PRIMARY KEY (c_id) 

        ); 

        /*水果表*/ 

        /*供货方表*/CREATE TABLE fruits 

        ( 

        f_id char(10) NOT NULL, 

        s_id INT NOT NULL, 

        f_name char(255) NOT NULL, 

        f_price decimal(8,2) NOT NULL, 

        PRIMARY KEY(f_id) 

        ) ; 

        CREATE TABLE suppliers 

        ( 

        s_id int NOT NULL AUTO_INCREMENT, 

        s_name char(50) NOT NULL, 

        s_city char(50) NULL, 

        s_zip char(10) NULL, 

        s_call CHAR(50) NOT NULL, 

        PRIMARY KEY (s_id) 

        ) ; 

        /*订单明细表*/ 

        CREATE TABLE orderitems 

        ( 

        o_num int NOT NULL, 

        o_item int NOT NULL, 

        f_id char(10) NOT NULL, 

        quantity int NOT NULL, 

        item_price decimal(8,2) NOT NULL, 

        PRIMARY KEY (o_num,o_item) ) ; 

        /*订单表*/ 

        CREATE TABLE orders 

        ( 

        o_num int NOT NULL AUTO_INCREMENT, 

        o_date datetime NOT NULL, 

        c_id int NOT NULL, 

        PRIMARY KEY (o_num) 

        ) ; 

        /*--------------------------插入数据--------------------------*/ 

        INSERT INTO customers(c_id, c_name, c_address, c_city, c_zip, c_contact, c_email) 

        VALUES(10001, 'RedHook', '200 Street ', 'Tianjin', '300000', 'LiMing', 'LMing@163.com'), 

        (10002, 'Stars', '333 Fromage Lane', 'Dalian', '116000', 'Zhangbo','Jerry@hotmail.com'), 

        (10003, 'Netbhood', '1 Sunny Place', 'Qingdao', '266000', 'LuoCong', NULL), 

        (10004, 'JOTO', '829 Riverside Drive', 'Haikou', '570000', 'YangShan', 'sam@hotmail.com'); 

        INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES('a1', 101,'apple',5.2), 

        ('b1',101,'blackberry', 10.2), 

        ('bs1',102,'orange', 11.2), 

        ('bs2',105,'melon',8.2), 

        ('t1',102,'banana', 10.3), 

        ('t2',102,'grape', 5.3), 

        ('o2',103,'coconut', 9.2), 

        ('c0',101,'cherry', 3.2), 

        ('a2',103, 'apricot',2.2), 

        ('l2',104,'lemon', 6.4), 

        ('b2',104,'berry', 7.6), 

        ('m1',106,'mango', 15.6), 

        ('m2',105,'xbabay', 2.6), 

        ('t4',107,'xbababa', 3.6), 

        ('m3',105,'xxtt', 11.6), 

        ('b5',107,'xxxx', 3.6); 

        INSERT INTO suppliers(s_id, s_name,s_city, s_zip, s_call) VALUES(101,'FastFruit Inc.','Tianjin','300000','48075'), (102,'LT Supplies','Chongqing','400000','44333'), (103,'ACME','Shanghai','200000','90046'), 

        (104,'FNK Inc.','Zhongshan','528437','11111'), (105,'Good Set','Taiyuang','030000', '22222'), (106,'Just Eat Ours','Beijing','010', '45678'), 

        (107,'DK Inc.','Zhengzhou','450000', '33332'); 

        INSERT INTO orderitems(o_num, o_item, f_id, quantity, item_price) 

        VALUES(30001, 1, 'a1', 10, 5.2), (30001, 2, 'b2', 3, 7.6), 

        (30001, 3, 'bs1', 5, 11.2), 

        (30001, 4, 'bs2', 15, 9.2), 

        (30002, 1, 'b3', 2, 20.0), 

        (30003, 1, 'c0', 100, 10), 

        (30004, 1, 'o2', 50, 2.50), 

        (30005, 1, 'c0', 5, 10), 

        (30005, 2, 'b1', 10, 8.99), 

        (30005, 3, 'a2', 10, 2.2), 

        (30005, 4, 'm1', 5, 14.99); 

        INSERT INTO orders(o_num, o_date, c_id) VALUES(30001, '2008-09-01', 10001), (30002, '2008-09-12', 10003), 

        (30003, '2008-09-30', 10004), 

        (30004, '2008-10-03', 10005), 

        (30005, '2008-10-08', 10001); 

        单表查询 

        查询所有字段 查询指定字段 

        【例7.1】从fruits表中检索所有字段的数据 

        SELECT * FROM fruits; 

        SELECT f_id, s_id ,f_name, f_price FROM fruits; 查询单个字段 

        【例7.2】查询当前表中f_name列所有水果名称，输入如下语句: 

        SELECT f_name FROM fruits; 

        查询多个字段 

        【例7.3】例如，从fruits表中获取f_name和f_price两列，输入如下语句: SELECT f_name, f_price FROM fruits; 查询指定记录 

        【例7.4】查询价格为10.2元的水果的名称，输入如下语句: 

        SELECT f_name, f_price FROM fruits WHERE f_price = 10.2; 

        1 

        【例7.5】查找名称为“apple”的水果的价格,输入如下语句: 

        SELECT f_name, f_price FROM fruits WHERE f_name = 'apple'; 

        【例7.6】查询价格小于10的水果的名称，输入如下语句: 

        SELECT f_name, f_price FROM fruits WHERE f_price < 10; 

        带in关键字的查询 in(m,n)等于m和等于n not in(m,n) 【例7.7】s_id为101和102的记录，输入如下语句: 

        SELECT s_id,f_name, f_price FROM fruits WHERE s_id IN (101,102) ORDER BY f_name; 

        【例7.8】查询所有s_id不等于101也不等于102的记录，输入如下语句: 

        SELECT s_id,f_name, f_price 

        FROM fruits 

        WHERE s_id NOT IN (101,102) 

        ORDER BY f_name; 

        带between and 的范围查询 between m and n 在M和N之间 

        【例7.9】查询价格在2.00元到10.5元之间水果名称和价格 

        SELECT f_name, f_price FROM fruits WHERE f_price BETWEEN 2.00 AND 10.20; 

        【例7.10】查询价格在2.00元到10.5元之外的水果名称和价格 

        SELECT f_name, f_price FROM fruits WHERE f_price NOT BETWEEN 2.00 AND 10.20; 

        带like的字符匹配查询，通配符---百分号'%' ,匹配任意长度的字符 

        M%N以M开头且以N结尾 

        M%以M开头 

        %N以N结尾 

        %M% 含有M 

        【例7.11】查找所有以‘b’字母开头的水果，输入如下语句: 

        SELECT f_id, f_name FROM fruits WHERE f_name LIKE 'b%'; 

        【例7.12】在fruits表中，查询f_name中包含字母‘g’的记录 

        SELECT f_id, f_name FROM fruits WHERE f_name LIKE '%g%'; 

        【例7.13】查询以‘b’开头，并以‘y’结尾的水果的名称 

        SELECT f_name FROM fruits WHERE f_name LIKE 'b%y'; 

        下划线通配符'_'一次只能匹配任意一个字符 

        【例7.14】在fruits表中，查询以字母‘y’结尾，且‘y’前面只有4个字母的记录 

        SELECT f_id, f_name FROM fruits WHERE f_name LIKE '____y'; 查询空值在子句中 不能使用’=‘ 使用 is null 表示空值 

        【例7.15】查询customers表中c_email为空的记录的c_id、c_name和c_email字段值: 

        SELECT c_id, c_name,c_email FROM customers WHERE c_email IS NULL; 

        【例7.16】查询customers表中c_email不为空的记录的c_id、c_name和c_email字段值 

        SELECT c_id, c_name,c_email FROM customers WHERE c_email IS NOT NULL; 带and/&& 的多条件查询 

        【例7.17】在fruits表中查询s_id = ‘101’，并且f_price大于5价格和名称的记录 

        SELECT f_id, f_price, f_name FROM fruits WHERE s_id = '101' AND f_price >=5; 

        【例7.18】在fruits表中查询s_id = ‘101’或者’102’，并且f_price大于5，并且f_name=’

        apple’的记录价格和名称 

        SELECT f_id, f_price, f_name FROM fruits 

        WHERE s_id IN('101', '102') AND f_price >= 5 AND f_name = 'apple'; 

        带OR的多条件查询 

        In(M,N)M和N 

        And 和，且 

        【例7.19】查询s_id=101或者s_id=102的水果供应商的f_price和f_name，SQL语句如下: SELECT s_id,f_name, f_price FROM fruits WHERE s_id = 101 OR s_id = 102;(不能写为=101,102 或者=101or 102(只求了101)) 

        【例7.20】查询s_id=101或者s_id=102的水果供应商的f_price和f_name 

        SELECT s_id,f_name, f_price FROM fruits WHERE s_id IN(101,102); 

        查询结果不重复 使用distinct 语法:select distinct 字段名 from 表名 

        【例7.21】查询fruits表中s_id字段的值，并返回s_id字段值不得重复 

        SELECT DISTINCT s_id FROM fruits; 

        对查询结果进行排序 order by 默认按升序来排序 即ASC 

        Order by 子节段(ASC)/DESC 

        Order by 子节段1，字节段2.。。。。。 

        单列排序 

        【例7.22】查询fruits表的f_name字段值，并对其进行排序 

        select f_name from fruits ORDER BY f_name; 多列排序 

        【例7.23】查询fruits表中的f_name和f_price字段，先按f_name排序，再按f_price排序 SELECT f_name, f_price FROM fruits ORDER BY f_name, f_price; 

        备注: 

        先按照字段1进行排序,如果字段1中的值有相等,那么相等的这些数据将按照字段2,在进行排序. 

        如果第一列数据中所有的值都是唯一的(值均不相等)，将不再对第二列进行排序. 【例7.24】查询fruits表中的f_name和f_price字段，对结果按f_price降序方式排序 SELECT f_name, f_price FROM fruits ORDER BY f_price DESC; 

        指定排序的方向 ASC 升序， DESC 降序 

        【例7.25】查询fruits表，先按f_price降序排序，再按f_name字段升序排序，SQL语句如下: 

        SELECT f_price, f_name FROM fruits ORDER BY f_price DESC, f_name; 

        分组查询:group by关键字通常和集合函数一起使用例如:max()，min(),count(),sun() ,avg() 

        每一个水果供应商提供多种水果，根据水果供应商分组使用count可以查看水果供应商供应的水果种类数 

        【例7.26】根据s_id对fruits表中的数据进行分组 

        SELECT s_id, COUNT(*) AS Total FROM fruits GROUP BY s_id; 

        group_concat()可以将分组的子元素查看出来 

        【例7.27】根据s_id对fruits表中的数据进行分组，将每个供应商的水果名称显示出来 SELECT s_id, GROUP_CONCAT(f_name) AS Names FROM fruits GROUP BY s_id; 

        having条件过滤 

        【例7.28】根据s_id对fruits表中的数据进行分组，并显示水果种类大于1的分组信息 SELECT s_id, GROUP_CONCAT(f_name) AS Names FROM fruits 

        GROUP BY s_id HAVING COUNT(f_name) > 1; 

        多字段分组,与多字段排序相同,先按照字段1分组,组中再按照字段2分组. 【例7.30】根据s_id和f_name字段对fruits表中的数据进行分组， SQL语句如下， SELECT * from fruits group by s_id,f_name; group by 和order by 一起使用o_num订单号，订单价格 

        【例7.31】查询总订单价格大于100的订单号和总订单价格 

        SELECT o_num, SUM(quantity * item_price) AS orderTotal 

        FROM orderitems 

        GROUP BY o_num 

        HAVING SUM(quantity*item_price) >= 100; 

        使用limit限制查询结果的数量 

        limit [位置偏移量]，行数 位置偏移量即从哪行开始显示 

        【例7.32】显示fruits表查询结果的前4行，输入如下语句: 

        SELECT * From fruits LIMIT 4; 

        记录从0开始 

        【例7.33】在fruits 表中，使用LIMIT子句，返回从第5个记录开始的，行数长度为3的记录 

        SELECT * From fruits LIMIT 4, 3; 

        使用集合函数查询 

        Select 集合函数(目的)(as 新名称) from 表明 

        Count() 求某列的总行数 

        Sum() 求和 

        Avg() 求平均数 

        Max() 求最大值 

        Min() 求最小值 

        count()函数 返回某一列的行总数 

        【例7.34】查询customers表中总的行数 

        SELECT COUNT(*) AS cust_num from customers; 

        【例7.35】查询customers表中有电子邮箱的顾客的总数，输入如下语句: SELECT COUNT(c_email) AS email_num FROM customers; 

        【例7.36】在orderitems表中，使用COUNT()函数统计不同订单号中订购的水果种类 SELECT o_num, COUNT(f_id) FROM orderitems GROUP BY o_num; 

        sum()求和函数 

        【例7.37】在orderitems表中查询30005号订单一共购买的水果总量，输入如下语句: SELECT SUM(quantity) AS items_total FROM orderitems 

        WHERE o_num = 30005; 

        【例7.38】在orderitems表中，使用SUM()函数统计不同订单号中订购的水果总量 SELECT o_num, SUM(quantity) AS items_total FROM orderitems 

        GROUP BY o_num; 

        avg()平均值函数 

        【例7.39】在fruits表中，查询s_id=103的供应商的水果价格的平均值，SQL语句如下: SELECT AVG(f_price) AS avg_price FROM fruits 

        WHERE s_id = 103; 

        【例7.40】在fruits表中，查询每一个供应商的水果价格的平均值，SQL语句如下: SELECT s_id,AVG(f_price) AS avg_price FROM fruits 

        GROUP BY s_id; 

        max()最大值 

        【例7.41】在fruits表中查找市场上价格最高的水果，SQL语句如下: mysql>SELECT MAX(f_price) AS max_price FROM fruits; 

        【例7.42】在fruits表中查找不同供应商提供的价格最高的水果 

        SELECT s_id, MAX(f_price) AS max_price FROM fruits 

        GROUP BY s_id; 

        【例7.43】在fruits表中查找f_name的最大值，SQL语句如下 

        SELECT MAX(f_name) from fruits; min()最小值 

        【例7.44】在fruits表中查找市场上价格最低的水果，SQL语句如下: mysql>SELECT MIN(f_price) AS min_price FROM fruits; 

        【例7.45】在fruits表中查找不同供应商提供的价格最低的水果 

        SELECT s_id, MIN(f_price) AS min_price FROM fruits 

        GROUP BY s_id; 

        子查询 

        定义两个表tb11和tb12 

        CREATE table tb11 ( num1 INT NOT NULL); CREATE table tb12 ( num2 INT NOT NULL); 向两个表中插入数据， 

        INSERT INTO tb11 values(1), (5), (13), (27); INSERT INTO tb12 values(6), (14), (11), (20); 使用any ，some关键字的子查询 同义词 

        Any(只要有，就可以) all(满足所有要求) 

        【例7.53】返回tb12表的所有 num2 列，然后将 tbl1 中的 num1 的值与之进行比较，只要大于 num2的任何值为符合查询条件的结果。 

        SELECT num1 FROM tb11 WHERE num1 > ANY (SELECT num2 FROM tb12); 

        带All关键字的子查询 

        【例7.54】返回tbl1表的中比tbl2表num2 列所有值都大的值 

        SELECT num1 FROM tb11 WHERE num1 > ALL (SELECT num2 FROM tb12); 

        带exists 关键字的子查询: exists 后的sql语句的结果不为空时,执行exists前的查询 

        【例7.55】查询表suppliers表中是否存在s_id=107的供应商，如果存在则查询fruits表中的记录 

        SELECT * from fruits 

        WHERE EXISTS 

        (SELECT s_name FROM suppliers WHERE s_id = 107); 

        【例7.56】查询表suppliers表中是否存在s_id=107的供应商，如果存在则查询fruits表中的f_price大于10.20的记录 

        SELECT * from fruits 

        WHERE f_price>10.20 AND EXISTS 

        (SELECT s_name FROM suppliers WHERE s_id = 107); 

        【例7.57】查询表suppliers表中是否存在s_id=107的供应商，如果不存在则查询fruits表中的记录 

        SELECT * from fruits 

        WHERE NOT EXISTS 

        (SELECT s_name FROM suppliers WHERE s_id = 107); 带in关键字的子查询 

        【例7.58】在orderitems表中查询订购f_id为c0的订单号，并根据订单号查询具有订单号的客户c_id 

        SELECT c_id FROM orders WHERE o_num IN (SELECT o_num FROM orderitems WHERE f_id = 'c0'); 

        【例7.59】与前一个例子语句类似，但是在SELECT语句中使用NOT IN操作符 

        SELECT c_id FROM orders WHERE o_num NOT IN (SELECT o_num FROM orderitems WHERE f_id = 'c0'); 带比较运算符的子查询 

        【例7.60】在suppliers表中查询s_city等于Tianjin的供应商s_id，然后在fruits表中查询所有该供应商提供的水果的种类 

        SELECT s_id, f_name FROM fruits 

        WHERE s_id = 

        (SELECT s1.s_id from suppliers AS s1 WHERE s1.s_city = 'Tianjin'); 

        【例7.61】在suppliers表中查询s_city等于Tianjin的供应商s_id，然后在fruits表中查询所有非该供应商提供的水果的种类，SQL语句如下， 

        SELECT s_id, f_name FROM fruits 

        WHERE s_id <> 

        (SELECT s1.s_id from suppliers AS s1 WHERE s1.s_city = 'Tianjin'); 

        为表和字段取别名 

        【例7.64】为orders表取别名o，查询订30001订单的下单日期 

        SELECT * from orders AS o 

        WHERE o.o_num = 30001; 

        【例7.65】为customers和orders表分别取别名，并进行连接查询 

        SELECT c.c_id, o.o_num 

        FROM customers AS c LEFT OUTER JOIN orders AS o ON c.c_id = o.c_id; 

        【例7.66】查询fruits表，为f_name取别名fruit_name，f_price取别名fruit_price，为fruits表取别名f1，查询表中f_price < 8的水果的名称 

        SELECT f1.f_name AS fruit_name, f1.f_price AS fruit_price 

        FROM fruits AS f1 

        WHERE f1.f_price < 8; 

        【例7.67】查询suppliers表中字段s_name和s_city，使用CONCAT函数连接这个两个字段值，并取列别名为suppliers_title。 

        如果没有对连接后的值取别名，其显示列名称将会不够直观，输入如下SQL， 

        SELECT CONCAT(RTRIM(s_name) , ' (', RTRIM(s_city), ')') FROM suppliers 

        ORDER BY s_name; 

        使用正则表达式查询 

        【例7.68】在fruits表中，查询f_name字段以字母‘b’开头的记录 

        SELECT * FROM fruits WHERE f_name REGEXP '^b'; 

        【例7.69】在fruits表中，查询f_name字段以“be”开头的记录 

        SELECT * FROM fruits WHERE f_name REGEXP '^be'; 

        【例7.70】在fruits表中，查询f_name字段以字母‘Y’结尾的记录 

        SELECT * FROM fruits WHERE f_name REGEXP 'y$'; 

        【例7.71】在fruits表中，查询f_name字段以字符串“rry”结尾的记录 

        SELECT * FROM fruits WHERE f_name REGEXP 'rry$'; 

        【例7.72】在fruits表中，查询f_name字段值包含字母‘a’与‘g’且两个字母之间只有一个字母的记录 

        SELECT * FROM fruits WHERE f_name REGEXP 'a.g'; 

        【例7.73】在fruits表中，查询f_name字段值以字母‘b’开头，且‘b’后面出现字母‘a’的记录 

        SELECT * FROM fruits WHERE f_name REGEXP '^ba*'; 

        【例7.74】在fruits表中，查询f_name字段值以字母‘b’开头，且‘b’后面出现字母‘a’至少一次的记录 

        SELECT * FROM fruits WHERE f_name REGEXP '^ba+'; 

        【例7.75】在fruits表中，查询f_name字段值包含字符串“on”的记录 

        SELECT * FROM fruits WHERE f_name REGEXP 'on'; 

        【例7.76】在fruits表中，查询f_name字段值包含字符串“on”或者“ap”的记录 

        SELECT * FROM fruits WHERE f_name REGEXP 'on|ap'; 

        【例7.77】在fruits表中，使用LIKE运算符查询f_name字段值为“on”的记录 

        SELECT * FROM fruits WHERE f_name LIKE '%on%'; 

        【例7.78】在fruits表中，查找f_name字段中包含字母o或者y的记录 

        SELECT * FROM fruits WHERE f_name REGEXP '[oy]'; 

        【例7.79】在fruits表，查询s_id字段中数值中包含4、5或者6的记录 

        SELECT * FROM fruits WHERE s_id REGEXP '[456]'; 

        【例7.80】在fruits表中，查询f_id字段包含字母a到e和数字1到2以外的字符的记录 SELECT * FROM fruits WHERE f_id REGEXP '[^a-e1-2]'; 

        【例7.81】在fruits表中，查询f_name字段值出现字符串‘x’至少2次的记录 

        SELECT * FROM fruits WHERE f_name REGEXP 'x{2,}'; 

        【例7.82】在fruits表中，查询f_name字段值出现字符串“ba”最少1次，最多3次的记录 

        SELECT * FROM fruits WHERE f_name REGEXP 'ba{1,3}';