1 / 19

MySQL

MySQL. MySQL. To start go to http://localhost/~labuser/phpmyadmin Login details: login: labuser password: macimd15 There are 2 existing databases that can be used (test & labuser) & you may create a new database

clara
Télécharger la présentation

MySQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. MySQL

  2. MySQL • To start go to http://localhost/~labuser/phpmyadmin • Login details: login: labuser password: macimd15 • There are 2 existing databases that can be used (test & labuser) & you may create a new database • You can import a pre-existing set of database tables (definition & data) • You can export a created database at the end of your user session (table definitions & data)

  3. Main PhpMyAdmin Menu

  4. PhpMyadmin Database Menu

  5. Table creation • Tables can be created using the create table form • Tables can also be created using an SQL CREATE TABLE STATEMENT CREATE TABLE emp ( eno char(3), ename char(12), salary decimal(7,2), supno char(3), dno char(3) NOT NULL , PRIMARY KEY (eno) , FOREIGN KEY (dno) REFERENCES dept (dno) ) ENGINE=InnoDB; Basic table creation statement has keywords CREATE TABLE with the tablename (emp here), then In round brackets Pairs of values columnname datatype delimited by commas With semicolon as termination symbol

  6. Table creation issues • Columns (attributes) can be specified as NOT NULL (allows null values) except for the primary key • Primary Key specified as • PRIMARY KEY (columnname) • Foreign Key specified as • FOREIGN KEY (columnname) REFERENCES Tablename(PK column) • Data Types • Text (char(size), varchar) • Integers (tinyint, smallint, mediumint, int, bigint) • Floating point numbers (Decimal(Length, Decimal Places), Float, Double) • Date/Time (Data, DateTime, Time)

  7. SQL Queries • Retrieval • Based on SELECT.....FROM....WHERE..... Clauses • e.g. SELECT ename, salary FROM EMP WHERE dno= 'd1'; Get the names and salary of employees in department ‘dno’ • Insert record(s) • Can be done using the INSERT record form • Can be done using the INSERT INTO tablename VALUES (.............................) • e.g. INSERT INTO EMP VALUES ('e35', 'woods', 25000, 'e5', 'd3'); Add a new record into EMP with eno 'e35', ename 'woods', salary 25000, supervisor number 'e5', department number 'd3'

  8. SQL Queries (2) • Update a record(s) • UPDATE tablename SET column = newvalue WHERE column compared with value • e.g. UPDATE EMP SET salary = 30000 WHERE eno = 'e35'; Change the salary of employee number 35 to £30,000

  9. SQL Queries (3) • Delete a record(s) • DELETE FROM tablename WHERE column compared with value • DELETE FROM EMP WHERE eno = 'e35'; Delete the employee record where employee number = 'e35'

  10. SQL Query Interface

  11. Query Result

  12. Table Structure Screen

  13. Insert Record Screen

  14. Export Table screen

  15. Import Table Screen

  16. Sample table definition and data exported to a .sql file -- phpMyAdmin SQL Dump Table structure for table `EMP`-- CREATE TABLE IF NOT EXISTS `emp` ( `eno` char(3) NOT NULL, `ename` char(12) DEFAULT NULL, `salary` decimal(7,2) DEFAULT NULL, `supno` char(3) DEFAULT NULL, `dno` char(3) NOT NULL, PRIMARY KEY (`eno`), FOREIGN KEY (`dno`) REFERENCES `dept` (`dno`) ) ENGINE=InnoDB, DEFAULT CHARSET=latin1; Dumping data for table `EMP`-- INSERT INTO `EMP` (`eno`, `ename`, `salary`, `supno`, `dno`) VALUES ('e1', 'armstrong', '54000.00', '', 'd1'), ('e10', 'jones', '48000.00', 'e1', 'd3'), ('e11', 'kelly', '33000.00', 'e7', 'd2'), ('e12', 'mccoy', '24000.00', 'e3', 'd1'), ('e13', 'neeson', '24000.00', 'e19', 'd1'), ('e14', 'oliver', '24000.00', 'e17', 'd3'), ('e15', 'pearse', '20000.00', 'e21', 'd1'), ('e16', 'quinn', '24000.00', 'e2', 'd1'), ('e17', 'roberts', '31000.00', 'e4', 'd3'), ('e18', 'smyth', '19000.00', 'e21', 'd3'), ('e19', 'trainor', '35000.00', 'e7', 'd1');

  17. Alter Table commands • Change table type (defauly MyISAM to innodb) ALTER TABLE emp ENGINE=innodb • Add primary key ALTER TABLE emp ADD PRIMARY KEY (eno) • Add foreign key ALTER TABLE emp ADD CONSTRAINT c1 FOREIGN KEY (dno) REFERENCES dept (dno)

  18. Referential Integrity • Requires the following • Table type as innodb (rather than default MyISAM) • Primary keys must be applied • Any data entered must be consistent (all FK values must match existing PK values) • Best applied before entering data • Can be applied using • CREATE TABLE statement • ALTER TABLE statement • Relation View of phpMyAdmin table form

  19. Referential Integrity Implications • INSERT: Can’t add a foreign key (FK) value unless the corresponding primary key (PK) value already exists • DELETE: Can’t delete a record with a PK matching some FK elsewhere unless all records containing that FK value are first deleted (cascading delete) • UPDATE: Can’t change a PK value where corresponding FK values exist, or change a FK to any value where the corresponding PK value does not already exists

More Related