SQL进阶之路：从创建库指令到炸裂函数，揭秘大厂面试高频考点

CREATE DATABASE

CREATE DATABASE my_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

utf8mb4

unicode_ci

mysqldump

mysqldump -u root -p my_database > my_database_backup.sql

GRANT

GRANT SELECT, INSERT ON my_database.* TO 'user'@'localhost';

my_database

CREATE INDEX

CREATE INDEX idx_name ON users(name);

users

name

SELECT *

SELECT *

WHERE

WHERE YEAR(date_column) = 2023

WHERE date_column >= '2023-01-01' AND date_column < '2024-01-01'

EXPLAIN

EXPLAIN SELECT * FROM users WHERE name = 'John';

GROUP BY

RANK()

SELECT
    employee_name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank
FROM
    employees;

PARTITION BY

ORDER BY

RANK()

ROW_NUMBER()

DENSE_RANK()

NTILE()

ROW_NUMBER()

DENSE_RANK()

SELECT
    month,
    sales,
    SUM(sales) OVER (ORDER BY month) as cumulative_sales
FROM
    sales_data;

IS NULL

IS NOT NULL

WHERE column_name IS NULL

column_name

COALESCE()

COALESCE(column_name, 'default_value')

column_name

default_value

COALESCE()

sqlite3

import sqlite3

conn = sqlite3.connect('my_database.db')
cursor = conn.cursor()

username = input("Enter username: ")
password = input("Enter password: ")

# 使用参数化查询
cursor.execute("SELECT * FROM users WHERE username = ? AND password = ?", (username, password))

results = cursor.fetchall()

conn.close()