MySQL Learning Note Two

øfunction function

Functions are much more useful, and are generally used after the SELECT query statement and the WHERE condition statement. According to the result returned by the function,

Can be divided into: multi-line functions and single-line functions, the so-called single-line function is to separate each piece of data to calculate, and then each piece of data to get a result.

such as: string function, and multi-line function, is more than one record at the same time, get finally only a result record. such as: Sum, AVG, etc.

Multi-line functions, also known as aggregation functions, grouping functions, are mainly used to accomplish some statistical functions. MySQL's single-line function has the following characteristics:

    The arguments to a single-line function can be variables, constants, or data columns. A single-line function can accept multiple arguments, but returns a value.

    The single-line function is that it works on each row individually, and each row (which may contain multiple parameters) returns a result.

    A single-line function can change the data type of a parameter. Single-line functions support nesting use: The return value of the inner function is the parameter of the outer function.

Single-line functions can be divided into:

    type conversion function;

    bit function;

    Process Control statements;

    cryptographic decryption function;

    Information functions

Single-line function

1,    char_length character length

Select Char_length (tel) from user;

2.    Sin function

Select sin from user;

Select sin (1.57);

3.    Add Date function

Select Date_add (' 2010-06-21 ', Interval 2 month);

Interval is a keyword, 2 month is 2 months meaning, 2 is the value, month is the unit

Select Adddate (' 2011-05-28 ', 2);

Add the following days to the previous date

4,    get the current system time, date

Select Curdate ();

Select Curtime ();

5.    Cryptographic functions

Select MD5 (' Zhangsan ');

6.    Null handler function

Select Ifnull (Birthday, ' is null birthday ') from user;

If birthday is null, the following string is returned

Select Nullif (age, 245) from user;

Returns null if Age equals 245, not equal to return age

Select IsNull (birthday) from user;

Determine if birthday is null

Select if (IsNull (birthday), ' birthday is null ', ' birthday isn't ' null ') from user;

If birthday is null or 0 returns birthday is NULL, otherwise birthday is null; similar to three-mesh operator

7. Case    Process function

The case function is a process control function that can accept multiple parameters, but will eventually return only one result.

(Case Sex

    When 1 Then ' Male '

    When 0 Then ' women '

    Else ' Mars people '

    End

) Sex

from user;

Group functions

A group function is a multiline function, which is an operation that completes one or more rows of result sets, and finally returns a result instead of returning a result for each record.

1.    avg Averaging operation

Select AVG (age) from user;

Select AVG (distinct age) from user;

2,    count record number of statistics

Select COUNT (*), count (age), COUNT (distinct age) from user;

3.    Max Max value

Select Max (age), Max (distinct age) from user;

4,    min min value

Select min (age), min (distinct age) from user;

5,    sum sum, gather and

Select SUM (age), SUM (distinct age) from user;

Select SUM (ifnull (age, 0)) from user;

6. GROUP BY    Group

Select COUNT (*), sex from the user group by sex;

Select COUNT (*) from the user group by age;

SELECT * from user group by sex, age;

7.    having the condition filter

You cannot filter groups in the WHERE clause, where clauses are used only to filter rows. Filter group by needs to have

A group function cannot be used in a WHERE clause to use a group function

Select COUNT (*) from the user group by sex have sex <> 2;

Ø Multi-Table query and sub-query

Database query function is most abundant, many times need to use the query to complete some things, and not simply to operate on a table. Instead, a federated query is made on multiple tables,

MySQL multi-table connection query has two specifications, the earlier SQL92 specification support, the following table connection query:

    Equivalent connection

    Non-equivalent connection

    External connection

    Generalized Cartesian product

The SQL99 rule provides a better readability of the multi-table connection syntax and provides more types of connection queries, SQL99 supports several multi-table connection queries such as the following:

    Cross Connect

    Natural connection

    Connection using a using clause

    Using the ON clause to connect

    All connections or left and right external connections

Connection query for SQL92

SQL92 Connection query syntax is relatively simple, more than one table placed in the FROM keyword, multiple table with "," separated;

The condition of the connection is placed after the where condition and is connected directly with the and logical operator with the query condition. If equality is used in the condition,

is called the equivalent connection, the opposite is called the non-equivalence, if there is no condition is called the generalized Cartesian product.

Generalized Cartesian product: Select S.*, c.* from student S, Classes C;

Equivalent: Select S.*, c.* from student s, classes C where s.cid = C.id;

Non-equivalent: select S.*, c.* from student s, Classes C where S.cid <> c.id;

Select S.*, c.name classes from classes C, student s where c.id = s.classes_id and s.name are NOT null;

SQL99 Connection Query

1. Cross join, similar to SQL92 's Cartesian product query, without conditions. Such as:

Select S.*, c.name from student s cross join classes C;

