☰

mysql 显示数据库；不起作用，它在脚本之后一次又一次地给出 ->

tag-icon • tag-icon mysql
mysql 显示数据库；不起作用，它在脚本之后一次又一次地给出 ->

我有一个脚本，可以运行它来创建数据库。我使用以下命令运行它：

source <file>

当它完成时显示

mysql>

然后我输入

show databases;

然后转到下一行并输入

->

好像我错过了;，但我没有。

它对

show tables;

我不知道我错过了什么。

脚本是

DROP DATABASE IF EXISTS app;
CREATE DATABASE app;
USE app;

CREATE TABLE `user`
(
    id INT PRIMARY KEY AUTO_INCREMENT, 
    forename VARCHAR(20) NOT NULL, 
    surname VARCHAR(20) NOT NULL, 
    `status` VARCHAR(15), 
    deleted BOOLEAN
);

CREATE TABLE login
(
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    email VARCHAR(50) NOT NULL UNIQUE,
    `password` VARCHAR(65),
    FOREIGN KEY(user_id) REFERENCES `user`(id)
);

DELIMITER //
CREATE PROCEDURE sp_Create_User
(
    in_forename VARCHAR(20), 
    in_surname VARCHAR(20), 
    in_status VARCHAR(15), 
    in_deleted BOOLEAN, 
    in_email VARCHAR(50), 
    in_password VARCHAR(65)
)
BEGIN
DECLARE found_user_id INT;
INSERT INTO `user`
(
    forename,
    surname,
    `status`,
    deleted 
)
VALUES
(
    in_forename,
    in_surname,
    in_status,
    in_deleted  
);
SET found_user_id = 
(
    SELECT id 
    FROM app.`user`
    WHERE 
    forename = in_forename 
    AND 
    surname = in_surname 
    AND 
    id 
    NOT IN 
    (
        SELECT user_id FROM app.login
    )
);
INSERT INTO app.login
(
    user_id,
    email,
    `password`
)
VALUES
(
    found_user_id,
    in_email,
    in_password
);
END //

CREATE PROCEDURE sp_Get_All_Users()
BEGIN
SELECT u.forename, u.surname, u.`status`, u.deleted, l.user_id, l.email, l.password 
FROM `user` u
INNER JOIN login l
ON
u.id = l.user_id;
END //

CREATE PROCEDURE sp_Update_User_Status(in_id INT, in_status VARCHAR(15))
BEGIN
UPDATE app.`user`
SET `status` = in_status
WHERE id = in_id;
END //

CREATE PROCEDURE sp_Delete_User(in_id INT, in_deleted BOOLEAN)
BEGIN
UPDATE app.`user`
SET deleted = in_deleted
WHERE id = in_id;
END //

CREATE PROCEDURE sp_Get_Login(in_email VARCHAR(50), in_password VARCHAR(65))
BEGIN
SELECT l.user_id, l.email, l.`password`, u.deleted, u.status
FROM login l
INNER JOIN `user` u
ON l.user_id = u.id
WHERE l.email = in_email AND l.`password` = in_password;
END //

CREATE PROCEDURE sp_Update_Password(in_user_id INT, in_old_password VARCHAR(65), in_new_password VARCHAR(65))
BEGIN
UPDATE app.login
SET `password` = in_new_password
WHERE user_id = in_user_id AND `password` = in_old_password;
END //

答案1

因为你有

DELIMITER //

在您的查询中。

在脚本中添加此操作或在脚本运行完成后手动运行它：

DELIMITER ;

相关内容