Shulou(Shulou.com)06/01 Report--

This article mainly introduces the mysql grammar structure handout, hoping to supplement and update some knowledge for you. If you have any other questions you need to know, you can continue to follow my updated article in the industry information.

One:

Create the database:

Create {database | schema} [if not exists] Library name [default] character set [=] charset_name

If if not exists exists, it is ignored.

Character set default character set encoding.

Create a database: create database

Modify the database: alter database

Delete database: drop database

1. Common data types of mysql

Data type refers to the data characteristics of columns, stored procedure parameters, expressions and local variables, which determines the storage format of the data and represents different types of information.

Integer:

Data type byte

Tinyint 1

Smallint 2

Mediumint 3

Int 4

Bigint 8

Floating point type:

Float [(MMagar D)] M is the total number of digits, and D is the number of digits after the decimal point.

If M and D are omitted, the value is preserved according to the limits allowed by the hardware. Single precision floating point number.

Double [(MMAE D)]

Date and time type:

Date 3-byte YYY-MMM-DD date value

Time 3-byte HH:MM:SS time value

Year 1 byte YYYY year value

Datetime 8-byte YYYY-MM-DD mixed date and time valu

Timestamp 8-byte YYYYMMDDHHMMSS mixes date and time values, timestamps.

Character type:

Char 0-255byte fixed length string

Varchar 0-65535 byte variable length string

Tinytext short text string

Text long text data

Mediumtext medium length text data

Longtext extremely large text data

Enum ('value1','value2') 1 or 2 bytes

Depends on the number of enumerated values (up to 65535).

Set ('value1','value2'), 1, 2, 3, 4 or 8 bytes, depending on the number of set members u (up to 64 members)

Open database: use library name

Verify the database opened by the user:

Select database ()

Create a datasheet:

Create table [if not exists] table name (column name data type,...)

View a list of datasheets

Show tables [from Library name] [like 'pattern' | where expr]

View the structure of the data table:

Show columns from table name; or desc table name

Insert record:

Insert [into] Table name [(field name,...)] Values (value,...)

Insert table name values (value,...)

Find a record

Select field,. From table name

Mysql null and non-null:

Null value and non-null value

NULL: indicates that the field can be empty

NOT NULL: indicates that the field is not allowed to be empty.

Mysql automatic numbering:

Automatic numbering and must be used in combination with the primary key

By default, the starting value is 1, and each increment is 1. Auto_increment

Mysql primary key constraints:

Primary key or key

Primary key constraint characteristics:

There can be only one primary key per data table

The primary key ensures the uniqueness of the record.

The primary key is automatically not null

Mysql unique constraint:

Unique key

Unique constraint characteristics:

Unique constraints can guarantee the uniqueness of records.

Fields with unique constraints can be null (null)

There can be multiple unique constraints per data table.

Mysql default constraint:

Default

Default value

When you insert a record, if the field is not explicitly assigned a value, the default value is automatically assigned.

Summary:

Numeric types: character type, integer type, floating point type, date-time type

Datasheet operation: insert records and find records.

Record operations: create data tables, the use of constraints.

Two:

Datasheet operation:

How to create a datasheet:

Primary key constraint (primary key)

Unique constraint (unique key)

Default constraint (default)

Non-null constraint (not null)

Record insertion

Record lookup

Constraints:

Constraints ensure the integrity and consistency of data

Constraints are divided into table-level constraints and column-level constraints

Constraint types include:

Primary key constraint (primary key)

Unique constraint (unique key)

Default constraint (default)

Non-null constraint (not null)

Foreign key constraint (foreign key)

Foreign key constraint (foreign key)

Ensure data consistency and integrity

Achieve an one-to-one or one-to-many relationship

Requirements for foreign key constraints:

1. Parent and child tables must use the same storage engine, and temporary tables are prohibited.

2. The storage engine of data table can only be INNODB.

3. Foreign key columns and reference columns must have similar data types. Where the length of the number or whether there are symbolic bits must be the same, while the length of the character can be different.