2, Natural connection natural join query, without conditions, the default condition is the same field in 2 table as the join condition, if there is no same field, the result of the query is empty.

Select S.*, c.name from student s natural join classes C;

3. Using clause connection query: The Using clause can be one or more columns, and the specified two tables are displayed with the same name as the join condition.

If you use a join query with natural join, all the same fields will be queried as joins. The using can specify the same column and number.

Select S.*, c.name from student s join Classes C using (ID);

4,    join ... on connection queries, query conditions are completed on, and each on statement can specify only one condition.

Select S.*, c.name from student S joins classes c on s.classes_id = C.id;

5,    left and right outside the connection: 3 kinds of outer joins, the [outer] join, the outer join, the connection conditions are specified by the ON clause, the condition can be equivalent, non-equivalent.

Select S.*, c.name from student s left joins classes C on s.classes_id = C.id;

Select S.*, c.name from student s right joins classes C on s.classes_id = C.id;

    Sub-query

    Subqueries refer to nesting another query in a query statement, which can support multiple layers of nesting. A subquery can appear in 2 locations:

    The FROM keyword is then queried as a table, a usage called inline view, because the essence of the subquery is a temporary view

    The value that appears after the Where condition as the filter condition

Sub-query Note points:

    Subqueries are enclosed in parentheses, and in special cases a temporary name is required.

    When a subquery is treated as a temporary table (a subquery after the from), you can alias the subquery, especially if you want to qualify the data column name as a prefix

    When a subquery is used as a filter condition, the subquery is placed to the right of the comparison operator, providing readability

    When a subquery is a filter, a single-row subquery uses a single-line operator, and a multiline subquery uses a multiline operator

Use the subquery following the from as a table:

SELECT * FROM (select ID, name from classes) s where S.id in (1, 2);

To use as a condition:

SELECT * from student s where s.classes_id in (select ID from classes);

SELECT * from student s where s.classes_id = any (select id from classes);

SELECT * from student s where s.classes_id > A (select id from classes);

Ø Operators and functions

1,    Boolean only judge

Select 1 is True, 0 is false, and null is unknown;

Select 1 are not unknown, 0 are not unknown, and Null is not unknown;

2,    coalesce function, returns the first non-null value

Select COALESCE (null, 1);

Select COALESCE (1, 1);

Select COALESCE (null, 1);

Select COALESCE (null, NULL);

3,    when there are 2 or more parameters, return the largest parameter value

Select Greatest (2, 3);

Select Greatest (2, 3, 1, 9, 55, 23);

Select Greatest (' D ', ' A ', ' B ');

4,    least function, returns the minimum value, if NULL, returns a null value

Select least (2, 0);

Select least (2, 0, NULL);

Select Least (2, 10, 22.2, 35.1, 1.1);

5.    Control Flow function

Select Case 1 While 1 then ' was 1 ' when 2 Then ' is 2 ' else ' none ' end;

Select Case at 1 > 2 Then ' yes ' else ' no ' end;

6.    ASCII string function

Select ASCII (' A ');

Select ASCII (' 1 ');

7. Binary    functions

Select Bin (22);

8.    return binary string length

Select Bit_length (11);

9.    Char Converts a value to a character and a decimal rounding

Select char (65);

Select char (65.4);

Select char (65.5);

Select char (65.6);

Select char (65, 66, 67.4, 68.5, 69.6, ' 55.5 ', ' 97.3 ');

10.    using Change Character set

Select CharSet (char (0*65)), CharSet (char (0*65 using UTF8));

11,    get the character length Char_length,character_length

Select Char_length (' abc ');

Select Character_length (' EFT ');

12,    compress compression string, uncompress decompression

Select Compress (' Abcedf ');

Select Uncompress (Compress (' ABCEDF '));

13.    Concat_ws Delimited string

Select Concat_ws (' # ', ' first ', ' second ', ' last ');

Select Concat_ws (' # ', ' first ', ' second ', null, ' last ');

Ø Transaction Processing

Action

    Start transaction: Start transaction

    Commit TRANSACTION: Commit

    ROLLBACK TRANSACTION: Rollback

    The Atuocommit system defaults to 1 commit mode, and set autocommit 0 is required if you want to manually control transactions;

    So we can use commit, rollback to control the transaction.

Disables autocommit instead of set autocommit in a block of statements

Start transaction;

Select @result: = AVG (age) from temp;

Update temp Set age = @result where id = 2;

SELECT * FROM temp where id = 2;//value is changed

rollback;//rollback

SELECT * FROM temp where id = 2;//changed back

During this time only the disable autocommit of commit, Rollback,start transaction will end. Then revert to the original autocommit mode;

Statements that cannot be rolled back

    Some statements cannot be rolled back. Typically, these statements include data definition language (DDL) statements, such as statements that create or cancel a database.

    And a statement that creates, cancels, or alters a table or stored subroutine.

    You should not include this type of statement when you design a transaction. If you publish a statement in the front of a transaction that cannot be rolled back,

    The other statements in the back section will have an error, in which case the full effect of the transaction cannot be rolled back by publishing the rollback statement.

