340 likes | 433 Views
Introduction to D atabase. Kanda Phawattanakul. Introduction. Programs we use variables to store information. But when the program terminates all the variables declared in it disappear . Why this happens????. Introduction.
E N D
Introduction toDatabase Kanda Phawattanakul
Introduction • Programs we use variables to store information. • But when the program terminates all the variables declared in it disappear. • Why this happens????
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……..
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 ?
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
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………….
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
DBMS • And they are called “Database Management System(DBMS)”. • Oracle • IBM DB2 • Microsoft SQL Server • Microsoft Access • PostgreSQL • MySQL • SQLite
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.
Organization of DB • Tables • Fields (Columns) • Has many types • Primary Key (Optional) • Records (Rows, Entries)
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).
users donations volunteers campaigns Organization of DB
CRUD • Create new tables and records • Retrieve records from tables • Update tables’ definition and record’s data • Delete existing tables and records
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;
Show Created Databases • SHOWDATABASES; • You will see something like this-
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;
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.
Create Table • CREATE TABLE [IF NOT EXISTS] table_name( column_list ) type=table_type Example: Suppose I want to create table named “User_Password”
Create Table Query will be: CREATE TABLE USER_PASSWORD ( username varchar(6), password varchar(6), );
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;
Insert data into table • INSERT INTO tbl_name () VALUES(); • Example: • INSERT INTO USER_PASSWORD VALUES(‘0705001’,’123456’); • INSERT INTO USER_PASSWORD VALUES(‘0705002’,’678901’);
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
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
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
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;
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’;
Primary key • Primary key is used to distinguish two rows in a table. • Now if I execute INSERT INTO USER_PASSWORD VALUES(‘0705001’,’hello’);
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.
Create Statement CREATE TABLE USER_PASSWORD( username varchar(6), password varchar(6), PRIMARY KEY (USERNAME) );
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’;
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’;
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.
THANK YOU Any question ?