MySQL Administration Basic
1:- Install MySQL Database
Use “yum” or “apt” package manager to install MySQL Database.
# yum install mysql mysql-client mysql-server (on Yum based Systems)
# apt-get install mysql mysql-client mysql-server (on Apt based Systems)
2. Start MySQL Server
Start MySQL database service as:
# service mysqld start
Or
# service mysql start
Well installing a MySQL database will take you to the configuration where you are asked to
setup Root user password, etc. Once finished installing and starting the server go to
your MySQL prompt.
3. Accessing MySQL database
# mysql -u root -p
User can replace root with your configured username and enter password when prompted, if
the login credential is correct, you will be at your MySQL prompt at the blink of your eyes.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 195
Server version: 5.5.31-0+wheezy1 (Debian)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
Now carrying out queries at this prompt is very educative and fun.
4. Create a database amazontest
mysql> create database amazontest ;
Query OK, 1 row affected (0.02 sec)
mysql>
It reports that the query was correct, means database is created. You can verify your newly
created database as.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| amazontest |
| test |
+--------------------+
9 rows in set (0.00 sec)
mysql>
Notice your database in the above output.
5. Select Database
Now you need to select the database to work upon it.
mysql> use amazontest;
Database changed
mysql>
6. Create Tables in MySQL
Here we will be creating a table say “test” with three fields as:
mysql> CREATE TABLE test (
-> id Int(3),
-> first_name Varchar (15),
-> email Varchar(20)
-> );
Query OK, 0 rows affected (0.08 sec)
mysql>
Note: The above query says OK which means table was created without any error. To verify the
table run the below query.
mysql> show tables;
+-------------------+
| Tables_in_amazontest |
+-------------------+
| test |
+-------------------+
1 row in set (0.00 sec)
mysql>
Things are going fine till now. Yup! You can view the columns you created in the table “test” as:
mysql> show columns from test;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int(3) | YES | | NULL | |
| first_name | varchar(15) | YES | | NULL | |
| email | varchar(20) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql>
It was nothing less than a magic. Anyway I will tell you about the types of declaration and their
meaning.
1. Int is Integer
2. Varchar is char having variable length as defined. The value after Type is the length of
field up-to which it can store data.
OK ,now we need to add a column say ‘last_name‘ after column ‘first_name‘.
mysql> ALTER TABLE test ADD last_name varchar (20) AFTER first_name;
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
Now, verify it in your table.
mysql> show columns from test;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int(3) | YES | | NULL | |
| first_name | varchar(15) | YES | | NULL | |
| last_name | varchar(20) | YES | | NULL | |
| email | varchar(20) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>
7. Add Column in MySQL
Now we will add a column to the right say a column ‘country‘ to the right of email.
mysql> ALTER TABLE test ADD country varchar (15) AFTER email;
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
Verify the above column insertion query.
mysql> show columns from test;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int(3) | YES | | NULL | |
| first_name | varchar(15) | YES | | NULL | |
| last_name | varchar(20) | YES | | NULL | |
| email | varchar(20) | YES | | NULL | |
| country | varchar(15) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql>
8. Insert Values in Field
What about inserting values to the field?
mysql> INSERT INTO test VALUES ('1' , 'Ravi' , 'Saive' , 'raivsaive@xyz.com' ,
'India' );
Query OK, 1 row affected (0.02 sec)
mysql>
How about inserting more than 1 value at a time in the above table.
mysql> INSERT INTO test VALUES ('2' , 'Narad' , 'Shrestha' , 'narad@xyz.com' ,
'India' ), ('3' , 'user' , 'singh' , 'user@xyz.com' , 'Aus' ), ('4' ,
'amazontest' , '[dot]com' , 'amazontest@gmail.com' , 'India' );
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
Verify the above insertion.
mysql> select * from test;
+------+------------+-----------+-------------------+---------+
| id | first_name | last_name | email | country |
+------+------------+-----------+-------------------+---------+
| 1 | Ravi | Saive | raivsaive@xyz.com | India |
| 2 | Narad | Shrestha | narad@xyz.com | India |
| 3 | user | singh | user@xyz.com | Aus |
| 4 | amazontest | [dot]com | amazontest@gmail.com | India |
+------+------------+-----------+-------------------+---------+
4 rows in set (0.00 sec)
mysql>
9. Delete Values in Field
Let’s say the third entry in the above output is invalid and we need to delete the third entry.
mysql> DELETE FROM test WHERE id = 3;
Query OK, 1 row affected (0.02 sec)
Verify the above operation.
mysql> select * from test;
+------+------------+-----------+-------------------+---------+
| id | first_name | last_name | email | country |
+------+------------+-----------+-------------------+---------+
| 1 | Ravi | Saive | raivsaive@xyz.com | India |
| 2 | Narad | Shrestha | narad@xyz.com | India |
| 4 | amazontest | [dot]com | amazontest@gmail.com | India |
+------+------------+-----------+-------------------+---------+
3 rows in set (0.00 sec)
10. Update Values in Field
The id (=4) needs to be edited.
mysql> UPDATE test SET id = 3 WHERE first_name = 'amazontest';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
Verify the above query.
mysql> UPDATE test SET id = 3 WHERE first_name = 'amazontest';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
Note: The above query, as performed is not a good idea. It will change the id to ‘4‘where ever
the first name is ‘amazontest’. It is always a good idea to use more than one column with where
clause to get minimal error, as:
mysql> UPDATE test SET id = 6 WHERE first_name = 'amazontest' AND last_name =
'[dot]com';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
11. Delete Column in MySQL
Let we need to drop (delete) a column we think, is of no importance say ‘country‘here.
mysql> ALTER TABLE test drop country;
Query OK, 3 rows affected (0.15 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
Verify the table.
mysql> select * from test;
+------+------------+-----------+-------------------+
| id | first_name | last_name | email |
+------+------------+-----------+-------------------+
| 1 | Ravi | Saive | raivsaive@xyz.com |
| 2 | Narad | Shrestha | narad@xyz.com |
| 6 | amazontest | [dot]com | amazontest@gmail.com |
+------+------------+-----------+-------------------+
3 rows in set (0.00 sec)
mysql>
12. Rename Table in MySQL
Don’t you think our table name “test” is not very much relevant. How about changing it
to amazontest_table.
mysql> RENAME TABLE test TO amazontest_table;
Query OK, 0 rows affected (0.03 sec)
mysql>
13. List all Tables
See all the tables under the current database.
mysql> show tables;
+-------------------+
| Tables_in_amazontest |
+-------------------+
| amazontest_table |
+-------------------+
1 row in set (0.00 sec)
mysql>
1:- Install MySQL Database
Use “yum” or “apt” package manager to install MySQL Database.
# yum install mysql mysql-client mysql-server (on Yum based Systems)
# apt-get install mysql mysql-client mysql-server (on Apt based Systems)
2. Start MySQL Server
Start MySQL database service as:
# service mysqld start
Or
# service mysql start
Well installing a MySQL database will take you to the configuration where you are asked to
setup Root user password, etc. Once finished installing and starting the server go to
your MySQL prompt.
3. Accessing MySQL database
# mysql -u root -p
User can replace root with your configured username and enter password when prompted, if
the login credential is correct, you will be at your MySQL prompt at the blink of your eyes.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 195
Server version: 5.5.31-0+wheezy1 (Debian)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
Now carrying out queries at this prompt is very educative and fun.
4. Create a database amazontest
mysql> create database amazontest ;
Query OK, 1 row affected (0.02 sec)
mysql>
It reports that the query was correct, means database is created. You can verify your newly
created database as.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| amazontest |
| test |
+--------------------+
9 rows in set (0.00 sec)
mysql>
Notice your database in the above output.
5. Select Database
Now you need to select the database to work upon it.
mysql> use amazontest;
Database changed
mysql>
6. Create Tables in MySQL
Here we will be creating a table say “test” with three fields as:
mysql> CREATE TABLE test (
-> id Int(3),
-> first_name Varchar (15),
-> email Varchar(20)
-> );
Query OK, 0 rows affected (0.08 sec)
mysql>
Note: The above query says OK which means table was created without any error. To verify the
table run the below query.
mysql> show tables;
+-------------------+
| Tables_in_amazontest |
+-------------------+
| test |
+-------------------+
1 row in set (0.00 sec)
mysql>
Things are going fine till now. Yup! You can view the columns you created in the table “test” as:
mysql> show columns from test;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int(3) | YES | | NULL | |
| first_name | varchar(15) | YES | | NULL | |
| email | varchar(20) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql>
It was nothing less than a magic. Anyway I will tell you about the types of declaration and their
meaning.
1. Int is Integer
2. Varchar is char having variable length as defined. The value after Type is the length of
field up-to which it can store data.
OK ,now we need to add a column say ‘last_name‘ after column ‘first_name‘.
mysql> ALTER TABLE test ADD last_name varchar (20) AFTER first_name;
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
Now, verify it in your table.
mysql> show columns from test;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int(3) | YES | | NULL | |
| first_name | varchar(15) | YES | | NULL | |
| last_name | varchar(20) | YES | | NULL | |
| email | varchar(20) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>
7. Add Column in MySQL
Now we will add a column to the right say a column ‘country‘ to the right of email.
mysql> ALTER TABLE test ADD country varchar (15) AFTER email;
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
Verify the above column insertion query.
mysql> show columns from test;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int(3) | YES | | NULL | |
| first_name | varchar(15) | YES | | NULL | |
| last_name | varchar(20) | YES | | NULL | |
| email | varchar(20) | YES | | NULL | |
| country | varchar(15) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql>
8. Insert Values in Field
What about inserting values to the field?
mysql> INSERT INTO test VALUES ('1' , 'Ravi' , 'Saive' , 'raivsaive@xyz.com' ,
'India' );
Query OK, 1 row affected (0.02 sec)
mysql>
How about inserting more than 1 value at a time in the above table.
mysql> INSERT INTO test VALUES ('2' , 'Narad' , 'Shrestha' , 'narad@xyz.com' ,
'India' ), ('3' , 'user' , 'singh' , 'user@xyz.com' , 'Aus' ), ('4' ,
'amazontest' , '[dot]com' , 'amazontest@gmail.com' , 'India' );
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
Verify the above insertion.
mysql> select * from test;
+------+------------+-----------+-------------------+---------+
| id | first_name | last_name | email | country |
+------+------------+-----------+-------------------+---------+
| 1 | Ravi | Saive | raivsaive@xyz.com | India |
| 2 | Narad | Shrestha | narad@xyz.com | India |
| 3 | user | singh | user@xyz.com | Aus |
| 4 | amazontest | [dot]com | amazontest@gmail.com | India |
+------+------------+-----------+-------------------+---------+
4 rows in set (0.00 sec)
mysql>
9. Delete Values in Field
Let’s say the third entry in the above output is invalid and we need to delete the third entry.
mysql> DELETE FROM test WHERE id = 3;
Query OK, 1 row affected (0.02 sec)
Verify the above operation.
mysql> select * from test;
+------+------------+-----------+-------------------+---------+
| id | first_name | last_name | email | country |
+------+------------+-----------+-------------------+---------+
| 1 | Ravi | Saive | raivsaive@xyz.com | India |
| 2 | Narad | Shrestha | narad@xyz.com | India |
| 4 | amazontest | [dot]com | amazontest@gmail.com | India |
+------+------------+-----------+-------------------+---------+
3 rows in set (0.00 sec)
10. Update Values in Field
The id (=4) needs to be edited.
mysql> UPDATE test SET id = 3 WHERE first_name = 'amazontest';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
Verify the above query.
mysql> UPDATE test SET id = 3 WHERE first_name = 'amazontest';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
Note: The above query, as performed is not a good idea. It will change the id to ‘4‘where ever
the first name is ‘amazontest’. It is always a good idea to use more than one column with where
clause to get minimal error, as:
mysql> UPDATE test SET id = 6 WHERE first_name = 'amazontest' AND last_name =
'[dot]com';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
11. Delete Column in MySQL
Let we need to drop (delete) a column we think, is of no importance say ‘country‘here.
mysql> ALTER TABLE test drop country;
Query OK, 3 rows affected (0.15 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
Verify the table.
mysql> select * from test;
+------+------------+-----------+-------------------+
| id | first_name | last_name | email |
+------+------------+-----------+-------------------+
| 1 | Ravi | Saive | raivsaive@xyz.com |
| 2 | Narad | Shrestha | narad@xyz.com |
| 6 | amazontest | [dot]com | amazontest@gmail.com |
+------+------------+-----------+-------------------+
3 rows in set (0.00 sec)
mysql>
12. Rename Table in MySQL
Don’t you think our table name “test” is not very much relevant. How about changing it
to amazontest_table.
mysql> RENAME TABLE test TO amazontest_table;
Query OK, 0 rows affected (0.03 sec)
mysql>
13. List all Tables
See all the tables under the current database.
mysql> show tables;
+-------------------+
| Tables_in_amazontest |
+-------------------+
| amazontest_table |
+-------------------+
1 row in set (0.00 sec)
mysql>
No comments:
Post a Comment