4. Foreign key columns and reference columns must be indexed. If no index exists for the foreign key column, mysql automatically creates the index.

Edit the default storage engine for the data table:

Mysql profile

Default-storage-engine=INNODB

View the engine or table detail structure used by the table name: show create table table name

View the index of the table: show indexes from table name\ G

View all databases under mysql: show databases

View all the tables under the current library: show tables

View basic information: desc view name

View basic information: show table status like 'View name'

View details: show create view view name

Query information for all triggers: show triggers

Query the details of the specified trigger:

Select * from information_schema.triggers where trigger_name=' trigger name'

View the status of stored procedures and functions:

Show {query stored procedure | query stored function} status [like 'pattern']

Note: like 'pattern': matches the stored procedure or function name.

View the status of stored procedures and functions:

Show create {procedure | function} sp_name

Procedure: represents a query stored procedure.

Function: represents the query storage function.

Sp_name: represents the name of a stored procedure or function.

Select * from information_schema.Routines WHERE routine_name='sp_name'

Note: routine_name: stored procedure and function name.

Sp_name stored procedure or function name.

Reference actions for mysql foreign key constraints:

1. Cascade: deletes or updates from the parent table and automatically deletes or updates matching rows in the child table.

2. Set null: delete or update rows from the parent table and set the foreign key column in the child table as null. If you use this option, you must ensure that the subtable column does not specify not null.

3. Resttict: refuses to delete or update the parent table.

4. No action: the keyword of standard SQL, which is the same as restrict in mysql.

Mysql table-level constraints and column-level constraints

Column-level constraint: a constraint established on a data column

Table-level constraint: a constraint established on multiple data columns.

Column-level constraints can be declared either when the column is defined or after the column is defined.

Table-level constraints can only be declared after a column definition.

Mysql modify data Table-add and delete columns

Modify the data table

Add a single column:

Alter table table name add [column] column name column definition [first | after column name]

Column name (col_name)

Column definition (column_definition)

Add multiple columns:

Alter table table name add [column] (column name column definition,...)

Delete the column:

Alter table table name drop [column] column name

Add a primary key constraint:

Alter table table name add [constraint [symbol]] primary key [index_type] (index_col_name)

For example:

Mysql > create table user2 (

-> username varchar (10) not null

-> pid smallint unsigned

->)

Query OK, 0 rows affected (0.02 sec)

Mysql > desc user2

Mysql > alter table user2 add id smallint unsigned

Mysql > alter table user2 add constraint pk_user2_id primary key (id)

Add a unique constraint:

Alter table table name: add [constraint [symbol]] unique [index | key] [index_name] [index_type] (index_col_name,....)

Example:

Mysql > alter table user2 add unique (username)

Mysql > desc user2

Mysql > alter table user2 add foreign key (pid) references provinces (id)

Add / remove default constraints

Alter table table name alter [column] col_name {set default literal | drop default}

Example:

Mysql > alter table user2 add age tinyint unsigned not null

Mysql > alter table user2 alter age set default 15

Mysql modifies the data table-delete constraints

Delete primary key constraint

Alter table table name drop primary key

Example:

Mysql > alter table user2 drop primary key

Mysql > desc user2

Delete a unique constraint:

Alter table table name drop {index | key} index_name

Example:

Mysql > alter table user2 drop index username

Delete a foreign key constraint:

Alter table table name drop foreign key fk_symbol (foreign key constraint name)

For example:

Mysql > show create table user2

Mysql > alter table user2 drop foreign key user2_ibfk_1

Mysql > alter table user2 drop index pid

Mysql modify data table-modify column definition and rename data table

Modify the column definition:

Alter table table name modify [column]

Col_name col_definition [first | after col_name]

For example:

Mysql > alter table user2 modify id smallint unsigned not null first

Mysql > show columns from user2

Mysql > alter table user2 modify id tinyint unsigned not null

