MySQL advanced statement (II)

_ Rui_ ao 2022-02-13 07:07:03 阅读数:57

mysql advanced statement ii

MySQL High level statements ( Two )

1. View

It can be thought of as a virtual table or a storage query

  1. The difference between a view and a table is , There is the actual stored information in the form , And a view is a structure built on a table , It doesn't actually store data itself .
  2. The temporary table disappears automatically when the user exits or loses the connection to the database , And the view doesn't disappear .
  3. Views don't contain data , Just store its definition , Its purpose is to simplify complex queries . For example, you need to query several tables , But also to carry out statistical sorting and other operations , Write SQL Sentences can be cumbersome , Join several tables with views , Then query the view , Just like querying a table , Very convenient .

# grammar :

CREATE VIEW “ View table name ” AS “SELECT sentence ”;

# Example 1: Create view table , Table, new_students, The data is following as hinder select sentence ,scores The alias of the table is a,students The alias of the table is b; take a Table and b Table equivalent connection , The connection field is stuid, take a Tabular score and stuid The columns displayed in the new table are score and stuid; take b In the table name The column displayed to the new table is name
create view new_students as select a.score score,a.stuid stuid,b.name name from scores a inner join students b on a.stuid=b.stuid;
# Delete view 
drop view new_students;

The view can display data . But no data is stored in the view , Only its definition can be stored

The view table needs to be deleted before it disappears , Exit or disconnect the database , The view will not disappear

2. combine

combine , Put two SQL The results of the statement are combined , Two SQL The fields generated by the statement need to be of the same data type

UNION

There will be no duplicate data values for the generated results , And sort according to the order of the fields

# grammar :
[SELECT sentence 1] UNION [SELECT sentence 2];

# Example : Search two tables courses All records in the column , First list the first... In the original order select The data in the statement , Then, compare the data in the second table with the data in the first table and list them in the order of source 
select courseid from coc union select courseid from courses;

image-20211223100740170

UNION ALL

List the data values of the generated results , With or without repetition

# grammar :
[SELECT sentence 1] UNION ALL [SELECT sentence 2];

# Example : Search two tables courses All records in the column , Not in order 
select courseid from coc union all select courseid from courses;

image-20211223100817765

3.case

# grammar :

SELECT CASE (" Field name ")
WHEN " Conditions 1" THEN " result 1"
WHEN " Conditions 2" THEN " result 2"
...
[ELSE " result N"]
END
FROM " Table name ";
# " Conditions " It can be a number or a formula . ELSE Clause is not necessary .

4. Date time function

String function describe
curdate() Returns the date of the current time
curtime() Returns the hour, minute and second of the current time
now() Returns the date and time of the current time
month(x) Return date x The month in is worth
week(x) Return date x It's the first few weeks of the year
hour(x) return x The hour value in
minute(x) return x Minutes in
second(x) return x The second value in
dayofweek(x) return x What day is ,1 Sunday ,2 Monday
dayofmonth(x) Calculate the date x It's the day of the month
dayofyear(x) Calculate the date x It's the day of the year
select curdate();
# The current date 
select curtime();
# At the present time Minutes and seconds 
select now();
# current time Mm / DD / yyyy HHM / S 
select month('2021-08-11');
# Return to the month 
select week('2021-12-02');
# Back to the day of the year 
select hour('2021-12-02 14:18');
# Returns the hour value 
select minute('2021-12-05 14:30:9');
# Returns the value of minutes 
select second()
# Returns the second value 
select dayofweek('2021-12-05');
# Day of the week 
select dayofmonth('2021-12-05');
# Returns the value of a day in the month 
select dayofyear('2021-12-05');
# Return to... In a year The next few days 

5.regexp Regular expressions

Matching mode describe example
^ Match the start character of the text ‘^bd’ Match with bd Starting string
$ Match the end character of the text ‘qn$’ Match with qn a null-terminated string
. Match any single character ‘s.t’ Match any s and t A string with one character between
* Match zero or more characters before it ‘fo*t’ matching t There is any one in front o
+ Match preceding characters 1 Times or times ‘hom+’ Match with ho start , At least one back m String
character string Match contains the specified string ‘clo’ The match contains clo String
p1|p2 matching p1 or p2 ‘bg|fg’ matching bg perhaps fg
[…] Match any character in the character set ‘[abc]’ matching a perhaps b perhaps c
[^…] Match any characters that are not in brackets [^ab] Match does not contain a perhaps b String
{n} Match the previous string n Time ‘g{2}’ The match contains 2 individual g String
{n,m} Match the previous string at least n Time , at most m Time f{1,3}’ matching f least 1 Time , most 3 Time

6. stored procedure

Usage method yes shell Script

Format and function are very similar

MySQL A database stored procedure is a set of procedures designed to perform specific functions SQL Collection of statements
In the process of using stored procedures, common or complex work is used in advance SQL The statement is written and stored with a specified name ,
This process is compiled and optimized and stored in the database server . When you need to use this stored procedure , Just call it .
Stored procedures perform better than traditional sql Faster 、 More efficient execution .

Stored procedure advantage :

  1. encapsulation

    It usually takes more than one to complete a logical function SQL sentence , And it is possible to pass parameters between statements , therefore , Writing logical functions is a little more complicated , And stored procedures can put these SQL Statements are contained in a separate unit , Make the complex invisible to the outside world SQL sentence , Only a simple call is needed to achieve the goal . And database professionals can modify stored procedures at any time , It doesn't affect the application source code that calls it

  2. Can be enhanced SQL The function and flexibility of statements

    Stored procedures can be written with process control statements , Very flexible , It can complete complex judgment and more complex operation .

  3. Can reduce network traffic

    Because stored procedures run on the server side , And fast execution , So when the stored procedure is called on the client computer , All that's passed in the network is the call statement , This reduces network load .

  4. Improve performance

    When the stored procedure is successfully compiled , It's stored in the database server , In the future, the client can directly call , So all of SQL Statement will be executed from the server , To improve performance . But it should be noted that , The more stored procedures, the better , Excessive use of stored procedures will affect system performance

  5. Improve database security and data integrity

    One way to improve the security of stored procedures is to use them as intermediate components , Some tables can be operated in stored procedures , Then the stored procedure is provided to the external program as an interface . such , External programs cannot directly manipulate database tables , The corresponding table can only be manipulated through stored procedures , So to some extent , Security can be improved .

  6. Make data independent

    The independence of data can achieve the effect of decoupling , in other words , Programs can call stored procedures , Instead of executing multiple SQL sentence . In this case , Stored procedures isolate data from users , The advantage is that when the structure of the data table changes , The call table does not need to modify the program , You just need the database manager to rewrite the stored procedure .

copyright:author[_ Rui_ ao],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130707017983.html