Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the MySQL connection methods". In the daily operation, I believe that many people have doubts about the MySQL connection methods. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts about "what are the MySQL connection methods?" Next, please follow the editor to study!

I. connection

Use the following two tables to test:

Mysql > desc users1

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | uid | tinyint (3) unsigned | YES | | NULL |

| | uname | varchar (255) | YES | | NULL |

| | gid | tinyint (3) unsigned | YES | | NULL |

+-+ +

3 rows in set (0.06 sec)

Mysql > desc groups1

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | gid | tinyint (3) unsigned | YES | | NULL |

| | gname | varchar (255) | YES | | NULL |

+-+ +

2 rows in set (0.06 sec)

[@ more@] mysql > select * from users1

+-+

| | uid | uname | gid | |

+-+

| | 0 | root | 0 | |

| | 201 | yuegao | 101 | |

| | 202 | fengsong | 102 | |

| | 201 | yuegao | 200 | |

+-+

4 rows in set (0.00 sec)

Mysql > select * from groups1

+-+ +

| | gid | gname |

+-+ +

| | 0 | root |

| | 101 | dba |

| | 200 | guest |

+-+ +

3 rows in set (0.00 sec)

1. Cross connection

Mysql > select * from users1 cross join groups1

+-+

| | uid | uname | gid | gid | gname | |

+-+

| | 0 | root | 0 | 0 | root |

| | 0 | root | 0 | 101 | dba |

| | 0 | root | 0 | 200 | guest |

| | 201 | yuegao | 101 | 0 | root |

| | 201 | yuegao | 101 | 101 | dba |

| | 201 | yuegao | 101 | 200 | guest |

| | 202 | fengsong | 102 | 0 | root |

| | 202 | fengsong | 102 | 101 | dba |

| | 202 | fengsong | 102 | 200 | guest |

| | 201 | yuegao | 200 | 0 | root |

| | 201 | yuegao | 200 | 101 | dba |

| | 201 | yuegao | 200 | 200 | guest |

+-+

12 rows in set (0.00 sec)

Or

Mysql > select * from users1, groups1

+-+

| | uid | uname | gid | gid | gname | |

+-+

| | 0 | root | 0 | 0 | root |

| | 0 | root | 0 | 101 | dba |

| | 0 | root | 0 | 200 | guest |

| | 201 | yuegao | 101 | 0 | root |

| | 201 | yuegao | 101 | 101 | dba |

| | 201 | yuegao | 101 | 200 | guest |

| | 202 | fengsong | 102 | 0 | root |

| | 202 | fengsong | 102 | 101 | dba |

| | 202 | fengsong | 102 | 200 | guest |

| | 201 | yuegao | 200 | 0 | root |

| | 201 | yuegao | 200 | 101 | dba |

| | 201 | yuegao | 200 | 200 | guest |

+-+

12 rows in set (0.00 sec)

two。 Internal connection

Mysql > select * from users1 inner join groups1 on users1.gid = groups1.gid

+-+

| | uid | uname | gid | gid | gname | |

+-+

| | 0 | root | 0 | 0 | root |

| | 201 | yuegao | 101 | 101 | dba |

| | 201 | yuegao | 200 | 200 | guest |

+-+

3 rows in set (0.00 sec)

If the connection column name is the same, you can use the following form:

Mysql > select * from users1 inner join groups1 using (gid)

+-+

| | gid | uid | uname | gname | |

+-+

| | 0 | 0 | root | root | |

| | 101 | 201 | yuegao | dba | |

| | 200 | 201 | yuegao | guest | |

+-+

3 rows in set (0.00 sec)

In this way, only one join column of the two tables will be displayed. And the most commonly used form:

Mysql > select * from users1, groups1 where users1.gid = groups1.gid

+-+

| | uid | uname | gid | gid | gname | |

+-+

| | 0 | root | 0 | 0 | root |

| | 201 | yuegao | 101 | 101 | dba |