Modify column name

Alter table table name change [column] old_col_name new_col_name col_definition [first | after col_name]

For example:

Mysql > desc user2

Mysql > alter table user2 change pid p_id tinyint unsigned not null

Mysql > desc user2

Rename the data table:

Method 1:

Alter table table name rename [to | as] new_tb1_name

Method 2:

Rename table table name to new table name [, table name 2 to new table name 2].

For example:

Mysql > alter table user2 rename user3

Or:

Mysql > rename table user3 to user2

Summary:

First, constraints:

(1) divided by function: not null,primary key,unique key, default, foreign key

(2) divided according to the number of data columns: table-level constraints, column-level constraints.

Second, modify the data table

(1) actions for fields: add / delete fields, modify column definitions, modify column names, etc.

(2) actions for constraints: add / remove various constraints.

(3) Operation on the data table: renaming the data table (two ways)

Mysql insert record insert

Insert record:

Insert [into] Table name [(col_name,...)]

{values | value} {{expr | default},...), (...),....}

For example:

Mysql > create table user (

-> id smallint unsigned primary key auto_increment

-> username varchar (20) not null

-> password varchar (30) not null

-> age tinyint unsigned not null default 10

-> sex boolean

->)

Mysql > insert user values (null,'Tom','123',24,1)

Mysql > insert user values (null,'join','456',25,1)

Mysql > insert user values (default,'Tom','456',26,0)

Mysql > insert user values (default,'join','123',2*8+3,0), (null,'jack','5678',default,0)

Insert record:

Insert [into] table name set col_name= {expr | default}.

For example:

Mysql > insert user set username='jack',password='123.cn'

Insert record:

Insert [into] Table name [(col_name,...)] Select...

Mysql single table update update and delete record delete

Update

Update record (single table update)

Update [low_priority] [ignore] table_reference set col_name1= {expr1 | default} [, col_name2= {expr2 | default}]. [where where_condition]

For example:

Mysql > update user set age=age+5

Mysql > update user set age=age-id,sex=0

Mysql > update user set age=age+5 where id%2=0

Delete

Delete record (single table delete)

Delete from table name [where search criteria]

For example:

Mysql > delete from user where id=3

Mysql > insert user values (null,'111','456','30',null)

Select

Find a record

Select select_expr [.select _ expr..]

[

From table_references

[where conditional expression]

[group by {col_name | position} [asc | desc],...]

[having conditional expression]

[order by {col_name | expr | position} [asc | desc],...]

[limit {[offset] row_count | row_count offset offset}]

]

Query expression

The query expression represents a column you want, and there must be at least one column

Multiple columns are separated by English commas.

An asterisk (*) represents all columns, and tbl_name.* can represent all columns of a named table

Query expressions can be aliased using [AS] alias_name.

Aliases can be used in group by,order by or having clauses.

Where

Conditional expression:

Records are filtered, and if no where clause is specified, all records are displayed.

In where expressions, you can use functions or operators supported by mysql.

Group by

Grouping query results

[group by {col_name | position} [asc | desc],...]

For example:

Mysql > select sex from user group by sex

Having

Grouping condition

[having grouping condition]

For example:

Mysql > select age from user group by age having age > 300

Mysql > select sex,age from user group by 1 having count (id) > = 2

Order by

Sort the query results

[order by {col_name | expr | position} [asc | desc],....]

For example:

Mysql > insert user values (null,'123','234','30',1)

Mysql > select * from user order by id asc

Mysql > select * from user order by age,id desc

Limit

Limit the number of query results returned

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

For example:

Mysql > select * from user limit 2

Mysql > select * from user limit 3

Mysql > create table test (

-> id tinyint unsigned primary key auto_increment

-> username varchar (20)

->)

Mysql > insert test (username) select username from user where age > = 30

Mysql > select * from test

Three common subqueries for mysql:

A comparison operator modified with any,some or all:

Operand comparison_operator any (subquery)

Operand comparison_operator some (subquery)

