A few bits of MySQL you may find useful
How to create a new table and to populate it with data from a query
— Create a new table for our data
create table movies_and_genres
(
title varchar(255),
genre varchar(255)
);
— Now pop in the data
insert into
movies_and_genres
(
title,
genre
)
SELECT
m.title,
g.name
FROM
movies m
inner join
genres_movies gm on gm.movie_id = m.id
inner join
genres g on gm.genre_id = g.id
order by
m.title desc
Same idea but dropping the table if it already exists
— Remove the table if it exists
drop table if exists movies_and_genres;
— Create a new table for our data
create table movies_and_genres
(
title varchar(255),
genre varchar(255)
);
— Now pop in the data
insert into
movies_and_genres
SELECT
m.title,
g.name
FROM
movies m
inner join
genres_movies gm on gm.movie_id = m.id
inner join
genres g on gm.genre_id = g.id
order by
m.title desc
Perhaps a better approach, create the table if needed (note that this version will add to the existing data in the table, you might wish to truncate it first – it appropriate)
— Create a new table for our data IF WE NEED TO!
create table if not exists movies_and_genres
(
title varchar(255),
genre varchar(255)
);
— Now pop in the data
insert into
movies_and_genres
SELECT
m.title,
g.name
FROM
movies m
inner join
genres_movies gm on gm.movie_id = m.id
inner join
genres g on gm.genre_id = g.id
order by
m.title desc
Example script to create a stored procedure
CREATE PROCEDURE `Do_Logging`
(
IN Message varchar(50)
)
BEGIN
insert into logging_table (AuditText, timestamp )
values ( Message, now() );
END
Example of creating a view
CREATE VIEW `Sample_View` AS
SELECT
m.title,
g.name
FROM
movies m
inner join
genres_movies gm on gm.movie_id = m.id
inner join
genres g on gm.genre_id = g.id
Tips & Tricks…
How to invoke an existing stored procedure (this assumes the required SP & tables exist).
select * from logging_table;
call Do_Logging (‘Today is Friday’);
select * from logging_table;
Compare two tables to find items that exist in one but not the other
select * from movies m where not exists
(
select * from some_movies sm where sm.id = m.id
)
Quickly copy a table
CREATE TABLE quick_method as select * from movies
Empty (without deleting a table OR writing to the transaction log)
truncate table quick_method