際際滷

際際滷Share a Scribd company logo

MySQL Presentation

Download as PPT, PDF
Manish Bothra
Manish Bothra

The document provides instructions on installing MySQL on Windows, including downloading the installer, selecting a setup type, and starting MySQL for the first time. It also demonstrates how to create and populate tables, insert and retrieve data, and test MySQL functionality using PHP. Basic SQL commands for creating, modifying, and querying databases and tables are also explained.

1 of 32
Downloaded 96 times
A PRESENTATION ON MYSQL - BY MANISH BOTHRA
MYSQL INSTALLATION COMMANDS
Installing MySQL on Windows Introduction Introduction Installing MySQL Running MySQL Testing with PHP Presented by Mike Barras, ITS Available online at
Installing MySQL on Windows Introduction Supported 32-bit Windows Platforms (Win32) Windows 9x (95/98/ME) Windows NT (NT/2000/XP) Workstation/Home/Professional Server Editions
Installing MySQL on Windows Installing MySQL Example environment Windows 2000 Server (SP 3) IIS 5.0 PHP 4.3.1 MySQL 4.0.12 Note: IIS (Web Server) and PHP (Script engine) are NOT required to install or run MySQL.
Installing MySQL on Windows Installing MySQL Step 1: Download the most recent version of the MySQL for Windows installer from http://www.mysql.org/ , and run it. Select the directory to which you would like to install MySQL (c:ysql).
Installing MySQL on Windows Installing MySQL Step 2: Select the setup type that you would like to use. The typical should be sufficient for just about everybody. It installs all items, including: The Mysql Servers Clients and maint. tools Documentation Examples and Libraries Grants tables and core files
Installing MySQL on Windows Installing MySQL Step 3: Thats it! Click finish and youre done installing (it really is just that simple)
Installing MySQL on Windows Running MySQL You must manually start MySQL the first time. c:ysqlininmysqladmin.exe You will be prompted to create an admin username and Password. This is the login information for the admin tool, not any specific database or table. Once the admin account is created, the server will be running (either as a program in Win 9x or as a service in NT) and will run each time you start Windows. The traffic light system tray icon shows you its working.
Installing MySQL on Windows Running MySQL Run the MySQL command interface by executing c:ysqlinysql.exe Type show databases; to See the current databases Configured on the server. By default, mysql and test should be there. Type use test; to specify that database.
Installing MySQL on Windows Running MySQL Lets create a table. Type show tables; to see Currently defined tables in test. Issue create table command to create a table. Now run show tables; again to verify what youve done. create table tablename ( column datatype );
Installing MySQL on Windows Running MySQL Insert some data into the table youve just created using the insert into SQL command. Verify the insert by selecting the information back out. insert into tablename ( field1, field2, ) values ( value1, value2, ); select [list of fieldnames or *] from tablename;
Installing MySQL on Windows Testing with PHP The true measure of success (requires PHP and web server) Put it all together. PHP Functions: mysql_connect( host[,user,pass] ) mysql_select_db( database ) mysql_query( SQL stmt ); mysql_close( database handle );
Installing MySQL on Windows Testing with PHP
Installing PHP on Windows Useful Links This Presentation http:// uts.cc.utexas.edu/~mpbarras/php / Download MySQL http://www.mysql.com/downloads/mysql-4.0.html Installation Documentation http://www.mysql.com/documentation/index.html PHP reference for MySQL functions http://www.php.net/manual/en/ref.mysql.php
BASIC SQL COMMANDS SQL STATEMENTS: T here are a number of SQL statements, few of which are explained below: Data Retrieval Statement: SELECT is the data extracting statement which retrieves the data from the database. Data Manipulation Language (DML): This language constitutes the statements that are used to manipulate with the data. It has three commands, which are INSERT, UPDATE and DELETE. Data Definition Language (DDL): This is the language used to define the tables. It sets up, changes, and removes data structures from the tables. It uses 5 commands, which are CREATE, ALTER, DROP, RENAME and TRUNCATE.
CREATING A TABLE Syntax: Create table <Table Name> ( <Field1> <Type> <(width)> [Not Null/Null], <Field2> <Type> <(width)> [Not Null/Null], ..................................) : Example: Create table student_table (Reg_No Text (6) Name Text (25), Class Text (5));
CREATING A TABLE WITH PRIMARY KEY Syntax: Create table <Table Name> ( <Field1> <Type> <(width)> Constraint <constraint name> Primary Key , <Field2> <Type> <(width)>, ..................................) : Example: a) Create table student_table2 (Reg_no Text (4) Constraint student_Reg_pk primary key, Name Text (25), Class Text (5));
CREATING A TABLE WITH COMPOSITE PK Syntax: Create table <Table Name> ( <Field1> <Type> <(width)> , <Field2> <Type> <(width)>, ..................................<field-n> <type-n> <width-n>, Constraint <constraint-name> Primary Key (Field1, Field2)) : Example: Create table student_table3 (Reg_no Text (4), Name Text (25), Class Text (5), Constraint student_Reg_Class_pk primary key (Reg_no, Class)); 油
CREATING A TABLE WITH FOREIGN KEY Create table student_table2 (Reg_no Text (4), Name Text (25), Class Text (5) Proj_No Text (4) Constraint student_Reg_pk primary key (Reg_no) Constraint project_PNo_fk Foreign Key (Proj_No) References Project (Proj_No));
To Delete a Table along with all contents Syntax: Drop Table <Table-Name>; Example: Drop Table student_table2;
To Add a field to the table (structure) Syntax: Alter Table <Table-Name> Add <Field Name> <Type> (width); Example: Alter Table Student_Table Add Roll_no Text (4);
To Insert Data Into a Table a) Syntax: Insert Into <Table-Name> (Fieldname1, Fieldname2, Fieldname3,..) Values (value1, value2, value3,..) Example: Insert Into Student_Table (Reg_no, Name, class) Values (1211, &quot;Umar&quot;, &quot;MCS&quot;); ;
Example: b-1) Insert Into Student_Table Values (1123, &quot;Babar&quot;, null, null); b-2) Insert Into Student_Table (Reg_no, Name, class, Roll_no) Values (1124, &quot;Babar&quot;, null, null); b-3) Insert Into Student_Table (Reg_no, Name) Values (1124, &quot;Babar&quot;); b) Syntax: Insert Into <Table-Name> Values (value1, value2, value3,.., value-n);
To Delete a Row or Rows (Records) Syntax: Delete from <table name> [where <condition>]; Example: a) Delete from student_table; (it will delete al the records from student table). 油 b) Delete from student_table (conditional deletion) Where class=&quot;MCS&quot;;
Modifying (Updating) Records: Syntax: UPDATE <table name> Set <Field Name> = <Value> Where <Condition>; Example: a) UPDATE Student Set Semester = 5 where Semester = 4 or Class = MCS; b) UPDATE Student Set Semester = 5, Class = MS where Semester = 4 or Class = MCS;
Creating a View: Syntax: CREATE VIEW <View-Name> AS Select Field1, Field2, Field3 From <Table_Name> Example: CREATE VIEW Student_BCS as Select Reg_No, Name, Class From Student Where Class = BCS; 油
Querying a View: Syntax: Select <Field List> From <View Name> Where <condition>; Example: a) Select * From Student_BCS ; b) Select * From Student_BCS where City = Islamabad;
Retrieving (Displaying) Data: Syntax: Select <field1, field2, fieldn> from <table name> where <condition>; Example: a) SELECT * FROM Student_table; 油 b) SELECT Reg_no, Name FROM Student_table;
Retrieving (Displaying) Data depending on some condition: Syntax: Select <field1, field2, fieldn> from <table name> Where <Condition>; Example: a) Select * From Student_table Where class= &quot;BCS&quot;; 油 b) Select Name, Reg_no From Student_table Where class= &quot;BCS&quot;;
THANK YOU
油

