自学SQL总结 by lighting_cui

转载请注明出处。

 

环境：MSSQL2000

 

环境：MSSQL2000

 

数据库操作常用功能——无非三种：创建、察看（查找）、修改。

一、SQL的创建功能
    包括对数据库、表、索引、数据还有存储过程触发器的创建——create。
 新建数据库：
          create database 数据库名。

 新建表： 
            create table 表名 (列名1 类型,列名2 类型，…)

            Tips：创建表之前应该先选择对应数据库： 
                       USE 数据库名

 新建索引：
            create [unique|cluster] index 索引名 on 表名(索引列1，索引列2…)
  
            Tip1：unique|cluster——唯一索引|聚集索引
            Tip2：创建索引后：改变表中数据索引自动更新，索引建立后查询表时索引自动被使用，索引数目不限制但索引越多更新数据越慢（对于仅用于查询的表可以多用索引，对于数据更新频繁的尽量少建立索引）
            eg：对表s的var列创建唯一索引s_var_only
                       create unique index s_var_only on s(var)

 新建视图：(把一个SELECT语句返回的结果作为一个新的逻辑表并且以一个名字进行保存，视图也可以用在SELECT语句中)
            create view 视图名 as SELECT语句
 
 新建存储过程（属于Transact-SQL程序,只了解简单功能）： 
            create procedure 过程名（@变量1，@变量2，@输出变量 output， …） as 语句

            eg1:定义显示表s中所有数据的存储过程show_s
                       create procedure show_s
                       as
                       select * from s
            eg2:定义带参数的向表s中添加数据的存储过程add_s
                       create procedure add_s
                       ( @var1 char(6),
                         @var2 char(6)
                       )
                       as
                       insert into s(s_var1,s_var2) values(@var1,@var2)
            eg3:定义带输出存储过程out_s
                       create procedure out_s
                       ( @var_in char(2),
                         @var_out char(4) output
                       )
                       as
                       select @var_out from s where s_var=@var_in

 新建触发器（可看作特殊的存储过程,简单了解）：
            create trigger 触发器名
            on 对应表名
            for 触发条件[eg:insert,updata,delete]
            as 语句
  
            eg:当表s数据变动时显示表的所有数据
                       create tigger onChange
                       on s
                       for insert,updata,delete
                       as
                       select * from s
 
二、SQL的察看功能——select
   
 select 列名1 [as '别名'], 列名2
 from 表名1，表名2
 where 检索条件
 group by 列名 [having 对组的检索条件]
 order by 列名 [asc|desc]     Tips：对结果按指定的列进行升序|降序排列
 
 注意：1. where group order三句可选择使用（不是必须）

         2. 查询可以嵌套（子查询）
            eg：查找表s中和列names中值为'张三'的年龄(age列)相同的人的名字(names列)
                       select names form s
                       where age=( select age from s where names='张三')

       3. group by 语句表示按列名中的项目分组（比如：把一个表每个年龄人数统计下，这个查询中年龄是二十的应该不只一个，需要分组后再统计。由于分组后 姓名列会出现‘一个年龄对应多个姓名’的情况，这样是SQL无法处理的，会报错，也就是说如果按照列AGE进行分组 那么如果不对姓名列处理是无法显示出来的。处理办法一般是加入统计查询函数[max,min,avg,sum,count]等 将多对一得情况改成统计数据显示。）
            eg：查询表S每个年龄人数统计下
                       select age, count(distinct names) from s group by age
                        Tip1：将结果按照年龄分组然后统计出来每个组里的人数
                        Tip2：distinct 关键字表示消去重复字段，即重名的不会统计
            eg：用于对比（无法用group 语句）：表S里20岁的都有谁
                       select names from group where age=20 

       4.select超级综合使用举例。
           要求：有一学生成绩表s，表中有姓名names 科目class 成绩score 三列,列出恰好有3门成绩不及格的人的名字和他们的总成绩并且按照降序排列。
             select names,sum(score) as 'sum'
             from s 
             where score<60
             group by names
              having count(class)=3 
             order by sum(score) desc
  执行过程分析：
             1.from 从表S中取出所有项目
             2.找出不及格（分数低于60）的学生
             3.按照名字分组（一人可以有很多科目同时一科目也会有很多人学习）
             4.分组后按照科目的数目进行统计并且找出来刚好有3个科目的学生
             5.把结果降序排列
  数据库实例分析：
   成绩表：   names   class  score
                    a       c      12
                    a      java    64
                    a       sql    23
                    a       c++    42
                    b        c     46
                    b      java    82
                     b       sql    42
                    b       c++    91
                    c       c      72
                    c      java    64
                    c       sql    82
                    c       c++     0
             经过第1步后整个表被取出。经过第2步筛选出不及格的项目，如下：
                 names    class  score 
                  a       c      12
                  a       sql    23
                  a       c++    42
                  b        c     46
                  b       sql    42
                 c       c++     0
             然后进入第三步 按照名字分组（注意此时分组后SQL无法处理原本只能一个成绩对应一个名字和一个科目 现在一个名字对应好多成绩好多科目 此处只是按照逻辑写出来，SQL无法显示） 分组后变成：
                                  names   class"堆"    score"堆"
                                       a---->c,sql,c++    12,23,42
                                      b---->s,sql        46,42
                                      c---->c++          0
               接着是HAVING语句对分组结果进行处理，统计处每个names对应的class堆中的项目数（此处 class中项目数和names是一一对应的 如果以这个作为一个表SQL可以处理，同时分组的逻辑表仍然存在）。然后选出 class数目=3 的一项（下列标有星号*的）
                 names    class数 
                      a          3        **
                      b          2
                      c          1
                        继续，从逻辑堆里取出 score堆进行统计总成绩 sum=12+23+42=77,按照别名输出结果。
                 names     sum
                  a            77
   最后进行排序。因为只有一个所以结果同上。

       5.数据表的连接查询（从多个表中取出需要的项目进行综合）
               内连接：（只显示两个表对于连接条件共有的部分） 
                       select 表1.所需列1 ,  表2.所需列2
                       from 表1,表2
                       where 连接条件 and  查询条件
   
                       eg1：多表连接：有两个表，学生情况表s（包括学号sno和姓名names）和导师任课情况表t（包括导师名字names和学生学号sno），查找 导师'张三' 说带的学生。
                                  select names from s,t
                                  where s.sno=t.sno and t.names='张三'
                       eg2：自身连接：找出教师工资表( 包括姓名names工资sal)s中 比'张三'工资低的老师
                                  select names from s as x,s as y
                                  where x.sal>y.sal and y.names='张三'
     外连接：（显示主表 将从表与主表匹配 主表没被匹配的项目 填成NULL）
             select 表1.所需列1 ,  表2.所需列2
             from 表1 left|right (outer) join 表2
             on 连接条件
   
             Tips：外连接分左右连接 left对应写在左边的表1 为主表 
                     外连接时连接条件用ON 不用WHERE
             Tips：另外还有full （outer）join 和 cross Join 。full不分主从全不显示出来（匹配的写不匹配的也全部写出来，就像一个学生没考试然后还有不是学生的也去考试了，这样学生表和成绩表的交集会被写出 同时也写出成绩表中不是学生那人的情况和学生表中买考试那人都要列出来。） cross将一个表的每一个记录和另一个表的每一个记录都进行匹配。这两都不常用。  
 
             eg：有两表 一个学生表s(names,学号sno)一个某科考试成绩表c（学号sno，成绩score），假设有学生没有考试那么成绩表中没有记录。 现在要显示所有学生的成绩。（那么如果该学生没有考试成绩表中就不会有他的成绩信息，以学生表为主表用成绩表去匹配）
                        select names score
                        from  s left outer join c
                        on S.SNO=C.SNO

       6.部分匹配（通配符）查询LIKE
             select 列1，列2
             from 表名
             where 列名 LIKE 通配符条件
   
             Tips：通配符'%'匹配多个 ， '_'匹配一个， '[]'在某一范围的一个字符 '[^ ]'不再某一范围的一个字符

             eg1：在成绩表s中找姓张的同学的成绩
                        select names,score 
                        from s
                        where names like '张_'

             eg2：在成绩表S中找成绩是九十多的
                        select names ，score from sc
                        where score like '9[0-9]'

       7.确定范围 （‘between and’| in）
             eg：找到成绩是90 80 70 的同学
                        select names,score from s
                        where score in (90,80,70)
    
