1 / 14

SQL

Learn the essentials of SQL file organization, indexing, and security concerns, along with optimal code implementation and theory. Explore different SQL servers like MySQL, MS SQL, Oracle, Sybase, and Access.

bevill
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 pepper

  2. Why SQL • File I/O is a great deal of code • Optimal file organization and indexing is critical and a great deal of code and theory implementation • File locking • Security concerns • Much more

  3. Different SQL Servers • Mysql (open source – no cost to use though may cost to embed and resell outside gpl) • Ms sql (microsoft ) • Oracle • Sybase • Access • Older AS/400 – SQL machine

  4. Structure • SQL Server runs a service • accepts sql commands using their version of the standard query language • Allows access to the data inside the SQL server • Organized into databases • Tables (like spreadsheets) inside databases • Gui management interface • Access / mysql workbench / mssql studio mgr

  5. Your databases • Connect with : mysql -u yourdbuser -p yourdatabase • The –p means the password will be entered later. You can also put the password right after the p as in pmypassword. No spaces and no quotes • Your database name is your db user and is usually your ecampus logon name • You should also be able to connect to pepperdb • See your tables show tables • See information inside your table select * from tablename

  6. Create a table • CREATE TABLE table_name (column_namecolumn_type ); • Plus insert PRIMARY KEY ( tutorial_id ) at end • Ex: • create table tutorials_tbl( tutorial_id INT NOT NULL AUTO_INCREMENT, tutorial_title VARCHAR(100) NOT NULL, tutorial_type VARCHAR(40) NOT NULL, submission_date DATE, PRIMARY KEY ( tutorial_id ) ); Credit to : http://www.tutorialspoint.com/mysql/mysql-create-tables.htm

  7. Create a table simpler • CREATE TABLE table_name (column_namecolumn_type ); • Plus insert PRIMARY KEY ( tutorial_id ) at end • Ex: • create table tutorials_tbl( tutorial_id INT , tutorial_title VARCHAR(100) , tutorial_type VARCHAR(40) ); Credit to : http://www.tutorialspoint.com/mysql/mysql-create-tables.htm

  8. Create a related table • Create table tutorials_types ( type VARCHAR(40) NOT NULL, type_name VARCHAR(100) NOT NULL, type_managerint not null, PRIMARY KEY ( type) ); Table picture from wikipedia

  9. Insert a row • INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN ); • Examples: • INSERT INTO tutorials_tbl (tutorial_title, tutorial_type, submission_date) VALUES ("Learn PHP", "php",NOW()); • INSERT INTO tutorials_tbl (tutorial_title, tutorial_type, submission_date) VALUES ("Learn MySQL", "db",NOW()); • INSERT INTO tutorials_tbl (tutorial_title, tutorial_type, submission_date) VALUES ("Learn Oracle", "db",NOW()); Credit: http://www.tutorialspoint.com/mysql/mysql-insert-query.htm

  10. Insert rows into another table • INSERT INTO tutorials_types values ("php", "php tutorials",10); • INSERT INTO tutorials_types values ("db", "database tutorials",20);

  11. Query your tables • select * from tutorials_tbl; • select * from tutorials_types; • select * from tutorials_types inner join tutorials_tbl on tutorials_tbl.tutorial_type = tutorials_types.type;

  12. Select statement • Select – keyword indicating just looking • List all columns (* for all; table.column) • From – keyword indicating table names follow • Table name • Join type + next table + on + matching fields • Where – keyword indicating row selection • Column = something

  13. Switch to another database • Show databases • Use <database name>

  14. Summary • What is SQL • How to open a database • How to create tables • How to query tables – very, very basic • How to switch database context

More Related