MySQL---Database get started on the Big God series (iv)-Stored procedures

This blog explains:
autogrow columns, field value uniqueness constraints, stored procedures, case-sensitive queries!

autogrow column, field value uniqueness constraint

create table aa(    id int auto_increment primary key,    sname varchar(32) unique);insert into aa values(5,‘abc‘);

Create an auto-growing id attribute (starting from 0 without starting to grow)
(After the ID if there is a value, if you add data without setting Id,mysql will use the maximum ID plus 1 as the latest ID)
Note: Auto_increment is supported in MySQL and other database settings are not the same as keywords in the auto-grow column.
The unique constraint on sname is that it cannot have the same sname (which can have a value of NULL).

Stored procedures:

In fact, the stored procedure in SQL is much like a defined function in Java, calling a function.

First look at the definition:

create procedure 过程名(参数...)begin    SQL语句...end

Call:

call 过程名(实参)

There is one place to be aware of:
Before the definition, you need to end the default statement '; ' to other, such as ' && ', so that the semicolon defined in the stored procedure is not considered to be the end of the statement (otherwise it will be submitted directly).
After the definition is finished, the '; ' Reverts to the default terminator.

Instance one: a stored procedure with no parameters

delimiter &&create procedure p1()begin    insert into stud values(‘P100‘,‘小李‘,43);    select * from stud;end&&delimiter ;call p1;/*调用p1()中的SQL语句,如果没有call之前，p1()中的SQL语句是没有被执行的*/

Example two: a stored procedure with an argument

Delimiter && Create procedure p2 (in ID varchar(+), in sname varchar (+), in age int)begininsert  into stud  Values(id,sname,age);         Select *  from stud; end &&delimiter;Call P2 (' P1007 ',' small white ',+);  Call   p2 (' P1008 ',' Xiao Li ', ');

Example three: A stored procedure with a return value

Delimiter && Create procedure p3 (in ID varchar(+), in sname varchar (+), in age int , out num int)begininsert  into stud values(id,sname,age);         Select *  from stud;     Select Count(*)  into num  from stud; end&&delimiter;Call P3 (' P012 ',' Little Five ',@num);  / * Call and receive results with num * / Select @num; / * Display user variable num*/

System variable name: @@ 变量 Name
User variable name: @ variable Name

Case-sensitive queries:

Because the query for MySQL is not case-sensitive by default:
If there are times when we need to be case sensitive, we need the binary keyword.
This can be used to find the sname with ' J '/' J ' in the Stud table:
Do not write binary to query first:

select * from stud where sname like ‘J%‘;

Write binary to query:

select * from stud where binary sname like ‘J%‘;

MySQL---Database get started on the Big God series (iv)-Stored procedures