catalogue

7, View and index

1. Improve query efficiency

2. View

2.1 definition and function of view

2.2 reasons and advantages of establishing view

2.3 use of views

3. Index

3.1 concept of index

3.2 functions and advantages of index

3.3 index and storage engine

3.4 classification of index

3.4.1 general index

3.4.2 unique index

3.4.3. Primary key index / foreign key index

3.4.4 full text index

3.5 disadvantages of index

8, Functions and stored procedures

1. Database function

1.1 aggregation function

1.2 time date function

1.3. Common library functions

2. Custom function

2.1. Creation of user-defined functions

2.2. User defined Terminator

2.3 function calling and deletion

3. Stored procedure

3.1. Create a parameterless stored procedure

3.2. Create a stored procedure with parameters

3.3 calling of stored procedure

3.4. View the information about the created specified stored procedure

3.5. View relevant information of all created stored procedures

3.6 deletion of stored procedure

4. Differences between stored procedures and functions

7, View and index

1. Improve query efficiency

         Query statements also have query efficiency problems, such as the difference between cross connection and internal and external links. Cross connection needs to generate a temporary Cartesian product table, which is inefficient.

The following methods are adopted to improve the efficiency of query statements

         In real life, the query often needs to find the data in multiple tables, and the queried data tables are required to be related, which involves multi table query.

Basic format of cross connection:

         ① Select a valid table name order. Generally, the select statement gives priority to the table written at the end of the from clause. Putting the least number of records or the parent table (referenced table) at the end can improve the query efficiency.

         ② select try to avoid using *. If you don't need to query all attributes, try to write out the fields to be queried one by one instead of using * instead. For example, there are 10 fields in total. You need to query 9 of them. It is better to write all 9 fields. If you can meet the needs, you can query one field less.

         ③ Integrate simple database query statements, and the data that can be queried at one time should not be divided into two or more queries.

         ④ Use where to replace having as much as possible. If you can use where to solve the problem, do not use the having clause of grouping query, because having is grouping filtering after querying all records. Sorting and statistics may also be required, which is cumbersome.

         ⑤ Try to use more internal functions and less operator operations, because the processing of internal functions is optimized better.

         ⑥ Use more table aliases to reduce parsing time.

         ⑦ Try to use exists instead of in and not exists instead of not in.

         ⑧ Try to use > = instead of >. For example, sometimes > 5 and > = 6 have the same query effect, but one is to find 5 first, the other is to find 6 first, and one more number is found.

2. View

2.1 definition and function of view

         Views can be used instead of queries. When the same query statement is used multiple times, you can create a view for this query for subsequent use.

         The view is equivalent to creating a shortcut for a query result set (virtual table).

2.2 reasons and advantages of establishing view

         ① Because some query statements are long, troublesome and often used, you can create a view for them to facilitate subsequent operations.

         ② Users only care about the data, but do not care about how the data is queried and how the connection operation between tables is. It is result oriented, not process oriented.

2.3 use of views

① view keyword: view

General format:

create view <View name> as <Query statement>;

create view <View name> as <select <Attribute name> from <Table name>>;

You can query all the data in the table to create a view, or you can query some data in the table to create a view

② Basic use of views

select * from <View name>; The view is consistent with the query statement above

③ View modification

alter view View name as Query statement; # Change view to another query

④ Delete view

drop view View name;

3. Index

3.1 concept of index

         Index is a storage structure that can efficiently obtain data to store data, such as hash, binary search tree, red black tree, etc.

3.2 functions and advantages of index

         Index is used to improve the efficiency of query. Indexing some fields that need frequent query is faster than ordinary fields. In addition, index is of no other use.

         Indexing can improve query speed.