Operand comparison_operator all (subquery)

Any,some,all keyword

Operator any some all

>, > = minimum, minimum, maximum

Any (select goods_price from tdb_goods where goods_cate=' ultrabook')

Subqueries using [not] in

Syntax:

Operand comparison_operator [not] in (subquery)

The = any operator is equivalent to in

! = all or all operation is equivalent to not in.

For example:

Create a commodity category:

Mysql > create table if not exists tdb_goods_cates (

-> cate_id smallint unsigned primary key auto_increment

-> cate_name varchar (40) not null

->)

Mysql > select goods_cate from tdb_goods group by goods_cate

Insert this into tdb_goods_cates

Mysql > desc tdb_goods_cates

Mysql > insert tdb_goods_cates (cate_name) select goods_cate from tdb_goods group by goods_cate

Mysql > select * from tdb_goods_cates

Mysql multi-table update

Multi-table update

Update table_references

Set col_name1 (column) = {expression 1 | default}

[, col_name2= {expression 2 | default}]..

[where where_condition]

Reference to the table:

Table_reference

{[within inner | cross] join | {left | right} [outer] join}

Table_reference

Join conditions of on conditional_expr table

Connection type:

Inner join, internal connection

In mysql, join,cross join and inner join are equivalent.

Left [outer] join, left outer connection.

Right [outer] join, right outside connection.

For example:

Mysql > update tdb_goods inner join tdb_goods_cates on goods_cate=cate_name set goods_cate=cate_id

Create...select

Create a data table and write the query results to the data table

Create table [if not exists] Table name

[(create_definition,....)]

Select_statement

For example:

Mysql > select brand_name from tdb_goods group by brand_name

Mysql > create table tdb_goods_brands (

-> brand_id smallint unsigned primary key auto_increment

-> brand_name varchar (40) not null

->)

-> select brand_name from tdb_goods group by brand_name

Mysql > select * from tdb_goods_brands

Mysql > update tdb_goods AS an inner join tdb_goods_brands AS b on a.brand_name=b.brand_name set a.brand_name=b.brand_id

Mysql > desc tdb_goods\ G

You need to modify the field name and field type:

Mysql > alter table tdb_goods

-> change goods_cate cate_id smallint unsigned not null

-> change brand_name brand_id smallint unsigned not null

Mysql > insert tdb_goods_cates (cate_name) values

-> ('router'), ('switch'), ('network card')

Mysql > insert tdb_goods_brands (brand_name) values ('Haier'), ('Tsinghua Tongfang'), ('Shenzhou')

Mysql > insert tdb_goods (goods_name,cate_id,brand_id,goods_price) values ('yuan 123.cn password set','13','4', '1849')

Connect inner join within mysql

Subquery and join:

Connect

Mysql select statement, multi-table update, multi-table delete statements support join operation.

Grammatical structure

Table_reference

{[inner | cross] join | {left | right} [outer] join}

Table_reference

On conditional_expr connection condition.

Data Table reference:

Table_reference

Table name [[as] alias] | table_subquery [as] alias

Data tables can be aliased using: table name AS alias or table name alias

Table_subquery can be used in the from clause as a subquery, which must be given an alias.

Connection type:

Inner join, internal connection

In mysql, join,cross join and inner join are equivalent.

Left [outer] join, left outer connection.

Right [outer] join, right outside connection.

Connection conditions:

Use the on keyword to set connection conditions, or you can use where instead.

The on keyword is usually used to set the connection conditions.

Use the where keyword to filter the result set records.

Internal connection

Displays records that match the join criteria in the left and right tables.

For example:

Mysql > select goods_id,goods_name,cate_name from tdb_goods inner join tdb_goods_cates on tdb_goods.cate_id=tdb_goods_cates.cate_id

Mysql external connection outer join

Left outer connection

Displays all the records of the left table and the records of the right table that meet the connection conditions

Right outer connection

Displays all the records of the right table and the records of the left table that meet the connection conditions.

