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
update TABLE_NAME set X=Y where ZZZ update 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. Also we can have "not 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)
select a,b,count (*) from v,… where … group by v.a having count(*) > 1; Finding duplicates. More Oracle way.
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
alter table price drop column date_modified; drop column
LIMIT LIMIT 1,5: the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1)
Drop database schema DROP DATABASE x
ALTER TABLE AD MODIFY COLUMN ADDRESS VARCHAR(100); Modify column
ALTER TABLE table_name RENAME COLUMN old_name to new_name; Rename column
ALTER TABLE contacts ADD email VARCHAR(60); Add column to the end
mysqldump - -no-data - -tables -u YOUR_USER_NAME -p DATABASE_YOU_WANT_SCRIPTED » FILENAME.sql export. remote - -nodata if you need data to be exported as well
mysql -u username -pPassword dbname < file import
order order by ASC or DESC
select * from all_tables all the tables in Oracle

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

MySQL Data Types

  • For floating point just use DOUBLE. FLOAT, DECIMAL and NUMERIC behave stupid !

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

Normal Forms

  • 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 after both [client] and [mysqld] and restart mysql. Also when creating tables you can have: (CREATE | ALTER) TABLE … DEFAULT CHARACTER SET utf8;

Where 1=1

This is a lazy (stupid?) way if you want to make the where clause right when not sure if you will add more where statements or not.

Joins

  • Inner Join: Return rows when there is at least one match in both tables - This is the normal join
  • Left Join: Return all rows from the left table, even if there are no matches in the right table
  • Right Join: Return all rows from the right table, even if there are no matches in the left table
  • Full Join: Return rows when there is a match in one of the tables
we have ad and promotion in a m-m relation with ad_promotion in the middle.

select * from ad a left join (ad_promotion adp) on  (adp.ad_id = a.id ) 
left join ad_category cat on a.cat_id =  cat.id
group by  adp.payment_status, a.ad_status
having a.ad_status = 'ACTIVE' and (adp.payment_status = 'PAID' or adp.payment_status is null)  
and cat.code = 'sale-clothes'

H2

Is a small and memory based database and very fast! supporting jdbc.

Database Client

SQuirreL SQL is a general purpose database client that can connect to any database.

NoSQL

I hate this name actually and would prefer non-relational databases. They could be of different types

  1. Column Family (Cassandra - a unique key linked to a set of key/values)
  2. key/value (no schema)
  3. Graph
  4. Document (MangoDB. Similar structure as json. have relation and FK)
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License