MySQL Articles - Page 96 of 439

To get only month name, the syntax is as follows −select date_format(yourColumnName, '%M %Y') from yourTableName;Let us first create a table −mysql> create table DemoTable1619     -> (     -> ArrivalTime datetime     -> ); Query OK, 0 rows affected (0.45 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1619 values(now()); Query OK, 1 row affected (0.40 sec) mysql> insert into DemoTable1619 values(curdate()); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1619 values('2019-12-31'); Query OK, 1 row affected (0.17 sec)Display all records from the table using select statement −mysql> select * ... Read More

For quicker querying, use MySQL IN() because it uses indexing internally. Let us first create a table −mysql> create table DemoTable1618     -> (     -> ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY,     -> ClientName varchar(20),     -> ClientEmailId varchar(30)     -> ); Query OK, 0 rows affected (1.53 sec)Insert some records in the table using insert command:mysql> insert into DemoTable1618(ClientName, ClientEmailId) values('Chris Brown', 'Brown323@gmail.com'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1618(ClientName, ClientEmailId) values('David Miller', 'MillerDavid@gmail.com'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1618(ClientName, ClientEmailId) values('John Doe', ... Read More

Let us first create a table −mysql> create table DemoTable1368     -> (     -> ClientId int,     -> ClientName varchar(20)     -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1368 values(101, 'Adam'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1368 values(102, 'Bob'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1368 values(103, 'John'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1368 values(104, 'Sam'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1368 values(105, ... Read More

To display none of the rows and columns, use SELECT NULL and FALSE as in the below syntax −select null from yourTableName where false;Let us first create a table −mysql> create table DemoTable1367     -> (     -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,     -> FirstName varchar(20)     -> ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1367(FirstName) values('Chris'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1367(FirstName) values('David'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1367(FirstName) values('Bob'); ... Read More

For this, use SUBSTR() as in the below syntax −select * from yourTableName where substr(yourColumnName, 2, 1 ) = ' ';Let us first create a table −mysql> create table DemoTable1365     -> (     -> Value varchar(20)     -> ); Query OK, 0 rows affected (0.66 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1365 values('9756757474'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1365 values('3 45322333'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable1365 values('8974646363'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1365 values('9 ... Read More

Let us first create a table. Here, we have set date as VARCHAR −mysql> create table DemoTable1364     -> (     -> ShippingDate varchar(100)     -> ); Query OK, 0 rows affected (0.87 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1364 values('01-09-2019 12:34:55'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1364 values('01-07-2018 17:10:00'); Query OK, 1 row affected (0.37 sec) mysql> insert into DemoTable1364 values('24-09-2019 10:31:22'); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable1364 values('20-09-2018 13:00:00'); Query OK, 1 row affected (0.13 sec)Display all records from ... Read More

Let us first create a table −mysql> create table DemoTable1363     -> (     -> StudentId varchar(20)     -> ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1363 values('901'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1363 values('702'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1363 values('901_John_Doe'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1363 values('1001_Carol_Taylor'); Query OK, 1 row affected (0.26 sec)Display all records from the table using select statement −mysql> select * from DemoTable1363;This will ... Read More

For this, the syntax is as follows −revoke show databases on *.* from 'yourUserName'@'yourHostName';Let us display all usernames along with host name −mysql> select user, host from MySQL.user;This will produce the following output −+------------------+-----------+ | user             | host      | +------------------+-----------+ | Bob              | %         | | Charlie          | %         | | Robert           | %         | | User2            | %   ... Read More

Use MySQL DECLARE for variables in stored procedure −DECLARE anyVariableName int DEFAULT anyValue;Let us implement the above syntax in order to create variables in stored procedure −mysql> DELIMITER // mysql> CREATE PROCEDURE variable_Demo()     -> BEGIN     -> DECLARE lastInsertedId int DEFAULT -1;     -> select lastInsertedId;     -> set @providedLastId=10001;     -> select @providedLastId;     -> END     -> // Query OK, 0 rows affected (0.32 sec) mysql> DELIMITER ;Now you can call the above stored procedure using CALL command −mysql> call variable_Demo();This will produce the following output −+----------------+ | lastInsertedId | ... Read More

Let us first create a table −mysql> create table DemoTable1362     -> (     -> ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY,     -> ClientName varchar(40)     -> ); Query OK, 0 rows affected (0.67 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1362(ClientName) values('Chris'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1362(ClientName) values(' '); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable1362(ClientName) values('Bob'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1362(ClientName) values(' '); Query OK, 1 row affected (0.12 sec) mysql> insert ... Read More