We already discussed some basics of MySQL database management. Here is some more command line usages of MySQL with example.
1. To connect to MySQL we can use the command
# mysql -u root -p
Then, enter the MySQL root password
Now we are in MySQL prompt.
2. Create DB and DB users.
We already discussed this previously, Click here for more details Creating DB and DB users.
For granting permission to database user you can refer New Database-user and Grand Permissions
3. To access a database
mysql> use database_name;
Example:
mysql> use crybit_test;
Database changed
4. To display the databases in a server
mysql> show databases;
Example:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| crybit_test |
| mysql |
| |
+--------------------+
3 rows in set (0.00 sec)
4. Create MySQL table
Connect to the database before proceeding with any operation in it
mysql> use database_name;
Then, you can follow the below step to create a table
mysql> create table staff ( id INT AUTO_INCREMENT PRIMARY KEY, name varchar (20), dept varchar (10), salary int (10) );
– Here the table’s name is “staff”
– The field AUTO_INCREMENT tells MySQL to go ahead and add the next available number to the id field
– PRIMARY KEY is used to define a column as primary key. You can use multiple columns separated by a comma to define primary key
– ;(semi colon) defines the end of a SQL command
5. To show tables
mysql> show tables;
Example:
mysql> show tables;
+-----------------------+
| Tables_in_crybit_test |
+-----------------------+
| staff |
+-----------------------+
1 row in set (0.00 sec)
6. To view table description
mysql> desc table_name
Example:
mysql> desc staff
-> ;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| dept | varchar(10) | YES | | NULL | |
| salary | int(10) | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
7. Insert records into a table
Use the following sample insert command to insert records into table
mysql> insert into staff values ( 101,'Amy','Technical','20000');
Query OK, 1 row affected (0.00 sec)
mysql> insert into staff values (102,'Betsy','Technical','25000');
Query OK, 1 row affected (0.00 sec)
mysql> insert into staff values (103,'Cherry','Technical','25000');
Query OK, 1 row affected (0.00 sec)
mysql> insert into staff values (104,'Dalmi','Technical','35000');
Query OK, 1 row affected (0.00 sec)
mysql> insert into staff values (105,'Esther','Technical','35000');
Query OK, 1 row affected (0.00 sec)
8. To insert values into specific columns, you can specify the column names as shown below
mysql> insert into staff (id,name,salary) values (106,'Fifi','40000');
Query OK, 1 row affected (0.00 sec)
9. To view the values in a table
mysql> select * from table_name;
Example:
mysql> select * from staff;
+-----+--------+-----------+--------+
| id | name | dept | salary |
+-----+--------+-----------+--------+
| 101 | Amy | Technical | 20000 |
| 102 | Betsy | Technical | 25000 |
| 103 | Cherry | Technical | 25000 |
| 104 | Dalmi | Technical | 35000 |
| 105 | Esther | Technical | 35000 |
| 106 | Fifi | NULL | 40000 |
+-----+--------+-----------+--------+
6 rows in set (0.00 sec)
10. To view only specific columns from a table
mysql> select name, dept from staff;
+--------+-----------+
| name | dept |
+--------+-----------+
| Amy | Technical |
| Betsy | Technical |
| Cherry | Technical |
| Dalmi | Technical |
| Esther | Technical |
+--------+-----------+
5 rows in set (0.00 sec)
11. Conditional selection
The following will displays whose salary value equals to 25000
mysql> select * from staff where salary = 25000
-> ;
+-----+--------+-----------+--------+
| id | name | dept | salary |
+-----+--------+-----------+--------+
| 102 | Betsy | Technical | 25000 |
| 103 | Cherry | Technical | 25000 |
+-----+--------+-----------+--------+
2 rows in set (0.00 sec)
That’s it!! 🙂 🙂
Related:
Reset mysql root password from command line
How to allow mysql client to access remote mysql databases
How to create/restore MySQL dump/backup of all the databases.
How to create a database and database user from command line
create a New Database-user and Grand Permissions
