Daily Dairy

A Basic MySQL Tutorial.

About MySQL.

MySQL is an open source database management software that helps users store, organize, and retrieve data. It is a very powerful program with a lot of flexibility—this tutorial will provide the simplest introduction to MySQL.

How to Install MySQL on Ubuntu.

$ sudo apt-get install mysql-server

How to Access the MySQL shell.

Once you have MySQL installed on your system, you can access the MySQL shell by typing the following command into terminal:

$ mysql -u <username>  -p<password>

List of all the current databases for the user.

show databases;

Example:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| newuser            |
| performance_schema |
| phpmyadmin         |
+--------------------+
5 rows in set (0.00 sec)

To use a particular database use the following command:

use <databasename>;

Example:

mysql> use newuser;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

To create a table:

create table <tablename> (<columnname> <datatype> (size), <columnname> <datatype> (size));

Example:

mysql> create table students (id INT PRIMARY KEY AUTO_INCREMENT, name varchar(20), class varchar(20));
ERROR 1050 (42S01): Table 'students' already exists

To see the structure of the table:

describe <tablename>;

Example:

mysql> describe students;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| class | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.26 sec)

To insert values into table:

insert into <tablename> values ("<>", "<>", "<>");

Example:

mysql> insert into students values (NULL, "Jagjeet", "D3IT");
Query OK, 1 row affected (0.12 sec)

To see values in a table:

select * from <tablename>;

Example:

mysql> select * from students;
+----+---------+-------+
| id | name    | class |
+----+---------+-------+
|  1 | Jagjeet | D3IT  |
+----+---------+-------+
1 row in set (0.08 sec)

To update any value:

update <tablename> set value="<>" where value="<>";

Example:

mysql> update students set name="harpreet" where name="Jagjeet";
Query OK, 1 row affected (0.14 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from students;
+----+----------+-------+
| id | name     | class |
+----+----------+-------+
|  1 | harpreet | D3IT  |
+----+----------+-------+
1 row in set (0.00 sec)

To add new column:

alter table <tablename> add <columnname> <datatype>(size);

Example:

mysql> alter table students add email varchar(20);
Query OK, 1 row affected (0.75 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from students;
+----+----------+-------+-------+
| id | name | class | email |
+----+----------+-------+-------+
| 1 | harpreet | D3IT | NULL |
+----+----------+-------+-------+
1 row in set (0.00 sec)

Similarly to delete a column:

alter table <tablename> drop <columnname>;

Example:

mysql> alter table students drop email;
Query OK, 1 row affected (0.32 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from students;
+----+----------+-------+
| id | name     | class |
+----+----------+-------+
|  1 | harpreet | D3IT  |
+----+----------+-------+
1 row in set (0.00 sec)

To delete a row of value:

delete from <tablename> where value="<>";

Example:

mysql> delete from students where name="harpreet";
Query OK, 1 row affected (0.13 sec)

mysql> select * from students;
Empty set (0.00 sec)

Thank’s.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s