My SQL


MySQL on mac: download and install the dmg file.
To start go to computer settings, search for 'mysql' there you can start/stop the server
Go to mysql workbench to reset the root password, then begin using mysql:
$ /usr/local/mysql/bin/mysql -u root -p  
mysql> 




Alter current table to add a foreign key source
ALTER TABLE `binary_relation` 
ADD CONSTRAINT `fk_binary_relation_rule`
    FOREIGN KEY (`rule_id`) REFERENCES `rule` (`id`) ON DELETE CASCADE; 
If it didn't work due to existing rows in the table
update pixel set `east_neighbor_pixel_id`=1    //initialize all current ones to a dummy existing id. 

ALTER TABLE `table1` 
DROP FOREIGN KEY `fk_name`;


$ sudo apt-get install mysql-server mysql-client
password for root username: root

Source

mysql configuration file:
/etc/mysql/my.conf



MySQL Command Line:
$ mysql -u my_username -p   //will prompt for password.
mysql> help   //show help
mysql> show databases;    // or simply   $ mysql -e “show databases” -u -p   //"'" could be any query
mysql> use database_name;
mysql> show tables;
mysql> desc table_name  // show details of table_name

Selects
mysql> SELECT distinct * FROM [table name] WHERE name != "Bob" AND familyName LIKE "BOB%" AND phone_number = '3444444' order by phone_number DESC;
..... LIMIT 1,5; //just records 1 through 5

Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with a.  mysql> SELECT * FROM [table name] WHERE rec RLIKE "^a";


to have mysql data in a excel file you need to install MyODBC. Control Panel->Administrative Tools->Data Sources(ODBC)->Add->MySQL ODBC Driver
in Excel->Data->From Other Sources->From Microsoft Query (ODBC)

Spatial

start with an existing table called geometry, add a spatial column and index it:
ALTER TABLE geometry ADD coord POINT NOT NULL;
CREATE SPATIAL INDEX coord ON geometry (coord);

Insert some data; think in terms of POINT(X Y) or POINT(lat lon):
INSERT INTO geometry (coord) VALUES( GeomFromText( 'POINT(40 -100)' ));
INSERT INTO geometry (coord) VALUES( GeomFromText( 'POINT(1 1)' ));

Get those X,Y coordinates back from the table:
SELECT X(coord), Y(coord) FROM geometry

Get points within a bounding rectangle:
SELECT MBRContains(
    GeomFromText( 'POLYGON((0 0,0 3,3 3,3 0,0 0))' ),
    coord
)
FROM geometry












Subpages (1): Import .sql File
Comments