3.3 index and storage engine

         In MySQL, the index is related to the storage engine, that is, the index is a storage engine level concept, and the indexed data also needs to be stored in the hard disk. Different storage engines implement the index differently, that is, the stored data structure is different.

         ① MyISAM (default) and InNODB support BTREE (B-tree and binary lookup tree) indexes, and data is stored in the form of binary tree.

         ② Memory and Heap support BTREE and HASH (HASH table and array) indexes. Data is stored in the form of HASH table (fast query speed).

3.4 classification of index

3.4.1 general index

         The index established for ordinary fields allows duplicate fields and null values to be defined for the index.

① Create normal index

Basic format for creating a normal index:

create index Index name on Table name<Field 1[,Field 2,...,field n]>;

For example:

create index Ind_Sage on student(s_age);

-- yes student Tabular s_age Property creates a normal index named Ind_Sage. 

select s_age from student;

-- For query student In the table s_age With this single field, the query efficiency will be improved

-- Due to the small amount of data in the data table, we can't see the obvious effect of improving query efficiency

-- The more data in the data table, the larger the amount of data, and the more obvious the improvement of index effect will be

② Add indexes in a modified manner

Basic format:

alter table <Table name> add index <Index name(Attribute name)>;

For example:

alter table student add index Ind_Ssex(s_sex);

-- by student In table s_sex Property to add a Ind_Ssex General index of

③ View all indexes in the table

Basic format:

show index from <Table name>;

For example:

show index from student;

-- see student All indexes in the table

3.4.2 unique index

         Unique index keyword: unique index

         The query efficiency will be improved by querying the fields with unique indexes.

         Unique indexes are related to unique constraints. After adding a unique index to an attribute, duplicate values of the attribute are not allowed.

① Create unique index

Basic format:

create unique index <Index name> on <Table name(Attribute name)>;

-- The table name is<Table name>The attribute name in the table is<Attribute name>The property creation index for is named<Index name>Unique index of

For example:

create unique index Ind_Sage on student(s_name);

-- The table name is student Attributes in the table s_name Create index named Ind_Sage Unique index of

#You can create a unique index automatically when you add a unique constraint to a property without manually creating a unique index.

② Adds a unique index in a modified manner

Basic format:

alter teble <Table name> add unique(<Attribute name>);

-- The table name is<Table name>The property name in the data table is<Attribute name>Add a unique index to the properties of

For example:

alter table student add unique(s_name);

-- The table name is student The property name in the data table is s_name Add a unique index to the properties of

3.4.3. Primary key index / foreign key index

① Basic concepts of primary key index

Primary key / foreign key index keyword: primary key

The query efficiency of primary keys and foreign keys is relatively high, because primary keys and foreign keys have primary key indexes.

② Creation of primary key index

         A primary key index is automatically created when a primary key or foreign key constraint is established for an attribute, or when a table is created.

For example:

create table Table name{ 
id int; 
name varchar(20); 
primary key(id) 
-- As attribute id Create a primary key index }

③ Addition of primary key index

Basic format:

ALTER TABLE <Table name> ADD PRIMARY KEY (<Listing>);

-- Add the primary key index in a modified way

3.4.4 full text index

         Full text index keyword: fulltext index

         Query the attributes of string or text type added with full-text index, and the query efficiency will be improved.

         Full text indexing is relatively less used, which is limited to adding data types such as text and string.

① Creation of full-text index

Basic format:

create fulltest index <Index name> <Table name(Attribute name)>;

-- The table name is<Table name>The property name in the data table is<Attribute name>Create a full-text index using the properties of<Index name>

For example:

create fulltext index fullind_tname teacher(t_name);

-- The table name is teacher The property name in the data table is t_name Add a full-text index to the properties of<Index name>

② Add a full-text index in a modified manner

Basic format:

alter table <Table name> add fulltext index fullind_<Index name>(<Attribute name>);

-- The table name is<Table name>The property name in the data table is<Attribute name>Add a full-text index to the properties of<Index name>

For example:

alter table teacher add fulltext index fullind_tname(t_name);

-- The table name is teacher The property name in the data table is t_name Add a full-text index to the properties of fullind_tname

