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 需求: 查询各商品分类的分类编号，分类名称，商品数量，平均零售价。
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
到此数据库第一阶段课程类容就完了，每一位学习者必须认真的把博客上的需求写完整，然后我们做一个习题讲解！
