"Database" MySQL database (iv)

First, the operation of the data (detailed version)

1. Add Data
1> insert into table name (Field 1, field 2 ...) VALUES (value 1, value 2 ...);

2> insert into table name (Field 1, field 2 ...) VALUES (value 1, value 2 ...), (value 1, value 2 ...);

3> insert into table name values (value 1, value 2, value 3 ...);

4> insert into table name values (value 1, value 2, value 3 ...), (value 1, value 2, value 3 ...);

5> INSERT INTO Stu set id=833689,name= ' Xiaojie ', sex= ' W ', age=28,classid= ' lamp155 ';

2. Delete data

1> Delete from table name;

2> Delete from table name where condition;

3> truncate TABLE table name; Preserves the tables structure, clears the table data, and the ID index starts again from 1

3. Modify the data

1> Update table name set field name = modified value;//non-conditional causes all data to be modified

2> Update table name set field name = modified value where condition;//must add condition

3> Update table name set field name 1= value 1, field name 2= value 2 ... where condition;//Modify multiple strips

4. Querying data
①select main sentence
Select database ();//view the databases currently in use
Select version ();//view current MySQL versions
Select User ();//View the currently used users
Select 1+2;//3//View the results of the operation

Note: The SELECT main clause can execute some commands separately

②from clause

1> select * from table name;//query data from the specified table

2> Select field 1, Field 2 ... from table name;//The specified field information is isolated from the specified table

③where clause "* * * * The knowledge points applied to the query of the data to which the conditional restrictions apply"

Include conditions, arithmetic operators, logical operators, comparison operators

Arithmetic operators: +-*/%
Comparison operators:> < >= <=! = =
Logical operators: Logical OR (or | |), logic and (and &&), logical non (not!)
Precedence of the ascending operator: () parentheses

1. Search all data id<20 in the Stu table;
SELECT * from Stu where id<20;

2. Search for data in the Stu table between 20-40 IDs;
SELECT * from Stu where id>20 && id<40;
SELECT * from Stu where ID between and 40;

3. Search for the data in the Stu table in addition to 20-40, and the ID is less than 50;
SELECT * from Stu where (id<20 | | id>40) && id<50;
SELECT * from Stu where ID not between and 40;

4. Search for data in the Stu table in the ID (1,3,5,7,9);
SELECT * from Stu where id=1 or id=3 or id=5 or id=7 or id=9;
SELECT * from Stu where id<10 && id%2=1;
SELECT * from Stu where id<10 && id%2!=0;
SELECT * from Stu where ID in (1,3,5,7,9);

5. Search all data for id%2=0
SELECT * from Stu where id%2=0;

6. Search for the data in the Stu table whose name ends with 5 and the data whose ID is less than 50;
SELECT * from Stu where name '%5 '

7. Search for data with ID data beginning with 2 and ID less than 30 in Stu table;
SELECT * from Stu where id like ' 2% ' and id<30;

8. Search the Stu table in class class contains 183 of the data, and the ID is less than 50 of the data;
SELECT * from Stu where classid like '%175% ';

In keyword:

Querying data for a collection
Search the data in the Stu table for IDs in 1,3,5,7,9;

Search data in Stu table with ID not in 1,3,5,7,9, and ID is less than 20;

Between...and ... Key words:
Querying a range of data
Search for data in the Stu table with IDs between 20-40;

Search the Stu table for data in addition to 20-40, and the ID is less than 50;

Not keyword:
With some other keywords to use

Like keyword (fuzzy query):
%: Any length

_: One length

④group by grouping clauses:

Query out the ID in the Stu table in 50, grouped by class, and count the number of students in each class,
Data with statistics greater than 8 are displayed.

