From mywiki

Contents 1 Frequent used SQL commands 1.1 SELECT 1.2 WHERE condition 1.2.1 Pattern Matching - LIKE 1.2.1.1 Starts with character 1.2.1.2 Ends with character 1.2.1.3 Contains a character 1.2.1.4 Second character is 'a' 1.2.2 Regular Expression 1.2.2.1 Match any single character '.' 1.2.2.2 Match zero or more character '*' 1.2.2.3 Match range 1.2.2.4 Match begining and end 1.2.2.5 {n} (“repeat-n-times”) operator 1.2.2.6 Case sensitive - BINARY 1.2.3 String functions 1.3 ORDER BY 1.4 INSERT 1.4.1 Insert many rows into a table from one or many tables. 1.4.2 Copy entire table ( duplicate table ) 1.5 UPDATE 1.5.1 UPDATE value 1.5.2 UPDATE AND REPLACE 1.6 DELETE 1.6.1 Empty Table 1.7 Drop Table 1.8 UNION 1.9 Merging Data from Multiple Tables: Joining 1.10 INNER JOIN 1.11 LEFT JOIN 1.12 RIGHT JOIN 1.13 Group Operations 1.13.1 Counting Rows 1.13.2 GROUP BY 1.13.3 MAX 1.14 Loading data from a file into a table , LOAD DATA INFILE 1.14.1 Example # 1 - local 1.14.2 Example # 2 - server 1.14.3 Example # 3 - terminated 1.14.4 Example # 4 - IGNORE 1.14.5 Example # 5 - variables @ 1.14.6 Example # 6 - ignore fields 1.15 User-Defined Variables 2 TABLES 2.1 Create Tables 2.2 Show fields 3 General SQL 3.1 Built-in Functions , keywords 3.1.1 Date and Time 3.1.2 Math 3.1.3 Strings 3.1.4 Misc 3.1.5 NULL 3.2 Database 3.2.1 CONNECT 3.2.2 USE 3.2.3 CREATE 3.2.4 DROP 3.2.5 EXIT 3.3 Variables 3.4 show ... 3.5 File Path 3.6 Language Structure 3.6.1 Strings 4 Tools 4.1 (1) Backup - mysqlimport — A Data Import Program 4.2 (2) Backup - mysqldump 4.3 Restore 4.3.1 UPDATE A TABLE 4.3.2 Moving Data Directly Between Databases 4.4 Table -> File , SELECT ... INTO OUTFILE 4.5 Using mysql in Batch Mode - Restore 5 Storage Engine 6 Tools 7 Links 8 Next 8.1 Index 8.1.1 DROP A COLUMN 8.1.2 PRIMARY KEY 8.2 my.ini 9 Stop/Start mysql 10 Tools 10.1 Reverse engineering 10.2 Tips

Frequent used SQL commands

SELECT

  SELECT columns 
  FROM  table(s)
  WHERE  criteria

WHERE condition

Pattern Matching - LIKE

Starts with character

 WHERE field LIKE 'a%';

Ends with character

 WHERE field LIKE '%a';

Contains a character

 WHERE field LIKE '%a%';

Second character is 'a'

 WHERE field LIKE '_a%'  : indicates a single character at that position in the pattern ( ex Jaber , Majed )

Regular Expression

Match any single character '.'

 WHERE field REGEXP '[abc]';

Match zero or more character '*'

 WHERE field REGEXP 'a*';

Match range

• All letters

 WHERE field REGEXP '[a-z]';

• All digits

 WHERE field REGEXP '[0-9];

• A REGEXP pattern match succeeds if the pattern matches anywhere in the value being tested.
• This differs from a LIKE pattern match, which succeeds only if the pattern matches the entire value.

Match begining and end

• To anchor a pattern so that it must match the beginning or end of the value being tested, use ‘^’ at the beginning or ‘$’ at the end of the pattern.

  WHERE field REGEXP '^a$'

{n} (“repeat-n-times”) operator

