Grijesh Chauhan's questions

在我的 MYSQL 数据库公司中，我有一个表Worker：：

mysql> DESC `Worker`;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| SSN    | varchar(64) | NO   |     | NULL    |       |
| name   | varchar(64) | YES  |     | NULL    |       |
| salary | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)  

我使用以下 MySQL 的命令创建：

CREATE TABLE `Worker` (
  `SSN` varchar(64) NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  `salary` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;  

该表具有以下数据：

mysql> SELECT * FROM `Worker` ORDER BY salary ASC;
+-----+---------+--------+
| SSN | name    | salary |
+-----+---------+--------+
| 6   | TWO     |     50 |
| 5   | ONE     |    100 |
| 1   | Grijesh |    200 |
| 3   | Sumit   |    250 |
| 2   | Rahul   |    300 |
| 4   | Harsh   |    500 |
| 7   | THREE   |   1000 |
+-----+---------+--------+
7 rows in set (0.00 sec)  

它的插入查询：

INSERT INTO `Worker`
    (SSN, name, salary) 
  VALUES 
    ("1", "Grijesh", 200),
    ('2','Rahul',300),
    ('3','Sumit',250),
    ('4','Harsh',500),
    ('5','ONE','100'),
    ('6','TWO',50),
    ('7','THREE',1000);  

[问题]

我想选择第一个n workers（在 asc 列表中）那些工资总和刚刚超过 500（> 500）。例如：

以下四名工人是第一低薪员工，工资总和为 600。（600 > 500）

| 6   | TWO     |     50 |
| 5   | ONE     |    100 |
| 1   | Grijesh |    200 |
| 3   | Sumit   |    250 |    

我需要SELECT * FROMWorker 之类的东西WHERE SUM(salary) < 501 ASC; （它的查询不正确）

可以假设所有人都有不同的薪水。

_{类似于削减成本：移除那些相对不重要的员工}

阅读建议或链接会有所帮助

MySQL 中的一棵树中的子树

在我的 MYSQLDatabase COMPANY中，我有一个Table: Employee递归关联，一个员工可以是其他员工的老板。A self relationship of kind (SuperVisor (1)- SuperVisee (∞) ).

查询创建表：

CREATE TABLE IF NOT EXISTS `Employee` (
  `SSN` varchar(64) NOT NULL,
  `Name` varchar(64) DEFAULT NULL,
  `Designation` varchar(128) NOT NULL,
  `MSSN` varchar(64) NOT NULL, 
  PRIMARY KEY (`SSN`),
  CONSTRAINT `FK_Manager_Employee`  
              FOREIGN KEY (`MSSN`) REFERENCES Employee(SSN)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

我插入了一组元组（查询）：

INSERT INTO Employee VALUES 
 ("1", "A", "OWNER",  "1"),  

 ("2", "B", "BOSS",   "1"), # Employees under OWNER 
 ("3", "F", "BOSS",   "1"),

 ("4", "C", "BOSS",   "2"), # Employees under B
 ("5", "H", "BOSS",   "2"), 
 ("6", "L", "WORKER", "2"), 
 ("7", "I", "BOSS",   "2"), 
 # Remaining Leaf nodes   
 ("8", "K", "WORKER", "3"), # Employee under F     

 ("9", "J", "WORKER", "7"), # Employee under I     

 ("10","G", "WORKER", "5"), # Employee under H

 ("11","D", "WORKER", "4"), # Employee under C
 ("12","E", "WORKER", "4")  

插入的行具有以下Tree-Hierarchical-Relationship：

         A     <---ROOT-OWNER
        /|\             
       / A \        
      B     F 
    //| \    \          
   // |  \    K     
  / | |   \                     
 I  L H    C        
/     |   / \ 
J     G  D   E

我写了一个查询来查找关系：

SELECT  SUPERVISOR.name AS SuperVisor, 
        GROUP_CONCAT(SUPERVISEE.name  ORDER BY SUPERVISEE.name ) AS SuperVisee, 
        COUNT(*)  
FROM Employee AS SUPERVISOR 
  INNER JOIN Employee SUPERVISEE ON  SUPERVISOR.SSN = SUPERVISEE.MSSN 
GROUP BY SuperVisor;

输出是：

+------------+------------+----------+
| SuperVisor | SuperVisee | COUNT(*) |
+------------+------------+----------+
| A          | A,B,F      |        3 |
| B          | C,H,I,L    |        4 |
| C          | D,E        |        2 |
| F          | K          |        1 |
| H          | G          |        1 |
| I          | J          |        1 |
+------------+------------+----------+
6 rows in set (0.00 sec)

[问题]
而不是完整的层次树，我需要一个SUB-TREE点（选择性）例如：
如果输入参数是B那么输出应该如下......

+------------+------------+----------+
| SuperVisor | SuperVisee | COUNT(*) |
+------------+------------+----------+
| B          | C,H,I,L    |        4 |
| C          | D,E        |        2 |
| H          | G          |        1 |
| I          | J          |        1 |
+------------+------------+----------+   

请帮助我。如果不查询，存储过程可能会有所帮助。
我试过了，但所有的努力都是徒劳的！

在我的数据库中，我有一个表：Employee通过递归关联，员工可以成为其他员工的老板。

表说明：

mysql> DESC Employee;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| SSN         | varchar(64)  | NO   | PRI | NULL    |       |
| name        | varchar(64)  | YES  |     | NULL    |       |
| designation | varchar(128) | NO   |     | NULL    |       |
| MSSN        | varchar(64)  | NO   | MUL | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

员工表的当前状态是：

mysql> SELECT * FROM Employee;
    +-----+------+-------------+------+
    | SSN | name | designation | MSSN |
    +-----+------+-------------+------+
    | 1   | A    | OWNER       | NULL |
    | 2   | B    | BOSS        | 1    |  
    | 3   | C    | WORKER      | 2    |  
    | 4   | D    | BOSS        | 2    |  
    | 5   | E    | WORKER      | 4    |   
    | 6   | F    | WORKER      | 1    |  
    | 7   | G    | WORKER      | 4    |  
    +-----+------+-------------+------+
    7 rows in set (0.00 sec)

以下是表中各行之间的层次关系：

     A
    / \
   B   F
  / \
 c   D
    / \
   G   E

我想对 INSERT 施加以下约束：

• 员工不能对自己进行 BOSS。因此查询之类的。
  INSERT INTO Employee VALUES ("8", "H", "BOSS", "8"); 应该拒绝。
• OWNER可以插入 新的新的。

因为我正在使用早于 5.5 的 MYSQL 版本（不支持信号）。
所以我使用 amy_signal()作为存储过程。

写成这样：

CREATE PROCEDURE `my_signal`(in_errortext VARCHAR(255))
BEGIN
    SET @sql=CONCAT('UPDATE `', in_errortext, '` SET x=1');
    PREPARE my_signal_stmt FROM @sql;
    EXECUTE my_signal_stmt;
    DEALLOCATE PREPARE my_signal_stmt;
END// 

并应用约束，我written a Trigger知道了check constraints are not yet implemented in MySQL！

DELIMITER $$
CREATE
  TRIGGER `employee_before_insert` BEFORE INSERT
    ON `Employee`
    FOR EACH ROW BEGIN
      CASE
       WHEN NEW.designation = 'OWNER'  THEN
          CALL my_signal('Error: can not insert new OWNER !');

       WHEN NEW.SSN = NEW.MSSN THEN
          CALL my_signal('Error: Row can not reference itself!');
      END CASE; 
  END$$   
DELIMITER ;  

它已成功编译并加载到数据库中。但是当我尝试插入时：

mysql> INSERT INTO Employee VALUES ("12", "K", "BOSS",   "12");
ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or trigger

我在 这里和这里学习

• SQL 准备语句（PREPARE、EXECUTE、DEALLOCATE PREPARE）可用于存储过程，但不能用于存储函数或触发器。因此，存储函数和触发器不能使用动态 SQL（您将语句构造为字符串然后执行它们）。

经过一番努力，我可以编写另一个触发器，如下所示。按照我的要求工作正常。

mysql> CREATE
    -> TRIGGER `employee_before_insert` BEFORE INSERT
    ->     ON `Employee`
    ->     FOR EACH ROW BEGIN
    ->      IF UCASE(NEW.designation) = 'OWNER'  THEN  /*UCASE*/
    ->        UPDATE `Error: can not insert new OWNER !` set x=1; 
    ->      END IF;
    ->      IF (NEW.SSN = NEW.MSSN) THEN
    ->        UPDATE `Error: Row can not reference itself!` set x=1;
    ->      END IF;
    -> END$$
Query OK, 0 rows affected (0.08 sec)

mysql> DELIMITER ;
mysql> INSERT INTO Employee VALUES ("12", "K", 'owner',   "11");
ERROR 1146 (42S02): Table 'dumy.Error: can not insert new OWNER !'
  doesn't exist
mysql> INSERT INTO Employee VALUES ("12", "K", 'Ajay',   "12");
ERROR 1146 (42S02): Table 'dumy.Error: Row can not reference itself!' 
  doesn't exist

但是我已经my_signal() 在许多程序中使用，我需要编写许多新的存储函数和触发器，我需要my_signal()再次使用函数。

有人可以建议我以其他方式编写 my_signal() 来打印自定义错误消息吗？

我尝试如下：

分隔符 $$
创建过程my_signal（in_errortext VARCHAR(255)）
声明 sql varchar(512);
开始
SET sql=CONCAT('UPDATE ', in_errortext, 'SET x=1');
更新 sql 设置 x =1;
结束$$

但没用:(。

请帮助我。我将非常感激！

我特别不擅长MYSQL @Stored Procedures。

如果您想在此处尝试您的系统，您可以快速找到构建此数据库的命令。

如何限制插入在递归关系表中添加自引用行（表本身包含外键点）？

mysql> SELECT * FROM Employee;
+-----+------+-------------+------+
| SSN | name | designation | MSSN |
+-----+------+-------------+------+
| 1   | A    | OWNER       | NULL |
| 2   | B    | BOSS        | 1    |
| 3   | C    | WORKER      | 2    |
| 4   | D    | BOSS        | 2    |
| 5   | E    | WORKER      | 4    |
| 6   | F    | WORKER      | 1    |
| 7   | G    | WORKER      | 4    |
| 8   | H    | BOSS        | 8    |
+-----+------+-------------+------+
8 rows in set (0.00 sec)

员工不能BOSS独善其身。因此

mysql> INSERT INTO Employee VALUES ("8", "H", "BOSS",   "8");  
Query OK, 1 row affected (0.04 sec)

应该通过某种约束来拒绝。如何添加这样的约束？

如果可能，建议不使用触发器。

在我的数据库中，我有一个表：Employee通过递归关联，员工可以成为其他员工的老板。

以下是我用来创建表的查询：

CREATE TABLE IF NOT EXISTS `Employee` (
  `SSN` varchar(64) NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  `designation` varchar(128) NOT NULL,
  `MSSN` varchar(64) DEFAULT NULL, 
  PRIMARY KEY (`SSN`),
  CONSTRAINT `FK_Manager_Employee`  FOREIGN KEY (`MSSN`) REFERENCES Employee(SSN)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

我可以成功地创建我的表：

mysql> DESCRIBE Employee;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| SSN         | varchar(64)  | NO   | PRI | NULL    |       |
| name        | varchar(64)  | YES  |     | NULL    |       |
| designation | varchar(128) | NO   |     | NULL    |       |
| MSSN        | varchar(64)  | YES  | MUL | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

然后我使用以下查询在 Employee 表中插入了一些元组。

mysql> INSERT INTO Employee VALUES 
    -> ("1", "A", "OWNER",  NULL), 
    -> ("2", "B", "BOSS",   "1"),
    -> ("3", "C", "WORKER", "2"),
    -> ("4", "D", "BOSS",   "2"),
    -> ("5", "E", "WORKER", "4"),
    -> ("6", "F", "WORKER", "1"),
    -> ("7", "G", "WORKER", "4")
    -> ;
Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0   

现在我在表中的行之间具有以下层次关系：

        A
       / \
      B   F
     / \
    C   D
       / \
      G   E

以下是表的 Select 语句：

mysql> SELECT * FROM Employee;
+-----+------+-------------+------+
| SSN | name | designation | MSSN |
+-----+------+-------------+------+
| 1   | A    | OWNER       | NULL |
| 2   | B    | BOSS        | 1    |  
| 3   | C    | WORKER      | 2    |  
| 4   | D    | BOSS        | 2    |  
| 5   | E    | WORKER      | 4    |   
| 6   | F    | WORKER      | 1    |  
| 7   | G    | WORKER      | 4    |  
+-----+------+-------------+------+
7 rows in set (0.00 sec)

现在，我想施加一个约束，例如 : If any employee (BOSS) deleted then new BOSS of workers under him become immediate BOSS of deleted employee (Old BOSS)。例如，如果我删除D，则B成为Gand的 BOSS E。

为此，我还编写了一个触发器，如下所示：

mysql>  DELIMITER $$
mysql>        CREATE
    ->        TRIGGER `Employee_before_delete` BEFORE DELETE
    ->          ON `Employee`
    ->          FOR EACH ROW BEGIN
    ->          UPDATE Employee
    ->          SET MSSN=old.MSSN
    ->          WHERE MSSN=old.MSSN; 
    ->        END$$
Query OK, 0 rows affected (0.07 sec)

mysql>        DELIMITER ;

但是当我执行一些删除时：

mysql> DELETE FROM Employee WHERE SSN='4';
ERROR 1442 (HY000): Can't update table 'Employee' in stored function/trigger
because it is already used by statement which invoked this stored 
function/trigger.

我在这里学习是this trigger is not possible因为In MySQL triggers can't manipulate the table they are assigned to.

还有其他可能的方法吗？有人可以建议我其他方法吗？一个建议就足够了，但应该是有效的。

编辑：可以使用Nested Query吗？
我也在这里问过。