Left outer connection:

For example:

Mysql > select goods_id,goods_name,cate_name from tdb_goods left join tdb_goods_cates on tdb_goods.cate_id=tdb_goods_cates.cate_id

Right outer connection:

Mysql > select goods_id,goods_name,cate_name from tdb_goods right join tdb_goods_cates on tdb_goods.cate_id=tdb_goods_cates.cate_id\ G

Mysql multi-table join:

For example:

Mysql > select goods_id,goods_name,cate_name,brand_name,goods_price from tdb_goods AS an inner join tdb_goods_cates AS b ON a.cate_id=b.cate_id

-> inner join tdb_goods_brands AS c ON a.brand_id=c.brand_id\ G

External connection:

A LEFT JOIN B join_condition

The result set of data table B depends on data table A

The result set of data table A depends on all data tables according to the left join condition (except Table B)

The left outer join condition determines how to retrieve data table B (if no where condition is specified)

If a record in data Table A meets the where condition, but there is no record in data Table B that meets the join condition, an additional B row with all columns empty will be generated.

External connection:

If the record found using the inner join does not exist in the connection data table, and try the following operation in the where clause: when col_name is null, if col_name is defined as not null, mysql will stop searching for more rows after finding the record that meets the join criteria.

Mysql infinite level classification table design:

Design of Infinite level classified data Table

For example:

Mysql > create table tdb_goods_types (

-> type_id smallint unsigned primary key

-> auto_increment

-> type_name varchar (20) not null

-> parent_id smallint unsigned not null default 0)

The ID of the parent_id parent class.

Mysql > select * from tdb_goods_types

For example:

Mysql > select s.typewritten name from tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p ON s.parent_id=p.type_id. Typewritten name from tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p ON s.parent_id=p.type_id.

Mysql > select p.typewritten _ name from tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id=p.type_id p.typewritten name _ journal

Mysql > select p.typewritten _ name from tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id=p.type_id GROUP BY p.type_name p.typewritten name _ journal

Mysql > select p.typewritten _ name from tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id=p.type_id GROUP BY p.type_name ORDER BY p.type_id p.typewritten name _ journal

Mysql > select p.typewriter p.typewriter name child_count from tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id=p.type_id GROUP BY p.type_name ORDER BY p.type_id count (s.type_name)

Mysql multi-table deletion:

For example:

Mysql > select goods_id,goods_name from tdb_goods group by goods_name

Mysql > select goods_id,goods_name from tdb_goods group by goods_name HAVING count (goods_name) > 1

Mysql > DELETE T1 from tdb_goods AS T1 LEFT JOIN (SELECT goods_id,goods_name from tdb_goods GROUP BY goods_name HAVING count (goods_name) > 1) AS T2 ON t1.goods_name=t2.goods_name WHERE t1.goods_id > t2.goods_id

Mysql > select * from tdb_goods\ G

Mysql character function:

Operators and functions

According to the different functions, it can be divided into:

1. Character function

2. Numeric operators and functions

3. Compare operations and functions

4. Date-time function

5. Information function

6. Aggregate function

7. Encryption function

Character function:

Function name

CONCAT (): character connection

CONCAT_WS (): character concatenation using the specified delimiter.

FORMAT (): number formatting

LOWER (): convert to lowercase letters

UPPER (): convert to uppercase letters

LEFT (): gets the left character.

RIGHT (): gets the right character.

For example:

Mysql > select * from test

Mysql > select concat ('yuan','_','MYSQL')

Mysql > select * from user

Merge two fields into one field.

Mysql > select concat (username,age) as fullname from user

Mysql > select concat ('dajiangtai','_','Mysql')

Mysql > select concat_ws ('@', 'axiomagy', 'axiomagy', 'axiomagery')

Mysql > select format (12345.678)

Mysql > select lower ('ANXIAOYU')

Mysql > select upper ('anxiaoyu')

Mysql > select left ('DaJiangTai',3)

