Cara Menjalankan Pertanyaan MySQL dari Baris Perintah Linux

Jika anda bertanggungjawab menguruskan pelayan pangkalan data, dari semasa ke semasa anda mungkin perlu menjalankan pertanyaan dan memeriksanya dengan teliti. Walaupun anda boleh melakukannya dari shell MySQL/MariaDB, tip ini akan membolehkan anda menjalankan Pertanyaan MySQL/MariaDB secara terus menggunakan baris perintah Linux DAN menyimpan hasilnya ke dalam fail untuk pemeriksaan kemudian (ini amat berguna jika pertanyaan tersebut mengembalikan banyak rekod).

Mari kita lihat beberapa contoh mudah menjalankan pertanyaan MYSQL terus dari baris arahan sebelum kita boleh beralih kepada pertanyaan yang lebih maju.

Menetapkan Pangkalan Data Contoh

Sebelum kita menyelami arahan-arahan, mari kita sediakan pangkalan data contoh yang akan kita gunakan sepanjang panduan ini, supaya anda boleh mengikuti dan mengamalkan teknik-teknik ini pada sistem anda sendiri.

Membuat Pangkalan Data tecmintdb

Pertama, mari kita buat pangkalan data tecmintdb dan jadual tutorials:

mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS tecmintdb;"

Seterusnya, untuk membuat jadual pangkalan data bernama tutorials dalam pangkalan data tecmintdb, jalankan arahan di bawah:

sudo mysql -u root -p tecmintdb << 'EOF'
CREATE TABLE IF NOT EXISTS tutorials (
    tut_id INT NOT NULL AUTO_INCREMENT,
    tut_title VARCHAR(100) NOT NULL,
    tut_author VARCHAR(40) NOT NULL,
    submission_date DATE,
    PRIMARY KEY (tut_id)
);

INSERT INTO tutorials (tut_title, tut_author, submission_date) VALUES
('Getting Started with Linux', 'John Smith', '2024-01-15'),
('Advanced Bash Scripting', 'Sarah Johnson', '2024-02-20'),
('MySQL Database Administration', 'Mike Williams', '2024-03-10'),
('Apache Web Server Configuration', 'Emily Brown', '2024-04-05'),
('Python for System Administrators', 'David Lee', '2024-05-12'),
('Docker Container Basics', 'Lisa Anderson', '2024-06-18'),
('Kubernetes Orchestration', 'Robert Taylor', '2024-07-22'),
('Linux Security Hardening', 'Jennifer Martinez', '2024-08-30');
EOF

Sahkan data telah dimasukkan:

sudo mysql -u root -p -e "USE tecmintdb; SELECT * FROM tutorials;"

Mewujudkan Pangkalan Data Pekerja

Sekarang, mari kita buat pangkalan data employees yang lebih kompleks dengan beberapa jadual berkaitan, ini adalah pangkalan data yang akan kita gunakan untuk contoh pertanyaan yang lebih lanjutan:

sudo mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS employees;"

Cipta jadual employees:

sudo mysql -u root -p employees << 'EOF'
CREATE TABLE IF NOT EXISTS employees (
    emp_no INT NOT NULL AUTO_INCREMENT,
    birth_date DATE NOT NULL,
    first_name VARCHAR(14) NOT NULL,
    last_name VARCHAR(16) NOT NULL,
    gender ENUM('M','F') NOT NULL,
    hire_date DATE NOT NULL,
    PRIMARY KEY (emp_no)
);

INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES
(10001, '1953-09-02', 'Georgi', 'Facello', 'M', '1984-06-02'),
(10002, '1964-06-02', 'Bezalel', 'Simmel', 'F', '1984-11-21'),
(10003, '1959-12-03', 'Parto', 'Bamford', 'M', '1984-08-28'),
(10004, '1954-05-01', 'Chirstian', 'Koblick', 'M', '1984-12-01'),
(10005, '1955-01-21', 'Kyoichi', 'Maliniak', 'M', '1984-09-15'),
(10006, '1953-04-20', 'Anneke', 'Preusig', 'F', '1985-02-18'),
(10007, '1957-05-23', 'Tzvetan', 'Zielinski', 'F', '1985-03-20'),
(10008, '1958-02-19', 'Saniya', 'Kalloufi', 'M', '1984-07-11'),
(10009, '1952-04-19', 'Sumant', 'Peac', 'F', '1985-02-18'),
(10010, '1963-06-01', 'Duangkaew', 'Piveteau', 'F', '1984-08-24');
EOF

