Mysql Commands

GRANTING PERMISSION TO IP

GRANT ALL ON alert_session.* TO root@'1.22.173.147' IDENTIFIED BY 'mysql';
GRANT ALL ON iespl_vts_beta.* TO bailoo@'localhost'

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'root'@'1.222.173.147';

SHOW GRANTS FOR 'root'@'localhost';

INDEXING IN MYSQL

create INDEX last_processed_time_key on last_processed_time(imei,account_id,type,routetype);

#### GET QUERY RESULT INTO FILE - (EX:VEHICLE REGISTRATION DATE)

SELECT vehicle.vehicle_name,vehicle_assignment.create_date, vehicle_assignment.edit_date from vehicle,vehicle_assignment,vehicle_grouping,account where vehicle.vehicle_id=vehicle_assignment.vehicle_id AND vehicle.status=1 and vehicle_assignment.status=1 AND vehicle_grouping.vehicle_id=vehicle.vehicle_id and vehicle_grouping.account_id=account.account_id AND account.account_id=1547 INTO OUTFILE 'D:\itc_vehicles.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

CASSANDRA PREPARED STATEMENT

https://datastax.github.io/python-driver/api/cassandra/cluster.html

JOIN AND GROUP BY QUERIES

SELECT * FROM my_transporters
LEFT JOIN transporter_vehicles ON

my_transporters.account_id=transporter_vehicles.account_id AND transporter_vehicles.capacity > 40 AND
transporter_vehicles.regrigerated_nonregrigerated='0' AND transporter_vehicles.close_open ='1'

INNER JOIN transporter_vehicle_route_assignment ON
transporter_vehicle_route_assignment.vehicle_id=transporter_vehicles.vehicle_id AND transporter_vehicle_route_assignment.material_type_id = '8' AND
transporter_vehicle_route_assignment.from_city_id='2664' AND transporter_vehicle_route_assignment.to_city_id='715'

INNER JOIN vehicle_availabilities ON
vehicle_availabilities.vehicle_id = transporter_vehicle_route_assignment.vehicle_id AND vehicle_availabilities.status=1

INNER JOIN material_type ON
material_type.material_type_id = transporter_vehicle_route_assignment.material_type_id

INNER JOIN city ON
city.city_id = transporter_vehicle_route_assignment.from_city_id OR
city.city_id = transporter_vehicle_route_assignment.to_city_id

INNER JOIN state ON
state.state_id = city.state_id

INNER JOIN country ON
country.country_id = state.country_id

GROUP BY (my_transporters.account_id);

Reset password

SET PASSWORD FOR root@localhost=PASSWORD('newpass');
FLUSH PRIVILEGES;

Create new user

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

select vehicle.vehicle_name, vehicle_assignment.device_imei_no, vehicle_assignment.create_date, vehicle_assignment.create_id, vehicle_assignment.edit_id, vehicle_assignment.status From vehicle,vehicle_assignment,vehicle_grouping,account where vehicle_assignment.vehicle_id = vehicle_grouping.vehicle_id AND vehicle_grouping.account_id = account.account_id AND vehicle_grouping.vehicle_id = vehicle.vehicle_id AND vehicle_assignment.vehicle_id = vehicle.vehicle_id AND vehicle_grouping.account_id=4 ORDER BY vehicle_assignment.create_date DESC;