sql basics pdf

transaction can lock the entire table. Intent locks include intent sharing (IS), intent Exclusive (IX), and intent exclusive sharing (SIX). Schema Lock: The database engine uses schema modification (SCH-M) locks during table data definition language (DDL) operations, such as adding columns or dropping tables. During the lock, the SCH-M lock prevents concurrent access to the table. Bulk Update locks: Bulk Update locks (BU locks) allow multiple threads to load data concurrently

two operands have the same data type, the result of the expression is the same data type. If two operands have different types, operands with lower type precedence are automatically converted to higher-level types T-SQL operator precedence rules: 1. () 2.*,/,% 3.+,-, 4.=,>, 5.NOT 6.AND 7.between,in,like,or 8. = --Operators Precedence --and precedes OR SELECTOrderID, CustID, Empid, OrderDate fromsales.ordersWHERECustID= 1 andEmpidinch(1,3,5)

@startdate datetime Set@startdate= ' 1/1/2000 ' Select ' Start Date: ' +convert (varchar @startdate) The--convert () function is used to convert the data type, see 4.8.4 The results of the operation are as follows: Start Date:jan 1 2000 Example 4-8: Use Pangu Select ' The highest salary of the employee is: ' + e_name+ ' monthly salary: ' +convert (varchar) e_wage From employee where e_wage= (select Max (e_wage) From employee) The results of the operation are as follows: The employe

; - Then RAISE age_exception;END IF;(3) in the Plsql abnormal handling part of the abnormal situation to make corresponding treatment.EXCEPTION when Then dbms_output.put_line (' age is only between 0-100! ');Example: (Judging whether the age is between 0-100)DECLAREV_age Number:= Age ; Age_exception EXCEPTION;BEGIN IFV_age 0 ORV_age> - ThenRAISE age_exception; END IF; EXCEPTION whenAge_exception ThenDbms_output.put_line ('age can only be between 0-100! ');END;Except

in ascending (ASC) or descending (DESC) Order of the value of the field, which is ascending by default. where *: denotes all fields of the corresponding table; DISTINCT: Removes duplicate rows from the result table; 2. Data insertion operation (1) Inserting the cell group: INSERT into base table name [(Column name of table)] values (tuple value) (2) Multiple group inserts: INSERT into base table name [(Column name of table)] values (tuple value), (tuple value),.., (tuple value) (3) Check Inser

In the fifth section of this chapter, we have noted that SQL Server 2000 supports after and instead of two types of triggers. Where the instead OF triggers is a newly added feature of SQL Server 2000, after triggers are equivalent to triggers in previous versions. When a instead type trigger for an action (INSERT, DELETE, UPDATE) is defined for a table or view and the appropriate action is performed, the tr

;SELECT ID, role_name from rolewhere> Choose> whenTest= "ID > 0">ID >= #{id} when> otherwise>and role_name like CONCAT ('% ', #{rolename, jdbctype=varchar}, '% ')otherwise> Choose> where>ORDER by ID ASCSelect>4.trim Element 4.1 Trim:if + whereSelectID= "Findbycondition"Resultmap= "Baseresultmap">SELECT ID, role_name from roleTrimprefix= "where"Prefixoverrides= "and | OR "> ifTest= "ID > 0">ID >= #{id}if> ifTest= "RoleName ! = null and RoleNam