三、SQL的修改功能——alter | drop
 修改数据表de列定义（增、改、删）
            alter table 表名
            add|alter 列定义| 完整性约束
  
            Tips：完整性约束：
                       constraint 约束名  约束类型
             约束名：
              用于删除约束的时候： drop constraint 约束名
               约束类型：
                        空/非空 NULL/NOT NULL
                        检测约束 check
                                      constraint 约束名 check(约束条件)
                        外键约束 （使表在外部键上取值是主表中某个值）
                                      constraint 约束名 foreign key peferences 主表名(列名)
                                            唯一    unique        （可空 但不可重复。最多只有一个空）
                                            主键    primary key   （不可重复不可空）
                                            tips：主键和唯一性约束异同：
                                                     一个基本表中只能一个主键但可多个唯一性约束
                                                     主键不允许又空值存在，唯一性约束后可以有空值
                                                     一个列不能同时作为主键并加上唯一性约束
            eg1：表s增加一个列class
                       alter table s
                       add class char(6)
            eg2：表s增加一个完整约束 要求score在0到100之间
                       alter table s
                       add 
                        constraint s_chk check(score between 0 and 100)
            eg3：修改表s中列names 加宽到10字符
                       alter table s
                       alter column names char(10)
   
                       tip:修改不允许毁坏原有数据 比如原来数据15字符不能修改为10字符
  
            删除列：
                       alter table 表名
                       drop column 列名
            删除表约束： 
                       alter table 表名
                       drop constraint 约束名
            删除数据表：
                       drop table 表名
            删除索引：
                       drop index 表名.索引名
                       tips: 不能删除由 create table.alter table创建的primary key 和unique。也不能删除系统表中的索引。

   修改表中数据：
           插入单条数据：
                       insert into 表名(列名1，列名2…) values(值1，值2…)
            插入多条数据：
                       insert into 表名(列名1，列名2…)  子查询
                       eg:求出表S中sal平均值存入SALAGV中
                                  insert into salagv
                                  select names,agv(sal)
                                  from s
                                  group by names
             修改数据：
                       update 表名
                       set 列名=表达式
                       where 条件
   
                       eg1：表S中工资SAL 全部涨100
                                  update s
                                  set sal=sal+100
                       eg2：表S中工资少于1000的提高20%
                                  update s
                                  set sal=sal*1.2
                                  where sal<1000  
  
            删除一行记录：
                       delete
                       from 表名
                       where 条件
   
                       eg：删除张三的记录
                                  delete  
                                  from s
                                  where names='张三'
    删除多条记录：
                       eg： delete 
                                  from t
                                 删除后t表为空表，当定义仍存在

 修改视图：
            alter view 视图名称 (视图列表)
            as 子查询
 删除视图：
            drop view 视图名
 更新视图：
            视图为一张虚表，对其操作都将最终转换成对基本表的操作。视图可以保证数据的逻辑独立性。