Introduction to D atabase

1. Introduction toDatabase Kanda Phawattanakul

2. Introduction • Programs we use variables to store information. • But when the program terminates all the variables declared in it disappear. • Why this happens????

3. Introduction • What if we need some information to be stored even after the termination of program. (We are here in this session for this need). • How can we fulfill this need? • Any suggestion……..

4. Introduction • The most simple solution is store the information in the disk. • Now the question is how we can store in disk. • Create a file and store there.(simple enough) • Is it a good solution or the best solution ?

5. Introduction • Suppose we want to implement the log in section.(feature 1) • We use a file and store the user_id and password in the following manner

6. Problems • When 1105120 wants to log in we have to check all the previous record. • Also we have to check whether multiple users are accessing the file. • Any more problem………….

7. Problems • So we need to care about a lot of facts beside storing, updating the file. • But we are lazy people. We always want to minimize our task. • Thank GOD there are some applications or Systems which handle the facts about which we were worried 

8. DBMS • And they are called “Database Management System(DBMS)”. • Oracle • IBM DB2 • Microsoft SQL Server • Microsoft Access • PostgreSQL • MySQL • SQLite

9. DBMS • But not all our problems have been solved.  • Why??? • Because now we have to learn how to maintain Database using DBMS . • Here we will see how to manage database using MySQL.

10. Organization of DB • Tables • Fields (Columns) • Has many types • Primary Key (Optional) • Records (Rows, Entries)

11. Organization of DB Database In practice, a set of tables. Relational databases (like the ones that underlie websites) have tables that are related to each other by common fields. Table A table is a collection of data organized into columns (fields) and rows (records) and related to other tables by common fields. Field A column. A field contains all the same type of data; each cell refers to a different unit. Record A row. A record contains all the different types of data about a unit of analysis (e.g. user, order, letter).

12. users donations volunteers campaigns Organization of DB

13. CRUD • Create new tables and records • Retrieve records from tables • Update tables’ definition and record’s data • Delete existing tables and records

14. Create a Database • CREATE DATABASE [IF NOT EXISTS] database_name; • Example: Suppose I want to create a database named “Quiz”. • So the query will be CREATE DATABASE IF NOT EXISTS QUIZ;

15. Show Created Databases • SHOWDATABASES; • You will see something like this-

16. Delete DataBase • DROP DATABASE [IF EXISTS] database_name; • Example : Suppose I want to delete the quiz database : • So the query will be DROP DATABASE IF EXISTS Quiz;

17. We have created Database in MySQL. • Now we will store data in Database. • We store data in Tabular format in MySQL. • That means we have to create tables to store data.

18. Create Table • CREATE TABLE [IF NOT EXISTS] table_name( column_list ) type=table_type Example: Suppose I want to create table named “User_Password”

19. Create Table Query will be: CREATE TABLE USER_PASSWORD ( username varchar(6), password varchar(6), );

20. Show Table • To see the created table in the database use querySHOW TABLES; • To see the description of a particular table use queryDESCRIBE table_name;

21. Insert data into table • INSERT INTO tbl_name () VALUES(); • Example: • INSERT INTO USER_PASSWORD VALUES(‘0705001’,’123456’); • INSERT INTO USER_PASSWORD VALUES(‘0705002’,’678901’);

22. Alter Table • Alter table is used to change the structure of the table • Suppose we forgot to add a column in the USER_PASSWORD table. • Now we want to add a column name ‘type’ in that table

23. Alter Table • ALTER TABLE USER_PASSWORDADD COLUMN TYPE VARCHAR(5); Using ALTER TABLE query: • We can also drop column • Rename the table • Change the properties of a column

24. Select Statement • SELECT column_name1,column_name2... FROM tables [WHERE conditions] [GROUP BY group [HAVING group_conditions]] [ORDER BY sort_columns] [LIMIT limits]; • Looking very complex 

25. Most simple select statement • SELECT * FROM table_name; • It selects the whole table; • SELECT COLUMN_NAME FROM TABLE • Example: SELECT USERNAME,TYPE FROM USER_PASSWORD;

26. Where clause • We do not always want to select the whole table .Most of the time part of a table. • Example: • SELECT PASSWORD,TYPE FROM USER_PASSWORD WHERE USERNAME = ‘0705001’;

27. Primary key • Primary key is used to distinguish two rows in a table. • Now if I execute INSERT INTO USER_PASSWORD VALUES(‘0705001’,’hello’);

28. Problems • Big problem. • One user two passwords. :O • We can avoid it by making USERNAME column as primary key. • By making USERNAME column a primary key we ensure that this column will have all distinct value.

29. Create Statement CREATE TABLE USER_PASSWORD( username varchar(6), password varchar(6), PRIMARY KEY  (USERNAME) );

30. Update Statement • Used to change data in the table. • UPDATE table_name SET column_name = value WHERE (Some condition is true); Suppose 0705001 wants to change his/her password. The query will be : UPDATE USER_PASSWORD SET PASSWORD = ‘1234’ WHERE USERNAME = ‘0705001’;

31. Delete Statement • Used to delete data in the table. • DELETE FROM table_name WHERE (Some condition is true); Suppose we want to throw out an user(0*0*200) from our Database !!! DELTE FROM USER_PASSWORD WHERE USERNAME = ‘0*0*200’;

32. Conclusion • These were very basic database queries. • Don’t be worried if you did not understand all the topics. • You will learn all this and many other features.

33. THANK YOU Any question ?