learning sql pdf

age6.INSERT into statementInsert to inserts a new record into the tableSyntax: 1. No need to specify the column name to insert, simply provide the inserted value to insert INTO table_name values (value1, value2, Value3)2. Specify the inserted column name INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...)Example: INSERT into user (name, gender, country) VALUES ("Wang Shuai", ' Male ', ' Chinese ') inserts a new row into the user table7.UPDATE statementsUPDATE

. nvarchar () double-byte storage. Text: Large space.　　C. Date data type: Datetime,smalldatetime　　D. Photo type: Image, generally not used, only the database record location.4. FOREIGN KEY constraintsRight-to-load, from table (foreign key table) to main table (primary key table).The data in the primary table cannot be deleted until the data in the child table is deleted.5. Check constraintsColumn value right--->check constraint--add keyword (email like '%@% ')6. Build diagramGraph right---Select

Tags: define type col document DEP pre raise system-defined variable1. Exception Classification: System exceptions, custom exceptions(1) System exceptions, see related API documentation(2) Custom exceptionsDefine your own exceptions: As with custom variables, the type is exceptionException thrown: Use raise to throw a custom exceptionSetServeroutput onDeclare cursorCemp is SelectEname fromEmpwhereDeptno= -; Pename Emp.ename%type; --Self define exceptionSelf_no_data_found exception;begin Op

Label:The following is a table of dataExecutes the select content from dbo. Logo, return the following resultsBut this time we only need one of the same content, but the query came out obviously a lot of repetition,So we just need to add the DISTINCT keyword in front of the field that needs to be queried, so execute the following codeSelect DISTINCT content from dbo. LogoAll the duplicate records were removed.We need to be careful when using the DISTINCT keyword:Distinct cannot be partially used

present in the target table. Assuming moreThe new data does not exist in the target table, which results in a violation of the FOREIGN KEY constraint exception. 2.4 Deletion of data Data in the database generally have a certain life cycle, when the data is no longer needed, we have to delete it,You can delete data from a table by running the DELETE statement.Just be aware that the deleted data row is aThe referenced data in a foreign key correlation relationship, the deletion will fail. If you

be done with the RTrim () function.LTRIM (remove all the blanks on the left), TRIM () strips the left and right sides of the string.3. When stitching is complete, a new column is created, but there is no name so that it cannot be referenced when it is used. Thus, we need to give it an alias, given by using the AS keyword.SELECT concat (Trim (vend_name),' (', Trim (vend_country),' )' ) as from vendors; Results:4. Perform arithmetic operations SELECT from WHERE order_num = 20008;Now calculate

=replace (count_,100,99)Replace the Count_ column in table A with 100 to 99;Delete Table ADrop table if exists A;Modify Table StructureALTER TABLE A modify column A.id_ VARCHAR (50); ----Modifying the type of a fieldREPLACE into a (a.id_, A.count_) VALUES (11,14);ALTER TABLE a MODIFY COLUMN a.id_ VARCHAR (50);/* Add a field not null*/ALTER Table A add COLUMN money int not NULL;/* Delete a field */ALTER TABLE A DROP COLUMN money;/* Modify a field */ALTER TABLE A MODIFY COLUMN a.id_ INT (4);ALTER

Label:1. Create a new database TestDB 2. Create a new table for the TestDB database userinfo Create a table named UserInfo, including the above fields, where the ID is the primary key and set the identity specification, Nation set the default value "China". Add some data to the table: --using the TestDB database use TestDB go--view data in a userinfo tableSelect* fromUserInfo--add data records for UserInfo insert into UserInfo values ('1001','Zhang San','123','Wuhan Hongshan District','[email

) Restriction conditions2.1) The child result set must have the same structure.2.2) The number of columns in the child result set must be the same2.3) The data type corresponding to the child result set must be compatible.2.4) Each child result set cannot contain an order BY and a COMPUTE clause.3) use of the intersection-- InterSect Intersection-- Select * from InterSectSelect* from Person24) Results of the queryReference URL:Http://www.cnblogs.com/kissdodog/archive/2013/06/24/3152743.htmlOpe

