MySQL database query operation

# # MySQL Database

# # # Knowledge Points:

1. Single-table query
2. Sub-query
3. Linked Table Query
4. Business

Before we make a query, we need to build the relational table and insert some data into the data table. Prepare for the query operation.

##### Five creation of a relational table:

"MySQL
#创建并进入数据库:
mysql> CREATE DATABASE ' info ';
Query OK, 1 row Affected (0.00 sec)
mysql> use ' info ';
Database changed

#创建学院表:
mysql> CREATE TABLE ' Department ' (
' id ' INT PRIMARY KEY auto_increment,
"Name" VARCHAR () not NULL
);
Query OK, 0 rows affected (0.69 sec)
#创建学生表:
mysql> CREATE TABLE ' Students ' (
' s_id ' INT PRIMARY KEY auto_increment,
' Name ' VARCHAR () not NULL,
' d_id ' INT,
-FOREIGN KEY (' d_id ') REFERENCES ' Department ' (' ID ')
);
Query OK, 0 rows affected (0.65 sec)
#创建学生的详细信息表:
mysql> CREATE TABLE ' stu_details ' (
' s_id ' INT PRIMARY KEY,
' Age ' INT,
' Gender ' CHAR (1)
,
FOREIGN KEY (' s_id ') REFERENCES ' students ' (' s_id ')
);
Query OK, 0 rows affected (0.55 sec)
#创建课程表:
mysql> CREATE TABLE ' Course ' (
' id ' INT PRIMARY KEY auto_increment,
"Name" VARCHAR () not NULL
);
Query OK, 0 rows affected (0.50 sec)
#创建中间表:
mysql> CREATE TABLE ' select ' (
' s_id ' INT,
' c_id ' INT,
-PRIMARY KEY (' s_id ', ' c_id '),
FOREIGN KEY (' s_id ') REFERENCES ' students ' (' s_id '),
-FOREIGN KEY (' c_id ') REFERENCES ' course ' (' ID ')
);
Query OK, 0 rows affected (0.84 sec)
#查看当前存在的表:
Mysql> SHOW TABLES;
+----------------+
| Tables_in_info |
+----------------+
| Course |
| Department |
| Select |
| Stu_details |
| Students |
+----------------+
5 rows in Set (0.00 sec)
```

##### adding data to a data table

"MySQL
#往学院表中添加数据:
mysql> INSERT into ' Department ' (' name ')
VALUES (' foreign Languages '),
(' Art '),
(' computer '),
(' Chemical ')
;
Query OK, 4 rows affected (0.11 sec)
Records:4 duplicates:0 warnings:0
#往学生表中添加数据:
Mysql> INSERT into ' students ' (' name ', ' d_id ')
VALUES (' Xiao Ming ', 1),
(' Little Red ', 3),
(' Floret ', 3),
(' Little New ', 4)
;
Query OK, 4 rows affected (0.09 sec)
Records:4 duplicates:0 warnings:0
#往学生详细信息表中添加数据:
Mysql> INSERT into Stu_details
VALUES (1,18, ' m '),
(4,20, ' m '),
(3,16, ' f '),
(2,19, ' F ')
;
Query OK, 4 rows affected (0.11 sec)
Records:4 duplicates:0 warnings:0
#往课程表中添加数据:
Mysql> INSERT into ' course '
(' name ') VALUES
(' psychology '),
(' Buddhism '),
(' recent history '),
(' Music Appreciation ')
;
Query OK, 4 rows affected (0.08 sec)
Records:4 duplicates:0 warnings:0
#查看中间表的结构:
mysql> DESC ' SELECT ';
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| s_id | Int (11) | NO | PRI | NULL | |
| c_id | Int (11) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in Set (0.03 sec)
#往中间表中添加数据
mysql> INSERT into ' select '
VALUES,
(1,4),
(2,1),
(2,4),
(4,1),
(4,2),
(bis)
;
Query OK, 7 rows affected (0.06 sec)
Records:7 duplicates:0 warnings:0
```

# # Query

##### querying All records

' SELECT * from Tb_name; '

##### Querying selected Column records

' SELECT col_name1,col_name2 from Tb_name; `

##### querying for records under specified conditions

