1 / 37

SQL DDL & DML

SQL DDL & DML. Chapters 6 & 7. Agenda. History of SQL DDL DML Summary. History of SQL. Structured Query Language (SQL) conceptualized by Codd , early '70s prototyped by IBM, mid-'70s as SEQUEL now standard for creating/manipulating relational structures

forest
Download Presentation

SQL DDL & DML

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 DDL & DML Chapters 6 & 7

  2. Agenda • History of SQL • DDL • DML • Summary

  3. History of SQL • Structured Query Language (SQL) • conceptualized by Codd, early '70s • prototyped by IBM, mid-'70s as SEQUEL • now standard for creating/manipulating relational structures • SQL-86, SQL-89, SQL-92 (+ SQL/CLI, '95), SQL-99 (SQL3), SQL:2003, SQL:2008, SQL:2011 • Many vendors extend standard

  4. SQL Commands • Data Definition Language (DDL) • define/change/remove database objects • Access to objects • Data Manipulation Language (DML) • maintain and query a data • control transaction integrity • Resources • http://www.w3schools.com/sql/default.asp • http://www.functionx.com/sqlserver/ • Sams Teach Yourself SQL in 10 Minutes, Forta, B. 2012. ISBN 9780672336072

  5. SQL Commands, con't… • Common Practices: • UPPERCASE text for reserved words • ProperCase for your names • Semi-colon to signify end of statement • Indentions and extra parentheses for readability • Use meaningful names

  6. SQL Server tips

  7. Sql server sql • SQL Server-specific practices • Use SSMS as UI • Preface each batch of SQL commands with USE dbname • Follow each batch of SQL commands with GO • Must be on line by itself • Use double quotes for object names, single quotes for literal strings • Click !Execute button (OR F5) to execute SQL commands • Commands will run without semi-colon, BUT use semi-colons anyway • All commands are permanent (ie, "COMMITed")

  8. Sql server Object References • Fully-Qualified Names • Server.Database.Schema.Object • Server/Instance Name • Defaults to local server OR server you're connected to • Created when SQL Server Express Edition installed • Database Name • Defaults to “Master” OR database you're connected to • Owner/Schema Name • Defaults to “DBO” or user's default schema • Object/Table Name • Required! • Examples: • [Gina-Green\SQLExpress].mis4340.student1.student • Order_t

  9. Sqlddl

  10. DDL • Creating Objects • Databases • Create database • Database Users • Create login • Create user • Database access rights • Create schema • Grant (permissions) • Data structures • Create table • Create (secondary) indexes • Maintaining Objects, Access • Alter • Drop • Revoke

  11. 1. Create databases • Computer file, physical container for objects • Must USE the Master database • Must have CREATE DATABASE permission • The 'sa' login ID has this permission • CREATE DATABASE dbname [ ON [ < filespec > [ ,...n ] ] [ , < filegroup > [ ,...n ] ] ] [ LOG ON { < filespec > [ ,...n ] } ] ; • Example: Use Master CREATE DATABASE mis_test;

  12. Create database Users & give them access rights • Steps • Create Login • Allows user to connect to the SQL Server instance • Create User • Allows user to access a specific DB in SQL Server • Create Schema (optional) • Specifies logical "container" to store user objects in • Grant Permissions • Gives users specific rights within a DB

  13. 2. Create users: logins • Allows users to connect to DB server • Ensure you are USEingthe Master database • Used for authentication • Windows • CREATE LOGIN existing_windows_login FROM WINDOWS [WITH DEFAULT_DATABASE = dbname]; • SQL Server • CREATE LOGIN new_db_login WITH PASSWORD = 'complex_pw' [, DEFAULT_DATABASE = dbname]; • Examples: • CREATE LOGIN [baylor\nancy_sanchez] FROM WINDOWS WITH DEFAULT_DATABASE = mis_test; • CREATE LOGIN student1 WITH PASSWORD = 'Password1', DEFAULT_DATABASE = mis_test;

  14. 2. Create users: Users • Allows users to access specific database(s) • Ensure you are USEing a specific database first • CREATE USER user_name [FOR LOGIN existing_db_login] | [WITHOUT LOGIN] [WITH DEFAULT_SCHEMA = schema_name]; • Example: • CREATE USER nancy_sanchez FOR LOGIN [baylor\nancy_sanchez]; • CREATE USER student1 FOR LOGIN student1 WITH DEFAULT_SCHEMA = student1;

  15. 3. Access Rights: schemas • Logical grouping (container) of objects w/in a DB • Establishes ownership of objects • Optional step (default is DBO) • Does NOT give owner right to create/access objects!!! • CREATE SCHEMA schema_name [AUTHORIZATION existing_user_name]; • Examples: CREATE SCHEMA student1 AUTHORIZATION student1; CREATE SCHEMA nancy AUTHORIZATION nancy_sanchez; NOTE: would also require an ALTER command to make this schema her default schema

  16. 3. Access Rights: Grants* • Give specific rights to access DB resources • Server • Database • Schema • Object • GRANT privilege [, privilege, role, …] [ON object_name] TO existing_user_name [WITH GRANT OPTION]; • Examples: • GRANT CONTROL TO nancy_sanchez; • GRANT CREATE PROCEDURE, CREATE VIEW, CREATE TABLE, CREATE ROLE, CREATE SCHEMA, CREATE SYNONYM, SELECT, INSERT, UPDATE, DELETE, EXECUTE TO student1; • GRANT SELECT ON SCHEMA :: student1 TO PUBLIC; • GRANT INSERT ON northwind.dbo.customer TO student1; *Roles, users, schemas, objects must be created first! Resources must exist first!*

  17. 4. Creating structures

  18. 4. Create tables • CREATE TABLEtable_name (column1_name data_type [(length)], column2_name data_type [(length)], …); • Example: CREATE TABLEOrder_t (Order_ID NUMERIC (4), Order_Date DATETIME, Customer_ID NUMERIC (4,0));

  19. Establishing Integrity Controls CREATE TABLE table_name (column_name data_type [(length)] [NULL|NOT NULL| DEFAULT value], other columns…, CONSTRAINT constraint_name PRIMARY KEY (column_name [, column_name, …]), CONSTRAINT constraint_name FOREIGN KEY (column_name [, column_name, …]) REFERENCES table_name (column_name) [ON DELETE|UPDATE NO ACTION|CASCADE], CONSTRAINT constraint_name CHECK (condition));

  20. Example • CREATE TABLE Order_t ( Order_ID NUMERIC(4) NOT NULL, Order_Date DATETIME DEFAULT GETDATE(), Customer_ID NUMERIC(4,0), CONSTRAINTOrder_pkPRIMARY KEY (Order_ID), CONSTRAINTOrder_customer_id_fkFOREIGN KEY (Customer_ID) REFERENCESCustomer_t (Customer_ID) ON DELETE CASCADE, CONSTRAINTOrder_Date_ccCHECK (YEAR(Order_Date) >= 2000) );

  21. Example: Create table from another table • Create a new table with the same structure as the Order_t table. SELECTTOP 0 * INTOOrder_Copy FROMOrder_t; • Create a new table with the same structure as the Order_t table, and load it with data for customer #1. SELECT * INTO Order_Customer1 FROM Order_t WHEREcustomer_ID = 1;

  22. 4. Create (secondary) Indexes • CREATE INDEX index_nameONtable_name (column_name[, column_name,…]); • Examples: CREATE INDEX Order_Cust_idxONOrder_t (Customer_ID); CREATE INDEXCustID_OrdIDONOrder_t (Customer_ID, Order_ID);

  23. Changing object definitions • ALTER LOGIN login ENABLE|DISABLE; DEFAULT_DATABASE =dbname; • ALTER USER existing_user_name WITH NAME = new_user_name; WITH DEFAULT_SCHEMA = schema_name; • ALTER SCHEMAschema_name TRANSFER schema.table_name; • ALTER AUTHORIZATION ON SCHEMA :: schema_nameTO user_name; • ALTER TABLEtable_name ADD column_namedata_type [(length)]; ADDCONSTRAINTconstraint_information; ALTER COLUMN column_namenew_data_type [new_length]; DROP COLUMNcolumn_name; DROP CONSTRAINTconstraint_name;

  24. Examples • ALTER TABLEOrder_tADD COLUMN OrdType VARCHAR(2); ALTER TABLEOrder_tADD OrdType VARCHAR(2); • ALTER TABLEOrder_tADDCONSTRAINTOrder_Date_Christmas_cc CHECK(DAY(Order_Date) <> 25 and MONTH(Order_Date) <> 12); ALTER TABLE Order_t ADD CONSTRAINT Order_Date_Christmas_cc CHECK (CONVERT(varchar(5),Order_Date,1) <> '12/25'); • ALTER TABLEOrder_tADDCONSTRAINTOrder_Time_cc CHECK (CONVERT(varchar(2),order_date,8)>='06' AND CONVERT(varchar(2),order_date,8)<='18'); ALTER TABLEOrder_tWITH NOCHECK ADDCONSTRAINTOrder_Time_cc CHECK (CONVERT(varchar(2),order_date,8)>='06' AND CONVERT(varchar(2),order_date,8)<='18'); • ALTER TABLEOrder_tDROP COLUMNOrdType; • ALTER TABLEOrder_tDROP CONSTRAINTOrder_Date_Christmas_cc; • ALTER TABLEOrder_tDROP CONSTRAINTOrder_Time_cc; • ALTER USER nancy_sanchezWITH NAME = nsanchez; • ALTER USER nsanchezWITH DEFAULT_SCHEMA = nancy1;

  25. Deleting objects or object access • DROP object_typeobject_name; • DROP INDEX order_cust_idx; • DROP TABLE order_t; • DROP SCHEMA nancy; • DROP DATABASEmis_test; • DROP USER nsanchez; • DROP LOGIN [baylor\nancy_sanchez]; • REVOKE privilege FROM user_name; • REVOKE SELECT ONnorthwind.dbo.customerFROM student1; • REVOKE SELECT ON SCHEMA :: student1 FROM public;

  26. Sqldml

  27. DML • Batching SQL Commands • Inserting Data • Updating Data • Deleting Data • Querying Data • Single Table • Multiple Tables

  28. Script Files • File of one or more SQL (T-SQL) commands • Good for batching multiple commands, automating tasks, and code sharing/reuse SSMS SQL... SQL... Database SQL... . . .

  29. Script Files, … • File of one or more SQL (T-SQL) commands • Good for batching multiple commands, automating tasks, and code sharing/reuse SSMS Database Script File SQL... SQL… SQL...

  30. Script Example use mis_test /* I expect to get errors from most of the DROP statements below since the tables don’t exist yet */ drop table order_line_t; drop table product_t; drop table order_t; drop table customer_t; GO /* create customer table */ create table customer_t (customer_id numeric(11,0) not null, customer_namevarchar(25) not null, customer_addressvarchar(30), city varchar(20), state varchar(2) default 'tx', postal_code numeric(5), constraint customer_pk primary key (customer_id)); GO /* add data to customer table */ insert into customer_t values (001, 'contemporary casuals','1355 s. hines blvd.','gainesville','fl','32601'); insert into customer_t values (002, 'value furniture','15145 sw 17th st.','plano','tx','75094'); insert into customer_t values (003, 'home furnishings','1900 allard ave.','albany','ny','12209'); insert into customer_t values (004, 'eastern furniture','1925 beltline rd.','carteret','nj','07008'); insert into customer_t values (005, 'impressions','5585 westcott ct.','sacramento','ca','94206'); insert into customer_t values (006, 'furniture gallery','325 flatiron dr.','boulder','co','80514'); insert into customer_t values (007, 'period furnishings','394 rainbow dr.','seattle','wa','97954'); insert into customer_t values (008, 'california classics','816 peach rd.','santa clara','ca','96915'); insert into customer_t values (009, 'm and h casual furniture','3709 first st.','clearwater','fl','34620'); insert into customer_t values (010, 'seminole interiors','2400 rocky point dr.','seminole','fl','34646'); insert into customer_t values (011, 'american euro lifestyles','2424 missouriave. n.','prospect park','nj','07508'); insert into customer_t values (012, 'battle creek furniture','345 capitol ave. sw.','battle creek','mi','49015'); insert into customer_t values (013, 'heritage furnishings','66789 college ave.','carlisle','pa','17013'); insert into customer_t values (014, 'kaneohe homes','112 kiowai st.','kaneohe','hi','96744'); insert into customer_t values (015, 'mountain scenes','4132 main street','ogden','ut','84403'); GO CREATE TABLE Order_t (Order_ID NUMERIC(4) NOT NULL, Order_Date DATETIME DEFAULT GETDATE(), Customer_ID NUMERIC(4,0), CONSTRAINTOrder_pkPRIMARY KEY (Order_ID), CONSTRAINTOrder_customer_id_fkFOREIGN KEY (Customer_ID) REFERENCESCustomer_t (Customer_ID) ON DELETE CASCADE, CONSTRAINTOrder_Date_ccCHECK (YEAR(Order_Date) >= 2000)); GO

  31. Steps to Create/Run Scripts • Create and edit script using text editor • SSMS Query Editor • Other text editor • Eg, Notepad or Notepad++ or Editpad, … • Save script as a file with .sql extension • Run script from SSMS • File | Open | File… • Click !Execute button (OR F5) • executes all commands in script

  32. Transaction Control • What is a Transaction? • Properties: ACID • Transaction Control • Commit; • eg, save • Rollback; • eg, undo • Default in SSMS is to: • Autocommit • Read committed data • To change SSMS defaults (don't do!): • Tools | Options | Query Execution | SQL Server | Ansi • Tools | Options | Query Execution | SQL Server | Advanced

  33. Inserting Data • INSERT INTO table_name [(column1_name, column2_name, …)] VALUES (column1_value, column2_value, …); • Examples (first create the order tables in figure 7-3): INSERT INTO Order_tVALUES (1001, '21-OCT-00',1); INSERT INTO Order_t (order_id, customer_id) VALUES (9000,8); INSERT INTOOrder_tVALUES (1099, CONVERT (datetime, '03:25:00', 8), 8); INSERT INTOOrder_tVALUES (1099, CONVERT (varchar(10), getdate(), 126) + 'T03:25:00', 8); INSERT INTOOrder_tVALUES (1098, CONVERT(varchar(4),'2003',112), 2); INSERT INTOOrder_tVALUES (1097, CONVERT (varchar(17), '02 MAY 2009 15:30', 113), 2);

  34. Inserting Data, cont… • Can insert multiple rows in a single INSERT • E.G., INSERT INTO tbl1 (fname, lname) VALUES ('Tom', 'Smith'), ('Geri', 'Green'), ('Leo', 'Smith'); INSERT INTOSeniorStudents SELECT * FROM student WHERE classification = 'sr';

  35. Updating Data • UPDATE table_name SET column1_name = new_value [,column2_name = new value,…] [WHERE condition(s)]; • Examples: UPDATE Order_t SET Customer_ID = 2 WHERECustomer_ID = 1; UPDATEOrder_t SET Order_Date = CONVERT (varchar(10), Order_Date ,120) + ' ' + '15:15:00' WHEREOrder_ID = 1010;

  36. Updating data, cont… • Can update a table with values from another table • Example: ALTER TABLE order_t ADD cname VARCHAR(50); UPDATE order_t SET cname = customer_t.customer_name FROM order_t, customer_t WHERE order_t.customer_id = customer_t.customer_id; ALTER TABLE order_t DROP COLUMN cname;

  37. Deleting Data • DELETE FROM table_name [WHERE condition(s)]; • Examples: DELETE FROM Order_t WHERECustomer_ID = 2; DELETE FROMOrder_t;

More Related