What is mysql

** Open source relational database managment system.
** Uses the sql (Structure Query Language).
** A leading database for web applications.
** Used for small apps to large enterprise apps.
** Used with multiple language (php, Node, Python, c#, etc).
** cross platform

MySQL Locations

• Mac /usr/local/mysql/bin
• Windows /Program Files/MySQL/MySQL version/bin
• Xampp /xampp/mysql/bin

---

Mysql Version

  mysql --version or MySQL -V

Login

  mysql -u root -p

Show Databases

  SHOW DATABASES

Create Database

  CREATE DATABASE databaseName;

Delete Database

  DROP DATABASE databaseName;

Select Database

  USE databaseName;

Create Table

  CREATE TABLE tableName(
  id INT AUTO_INCREMENT,
   first_name VARCHAR(100),
   last_name VARCHAR(100),
   email VARCHAR(50),
   password VARCHAR(20),
   location VARCHAR(100),
   dept VARCHAR(100),
   is_admin TINYINT(1),
   register_date DATETIME,
   PRIMARY KEY(id)
);

Show Tables

  SHOW TABLES;

Describe a Table

DESCRIBE tableName;

Update Row/Data

 UPDATE employee SET name ="Siam", department="Author", position="CEO" where id ='2';
 ** Here employee is table name

Delete Row

DELETE FROM employee WHERE id ='4';
** Here employee is table name

Delete / Drop Table

  DROP TABLE tablename;

Insert Row / Record

  INSERT INTO users (first_name, last_name, email, password, location, dept, is_admin, register_date) values ('Brad', 'Traversy', '[email protected]', '123456','Massachusetts', 'development', 1, now());

Insert Multiple Rows

INSERT INTO users (first_name, last_name, email, password, location, dept,  is_admin, register_date) values ('Fred', 'Smith', '[email protected]', '123456', 'New York', 'design', 0, now()), ('Sara', 'Watson', '[email protected]', '123456', 'New York', 'design', 0, now()),('Will', 'Jackson', '[email protected]', '123456', 'Rhode Island', 'development', 1, now()),('Paula', 'Johnson', '[email protected]', '123456', 'Massachusetts', 'sales', 0, now()),('Tom', 'Spears', '[email protected]', '123456', 'Massachusetts', 'sales', 0, now());

Exit

 exit; 

---

Mysql SELECT statement

The SELECT statement allows you to read data from one or more tables. To write a SELECT statement in MySQL, you follow this syntax:

 
 SELECT select_list
FROM table_name;

SELECT 
    lastname, firstname, jobtitle FROM employees;

---

MySQL ORDER BY clause

When you use the SELECT statement to query data from a table, the result set is not sorted. It means that the rows in the result set can be in any order.

To sort the result set, you add the ORDER BY clause to the SELECT statement. The following illustrates the syntax of the ORDER BY clause:

SELECT 
   select_list
FROM 
   table_name
ORDER BY 
   column1 [ASC|DESC], 
   column2 [ASC|DESC],
   ...;

 SELECT
	contactLastname,
	contactFirstname
FROM
	customers
ORDER BY
	contactLastname DESC,
	contactFirstname ASC;

SELECT 
    orderNumber, 
    orderlinenumber, 
    quantityOrdered * priceEach
FROM
    orderdetails
ORDER BY 
   quantityOrdered * priceEach DESC;

---

Mysql Where clause

The WHERE clause allows you to specify a search condition for the rows returned by a query. The following shows the syntax of the WHERE clause :

        SELECT 
    select_list
FROM
    table_name
WHERE
    search_condition;
    

The following query uses the WHERE clause to find all employees whose job titles are Sales Rep:

    SELECT 
    lastname, 
    firstname, 
    jobtitle
FROM
    employees
WHERE
    jobtitle = 'Sales Rep';

The following example uses the WHERE clause to find employees whose job titles are Sales Rep and office codes are 1:

    SELECT 
    lastname, 
    firstname, 
    jobtitle,
    officeCode
FROM
    employees
WHERE
    jobtitle = 'Sales Rep' AND 
    officeCode = 1;

The following query returns employees with office code less than or equal 4 (<=4):

    SELECT 
    lastname, 
    firstname, 
    officeCode
FROM
    employees
WHERE 
    officecode <= 4;

---

MySQL AND Operator

The AND operator is a logical operator that combines two or more Boolean expressions and returns true only if both expressions evaluate to true. The AND operator returns false if one of the two expressions evaluate to false.

The following statement uses the AND operator to find customers who locate in California (CA), USA:

 SELECT 
    customername, 
    country, 
    state
FROM
    customers
WHERE
    country = 'USA' AND state = 'CA';

By using the AND operator, you can combine more than two Boolean expressions. For example, the following query returns the customers who locate in California, USA, and have the credit limit greater than 100K.

 SELECT    
	customername, 
	country, 
	state, 
	creditlimit
FROM    
	customers
WHERE country = 'USA'
	AND state = 'CA'
	AND creditlimit > 100000;

---

MySQL OR Operator.

The MySQL OR operator combines two Boolean expressions and returns true when either condition is true.

For example, to get the customers who locate in the USA or France, you use the OR operator in the WHERE clause as follows:

 
SELECT    
	customername, 
	country
FROM    
	customers
WHERE country = 'USA' OR 
      country = 'France';

---

MySQL IN

The IN operator allows you to determine if a specified value matches any value in a set of values or returned by a subquery.

 SELECT 
    column1,column2,...
FROM
    table_name
WHERE 
	(expr|column_1) IN ('value1','value2',...);

If you want to find the offices that locate in the U.S. and France, you can use the IN operator as the following query:

 SELECT 
    officeCode, 
    city, 
    phone, 
    country
FROM
    offices
WHERE
    country IN ('USA' , 'France');

---

MySQL Trigger

In MySQL, a trigger is a stored program invoked automatically in response to an event such as insert, update, or delete that occurs in the associated table. For example, you can define a trigger that is invoked automatically before a new row is inserted into a table.

MySQL supports triggers that are invoked in response to the INSERT, UPDATE or DELETE event.

The SQL standard defines two types of triggers: row-level triggers and statement-level triggers.

** Row-level trigger is activated for each row that is inserted, updated, or deleted. For example, if a table has 100 rows inserted, updated, or deleted, the trigger is automatically invoked 100 times for the 100 rows affected.

** A statement-level trigger is executed once for each transaction regardless of how many rows are inserted, updated, or deleted.

CREATE TRIGGER trigger_name    
    (AFTER | BEFORE) (INSERT | UPDATE | DELETE)  
         ON table_name FOR EACH ROW    
         BEGIN    
        --variable declarations    
        --trigger code    
        END;     

DELIMITER $$
CREATE TRIGGER after_insert_employees
AFTER INSERT ON employees
FOR EACH  ROW
BEGIN
INSERT INTO employees_log VALUES('',NEW.employee_id,'create',now());
END$$
DELIMITER ;

DELIMITER //
CREATE TRIGGER after_insert_employee
AFTER INSERT ON employees
FOR EACH  ROW
BEGIN
INSERT INTO employees_log
SET employee_id = NEW.employee_id,
    i_event = 'INSERT',
    date_time = NOW();
END//
DELIMITER;

---

MySQL Stored Procedures

In this section, you will learn how to develop stored programs including stored procedures and stored functions in MySQL.

Here is the basic syntax of the CREATE PROCEDURE statement:

CREATE PROCEDURE procedure_name(parameter_list)
BEGIN
   statements;
END //

The following statement creates a new stored procedure that wraps the query:

DELIMITER //
CREATE PROCEDURE GetAllProducts()
BEGIN
SELECT *  FROM products;
END //
DELIMITER ;

See More...