NOTA MYSQL 17HB

MYSQL DATABASE SERVER
=====================




Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>mysql -u root -p
Enter password: *********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Enter password: *********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> $ mysql -u root -p
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '$ mys
ql -u root -p' at line 1
mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)

mysql> use information_schema;
Database changed
mysql> use msql;
ERROR 1049 (42000): Unknown database 'msql'
mysql> use mysql;
Database changed

mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.00 sec)

mysql> select interval 1 day + '2009-12-31';
+-------------------------------+
| interval 1 day + '2009-12-31' |
+-------------------------------+
| 2010-01-01 |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select '2010-01-01'-interval 1 second;
+--------------------------------+
| '2010-01-01'-interval 1 second |
+--------------------------------+
| 2009-12-31 23:59:59 |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select date_add('2009-12-31 23:59:59', interval 1 second);
+----------------------------------------------------+
| date_add('2009-12-31 23:59:59', interval 1 second) |
+----------------------------------------------------+
| 2010-01-01 00:00:00 |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select date_sub('2010-01-01 00:00:00', interval '1 1:1:1' DAY_SECOND);
+----------------------------------------------------------------+
| date_sub('2010-01-01 00:00:00', interval '1 1:1:1' DAY_SECOND) |
+----------------------------------------------------------------+
| 2009-12-30 22:58:59 |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_SUB('2010-01-02', INTERVAL 31 DAY);
+-----------------------------------------+
| DATE_SUB('2010-01-02', INTERVAL 31 DAY) |
+-----------------------------------------+
| 2009-12-02 |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> select datediff(now(),'1967-07-12')/365 as 'year\'s old';
+------------+
| year's old |
+------------+
| 44.2959 |
+------------+
1 row in set (0.00 sec)

mysql> select ceil(datediff(now(),'1967-07-12')/365) as 'year\'s old';
+------------+
| year's old |
+------------+
| 45 |
+------------+
1 row in set (0.00 sec)

mysql> select floor(datediff(now(),'1967-07-12')/365) as 'year\'s old';
+------------+
| year's old |
+------------+
| 44 |
+------------+
1 row in set (0.00 sec)

mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2011-10-17 |
+------------+
1 row in set (0.00 sec)

mysql> select week(current_date()) as 'This is week';
+--------------+
| This is week |
+--------------+
| 42 |
+--------------+
1 row in set (0.00 sec)

mysql> select week(current_date());
+----------------------+
| week(current_date()) |
+----------------------+
| 42 |
+----------------------+
1 row in set (0.00 sec)

mysql> select concat_ws('-', 'Salam', 'Sejahtera');
+--------------------------------------+
| concat_ws('-', 'Salam', 'Sejahtera') |
+--------------------------------------+
| Salam-Sejahtera |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> select concat_ws(' ', 'Salam', 'sejahtera', ',', 'semua');
+----------------------------------------------------+
| concat_ws(' ', 'Salam', 'sejahtera', ',', 'semua') |
+----------------------------------------------------+
| Salam sejahtera , semua |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select concat_ws(' ', 'Salam', 'sejahtera,', 'semua');
+------------------------------------------------+
| concat_ws(' ', 'Salam', 'sejahtera,', 'semua') |
+------------------------------------------------+
| Salam sejahtera, semua |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> select concat_ws(' ', 'Salam', 'sejahtera,', 'semua');
+------------------------------------------------+
| concat_ws(' ', 'Salam', 'sejahtera,', 'semua') |
+------------------------------------------------+
| Salam sejahtera, semua |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql> select concat_ws(' ', 'Salam', 'sejahtera,', 'semua');
+------------------------------------------------+
| concat_ws(' ', 'Salam', 'sejahtera,', 'semua') |
+------------------------------------------------+
| Salam sejahtera, semua |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql> create database uniclub;
Query OK, 1 row affected (0.00 sec)

mysql> use uniclub;
Database changed
mysql> create table students(
-> student_id varchar(32) not null primary key,
-> matrix_no varchar(10) not null,
-> full_name varchar(15));
Query OK, 0 rows affected (0.06 sec)

mysql> create table clubs(
-> club_id varchar(32) not null primary key,
-> est_date date not null,
-> club_name varchar(50) not null,
-> supervisor_id varchar(32) not null);
Query OK, 0 rows affected (0.06 sec)

mysql> create table lectures (
-> lesturer_id varchar(32) not null primary key,
-> staff_no varchar(10) not null,
-> full_name varchar(100) not null);
Query OK, 0 rows affected (0.05 sec)

mysql> create table activities (
-> activity_id varchar(32) not null primary key,
-> start datetime,
-> end datetime,
-> venue varchar(100),
-> club_id varchar(32) not null);
Query OK, 0 rows affected (0.06 sec)

mysql> create table students_clubs (
-> student_id varchar(32) not null,
-> club_id varchar(32) not null);
Query OK, 0 rows affected (0.08 sec)

