02_数据库基础之（二）sql语句入门

1.基本增删改查操作

 1 #一。 数据类型  常用的3中
 2  # 1.字符串  例如：你的名字 我是中国人    在数据库中要使用 ‘’引起来   '苹果手机'
 3  # 2.整数类型  例如： 你的年龄 23 ，办公室的人数  5 个 ，直接使用  5000
 4  # 3.时间    当着一个字符串用
 5 
 6 #二。几乎所有的编程标点符号都要使用 英文状态下的
 7 
 8 #三。数据库中不区分大小写，但是  生产上的 规范  关键字大写 INSERT，其他的小写
 9 
10 新增
11 需求：添加一条数据到产品表   产品名称为苹果手机   卖价为5000
12 INSERT INTO  product (product_name,sale_price) VALUES ('苹果手机'，5000)
13 
14 删除
15 需求：删除产品表中id=20的数据
16 DELETE FROM product WHERE id=20
17 # = >  <   >=    <=   
18 # product_name='联想M115'
19 #  或OR  且AND  非!     两个条件同时满足  AND
20 需求：删除产品表中id=20并且product_name='联想M115'的数据
21 DELETE FROM product WHERE id=20  AND product_name='联想M115'
22 
23 需求：删除产品表中id=20或者product_name='联想M115'的数据
24 DELETE FROM product WHERE id=20  OR product_name='联想M115'
25 
26 需求：删除产品表中product_name！='联想M115'的数据
27 DELETE FROM product WHERE product_name !='联想M115'
28 
29 更新
30 
31 #需求：把成本价大于100的所有商品的卖价修改为200，并且把名称修改为特殊商品
32 UPDATE product SET sale_price=200,product_name='特殊商品'  WHERE cost_price>100
33 
34 查询   
35 #查询所有商品
36 SELECT * FROM product  
37 #需求：查询id<8的数据
38                        #过滤行
39 SELECT * FROM product  WHERE id<8
40 #需求：查询id<8的数据，只看id，产品名称，卖价
41 SELECT id,product_name,sale_price FROM product  WHERE id<8

2.创建表与规范命名

2.1.命名规则:

             a.见名知意千万不要使用中文拼音
         b.多个单词使用下划线(数据库中不区分大小写，生产规范关键字大写，其他小写)
             c.不要使用关键字 #关键字 INSERT INTO VALUES

2.2.数据库设计必备基本字段
　　　　1.id 序号
　　　　2.state 状态
　　　　3.type 类型
　　　　4.create_time 创建时间
　　　　5.update_time 更新时间

2.3.创建用户表案例

3.表结构认识

4.简单查询之列操作

 1 简单查询:
 2 语法：
 3 SELECT {*, column [alias],...}
 4 FROM       table_name;
 5 说明：
 6 SELECT  选择查询列表
 7 FROM      提供数据源(表、视图或其他的数据源)
 8 如果为 * 和创建表时的顺序一致。
 9 可以自己调整顺序，在select后边加上要查询的列名。
10 需求:查询所有货品信息
11 需求:查询所有货品的id,product_name,sale_price
12 --------------------------------------------------------------
13 消除结果中重复的数据。
14 需求:查询商品的分类编号。
15 语法：
16 SELECT    DISTINCT 列名，..
17 --------------------------------------------------------------
18 实现数学运算查询:
19 
20 对NUMBER型数据可以使用算数操作符创建表达式（+  -  *  /）
21 
22 对DATE型数据可以使用部分算数操作符创建表达式 （+  -）
23 
24 运算符优先级：
25 
26 1、乘法和除法的优先级高于加法和减法
27 
28 2、同级运算的顺序是从左到右
29 
30 3、表达式中使用"括号"可强行改变优先级的运算顺序
31 
32 -----------------------------------------------------------------
33 需求:查询所有货品的id，名称和每零售1个产品所赚取的钱(每零售1个产品所赚取的钱=零售价-成本价格)
34 SELECT id ,product_name,sale_price-cost_price FROM product
35 
36 需求:查询所有货品的id，名称和批发价(批发价=卖价*折扣)
37 
38 需求:查询所有货品的id，名称，和各进50个的成本价(成本=cost_price)
39 
42 ------------------------------------------------------------------
43 
44 设置列名的别名。
45 
46 1、改变列的标题头；
47 
48 2、用于表示计算结果的含义；
49 
50 3、作为列的别名；
51 
52 4、如果别名中使用特殊字符，或者是强制大小写敏感，或有空格时，都需加单引号；--->英文单词
53 
54 需求:查询所有货品的id，名称和每零售1个产品所赚取的钱(每零售1个产品所赚取的钱=零售价-成本价格),并取别名
55 
56 SELECT id ,product_name 名称 ,sale_price-cost_price 每零售1个产品所赚取的钱 FROM product
57 
58 需求:查询所有货品的id，名称，各进50个，并且每个运费1元的成本(使用别名)
59 
60 ------------------------------------------------------------------
61 
62 设置显示格式:
63 
64 为方便用户浏览查询的结果数据，有时需要设置显示格式，可以使用CONCAT函数来连接字符串。
65 
66 需求:查询所有货品名称和每零售1个产品所赚取的钱(每零售1个产品所赚取的钱=零售价-成本价格),
67 格式为: 每零售一个XXX产品,赚取:XX元
68 SELECT CONCAT('每零售一个',product_name,'产品,赚取:',sale_price-cost_price,'元') FROM product 
69 
70 需求:查询商品的名字和零售价。
71      格式：xxx商品的零售价为：xxx
72 
73 SELECT CONCAT(productName,'商品的零售价为：',salePrice) AS productSalePrice FROM product;
74  //CONCAT('你好','吗','?','我很好')  你好吗?我很好

