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 288 of 341
How to change a primary key in MySQL to auto_increment?
To change a primary key to auto_increment, you can use MODIFY command. Let us first create a table.mysql> create table changePrimaryKeyInAutoIncrement -> ( -> StudentId int not null primary key, -> StudentName varchar(100), -> StudentAge int, -> StudentAddress varchar(100) -> ); Query OK, 0 rows affected (0.63 sec)Let us now check the description of table using desc command:mysql> desc changePrimaryKeyInAutoIncrement;This will produce the following output+----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+-------+ | StudentId | int(11) ...
Read MoreCount value for multiple columns in MySQL?
To count value for multiple columns, use the CASE statement. Let us first create a table::mysql> create table countValueMultipleColumnsDemo -> ( -> Value1 int, -> Value2 int, -> Value3 int -> ); Query OK, 0 rows affected (0.62 sec)Following is the query to insert some records in the table using insert command:mysql> insert into countValueMultipleColumnsDemo values(10, 15, 10); Query OK, 1 row affected (0.15 sec) mysql> insert into countValueMultipleColumnsDemo values(20, 30, 10); Query OK, 1 row affected (0.14 sec) mysql> insert into countValueMultipleColumnsDemo values(40, 10, 60); Query OK, 1 row affected (0.18 sec)Following ...
Read MoreIn a MySQL schema, what is the meaning of "AUTO_INCREMENT=3
In MySQL, AUTO_INCREMENT=3 tells that the inserted record will start from 3 not the default 1. Let us first create a sample table and set auto increment to 3:mysql> create table Auto_incrementDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(20) -> )AUTO_INCREMENT=3; Query OK, 0 rows affected (0.52 sec)Following is the query to insert some records in the table using insert command:mysql> INSERT INTO Auto_incrementDemo(Name) values('John'); Query OK, 1 row affected (0.12 sec) mysql> INSERT INTO Auto_incrementDemo(Name) values('Larry'); Query OK, 1 row affected (0.15 sec) mysql> INSERT INTO Auto_incrementDemo(Name) ...
Read MoreMySQL query to select one specific row and another random row?
To select one specific row and another random row, you can use ORDER BY and RAND(). Let us first create a sample table:mysql> create table oneSpecificRowAndOtherRandom -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(20) -> ); Query OK, 0 rows affected (0.72 sec)Following is the query to insert some records in the table using insert command:mysql> insert into oneSpecificRowAndOtherRandom(Name) values('Larry'); Query OK, 1 row affected (0.56 sec) mysql> insert into oneSpecificRowAndOtherRandom(Name) values('Sam'); Query OK, 1 row affected (0.13 sec) mysql> insert into oneSpecificRowAndOtherRandom(Name) values('Mike'); Query OK, 1 row affected ...
Read MoreDoes MySQL converts bool to tinyint(1) internally?
Yes, MySQL internally convert bool to tinyint(1) because tinyint is the smallest integer data type.You can also say the bool is synonym for tinyint(1). Let us first create a sample table:mysql> create table boolToTinyIntDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(20), -> isAgeGreaterThan18 bool -> ); Query OK, 0 rows affected (1.02 sec)Let us now check the description of table:mysql> desc boolToTinyIntDemo;This will produce the following output+--------------------+-------------+------+-----+---------+----------------+ | Field ...
Read MoreHow to use "OR" condition in MySQL CASE expression?
Set the same condition like “OR” in a MySQL CASE expression. Let us first create a sample table.Following is the querymysql> create table caseOrConditionDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(100), -> Score int -> ); Query OK, 0 rows affected (0.49 sec)Following is the query to insert some records in the table using insert command:mysql> insert into caseOrConditionDemo(Name, Score) values('Larry', 85); Query OK, 1 row affected (0.18 sec) mysql> insert into caseOrConditionDemo(Name, Score) values('Sam', 74); Query OK, 1 row affected (0.20 sec) mysql> insert into caseOrConditionDemo(Name, ...
Read MoreDeleting the nth row in MySQL?
To delete nth row in MySQL, use DELETE statement and work with subquery. Let us first create a table:mysql> create table DemoTable1 -> ( -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentName varchar(100) -> ); Query OK, 0 rows affected (0.99 sec)Following is the query to insert some records in the table using insert command:mysql> insert into DemoTable1(StudentName) values('Larry'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1(StudentName) values('Sam'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1(StudentName) values('Mike'); Query OK, 1 row affected (0.18 sec) mysql> ...
Read MoreHow to work with array variable in MySQL?
MySQL does not support array variables. To get the same result, use the table DUAL. Following is the syntax:SELECT yourValue1 AS ArrayValue FROM DUAL UNION ALL SELECT yourValue2 FROM DUAL UNION ALL SELECT yourValue3 FROM DUAL UNION ALL SELECT yourValue4 FROM DUAL UNION ALL . . . . . . SELECT yourValueN FROM DUAL;Let us create a sample table:mysql> SELECT 1 AS ArrayValue FROM DUAL UNION ALL SELECT 2 FROM DUAL UNION ALL SELECT 3 FROM DUAL UNION ALL SELECT 4 FROM ...
Read MoreHow to call a stored procedure using select statement in MySQL?
In MySQL, it is not possible to use select from procedure in FROM clause. You can use CALL command and after that the SELECT statement can be executed.Let us first create a table:mysql> create table DemoTable2 -> ( -> CustomerId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> CustomerName varchar(100), -> ShippingDateTime datetime -> ); Query OK, 0 rows affected (0.66 sec)Following is the query to create stored procedure:mysql> DELIMITER // mysql> CREATE PROCEDURE insert_information(Name varchar(100), shippingtime datetime) -> BEGIN -> -> INSERT INTO DemoTable2(CustomerName, ShippingDateTime) VALUES(Name, shippingtime); -> END -> ...
Read MoreMySQL query to retrieve records from the part of a comma-separated list?
To retrieve records from the part of a comma-separated list, you can use built in function FIND_IN_SET().Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Name varchar(20), Marks varchar(200) ); Query OK, 0 rows affected (0.61 sec)Following is the query to insert some records in the table using insert command −mysql> insert into DemoTable(Name, Marks) values('Larry', '98, 34, 56, 89'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(Name, Marks) values('Chris', '67, 87, 92, 99'); Query OK, 1 row affected (0.15 sec) mysql> insert ...
Read More