关于sql的一部分知识

用sql创建用户数据库这部分不讲了，因为我都是通过可视化界面的因为没什么技术含量（可能是没遇到过，不足之处请大牛补充）。我这里都是用sql语句的非可视化界面

文章中使用的exp是例子的意思。

1.sql的数据类型有：

                            字符数据： char(n),  varchar(n),  text  
                            二进制数据：binary(n), varbinary(n), image 

                            整数数据：int,  smallint,  tinyint,  bit
                                          4字节   2字节     1字节   1位
                            浮点数据： float,    real
                                             8字节   4字节
                            货币数据： money,  smallmoney
                                              8字节     4字节
                            日期时间数据： datetime,   smalldatetime
                                                     8字节         4字节

当然了用户也可以创建属于自己的类型，  可以用sp_addtype<类型名>， <系统类型> [,<null说明>].

删除就可以使用 sp_droptype <类型名>.就可以了

2.创建表：
        
CREATE TABLE [[<数据库名>.]<主人名>.]<表名>(<列名1> <类型1> {NOT NULL∣NULL} [,<列名2> <类型2>{NOT NULL∣NULL}……])

exp:

CREATE TABLE student
  (sno char (7)  NOT NULL  PRIMARY KEY ,
   sname varchar (20)  NOT NULL ,
   dept varchar (20)  NOT NULL ,
   birthday datetime  NULL ,
   tel varchar (20)  NULL ,
   email varchar (20)  NULL  UNIQUE)   

3.这里出现了主键（primary key）这个概念：

主键是为了保证数据的实体的完整性和防止重复，并且主键的设置应该是让用户感觉不到的，就是说没有什么存在感这种，那么你的主键设置的不错，主键也可以是多个的（一般都是单一的）。

一般的语法是这样的：像上面创建表的时候在列后面加上primary key或者是在列定义完以后primary ket(这里是列的名字可以是多个的)或者在使用alter 修改表的时候添加

exp:alter table student_course1 add constraint pk_student_course1 primary key(sno,cno) 

4.外建（foreign key）：在某一张表中的主键，在另一张表中就是外建，当然这个外建也可是那张表的主键。

外建有以下特性：

                         （1） 在插入的时候如果主键没有这个值那么就无法插入

                         （2）更新时，不能改为主键表中没有的值。       

          （3）删除主键表记录时，你可以在建外键时选定外键记录一起级联删除还是拒绝删除。

                          （4）更新主键记录时，同样有级联更新和拒绝执行的选择。

exp：

CREATE TABLE student_course1(
    sno char (7) not null foreign key(sno)  references
                           student(sno) on delete  cascade,//这个就是级联删除
    cno char (5) not null foreign key(cno)  references
                           course(cno) on delete no action,//这个就是拒绝删除
    grade decimal(5,0) null check(grade>=0 and
                           grade<=100))//这里我介绍一下check，作用是用来限制你列的范围。

 

5.修改表的内容（这里不是指对表中的数据进行操作，而是表的属性）：

添加或删除列（前提不是主键）：

ALTER TABLE 表名 ADD 列名 类型 [NULL][，列名 类型 [NULL]…]；//添加
ALTER TABLE 表名 DROP COLUMN列名；//删除

修改列的属性（前提不是主键）：

ALTER TABLE表名 ALTER COLUMN 列名 类型 [NULL | NOT NULL] [，列名 类型 [NULL | NOT NULL]]…)；

注意:一个列要改变成非空值(NOT NULL)，要求该列当前不含有空值，如果不选择该项,其默认的值保持原有的值；若一个列要改变数据类型，则该列数据必须全为空值,否则不能改变。

增加主键：

alter TABLE  表名 add constraint pk_表名 primary key(这里是列的名字)

删除主键：

这个比较麻烦，先要把约束删掉，然后在删。

select * from sysobjects where xtype='PK'这个是查当前的主键。alter table 表名 drop constraint 主键的约束名就可以了。

删除表：

drop table 表名.

6.建立索引

索引好处是为了加快对数据的读取的，坏处是会增加额外的物理空间，数据改变的时候还要话时间对索引进行修改要花很多的时间。

添加索引：

CREATE [UNIQUE] {CLUSTERED|NONCLUSTERED} INDEX <索引名> ON <表名>(<列名1>[,<列名2>···])

删除索引：

DROP INDEX <表名>.<索引名1> [,<表名>.<索引名2>···]

exp:Create unique clustered index kh_ind on  course(cno);

Drop index course.kh_ind;

7.对sql表中的数据进行更新

 7.1数据的插入

 first:   INSERT [INTO] <表名>[(<列名1>，…，<列名n>)]  VALUES (<常量1>，…，<常量n>)

second:INSERT INTO <表名>[(<列名1>，…，<列名n>)]  子查询

7.2数据更新

UPDATE <表名>SET <列名1>=<表达式l> [,<列名2>=<表达式2>，…] [FROM <表名1>[,<表名2>,…]] [WHERE <条件>]

exp:  UPDATE student_courseSET  grade = 0 FROM  studentWHERE  dept ='计算机系'  and student.sno=student_course.sno

7.3数据删除

DELETE FROM <表名> [FROM <表名1>[,<表名2>,…]][WHERE 条件]

exp:  DELETE FROM student_courseWHERE grade is null

7.4数据查询
 SELECT <投影的字段列表>
        FROM <参与查询的表列表>
        [WHERE <查询选择的条件> ]
        [GROUP BY <分组表达式>]
                                 [HAVING<分组查询条件>]
        [ORDER BY <排序表达式> [ASC∣DESC ]]

