首页 > 数据库 >sql

sql

时间：2023-05-31 13:44:37浏览次数：30  
标签：BEGIN END grade sql SC WHERE SELECT

实验的SQL

触发器

DDL
  1. 设置一个触发器，当对student表某位学生进行更新时，限制学生年龄必须超过18，否则报错“学生年龄应该大于18”。
CREATE TRIGGER UP_STUDENT
ON STUDENT 
FOR UPDATE
AS
BEGIN
    DECLARE @SAGE INT;
    SELECT @SAGE = SAGE FROM INSERTED;
    IF @SAGE <= 18
    BEGIN
        RAISERROR('学生年龄应该大于18', 16, 1);
        ROLLBACK TRANSACTION;
    END
END;


UPDATE STUDENT SET SAGE=17 WHERE SNO='98003'
  1. 设置一个针对course表的插入触发器，当插入课程记录， 学分Ccredit这项为空时，设定为1。
CREATE TRIGGER INS_COURSE
ON COURSE
FOR INSERT
AS
BEGIN
    DECLARE @CNO CHAR(2), @CCREDIT INT;
    SELECT @CNO = CNO, @CCREDIT = CCREDIT FROM INSERTED;
    IF (@CCREDIT IS NULL) 
        UPDATE COURSE SET CCREDIT = 1 WHERE CNO = @CNO;
END;


INSERT INTO COURSE (CNO, CNAME, CCREDIT, CTEACHER)
VALUES ('8', 'c语言B', '6', NULL);


SELECT * FROM COURSE WHERE CNO = '8';
  1. 对course创建一个一次只删除一条记录的触发器，否则报错“此删除操作可能会删除多门课程数据!!!”
CREATE TRIGGER del_course
ON course
FOR DELETE
AS
BEGIN
    DECLARE @row_cnt INT;
    SELECT @row_cnt = COUNT(*) FROM deleted;
    IF @row_cnt > 1
    BEGIN
        RAISERROR('此删除操作可能会删除多门课程数据!!!', 16, 1);
        ROLLBACK TRANSACTION;
    END;
END;


DELETE FROM course
WHERE ccredit = 4;
  1. 级联更新：当修改student表中sno列的值时，同时修改sc表中的sno列的值，并且一次只能更新一行。
CREATE TRIGGER student_update1
ON student
FOR UPDATE
AS
BEGIN
    DECLARE @row_cnt INT;
    SELECT @row_cnt = COUNT(*) FROM deleted;
    IF @row_cnt > 1
    BEGIN
        RAISERROR('此删除操作可能会修改多位学生数据!!!', 16, 1);
        ROLLBACK TRANSACTION;
    END
    ELSE
    BEGIN
        IF UPDATE(sno)
        BEGIN
            DECLARE @OLDSNO CHAR(5), @NEWSNO CHAR(5);
            SELECT @OLDSNO = SNO FROM DELETED;
            SELECT @NEWSNO = SNO FROM INSERTED;
            UPDATE sc SET sno = @NEWSNO WHERE sno = @OLDSNO;
        END
    END
END;

①第一种更新多行：

UPDATE student SET sage = sage + 1;

②第二种更新单行，完成级联更新：

UPDATE student SET sno = '08001' WHERE sno = '98001';
  1. 创建触发器，当插入某个同学选课成绩时，如果他所在系是CS，其成绩自动加5分。
CREATE TRIGGER sc_insert ON SC 
FOR INSERT 
AS 
BEGIN
    DECLARE @SNO CHAR(5), @CNO CHAR(2), @SDEPT CHAR(2);
    SELECT @SNO = SNO, @CNO = CNO FROM INSERTED;
    SELECT @SDEPT = SDEPT FROM STUDENT WHERE SNO = @SNO;
    IF (@SDEPT = 'CS')
    BEGIN
        UPDATE SC SET GRADE = GRADE + 5 WHERE SNO = @SNO AND CNO = @CNO;
    END;
END;


