Remote Technology Jobs

SELECT  * FROM    a LEFT JOIN b ON      b.a_id = a.id WHERE   b.column = 'sth' Return the same result as (but less efficient) SELECT  * FROM    a INNER JOIN b ON      b.a_id = a.id WHERE   b.column = 'sth' Put additional conditions on left table on left join will actually filter out those rows where b.column is null. Because   NULL in SQL  doesn't equal to anything. So the only exception to put extra condition on left table of left join is to check b.column is null or not. If you actually want to return those  b.column = 'sth' and at the same time with all records of table a, then the condition should be moved into ON clause: SELECT  * FROM    a LEFT JOIN         b ON      b.a_id = a.id AND b.column = 'sth'

SELECT  * FROM    a LEFT JOIN b ON      b.a_id = a.id WHERE   b.column = 'sth' Return the same result as (but less efficient) SELECT  * FROM    a INNER JOIN b ON      b.a_id = a.id WHERE   b.column = 'sth' Put additional conditions on left table on left join will actually filter out those rows where b.column is null. Because   NULL in SQL  doesn't equal to anything. So the only exception to put extra condition on left table of left join is to check b.column is null or not. If you actually want to return those  b.column = 'sth' and at the same time with all records of table a, then the condition should be moved into ON clause: SELECT  * FROM    a LEFT JOIN         b ON      b.a_id = a.id AND b.column = 'sth'

You can go to https://www.katacoda.com/mysql-db-sandbox/scenarios/mysql-sandbox to access the MySQL Sandbox, which has the Sakila sample database loaded in a MySQL instance. You’ll have to set up a (free) Katacoda account. Then, click the Start Scenario button. See also: https://dev.mysql.com/doc/index-other.html

An expression can be null, but it can never equal null. Two nulls are never equal to each other.

Permanent tables (i.e., created using the create table statement) Derived tables (i.e., rows returned by a sub-query and held in memory) Temporary tables (i.e., volatile data held in memory) Virtual tables (i.e., created using the create view statement)

