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 55 of 355
What is the MySQL user creation script?
First, create a user and password using CREATE command. The syntax is as follows.CREATE USER 'yourUserName'@'localhost' IDENTIFIED BY 'yourPassword';The syntax to give all privileges of the specific database to the user is as follows.GRANT ALL PRIVILEGES ON yourDatabaseName . * TO 'yourUserName'@'localhost';Now you can implement the above syntaxes to create a user and grant all privileges.Step 1 − Create a userThe query is as follows.mysql> create user 'Adam Smith'@'localhost' IDENTIFIED BY 'Adam123456'; Query OK, 0 rows affected (0.29 sec)Step 2 − Grant all privileges to the user.The query is as follows.mysql> GRANT ALL PRIVILEGES ON test . * TO 'Adam ...
Read MoreList MySQL tables and sizes ordered by size?
You can do this with the help of information_schema.tables. The syntax is as follows -SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024), 2) "MB Size" FROM information_schema.TABLES WHERE table_schema = "yourDatabaseName" ORDER BY (data_length + index_length) ASC;To understand the above syntax, let us implement it for any database. Here, I am using database TEST. Let us see the query for our database TEST.mysql> SELECT TABLE_NAME, table_rows, data_length, index_length, -> round(((data_length + index_length) / 1024 / 1024), 2) "MB Size" -> FROM information_schema.TABLES WHERE table_schema = "test" -> ORDER BY (data_length + index_length) ASC;The following is the ...
Read MoreCan we use LIKE and OR together in MySql?
You can use LIKE with OR operator which works same as IN operator.Let us see the syntax for both the cases −Case 1 − Using Like with OR operator.select *from yourTableName where yourColumnName Like ‘Value1’ or yourColumnName Like ‘Value2’ or yourColumnName Like ‘Value3’ . . . NCase 2 − Using IN operator.select *from yourTableName where IN(value1, value2, value3, .....N);To understand both the syntaxes, let us create a table. The query to create a table is as follows −mysql> create table LikeDemo −> ( −> Id varchar(20) −> ); Query OK, 0 rows affected (0.58 sec)Now you can insert records in ...
Read MoreUPDATE column to append data into it in MySQL?
To achieve this, the following is the syntax.UPDATE yourTableName set yourColumnName=concat(ifnull(yourColumnName, ””), ’anyValue1, anyValue2, anyValue);To understand the above syntax, let us first create a table. The query to create a table is as follows -mysql> create table AppendDataDemo -> ( -> StudentId int, -> StudentName varchar(100), -> StudentAge int -> ); Query OK, 0 rows affected (1.54 sec)Insert some records in the table using insert command. The query is as follows.mysql> insert into AppendDataDemo values(101, 'John', 23); Query OK, 1 row affected (0.24 sec) mysql> insert into AppendDataDemo values(102, null, 24); Query OK, 1 row affected (0.74 sec) ...
Read MoreMySQL Sum Query with IF Condition using Stored Procedure
The Sum() is an aggregate function in MySQL. You can use sum query with if condition. To understand the sum query with if condition, let us create a table.The query to create a table −mysql> create table SumWithIfCondition −> ( −> ModeOfPayment varchar(100) −> , −> Amount int −> ); Query OK, 0 rows affected (1.60 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into SumWithIfCondition values('Offline', 10); Query OK, 1 row affected (0.21 sec) mysql> insert into SumWithIfCondition values('Online', 100); Query OK, 1 row affected ...
Read Moreint(5) vs. int(10) in MySQL?
The value in the parentheses is used to display only the width and sets the zerofill. The width is 5 for int(5), whereas 10 for int(10). Let us see another example with a different width value set for int.Let us first create a table. Here, we have set the int to int(11) and int(13). The following is the query to create a table −mysql> create table intVsIntAnyThingDemo −> ( −> Number1 int(11) unsigned zerofill, −> Number int(13) unsigned zerofill −> ); Query OK, 0 rows affected (1.17 sec)Now you can insert record in the table with the help of insert ...
Read MoreMySQL Select Multiple VALUES?
To select multiple values, you can use where clause with OR and IN operator.The syntax is as follows −Case 1 − Using ORselect *from yourTablename where yourColumnName = value1 or yourColumnName = value2 or yourColumnName = value3, .........N;Case 2 − Using INselect *from yourTableName where yourColumnName IN(value1, value2, ....N);To understand the above syntax, let us create a table. The following is the query to create a table −mysql> create table selectMultipleValues −> ( −> BookId int, −> BookName varchar(200) −> ); Query OK, 0 rows affected (1.68 sec)Now you can insert some records in the table with the help of ...
Read MoreHow to use user variables in MySQL LIKE clause?
Using the CONCAT() function, we can work with user variables in LIKE clause. The syntax is as follows.set @anyVariableName='anyValue'; select yourColumnName1, yourColumnName2, yourColumnName3, ...N from yourTableName whereyourColumnName like CONCAT('%', @anyVariableName, '%');To understand the above syntax, let us first create a table. The query to create a table is as follows.mysql> create table UserVariableInLike -> ( -> id int, -> Name varchar(100), -> Age int -> ); Query OK, 0 rows affected (0.83 sec)Insert records in the table using insert command. The query is as follows.mysql> insert into UserVariableInLike values(101, 'John', 23); Query OK, 1 row affected (0.23 sec) mysql> ...
Read MoreConcat a string to SELECT * in MySQL?
To concat a string, use the CONCAT() function from MySQL as shown in the below syntaxSELECT CONCAT(yourColumnName1, ’anyConcatenationString’), CONCAT(yourColumnName2, ’anyC oncatenationString’), ....N from yourTableName;To understand the above syntax, let us first create a table. The query to create a table is as followsmysql> create table selectConcat -> ( -> StudentId int, -> StudentName varchar(100), -> StudentAge int -> ); Query OK, 0 rows affected (1.32 sec)Insert some records in the table using insert command. The query is as followsmysql> insert into selectConcat values(1, 'Carol', 23); Query OK, 1 row affected (0.19 sec) mysql> insert into selectConcat values(2, 'John', 24); ...
Read MoreHow to set max_connections in MySQL Programmatically?
To set max_connections in MySQL programmatically, you can use SET command. The syntax is as follows −SET GLOBAL max_connections=yourIntegerValue;Let us implement the above query to set maximum connections. The query is as follows −mysql> set global max_connections=1000; Query OK, 0 rows affected (0.04 sec)Check maximum connections are set or not, using the show variables command. The query is as follows.mysql> show variables like 'max_connections';The following is the output.+-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 1000 | +-----------------+-------+ 1 row in set (0.18 sec)
Read More