INSERT INTO SC VALUES('98002', '6' ,80);
SELECT * FROM SC WHERE SNO='98002' AND CNO='5';
  1. 为SC表建立一个更新触发器updateSC，当修改SC中某门课程的成绩时自动输出此门课程的平均成绩。
CREATE TRIGGER updateSC
ON SC 
FOR UPDATE
AS
BEGIN
    DECLARE @AVERAGE INT;
    SELECT @AVERAGE = AVG(SC.GRADE) FROM SC, INSERTED WHERE SC.CNO = INSERTED.CNO;
    PRINT @AVERAGE;
END;


SELECT * FROM SC WHERE CNO = '2';
SELECT AVG(GRADE) FROM SC WHERE CNO = '2';
UPDATE SC SET GRADE = GRADE * 0.7 WHERE CNO = '2';
  1. 为SC表建立一个插入触发器insertSC，当向SC表插入一条选课记录后自动统计输出该学生所选课程的门数。
CREATE TRIGGER insertSC
ON SC 
FOR INSERT
AS
BEGIN
    DECLARE @num INT;
    SELECT @num = COUNT(SC.CNO) FROM SC, INSERTED WHERE SC.SNO = INSERTED.SNO;
    PRINT @num;
END;


SELECT * FROM SC WHERE SNO = '98002';
SELECT COUNT(CNO) FROM SC WHERE SNO = '98002';
INSERT INTO SC VALUES('98002', '7', 72);

存储过程

  1. 利用存储过程，给student表添加一位学生信息。
-- 定义存储过程
CREATE PROCEDURE pro_ins_student
    @sno CHAR(5),
    @sname VARCHAR(20),
    @sage SMALLINT,
    @ssex CHAR(2),
    @sdept CHAR(2)
AS
BEGIN
    -- 插入学生信息到 student 表
    INSERT INTO student VALUES (@sno, @sname, @sage, @ssex, @sdept)
END

-- 执行存储过程，插入学生信息
EXEC pro_ins_student '98005', '王二', 20, '女', 'CS'

-- 验证插入结果，查询 student 表
SELECT * FROM student WHERE sno = '98005'

（2）利用存储过程查找王林同学所选课程的课程名称、学分和成绩。

-- 定义
CREATE PROCEDURE cname_sname
    @sname VARCHAR(20)
AS
BEGIN
    SELECT course.cname, course.ccredit, sc.grade
    FROM student, sc, course
    WHERE student.sno = sc.sno
        AND sc.cno = course.cno
        AND sname LIKE @sname;
END

-- 执行
EXEC cname_sname '王林';

（3）利用存储过程返回‘2’号课程的平均成绩。

-- 定义
CREATE PROCEDURE cno_gradeave
    @cno CHAR(2),
    @grade_ave NUMERIC(6, 2) OUTPUT
AS
BEGIN
    SELECT @grade_ave = AVG(grade)
    FROM sc
    WHERE cno = @cno;
END

-- 执行
DECLARE @ave_grade NUMERIC(6, 2);
EXEC cno_gradeave '2', @ave_grade OUTPUT;
SELECT @ave_grade;

（4）利用存储过程从sc表中返回课程的课程号和最高分。

-- 定义
CREATE PROCEDURE max_course
AS
BEGIN
    SELECT cno, MAX(grade)
    FROM sc
    GROUP BY cno;
END

-- 执行
EXEC max_course;

（5）利用存储过程，通过游标方式获取某个给定学号（如‘98001’学生）的最高成绩。

-- 定义
CREATE PROCEDURE pro_sno
    @sno CHAR(5)
AS
BEGIN
    DECLARE @grade INT, @max INT;
    SET @max = -1;
    DECLARE get_grade CURSOR FOR
        SELECT grade
        FROM sc
        WHERE sno = @sno;
    OPEN get_grade;
    FETCH get_grade INTO @grade;
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        IF @grade IS NOT NULL
        BEGIN
            IF @grade > @max
                SET @max = @grade;
        END;
        FETCH get_grade INTO @grade;
    END;
    CLOSE get_grade;
    DEALLOCATE get_grade;
    IF @max = -1
        PRINT '该学生未选课或者未参加过考试';
    ELSE
        SELECT @max;
