Skip to content

Instantly share code, notes, and snippets.

@maheshj01

maheshj01/mysql.md

Last active March 2, 2023 20:30
Show Gist options
  • Save maheshj01/d80f614ce1a4c3071f4433aef26c3c2c to your computer and use it in GitHub Desktop.
Save maheshj01/d80f614ce1a4c3071f4433aef26c3c2c to your computer and use it in GitHub Desktop.
Download ZIP
my sql workbench quick notes
Raw
mysql.md

MYSQL Workbench (2.0.31) docs.

Contents

1. Configuration

2. Chore Queries

Modify column Type

ALTER Table events
MODIFY COLUMN description VARCHAR(100)

Choose a existing database

USE <database name>;

3. Schema Creation

  1. MySQL SERVER must be installed and running before starting my sqlworkbench
  2. Create a ER Diagram.
  3. Generate SQL script using forward engineering
  4. Under Home -> Go to mysql conections to open sql editor and run the queries to check if the physical schema is created.

4. Data Populate

-- Populate users
INSERT INTO user (user_id, email, avatar_url, isAdmin, username, created_at)
VALUES
('1', 'johnsmith@example.com', 'http://example.com/avatar1.jpg', 1, 'johnsmith', NOW()),
('2', 'janedoe@example.com', 'http://example.com/avatar2.jpg', 0, 'janedoe', NOW()),
('3', 'davidsanchez@example.com', 'http://example.com/avatar3.jpg', 0, 'davidsanchez', NOW()),
('4', 'mariaperez@example.com', 'http://example.com/avatar4.jpg', 0, 'mariaperez', NOW()),
('5', 'markjohnson@example.com', 'http://example.com/avatar5.jpg', 0, 'markjohnson', NOW()),
('6', 'sarahlee@example.com', 'http://example.com/avatar6.jpg', 0, 'sarahlee', NOW()),
('7', 'brianharris@example.com', 'http://example.com/avatar7.jpg', 0, 'brianharris', NOW()),
('8', 'jessicawang@example.com', 'http://example.com/avatar8.jpg', 0, 'jessicawang', NOW()),
('9', 'andrewnguyen@example.com', 'http://example.com/avatar9.jpg', 0, 'andrewnguyen', NOW()),
('10', 'emilytaylor@example.com', 'http://example.com/avatar10.jpg', 0, 'emilytaylor', NOW()),
('11', 'jamesbrown@example.com', 'https://i.pravatar.cc/150?img=11', 0, 'jamesbrown', NOW()),
('12', 'ashleywilson@example.com', 'https://i.pravatar.cc/150?img=12', 0, 'ashleywilson', NOW()),
('13', 'samueljackson@example.com', 'https://i.pravatar.cc/150?img=13', 0, 'samueljackson', NOW()),
('14', 'lauradavis@example.com', 'https://i.pravatar.cc/150?img=14', 0, 'lauradavis', NOW()),
('15', 'robertmorris@example.com', 'https://i.pravatar.cc/150?img=15', 0, 'robertmorris', NOW()),
('16', 'dianeparker@example.com', 'https://i.pravatar.cc/150?img=16', 0, 'dianeparker', NOW()),
('17', 'kevinsmith@example.com', 'https://i.pravatar.cc/150?img=17', 0, 'kevinsmith', NOW()),
('18', 'kellygreen@example.com', 'https://i.pravatar.cc/150?img=18', 0, 'kellygreen', NOW()),
('19', 'williamturner@example.com', 'https://i.pravatar.cc/150?img=19', 0, 'williamturner', NOW()),
('20', 'jessicamiller@example.com', 'https://i.pravatar.cc/150?img=20', 0, 'jessicamiller', NOW());