• fields containing exactly five characters

  WHERE field REGEXP '^.....$'; 

  WHERE field REGEXP '^.{5}$';

Case sensitive - BINARY

• fileds starts with small a.

  WHERE field REGEXP BINARY '^a';

String functions

• http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

ORDER BY

 ORDER BY field ASC / DESC
 ORDER BY field1 sortingOrder, field2 sortingOrder,

INSERT

  INSERT INTO tableName ( fieldName1, fieldName2, …, fieldNameN )  VALUES ( value1, value2, …, valueN )

 e.g., 'OMalley' = O'Mally

Insert many rows into a table from one or many tables.

 INSERT INTO new_table (fld_id,...)
 SELECT old_table.field 
 FROM old_table
 WHERE old_table.field > 100;

Copy entire table ( duplicate table )

 INSERT INTO new_table 
 SELECT *
 FROM old_table

UPDATE

UPDATE value

 UPDATE tableName
 SET fieldName1 = value1, fieldName2 = value2, …, fieldNameN = valueN
 WHERE criteria

UPDATE AND REPLACE

 UPDATE tableName 
 SET fieldName1  = ( REPLACE ( fieldName1  ,'oldText','newText'));

• No AND ! ( set x=y and w=d , this result in first column update only )
• Multiple tables , must list them all , even if not updates.

DELETE

  DELETE FROM tableName WHERE criteria

• This could be danger !!

Lets say a table contains a field ID with values ( 1,2,3)

  DELETE from TABLE where ID = 1 and 2;

Result = delete the record with ID = 1

 DELETE from TABLE where ID =4 or 5;

Result = delete all the records in the tables , since the values 5 = true !

The correct query will be

 DELETE from TABLE where ID=4 or ID=5;

Empty Table

  DELETE FROM tableName

  TRUNCATE [TABLE] tbl_name

Drop Table

DROP [TEMPORARY] TABLE [IF EXISTS]

   tbl_name [, tbl_name] ...
   [RESTRICT | CASCADE]

UNION

 SELECT id, name FROM my_table  
 UNION   
 SELECT id, name FROM my_table;

Merging Data from Multiple Tables: Joining

  SELECT firstName, lastName, isbn
  FROM authors, authorISBN
  WHERE authors.authorID = authorISBN.authorID
  ORDER BY lastName, firstName

• The query merges the firstName and lastName fields from table authors and the isbn field from table authorISBN and sorts the results in ascending order by last-

Name and firstName.

• Only return matches.

Where field is NULL

INNER JOIN

  SELECT field1 
  FROM table1 INNER JOIN table2 ON table1.field = table2.field

• The query uses an INNER JOIN to combine the tables

• Sometimes it is useful to join a table to itself, if you want to compare records in a table to other records in that same table.

  SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
  FROM pet AS p1 INNER JOIN pet AS p2
  ON p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';

LEFT JOIN

SELECT field1 
FROM table1 LEFT JOIN table2 
ON table1.field = table2.field

• Include all the rows from the table that appears before the LEFT JOIN in our SQL statement. If nothing corresponds in the right table, display NULL.
• All matches + no matches on LEFT returns NULL

RIGHT JOIN

SELECT field1 
FROM table1 RIGHT JOIN table2 
ON table1.field = table2.field

• Include all the rows from the table that appears before the RIGHT JOIN in our SQL statement. If nothing corresponds in the right table, display NULL.
• All matches + no matches on RIGHT returns NULL

Group Operations

Counting Rows

  SELECT COUNT(*) FROM TABLE;

GROUP BY

• GROUP BY clause must be used when mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is

  SELECT field1, COUNT(field2) FROM table GROUP BY field1

MAX

