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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.