| | 201 | yuegao | 200 | 200 | guest |

+-+

3 rows in set (0.00 sec)

3. External connection

Left outer connection:

Mysql > select * from users1 left join groups1 on users1.gid = groups1.gid

+-+

| | uid | uname | gid | gid | gname | |

+-+

| | 0 | root | 0 | 0 | root |

| | 201 | yuegao | 101 | 101 | dba |

| | 202 | fengsong | 102 | NULL | NULL | |

| | 201 | yuegao | 200 | 200 | guest |

+-+

4 rows in set (0.00 sec)

If the connection column name is the same, you can use the following form:

Mysql > select * from users1 left join groups1 using (gid)

+-+

| | gid | uid | uname | gname | |

+-+

| | 0 | 0 | root | root | |

| | 101 | 201 | yuegao | dba | |

| | 102 | 202 | fengsong | NULL | |

| | 200 | 201 | yuegao | guest | |

+-+

4 rows in set (0.00 sec)

This displays only the join columns of the table on the left.

Right outer connection:

Mysql > select * from users1 right join groups1 on users1.gid = groups1.gid

+-+

| | uid | uname | gid | gid | gname | |

+-+

| | 0 | root | 0 | 0 | root |

| | 201 | yuegao | 101 | 101 | dba |

| | 201 | yuegao | 200 | 200 | guest |

+-+

3 rows in set (0.00 sec)

If the connection column name is the same, you can use the following form:

Mysql > select * from users1 right join groups1 using (gid)

+-+

| | gid | gname | uid | uname | |

+-+

| | 0 | root | 0 | root |

| | 101 | dba | 201 | yuegao |

| | 200 | guest | 201 | yuegao |

+-+

3 rows in set (0.00 sec)

This displays only the join columns of the table on the right.

External connections are used in conjunction with grouping to list the groups to which each member belongs and the members each group contains:

Mysql > select u.uname, group_concat (g.gname separator',') from users1 as u left join groups1 as g u

Sing (gid) group by u.uid

+-- +

| | uname | group_concat (g.gname separator',') |

+-- +

| | root | root |

| | yuegao | dba,guest |

| | fengsong | NULL |

+-- +

3 rows in set (0.00 sec)

Mysql > select g.gname, group_concat (u.uname separator',') from users1 as u right join groups1 as g

Using (gid) group by g.gid

+-- +

| | gname | group_concat (u.uname separator',') |

+-- +

| | root | root |

| | dba | yuegao |

| | guest | yuegao |

+-- +

3 rows in set (0.00 sec)

4. Self-connection

Mysql > select * from processes1

+-+

| | pid | pname | ppid | |

+-+

| | 1 | init | 0 | |

| | 2915 | crond | 1 | |

| | 3020 | hald | 1 | |

| | 3021 | hald-runner | 3020 | |

| | 4707 | gnome-terminal | 1 | |

| | 4709 | gnome-pty-helper | 4707 | |

| | 4710 | bash | 4707 | |

+-+

7 rows in set (0.00 sec)

Make a self-join query on the above table and return the process name and its parent process name:

Mysql > select a.pname as pname, b.pname as ppname from processes1 as a, processes1 as b where a.ppid

= b.pid

+-+ +

| | pname | ppname |

+-+ +

| | crond | init |

| | hald | init |

| | gnome-terminal | init |

| | hald-runner | hald |

| | gnome-pty-helper | gnome-terminal |

| | bash | gnome-terminal |

+-+ +

6 rows in set (0.00 sec)

Mysql > select a.pname as pname, b.pname as ppname from processes1 as a left join processes1 as b on

A.ppid = b.pid

+-+ +

| | pname | ppname |

+-+ +

| | init | NULL |

| | crond | init |

| | hald | init |

| | hald-runner | hald |

| | gnome-terminal | init |

| | gnome-pty-helper | gnome-terminal |

