SQL数据库设计 常用语法及结构

/*****SQL数据库设计 常用语法及结构*****/

//SQL的数据类型
/**********************************************************************************/
CHAR(n)       
VARCHAR(n)    //最大长度为n的变长字符串
INT
SMALLINT
NUMERIC(p,d)  //定点数，由p位数字（不包含符号，小数点）组成，小数后面有d位数字
REAL          //取决于机器精度的浮点数
Double Precision //取决于机器精度的双精度浮点数
FLOAT(n)      //浮点数，精度至少为n位数字
DATE          //日期，包含年、月、日，格式为YYYY-MM-DD
TIME          //时间，包含一日的时、分、秒，格式为HH：MM：SS
/**********************************************************************************/



//常用的查寻条件
/**********************************************************************************/
比较    = , > , < , >= , <= , != , <> , !> , !< , ; NOT + 上述比较运算符
确定范围   BETWEEN AND, NOT BETWEEN AND
确定结合   IN , NOT IN
字符普配   LIKE, NOT LIKE
空值       IS NULL , IS NOT NULL
多重条件（逻辑条件） AND , OR ,NOT
/**********************************************************************************/




//聚集函数
/**********************************************************************************/
COUNT ( [DISTINCT | ALL ] * )  //统计元组个数
COUNT ( [DISTINCT | ALL ] <列名> ) //统计一列中值的个数
SUM ( [DISTINCT | ALL ] <列名> ) //计算一列值的总和
AVG  ( [DISTINCT | ALL ] <列名> ) //计算一列值的平均值
MAX ( [DISTINCT | ALL ] <列名> ) //求一列值的最大值
MIN ( [DISTINCT | ALL ] <列名> ) //求一列值的最小值
/**********************************************************************************/




//模式的定义与删除
/**********************************************************************************/
CREATE SCHEMA <模式名> AUTHORIZATION <用户名>
//在该模式下创建表
CREATE TABLE "S-T".Student(.....);
CREATE TABLE "S-T".Course(.....);

------//例句
CREATE SCHEMA "S-T" AUTHORIZATION WANG;
/**********************************************************************************/




//基本表的定义、删除、修改
/**********************************************************************************/

//定义
CREATE TABLE Student
      ( Sno CHAR(4) PRIMARY KEY,    //列级完整性约束条件，Cno是主码
        Cname CHAR(40),
        Cpno CHAR(4),
        Ccredit SMALLINT,
        FORRIGN KEY Cpno REFERENCES Course(Cno) //表级完整性约束条件，Cpno是外码，被参照性是Course，被参照列是Cno
      );

//修改

//向Student表增加“入学时间”列，其数据类型为日期型
ALTER TABLE Student ADD S_entrance DATE
//将年龄的数据类型由字符型改为整型
ALTER TABLE Course ALTER COLUMN Sage INT;
//增加课程名称必须取唯一值的约束条件
ALTER TABLE Course ADD UNIQUE(Cname);

//删除
DROP TABLE <表名> [RESTRICT | CASCADE]; //RESTRICT 有条件的删除   ；   CASCADE 删除没有限制条件
/**********************************************************************************/