disaster recovery plan. The key feature of log shipping is that it automatically backs up transaction logs throughout the day and automatically restore them to the standby server at a predetermined time interval. 17. Give three ways to get the record entries in the table? For: COUNTobject_id (table1 218. What does the switch QUOTED_IDENTIFIER open mean? What does it mean to turn it off?For:When SET quoted_identifier on, identifiers can be separated by double quotation marks (double quotatio

D from B)Or: Select A,b,c from a where a in (all-in-a-2, Description: Between usage, between limits the query data range includes the boundary value, not between does not includeSELECT * FROM table1 where time between time1 and time2Select A,b,c, from table1 where a is not between value 1 and value 23. Description: How to useSELECT * FROM table1 where a [not] in (' Value 1 ', ' Value 2 ', ' Value 4 ', ' Value 6 ')4, Description: Two related tables, delete the main table is already in the second

contains cursors. First delete stored procedures with the same name Jobs_cursor Then create the stored procedure Jobs_cursor 13.7.2 nested cursors To handle the result set flexibly, you can use a multi-tier cursor in a stored procedure. Example 13-10: This example creates a stored procedure that contains a two-tier cursor. The first-level cursor holds data records in the authors table that conform to the process parameters, reads a authors data from the first-tier cursor, and then

column in the cursor result set, and the data type of each variable also matches the data type of the data column in the cursor. @ @FETCH_STATUS global variable returns the state of the last FETCH command executed. When you read data from a cursor every time you use fetch, you should examine the variable to determine whether the last fetch operation was successful and decide how to proceed next. @ @FETCH_STATUS variable has three different return values, such as table 13-2. When reading dat

,start_date,end_date) obtains the difference between two time units of Date_name as the time unitExample: SELECT DATEDIFF (Minute,getdate (), DATEADD (Year,2,gatdate ()))-datepart (date) Gets a specific part of a dateExample: SELECT DATEPART (Year,getdate ())SELECT DATEPART (Month,getdate ())SELECT DATEPART (Hour,getdate ())-year (date) year of acquisition time-month (date) month of acquisition time-day (date) Day of acquisition time• Absolute ABS (value)"Advanced Application"-insert1. Precede t

type]) ...]is SELECT statement;Properties:%found,%notfound--is there a record%isopen--Determine if the cursor is open%rowcount--Number of rows affectedNumber of cursors limit:Connecting the SYS user in the SqlplusConn Sys/[email PROTECTED]:1521/ORCL as SysdbaTo view configuration parameters:Show parameter cursorTo modify the settings for the number of cursors:Alter system set OPEN_CURSORS=400 Scope=both;Scope Value: both,memory (changes the current instance only, does not change the parameter f

later found that it is not at all, and that the error Dup_val_on_index, his book is also wrong.Here is an anonymous blockDate.sqlDECLARE l_date date;BEGIN l_date: = SYSDATE-5; Dbms_output. Put_Line (l_date); end;/I still don't know how to call that process, although I tried it before, but I didn't remember it.I'm going to try to write a call on my own, I write a process for printing time, and then call it in an anonymous block.Get_date.sqlCREATE OR REPLACE PROCEDURE get_date (num_in in number)

...); Delete: Delete from table where condition; Update: The Update table set col_name=value_new where condition; Find: SELECT * FROM table_name where col_name like '%value% '; Sort: SELECT * FROM table_name order BY Col_name; Total: Select count as TotalCount from table_name; Sum: Select SUM (col_name) as sumvalue from table_name; Average: Select AVG (col_name) as avgvalue from table_name; Max, min: Max (col_name) min (col_name); 9. Use external connection

It's all written in MySQL.1. EnquiryThe following sentence can handle most simple SQL queries:SELECT field from table WHERE field = value ORDER by field 1 ASC, field 2 DESC LIMIT starting point, number of data barsTotal number of query data:SELECT COUNT (*) as C from table2. InsertINSERT into table (field 1, Field 2 ...) Values (value 1, value 2 ...)3. UpdateUPDATE SET field 1= value 1, field 2= value 2 WHERE field = value4. DeleteDELETE from table WH

you to change multiple tables in a single statement, so you can create multiple indexes at the same time. 2. CREATE index CREATE INDEX to add a normal or unique index to a table . Create Index index_name on table_name (column_list) create UNIQUE INDEX index_name on ta Ble_name (column_list) table_name, index_name, and column_list have the same meaning as in the ALTER table statement, and the index name is not selectable. In addition, the primary key index cannot be created with the Create inde

operation are as follows: (1 row (s) affected) Type added. Example 7-10: Define the ID Number data type. exec sp_addtype cardid, ' char ', ' NOT NULL ' The results of the operation are as follows: (1 row (s) affected) Type added. Example 7-11: Define the Address data type. EXEC sp_addtype address, ' varchar ', ' NOT NULL ' The results of the operation are as follows: (1 row (s) affected) Type added. 7.3.3 Delete user custom data types After you select a user-defined type in Enterp

A merge query uses the union operator to combine data from different queries to form a query result with comprehensive information. The UNION operation automatically excludes duplicate rows of data. It is important to note that each subquery participating in a merged query uses the same table structure, that is, the number of data in each subquery and the corresponding data type must be the same. In a SELECT statement that uses union, it is best to specify a sort order in a sort clause by u

Loop structure:1, Loop loop structureGrammar:LOOPThe statement to execute;Exit when END LOOP;Example: An integer that loops out 1-10DECLARE Number: = 1; BEGIN LOOP dbms_output.put_line (v_num); V_num:=+1; EXIT when Ten ; END LOOP; END;The drawback of loop structure: The structure is not clear enough, exit easy to write.2. While loop structureGrammar:While The statement to execute;END LOOP;Example: Displaying an integer of 1-10;DECLARE Number: = 1; B