1 / 20

SQL

SQL. CMSC 461 Michael Wilson. Finally, some code. This is where the theory and practice actually come together Basically taking the relational algebra and mapping it all to a query language The concepts are very close to what we’ve already seen, but there are some nuances. One true SQL?.

kermit
Download Presentation

SQL

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. SQL CMSC 461 Michael Wilson

  2. Finally, some code • This is where the theory and practice actually come together • Basically taking the relational algebra and mapping it all to a query language • The concepts are very close to what we’ve already seen, but there are some nuances

  3. One true SQL? • There are ISO/ANSI SQL standards • In my experience, different DBMSes have different/proprietary extensions that can make raw SQL queries pretty DBMS specific • The extensions aren’t exactly frequent • Invariably, it affects whatever thing you’re working on

  4. DBMS to focus on? • I firmly believe that PostgreSQL is going to be a big deal • MySQL is getting mired in some drama relating to being owned by Oracle • Forked off into MariaDB • Therefore, I’m going to focus on Postgres • Also, AWS RDS supports Postgres now • You are welcome to use whatever you wish for projects • RDS supports MySQL and PostgreSQL, but you’re not limited to AWS

  5. Before we get started • PostgreSQL’s SQL language documentation • http://www.postgresql.org/docs/9.3/static/sql.html • Use it! • PostgreSQL is case insensitive and insensitive to whitespace between commands • Can spread a command across multiple lines or type it all on one line

  6. Getting started • Most DBMSes can have multiple databases • Each database can consist of many tables • If we configure PostgreSQL using AWS RDS, then we don’t have to create the database ourselves • This can be a little complex in Postgres • We’re going to assume the database is created

  7. Connecting to your database using psql • psql –h <hostname> -d <database_name> -U <username> • Command line PostgreSQL client • On success, this will drop you to a prompt

  8. Connecting to your database using psql

  9. Table creation • Before you can do anything, you need to create a table • Basic syntax: • CREATE TABLE table_name (column_namedata_type,column_namedata_type, ….);

  10. Table creation • This is effectively creating a new relation • We’ll be referencing this a lot • We’ll also be building on it, so expect to learn new features about table creation as we go • This syntax will create a basic table with columns

  11. Data types • Some basic ones to know • Integer (signed 4 byte integer) • Numeric (numbers with high precision) • Kind of like a double, but has EXACT calculations • Boolean (true/false) • Varchar (character strings of variable lengths) • Text(unlimited length strings) • Not part of SQL standard

  12. Data types • Databases support TONS of data types • Numbers, dates, booleans, money, geometric shapes, JSON, etc. • Some are part of the SQL standard, some aren’t • Best to refer to the PostgreSQL documentation for guidance on data types • Lots of nuances, recommendations, etc.

  13. Table creation example

  14. Table creation example • CREATE TABLE AddressBook (address varchar(256),daysSinceContact integer,contactPhoneNumbervarchar(64),numberTypevarchar(16),contactNamevarchar(256));

  15. Table insertion • Figure it might be handy to actually add data to our table • This is like adding a tuple to the relation • Basic syntax: • INSERT INTO <table> (column_name1, column_name2, … column_name_n)values (value_1, value_2, …, value_n)

  16. Table insertion • You can specify the columns in any order you want • As long as you specify the values in the corresponding order

  17. Table insertion • A note • If you recall, tuples are explicitly unique in databases • This is not the case in tables • They can be made this way, but this is not the default behavior

  18. Table insertion example • INSERT INTO AddressBook (address, daysSinceContact, contactPhoneNumber, numberType, contactName)VALUES (’21 Jump Street’, 40, ‘123 4567’, ‘Cell’, ‘Johnny’)

  19. Table insertion example

  20. Enough for today • See you next class!

More Related