More Related Content

MySQL Presentation

  • 1. A PRESENTATION ON MYSQL - BY MANISH BOTHRA
  • 3. Installing MySQL on Windows Introduction Introduction Installing MySQL Running MySQL Testing with PHP Presented by Mike Barras, ITS Available online at
  • 4. Installing MySQL on Windows Introduction Supported 32-bit Windows Platforms (Win32) Windows 9x (95/98/ME) Windows NT (NT/2000/XP) Workstation/Home/Professional Server Editions
  • 5. Installing MySQL on Windows Installing MySQL Example environment Windows 2000 Server (SP 3) IIS 5.0 PHP 4.3.1 MySQL 4.0.12 Note: IIS (Web Server) and PHP (Script engine) are NOT required to install or run MySQL.
  • 6. Installing MySQL on Windows Installing MySQL Step 1: Download the most recent version of the MySQL for Windows installer from http://www.mysql.org/ , and run it. Select the directory to which you would like to install MySQL (c:ysql).
  • 7. Installing MySQL on Windows Installing MySQL Step 2: Select the setup type that you would like to use. The typical should be sufficient for just about everybody. It installs all items, including: The Mysql Servers Clients and maint. tools Documentation Examples and Libraries Grants tables and core files
  • 8. Installing MySQL on Windows Installing MySQL Step 3: Thats it! Click finish and youre done installing (it really is just that simple)
  • 9. Installing MySQL on Windows Running MySQL You must manually start MySQL the first time. c:ysqlininmysqladmin.exe You will be prompted to create an admin username and Password. This is the login information for the admin tool, not any specific database or table. Once the admin account is created, the server will be running (either as a program in Win 9x or as a service in NT) and will run each time you start Windows. The traffic light system tray icon shows you its working.
  • 10. Installing MySQL on Windows Running MySQL Run the MySQL command interface by executing c:ysqlinysql.exe Type show databases; to See the current databases Configured on the server. By default, mysql and test should be there. Type use test; to specify that database.
  • 11. Installing MySQL on Windows Running MySQL Lets create a table. Type show tables; to see Currently defined tables in test. Issue create table command to create a table. Now run show tables; again to verify what youve done. create table tablename ( column datatype );
  • 12. Installing MySQL on Windows Running MySQL Insert some data into the table youve just created using the insert into SQL command. Verify the insert by selecting the information back out. insert into tablename ( field1, field2, ) values ( value1, value2, ); select [list of fieldnames or *] from tablename;
  • 13. Installing MySQL on Windows Testing with PHP The true measure of success (requires PHP and web server) Put it all together. PHP Functions: mysql_connect( host[,user,pass] ) mysql_select_db( database ) mysql_query( SQL stmt ); mysql_close( database handle );
  • 14. Installing MySQL on Windows Testing with PHP
  • 15. Installing PHP on Windows Useful Links This Presentation http:// uts.cc.utexas.edu/~mpbarras/php / Download MySQL http://www.mysql.com/downloads/mysql-4.0.html Installation Documentation http://www.mysql.com/documentation/index.html PHP reference for MySQL functions http://www.php.net/manual/en/ref.mysql.php
  • 16. BASIC SQL COMMANDS SQL STATEMENTS: T here are a number of SQL statements, few of which are explained below: Data Retrieval Statement: SELECT is the data extracting statement which retrieves the data from the database. Data Manipulation Language (DML): This language constitutes the statements that are used to manipulate with the data. It has three commands, which are INSERT, UPDATE and DELETE. Data Definition Language (DDL): This is the language used to define the tables. It sets up, changes, and removes data structures from the tables. It uses 5 commands, which are CREATE, ALTER, DROP, RENAME and TRUNCATE.
  • 17. CREATING A TABLE Syntax: Create table <Table Name> ( <Field1> <Type> <(width)> [Not Null/Null], <Field2> <Type> <(width)> [Not Null/Null], ..................................) : Example: Create table student_table (Reg_No Text (6) Name Text (25), Class Text (5));
  • 18. CREATING A TABLE WITH PRIMARY KEY Syntax: Create table <Table Name> ( <Field1> <Type> <(width)> Constraint <constraint name> Primary Key , <Field2> <Type> <(width)>, ..................................) : Example: a) Create table student_table2 (Reg_no Text (4) Constraint student_Reg_pk primary key, Name Text (25), Class Text (5));
  • 19. CREATING A TABLE WITH COMPOSITE PK Syntax: Create table <Table Name> ( <Field1> <Type> <(width)> , <Field2> <Type> <(width)>, ..................................<field-n> <type-n> <width-n>, Constraint <constraint-name> Primary Key (Field1, Field2)) : Example: Create table student_table3 (Reg_no Text (4), Name Text (25), Class Text (5), Constraint student_Reg_Class_pk primary key (Reg_no, Class)); 油
  • 20. CREATING A TABLE WITH FOREIGN KEY Create table student_table2 (Reg_no Text (4), Name Text (25), Class Text (5) Proj_No Text (4) Constraint student_Reg_pk primary key (Reg_no) Constraint project_PNo_fk Foreign Key (Proj_No) References Project (Proj_No));
  • 21. To Delete a Table along with all contents Syntax: Drop Table <Table-Name>; Example: Drop Table student_table2;
  • 22. To Add a field to the table (structure) Syntax: Alter Table <Table-Name> Add <Field Name> <Type> (width); Example: Alter Table Student_Table Add Roll_no Text (4);
  • 23. To Insert Data Into a Table a) Syntax: Insert Into <Table-Name> (Fieldname1, Fieldname2, Fieldname3,..) Values (value1, value2, value3,..) Example: Insert Into Student_Table (Reg_no, Name, class) Values (1211, &quot;Umar&quot;, &quot;MCS&quot;); ;
  • 24. Example: b-1) Insert Into Student_Table Values (1123, &quot;Babar&quot;, null, null); b-2) Insert Into Student_Table (Reg_no, Name, class, Roll_no) Values (1124, &quot;Babar&quot;, null, null); b-3) Insert Into Student_Table (Reg_no, Name) Values (1124, &quot;Babar&quot;); b) Syntax: Insert Into <Table-Name> Values (value1, value2, value3,.., value-n);
  • 25. To Delete a Row or Rows (Records) Syntax: Delete from <table name> [where <condition>]; Example: a) Delete from student_table; (it will delete al the records from student table). 油 b) Delete from student_table (conditional deletion) Where class=&quot;MCS&quot;;
  • 26. Modifying (Updating) Records: Syntax: UPDATE <table name> Set <Field Name> = <Value> Where <Condition>; Example: a) UPDATE Student Set Semester = 5 where Semester = 4 or Class = MCS; b) UPDATE Student Set Semester = 5, Class = MS where Semester = 4 or Class = MCS;
  • 27. Creating a View: Syntax: CREATE VIEW <View-Name> AS Select Field1, Field2, Field3 From <Table_Name> Example: CREATE VIEW Student_BCS as Select Reg_No, Name, Class From Student Where Class = BCS; 油
  • 28. Querying a View: Syntax: Select <Field List> From <View Name> Where <condition>; Example: a) Select * From Student_BCS ; b) Select * From Student_BCS where City = Islamabad;
  • 29. Retrieving (Displaying) Data: Syntax: Select <field1, field2, fieldn> from <table name> where <condition>; Example: a) SELECT * FROM Student_table; 油 b) SELECT Reg_no, Name FROM Student_table;
  • 30. Retrieving (Displaying) Data depending on some condition: Syntax: Select <field1, field2, fieldn> from <table name> Where <Condition>; Example: a) Select * From Student_table Where class= &quot;BCS&quot;; 油 b) Select Name, Reg_no From Student_table Where class= &quot;BCS&quot;;
  • 32. 油