5.带条件查询

  1 使用WHERE子句限定返回的记录：
  2 
  3 语法：
  4 
  5 SELECT  <selectList> 
  6 
  7 FROM        table_name
  8 
  9 WHERE    条件1 AND/OR 条件2;
 10 
 11 注意：
 12 
 13 WHERE子句在 FROM 子句后
 14 
 15 ----------------------------------------------------
 16 
 17 比较运算符               含义
 18 
 19 -------------------------------------------------
 20 
 21 =                     等于
 22 
 23 >                     大于
 24 
 25 >=                    大于或等于
 26 
 27 <                     小于
 28 
 29 <=                    小于或等于
 30 
 31 !=(<>)                不等于
 32 
 33 ---------------------------------------------------
 34 需求: 查询货品零售价大于119的所有货品信息.
 35 SELECT * FROM product WHERE sale_price>119
 36 
 37 需求: 查询货品零售价为119的所有货品信息.
 38 
 39 需求: 查询货品名为联想G9X的所有货品信息.
 40 
 41 需求: 查询货品名 不为 联想G9X的所有货品信息.
 42 
 43 需求: 查询分类编号不等于2的货品信息
 44 
 45 需求: 查询货品名称,零售价小于等于200的货品
 46 
 47 需求: 查询id，货品名称，批发价大于350的货品
 48 
 49 思考：where后面使用别名不行,总结select和where的执行顺序   先执行where ,在执行select
 50 
 51 
 52 
 53 SQL的执行顺序:
 54 
 55 1.先执行FROM子句:  确定查询哪一张表
 56 
 57 2.接着执行WHERE :  过滤筛选条件
 58 
 59 3.接着做SELECT  :  确定选择的列
 60 
 61 4.最后做ORDER BY:  对结果集按照某列排序
 62 
 63 ---------------------------------------------------
 64 
 65 注意：字符串和日期要用单引号扩起来.
 66 
 67 要让MySQL查询区分大小写，可以：使用 BINARY
 68 
 69 SELECT * FROM table_name WHERE BINARY product_name='g9x'
 70 
 71 SELECT * FROM table_name WHERE BINARY product_name='G9X'
 72 
 73 -----------------------------------------------------------------
 74 
 75 逻辑运算符                含义
 76 
 77 ----------------------------------------
 78 
 79 AND              如果组合的条件都是TRUE,返回TRUE
 80 
 81 OR               如果组合的条件之一是TRUE,返回TRUE
 82 
 83 NOT(!)               如果下面的条件是FALSE,返回TRUE`
 84 
 85 -------------------------------------------------------------
 86 
 87 需求: 选择id，货品名称，批发价在300-400之间的货品
 88 
 89 需求: 选择id，货品名称，分类编号为2,4的所有货品
 90 
 91 需求: 选择id，货品名词，分类编号不为2的所有商品
 92 
 93 需求: 选择id，货品名称，分类编号的货品零售价大于等于250或者是成本大于等于200
 94 
 95 -----------------------------------------------------------------------
 96 优先级            运算符
 97 ------------------------------------
 98 1                所有比较运算符
 99 2                NOT
100 3                AND
101 4                OR
102 
103 注意：括号将跨越所有优先级规则

 1 使用BETWEEN运算符显示某一值域范围的记录，这个操作符最常见的使用在数字类型数据的范围上，但对于字符类型数据和日期类型数据同样可用。
 2 格式：
 3 SELECT <selectList>
 4 FROM table_name
 5 WHERE 列名 BETWEEN minvalue AND maxvalue：闭区间。
 6 需求: 选择id，货品名称，批发价在300-400之间的货品
 7 需求: 选择id，货品名称，批发价不在300-400之间的货品
 8 --------------------------------------------------------
 9 使用IN运算符，判断列的值是否在指定的集合中。
10 格式： 
11 SELECT <selectList>
12 FROM table_name
13 WHERE 列名 IN (值1，值2....);
14 
15 需求:选择id，货品名称，分类编号为2,4的所有货品
16 需求:选择id，货品名称，分类编号不为2,4的所有货品
17 --------------------------------------------------------
18 IS NULL:判断列的值是否为空。
19 格式：WHERE  列名 IS NULL;
20 需求:查询商品名为NULL的所有商品信息。
21 --------------------------------------------------------
22 使用LIKE运算符执行通配查询，查询条件可包含文字字符或数字：
23 %:通配符：可表示零或多个字符。
24 _:通配符：可表示一个字符。
25 通配符：用来实现匹配部分值得特殊字符。
26 -----------------------------------------------------
27 需求: 查询id，货品名称，货品名称匹配'%联想M9_'
28 需求: 查询id，货品名称，分类编号,零售价大于等于200并且货品名称匹配'%联想M1__'

6.排序

使用ORDER BY子句将结果的记录排序.
ASC : 升序，缺省。
DESC: 降序。
ORDER BY 子句出现在SELECT语句的最后。
格式：
SELECT <selectList> 
FROM table_name
WHERE 条件
ORDER BY 列名1 [ASC/DESC],列名2 [ASC/DESC]...;
-------------------------------------------------------
需求:选择id，货品名称，分类编号,零售价并且按零售价降序排序
需求: 选择id，货品名称，分类编号,零售价先按分类编号排序,再按零售价排序
需求:查询M系列并按照批发价排序(加上别名)
需求:查询分类为2并按照批发价排序(加上别名)
注意：别名不能使用引号括起来,否则不能排序。
--------------------------------------------------------------------------
SELECT语句执行顺序：
先执行FROM--->接着执行WHERE--->再执行SELECT--->最后执行ORDER BY

7.分页

 1 分页查询:
 2 分页设计:
 3   假分页(逻辑分页): 把数据全部查询出来,存在于内存中,翻页的时候,直接从内存中去截取.
 4   真分页(物理分页): 每次翻页都去数据库中去查询数据.
 5 
 6   假分页: 翻页比较快,但是第一次查询很慢,若数据过大,可能导致内存溢出.
 7   真分页: 翻页比较慢,若数据过大,不会导致内存溢出.
 8 ----------------------------------------------------------------------------------------
 9 规定:每页显示3条数据. pageSize = 3
10 第一页:   SELECT * FROM `product` LIMIT 0, 3
11 第二页:   SELECT * FROM `product` LIMIT 3, 3
12 第三页:   SELECT * FROM `product` LIMIT 6, 3
13 第 N页:   SELECT * FROM `product` LIMIT (N-1)*3, 3
14 ----------------------------------------------------------------------------------------
15 分页查询的SQL:
16 SELECT * FROM table_name LIMIT ?,?;
17 SELECT * FROM table_name LIMIT beginIndex,pageSize;
18 beginIndex = (currentPage-1) * pageSize;
19 第一个?: 表示本页,开始索引(从0开始).
20 第二个?: 每页显示的条数

8.函数

 1 什么是聚集函数：统计函数.
 2 聚集函数作用于一组数据，并对一组数据返回一个值。
 3 -------------------------------------------------------
 4 聚集函数:
 5 COUNT：统计结果记录数  
 6 MAX：  统计计算最大值
 7 MIN：  统计计算最小值
 8 SUM：  统计计算求和
 9 AVG：  统计计算平均值
10 -------------------------------------------------------
11 需求:查询所有商品平均零售价   
    SELECT AVG(sale_price) FROM product
12 需求:查询商品总记录数
13 需求:查询分类为2的商品总数
14 需求:查询商品的最小零售价，最高零售价，以及所有商品零售价总和

9.分组

 1 分组查询:
 2 可以使用GROUP BY 子句将表中的数据分成若干组，再对分组之后的数据做统计计算，一般使用聚集函数才使用GROUP BY.
 3 语法格式：
 4 SELECT <selectList>,聚集函数
 5 FROM table_name  
 6 WHERE 条件
 7 GROUP BY 列名
 8 注意：GROUP BY 后面的列名的值要有重复性分组才有意义。
 9 
10 ----------------------------------------------------------------------
11 需求:查询每个商品分类编号和每个商品分类各自的平均零售价
   SELECT classify_id,AVG(sale_price) FROM product  GROUP BY classify_id
12 需求:查询每个商品分类编号和每个商品分类各自的商品总数。
13 需求:查询每个商品分类编号和每个商品分类中零售价大于100的商品总数：
14 需求:查询零售价总和大于1500的商品分类编号以及总零售价和：

10.笛卡尔积

 1 单表查询:从一张表中查询数据
 2 多表查询:从多张表中联合查询出数据
 3 ------------------------------------
 4 单表查询:
 5 SELECT <selectList>
 6 FROM  table_name
 7 -----------------------------------
 8 多表查询(最简单的):
 9 SELECT <selectList>
10 FROM  表名A,表名B
11 ----------------------------------------------------------------------
12 笛卡尔积:
13 需求:查询所有的货品信息+对应的货品分类信息
14 没有连接条件的表关系返回的结果。
15 多表查询会产生笛卡尔积:
16 
17 假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。
18 
19 实际运行环境下，应避免使用全笛卡尔集。
20 
21 解决方案: 在WHERE加入有效的连接条件---->等值连接
22 注意:
23 连接 n张表,至少需要 n-1个连接条件。

11.内连接查询

 1 内连接查询:
 2 内连接查询：是相对于外连接。
 3 内连接分为：隐式内连接、显示内连接(推荐)，其查询效果相同,仅仅只是语法不同而已。
 4 -----------------------------------------------
 5 隐式内连接：
 6 SELECT    <selectList>
 7 FROM    A ,B  WHERE A.列 = B.列
 8 -----------------------------------------------
 9 显示内连接(推荐写法)：.
10 SELECT    <selectList>
11 FROM    A [INNER] JOIN B ON A.列 = B.列 
12 -----------------------------------------------
13 需求:查询所有商品的名称和分类名称:
14 隐式内连接: SELECT p.product_name,pc.classify_name FROM product p,product_classify  pc WHERE p.classify_id = pc.id
15 显示内连接:  SELECT p.product_name,pc.classify_name FROM product p INNER JOIN product_classify pc ON p.classify_id = pc.id
16 显示内连接: SELECT p.product_name,pc.classify_name FROM product p  JOIN product_classify pc ON p.classify_id = pc.id
17 -----------------------------------------------
18 在做等值连接的时候,若A表中和B表中的列名相同. 则可以简写:
19 SELECT    <selectList>
20 FROM    A [INNER] JOIN B USING(同名的列)
21 前提:在emp员工和dept部门表中都有deptno部门编号列. 并且是外键关系。
22 则: SELECT * FROM emp JOIN dept USING (deptno)
23 ---------------------------------------------------------------------
24 使用表名前缀在多个表中区分相同的列。
25 在不同表中具有相同列名的列可以用表的别名加以区分。
26 使用别名可以简化查询。
27 使用表名前缀可以提高执行效率。
28 如果使用了表的别名，则不能再使用表的真名
29 ---------------------------------------------------------------------
30 需求: 查询货品id，货品名称，货品所属分类名称
31 需求: 查询零售价大于200的无线鼠标
32 需求: 查询零售价大于200的无线鼠标(使用表的别名)
33 需求: 查询每个货品对应的分类以及对应的库存
34 需求: 如果库存货品都销售完成,按照利润从高到低查询货品名称,零售价,货品分类（三张表）.

12.外连接查询

 1 外连接查询:
 2   左外连接:查询出JOIN左边表的全部数据查询出来,JOIN右边的表不匹配的数据使用NULL来填充数据.
 3   右外连接:查询出JOIN右边表的全部数据查询出来,JOIN左边的表不匹配的数据使用NULL来填充数据.
 4 ----------------------------
 5 语法格式:
 6 SELECT    <selectList>
 7 FROM    A LEFT/RIGHT [OUTER] JOIN B
 8   ON (A.column_name = B.column_name)];
 9 查询所有的商品信息和对应的分类信息.
10 左连接:
11 SELECT * FROM product p LEFT JOIN product_classify pc ON p.classify_id = pc.id
12 右连接:
13 SELECT * FROM product p RIGHT  JOIN product_classify pc ON p.classify_id = pc.id
14 ----------------------------------------------
15 在做等值连接的时候,若A表中和B表中的列名相同. 则可以简写:
16 SELECT    <selectList>
17 FROM    A LEFT JOIN B USING(同名的列)
18 前提:在emp员工和dept部门表中都有deptno部门编号列. 并且是外键关系
19 如: SELECT * FROM emp LEFT JOIN dept USING (deptno)
20 
21 思考：查询每种商品分类的名称和包含的的商品总数：
22 
23 SELECT pc.classify_name,COUNT(p.id)
24 
25 FROM product p RIGHT JOIN product_classify pc ON p.classify_id = pc.id
26 
27 GROUP BY pc.classify_name

13.自连接查询

 1 自连接查询:
 2   把一张表看成两张来做查询.
 3 需求: 查询每个商品分类的名称和父分类名称:
 4 
 5 隐式内连接：
 6 SELECT sub.classify_name,super.classify_name
 7         FROM product_classify super,product_classify sub
 8         WHERE sub.parent_id = super.id
 9 
10 显示内连接：
11 SELECT sub.classify_name,super.classify_name
12         FROM product_classify super JOIN product_classify sub
13 
14                 ON sub.parent_id = super.id

14.子查询

 1 什么是子查询（嵌套查询）：一个查询语句中嵌套在另一个查询语句中，内层查询的结果可以作为外层查询条件或者临时表。
 2 一般的，嵌套在WHERE或者FROM字句中。
 3 为什么使用子查询：
 4    多表连接查询过程：
 5    1）：两张表做笛卡尔积。
 6    2）：筛选匹配条件的数据记录。
 7    若，笛卡尔积记录数比较大，可能造成服务器崩溃。
 8 -----------------------------------------------------------------------
 9 单行单列子查询:
10 需求: 查询零售价比联想MX1100更高的所有商品信息。
11 SELECT * FROM product
12 WHERE salePrice > (
13     SELECT salePrice FROM product WHERE productName = '联想MX1100'
14 )
15 单行多列子查询:
16 需求: 查询分类编号和折扣与联想M100相同的所有商品信息。
17 SELECT * FROM product
18 WHERE (dir_id,cutoff)=(
19     SELECT dir_id,cutoff FROM product WHERE productName = '联想M100'
20 )
21 多行多列子查询:
22 需求: 查询各商品分类的分类编号，分类名称，商品数量，平均零售价。
23 
24 内连接：
25 SELECT  pc.id, pc.classify_name,COUNT(p.id),AVG(p.sale_price)
26 
27 FROM product p JOIN product_classify pc ON p.classify_id = pc.id
28 
29 GROUP BY pc.id
30 
31 产生的笛卡尔积记录:80条
32 SELECT COUNT(*) FROM product,product_classify
33 
34 子查询:
35 SELECT pc.id,pc.classify_name,countNum,avgSalePrice
36 
37 FROM product_classify pc JOIN (
38 
39         SELECT classify_id ,COUNT(id) countNum,AVG(sale_price) avgSalePrice
40 
41         FROM product 
42 
43         GROUP BY classify_id) p 
44 
45 ON pc.id = p.classify_id

15.保存之前先检查该数据是否存在

举例:角色不能一样

INSERT INTO tab_pc_role (role_name,remark) SELECT 'testMast2','测试权限' FROM DUAL WHERE NOT EXISTS (SELECT id FROM tab_pc_role WHERE role_name='testMast2')

16.考查与评估

　　a.练习题

 1 1.基本增删改查操作
 2 需求：添加一条数据到产品表   产品名称为苹果手机   卖价为5000
 3 需求：删除产品表中id=20的数据
 4 需求：删除产品表中id=20并且product_name='联想M115'的数据
 5 需求：删除产品表中id=20或者product_name='联想M115'的数据
 6 需求：删除产品表中product_name！='联想M115'的数据
 7 需求：把成本价大于100的所有商品的卖价修改为200，并且把名称修改为特殊商品
 8 需求:查询所有商品
 9 需求：查询id<8的数据
10 需求：查询id<8的数据，只看id，产品名称，卖价
11 
12 2.简单查询之列操作
13 需求:查询所有货品信息
14 需求:查询所有货品的id,product_name,sale_price
15 需求:查询商品的分类编号。
16 需求:查询所有货品的id，名称和每零售1个产品所赚取的钱(每零售1个产品所赚取的钱=零售价-成本价格)
17 需求:查询所有货品的id，名称和批发价(批发价=卖价*折扣) 
18 需求:查询所有货品的id，名称，和各进50个的成本价(成本=cost_price)
19 需求:查询所有货品的id，名称和每零售1个产品所赚取的钱(每零售1个产品所赚取的钱=零售价-成本价格),并取别名
20 需求:查询所有货品的id，名称，各进50个，并且每个运费1元的成本(使用别名)
21 需求:查询所有货品名称和每零售1个产品所赚取的钱(每零售1个产品所赚取的钱=零售价-成本价格),
22 需求:查询商品的名字和零售价。格式：xxx商品的零售价为：xxx
23 
24 3.带条件查询
25 需求: 查询货品零售价大于119的所有货品信息.
26 需求: 查询货品零售价为119的所有货品信息.
27 需求: 查询货品名为联想G9X的所有货品信息.
28 需求: 查询货品名 不为 联想G9X的所有货品信息.
29 需求: 查询分类编号不等于2的货品信息
30 需求: 查询货品名称,零售价小于等于200的货品
31 需求: 查询id，货品名称，批发价大于350的货品
32 需求: 选择id，货品名称，批发价在300-400之间的货品
33 需求: 选择id，货品名称，分类编号为2,4的所有货品
34 需求: 选择id，货品名词，分类编号不为2的所有商品
35 需求: 选择id，货品名称，分类编号的货品零售价大于等于250或者是成本大于等于200
36 需求: 选择id，货品名称，批发价在300-400之间的货品
37 需求: 选择id，货品名称，批发价不在300-400之间的货品
38 需求: 选择id，货品名称，分类编号为2,4的所有货品
39 需求: 选择id，货品名称，分类编号不为2,4的所有货品
40 需求: 查询商品名为NULL的所有商品信息。
41 需求: 查询id，货品名称，货品名称匹配'%联想M9_'
42 需求: 查询id，货品名称，分类编号,零售价大于等于200并且货品名称匹配'%联想M1__'
43 需求:选择id，货品名称，分类编号,零售价并且按零售价降序排序
44 需求: 选择id，货品名称，分类编号,零售价先按分类编号排序,再按零售价排序
45 需求:查询M系列并按照批发价排序(加上别名)
46 需求:查询分类为2并按照批发价排序(加上别名)
47 
48 4.函数
49 需求:查询所有商品平均零售价   
50 需求:查询商品总记录数
51 需求:查询分类为2的商品总数
52 需求:查询商品的最小零售价，最高零售价，以及所有商品零售价总和
53 
54 5.分组
55 需求:查询每个商品分类编号和每个商品分类各自的平均零售价
56 需求:查询每个商品分类编号和每个商品分类各自的商品总数。
57 需求:查询每个商品分类编号和每个商品分类中零售价大于100的商品总数：
58 需求:查询零售价总和大于1500的商品分类编号以及总零售价和
59 
60 6.综合查询
61 需求:查询所有的货品信息+对应的货品分类信息
62 需求:查询所有商品的名称和分类名称
63 需求: 查询货品id，货品名称，货品所属分类名称
64 需求: 查询零售价大于200的无线鼠标
65 需求: 查询零售价大于200的无线鼠标(使用表的别名)
66 需求: 查询每个货品对应的分类以及对应的库存
67 需求: 如果库存货品都销售完成,按照利润从高到低查询货品名称,零售价,货品分类（三张表）.
68 需求: 查询每个商品分类的名称和父分类名称
69 需求: 查询零售价比联想MX1100更高的所有商品信息。
70 需求: 查询分类编号和折扣与联想M100相同的所有商品信息。
71 需求: 查询各商品分类的分类编号，分类名称，商品数量，平均零售价。

练习sql

b.需求:以常见OA系统中的员工部门为案例完成:

　　1.设计员工表

　　2.设计部门表

　　3.sql语句练习

　　3.1查询所有员工按照年龄排序

　　3.2查询各部门的平均工资

　　3.3查询各部门人数并按照部门人数排序

　　3.4查询研发部门的所有员工

　　3.5查询工资高于10000的员工信息

　　3.6查询工资低于平均工资的员工信息

17.练习题sql语句答案

  1 CREATE TABLE `product` (
  2   `id` int(11) NOT NULL AUTO_INCREMENT,
  3   `product_name` varchar(50) DEFAULT NULL,
  4   `classify_id` int(11) DEFAULT NULL,
  5   `sale_price` double(10,2) DEFAULT NULL,
  6   `supplier` varchar(50) DEFAULT NULL,
  7   `brand` varchar(50) DEFAULT NULL,
  8   `cutoff` double(2,2) DEFAULT NULL,
  9   `cost_price` double(10,2) DEFAULT NULL,
 10   PRIMARY KEY (`id`)
 11 ) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;
 12 
 13 1.基本增删改查操作
 14 需求：添加一条数据到产品表   产品名称为苹果手机   卖价为5000
 15 INSERT INTO product (product_name,sale_price) VALUES ('苹果手机',5000);
 16 
 17 需求：删除产品表中id=20的数据
 18 DELETE FROM product WHERE id=20;
 19 需求：删除产品表中id=20并且product_name='联想M115'的数据
 20 DELETE FROM product WHERE id=20 AND product_name='联想M115';
 21 
 22 需求：删除产品表中id=20或者product_name='联想M115'的数据
 23 DELETE FROM product WHERE id=20 OR product_name='联想M115';
 24 
 25 
 26 需求：删除产品表中product_name！='联想M115'的数据
 27 DELETE FROM product WHERE product_name='联想M115';
 28 
 29 
 30 需求：把成本价大于100的所有商品的卖价修改为200，并且把名称修改为特殊商品
 31 UPDATE product SET sale_price=200,product_name='特殊商品' WHERE cost_price>100
 32 
 33 
 34 需求:查询所有商品
 35 SELECT * FROM product
 36 
 37 需求：查询id<8的数据
 38 SELECT * FROM product WHERE id<8
 39 
 40 需求：查询id<8的数据，只看id，产品名称，卖价
 41 
 42 SELECT id,product_name,sale_price FROM product WHERE id<8
 43 
 44 2.简单查询之列操作
 45 需求:查询所有货品信息
 46 
 47 SELECT * FROM product
 48 
 49 需求:查询所有货品的id,product_name,sale_price
 50 SELECT id,product_name,sale_price FROM product 
 51 
 52 
 53 需求:查询商品的分类编号。
 54 SELECT classify_id FROM product
 55 
 56 需求:查询所有货品的id，名称和每零售1个产品所赚取的钱(每零售1个产品所赚取的钱=零售价-成本价格)
 57 
 58 SELECT id,product_name,sale_price,cost_price,(sale_price-cost_price)*1 FROM product 
 59 
 60 需求:查询所有货品的id，名称和批发价(批发价=卖价*折扣) 
 61 SELECT id,product_name,sale_price,cutoff,(sale_price*cutoff) FROM product 
 62 
 63 需求:查询所有货品的id，名称，和各进50个的成本价(成本=cost_price)
 64 SELECT id,product_name,cost_price,cost_price*50 FROM product 
 65 
 66 需求:查询所有货品的id，名称和每零售1个产品所赚取的钱(每零售1个产品所赚取的钱=零售价-成本价格),并取别名
 67 SELECT id,product_name,sale_price,cost_price,(sale_price-cost_price)*1 每零售1个产品所赚取的钱 FROM product 
 68 
 69 
 70 需求:查询所有货品的id，名称，各进50个，并且每个运费1元的成本(使用别名)
 71 SELECT id,product_name,cost_price,(cost_price+1)*50 成本 FROM product 
 72 
 73 
 74 需求:查询所有货品名称和每零售1个产品所赚取的钱(每零售1个产品所赚取的钱=零售价-成本价格)
 75 SELECT product_name,sale_price,cost_price,(sale_price-cost_price) 利润 FROM product
 76 
 77 需求:查询商品的名字和零售价。格式：xxx商品的零售价为：xxx
 78 
 79 SELECT product_name,'商品的零售价为：',sale_price FROM product 
 80 SELECT CONCAT(product_name,'商品的零售价为：',sale_price) FROM product
 81 
 82 3.带条件查询
 83 需求: 查询货品零售价大于119的所有货品信息.
 84 SELECT * FROM product WHERE sale_price>119
 85 
 86 需求: 查询货品零售价为119的所有货品信息.
 87 SELECT * FROM product WHERE sale_price=119
 88 
 89 
 90 需求: 查询货品名为联想G9X的所有货品信息.
 91 SELECT * FROM product WHERE product_name='联想G9X'
 92 
 93 
 94 需求: 查询货品名 不为 联想G9X的所有货品信息.
 95 SELECT * FROM product WHERE product_name !='联想G9X'
 96 
 97 
 98 需求: 查询分类编号不等于2的货品信息
 99 
100 SELECT * FROM product WHERE classify_id!=2
101 
102 需求: 查询货品名称,零售价小于等于200的货品
103 
104 SELECT product_name,sale_price FROM product WHERE sale_price=200
105 
106 需求: 查询id，货品名称，批发价大于350的货品
107 
108 SELECT id, product_name,sale_price*cutoff FROM product WHERE sale_price*cutoff>350
109 
110 需求: 选择id，货品名称，批发价在300-400之间的货品
111 SELECT id, product_name,sale_price*cutoff FROM product WHERE sale_price*cutoff>300 AND sale_price*cutoff<400
112 
113 
114 需求: 选择id，货品名称，分类编号为2,4的所有货品
115 SELECT id, product_name,classify_id FROM product WHERE classify_id=2 OR  classify_id=4
116 
117 SELECT id, product_name,classify_id FROM product WHERE classify_id  IN (2,4) # (推荐使用这种)
118 
119 
120 
121 需求: 选择id，货品名词，分类编号不为2的所有商品
122 SELECT id, product_name,classify_id FROM product WHERE classify_id !=2 # (推荐使用这种)
123 
124 SELECT id, product_name,classify_id FROM product WHERE classify_id NOT IN (2) 
125 
126 
127 
128 需求: 选择id，货品名称，分类编号的货品零售价大于等于250或者是成本大于等于200
129 
130 SELECT id, product_name,classify_id FROM product WHERE sale_price>=250 OR cost_price>=200
131 需求: 选择id，货品名称，批发价在300-400之间的货品
132 SELECT id, product_name,sale_price*cutoff FROM product WHERE sale_price*cutoff>300 AND sale_price*cutoff<400  #推荐这种
133 
134 
135 SELECT id, product_name,sale_price*cutoff FROM product WHERE  sale_price*cutoff BETWEEN  300 AND 400
136 
137 
138 
139 需求: 选择id，货品名称，批发价不在300-400之间的货品
140 
141 SELECT id, product_name,sale_price*cutoff FROM product WHERE sale_price*cutoff<=300 OR sale_price*cutoff>=400  #推荐这种
142 
143 SELECT id, product_name,sale_price*cutoff FROM product WHERE  sale_price*cutoff  NOT BETWEEN  300 AND 400
144 
145 
146 需求: 选择id，货品名称，分类编号为2,4的所有货品
147 SELECT id, product_name,classify_id FROM product WHERE classify_id=2 OR  classify_id=4
148 
149 SELECT id, product_name,classify_id FROM product WHERE classify_id  IN (2,4) # (推荐使用这种)
150 
151 需求: 选择id，货品名称，分类编号不为2,4的所有货品
152 SELECT id, product_name,classify_id FROM product WHERE classify_id!=2 AND  classify_id!=4
153 
154 SELECT id, product_name,classify_id FROM product WHERE classify_id NOT  IN (2,4) # (推荐使用这种)
155 
156 需求: 查询商品名为NULL的所有商品信息。
157 SELECT * FROM product WHERE product_name IS NULL
158 
159 SELECT * FROM product WHERE product_name = NULL  （这是错误的写法     必须重视，初学者很容易犯错）
160 
161 需求: 查询id，货品名称，货品名称匹配'%联想M9_'
162 SELECT id, product_name FROM product WHERE product_name LIKE '%联想M9_'
163 
164 
165 需求: 查询id，货品名称，分类编号,零售价大于等于200并且货品名称匹配'%联想M1__'
166 SELECT id, product_name,classify_id,sale_price FROM product WHERE product_name LIKE '%联想M1__' AND sale_price>=200
167 
168 
169 需求:选择id，货品名称，分类编号,零售价并且按零售价降序排序
170 SELECT id, product_name,classify_id,sale_price FROM product ORDER BY sale_price DESC   (默认的ASC是升序)
171 SELECT id, product_name,classify_id,sale_price FROM product ORDER BY sale_price ASC 
172 
173 需求: 选择id，货品名称，分类编号,零售价先按分类编号排序,再按零售价排序
174 SELECT id, product_name,classify_id,sale_price FROM product ORDER BY classify_id ASC ,sale_price ASC 
175 
176 
177 需求:查询M系列并按照批发价排序(加上别名)
178 
179 #ORDER BY sc   可以使用别名，因为执行select在执行order。。by..
180 SELECT id, product_name,classify_id,sale_price,cutoff,(sale_price*cutoff) sc FROM product WHERE product_name LIKE '%M%' ORDER BY sc
181 
182 需求:查询分类为2并按照批发价排序(加上别名)
183 
184 SELECT id, product_name,classify_id,sale_price,cutoff,(sale_price*cutoff) sc FROM product WHERE classify_id=2 ORDER BY sc
185 
186 
187 4.函数
188 需求:查询所有商品平均零售价
189 
190 SELECT AVG(sale_price) FROM product
191    
192 需求:查询商品总记录数
193 SELECT COUNT(1) FROM product
194 
195 需求:查询分类为2的商品总数
196 SELECT COUNT(1) FROM product WHERE classify_id=2
197 
198 需求:查询商品的最小零售价，最高零售价，以及所有商品零售价总和
199 
200 SELECT MIN(sale_price),MAX(sale_price),SUM(sale_price) FROM product 
201 
202 5.分组
203 需求:查询每个商品分类编号和每个商品分类各自的平均零售价
204 
205 SELECT classify_id,AVG(sale_price) FROM product GROUP BY classify_id
206 
207 需求:查询每个商品分类编号和每个商品分类各自的商品总数。
208 SELECT classify_id,COUNT(1) FROM product GROUP BY classify_id
209 
210 需求:查询每个商品分类编号和每个商品分类中零售价大于100的商品总数：
211 SELECT classify_id,COUNT(1) FROM product WHERE sale_price>100 GROUP BY classify_id
212 
213 需求:查询零售价总和大于1500的商品分类编号以及总零售价和
214 
215 SELECT classify_id,SUM(sale_price) s FROM product GROUP BY classify_id HAVING s>1500
216 
217 6.综合查询
218 需求:查询所有的货品信息+对应的货品分类信息
219 
220 SELECT * FROM product p LEFT  JOIN product_classify pc ON (p.classify_id=pc.id)
221 
222 需求:查询所有商品的名称和分类名称
223 
224 SELECT p.product_name,pc.classify_name FROM product p LEFT  JOIN product_classify pc ON (p.classify_id=pc.id)
225 
226 
227 需求: 查询货品id，货品名称，货品所属分类名称
228 SELECT p.id,p.product_name,pc.classify_name FROM product p LEFT  JOIN product_classify pc ON (p.classify_id=pc.id)
229 
230 
231 需求: 查询零售价大于200的无线鼠标
232 SELECT p.id,p.product_name,pc.classify_name,p.sale_price 
233 FROM product p LEFT  JOIN product_classify pc ON (p.classify_id=pc.id) 
234 WHERE p.sale_price>200 AND pc.classify_name='无线鼠标'
235 
236 需求: 查询零售价大于200的无线鼠标(使用表的别名)
237 SELECT p.id,p.product_name,pc.classify_name,p.sale_price 
238 FROM product p LEFT  JOIN product_classify pc ON (p.classify_id=pc.id) 
239 WHERE p.sale_price>200 AND pc.classify_name='无线鼠标'
240 
241 需求: 查询每个货品对应的分类以及对应的库存
242 SELECT * 
243 FROM product p 
244 LEFT  JOIN product_classify pc ON (p.classify_id=pc.id)
245 LEFT JOIN product_stock ps ON (ps.product_id=p.id)
246 
247 需求: 如果库存货品都销售完成,按照利润从高到低查询货品名称,零售价,货品分类（三张表）.
248 SELECT p.product_name,p.sale_price,pc.classify_name,(p.sale_price-p.cost_price)*ps.store_num c
249 FROM product p 
250 LEFT  JOIN product_classify pc ON (p.classify_id=pc.id)
251 LEFT JOIN product_stock ps ON (ps.product_id=p.id)
252 ORDER BY c DESC
253 
254 需求: 查询每个商品分类的名称和父分类名称
255 
256 SELECT * 
257 FROM product_classify a
258 LEFT JOIN product_classify b ON (a.parent_id=b.id)
259 
260 
261 需求: 查询零售价比联想MX1100更高的所有商品信息。
262 
263 SELECT sale_price FROM product WHERE product_name='联想MX1100'   #sale_price=119
264 
265 SELECT * FROM product WHERE sale_price>119
266 
267 使用的是子查询
268 
269 SELECT * FROM product WHERE sale_price>(SELECT sale_price FROM product WHERE product_name='联想MX1100' )
270 
271 
272 
273 需求: 查询分类编号和折扣与联想M100相同的所有商品信息。
274 
275 SELECT classify_id,cutoff FROM product WHERE product_name='联想M100'
276 
277 SELECT * FROM product WHERE (classify_id,cutoff) = (3,0.9)
278 
279 #单行多列 子查询  
280 
281 最后：
282 SELECT * FROM product WHERE (classify_id,cutoff) = (SELECT classify_id,cutoff FROM product WHERE product_name='联想M100')
283 
284 
285 需求: 查询各商品分类的分类编号，分类名称，商品数量，平均零售价。
286 
287 SELECT p.classify_id,pc.classify_name,COUNT(1),AVG(p.sale_price)
288 FROM product p
289 LEFT JOIN product_classify  pc ON (p.classify_id=pc.id)
290 GROUP BY p.classify_id

View Code

到此数据库第一阶段课程类容就完了，每一位学习者必须认真的把博客上的需求写完整，然后我们做一个习题讲解！