' SELECT col_name from tb_name WHERE condition '

##### the column alias after querying

' SELECT col_name as new_name from Tab_name '

"MySQL
#查询所有记录:
Mysql> SELECT * from ' students ';
+------+--------+------+
| s_id | name | d_id |
+------+--------+------+
| 1 | Xiao Ming | 1 |
| 2 | Little Red | 3 |
| 3 | Small Flower | 3 |
| 4 | Xiao Xin | 4 |
+------+--------+------+
4 rows in Set (0.00 sec)

#查询选中列记录
Mysql> SELECT name,d_id from students;
+--------+------+
| name | d_id |
+--------+------+
| Xiao Ming | 1 |
| Little Red | 3 |
| Small Flower | 3 |
| Xiao Xin | 4 |
+--------+------+
4 rows in Set (0.00 sec)

#查询指定条件下的记录
Mysql> SELECT * from students WHERE ' name ' = ' Little Red ';
+------+--------+------+
| s_id | name | d_id |
+------+--------+------+
| 2 | Little Red | 3 |
+------+--------+------+
1 row in Set (0.00 sec)

#查询后为列取别名
mysql> SELECT name as ' name ', d_id as Academy ID from students WHERE s_id>=2;
+--------+----------+
| name | Academy ID |
+--------+----------+
| Little Red | 3 |
| Small Flower | 3 |
| Xiao Xin | 4 |
+--------+----------+
3 Rows in Set (0.00 sec)
```

##### sort ' ORDER by '

' ASC ' ascending (default) ' DESC ' descending

"MySQL
#查询学生的选修表 (intermediate table)
Mysql> SELECT * from ' SELECT ';
+------+------+
| s_id | c_id |
+------+------+
| 2 | 1 |
| 4 | 1 |
| 1 | 2 |
| 4 | 2 |
| 1 | 4 |
| 2 | 4 |
| 4 | 4 |
+------+------+
7 Rows in Set (0.00 sec)

#按学生学号升序输出
Mysql> SELECT * from ' SELECT ' ORDER by ' s_id ';
+------+------+
| s_id | c_id |
+------+------+
| 1 | 2 |
| 1 | 4 |
| 2 | 1 |
| 2 | 4 |
| 4 | 1 |
| 4 | 2 |
| 4 | 4 |
+------+------+
7 Rows in Set (0.00 sec)

#按课程id降序输出:
Mysql> SELECT * from ' SELECT ' ORDER by ' c_id ' DESC;
+------+------+
| s_id | c_id |
+------+------+
| 4 | 4 |
| 2 | 4 |
| 1 | 4 |
| 4 | 2 |
| 1 | 2 |
| 4 | 1 |
| 2 | 1 |
+------+------+
7 Rows in Set (0.00 sec)
```

##### limit the number of displayed data ' limit '

"MySQL
#按学生学号升序输出的前4条数据
Mysql> SELECT * from ' SELECT ' ORDER by ' s_id ' LIMIT 4;
+------+------+
| s_id | c_id |
+------+------+
| 1 | 2 |
| 1 | 4 |
| 2 | 1 |
| 2 | 4 |
+------+------+
4 rows in Set (0.00 sec)
#指定的返回的数据的位置和数量
Mysql> SELECT * from ' SELECT ' ORDER by ' s_id ' LIMIT 4, 2;
+------+------+
| s_id | c_id |
+------+------+
| 4 | 1 |
| 4 | 2 |
+------+------+
2 rows in Set (0.00 sec)
```

##### packet query ' GROUP BY '

```
Cases:
To group students in the college column and to count the number of students in the school:
Mysql> SELECT d_id as college Id,count (d_id) as student number from students GROUP by ' d_id ';
+----------+--------------+
| Academy ID | Number of Students |
+----------+--------------+
| 1 | 1 |
| 3 | 2 |
| 4 | 1 |
+----------+--------------+
3 Rows in Set (0.00 sec)