returned minus the data rows that do not conform to the WHERE clause query criteria.(1) Do not use the WHERE clauseStatement format: SELECT * from A *, b * from A as a cross JOIN B as B(2) using the WHERE clauseStatement format: Select a *, B * from A as a cross JOIN B as B where a.id=b.id and number = ' 10 '6. Natural connection-when querying multiple tables, not only can you connect to two different tables, but you can also query a table with yourself, which is called Natural connectionStatem

1 ----Paged Query @pageindex page @pageSize page There are several records2 Create procgetpagelist3 @pageIndex int,4 @pageSize int,5 @rowsCount intOutput6 as7 begin8 Select @rowsCount= COUNT(*) fromStudentinfo9 Select * fromTen(Select *, Row_number () Over(Order bystuid) One asRowIndex fromStudentinfo) asSTU1 A whereRowIndexbetween(@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize - End - --Execute the query process the Declare @temp int - execGetpagelist2,2,@tempOutput - Print @

table, so it is certainly not possibleDECLAREDECLATR @ variable name data typeThe default value of the variable is NULLAssign valueSET @ variable name = valueSELECT @ variable name = valueTake valuePRINT @ Variable nameGlobal variables@ @ERROR Record the last error number that occurred@ @IDENTITI The last marked value inserted@ @MAX_CONNECTIONS The number of connection that can be created at the same time per server is limited@ @ROWCOUNT The number of rows affected by the last statementIF ELSEi

Label:--1-10--set serveroutput on declare with a while loop to print the numbers- -Define the loop variable to assign an initial value of 1 pnum number: = 1; Begin While pnum --use loop loop to output 1-10 -Note that the loop loop and the while loop are opposite declare pnum number:=1; Begin Loop exit when pnum>10;--exits loop loop when Pnum variable is greater than 10 -output loop variable dbms_output.put_line (pnum) ; --cyclic variable self-increment pnum:

Label: 1 Select --* 2request_session_id asspid,3Resource_type asResType,4resource_database_id asdbid,5 db_name(resource_database_id) asdbname,6Resource_description asRes,7resource_associated_entity_id asResid,8 Case whenResource_type= 'OBJECT' Then object_name(resource_associated_entity_id)9 End Ten asObjectName, OneRequest_mode asmode, ARequest_status asStatus - fromsys.dm_tran_locks - the - Select --* -session_id asspid, - Connect_time, + Last_read, - Last_write, + Most_recent_sq

When I first watched the instructor's video on Database learning, I was actually looking at it for half-knowledge and half-solution. I felt like listening to tianshu. What did I talk about? Why? But let's think about what Mr. Mi asked us and didn't take the exam. Just take a good note once and summarize and draw a mind map from time to time, this is roughly the case. The original understanding was that SQL

Where can I start learning about Oracle? Here, I will take you to the door of Oracle. One chapter a day, seven days of entry is not a dream. 1. SQL language Overview 1.1 SQL language classification Data Definition Language (DDL) Used to CREATE, modify, and delete database objects, such as create table, alter table, and drop table. DDL statements automatically com

Label:The test case table is as follows: 1. Take the intersection of 2 member tables (A∩B) T-sql: Select Member1.name,member1.age from Member1 join Member2 on Member1.name=member2.name SELECT * from Member1 intersect select * FROM Member2 2. Take the ∪ of the 2 member tables (a-B) T-sql: SELECT * FROM Member1 Union SELECT * FROM Member2 3. Take in A is not in B (A- B) T-

Tags: UTC level init Delete Table control CIN suggested statement desc1.1 SQL Overview 1.1.1 SQL statement IntroductionStructured Query Language (structured query Language) is referred to as SQL, which is the specification that the relational database management system should follow. Different database Manufacturers support S