END

-- 执行
EXEC pro_sno '98001';

（6）创建存储过程，通过游标扫描学生选课表中某同学的成绩，如果该学生的成绩小于60分，则把该同学该门课程选课记录删除。

-- 定义
CREATE PROCEDURE Pro_delete_grade
    @sno CHAR(5)
AS
BEGIN
    DECLARE @grade INT;
    DECLARE cur_grade CURSOR FOR
        SELECT grade
        FROM sc
        WHERE sno = @sno;
    OPEN cur_grade;
    FETCH cur_grade INTO @grade;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @grade < 60
        BEGIN
            DELETE FROM sc
            WHERE CURRENT OF cur_grade;
        END;
        FETCH cur_grade INTO @grade;
    END;
    CLOSE cur_grade;
    DEALLOCATE cur_grade;
END

-- 执行
EXEC Pro_delete_grade '98001';
-- 验证是否进行了删除
SELECT * FROM sc WHERE sno = '98001';

授权(直接与间接)

可以通过直接和间接的方式授权权限给用户或角色。下面是授权权限的示例：

  1. 直接授权权限：
GRANT 权限1, 权限2, ...
ON 对象名
TO 用户名或角色名

示例：

GRANT SELECT, INSERT
ON Customers
TO UserA

上述语句直接将 SELECT 和 INSERT 权限授予了 UserA 用户，使其可以在 Customers 表上执行这些操作。

  1. 间接授权权限：
GRANT 角色名
TO 用户名

示例：

GRANT SalesRole
TO UserA

上述语句将 SalesRole 角色授予了 UserA 用户。然后，在 SalesRole 角色上授予的权限将间接应用于 UserA 用户。

创建表

  1. 创建一个简单的表：
CREATE TABLE Customers (
    CustomerID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100)
)
  1. 创建一个带有主键的表：
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2)
)
  1. 创建一个带有外键关系的表：
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
)

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
)
  1. 创建一个带有自增长列的表：
CREATE TABLE Products (
    ProductID INT IDENTITY(1,1) PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2),
    Quantity INT
)
  1. 创建一个带有默认约束的表：
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Gender CHAR(1) DEFAULT 'M',
    DateOfBirth DATE
)
  1. 创建一个包含检查约束的表：
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Age INT CHECK (Age >= 18),
    Grade CHAR(2)
)

以上是六个建表语句的例子，每个例子都使用了不同的约束和数据类型来展示不同的功能和用法。

以下为出现过的sql

CREATE TABLE STUDENT (
    SNO CHAR(5) PRIMARY KEY,
    SNAME VARCHAR(20),
    SAGE SMALLINT CHECK (SAGE >= 15 AND SAGE <= 45),
    SSEX CHAR(2) DEFAULT '男' CHECK (SSEX = '男' OR SSEX = '女'),
    SDEPT CHAR(2)
);

CREATE TABLE COURSE (
    CNO CHAR(2) PRIMARY KEY,
    CNAME VARCHAR(20),
    CPNO CHAR(2),
    CCREDIT SMALLINT
);

CREATE TABLE SC (
    SNO CHAR(5) NOT NULL,
    CNO CHAR(2) NOT NULL,
    GRADE SMALLINT CHECK ((GRADE IS NULL) OR (GRADE BETWEEN 0 AND 100)),
    PRIMARY KEY (SNO, CNO),
    FOREIGN KEY (SNO) REFERENCES STUDENT (SNO),
    FOREIGN KEY (CNO) REFERENCES COURSE (CNO)
);

1

标签：BEGIN,END,grade,sql,SC,WHERE,SELECT
From： https://www.cnblogs.com/flyleixin/p/17445886.html