-- Populate events
INSERT INTO `events` (`id`, `created_at`, `name`, `description`, `starts_at`, `ends_at`, `cover_image`, `address`, `private`, `deleted`, `user_id`)
VALUES
  ('11', NOW(), 'TED2023 Conference', 'TED is a nonprofit devoted to spreading ideas, usually in the form of short, powerful talks.', NOW() + INTERVAL 1 MONTH, NOW() + INTERVAL 1 MONTH + INTERVAL 3 DAY, 'https://ted.com/talks/cover.jpg', 'Vancouver Convention Centre, Vancouver, Canada', 0, 0, '2'),
  ('12', NOW(), 'Web Summit 2023', 'Web Summit brings together the people and companies redefining the global tech industry.', NOW() + INTERVAL 2 MONTH, NOW() + INTERVAL 2 MONTH + INTERVAL 4 DAY, 'https://websummit.com/cover.jpg', 'Altice Arena, Lisbon, Portugal', 0, 0, '3'),
  ('13', NOW(), 'Comic Con 2023', 'Comic Con is a multi-genre entertainment and comic convention.', NOW() + INTERVAL 3 MONTH, NOW() + INTERVAL 3 MONTH + INTERVAL 3 DAY, 'https://comiccon.com/cover.jpg', 'San Diego Convention Center, San Diego, USA', 0, 0, '14'),
  ('14', NOW(), 'New York Fashion Week', 'New York Fashion Week is a fashion event held twice a year in New York City.', NOW() + INTERVAL 4 MONTH, NOW() + INTERVAL 4 MONTH + INTERVAL 5 DAY, 'https://nyfw.com/cover.jpg', 'Skylight Clarkson Sq, New York City, USA', 0, 0, '15'),
  ('15', NOW(), 'Cannes Film Festival 2023', 'Cannes Film Festival is an annual film festival held in Cannes, France.', NOW() + INTERVAL 5 MONTH, NOW() + INTERVAL 5 MONTH + INTERVAL 7 DAY, 'https://cannesfilmfestival.com/cover.jpg', 'Palais des Festivals et des Congrès, Cannes, France', 0, 0, '16'),
  ('16', NOW(), 'Coachella 2023', 'Coachella is an annual music and arts festival held in California.', NOW() + INTERVAL 6 MONTH, NOW() + INTERVAL 6 MONTH + INTERVAL 4 DAY, 'https://coachella.com/cover.jpg', 'Empire Polo Club, Indio, California, USA', 0, 0, '17'),
  ('17', NOW(), 'Art Basel 2023', 'Art Basel is an international art fair held annually in Basel, Switzerland.', NOW() + INTERVAL 7 MONTH, NOW() + INTERVAL 7 MONTH + INTERVAL 6 DAY, 'https://artbasel.com/cover.jpg', 'Messe Basel, Basel, Switzerland', 0, 0, '18'),
  ('18', NOW(), 'South by Southwest (SXSW) 2023', 'SXSW is an annual conglomerate of film, interactive media, and music festivals.', NOW() + INTERVAL 8 MONTH, NOW() + INTERVAL 8 MONTH + INTERVAL 7 DAY, 'https://sxsw.com/cover.jpg', 'Austin Convention Center, Austin, Texas, USA', 0, 0, '19'),
  ('19', NOW(), 'Paris Fashion Week 2023', 'Paris Fashion Week is a fashion event held biannually in Paris, France.', NOW() + INTERVAL 9 MONTH, NOW() +  INTERVAL 9 MONTH + INTERVAL 8 DAY, 'https://sxsw.com/cover.jpg', 'Texas Convention Center, Texas, USA', 0, 0, '1'); 

-- Populate attendees
INSERT INTO attendees (id, event_id, user_id)
VALUES 
(1, 11, 1),
(2, 11, 2),
(3, 11, 3),
(11, 12, 2),
(16, 12, 10),
(17, 13, 11),
(18, 13, 12),
(19, 13, 13),
(20, 13, 14),
(21, 14, 15),
(22, 14, 16),
(23, 14, 17),
(24, 15, 18),
(25, 15, 19),
(26, 16, 20),
(27, 16, 19),
(28, 16, 18),
(29, 16, 17),
(30, 16, 16);

-- Populate tags
INSERT INTO tags (tag_id, tag_name)
VALUES 
    (1, 'Technology'),
    (2, 'Innovation'),
    (3, 'Science'),
    (4, 'Art'),
    (5, 'Design'),
    (6, 'Fashion'),
    (7, 'Music'),
    (8, 'Film'),
    (9, 'Entertainment');