• With NO SPACE between MAX and ( ?!

SELECT MAX(field) FROM tableName

• Row Holding the Maximum of a Certain Column

 SELECT field1
 FROM   tabele
 WHERE field2 =(SELECT MAX(field2) FROM table);

OR

 SELECT field1 
 FROM table 
 ORDER BY field2 DESC
 LIMIT 1;

• Note: If there were several most expensive field1, each with a values of 10.5, the LIMIT solution would show only one of them.

Loading data from a file into a table , LOAD DATA INFILE

• The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. The filename must be given as a literal string.

   LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
   [REPLACE | IGNORE]
   INTO TABLE tbl_name
   [FIELDS
       [TERMINATED BY 'string']
       [[OPTIONALLY] ENCLOSED BY 'char']
       [ESCAPED BY 'char']
   ]
   [LINES
       [STARTING BY 'string']
       [TERMINATED BY 'string']
   ]
   [IGNORE number LINES]
   [(col_name_or_user_var,...)]
   [SET col_name = expr,...)]

• If you use LOW_PRIORITY, execution of the LOAD DATA statement is delayed until no other clients are reading from the table.

Example # 1 - local

  LOAD DATA LOCAL INFILE 'C:/Documents and Settings/xyz/Desktop/a.txt' INTO table t;

Example # 2 - server

Store the data into the database directory folder ( ex C:\Program Files\MySQL\MySQL Server 5.0\data\databaseName)

  LOAD DATA INFILE 'a.txt' INTO table t;

• If LOCAL is specified, the file is read by the client program on the client host and sent to the server. The file can be given as a full pathname to specify its exact location. If given as a relative pathname, the name is interpreted relative to the directory in which the client program was started.

• If LOCAL is not specified, the file must be located on the server host and is read directly by the server. The server uses the following rules to locate the file:
  1. If the filename is an absolute pathname, the server uses it as given.
  2. If the filename is a relative pathname with one or more leading components, the server searches for the file *#relative to the server's data directory.

• Using LOCAL is a bit slower than letting the server access the files directly, because the contents of the file must be sent over the connection by the client to the server. On the other hand, you do not need the FILE privilege to load local files.

• If you specify a FIELDS clause, each of its subclauses (TERMINATED BY, [OPTIONALLY] ENCLOSED BY, and ESCAPED BY) is also optional, except that you must specify at least one of them.

If you specify no FIELDS clause, the defaults are the same as if you had written this:

  FIELDS TERMINATED BY '\t' ENCLOSED BY ' ' ESCAPED BY '\\'

• If you specify no LINES clause, the defaults are the same as if you had written this:

  LINES TERMINATED BY '\n' STARTING BY ' '

Example # 3 - terminated

  load data local infile 'C:/Documents and Settings/xyz/Desktop/b.txt' 
  into table t 
  fields terminated by '\t' 
  lines terminated by '\r\n';

  LOAD DATA local INFILE 'C:/Documents and Settings/xyz/Desktop/b.txt' 
  INTO TABLE t
  FIELDS TERMINATED BY ','  
  LINES STARTING BY 'xxx';

• If the data file looks like this:
  • xxx1,"abc"
  • something xxx2,"def"
  • 3,"ghi"

The resulting rows will be (1,"abc") and (2,"def"). The third row in the file is skipped because it does not contain the prefix.

• The IGNORE number LINES option can be used to ignore lines at the start of the file. For example, you can use IGNORE 1 LINES to skip over an initial header line containing column names:

Example # 4 - IGNORE

  LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;

  LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\n';

By default, when no column list is provided at the end of the LOAD DATA INFILE statement, input lines are expected to contain a field for each table column. If you want to load only some of a table's columns, specify a column list:

  LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);

• You must also specify a column list if the order of the fields in the input file differs from the order of the columns in the table. Otherwise, MySQL cannot tell how to match input fields with table columns.

• Using colums didn't work with lines terminated by.

Example # 5 - variables @

With user variables, the SET clause enables you to perform transformations on their values before assigning the result to columns.

User variables in the SET clause can be used in several ways. The following example uses the first input column directly for the value of t1.column1, and assigns the second input column to a user variable that is subjected to a division operation before being used for the value of t1.column2:

 LOAD DATA INFILE 'file.txt'
 INTO TABLE t1
 (column1, @var1)
 SET column2 = @var1/100;

