MySQL Tutorial

1. By Mr. Jiaming Su MySQL Tutorial CS3754 Classnote #9

2. CS3754 Classnote #9 Install MySQL • Where to find the software • http://www.mysql.com/products/database/mysql/community_edition.html • Steps of the Installation

3. CS3754 Classnote #9 1th

4. CS3754 Classnote #9 2th

5. CS3754 Classnote #9 3th

6. CS3754 Classnote #9 4th

7. CS3754 Classnote #9 5th

8. CS3754 Classnote #9 6th

9. CS3754 Classnote #9 7th

10. CS3754 Classnote #9 8th

11. CS3754 Classnote #9 New program installed

12. CS3754 Classnote #9 Starting MySQL

13. CS3754 Classnote #9 Change Password • Type in the following commends: • delete from user where Host <> 'localhost' and User <> 'root'; • delete from db; • update user set Password=password('NewPd') where User='root'; • flush privileges; • exit

14. CS3754 Classnote #9 Log in

15. CS3754 Classnote #9 After you log in

16. CS3754 Classnote #9 Build a database • Create databases • create database University; • Use a database • use University; • Create tables • create table table_Name (field_1 integer, field_2 char(10), …); • List Tables • show tables;

17. CS3754 Classnote #9 Results

18. CS3754 Classnote #9 Results (Cont’1)

19. CS3754 Classnote #9 Results (Cont’2)

20. CS3754 Classnote #9 Results (Cont’3)

21. CS3754 Classnote #9 Results (Cont’4)

22. CS3754 Classnote #9 Put data into a database • Insert a record to a table • insert into table_1 (field_1, field_2) values (1, 'first');

23. CS3754 Classnote #9 Show records

24. CS3754 Classnote #9 Modify Database • Modify one field of records • update table_1 set field_1=5 where field_1=1; e.g., update employee set sex = ‘F’ where ssn=‘665170901’; • Update multiple records in one stroke • update table_1 set field_5=12 where field_1>11; • Deleting Records • delete from table_1 where field_1=3;

25. CS3754 Classnote #9 Alter Database • Adding a field of a table • alter table table_1 add column field_5 char(20); • Delete a database, table, … • drop database_name; • drop Table_name; • Add or delete a index • add index ind_name (column_name, …); • drop index ind_name;

26. CS3754 Classnote #9 Add foreign Keys

27. CS3754 Classnote #9 After adding foreign keys

28. CS3754 Classnote #9 Query database • select column_name, … from table_name, … where column=condition, … ; • Note: any join query need follow relationships between tables.

29. CS3754 Classnote #9 An Join Query

30. CS3754 Classnote #9 Quit Database • Quit • bye