-- Populate event_tag_map 
INSERT INTO `event_tag_map` (`event_id`, `tag_id`, `map_id`)
VALUES
  ('11', '1', UUID()),
  ('11', '2', UUID()),
  ('13', '4', UUID()),
  ('14', '5', UUID()),
  ('15', '4', UUID()),
  ('15', '7', UUID()),
  ('16', '5', UUID()),
  ('16', '6', UUID()),
  ('17', '7', UUID()),
  ('17', '8', UUID()),
  ('18', '8', UUID()),
  ('18', '9', UUID()),
  ('19', '4', UUID()),
  ('19', '7', UUID()),
  ('19', '9', UUID());
  
-- Populate bookmarks 
INSERT INTO `bookmarks` (`id`, `created_at`, `event_id`, `user_id`)
VALUES 
    (UUID(), NOW(), '12', '16'),
    (UUID(), NOW(), '14', '15'),
    (UUID(), NOW(), '16', '14'),
    (UUID(), NOW(), '19', '3'),
    (UUID(), NOW(), '19', '1'),
    (UUID(), NOW(), '13', '12'),
    (UUID(), NOW(), '12', '2');

5. Procedures

1. Add a new user

CREATE PROCEDURE `insert_user` (
    IN p_user_id VARCHAR(128),
    IN p_email VARCHAR(128),
    IN p_avatar_url VARCHAR(256),
    IN p_isAdmin BOOLEAN,
    IN p_username VARCHAR(128)
)
BEGIN
    INSERT INTO user (user_id, email, avatar_url, isAdmin, username, created_at)
    VALUES (p_user_id, p_email, p_avatar_url, p_isAdmin, p_username, NOW());
END

2. Load dummy data

CREATE PROCEDURE `loadData`()
BEGIN

-- Insert users
INSERT INTO user (user_id, email, avatar_url, isAdmin, username, created_at)
VALUES
  ('1', 'johnsmith@example.com', 'http://example.com/avatar1.jpg', 1, 'johnsmith', NOW()),
  ('2', 'janedoe@example.com', 'http://example.com/avatar2.jpg', 0, 'janedoe', NOW()),
  ('3', 'davidsanchez@example.com', 'http://example.com/avatar3.jpg', 0, 'davidsanchez', NOW()),
  ('4', 'mariaperez@example.com', 'http://example.com/avatar4.jpg', 0, 'mariaperez', NOW()),
  ('5', 'markjohnson@example.com', 'http://example.com/avatar5.jpg', 0, 'markjohnson', NOW()),
  ('6', 'sarahlee@example.com', 'http://example.com/avatar6.jpg', 0, 'sarahlee', NOW()),
  ('7', 'brianharris@example.com', 'http://example.com/avatar7.jpg', 0, 'brianharris', NOW()),
  ('8', 'jessicawang@example.com', 'http://example.com/avatar8.jpg', 0, 'jessicawang', NOW()),
  ('9', 'andrewnguyen@example.com', 'http://example.com/avatar9.jpg', 0, 'andrewnguyen', NOW()),
  ('10', 'emilytaylor@example.com', 'http://example.com/avatar10.jpg', 0, 'emilytaylor', NOW()),
  ('11', 'jamesbrown@example.com', 'https://i.pravatar.cc/150?img=11', 0, 'jamesbrown', NOW()),
  ('12', 'ashleywilson@example.com', 'https://i.pravatar.cc/150?img=12', 0, 'ashleywilson', NOW()),
  ('13', 'samueljackson@example.com', 'https://i.pravatar.cc/150?img=13', 0, 'samueljackson', NOW()),
  ('14', 'lauradavis@example.com', 'https://i.pravatar.cc/150?img=14', 0, 'lauradavis', NOW()),
  ('15', 'robertmorris@example.com', 'https://i.pravatar.cc/150?img=15', 0, 'robertmorris', NOW()),
  ('16', 'dianeparker@example.com', 'https://i.pravatar.cc/150?img=16', 0, 'dianeparker', NOW()),
  ('17', 'kevinsmith@example.com', 'https://i.pravatar.cc/150?img=17', 0, 'kevinsmith', NOW()),
  ('18', 'kellygreen@example.com', 'https://i.pravatar.cc/150?img=18', 0, 'kellygreen', NOW()),
  ('19', 'williamturner@example.com', 'https://i.pravatar.cc/150?img=19', 0, 'williamturner', NOW()),
  ('20', 'jessicamiller@example.com', 'https://i.pravatar.cc/150?img=20', 0, 'jessicamiller', NOW());
  
