Cơ sở dữ liệu - SQL

SQL 7/11/2011 1 Introduction  SQL commands can be classified in to three types:  Data Definition Language commands (DDL)  Data Manipulation Language commands (DML)  Data Control Language commands (DCL) 7/11/2011 2 Domain types  Numeric:  int. Integer (a finite subset of the integers that is machine-dependent).  smallint. Small integer (a machine-dependent subset of the integer domain type).  numeric(p,d). Fixed point number, with user- specified precision of p digits, with n digits to the right of decimal point.  real, double precision. Floating point and double- precision floating point numbers, with machine- dependent precision.  float(n). Floating point number, with user-specified precision of at least n digits. 7/11/2011 3 Domain types  String:  char(n). Fixed length character string, with user-specified length n.  varchar(n). Variable length character strings, with user-specified maximum length n  nvarchar(n). similar to varchar, except it uses Unicode and therefore doubles the amount of space required to store the data.  Text: holds data that is longer than 8,000 characters 7/11/2011 4 Domain types  Datetime:  datetime. store not only a date, but also a time alongside it.  smalldatetime. 7/11/2011 5 Create Table Construct  An SQL relation is defined using the create table command:  r is the name of the relation  each Ai is an attribute name in the schema of relation r  Di is the data type of values in the domain of attribute Ai 7/11/2011 6 create table r (A1 D1, A2 D2, ..., An Dn, (integrity-constraint1), ..., (integrity-constraintk)) Create Table Construct  department (dept_no, dept_name, location)  Primary key: dept_no  Candidate key: dept_name  CREATE TABLE department ( dept_no char(3) primary key, dept_name varchar(36) unique not null, location varchar(20) ) 7/11/2011 7 Integrity Constraints  Constraints  Null  Not null  Unique  Primary key  References (Foreign key)  Check  Default  On delete cascade  On update cascade  On delete set null 7/11/2011 8 Delete table  Delete the whole table (include all data) in database.  DROP TABLE TableName  Ex:  DROP TABLE DEPARTMENT 7/11/2011 9 Truncate table  The TRUNCATE TABLE command removes all the rows from the table. The truncate table also releases the storage space used by the table.  The syntax of TRUNCATE command is:  TRUNCATE TABLE tablename 7/11/2011 10 Alias  Tables listed in the FROM clause can be given an alternative name  An alias is created by:  Typing the name of the table  Pressing the space bar  Typing the name of the alias  One reason for using an alias is simplicity  A second reason for using an alias is that it is needed when joining a table to itself, called a self-join. 7/11/2011 11 Data Manipulation Language  Adding a New Row to the Table INSERT INTO tablename VALUES ('value1', 'value2',. . . ,'valuen')  Updating the Data in the Table UPDATE table name SET attribute value=new value WHERE condition;  Deleting Row from the Table DELETE FROM table name WHERE condition; 7/11/2011 12 INSERT statement  Adds one or more new rows to a table or a view.  Example:  Insert into department values('MT','Mathematics','E2')  Insert into department values(‘CSE',‘Computer Science & Engineering','E2')  Insert into employee values ('111111',‘Anna',‘Smith','MT','0122916425') 7/11/2011 13 UPDATE statement  Changes existing data in a table or view.  Example: update department set location='H1' where dept_no=‘CSE' DELETE statement  Removes rows from a table or view.  Example: Delete from Employee Where emp_fname='Anna ' SELECT Statement  Used for queries on single or multiple tables  SELECT  List the columns (and expressions) that should be returned from the query  FROM  Indicate the table(s) or view(s) from which data will be obtained  WHERE  Indicate the conditions under which a row will be included in the result  GROUP BY  Indicate categorization of results  HAVING  Indicate the conditions under which a category (group) will be included  ORDER BY  Sorts the result according to specified criteria Operators  Comparison operators (=, , and so on)  String comparisons (LIKE, NOT LIKE)  Logical operators (AND, OR, NOT)  Ranges (BETWEEN and NOT BETWEEN)  Lists of values (IN and NOT IN)  Unknown Values (IS NULL and IS NOT NULL)  Exists in Subquery (EXISTS and NOT EXISTS)  Use DISTINCT to eliminate duplicates  [ TOP (expression) [PERCENT] [ WITH TIES ] ] 7/11/2011 17 SELECT statement  The SQL syntax to see all the columns of the table is: SELECT * FROM table name  Syntax of SELECTION Operation: SELECT * FROM table name WHERE condition;  Syntax of PROJECTION Operation SELECT column name1, column name2, Column name N FROM table name  Syntax for SELECTION and PROJECTION SELECT column name1, column name 2. .... column nameN FROM table name WHERE condition; 7/11/2011 18 SELECT statement 7/11/2011 19 - ASCII() - CHAR() - UPPER() - LOWER() - LEN() - LTRIM() - RTRIM() - LEFT() - RIGHT() - AVG() - MIN() - MAX() - SUM() - COUNT() - SQUARE() - SQRT() - ROUND() - GETDATE() - DATEPART(YY,getda te()) - DATEDIFF(X,Y,Z) - DAY(),MONTH(),YE AR() Maths String Time SELECT data  SELECT * FROM DANHMUCSACH 7/11/2011 20 SELECT data  SELECT * FROM DANHMUCSACH WHERE MANHOM = 'N001' 7/11/2011 21 SELECT - COUNT  COUNT (*) Function:  return the number of rows of the relation. Command will take NULL values into account.  COUNT (DISTINCT attribute_name):  Return the number of Rows of the relation, by eliminating duplicate values. SELECT - COUNT  SELECT COUNT(*) TONGSOSACH FROM DANHMUCSACH WHERE MANHOM = 'n001' MAX, MIN, AVG, and SUM  MAX Command SELECT MAX (attribute name) FROM table name;  MIN Command SELECT MIN (attribute name) FROM table name;  AVG Command SELECT AVG (attribute name) FROM table name;  SUM Command SELECT - GROUP BY  The GROUP BY clause is used to group rows to compute group-statistics.  The syntax of GROUP BY command is: SELECT attribute name, aggregate function FROM table name GROUP BY attribute name; SELECT - GROUP BY  SELECT MAHD, SUM(SOLUONG) AS 'TONG SO SACH' FROM CHITIETHOADON GROUP BY MAHD SELECT - HAVING  The HAVING command is used to select the group. In other words HAVING restricts the groups according to a specified condition.  The syntax of HAVING command is: SELECT attribute name, aggregate function FROM table name GROUP BY attribute name HAVING condition; HAVING  SELECT MAHD, SUM(SOLUONG) FROM CHITIETHOADON GROUP BY MAHD HAVING SUM(SOLUONG)>100 SELECT - SORTING  The SQL command ORDER BY is used to sort the result in ascending or descending order.  The syntax of ORDER BY command to arrange the result in ascending/ descending order is: SELECT * FROM table name ORDER BY attribute name ASC/ DESC; SELECT - SORTING  SELECT * FROM NHANVIEN ORDER BY TENNV Join Types  JOIN or INNER JOIN  Each row in table to left will be joined with one or more rows in table to right.  Any rows in left table that do not have corresponding rows in the right table will not be in the result table.  LEFT JOIN or LEFT OUTER JOIN  Any rows in left table that do not have corresponding rows in the right table will be in the result table with null values for fields from the right table. Join Types  RIGHT OUTER JOIN  Keeps stray rows from the right table, filling columns from the left table with NULL values if no corresponding data. Join Types  SELECT MAHD, D.MASACH, SOLUONG FROM CHITIETHOADON CT JOIN DANHMUCSACH DM ON D.MASACH = C.MASACH OR  SELECT MAHD, D.MASACH, SOLUONG FROM CHITIETHOADON CT, DANHMUCSACH DM WHERE CT.MASACH = DM.MASACH Set Operations  UNION Operation  If we have two relations R and S then the set UNION operation contains tuples that either occurs in R or S or both.  UNION/ UNION ALL  INTERSECT Operation  The intersection operation returns the tuples that are common to the two relations.  EXCEPT Operation:  EXCEPT returns any distinct values from the left query that are not also found on the right query. View  View: a virtual collection of records from existing tables  Being used for security and/or performance issues View  Creating view  Removing view CREATE VIEW [schema_name.]view_name [(column[,...n])] [ WITH [ ,...n ] ] AS select_statement [ WITH CHECK OPTION ] [ ; ] ::={[ENCRYPTION] [SCHEMABINDING] [ VIEW_METADATA ]} DROP VIEW { view_name } [ ,...n ] View  Creating view  CREATE VIEW VWSACH AS SELECT TOP 10* FROM DANHMUCSACH ORDER BY TENSACH STORE PROCEDURE  A stored procedure is a group of Transact- SQL statements that is compiled one time, and then can be executed many times.  This increases performance when the stored procedure is executed because the Transact-SQL statements do not have to be recompiled. STORE PROCEDURE STORE PROCEDURE  Execute store procedure EXECUTE ProductName [[, n][ OUTPUT ]]  Ex: EXECUTE PTBACNHAT 1,2 STORE PROCEDURE  CREATE PROC PTBACNHAT @A INT, @B INT AS IF(@A=0) BEGIN IF(@B=0) BEGIN PRINT 'PHUONG TRINH VO SO NGHIEM' END ELSE BEGIN PRINT 'PHUONG TRINH VO NGHIEM' END END ELSE BEGIN PRINT 'NGHIEM PT = '+ CAST((-@B*1.0/@A)AS CHAR) END