Cómo ejecutar consultas MySQL desde la línea de comandos de Linux

Verificar que se hayan insertado los datos:

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

Crear base de datos de empleados

Ahora, creemos uno más complejo. employees Una base de datos con múltiples tablas relacionadas, esta es la base de datos que usaremos para ejemplos de consultas más avanzadas:

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

crear employees mesa:

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

crear salaries mesa:

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

crear departments Tabla de unión más compleja:

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

crear dept_emp tabla para vincular employees llegar 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

Verifique que todo esté configurado correctamente:

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

Ahora que ha configurado dos bases de datos con datos de muestra, puede seguir todos los ejemplos de esta guía. este tecmintdb Las bases de datos son excelentes para consultas simples y employees Las bases de datos le permiten practicar operaciones más complejas, como uniones y agregaciones.

Ejecución de consultas básicas

Para ver todas las bases de datos en el servidor, puede emitir el siguiente comando:

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

A continuación, cree una tabla de base de datos llamada tutorials en la base de datos tecmintdbejecute el siguiente comando:

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));"

Guarde los resultados de la consulta MySQL en un archivo

Usaremos el siguiente comando y canalizaremos la salida al comando tee seguido del nombre del archivo donde queremos almacenar la salida.

Para facilitar la explicación, usaremos un employees y conexiones simples entre employees y salaries superficie. En su caso, simplemente escriba la consulta SQL entre comillas y presione Enter.

Tenga en cuenta que se le solicitará la contraseña del usuario de la base de datos:

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

Utilice el comando cat para ver los resultados de la consulta.

cat queryresults.txt

Ejecute consultas MySQL/MariaDB desde la línea de comando

Con los resultados de la consulta en un archivo de texto sin formato, puede utilizar otras utilidades de línea de comandos para procesar registros más fácilmente. Ahora que conoce los conceptos básicos, exploremos algunas técnicas más avanzadas que harán que su base de datos de línea de comandos funcione de manera más eficiente.

Formatear la salida para una mejor legibilidad

El formato de tabla predeterminado es excelente para verlo en la terminal, pero a veces necesitas un formato diferente. Puede generar los resultados en formato vertical, lo cual es particularmente útil cuando se trabaja con tablas con muchas columnas:

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

este \G Finalmente, muestre cada fila verticalmente en lugar de en una tabla, de modo que en lugar de ver una tabla horizontal estrecha, vea algo como:

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

Exportar a formato CSV

El formato CSV es su mejor opción cuando necesita importar resultados de consultas a una aplicación de hoja de cálculo u otra herramienta:

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

Esto se canaliza a través de sed, reemplazando las pestañas con comas, creando un archivo CSV correcto que se puede abrir limpiamente en Sobresalir, Calculadora de oficina gratuitao cualquier otro software de hoja de cálculo.

Ejecutar consulta sin solicitud de contraseña

Si utiliza trabajos cron o scripts para automatizar tareas de bases de datos y no desea ingresar manualmente su contraseña cada vez, aquí es donde entran los archivos de configuración de MySQL.

Crear un archivo en ~/.my.cnf con tus credenciales:

[client]
user=root
password=your_password_here

Luego protégelo para que sólo tú puedas leerlo:

chmod 600 ~/.my.cnf

Ahora puedes ejecutar consultas sin -p Marcado y sin aviso:

mysql -e "SHOW DATABASES;"

Tenga en cuenta que almacenar contraseñas en archivos de texto sin formato crea implicaciones de seguridad, así que utilice este método sólo en servidores a los que controle el acceso y considere utilizar los métodos de autenticación más seguros de MySQL en entornos de producción.

Ejecute consultas complejas de varias filas

A veces, su consulta es demasiado compleja para escribirla en una sola línea de comando, especialmente cuando se trata de múltiples uniones, subconsultas o condiciones complejas.

Puede poner el SQL en un archivo y ejecutarlo:

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

Ejecútelo ahora.

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

Este enfoque mantiene sus consultas organizadas y reutilizables, y puede versionarlas usando git como cualquier otro código.

Lote de múltiples consultas

Si necesita ejecutar varias consultas relacionadas y guardar cada resultado por separado, puede escribir un script:

#!/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

Guárdelo como un script para hacerlo ejecutable. chmod +xtiene una herramienta de consulta por lotes reutilizable.

Supervisar consultas de larga duración

Cuando ejecuta consultas que pueden tardar un poco, desea ver el progreso o al menos saber que todavía están funcionando.

Combine su consulta con la salida de estado:

(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

Para consultas más largas, ejecútelas en segundo plano y supervise la lista de procesos de 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"'

Esto ejecuta su consulta en segundo plano mientras muestra la lista de procesos cada 5 segundos para que pueda ver si todavía está funcionando y cuánto progreso se ha logrado.

Filtrar y procesar resultados

Una vez que tenga los resultados de la consulta en un archivo de texto, puede procesarlos aún más utilizando herramientas estándar de Linux. Aquí hay algunos patrones útiles:

Cuente el número de filas de resultados (excluidos los encabezados):

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

Utilice awk para extraer columnas específicas:

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

Patrones específicos en los resultados de búsqueda:

grep -i "engineering" dept_distribution.txt

Ordenar resultados por columna numérica:

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

Manejar caracteres especiales y grandes conjuntos de datos

Cuando sus datos contienen caracteres especiales, tabulaciones o nuevas líneas, la salida predeterminada puede resultar confusa, así que use --batch y --raw Opciones para una producción más limpia:

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

Para consultas que devuelven millones de filas, es posible que tenga problemas de memoria y, en lugar de cargar todo en la memoria, transmita los resultados:

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

este --quick La opción le dice a MySQL que recupere una fila a la vez en lugar de almacenar en el buffer todo el conjunto de resultados, y la canalización a través de gzip comprime la salida sobre la marcha, ahorrando espacio en el disco.

Cree una copia de seguridad rápida de la base de datos

Aunque técnicamente esto no es ejecutar una consulta, puede utilizar técnicas de línea de comandos similares para crear un volcado rápido de la base de datos con el comando mysqldump.

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

O haga una copia de seguridad solo de tablas específicas:

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

Programe informes automatizados

Combine todo lo que cubrimos para crear informes diarios automatizados Tareas programadas Con la ayuda del siguiente script bash.

#!/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"

Agregue esto al cron para que se ejecute todos los días a las 6 a. m.:

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

generalizar

Hemos compartido algunos consejos de Linux que usted, como administrador del sistema, puede encontrar útiles al automatizar las tareas diarias de Linux o realizarlas más fácilmente.

La conclusión clave aquí es que no siempre es necesario iniciar un shell MySQL o utilizar herramientas GUI pesadas para trabajar con la base de datos; la línea de comando le brinda velocidad, capacidades de automatización y la capacidad de integrar operaciones de bases de datos en scripts de shell y flujos de trabajo existentes.

¿Tiene algún otro consejo que le gustaría compartir con el resto de la comunidad? Si es así, utilice el formulario de comentarios a continuación.