Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
To set default field value, use the “default”. Let us first create a table −mysql> create table DemoTable -> ( -> Age int -> ); Query OK, 0 rows affected (0.58 sec)Here is the query to set default field value in MySQL −mysql> alter table DemoTable MODIFY Age int default 18; Query OK, 0 rows affected (0.25 sec) Records: 0 Duplicates: 0 Warnings: 0Now you can check the table description −mysql> desc DemoTable;OutputThis will produce the following output −+-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | Age ... Read More
Use SUBSTRING_INDEX to select everything before @ in an email-id −select substring_index(yourColumnName, '@', 1) from yourTableName;Let us first create a table −mysql> create table DemoTable -> ( -> EmployeeMailId varchar(100) -> ); Query OK, 0 rows affected (0.52 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Larry123@gmail.com'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('987Sam@hotmail.com'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('123456David_98@gmail.com'); Query OK, 1 row affected (0.20 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the ... Read More
To update an existing table, use UPDATE. With that, to set the current date, use the CURDATE() method −update yourTableName set yourCoumnName=CURDATE();Let us first create a table −mysql> create table DemoTable -> ( -> DueDate datetime -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-01-10') ; Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable values('2019-03-31'); Query OK, 1 row affected (0.18 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following output −+---------------------+ | DueDate ... Read More
For this, you can use aggregate function COUNT(*). Let us first create a table in let’s say database “web” −mysql> create table DemoTable1 -> ( -> Value int -> ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(10); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable1 values(20); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> select *from DemoTable1;OutputThis will produce the following output −+-------+ | Value | +-------+ | 10 | | ... Read More
To order an alphanumeric column with values like “100X, “2Z”, etc. use the ORDER BY. Let us first create a table −mysql> create table DemoTable -> ( -> StudentId varchar(100) -> ); Query OK, 0 rows affected (0.52 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2X'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable values('100Y'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values('100X'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('2Z'); Query OK, 1 row affected (0.14 sec) mysql> ... Read More
The local variable has the scope for only a set of statements or block of statement. Whenever a set of statements or block of statement has completed then local variable goes out of scope.For ExampleLocal variable can be used in stored procedure, function etc. It is used with the DECLARE keyword.The syntax is as follows to local variables.DECLARE yourVariableName dataType;The global variables or system variables has the scope across connections until server restart. It is set using GLOBAL keyword. The syntax is as follows −SET GLOBAL max_connections=value; OR SET @@global.max_connections=value;If you know port number then you can use system variable ... Read More
Let us first see the syntax of IF NOT IN in MySQL −if(yourVariableName NOT IN (yourValue1, yourValue2, ........N) ) then statement1 else statement2 endif Let us implement the above syntax to use IF NOT IN −mysql> DELIMITER // mysql> CREATE PROCEDURE IF_NOT_INDemo(IN value int) -> BEGIN -> if(value NOT IN (10, 20, 30) ) then -> select "Value Not Found"; -> else -> select "Value Found"; -> end if; -> END ... Read More
Let us first create a table −mysql> create table DemoTable -> ( -> Number int -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(9); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(4); Query OK, 1 row affected (0.31 sec) mysql> insert into DemoTable values(0); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(7); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(11); Query OK, 1 row affected (0.12 sec)Display all ... Read More
The TYPE=HEAP deprecated in newer MySQL versions. You can use ENGINE=HEAP instead of TYPE. Following is the syntax −ENGINE=HEAP;Let us first create a table. Here, we have set Engine=HEAP −mysql> create TEMPORARY table DemoTable -> ( -> StudentId int, -> StudentName varchar(30) -> )Engine = HEAP; Query OK, 0 rows affected (0.00 sec)Let us check the definition of table −mysql> show create table DemoTable;OutputThis will produce the following output −+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | DemoTable | CREATE TEMPORARY TABLE `DemoTable` (`StudentId` int(11) DEFAULT NULL, `StudentName` varchar(30) COLLATE utf8_unicode_ci DEFAULT ... Read More
Use ALTER command for this. Let us first create a table −mysql> create table DemoTable -> ( -> StudentName varchar(100) -> ); Query OK, 0 rows affected (0.46 sec)Here is the query to insert auto_increment −mysql> alter table DemoTable ADD COLUMN StudentId int NOT NULL; Query OK, 0 rows affected (0.50 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table DemoTable ADD PRIMARY KEY(StudentId); Query OK, 0 rows affected (1.23 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table DemoTable CHANGE StudentId StudentId int NOT NULL AUTO_INCREMENT; Query OK, 0 rows affected (2.20 sec) Records: 0 ... Read More