Basic Sql Statements for MySQL

These are some basic Sql Statements I have used to work with MySQL database:

To insert new column in an existing table:

To add a column with data type equivalent to nvarchar in MSSQL
ALTER TABLE users ADD remarks varchar(1000) charset utf8;

To add a column after a specific column
ALTER TABLE users ADD status tinyint AFTER name;

To add a column at the first place
ALTER TABLE users ADD id int FIRST;

To delete a column from an existing table:

ALTER TABLE users DROP COLUMN status;

To rename a column from an existing table:

ALTER TABLE users CHANGE COLUMN oldName newName VARCHAR(255) NOT NULL;

To delete a record from an existing table:

DELETE FROM users WHERE Id=1;

Remember to include MySql.Data.dll

After I have uploaded the files to server, created the database and tables, when I accessed the website, I got the following error:

"Could not load file or assembly 'MySql.Data, Version=6.9.6.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d' or one of its dependencies. The system cannot find the file specified."

Solution:
1. Go to the project.
2. Go to Reference folder, select MySql.Data.
3. Select True for Copy Local.
4. Then copy the bin folder (MySql.Data.dll is included) over to the server.

How to create table in server using script

I just learned how to create tables from scripts. This is really much faster to do.

1. Open Visual Studio, expand Server Explorer, and connect to MySQL database.

2. Right click on the table which you wish to generate the script, select Generate Table Script.

3. Copy the script to a text file, and save the script with extension .sql.

4. Go to phpMyAdmin.

5. Connect to the database and click on Import on the menu.

6. Choose the script file and click Go.

How to create database and tables in server?

Finally, my project has changed from working with MSSQL database to MySQL database. It's time for me to upload it to the server, and run from there.

I am hosting my site in Exabytes, it might be different from how others do in different web hosting.

1. Click on Databases

2. Click on Create Database.

3. Enter the database name and select User(s).

4. Click on Browse Database, and the phpMyAdmin will prompt for username and password to login.

5. Click on Databases on the menu, and select the database you want to connect.

6. As there is no table created, so I am prompted to create a new table.
Enter the Name, Number of columns and click Go.

7. Enter the column name and properties.
Remember for Id field, select PRIMARY from the Index property, and check A_I for auto increment.

8. Click on Insert to insert some data.

9. Enter the values and click Go.

10. You can also go to SQL tab to write the SQL query to insert the data.

I am going to figure out how to create tables from script, so it'll be easier for me to  create the tables in future.

How to rename MySQL table

I have made a mistake in MySQL table, so here is the simple command that helps to make the change:

MySql > RENAME TABLE oldName TO newNew;

How to change codes from MSSQL to MySQL

I have already developed my codes to work with MSSQL (Microsoft SQL Server) database. As I am changing my database to MySQL, I knew there are codes that I need to change. I was quite surprise that it's actually quite easy to make the change, and changes are really minimal.

1. using System.Data.SqlClient ---> using MySql.Data.MySqlClient

2. All classes to change from Sql to MySql.
For example:
SqlConnection ---> MySqlConnection
SqlDataAdapter ---> MySqlDataAdapter
SqlDataReader ---> MySqlDataReader
SqlParameter ---> MySqlParameter
SqlTransaction ---> MySqlTransaction

3. I used to use brackets to enclose my table name, just to be save. But, it seems like MySQL doesn't recognize the brackets. So, I have to remove all the brackets.
E.g.
SELECT Name FROM [Users] ---> SELECT Name FROM Users

4. To get current  UTC time:
GetUTCDate() ---> UTC_DATE()

5. To select the last inserted Id:
SELECT @@Identity ---> SELECT LAST_INSERT_ID();

NOTE:
I read that as we call from stored procedure, MySQL recognizes the parameter to prefix with ? instead of @. But, perhaps MySQL has updated this, as it works pretty fine when I am using all @ for the parameters.

Unable to connect to any of the specified MySQL hosts

