MYSQL Tutorial
Creating a database in MySQL
CREATE DATABASE mydb;Return value:
Query OK, 1 row affected (0.05 sec)
Using the created database
mydbUSE mydb;Return value:
Database Changed
Creating a table in MySQL
CREATE TABLE mytable
(
id int unsigned NOT NULL auto_increment,
username varchar(100) NOT NULL,
email varchar(100) NOT NULL,
PRIMARY KEY (id)
);CREATE TABLE mytable will create a new table called mytable.
id int unsigned NOT NULL auto_increment creates the id column, this type of field will assign a unique numeric ID to each record in the table (meaning that no two rows can have the same id in this case), MySQL will automatically assign a new, unique value to the record's id field (starting with 1).
Return value:
Query OK, 0 rows affected (0.10 sec)
Inserting a row into a MySQL table
INSERT INTO mytable ( username, email )
VALUES ( "myuser", "myuser@example.com" );Example return value:
Query OK, 1 row affected (0.06 sec)
The varchar a.k.a strings can be also be inserted using single quotes:
INSERT INTO mytable ( username, email )
VALUES ( 'username', 'username@example.com' );Updating a row into a MySQL table
UPDATE mytable SET username="myuser" WHERE id=8Example return value:
Query OK, 1 row affected (0.06 sec)
The int value can be inserted in a query without quotes. Strings and Dates must be enclosed in single quote ' or double quotes ".
Deleting a row into a MySQL table
DELETE FROM mytable WHERE id=8Example return value:
Query OK, 1 row affected (0.06 sec)
This will delete the row having id is 8.
Selecting rows based on conditions in MySQL
SELECT * FROM mytable WHERE username = "myuser";Return value:
+----+----------+---------------------+
| id | username | email |
+----+----------+---------------------+
| 1 | myuser | myuser@example.com |
+----+----------+---------------------+1 row in set (0.00 sec)
Show list of existing databases
SHOW databases;Return value:
+-------------------+
| Databases |
+-------------------+
| information_schema|
| mydb |
+-------------------+2 rows in set (0.00 sec)
You can think of "information_schema" as a "master database" that provides access to database metadata.
Show tables in an existing database
SHOW tables;Return value:
+----------------+
| Tables_in_mydb |
+----------------+
| mytable |
+----------------+1 row in set (0.00 sec)
Show all the fields of a table
DESCRIBE databaseName.tableName;or, if already using a database:
DESCRIBE tableName;Return value:
+-----------+----------------+--------+---------+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------+--------+---------+-------------------+-------+
| fieldname | fieldvaluetype | NO/YES | keytype | defaultfieldvalue | |
+-----------+----------------+--------+---------+-------------------+-------+