1. First query the data in the Stu table in the ID 50
Mysql> select * from Stu where id<50;
+----+------------+-----+-----+---------+
| ID | name | sex | Age | ClassID |
+----+------------+-----+-----+---------+
| 1 | Zhangsan1 | W | 23 | lamp177 |
| 2 | zhangsan2 | W | 28 | lamp176 |
| 3 | Zhangsan3 | W | 21 | lamp177 |
| 4 | Zhangsan4 | W | 19 | lamp180 |
| 5 | Zhangsan5 | W | 26 | lamp176 |
| 6 | Zhangsan6 | W | 24 | lamp180 |
..................

2. Next GROUP by class (grouped by that field, write the field to group by)
Mysql> select * from Stu where id<50 group by ClassID;
+----+------------+-----+-----+---------+
| ID | name | sex | Age | ClassID |
+----+------------+-----+-----+---------+
| 13 | Zhangsan13 | W | 28 | lamp175 |
| 2 | zhangsan2 | W | 28 | lamp176 |
| 1 | Zhangsan1 | W | 23 | lamp177 |
| 7 | Zhangsan7 | W | 18 | lamp178 |
| 33 | Zhangsan33 | W | 23 | lamp179 |
| 4 | Zhangsan4 | W | 19 | lamp180 |
+----+------------+-----+-----+---------+

Note: The information in front of ClassID is not related to the information after the group, it just gets each class for the first time
The message of the man who appeared was revealed!

3. Count the number of students in each class
Mysql> Select COUNT (*) from Stu where id<50 group by ClassID;
+----------+
| COUNT (*) |
+----------+
| 6 |
| 8 |
| 9 |
| 6 |
| 5 |
| 15 |
+----------+


4. The number of light is not enough, I need to know the corresponding class, which class
Mysql> Select Classid,count (*) from Stu where id<50 group by ClassID;
+---------+----------+
| ClassID | COUNT (*) |
+---------+----------+
| lamp175 | 6 |
| lamp176 | 8 |
| lamp177 | 9 |
| lamp178 | 6 |
| lamp179 | 5 |
| lamp180 | 15 |
+---------+----------+


5. After the end of the group, there is a grouped condition when the statistics number is greater than 6 display
Mysql> Select Classid,count (*) from Stu where Id<50 group by CLASSID have Count (*) >6;
+---------+----------+
| ClassID | COUNT (*) |
+---------+----------+
| lamp176 | 8 |
| lamp177 | 9 |
| lamp180 | 15 |
+---------+----------+


6. After the results are queried, change the alias of the Count (*) field to sum
Mysql> Select Classid,count (*) as sum from Stu where id<50 group by ClassID have sum>6;
+---------+-----+
| ClassID | Sum |
+---------+-----+
| lamp176 | 8 |
| lamp177 | 9 |
| lamp180 | 15 |
+---------+-----+

Exercises:

Query the ID&LT;30 data in the Stu table, and group by gender to count the number of boys and girls,
The field that gives the statistic number one is called sum

Select Sex,count (*) as sum from Stu where id<30 group by sex;



⑤order by: Sort clause "* * * * The knowledge points to be applied to the data sorting in the project period * * * *"

ASC: Sort Ascending (default)

Desc: Descending Sort

1. Query the data in the Stu table with IDs less than 50, sorted by age descending
SELECT * from Stu where id<50 order by age Desc;

⑥limit: Pagination Clause "* * * * knowledge points applied to the data paging query in the project period * * * *"

NOTE: The paging clause can be followed directly by a value, but we recommend writing two values

Limit 0, 5;
0: Starting from the first (indicates the subscript of the data)
5: Show several pages per page

Mysql> select * from Stu limit 0, 5;
+----+-----------+-----+-----+---------+
| ID | name | sex | Age | ClassID |
+----+-----------+-----+-----+---------+
| 1 | Zhangsan1 | m | 27 | lamp178 |
| 2 | zhangsan2 | m | 20 | lamp180 |
| 3 | Zhangsan3 | m | 22 | lamp175 |
| 4 | Zhangsan4 | W | 25 | lamp176 |
| 5 | Zhangsan5 | m | 28 | lamp178 |
+----+-----------+-----+-----+---------+
5 rows in Set (0.00 sec)