Buat jadual salaries:

sudo mysql -u root -p employees << 'EOF'
CREATE TABLE IF NOT EXISTS salaries (
    emp_no INT NOT NULL,
    salary INT NOT NULL,
    from_date DATE NOT NULL,
    to_date DATE NOT NULL,
    PRIMARY KEY (emp_no, from_date),
    FOREIGN KEY (emp_no) REFERENCES employees(emp_no) ON DELETE CASCADE
);

INSERT INTO salaries (emp_no, salary, from_date, to_date) VALUES
(10001, 60117, '1984-06-02', '1985-06-02'),
(10001, 62102, '1985-06-02', '1986-06-02'),
(10001, 66074, '1986-06-02', '9999-01-01'),
(10002, 65828, '1984-11-21', '1985-11-21'),
(10002, 65909, '1985-11-21', '9999-01-01'),
(10003, 40006, '1984-08-28', '1985-08-28'),
(10003, 43616, '1985-08-28', '9999-01-01'),
(10004, 40054, '1984-12-01', '1985-12-01'),
(10004, 42283, '1985-12-01', '9999-01-01'),
(10005, 78228, '1984-09-15', '1985-09-15'),
(10005, 82507, '1985-09-15', '9999-01-01'),
(10006, 40000, '1985-02-18', '1986-02-18'),
(10006, 43548, '1986-02-18', '9999-01-01'),
(10007, 56724, '1985-03-20', '1986-03-20'),
(10007, 60605, '1986-03-20', '9999-01-01'),
(10008, 46671, '1984-07-11', '1985-07-11'),
(10008, 48584, '1985-07-11', '9999-01-01'),
(10009, 60929, '1985-02-18', '1986-02-18'),
(10009, 64604, '1986-02-18', '9999-01-01'),
(10010, 72488, '1984-08-24', '1985-08-24'),
(10010, 74057, '1985-08-24', '9999-01-01');
EOF

Buat jadual departments untuk sambungan yang lebih kompleks:

sudo mysql -u root -p employees << 'EOF'
CREATE TABLE IF NOT EXISTS departments (
    dept_no CHAR(4) NOT NULL,
    dept_name VARCHAR(40) NOT NULL,
    PRIMARY KEY (dept_no),
    UNIQUE KEY (dept_name)
);

INSERT INTO departments (dept_no, dept_name) VALUES
('d001', 'Marketing'),
('d002', 'Finance'),
('d003', 'Human Resources'),
('d004', 'Production'),
('d005', 'Development'),
('d006', 'Quality Management');
EOF

Buat jadual dept_emp untuk menghubungkan employees dengan departments:

sudo mysql -u root -p employees << 'EOF'
CREATE TABLE IF NOT EXISTS dept_emp (
    emp_no INT NOT NULL,
    dept_no CHAR(4) NOT NULL,
    from_date DATE NOT NULL,
    to_date DATE NOT NULL,
    PRIMARY KEY (emp_no, dept_no),
    FOREIGN KEY (emp_no) REFERENCES employees(emp_no) ON DELETE CASCADE,
    FOREIGN KEY (dept_no) REFERENCES departments(dept_no) ON DELETE CASCADE
);

INSERT INTO dept_emp (emp_no, dept_no, from_date, to_date) VALUES
(10001, 'd005', '1984-06-02', '9999-01-01'),
(10002, 'd005', '1984-11-21', '9999-01-01'),
(10003, 'd004', '1984-08-28', '9999-01-01'),
(10004, 'd004', '1984-12-01', '9999-01-01'),
(10005, 'd003', '1984-09-15', '9999-01-01'),
(10006, 'd005', '1985-02-18', '9999-01-01'),
(10007, 'd004', '1985-03-20', '9999-01-01'),
(10008, 'd005', '1984-07-11', '9999-01-01'),
(10009, 'd006', '1985-02-18', '9999-01-01'),
(10010, 'd006', '1984-08-24', '9999-01-01');
EOF

Sahkan bahawa semuanya telah disediakan dengan betul:

