MySQL

MySQL CLASS

----------------------------------------------------------------------------------------

Day 1 (17hb. 10 2011)

1. Create database database_name;
2. Create table tables_name( NAME DATA-TYPE( 00) NOT NULL;
3. ALTER TABLES TABLESNAME ADD TABLESCONTENT DATATYPE(00) NOT NULL;
4. ALTER TABLES TABLESNAME CHANGE SALAHTABLESNAME TABLESCONTENT DATATYPE(00) NOT NULL;
5. publish ENUM('Y','N') not null default 'N' );
6. SHOW DATABASES;
7. USE DATABASENAME;
8. SHOW TABLES;
9. DESCRIBE TABLES;
10. DROP DATABASE database_name;

----------------------------------------------------------------------------------------

Day 2 (18hb. 10 2011)

1. Jawapan Untuk EIP case study
2. INSERT INTO tables_name VALUES('value');
3. INSERT INTO tables_name (field) VALUES('value');
4. INSERT INTO tables_name SET field ='value';
5. UPDATE students SET field ='value';
6. UPDATE students SET field ='value' WHERE field='value'; with condition
7. SELECT*FROM tables;
8. SELECT*FROM tables WHERE YEAR(dcb)<'1995';
9. SELECT*FROM field FROM tables WHERE YEAR(dcb)<'1995';
10. SELECT user,password, host FROM user;
11. DELETE FROM tables WHERE YEAR(field)<'CONDITION';
12. TRUNCATE tables;
13. START TRANSACTION ----> ROLLBACK OR COMMIT
14. START TRANSACTION ----> SAVEPOINT NAME

...................................................................................

Day 3 (19hb. 10 2011)

1. Stored Precedure;
2. delimiter { (can be other symbols)
   CREATE PROCEDURE procedure_name(OUT table_field INT)
   BEGIN

   SELECT COUNT(*) INTO
   table_field FROM tables;

END{

delimiter ;

CALL procedure_name(@VALUE);

SELECT @VALUE;

delimiter { (example stored procedure without tables)

CREATE PROCEDURE gabung(IN data1 varchar(10), IN data2 varchar(10), OUT result varchar(30))

BEGIN

SELECT CONCAT_WS("-",data1,data2) into result;

END{

delimiter ;

CALL gabung("kenapa","lembab", @test);

SELECT @test;

CREATE FUNCTION hello(str char(20))

RETURNS char(50) DETERMINISTIC (if function return a same value(the process can be faster))

RETURN CONCAT('hello,',str);

SELECT hello('universe');

SHOW FUNCTION STATUS; (show active function on database))

delimiter { (after)

CREATE TRIGGER name_trigger AFTER INSERT ON tables1 FOR EACH ROW

begin

INSERT INTO tables2 SET field(tables2)=new.field(tables1), field="unknows"; optional condition

end{

delimiter ;

DROP TRIGGER trigger_name;

SHOW TRIGGER STATUS; (show active TIGGER on database))

VIEWS>>> rujuk pada day3(views).txt

MYSQLDUMP -u root -proot eip > backup_eip.sql; backup data n schema

MYSQLDUMP -u root -proot --no -create -info eip > data_eip.sql;

MYSQLDUMP -u root -proot --no -data eip > schema_eip.sql;

MYSQLDUMP -u root -proot eip < backup_eip.sql; recovery data n schema

...................................................................................

Day 4 (20hb. 10 2011)

1. SELECT user,password, host FROM user;
2. CREATE USER 'azlirul'@'localhost' IDENTIFIED BY 'BC022001'; lLocalhost--> identify user from where(it can be [ip address,domain or machine name]
3. DROP USER 'azlirul'@'google.com';
4. RENAME USER 'azlirul'@'google.com' to 'azlirul'@'10.10.10.2';
5. SET PASSWORD FOR 'azlirul'@'10.10.10.2' = password('azlirul');
6. SHOW GRANTS FOR 'azlirul'@'10.10.10.2';
7. GRANT SELECT,INSERT,DELETE ON eip.* TO 'azlirul'@'10.10.10.2';
8. GRANT CREATE,SELECT,INSERT,DELETE,UPDATE ON eip.* TO 'azlirul'@'10.10.10.2' WITH GRANT OPTION;
9. REVOKE ALL PRIVILEGES, GRANT OPTION ON mydb.* 'azlirul'@'10.10.10.2';
10. revoke all privileges on *.* from 'account1'@'localhost';

...................................................................................

Day 5 (21hb. 10 2011)

1. RUJUK BUKU NOTA ANDA (2 last topic)

RESET PASSWORD

• Start ---> control panel ---> Administrative Tools ---> service stopmysql
• create txt file
• type this text

UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;

• save as mysql-init.txt on c:\
• type this on command prompt (windows+R --->cmd)

C:\> C:\mysql\bin\mysqld-nt --init-file=C:\\mysql-init.txt

" -nt " not for windows 7 , so just throw away -nt code

• log in mysql -u root -p[newpassword]
• PERHATIAN !!!!!: after that delete file mysql-init.txt on c:\

------------------------------------------------------------------------------------------------

LOG CODE (on my.ini)

[mysqld]

# The TCP/IP Port the MySQL Server will listen on
port=3306
# Update 18 OCT 2011
# Contains all SQL statements activities
# Useful to identify SQL statement errors
general_log=1
general_log_file="c:/mysql.log"

-------------------------------------------------------------------------------------------------------------------------

RUJUKAN WEB

-------------------------------------------------------------------------------------------------------------------------

• http://www.mysql.com/
• http://www.mysql.com/downloads/mysql/
• http://dev.mysql.com/doc/refman/4.1/en/resetting-permissions.html
• http://search.oracle.com/search/search?q=how+to+reset+password&group=MySQL&x=0&y=0
• http://ss64.com/sql/grant_user.html
• http://msdn.microsoft.com/en-us/library/ms178569.aspx
• http://www.java2s.com/Tutorial/MySQL/0540__Privilege/0120__REVOKE.htm
• http://en.wikipedia.org/wiki/Kernel_(computing) <---- about kernel
• http://sqlzoo.net/hack/
• http://www.windowsecurity.com/articles/Hacking_an_SQL_Server.html

--------------------------------------------------------------------------------------------------------------------------

• GeneticNEO rest Studio28
  Amirul Azlirul Azwan B. Abdul Aziz,
  SMK Raja Muda Musa,
  ICT selangor,
  Kementerian Pendidikan Selangor.