MySQL Articles - Page 74 of 439

For this, you can use GROUP_CONCAT(). Let us first create a table −mysql> create table DemoTable1561    -> (    -> StudentName varchar(20),    -> Title text    -> ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1561 values('Adam', 'Learning Java'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1561 values('Bob', 'Learning C'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1561 values('Adam', 'Learning Spring and Hibernate Framework'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable1561 values('Carol', 'Learning MySQL from basic'); Query ... Read More

For same results, do not use ! operator. The NOT keyword is already provided by MySQL. Let us first create a table −mysql> create table DemoTable1560    -> (    -> Value1 int    -> ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1560 values(0); Query OK, 1 row affected (0.16 sec)Display all records from the table using select statement −mysql> select * from DemoTable1560;This will produce the following output −+--------+ | Value1 | +--------+ |      0 | +--------+ 1 row in set (0.00 sec)Here is the ... Read More

You can’t use the index as a column name because it is a reserved word. For this, you need to use backticks around the column name.If you will use a reserved word as the column name, you can see the following error−mysql> create table DemoTable    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> index int    -> )ENGINE=MyISAM; 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 'int )ENGINE=MyISAM' at line 4Let us first ... Read More

To order results of a query randomly, use ORDER BY RAND(). The syntax is as follows −select * from DemoTable1559 where yourColumnName IN(yourValue1, yourValue2, ....N) order by rand() limit yourLimitValue;Let us first create a table −mysql> create table DemoTable1559    -> (    -> EmployeeId int,    -> EmployeeName varchar(20),    -> EmployeeAge int    -> ); Query OK, 0 rows affected (1.22 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1559 values(101, 'Bob', 28); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1559 values(102, 'Robert', 26); Query OK, 1 row affected (0.16 sec) ... Read More

To re-format datetime in MySQL, you can use DATE_FORMAT(). MySQL gives in the format yyyy-mm-dd.Let us first create a table −mysql> create table DemoTable1558    -> (    -> EmployeeJoiningDate datetime    -> ); Query OK, 0 rows affected (1.10 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1558 values(CURDATE()); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1558 values(NOW()); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1558 values('2018-01-10'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1558 values('2017-12-31'); Query OK, 1 row affected (0.18 sec)Display all records from ... Read More

To shuffle elements, you need to use ORDER BY RAND(). Let us first create a table −mysql> create table DemoTable1557    -> (    -> SubjectId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> SubjectName varchar(20)    -> ); Query OK, 0 rows affected (0.91 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1557(SubjectName) values('MySQL'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1557(SubjectName) values('MongoDB'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1557(SubjectName) values('Java'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1557(SubjectName) values('C'); Query OK, 1 row affected ... Read More

Let us first create a table −mysql> create table DemoTable1    -> (    -> Id int,    -> FirstName varchar(20)    -> ); Query OK, 0 rows affected (0.52 sec)Here is the query to create second table −mysql> create table DemoTable2    -> (    -> EmployeeId int,    -> EmployeeName varchar(20)    -> ); Query OK, 0 rows affected (0.51 sec)Let us now create a trigger to update MySQL table on insert command −mysql>  DELIMITER // mysql>     CREATE TRIGGER updateDemoOnInsert    ->      AFTER INSERT ON DemoTable2    ->         FOR EACH ... Read More

Use FULLTEXT search to quickly search for a string. Let us first create a table −mysql> create table DemoTable1554    -> (    -> Title text    -> ); Query OK, 0 rows affected (0.63 sec)Here is the query to create full text search −mysql> create fulltext index faster_title on DemoTable1554(Title); Query OK, 0 rows affected, 1 warning (7.09 sec) Records: 0  Duplicates: 0  Warnings: 1Insert some records in the table using insert command −mysql> insert into DemoTable1554 values('John is working on MySQL database'); Query OK, 1 row affected (0.26 sec) mysql> insert into DemoTable1554 values('Adam Smith is working on ... Read More

For this, you can use UNIQUE KEY. Let us first create a table −mysql> create table DemoTable1553    -> (    -> EmployeeId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> EmployeeName varchar(20),    -> EmployeeSalary int    -> ); Query OK, 0 rows affected (0.47 sec)Here is the query to add unique key −mysql> alter table DemoTable1553 add unique(EmployeeSalary); Query OK, 0 rows affected (0.53 sec) Records: 0  Duplicates: 0  Warnings: 0Insert some records in the table using insert command −mysql> insert into DemoTable1553(EmployeeName, EmployeeSalary) values('Chris', 45000); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable1553(EmployeeName, EmployeeSalary) ... Read More

Use single quotes on string input value. If there is an identifier like table name or column name, then do not use single quotes (use backticks).Let us first create a table −mysql> create table DemoTable1552    -> (    -> `key` int,    -> Name varchar(20)    -> ); Query OK, 0 rows affected (0.82 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1552 values(101, 'Chris'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1552 values(102, 'David'); Query OK, 1 row affected (0.28 sec) mysql> insert into DemoTable1552 values(103, 'Mike'); Query OK, 1 row ... Read More