MySQL and SQL Server Cheat Sheet: Backups, Database Creation, Stored Procedures and Views

Exporting Data to a File

Use SELECT ... INTO OUTFILE to export query results to a text file:

SELECT nombre, ciudad
INTO OUTFILE 'copia.txt'
FIELDS TERMINATED BY 'l'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM equipos
WHERE zona = 'oeste';

Database Backups with mysqldump

Backing Up a Single Database

Include drop table statements and lock tables during the dump:

mysqldump --add-drop-table --lock-tables --databases --user=root --password empresa > empresa.sql

Or, without the extra options:

mysqldump -u root -p empresa > empresa.sql

Backing Up Multiple Databases

mysqldump -u root -p --databases empresa jardineria > copia.sql

Backing Up All Databases

mysqldump -u root -p --all-databases > copia.sql

Backing Up Tables

To back up a single table:

mysqldump -u root -p jardineria clientes > copia.sql

To back up two or more tables:

mysqldump -u root -p jardineria clientes empleados > copia.sql

Creating a Database in SQL Server

The following script creates the jardineria database with a primary data file and a transaction log file:

USE master;
GO
CREATE DATABASE jardineria
ON
(
    NAME = 'jardineria',
    FILENAME = 'D:\basesdedatos\sqlmanagement\jardineria.mdf',
    SIZE = 0,
    MAXSIZE = 50,
    FILEGROWTH = 5
) 
LOG ON
(
    NAME = 'jardineria_log',
    FILENAME = 'D:\basesdedatos\sqlmanagement\jardineria.ldf',
    SIZE = 0,
    MAXSIZE = 50,
    FILEGROWTH = 5
);
GO

Stored Procedures

Deleting Stored Procedures

DROP PROCEDURE proc_empresa;

To drop several procedures at once:

DROP PROCEDURE proc_empresa, proc_jardineria;

Creating Stored Procedures

Example: Minimum Hourly Price

This procedure returns the minimum price per hour for vehicles matching a plate pattern:

USE concesionario;
GO
CREATE PROC proc_vehiculos
    @matri VARCHAR(30) = '%',
    @minim DECIMAL(6,2) OUTPUT
AS
SELECT @minim = MIN(precio / hora)
FROM vehiculos
WHERE matricula LIKE @matri;
GO

DECLARE @var DECIMAL(6,2);
EXEC proc_vehiculos '%cl%', @var OUTPUT;
SELECT @var AS minimo_precio_hora;

Example: Deleting a Rental

CREATE PROC pa_borradoalquiler
    @contrato INT
AS
IF @contrato <= 0
    RETURN 0;
ELSE
BEGIN
    DELETE FROM alquileres
    WHERE contrato = @contrato;
    RETURN 1;
END;

DECLARE @var INT;
EXEC @var = pa_borradoalquiler 1;
IF @var = 0
    PRINT 'No se pudo borrar el alquiler';

Example: Calculating an Average

CREATE PROC proc_num
    @num1 DECIMAL(4,2),
    @num2 DECIMAL(4,2),
    @resultado DECIMAL(4,2) OUTPUT
AS
SELECT @resultado = (@num1 + @num2) / 2;

DECLARE @var DECIMAL(4,2);
EXEC proc_num 6, 6, @var OUTPUT;
SELECT @var;

Example: Updating a Driver's Address

USE concesionario;
GO
CREATE PROC proc_actualizar
    @nuevadir VARCHAR(50),
    @ape VARCHAR(50)
AS
UPDATE chofer
SET direccion = @nuevadir
WHERE apellido = @ape;
GO

EXEC proc_actualizar 'c/xx', 'garcia';

Example: Clients Exceeding a Spending Threshold

USE concesionario;
GO
CREATE PROC p_clientes
AS
SELECT dnicliente, SUM(importe) AS total
FROM alquileres
GROUP BY dnicliente
HAVING SUM(importe) > 400000;
GO

EXEC p_clientes;

Views

The following view lists the models rented by a specific client:

CREATE VIEW v_modelos
AS
SELECT TOP (100) PERCENT modelo
FROM vehiculos
INNER JOIN (
    alquileres
    INNER JOIN clientes ON alquileres.dnicliente = clientes.dnicliente
) ON vehiculos.matricula = alquileres.matricula
WHERE nombre = 'luis' AND apellido = 'garcia'
ORDER BY modelo;