Some operations also implicitly commit the transaction

such as alter, create, drop, rename table, lock table, set autocommit, start transaction, TRUNCATE TABLE, and so on,

Occurrences of these statements in a transaction will also commit the transaction's

    Transaction cannot nest transactions

    SavePoint of a transaction

SavePoint Pointname/rollback to SavePoint pointname

A transaction can set multiple savepoint, rollback can roll back to the specified savepoint and restore the operation after the savepoint.

If there is a subsequent save point and the previous name, delete the previous savepoint.

Release SavePoint Deletes a savepoint, and if a commit or rollback is executed in a transaction, the transaction ends, so the savepoint is deleted.

Set Transaction Design Database Isolation Level

SET [GLOBAL | SESSION] TRANSACTION Isolation Level

{READ Uncommitted | READ COMMITTED | Repeatable READ | SERIALIZABLE}

This statement is used to set the transaction isolation level for the next transaction or for the current session.

By default, set transaction sets the isolation level for the next transaction (not yet started).

If you use the Global keyword, the statement sets the global default transaction level,

Used to create all new connections from this point onwards. The original connection is not affected. The default transaction level can be set by using the session key test.

Used for all future transactions performed on the current connection.

The default rating is repeatable READ Global isolation level.

Ø notes

Select;     # single Comment

Select;     --Single line comment

Select 1/* Multiline Comment */+ 1;

Ø Basic Data type operation

String

    Select ' Hello ', ' "Hello" ', ' "" "Hello" "', ' Hel ' lo ', ' \ ' Hello ';

    Select "Hello", "' Hello '", "' Hello '", "Hel" "Lo", "\" Hello ";

\ nthe line break

    Select ' This\nis\nfour\nlines ';

\ Escape

    Select ' Hello \ world! ';

    Select ' Hello \world! ';

    Select ' Hello \ \ world! ';

    Select ' Hello \ ' world! ';

Ø Set Database mode mode

SET sql_mode= ' ansi_quotes ';

CREATE TABLE T (a int);

CREATE TABLE "TT" (a int);

CREATE TABLE "T" "T" (a int);

Craate talbe tab ("a" "B" int);

Ø User Variables

Ø Stored Procedures

To create a stored procedure:

Delimiter//

CREATE PROCEDURE get (out result int)

Begin

Select Max (age) to result from temp;

end//

To call a stored procedure:

Call Get (@temp);

Query Result:

Select @temp;

To delete a stored procedure:

drop procedure get;

To view the stored procedure creation statement:

Show CREATE PROCEDURE get;

Select...into can complete the assignment of single-Line records:

CREATE PROCEDURE Getrecord (SID int)

Begin

    DECLARE v_name varchar (default ' Jason ');

    declare v_age int;

    declare v_sex bit;

    Select name, age, sex to V_name, v_age, v_sex from temp where id = SID;

    Select V_name, V_age, V_sex;

End

Call Getrecord (1);

Ø function

Functions are similar to stored procedures, but are called in different ways

For example: select Max (age) from temp;

To create a function:

Create function addage (age int) returns INT

     return age + 5;

Using functions:

Select Addage (age) from temp;

To delete a function:

Drop function if exists addage;

Drop function addage;

Display creation Syntax:

Ø Cursors

Declaration cursor: Declare cur_name cursor FOR select Name from temp;

Opening cursors: Open cur_name;

FETCH cursor: Fetch cur_name into @temp;

Close cursor: close cur_name;

Example:

CREATE PROCEDURE cur_show ()

BEGIN

  DECLARE done INT DEFAULT 0;

  DECLARE v_id, V_age INT;

  DECLARE v_name varchar (20);

  DECLARE cur_temp CURSOR for SELECT ID, name, age from temp;

  DECLARE CONTINUE HANDLER for SQLSTATE ' 02000 ' SET done = 1;

  OPEN cur_temp;

  REPEAT

    FETCH cur_temp into v_id, V_name, v_age;

    IF not do Then

       IF IsNull (V_name) Then

          Update temp Set name = Concat (' Test-json ', v_id) where id = v_id;

       ELSEIF IsNull (v_age) Then

          Update temp Set age = where id = v_id;

       END IF;

    END IF;

  UNTIL done END REPEAT;

  CLOSE cur_temp;

END

Ø Trigger

Trigger is divided into insert, UPDATE, delete three types of trigger events

And after, before trigger time

To create a trigger:

Create Trigger Trg_temp_ins

Before insert

On temp for each row

Begin

INSERT into Temp_log values (new.id, new.name);

end//

To delete a trigger:

Drop Trigger Trg_temp_ins

MySQL Learning Note Two