Toytoy Googie's Geeky blog

I just figured out this trick in MySQL with the INSERT ... SELECT statement functionality. How to do it, so I have a table named OFFERS and table named ACCOUNTS. Let's assumed that I have fields on these table, and it has OFFERS.CODE, OFFERS.USERID and ACCOUNTS.USERID. What I want is, I want to check if ACCOUNTS.USERID exist in OFFERS table in just one SQL statement. To do this, just do      INSERT INTO OFFERS (USERID, CODE)     SELECT         ' 22 ',' XXHH22BBMM '     FROM         ACCOUNT a     WHERE     A.USERID=' 22 ' AND A.USERID  NOT IN (     SELECT OFFER.USERID FROM  OFFER     WHERE OFFER.USERID=' 22 ' )     LIMIT 1 Now, the 22 is the USERID and the XXHH22BBMM values are used as my return value that I wanted actually to be inserted into the database. What exactly is happening here is that, this is equivalent to, INS...

I use the command below with -f [requests ssh to run in the background], and -N [to not execute a remote command] $> ssh username@example.com -L 3307:example.com:3306 -p 2222 -f -N the -L argument specifies that 3307 is the port to be used locally from example.com [the remote server] on its 3306 port [MySQL default port]. Then run the command below to connect to MySQL with port 3007. $> mysql --host=127.0.0.1 --port=3307 --protocol=TCP -u mysqluser -p Hope this helps.

Just to take this note that to revoke a privilege in MySQL, you need to execute the command below, mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'dealme'@'192.168.1.1'; mysql> flush privileges; and specifying the HOST/IP address would allow the user to be validated and verified by its IP allowing to connect into the host, i.e. remote logged in which can be done by the grant command below. mysql> GRANT ALL ON *.* TO 'dealme'@'192.168.1.1' IDENTIFIED BY 'dealmepassword' and the command above will allow the user to connect to all database and with all privileges granted. This can be seen by querying into the database. mysql> use mysql mysql> select * from user where User='dealme' \G \G would view the result vertically.

To create a user, do create user 'myuser' identified by 'iampassword'; To grant a user, do grant select, alter, alter routine, create, create routine, create temporary tables, create user, create view, delete, drop, execute, file, grant option, index, insert, lock tables, process, references, reload, replication client,replication slave, show databases, show view, update, usage on *.* TO 'ipyrusc'@'localhost'; The strings "select, lock, alter, alter routine, etc..." are privileges that you can select to what privilige you want the user created to have. Note that the 'myuser' is specified when granting the priviliges. To assure this user exist, you can do query by select user,host from mysql.user; to show the users of MySQL . To know more about this, check the page about granting and adding a user at MySQL website .

FreeBSD places its packages under /usr/ports/. Now for databases, types this command cd /usr/ports/databases/mysql60-server Then issue make after make, issue make install You can even specify it's configuration options like --prefix by doing "make install --prefix=/usr/local" where it will install the mysql packages. This is applied also to the client package, just follow the steps how the server is being installed. Create /var/db/mysql if that direcotry doesn't exist, then " chown mysql:mysql /var/db/mysql " and " chmod 770 /var/db/mysql " for security. Run the mysql_install_db command which will create default tables that mysql will used on its server process. Run the mysql background process by issuing the command " /usr/local/etc/rc.d/mysql-server start " or perhaps it might be " /usr/local/etc/rc.d/mysql-server.sh start ". I think older BSD preserves the .sh suffix on the start up scripts. Don't forget to provide pass...

Finding ou how cool MySQL is, I gathered this statement that might be useful when doing a CLI tasks. select count(*) as number_of_tables from information_schema.tables WHERE table_schema='schema_name'; OR select count(*) as number_of_tables from information_schema.tables WHERE table_schema='schema_name' and table_type = 'BASE TABLE' ; The latter one does only selects those base tables, BASE TABLE is actually a MySQL valid value of a table_type. This link can give some information about schema -> http://dev.mysql.com/doc/refman/5.0/en/information-schema.html