MySQL Articles - Page 149 of 402

This kind of errors arise when you have used incorrect syntax. Let us see an example wherein we have created a table and the same error “1054” arise.Here’s the table −mysql> create table DemoTable689(    UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    UserName varchar(100),    UserLoginDate date(100) NOT NULL );This will produce the following output i.e. an error for incorrect syntax usage −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 '(100) NOT NULL )' at line 5Let us now ... Read More

To find missing value between two MySQL tables, use NOT IN. Let us first create a table −mysql> create table DemoTable1(Value int); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(1); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1 values(2); Query OK, 1 row affected (0.28 sec) mysql> insert into DemoTable1 values(5); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable1 values(6); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable1 values(8); Query OK, 1 row affected (0.16 sec)Display all records from ... Read More

Let us first create a table −mysql> create table DemoTable1(Name varchar(100)); Query OK, 0 rows affected (0.83 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values('John'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable1 values('Chris'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1 values('Robert'); Query OK, 1 row affected (0.13 sec)Display all records from the table using select statement −mysql> select *from DemoTable1;This will produce the following output −+--------+ | Name | +--------+ | John | | Chris | | Robert | +--------+ 3 ... Read More

Yes, an alternative for MySQL “WHERE.. OR” is using REGEXP.Let us first create a table −mysql> create table DemoTable684(EmployeeInformation text); Query OK, 0 rows affected (0.68 sec)Insert some records in the table using insert command −mysql> insert into DemoTable684 values('John 21 Google'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable684 values('Carol 23 Amazon'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable684 values('Carol 26 Flipkart'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable684 values('David 29 Microsoft'); Query OK, 1 row affected (0.18 sec)Display all records from the table using select statement −mysql> ... Read More

To select first 10 records, we can first order the records in ascending or descending order. With that, use LIMIT 10 to get only 10 records −select *from (select *from yourTableName ORDER BY yourColumnName ASC LIMIT 10)anyAliasName ORDER BY yourColumnName DESC;Let us first create a table −mysql> create table DemoTable683(Page int); Query OK, 0 rows affected (0.83 sec)Insert some records in the table using insert command −mysql> insert into DemoTable683 values(100); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable683 values(101); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable683 values(102); Query OK, 1 row affected ... Read More

Let us first create a table −mysql> create table DemoTable793(    StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    StudentName varchar(100) ); Query OK, 0 rows affected (0.81 sec)Insert some records in the table using insert command −mysql> insert into DemoTable793(StudentName) values('Adam'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable793(StudentName) values('Bob'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable793(StudentName) values(null); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable793(StudentName) values('Chris'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable793(StudentName) values('Robert'); Query OK, 1 row affected (1.03 sec)Display all records from ... Read More

To concatenate string with numbers, use the CONCAT() method. Let us first create a table −mysql> create table DemoTable682(    Name varchar(100),    Age int ); Query OK, 0 rows affected (0.49 sec)Insert some records in the table using insert command −mysql> insert into DemoTable682 values('John', 23); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable682 values('Chris', 21); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable682 values('David', 25); Query OK, 1 row affected (0.17 sec) Display all records from the table using select statement:Display all records from the table using select statement -mysql> select *from ... Read More

To select the date records between two dates, you need to use the BETWEEN keyword. Let us first create a table −mysql> create table DemoTable681(AdmissionDate datetime); Query OK, 0 rows affected (0.75 sec)Insert some records in the table using insert command −mysql> insert into DemoTable681 values('2019-01-21'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable681 values('2019-11-01'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable681 values('2019-12-03'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable681 values('2019-07-03'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable681 values('2019-02-04'); Query OK, 1 row affected (0.34 ... Read More

To return a column value only once in MySQL, let us first see an example and create a table −mysql> create table DemoTable680(Status varchar(100)); Query OK, 0 rows affected (0.75 sec)Insert some records in the table using insert command −mysql> insert into DemoTable680 values('Busy'); Query OK, 1 row affected (0.36 sec) mysql> insert into DemoTable680 values('At Work'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable680 values('Busy'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable680 values('Blocked'); Query OK, 1 row affected (0.28 sec) mysql> insert into DemoTable680 values('Offline'); Query OK, 1 row affected (0.67 sec) ... Read More

Let us first create a table −mysql> create table DemoTable679(FirstName varchar(100)); Query OK, 0 rows affected (0.44 sec)Insert some records in the table using insert command −mysql> insert into DemoTable679 values('John'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable679 values('Chris'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable679 values('David'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable679 values('Bob'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable679 values('Mike'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable679 values('Sam'); Query OK, 1 row affected (0.15 sec) mysql> insert ... Read More