MySQL Create DB, table and Insert contents from command line

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

Arunlal Ashok

Linux Server Administrator. I'm dealing with Linux servers since 2012. I started this blog to share and discuss my ideas with the world. Check My Profile!! in uPwork (oDesk) and let me know if you need any assistance. Thanks!!

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *