MySQL Database (iii)

View

A view is a virtual table (not a real one), which is essentially "getting a dynamic dataset from an SQL statement and naming it", which allows the user to get a result set using only "name" and use it as a table.

Temporary table Search

SELECT
   *
FROM
   (
       SELECT
           nid,
           NAME
       FROM
           tb1
       WHERE
           nid > 2
   ) AS A
WHERE
   A. NAME > ‘zhang‘;

1. Create a View

--格式：CREATE VIEW 视图名称 AS  SQL语句
CREATE VIEW v1 AS 
SELET nid, 
   name
FROM
   A
WHERE
   nid > 4

2. Delete View

--格式：DROP VIEW 视图名称
DROP VIEW v1

3. Modify the View

-- 格式：ALTER VIEW 视图名称 AS SQL语句
ALTER VIEW v1 AS
SELET A.nid,
   B. NAME
FROM
   A
LEFT JOIN B ON A.id = B.nid
LEFT JOIN C ON A.id = C.nid
WHERE
   A.id > 2
AND C.nid < 5

4. Using views

When you use a view, you manipulate it as a table, and because the view is a virtual table, you cannot use it to create, update, and delete real tables, only for queries.

select * from v1

Trigger

Before and after an "Add/delete/change" operation on a table you can use triggers when you want to trigger a particular behavior, which is used to customize the behavior of the user before and after the "Add/delete/change" row of the table.

1. Create basic syntax

# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
   ...
END

# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
   ...
END

# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
   ...
END

# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
   ...
END

# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
   ...
END

# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
   ...
END

Insert Pre-Trigger

delimiter //
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN

IF NEW. NAME == ‘zhang‘ THEN
   INSERT INTO tb2 (NAME)
VALUES
   (‘aa‘)
END
END//
delimiter ;

Post-Insert Trigger

delimiter //
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
   IF NEW. num = 666 THEN
       INSERT INTO tb2 (NAME)
       VALUES
           (‘666‘),
           (‘666‘) ;
   ELSEIF NEW. num = 555 THEN
       INSERT INTO tb2 (NAME)
       VALUES
           (‘555‘),
           (‘555‘) ;
   END IF;
END//
delimiter ;

Special: New represents the data row that is about to be inserted, and the old represents the data row that is about to be deleted.

2. Delete Trigger

DROP TRIGGER tri_after_insert_tb1;

3. Using triggers

Triggers cannot be called directly by the user, but are caused passively by the "Add/delete/change" operation of the table.

insert into tb1(num) values(666)

Stored Procedures

A stored procedure is a collection of SQL statements in which the internal SQL statements are executed logically when the stored procedure is actively invoked.

1. Create a stored procedure

No parameter stored procedure

-- 创建存储过程

delimiter //
create procedure p1()
BEGIN
   select * from t1;
END//
delimiter ;

-- 执行存储过程

call p1()

For stored procedures, you can receive parameters with three types of parameters:

• In only for incoming parameters

• Out is used only for return values

• InOut can be passed in and can be used as a return value

Stored Procedures with parameters

-- 创建存储过程
delimiter \\
create procedure p1(
   in i1 int,
   in i2 int,
   inout i3 int,
   out r1 int
)
BEGIN
   DECLARE temp1 int;
   DECLARE temp2 int default 0;
   set temp1 = 1;
   set r1 = i1 + i2 + temp1 + temp2;
   set i3 = i3 + 100;

end\\
delimiter ;

-- 执行存储过程
set @t1 =4;
set @t2 = 0;
CALL p1 (1, 2 ,@t1, @t2);
SELECT @t1,@t2;

Result set

delimiter //
create procedure p1()
begin
   select * from v1;
end //
delimiter ;

Result set +out value

delimiter //
create procedure p2(
   in n1 int,
   inout n3 int,
   out n2 int,
)
begin
declare temp1 int ;
declare temp2 int default 0;
select * from v1;
set n2 = n1 + 100;
set n3 = n3 + n1 + 100;
end //
delimiter ;

Cursor

Delimiter//
CREATE PROCEDURE P3 ()
Begin
declare SSID int; --Custom Variable 1
DECLARE ssname varchar (50); --Custom Variable 2
DECLARE done INT DEFAULT FALSE;
DECLARE my_cursor cursor FOR select sid,sname from student;
DECLARE CONTINUE HANDLER for don't FOUND SET done = TRUE;
Open my_cursor;
Xxoo:loop
Fetch my_cursor into ssid,ssname;
If do then
Leave Xxoo;
END IF;
Insert into teacher (Tname) values (ssname);
End Loop Xxoo;
Close my_cursor;
End//
Delimter;

2. Delete stored Procedures

drop procedure proc_name;

3. Execute Stored Procedure

-- 无参数
call proc_name()

-- 有参数，全in
call proc_name(1,2)

-- 有参数，有in，out，inout
set @t1=0;
set @t2=3;
call proc_name(1,2,@t1,@t2)

Pymysql Executing stored procedures

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql

