Database

MySQL Commands

Command	Description
source file_name	Run a script file like a .sql file
INSERT into table_name (column1, column2….) values (value1, value2…);	insert command
mysqldump database_name	creates a dump. Gives one sql file. for example: mysqldump -u root -p<pass> my_schema > output.sql
select * from xx where user like '%2554024%';	Usage of like
mysql -u root -p<password> :no space between p and the password	login
select * from xxx where xx in(a,b,c);	usage of in
show tables	shows the tables of a selected database
desc table_name	description of a table
show databases;	shows all the databases
TRUNCATE TABLE tablename;	This will delete all data in the table very quickly. In MySQL the table is actually dropped and recreated, hence the speed of the query.
DELETE FROM tablename;	This also deletes all the data in the table, but is not as quick as using the "TRUNCATE TABLE" method.
INSERT IGNORE INTO new_tbl SELECT * FROM orig_tbl;	This command will take all the data from orig_tbl and add it to new_tbl. The IGNORE is only necessary if there might be duplicate keys.
select count (*) as ssum from TT group by a , b having ssum > 1;	Finding duplicate rows in table TT(a,b)
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'pass1';	Create a user with the specified password
GRANT ALL PRIVILEGES ON *.* TO 'user1'@'localhost';	Grant all privilages to the user
GRANT ALL ON someschema.* TO root@'the-ip' IDENTIFIED BY 'password';
CREATE TABLE mytable (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, data VARCHAR(100), curr TIMESTAMP(8));	Create Table
create database gfr;	create a schema
/etc/mysql/my.cnf	Config file
/etc/init.d/mysqld start	To start or similarly we can restart or stop
status	this command gives some useful info about the database. for example encoding. you can change encoding in the config file by putting default-character-set=utf8 after [mysqld]
mysqladmin -u root password the_new_pass	set the root password for the first time
mysqladmin -u root -p'abc' password '123456'	change the root password
mysql -u root -pxxxx < sql_scripts.sql	Run a sql file from command

http://www.yolinux.com/TUTORIALS/LinuxTutorialMySQL.html

Remote Access to MySQL

My MySQL is behind a firewall on a Linux machine and I want to access it using remote clients.

1. In "/etc/mysql/my.cnf" change bind-address to the IP of you server or totally comment bind-address.
2. Open MySQL port on your firewall (usually 3306)
3. restart MySQL server using "service mysql restart" command and finally:
4. Grand access to a user:

GRANT ALL ON your_db_name.* TO your_user_name@'your_gateway_ip' IDENTIFIED BY 'that_user_password';  
GRANT ALL ON mytestdb.* TO reza@'192.168.1.1' IDENTIFIED BY 'test';
select User,Host from mysql.user; // to see the list of grants;

• To disable remote tcp access add: skip-networking to /etc/my.conf

1NF

A table with a unique key and without any nullable columns is in 1NF.

2NF

3NF

It should be 1NF and 2NF and also every non-key attribute should be directly dependent only to one key. If some data in a column is repeating then we are not in 3NF. The following is not 3NF:

key-col	non-key-col	non-key-col2
1	a	d
2	b	d
3	c	e

JDBC Connection Code

All you need is the driver's jar file for whatever database your want to use.

public static void main(String[] args) {
        Connection conn = null;
 
        try{
            String userName = "root";
            String password = "reza";
            String url = "jdbc:mysql://localhost/test";// For Oracle: jdbc:oracle:thin:@machine_name:1521:database_name
            Class.forName ("com.mysql.jdbc.Driver").newInstance ();// For Oracle: oracle.jdbc.driver.OracleDriver
            conn = DriverManager.getConnection (url, userName, password);
            System.out.println ("Database connection established");
        } catch (Exception e) {
            System.err.println ("Cannot connect to database" + e);
        } finally {
            if (conn != null) {
                try {
                    conn.close ();
                    System.out.println ("Database connection terminated");
                } catch (Exception e) {  }
            }
        }
}

Null Handling

Basic SQL comparison operators always return Unknown when comparing anything with Null, so the SQL standard provides for two special Null-specific comparison predicates. The IS NULL and IS NOT NULL predicates test whether data is, or is not, Null.

where i = NULL  -- unknown
where i IS NULL   -- ok
where i IS NOT NULL  -- ok

Make MySQL understand UTF-8

In /etc/my.cnf include: default-character-set=utf8 and default-collation=utf8_general_ci.
Also when creating tables: (CREATE | ALTER) TABLE … DEFAULT CHARACTER SET utf8;