The SET clause can be used to supply values not derived from the input file. The following statement sets column3 to the current date and time:

 LOAD DATA INFILE 'file.txt'
 INTO TABLE t1
 (column1, column2)
 SET column3 = CURRENT_TIMESTAMP;

Example # 6 - ignore fields

You can also discard an input value by assigning it to a user variable and not assigning the variable to a table column:

 LOAD DATA INFILE 'file.txt'
 INTO TABLE t1
 (column1, @dummy, column2, @dummy, column3);

User-Defined Variables

  SELECT @var1:=MIN(field1)FROM table;
  SELECT * FROM table WHERE field=@var1

TABLES

Create Tables

  create table names (
   personID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1),    
   firstName varchar (30) NOT NULL,
  lastName varchar (30) NOT NULL,
  constraint pk_name primary key (personID)
  );

  create table addresses (
  addressID int DEFAULT AUTOINCREMENT,
  personID int NOT NULL,
  address1 varchar (50),
  address2 varchar (50),
  city varchar (30),
  state varchar (2),
  zipcode varchar (10),
  constraint fk_addresses foreign key (personID) references names (personID),
  constraint pk_addresses primary key (addressID)
  );

Show fields

 DESCRIBE tablename

OR

 DESC tablename

General SQL

• MySQL is not case sensitive . SELECT = select = SEleCT

Built-in Functions , keywords

 SELECT NOW (),CURRENT_DATE;

Date and Time

CURRENT_DATE	2006-10-20
CURDATE()	2006-10-20
DATE_ADD(CURDATE , INTERVAL 1 MONTH)	2006-11-20
MONTH(date)	10
NOW()	2006-10-20 09:09:15
YEAR ( date )	2006

• DAY
• MONTH
• YEAR

Math

• MOD ( 120 , 100) ->10

SQL - Aggregates

• sum(column)
• avg(column)
• min(column)
• max(column)
• count(*)
  • counts the # of rows that meet the where condition
• count(distinct column)
  • counts the # of rows that have unique values in the named column

Strings

• RIGHT ('abc',3) -> bc
• LEFT ('abc',2) -> ab

Misc

• VERSION() ->
• USER() ->

NULL

• 1 = NULL -> NULL
• 1<> NULL -> NULL
• 1 > NULL -> NULL
• 1 < NULL -> NULL
• 1 IS NULL -> 0
• 1 IS NOT NULL -> 1

Database

CONNECT

 shell> mysql -h host -u user -p[password] 
 mysql> 

USE

 Use database_name;

CREATE

 CREATE DATABASE database_name;

DROP

 DROP DATABASE database_name;

EXIT

 mysql> quit

Variables

• variables , just use @var , without any declaration.

show ...

  show variables;
  show warnings;
  show tables;
  show columns from table_name;
  SHOW INDEX FROM tbl_name

File Path

• Windows pathnames are specified using / rather than \.
  1. forward slash , 'c:/path/subpath/'
  2. If you do use \, you must double them\\.

Language Structure

Strings

• A string is a sequence of bytes or characters, enclosed within either single quote (‘'’) or double quote (‘"’) characters. Examples:

 'a string'
 "another string"

Tools

(1) Backup - mysqlimport — A Data Import Program

• The mysqlimport client provides a command-line interface to the LOAD DATA INFILE SQL statement. Most options to mysqlimport correspond directly to clauses of LOAD DATA INFILE syntax.

  shell> mysqlimport [options] db_name textfile1 [textfile2 ...]

For each text file named on the command line, mysqlimport strips any extension from the filename and uses the result to determine the name of the table into which to import the file's contents. For example, files named patient.txt, patient.text, and patient all would be imported into a table named patient.

(1)

 --fields-terminated-by=...,   --fields-enclosed-by=...,   --fields-optionally-enclosed-by=...,
 --fields-escaped-by=...

These options have the same meaning as the corresponding clauses for LOAD DATA INFILE. See

(2)

  --force, -f

Ignore errors. For example, if a table for a text file does not exist, continue processing any remaining files. Without --force, mysqlimport exits if a table does not exist.

(3)

  --host=host_name, -h host_name

Import data to the MySQL server on the given host. The default host is localhost.

(4) --local, -L

Read input files locally from the client host.

(5)

  --password[=password], -p[password]

The password to use when connecting to the server. If you use the short option form (-p), you cannot have a space between the option and the password. If you omit the password value following the --password or -p option on the command line, you are prompted for one.

(6)

  --user=user_name, -u user_name

The MySQL username to use when connecting to the server.

(2) Backup - mysqldump

• from the shell >
• No space between p and password

  mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]

