What is MySQL?
MySQL is an open-source relational database management system (RDBMS) that uses Structured Query Language (SQL) for managing data.
MySQL was first developed in the mid-1990s by a Swedish company called MySQL AB, which was later acquired by Sun Microsystems and then by Oracle Corporation. MySQL is now owned by Oracle and is released under both open-source and commercial licenses.
The name MySQL is a combination of two words—My + SQL. My is the daughter’s name of MySQL’s co-founder, Monty Widenius, and SQL (Structured Query Language).
MySQL is written in C and C++. Its SQL parser is written in YACC (Yet Another Compiler-Compiler), but it uses a home-brewed lexical analyzer.
MySQL is also known for its performance and scalability. It can handle large amounts of data and can be used in high-traffic environments. It is widely used by companies of all sizes, from small startups to large enterprises, and is an important part of many web-based applications and services.
In which language MySQL has been written?
MySQL is written in C and C++. Its SQL parser is written in YACC (Yet Another Compiler-Compiler), but it uses a home-brewed lexical analyzer.
How to install MySQL in Windows?
To install MySQL, please refer to this link.
Install MySQLHow to check the MySQL version?
To check the version of MySQL, type and execute mysql -V (note the uppercase V) in the command line.
mysql -V
What is the MySQL default port number?
MySQL's default port number is 3306.
How to create a database in MySQL?
In MySQL, we can create a new database using CREATE DATABASE statement followed by the name of the database.
See the syntax and example below.
Syntax:
CREATE DATABASE database_name;
Example:
CREATE DATABASE schoolDB;
How to drop a database in MySQL?
The DROP DATABASE statement is used to drop an existing database from MySQL.
Deleting a database means all the tables, indexes, views etc. related to the database will deleted.
See the syntax and example below.
Syntax:
DROP DATABASE database_name;
Example:
DROP DATABASE schoolDB;
How to create a table in MySQL database?
To create a new table in MySQL, we can use the CREATE TABLE statement followed by the table name and the list of columns along with their data types and any constraints.
See the syntax and example below.
Syntax:
CREATE TABLE table_name (
column_definition1,
column_definition2,
........,
table_constraints
);
Example:
CREATE TABLE student (
stu_id INT AUTO_INCREMENT PRIMARY KEY,
stu_name VARCHAR(100) NOT NULL,
class VARCHAR(30) NOT NULL,
roll SMALLINT,
age TINYINT,
gender VARCHAR(1),
date_of_admission DATE,
remarks TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
How to drop a table from MySQL database?
The DROP TABLE statement is used to drop an existing table from the MySQL database.
This statement permanently deletes all records, indexes, and privileges associated with the table, so before deleting a table, just make sure the table we are going to delete is no longer needed.
The syntax and example for the DROP TABLE statement are as follows:
Syntax:
DROP TABLE table_name;
Example:
DROP TABLE student;
How to change the table name in MySQL?
To rename or change the table name in MySQL, we can use the RENAME TABLE statement.
Here's the syntax:
RENAME TABLE current_table_name TO new_table_name;
Replace current_table_name with the name of the table we want to rename, and new_table_name with the new name we want to assign to the table.
Here's an example to illustrate the usage:
Before Renaming a Table
mysql> show tables; +---------------------+ | Tables_in_school_db | +---------------------+ | admin | | students | | teachers | +---------------------+ 3 rows in set (0.05 sec)
Now, executing the RENAME TABLE statement.
mysql> RENAME TABLE admin TO management; Query OK, 0 rows affected (4.28 sec) mysql> show tables; +---------------------+ | Tables_in_school_db | +---------------------+ | management | | students | | teachers | +---------------------+ 3 rows in set (0.04 sec)
As we can see in the above example, the table name admin has been renamed to management using the RENAME TABLE statement.
In MySQL, how to add a new column in a table?
In MySQL, We can add one or more new columns in the existing table with the help of ALTER TABLE statement.
The syntax and example are as follows:
Syntax:
ALTER TABLE table_name ADD new_column_name column_definition [FIRST | AFTER column_name];
Example:
ALTER TABLE student ADD marks int NOT NULL;
In MySQL, how to drop a column from a table?
To drop a column from a table in MySQL, we can use the ALTER TABLE statement with the DROP COLUMN clause.
The syntax and example are as follows:
Syntax:
ALTER TABLE table_name DROP COLUMN column_name;
Replace table_name with the name of the table from which we want to remove the column, and column_name with the name of the column we wish to drop.
Example:
If we want to delete the column section from the students table, we will write the following query.
ALTER TABLE students DROP COLUMN section;
How to add a Primary key in a table?
In MySQL, a primary key is a column or a set of columns that uniquely identify each row (record) in a table. It serves as a unique identifier and enforces data integrity by ensuring that no two rows in the table have the same primary key value. Having a primary key is essential for most database tables, as it allows for efficient searching, indexing, and maintaining relationships between different tables.
A table can have only one primary key.
To define a primary key in MySQL, we can use the PRIMARY KEY constraint.
There are two ways to add the primary key to a table:
At the time of creating a new table
By altering an existing table
Let's understand it through the syntax and examples given below.
1. Add Primary Key during table creation
Generally, we add a primary key at the time of table creation, which is a very simple way to add a primary key to a table.
CREATE TABLE table_name ( column1 datatype PRIMARY KEY, column2 datatype, ... );
Here's an example of creating a table with a primary key:
CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), department VARCHAR(100) );
In this example, the employee_id column is designated as the primary key, ensuring that each employee's ID is unique within the table.
2. Add Primary Key by altering an existing table
We can also define the PRIMARY KEY constraint separately after creating the table, so let's first create a table and then add a primary key.
CREATE TABLE customer ( cust_id INT, name VARCHAR(50), age INT );
Adding a primary key using ALTER TABLE statement.
ALTER TABLE customer ADD PRIMARY KEY (cust_id);
In this example, the cust_id column is defined separately as the primary key using the ALTER TABLE statement.
How to drop a primary key in MySQL?
To drop a primary key from a table, we use the ALTER TABLE statement in MySQL.
The syntax to drop a primary key in MySQL is:
ALTER TABLE table_name DROP PRIMARY KEY;
Suppose we have a table called customer and it has a primary key. Let's see in the below query how the primary key is being removed from the table.
ALTER TABLE table_name DROP PRIMARY KEY;
In this example, we've dropped the primary key from the customer table. We do not need to specify the name of the primary key.
What is the difference between CHAR and VARCHAR data types in MySQL?
Ans.: Let's understand difference between CHAR and VARCHAR throgh the given definition:
CHAR: It is a fixed-length string data type in MySQL. When you define a CHAR column, you must specify a fixed length for it. It takes up the specified length of storage, even if the actual data is shorter. For example, if you define a CHAR(10) column and store "Hello" in it, it will take up 10 bytes of storage.
VARCHAR: It is a variable-length string data type in MySQL. When you define a VARCHAR column, you specify the maximum length it can hold. It takes up the actual length of storage plus one or two bytes for length information. For example, if you define a VARCHAR(10) column and store "Hello" in it, it will take up 6 bytes of storage.
What is BLOB in MySQL?
BLOB stands for a Binary Large Object. It is used to hold a variable amount of data. BLOB values are treated as binary strings (byte strings).
There are four types of BLOB:
These differ only in the maximum length of the values they can hold.
We use BLOB data-type for a column if we need to store very large amount of data such as documents, images, videos etc.
How to display the nth highest salary from a table in a MySQL query?
To find Nth highest salary is:
SELECT DISTINCT(salary) FROM employee ORDER BY salary DESC LIMIT n-1,1
if you want to find 3rd largest salary:
SELECT DISTINCT(salary) FROM employee ORDER BY salary DESC LIMIT 2,1
How many columns can you create for an index?
We can a create maximum of 16 indexed columns for a standard table.
What is the difference between NOW() and CURRENT_DATE()?
NOW() command is used to show current year, month, date with hours, minutes, and seconds while CURRENT_DATE() shows the current year with month and date only.
What is the query to fetch top 20 rows from a table?
To fetch the top 20 rows (records) from a table we execute the below query.
SELECT * FROM table_name LIMIT 0,20;
Write a query to count the total number of rows of a table in MySQL.
To count the total number of rows of a table, find the below query.
SELECT COUNT(*) AS 'Row count' FROM table_name;
feedback@javabytechie.com