How to Create Database, Tables, Data Types in MySQL?
In this article, we will describe how to do some of the most common MYSQL database administration tasks from the command line using the mysql command line tool.
Note: this doesn’t cover the MySQL management tools included within the base cPanel installation.
MySQL- Overview
MySQL is the most popular open source relational database management system in the world. MySQL lets you connect to and manipulate databases in a simple and straightforward manner, with the use of 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 most of the world’s websites. MySQL is a free, open source software that’s compatible with most modern hosting platforms. Using the SQL language, MySQL stores data in tables, which are collections of related data consisting of columns and rows.
Features Of MySQL
Client/Server Architecture: MySQL runs on a client/server system. This means that there is a single main database server running on MySQL with many clients (application programs) that communicate with the database server. These communications will consist of querying data, saving changes to the databases, etc. Clients run on either the same computer as the server or a separate computer connected by 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 and querying data. MySQL currently adheres to the currently SQL standard.
Views: Views are essentially SQL queries that are viewed as distinct database objects, made available since MySQL version 5.0.
Triggers: As the name suggests, triggers are SQL commands that are automatically executed when a certain database operation has happened, triggering the command.
Replication: Replication is when 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 ensuring that all the operations are executed, or none of them.
Platform Independence: MySQL can be run in a number of operating systems such as Mac OS X, Windows, Linux, and many more.
Foreign Key Constraints: These are rules ensuring that no cross references in linked tables lead to nowehere.
ODBC: ODBC interface allows MySQL to be addressed by all the languages that run under Microsoft Windows.
MySQL Data Types
MySQL has many different data types, so we will categories 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 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 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 to 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 have to grant permissions to the database user. So, 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 explain how to add a table to our MySQL database.
For this we need to 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 now needs to 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 explain how to delete tables and databases from our MySQL server.
Deleting tables
To delete a MySQL table, we need to use the DROP TABLE operation, followed by the name of the table. In this example, we will delete the MyTable table:
DROP TABLE MyTable;
Deleting databases
To delete a MySQL database, we need to 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;
Image Source: Pixabay.com
