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 301 of 341
MySQL order by 0 first and then display the record in descending order?
In order to order by 0 first and then largest, you can use the below syntax −select *from yourTableName order by yourColumnName=0 DESC, yourColumnName DESC;Let us first create a table −mysql> create table DemoTable ( Value int ); Query OK, 0 rows affected (0.65 sec)Insert records in the table using insert command −mysql> insert into DemoTable values(90); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values(0); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(20); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(0); Query OK, 1 row affected (0.12 ...
Read MoreWhat MySQL databases do I have permissions on?
To check this, you can use SHOW command. The syntax is as follows −show grants\GLet us implement the above syntax to display the permissions you have −mysql> SHOW GRANTS\GThis will produce the following output −*************************** 1. row *************************** Grants for root@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%` WITH GRANT OPTION *************************** 2. row *************************** Grants for root@%: ...
Read MoreHow to match underscore in a MySQL String?
To match underscore in a MySQL string, you can use the below syntax −select *from yourTableName where yourColumnName LIKE '%\_%';Let us first create a table −mysql> create table DemoTable ( ClientId varchar(200) ); Query OK, 0 rows affected (0.79 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('CLI_101'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('CLI1110'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('_CLI102'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('CLI103_'); Query OK, 1 row affected (0.12 sec) mysql> insert into ...
Read MoreHow to use IF in stored procedure and select in MySQL?
You can use IF in stored procedure and IF() in select statement as well.IF() in select statementIF() in select statement mysql> select if(0=0, 'Hello MySQL', 'condition is wrong');This will produce the following output −+------------------------------------------------------+ | if('test'='test', 'Hello MySQL', 'condition is wrong') | +------------------------------------------------------+ | Hello MySQL | +------------------------------------------------------+ 1 row in set (0.00 sec)The second case if your condition ...
Read MorePHP and MYSQL database connection and table creation only once if it does not already exist?
To create database only once, use the below syntax.CREATE DATABASE IF NOT EXISTS yourDatabaseName;To create table only once, use the below syntax −CREATE TABLE IF NOT EXISTS yourTableName ( yourColumnName yourDatatype, . . . N );Let us implement both the above syntaxes to create database and table only once if does not already exist −mysql> CREATE DATABASE IF NOT EXISTS login; Query OK, 1 row affected (0.23 sec)Above query creates a database successfully.Following is the query to create a table −mysql> CREATE TABLE IF NOT EXISTS DemoTable ( Id int ); Query OK, 0 rows affected (0.56 sec)Above query creates a table successfully.
Read MoreCan we know the last MySQL error?
In order to know the last MySQL error, you can use SHOW command −SHOW ERRORS;Or you can use another syntax −SHOW WARNINGS;Here, we are creating a table that displays an error and then we will find out how to know the last MySQL error. Here, the error occurs because we have intentionally written incorrect create table statement −mysql> create table DemoTable(Id int); 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 'create table DemoTable(Id int)' at line 1Following is the query to ...
Read MoreWrite a MySQL query to check if field exists and then return the result set?
To check if field exists and then to return the result set, you can use the below syntax −show columns from yourTableName where field='yourColumnName';Let us first create a table −mysql> create table DemoTable ( UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, UserFirstName varchar(20), UserLastName varchar(20), UserAge int, UserAddress varchar(200), UserCountryName varchar(20) ); Query OK, 0 rows affected (0.67 sec)Here is the query to check if field exists and then return the result set −mysql> show columns from DemoTable where field='UserCountryName';This will produce the following output −+-----------------+-------------+------+-----+---------+-------+ | Field | ...
Read MoreHow to get first N characters from a MySQL column?
Use SUBSTRING() to get first N characters from a MySQL column. Let us first create a table −mysql>create table DemoTable ( Information text ); Query OK, 0 rows affected (2.63 sec)Insert records in the table using insert command −mysql>insert into DemoTable values('MySQL is a structured query language'); Query OK, 1 row affected (0.13 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 −+--------------------------------------+ | Information | +--------------------------------------+ | MySQL is a structured query ...
Read MoreGet MySQL DISTINCT to work correctly if the records contain whitespace?
To get distinct including whitespace, you can use below syntax −SELECT DISTINCT replace(yourColumnName, ' ', '') FROM yourTableName;Let us first create a table:mysql>create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Name varchar(20) ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command −mysql>insert into DemoTable(Name) values('John '); Query OK, 1 row affected (0.14 sec) mysql>insert into DemoTable(Name) values(' John '); Query OK, 1 row affected (0.14 sec) mysql>insert into DemoTable(Name) values('John'); Query OK, 1 row affected (0.09 sec) mysql>insert into DemoTable(Name) values('Sam'); Query OK, 1 row affected (0.15 sec) ...
Read MoreMySQL Server port number?
If you will install MySQL on your system, then you will get the default MySQL server port number i.e. 3306.To know the MySQL server port number, you can use the following query. Here, we have used the SHOW VARIABLES command. The query is as follows −mysql> SHOW VARIABLES WHERE Variable_Name = 'port';The following is the output −+---------------+-------+ | Variable_Name | Value | +---------------+-------+ | port | 3306 | +---------------+-------+ 1 row in set (0.04 sec)
Read More