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
-
Economics & Finance
Articles by AmitDiwan
Page 741 of 840
Create MySQL datetime column with default 0000-00-00?
To set default, you can use the DEFAULT keyword in MySQL. Let us first create a table −mysql> create table DemoTable -> ( -> EmployeeId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> EmployeeName varchar(20), -> EmployeeJoiningDate datetime default '0000-00-00 00:00:00' -> )ENGINE=MyISAM, AUTO_INCREMENT=100; Query OK, 0 rows affected (0.21 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(EmployeeName) values('Chris'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(EmployeeName) values('David'); Query OK, 1 row affected (0.04 sec) mysql> insert into DemoTable(EmployeeName) values('Mike'); Query OK, 1 row affected (0.03 sec) mysql> ...
Read MoreMySQL query to get a single value from position of comma-separated string?
For this, use SUBSTRING_INDEX(). Let us first create a table −mysql> create table DemoTable1615 -> ( -> ListOfSubject text -> ); Query OK, 0 rows affected (0.81 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1615 values('Python, Java, MySQL, MongoDB, C, C++, ASP.net'); Query OK, 1 row affected (0.19 sec)Display all records from the table using select statement −mysql> select * from DemoTable1615; This will produce the following output −+-----------------------------------------+ | ListOfSubject | +-----------------------------------------+ | Python, Java, MySQL, MongoDB, C, ...
Read MoreGet three records having higher value from MySQL
Let us first create a table −mysql> create table DemoTable1614 -> ( -> StudentName varchar(20), -> StudentScore int -> ); Query OK, 0 rows affected (0.78 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1614 values('Adam', 65); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1614 values('Chris', 89); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1614 values('Bob', 58); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable1614 values('Sam', 98); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1614 values('Mike', 87); Query OK, 1 ...
Read MoreIs there a function similar to Oracle's trunc (sysdate) in MySQL?
Yes, you can use DATE() to get only date part in MySQL and you can use CURDATE() to get the current date in MySQL.The current date is as follows −mysql> select curdate(); +------------+ | curdate() | +------------+ | 2019-10-20 | +------------+ 1 row in set (0.00 sec)Let us first create a table −mysql> create table DemoTable1613 -> ( -> PostingDate datetime -> ); Query OK, 0 rows affected (0.48 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1613 values('2019-10-20 12:02:45'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1613 values('2018-10-20 12:02:45'); ...
Read MoreGet the size of selected rows in MySQL
To get the size of selected rows, use CHAR_LENGTH(). Let us first create a table −mysql> create table DemoTable1612 -> ( -> FirstName varchar(20), -> LastName varchar(20) -> ); Query OK, 0 rows affected (0.87 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1612 values('David', 'Brown'); Query OK, 1 row affected (0.75 sec) mysql> insert into DemoTable1612 values('John', 'Smith'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable1612 values('Bob', 'Taylor'); Query OK, 1 row affected (0.13 sec)Display all records from the table using select statement −mysql> select * from DemoTable1612;This ...
Read MoreCombine multiple text records to one in MySQL
To combine multiple text records, use GROUP_CONCAT(). Let us first create a table −mysql> create table DemoTable1611 -> ( -> Value text -> ); Query OK, 0 rows affected (0.86 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1611 values('John'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1611 values('is'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1611 values('learning'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1611 values('Java'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1611 values('with'); Query OK, 1 row affected ...
Read MoreCustom sorting using two different columns in MySQL?
For this, use ORDER BY clause along with CASE statement. Let us first create a table −mysql> create table DemoTable1610 -> ( -> Marks int, -> Name varchar(20) -> ) ; Query OK, 0 rows affected (0.51 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1610 values(85, 'John'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1610 values(78, 'Carol'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1610 values(78, 'John'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable1610 values(85, 'Carol'); Query OK, 1 row affected ...
Read MoreHow to correctly use DELIMITER in a MySQL stored procedure?
The correct way is as follows −DELIMITER // CREATE PROCEDURE yourStoredProcedureName() BEGIN IF yourCondition then yourStatement1 ; else yourStatement2 ; END IF ; END // DELIMITER ;Let us now see an example and create a stored procedure −mysql> DELIMITER // mysql> CREATE PROCEDURE delimiter_demo() -> BEGIN -> IF 1 THEN -> SELECT "If condition will always true"; -> else -> select "No" ; -> END IF ; -> END -> // Query OK, 0 rows affected (0.17 sec) mysql> DELIMITER ;Now you can call the ...
Read MoreSelect the minimum value from the maximum values of two tables with a single MySQLnquery?
For this, you can use UNION in MySQL. Let us first create a table −mysql> create table DemoTable1 -> ( -> Value int -> ) -> ; Query OK, 0 rows affected (0.48 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(60); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1 values(78); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1 values(57); Query OK, 1 row affected (0.08 sec)Display all records from the table using select statement −mysql> select * from DemoTable1; This will produce the following ...
Read MoreWhat are the minimum MySQL user privileges to allow optimize and repair?
The select and insert statements are the minimum required MySQL user privileges to allow optimize and repair.You can use below syntax to give insert and select privileges to the user −grant insert, select on yourDatabaseName.* to 'yourUserName'@'localhost';At first, here is the query to create a user −mysql> create user 'Emma'@'localhost' identified by 'Emma123'; Query OK, 0 rows affected (0.26 sec)Here is the query to give grants for the above user −mysql> grant insert, select on web.* to 'Emma'@'localhost'; Query OK, 0 rows affected (0.21 sec)Here is the query to display all grants of the above user −mysql> show grants for ...
Read More