MySQLi Articles - Page 15 of 341

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 More

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 More

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 More

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 More

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 More

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 More

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 More

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 More

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 More

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