| | bash | gnome-terminal |

+-+ +

7 rows in set (0.00 sec)

II. Combination

Mysql > select * from groups1

+-+ +

| | gid | gname |

+-+ +

| | 0 | root |

| | 101 | dba |

| | 200 | guest |

+-+ +

3 rows in set (0.02 sec)

Mysql > create table groups2 as select * from groups1 where 0 = 1

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > insert into groups2 values (102,' vip')

Query OK, 1 row affected (0.00 sec)

Perform a joint query on groups1 and groups2:

Mysql > select * from groups1 union select * from groups2

+-+ +

| | gid | gname |

+-+ +

| | 0 | root |

| | 101 | dba |

| | 200 | guest |

| | 102 | vip |

+-+ +

4 rows in set (0.00 sec)

Mysql > select * from groups1 where gid 0 union select * from groups2

+-+ +

| | gid | gname |

+-+ +

| | 101 | dba |

| | 200 | guest |

| | 102 | vip |

+-+ +

3 rows in set (0.00 sec)

To make a union, two basic conditions must be met:

1 > the number of fields returned by each query must be the same.

2 > the data types of the fields returned by each query must match each other.

The UNION operator automatically eliminates duplicate records in the union, including duplicate records in the same table:

Mysql > insert into groups1 values (0, 'root')

Query OK, 1 row affected (0.00 sec)

Mysql > insert into groups2 values (0, 'root')

Query OK, 1 row affected (0.00 sec)

Mysql > select * from groups1 union select * from groups2

+-+ +

| | gid | gname |

+-+ +

| | 0 | root |

| | 101 | dba |

| | 200 | guest |

| | 102 | vip |

+-+ +

4 rows in set (0.00 sec)

To return all records in the union, you can use UNION ALL:

Mysql > select * from groups1 union all select * from groups2

+-+ +

| | gid | gname |

+-+ +

| | 0 | root |

| | 101 | dba |

| | 200 | guest |

| | 0 | root |

| | 102 | vip |

| | 0 | root |

+-+ +

6 rows in set (0.00 sec)

3. Sub-query

Subqueries are used in different ways:

1 > used in a WHERE or HAVING clause.

2 > is used with comparison and logical operators.

3 > use the testing equipment with IN members.

4 > used with EXISTS Boolean tests.

5 > used in a FROM clause.

6 > used with the connection.

7 > used with UPDATE and DELETE queries.

MySQL does not allow you to delete or update the same table data while reading data using a subquery:

Mysql > update users1 set gid = 200where gid in (select users1.gid from users1 left join groups1 usi

Ng (gid) where groups1.gname is null)

ERROR 1093 (HY000): You can't specify target table 'users1' for update in FROM clause

The main advantages of subqueries are:

1 > structure the query and isolate the parts of the statement.

2 > provides another way to perform operations that require complex joins and federations.

3 > in many people's minds, subqueries are more readable than joins or unions.

However, subqueries can overload RDBMS and significantly degrade performance, especially in the case of xrefs (), which should be used with caution.

In most cases, subqueries can be converted to joins, as in the following example (using the statement in oracle to view tablespace usage, just to illustrate):

Select

A.tablespace_name tsname

Round (a.bytesd2) Total_Mb

Round (a. MAXsizepar 2) MAXSIZE_Gb

Round (b.bytesMagne2) Free_Mb

(1-(b.bytes/a.bytes)) * 100 Pct_used

From

(

Select tablespace_name,sum (MAXBYTES/1024/1024/1024) MAXsize,sum (bytes) / 1024 plus 1024 bytes

From dba_data_files

Group by tablespace_name

) a

(

Select tablespace_name,sum (bytes) / 1024 Universe 1024 bytes

From dba_free_space

Group by tablespace_name

) b

Where a.tablespace_name = b.tablespace_name

Order by Pct_used

Can be converted to:

Select

A.tablespace_name tsname

