一个SQL小白的部分笔记

sql笔记

• 一张图总结sql语句的使用顺序：

• 其它基本使用可参照
  （https://www.jianshu.com/p/e8f089c46804）

主键约束

• 由多列同时形成主键：

primary key(column1, column2)

• 外键约束

    foreign key(学号)
    references
    学生表（学号）;

• 向表中添加列

  alter table 学生表 add 身高 numeric(3,2);

• 改变表中的列

sql语句不支持直接改变列，所以用以下方法较合适

 alter table 学生表 add 党员否 bit;

 update 学生表 set 党员否 = 是否党员;

 alter table 学生表 drop 是否党员;

• 添加条件限制

alter table 学生表 add constraint check(身高 between 1 and 2.0）)

• 添加唯一约束

alter tbale 课程表 add unique(课程号);

• 连接

  • 理解join—on—:每一个join后加一个表名,相当于打开一个表,on后接查询的选择条件;
  • 特点:同时打开几张表进行筛选
  • 可分为内连接(inner join) 和 外连接(outer join)

  • 除了inner join 外的都叫外连接

  • 主要分为以下四种情况

    1. INNER JOIN 等同于（INNER）——>取交集

    

    SELECT * FROM TableA INNER JOIN TableB ON 
    TableA.name = TableB.name

    1. FULL [OUTER] JOIN ——>取并集

    

  SELECT * FROM TableA FULL OUTER JOIN TableB ON
  TableA.name = TableB.name

  1. LEFT [OUTER] JOIN ——>取左表的全集

     
     SELECT * FROM TableA LEFT OUTER JOIN TableB ON
     TableA.name = TableB.name

  2. RIGHT [OUTER] JOIN

  3. UNION 与 UNION ALL

  特例:

  • 两种操作方式（两表关联查询）：

    1. select
       学生表.学号,姓名,课程号,成绩
       from 学生表,成绩表
       where 成绩表.成绩>80 and 成绩表.学号=学生表.学号;
       2.
       select a.学号,姓名,课程号,成绩
       from 学生表 as a join 成绩表 as b
       on b.成绩>80 and a.学号=b.学号;
       两种操作的效果图相同(如图)：
       

       • 三表操作
         

         查出李大方和李四方两个的人大学英语和高等数学成绩
         (1)
         select 学号,成绩
         from 成绩表 where 学号 in(
         select 学号 from 学生表 where 姓名 like '李[四大]方')
         and 课程号 in(
         select 课程号 from 课程表 where 
           名称 in('大学英语','高等数学下')
         )
         
         (2)
         例要看到完整数据:应该使用关联连接查询
         select 学生表.学号,姓名,名称,成绩
         from 学生表 join 成绩表 on 学生表.学号=成绩表.学号
         join 课程表 on 成绩表.课程号=课程表.课程号
         where  姓名 like '李[四大]方'
         and 课程表.名称 in('大学英语','高等数学下')
         

• Group By与聚合函数

  • Group by

  注意：group by关键字只对聚合函数起作用，记住这一点将对你理解这个关键很有帮助，
  “Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。

  • 函数

    函数	作用
    sum(列名)	求和
    max(列名)	最大值
    min(列名)	最小值
    avg(列名)	平均值
    first(列名)	第一条记录
    last(列名)	最后一条记录
    count(列名)	统计记录数

    1. sum(数值型列) 纵向求和

  例:把成绩表中所指列的值累加起来

  1. count(任意列名/*) 统计满足条件的记录行数

  例:求 某一个记录的属性的总数

  1. average(数值型列) 纵向上求平均数

例如：从成绩表中查询某个同学的总成绩，选课门数，平均分：

    select sum(成绩) as 成绩和,count(学号) as
    门数,avg(成绩) as 科平均 from 成绩表  where
    学号='3120065468';

1. 聚合函数常和group by联合起来使用

   • 示例：求各组平均值

     select 类别, avg(数量) AS 平均值 from A group by 类别;

   • 示例：求各组记录数目

     select 类别, count(*) AS 记录数 from A group by 类别;

   • Having与Where的区别

   • where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，where条件中不能包含聚组函数，使用where条件过滤出特定的行。

   • having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件过滤出特定的组，也可以使用多个分组标准进行分组。

   • 示例

     select 类别, sum(数量) as 数量之和 from A
     group by 类别
     having sum(数量) > 18

   • 示例9：Having和Where的联合使用方法

     select 类别, SUM(数量)from A
     where 数量 >8
     group by 类别
     having SUM(数量) = 10

   • Mysql 与 SQL server 常的区别

   • *

   MySQL	SQL server
   LIMIT s_num1,e_num2	TOP number/num PERSENT

   • 求两个表（查询结果）的差集

   • 求两个查询结果的差集，Mysql直接提供了关键字not in，但是not in的效率极其低下，出现例如求一个上千查询结果在上万查询结果的差集，那么使用not in来查询的查询速度极其缓慢，这是必须使用左链接的方式求查询。

   • 先从简单的说起

   比如在表blog的id情况是这样的：

   id
   1
   2
   3
   4
   5
   6

   在表usertable的id情况是这样的：

   id
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10

   现在要求这两个结果的差集，可以使用如下的语句：

   select id from usertable  
   where id not in (select id from blog)  

   但是，not in的查询效率是极度低下的，主要是去到上万，仅仅是上万级的数据查询就会出现在查询的时候死机，无法查询的情况，因此还不得不得换做左链接查询的方式，具体如下：

   select id FROM usertable LEFT JOIN  
   (select id as i from blog) as t1  
   ON usertable.id=t1.i where t1.i IS NULL  

   这里blog的id，也就是t1中的id，还必须换成i，或者其它变量名，不然连接的时候会出现混乱

   这种左链接的查询方式得出来结果与not in一样，但是查询时间与效率，却是复杂的左链接远远快于not in：

   id
   7
   8
   9
   10