MySQL Basic

MySQL Create Database

A MySQL Server can have one or many databases. You can create a different database for a different application, or you can share a database for multiple applications.

Login to MySQL Server (-uuser -ppassword): 
mysql -uroot -pcaojiang 

Create Database:
CREATE DATABASE IF NOT EXISTS databasename

---

sophal@ubuntu:~/system/tea/ss$ mysql -uroot -pcaojiang
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1286
Server version: 5.1.54-1ubuntu4 (Ubuntu)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE IF NOT EXISTS myw3cyberlearningDatabase;
Query OK, 1 row affected (0.00 sec)

Display All Databases:

mysql> SHOW Databases;
+---------------------------+
| Database                  |
+---------------------------+
| information_schema        |
| moodle                    |
| mysql                     |
| myw3cyberlearningDatabase |
| phpmyadmin                |
| w3cyberlearningwiki_chao  |
+---------------------------+
12 rows in set (0.00 sec)

MySQL Delete Database

When you delete the database, everything within that database is also deleted.

mysql> drop database myw3cyberlearningDatabase;
Query OK, 0 rows affected (0.26 sec)

MySQL Create Table

• Create a new database called mystore
• Create a table called customer
• Use the DESC keyword to list the detail about the table

mysql> CREATE DATABASE mystore;
Query OK, 1 row affected (0.00 sec)