exp：SELECT DISTINCT sname as 姓名,dept as 系名,
              year(getdate())-year(birthday) as 年龄
FROM student

7.5其他

介绍一下distinct这个是为了去重的。还有：SQL Server的通配符有以下几个：%代表任意多个字符。（下划线）代表单个字符。[ ]代表指定范围内的单个字符，[ ]中可以是单符，也可以是字符范围。[ ^]代表不在指定范围内的单个字符，[^ ]中可以是单个字符，也可以是字符范围。

这里有一篇将通配符很好的可以看一下：

点击打开链接

7.6集合

IN <值表>,  NOT IN <值表>使用这两个谓词

exp:SELECT Sname，Ssex
    FROM  Student
    WHERE Sdept IN ( 'IS'，'MA'，'CS' );

7.7空值

is null , is not null不能用=取代is

7.8 order by

我的话就是用来给选定的集合排序的

exp:SELECT  *
        FROM  Student
        ORDER BY Sdept，Sage DESC；

聚集函数 ：

计数
COUNT（[DISTINCT|ALL] *）
COUNT（[DISTINCT|ALL] <列名>）
计算总和
SUM（[DISTINCT|ALL] <列名>）    
 计算平均值
AVG（[DISTINCT|ALL] <列名>）
最大最小值
      MAX（[DISTINCT|ALL] <列名>）
     MIN（[DISTINCT|ALL] <列名>）

7.9 group by

我的话就是用来分组的

exp：SELECT s#, AVG(score)
    FROM SC
    GROUP BY s#

having的作用是为了对聚集函数得到的进行筛选

exp:select sno
     from sc
    group by sno
    having count(*)>3

8.一些谓词

any,some,all

any的意思只要存在一个就可以满足，some和any差不多满足就行，all是全部满足

exp: SELECT Sname，Sage
    FROM    Student
    WHERE Sage < ANY (SELECT  Sage
                                         FROM    Student
                                         WHERE Sdept= ' CS ')
           AND Sdept <> ‘CS ' ; //这个符号是不等于的意思

9.连接

9.1内连接（也就是自然连接）

first:SELECT <投影的字段列表>
          FROM  <表1>  [INNER]  JOIN <表2>
          ON  <表1.列1> = <表2.列2>

second:SELECT <投影的字段列表>
           FROM  <表1> ,<表2>
           WHERE <表1.列1> =<表2.列2>

这是两种语法但是效果是一样的

exp:SELECT s.*
FROM  student s , student_course sc , course c
WHERE    s.sno=sc.sno
                  and sc.cno=c.cno
                  and c.cname='数据库原理及应用'

9.2外连接（本质上还是用到了自然连接）

分为三种左外连接，右外连接，全外连接。

左外连接是对连接条件中左边的表不加限制，在右边增加万能行（全由空值组成）；
右外连接是对连接条件中右边的表不加限制，在左边增加万能行（全由空值组成） ；
全外连接是对连接条件中的两个表都不加限制，在两边增加万能行（全由空值组成），所有两个表中的行都会包括在结果集中。

左外连接的语法为：
 SELECT <投影的字段列表>
 FROM  <表1>  LEFT  JOIN <表2>
                              ON  <表1.列1> = <表2.列2>
右外连接的语法为：
 SELECT <投影的字段列表>
 FROM  <表1>  RIGHT  JOIN <表2>
                              ON  <表1.列1> = <表2.列2>
全外连接的语法为：
 SELECT <投影的字段列表>
 FROM  <表1>  FULL  JOIN <表2>
                            ON  <表1.列1> = <表2.列2>

exp（右外连接）:SELECT sno, student_course.cno,cname,grade
FROM  student_course  RIGHT JOIN course ON  student_course.cno=course.cno

9.3自连接（就是自己和自己连接）

exp:SELECT s1.sno, s1.sname,s2.sno,s2.sname
FROM  student  s1  JOIN student  s2  ON
              s1.sname=s2.sname
WHERE  s1.sno<s2.sno

9.4交叉连接（就是笛卡尔积的运算）

first :SELECT <投影的字段列表>
FROM  <表1>  CROSS  JOIN <表2>

second:SELECT <投影的字段列表>
FROM  <表1>, <表2>

10 exist量词

 使用存在量词EXISTS或NOT EXISTS后，若内层查询结果非空或空，则外层的WHERE子句返回真值，否则返回假值。

exp:SELECT DISTINCT sname
FROM  student
WHERE EXISTS
      ( SELECT * FROM student_course
        WHERE sno=student.sno and cno='10101' )

这里讲一下exist和in的区别，好像是exist的效率大于in的，相同的地方我感觉是差不多的使用起来（原谅我语文不好）

11合并结果集

 SELECT 语句1UNION  [ALL]SELECT 语句2

注意：(1)UNION中的每一个查询所涉及到的列必须在列数、顺序和类型上保持一致。(2)最后结果集中的列名来自第一个查询语句。(3)若UNION中包含ORDER BY子句，则将对最后的结果集排序。(4)默认将在结果集中删除重复的行，除非使用ALL关键字。

12.查询的时候建立新表

SELECT <投影的字段列表>INTO <新表>FROM <参与查询的表列表> [ WHERE <查询选择的条件> ][ GROUP BY <分组表达式> ] [ HAVING <分组查询条件> ]
 [ ORDER BY <排序表达式> [ ASC∣DESC ] ]
由于新表的结构由<投影的字段列表>定义，所以<投影的字段列表>中的每一列必须有名称，如果是一个表达式，则应该为其指定别名。