【数据库复习_基本查询】

基本的select语句包含以下4部分内容：

select select_list

from table_list

where search_conditions group by group_by_list having search_condition

order by order_list.

下来详细描述下：

1、where常用运算符：

  比较运算符
                > ,  < ,=  , != （< >）,>=   ,   <=  
                in(v1,v2..vn)  
                between v1 and v2    在v1至v2之间（包含v1,v2）
  逻辑运算符
                not ( ! )  逻辑非
                or ( || )    逻辑或
                and ( && )  逻辑与

模糊查询
                like 像
                通配符:
                %  任意字符
                _   单个字符
                    where goods_name like '诺基亚%'
                    where goods_name like '诺基亚N__'

2、group by 分组

 一般情况下group需与统计函数（聚合函数）一起使用才有意义

                如：select goods_id,goods_name,cat_id,max(shop_price) from goods group by cat_id;

                        这里取出来的结果中的good_name是错误的！因为shop_price使用了max函数，那么它是取最大的，而语句中使用了group by 分组，那么goods_name并没有使用聚合函数，它只是cat_id下的第一个商品，并不会因为shop_price改变而改变

3、having 与where 的异同点

where搜索条件实在分组操作之前对记录进行筛选，然后再由group by对筛选后符合田间的进行分组。而Having搜索条件则是对分组操作之后得到的行在进行筛选操作。在Having子句之中可以包含聚合函数，而where子句不能。

   where针对表中的列发挥作用， 查询数据

                    having对查询结果中的列发挥作用， 筛选数据

                    #查询本店商品价格比市场价低多少钱，输出低200元以上的商品

                    select goods_id,good_name,market_price - shop_price as s from goods havings >200 ;

                    //这里不能用where因为s是查询结果，而where只能对表中的字段名筛选

                    如果用where的话则是：

                    select goods_id,goods_name from goods where market_price - shop_price > 200;

 

                    #同时使用where与having

                    select cat_id,goods_name,market_price - shop_price as s from goods where cat_id = 3 having s > 200;

                    #查询积压货款超过2万元的栏目，以及该栏目积压的货款

                    select cat_id,sum(shop_price * goods_number) as t from goods group by cat_id having s > 20000

                    #查询两门及两门以上科目不及格的学生的平均分

                          思路：

                            #先计算所有学生的平均分

                             select name,avg(score) as pj from stu group by name;

                            #查出所有学生的挂科情况

                            select name,score<60 from stu;

                                    #这里score<60是判断语句，所以结果为真或假，mysql中真为1假为0

                            #查出两门及两门以上不及格的学生

                            select name,sum(score<60) as gk from stu group by name having gk > 1;

                            #综合结果

                            select name,sum(score<60) as gk,avg(score) as pj from stu group by name having gk >1;

  4、order by

                    （1） order by price  //默认升序排列

                    （2）order by price desc //降序排列

                    （3）order by price asc //升序排列，与默认一样

                    （4）order by rand() //随机排列，效率不高

                        #按栏目号升序排列，每个栏目下的商品价格降序排列

                        select * from goods where cat_id !=2 order by cat_id,price desc;

基本查询其实挺简单的。下面主要说一下级联查询。

      1、where型子查询

                 （把内层查询结果当作外层查询的比较条件）

                #不用order by 来查询最新的商品

                select goods_id,goods_name from goods where goods_id = (select max(goods_id) from goods);

                #取出每个栏目下最新的产品(goods_id唯一)

                select cat_id,goods_id,goods_name from goods where goods_id in(select max(goods_id) from goods group by cat_id);

 

         2、from型子查询

                 (把内层的查询结果供外层再次查询)

                #用子查询查出挂科两门及以上的同学的平均成绩

                    思路：

                        #先查出哪些同学挂科两门以上

                        select name,count(*) as gk from stu where score < 60 having gk >=2;

                        #以上查询结果，我们只要名字就可以了，所以再取一次名字

                        select name from (select name,count(*) as gk from stu having gk >=2) as t;

                        #找出这些同学了，那么再计算他们的平均分

                        select name,avg(score) from stu where name in (select name from (select name,count(*) as gk from stu having gk >=2) as t) group by name;

 

         3、exists型子查询

                 （把外层查询结果拿到内层，看内层的查询是否成立）

                #查询哪些栏目下有商品，栏目表category,商品表goods

                    select cat_id,cat_name from category where exists(select * from goods where goods.cat_id = category.cat_id);

4.交叉连接：cross  join

     即让两个表进行笛卡尔积。数目等于：表1的数量*表2的数量

    语法格式： select * from table1 cross join table2;

  5.内连接

  使用=、>、<进行条件连接。

 语法：select* from table1 inner join table2 on table1.colmn = table2.column ,其中inner可以省略。

              select* from table1 join table2 on table1.column = table2.column

 内链接的效果与等值连接差不多。

   6.外连接

 外连接有三种不同的连接方法：

  左外：left join .. on

  右外:right join .. on

  完全外部连接:full join .. on

  

下来看下事例：

create table employee(
id int,
d_id int,
name varchar(32),
age int,
sex varchar(32),
homeaddr varchar(32)
);

create table department(
	d_id int,
    d_name varchar(32),
    function varchar(32),
    address varchar(32)
);

insert into employee values (1,1001,'张三',26,'男','北京市海淀区'),(2,1001,'李四',19,'女','北京市昌平区'),(3,1002,'王五',25,'男','湖南长沙市'),(4,1004,'Aric',15,'男','englian')

insert into department values (1001,'科技部','研发产品','3号楼5曾'),(1002,'生产部','生产产品','5号楼1层'),(1003,'销售部','销售产品','1号楼销售大厅')

左外查询：显示符合条件的数据行，同时显示左边数据表不符合条件的数据行，右边没有对应的条目显示NULL 

select * from employee left join department on employee.d_id = department.d_id

我们发现结果的数目与empoyee表的数目相同。最后一个aric中在department里面没有数据，用null 填补。

右外查询： 显示符合条件的数据行，同时显示右边数据表不符合条件的数据行，左边没有对应的条目显示NULL

     

select * from employee right join department on employee.d_id = department.d_id

我们发现这时候的查询结果，保持右表的基准。但是数目却不同~ 右表有三个，但是最后的结果有四个。

原因是有两个人的d_id都是1001.

7.全外连接： 显示符合条件的数据行，同时显示左右不符合条件的数据行，相应的左右两边显示NULL