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
Database Articles
Page 333 of 547
Get only a single value from a specific MySQL row?
For this, use SELECT INTO variable with where clause. Let us first create a table −mysql> create table DemoTable1896 ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentName varchar(20), StudentMarks int ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1896(StudentName, StudentMarks) values('Chris', 56); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1896(StudentName, StudentMarks) values('David', 98); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1896(StudentName, StudentMarks) values('Mike', 89); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1896(StudentName, StudentMarks) values('Sam', ...
Read MoreWhat to assign to a MySQL column that must not be empty?
Define with NOT NULL, if a column must not be empty. Let us first create a table with one of the columns as NOT NULL −mysql> create table DemoTable1895 ( Id int NOT NULL, FirstName varchar(20), LastName varchar(20) NOT NULL ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1895 values(100, 'John', 'Smith'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1895 values(NULL, 'Chris', 'Brown'); ERROR 1048 (23000): Column 'Id' cannot be null mysql> insert into DemoTable1895 values(102, 'Carol', NULL); ERROR 1048 (23000): ...
Read MoreSet a MySQL field with the current date (UNIX_TIMESTAMP(now))
For this, use unix_timestamp(). Let us first create a table −mysql> create table DemoTable1894 ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, DueTime int ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1894 values(); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1894 values(); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1894 values(); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1894;This will produce the following output −+----+---------+ | ...
Read MoreCan you allow a regex match in a MySQL Select statement?
Yes, we can do regex match in a select statement −select yourColumnName from yourTableName where yourColumnName regexp '^yourValue';Let us first create a table −mysql> create table DemoTable1892 ( FirstName varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1892 values('John'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1892 values('Adam'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1892 values('Jace'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1892 values('Johny'); Query OK, 1 row affected (0.00 sec) mysql> insert into ...
Read MoreMySQL Stored procedure to declare two values and perform mathematical operation
Let us first create a stored procedure −mysql> delimiter // mysql> create procedure declare_demo_sp() begin declare Value1 int; declare Value2 int; set Value1=100; set Value2=2000; select Value1,Value2,Value1*Value2 as MultiplicationResult; end // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ;Call a stored procedure using CALL command −mysql> call declare_demo_sp();This will produce the following output −+--------+--------+----------------------+ | Value1 | Value2 | MultiplicationResult | +--------+--------+----------------------+ | 100 | 2000 | 200000 | +--------+--------+----------------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
Read MoreCheck if MySQL entry exists and if it does, how to overwrite other columns?
For this, use INSERT ON DUPLICATE KEY UPDATE command. Let us first create a table −mysql> create table DemoTable1891 ( FirstName varchar(20), UNIQUE KEY(FirstName) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1891 values('Chris') on duplicate key update FirstName='Robert'; Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1891 values('David') on duplicate key update FirstName='Robert'; Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1891 values('Chris') on duplicate key update FirstName='Robert'; Query OK, 2 rows affected (0.00 sec)Display all records from the table ...
Read MoreSelect data and set value to boolean based on timestamp column in MySQL
For this, use IF(). Let us first see the current date −mysql> select curdate(); +------------+ | curdate() | +------------+ | 2019-12-10 | +------------+ 1 row in set (0.00 sec)Let us first create a table −mysql> create table DemoTable1890 ( DueDate timestamp ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1890 values('2017-12-10'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1890 values('2021-12-10'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1890 values('2018-04-24'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1890 ...
Read MoreMySQL query to update all entries with md5 version of name?
For this, you can use MD5(). Let us first create a table −mysql> create table DemoTable1887 ( Password text, HashPassword text ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1887(Password) values('John@9089'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1887(Password) values('90987_Carol'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1887(Password) values('656464_David_4343'); Query OK, 1 row affected (0.00 sec)Display some records in the table using insert command −mysql> select * from DemoTable1887;This will produce the following output −+-------------------+--------------+ | Password ...
Read MoreHow do you get whether a column is a primary key in MySQL?
To get whether a column is a primary key, use COLUMN_NAME and COLUMN_KEY='PRI'. With that, the entire syntax is as follows −select column_name, case when column_key= 'PRI' then 'yourMessage1' else ''yourMessage2' end as anyAliasName from information_schema.columns where table_schema =database() and `table_name` = yourTableName order by `table_name`, ordinal_position;To understand the above syntax, let us create a table −mysql> create table DemoTable1886 ( Id int NOT NULL, FirstName varchar(20), LastName varchar(20), Age int, DateOfBirth datetime, Education varchar(40), PRIMARY KEY(Id) ); Query OK, 0 rows affected (0.00 sec)Here is the query to get whether ...
Read MoreHow to select row when column must satisfy multiple value in MySQL?
For this, you can use GROUP BY HAVING clause along with IN(). Let us first create a table −mysql> create table DemoTable1885 ( FirstName varchar(20), Subject varchar(50) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1885 values('John', 'MySQL'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1885 values('John', 'MongoDB'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1885 values('Carol', 'MySQL'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1885 values('David', 'Java'); Query OK, 1 row affected (0.00 sec)Display some ...
Read More