sudo mysql -u root -p -e "USE employees; SHOW TABLES;"

Kini anda telah menyediakan kedua-dua pangkalan data dengan data contoh, dan anda boleh mengikuti semua contoh dalam panduan ini. Pangkalan data tecmintdb sesuai untuk pertanyaan mudah, manakala pangkalan data employees membolehkan anda berlatih operasi yang lebih kompleks seperti penggabungan dan agregasi.

Pelaksanaan Pertanyaan Asas

Untuk melihat semua pangkalan data di pelayan anda, anda boleh mengeluarkan arahan berikut:

sudo mysql -u root -p -e "show databases;"

Seterusnya, untuk membuat jadual pangkalan data bernama tutorials dalam pangkalan data tecmintdb, jalankan arahan di bawah:

sudo mysql -u root -p -e "USE tecmintdb; CREATE TABLE tutorials(tut_id INT NOT NULL AUTO_INCREMENT, tut_title VARCHAR(100) NOT NULL, tut_author VARCHAR(40) NOT NULL, submissoin_date DATE, PRIMARY KEY (tut_id));"

Menyimpan Keputusan Pertanyaan MySQL ke dalam Fail

Kita akan menggunakan arahan berikut dan menyalurkan output ke arahan tee diikuti dengan nama fail di mana kita ingin menyimpan output tersebut.

Sebagai ilustrasi, kami akan menggunakan pangkalan data bernama employees dan sambungan mudah antara jadual employees dan salaries. Dalam kes anda sendiri, cuma taipkan kueri SQL antara tanda petik dan tekan Enter.

Perhatikan bahawa anda akan diminta untuk memasukkan kata laluan bagi pengguna pangkalan data:

sudo mysql -u root -p -e "USE employees; SELECT DISTINCT A.first_name, A.last_name FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE hire_date < '1985-01-31';" | tee queryresults.txt

Lihat keputusan pertanyaan dengan bantuan arahan cat.

cat queryresults.txt

Jalankan Pertanyaan MySQL/MariaDB dari Baris Perintah

Dengan keputusan carian dalam fail teks biasa, anda boleh memproses rekod dengan lebih mudah menggunakan utiliti baris perintah yang lain. Kini setelah anda melihat asas-asasnya, mari kita terokai beberapa teknik yang lebih maju yang akan menjadikan kerja pangkalan data baris perintah anda lebih efisien.

Memformat Output untuk Bacaan yang Lebih Baik

Format jadual lalai sangat baik untuk dilihat di terminal, tetapi kadang-kadang anda memerlukan format yang berbeza. Anda boleh mengeluarkan keputusan dalam format menegak, yang amat berguna apabila mengendalikan jadual yang mempunyai banyak lajur:

sudo mysql -u root -p -e "USE employees; SELECT * FROM employees LIMIT 1\G"

\G di hujung memaparkan setiap baris secara menegak dan bukannya dalam bentuk jadual, jadi daripada melihat jadual mendatar yang sempit, anda akan mendapat sesuatu seperti:

*************************** 1. row ***************************
    emp_no: 10001
birth_date: 1953-09-02
first_name: Georgi
 last_name: Facello
    gender: M
 hire_date: 1984-06-02

Mengeksport ke Format CSV

Apabila anda perlu mengimport hasil carian ke dalam aplikasi hamparan atau alat lain, format CSV adalah pilihan terbaik anda:

sudo mysql -u root -p -e "USE employees; SELECT first_name, last_name, hire_date FROM employees WHERE hire_date < '1985-01-31';" | sed 's/\t/,/g' > employees.csv

Ini menyalurkan output melalui sed untuk menggantikan tab dengan koma, menghasilkan fail CSV yang betul yang boleh dibuka dengan lancar di Excel, LibreOffice Calc, atau mana-mana perisian hamparan lain.

Menjalankan Pertanyaan Tanpa Prompt Kata Laluan

Jika anda mengautomasikan tugas pangkalan data dengan cron job atau skrip, anda tidak mahu memasukkan kata laluan secara manual setiap kali, di situlah fail konfigurasi MySQL digunakan.

Cipta fail di ~/.my.cnf dengan kelayakan anda:

[client]
user=root
password=your_password_here

