MySQL Basics - Introduction to advanced features

Five, view,

1. Brief introduction

A view is a virtual table that is used in the same way as a regular table. A new feature in MySQL5.1 is dynamically generated data from tables.

Dance class and regular class: there is no dance class, only when the leader comes, the temporary dance class is dissolved automatically after the leader leaves, ordinary class is not like this.

Example (before using views) :

Select * from student where name = 'zhang'SELECT stuname,majorname
FROM stuname s
INNER JOIN major m
ON s.`majorid` = m.`id`
WHERE s.`stuname` LIKE 'a %';
Copy the code

Example (with views) :

We need to query it first and wrap it into view v1CREATE VIEW v1
AS
SELECT stuname,majorname
FROM stuname s
INNER JOIN major m
ON s.`majorid` = m.`id`
WHERE s.`stuname` LIKE 'a %'; Select * from v1SELECT * FROM v1 WHERE stuname LIKE 'a %';
Copy the code

2. View creation

# grammarCREATE VIEWView nameASThe statement to query;Copy the code

Benefits of views:

Reuse SQL statements
Simplify complex SQL operations without knowing its query details
Protect data and improve security

3. View modification

# Method 1:CREATE OR REPLACE VIEWView nameASQuery statement;Copy the code

# Method 2:ALTER VIEWView nameASQuery statement;Copy the code

4. Delete views

DROP VIEWView name1The view name2;
Copy the code

5. View

DESCView name; # This is ok, but not recommendedSHOW CREATE VIEWView name;Copy the code

6. Update the view

Use to change data in a view. It’s the same as updating a table.

INSERT INTOView name (...).VALUES(...). ; # Everything else, tooCopy the code

But there are special cases:

SQL statements containing the following keywords: grouping function, DISTINCT, Group by, HAVING, Union, or Union all;
Constant view;
Select contains subqueries;
The join.
From a view that cannot be updated;
The subquery of the WHERE clause references the table in the FROM clause;

The difference between views and tables

One is create View, one is Create Table
Does it actually take up physical space? The view takes up a small amount of space, and the table takes up physical space
Views generally cannot be added, deleted, or changed (with restrictions), while tables can be added, deleted, changed, or checked

Six, variables,

1. Classification of variables

System variables:
- The global variable
- Session variable
Custom variables:
- The user variables
- A local variable

2. System variables

It is provided by the system, not user-defined, and belongs to the server level.

The default is SESSION, same as the followingSHOW GLOBAL|The SESSION VARIABLES 】; View some system variablesSHOW GLOBAL|The SESSION VARIABLES 】LIKEQuery conditions; View a system variableSELECT @@GLOBAL|【SESSION】 The name of a system variable; Assign a value to a system variableSET GLOBAL|[SESSION] The name of a system variable=Value; Way # 2SET @@GLOBAL|[SESSION]. Name of the system variable=Value;Copy the code

The server assigns an initial value to the global variable each time it is started, which is valid for all sessions. However, it cannot be restarted across sessions. If you want to restart the global variable, you need to modify the configuration file.

Session variables are scoped for the current session (connection), not for another connection.

3. Customize variables

Variables are self-defined.

3.1 User Variables

Scope: A Session that is valid for the current Session is invalid for another connection, as is the scope of the Session.

Use anywhere, inside or outside of begin end.

Declare and initialize=Or:=The assignmentSET@user variable name=Value; # The Other waySET@user variable name:=Value; # The Other waySELECT@user variable name:=Value; # Change the value of a user variable # Change the value of a user variableSET@user variable name=Value; # The Other waySET@user variable name:=Value; # The Other waySELECT@user variable name:=Value; # Method 2:SELECT INTOTo assign a valueSELECTfieldINTO@ the variable nameFROMTable; # useSELECT@user variable name;Copy the code

3.2 Local variables

Scope: only valid locally, in begin end.

# the statementDECLAREVariable name type;DECLAREVariable name typeDEFAULTValue; # assignment # method 1SETLocal variable name=Value; # The Other waySETLocal variable name:=Value; # The Other waySELECT@ Local variable name:=Value; # Method 2:SELECT INTOTo assign a valueSELECTfieldINTOThe variable nameFROMTable; # useSELECTLocal variable name;Copy the code

Stored procedures and functions

1. Brief introduction

Stored procedures and functions are similar to Java methods.

Stored procedure and function definitions: A collection of SQL statements that have been compiled and stored in a database.

Benefits:

Simplify a lot of work for application developers
Reduce the transfer of data between the database and the application server
Improve the efficiency of data processing

2. Stored procedures

2.1 Creating a Stored Procedure

CREATE PROCEDUREStored procedure name (parameter list)BEGINStored procedure body (a groupSQLStatements);END;
Copy the code

