MySQL Tutorial (part 1)

Posted: April 21, 2013 in Database, MySQL

Some commands in MySQL you need to be remembered:

1. Show version:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.12    |
+-----------+
1 row in set (0.03 sec)
mysql>

2. Create a new database:

mysql> create database demo;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| demo               |
| mysql              |
| performance_schema |
| test               |
| testdb             |
+--------------------+
6 rows in set (0.00 sec)
mysql>

3. Create a new table:

mysql> create table audience (Id int primary key auto_increment, Name varchar(100)) engine = InnoDB;
Query OK, 0 rows affected (0.16 sec)
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| audience         |
| authors          |
| books            |
| images           |
| testing          |
+------------------+
5 rows in set (0.00 sec)
mysql>

4. Show databases:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| testdb             |
+--------------------+
5 rows in set (0.44 sec)
mysql>

5. Show tables:

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| authors          |
| books            |
| images           |
| testing          |
+------------------+
4 rows in set (0.05 sec)
mysql>

6. Describe the structure of a table:

mysql> describe audience;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| Id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| Name  | varchar(100) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.05 sec)
mysql>

Other way:


mysql> show create table audience;
+----------+--------------------------------------------------------------------------------+
| Table    | Create Table                                                                   |
+----------+--------------------------------------------------------------------------------+
| audience | CREATE TABLE `audience` (`Id` int(11) NOT NULL AUTO_INCREMENT,`Name` varchar(100) DEFAULT NULL, PRIMARY KEY (`Id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1         |
+----------+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>

7. Use a database:

We have to use this command before you want to interact with a database and its tables.

mysql> use testdb;
Database changed
mysql>

8. Create a new user:

mysql> create user 'ledlong'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.03 sec)
mysql> use mysql;
Database changed
mysql> select Host, User, Password from user;
+-----------+---------+-------------------------------------------+
| Host      | User    | Password                                  |
+-----------+---------+-------------------------------------------+
| localhost | root    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | admin   | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | ledlong | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+---------+-------------------------------------------+
3 rows in set (0.00 sec)
mysql>

9. Grant privilege for a user:

mysql> grant all privileges on testdb.* to 'ledlong'@'localhost';
Query OK, 0 rows affected (0.00 sec)

This way can be used when you have already created a new user before.
Other way:

mysql> grant all privileges on testdb.* to 'ledlong'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

This way can be used when you have not created a new user before.
Use “flush privileges” command to active all privileges above:

mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)

10. Drop a database:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| demo               |
| mysql              |
| performance_schema |
| test               |
| testdb             |
+--------------------+
6 rows in set (0.00 sec)
mysql> drop database test;
Query OK, 0 rows affected (0.36 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| demo               |
| mysql              |
| performance_schema |
| testdb             |
+--------------------+
5 rows in set (0.00 sec)
mysql>

11. Drop a table:

mysql> use testdb;
Database changed
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| audience         |
| authors          |
| books            |
| images           |
| testing          |
+------------------+
5 rows in set (0.00 sec)
mysql> drop table audience;
Query OK, 0 rows affected (0.41 sec)
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| authors          |
| books            |
| images           |
| testing          |
+------------------+
4 rows in set (0.00 sec)
mysql>

12. SELECT command:

SELECT (Columns_Name) FROM (Tables_Name);

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| authors          |
| books            |
| images           |
| testing          |
+------------------+
4 rows in set (0.08 sec)

If you want to show all field of a table:

mysql> select * from authors;
+----+-------------------+
| Id | Name              |
+----+-------------------+
| 1  | Jack London       |
| 2  | Honore de Balzac  |
| 3  | Lion Feuchtwanger |
| 4  | Emile Zola        |
| 5  | Truman Capote     |
| 6  | Le Duong Long     |
+----+-------------------+
6 rows in set (0.05 sec)

Or if you only want to show some specific fields of a table:

mysql> select Name from Authors;
+-------------------+
| Name              |
+-------------------+
| Jack London       |
| Honore de Balzac  |
| Lion Feuchtwanger |
| Emile Zola        |
| Truman Capote     |
| Le Duong Long     |
+-------------------+
6 rows in set (0.01 sec)

13. INSERT command:

INSERT INTO <Table_Name(Columns_Name)> VALUES ();

mysql> select * from authors;
+----+-------------------+
| Id | Name              |
+----+-------------------+
| 1  | Jack London       |
| 2  | Honore de Balzac  |
| 3  | Lion Feuchtwanger |
| 4  | Emile Zola        |
| 5  | Truman Capote     |
| 6  | Le Duong Long     |
+----+-------------------+
6 rows in set (0.00 sec)
mysql> insert into authors(Name) values ('Le Vinh Hien');
Query OK, 1 row affected (0.05 sec)
mysql> select * from authors;
+----+-------------------+
| Id | Name              |
+----+-------------------+
| 1  | Jack London       |
| 2  | Honore de Balzac  |
| 3  | Lion Feuchtwanger |
| 4  | Emile Zola        |
| 5  | Truman Capote     |
| 6  | Le Duong Long     |
| 11 | Le Vinh Hien      |
+----+-------------------+
7 rows in set (0.00 sec)
mysql>

(Continue)

Hanoi, April 21th, 2012

ledlong

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s