SQL语言实践

1.数据库

创建

CREATE DATABASE Database;

改名

ALERT DATABASE Data RENAME TO Database;

删除

DROP DATABASE Database;

2.表

创建：

CREATE TABLE table();

与主键，外键有关

CREATE TABLE Table(
    特殊的主键    serial    NOT NULL,
    外键          char(20)    NULL,
    CONSTRAINT    Table_PK    PRIMARY KEY(主键),
    CONSTRAINT    Table_FK    FOREIG KEY(外键),
    REFERENCES    外键存在的地方(外键),
    ON DELETE    CASCADE
);

与缺省值，范围，唯一有关

CREATE TABLE Table(
    A    char(20)    PRIMARY KEY,
    B    varchar(12)    NOT NULL    UNQIUE,
    C    date    NULL    CHEAK(date IN '22','11','30'),
    D    char(15)    NULL    DEFAULT'再见',
);

增减

ALTER TABLE Table ADD Date char(20);
ALTER TABLE Table DROP COLUMN Date;
ALTER TABLE Table CONSTRAINT NULL;

改名

ALTER TABLE Table RENAME TO table;
ALTER TABLE Table RENAME A TO B;
ALTER TABLE Table ALTER COLUMN Date TYPE varchar(20);

删除

DROP TABLE Table;

3.索引

CREATE INDEX Birthday_Idx ON Student(Birthday);
ALTER INDEX irthday_Idx RENAME TO Birth;
DROP INDEX Birth

4.数据

增删改

INSET INTO Table VALUES ();
UPDATE Table SET StuID='123' WHERE Name='li';
DELETE FROM Table WHERE StuID='123';

查找

SELECT *    //查找行
FROM Table
WHERE StuID='li';

SELECT Name    //查找列
FROM Table
WHERE bir BEWTEEN '11' AND '15' AND Email LIKE '%.163.com';

SELECT CourseID , COUNT(*) AS NUM    //运用函数
FROM TABLE
WHERE Name = 'li'
GROUP BY Class
Having COUNT(*) > 2;

SELECT TeacherID,TeacherName,CLASS
FROM Teacher
WHERE TeacherID IN(SELECT * 
    FROM TEST
    WHERE Nun IN('计算机')
);

5.授权

GRANT SELECT ON Table TO Stu;
REVOKE SELECT ON TABLE FROM Stu;
DENY SELECT ON TEACHER TO Stu;

6.视图

CREATE VIEW view AS
    SELECT ID,Name
    FROM Tea
    WHERE Name = 'li';


SELECT *
FROM view
WHERE ID = '4';

DROP VIEW view;

7.服务器

特殊语句

//if语句
IF    
ELSIF
ELSE
END IF;

//case语句
CASE
WHEN  THEN
...
ELSE
END CASE;

//LOOP语句

DECLARE
    counter INTEGER:=1;    //声明变量
BEGIN
  LOOP
    RAISE NOTICE '%',counter;
    counter := counter+1;
    IF counter > p_max THEN
        EXIT;
    END IF;
  END LOOP;
END;

创建执行数据库

CREATE OR REPLACE FUNCTION name()
RETURNS  +返回值类型
AS $$
DECLARE
    --定义内容
BEGIN;
    --函数体语句
END;
$$ LANGUAGE plpsql;

8.游标

创建

游标名称  CURSOR FOR  
    SELECT *
    FROM...;

打开

OPEN 未绑定的游标名    FOR 
SELECT    //返回记录的SELECT

OPEN  游标名    FOR EXECUTE
SELECT    //将动态执行查询字符串

OPEN 游标名;    //适用于已绑定的游标

获取数据

FETCH 游标名 INTO 变量名;

关闭游标

CLOSE 游标名

9.存储

CREATE OR REPLACE PROCEDURE 存储名称
AS $$

DECLARE
变量1 数据类型 := 初始值1
...
BEGIN
程序执行语句
END
$$ LANGUAGE plpsql;

修改

ALTER PROCEDURE name RENAME TO new_name;    //名称
ALTER PROCEURE name OWNER TO new_owner;    //所有者
ALTER PROCEURE name SET SCHEMA new_schema; //修改模式

删除

DROP PROCEDURE name;

10.触发器

创建

CREATE TRIGGER 触发器名称
{ BEFORE | AFTER | INSTEAD OF }    //事件前触发，事件后触发，替代触发

ON 表名
     [ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE 存储过程名;

修改

ALTER TRIGGER name ON table_name RENAME TO new_name;

删除

DROP TRIGGER name ON table_name;