Note:

The parameter list consists of three parts: parameter mode, parameter name, and parameter type

# forIN stuname VARCHAR(20);
Copy the code

Parameter mode:
- IN: This parameter can be used as input, which requires the caller to pass IN a value
- OUT: This parameter can be output, that is, this parameter can be returned
- INOUT: This parameter can be either input or output, requiring the caller to pass in a value or return a value
If the stored procedure body contains only one sentence, the BEGIN END can be omitted. Each SQL statement in the stored procedure body must END with a semicolon (;)delimiterTo reset

# Syntax DELIMITER End tag # case DELIMITER $Copy the code

2.2 Calling a Stored Procedure

# grammarCALLStored procedure name (argument list);Copy the code

2.3 case

A little difficult, case record!!

Stored procedures for common type parameters

Insert five records into the admin table. Write the stored procedure DELIMITER $CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO admin(username,`password`) VALUES 
		('zhangsan1'.'001'),
		('zhangsan2'.'002'),
		('zhangsan3'.'003'),
		('zhangsan4'.'004'),
		('zhangsan5'.'005');
END$# Uses stored proceduresCALL myp1()$
Copy the code

Stored procedure with in-mode parameters

Write the stored procedure DELIMITER $CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN 
	SELECT bo.*
	FROM boys bo
	RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
END$# Uses stored proceduresCALL myp2('Ada') $Copy the code

