# MySQL ## Some common MySQL commands ``` Joining 2 tables on some columns mysql> select T1.training_data_set_id, T1.food_aws_url from \ _training_data_set as T1, _observation_data as T2 \ where T2.source like '%' and T2.training_data_set_id=T1.training_data_set_id; mysql> SELECT T1.inserted_date, T1.training_data_set_id, T1.food_aws_url, T1.annotate_file_url, \ T3.commodity FROM _training_data_set as T1, _observation_data as \ T2, _training_commodity_variety as T3 where T2.source='' \ and T2.training_data_set_id=T1.training_data_set_id and T1.training_data_set_id=T3.training_data_set_id \ and T3.commodity='blueberry'; mysql> SELECT T1.inserted_date, T1.training_data_set_id, T1.food_aws_url, T1.annotate_file_url, \ T3.commodity FROM _training_data_set as T1, _observation_data as T2, \ _training_commodity_variety as T3 where T2.source=''; # to connect multiple tables and also use pattern search using 'like' mysql> select tds.training_data_set_id from _training_commodity_variety as tcv, \ _training_data_set as tds where tds.training_data_set_id=tcv.training_data_set_id and \ tcv.commodity="strawberry-IU" and tcv.variety="green" and food_aws_url like '%_top_0%'; ``` ## To see the structure of the table ``` mysql> show create table _coordinate_data; mysql> describe _coordinate_data; ``` ## To find all the tables that have the 'delete cascade on' set on the FK Ref: ``` mysql> use information_schema; No connection. Trying to reconnect... Connection id: 76494 Current database: *** NONE *** Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> mysql> select table_name from referential_constraints \ where constraint_schema='' \ and referenced_table_name='_training_data_set' \ and delete_rule='cascade'; +------------------------------------------+ | table_name | +------------------------------------------+ | _camera_data | | _config_data | | _coordinate_data | | _driscoll_produce_parameter | | _driscoll_produce_parameter_count | | _generic_produce_parameter | | _observation_data | | _pixel_data | | _usda_produce_count | | _usda_produce_parameter | +------------------------------------------+ 10 rows in set (0.29 sec) ``` ## To get to the EC2 MySQL from Mac terminal as root ``` ------------------------------ For the RDS first instance ------------------------------ mysql -h .us-west-2.rds.amazonaws.com -P 3306 -u root -p for password enter: regular user password: ------------------------------ For the RDS second instance ------------------------------ root user: mysql -h .us-west-2.rds.amazonaws.com -P 3306 -u --password="" regular user: mysql -h .us-west-2.rds.amazonaws.com -P 3306 -u --password="" ``` ## To copy database ``` # From Mac or Ubuntu machine, first create an empty destination database: - did it as root ) > mysqladmin -h .us-west-2.rds.amazonaws.com -P 3306 -u root -p create # Next dump the source database locally on mac disk - did it as a regular user > mysqldump -h .us-west-2.rds.amazonaws.com -P 3306 -u -p > .sql # Finally, copy the dumped *.sql file on the empty destination database - did it as root > mysql -h .us-west-2.rds.amazonaws.com -P 3306 -u root -p < .sql # Another example - From Mac mysqladmin -h .us-west-2.rds.amazonaws.com -P 3306 -u -p create mysqldump -h .us-west-2.rds.amazonaws.com -P 3306 -u -p > /Users/folder1/folder2/.sql # copy the dumped sql file as root mysql -h .us-west-2.rds.amazonaws.com -P 3306 -u root -p < /Users/folder1/folder2/.sql ``` ## To get csv file from command line ``` mysql -h .us-west-2.rds.amazonaws.com -P 3306 -u --password="" -e "select inserted_date, training_data_set_id, license_key, input_data_id from _training_data_set where food_aws_url like '%almond_DC%' ;" > /tmp/almond_DC_full.txt # Then open the file and replace tabs with comma using :%s/\t/,/g ``` ## To drop database ``` mysql> drop database ; mysql> drop database ``; # use backtick to escape characters like '-' to do the same using mysqladmin from Mac terminal: mysqladmin -h .us-west-2.rds.amazonaws.com -P 3306 -u root -p drop then enter root password ``` ## To DROP table ``` mysql> DROP TABLE IF EXISTS `_training_commodity_variety`; # To drop multiple tables at once: DROP TABLE IF EXISTS auth__access_tokens, auth__clients; ``` ## To create table ``` mysql> CREATE TABLE `_training_commodity_variety` ( `id` int(11) NOT NULL AUTO_INCREMENT, `training_data_set_id` int(11) NOT NULL, `commodity` varchar(45) DEFAULT NULL, `variety` varchar(45) DEFAULT NULL, PRIMARY KEY (`id`), KEY `commodity_variety_data_id_idx` (`training_data_set_id`), CONSTRAINT `commodity_variety_data_id` FOREIGN KEY (`training_data_set_id`) REFERENCES `_training_data_set` (`training_data_set_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB; mysql> CREATE TABLE `_cgroup` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> `training_data_set_id` int(11) NOT NULL, -> `cgroup` varchar(45) DEFAULT NULL, -> `pair_shot` boolean DEFAULT 0, -> PRIMARY KEY (`id`), -> KEY `cgroup_id_idx` (`training_data_set_id`), -> CONSTRAINT `cgroup_id` FOREIGN KEY (`training_data_set_id`) REFERENCES `_training_data_set` (`training_data_set_id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB; ``` ## To update table ``` # to deactivate a user from using the annotation platform update _user_details SET status='0' where user_id=9; # to update multiple records (deactivate multiple users) update _user_details SET status='0' where user_id in (13, 18, 19, 20, 21, 22, 23, 24, 25, 26); # To update table, after selecting id's UPDATE _training_data_set as t, ( SELECT training_data_set_id, annotated_food_aws_url FROM _training_data_set WHERE annotated_food_aws_url like '%INPUTEDITEDImages%' ) as s SET t.annotated_input_food_aws_url = s.annotated_food_aws_url, t.annotated_food_aws_url = NULL WHERE t.training_data_set_id=s.training_data_set_id; ``` ## Other commands on table ``` # to know the directory where the DB is created mysql> select @@datadir; # insert insert into _organization (org_name, org_short_name) values ("com.jiddu.agskore-agshift", "agshift"); insert into _training_user_register (email, first_name, last_name, company_name, status, mask) values ("alexa@agshift.com", "Alexandra", "Perez", "agshift", "Active", 0); # delete delete from _training_user_register where id=33; # one complicated query: mysql> SELECT ts.training_data_set_id,ts.user_id,ts.food_id,ts.food_aws_url,ts.file_name,ts.file_size,ts.annotate_status,annotate_file_url,UNIX_TIMESTAMP(ts.inserted_date) as inserted_date,ps.depth,ps.pixel_height,ps.pixel_width, T3.commodity FROM agshift_training_data_set ts,_observation_data os, _pixel_data ps, _training_commodity_variety as T3 where os.training_data_set_id=ts.training_data_set_id and os.source='' and ps.training_data_set_id = ts.training_data_set_id and ts.training_data_set_id=T3.training_data_set_id and T3.commodity='cashews' AND T3.variety='gc' and ts.training_data_set_id IS NOT NULL order by ts.inserted_date DESC; ``` ## To CREATE USER and grant privileges ``` # first login as root user mysql -h .us-west-2.rds.amazonaws.com -P 3306 -u --password="" # Next create a user with a password CREATE USER ''@'%' IDENTIFIED BY '' # Grant all privileges to the DB .* to this user GRANT ALL PRIVILEGES ON .* TO ''@'%' WITH GRANT OPTION; # See the privilege SHOW GRANTS FOR ''@'%'; +-------------------------------------------------------------------------------+ | Grants for @% | +-------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO ''@'%' IDENTIFIED BY PASSWORD | | GRANT ALL PRIVILEGES ON ``.* TO ''@'%' WITH GRANT OPTION | +-------------------------------------------------------------------------------+ 2 rows in set (0.04 sec) ``` ## To change GRANT Privileges ``` mysql> SHOW GRANTS FOR ; mysql> SHOW GRANTS FOR root; mysql> GRANT DELETE, DROP on .* TO ''@'%'; # for database with dashes in the name mysql> GRANT DELETE, DROP on ``.* TO ''@'%'; mysql> GRANT SELECT, INSERT, UPDATE, DELETE, DROP ON ``.* TO ''@'%'; ``` ## To DELETE data from table ``` mysql> delete from _training_data_set where food_aws_url like '%strawberry-IR%'; Query OK, 1128 rows affected (0.20 sec) ## Check the dependent table that the deletion actually worked mysql> select * from _training_data_set where food_aws_url like '%strawberry-IR%'; Empty set (0.05 sec) mysql> select training_data_set_id from _training_commodity_variety where commodity="strawberry-IR"; Empty set (0.05 sec) mysql> delete from _training_data_set where food_aws_url like '%strawberry-IU%'; Query OK, 748 rows affected (0.15 sec) ## Check the dependent table that the deletion actually worked mysql> select training_data_set_id from _training_commodity_variety where commodity="strawberry-IU"; Empty set (0.24 sec) ``` --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://bhabs.gitbook.io/allsetup/mysql.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.