相关文章

  • sqlmap的使用 ----常用tamper模块，TODO，绕过WAF的测试
    sqlmap的使用----自带绕过脚本tamperwkend2018-09-1520:23:39sqlmap在默认的的情况下除了使用char()函数防止出现单引号，没有对注入的数据进行修改，还可以使用–tamper参数对数据做修改来绕过waf等设备。0x01命令如下sqlmap-u[url]--tamper[模块名]sqlmap的绕过脚本在目录u......
  • MySQL——json类型的应用
    在制作动态报表的时候，如果需要字段灵活配置，用json存储数据，可以让查询变得非常简单。 业务场景：绩效系统中，需要从10个系统中抓取不同数据，并且性能不会太高，但是用于计算的只有其中3个，用哪3个看领导心情，设计一张表存储抓取到的数据。1、傻瓜式做法，设计一张表，从字段1列到......
  • SQl Server添加索引
    USEDBGOIFNOTEXISTS(SELECTNameFROMSysColumnsWHEREid=Object_Id('[HIS_QUEUE]')ANDName='InsertDateTime')BEGINALTERTABLEHIS_QUEUEADDInsertDateTimedatetimeDEFAULT(CONVERT([date],CONVERT([varchar](100),getdate()......
  • mysql数据库自动删除
    关于早上发现数据库丢失，留下了一份文档 检查了下发现是命令的问题 由于使用了这条命令，导致后门被开了，真是血泪的教训。修改远程登录权限的话还是使用updateuser表来修改。......
  • kettle 日志表migrate SQL
    日志kettle.properties配置文件#trans日志（转换）KETTLE_TRANS_LOG_DB=10.15.30.168KETTLE_TRANS_LOG_TABLE=r_log_trans#步骤日志表（步骤）KETTLE_STEP_LOG_DB=localhostKETTLE_STEP_LOG_TABLE=r_log_trans_step#trans性能日志（运行）KETTLE_TRANS_PERFORMANCE_LOG_DB=l......
  • SqlServer2014管理工具创建用户并设置对应访问权限
     需求：创建一个具有访问权限登录的用户，用以访问指定数据库， 对其放开指定数据库的指定表  一、用户名创建以及设置1、首先使用管理员账号登陆到数据库，【安全性】-【登录名】-右键【新建登录名】 【服务器角色】页签中：用于向用户授予服务器范围内的安全特权 【用户映......
  • java同步mysql的数据到PostgreSQL时报错ERROR: invalid byte sequence for encoding "
    最近，同事在做一个功能，通过java程序将mysql中的一张表的数据同步到pgsql中，在同步过程中，插入到pgsql中出现了如下错误：`###Errorupdatingdatabase.Cause:org.postgresql.util.PSQLException:ERROR:invalidbytesequenceforencoding"UTF8":0x00在位置：unnamedportalpa......
  • SQL优化之EXPLAIN执行计划
    前言从今天开始本系列文章就带各位小伙伴学习数据库技术。数据库技术是Java开发中必不可少的一部分知识内容。也是非常重要的技术。本系列教程由浅入深,全面讲解数据库体系。非常适合零基础的小伙伴来学习。全文大约【1965】字，不说废话，只讲可以让你学到技术、明白原理的纯干......
  • 关于mysql 创建索引报错 1071 specified key was too long ;max key length is 3027
    另一种张表也是相同的字段创建索引却能成功，在网上查了一些资料。后来发现是两张表字段都用的varchar类型，不过能成功建索引的表设置的长度是50，而不能成功的表里设置的255，修改字符长度就能成功建索引了。关于varchar(50)和varchar(255)的区别：https://dba.stackexchange.com/questio......
  • sql解释执行顺序
    SQL查询语句执行的先后顺序 查询语句执行顺序查询中⽤到的关键词主要包含六个，并且他们的顺序依次为select--from--where--groupby--having--orderby。其中select和from是必须的，其他关键词是可选的，这六个关键词的执⾏顺序与sql语句的书写顺序并不是⼀样的，而是按照下⾯的顺......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99