Having group conditions
After having the field must be a select after the occurrence of the
Mysql> SELECT d_id as college Id,count (d_id) as student number from students GROUP by ' d_id ' have student number = 1;
+----------+--------------+
| Academy ID | Number of Students |
+----------+--------------+
| 1 | 1 |
| 4 | 1 |
+----------+--------------+
2 rows in Set (0.01 sec)
```

Some of the more common functions in ###### queries

"MySQL
#求最大年龄
mysql> SELECT MAX (' age ') from ' stu_details ';
+------------+
| MAX (' Age ') |
+------------+
| 20 |
+------------+
1 row in Set (0.03 sec)
#求最小年龄
mysql> SELECT MIN (' age ') from ' stu_details ';
+------------+
| MIN (' Age ') |
+------------+
| 16 |
+------------+
1 row in Set (0.00 sec)
#求和
mysql> SELECT SUM (' age ') from ' stu_details ';
+------------+
| SUM (' Age ') |
+------------+
| 73 |
+------------+
1 row in Set (0.05 sec)
#求平均数
mysql> SELECT AVG (' age ') from ' stu_details ';
+------------+
| AVG (' Age ') |
+------------+
| 18.2500 |
+------------+
1 row in Set (0.00 sec)
```

##### Sub-query

The select sentence that appears in other SQL statements.

1) nested inside the query
2) must always appear within parentheses
3) can contain multiple keywords or conditions

"MySQL
#查找出大于平均年龄的数据
Mysql> SELECT * from ' stu_details ' WHERE's ' age ' >18.25;
+------+------+--------+
| s_id | Age | Gender |
+------+------+--------+
| 2 | 19 | f |
| 4 | 20 | m |
+------+------+--------+
2 rows in Set (0.00 sec)
#将平均数的SQL语句作为子查询放入上一条语句中
Mysql> select * from ' stu_details ' WHERE's ' Age ' > (select AVG (' Age ') from ' stu_details ');
+------+------+--------+
| s_id | Age | Gender |
+------+------+--------+
| 2 | 19 | f |
| 4 | 20 | m |
+------+------+--------+
2 rows in Set (0.10 sec)
```

# # # Table Query

##### internal connection ' [inner| Cross] JOIN '

Unconditional Internal connection:
Unconditional internal connection, aka Cross connect/Cartesian connection
Each direction of the first table is combined with each of the other tables

Connected within a condition
On the basis of an unconditional inner join, add an ON clause
When connecting, filter out rows of meaningful records for stitching

Note whether the column names of two tables are the same when writing conditions,
If the time is the same, you need to precede the table name, tb_name.colname this form exists

"MySQL
#无条件内连接:
Mysql> SELECT * from ' students ' INNER JOIN ' department ';
+------+--------+------+----+-----------+
| s_id | name | d_id | ID | name |
+------+--------+------+----+-----------+
| 1 | Xiao Ming | 1 | 1 | Foreign language |
| 2 | Little Red | 3 | 1 | Foreign language |
| 3 | Small Flower | 3 | 1 | Foreign language |
| 4 | Xiao Xin | 4 | 1 | Foreign language |
| 1 | Xiao Ming | 1 | 2 | Art |
| 2 | Little Red | 3 | 2 | Art |
| 3 | Small Flower | 3 | 2 | Art |
| 4 | Xiao Xin | 4 | 2 | Art |
| 1 | Xiao Ming | 1 | 3 | Computer |
| 2 | Little Red | 3 | 3 | Computer |
| 3 | Small Flower | 3 | 3 | Computer |
| 4 | Xiao Xin | 4 | 3 | Computer |
| 1 | Xiao Ming | 1 | 4 | Chemical Industry |
| 2 | Little Red | 3 | 4 | Chemical Industry |
| 3 | Small Flower | 3 | 4 | Chemical Industry |
| 4 | Xiao Xin | 4 | 4 | Chemical Industry |
+------+--------+------+----+-----------+
Rows in Set (0.04 sec)
#有条件内连接:
Mysql> SELECT * from ' students ' INNER JOIN ' department '
-On D_id=id;
+------+--------+------+----+-----------+
| s_id | name | d_id | ID | name |
+------+--------+------+----+-----------+
| 1 | Xiao Ming | 1 | 1 | Foreign language |
| 2 | Little Red | 3 | 3 | Computer |
| 3 | Small Flower | 3 | 3 | Computer |
| 4 | Xiao Xin | 4 | 4 | Chemical Industry |
+------+--------+------+----+-----------+
4 rows in Set (0.03 sec)
```

