Common MySql commands

Tags: , ,

In this post, we describe the most popular Sql commands. 

How to Install MySql

Please refer to this post on the process of installing Mysql database

The most popular MySql Commands

Here are some of the most popular SQL commands we have to know.

Database Level Mysql Commands

Table Level Mysql Commands

 

Row Level Mysql Commands

 

How to Create and Delete a MySQL Database

MySQL organizes its information into databases; each one can hold tables with specific data. The show database command can help you quickly check what databases are available

SHOW DATABASES;

Your screen should look something like this:

Creating a database using the create Database command:

CREATE DATABASE database name;

In this case, for example, we will call our database “events.”

We can delete a MySQL database with the drop database command:

 

How to Access a MySQL Database

Once we have a new database, we can begin to fill it with information. The first step is to create a new table within the database.

Let’s open up the database we want to use:

List the available tables in the database.

Since this is a new database, MySQL has nothing to show, and you will get a message that says, “Empty set”

 

How to Create a MySQL Table

We can create a mysql table using the following command:

We can show the table’s organization with this command:

Keep in mind throughout that the table and database names are case sensitive: student is not the same as Student. 

How to Add Information to a MySQL Table

We can use the Insert command to add new information to the mysql table:

Once you input that in, you will see the the msgs:

Query OK, 1 row affected (0.00 sec)

We can take a look at our table:

mysql> SELECT * FROM potluck;
+----+-------+----------------+-----------+-------------+
| id | name  | address           | gender | birthday |
+----+-------+----------------+-----------+-------------+
|  1 | David  | 121 kearny ave, NJ      | M         | 1988-04-11  |
+----+-------+----------------+-----------+-------------+
1 row in set (0.00 sec)
 

How to Update Information in the Table

We can use the update command to update the information in the table. See we can update the address of david. 

You can also use this command to add information into specific cells, even if they are empty. 

How to Add and Delete a Column

The student table doesn’t contain his email address, we can easily add this:

This command puts the new column called “email” at the end of the table by default, and the VARCHAR command limits it to 40 characters.

However, if you need to place that column in a specific spot in the table, we can add one more phrase to the command.

Now the new “email” column goes after the column “name”.

Drop a column from a table

You can also delete a column:

I guess we will never know how to reach the picnickers.

 

SQL command to Delete a Row 

We can delete rows from the table with the following command:

For example, we can delete david from the table using the following command:

 

Querying the Database – SELECT

The most common, important and complex task is to query a database for a subset of data that meets your needs – with the SELECT command. The SELECT command has the following syntax:

For examples,

-- List all rows for the specified columns
mysql> SELECT name, gender FROM student;
+-----------+-------+
| name      | gender |
+-----------+-------+
| david   |  M |
+-----------+-------+
1 row in set (0.00 sec)
String Pattern Matching – LIKE and NOT LIKE

For strings, in addition to full matching using operators like '=' and '<>', we can perform pattern matching using operator LIKE (or NOT LIKE) with wildcard characters. The wildcard '_' matches any single character; '%'matches any number of characters (including zero). For example,

  • 'abc%' matches strings beginning with 'abc';
  • '%xyz' matches strings ending with 'xyz';
  • '%aaa%' matches strings containing 'aaa';
  • '___' matches strings containing exactly three characters; and
  • 'a_b%' matches strings beginning with 'a', followed by any single character, followed by 'b', followed by zero or more characters.
ORDER BY Clause

You can order the rows selected using ORDER BY clause, with the following syntax:

LIMIT Clause

A SELECT query on a large database may produce many rows. You could use the LIMIT clause to limit the number of rows displayed

Reference:

https://www.ntu.edu.sg/home/ehchua/programming/sql/MySQL_Beginner.html