MySQL数据查询.docx

1、MySQL数据查询.0.Mysql查询数据本章所用到的数据表/*顾客表*/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 cha

2、r(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

3、 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) ;/*

4、-插入数据-*/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),(10002, Stars, 333 Fromage Lane, Dalian, 116000, Zhangbo,Jerry),(10003, Netbhood, 1 Sunny Place, Qingdao, 266000, LuoCong, NULL),(10004, JOTO,

5、 829 Riverside Drive, Haikou, 570000, YangShan, sam);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

6、.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,AC

7、ME,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),(30

8、001, 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

9、-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_na

10、me和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 W

11、HERE 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和NAnd 和，且【例7.19】查询s_id=101或者s_id=102的水果供应商的f_price和f_name，SQL语句如下：SELECT s_id,f_name, f_price FROM

12、 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_nameSELECT 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

13、fruits;对查询结果进行排序 order by 默认按升序来排序 即ASCOrder by 子节段（ASC）/DESCOrder 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进行排序,如果字

14、段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

15、_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,

16、 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

17、 fruits group by s_id,f_name;group by 和order by 一起使用o_num订单号，订单价格【例7.31】查询总订单价格大于100的订单号和总订单价格SELECT o_num, SUM(quantity * item_price) AS orderTotalFROM orderitemsGROUP BY o_numHAVING SUM(quantity*item_price) = 100;使用limit限制查询结果的数量limit 位置偏移量，行数 位置偏移量即从哪行开始显示【例7.32】显示fruits表查询结果的前4行，输入如下语句：SELECT *

18、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】查询custom

19、ers表中有电子邮箱的顾客的总数，输入如下语句：SELECT COUNT(c_email) AS email_numFROM 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_totalFROM orderitemsWHERE o_num = 300

20、05;【例7.38】在orderitems表中，使用SUM()函数统计不同订单号中订购的水果总量SELECT o_num, SUM(quantity) AS items_totalFROM orderitemsGROUP BY o_num;avg（）平均值函数【例7.39】在fruits表中，查询s_id=103的供应商的水果价格的平均值，SQL语句如下：SELECT AVG(f_price) AS avg_priceFROM fruitsWHERE s_id = 103;【例7.40】在fruits表中，查询每一个供应商的水果价格的平均值，SQL语句如下：SELECT s_id,AVG(f_

21、price) AS avg_priceFROM fruitsGROUP BY s_id;max()最大值【例7.41】在fruits表中查找市场上价格最高的水果，SQL语句如下：mysqlSELECT MAX(f_price) AS max_price FROM fruits;【例7.42】在fruits表中查找不同供应商提供的价格最高的水果SELECT s_id, MAX(f_price) AS max_priceFROM fruitsGROUP BY s_id;【例7.43】在fruits表中查找f_name的最大值，SQL语句如下SELECT MAX(f_name) from fruit

22、s;min（）最小值【例7.44】在fruits表中查找市场上价格最低的水果，SQL语句如下：mysqlSELECT MIN(f_price) AS min_price FROM fruits;【例7.45】在fruits表中查找不同供应商提供的价格最低的水果SELECT s_id, MIN(f_price) AS min_priceFROM fruitsGROUP BY s_id;子查询定义两个表tb11和tb12CREATE table tb11 ( num1 INT NOT NULL);CREATE table tb12 ( num2 INT NOT NULL);向两个表中插入数据，IN

23、SERT 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表的中比tbl

24、2表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 fruitsWHERE EXISTS(SELECT s_name FROM suppliers WHERE s_id = 107);【例7.56】查询表suppliers表中是否存在s_id=107的供应商

25、，如果存在则查询fruits表中的f_price大于10.20的记录SELECT * from fruitsWHERE f_price10.20 AND EXISTS(SELECT s_name FROM suppliers WHERE s_id = 107);【例7.57】查询表suppliers表中是否存在s_id=107的供应商，如果不存在则查询fruits表中的记录SELECT * from fruitsWHERE NOT EXISTS(SELECT s_name FROM suppliers WHERE s_id = 107);带in关键字的子查询【例7.58】在orderitems表中查询订购f_id为c0的订单号，并根据订单号查询具有订单号的客户c_idSELECT 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_n