Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
MySQL Articles
Page 53 of 355
MySQL SELECT last few days?
To select last few days, use DATE_ADD() function in MySQL. The syntax is as follows −select date_add(curdate(), interval - anyIntgegerValue day);Or you can DATE_SUB() from MySQL.select date_sub(curdate(), interval anyIntgegerValue day);Or you can use the following syntax −select curdate() - interval anyIntgegerValue day;Here is the example of all syntaxes shown above to select last few days.Case 1 − Use of DATE_ADD() functionThe query is as follows −mysql> select date_add(curdate(), interval -6 day);Here is the output −+-------------------------------------+ | date_add(curdate(), interval -6 day) | +-------------------------------------+ | 2018-11-20 | +-------------------------------------+ 1 row ...
Read MoreHow to remove leading and trailing whitespace from a MySQL field value?
We can remove the leading and trailing whitespaces from MySQL with the help of trim() function.The following is the syntax.mysql> SELECT TRIM(' AnyStringWithWhitespaces ');Let us now implement the above syntax in the below query.mysql> SELECT TRIM(' Leading And Trailing whitespaces Demo '); Here is the output that removes the whitespaces.+---------------------------------------+ | TRIM(' Leading And Trailing whitespaces Demo ') | +---------------------------------------+ | Leading And Trailing whitespaces Demo | +---------------------------------------+ 1 row in set (0.00 sec)Let us now see another way to remove the leading and trailing whitespaces.Firstly, let us create a new table.mysql> create table TrimDemo2 -> ( -> name varchar(200) ...
Read MoreDifferent methods to check if a MySQL table exist?
Let us see some of the ways to check if a table exist in MySQL or not.Using SHOWThe SHOW command displays all the tables.SHOW tables;Here is the output.+--------------------------+ | Tables_in_business | +--------------------------+ | addcheckconstraintdemo | | addcolumntable | | addconstraintdemo | | alphademo | | autoincrement | | autoincrementtable | | backticksymbol | | bookindexes | | chardemo ...
Read MoreHow do I know if a MySQL table is using myISAM or InnoDB Engine?
To know a MySQL table is using MyISAM or InnoDB engine, you can use the command show status table. The syntax is as follows −SHOW TABLE STATUS from yourDatabaseName LIKE ‘yourTableName’.The above syntax tells about the specific table engine. Now you can apply the above syntax to know whether the MySQL table engine is using MyISAM or InnoDB.Here, I have database ‘business’ and table ‘student’. The query is as follows −mysql> show table status from business like 'student';The following displays the engine our table ‘student’ is using −+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | ...
Read MoreHow to insert current date/time in MySQL?
To insert current date/ time in MySQL, use the now() function. Let us now see an example.At first, we will create a table. The CREATE command is used to create a table.mysql > create table CurrentDateTime -> ( -> CurrentTime datetime -> ); Query OK, 0 rows affected (1.14 sec)Syntax to insert the current date/time with the help of insert command and now()mysql> insert into CurrentDateTime values(now()); Query OK, 1 row affected (0.11 sec)To check that the current date/ time is inserted in the table or not, use the select command.mysql> select *from CurrentDateTime; The following is the output that ...
Read MoreSimulating MySQL's ORDER BY FIELD() in PostgreSQL?
The following is the process to simulate MySQL’s ORDER BY FIELD() in PostgreSQL.We have used an Online Compiler to run PostgreSQL.Let us now see what we did above to get the output.Firstly, we created a table.create table PostgreOrderIdDemo ( countryName varchar(20) );Inserted records with the help of INSERT command.insert into PostgreOrderIdDemo values('IND'); insert into PostgreOrderIdDemo values('US'); insert into PostgreOrderIdDemo values('UK');The following is the syntax in PostgreSQL.SELECT * FROM yourTableName ORDER BY CASE WHEN columnName='IND' THEN 1 WHEN columnName='US' THEN 2 WHEN columnName='UK' THEN 3 ELSE 0 END, columnName;ExampleLet us now use the above syntax to get the output.SELECT * ...
Read MoreHow to store usernames and passwords safely in MySQL database?
To store username and passwords safely in MySQL database, we can use MD5().Let us see an example. First, we will create a table. The CREATE command is used to create a table.mysql> create table UserNameAndPasswordDemo - > ( - > U_Id int(10) unsigned NOT NULL AUTO_INCREMENT, - > UserId varchar(255) DEFAULT NULL, - > UserPassword varchar(255) DEFAULT NULL, - > primary key(U_Id), - > UNIQUE KEY `UserId` (`UserId`) - > ); Query OK, 0 rows affected (0.61 sec)Inserting records and safely storing passwords with the help of MD5().mysql> INSERT INTO UserNameAndPasswordDemo(UserId, UserPassword) VALUES ('John@gg.com', MD5('john123')); Query OK, 1 row affected ...
Read MoreRemove new line characters from rows in MySQL?
The Trim() function is used to remove new line characters from data rows in MySQL. Let us see an example. First, we will create a table. The CREATE command is used to create a table.mysql> create table tblDemotrail - > ( - > id int, - > name varchar(100) - > ); Query OK, 0 rows affected (0.57 sec)Let us now insert some records.mysql> insert into tblDemotrail values(1, 'John '); Query OK, 1 row affected (0.15 sec) mysql> insert into tblDemotrail values(2, ' Carol'); Query OK, 1 row affected (0.32 sec) mysql> insert into tblDemotrail values(3, ' Sam ...
Read MoreShow constraints on table command in MySQL?
You can show constraints on tables with the help of SHOW command. The syntax is as follows −show create table yourTableName;The above command will show all constraints with table ENGINE. Using this, you can even see all the column names and corresponding data types.To understand the above MySQL statement, let us first create a table −mysql> create table ShowConstraintsDemo -> ( -> BookId int not null, -> BookName varchar(200) not null, -> BookAuthor varchar(200) Unique not null, -> Primary key(BookId, BookName) -> ); Query OK, 0 rows affected (1.04 sec)Now you can apply the above syntax in order ...
Read MoreWhat do column flags mean in MySQL Workbench?
In MySQL Workbench, column flags can be used with a column to maintain integrity. The column flags are as follows −PK − Primary KeyNN − NOT NULLBIN − BinaryUN − UnsignedUQ − UniqueZF − Zero FilledG − Generate ColumnAI − Auto IncrementLet us learn about them one by one −PKThis stands for the primary key. It can be used to make the column as a primary key.NNIt is for NOT NULL. Used to enforce the column that it will not insert a NULL value.BINThis stands for Binary. This can be used to store data as a binary string.UNIt is for ...
Read More