MySQL & SQL Queries

MySQL import export in Linux

MySQL Import
$ ssh loginname@server.hosting.com

$ mysql -u username -p -h localhost data-base-name < data.sql

$ mysql -u username -p -h 202.54.1.10 databasename < data.sql

$ mysql -u username -p -h mysql.hosting.com database-name < data.sql

$ mysql -u username -p -h 202.54.1.10 < data.sql

MySQL Dump/Export
mysqldump -u [user] -p [database_name] > [backupfile].dump

mysqldump --opt -u [user_name] -p [database_name] > [backup_file].dump

mysqldump --opt -u [user_name] -p [database_name] |
gzip > [backup_file].dump.gz

mysql [database_name] < [backup_file].dump



How to create index to date field
create index indexName on tableName(datefield);

Full text index example query & Features
SELECT * FROM fulltext_sample WHERE MATCH(copy) AGAINST('love');

Features:
Excludes partial words

Excludes words less than 4 characters in length (3 or less)

Excludes words that appear in more than half the rows (meaning at least 3 rows are required)

Hyphenated words are treated as two words
Rows are returned in order of relevance, descending
Words in the stopword list (common words) are also excluded from the search results. The stopword list is based upon common English words, so if your data is used for a different purpose, you'll probably want to change the list. Unfortunately, doing so at present is not easy. You'll need to edit the file myisam/ft_static.c. recompile MySQL, and rebuild the indexes! To save you hunting through the source, or if you have a binary version of MySQL, here is a list of stopwords. Note that these can and do change with different versions. To be absolutely sure, you'll have to check the specific list for your version.


myisamchk examples
myisamchk [options] tbl_name ...

Description

The myisamchk utility gets information about your database tables or checks, repairs, or optimizes them. myisamchk works with MyISAM tables (tables that have .MYD and .MYI files for storing data and indexes).

Invoke myisamchk like this:

shell> myisamchk [options] tbl_name ...
The options specify what you want myisamchk to do. They are described in the following sections. You can also get a list of options by invoking myisamchk --help.
With no options, myisamchk simply checks your table as the default operation. To get more information or to tell myisamchk to take corrective action, specify options as described in the following discussion.

tbl_name is the database table you want to check or repair. If you run myisamchk somewhere other than in the database directory, you must specify the path to the database directory, because myisamchk has no idea where the database is located. In fact, myisamchk doesn't actually care whether the files you are working on are located in a database directory. You can copy the files that correspond to a database table into some other location and perform recovery operations on them there.

You can name several tables on the myisamchk command line if you wish. You can also specify a table by naming its index file (the file with the .MYI suffix). This allows you to specify all tables in a directory by using the pattern *.MYI. For example, if you are in a database directory, you can check all the MyISAM tables in that directory like this:

shell> myisamchk *.MYI
If you are not in the database directory, you can check all the tables there by specifying the path to the directory:
shell> myisamchk /path/to/database_dir/*.MYI
You can even check all tables in all databases by specifying a wildcard with the path to the MySQL data directory:
shell> myisamchk /path/to/datadir/*/*.MYI
The recommended way to quickly check all MyISAM tables is:
shell> myisamchk --silent --fast /path/to/datadir/*/*.MYI

MySQL Dump examples
The most common use of mysqldump is probably for making a backup of an entire database:

shell> mysqldump --opt db_name > backup-file.sql
You can read the dump file back into the server like this:
shell> mysql db_name < backup-file.sql
Or like this:
shell> mysql -e "source /path-to-backup/backup-file.sql" db_name
mysqldump is also very useful for populating databases by copying data from one MySQL server to another:
shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name
It is possible to dump several databases with one command:
shell> mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql
To dump all databases, use the --all-databases option:
shell> mysqldump --all-databases > all_databases.sql
For InnoDB tables, mysqldump provides a way of making an online backup:
shell> mysqldump --all-databases --single-transaction > all_databases.sql

Advantages of MySQL Indexes
Generally speaking, MySQL indexing into database gives you three advantages:

Query optimization: Indexes make search queries much faster.
Uniqueness: Indexes like primary key index and unique index help to avoid duplicate row data.
Text searching: Full-text indexes in MySQL version 3.23.23, users have the opportunity to optimize searching against even large amounts of text located in any field indexed as such.
Disadvantages of MySQL indexes
When an index is created on the column(s), MySQL also creates a separate file that is sorted, and contains only the field(s) you're interested in sorting on.

Firstly, the indexes take up disk space. Usually the space usage isn’t significant, but because of creating index on every column in every possible combination, the index file would grow much more quickly than the data file. In the case when a table is of large table size, the index file could reach the operating system’s maximum file size.

Secondly, the indexes slow down the speed of writing queries, such as INSERT, UPDATE and DELETE. Because MySQL has to internally maintain the “pointers” to the inserted rows in the actual data file, so there is a performance price to pay in case of above said writing queries because every time a record is changed, the indexes must be updated. However, you may be able to write your queries in such a way that do not cause the very noticeable performance degradation.


Create database & privileges in Linux Machine:

mysql> create database amarokdb;

mysql> grant usage on *.* to amarokuser@localhost identified by 'amarokpasswd';

mysql> grant all privileges on amarokdb.* to amarokuser@localhost ;



Simple Stored Procedure with CURSORS
CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a CHAR(16);
DECLARE b,c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur1;
OPEN cur2;

read_loop: LOOP
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF done THEN
LEAVE read_loop;
END IF;
IF b < c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END LOOP;

CLOSE cur1;
CLOSE cur2;
END;

VIEW example
CREATE OR REPLACE VIEW 'vw_students1'
AS
SELECT
lastname ,
firstname ,
concat(firstname,' ',lastname) as "Fullname_fl",
concat(lastname,', ',firstname) as "Fullname_lf",
birth_dttm ,
DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birth_dttm)), '%Y')+0 as "Age"
FROM students
GO

Advantages of views:

1. View the data without storing the data into the object.

2. Restict the view of a table i.e. can hide some of columns in the tables.

3. Join two or more tables and show it as one object to user.

4. Restict the access of a table so that nobody can insert the rows into the table.

Disadvatages:

1. Can not use DML operations on this.

2. When table is dropped view becomes inactive.. it depends on the table objects.

3. It is an object so it occupies space.

&&&&&&&

1. hiding the data.

2. you can use two tables data in view.

3. security will be there.

disadvantages

1.when table is not there view will not work.

2. dml is not possible if that is more than one table.

3. it is also database object so it will occupy the space.