jDTqVR9i

1/************************************/
2		SQL commands:
3/************************************/
40)Create database - example 1
5CREATE DATABASE unleashed;
6
71)Create database - example 2
8CREATE DATABASE `unleashed`;
9
102) Creating table
11CREATE TABLE `students` (`id` INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(10), `age` INT DEFAULT 0);
12
133) Creating table if not already exists
14CREATE TABLE IF NOT EXISTS `students` (`id` INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(10), `age` INT DEFAULT 0);
15
164) Creating table on specific database
17CREATE TABLE `phases`.`students` (`id` INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(10), `age` INT DEFAULT 0);
18
195) Insert row into table
20INSERT INTO `students` (`id`, `name`, `age`) VALUES (NULL, 'Almog', 21);
21
226) Insert row into table
23INSERT INTO students (name, age) VALUES ('Tom', 21);
24
257) Insert row into table
26INSERT INTO students (name) VALUES ('Stav');
27
288) Insert multiple rows into table
29INSERT INTO students (name, age) VALUES ('Mahiash', 23), ('Tomer', 21), ('Shenhav', 21);
30
319) Update data in table
32UPDATE students SET age = 20;
33
3410) Update data in table
35UPDATE students SET name = 'Mashiah' WHERE id = 6 AND name = 'Mahiash';
36
3711) Update data in table
38UPDATE students SET age = 22 WHERE id > 3 AND id < 6;
39
4012) Update data in table
41UPDATE students SET age = 24 WHERE id BETWEEN 4 AND 7;
42
4313) Alter table - add column
44ALTER TABLE teachers ADD COLUMN (age INT(3) DEFAULT 18);
45
4614) Alter table - index column name
47ALTER TABLE teachers ADD INDEX (name);
48
4915) Select VIRTUAL data (created on the fly)
50SELECT 'Hello World';
51
5216) Select VIRTUAL data with alias
53SELECT 'Tom Barzilay amelech' AS `tom_data`;
54
5517) Select names from table teachers
56SELECT name FROM teachers;
57
5818) Select names from table teachers with alias
59SELECT name AS teacher_name FROM teachers;
60
6119) select multiple fields from teachers	
62SELECT name, email FROM teachers;
63
6420) concatenate (on the fly) some data and use with alias
65SELECT CONCAT(name, ' - ',email) AS my_data FROM teachers;
66
6721) calculate (on the fly) some data and use with alias
68SELECT name, (price * quantity) AS total_price FROM products;
69
7022) Implicit JOIN tables (Old school)
71SELECT teachers.name AS teacher_name, courses.name AS course_name FROM teachers, courses WHERE courses.teacher = teachers.id;
72
7323) Explicit JOIN tables (Recommended)
74SELECT teachers.name AS teacher_name, courses.name AS course_name FROM teachers INNER JOIN courses ON courses.teacher = teachers.id;
75
7624) Explicit LEFT\RIGHT JOIN tables
77SELECT teachers.name AS teacher_name, courses.name AS course_name FROM teachers LEFT JOIN courses ON courses.teacher = teachers.id;
78
7925) UNION selects - stored data + created on the fly
80SELECT name FROM teachers UNION SELECT 'Zeev';
81
8226) UNION joins - to create FULL OUTER JOIN
83SELECT teachers.name AS teacher_name, courses.name AS course_name FROM teachers LEFT JOIN courses ON courses.teacher = teachers.id 
84UNION
85SELECT teachers.name AS teacher_name, courses.name AS course_name FROM teachers RIGHT JOIN courses ON courses.teacher = teachers.id 
86
8727) Check existing databases
88SHOW DATABASES
89
9028) Switch to database
91USE `unleashed`
92
9329) Check existing tables in database
94SHOW TABLES
95
9630) Check structure of a table
97DESC `users`
98
9931) Table deletion
100DROP TABLE users;
101
10231) Table deletion - conditional
103DROP TABLE IF EXISTS users;
104
10532) Data deletion
106DELETE FROM users;
107
10833) Data deletion with WHERE clause
109DELETE FROM users WHERE id = 2;