db-sql1.pdf - Databases - SQL - 1 Jianxin Li School of...

This preview shows page 1 - 10 out of 33 pages.

Databases - SQL - 1Jianxin LiSchool of Computer Science & Software EngineeringUniversity of Western AustraliaJianxin Li (UWA)Databases - SQL1 / 30
SQL - Part 1This lecture will coverThe Client-Server ModelStructured Query LanguageData Definition Language — how to create, alter and deletetablesData Manipulation Language — how to write queries on a single tableJianxin Li (UWA)Databases - SQL2 / 30
Client-serverDatabases are almost always accessed in aclient-serverfashionTheserveris a program that controls the data, “listens for” requests, andthenservesthe requestorsTheclientsare programs that make the requests and receive the responsesClient programs can be on the same machine or (more usually) remoteTypically, the server will handle requests from many different client programs“at the same time”.One of the most important tasks of a DBMS is to ensure that each user appearsto beisolatedfrom the others, even if they are working with thesame tableJianxin Li (UWA)Databases - SQL3 / 30
SQL implementationsThere are numerousimplementationsof SQL, and equally manyclientprograms, ranging from simple command-line-interface (CLI) programs tosophisticated GUI-based interfaces.In these lectures, I will useMySQL (v5.6.12) as the serverThe terminal programmysqlas the clientJianxin Li (UWA)Databases - SQL4 / 30
Database structureA single MySQL server manages a two-level hierarchyAt the top-level there are a number ofdatabasesEach database contains a number oftablesClient programs always maintain a notion of “the current database” and allnames and commands are interpreted relative to this database.Jianxin Li (UWA)Databases - SQL5 / 30
Looking around the databaseSHOW DATABASES;This shows all the databases on the serverUSE world;This makesworldthe current databaseSHOW TABLES;mysql> show tables;+-----------------+| Tables_in_world |+-----------------+| City|| Country|| CountryLanguage |+-----------------+3 rows in set (0.00 sec)Jianxin Li (UWA)Databases - SQL6 / 30
Looking round the databaseTheDESCRIBEcommand tells you about a table’s schema:mysql> DESCRIBE City;+-------------+----------+------+-----+---------+----------------+| Field| Type| Null | Key | Default | Extra|+-------------+----------+------+-----+---------+----------------+| ID| int(11)| NO| PRI | NULL| auto_increment || Name| char(35) | NO||||| CountryCode | char(3)| NO| MUL |||| District| char(20) | NO||||| Population| int(11)| NO|| 0||+-------------+----------+------+-----+---------+----------------+5 rows in set (0.01 sec)This table/relation hasfive attributes, so eachrowcomprises anID, aName, aCountryCode, aDistrictand aPopulation.Jianxin Li (UWA)Databases - SQL7 / 30
Making tablesThere are three commands that work withan entire tableat once:These commands create, alter, or delete therelation schema, rather thanworking with the data items (the rows).CREATE TABLE ...ALTER TABLE ...DROP TABLE ...REMEMBER: These commands all haveTABLEin their name — leaving this out is acommon error!Jianxin Li (UWA)Databases - SQL8 / 30
CreatingThe new table must benamedand itsschemadefinedCREATE TABLE City (ID INT,Name CHAR(35),CountryCode CHAR(3),

Upload your study docs or become a

Course Hero member to access this document

Upload your study docs or become a

Course Hero member to access this document

End of preview. Want to read all 33 pages?

Upload your study docs or become a

Course Hero member to access this document

Term
One
Professor
N/A
Tags
Relational model, Jianxin Li

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture