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 147 of 355
How to get a specific column record from SELECT query in MySQL?
Let us first create a table −mysql> create table DemoTable1837 ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentName varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1837(StudentName) values('Chris'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1837(StudentName) values('David'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1837(StudentName) values('Bob'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1837(StudentName) values('Mike'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement ...
Read MoreFix MySQL Error #1064 - You have an error in your SQL syntax... near 'TYPE=MyISAM?
This error occurs when we use TYPE for ENGINE NAME. The error is as follows −mysql> create table DemoTable1836 ( ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY, ClientName varchar(20) )Type=MyISAM AUTO_INCREMENT=1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Type=MyISAM AUTO_INCREMENT=1' at line 5Now, in MySQL 8, you can use ENGINE instead of Type. Let us first create a table −mysql> create table DemoTable1836 ( ClientId int NOT ...
Read MoreHow to fetch random rows in MySQL with comma separated values?
To fetch random rows in MySQL, use ORDER BY RAND(). Let us first create a table −mysql> create table DemoTable1835 ( ListOfIds varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1835 values('10, 20, 30'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1835 values('70, 80, 90'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1835 values('45, 67, 89'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1835 values('98, 96, 49'); Query OK, 1 row affected (0.00 ...
Read MoreSelect rows containing a string in a specific column with MATCH and AGAINST in MySQL
Let us first create a table −mysql> create table DemoTable1833 ( Name varchar(20) ); Query OK, 0 rows affected (0.00 sec)Alter table −Mysql> alter table DemoTable1833 ADD FULLTEXT(Name); Query OK, 0 rows affected, 1 warning (0.00 sec) Records: 0 Duplicates: 0 Warnings: 1Insert some records in the table using insert command −mysql> insert into DemoTable1833 values('John Doe'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1833 values('Adam Smith'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1833 values('Chris Brown'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1833 ...
Read MoreInsert JSON into a MySQL table?
Let us create a table and set a column value with type JSONmysql> create table DemoTable1832 ( ListOfNames JSON ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1832(ListOfNames) values('["Sam", "Mike", "Carol"]'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1832(ListOfNames) values('["David", "Bob"]'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1832(ListOfNames) values('["Adam", "John", "Sam"]'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1832; This will produce the following ...
Read MoreSet custom Auto Increment with ZEROFILL in MySQL
Let us first create a table. Here. We have set UserId column with ZEROFILL and AUTO_INCREMENTmysql> create table DemoTable1831 ( UserId int(7) zerofill auto_increment, PRIMARY KEY(UserId) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1831 values(101); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1831 values(); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1831 values(); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1831 values(); Query OK, 1 row affected (0.00 sec)Display all records ...
Read MoreMySQL query to return the count of only NO values from corresponding column value
Let us first create a table −mysql> create table DemoTable1829 ( Name varchar(20), isTopper ENUM('YES', 'NO') ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1829 values('Chris', 'yes'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1829 values('David', 'yes'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1829 values('Mike', 'no'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1829 values('David', 'yes'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement ...
Read MoreIs it possible to add a set of elements in one cell with MySQL?
To add a set of elements in a single cell, use the concept of JSON. Let us first create a table −mysql> create table DemoTable1828 ( EmployeeId int, EmployeeRecords JSON ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1828 values(1, '[{"EmployeeName":"Chris", "EmployeeAge":29}, {"EmployeeName":"David", "EmployeeAge":27}]'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1828 values(2, '[{"EmployeeName":"John", "EmployeeAge":36}, {"EmployeeName":"Mike", "EmployeeAge":32}]'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1828; This ...
Read MorePerform mathematical operations in a MySQL Stored Procedure?
Let us create a stored procedure. Here, we are calculating amount*quantity i.e. implementing mathematical operations −mysql> delimiter // mysql> create procedure calculation_proc(amount int,quantity int) begin select amount,quantity,(amount*quantity) as Total; end // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ;Now you can call a stored procedure using call command −mysql> call calculation_proc(250,3);This will produce the following output −+--------+----------+-------+ | amount | quantity | Total | +--------+----------+-------+ | 250 | 3 | 750 | +--------+----------+-------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
Read MoreHow to search for ^ character in a MySQL table?
To search for ^ character, use the LIKE operator as in the below syntax −select table_schema, table_name, column_name from information_schema.columns where column_name like '%^%';Let us first create a table −mysql> create table DemoTable1826 ( `^` varchar(20), Name varchar(20), `^Age` int ); Query OK, 0 rows affected (0.00 sec)Here is the query to search for ^ character in a MySQL tablemysql> select table_schema, table_name, column_name from information_schema.columns where column_name like '%^%';This will produce the following output −+--------------+---------------+-------------+ | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | +--------------+---------------+-------------+ | ...
Read More