A MySQL dump, also known simply as a “dump,” is a process of exporting or backing up the data and structure of a MySQL database into a file. This file contains a snapshot of the database’s contents at a specific point in time, making it useful for various purposes, such as: To create a MySQL … Read more
What is innodb_lock_wait_timeout ? In MySql innodb_lock_wait_timeout is an Innodb transaction wait time in seconds for a row lock. An another transaction has to wait for specified innodb_lock_wait_timeout if a transaction is taking place. innodb-lock-wait-timeout in a system variable in global and session scope this variable can set dynamically to both scopes. We can set … Read more
After creating user. Privileges are granter to user to access specific database or to access all databases in mysql GRANT ALL will give all privileges to user To Grant All privileges to user following query is used
1 | GRANT ALL PRIVILEGES ON *.* TO 'manish'@'localhost' WITH GRANT OPTION; |
To provide all privileges on specific database following query is used
1 | GRANT ALL PRIVILEGES ON DatabaseName.* TO 'manish'@'localhost' WITH GRANT OPTION; |
To provide all privileges on … Read more
We can provide a default value for a column. If we don specify its value, it will automatically assign a default value. What is the Default value in MySql n MySQL, the default value is a predefined value that is assigned to a column if no explicit value is provided during the insertion of a … Read more
Suppose we have a table user and we want to fetch all its column name.For this we use INFORMATION_SCHEMA. we will use following query to fetch all column name of table
1 2 3 4 | SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='social' AND `TABLE_NAME`='user'; |
Above query will fetch all columns of user table available in social database. Read More Setting innodb_lock_wait_timeout Set time zone in mysql … Read more
To eliminate a column from table we use drop command. Suppose we have table employee with an extra field Address and we want to eliminate this.
1 2 3 4 | create table student(id int(11) NOT NULL AUTO_INCREMENT, name varchar(40), address varchar(40), PRIMARY KEY(id)); |
1 2 3 4 5 6 7 8 9 | mysql> desc student; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(40) | YES | | NULL | | | address | varchar(40) | YES | | NULL | | +---------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) |
To remove this column we use Drop query. Syntax:
1 | ALTER TABLE table_name drop column_name; |
1 | ALTER TABLE student drop address; |
1 2 3 4 5 6 7 8 | mysql> desc student; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(40) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) |
Read More How to create a table in MySql
MySQL is a widely used relational database management system. It is used by developers, database administrators, and data analysts to store and manage data. In this tutorial, we will discuss how to create a table in MySQL using the CREATE TABLE statement. The CREATE TABLE statement is used to create a new table in a … Read more
When we create a table we can give a auto increment value to a primary key filed. This field is auto incremented by one for newly inserted value. To start auto increment from specified number AUTO_INCREMENT = value is used while creating table. Example:
1 2 3 4 5 6 7 | CREATE TABLE students ( id int(5) not null AUTO_INCREMENT, firstname varchar(30), lastname varchar(30), class varchar(30), PRIMARY KEY (id) ) AUTO_INCREMENT = 1001; |
If you have already created a table and then want … Read more
suppose we have a column address in table employee.
1 2 3 4 5 | create table employee( id int(10) NOT NULL AUTO_INCREMENT, name varchar(40), department varchar(30), address varchar(50), PRIMARY KEY(id)); |
To see the description of table we use desc employee that will produce following result here we can see the description of table.
1 2 3 4 5 6 7 8 9 10 | mysql> desc employee; +------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | id | int(10) | NO | PRI | NULL | auto_increment | | name | varchar(40) | YES | | NULL | | | department | varchar(30) | YES | | NULL | | | address | varchar(50) | YES | | NULL | | +------------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) |
if we want to eliminate the column address from employee table then we use Drop command. Syntax:
1 | ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME; |
Drop query
1 | ALTER TABLE employee DROP COLUMN address; |
There are various pre defined function for data and time we will see them one by one. If you want to show current time then we use CURTIME().It returns time in HH:MM:SS string format
1 2 3 4 5 6 7 8 9 | mysql>SELECT CURTIME(); +-----------+ | CURTIME() | +-----------+ | 16:06:23 | +-----------+ 1 row in set (0.06 sec) |
below is HHMMSS in numeric format
1 2 3 4 5 6 7 | mysql> select CURTIME()+0; +-------------+ | CURTIME()+0 | +-------------+ | 162404 | +-------------+ 1 row in set (0.06 sec) |
To select current data we use CURDATE().It returns data in YYYY-MM-DD string format. … Read more