Although mysqldump is recommended for upgrading mysql server. The following process maybe faster and save you headache from issues relative to mysqldump. Example: upgrade from 5.5.12 to 5.6.14 Make a hot copy of 5.5.12 and restore it to another server. In the new server, un-install mysql server (keep the data of course). In the new server, install 5.6.14 Start mysql server run mysql_upgrade Done The following tested and works on CentOS 6: Make a hot copy of 5.5.12 and restore it to another server using mysqlbackup from Oracle. On the new database server: /etc/init.d/mysql stop rm -rf /var/lib/mysql/* rpm -e MySQL-server-5.6.16-1.linux_glibc2.5.x86_64 rpm -e MySQL-client-5.6.16-1.linux_glibc2.5.x86_64 /opt/mysql/meb-3.8/bin/mysqlbackup --defaults-file=/backup/backup-my.cnf --backup-dir=/backup/  copy-back --datadir=/var/lib/mysql chown -R mysql:mysql /var/lib/mysql yum -y install /opt/MySQL-server-5.5.36-1.linux2.6.x86_64.rpm yum -y install /opt/MySQL-client-5.5.36-1.linux2.6.x...

A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction. SQL > SAVEPOINT SP1; Savepoint created. SQL > DELETE FROM CUSTOMERS WHERE ID = 1 ; 1 row deleted. SQL > SAVEPOINT SP2; Savepoint created. SQL > DELETE FROM CUSTOMERS WHERE ID = 2 ; 1 row deleted. SQL > SAVEPOINT SP3; Savepoint created. SQL > DELETE FROM CUSTOMERS WHERE ID = 3 ; 1 row deleted. Now that the three deletions have taken place, say you have changed your mind and decided to ROLLBACK to the SAVEPOINT that you identified as SP2. Because SP2 was created after the first deletion, the last two deletions are undone: SQL > ROLLBACK TO SP2; Rollback complete. Notice that only the first deletion took place since you rolled back to SP2.

Example 1:  public void myMethod( Lists list, Account account) { Query query = em . createNativeQuery( " call example_procedure(?,?); " ); query . setParameter( 1 , list . getListid()) . setParameter( 2 , account . getUserid()); query . executeUpdate(); } or public void myMethod( Lists list, Account account) { em . createNativeQuery( " call example_procedure(?p1,?p2); " ) .setParameter( " p1 " , list . getListid()) .setParameter( " p2 " , account . getUserid()) . executeUpdate(); } Example 2: // for getting the result back. String q = " call `i88ca`.`example_procedure2`(); " ; Object o = em . createNativeQuery(q) . getSingleResult(); // or List l = em . createNativeQuery(q) . getResultList(); PROCEDURE `example_procedure2`() BEGIN select 8888 ; -- or any other select statements for output. END Example 3: @Resource(name = " db2 " ) private DataSource dataSource; public vo...

Example 1:  public void myMethod( Lists list, Account account) { Query query = em . createNativeQuery( " call example_procedure(?,?); " ); query . setParameter( 1 , list . getListid()) . setParameter( 2 , account . getUserid()); query . executeUpdate(); } or public void myMethod( Lists list, Account account) { em . createNativeQuery( " call example_procedure(?p1,?p2); " ) .setParameter( " p1 " , list . getListid()) .setParameter( " p2 " , account . getUserid()) . executeUpdate(); } Example 2: // for getting the result back. String q = " call `i88ca`.`example_procedure2`(); " ; Object o = em . createNativeQuery(q) . getSingleResult(); // or List l = em . createNativeQuery(q) . getResultList(); PROCEDURE `example_procedure2`() BEGIN select 8888 ; -- or any other select statements for output. END Example 3: @Resource(name = " db2 " ) private DataSource dataSource; public vo...

Java JDBC This example uses Java and the JDBC API: java.sql . PreparedStatement stmt = connection . prepareStatement( " SELECT * FROM users WHERE USERNAME = ? AND ROOM = ? " ); stmt . setString( 1 , username); stmt . setInt( 2 , roomNumber); stmt . executeQuery(); Java PreparedStatement provides "setters" (setInt(int), setString(String), setDouble(double), etc.) for all major built-in data types. PHP PDO This example uses PHP and PHP Data Objects (PDO): $stmt = $dbh -> prepare( " SELECT * FROM users WHERE USERNAME = ? AND PASSWORD = ? " ); $stmt -> execute( array ( $username , $password )); MySQL PREPARE i88_ca FROM ' insert low_priority ignore into sharedlist(listid, contactid) select ?, sl.contactid from sharedlist sl join contacts c on c.contactid=sl.contactid where sl.listid=? order by c.email limit ? ' ; EXECUTE i88_ca USING @v_listid1,@v_listid,@count;

To pass LIMIT as parameters to MySQL stored procedure,  because LIMIT cannot be parametrized in MySQL stored procedures, we have to do it indirectly using  prepared statements. delimiter $$ CREATE DEFINER = ` i88ca ` @ ` 192.168.% ` PROCEDURE ` general_split_list ` ( in v_listid int , in v_listid1 int , in v_listid2 int , in percentage int ) BEGIN -- split v_listid into v_listid1 and v_listid2, the ratio is the percentage.set @v_listid=v_listid; select l . totalcontacts into @total from lists l where l . listid = @v_listid; select @total * percentage / 100 into @count; set @v_listid1 = v_listid1; set @v_listid2 = v_listid2; set @count2 = @total - @count; PREPARE i88_ca FROM ' insert low_priority ignore into sharedlist(listid, contactid) select ?, sl.contactid from sharedlist sl join contacts c on c.contactid=sl.contactid where sl.listid=? order by c.email limit ? ' ; EXECUTE i88_ca USING @v_listid1,@v_listid,@count; END$$