I was working with MySQL and everything worked just fine. Suddenly, or just after some time, I got error connecting to MySQL database.

I tried to connect to MySQL via Command Line Client, but once I entered the password, it just showed me error.

I had no idea what went wrong. I googled, and found out some possible solutions. There was one suggestion to reset the root password. To reset the password, I need to stop the MySQL server. When I was there, only I found out the MySQL server has stopped. So, I just started it, and it runs well again.

To start MySQL server:
1. Go to Control Panel > Administration Tools > Service
2. Look for MySQL
3. Right click and click on Start if it's already stopped

How to insert data into MySQL table?

I have just finished creating all the tables in the database, now it's time to insert some data manually.

I have not found out how to use MySQL for Visual Studio to do so, so I just used the MySQL Command Line Client.

mysql> INSERT INTO zef_status
             -> (Name)
             -> VALUES
             -> ("Active");

-> indicate a new line. It occurs when we press Enter.

We can also write the INSERT statement in one line:
mysql > INSERT INTO zef_status (Name) VALUES ("Inactive");

I will need to figure out how to connect to MySQL database in my ASP.NET project soon.

Creating table in MySQL

I have just successfully created my first table in the newly created MySQL database. I'd like to learn how to create the database and tables from script file, so it will be easier for me to re-create the same database in the web hosting server in future, but now I just learned how to create the table from Visual Studio Server Explorer.

1. Connect to the MySQL database in Visual Studio. (You can read how to do so here.)

2. Expand the database, you'll see these folders: Tables, Views, StoredProcedures, StoredFunctions,UDFs.

3. Right click on the Tables folder and click Create Table.

4. Just define the columns almost as how we do in MS SQL database.

5. To define the Primary Key:
1) In Column Properties, set Allow Nulls to No and Autoincrement to Yes.

2) Highlight the primary key column, select Table Designer in the menu, and click on Set Primary Key.

Note: To define like nvarchar() in MSSQL, here we can set the Data Type as varchar(n) and the Character Set as utf8.

This is how I created my first MySQL table in this application. I am going to explore more and convert my MS SQL database to MySQL database very soon.

Connecting MySQL database in Visual Studio

I have installed and set up MySQL Server in my Windows computer. As I have installed MySQL for Visual Studio, I'd like to connect MySQL database in Visual Studio, so it will be easier for me to work with the database while I am developing the application, as how I do with MS SQL database always.

1. Open Visual Studio.

2. Expand Server Explorer.

3. Click on Connect to database icon on top.

4. Change the Data Source to MySQL Database.
(You'll see this option if you have installed MySQL for Visual Studio)

5. Enter the information required to connect to database.
Server name: Enter the Server name as localhost if you are connecting the database locally
Username & password: Enter the user name and password that you've defined while setting up the MySQL Server.
Database name: the database that you wish to connect
Click on Test Connection button to test if the connection to database has been successful.

I refer to this tutorial to get my MySQL database connected in Visual Studio.

Creating MySQL database

I am developing an application which requires me to use MySQL database. I'd just like to write down how I achieve it.

While I am documenting this, I just found out I have written a few blog posts about MySQL too. I did another application with MySQL before, but I totally forgot about how to work with MySQL. So, it's good for me to review and re-learn.

Install & Set up MySQL
Download the installer here.
The version I downloaded is 5.6.24.

I follow this tutorial to set up the MySQL server in my Windows OS.

The products I chose: MySQL Server, MySQL Connector, MySQL for Visual Studio

Create Database (using MySQL Command Line)
1. Launch the MySQL Command Line Client.

2. Enter password

3. To create database:
mysql> create database [database_name];

4. To display a list of available databases (to check if the database has been created):
mysql> show databases;

I refer to this tutorial.

Note: I don't know how to use MySQL for Visual Studio to create database, so I used the command line to do so.

I will write how to connect to MySQL database in Visual Studio in next post.