Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Economics & Finance
Firstly, check the MySQL database with DESC command. mysql>DESC mysql.db; The following is the output. +-----------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+---------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | ... Read More
We can get new record key with the help of LAST_INSERT_ID() function from MySQL. First, we will create a table and for inserting record, we will use LAST_INSERT_ID(). Let us create a table with the help of create command. The query is as follows − mysql> create table LastInsertRecordIdDemo -> ( -> id int auto_increment, -> value varchar(100), -> primary key(id) -> ); Query OK, 0 rows affected (0.52 sec) After creating a table, we will insert records and set it using LAST_INSERT_ID() ... Read More
‘LPAD(lower(column_name))’ is used to sort the varchar field numerically in MySQL. Let us see an example. Firstly, we will create a table. The CREATE command is used to create a table. mysql> create table SortingvarcharDemo -> ( -> List varchar(10) -> ); Query OK, 0 rows affected (0.82 sec) Records are inserted with the help of INSERT command. mysql> insert into SortingvarcharDemo values("99"); Query OK, 1 row affected (0.12 sec) mysql> insert into SortingvarcharDemo values("9"); Query OK, 1 row affected (0.17 sec) mysql> insert into SortingvarcharDemo ... Read More
The backtick can be used in MySQL. To create a table, we can put table_name in backticks. Example of Backtick in MySQL. The CREATE command is used to create a table. Here, we have added the table name using the backtick symbol. mysql> create table `backtickSymbol` -> ( -> uniId int -> ); Query OK, 0 rows affected (1.65 sec) Records are inserted with the help of INSERT command. mysql> insert into `backtickSymbol` values(1); Query OK, 1 row affected (0.20 sec) mysql> insert into `backtickSymbol` values(2); Query ... Read More
First, we will create a table and insert some values into the table. Let us create a table. mysql> create table RowValueDemo -> ( -> Name varchar(100) -> ); Query OK, 0 rows affected (0.69 sec) Insert records using the insert command. We have added duplicate values as well for our example. mysql> insert into RowValueDemo values('John'); Query OK, 1 row affected (0.14 sec) mysql> insert into RowValueDemo values('Bob'); Query OK, 1 row affected (0.14 sec) mysql> insert into RowValueDemo values('Carol'); Query OK, 1 row affected ... Read More
In general, we use ON in MySQL. In Joins, we use ON in a set of columns. USING is useful when both the tables share a column of the exact same name on which they join. Example of On. Creating our first table. mysql> CREATE table ForeignTableDemo -> ( -> Id int, -> Name varchar(100), - > FK int - > ); Query OK, 0 rows affected (0.47 sec) Creating our second table. mysql> CREATE table PrimaryTableDemo - > ... Read More
The MySQL JDBC connection string look like this − Class.forName("com.mysql.jdbc.Driver"); Above, Driver is an interface. Whenever your JDBC is running outside an application server, then the class DriverManager establish the connection. The DriverManager class is as follows − conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/yourdatabaseName",”yourRootName","yourPassword"); Now, I am applying the above connection string to connect Java to MySQL database. The code is as follows. The following is the output that shows that MySQL connection with Java is successful.
We can use ‘cross join’ without on condition. Cross join gives the result in cartesian product form. For instance, if in one table there are 3 records and another table has 2 records, then the first record will match with all the second table records. Then, the same process will be repeated for second record and so on. Example of cross join Creating the first table mysql> CREATE table ForeignTableDemo - > ( - > Id int, - > Name varchar(100), - > FK int ... Read More
The active or total connection can be known with the help of threads_connected variable. The variable tells about the number of currently open connections. The query is as follows − mysql> show status where `variable_name` = 'Threads_connected'; Here is the output. +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_connected | 1 | +-------------------+-------+ 1 row in set (0.06 sec) We can check the same with the help of show command. The query is as follows − mysql> show processlist; Here is the output. +----+-----------------+-----------------+----------+---------+--------+------------------------+------------------+ ... Read More
In order to generate a 10 character string, we can use inbuilt functions ‘rand()’ and ‘char()’. The following is the query to generate random 10 character string. mysql> SELECT concat( - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97), - > char(round(rand()*25)+97) - > )AS Random10CharacterString; ... Read More