Kemudian pastikan ia supaya hanya anda boleh membacanya:

chmod 600 ~/.my.cnf

Sekarang anda boleh menjalankan pertanyaan tanpa menggunakan flag -p dan tanpa diprompt:

mysql -e "SHOW DATABASES;"

Ingat, menyimpan kata laluan dalam fail teks biasa mempunyai implikasi keselamatan, jadi gunakan pendekatan ini hanya pada pelayan di mana anda mengawal akses, dan pertimbangkan untuk menggunakan kaedah pengesahan MySQL yang lebih selamat untuk persekitaran pengeluaran.

Melaksanakan Pertanyaan Berbilang Baris yang Kompleks

Kadang-kadang pertanyaan anda terlalu kompleks untuk ditulis dalam satu baris arahan, terutamanya apabila anda berurusan dengan pelbagai penggabungan, subquery, atau syarat yang kompleks.

Anda boleh meletakkan SQL anda dalam satu fail dan menjalankannya:

cat > complex_query.sql << 'EOF'
USE employees;
SELECT 
    e.first_name,
    e.last_name,
    d.dept_name,
    s.salary
FROM employees e
INNER JOIN dept_emp de ON e.emp_no = de.emp_no
INNER JOIN departments d ON de.dept_no = d.dept_no
INNER JOIN salaries s ON e.emp_no = s.emp_no
WHERE e.hire_date BETWEEN '1985-01-01' AND '1985-12-31'
    AND s.from_date = (
        SELECT MAX(from_date) 
        FROM salaries 
        WHERE emp_no = e.emp_no
    )
ORDER BY s.salary DESC
LIMIT 10;
EOF

Sekarang laksanakannya.

sudo mysql -u root -p < complex_query.sql > top_earners_1985.txt

Pendekatan ini memastikan pertanyaan anda tersusun dan boleh digunakan semula, dan anda boleh mengawal versi mereka dengan git seperti mana-mana kod lain.

Pemprosesan Kumpulan Pelbagai Pertanyaan

Jika anda perlu menjalankan beberapa pertanyaan yang berkaitan dan menyimpan setiap keputusan secara berasingan, anda boleh menulis skripnya:

#!/bin/bash

QUERIES=(
    "SELECT COUNT(*) as total_employees FROM employees"
    "SELECT dept_name, COUNT(*) as employee_count FROM dept_emp de JOIN departments d ON de.dept_no = d.dept_no GROUP BY dept_name"
    "SELECT YEAR(hire_date) as year, COUNT(*) as hires FROM employees GROUP BY YEAR(hire_date) ORDER BY year"
)

FILENAMES=(
    "total_count.txt"
    "dept_distribution.txt"
    "yearly_hires.txt"
)

for i in "${!QUERIES[@]}"; do
    echo "Running query $((i+1))..."
    mysql -u root -p -e "USE employees; ${QUERIES[$i]}" > "${FILENAMES[$i]}"
    echo "Results saved to ${FILENAMES[$i]}"
done

Simpan ini sebagai skrip, jadikannya boleh dijalankan dengan chmod +x, dan anda telah mempunyai alat pertanyaan batch yang boleh digunakan semula.

Memantau Pertanyaan Jangka Panjang

Apabila anda menjalankan pertanyaan yang mungkin mengambil masa, anda ingin melihat kemajuan atau sekurang-kurangnya mengetahui ia masih berfungsi.

Gabungkan pertanyaan anda dengan output status:

(sudo mysql -u root -p -e "USE employees; SELECT COUNT(*) FROM large_table WHERE complex_condition;" && echo "Query completed at $(date)") | tee query_log.txt

Untuk pertanyaan yang lebih panjang, jalankan ia di latar belakang dan pantau senarai proses MySQL:

sudo mysql -u root -p -e "USE employees; SELECT * FROM massive_table;" > output.txt &
sudo watch -n 5 'mysql -u root -p -e "SHOW PROCESSLIST\G" | grep -A 5 "SELECT"'

Ini menjalankan pertanyaan anda di latar belakang sambil memaparkan senarai proses setiap 5 saat, supaya anda dapat melihat bahawa ia masih berfungsi dan sejauh mana kemajuannya.

Menapis dan Memproses Keputusan

