mysql

How do you create a new mysql database?

CREATE DATABASE classroom_db;

How do you delete a row/record from a table?

DELETE FROM teachers WHERE id = 3;

How do you view a table, it's schema and contents?

DESCRIBE teachers;

What is a FOREIGN KEY and how do you use it?

FOREIGN KEY (student_id) REFERENCES teachers(id), You can add a foreign key when creating a table. You would do this if you want to link with a specific column from a different table. For example if you have a column in Students, called teacher_id and you want to associate it to the id column in the teachers table.

How do you add new rows/records to your table?

INSERT INTO teachers (teachers, subjects) VALUES ('pavan', 'mysql'), ('manny', 'node'), ('mark', 'javascript');

How do you view a specific record/s in your table, for example the 3rd record, or all records that meet a certain criteria?

SELECT * FROM teachers WHERE id = 3; SELECT * FROM teachers WHERE subject IN ('node', 'mysql');

How do you view all the record in your table?

SELECT * FROM teachers;

How do you count the number of records that meet a certain criteria?

SELECT Count(*) AS num_students, teacher_id FROM students GROUP BY teacher_id;

How do you view records using data across multiple tables?

SELECT t.name AS teacher_name, Count(s.id) AS num_students, t.id AS teacher_id FROM students s RIGHT JOIN teacher t ON s.teacher_id = t.id GROUP BY t.id;

How do you view all of your databases?

SHOW DATABASES;

How do you update a record in your table?

UPDATE teachers SET subject = 'node' WHERE id = 1;

How do you create a new table?

USE classroom_db; CREATE TABLE IF NOT EXISTS teachers ( id INT AUTO_INCREMENT, name VARCHAR(255) NOT NULL, subject VARCHAR(255) NOT NULL, PRIMARY KEY (id) );

What does EXPLAIN do and how do you use it?

You can add EXPLAIN to the front of any query and it will explain the query, but also show how long it takes to run the query. This is particularly useful if you are trying to optimize your search query.