3.4.5 spatial index

         Spatial index is an index established for spatial data types (point, line, surface and three-dimensional graphics). Because the application scope of spatial index is not extensive, it is generally only used in the database storing map, model and other related data

3.5 disadvantages of index

Compared with ordinary fields, the indexed fields need to occupy additional disk space. Due to the differences in the stored data structure, the indexed fields need to be dynamically maintained when adding, deleting and modifying data, which increases the workload of the DBMS and reduces the maintainability of the database.

8, Functions and stored procedures

1. Database function

         High level languages have their own library functions, and databases are no exception. Some functions for realizing specific functions are also provided in the database.

1.1 aggregation function

         Among database functions, aggregation function is the most commonly used one. The so-called aggregation is to combine a group of values and return a single value.

① count

         Count function to count tuples or attributes.

         General format: count (attribute name).

select count(*) from Table name;

# '*' counts tuples of the entire table

select count(Attribute name)from Table name where Conditional expression;

# count supports condition counting. Only valid values are calculated, and null values are not calculated

② max

         Find the maximum function to query the maximum number in the field.

         General format: Max (attribute name)

For example:  

 select max(Attribute name) from Table name [where Conditional expression];

③ min

         Find the minimum value function to query the minimum number in the field.

         General format: min (attribute name)

For example:

select min(Attribute name) from Table name [where Conditional expression];

④ avg

         Find the average function to query the average value of all numbers in the field.

         General format: AVG (attribute name)

For example:

select avg(Attribute name) from Table name [where Conditional expression];

⑤ sum

         The summation function queries the sum of all numbers in the attribute.

         General format: sum (attribute name)

For example:

select sum(Attribute name) from Table name [where Conditional expression];

# In addition to calculating by function, it can also be calculated directly by operator

For example:

select Property name 1*2,Property name 2+Property name 3 from Table name;

1.2 time date function

① now

Find the current date and time.

General format: now().

For example:

select now();

② date

Find the date function.

General format: date (attribute name of date time type).

For example:

select date(birthday) from student;

③ time

Find the time function.

General format: time (attribute name of date time type).

For example:

select time(birthday) from student;

④ date_format

Time date type to string type function.

General format: Date_ From (date time type, format string)

For example:

select date_format(now(),'%Y%m%d:%H%i%s');
# Format strings can be replaced by variables

⑤ Other library functions for obtaining date and time

time(); -- Time:branch:second

year(); -- year

month(); -- month

day(); -- day

hour(); -- hour

minute(); -- minute

second(); -- second

1.3. Common library functions

① upper

         Lowercase to uppercase function.

General format: upper (character attribute name);

For example:

select upper('abc');

# It can also be replaced by the ucase function, such as select ucase('abc ');

② lower

         Uppercase to lowercase function.

General format: lower (character attribute name);

For example:

select upper('ABC');

# It can also be replaced by lcase function, such as select lcase('ABC ');

③ substring

         Extract string function to find a substring in the string with the given parameters.

General format:

substring(character string,start,lenth);

# Take out the string with length lenth from the start character in the string.

For example:

select substring('abc123@#$',4,3);# The result is 123

# It can also be used mid Function instead, such as: select mid('abc123@#$',4,3);

④ round

         Rounding function. (instead of the floor function, it can be used for rounding)

General format:

round(value type[,Exact digit]);

For example:

select round(1234.56); # Without adding exact digits, the default is to the whole digit

select round(123.456,2); #How many decimal places do you keep when you add the exact number of digits

⑤ power

Power function

General format:

power(base number,index);

For example:

select power(2,3); # Find the value of the third power of 2

# POW function can be used instead of power function, such as select pow(2,3);

⑥ length

Returns the length of the string

General format:

length(character string)

⑦ concat

String connection function, connecting multiple strings

General format:

concat(String 1,String 2,......,character string n)

⑧ database

Returns the current database name

