115.70.54.180 at 18:15, 30 January 2023

2023-01-30T18:15:39Z

← Older revision		Revision as of 13:15, 30 January 2023
Line 1:		Line 1:
	[[KBH Software]]<br> INDEX - SQL STATEMENTS PROBLEMS -		'''Return to - [[Main_Page]]''' - [[KBH Software]]<br> INDEX - SQL STATEMENTS PROBLEMS -

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

115.70.54.180: Created page with "KBH Software
INDEX - SQL STATEMENTS PROBLEMS - MYSQL String functions: [http://dev.mysql.com/doc/refman/5.0/en/string-functions.html dev.mysql.com/doc/refman/5..."

2020-10-10T01:52:50Z

Created page with "KBH Software INDEX - SQL STATEMENTS PROBLEMS - MYSQL String functions: [http://dev.mysql.com/doc/refman/5.0/en/string-functions.html dev.mysql.com/doc/refman/5..."

New page

[[KBH Software]] INDEX - SQL STATEMENTS PROBLEMS - 

MYSQL String functions: [http://dev.mysql.com/doc/refman/5.0/en/string-functions.html 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'''

# update items_import set Code = replace (Code, "&", "@") WHERE Id > 0;
# UPDATE `items_import` SET `Code` = CONCAT('CRO_',LEFT(`Name`,4)) WHERE `Supplier` = 'Crowson'
# 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):'''

# 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:'''

# 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:'''
# 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

MYSQL - Revision history

115.70.54.180 at 18:15, 30 January 2023

115.70.54.180: Created page with "KBH Software INDEX - SQL STATEMENTS PROBLEMS - MYSQL String functions: [http://dev.mysql.com/doc/refman/5.0/en/string-functions.html dev.mysql.com/doc/refman/5..."

115.70.54.180: Created page with "KBH Software
INDEX - SQL STATEMENTS PROBLEMS - MYSQL String functions: [http://dev.mysql.com/doc/refman/5.0/en/string-functions.html dev.mysql.com/doc/refman/5..."