• Choose the host

  mysqldump -h [localhost] -u user -p[password] db_name > backup.sql

• Only specified tables

  mysqldump -h localhost -u user -p[password]  db_name tbl_name -w  "condition " > out.sql

  -w, --where=name    Dump only selected records; QUOTES mandatory!

• Backing up only the Database Structure

  mysqldump --no-data db_name  > structurebackup.sql   

• Multiple Databases

 mysqldump -u [root] -p [password] --databases DB1 DB2 DB3 > multibackup.sql 
 mysqldump --all-databases> alldatabases.sql 

• Compressing your Backup file on the Fly

 mysqldump --all-databases | bzip2 -c >databasebackup.sql.bz2 

 mysqldump --all-databases | gzip >databasebackup.sql.gz 

• A Shell Script for Automating Backups

 #!/bin/sh
 date=`date -I`
 mysqldump --all-databases | gzip > /var/backup/backup-$date.sql.gz

Restore

• Restore

 mysql -u [username] -p [password] [database_to_restore] < [backupfile]

• Restore zipped backup files by first uncompressing its contents and then sending it to mysql.

 gunzip < back.sql.sql.gz | mysql -u [user] -p [password] DB

• You can also combine two or more backup files to restore at the same time, using the cat command. Here's how you can do that.

 cat backup1.sql backup.sql | mysql -u [user] -p [password]

UPDATE A TABLE

• When restoring portion of the DB or table , be sure that you don't delete the tables.

Moving Data Directly Between Databases

You can directly copy data to the new database without having to create a database backup on your machine and restoring the same on the new server

  mysqldump -u [user] -p [password] DB | mysql --host=localhost -C DB

Table -> File , SELECT ... INTO OUTFILE

To write data from a table to a file

Using mysql in Batch Mode - Restore

 shell> mysql -h host -u user -p < batch-file
 Enter password: ********

• When you use mysql this way, you are creating a script file, then executing the script.

• If you want the script to continue even if some of the statements in it produce errors, you should use the --force command-line option.

• You can catch the output in a file for further processing:

 shell> mysql < batch-file > mysql.out

• If you want to get the interactive output format in batch mode, use mysql -t

Storage Engine

• MyISAM = very fasy
• InooDB = safe

Tools

• MySQL Browser
• MySQL Control Center
• http://www.surveymonkey.com/

Links

• http://www.webdevelopersnotes.com/tutorials/sql/
• http://www.tizag.com/mysqlTutorial/

Next

Index

 ALTER TABLE table ADD INDEX field (field);  

DROP A COLUMN

  ALTER TABLE 'table_name' DROP 'col_name'

PRIMARY KEY

  ALTER TABLE `table_name` DROP PRIMARY KEY, ADD PRIMARY KEY(`PK1`, .. );

• Innet Join
• Outer Join
• Left , right
• sync DB

my.ini

• Got a packet bigger than max_allowed_packet bytes.
  • Change in my.ini

• set new variables there
• set-variable=lower_case_table_names=0

Stop/Start mysql

• net stop mysql
• net start mysql

Tools

Reverse engineering

• http://www.microolap.com/

Tips

• Be aware of case sensitivity ( -w != -W)