Setelah anda mempunyai hasil carian dalam fail teks, anda boleh menggunakan alat Linux standard untuk memprosesnya dengan lebih lanjut. Berikut adalah beberapa corak yang berguna:

Kira bilangan baris keputusan (tidak termasuk tajuk):

tail -n +2 queryresults.txt | wc -l

Ekstrak lajur tertentu menggunakan awk:

awk '{print $1, $3}' queryresults.txt

Cari corak tertentu dalam keputusan:

grep -i "engineering" dept_distribution.txt

Susun keputusan mengikut lajur nombor:

tail -n +2 queryresults.txt | sort -k3 -n

Mengendalikan Aksara Khas dan Set Data Besar

Apabila data anda mengandungi aksara khas, tab, atau baris baru, output lalai boleh menjadi berselerak, jadi gunakan pilihan --batch dan --raw untuk output yang lebih kemas:

sudo mysql -u root -p --batch --raw -e "SELECT description FROM products WHERE category='electronics';" > products.txt

Untuk pertanyaan yang mengembalikan berjuta-juta baris, anda mungkin menghadapi masalah memori, daripada memuatkan semuanya ke dalam memori, alirkan hasilnya:

sudo mysql -u root -p --quick -e "SELECT * FROM huge_table;" | gzip > huge_results.txt.gz

Pilihan --quick memberitahu MySQL untuk mengambil baris satu persatu daripada menampung keseluruhan set keputusan, dan menghantar melalui gzip memampatkan output secara terus, menjimatkan ruang cakera.

Membuat Sandaran Pangkalan Data Dengan Cepat

Walaupun ini secara teknikalnya bukan menjalankan pertanyaan, anda boleh menggunakan teknik baris arahan yang serupa untuk membuat salinan pangkalan data dengan cepat menggunakan arahan mysqldump.

sudo mysqldump -u root -p employees | gzip > employees_backup_$(date +%Y%m%d).sql.gz

Atau sandarkan hanya beberapa jadual tertentu:

sudo mysqldump -u root -p employees employees salaries | gzip > critical_tables_$(date +%Y%m%d).sql.gz

Menjadualkan Laporan Automatik

Gabungkan semua yang telah kita pelajari untuk membuat laporan harian automatik menggunakan cron dengan bantuan skrip bash berikut.

#!/bin/bash
REPORT_DATE=$(date +%Y-%m-%d)
REPORT_FILE="/var/reports/daily_stats_${REPORT_DATE}.txt"

{
    echo "Database Statistics Report - ${REPORT_DATE}"
    echo "=========================================="
    echo
    
    echo "Total Employees:"
    mysql -e "USE employees; SELECT COUNT(*) FROM employees;"
    echo
    
    echo "New Hires This Month:"
    mysql -e "USE employees; SELECT COUNT(*) FROM employees WHERE MONTH(hire_date) = MONTH(CURRENT_DATE()) AND YEAR(hire_date) = YEAR(CURRENT_DATE());"
    echo
    
    echo "Department Distribution:"
    mysql -e "USE employees; SELECT d.dept_name, COUNT(*) as count FROM dept_emp de JOIN departments d ON de.dept_no = d.dept_no WHERE de.to_date = '9999-01-01' GROUP BY d.dept_name ORDER BY count DESC;"
    
} > "$REPORT_FILE"

echo "Report generated: $REPORT_FILE"

Tambahkan ia ke cron untuk dijalankan setiap hari pada pukul 6 pagi:

0 6 * * * /usr/local/bin/generate_db_report.sh

Ringkasan

Kami telah berkongsi beberapa petua Linux yang mungkin berguna bagi anda, sebagai pentadbir sistem, apabila berkaitan dengan mengautomasikan tugas harian Linux anda atau melaksanakannya dengan lebih mudah.

Inti utama di sini ialah anda tidak selalu perlu membuka shell MySQL atau menggunakan alat GUI yang berat untuk bekerja dengan pangkalan data anda; baris perintah memberi anda kelajuan, keupayaan automasi, dan kemampuan untuk mengintegrasikan operasi pangkalan data ke dalam skrip shell dan aliran kerja sedia ada anda.

Adakah anda mempunyai sebarang petua lain yang ingin dikongsi dengan komuniti lain? Jika ya, sila kongsikannya menggunakan borang komen di bawah. :