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
MySQLi Articles
Page 305 of 341
ALTER table by adding AUTOINCREMENT in MySQL?
To add AUTOINCREMENT in MySQL, you can use the ALTER command.ALTER TABLE yourTableName change yourColumName yourColumnName dataType AUTO_INCREMENT PRIMARY KEY;To understand the above concept, create a table with a column. Let us create a table −mysql> create table AlterTableToAddAutoIncrement -> ( -> StudentId int -> ); Query OK, 0 rows affected (0.57 sec)Implement the above syntax to change “StudentId” with AUTOINCREMENT. The query is as follows −mysql> alter table AlterTableToAddAutoIncrement change StudentId StudentId int AUTO_INCREMENT Primary key; Query OK, 0 rows affected (1.93 sec) Records: 0 Duplicates: 0 Warnings: 0We ...
Read MoreMySQL Select IN range?
You cannot do select IN range. For the same result, use BETWEEN. Let us see an example −IN(start, end): It means that the intermediate value between start and end won’t get displayed. For the above logic, you can use BETWEEN.BETWEEN clause is inclusive, for example, suppose there are 1, 2, 3, 4, 5, 6 numbers. If you want to display numbers from 2 to 6 inclusively, then using BETWEEN the numbers 2 and 6 will also get displayed.Let us create a table −mysql> create table SelectInWithBetweenDemo -> ( -> PortalId int ...
Read MoreIs there a way to know your current username in MySQL?
Yes, you can use the method CURRENT_USER() to know the current username in MySQL.The above method returns the username that can be used to authenticate the client connection.The query is as follows −mysql> select CURRENT_USER();The following is the output −+----------------+ | CURRENT_USER() | +----------------+ | root@% | +----------------+ 1 row in set (0.00 sec)Or you can use USER() method from MySQL. The query is as follows −mysql> select user();Here is the output −+----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec)
Read MoreHow to implement ternary conditional operator in MySQL?
A ternary conditional operator looks like ?: in programming language like C, C++, Java etc. The syntax is as follows −(yourCondition) ? statement1:statement2;In the above syntax, if yourCondition becomes true then statement1 will evaluate and if yourCondition becomes false then statement2 will evaluate.But the above syntax does not work in MySQL. We can use IF() function from MySQL for the same purpose.Let us see an example −Case 1mysql> select if(3 > 5, 'Condition is true', 'Condition is not true') as ConditionalResult;The following is the output in which second statement evaluates since is 3 isn’t more than 5 −+-----------------------+ | ConditionalResult ...
Read MoreCalculate age based on date of birth in MySQL?
Calculate Age based on date of birth with the help of DATE_FORMAT() method in MySQL. Firstly, get the current date time with the help of now() method and you can place your date of birth in DATE_FORMAT().The syntax is as follows −SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(now(), 'yourDateofbirth')), '%Y')+0 AS anyVariableName;Apply the above syntax to calculate age from yourDateofbirth. In the above syntax, replace yourDateofbirth with your date of birth. The query is as follows −SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(now(), '2010-11-25')), '%Y')+0 AS Age;The following is the output −+------+ | Age | +------+ | 8 | +------+ 1 row in set (0.00 sec)Let ...
Read MoreHow to change the default charset of a MySQL table?
To change the default charset of a MySQL table, you can use the below syntax. The syntax is as follows −alter table yourTableName convert to character set yourCharsetName;Let us create a table and apply the above syntax to change the default charset. The query to create a table −mysql> create table CharsetDemo -> ( -> Id int, -> Name varchar(200), -> Age int -> ); Query OK, 0 rows affected (0.73 sec)Now you can change the charset of a table. The following is the query ...
Read MoreHow do I get the creation date of a MySQL table?
To get the creation date of MySQL table, use the information_schema. The syntax is as follows −SELECT create_time FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'yourDatabaseName’ AND table_name = 'yourTableName';Apply the above syntax for your database and the table name. Here I am using the database ‘business’ and table name is ‘student’. The query is as follows −mysql> SELECT create_time FROM INFORMATION_SCHEMA.TABLES -> WHERE table_schema = 'business' -> AND table_name = 'student';The following is the output displaying the creation time of a table −+---------------------+ | CREATE_TIME ...
Read MoreMySQL - CAST DECIMAL to INT?
Cast DECIMAL to INT with the help of FLOOR() function. The syntax is as follows −SELECT FLOOR(yourColumnName) from yourTableName where condition;Let us first create a table. The following is the query to create a table.mysql> create table DecimalToIntDemo -> ( -> Amount DECIMAL(3, 1) -> ); Query OK, 0 rows affected (0.88 sec)Now you can insert records into the table with the help of insert command. The query is as follows −mysql> insert into DecimalToIntDemo values(12.5); Query OK, 1 row affected (0.23 sec) mysql> insert into DecimalToIntDemo values(50.4); Query OK, 1 ...
Read MoreWhere does MySQL store database files?
To know where MySQL store database files, you can use the variable @@datadir. The query is as follows −mysql> select @@datadir;The following is the output that displays the path −+---------------------------------------------+ | @@datadir | +---------------------------------------------+ | C:\ProgramData\MySQL\MySQL Server 8.0\Data\ | +---------------------------------------------+ 1 row in set (0.00 sec)Here is the snapshot where MySQL store database files i.e. the same path we got above −Now, let us verify whether ...
Read MoreSort by date & time in descending order in MySQL?
Let us create a table to sort date and time in ascending order. The query to create a table is as follows −mysql> create table SortByDateAndTime -> ( -> UserId int, -> UserName varchar(100), -> IssueDate date, -> IssueTime time -> ); Query OK, 0 rows affected (0.60 sec)Insert the records in the table using insert command. The query is as follows −mysql> insert into SortByDateAndTime values(1, 'John', '2018-12-16', '10:30'); Query OK, 1 row affected (0.14 sec) mysql> insert into SortByDateAndTime values(2, 'Bob', '2018-12-16', '10:10'); Query OK, 1 row affected (0.14 sec) ...
Read More