230 likes | 358 Views
CpSc 3220 The Language of SQL. Chapter 17 Modifying Data. SQL handles CRUD operations. The DML part of the SQL language allows the implementation of all CRUD operations C - create: handled by the INSERT statement R – retrieve: handled by the SELECT statement
E N D
CpSc 3220The Language of SQL Chapter 17 Modifying Data
SQL handles CRUD operations • The DML part of the SQL language allows the implementation of all CRUD operations • C - create: handled by the INSERT statement • R – retrieve: handled by the SELECT statement • U – update: handled by the UPDATE statement • D – delete: handled by the DELETE statement
The INSERT statement • INSERT statements allow in insertion of rows of data into an existing table • Each inserted row must be unique, i.e., it must contain the primary key • Some tables are defined to have auto-increment PKs and the DBMS will insert those values automatically • Values need not be inserted for all columns, columns for which no value is inserted are set to NULL • Insertions can be constrained by tables rules; i.e., PK and FK constraints
Basic Forms of the INSERT statement • There are two basic types of INSERT statements • Insert specific data that is included explicitly in the INSERT statement • Insert data that is obtained from a SELECT statement (a subquery)
General Format for Type One INSERT INSERT INTO tableName [(columnList)] VALUES (row1Values) [,(row2Values) . . .] ;
Example of Type One INSERT INSERT INTO Student (ID, name, dept_name, tot_cred) VALUES ('12127','Howard','Comp. Sci.','0') ;
Example of Type One INSERT insert into student values ('54444','Wilson','Comp. Sci.', '0');
Example of Type One INSERT insert into student (ID,name,tot_cred) values ('44499','Wilson', '0');
Example of Type One INSERT insert into student (name,ID,tot_cred) values ('Bixby','44999', '0');
Example of Type One INSERT INSERT INTO Student (ID, name, dept_name, tot_cred) VALUES (12127,’Howard’,,0);
Example of Type One INSERT INSERT INTO Student (ID, name, tot_cred) VALUES (12127,’Howard’,0);
Example of Type One INSERT INSERT INTO Student (ID,tot_cred, name) VALUES (12127, 0, ’Howard’);
Example of Type One INSERT insert into student values ('64445','Wilson','Comp. Sci.', '0'), ('74446','Jackson','Comp. Sci.', '0'), ('74447','George','Comp. Sci.', '0'), ('74440','James','Comp. Sci.', '0'), ('74440','Andrews','Comp. Sci.', '0');
Error in Type One INSERT insert into student values ('64445','Wilson','Comp. Sci.', '0'), ('74446','Jackson','Comp. Sci.', '0'), ('74447','George','Comp. Sci.', '0'), ('74440','James','Comp. Sci.', '0'), ('74440','Andrews','Comp. Sci.', '0');
Error in Type One INSERT insert into student values ('64445','Wilson','Comp. Sci.', '0'), ('74446','Jackson','Comp. Sci.', '0'), ('74447','George','Comp. Sci.', '0'), ('74440','James','Comp. Sci.', '0'), ('74448','Andrews','CompSci.', '0');
General Format for Type Two INSERT INTO tableName [(columnList1)] SELECT columnList2 FROM tableName2 WHERE condition;
Example of Type Two INSERT insert into student (ID,name,dept_name,tot_cred) select i.id,i.name,'Comp. Sci.','0' from instructor as i where dept_name='History';
The DELETE statement • Even simpler than the UPDATE • General form DELETE FROM tableName [WHERE condition] • There is an alternate form to delete an entire table: TRUNCATE TABLE tableName
The UPDATE statement • A bit more complex • We must specify the columns to be updated, give an expression that specifies the update, and include logic for determining which rows are to be updated. • All data can be supplied in the UPDATE statement • Some data may be determined by subqueries
General Form of an UPDATE statement UPDATE table SET colName1 = expression1 [,colName2 = exp2 . . . ] WHERE condition
An Example of an UPDATE statement UPDATE instructor SET salary = salary * 1.5 WHERE dept_name = 'Comp. Sci.';
Another Example of an UPDATE statement UPDATE takes SET grade = 'A+' WHERE course_id = 12345;
That’s It! • You know all the CRUD about SQL, the DML part • But you don’t know how to create at database • That’s what we look at next; the DDL part of SQL • Read Chapter 18 of the Rockoff textbook