General format:

select database();

⑨ user

Get current user name

Basic format:

select user();

2. Custom function

         Custom functions in the database are similar to C language functions. You can customize functions and edit the code executed by functions to complete the functions you want.

2.1. Creation of user-defined functions

Basic format for creating custom functions:

create function Function name(Function parameter data type)

returns return type

begin

# Function body

return(Function return value)

end;

# Version 8.0 database adds a database security option

You need to execute the following code to create a function

set global log_bin_trust_function_creators=TRUE;

2.2. User defined Terminator

The default statement terminator in MySQL database is'; ' In addition, MySQL database also supports user-defined terminators.

Custom terminator keyword: delimiter

Basic format of custom Terminator:

delimiter <character> -- take<character>Define as Terminator

For example:

delimiter , -- Default statement Terminator';'Change to','，After all SQL The statement will','As Terminator

delimiter $$ -- Default statement Terminator';'Change to'$$'，After all SQL The statement will'$$'As Terminator

2.3 function calling and deletion

① Function call

Basic format of function call:

select Function name(Function parameters);

② Use table data as function parameters

select Function name(Table properties) from Table name;

③ Delete function

drop function Function name;

3. Stored procedure

         Similar to functions, stored procedures are a collection of MySQL statements written and compiled in advance and stored in the database. They are used to simplify the work of developers, reduce data transmission between the database and the application server, and improve processing efficiency.

         Stored procedure: parameter passing (parameter type) can use IN, OUT and INOUT, and there is no direct return value. IN indicates data incoming, OUT indicates data outgoing, INOUT indicates both incoming and outgoing. Use cannot be used to switch databases within a stored procedure.

         Function: parameters can only use IN and must have a return value.

         Stored procedure keyword: Procedure

3.1. Create a parameterless stored procedure

Basic format for creating parameterless stored procedures:

delimiter Custom Terminator

create procedure Stored procedure name()

begin

SQL Statement 1;

SQL Statement 2;

......

SQL sentence n;

end;

delimiter ;

For example:

delimiter //

create procedure select_stu()

begin -- reunite with SQL sentence

select s_id from student where s_id > 3; -- stored procedure select_stu Complete the function of querying student table data

select s_name from student where s_id > 3;

select s_cid from student where s_id > 3;

end//

delimiter ;



call select_stu(); -- Executing this stored procedure is similar to a view

3.2. Create a stored procedure with parameters

① Basic format for creating a stored procedure with parameters:

delimiter Custom Terminator create procedure Stored procedure name(

Parameter type parameter name 1 data type,

Parameter type parameter name 2 data type,......,

Parameter type parameter name n data type)

began

SQL sentence;

end Custom Terminator

delimiter ;

3.3 calling of stored procedure

Basic format of calling stored procedure:

call Stored procedure name(parameter list);

For example:

call select_stu(); -- Calling a parameterless stored procedure

call select_stu; -- Parentheses can be omitted when calling a parameterless stored procedure

3.4. View the information about the created specified stored procedure

To view the basic format of the specified stored procedure:

show create procedere <Stored procedure name>;

For example:

show create procedure select_stu;

3.5. View relevant information of all created stored procedures

To view the basic format of all stored procedures:

show procedure status;

3.6 deletion of stored procedure

Basic format for deleting stored procedures:

drop procedure <Stored procedure name>；

4. Differences between stored procedures and functions

         ① The. Function can only return one data, while the stored procedure can return multiple data;

         ② . there are many restrictions on functions, which can not return temporary tables (select), and only table variables can be used. There are few restrictions on stored procedures, and dynamic SQL statements (such as select) can be used;

         ③ The function of stored procedure processing is complex, and the calculation of function implementation data is highly targeted;

         ④ . the stored procedure can modify the table, but the function cannot perform a set of operations to modify the global database state;

         ⑤ . stored procedures can return multiple parameters through out, while functions can only return a single data or table object.