-- Insert events
INSERT INTO `events` (`id`, `created_at`, `name`, `description`, `starts_at`, `ends_at`, `cover_image`, `address`, `private`, `deleted`, `user_id`)
VALUES
  ('11', NOW(), 'TED2023 Conference', 'TED is a nonprofit devoted to spreading ideas, usually in the form of short, powerful talks.', NOW() + INTERVAL 1 MONTH, NOW() + INTERVAL 1 MONTH + INTERVAL 3 DAY, 'https://ted.com/talks/cover.jpg', 'Vancouver Convention Centre, Vancouver, Canada', 0, 0, '2'),
  ('12', NOW(), 'Web Summit 2023', 'Web Summit brings together the people and companies redefining the global tech industry.', NOW() + INTERVAL 2 MONTH, NOW() + INTERVAL 2 MONTH + INTERVAL 4 DAY, 'https://websummit.com/cover.jpg', 'Altice Arena, Lisbon, Portugal', 0, 0, '3'),
  ('13', NOW(), 'Comic Con 2023', 'Comic Con is a multi-genre entertainment and comic convention.', NOW() + INTERVAL 3 MONTH, NOW() + INTERVAL 3 MONTH + INTERVAL 3 DAY, 'https://comiccon.com/cover.jpg', 'San Diego Convention Center, San Diego, USA', 0, 0, '14'),
  ('14', NOW(), 'New York Fashion Week', 'New York Fashion Week is a fashion event held twice a year in New York City.', NOW() + INTERVAL 4 MONTH, NOW() + INTERVAL 4 MONTH + INTERVAL 5 DAY, 'https://nyfw.com/cover.jpg', 'Skylight Clarkson Sq, New York City, USA', 0, 0, '15'),
  ('15', NOW(), 'Cannes Film Festival 2023', 'Cannes Film Festival is an annual film festival held in Cannes, France.', NOW() + INTERVAL 5 MONTH, NOW() + INTERVAL 5 MONTH + INTERVAL 7 DAY, 'https://cannesfilmfestival.com/cover.jpg', 'Palais des Festivals et des Congrès, Cannes, France', 0, 0, '16'),
  ('16', NOW(), 'Coachella 2023', 'Coachella is an annual music and arts festival held in California.', NOW() + INTERVAL 6 MONTH, NOW() + INTERVAL 6 MONTH + INTERVAL 4 DAY, 'https://coachella.com/cover.jpg', 'Empire Polo Club, Indio, California, USA', 0, 0, '17'),
  ('17', NOW(), 'Art Basel 2023', 'Art Basel is an international art fair held annually in Basel, Switzerland.', NOW() + INTERVAL 7 MONTH, NOW() + INTERVAL 7 MONTH + INTERVAL 6 DAY, 'https://artbasel.com/cover.jpg', 'Messe Basel, Basel, Switzerland', 0, 0, '18'),
  ('18', NOW(), 'South by Southwest (SXSW) 2023', 'SXSW is an annual conglomerate of film, interactive media, and music festivals.', NOW() + INTERVAL 8 MONTH, NOW() + INTERVAL 8 MONTH + INTERVAL 7 DAY, 'https://sxsw.com/cover.jpg', 'Austin Convention Center, Austin, Texas, USA', 0, 0, '19'),
  ('19', NOW(), 'Paris Fashion Week 2023', 'Paris Fashion Week is a fashion event held biannually in Paris, France.', NOW() + INTERVAL 9 MONTH, NOW() +  INTERVAL 9 MONTH + INTERVAL 8 DAY, 'https://sxsw.com/cover.jpg', 'Texas Convention Center, Texas, USA', 0, 0, '1'); 

-- Insert attendees
INSERT INTO attendees (id, event_id, user_id)
VALUES 
(1, 11, 1),
(2, 11, 2),
(3, 11, 3),
(11, 12, 2),
(16, 12, 10),
(17, 13, 11),
(18, 13, 12),
(19, 13, 13),
(20, 13, 14),
(21, 14, 15),
(22, 14, 16),
(23, 14, 17),
(24, 15, 18),
(25, 15, 19),
(26, 16, 20),
(27, 16, 19),
(28, 16, 18),
(29, 16, 17),
(30, 16, 16);

