2. Advanced operations
2.1. Three paradigms of database design
2.1.1. Paradigm principle:
2.1.2. Paradigm 2 principle:
2.1.3. Connection query
2.2. Table relationship
2.2.1, one-to-many
2.3. Introduction to sample database
2.3.1, Account information table
2.3.2, Bank card
2.3.3. Transaction information table
Records of deposits and withdrawals of money
2.3.4. Transfer information table
2.3.5. Status information change table
2.4, variables
2.4.1 Information printing
--Information printing print 'hello sql' --display in message select 'hello sql' --display in the results
2.4.2, variables
local variable declare
set
global variables
Query Zhang Fei card information
Query using variables
2.4.3. go statement
In case the database creation is not completed, execute the following statements and add go in the middle.
--Information printing print 'hello sql' --display in message select 'hello sql' --display in the results --Variables: (1) local variables (2) global variables --(1) Local variables: starting with @, declare first, then assign value declare @str varchar(20) --Declare variables set @str='i like a' print @str --The difference between set and select when assigning values --set: Assign the value specified by the variable --select: Generally used to assign the data queried in the table to variables. If there are multiple query results, the last one is assigned. --exp:select @a=field name from table name --A certain field in the last row of the current table is given to @a --(2) Global variables: starting with @@, defined and maintained by the system --go statement --(1) Wait for the code before the go statement to be executed before it can be executed and become the code --(2) Sign of the end of batch processing declare @num int --@num scope global set @num =100 print @num go declare @num1 int --num1 scope is between two go, or before go set @num1=300 go set @num1=200
2.5, Operator
2.5.1, Basics
declare @c int =10 declare @k int =2 declare @zc int =5
Convert data conversion
2.5.2, in statement
2.5.3, if syntax structure
Grammatical structures:
if
begin
end
else
begin
end
statement
2.5.4, all statement, all
2.5.5any statement exists in all
Priority level
2.6, Process Control
2.6.1. Select branch structure
Query statement: case end judgment statement
when then else statement
2.6.2, Loop structure
while loop
2.7, Subquery
Question 1: Subquery
top1
Equal sign = changed to in
Year, month and day SQL language query
Group by card number, then count, query card number and number of transactions
temp subtable name, understood in combination with 1.7 and group query
temp is a temporary table name
case statement
2.8, paging
2.8.1, top mode paging
2.8.2. Use row_number for paging
Add another line
2.9, Transactions
2.10, Index
2.10.1, Clustering Index
Database-View-System View-sys.indexs Query the newly created index
with statement
2.10.2, non-clustering index
2.11, View
2.12, Cursor
Extract cursor information and store it in variables
Delete the row pointed to by the cursor
2.13, function
Create function call
Returns i if there are parameters
Return table data Option 1: In addition to SQL query statements, there are other logical statements, Option 2 is not possible
Query data and insert into @result
My understanding is: GO is equivalent to the end tag of a .sql file
Option 2: Simplify
another kind of problem
2.14 trigger
trigger trigger
The generated temporary table inserted
The generated temporary table deleted, which table is created to trigger the event
Chaungjianyige trigger, when deleting a department, determine whether the department has employees. If there are employees, it will not be deleted. If not, it will be deleted.
The new number is equal to the inserted number, and the old number is equal to the deleted number
2.15, stored procedure
Encapsulated into a stored procedure: can be called externally
procedure stored procedure
Deposit money: There are input parameters
There are input parameters and return value:
Concatenate strings ’00:00:00′ ’23:59:59′
transfer
Variables and outputs are used to determine whether they are input or output and whether they are assigned a value during life. If they are assigned a value, they are input and output.
BandCard below should be BankCard
transfer
Quote: 15_05-Stored Procedure 2_bilibili_bilibili
Take a closer look at stored procedures
The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge. MySQL entry skill treeSQL advanced skillsCTE and recursive query 76307 people are learning the system