Mysql> select * from Stu limit 5, 5;
+----+------------+-----+-----+---------+
| ID | name | sex | Age | ClassID |
+----+------------+-----+-----+---------+
| 6 | Zhangsan6 | W | 28 | lamp176 |
| 7 | Zhangsan7 | W | 18 | lamp180 |
| 8 | Zhangsan8 | m | 27 | lamp178 |
| 9 | Zhangsan9 | W | 27 | lamp179 |
| 10 | Zhangsan10 | W | 22 | lamp177 |
+----+------------+-----+-----+---------+
5 rows in Set (0.00 sec)

Mysql> select * from Stu limit 10, 5;
+----+------------+-----+-----+---------+
| ID | name | sex | Age | ClassID |
+----+------------+-----+-----+---------+
| 11 | Zhangsan11 | W | 28 | lamp179 |
| 12 | Zhangsan12 | W | 19 | lamp177 |
| 13 | Zhangsan13 | m | 19 | lamp176 |
| 14 | Zhangsan14 | W | 19 | lamp178 |
| 15 | Zhangsan15 | W | 26 | lamp176 |
+----+------------+-----+-----+---------+
5 rows in Set (0.00 sec)

Mysql> select * from Stu limit 15, 5;
+----+------------+-----+-----+---------+
| ID | name | sex | Age | ClassID |
+----+------------+-----+-----+---------+
| 16 | Zhangsan16 | m | 26 | lamp175 |
| 17 | zhangsan17 | W | 19 | lamp178 |
| 18 | zhangsan18 | W | 18 | lamp177 |
| 19 | zhangsan19 | W | 23 | lamp179 |
| 20 | Zhangsan20 | m | 20 | lamp175 |
+----+------------+-----+-----+---------+
5 rows in Set (0.00 sec)

Some functions commonly used in ⑦ search statements

Count ();//Statistical function

Mysql> Select COUNT (*) from Stu;
+----------+
| COUNT (*) |
+----------+
| 100 |
+----------+

Max ();//ask for maximum value

Mysql> select Max (age) from Stu;
+----------+
| Max (age) |
+----------+
| 28 |
+----------+
1 row in Set (0.00 sec)

Min ();//Find minimum value

Mysql> Select min (age) from Stu;
+----------+
| Min (age) |
+----------+
| 18 |
+----------+
1 row in Set (0.00 sec)

sum ();//Sum

Mysql> select SUM (age) from Stu;
+----------+
| SUM (age) |
+----------+
| 2295 |
+----------+
1 row in Set (0.00 sec)

AVG ();//averaging

Mysql> Select AVG (age) from Stu;
+----------+
| AVG (age) |
+----------+
| 22.9500 |
+----------+
1 row in Set (0.00 sec)

⑧ subquery (nested query)

The result of one query statement is the condition of another query statement

Mysql> select * from Stu where age= (select Max (age) from Stu);
+-----+-------------+-----+-----+---------+
| ID | name | sex | Age | ClassID |
+-----+-------------+-----+-----+---------+
| 5 | Zhangsan5 | m | 28 | lamp178 |
| 6 | Zhangsan6 | W | 28 | lamp176 |
| 11 | Zhangsan11 | W | 28 | lamp179 |
| 49 | zhangsan49 | m | 28 | lamp177 |
| 68 | zhangsan68 | W | 28 | lamp178 |
| 87 | zhangsan87 | m | 28 | lamp180 |
| 99 | Zhangsan99 | W | 28 | lamp178 |
| 100 | zhangsan100 | W | 28 | lamp180 |
+-----+-------------+-----+-----+---------+

⑨ the order in which query statements are written

Select field information from table name where group before condition group By field information having group after condition order by sort field [Asc|desc] limit paging condition;

Select, from, where group by--

⑩ Association query (Multi-table) "* * * * Use this knowledge point for multi-table linking in the project period * * *"
When we query the data of a table to not be able to complete the function we want, we may need to use
Multi-Table Joint search Method!

