学习数据库笔记三

一

select查询模型？

灰常重要！决定你能否写出强大的查询语句！

模型【列是变量（变量可以计算，所以列可以计算）

           where后是表达式，值为真假，值为真时取出这行变量（列）的值】 （where id=3和id为3的进行比较，结果为真，就取id为3的行）

**所以SQL语句的执行过程分两步1.判断where后表达式的值，2.等于true时，取出列（变量）的值，否则不取出。

select * from user where 1;         这条语句按照模型来解释就很好理解了，表达式什么情况下都等于真，那就是整个表的行都等于真，取出*所有列，结果就是取出整张表的数据

select * from user where  0;       这条语句是不管什么情况都为假，那就根本没有符合条件的行数，所以一行内容也没有查出

现在来说说’变量（列）可以计算‘？

先来个简单的，假如查询明年的年龄

select uid,name,age+1 from user;      直接在age上加1

假如你的老板让你查询本店铺商品比市场价格便宜多少

select goods_id,goods_name,shop_price,market_price,market_price-shop_price from goods;       直接拿市场价格减去本店价格就可以了

只查某几列的术语叫“投影运算”，意为把这两列的值投影出来，你要取这两列的值可以说，把这哪几列做个投影。

把列看成变量，并拿两列做运算，这叫做“广意投影”。

二

SQL语句查询练习

1.查询出商品ID为32的商品

select goods_id,goods_name from goods where goods_id=32;

2.查询出不属于第3栏目的所有商品（栏目列名cat_id）

select cat_id,goods_id,goods_name from goods wher cat_id <> 3;         在SQL语句中<>和！=都是不等于的意思

3.查询出本店价格大于3000的商品

select shop_price,goods_id,goods_name from goods where shop_price >3000;

4.查询出本店价格低于或等于100元的商品

select shop_price,goods_id,goods_name from goods where shop_price <=100;

5.查询出栏目为4或11的所有商品，不许用or

不许用or就用||，或in

用||   select cat_id,goods_id,goods_name from goods where cat_id=4 || cat_id=11;

它出这题目的意思是考你会不会用in，所以用下面这样写法好

用in    select cat_id,goods_id,goods_name from goods where cat_id in (4,11);

6.查询出价格大于100小于500的商品，不许用or

select shop_price,goods_id,goods_name from goods where shop_price between 100 and 500;

这种写法也可以

select shop_price,goods_id,goods_name from goods where (shop_price >=100) and (shop_price<=500);

7.查询出栏目id不等于3和栏目id不等于11的商品，用and和not in分别实现

select cat_id,goods_id,goods_name from goods where (cat_id<>3) and (cat_id<>11);

select cat_id,goods_id,goods_name from goods where cat_id not in (3,11);

8.查询出商品价格大于100小于500 或 大于4000小于5000的商品

select shop_price,goods_id,goods_name from goods where (shop_price between 100 and 500) or (shop_price between 4000 and 5000);

9.取出第3栏目下，价格小于1000或大于3000，并且点击量大于5的商品

这种情况把条件一一列出用and或or连接它们

select cat_id,shop_price,click_count,goods_name from goods where (cat_id=3) and (shop_price<1000 or shop_price>3000) and (click_count>5);

10.取出第一个栏目下的商品（第一个栏目通常是父结点，一般它下面是没有直接的商品的，而是放子栏目，所以在没有学子查询的情况下，要先看栏目表中，哪些属于栏目一，把这些栏目下的商品取出）

select cat_id,goods_id,goods_name from goods where cat_id in (2,3,4,5);   

11.取出以‘诺基亚’开头的商品

select goods_id,goods_name from goods where goods_name like '诺基亚%';      记不清的部分用%代替，它代表任意字符。

12.取出名字为‘诺基亚Nxx’的商品

select goods_id,goods_name from goods where goods_name like '诺基亚N__';      _代表一个字符

13.取出名字不以‘诺基亚’开头的商品

select goods_id,goods_name from goods where goods_name not like '诺基亚%';

14.取出第3个栏目下，价格大于1000小于3000，点击量大于5，名字以‘诺基亚’开头的商品*

select cat_id,shop_price,goods_name from goods where (cat_id=3) and (shop_price between 1000 and 3000) and (click_count >5) and (goods_name like '诺基亚%');

like模糊匹配

%匹配任意字符

_匹配单一字符

三

两道面试题

1.把num值处于[20,29]之间的数，改为20

   把num值处于[30,39]之间的数，改为30

答update mian set num=floor(num/10)*10 where num between 20 and 39;

解题思路  首先where的范围在20~39，这是把20、30两个范围的数一起算，然后对这20~39的数进行num=floor(num/10)*10，先除以10，再向下取整，现在20多的都是2，30多的都是3，再乘以10就得到想要的结果了。

2.把goods表中商品名为‘诺基亚xxx’的商品改为‘HTCxxx’ 

这道题比较复杂，用到了函数

可以分步骤完成

a)   select goods_name from goods where goods_name like '诺基亚___';     先把‘诺基亚xxx’的数据查出来

+------------+
| 诺基亚e66       |
| 诺基亚n96       |
| 诺基亚n85       |
+------------+

b)   select  substring(goods_name,4)  from goods where goods_name like '诺基亚___';     用substring函数把前面诺基亚三个字截取掉

+-------------------------+

| substring(goods_name,4) |
+-------------------------+
| e66                     |
| n96                     |
| n85                     |
+-------------------------+

c)   select  concat('HTC',substring(goods_name,4))  from goods where goods_name like '诺基亚___';      用concat函数在前面拼接HTC三个字符，即可替换成功。