mysql> create table students_activities (
-> student_id varchar(32) not null,
-> club_id varchar(32) not null);
Query OK, 0 rows affected (0.08 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| uniclub |
+--------------------+
5 rows in set (0.00 sec)

mysql> use uniclub;
Database changed
mysql> show tables;
+---------------------+
| Tables_in_uniclub |
+---------------------+
| activities |
| clubs |
| lectures |
| students |
| students_activities |
| students_clubs |
+---------------------+
6 rows in set (0.00 sec)

mysql> describe students;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| student_id | varchar(32) | NO | PRI | NULL | |
| matrix_no | varchar(10) | NO | | NULL | |
| full_name | varchar(15) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table students add phone varchar(15) not null;
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> describe students;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| student_id | varchar(32) | NO | PRI | NULL | |
| matrix_no | varchar(10) | NO | | NULL | |
| full_name | varchar(15) | YES | | NULL | |
| phone | varchar(15) | NO | | NULL | |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Restore MySQL Database

Exercise Questions

Exercise Questions

Question 1:
$locations = array('Selangor' => array('PJ', 'Puchong', 'Ampang'), 'Penang' => array('George Town', 'Seberang Jaya'), 'Sarawak' => array('Kuching', 'Miri', 'Bintulu'));

1. Write a single line code to output Seberang Jaya
2. Write a single line code to find the total number of towns in Penang
3. Using loop statements, create and HTML output in the following format:
Locations in Selangor: PJ, Puchong, Ampang
Locations in Penang: George Town, Seberang Jaya
Locations in Sarawak: Kuching, Miri, Bintulu

4. Write a single line code to insert the location \"Prai\" to the end of the array \"Penang\".
Overwrite \"Puchong\" with \"Damansara\" in the array \"Selangor\"

Question 2: Loop
Using a loop statement, produce the following output:
3, 6, 9, 12, 15, 18, 21

Question 3: Arrays
Output the values in the following array in ascending order using your preferred sort algorithm (do not use the asort() PHP function):
array(10, 38, 3, 58, 1, 4, 0, 29, 48, 29, 22, 66, 23, 76);

Question 4: PHP Date/Time
Using mktime() in PHP, find out what day of the week is it on your 30th birthday

Question 5: Database
Using SQL, find out the date 60 days ago.

Question 6: Database
Study and analyse the following table structure:
employees(employee_id, full_name, dept_id, position, phone, fax)
departments(dept_id, dept_name, hod_manager)
attendance(attendance_id, employee_id, clock_in, clock_out)
payroll(payroll_id, employee_id, payroll_date, gross_salary, total_claims,
total_deductions, net_salary)

1. Create a database and its tables based on the above definition
2. Produce the SQL statement to retrieve all employees arriving to work after 9:00am in the last 25 days
3. Produce the SQL statement to retrieve all employees in descending order with gross salary below 5,000.00 and total claims above 1500.00 in the last 25 days
4. Produce the SQL statement to retrieve all employees' full name, position, and phone from IT department (dept_id as '1000') who leaves work after 9:00pm in the last 25 day
5. Produce the SQL statement to retrieve only 100 employees\nProduce the SQL statement to retrieve the top 10 highest earning employees


JAWAPAN ADA DI SINI.

MySQL EasyPHP Stopped

CARA PENYELESAIAN KLIK DI SINI

Install EasyPHP

1. Layari http://www.easyphp.org/download.php
2. Pilih version yang dikehendaki (EasyPHP 5.3.3.1) PHP 5.3.3 | Apache 2.2.17 | MySQL 5.1.52 | PhpMyAdmin 3.3.8
3. Klik EasyPHP 5.3.3.1, save dan install
4. Pastikan EasyPHP running. (Start - All Programs - EasyPHP 5.3.3.1 - kedua2 trafic light Apache & MySQL berwarna hijau)
   
5. Buat Konfigurasi - (right click - configuration - Apache) - find AddType application/x-httpd-php .phtml .pwml .php5 .php4 .php3 .php2 .php .inc
6. Right click - configuration - PHP (find error_reporting - error_reporting = E_ALL | E_STRICT tukar kepada error_reporting = E_ALL & ~E_NOTICE)
7. Scroll down dan pastikan display_errors = On, log_errors = On, register_globals = Off, file_uploads = On, upload_max_filesize = 2M <== boleh tukar nilai lebih besar
8. Buka EasyPHP folder C:\Program Files\EasyPHP-5.3.3.1\www (www utk save website) - setiap file buka guna crimson (JANGAN guna notepad).
   

Install Crimson Editor

Crimson Editor adalah editor kod sumber profesional untuk Windows.

1. Layari http://www.crimsoneditor.com
2. Klik Download
3. Pilih Crimson Editor 3.70 Release
   
4. Save dan Install

Download MySQL

1. http://www.mysql.com/downloads

2. Pilih MySQL Community Server dan platform yang sesuai dengan PC

3. Klik ke link download tanpa mendaftar

4. Pilih Mirror dan klik HTTP untuk download

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.