Function:
I would like to inquire about the name of each person in the Red House table and the corresponding position;

1. Find associations: Watch multiple tables and see their relationships between 22 fields
Determine Who the field in the two table is related to

2. Write First: SELECT * from Stulist,job;

Mysql> select * from Stulist,job;
+----+------+-----+-----+-----+----+-----+------------+
| ID | name | sex | Age | Job | ID | Jid | name |
+----+------+-----+-----+-----+----+-----+------------+
| 1 | Kang Hui | m | 28 | 4 | 1 | 1 | The owner of Yee Hong Court |
| 1 | Kang Hui | m | 28 | 4 | 2 | 2 | Yee Hong Court |
| 1 | Kang Hui | m | 28 | 4 | 3 | 3 | Two Yi Hong Court |
| 1 | Kang Hui | m | 28 | 4 | 4 | 4 | Yee Hong Hospital Madam |
| 1 | Kang Hui | m | 28 | 4 | 5 | 5 | Yue Hong Yuan Turtle |
| 2 | Ding Hui | W | 38 | 2 | 1 | 1 | The owner of Yee Hong Court |
| 2 | Ding Hui | W | 38 | 2 | 2 | 2 | Yee Hong Court |
| 2 | Ding Hui | W | 38 | 2 | 3 | 3 | Two Yi Hong Court |
| 2 | Ding Hui | W | 38 | 2 | 4 | 4 | Yee Hong Hospital Madam |
| 2 | Ding Hui | W | 38 | 2 | 5 | 5 | Yue Hong Yuan Turtle |
| 3 | ET | W | 48 | 2 | 1 | 1 | The owner of Yee Hong Court |
| 3 | ET | W | 48 | 2 | 2 | 2 | Yee Hong Court |
| 3 | ET | W | 48 | 2 | 3 | 3 | Two Yi Hong Court |
| 3 | ET | W | 48 | 2 | 4 | 4 | Yee Hong Hospital Madam |
| 3 | ET | W | 48 | 2 | 5 | 5 | Yue Hong Yuan Turtle |
| 4 | Crape Myrtle | W | 58 | 3 | 1 | 1 | The owner of Yee Hong Court |
| 4 | Crape Myrtle | W | 58 | 3 | 2 | 2 | Yee Hong Court |
| 4 | Crape Myrtle | W | 58 | 3 | 3 | 3 | Two Yi Hong Court |
| 4 | Crape Myrtle | W | 58 | 3 | 4 | 4 | Yee Hong Hospital Madam |
| 4 | Crape Myrtle | W | 58 | 3 | 5 | 5 | Yue Hong Yuan Turtle |
| 5 | Seoul Kang | m | 68 | 5 | 1 | 1 | The owner of Yee Hong Court |
| 5 | Seoul Kang | m | 68 | 5 | 2 | 2 | Yee Hong Court |
| 5 | Seoul Kang | m | 68 | 5 | 3 | 3 | Two Yi Hong Court |
| 5 | Seoul Kang | m | 68 | 5 | 4 | 4 | Yee Hong Hospital Madam |
| 5 | Seoul Kang | m | 68 | 5 | 5 | 5 | Yue Hong Yuan Turtle |
| 6 | Brother Fei | m | 18 | 1 | 1 | 1 | The owner of Yee Hong Court |
| 6 | Brother Fei | m | 18 | 1 | 2 | 2 | Yee Hong Court |
| 6 | Brother Fei | m | 18 | 1 | 3 | 3 | Two Yi Hong Court |
| 6 | Brother Fei | m | 18 | 1 | 4 | 4 | Yee Hong Hospital Madam |
| 6 | Brother Fei | m | 18 | 1 | 5 | 5 | Yue Hong Yuan Turtle |
+----+------+-----+-----+-----+----+-----+------------+