Round (sum (a.bytes) / 1024 take 1024) Total_Mb

Round (sum (a.maxbytes/1024/1024/1024), 2) MAXSIZE_Gb

Round (sum (b.bytes) / 1024 take 1024) Free_Mb

(1-(sum (b.bytes) / sum (a.bytes) * 100 Pct_used

From dba_data_files a, dba_free_space b where a.tablespace_name = b.tablespace_name

Group by a.tablespace_name order by Pct_used

IV. Affairs

1. Transaction related statement

The START TRANSACTION statement is used to initialize a transaction, or you can use a BEGIN or BEGIN WORK statement.

Use the commit statement to confirm all changes, or use the rollback statement to undo all changes.

MySQL uses a flat transaction model: nested transactions are not allowed, and starting a new transaction automatically commits the previous transaction. Other statements also hide the execution of a COMMIT command:

1 > DROP DATABASE/DROP TABLE

2 > CREATE INDEX/DROP INDEX

3 > ALTER TABLE/RENAME TABLE

4 > LOCK TABLES/UNLOCK TABLES

5 > SET AUTOCOMMIT=1

In addition, MySQL also supports savepoints, which I will not repeat here.

two。 Control transaction behavior

The AUTOCOMMIT variable specifies whether autocommit mode is turned on. The default value is 1. MySQL treats each statement as a single statement transaction.

Mysql > select @ @ autocommit

+-+

| | @ @ autocommit |

+-+

| | 1 |

+-+

1 row in set (0.03 sec)

The TRANSACTION ISOLATION LEVEL variable specifies the isolation level of the transaction. The default is REPEATABLE READ.

Mysql > select @ @ tx_isolation

+-+

| | @ @ tx_isolation |

+-+

| | REPEATABLE-READ |

+-+

1 row in set (0.00 sec)

The four different levels that can be set are, in order of strictness, from high to low:

1 > SERIALIZABLE (serialization)

2 > REPEATABLE READ (repeatable)

3 > READ COMMITTED (submitted read)

4 > READ UNCOMMITTED (uncommitted reads)

You can set this variable as follows:

Mysql > set transaction isolation level read uncommitted

Query OK, 0 rows affected (0.05 sec)

Mysql > select @ @ tx_isolation

+-+

| | @ @ tx_isolation |

+-+

| | READ-UNCOMMITTED |

+-+

1 row in set (0.00 sec)

You can also modify the value of the tx_isolation variable directly:

Mysql > set tx_isolation='REPEATABLE-READ'

Query OK, 0 rows affected (0.00 sec)

Mysql > select @ @ tx_isolation

+-+

| | @ @ tx_isolation |

+-+

| | REPEATABLE-READ |

+-+

1 row in set (0.00 sec)

3. Transactions and performanc

When using transaction table types, there are some ways to reduce the impact on database performance.

1) use small transactions

KISS principle-Keep It Simple, Stupid!

Make transactions as small as possible and change and exit quickly so that transactions in other queues are not unduly delayed:

1 > make sure that all required user input has been completed before issuing the START TRANSACTION command.

That is, do not cause unnecessary delays by waiting for user input after the start of the transaction.

2 > try to divide large transactions into smaller transactions and execute them separately.

2) choose the appropriate isolation level

The higher the isolation level (strict), the greater the impact on performance. Choosing which isolation level is based on the application's fault tolerance and the impact of potential data errors, the default REPEATABLE READ is applicable in most cases.

3) avoid deadlock

InnoDB table handlers have built-in intelligence to check for deadlocks and resolve deadlocks by undoing one of the transactions (or releasing locks) when deadlocks are found.

There are many things developers can do at the application level to avoid deadlocks: get all the locks they need at the beginning of the session; always process the tables in the same order; and if RDBMS undoes the transaction when resolving the deadlock, use the built-in recovery program to re-execute the transaction.

At this point, the study of "what are the ways to connect with MySQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.