//插入、修改、删除数据
/**********************************************************************************/
//插入
INSERT
INTO  <表名> [ ( <属性列1> [ , <属性列2> ...)]
VALUES (<常量1> [, <常量2>] ...);

INSERT
INTO Student(Sno, Sname, Ssex, Sdept, Sage)
VALUES ('200215128', '陈冬', '男', 'IS', 18);
//若 INTO Student() 则 VALUES 值 需完全按照原有的顺序，且没有的值要为null

//修改
UPDATE <表名>
SET <列名>  = <表达式> ...
[ WHERE <条件> ];

//将学生200215121的年龄改为21
UPDATE Student
SET Sage = 22
WHERE Sno = '200215121';

//删除数据
DELETE
FROM <表名>
[ WHERE <条件> ];
/**********************************************************************************/





//索引的建立与删除
/**********************************************************************************/
//建立
CREATE [ UNIQUE ][ CLUSTER ] INDEX <索引名>
ON <表名>( <列名> [ <次序>] [ , <列名>　[次序]] ...);

//
CREATE CLUSTER INDEX Stusname ON Student(Sname);

//删除索引
Drop INDEX <索引名>
/**********************************************************************************/



//数据查寻
/**********************************************************************************/
DISTINCT  //取消重复项

ORDER BY 子句 //可以用 ORDER BY 子句对查询结果按照一个或多个属性列的升序ASC或降序DESC排列，缺省为升序
//查询选修了3号课程的学生学号及成绩，查询结果按分数降序排列
SELECT Sno,Grade
FROM  SC
WHERE Cno = '3';
ORDER BY Grade DESC;

GROUP BY 子句// 将查询结果按某一列或多列的值分组，值相等的为一组
//求各个课程号及相应的选课人数
SELECT Cno,COUNT(Sno)
FROM SC
GROUP BY Cno;

//连接查询： 1、等值与非等值连接查询  2、自身连接  3、外连接  4、复合条件连接

//带有 ANY  或 ALL 谓词的子查询
SELECT Sname,Sage
FROM Student
WHERE Sage < ANY( SELECT Sage
                  FROM  Student
                  WHERE Sdept = 'CS')
            AND Sdept <> 'CS';

//带有 EXISTS 谓词的子查询    含： EXISTS ,  NOT EXISTS

//集合查询
并操作 UNION  ;   交操作  INTERSECT   ;   差操作   EXCEPT

 

ROUND //取整函数, 可选择近似位数

ROUND ( numeric_expression , length [ , function ] )

参数
numeric_expression
精确数字或近似数字数据类型类别的表达式（bit 数据类型除外）。

length
是 numeric_expression 将要四舍五入的精度。numeric_expression 按 length 所指定的在小数点的左边四舍五入。

function
是要执行的操作类型。function 必须是 tinyint、smallint 或 int。如果省略 function 或 function 的值为 0（默认），numeric_expression 将四舍五入。当指定 0 以外的值时，将截断 numeric_expression。

例:
Select ROUND(150.75, 1)
151.8

 

 字符串处理函数

http://zhidao.baidu.com/question/77744892.html?si=4
/**********************************************************************************/





//完整性
/**********************************************************************************/
//实体完整性
PRIMARY KEY //A在列级定义主码  |  在表级定义主码

//参照完整性
FOREIGN KEY

FOREIGN KEY (Sno) REFERENCES Student(Sno);
//拒绝执行  NO ACTION
//级联操作  CASCADE

//说明参照完整性的违约处理实例
CREATE TABLE SC
( Sno CHAR(9) NOT NULL,
  Cno CHAR(4) NOT NULL,
  Grade SMALLINT,
  PRIMARY KEY(Sno, Cno),                         //在表级定义实体完整性
  FOREIGN KEY (Sno) REFERENCES Student(Sno)      //在表级定义参照完整性
      ON DELETE CASCADE                          //当删除student表中的元组时，级联删除SC表中相应的元组
      ON UPDATE CASCADE                             //当更新student表中的元组时，级联更新SC表中相应的元组
  FOREIGN KEY (Cno) REFERENCES Course(Cno)            
      ON DELETE NO ACTION                         //当删除course表中的元组造成了与SC表不一致时拒绝删除
      ON UPDATE CASCADE                             //当更新course表中的cno时，级联更新SC表中相应的元组
  )

  //属性上的约束条件检查和违约处理  CHECK
  CHECK (Ssex IN('男', '女'));

  //完整性约束命名子句
  CONSTRAINT

  CREATE TABLE student
  (Sno NUMERIC(6)
      CONSTRAINT C1 CHECK(Sno BETWEEN 90000 AND 99999),
   Sname CHAR(20)
      CONSTRAINT C2 NOT NULL,
   Sage NUMERIC(3)
      CONSTRAINT C3 CHECK(Sage < 30),
   Ssex CHAR(2)
      CONSTRAINT C4 CHECK(Ssex IN ('男','女')),
   CONSTRAINT studentKey PRIMARY KEY (Sno)
  );

  //修改表中的完整性限制
  //去掉student表中对性别的限制
  ALTER TABLE student
     DROP CONSTRAINT C4;
  //修改student中的约束条件，学号改为900000-999999， 年龄由小于30改为小于40， 课先删除原有约束，再增加新约束
  ALTER TABLE student
     DROP CONSTRAINT C1;
  ALTER TABLE student
     ADD CONSTRAINT C1 CHECK(Sno BETWEEN 900000 AND 999999),
  ALTER TABLE student
     DROP CONSTRAINT C3
  ALTER TABLE student
     ADD CONSTRAINT C3 CHECK(Sage < 40);

  /**********************************************************************************/



//触发器
/**********************************************************************************/
//创建
CREATE TRIGGER <触发器名>
{BEFORE | AFTER} <触发事件> ON <表名>    //befor 和 after , 在内容改变前触发还是之后触发
FOR EACH {ROW | STATEMENT}               //触发器类型，行级触发（FOR EACH ROW）,语句级触发（FOR EACH STATEMENT）,如果某表有1000行
[WHEN <触发条件>]                                                        //前者触发一次，后者触发1000次
<触发动作体>

//定义一个before行级触发器， 为教师表Teacher定义完整性规则“教授的工资不得低于4000，若低于，自动改为4000”
CREATE TRIGGER Insert_Or_Update_Sal                      //在教师表表上定义触发器
   BEFORE INSERT OR UPDATE ON Teacher                 //触发器是插入或更新操作
   FOR EACH ROW                                          //行级触发器
   AS BEGIN                                              //定义触发动作体，这是一个PL/SQL过程块       
      IF(new.Job = '教授') AND (new.Sal < 4000) THEN  //因为是行级触发器，可以在过程体中使用插入或更新操作后的新值
         new.Sal := 4000;
      END IF;
   END;                                                  //出发动作体结束   

//删除触发器
DROP TRIGGER <触发器名> ON <表名>
/**********************************************************************************/

---

SQL 语句参考


/* INSERT 插入语句*/

INSERT INTO tablename(c1,c2,...,cn)
    VALUES (v1, v2, ..., vn)
    [USING TransactionObject];

/* 参数说明：
   cl，c2，…，cn是要插入数据的友中的各字段名
   vl，v2，…，vn是插入的数据。
   功能：对指定的事务处理对象，向指定的表中插入一行给定的数据。
 */


 /** DELETE 删除 */
 DELETE FROM tablename WHERE criteria
   [USING TransactionObject];

 /* 功能:删除指定表中满足where子句条件的指定行。*/

 /** SELECT 检索*/
    SELECT FieldOFTablelist
    INTO variablelist
    FROM Tablename
    WHERE criteria
    [USING TransactionObject];

/*参数说明:
    FieldOFTablelist 表示字段名列表; variablelist表示存放检索到的数据的变量列表
    功能:在指定的表中根据指定的条件检索一行数据.如果检索到的数据多于一行,则会产生错误
*/

/**UPDATE 更新*/
    UPDATE Tablename
    SET FieldName = VarName
    WHERE criteria
    [USING TransactionObject]

    /*SET后面是赋值语句
    功能:对指定事务对象,根据指定的条件更新指定表中的数据