Database mysql/mariadb Knowledge Point--Operation chapter (4) Data operation statement

Insert Inserts Data

Heads-Up record insertion

INSERT into Tb_name (col1,col2,...) VALUES (Val1,val2,...);

Example:

INSERT INTO tablename (Field 1 name, field 2 name,......) VALUES (Field 1 value, field 2 value,...)

MariaDB [testdb]> INSERT Students (Id,name,ages,gender) VALUES (1, ' Tom ', +, ' M '); MariaDB [testdb]> INSERT Students (name,ages,gender) VALUES (' Jerry ', +, ' M '); MariaDB [testdb]> INSERT Students (name,ages,gender) VALUES (' Maria ', ' n ', ' M '); MariaDB [testdb]> INSERT students SET name= ' Ouyangfeng ', ages=56,gender= ' M ';

Multiple record insertions

INSERT into Tb_name (col1,col2,...) VALUES (Val1,val2,...) [, (Val1,val2,...),...];

MariaDB [testdb]> INSERT Students (name,ages,gender) VALUES (' Xiaolongnv ', ', ' F '), (' Dongfangbubai ', ' f ');

MariaDB [testdb]> SELECT * from students;+----+---------------+------+--------+| ID | Name          | ages | gender |+----+---------------+------+--------+|  1 | Tom           |   26 | M      | |  2 | Jerry         |   19 | M      | |  3 | Maria         |   19 | M      | |  4 | XIAOLONGNV    |   18 | F      | |  5 | Dongfangbubai |   28 | F      | |  6 | Ouyangfeng    |   56 | M      |+----+---------------+------+--------+

Query data from other tables is saved to this table

MariaDB [testdb]> ALTER TABLE students ADD address TEXT;  #加个字段做测试用

MariaDB [testdb]> INSERT Students (name,address) SELECT user,host from Mysql.user;

MariaDB [testdb]> SELECT * from students;+----+---------------+------+--------+-----------+| ID | Name          | ages | gender | Address   |+----+---------------+------+--------+-----------+|  1 | Tom           |   26 | M      | NULL      | |  2 | Jerry         |   19 | M      | NULL      | |  3 | Maria         |   19 | M      | NULL      | |  4 | XIAOLONGNV    |   18 | F      | NULL      | |  5 | Dongfangbubai |   28 | F      | NULL      | |  6 | Ouyangfeng    |   56 | M      | NULL      | |  7 | Root          |    0 | NULL   | 127.0.0.1 | |  8 | Root          |    0 | NULL   |:: 1       | |  9 |               |    0 | NULL   | centos7   | | | root          |    0 | NULL   | centos7   |    | | | 0 | NULL   | localhost | | | | root          |    0 | NULL   | localhost |+----+---------------+------+--------+-----------+

UPDATE modifies data

UPDATE tbl_name SET col1=value1,col2=value2,... WHERE Col=value;

Modify the data, mainly through the WHERE clause given the modification, but the where sentence example can refer to select common statements;

Example:

MariaDB [testdb]> UPDATE students SET gender= ' F ' WHERE id=3;

Delete Deletes data

Delete the statement is relatively simple, mainly through the WHERE clause to give the deletion scope, and where the example can refer to the SELECT statement, before deleting please make sure that the conditions do not have any problems, in the uncertain circumstances do not arbitrarily delete the data.

MariaDB [testdb]> DELETE from students WHERE name= ';  #删除名字为空的记录MariaDB [testdb]> TRUNCATE TABLE user;  #情况表记录

Note: Be sure to have a restriction (WHERE | LIMIT), otherwise the specified field of all rows will be modified

Select data query Basic Query 1, choose

Example:

Query Maria for information

MariaDB [testdb]> SELECT * from students WHERE name= ' Maria ';

Find information for students from 2 to 5th

MariaDB [testdb]> SELECT * from students WHERE ID between 2 and 5;

Find information on Jerry and XIAOLONGNV

MariaDB [testdb]> SELECT * from students WHERE name in (' Jerry ', ' Xiaolongnv ');

Query for information that is not of an empty age

MariaDB [testdb]> SELECT * from students WHERE gender are not NULL;

Query For information containing ' O ' in name

MariaDB [testdb]> SELECT * from students WHERE name is like '%o% ';

2. Projection

Add an alias to a field when querying

MariaDB [testdb]> SELECT user as, host as master, password as password from Mysql.user;

3. Grouping

Example

Check the average age of boys and girls

MariaDB [testdb]> SELECT Gender,avg (ages) from students GROUP by gender;

Show only average age information for boys

MariaDB [testdb]> SELECT Gender,avg (ages) from students GROUP by gender have gender= ' M ';

4. Sorting

Example

Sorted by age, reverse display

MariaDB [testdb]> SELECT * from students ORDER by ages DESC;

Sorted by age, filtered by age greater than 0, sorted in positive order, top three records

MariaDB [testdb]> SELECT * from students WHERE ages > 0 ORDER by ages LIMIT 3;

5. Common Parameters

1 As : Aliases2 WHERE: Specify filtering criteria to implement the "select" Function3+, -, *, /, %: Arithmetic Operators4=,! =, <>,, <, >=, <=: Comparison Operators5 between Min_num and Max_num: between Min_num and Max_mun6 In (Element1,element2,...) : in Element ... In the7 is null: Empty8 Is isn't null: not empty9 like: Make a match, as ... Ten%: Any character of any length One _: Single arbitrary character A rlike: Regular expressions, not recommended - REGEXP: Ibid . - Not , and, OR, XOR: logical operator the GROUP By: "Group" Query results according to specified criteria for "aggregation" operations - AVG () Average - Max () max number - min () Minimum number + count () statistics - sum () sum + Having: Specifies the filter condition for the result of the grouping aggregation operation. Like where, but can only be used in groups A ORDER By: Sort at ASC: Positive order, default - DESC: Reverse --keyword: In the sort order before the keyword plus-you can avoid putting null in front of the line -limit [[Offset,]row_count]: Limits the number of output rows for the results of a query

Parameters

Multi-Table Query

Before we start a multi-table query, we extend the previous table manually

MariaDB [testdb]> DELETE from students WHERE ID between 7 and 12; MariaDB [testdb]> CREATE TABLE score (ID TINYINT (2) UNSIGNED auto_increment PRIMARY key,score TINYINT (3)); MariaDB [testdb]> ALTER TABLE students ADD Sid TINYINT (2); MariaDB [testdb]> UPDATE students SET sid=6 WHERE id=6; MariaDB [testdb]> INSERT score SET score=87; MariaDB [testdb]> SELECT * from students;+----+---------------+------+--------+---------+------+| ID | name | Ages | Gender | Address |  Sid |+----+---------------+------+--------+---------+------+| 1 |   Tom | 26 | M |    NULL |  1 | | 2 |   Jerry | 19 | M |    NULL |  2 | | 3 |   Maria | 19 | F |    NULL |  3 | | 4 |   XIAOLONGNV | 18 | F |    NULL |  4 | | 5 |   Dongfangbubai | 28 | F |    NULL |  5 | | 6 |   Ouyangfeng | 56 | M |    NULL |   6 |+----+---------------+------+--------+---------+------+mariadb [testdb]> SELECT * from score; +----+-------+| Id |  Score |+----+-------+|    1 |  99 | |    2 |  98 | |    3 |  88 | |    4 |  68 | |    5 |  78 | |    6 | |+----+-------+

1, two tables to take intersection

MariaDB [testdb]> SELECT * from students as s,score as O WHERE s.sid=o.id;

2. Join on: Cross Connect

MariaDB [testdb]> SELECT * from students JOIN score;

3. INNER join on: Internal connection

MariaDB [testdb]> SELECT T.name,s.score from students as T INNER joins score as S on t.sid=s.id;+---------------+------- +| Name          | score |+---------------+-------+| Tom           |    99 | | Jerry         |    98 | | Maria         |    88 | | XIAOLONGNV    |    68 | | Dongfangbubai |    78 | | Ouyangfeng    |    |+---------------+-------+

4. Left OUTER join on: Outer connection

MariaDB [testdb]> SELECT T.name,s.score from students as T left joins score as s on T.sid=s.id;  #左外连接 +---------------+-------+| Name          | score |+---------------+-------+| Tom           |    99 | | Jerry         |    98 | | Maria         |    88 | | XIAOLONGNV    |    68 | | Dongfangbubai |    78 | | Ouyangfeng    |    |+---------------+-------+

5. Right OUTER join on: Left outer connection

MariaDB [testdb]> SELECT * from students as T right joins score as s on T.sid=s.id;  #右外连接

6. UNION on: Full outer connection

MariaDB [testdb]> Select name,address from Students UNION,    select User,host from mysql.user;+--------------- +-----------+| Name          | address   |+---------------+-----------+| Tom           | NULL      | | Jerry         | NULL      | | Maria         | NULL      | | XIAOLONGNV    | NULL      | | dongfangbubai | NULL      | | Ouyangfeng    | NULL      | | root |          127.0.0.1 | | root |          :: 1       | |               | CENTOS7   | | root          | CENTOS7   | |               | localhost | | Root          | localhost |+---------------+-----------+

7. Self-connected

mariadb [testdb]> ALTER TABLE students ADD tid TINYINT (2); #再加一个tid字段MariaDB [testdb]> SELECT * from students;+----+---------------+------+--------+---------+------+------ +| ID | name | Ages | Gender | Address | Sid |  Tid |+----+---------------+------+--------+---------+------+------+| 1 |   Tom | 26 | M |    NULL |    1 |  2 | | 2 |   Jerry | 19 | M |    NULL |    2 |  1 | | 3 |   Maria | 19 | F |    NULL |    3 |  4 | | 4 |   XIAOLONGNV | 18 | F |    NULL |    4 |  5 | | 5 |   Dongfangbubai | 28 | F |    NULL |    5 |  4 | | 6 |   Ouyangfeng | 56 | M |    NULL |    6 | 4 |+----+---------------+------+--------+---------+------+------+ 

MariaDB [testdb]> SELECT S1.name as studentname,s2.name as teachername from students as S1 INNER joins students as S2 on s1.id=s2.tid;+---------------+---------------+| Studentname   | teachername   |+---------------+---------------+| Jerry         | Tom           | | Tom           | Jerry         | | XIAOLONGNV |    Maria         | | dongfangbubai | XIAOLONGNV    | | XIAOLONGNV    | dongfangbubai | | XIAOLONGNV    | Ouyangfeng    |+---------------+---------------+

Sub-query

Subquery: Query statement nested in the query statement, poor performance, based on a statement of query results again query

1. Subqueries used in the WHERE clause

• Used to compare subqueries in an expression; a subquery can only return a single value

Example:

Query more than the average age of students

• Subquery for in: subquery should single-key query and return one or more values from the constituent list

2. For subqueries in the FROM clause

SELECT Tb_alias.col1,... From (SELECT clause) as Tb_alias WHERE clause;

Database mysql/mariadb Knowledge Point--Operation chapter (4) Data operation statement