How to Create Databases, Tables, and Data Types in MySQL
A MySQL Database Administration Guide.
Have you ever got stuck to the point of… How to Create Databases? In this guide, we will show you how to perform some of the most common MySQL database administration tasks from the command line, using the MySQL command line tool.
PLEASE NOTE: This article doesn’t cover the MySQL management tools included within the default cPanel installation. If you have any technical queries or questions, feel free to contact our support via our ticket system or via our live chat.
MySQL – Overview
MySQL is the most popular relational database management system in the world. It’s a free, open source software that’s compatible with most modern operating systems. MySQL functions by connecting to and manipulating databases in a simple and straightforward manner, using SQL commands.
PHP and MySQL form the basis of a LAMP (Linux, Apache, MySQL and PHP) stack, which is the software configuration used for hosting a large portion of websites currently available on the internet. Using the SQL language, MySQL stores data in tables, which are collections of related data consisting of columns and rows.
MySQL Features
Client/Server Architecture: MySQL operates on a client/server system. This means that there is a single main database server running MySQL with many clients (application programs) communicating with the main server. These communications will consist of querying data, saving changes to the databases, etc. Clients run on either the same machine as the server, or a separate computer connected through the internet or a local network.
SQL Compatibility: MySQL supports SQL (structured query language) as it’s database language. SQL is a common standardised language used for updating databases as well as querying data.
Views: Views are SQL queries that are viewed as distinct database objects, made available since MySQL version 5.0.
Triggers: Triggers are SQL commands that are automatically executed when a certain database operation has happened, which triggers the command.
Replication: Replication is where the contents of a database is copied, or replicated, onto a number of computers.
Transactions: A transaction is when several database operations are executed as a block, with the database system making sure that either all the operations are executed, or none of them.
Platform Independence: MySQL can be run on a large number of operating systems such as Mac OS X, Windows, Linux, and many more.
Foreign Key Constraints: These are rules to ensure that no cross references in linked tables lead to nowhere.
ODBC: ODBC interfaces allow MySQL to be addressed by all the languages that run under Microsoft Windows.
MySQL Data Types
MySQL has several different data types, so we will categorise them as such:
- Numeric Data Types
- Date and Time
- String Types
- Numeric Data Types
MySQL uses the following common numeric data types:
INT: A normal integer that can be either signed or unsigned. If signed, the allowable range is from -2147483648 to 2147483647. If unsigned, the allowable range is from 0 to 4294967295.
TINYINT: A very small integer that is either signed or unsigned. If signed, the allowable range is from -128 to 127. If unsigned, the allowable range is from 0 to 255. You can specify a width of up to 4 digits.
SMALLINT: A small integer that is either signed or unsigned. If signed, the allowable range is from -32768 to 32767. If unsigned, the allowable range is from 0 to 65535. You can specify a width of up to 5 digits.
MEDIUMINT: A medium-sized integer that is either signed or unsigned. If signed, the allowable range is from -8388608 to 8388607. If unsigned, the allowable range is from 0 to 16777215. You can specify a width of up to 9 digits.
BIGINT: A large integer that is either signed or unsigned. If signed, the allowable range is from -9223372036854775808 to 9223372036854775807. If unsigned, the allowable range is from 0 to 18446744073709551615. You can specify a width of up to 20 digits.
FLOAT(M,D): A floating-point number that must be signed and cannot be unsigned. The display length (M) and the number of decimals (D) is defined by you.
DOUBLE(M,D): A double precision floating point number that must be signed and cannot be unsigned. The display length (M) and the number of decimals (D) is defined by you.
DECIMAL(M,D): An unpacked floating-point number that must be signed and cannot be unsigned. The display length (M) and the number of decimals (D) is defined by you.
Date and Time Types
MySQL uses the following Date and Time data types:
DATE: A date in the standard YYYY-MM-DD format
DATETIME: A date and time combination in the standard YYYY-MM-DD HH:MM:SS format
TIMESTAMP: A timestamp in the standard DATETIME format, but without the hyphens between numbers
TIME: The time in a standard HH:MM:SS format
YEAR: A year in either 2-digit or 4-digit format
String Types
MySQL uses the following string data types:
CHAR: A fixed length character string between 1 and 255 characters in length, padded with spaces
VARCHAR: A variable-length string betweeen 1 and 255, with a defined length
BLOB/TEXT: A BLOB or TEXT column with a maximum legnth of 65535 characters.
TINYBLOB/TINYTEXT: A BLOB or TEXT column with a maximum legnth of 255 characters.
MEDIUMBLOB/MEDIUMTEXT: A BLOB or TEXT column with a maximum length of 16777215 characters.
MEDIUMBLOB/MEDIUMTEXT: A BLOB or TEXT column with a maximum legnth of 4294967295 characters.
ENUM: An enumeration or a list of items from which a value must be selected
Creating and Selecting a Database
This section will explain how you can create and select a MySQL database.
First, login to MySQL from the command line, as the root user:
mysql -u root -p
Type the MySQL root password and press enter to login.
To create a database, type the following command replacing dbname with the name of the database to be created:
CREATE DATABASE dbname;
To select a database, simply use the following command replacing dbname with the name of the database:
Use dbname
Adding users to MySQL
This section will explain how to add a user to a MySQL database.
First, use the following command to add a new user to the server, replacing newuser with the username and password with the password:
CREATE USER ‘newuser’@’localhost’ IDENTIFIED BY ‘password’;
Now we need to grant permissions to the database user. So, we must use the following command to grant all privileges to the new user:
GRANT ALL PRIVILEGES ON * . * TO ‘newuser’@’localhost’;
Creating a MySQL Table
This section will show you how to add a table to our MySQL database.
For this we will use the CREATE TABLE statement, along with columns. In this example we will use id, firstname, lastname, and email address as our columns:
CREATE TABLE MyTable (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
)
Inserting data into MySQL tables
This section will explain how to insert data into our MySQL table.
First, we need to use the INSERT INTO statement to insert data into our database table. This is used along with the columns to insert data into, like this:
INSERT INTO MyTable (id, firstname, lastname, email)
This needs to now be followed by the values to be inserted into those columns like so:
VALUES (‘1’, ‘Tom’, ‘Smith’, ‘tom@gmail.com’);
Deleting data from MySQL Tables
This section will explain how to delete data from our MySQL table.
For this we need to use the DELETE FROM command, followed by the name of the database table, as well as what should be deleted. This example will delete a record from the MyTable databale table where the firstname is Tom:
DELETE FROM MyTable WHERE firstname=Tom;
Deleting tables and databases in MySQL
This section will show you how to delete tables and databases from our MySQL server.
Deleting tables
To delete a MySQL table, we must use the DROP TABLE operation, followed by the name of the table. In this example, we will delete the table named MyTable:
DROP TABLE MyTable;
Deleting databases
To delete a MySQL database, we must use the DROP DATABASE command, followed by the name of the database. In this example, we will drop the database named first_database:
DROP DATABASE first_database;
Included in all our plans, you’ll find unlimited databases amount and a lot of powerful features we added just for you.
Need our assistance? You have a question?
If you need our support, feel free to contact our 24/7 super friendly support team available on our ticket platform or directly via our Live Chat. On top of that you can always look at our Frequent Asked Questions our Support Staff receives.
Leave a Reply
You must be logged in to post a comment.