← Volver

Resumen comandos SQL

Publicado el 8 de septiembre de 2025

MySQL

Consulta SELECT:

SELECT * FROM users WHERE age = 15;

Consulta INSERT:

INSERT INTO users (user_id, name, surname) VALUES (8, 'Maria', 'Lopez');

Consulta UPDATE:

UPDATE users SET age = '20',init_date = '2020-10-12' WHERE user_id = 11

Consulta DELETE:

DELETE FROM users WHERE user_id = 11;

Crear y eliminar base de datos:

CREATE DATABASE test;

DROP DATABASE test;

Crear tabla:

CREATE TABLE dni(
	  dni_id int AUTO_INCREMENT PRIMARY KEY,
    dni_number int NOT NULL,
    user_id int,
    UNIQUE(dni_id),
    FOREIGN KEY(user_id) REFERENCES users(user_id)
)

Borrar tabla:

DROP TABLE persons;

Modificar tabla:

ALTER TABLE persons8
ADD surname varchar(150);

ALTER TABLE persons8
RENAME COLUMN surname TO description;

ALTER TABLE persons8
MODIFY COLUMN description varchar(250);

ALTER TABLE persons8
DROP column description;

Consulta INNER JOIN:

SELECT * FROM users
JOIN dni
ON users.user_id = dni.user_id

Consulta LEFT JOIN:

SELECT * FROM users
LEFT JOIN dni
ON users.user_id = dni.user_id

Consulta RIGHT JOIN

SELECT * FROM users
RIGHT JOIN dni
ON users.user_id = dni.user_id

Crear y borrar índices:

CREATE INDEX idx_name ON users(name);

DROP INDEX idx_name ON users;

Triggers:

delimiter |

CREATE TRIGGER tg_email
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
	IF OLD.email <> NEW.email THEN
		INSERT INTO email_history (user_id, email) VALUES (OLD.user_id, OLD.email);
	END IF;
END;

|

delimiter ;

Crear y borrar view:

CREATE VIEW v_adult_users AS
SELECT name, age
FROM users
WHERE age >= 18

DROP VIEW v_adult_users;

Crear stored procedure:

DELIMITER //
CREATE PROCEDURE p_all_users()
BEGIN 
	SELECT * FROM users;
END;

CALL p_all_users();

DELIMITER //
CREATE PROCEDURE p_age_users(IN age_param int)
BEGIN 
	SELECT * FROM users WHERE age = age_param;
END;

CALL p_age_users(30);