CHAPTER 6 SQL SQL SQL
6-1 Table Column Data Type Row Record 1. DBMS 2. DBMS MySQL Microsoft Access SQL Server Oracle 3. ODBC SQL 1. Structured Query Language 2. IBM 3. 1986 10 ANSI SQL ANSI X3. 135-1986 1987 4. SQL SQL 6-2
6 DDL Data Definition Language CREATE ALTER DROP SQL DML Data Manipulation Language INSERT UPDATE DELETE DQL Data Query Language SELECT DCL Data Control Language GRANT REVOKE COMMIT ROLLBACK 6-3
6 6-4 SQL Select SELECT " " Distinct SELECT DISTINCT " " Where SELECT " " WHERE "condition" And/Or SELECT " " WHERE " " {[AND OR] " "}+ In SELECT " " WHERE " " IN(' 1', ' 2',...) Between SELECT " " WHERE " " BETWEEN ' 1' AND ' 2' 6-5
Like SELECT " " WHERE " " LIKE { } Order By SELECT " " [WHERE " "] ORDER BY " " [ASC, DESC] Count SELECT COUNT(" ") Group By SELECT " 1", SUM(" 2") GROUP BY " 1" Having SELECT " 1", SUM(" 2") GROUP BY " 1" HAVING( ) 6-6
6-5 SQL 1000 SELECT TOP 1000 [ ] FROM [ ] SELECT * FROM [ ] SELECT [ ] FROM [ ] 2 SELECT TOP 2 [ ] FROM [ ] 1000 SELECT DISTINCT TOP 1000 [ ] FROM [ ] 6-8
6 1000 SELECT TOP 1000 [ ] FROM [ ] SELECT [ ] as FROM [ ] 1000 SELECT [ ] FROM [ ] WHERE [ ] > 1000 1000 40 SELECT [ ] FROM [ ] WHERE [ ] > 1000 or >=40 6-9
1. DDL DML (A) C++ (C) Passcal (B) Jave (D) SQL 2. (A) C++ (C) SQL (B) Jave (D) Visual Basic 3. Crow s Foor Notation Chen Notation Crow s Foor Notation [Crow s Foor Notation] Chen Notation [Chen Notation] Customer (A) attribute (B) (C) (D) property 4. Crow s Foot Notation Chen Notation Crow s Foot Notation [Crow s Foot Notation] Chen Notation [Chen Notation] Customer Order (A) (B) (C) (D) (E) 5. (A) (C) (B) (D) 6-13
APPENDIX A Visual Basic Visual Basic Visual Basic Microsoft
A-4 Visual Basic Namespace Imports Namespace Data Types Boolean,Byte,Char,DateTime,Decimal,Double,Int16,Int32,Int64,Integer, Long, Object, Short,Single,String,IntPtr,UInteger,UintPtr Variable Declaration Dim Public Private <variable_name> As <type> Type Declaration Dim Public <variable><suffix> Suffixes % -Integer. $ - String, @ - Decimal, & - Long, # - Double,! - Single Arrays Dim <arrayname>(<maxindexval>) As <type> Dim <arrayname>(<lowerbound> To <UpperBound>) As <type> Initialize Array Dim <arrayname>() As <type> = {<value1>, <value2>,..., <valuen>} Change Size of Array ReDim <arrayname>(<maxindexval>) Comments 'Comment text 'No multi-line comments at this time A-8
Comments A XML Press the ' (apostrophe) key thrice. Line Continuation strtext = "To break a long string across multiple lines, " & _ "end the string, add the line continuation character " & _ "and continue the string on the next line. Arithmetic Operators + (Addition), - (Subtraction), * (Multiplication), / (Division), % (Modulus) String Concatenation +, & Relational Operators < (Less Than), <= (Less Than or Equal To),> (Greater Than),>= (Greater Than or Equal To),= (Equal To),<> (Not Equal To) Logical Operators OR, NOT, AND, AndAlso, OrElse, Xor String Manipulation.Substring(<start>,[<length>]).Trim() <trims from beginning & end of string>.trimend([<char array>]).trimstart([char array]).tolower() <to lower case>.toupper() <to upper case>.replace(<find>,<replace>).equals(<expression>) <6 available overloads>.contains(<string>).join(<seperator>,<value>,[<count>]).compare(<string1>,<string2>,[<ignore case>]) <7 overloads available>.copy(<string>) A-9
A-5 Microsoft.NET Framework Windows Web.NET Framework Common Language Runtime Class Library ASP ASP.NET.NET Framework Class Library.NET Framework.NET Framework SDK Value Type.NET Framework.NET Framework Data Provider NET Framework ADO.NET.NET Framework.NET Framework Populate DataSet DataSet ActiveX Control ActiveX Microsoft Visual Basic for Applications Microsoft Script Editor ADO.NET.NET Framework Class Library XML ADO.NET A-12
SqlTypes for SQL Server Application Service ASP.NET ASP.NET ASP.NET Application State ASP.NET Framework A.NET ASP.NET Microsoft.NET Framework Web Web ASP.NET HTML WML XML ASP.NET ASP.NET ASP.NET Web Visual Basic C#.NET Web Web Common Language Runtime Type Safety Inheritance Interoperability ASP.NET Application Services Database ASP.NET ASP.NET ASP.NET Web App_ Data SQL Server ASP.NET Page ASP.NET ASP.NET A-13
ASP.NET Server Control ASP.NET System.Web.UI.Control ASP.NET ASP.NET Web HTML ASP.NET Mobile ASP.NET runat="server" Attribute ASP.NET Web HTTP Web ASP.NET ASP.NET Web ASP.NET Web HTTP HTTP Assembly.NET Framework Managed Assembly Manifest Identity XCOPY Asynchronous Postback ASP.NET Web ViewState AJAX A-14