MYSQL

KBH Software
INDEX - SQL STATEMENTS PROBLEMS - 

MYSQL String functions: dev.mysql.com/doc/refman/5.0/en/string-functions.html

IMPORT MYSQL TO LOCAL PC

Import MYSQL database to local PC \bin folder The /bin folder contains all the mysql*.exe programs

Home folder is:  C:\Data\EasyPHP-DevServer-14.1VC11\binaries\mysql\bin
 Work Foler is:  C:\Data\EasyPHP-Devserver-16.1\eds-binaries\dbserver\mysql5711x86x160531140214\bin

• Copy backup file X:\Bak\Data\Sols\Msql_Dec.gz to /bin/Folder
• Extract Msql_Dec.gz to above folder creating file Msql_Dec
• phpMyAdmin Select all files in dec database - Drop all Files
• If above step done correctly no need to create new database dec with colition utf8_general_ci
• mysql -u root -p -h localhost dec < Msql_Dec

  Copy above to Xplorer2 - F10 (Dos Command)  [Ret] opens a dos window  
  Paste
  On password - Just enter
  Wait 2-3 minutes 
  exit

New database populated

MSQL CHEAT SHEET

03/08/2010 COPY WORK DATA TO HOME PC . See notes on laptop at C:\Data\EasyPHP-5.3.2\mysql\bin\-command-lst.txt

Start mysql:

mysql -h localhost -u root

Selecting a database:

mysql> USE database; e.g. use dec

Listing databases:

mysql> SHOW DATABASES;

Listing tables in a db:

mysql> show tables;

Describing the format of a table:

mysql> DESCRIBE table;

Creating a database:

mysql> CREATE DATABASE db_name;

Creating a table:

mysql> CREATE TABLE table_name (field1_name TYPE(SIZE), field2_name TYPE(SIZE)); Ex: mysql> CREATE TABLE pet (name VARCHAR(20), sex CHAR(1), birth DATE);

Duplicate a table  - kbh 30/06/10

CREATE TABLE zz_items_30_06_10 SELECT * FROM items
Load tab-delimited data into a table:

mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table_name; (Use \n for NULL)

Inserting one row at a time:

mysql> INSERT INTO table_name VALUES ('MyName', 'MyOwner', '2002-08-31'); (Use NULL for NULL)

Retrieving information (general):

mysql> SELECT from_columns FROM table WHERE conditions; All values: SELECT * FROM table; Some values: SELECT * FROM table WHERE rec_name = "value"; Multiple critera: SELECT * FROM TABLE WHERE rec1 = "value1" AND rec2 = "value2";

Reloading a new data set into existing table:

mysql> SET AUTOCOMMIT=1; # used for quick recreation of table mysql> DELETE FROM pet; mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table;

Fixing all records with a certain value:

mysql> UPDATE table SET column_name = "new_value" WHERE record_name = "value";

Selecting specific columns:

mysql> SELECT column_name FROM table;

Retrieving unique output records:

mysql> SELECT DISTINCT column_name FROM table;

Sorting:

mysql> SELECT col1, col2 FROM table ORDER BY col2; Backwards: SELECT col1, col2 FROM table ORDER BY col2 DESC;

Date calculations:

mysql> SELECT CURRENT_DATE, (YEAR(CURRENT_DATE)-YEAR(date_col)) AS time_diff [FROM table]; MONTH(some_date) extracts the month value and DAYOFMONTH() extracts day.

Pattern Matching:

mysql> SELECT * FROM table WHERE rec LIKE "%blah%"; (% is wildcard - arbitrary # of chars) Find 5-char values: SELECT * FROM table WHERE rec like "_____"; (_ is any single character)

String Editing/Manipulation

1. update items_import set Code = replace (Code, "&", "@") WHERE Id > 0;
2. UPDATE `items_import` SET `Code` = CONCAT('CRO_',LEFT(`Name`,4)) WHERE `Supplier` = 'Crowson'
3. FIND DUPLICATES [SELECT id, Code, count(Code) FROM items WHERE `Supplier` = 'Crowson' GROUP BY Code HAVING count(Code) > 1]

Extended Regular Expression Matching:

mysql> SELECT * FROM table WHERE rec RLIKE "^b$"; (. for char, [...] for char class, * for 0 or more instances ^ for beginning, {n} for repeat n times, and $ for end) (RLIKE or REGEXP) To force case-sensitivity, use "REGEXP BINARY"

Counting Rows:

mysql> SELECT COUNT(*) FROM table;

Grouping with Counting:

mysql> SELECT owner, COUNT(*) FROM table GROUP BY owner; (GROUP BY groups together all records for each 'owner')

Selecting from multiple tables:

(Example) mysql> SELECT pet.name, comment FROM pet, event WHERE pet.name = event.name; (You can join a table to itself to compare by using 'AS')

Currently selected database:

mysql> SELECT DATABASE();

Maximum value:

mysql> SELECT MAX(col_name) AS label FROM table;

Auto-incrementing rows:

mysql> CREATE TABLE table (number INT NOT NULL AUTO_INCREMENT, name CHAR(10) NOT NULL); mysql> INSERT INTO table (name) VALUES ("tom"),("dick"),("harry");

Adding a column to an already-created table:

mysql> ALTER TABLE tbl ADD COLUMN [column_create syntax] AFTER col_name;

Removing a column:

mysql> ALTER TABLE tbl DROP COLUMN col; (Full ALTER TABLE syntax available at mysql.com.)

Batch mode (feeding in a script):

1. mysql -u user -p < batch_file

(Use -t for nice table layout and -vvv for command echoing.) Alternatively: mysql> source batch_file;

Backing up a database with mysqldump:

1. mysqldump --opt -u username -p database > database_backup.sql

(Use 'mysqldump --opt --all-databases > all_backup.sql' to backup everything.) (More info at MySQL's docs.)

Restoring database from a mysqldump:

1. mysql -uroot -p < partial_database_backup.sql

PROBLEMS

Repair Table(Sympton internal error) e.g. REPAIR TABLE fixtures; Make a backup of table first

• 13/06/06 Thi finally fixed problem
  • REPAIR TABLE searchindex; so that you can trick table so you can view
  • searchindex opperations - copy table to temp
    
  • drop searchindex
  • copy temp to searchindex - seamed to work

03/06 run from within MYSQL as an SQL command
05/04/06 did repair to (REPAIR TABLE searchindex;) - Seamed to work
(check table searchindex; ) will check integrity

PROBLEM APPEARS TO BE CAUSED BY USING SQUARE BRACKETS I.E. "[" OR ] for a non valid link