#spatial 

create TABLE geometry (
	coord POINT; 
);


ALTER TABLE geometry ADD coord POINT NOT NULL; 
CREATE SPATIAL INDEX coord ON geometry (coord);

point(lat lon)
INSERT INTO geometry (coord) VALUES( GeomFromText( 'POINT(40 -100)' )); 
INSERT INTO geometry (coord) VALUES( GeomFromText( 'POINT(1 1)' )); 

SELECT X(coord) lat , Y(coord) lon 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

update activity 
set pPos = GeomFromText( 'POINT(-6.176392 106.828153)' ) 
where sdo = 'jalan' ; 


#waktu
date_format(dt, '%Y-%m-%d %H:%i:%s') tglwkt

%a	Short weekday name in current locale (Variable lc_time_names).
%b	Short form month name in current locale. For locale en_US this is one of: Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov or Dec.
%c	Month with 1 or 2 digits.
%D	Day with English suffix 'th', 'nd', 'st' or 'rd''. (1st, 2nd, 3rd...).
%d	Day with 2 digits.
%e	Day with 1 or 2 digits.
%f	Sub seconds 6 digits.
%H	Hour with 2 digits between 00-23.
%h	Hour with 2 digits between 01-12.
%I	Hour with 2 digits between 01-12.
%i	Minute with 2 digits.
%j	Day of the year (001-366)
%k	Hour with 1 digits between 0-23.
%l	Hour with 1 digits between 1-12.
%M	Full month name in current locale (Variable lc_time_names).
%m	Month with 2 digits.
%p	AM/PM according to current locale (Variable lc_time_names).
%r	Time in 12 hour format, followed by AM/PM. Short for '%I:%i:%S %p'.
%S	Seconds with 2 digits.
%s	Seconds with 2 digits.
%T	Time in 24 hour format. Short for '%H:%i:%S'.
%U	Week number (00-53), when first day of the week is Sunday.
%u	Week number (00-53), when first day of the week is Monday.
%V	Week number (01-53), when first day of the week is Sunday. Used with %X.
%v	Week number (01-53), when first day of the week is Monday. Used with %x.
%W	Full weekday name in current locale (Variable lc_time_names).
%w	Day of the week. 0 = Sunday, 6 = Saturday.
%X	Year with 4 digits when first day of the week is Sunday. Used with %V.
%x	Year with 4 digits when first day of the week is Monday. Used with %v.
%Y	Year with 4 digits.
%y	Year with 2 digits.
%#	For str_to_date(), skip all numbers.
%.	For str_to_date(), skip all punctation characters.
%@	For str_to_date(), skip all alpha characters.
%%	A literal % character.

mysql -ulabs8687_user -p18mgNikotin mobilbekas < /home/labs8687/labs8687_mobilbekas.sql
ALTER TABLE tBuyItem AUTO_INCREMENT = 1;

update member m set m.sPassword=SHA2('asbak2210',256)  where m.sUserID='ganteng';
insert into member values (1665065286,'test',SHA2('test',256));

#delete semua datanya
TRUNCATE TABLE table1;
#IF
IF(condition, value_if_true, value_if_false) 

#dr posisi 1 ambil 10 char
substr(lContent,1,10)

#add column
ALTER TABLE fromwhere ADD COLUMN sTime varchar(8) null default null;

ALTER TABLE visitor ADD COLUMN sClickWhat varchar(50) null default null;
ALTER TABLE visitor ADD COLUMN sClickDetail varchar(50) null default null;

#rename table
rename table oldname to newname;