1 / 17

Very basics on DB access

Learn the basics of database access, including creating accounts in Oracle and MySQL, setting up the Oracle environment, and useful SQL commands.

mpatricia
Download Presentation

Very basics on DB access

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. Very basics on DB access Elke A. Rundensteiner

  2. DBMS? • Oracle • Accounts already created • Documentation: http://www.oracle.com • mySQL: • To create an account, visit http://www.wpi.edu/Academics/CCC • Documentation: http://www.mysql.com • Many other DBMS engines out there

  3. Oracle Version at WPI • The oracle server SID is WPI11GR2 (or, CS) • Version: 11g 11.2.0.3.0 (64 bit version) • System name is oracle.wpi.edu • port is #1521 Problems: user names cannot have a “.” in them.

  4. How to set up path for Oracle • If you use CCC linux systems, then you need to source /usr/local/bin/csoraenv file to setup your shell to use oracle • On CCC, if your shell is set to /bin/tcsh, then run : source /usr/local/bin/csoraenv • On CCC, if your shell is set to /bin/bash, then run : source /usr/local/bin/oraenv • To figure out what the type of shell that you use, type: echo $SHELL • From your shell prompt, type : echo $PATH

  5. How to set up Oracle • Or, set environment variables by adding the needed settings into your .cshrc file on the ccc.wpi.edu machine (which gets sourced automatically when you log on). setenv ORACLE_BASE /usr/local/oracle11gr203 setenv ORACLE_HOME /usr/local/oracle11gr203/product/11.2.0/db_1 setenv PATH ${PATH}:${ORACLE_HOME}/bin setenv ORACLE_SID WPI11GR2 setenv TWO_TASK ${ORACLE_SID}

  6. How to set up Oracle • Or, set environment variables by adding the needed settings into your .cshrc file on the ccc.wpi.edu machine (which gets sourced automatically when you log on). setenv ORACLE_BASE /usr/local/oracle11gr203 setenv ORACLE_HOME /usr/local/oracle11gr203/product/11.2.0/db_1 setenv PATH ${PATH}:${ORACLE_HOME}/bin setenv ORACLE_SID WPI11GR2 setenv TWO_TASK ${ORACLE_SID}

  7. How to set up Oracle • If in tcshor csh shell, your .cshrcfile would be: setenv ORACLE_BASE /usr/local/oracle11gr203 setenv ORACLE_HOME ${ORACLE_BASE}/product/11.2.0/db_1 setenv PATH ${PATH}:${ORACLE_HOME}/bin setenv ORACLE_SID WPI11GR2 setenv TWO_TASK WPI11GR2 setenv LD_LIBRARY_PATH ${ORACLE_HOME}/lib

  8. How to set up Oracle If in bash shell, your .cshrc file would be: setenv ORACLE_BASE=/usr/local/oracle11gr203 setenv ORACLE_HOME=${ORACLE_BASE}/product/11.2.0/db_1 setenv PATH=${PATH}:${ORACLE_HOME}/bin setenv ORACLE_SID=WPI11GR2 setenv TWO_TASK=WPI11GR2 setenv LD_LIBRARY_PATH=${ORACLE_HOME}/lib

  9. How to set up Oracle (contd…) • If your path is empty, then add one additional line, as below, on top of your .cshrc file : setenv PATH . The rest is as on previous slide

  10. How to set up Oracle • After editing the file “.cshrc “ • Please run below: or log out and back in: source .cshrc Or, log out and back in.

  11. Problems while setting up Oracle • Important – Set up Oracle immediately and see that it is working • you may run into problems, typically due to simple typos or minor setup issues • If you have identified a project partner, start working with him/her on this to help each other • If you get really stuck, please send a message to mywpi to show a screen-dump of what happens.

  12. Oracle introduction • Connecting • sqlplus <userName>/<PASSWORD> • Change passwd using password command • You will end up submitting your passwd; • therefore don’t use password that you use for other purposes.

  13. Oracle useful commands These commands can be executed from SQL shell SELECT * FROM cat; -- lists tables you have created SELECT table_name FROM user_tables; -- as above. DESCRIBE <tableName>; -- describes schema for table with name tableName help index; -- shows list of help topics; help start; -- illustrates how to use command start exit; -- exit from SQL shell

  14. Using Oracle from Windows • Multiple ways: • Use aquastudio software from aquafold.com. connect to -- server: oracle.wpi.edu port: 1521 (this is the default) SID: WPI11GR2 • Download oracle client for windows. Connect using sqlplus client or other tools: sqlplus rundenst/rundenst@//oracle.wpi.edu:1521/cs.wpi.edu

  15. Working with the Data Server

  16. Basic SQL Commands CREATE TABLE student(sNum INTEGER,sName VARCHAR (30)); -- creates table student with two columns INSERT INTO student VALUES (1, ‘Joe’); -- insert one row into the student table SELECT * FROM student; -- select all rows from student table DELETE FROM student; -- delete all rows in the student table DROP TABLE student; -- drop student table Purge recyclebin; -- purge recyclebin tables that get created. -- Only works if you are logged onto CCC1

  17. Saving your Interactive Session in SQLPlus • If you want to save your output to a file (for homework) • spool <fileName> • <executeCmds to create tables ...> • spool off;

More Related