-- 授权用户'user_name'@'host_name'使用密码'password'对数据库'db_name'有SELECT, INSERT, UPDATE权限
GRANT SELECT, INSERT, UPDATE ON db_name.* TO 'user_name'@'host_name' IDENTIFIED BY 'password';

-- 授权用户'user_name'@'host_name'对数据库'db_name'的表'table_name'有所有权限
GRANT ALL ON db_name.table_name TO 'user_name'@'host_name';

-- 授权用户'user_name'@'host_name'执行存储过程'procedure_name'的权限
GRANT EXECUTE ON PROCEDURE db_name.procedure_name TO 'user_name'@'host_name';

-- 授权用户'user_name'@'host_name'执行任何函数的权限
GRANT EXECUTE ON FUNCTION * TO 'user_name'@'host_name';

-- 授权用户'user_name'@'host_name'对所有数据库的所有表执行SELECT操作的权限
GRANT SELECT ON *.* TO 'user_name'@'host_name';

-- 授权用户'user_name'@'host_name'同时具有SELECT和INSERT权限，并设置密码
GRANT SELECT, INSERT ON db_name.* TO 'user_name'@'host_name' IDENTIFIED BY 'password';

-- 撤销用户'user_name'@'host_name'对表'table_name'的SELECT权限
REVOKE SELECT ON db_name.table_name FROM 'user_name'@'host_name';

-- 删除用户'user_name'@'host_name'
DELETE FROM mysql.user WHERE User='user_name' AND Host='host_name';
FLUSH PRIVILEGES; -- 刷新权限使更改生效

sudo apt-update
sudo apt-get install postfix dovecot-imapd dovecot-pop3d

sudo apt-get install mysql-server mysql-client libsasl2-modules-sql
sudo systemctl enable mysql
sudo systemctl start mysql

sudo mysql
CREATE DATABASE mailserver;
GRANT SELECT ON mailserver.* TO 'mailuser'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
exit;

myhostname = mail.example.com
mydomain = example.com
myorigin = $mydomain
mydestination = $myhostname, localhost.$mydomain, $mydomain
relay_domains = $config_directory/relay_domains
mynetworks = 127.0.0.0/8 [::1]/128
home_mailbox = Maildir/
smtpd_sasl_auth_enable = yes
smtpd_sasl_security_options = noanonymous
smtpd_sasl_local_domain = $myhostname
smtpd_recipient_restrictions = permit_mynetworks,permit_sasl_authenticated,reject_unauth_destination

protocols = imap pop3 lmtp
login_trusted_networks = 0.0.0.0/0
disable_plaintext_auth = no
mail_location = maildir:~/Maildir

pwcheck_method: saslauthd
mech_list: plain login

sudo systemctl restart postfix dovecot
sudo systemctl enable postfix dovecot

-- 创建部门表
CREATE TABLE department (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);

-- 创建员工表，每个员工属于一个部门
CREATE TABLE employee (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  department_id INT,
  FOREIGN KEY (department_id) REFERENCES department(id)
);

-- 创建书籍表
CREATE TABLE book (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(50) NOT NULL
);

-- 创建作者表
CREATE TABLE author (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);

-- 创建书籍与作者的关系表
CREATE TABLE book_author (
  book_id INT,
  author_id INT,
  FOREIGN KEY (book_id) REFERENCES book(id),
  FOREIGN KEY (author_id) REFERENCES author(id)
);

-- 查询每本书的标题和作者姓名
SELECT b.title, a.name
FROM book b
JOIN book_author ba ON b.id = ba.book_id
JOIN author a ON ba.author_id = a.id;

SELECT GROUP_CONCAT(column_name SEPARATOR ',') AS column_name
FROM table_name
WHERE condition;

SELECT CONCAT_WS(',', column_name1, column_name2, ...) AS column_name
FROM table_name
WHERE condition;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class JdbcExample {
    public static void main(String[] args) {
        // 数据库URL，用户名和密码
        String url = "jdbc:mysql://localhost:3306/数据库名";
        String user = "用户名";
        String password = "密码";

        // 使用try-with-resources确保连接最终关闭
        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            // 连接成功后的操作
            System.out.println("连接成功！");
            // 例如：执行查询、更新等操作
        } catch (SQLException e) {
            // 异常处理
            e.printStackTrace();
        }
    }
}

SELECT users.id, users.name, orders.order_id, orders.amount
FROM users
INNER JOIN orders ON users.id = orders.user_id;

SELECT users.id, users.name, orders.order_id, orders.amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

SELECT * FROM your_table
WHERE id > (SELECT id FROM your_table ORDER BY id LIMIT 100000, 1)
LIMIT 10;

UPDATE your_table
SET column1 = CASE id
    WHEN 1 THEN 'value1'
    WHEN 2 THEN 'value2'
    ...
    WHEN N THEN 'valueN'
END
WHERE id IN (1, 2, ..., N);

INSERT INTO your_table (column1, column2)
VALUES
('value1', 'value2'),
('value3', 'value4'),
...
('valueN', 'valueN+1');

SELECT * FROM your_table
WHERE column1 = 'value1'
AND column2 IN ('value2', 'value3', ..., 'valueN');

import mysql.connector

# 建立数据库连接
cnx = mysql.connector.connect(user='username', password='password', host='127.0.0.1', database='database_name')
cursor = cnx.cursor()

# 插入数据
add_data = [
    (1, 'value1', 'value2'),
    (2, 'value3', 'value4'),
    ...
    (N, 'valueN', 'valueN+1')
]

# 批量插入
cursor.executemany('INSERT INTO your_table (column1, column2) VALUES (%s, %s)', add_data)

# 提交操作
cnx.commit()

# 关闭连接
cursor.close()
cnx.close()