编程代考 A few bits of MySQL you may find useful – cscodehelp代写

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