/*顧客表*/ 

        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) 

        ) ; 

        /*訂單明細(xì)表*/ 

        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) 

        ) ; 

        /*--------------------------插入數(shù)據(jù)--------------------------*/ 

        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', '[email protected]'), 

        (10002, 'Stars', '333 Fromage Lane', 'Dalian', '116000', 'Zhangbo','[email protected]'), 

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

        (10004, 'JOTO', '829 Riverside Drive', 'Haikou', '570000', 'YangShan', '[email protected]'); 

        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表中檢索所有字段的數(shù)據(jù) 

        SELECT * FROM fruits; 

        SELECT f_id, s_id ,f_name, f_price FROM fruits; 查詢單個(gè)字段 

        【例7.2】查詢當(dāng)前表中f_name列所有水果名稱，輸入如下語(yǔ)句: 

        SELECT f_name FROM fruits; 

        查詢多個(gè)字段 

        【例7.3】例如，從fruits表中獲取f_name和f_price兩列，輸入如下語(yǔ)句: SELECT f_name, f_price FROM fruits; 查詢指定記錄 

        【例7.4】查詢價(jià)格為10.2元的水果的名稱，輸入如下語(yǔ)句: 

        SELECT f_name, f_price FROM fruits WHERE f_price = 10.2; 

        1 

        【例7.5】查找名稱為“apple”的水果的價(jià)格,輸入如下語(yǔ)句: 

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

        【例7.6】查詢價(jià)格小于10的水果的名稱，輸入如下語(yǔ)句: 

        SELECT f_name, f_price FROM fruits WHERE f_price < 10; 

        帶in關(guān)鍵字的查詢 in(m,n)等于m和等于n not in(m,n) 【例7.7】s_id為101和102的記錄，輸入如下語(yǔ)句: 

        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的記錄，輸入如下語(yǔ)句: 

        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】查詢價(jià)格在2.00元到10.5元之間水果名稱和價(jià)格 

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

        【例7.10】查詢價(jià)格在2.00元到10.5元之外的水果名稱和價(jià)格 

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

        帶like的字符匹配查詢，通配符---百分號(hào)'%' ,匹配任意長(zhǎng)度的字符 

        M%N以M開頭且以N結(jié)尾 

        M%以M開頭 

        %N以N結(jié)尾 

        %M% 含有M 

        【例7.11】查找所有以‘b’字母開頭的水果，輸入如下語(yǔ)句: 

        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’結(jié)尾的水果的名稱 

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

        下劃線通配符'_'一次只能匹配任意一個(gè)字符 

        【例7.14】在fruits表中，查詢以字母‘y’結(jié)尾，且‘y’前面只有4個(gè)字母的記錄 

        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價(jià)格和名稱的記錄 

        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’的記錄價(jià)格和名稱 

        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的水果供應(yīng)商的f_price和f_name，SQL語(yǔ)句如下: 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的水果供應(yīng)商的f_price和f_name 

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

        查詢結(jié)果不重復(fù) 使用distinct 語(yǔ)法:select distinct 字段名 from 表名 

        【例7.21】查詢fruits表中s_id字段的值，并返回s_id字段值不得重復(fù) 

        SELECT DISTINCT s_id FROM fruits; 

        對(duì)查詢結(jié)果進(jìn)行排序 order by 默認(rèn)按升序來(lái)排序 即ASC 

        Order by 子節(jié)段(ASC)/DESC 

        Order by 子節(jié)段1，字節(jié)段2.。。。。。 

        單列排序 

        【例7.22】查詢fruits表的f_name字段值，并對(duì)其進(jìn)行排序 

        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進(jìn)行排序,如果字段1中的值有相等,那么相等的這些數(shù)據(jù)將按照字段2,在進(jìn)行排序. 

        如果第一列數(shù)據(jù)中所有的值都是唯一的(值均不相等)，將不再對(duì)第二列進(jìn)行排序. 【例7.24】查詢fruits表中的f_name和f_price字段，對(duì)結(jié)果按f_price降序方式排序 SELECT f_name, f_price FROM fruits ORDER BY f_price DESC; 

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

        【例7.25】查詢fruits表，先按f_price降序排序，再按f_name字段升序排序，SQL語(yǔ)句如下: 

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

        分組查詢:group by關(guān)鍵字通常和集合函數(shù)一起使用例如:max()，min(),count(),sun() ,avg() 

        每一個(gè)水果供應(yīng)商提供多種水果，根據(jù)水果供應(yīng)商分組使用count可以查看水果供應(yīng)商供應(yīng)的水果種類數(shù) 

        【例7.26】根據(jù)s_id對(duì)fruits表中的數(shù)據(jù)進(jìn)行分組 

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

        group_concat()可以將分組的子元素查看出來(lái) 

        【例7.27】根據(jù)s_id對(duì)fruits表中的數(shù)據(jù)進(jìn)行分組，將每個(gè)供應(yīng)商的水果名稱顯示出來(lái) SELECT s_id, GROUP_CONCAT(f_name) AS Names FROM fruits GROUP BY s_id; 

        having條件過(guò)濾 

        【例7.28】根據(jù)s_id對(duì)fruits表中的數(shù)據(jù)進(jìn)行分組，并顯示水果種類大于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】根據(jù)s_id和f_name字段對(duì)fruits表中的數(shù)據(jù)進(jìn)行分組， SQL語(yǔ)句如下， SELECT * from fruits group by s_id,f_name; group by 和order by 一起使用o_num訂單號(hào)，訂單價(jià)格 

        【例7.31】查詢總訂單價(jià)格大于100的訂單號(hào)和總訂單價(jià)格 

        SELECT o_num, SUM(quantity * item_price) AS orderTotal 

        FROM orderitems 

        GROUP BY o_num 

        HAVING SUM(quantity*item_price) >= 100; 

        使用limit限制查詢結(jié)果的數(shù)量 

        limit [位置偏移量]，行數(shù) 位置偏移量即從哪行開始顯示 

        【例7.32】顯示fruits表查詢結(jié)果的前4行，輸入如下語(yǔ)句: 

        SELECT * From fruits LIMIT 4; 

        記錄從0開始 

        【例7.33】在fruits 表中，使用LIMIT子句，返回從第5個(gè)記錄開始的，行數(shù)長(zhǎng)度為3的記錄 

        SELECT * From fruits LIMIT 4, 3; 

        使用集合函數(shù)查詢 

        Select 集合函數(shù)(目的)(as 新名稱) from 表明 

        Count() 求某列的總行數(shù) 

        Sum() 求和 

        Avg() 求平均數(shù) 

        Max() 求最大值 

        Min() 求最小值 

        count()函數(shù) 返回某一列的行總數(shù) 

        【例7.34】查詢customers表中總的行數(shù) 

        SELECT COUNT(*) AS cust_num from customers; 

        【例7.35】查詢customers表中有電子郵箱的顧客的總數(shù)，輸入如下語(yǔ)句: SELECT COUNT(c_email) AS email_num FROM customers; 

        【例7.36】在orderitems表中，使用COUNT()函數(shù)統(tǒng)計(jì)不同訂單號(hào)中訂購(gòu)的水果種類 SELECT o_num, COUNT(f_id) FROM orderitems GROUP BY o_num; 

        sum()求和函數(shù) 

        【例7.37】在orderitems表中查詢30005號(hào)訂單一共購(gòu)買的水果總量，輸入如下語(yǔ)句: SELECT SUM(quantity) AS items_total FROM orderitems 

        WHERE o_num = 30005; 

        【例7.38】在orderitems表中，使用SUM()函數(shù)統(tǒng)計(jì)不同訂單號(hào)中訂購(gòu)的水果總量 SELECT o_num, SUM(quantity) AS items_total FROM orderitems 

        GROUP BY o_num; 

        avg()平均值函數(shù) 

        【例7.39】在fruits表中，查詢s_id=103的供應(yīng)商的水果價(jià)格的平均值，SQL語(yǔ)句如下: SELECT AVG(f_price) AS avg_price FROM fruits 

        WHERE s_id = 103; 

        【例7.40】在fruits表中，查詢每一個(gè)供應(yīng)商的水果價(jià)格的平均值，SQL語(yǔ)句如下: SELECT s_id,AVG(f_price) AS avg_price FROM fruits 

        GROUP BY s_id; 

        max()最大值 

        【例7.41】在fruits表中查找市場(chǎng)上價(jià)格最高的水果，SQL語(yǔ)句如下: mysql>SELECT MAX(f_price) AS max_price FROM fruits; 

        【例7.42】在fruits表中查找不同供應(yīng)商提供的價(jià)格最高的水果 

        SELECT s_id, MAX(f_price) AS max_price FROM fruits 

        GROUP BY s_id; 

        【例7.43】在fruits表中查找f_name的最大值，SQL語(yǔ)句如下 

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

        【例7.44】在fruits表中查找市場(chǎng)上價(jià)格最低的水果，SQL語(yǔ)句如下: mysql>SELECT MIN(f_price) AS min_price FROM fruits; 

        【例7.45】在fruits表中查找不同供應(yīng)商提供的價(jià)格最低的水果 

        SELECT s_id, MIN(f_price) AS min_price FROM fruits 

        GROUP BY s_id; 

        子查詢 

        定義兩個(gè)表tb11和tb12 

        CREATE table tb11 ( num1 INT NOT NULL); CREATE table tb12 ( num2 INT NOT NULL); 向兩個(gè)表中插入數(shù)據(jù)， 

        INSERT INTO tb11 values(1), (5), (13), (27); INSERT INTO tb12 values(6), (14), (11), (20); 使用any ，some關(guān)鍵字的子查詢 同義詞 

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

        【例7.53】返回tb12表的所有 num2 列，然后將 tbl1 中的 num1 的值與之進(jìn)行比較，只要大于 num2的任何值為符合查詢條件的結(jié)果。 

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

        帶All關(guān)鍵字的子查詢 

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

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

        帶exists 關(guān)鍵字的子查詢: exists 后的sql語(yǔ)句的結(jié)果不為空時(shí),執(zhí)行exists前的查詢 

        【例7.55】查詢表suppliers表中是否存在s_id=107的供應(yīng)商，如果存在則查詢fruits表中的記錄 

        SELECT * from fruits 

        WHERE EXISTS 

        (SELECT s_name FROM suppliers WHERE s_id = 107); 

        【例7.56】查詢表suppliers表中是否存在s_id=107的供應(yīng)商，如果存在則查詢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的供應(yīng)商，如果不存在則查詢fruits表中的記錄 

        SELECT * from fruits 

        WHERE NOT EXISTS 

        (SELECT s_name FROM suppliers WHERE s_id = 107); 帶in關(guān)鍵字的子查詢 

        【例7.58】在orderitems表中查詢訂購(gòu)f_id為c0的訂單號(hào)，并根據(jù)訂單號(hào)查詢具有訂單號(hào)的客戶c_id 

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

        【例7.59】與前一個(gè)例子語(yǔ)句類似，但是在SELECT語(yǔ)句中使用NOT IN操作符 

        SELECT c_id FROM orders WHERE o_num NOT IN (SELECT o_num FROM orderitems WHERE f_id = 'c0'); 帶比較運(yùn)算符的子查詢 

        【例7.60】在suppliers表中查詢s_city等于Tianjin的供應(yīng)商s_id，然后在fruits表中查詢所有該供應(yīng)商提供的水果的種類 

        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的供應(yīng)商s_id，然后在fruits表中查詢所有非該供應(yīng)商提供的水果的種類，SQL語(yǔ)句如下， 

        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表分別取別名，并進(jìn)行連接查詢 

        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函數(shù)連接這個(gè)兩個(gè)字段值，并取列別名為suppliers_title。 

        如果沒(méi)有對(duì)連接后的值取別名，其顯示列名稱將會(huì)不夠直觀，輸入如下SQL， 

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

        ORDER BY s_name; 

        使用正則表達(dá)式查詢 

        【例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’結(jié)尾的記錄 

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

        【例7.71】在fruits表中，查詢f_name字段以字符串“rry”結(jié)尾的記錄 

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

        【例7.72】在fruits表中，查詢f_name字段值包含字母‘a(chǎn)’與‘g’且兩個(gè)字母之間只有一個(gè)字母的記錄 

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

        【例7.73】在fruits表中，查詢f_name字段值以字母‘b’開頭，且‘b’后面出現(xiàn)字母‘a(chǎn)’的記錄 

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

        【例7.74】在fruits表中，查詢f_name字段值以字母‘b’開頭，且‘b’后面出現(xiàn)字母‘a(chǎn)’至少一次的記錄 

        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運(yùn)算符查詢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字段中數(shù)值中包含4、5或者6的記錄 

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

        【例7.80】在fruits表中，查詢f_id字段包含字母a到e和數(shù)字1到2以外的字符的記錄 SELECT * FROM fruits WHERE f_id REGEXP '[^a-e1-2]'; 

        【例7.81】在fruits表中，查詢f_name字段值出現(xiàn)字符串‘x’至少2次的記錄 

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

        【例7.82】在fruits表中，查詢f_name字段值出現(xiàn)字符串“ba”最少1次，最多3次的記錄 

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