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
Articles by AmitDiwan
Page 821 of 839
Prevent having a zero value in a MySQL field?
Use trigger BEFORE INSERT on the table to prevent having a zero value in a MySQL field. Let us first create a table −mysql> create table DemoTable(Value int); Query OK, 0 rows affected (0.85 sec)Let us create a trigger to prevent having a zero value in a MySQL field −mysql> DELIMITER // mysql> create trigger preventing_to_insert_zero_value before insert on DemoTable for each row begin if(new.Value = 0) then SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'You can not provide 0 value'; END if; end // Query OK, 0 rows affected (0.34 sec) mysql> DELIMITER ...
Read MoreI used ‘from’ and ‘to’ with backticks as column titles in my database table. Now, how do I SELECT them?
At first, to use reserved words, it should be set with backticks like −`from` `to`Since you want to select the column names set with backtick as shown above, you need to implement the following −select `to`, `from` from yourTableName;Let us first create a table with column names as from and to with backticks −mysql> create table DemoTable720 ( `from` date, `to` date ); Query OK, 0 rows affected (0.69 sec)Insert some records in the table using insert command −mysql> insert into DemoTable720 values('2019-01-21', '2019-07-23'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable720 values('2017-11-01', '2018-01-31'); Query ...
Read MoreWhat does the slash mean in a MySQL query?
The slash means division ( /) in MySQL query. This can be used to divide two numbers. Here, we will see an example to divide numbers from two columns and display result in a new column.Let us first create a table −mysql> create table DemoTable719 ( FirstNumber int, SecondNumber int ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable719 values(20, 10); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable719 values(500, 50); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable719 values(400, 20); ...
Read MoreHow to find all records which are NOT in the set array with MySQL?
For this, you can use NOT IN() function.Let us first create a table −mysql> create table DemoTable718 ( Id int, FirstName varchar(100), Age int ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using insert command −mysql> insert into DemoTable718 values(101, 'Chris', 26); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable718 values(102, 'Robert', 24); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable718 values(103, 'David', 27); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable718 values(104, 'Mike', 28); Query OK, 1 row affected (0.19 sec) mysql> ...
Read MoreIs it possible to sort varchar data in ascending order that have both string and number values with MySQL?
For this, you can use ORDER BY IF(CAST()). Let us first create a table −mysql> create table DemoTable(EmployeeCode varchar(100)); Query OK, 0 rows affected (1.17 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('190'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('100'); Query OK, 1 row affected (0.30 sec) mysql> insert into DemoTable values('John'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values('120'); Query OK, 1 row affected (0.21 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+--------------+ ...
Read MoreHow do I create a random four-digit number in MySQL?
Let us first create a table −mysql> create table DemoTable717 ( UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, UserPassword int ); Query OK, 0 rows affected (0.81 sec)Insert some records in the table using insert command −mysql> insert into DemoTable717(UserPassword) values(1454343); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable717(UserPassword) values(674654); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable717(UserPassword) values(989883); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable717(UserPassword) values(909983); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> select *from DemoTable717;This will produce ...
Read MoreUpdate multiple values in a table with MySQL IF Statement
Let us first create a table −mysql> create table DemoTable716 ( Id varchar(100), Value1 int, Value2 int, Value3 int ); Query OK, 0 rows affected (0.65 sec)Insert some records in the table using insert command −mysql> insert into DemoTable716 values('100', 45, 86, 79); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable716 values('101', 67, 67, 99); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable716 values('102', 77, 57, 98); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable716 values('103', 45, 67, 92); Query OK, 1 row affected (0.16 sec)Display all ...
Read MoreOrder By Length of Column in MySQL
To order by length of column in MySQL, use ORDER BY LENGTH.Let us first create a table −mysql> create table DemoTable715 (UserMessage varchar(100)); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable715 values('Aw'); Query OK, 1 row affected (0.49 sec) mysql> insert into DemoTable715 values('Awe'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable715 values('A'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable715 values('Awes'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable715 values('Awesom'); Query OK, 1 row affected (0.16 sec) mysql> insert ...
Read MoreMySQL query to count rows in multiple tables
Let us first create a table −mysql> create table DemoTable1 (FirstName varchar(100)); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values('Bob'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1 values('James'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1 values('John'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1 values('David'); Query OK, 1 row affected (0.18 sec)Display all records from the table using select statement −mysql> select *from DemoTable1;This will produce the following output -+-----------+ | FirstName | +-----------+ | Bob ...
Read MoreCompare date strings in MySQL
To compare date strings, use STR_TO_DATE() from MySQL.Let us first create a table −mysql> create table DemoTable712 ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, ArrivalDate varchar(100) ); Query OK, 0 rows affected (0.65 sec)Insert some records in the table using insert command −mysql> insert into DemoTable712(ArrivalDate) values('10.01.2019'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable712(ArrivalDate) values('11.12.2018'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable712(ArrivalDate) values('01.11.2017'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable712(ArrivalDate) values('20.06.2016'); Query OK, 1 row affected (0.23 sec)Display all records from the table using select ...
Read More