# Whether the user logs in successfully DELIMITER $CREATE PROCEDURE myp3(IN username VARCHAR(20),IN `password` VARCHAR(20))
BEGIN
	DECLARE result INT(20) DEFAULT ' '; Declare and initializeSELECT count(*) INTO result# assignmentFROM admin
	WHERE admin.username = username
	AND admin.password = `password`;
	
	SELECT IF(result>0.'success'.'failure)'; # Determine successEND$# callCALL myp3('Joe'.'0001') $Copy the code

Stored procedure with out mode parameters

Write the stored procedure DELIMITER $CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN 
	SELECT bo.boyName INTO boyName
	FROM boys bo
	RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
END$# Uses stored proceduresSET @bName$
CALL myp5('Ada'.@bName) $SELECT @bName$
Copy the code

Stored procedure with inout mode parameters

# pass a and B, and eventually both a and B double and return # write the stored procedureCREATE PROCEDURE myp8(INOUT a INT.INOUT b INT)
BEGIN
	SET a = a*2;
	SET b = b*2;
END$# Uses stored proceduresSET @m=10$
SET @n=20$
CALL myp8(@m.@n$# Query resultSELECT @m.@n $
Copy the code

2.4 Deleting a Stored Procedure

You cannot modify the contents of a stored procedure. If you want to modify it, delete it and create a new one.

# grammarDROP PROCEDUREStored procedure name; You can only delete one at a timeCopy the code

2.5 Viewing Stored Procedures

# grammarSHOW CREATE PROCEDUREStored procedure name;Copy the code

3, functions,

The difference between stored procedures and functions:

Functions can have only one return, and stored procedures can have any number of returns, zero or multiple
Add, delete, and modify are better for stored procedures, and querying a value is better for functions

3.1 Creating a Function

# create syntaxCREATE FUNCTIONFunction name (function name)RETURNSThe return typeBEGINThe body of the functionEND
Copy the code

The parameter list consists of two parts: parameter name and parameter type. Functions are closer to methods in Java.

Function body: There must be a return statement, and an error is reported if there is no return statement, or if the return statement is not placed at the end of the function body, but it does not mean anything.

If the stored procedure body contains only one sentence, you can omit the BEGIN END and use delimiter at the END of the stored procedure.

3.2 Call Syntax

SELECTFunction name (argument list)Copy the code

3.3 case

No parameter returns

Return the number of employees in the companyCREATE FUNCTION myf1() RETURNS INT
BEGIN
	DECLARE c INT DEFAULT 0;
	SELECT COUNT(*) INTO c
	FROM employees;
	RETURN c;
END$# calls the functionSELECT myf1()$
Copy the code

There are parameters and there are returns

Return the salary based on the employee's nameCREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @sal=0; Define a user variableSELECT salary INTO @sal# assignmentFROM employees
	WHERE last_name = empName;
	
	RETURN @sal;
END$# calls the functionSELECT myf2('k_ing');
Copy the code

3.3 Viewing Functions

# grammarSHOW CREATE FUNCTIONThe name of the function;Copy the code

3.4 Delete Function

# grammarDROP FUNCTIONThe name of the function;Copy the code

Viii. Process control structure

1. Structural classification

Sequential structure: programs run from top to bottom;

Branch structure: a program can choose one of multiple paths to execute;

Loop structure: program to meet certain conditions on the basis of repeated execution of a section of code;

2. Branch structure

2.1 the if function

Simple double branching can be implemented. It can be applied anywhere.

# syntax IF(expression1The expression2The expression3) # if the expression1True, returns the expression2Otherwise, the expression is returned3Value of (trinary operator)Copy the code

2.2 the case structure

Case 1: Similar to the switch statement, generally used for equivalence judgment

# grammarCASEvariable|expression|fieldWHENThe value to determineTHENThe value returned1[or statements1; ]WHENThe value to determineTHENThe value returned2[or statements2; ] .ELSEThe value n to return [or statement n;]END CASE;
Copy the code

Case 2: Similar to multiple If, it is generally used to realize interval judgment and see which interval the bottom is in

# grammarCASE 
WHENConditions to judge1 THENThe value returned1[or statements1; ]WHENConditions to judge2 THENThe value returned2[or statements2; ] .ELSEThe value n to return [or statement n;]END CASE;
Copy the code

Can be used as expressions nested within other statements, and can be placed anywhere within or outside of begin end. It can also be used as a separate statement and can only be placed in a begin end.

2.3 if construct

Implement multiple branches. This parameter can be used only in begin End.

# syntax IF condition1 THENstatements1; ELSEIF conditions2 THENstatements2; . 【ELSEStatements n; 】END IF;
Copy the code

3. Circular structure

3.1 Introduction

Categories: while, loop, repeat.

Iterate control: Iterate is similar to the Continue in Java, which ends the current loop for the next one. Leave is equivalent to break in Java

3.2 the while loop

# syntax 【 tag: 】WHILE condition DO loop body;ENDWHILE label;Copy the code

case

Insert multiple records into admin tableCREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i < = insertCount DO
	INSERT INTO admin(username,`password`) VALUES(CONCAT('Jame'+i),'666');
	SET i = i + 1;
	END WHILE a;
END $

CALL pro_while1(100) $Copy the code

The statement containing leave

Insert multiple records into admin table20End of the articleCREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i < = insertCount DO
        INSERT INTO admin(username,`password`) VALUES(CONCAT('Jame'+i),'666');
        IF i> =20 THEN LEAVE a;
        END IF;
        SET i = i + 1;
	END WHILE a;
END $

CALL pro_while1(100) $Copy the code

3.3 the loop cycle

# syntax 【 tag: 】LOOP body;ENDLOOP 【 label 】;Copy the code

Can be used to simulate a simple infinite loop.

3.4 repeat loop

【 tag: 】REPEAT; UNTIL terminates the loop conditionENDREPEAT【 label 】;Copy the code

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

MySQL Basics – Introduction to advanced features

Five, view,

1. Brief introduction

2. View creation

3. View modification

4. Delete views

5. View

6. Update the view

Six, variables,

1. Classification of variables

2. System variables

3. Customize variables

3.1 User Variables

3.2 Local variables

Stored procedures and functions

1. Brief introduction

2. Stored procedures

2.1 Creating a Stored Procedure

2.2 Calling a Stored Procedure

2.3 case

2.4 Deleting a Stored Procedure

2.5 Viewing Stored Procedures

3, functions,

3.1 Creating a Function

3.2 Call Syntax

3.3 case

3.3 Viewing Functions

3.4 Delete Function

Viii. Process control structure

1. Structural classification

2. Branch structure

2.1 the if function

2.2 the case structure

2.3 if construct

3. Circular structure

3.1 Introduction

3.2 the while loop

3.3 the loop cycle

3.4 repeat loop

MySQL Basics – Introduction to advanced features

Five, view,

1. Brief introduction

2. View creation

3. View modification

4. Delete views

5. View

6. Update the view

Six, variables,

1. Classification of variables

2. System variables

3. Customize variables

3.1 User Variables

3.2 Local variables

Stored procedures and functions

1. Brief introduction

2. Stored procedures

2.1 Creating a Stored Procedure

2.2 Calling a Stored Procedure

2.3 case

2.4 Deleting a Stored Procedure

2.5 Viewing Stored Procedures

3, functions,

3.1 Creating a Function

3.2 Call Syntax

3.3 case

3.3 Viewing Functions

3.4 Delete Function

Viii. Process control structure

1. Structural classification

2. Branch structure

2.1 the if function

2.2 the case structure

2.3 if construct

3. Circular structure

3.1 Introduction

3.2 the while loop

3.3 the loop cycle

3.4 repeat loop

Related Posts

Database principle and application (4) — SQL statement (2) SQL basic query and common operators

How does Redis persist and recover?

Storage features -Smart Migration: One of the super many uses, make O (*￣▽￣*)ブ

Storage features -Smart Migration: One of the super many uses, make O (￣▽￣)ブ