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
MySQL Articles
Page 32 of 355
Creating a Table in MySQL to set current date as default
Following is the syntax for creating a table and adding DEFAULT constraint to set default value −CREATE TABLE yourTableName ( yourColumnName1 dataType not null , yourColumnName2 dataType default anyValue, . . . N );;Let us create a table wherein we have set “employee_joining_date” with default constraint for current date as default −mysql> create table demo43 −> ( −> employee_id int not null auto_increment primary key, −> employee_name varchar(40) not null, −> employee_status varchar(60) default "NOT JOINED", −> employee_joining_date date default(CURRENT_DATE) −> ); Query OK, 0 rows affected (0.66 sec)Insert some records into the table with the help of insert command ...
Read MoreSELECT a row by subtracting dates in WHERE in MySQL?
For this, use TIMESTAMPDIFF(). Let us create a table −mysql> create table demo42 −> ( −> start_date datetime −> ); Query OK, 0 rows affected (0.77 sec)Insert some records into the table with the help of insert command −mysql> insert into demo42 values('2020-01-10 12:30:05'); Query OK, 1 row affected (0.11 sec) mysql> insert into demo42 values('2019-02-24 10:40:45'); Query OK, 1 row affected (0.11 sec) mysql> insert into demo42 values('2020-05-12 05:45:55'); Query OK, 1 row affected (0.17 sec) mysql> insert into demo42 values('2020-05-12 05:40:55'); Query OK, 1 row affected (0.15 sec) mysql> insert into demo42 values('2020-05-12 05:42:55'); ...
Read MoreMySQL regexp to display only records with strings or strings mixed with numbers. Ignore only the number records
For this, you can use REGEXP. Following is the syntax −select yourColumnName from yourTableName where yourColumnName REGEXP '[a−zA&minu;Z]';Let us create a table −mysql> create table demo41 −> ( −> name varchar(40) −> ); Query OK, 0 rows affected (0.64 sec)Insert some records into the table with the help of insert command −mysql> insert into demo41 values('John Smith34') −> ; Query OK, 1 row affected (0.13 sec) mysql> insert into demo41 values('John Smith'); Query OK, 1 row affected (0.11 sec) mysql> insert into demo41 values('9234John Smith'); Query OK, 1 row affected (0.14 sec) mysql> insert into demo41 values('john smith'); Query OK, ...
Read MoreHow to select next row pagination in MySQL?
For this, use the LIMIT concept. Let us create a table −mysql> create table demo40 −> ( −> id int not null auto_increment primary key, −> name varchar(40) −> ); Query OK, 0 rows affected (1.73 sec)Insert some records into the table with the help of insert command −mysql> insert into demo40(name) values('Chris'); Query OK, 1 row affected (0.23 sec) mysql> insert into demo40(name) values('David'); Query OK, 1 row affected (0.12 sec) mysql> insert into demo40(name) values('Mike'); Query OK, 1 row affected (0.10 sec) mysql> insert into demo40(name) values('Sam'); Query OK, 1 row affected (0.19 sec) ...
Read MoreHow to select rows with condition via concatenate in MySQL?
For this, you can use CONCAT_WS(). Let us create a table −mysql> create table demo38 −> ( −> user_id int, −> user_first_name varchar(20), −> user_last_name varchar(20), −> user_date_of_birth date −> ); Query OK, 0 rows affected (1.70 sec)Insert some records into the table with the help of insert command −mysql> insert into demo38 values(10, 'John', 'Smith', '1990−10−01'); Query OK, 1 row affected (0.14 sec) mysql> insert into demo38 values(11, 'David', 'Miller', '1994−01−21'); Query OK, 1 row affected (0.13 sec) mysql> insert into demo38 values(11, 'John', 'Doe', '1992−02−01'); Query OK, 1 row affected (0.13 sec) mysql> insert into ...
Read MoreUsing Prepared statement correctly with WHERE condition in case of any value in MySQL Java
For this, you can use PrepareStatement in Java. Following is the syntax −String anyVariableName="select yourColumnName from yourTableName where name = ?"; PreparedStatement ps = (PreparedStatement) con.prepareStatement(yourVariableName); ps.setString(yourColumnIndex, yourValue);Let us create a table −mysql> create table demo37 −> ( −> id int not null auto_increment primary key, −> name varchar(200) −> ); Query OK, 0 rows affected (2.46 sec)Insert some records into the table with the help of insert command −mysql> insert into demo37(name) values('John'); Query OK, 1 row affected (0.09 sec) mysql> insert into demo37(name) values('Bob'); Query OK, 1 row affected (0.08 sec) mysql> insert into demo37(name) values('John'); Query OK, ...
Read MoreHow to select between/before/after dates in MySQL conditionally?
Following is the syntax −select *from yourTableName where yourColumnName1 < yourValue1 AND (yourColumnName2 > yourValue2 OR yourColumnName2 is null);Let us create a table −mysql> create table demo35 −> ( −> id int NOT NULL AUTO_INCREMENT PRIMARY KEY, −> joining_date date, −> relieving_date date −> ); Query OK, 0 rows affected (3.88 sec)Insert some records into the table with the help of insert command −mysql> insert into demo35(joining_date, relieving_date) values('2020−01−10', '2020−07−11'); Query OK, 1 row affected (0.15 sec) mysql> insert into demo35(joining_date, relieving_date) values('2020−05−07', '2020−12−08'); Query OK, 1 row affected (0.17 sec) mysql> insert into demo35(joining_date, relieving_date) values('2020−04−11', '2020−09−18'); Query OK, ...
Read MoreExtract Numeric Date Value from Date Format in MySQL?
For this, use UNIX_TIMESTAMP(). Following is the syntax −select UNIX_TIMESTAMP(STR_TO_DATE(yourColumnName, "%d-%b-%y")) as anyAliasName from yourTableName;Let us create a table −mysql> create table demo34 −> ( −> datevalue varchar(40) −> ); Query OK, 0 rows affected (1.51 sec)Insert some records into the table with the help of insert command −mysql> insert into demo34 values('31−Jan−19'); Query OK, 1 row affected (0.13 sec) mysql> insert into demo34 values('03−Mar−21'); Query OK, 1 row affected (0.12 sec) mysql> insert into demo34 values('13−Jun−20'); Query OK, 1 row affected (0.11 sec)Display records from the table using select statement −mysql> select *from demo34;This will produce the ...
Read MoreCreate a new table in MySQL with specific options with DEFAULT?
For this, use DEFAULT keyword after the column data type.Let us create a table −mysql> create table demo33 −> ( −> id int not null auto_increment primary key, −> name varchar(20) not null, −> start_date date default(current_date), −> end_date date default NULL, −> category enum('Good', 'Medium', 'Low') default 'Low' −> ); Query OK, 0 rows affected (2.32 sec)Insert some records into the table with the help of insert command −mysql> insert into demo33(name) values('John'); Query OK, 1 row affected (0.15 sec) mysql> insert into demo33(name, end_date, category) values('David', '2020−12−21', 'Medium'); Query OK, 1 row affected (0.09 sec) mysql> ...
Read MoreHow to change a table (create/alter) so that the calculated "Average score" field is shown when querying the entire table without using MySQL INSERT, UPDATE?
Following is the syntax −alter table yourTableName add column yourColumnName yourDataType generated always as ((yourColumName1+yourColumName2+....N) / N) virtual;Let us create a table −mysql> create table demo32 −> ( −> value1 int, −> value2 int −> ); Query OK, 0 rows affected (1.42 sec)Insert some records into the table with the help of insert command −mysql> insert into demo32 values(30, 60); Query OK, 1 row affected (0.16 sec) mysql> insert into demo32 values(20, 40); Query OK, 1 row affected (0.15 sec) mysql> insert into demo32 values(35, 35); Query OK, 1 row affected (0.08 sec)Display records from the table using ...
Read More