Mysql > select lower (left ('DaJiangTai',3))

Mysql > select length ('dajiangtai')

Length (): gets the string length.

Ltrim (): removes leading spaces.

Rtrim (): deletes the trailing spaces.

Trim (): removes leading and trailing spaces.

Substring (): string interception.

[not] like: pattern matching.

Replace (): string substitution.

For example:

Mysql > select ltrim ('dajiangtai')

Mysql > select length (ltrim ('dajiangtai'))

Mysql > select trim (leading'@ 'from' @ dajiangtai@@@@')

Mysql > select trim (trailing'@ 'from' @ dajiangtai@@@@')

Mysql > select trim (both'@ 'from' @ dajiangtai@@@@')

Mysql > select replace ('@ dajiang@@tai@@@','@','')

Mysql > select replace ('@ dajiang@@tai@@@','@','##')

Mysql > select substring ('dajiangtai',3,5)

Mysql > select substring ('dajiangtai',3)

Mysql > select substring ('dajiangtai',-3)

Mysql > select 'dajiangtai' like'd%'

Mysql numeric operators and functions

Ceil (): rounding in one

Div: integer division

Floor (): round off

Mod: take the remainder (modulo)

Power (): exponentiation.

Round (): rounded

Truncate (): digital interception

For example:

Mysql > select 1: 2

Mysql > select ceil (3.14)

Mysql > select floor (3.14)

Mysql > select 1amp 2

Mysql > select 1 div 2

Mysql > select 4% 3

Mysql > select 4 mod 3

Mysql > select 4.2 mod 3

Mysql > select power (2pr 3)

Mysql > select round (3.1415pr 3)

Mysql > select truncate (12.3456)

Mysql > select truncate (12.3456 Murray 1)

Mysql compare operators and functions

[not] between...and... [not] within the scope

[not] in (): [not] is within the range of listed values.

Is [not] null: [no] is empty.

For example

Mysql > select 2 between 1 and 3

Mysql > select 1 between 2 and 3

Mysql > select 2 between 2 and 3

Mysql > select 1 in (1, 2, 3, 4)

Mysql > select null is null

Mysql > select''is null

Mysql date-time function

Date-time function:

Now (): current date and time

Curdate (): current date

Curtime (): current time

Date_add (): date change.

Datediff (): date difference.

Date_format (): date formatting.

For example:

Mysql > select now ()

Mysql > select curdate ()