mysql> USE mystore;
Database changed
mysql> CREATE TABLE customer(
    -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> name VARCHAR(20),
    -> age INT,
    -> email VARCHAR(50)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> DESC customer;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
| email | varchar(50) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

• INT: the INTEGER TYPE
• NOT NULL: Do not allow Null Value
• AUTO_INCREMENT: When you insert record, the table column id will automatically increment
• VARCHAR(n): for string input, the n can be 1 to 255

MySQL Delete Table

Delete a table is easy! Here how to do it!

mysql> DROP TABLE customer;
Query OK, 0 rows affected (0.00 sec)

MySQL INSERT

Insert a new record using the INSERT keyword

• Create a new table
• Insert a record
• When a table is AUTO_INCREMENT, you can use null to insert to the table.

The null value will automatically recognize by the insert statement, and it will automatically insert the correct order number according to the previous value. ie. if the current value is 1, the next value is 2 and so on and on.

mysql> CREATE TABLE customer
    -> ( 
    -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    -> name VARCHAR(20), 
    -> age INT, 
    -> email VARCHAR(50) );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO customer(id,name, age, email) 
    -> VALUES(null,'Bob',53,'[email protected]');
Query OK, 1 row affected (0.00 sec)

MySQL Insert Multiple Records at once

You also can insert multiple records to a table.

mysql> INSERT INTO customer(id,name, age, email) 
    -> VALUES(null,'John',53,'[email protected]'),
    -> (null,'Mark',30,'[email protected]'),
    -> (null,'Janny',40,'[email protected]');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

Create Table From Another Table

• Create a new table by copying from the other table.
• While you duplicate one table from another, all the values/records from the original table will also copy into the newly created table.
• We create myclient table from the customer table.

mysql> CREATE TABLE myclient AS (SELECT * FROM customer);
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> select * from myclient;
+----+-------+------+---------------------------+
| id | name  | age  | email                     |
+----+-------+------+---------------------------+
|  1 | Bob   |   53 | [email protected]         |
|  2 | John  |   53 | [email protected]            |
|  3 | Mark  |   30 | [email protected] |
|  4 | Janny |   40 | [email protected]           |
+----+-------+------+---------------------------+
4 rows in set (0.00 sec)

Create Table from another table without the record or value

You just want the empty table, and not the records.

mysql> CREATE TABLE myboss AS (SELECT * FROM myclient WHERE 1=2);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL Select or Query

Here how we query a table.

mysql> select * from customer;
+----+-------+------+---------------------------+
| id | name  | age  | email                     |
+----+-------+------+---------------------------+
|  1 | Bob   |   53 | [email protected]         |
|  2 | John  |   53 | [email protected]            |
|  3 | Mark  |   30 | [email protected] |
|  4 | Janny |   40 | [email protected]           |
+----+-------+------+---------------------------+
4 rows in set (0.00 sec)

Insert Record From another Table

• Mark and Janny records are in the customer table
• You would like to insert them into the myboss table.

mysql> SELECT * FROM myboss;
Empty set (0.00 sec)

mysql> INSERT INTO myboss  SELECT id, name, age, email FROM customer WHERE id IN(4,3);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MySQL Replace

• REPLACE is similar to INSERT. We can use REPLACE to insert any record.
• we use the MySQL function called password(value)
• The password(value) uses to encrypt the password

mysql> use w3cyberlearnings;
Database changed
mysql> CREATE TABLE user(
    -> user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> login_email VARCHAR(150) NOT NULL,
    -> login_password VARCHAR(150) NOT NULL);
Query OK, 0 rows affected (0.04 sec)

mysql> REPLACE INTO user 
    -> SET login_email='[email protected]', 
    -> login_password=password('itssecretpassword');
Query OK, 1 row affected (0.00 sec)

Using Replace to insert record from another table

In this demonstration, we create another table called profile. We use the REPLACE to insert record from the user table to the profile table.

mysql> SELECT * FROM user;
+---------+----------------------+-------------------------------------------+
| user_id | login_email          | login_password                            |
+---------+----------------------+-------------------------------------------+
|       1 | [email protected] | *B1D3B793FBE8077ECA2A05557EF4E88CDA45009C |
+---------+----------------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE profile
    -> (id int not null auto_increment primary key,
    -> email varchar(200) not null,
    -> password varchar(200) not null);
Query OK, 0 rows affected (0.08 sec)

mysql> REPLACE INTO profile(email,password) 
    -> SELECT login_email,login_password FROM user WHERE user_id=1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

MySQL Update

How do you update a record? Here we will show you how to update a table record. We update a new password for the row that has the email address as [email protected].

mysql> SELECT * FROM profile;
+----+----------------------+-------------------------------------------+
| id | email                | password                                  |
+----+----------------------+-------------------------------------------+
|  1 | [email protected] | *B1D3B793FBE8077ECA2A05557EF4E88CDA45009C |
+----+----------------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> UPDATE profile SET password=password('myNewPassword') WHERE email='[email protected]';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MySQL Delete Record

We want to delete some record in the table.

• Insert some sample records
• Query the table to see all the records
• Run the DELETE statement to delete a record which has email address [email protected].
• Query the table again to see the result

mysql> INSERT into profile(email,password) 
   -> values('[email protected]',password('pass1')),('[email protected]',password('password2'));
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * from profile;
+----+----------------------+-------------------------------------------+
| id | email                | password                                  |
+----+----------------------+-------------------------------------------+
|  1 | [email protected] | *12700A347D4EFCCBA4518223F07D4B6145DCBBF0 |
|  2 | [email protected]      | *22A99BA288DB55E8E230679259740873101CD636 |
|  3 | [email protected]      | *DC52755F3C09F5923046BD42AFA76BD1D80DF2E9 |
+----+----------------------+-------------------------------------------+
3 rows in set (0.00 sec)

mysql> DELETE FROM profile WHERE email='[email protected]';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM profile;
+----+----------------------+-------------------------------------------+
| id | email                | password                                  |
+----+----------------------+-------------------------------------------+
|  1 | [email protected] | *12700A347D4EFCCBA4518223F07D4B6145DCBBF0 |
|  2 | [email protected]      | *22A99BA288DB55E8E230679259740873101CD636 |
+----+----------------------+-------------------------------------------+
2 rows in set (0.00 sec)

SELECT and WHERE Statement

• You can using the SELECT statement with the WHERE clause to filter record.
• In the WHERE clause is where you want the record to be filtered.

mysql> SELECT * FROM user;
+---------+----------------------+-------------------------------------------+
| user_id | login_email          | login_password                            |
+---------+----------------------+-------------------------------------------+
|       1 | [email protected] | *B1D3B793FBE8077ECA2A05557EF4E88CDA45009C |
+---------+----------------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM profile;
+----+----------------------+-------------------------------------------+
| id | email                | password                                  |
+----+----------------------+-------------------------------------------+
|  1 | [email protected] | *12700A347D4EFCCBA4518223F07D4B6145DCBBF0 |
|  2 | [email protected]      | *22A99BA288DB55E8E230679259740873101CD636 |
+----+----------------------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT u.* FROM user u, profile p WHERE u.login_email = p.email;
+---------+----------------------+-------------------------------------------+
| user_id | login_email          | login_password                            |
+---------+----------------------+-------------------------------------------+
|       1 | [email protected] | *B1D3B793FBE8077ECA2A05557EF4E88CDA45009C |
+---------+----------------------+-------------------------------------------+
1 row in set (0.00 sec)