mysql基礎

2025-06-02

進入

mysql -u root -p

PHP連接mysql

$conn = new mysqli("localhost", "user", "pass");


$sql = "CREATE DATABASE database1";
$conn->query($sql)

$conn = new mysqli("localhost", "user", "pass", "database1");
$query = "select * from table_1";
$result = $conn->query($query);



$searchInput =  $_POST['findUser'];
$query = "select * from users where name like '%$searchInput%'";
$result = $conn->query($query);

while($row = $result->fetch_assoc() ){
echo $row["name"]."<br>";
}

查版本

SELECT VERSION();

查表

SHOW TABLES FROM 資料庫名稱;
SHOW TABLES;

查庫

SHOW DATABASES;

SELECT SCHEMA_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');



CREATE TABLE account (
    id INT AUTO_INCREMENT PRIMARY KEY,
    account VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE gacha_cards (
    card_id INT PRIMARY KEY,        -- 卡牌 ID
    rarity TINYINT NOT NULL,        -- 星級
    weight INT NOT NULL,            -- 權重（整數）
    pool VARCHAR(50) DEFAULT 'standard',  -- 卡池分類（可支援多卡池）
    is_enabled BOOLEAN DEFAULT TRUE -- 是否啟用（上架中）
);

ALTER TABLE account
ADD COLUMN login_token VARCHAR(255) NULL AFTER updated_at,
ADD COLUMN token_expiry DATETIME NULL AFTER login_token;

mysql -u root -p

GRANT INSERT ON aaaa.account TO ‘hacker‘@’localhost’;
FLUSH PRIVILEGES;
GRANT SELECT, INSERT, UPDATE, DELETE ON aaaa.account TO ‘hacker‘@’localhost’;

GRANT INSERT, UPDATE ON aaaa.gacha_cards TO ‘hacker‘@’localhost’;
FLUSH PRIVILEGES;

CREATE TABLE player_cards (
card_uid INT AUTO_INCREMENT PRIMARY KEY, – 唯一實體卡片 ID
player_id INT NOT NULL, – 所屬玩家 ID
card_id INT NOT NULL, – 參照卡池的卡牌 ID
level INT DEFAULT 1, – 卡牌等級
breakthrough INT DEFAULT 0, – 突破次數 / 星數
exp INT DEFAULT 0, – 經驗值（可選）
is_locked BOOLEAN DEFAULT FALSE, – 鎖卡保護
location ENUM(‘inventory’, ‘storage’) DEFAULT ‘inventory’, – 當前位置
obtained_at DATETIME DEFAULT CURRENT_TIMESTAMP,

FOREIGN KEY (player_id) REFERENCES account(id),
FOREIGN KEY (card_id) REFERENCES gacha_cards(card_id)

);

GRANT INSERT, SELECT, UPDATE, DELETE ON aaaa.player_cards TO 'hacker'@'localhost';
FLUSH PRIVILEGES;

給root權限

GRANT ALL PRIVILEGES ON *.* TO 'hacker'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;