Found 4219 Articles for MySQLi

MySQL uses foreign key constraint instead of inheritance. MySQL does not support table inheritance.You can achieve the same with the help of foreign key constraint. Let us create a table and use the foreign key constraint. The query to create the first table is as follows −mysql> create table Parent_Table -> ( -> ParentId int, -> PRIMARY KEY(ParentId) -> ); Query OK, 0 rows affected (3.59 sec)Now create the second table. The query to create the second table is as follows −mysql> create table Child_Table ... Read More

Let us first create a table −mysql> create table OrderDemo    -> (    -> OrderId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> OrderPrice int,    -> OrderDatetime datetime    -> ); Query OK, 0 rows affected (0.66 sec)ExampleNow you can insert some records in the table using insert command. The query is as follows −mysql> insert into OrderDemo(OrderPrice, OrderDatetime) values(200, '2016-09-12'); Query OK, 1 row affected (0.24 sec) mysql> insert into OrderDemo(OrderPrice, OrderDatetime) values(NULL, '2002-11-18'); Query OK, 1 row affected (0.26 sec) mysql> insert into OrderDemo(OrderPrice, OrderDatetime) values(1000, '2017-12-28'); Query OK, 1 row affected (0.15 sec)Display all records ... Read More

To use HAVING with GROUPBY in MySQL, the following is the syntax. Here, we have set a condition under HAVING to get check for maximum value condition −SELECT yourColumnName FROM yourTableName GROUP BY yourColumnName HAVING MAX(yourColumnName) < yourValue;Let us see an example by creating a table in MySQL −mysql> create table WhereAfterGroupDemo -> ( -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> UserProcess int, -> UserThreadId int -> ); Query OK, 0 rows affected (5.74 sec)ExampleInsert some records in the table using insert command. The query is as follows −mysql> insert into WhereAfterGroupDemo(UserProcess, UserThreadId) values(1211, 3); Query OK, 1 ... Read More

You can get the enum possible values in a MySQL database with the help of INFORMATION_SCHEMA.COLUMNS table. The syntax is as follows −SELECT    COLUMN_TYPE AS anyAliasName FROM    INFORMATION_SCHEMA.COLUMNS WHERE    TABLE_SCHEMA = ‘yourDatabaseName’ AND TABLE_NAME = 'yourTableName' AND COLUMN_NAME = 'yourEnumColumnName';To understand the above syntax, let us create a table with an ENUM data type. The query to create a table is as follows −mysql> create table EnumDemo -> ( -> Id int, -> Color ENUM('RED', 'GREEN', 'BLUE', 'BLACK', 'ORANGE') -> ); Query OK, 0 rows affected (0.66 sec)Here the table ‘EnumDemo’ is present in the ‘sample’ database. ... Read More

You can achieve this with the help of INSERT statement i.e, you can simply insert it like a normal insert. The syntax is as follows −INSERT INTO yourTableName (yourIdColumnName, yourColumnName) values(value1, 'value2'); Let us first create a table: mysql> create table InsertValueInAutoIncrement -> ( -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> UserName varchar(20) -> ); Query OK, 0 rows affected (0.59 sec)Now you can insert some records in the table using insert command. Here, we are also inserting our own values for the auto_increment field UserId. ... Read More

To lag a column in MySQL, first, let us create a table. The query to create a table is as follows −mysql> create table LagDemo    -> (    -> UserId int,    -> UserValue int    -> ); Query OK, 0 rows affected (1.74 sec)ExampleInsert some records in the table using insert command. The query is as follows −mysql> insert into LagDemo values(12, 158); Query OK, 1 row affected (0.61 sec) mysql> insert into LagDemo values(18, 756); Query OK, 1 row affected (0.21 sec) mysql> insert into LagDemo values(15, 346); Query OK, 1 row affected (0.25 sec) mysql> insert ... Read More

To set the default storage engine, use the following syntax −set @@default_storage_engine = ’yourEngineType’;Now implement the above syntax to set the default engine to MyISAM. The query is as follows −mysql> set @@default_storage_engine = 'MyISAM'; Query OK, 0 rows affected (0.05 sec)Now you can check the default engine type with the help of SELECT statement. The query is as follows −mysql> select @@default_storage_engine;The following is the output displaying the engine as MyISAM −+--------------------------+ | @@default_storage_engine | +--------------------------+ | MyISAM | +--------------------------+ 1 ... Read More

Use the singleton design pattern. Here is the Java code that returns a single object −ConnectDatabase.javaimport java.sql.Connection; import java.sql.DriverManager; public class ConnectDatabase {    static Connection conn = null;    public static Connection getConnection() {       if (conn != null) return conn;       String database = "test";       String Username = "root";       String password = "123456";       return getConnection(database, Username, password);    }    private static Connection getConnection(String databaseName, String UserName, String password) {       try {          Class.forName("com.mysql.jdbc.Driver");          conn ... Read More

You need to use TOP(1) in SQL Server. The syntax is as follows −SELECT TOP(1) *FROM yourTableName WHERE yourCondition;To understand the above syntax, let us create a table. The query to create a table is as follows −create table TopDemoInSQLServer (    Id int,    Name varchar(10) );The snapshot of creation of table is as follows −Insert some records in the table using insert command. The query is as follows −insert into TopDemoInSQLServer values(10, 'John'); insert into TopDemoInSQLServer values(14, 'Carol'); insert into TopDemoInSQLServer values(1, 'Sam'); insert into TopDemoInSQLServer values(11, 'Bob'); insert into TopDemoInSQLServer values(18, 'David'); insert into TopDemoInSQLServer values(20, 'Sam');The ... Read More

To extract tuples with specified common values, use the following syntax −SELECT DISTINCT aliasName.yourColumnName1, aliasName.yourColumnName2, aliasName1.yourColumnName 1, aliasName1.yourColumnName2 FROM yourTableName aliasName INNER JOIN yourTableName aliasName1 ON aliasName.yourColumnName1 = aliasName1.yourColumnName1 WHERE aliasName.yourColumnName2 = 'value1' AND aliasName1.yourColumnName2 = 'value2';To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table extractTuples    -> (    -> Id int,    -> Name varchar(20),    -> Comments text    -> ); Query OK, 0 rows affected (0.77 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into ... Read More