conn = pymysql.connect(host=‘127.0.0.1‘, port=3306, user=‘root‘, passwd=‘123‘, db=‘t1‘)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc(‘p1‘, args=(1, 22, 3, 4))
# 获取执行完存储的参数
cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")
result = cursor.fetchall()

conn.commit()
cursor.close()
conn.close()
print(result)

Function

A number of built-in functions are available in MySQL, such as:

Char_length (str)
The return value is the length of the string str, and the length of the unit is a character. A multibyte character counts as a single character.
For one containing five two-byte character sets, the LENGTH () return value is 10, and the return value of Char_length () is 5.

CONCAT (STR1,STR2,...)
string concatenation
If any one of the arguments is NULL, the return value is null.
Concat_ws (SEPARATOR,STR1,STR2,...)
string concatenation (custom connector)
CONCAT_WS () does not ignore any empty strings. (All NULL is ignored, however).

CONV (N,from_base,to_base)
Binary conversion
For example:
SELECT CONV (' A ', 16,2); Represents the conversion of a from 16 to a 2 binary string

FORMAT (X,D)
Writes the format of the number x as ' #,###,###.## ', preserves the D-bit after the decimal point in a rounded manner, and returns the result as a string. If D is 0, the result is returned without a decimal point, or with no fractional part.
For example:
SELECT FORMAT (12332.1,4); The result: ' 12,332.1000 '
INSERT (STR,POS,LEN,NEWSTR)
Inserts a string at the specified location in str
POS: To replace the location in fact
Len: The length of the replacement
NEWSTR: New String
In particular:
Returns the original string if the POS exceeds the length of the original string
If Len exceeds the length of the original string, it is completely replaced by the new string
INSTR (STR,SUBSTR)
Returns the first occurrence of a substring of string str.

Left (Str,len)
Returns the substring character of the string Str from the beginning of the Len position.

LOWER (str)
Change lowercase

UPPER (str)
Change capitalization

LTRIM (str)
Returns the string str, whose boot space character is deleted.
RTRIM (str)
Returns the string str, trailing whitespace character is deleted.
SUBSTRING (Str,pos,len)
Get string subsequence

LOCATE (Substr,str,pos)
Get sub-sequence index position

REPEAT (Str,count)
Returns a string consisting of a repeating string str, with the number of string str equal to count.
If Count <= 0, an empty string is returned.
If STR or count is NULL, NULL is returned.
REPLACE (STR,FROM_STR,TO_STR)
Returns the string str and all string from_str that are substituted by the string to_str.
REVERSE (str)
Returns the string str, in reverse order and character order.
Right (Str,len)
Starting from the string str, returns a subsequence of Len characters starting from behind

SPACE (N)
Returns a string consisting of n spaces.

SUBSTRING (Str,pos), SUBSTRING (str from POS) SUBSTRING (Str,pos,len), SUBSTRING (str from POS for Len)
The format without the Len parameter returns a substring from the string str, starting at position pos. The format with the Len parameter returns a substring of the same length as the Len character from the string str, starting at position pos. Use the from format as standard SQL syntax. You may also use a negative value for the POS. If so, the position of the substring starts at the POS character at the end of the string, not at the beginning of the string. You can use a negative value for the POS in the following format function.

mysql> SELECT SUBSTRING (' quadratically ', 5);
' Ratically '

mysql> SELECT SUBSTRING (' Foobarbar ' from 4);
' Barbar '

mysql> SELECT SUBSTRING (' quadratically ', 5,6);
' Ratica '

mysql> SELECT SUBSTRING (' Sakila ',-3);
' Ila '

mysql> SELECT SUBSTRING (' Sakila ',-5, 3);
' Aki '

mysql> SELECT SUBSTRING (' Sakila ' FROM-4 for 2);
' Ki '

TRIM ([{BOTH | Leading | TRAILING} [REMSTR] from] str) TRIM (remstr from] str)
Returns the string str, where all remstr prefixes and/or suffixes have been deleted. If none of the classifier both, leadin, or trailing is given, it is assumed to be both. REMSTR is optional and can be removed without specifying a space.

mysql> SELECT TRIM (' Bar ');
' Bar '

Mysql> SELECT TRIM (leading ' x ' from ' xxxbarxxx ');
' Barxxx '

Mysql> SELECT TRIM (BOTH ' x ' from ' xxxbarxxx ');
' Bar '

Mysql> SELECT TRIM (TRAILING ' xyz ' from ' barxxyz ');
' Barx '

1. Custom Functions

delimiter \\
create function f1(
   i1 int,
   i2 int)
returns int
BEGIN
   declare num int;
   set num = i1 + i2;
   return(num);
END \\
delimiter ;

2. Delete function

drop function func_name;

3. Execution function

# 获取返回值
declare @i VARCHAR(32);
select UPPER(‘alex‘) into @i;
SELECT @i;

# 在查询中使用
select f1(11,nid) ,name from tb2;

Identify the QR code in the image and collect the full Python video

MySQL Database (iii)