Conditional outer Joins:
{left| Right} [OUTER] JOIN

Left Outer connection:
When two tables are connected, the connection conditions do not match.
Leave the data in the left table, and the data in the right table is filled with null

Right outer connection
When connecting to two tables, when the connection condition does not match
Leave the data in the right table, and the data in the left table is filled with null

"MySQL
#往学生表中添加数据, just add a name
Mysql> INSERT into students (name)
VALUES (' Xixi ');
Query OK, 1 row affected (0.11 sec)
#查看所有学生表数据
Mysql> SELECT * from StudentS;
+------+--------+------+
| s_id | name | d_id |
+------+--------+------+
| 1 | Xiao Ming | 1 |
| 2 | Little Red | 3 |
| 3 | Small Flower | 3 |
| 4 | Xiao Xin | 4 |
| 5 | Xixi | NULL |
+------+--------+------+
5 rows in Set (0.00 sec)
#使用内连接加条件只能看到有分配好学院的学生的信息;
Mysql> SELECT * FROM students INNER JOIN Department
-On D_id=id;
+------+--------+------+----+-----------+
| s_id | name | d_id | ID | name |
+------+--------+------+----+-----------+
| 1 | Xiao Ming | 1 | 1 | Foreign language |
| 2 | Little Red | 3 | 3 | Computer |
| 3 | Small Flower | 3 | 3 | Computer |
| 4 | Xiao Xin | 4 | 4 | Chemical Industry |
+------+--------+------+----+-----------+
4 rows in Set (0.02 sec)
#使用左连接把学生的数据全取出来, the student has no college information with null padding
Mysql> SELECT * FROM students left JOIN department
-On D_id=id;
+------+--------+------+------+-----------+
| s_id | name | d_id | ID | name |
+------+--------+------+------+-----------+
| 1 | Xiao Ming | 1 | 1 | Foreign language |
| 2 | Little Red | 3 | 3 | Computer |
| 3 | Small Flower | 3 | 3 | Computer |
| 4 | Xiao Xin | 4 | 4 | Chemical Industry |
| 5 | Xixi | NULL | NULL | NULL |
+------+--------+------+------+-----------+
5 rows in Set (0.00 sec)
#使用右外连接把目前还没有学生的学院的数据也显示出来
Mysql> SELECT * FROM students right JOIN department
-On D_id=id;
+------+--------+------+----+-----------+
| s_id | name | d_id | ID | name |
+------+--------+------+----+-----------+
| 1 | Xiao Ming | 1 | 1 | Foreign language |
| 2 | Little Red | 3 | 3 | Computer |
| 3 | Small Flower | 3 | 3 | Computer |
| 4 | Xiao Xin | 4 | 4 | Chemical Industry |
| NULL | NULL | NULL | 2 | Art |
+------+--------+------+----+-----------+
5 rows in Set (0.00 sec)

Mysql>
```

##### optimization of Query sql

MySQL Order of execution

"MySQL
1.FROM: Calculates the Cartesian product on the left table of the From and the table on the right. Generating a virtual table VT1

2.ON: On filtering the virtual table VT1, only those rows that conform to <join-condition> are recorded in the virtual table VT2.

3.JOIN: If a outer join is specified (such as a LEFT JOIN or right join), rows that are not matched in the reserved table are added to the virtual table VT2 as an outer row, resulting in a virtual table VT3, which contains more than two tables in the rug FROM clause. Then the result of the previous join connection VT3 and the next table repeat steps three steps, until all the tables have been processed.

4.WHERE: WHERE conditional filtering is performed on virtual table VT3. Only records that match <where-condition> will be inserted into the virtual table VT4.

5.GROUP by: The records in VT4 are grouped according to the columns in the GROUP BY clause, resulting in VT5.

6.CUBE | ROLLUP: A cube or ROLLUP operation is performed on the table VT5, resulting in a table VT6.

7.HAVING: Apply a have filter to the virtual table VT6, only records that match

8.SELECT: Perform a select operation, select the specified column, and insert into the virtual table VT8.

9.DISTINCT: VT8 the records in the Generates virtual table VT9.

10.ORDER by: Sorts the records in the virtual table VT9 according to <order_by_list>, generating the virtual table VT10.

11.LIMIT: Takes out the record of the specified row, generates the virtual table VT11, and returns the result.
```

