MySQL Review

mysql_review.txt

SELECT * FROM `users`;

SELECT * FROM `users` WHERE id = 1;

SELECT * FROM `users` WHERE id > 1;

SELECT * FROM `users` WHERE id >= 1;

CREATE DATABASE <database_name>;

SHOW DATABASES;

CREATE TABLE <table_name> (
	id	INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	testing_name VARCHAR(255) NOT NULL,
	testing_email VARCHAR(255) NOT NULL,
	date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO <table_name> (`id`, `name`, `email`) VALUES (6, 'testuser', 'testemail@email'); 

ALTER TABLE `users` CHANGE COLUMN `digit` `price` char(100) NOT NULL;

ALTER TABLE `users` ADD COLUMN `testingcolumn` CHAR(10);

ALTER TABLE `users` DROP COLUMN `testingcolumn`;

RENAME TABLE `testing` TO `testingupdate`;

DROP TABLE `testingupdate`;

SELECT `name`, `email` FROM `users`;

SELECT * from `users` WHERE id = 1;

SELECT * FROM `users` WHERE email = 'aung@gmail.com';

SELECT * FROM `users` WHERE  id <> 2;

SELECT * FROM `users` WHERE id IN (1,3,4);

SELECT * FROM `users` WHERE id NOT IN (1,3,4);

SELECT COUNT(*) FROM `users`;

SELECT DISTINCT name FROM `users`;

SELECT name FROM `users` GROUP BY name;

SELECT MIN(`price`) FROM `users`;

SELECT MAX(`price`) FROM `users`;

SELECT SUM(`price`) FROM `users`;

SELECT AVG(`price`) FROM `users`;

SELECT * FROM `users` LIMIT 2;

SELECT * FROM `users` LIMIT 4,2;

SELECT * FROM `users` ORDER BY `id` DESC;

SELECT * FROM `users` ORDER BY `id` ASC;

SELECT * FROM `users` WHERE `name` LIKE '%aung%';

TRUNDATE TABLE `users`;

DELETE FROM `users` where id = 6;

UPDATE `users` SET name = 'agag' WHERE id = 1;


SELECT * FROM `users`
INNER JOIN `class`
ON users.id = class.user_id


SELECT * FROM `users`
LEFT JOIN `class`
ON users.id = class.user_id (select all records from left table)


SELECT * FROM `users`
RIGHT JOIN `class`
ON users.id = class.user_id (select all records from right table)


SELECT * FROM `users`
LEFT JOIN `class`
ON users.id = class.user_id
UNION
SELECT * FROM `users`
RIGHT JOIN `class`
ON users.id = class.user_id