MySQL > select * from 'MySQL'; - Moment For Technology

Views A view is a virtual table (it does not exist). The essence of a view is to obtain a dynamic data set based on an SQL statement and name it. Users only need to use [name] to obtain the result set 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';
Copy the code

1. Create a view

-- Format: CREATE VIEW VIEW name AS SQL statement CREATE VIEW v1 AS SELET nid, name FROM A WHERE nID > 4Copy the code

2. Delete the view

— Format: DROP VIEW VIEW name DROP VIEW v1

3. Modify the view

- the format: ALTER VIEW v1 AS SELET a.id, 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 < 5Copy the code

4. Use views

You can use a view as a table. Because a view is a virtual table, it cannot be used to create, update, or delete real tables, but can only be used for query.

select * from v1

Trigger Before and after adding, deleting, or modifying a table To trigger a specific behavior, you can use a trigger to customize the behavior before and after adding, deleting, or modifying rows in a table.

1. Create the basic syntax

# before
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
   ...
END

# after insert
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
   ...
END

Before deleting #
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
   ...
END

After deleting #
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
   ...
END

Update # before
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
   ...
END

# updated
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
   ...
END
Copy the code

Pre-insertion flip-flop

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 ;
Copy the code

Post insert flip-flop

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 ;Copy the code

In particular: NEW denotes the row to be inserted and OLD denotes the row to be deleted.

Delete trigger

DROP TRIGGER tri_after_insert_tb1;

3. Use triggers

Triggers cannot be invoked directly by the user, but are passively triggered by an add/delete/alter operation on a 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 invoked.

Create a stored procedure

A parameterless stored procedure

Create a stored procedure

delimiter // create procedure p1() BEGIN select * from t1; END// delimiter ; -- Execute stored procedure Call p1()Copy the code

For stored procedures, you can receive parameters, which fall into three categories:

inOnly for passing in parameters. Out only for returning values. Inout can be passed in as well as returnedCopy the code

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 ;
Copy the code

-- Execute the stored procedureset @t1 =4;
set @t2 = 0;
CALL p1 (1, 2 ,@t1, @t2);
SELECT @t1,@t2;
Copy the code

The result set

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

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 ;
Copy the code

The cursor

delimiter //
create procedure p3()
begin 
   declaressid int; -- Customize variable 1declaressname varchar(50); -- Customize variable 2 DECLAREdone INT DEFAULT FALSE;
   DECLARE my_cursor CURSOR FOR select sid,sname from student;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;                      
   open my_cursor;
       xxoo: LOOP
          fetch my_cursor into ssid,ssname;
          if done then 
               leave xxoo;
          END IF;
          insert into teacher(tname) values(ssname);
       end loop xxoo;
  close my_cursor;
end  //
delimter ;
Copy the code

2. Delete the stored procedure

drop procedure proc_name; 3. Execute the stored procedure

— No 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 executes 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)
Execute the stored procedure
cursor.callproc('p1', args=(1, 22, 3, 4))
Get the parameters of the store after execution
cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")
result = cursor.fetchall()

conn.commit()
cursor.close()
conn.close()
print(result)
Copy the code

MySQL provides many built-in functions, such as:

CHAR_LENGTH(STR) Returns the length of string STR in characters. A multi-byte character counts as a single character. For a set of five two-byte characters, LENGTH() returns 10 and CHAR_LENGTH() returns 5. CONCAT(str1,str2,...) String concatenation returns NULL if any argument is NULL. CONCAT_WS(separator,str1,str2,...) String concatation (custom concatation) CONCAT_WS() does not ignore any empty strings. (All nulls are ignored, however). CONV(N,from_base,to_base)'a', 16, 2); Converts a from hexadecimal to binary. The string FORMAT(X,D) converts the number X to'# # # #, # # #. # #'To keep D decimal places rounded and return the result as a string. If D is 0, the result is returned with no decimal point, or with no decimal part. For example, SELECT FORMAT(12332.1,4); The result is:'12332100'INSERT(STR,pos,len,newstr) INSERT(STR,pos,len,newstr) at the specified position of STR pos: the position to be replaced INSTR(STR,substr) returns the string STR at the first occurrence of the substring string. LEFT(STR,len) returns a subsequence character of the string STR starting at len position. LOWER(STR) reduced Write UPPER(STR) changed to uppercase LTRIM(STR) Returns the string STR with the boot space character removed. RTRIM(STR) returns the string STR with the trailing space character removed. SUBSTRING(STR,pos,len) retrieves the character string subsequence LOCATE(substr, STR,pos) retrieves the subsequence index location REPEAT(STR,count) returns a string consisting of repeated string STR, The number of strings STR is 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 any string from_str replaced by the string to_str. REVERSE(STR) returns the string STR, in REVERSE of the character order. RIGHT(STR,len) starts with the string STR and returns a subsequence of len characters starting from the back. SPACE(N) returns a string of N Spaces. SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(STR FROM pos FOR len) Format without len returns a SUBSTRING FROM string STR, starting at position pos. The format with len argument returns a substring of the same length as len character from string STR, starting at position pos. The format for using FROM is standard SQL syntax. It is also possible to use a negative value for pos. In this case, the position of the substring begins with the pos character at the end of the string, not at the beginning of the string. You can use a negative value for pos in a function of the following format. 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 a string STR, including all remstr prefix and/or suffix has been deleted. If neither of the classifiers BOTH, LEADIN, or TRAILING is given, BOTH is assumed. Remstr is optional. If not specified, Spaces can be deleted. 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'
Copy the code

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 ;
Copy the code

2. Delete functions

drop function func_name;

3. Execute the function

Get the return value
declare @i VARCHAR(32);
select UPPER('alex') into @i;
SELECT @i;

Use it in the query
select f1(11,nid) ,name from tb2;
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 > select * from ‘MySQL’;

MySQL > select * from ‘MySQL’;

Related Posts

TCP Networks (super detailed)

How do I upgrade my website to HTTPS for free

Divide-and-conquer algorithm – merge, fast sorting, fast selection