3. Write conditions:
Mysql> SELECT * from Stulist,job where Stulist.job=job.jid;
+----+------+-----+-----+-----+----+-----+------------+
| ID | name | sex | Age | Job | ID | Jid | name |
+----+------+-----+-----+-----+----+-----+------------+
| 1 | Kang Hui | m | 28 | 4 | 4 | 4 | Yee Hong Hospital Madam |
| 2 | Ding Hui | W | 38 | 2 | 2 | 2 | Yee Hong Court |
| 3 | ET | W | 48 | 2 | 2 | 2 | Yee Hong Court |
| 4 | Crape Myrtle | W | 58 | 3 | 3 | 3 | Two Yi Hong Court |
| 5 | Seoul Kang | m | 68 | 5 | 5 | 5 | Yue Hong Yuan Turtle |
| 6 | Brother Fei | m | 18 | 1 | 1 | 1 | The owner of Yee Hong Court |
+----+------+-----+-----+-----+----+-----+------------+

4. Limit the content of the fields to be queried
Mysql> Select Stulist.name,job.name from Stulist,job where Stulist.job=job.jid;
+------+------------+
| name | name |
+------+------------+
| Kang Hui | Yee Hong Hospital Madam |
| Ding Hui | Yee Hong Court |
| ET | Yee Hong Court |
| Crape Myrtle | Two Yi Hong Court |
| Seoul Kang | Yue Hong Yuan Turtle |
| Brother Fei | The owner of Yee Hong Court |
+------+------------+

Note: Although the results we have found out! But the name is the same! So we need an alias.

5. Alias each of the two fields that can be aliased and use them
Mysql> Select Stulist.name as sname,job.name as Jname from Stulist,job where Stulist.job=job.jid;
+-------+------------+
| sname | Jname |
+-------+------------+
| Kang Hui | Yee Hong Hospital Madam |
| Ding Hui | Yee Hong Court |
| ET | Yee Hong Court |
| Crape Myrtle | Two Yi Hong Court |
| Seoul Kang | Yue Hong Yuan Turtle |
| Brother Fei | The owner of Yee Hong Court |
+-------+------------+

6. Alias the names of two tables and use them
Mysql> Select S.name as sname,j.name as Jname from Stulist as s,job as J where S.job=j.jid;
+-------+------------+
| sname | Jname |
+-------+------------+
| Kang Hui | Yee Hong Hospital Madam |
| Ding Hui | Yee Hong Court |
| ET | Yee Hong Court |
| Crape Myrtle | Two Yi Hong Court |
| Seoul Kang | Yue Hong Yuan Turtle |
| Brother Fei | The owner of Yee Hong Court |
+-------+------------+

7. All as can be omitted point, also represents the meaning of the alias
Mysql> Select S.name sname,j.name jname from Stulist S,job J where S.job=j.jid;
+-------+------------+
| sname | Jname |
+-------+------------+
| Kang Hui | Yee Hong Hospital Madam |
| Ding Hui | Yee Hong Court |
| ET | Yee Hong Court |
| Crape Myrtle | Two Yi Hong Court |
| Seoul Kang | Yue Hong Yuan Turtle |
| Brother Fei | The owner of Yee Hong Court |
+-------+------------+

⑩① field Stitching concat (): "Category management in Project period applies this knowledge point"

The effect of concat () is very similar to the one in PHP. We just need to stitch the field names
After writing the parentheses in the concat, it is possible to stitch all the contents of the field, as well as
string concatenation, so that we can put some of our own defined symbols to make the connection between multiple fields of content
Then, this is the concat () function

Mysql> Select concat (Name, '-', sex, '-', age) from Stulist;
+------------------------------+
| Concat (name, '-', sex, '-', age) |
+------------------------------+
| Kang-m-28 |
| Ding hui-w-38 |
| ET-w-48 |
| Crape Myrtle-w-58 |
| Seoul Kang-m-68 |
| Flying Brother-m-18 |
+------------------------------+


$a = "Hello";
$b = "world!";
$c = $a. $b;//hello world!

"Database" MySQL database (iv)