Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
In MySQL, you can achieve sp_help with the help of SHOW CREATE command.CASE 1 −For table, the syntax is as follows −SHOW CREATE TABLE yourTableName;CASE 2 −For stored procedure, the syntax is as follows −SHOW CREATE PROCEDURE yourProcedureName;Let us first create a table −mysql> create table DemoTable ( EmployeeId int NOT NULL AUTO_INCREMENT, EmployeeFirstName varchar(40) NOT NULL, EmployeeLastName varchar(40) NOT NULL, EmployeeAge int, EmployeeCountryName varchar(40), EmployeeCityName varchar(40), PRIMARY KEY(EmployeeId), UNIQUE KEY(EmployeeFirstName, EmployeeCityName) ); Query OK, 0 rows affected (0.97 sec)Let us implement the above syntax for our table −mysql> SHOW CREATE TABLE ... Read More
For this, you can use SUBSTRING_INDEX(). Let us first create a table −mysql> create table DemoTable ( Words TEXT ); Query OK, 0 rows affected (1.62 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Learn With Ease'); Query OK, 1 row affected (0.32 sec) mysql> insert into DemoTable values('Intro To MySQL'); Query OK, 1 row affected (0.35 sec) mysql> insert into DemoTable values('Deep Dive Using Java'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('C++ In Depth'); Query OK, 1 row affected (0.16 sec)Display all records from the table using select ... Read More
Let us first create a table −mysql> create table DemoTable ( ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY, ClientName varchar(40), ClientAge int, ClientCountryName varchar(40) ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(ClientName, ClientAge, ClientCountryName) values('Chris', 25, 'US'); Query OK, 1 row affected (0.33 sec) mysql> insert into DemoTable(ClientName, ClientAge, ClientCountryName) values('Bob', 55, 'UK'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable(ClientName, ClientAge, ClientCountryName) values('David', 45, 'AUS'); Query OK, 1 row affected (0.14 sec)Display all records from the table using select statement ... Read More
To make a date column null, use ALTER TABLE and MODIFY and set the date to NULL. Following is the syntax −alter table yourTableName modify column yourColumnName date NULL;Let us first create a table. Here, we have set the column as NOT NULL −mysql> create table DemoTable ( ShippingDate date NOT NULL ); Query OK, 0 rows affected (0.78 sec)Now, insert NULL value in the above table. An error would generate since we have set the column to be NOT NULL −mysql> insert into DemoTable values(null); ERROR 1048 (23000) − Column 'ShippingDate' cannot be nullNow, let us alter the ... Read More
The syntax is as follows to get the column names of a table −select column_name from information_schema.columns where table_schema='yourDatabaseName' and table_name=’yourTableName’;Let us first create a table −mysql> create table DemoTable ( EmployeeId int, EmployeeFirstName varchar(20), EmployeeLastName varchar(20), EmployeeAge int, EmployeeCountryName varchar(40), IsMarried tinyint(1), isActive ENUM('ACTIVE', 'INACTIVE') ); Query OK, 0 rows affected (0.65 sec)Following is the query to get the column names of a table. Here, we are fetching the column names of DemoTable −mysql> select column_name from information_schema.columns where table_schema='web' and table_name='DemoTable';This will produce the following output −+---------------------+ | ... Read More
To check if field of a table has NOT NULL property, you can use any of the two syntaxes. The first syntax is as follows −desc yourTableName;Following is the second syntax −select column_name, is_nullable from information_schema.columns where table_schema = ‘yourDatabaseName’ and table_name = 'yourTableName’;Let us first see an example and create a table −mysql> create table DemoTable ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentName varchar(40), StudentAge int NOT NULL, IsActiveStudent ENUM('ACTIVE", INACTIVE') NOT NULL, StudentCountryName varchar(40) ); Query OK, 0 rows affected (1.53 sec)The first syntax is as follows ... Read More
To prevent a combination of items from being inserted twice, alter the table and set UNIQUE for the column as shown in the below syntax −alter table yourTableName add constraint yourConstraintName unique(yourColumnName1, yourColumnName2, ....N);Let us first create a table −mysql> create table DemoTable ( Value1 int, Value2 int ); Query OK, 0 rows affected (0.47 sec)Here is the query to prevent a combination of items from being inserted twice −mysql> alter table DemoTable add constraint Value1_Value2_ConstraintKey unique(Value1, Value2); Query OK, 0 rows affected (0.80 sec) Records : 0 Duplicates : 0 Warnings : 0Insert some records in the ... Read More
For this, use BETWEEN -1 AND 1. Let us first create a table −mysql> create table DemoTable ( Value int ); Query OK, 0 rows affected (0.76 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(14); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values(15); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values(16); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values(17); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable values(18); Query OK, 1 row affected (0.09 sec)Display all records from ... Read More
For this, you need to use UNIQUE KEY for the column. Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, FirstName varchar(30), UNIQUE KEY(FirstName) ); Query OK, 0 rows affected (1.76 sec)Insert some records in the table using insert command. Now, we are also inserting duplicate records like “David”, but it won’t get inserted twice, since we have set the column as UNIQUE KEY −mysql> insert ignore into DemoTable(FirstName) values('Chris'); Query OK, 1 row affected (0.42 sec) mysql> insert ignore into DemoTable(FirstName) values('David'); Query OK, 1 row affected ... Read More
Let us first create a table −mysql> create table DemoTable ( ProductAmount int, PurchaseDate datetime ); Query OK, 0 rows affected (0.94 sec)Note − Let’s say the current date is 2010-09-15.Insert some records in the table using insert command −mysql> insert into DemoTable values(567, '2019-09-10'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values(1347, '2019-09-14'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values(2033, '2019-09-13'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values(1256, '2019-09-11'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(1000, '2019-09-16'); Query ... Read More