Mysql > select date_add ('2015-1-1 minute interval 365 day)

Mysql > select date_add ('2015-1-1 day)

Mysql > select datediff ('2015-1-2')

Mysql > select date_format ('2015-5-5 years 5-5 minutes)% m _ max% d _ max% y')

Mysql information function

Connection_id (): connect ID

Database (): current database

Last_insert_id (): last insert the ID number of the record.

User (): current user

Version (): version information.

For example:

Mysql > select connection_id ()

Mysql > select database ()

Mysql > create table test (

-> id tinyint (3) unsigned not null primary key auto_increment

-> username varchar (20) not null)

Mysql > select last_insert_id ()

Mysql > insert test (username) values ('join')

Mysql > insert test (username) values ('111')

Mysql > insert test (username) values ('123')

Mysql > insert test (username) values ('11')

Mysql > insert test (username) values ('22'), (' 33')

Mysql > select last_insert_id ()

Mysql > select user ()

Mysql > select version ()

Mysql aggregate function:

Avg (): average

Count (): count.

Max (): maximum.

Min (): minimum.

Sum (): summation.

For example:

Mysql > select avg (id) from test

Mysql > select count (id) from test

Mysql encryption function:

MD5 (): information digest algorithm

PASSWORD (): cryptographic algorithm

For example:

Mysql > select md5 ('admin')

Mysql > select PASSWORD ('admin')

Mysql > set PASSWORD=PASSWORD ('root')

Mysql custom function:

User-defined functions (UDF) are a way to extend mysql in the same way as built-in functions.

Two necessary conditions for a custom function:

(1), parameter

(2), return value.

Functions can return values of any type, and they can also receive parameters of these types.

Create a custom function:

Create function function_name

Returns

{string | integer | real | decimal}

Routine_body

About the function body:

(1) the function body is composed of legal SQL statements.

(2) the function body can be a simple select or insert statement.

(3) the beginning statement is used if the function body is a compound structure.

(4) compound structures can contain declarations, loops, and control statements.

Set the client code to gbk:

Mysql > set names gbk

Mysql > select date_format (now (),'% Y% m / d% H point:% I minutes:% s seconds')

Mysql > create function F1 ()

> returns varchar (30)

-> return date_format (now (),'% Y% m / d% H point:% I minutes:% s seconds')

Mysql > CREATE FUNCTION Q1 (num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)

-> RETURNS FLOAT (10jue 2) UNSIGNED

-> RETURN (num1*num2) / 2

Mysql > select Q1 (3pr 4)

Mysql > create function adduser (username VARCHAR (20))

-> RETURNS INT UNSIGNED

-> BEGIN

-> INSERT test (username) values (username)

-> RETURN LAST_INSERT_ID ()

-> END

-> / /

Mysql > select adduser ('hello')

-> / /

Delete function:

DROP FUNCTION [IF EXISTS] function_name

Mysql stored procedures:

SQL command-> mysql engine-"parsing -" executable command-"execution result -" client.

Stored procedure:

A stored procedure is a precompiled collection of SQL statements and control statements, stored in a name and processed as a unit.

Advantages of stored procedures:

The function and flexibility of SQL statement are enhanced.

Achieve faster execution speed.

Reduce network traffic.

Syntax for creating a stored procedure:

Create

[DEFINER= {USER | CURRENT_USER}]

PROCEDURE sp_name ([proc_parameter [,...]])

[characteristic...] Routine_body

Proc_parameter:

[IN | OUT | INOUT] param_name type

Parameters:

IN: indicates that the value of this parameter must be specified in the calling stored procedure.

OUT: indicates that the value of this parameter can be changed by the stored procedure and can be returned.

INOUT: indicates that the parameter is specified at call time and can be changed and returned.

Process body:

The body of a procedure is made up of legal SQL statements.

The process body can be an "arbitrary" SQL statement.

If the procedure body is a compound structure, the begin.. end statement is used.

Composite structures can contain declarations, loops, and control structures.

For example:

Mysql > create procedure sp1 () select VERSION ()

Mysql > CALL sp1

Call the stored procedure:

CALL sp_name ([parameter [,...]])

CALL sp_name [()]

Mysql creates a stored procedure with IN type parameters:

For example:

Mysql > delimiter / /

Mysql >

Mysql > create procedure removeTestByid (IN id INT unsigned)

-> BEGIN

-> DELETE FROM test where id=id

-> END

-> / /

Modify the stored procedure:

Alter procedure sp_name

[characteristic...]

COMMENT 'string'

| | {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL |

DATA}

| | SQL SECURITY {DEFINER | INVOKER} |

Delete a stored procedure:

Drop procedure name

For example:

Mysql > insert user (username,password,age,sex) values ('Tom1','123',21,1), (' Tom2'

'1234) 22. 1)

Mysql > insert user (username,password,age,sex) values ('Tom3','123',30,1), (' Tom4'

'1234 magic 30 1)

Mysql > insert user (username,password,age,sex) values ('Tom5','12',20,1), (' Tom6','

1234) 32 (1)

Mysql > delimiter / /

Mysql > create procedure removeUserByid (IN p_id INT UNSIGNED)

-> BEGIN

-> DELETE FROM user where id=p_id

-> END

-> / /

Mysql > delimiter

Mysql > CALL removeUserByid (2)

Mysql > SELECT * FROM user where id=2

After reading the above handouts on the grammatical structure of mysql, I hope it can bring some help to you in practice. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.