-- Insert tags
INSERT INTO tags (tag_id, tag_name)
VALUES 
    (1, 'Technology'),
    (2, 'Innovation'),
    (3, 'Science'),
    (4, 'Art'),
    (5, 'Design'),
    (6, 'Fashion'),
    (7, 'Music'),
    (8, 'Film'),
    (9, 'Entertainment');
    
-- Insert event_tag_map
INSERT INTO `event_tag_map` (`event_id`, `tag_id`, `map_id`)
VALUES
  ('11', '1', UUID()),
  ('11', '2', UUID()),
  ('3', '4', UUID()),
  ('4', '5', UUID()),
  ('5', '4', UUID()),
  ('5', '7', UUID()),
  ('6', '5', UUID()),
  ('6', '6', UUID()),
  ('7', '7', UUID()),
  ('7', '8', UUID()),
  ('8', '8', UUID()),
  ('8', '9', UUID()),
  ('9', '4', UUID()),
  ('9', '7', UUID()),
  ('9', '9', UUID());

INSERT INTO `bookmarks` (`id`, `created_at`, `event_id`, `user_id`)
VALUES
    (UUID(), NOW(), '12', '16'),
    (UUID(), NOW(), '14', '15'),
    (UUID(), NOW(), '16', '14'),
    (UUID(), NOW(), '19', '3'),
    (UUID(), NOW(), '19', '1'),
    (UUID(), NOW(), '13', '12'),
    (UUID(), NOW(), '12', '2');
END

3. Delete all data

CREATE PROCEDURE `delete_all_data` ()
BEGIN
	SET FOREIGN_KEY_CHECKS=0;
	DELETE FROM events;
	DELETE FROM user;
	DELETE FROM attendees;
	DELETE FROM bookmarks;
	DELETE FROM leaderboard;
	DELETE FROM tags;
	DELETE FROM event_tag_map;
	DELETE FROM feedback;
	SET FOREIGN_KEY_CHECKS=1;
END

4. Get event attendees by event_id

CREATE PROCEDURE `get_event_attendees`(IN event_id varchar(128))
BEGIN
    SELECT `user`.*
    FROM attendees
    INNER JOIN events ON attendees.event_id = events.id
    INNER JOIN `user` ON attendees.user_id = `user`.user_id
    WHERE events.id = event_id;
END

Invoke using

CALL get_event_attendees(12);

5. Create a event

CREATE PROCEDURE create_event (
  IN id varchar(128),
  IN name VARCHAR(255),
  IN description TEXT,
  IN starts_at DATETIME,
  IN ends_at DATETIME,
  IN cover_image VARCHAR(255),
  IN address varchar(100),
  IN private tinyint(1),
  IN user_id varchar(128)
)
BEGIN
  INSERT INTO events (id, created_at, name, description, starts_at, ends_at, cover_image, address, private, deleted, user_id)
  VALUES (id, now(),name, description, starts_at, ends_at, cover_image, address, private, '0',user_id);
END

Invoke using

CALL create_event(21, 'abc', 'abc', now(), now(), 'aosbboa', 'New Bedford', 0, 18);

6. Register an attendee for a event.

CREATE PROCEDURE register_for_event (
  IN id	varchar(128),
  IN event_id varchar(128),
  IN user_id varchar(128)
)
 BEGIN
  INSERT INTO attendees (id, event_id, user_id) VALUES (id, event_id, user_id);
END

Invoke using

CALL get_event_attendees(12);

6. Triggers

1. Trigger for Event Deletion

DELIMITER $$
CREATE TRIGGER delete_attendees_on_cancel
AFTER UPDATE ON events
FOR EACH ROW
BEGIN
  IF NEW.deleted = 1 AND OLD.deleted = 0 THEN
    DELETE FROM attendees WHERE event_id = OLD.id;
  END IF;
END$$
DELIMITER ;
@maheshj01
Copy link
Author 

INSERT INTO user (user_id, email, avatar_url, isAdmin, username, created_at)
VALUES
('22', 'joerogan@example.com', 'http://example.com/avatar1.jpg', 1, 'joerogan', NOW());

@maheshj01
Copy link
Author 

SET SQL_SAFE_UPDATES=0;
SET FOREIGN_KEY_CHECKS=0;

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment