Linux command: MySQL series of five--select single-table query, multi-table query upgrade and deletion, insert

SELECT: Query

SELECT select-list from TB WHERE qualification find corresponding rows based on standard qualification

Query Statement type: Qualification condition Field field distinct unique

Simple query:

Multi-Table query:

Sub-query:

SELECT * FROM Tb_name: Querying all information of the Tb_name table

SELECT field1,field2 from Tb_name: projection Displays the field entry (field), where a field is a column

SELECT [DISTINCT] * from Tb_name WHERE qualification; Choose unique, non-repeating entries from the Tb_name table that match the criteria.

FROM clause: table, multiple tables, other SELECT statements

WHERE clause: boolean relationship expression =, >, <, <=, >= = greater than or equal, less than equals, greater than, less than, equals.

650) this.width=650; "src=" https://s5.51cto.com/wyfs02/M01/92/A7/wKiom1kBnlGjlLquAAKXCZGPVtM384.jpg "title=" 1.jpg "alt=" Wkiom1kbnlgjllquaakxczgpvtm384.jpg "/>

Logical Relationship:    and (with &&) or (or | |)    Not (non!) XOR (different or)

mysql> SELECT Name,age,gender from students WHERE not age>20 and not gender= ' M '; #选择查询年龄不大于20, and gender is not a male data, can also be written as below conditions

mysql> SELECT Name,age,gender from students WHERE not (age>20 OR gender= ' M ');

Special Relationship: between ...  And ... Between the two.

Like ' '

%: Any character of any length

_: Any single character

Usage:select name from students WHERE Name is like ' Y% '; Look for data with the name field beginning with Y.

SELECT name from students WHERE name is like '%ing% '; Find fields in the Name field that must include ing

SELECT name from students WHERE name is like ' y___ '; find at least 3 characters after Y in the Name field

RegExp or rlike supports regular expressions:

usage:select name,age from students WHERE Name rlike ' ^[xy].*$ ';

Look for data with the name field beginning with X or Y

in discrete condition lookup:

usage:select name,age from students WHERE age in (18,20,25);

Find related data with age field 18 or 20 or 25 years old

have free value, compare: Is null, NOT null ORDER by ... To... A word orderby order

ORDER by ... To... Sort a field in descending order

usage:select name,age from students WHERE Name is NULL;

Find data with a null value in the Name field

SELECT name,age from students WHERE Name is NULL;

Finding data with a name field that is not a null value

Sort the found data in ascending or descending order: order by Field_name {asc| DESC};

ASC Ascending order (default value) desc Descending sort

SELECT Name,age from students WHERE the CID is not NULL ORDER by Name;

finds data that is not empty for the CID field and is sorted in ascending order by field name;

SELECT name,age from students WHERE CID was not NULL ORDER by Name DESC;

Finds data that is not empty in the CID field and is listed in descending order by field name;

Field aliases: As

Usage:select Name as student_name from Student;

mysql> SELECT Name from student;

+------------+

| Name |

+------------+

| Li Lianjie |

| Cheng Long |

| Yang Guo |

| Guo Jing |

+------------+

4 rows in Set (0.00 sec)

mysql> SELECT Name as Student_name from Student;

+--------------+

| Student_name | as alias Student_name

+--------------+

| Li Lianjie |

| Cheng Long |

| Yang Guo |

| Guo Jing |

+--------------+

4 rows in Set (0.00 sec)

Limit clause: limit [offset,]count offset offset, Count shows how much

Usage:select Name as Student_name from Student LIMIT 3;

Look for the display name alias Student_name, showing only the first 3 data

Usage:select Name as Student_name from Student LIMIT 2, 2;

Find Display name alias is Student_name, offset off the first 2 data is not displayed, display the 2nd data after 2 data

mysql> SELECT Name as Student_name from Student LIMIT 3;

+--------------+

| Student_name |

+--------------+

| Li Lianjie |

| Cheng Long |

| Yang Guo |

+--------------+

3 Rows in Set (0.00 sec)

mysql> SELECT Name as Student_name from Student LIMIT 2,2;

+--------------+

| Student_name |

+--------------+

| Yang Guo |

| Guo Jing |

+--------------+

2 rows in Set (0.00 sec)

Aggregation operations: SUM () sum, min () min, Max () max, AVG () average, count ();

Create a new table:

mysql> CREATE TABLE Class (ID INT NOT null PRIMARY KEY auto_increment,name CHAR (a) NOT null UNSIGNED, age TINYINT not NULL); /c4>

mysql> INSERT into Class (' Name,age ') VALUES (' Yang Guo '), (' Guo Jing ', ' Max '), (' Xiao longnv '), (' Huang Rong ', +);

mysql> DESC class;

+-------+------------+------+-----+---------+----------------+

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

+-------+------------+------+-----+---------+----------------+

| ID | Int (11) | NO | PRI | NULL | auto_increment |

| Name | char (20) |     NO | |                NULL | |

| Age | Tinyint (4) |     NO | |                NULL | |

+-------+------------+------+-----+---------+----------------+

3 Rows in Set (0.00 sec)

mysql> SELECT * from class;

+----+-------------+-----+

| ID | Name | Age |

+----+-------------+-----+

| 1 |  Yang Guo | 22 |

| 2 |  Guo Jing | 46 |

| 3 |  Xiao LONGNV | 18 |

| 4 |  Huang Rong | 40 |

+----+-------------+-----+

4 rows in Set (0.00 sec)

mysql> SELECT SUM (age) from class;

+----------+

| SUM (age) |

+----------+

| 126 |

+----------+

1 row in Set (0.02 sec)

mysql> SELECT MAX (age) from class;

+----------+

| MAX (age) |

+----------+

| 46 |

+----------+

1 row in Set (0.00 sec)

mysql> SELECT MIN (age) from class;

+----------+

| MIN (age) |

+----------+

| 18 |

+----------+

1 row in Set (0.00 sec)

mysql> SELECT AVG (age) from class;

+----------+

| AVG (age) |

+----------+

| 31.5000 |

+----------+

1 row in Set (0.00 sec)

Group: GROUP BY ... Having qualification according to ... Grouping and satisfying conditions qualification

The conditional selection for group by requires having a having as a condition filter, rather than using where

mysql> SELECT Age,gender from Class1 GROUP by Gender; #以性别分组显示

+-----+--------+

| Age | Gender |

+-----+--------+

| 18 | F |

| 22 | M |

+-----+--------+

2 rows in Set (0.00 sec)

mysql> SELECT AVG (age), Gender from Class1 GROUP by Gender; #求男同学和女同学的平均年龄.

+----------+--------+

| AVG (age) | Gender |

+----------+--------+

| 29.0000 | F |

| 34.0000 | M |

+----------+--------+

2 rows in Set (0.00 sec)

mysql> SELECT Name,avg (age) is the Name rlike ' y.* ' from the Class1 GROUP by Gender;

#求以Gender分组的年龄平局值, and displays only the average and name of the name field beginning with Y

+----------+----------+

| Name | AVG (age) |

+----------+----------+

|  Yang Guo | 34.0000 |

+----------+----------+

1 row in Set (0.00 sec)

This article is from the "Learn Linux history" blog, please be sure to keep this source http://woyaoxuelinux.blog.51cto.com/5663865/1920579

Linux command: MySQL series of five--select single-table query, multi-table query upgrade and deletion, insert