To create a table with a space in the table name in MySQL, you must use backticks otherwise you will get an error.Let us first see what error will arise by creating a table with a space in the name i.e. “Demo Table” table name below:mysql> create table Demo Table (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    EmployeeFirstName varchar(20),    EmployeeLastName varchar(20),    EmployeeAge int,    EmployeeSalary int,    EmployeeAddress varchar(200) ); ERROR 1064 (42000): You have an error in your syntax; check the manual that corresponds to your MySQL server version for the right syntax to ... Read More

To convert date from 'dd/mm/yyyy' to 'yyyymmdd', you can use the date_format() method. Let us first create a table −mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    Admissiondate varchar(200) ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Admissiondate) values('21/10/2014'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable(Admissiondate) values('01/12/2016'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(Admissiondate) values('31/01/2017'); Query OK, 1 row affected (0.14 sec)Following is the query to display all records from the table using select statement ... Read More

To add column to MySQL query and give it a value, use the below syntax −select yourColumnName1, yourColumnName2, .....N ,yourValue AS anyAliasName from yourTableName;Let us first create a table −mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    FirstName varchar(20) ); Query OK, 0 rows affected (0.84 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(FirstName) values('John'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(FirstName) values('Larry'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(FirstName) values('Chris'); Query OK, 1 row affected (0.14 sec) mysql> insert into ... Read More

To extract the User ID only from MySQL, you can use SUBSTRING_INDEX(), which extracts the part of a string from the Username to get the User ID. Let us first display the user −mysql> SELECT USER();This will produce the following output −+----------------+ | USER() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec)Let us now extract the UserID only −mysql> SELECT SUBSTRING_INDEX(USER(), '@', 1);This will produce the following output −+-------------------------------+ | SUBSTRING_INDEX(USER(), '@', 1) | +-------------------------------+ | root ... Read More

To change a specific char in a MySQL string, you can use CONCAT() along with SUBSTRING().Let us first create a table −mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    Value varchar(200) ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Value) values('98764'); Query OK, 1 row affected (0.17 sec)Following is the query to display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+----+-------+ | Id | Value | +----+-------+ | 1  | 98764 | +----+-------+ ... Read More

You can use CAST() with MAX() for this. Since the string is filled with string and integer, fir example, “STU201”, therefore we need to use CAST().Let us first create a table −mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    StudentBookCode varchar(200) ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(StudentBookCode) values('STU201'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(StudentBookCode) values('STU202'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(StudentBookCode) values('STU203'); Query OK, 1 row affected (0.20 sec) mysql> ... Read More

Let us first create a table. Within that we have set a column with type TIME to get the login time −mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    LoginTime TIME NULL ); Query OK, 0 rows affected (0.69 sec)Insert records in the table using insert command −mysql> insert into DemoTable(LoginTime) values('12:34:45'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(LoginTime) values('13:56:01'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(LoginTime) values('04:12:23'); Query OK, 1 row affected (0.13 sec)Following is the query to display all records from the table using ... Read More

Let us first create a table. Here, our columns is of type bit(1) −mysql> create table DemoTable (    isCaptured bit(1) ); Query OK, 0 rows affected (1.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(0); Query OK, 1 row affected (0.26 sec) mysql> insert into DemoTable values(1); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable values(0); Query OK, 1 row affected (0.47 sec) mysql> insert into DemoTable values(1); Query OK, 1 row affected (0.29 sec)Following is the query to display all records from the table using select statement −mysql> select ... Read More

This is a type of comment. The /* is the beginning of a comment and */ is the end of comment.Let us implement and display how to create a commentmysql> /* This is the first MySQL Program */MySQL will ignore the above comment.Let us see an example. Here, we have written a comment with /* and */mysql> /*This table has information about person */ mysql> create table DemoTable (    PersonId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    PersonName varchar(20),    PersonAge int ); Query OK, 0 rows affected (0.58 sec)

You can use LENGTH() to count number of specific symbols in a row. Let us first create a table −mysql> create table DemoTable (    Value varchar(200) ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('?1234?6789?5656?324?'); Query OK, 1 row affected (0.17 sec)Following is the query to display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+----------------------+ | Value | +----------------------+ | ?1234?6789?5656?324? | ... Read More