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 258 of 355
MySQL automatic conversion on lowercase? Is this possible?
Yes, it is possible with triggers. You can create trigger for automatic conversion on lower case. Let us first create a table −mysql> create table DemoTable ( StudentSubject text ); Query OK, 0 rows affected (0.61 sec)Let us create a trigger for automatic conversion on lower case −mysql> CREATE TRIGGER lowerCaseOnInsertDemo BEFORE INSERT ON DemoTable FOR EACH ROW SET NEW.StudentSubject = LOWER(NEW.StudentSubject); Query OK, 0 rows affected (0.21 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('MOngoDb'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('JaVA'); Query OK, ...
Read MoreHow to apply NOW() to timestamps field in MySQL Workbench?
Let us first create a table −create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, ShippingDate timestamp );Insert some records in the table using insert command. Here, we have included the current date with NOW() −INSERT INTO DemoTable(ShippingDate) VALUES(now());Display all records from the table using select statement −SELECT *FROM DemoTable;OutputFollowing is the screenshot of query in MySQL workbench to set NOW() to timestamp field “ShippingDate”. The query also displays the output below −
Read MoreCheck if table exists in MySQL and display the warning if it exists?
To check if table exists, use the following syntax −CREATE TABLE IF NOT EXISTS yourTableName ( yourColumnName1 dataType, . . . . N );Here, we will try to create a table that already exists and then it will produce a warning message “Table already exists”. Let us first create a table. This table already exists −mysql> CREATE TABLE IF NOT EXISTS DemoTable ( Id int ); Query OK, 0 rows affected, 1 warning (0.06 sec)The warning message is as follows −mysql> show warnings;Output+-------+------+-------------------------------------+ | Level | Code | Message ...
Read MoreHow to work multiple AND conditions in MySQL?
To work with multiple AND conditions in MySQL, following is the syntax −select *from yourTableName where yourColumnName1=yourValue1 and yourColumnName2=yourValue2 and yourColumnName3=yourValue3;Let us first create a table −mysql> create table DemoTable ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentName varchar(20), StudentAge int, StudentCountryName varchar(40) ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(StudentName, StudentAge, StudentCountryName) values('John', 23, 'US'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(StudentName, StudentAge, StudentCountryName) values('Carol', 21, 'UK'); Query OK, 1 row affected (0.15 sec) mysql> insert ...
Read MoreConcatenate two tables in MySQL with a condition?
To concatenate two tables, UNION ALL in MySQL. Let us create a table −mysql> create table DemoTable1 ( Id int, FirstName varchar(20) ); Query OK, 0 rows affected (1.52 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(10, 'John'); Query OK, 1 row affected (0.28 sec) mysql> insert into DemoTable1 values(20, 'Carol'); Query OK, 1 row affected (0.28 sec)Display all records from the table using select statement −mysql> select *from DemoTable1;Output+------+-----------+ | Id | FirstName | +------+-----------+ | 10 | John | ...
Read MoreHow to search between columns in MySQL?
Use BETWEEN clause to search between columns. Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Score1 int, Score2 int ); Query OK, 0 rows affected (0.78 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Score1, Score2) values(45, 65); Query OK, 1 row affected (0.26 sec) mysql> insert into DemoTable(Score1, Score2) values(450, 680); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(Score1, Score2) values(800, 900); Query OK, 1 row affected (0.17 sec)Display all records from the table using select statement ...
Read MoreDisplay dates after NOW() + 10 days from a MySQL table?
You can use DATE_ADD() function with where clause for this. Let us first create a table −mysql> create table DemoTable ( ShippingDate date ); Query OK, 0 rows affected (0.54 sec)Note : The current date and time is as follows, we found using NOW() −mysql> select now(); +-----------------------+ | now() | +-----------------------+ | 2019-06-04 20 :43 :57 | +-----------------------+ 1 row in set (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-06-16'); Query OK, 1 ...
Read MoreRemove last char if it's a specific character in MySQL?
To remove last char if it is a specific character then use SUBSTRING(). Let us first create a table −mysql> create table DemoTable ( SubjectName varchar(100) ); Query OK, 0 rows affected (0.47 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('MySQL'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('MongoDB?'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('Java?'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('C'); Query OK, 1 row affected (0.16 sec)Display all records from the table using select ...
Read MoreMySQL query to decrease value by 10 for a specific value?
Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Score int ); Query OK, 0 rows affected (0.89 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Score) values(67); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(Score) values(78); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable(Score) values(90); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(Score) values(56); Query OK, 1 row affected (0.13 sec)Display all records from the table using select statement −mysql> select *from DemoTable;Output+----+-------+ ...
Read MoreConvert the column to a case-sensitive collation in MySQL?
For this, you can use COLLATE. Following is the syntax −select *from yourTableName where yourColumnName LIKE yourValue COLLATE utf8_bin;Let us first create a table −mysql> create table DemoTable ( LastName varchar(100) ); Query OK, 0 rows affected (0.51 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Brown'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values('BROWN'); Query OK, 1 row affected (0.29 sec) mysql> insert into DemoTable values('brown'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('BRoWN'); Query OK, 1 row affected (0.14 sec)Display all records ...
Read More