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
MySQL Articles
Page 44 of 355
How to count number of NULLs in a row with MySQL?
Use ISNULL() from MySQL. Let us first create a table −mysql> create table DemoTable -> ( -> Number1 int, -> Number2 int -> ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(10, NULL); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values(NULL, NULL); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values(29, 98); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(NULL, 119); Query OK, 1 row affected (0.15 sec)Display all records ...
Read MoreMySQL: What is 'AUTO_INCREMENT=5' in a create table query?
The AUTO_INCREMENT=5 in a create table query tells that the first record will start from 5 i.e. not default 1. As we know if you do not set the value to AUTO_INCREMENT then MySQL starts from 1 by default.The syntax is as follows:CREATE TABLE yourTableName ( yourColumnName1 dataType NOT NULL AUTO_INCRMENT, . . . N, PRIMARY KEY(yourColumnName1 ) )AUTO_INCREMENT=5;To understand the above syntax, let us create a table.Case1 − The table starts auto increment from 1 because it is the default standard.The query to create a table is as follows:mysql> create table defaultAutoIncrementDemo -> ( -> Id int ...
Read MoreHow to use if clause in MySQL to display Students result as Pass or Fail in a new column?
Let us first create a table −mysql> create table DemoTable -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(100), -> Subject varchar(100), -> Score int -> ); Query OK, 0 rows affected (0.94 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Name, Subject, Score) values('Chris', 'MySQL', 80); Query OK, 1 row affected (0.32 sec) mysql> insert into DemoTable(Name, Subject, Score) values('Robert', 'MongoDB', 45); Query OK, 1 row affected (0.62 sec) mysql> insert into DemoTable(Name, Subject, Score) values('Adam', 'Java', 78); Query OK, 1 row affected ...
Read MoreDisplay records ignoring NULL in MySQL
Use IS NOT NULL to display only NOT NULL records. Let us first create a table −mysql> create table DemoTable -> ( -> FirstName varchar(100) -> ); Query OK, 0 rows affected (3.01 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John'); Query OK, 1 row affected (0.44 sec) mysql> insert into DemoTable values(NULL); Query OK, 1 row affected (0.58 sec) mysql> insert into DemoTable values('Chris'); Query OK, 1 row affected (0.31 sec) mysql> insert into DemoTable values(NULL); Query OK, 1 row affected (0.20 sec) mysql> insert into ...
Read MoreHow to log in as a different user on MySQL?
If you want to login as a different user on MySQL, you need to use “mysql -u -p command”. The syntax is as follows to login as a different user.>mysql -u yourUsername -p After pressing enter key Enter password −To understand the above syntax, let us create a user in MySQL. The syntax is as follows −CREATE USER 'yourUserName'@'localhost' IDENTIFIED BY 'yourPassword';Now I am going to create a user with name ‘John’ and password is ‘john123456’. The query is as follows −mysql> CREATE USER 'John'@'localhost' IDENTIFIED BY 'john123456'; Query OK, 0 rows affected (0.15 sec)Now check the user has been ...
Read MoreSelect the topmost record from a table ordered by desc on the basis of ID?
For this, use ORDER BY DESC with LIMIT 1. Let us first create table −mysql> create table DemoTable -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> UserName varchar(100), -> UserMessage text -> ); Query OK, 0 rows affected (1.17 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(UserName, UserMessage) values('Adam', 'Hi'); Query OK, 1 row affected (0.92 sec) mysql> insert into DemoTable(UserName, UserMessage) values('Chris', 'Awesome'); Query OK, 1 row affected (0.52 sec) mysql> insert into DemoTable(UserName, UserMessage) values('Robert', 'Nice'); Query OK, 1 row affected (0.65 sec) ...
Read MoreHow to get the seed value of an identity column in MySQL?
For this, you can use SHOW VARIABLES command −mysql> SHOW VARIABLES LIKE 'auto_inc%';OutputThis will produce the following output −+--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | +--------------------------+-------+ 2 rows in set (0.95 sec)You can control over AUTO_INCREMENT outside.Let us first create a table −mysql> create table DemoTable -> ( -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY -> ); Query OK, 0 rows affected (0.94 sec)Insert some records in the table using insert command −mysql> insert into DemoTable ...
Read MoreCan we compare numbers in a MySQL varchar field?
Yes, we can do this by first using CAST(). Let us first create a table −mysql> create table DemoTable -> ( -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentScore varchar(100) -> ); Query OK, 0 rows affected (0.66 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(StudentScore) values('90'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable(StudentScore) values('100'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(StudentScore) values('56'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable(StudentScore) values('98'); Query OK, 1 ...
Read MoreMySQL select for exact case sensitive match with hyphen in records
For exact case sensitive match, use BINARY after WHERE clause in MySQL. Let us first create a table −mysql> create table DemoTable -> ( -> EmployeeCode varchar(100) -> ); Query OK, 0 rows affected (0.64 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('EMP-1122'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('emp-1122'); Query OK, 1 row affected (0.43 sec) mysql> insert into DemoTable values('EMP-6756'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('EMP-8775'); Query OK, 1 row affected (0.16 sec)Display all records ...
Read MoreWhat would be a query to remove nn from the text in MySQL?
To remove \r from the text, you need to use REPLACE command. The syntax is as follows −UPDATE yourTableName SET yourColumnName=REPLACE(yourColumnName, ’\r’, ’ ‘);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table removeDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name text, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.79 sec)Now insert some records in the table using insert command. The query is as follows −mysql> insert into removeDemo(Name) values('John\rSmithCarol'); Query OK, 1 row affected (0.13 sec) mysql> insert into removeDemo(Name) values('LarryMike\rSam'); ...
Read More