It is not difficult to think of the above execution order, if the SQL execution is faster, you must put the filter conditions as far forward as possible. As follows:

"MySQL
SELECT
S. ' Name ',
E. ' Name '
From
' Students ' s
Left JOIN (
SELECT
Se. ' s_id ',
C. ' Name '
From
' Select ' SE
JOIN ' course ' C on se. ' c_id ' = c. ' ID '
E on S. ' id ' =e. ' stu_id '

SELECT
*
From
' Student ' s
WHERE
S. ' Dep_id ' = (
SELECT
' ID '
From
' Department ' d
WHERE
D. ' Name ' = ' Foreign Language Institute '
)
```

In these two examples, the child table in the first SQL is queried only once, but in the second SQL, the Sub-table is executed n times, which depends on the number of data bars in the student table, and if the data size of the child table is large, then SQL executes very slowly.

This is typically done in order to optimize SQL, in addition to SQL execution faster, should try to avoid fuzzy matching, such as: Like,in,not in and other these matching conditions.

There are a few suggestions for you:

1. Avoid whole-table scans as much as possible, such as SELECT *

2. Create an appropriate index

3. Use the appropriate storage engine

4. In join, try to use small table left join large table

5. Unless necessary, try not to use order By,group by and DISTINCT (de-weight), try to use an index instead

##### transactions

To ensure that updates to database records change from one consistent state to another
It is very necessary to use transactions for processing.

```
Cases:
Create a table of bank accounts
mysql> CREATE TABLE ' account ' (
' id ' INT PRIMARY KEY auto_increment,
' Name ' VARCHAR () not NULL,
' Balance ' INT
);
Query OK, 0 rows affected (0.52 sec)
Add two user and user's deposit information
Mysql> INSERT into ' account ' (' name ', ' balance ')
VALUES (' Shangdian ', 10000),
(' Xiaoming ', 2000)
;
Query OK, 2 rows affected (0.09 sec)
Records:2 duplicates:0 warnings:0

Suppose now the user Xiao Ming in the store to buy 500 yuan things, now to transfer to the store, then you need to subtract 500 from the account of the small Ming, and then add 500 to the store users, but if the process of reducing 500 in the system failure, and then restart after the discovery of Xiao Ming's money buckle, but the store did not receive, There will be inconsistent data changes. For this kind of data modification we need to either modify the success at the same time, or modify the failure at the same time, so this needs to be done with a transaction.

Start TRANSACTION: Starting a new transaction
Commit: Commit the current transaction and make a permanent change
ROLLBACK: Rollback current transaction, discard modification

Mysql> START TRANSACTION;
Query OK, 0 rows Affected (0.00 sec)

mysql> UPDATE ' account '
SET ' balance ' = ' balance '-50
--WHERE ' name ' = ' xiaoming '
;
Query OK, 1 row affected (0.04 sec)
Rows matched:1 changed:1 warnings:0

Use rollback to make changes to the data do not take effect back to the state before the transaction:
Mysql> ROLLBACK;
Query OK, 0 rows affected (0.06 sec)

Do a proper operation:
Mysql> START TRANSACTION;
Query OK, 0 rows Affected (0.00 sec)

mysql> UPDATE ' account '
SET ' balance ' = ' balance '-50
--WHERE ' name ' = ' xiaoming '
;
Query OK, 1 row affected (0.03 sec)
Rows matched:1 changed:1 warnings:0

mysql> UPDATE ' account '
SET ' balance ' = ' balance ' +50
-
--WHERE ' name ' = ' Shangdian '
;
Query OK, 1 row Affected (0.00 sec)
Rows matched:1 changed:1 warnings:0

Mysql> SELECT * from ' account ';

Mysql> COMMIT;
Query OK, 0 rows affected (0.07 sec)

After the commit, the data modified successfully, rollback can not return to the previous.

Mysql> ROLLBACK;
Query OK, 0 rows Affected (0.00 sec)
```

MySQL database query operation