DWL and DCL statements of SQL statements

DWL and DCL statements of SQL statements

=

Overview:

This chapter will mainly introduce the use of DWL and DCL simple statements in SQL statements, as follows:

INSERT (add), DELETE (delete), UPDATE (change), SELECT (check) of DWL statement

Detailed explanation of SELECT single table query statement

The execution flow of the SELECT statement:

Fields can use aliases

WHERE clause:

GROUP BY, grouping

Aggregate function

HAVING, which performs conditional filtering on the result of grouping aggregation

Simple use of DCL statements

GRANT (authorization), REVOKE (recall rights)

=

DWL statement of SQL statement

-INSERT,SELECT,DELETE,UPDATE

1.INSERT adds insert data

★ syntax:

INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...), (...),...

Demo:

MariaDB [mydb] > desc tbl1 +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | smallint (5) unsigned | NO | PRI | NULL | auto_increment | | name | char (30) | NO | MUL | NULL | | age | tinyint (3) unsigned | YES | | NULL | ClassID | tinyint (3) unsigned | NO | | NULL | | gender | enum ('F') | 'M') | YES | | M | | +-+-+ 5 rows in set (0.01sec) MariaDB [mydb] > insert into tbl1 (name,ClassID) value (' tom',1) # add a classmate named tom in Class 1, Query OK, 1 row affected (0.02 sec) MariaDB [mydb] > select * from tbl1 # see as follows: +-+ | id | name | age | ClassID | gender | +-+ | 1 | tom | NULL | 1 | M | +-+- -+ 1 row in set (0.00 sec) MariaDB [mydb] > insert into tbl1 (name) ClassID) value ('tao',1), (' xiu',2) # you can add multiple Query OK at a time. 2 rows affected (0.04 sec) Records: 2 Duplicates: 0 Warnings: 0MariaDB [mydb] > select * from tbl1 +-+ | id | name | age | ClassID | gender | +-- + | 1 | tom | NULL | 1 | M | 2 | tao | NULL | 1 | M | 3 | xiu | | NULL | 2 | M | +-+ 3 rows in set (0.00 sec) MariaDB [mydb] > insert into tbl1 value (4) | 'wang',25,5,default) # directly specify an increase in value, but each field must have a value of Query OK, 1 row affected (0.02 sec) MariaDB [mydb] > select * from tbl1 +-+ | id | name | age | ClassID | gender | +-- + | 1 | tom | NULL | 1 | M | 2 | tao | NULL | 1 | M | 3 | xiu | | NULL | 2 | M | | 4 | wang | 25 | 5 | M | +-+ 4 rows in set (0.00 sec) |

2.DELETE delete data

★ syntax:

☉ DELETE FROM tbl_name

[WHERE where_condition]

Demo:

MariaDB [mydb] > select * from tbl1 +-+ | id | name | age | ClassID | gender | +-- + | 1 | tom | NULL | 1 | M | 2 | tao | NULL | 1 | M | 3 | xiu | | NULL | 2 | M | | 4 | wang | 25 | 5 | M | +-+ 4 rows in set (0.00 sec) MariaDB [mydb] > delete from tbl1 where ClassID = 5 | # Delete the line Query OK with a ClassID of 5, 1 row affected (0.00 sec) MariaDB [mydb] > select * from tbl1 +-+ | id | name | age | ClassID | gender | +-- + | 1 | tom | NULL | 1 | M | 2 | tao | NULL | 1 | M | 3 | xiu | | NULL | 2 | M | +-+ 3 rows in set (0.00 sec) |

3.UPDATE modifies data

★ syntax:

☉ UPDATE table_reference SET col_name1= {expr1 | DEFAULT} [, col_name2= {expr2 | DEFAULT}].

[WHERE where_condition]

Demo:

MariaDB [mydb] > update tbl1 set age=18 where id=2; # modify the line age=18Query OK of id=2, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0MariaDB [mydb] > select * from tbl1 +-+ | id | name | age | ClassID | gender | +-- + | 1 | tom | NULL | 1 | M | 2 | tao | 18 | 1 | M | 3 | Xiu | NULL | 2 | M | +-+ 3 rows in set (0.00 sec) MariaDB [mydb] > update tbl1 set age=18 ClassID=3 where id=1 # modify the line age of id=2 = 18 classIDers 3 query OK, 1 row affected (0.05 sec) Rows matched: 1 Changed: 1 Warnings: 0MariaDB [mydb] > select * from tbl1 +-+ | id | name | age | ClassID | gender | +-- + | 1 | tom | 18 | 3 | M | 2 | tao | 18 | 1 | M | | 3 | | xiu | NULL | 2 | M | +-+ 3 rows in set (0.00 sec) |

4.SELECT: query

★ Query Cache: cache the execution results of the query

☉ key: hash value of the query statement

☉ value: the execution result of the query statement

How ☉ SQL statements are written:

To develop the same style of writing, uppercase should be uppercase and lowercase should be lowercase to facilitate query cache.

★ query execution path:

Request-- > query cache

Request-- > query cache-- > parser-- > preprocessor-- > optimizer-- > query execution engine-- > storage engine-- > cache-- > response.

The execution flow of the ★ SELECT statement:

FROM-- > WHERE (conditional filtering)-- > Group By (grouping aggregation)-- > Having (adding restrictions on grouping statistics)-- > Order BY (sorting)-- > SELECT-- > Limit (limiting the number of rows to be displayed after data processing)

Select is used to select fields, while where is used to select rows; mysql relational database is a row-level database in which all data is fetched first, and then key fields are retained after row filtering

★ single table query:

☉ SELECT

☉ statement usage:

◆ SELECT * FROM tbl_name

Query all rows of a specified table (must not be available in a production environment)

◆ SELECT col1, col2,... FROM tble_name

The query specifies the fields in which the table meets the criteria (fields are fields for all rows)

Field alias: col1 AS ALIAS

◆ SELECT col1, col2,... FROM tble_name WHERE clause

Filter the row with the specified criteria before selecting the field

◆ SELECT col1, col2,... FROM tble_name [WHERE clause] GROUP BY col_name [HAVING clause]

Group with the specified fields, aggregate after grouping, and then filter the aggregated results using the having clause

◆ SELECT col1,... FROM tbl1_name [WEHRE CLAUSE] ORDER BY col1, col2,... [ASC | DESC]

Sort by specified field after query (default is ascending order)

★ options:

☉ DISTINCT: data deduplication

☉ SQL_CACHE: explicitly specifies the results of cached query statements

☉ SQL_NO_CACHE: explicitly specifies that the results of query statements are not cached

The ☉ query_cache_type server variable has three values:

ON: enablin

SQL_NO_CACHE: no cache; all cache conditions are cached by default

OFF: off

DEMAND: caching on demand

SQL_CACHE: cached; not cached by default

The ☉ field can use an alias:

Col1 AS alias1, col2 AS alias2,...

☉ WHERE clause: indicates the filter condition to achieve the select function

◆ filter condition: Boolean expression

◆ [WHERE where_condition]

Arithmetic operator: +, -, *, /,%

Comparison operator: =,! =, >, > =, select name,gender from tbl1 # query the qualified fields of the specified table +-+-+ | name | gender | +-+-+ | tom | M | | tao | M | xiu | M | wang | M | +-+-+ 4 rows in set (0.00 sec) MariaDB [mydb] > select name AS StuName,gender from tbl1 # definition field alias +-+-+ | StuName | gender | +-+-+ | tom | M | tao | M | M | xiu | M | wang | M | +-+-+ 4 rows in set (0.00 sec) MariaDB [mydb] >

2.where clause demo: Classe

MariaDB [mydb] > select * from tbl1 +-+ | id | name | age | ClassID | gender | +-- + | 1 | tom | NULL | 1 | M | 2 | tao | NULL | 1 | M | 3 | xiu | | NULL | 2 | M | | 4 | wang | 25 | 5 | M | +-+ 4 rows in set (0.00 sec) MariaDB [mydb] > select * from tbl1 where ClassID > 2 | # query +-+ | id | name | age | ClassID | gender | +-+ | 4 | wang | 25 | 5 | M | + -+ 1 row in set (0.00 sec) MariaDB [mydb] > select * from tbl1 where ClassID > = 2 and ClassID select * from tbl1 where ClassID between 2 and 5 # ditto Another way to write between...and..+----+ | id | name | age | ClassID | gender | +-- + | 3 | xiu | NULL | 2 | M | 4 | wang | 25 | | 5 | M | +-+ 2 rows in set (0.00 sec) MariaDB [mydb] > select name from tbl1 where name like'% o%'| # matches the name with o in name +-+ | name | +-+ | tom | | tao | +-+ 2 rows in set (0.01 sec) MariaDB [mydb] > select name from tbl1 where name like'% u' # match the names ending with u in name +-+ | name | +-+ | xiu | +-+ 1 row in set (0.00 sec) MariaDB [mydb] > select name from tbl1 where name rlike'^. * o.roommates' # regular expressions match +-+ | name | +-+ | tom | | tao | +-+ 2 rows in set (0.00 sec) MariaDB [mydb] > select name,ClassID from tbl1 where ClassID in (1) # find +-+-+ in a given list element | name | ClassID | +-+-+ | tom | 1 | tao | 1 | xiu | 2 | +-+-+ 3 rows in set (0.00 sec)

3. Sort

MariaDB [mydb] > select * from tbl1 +-+ | id | name | age | ClassID | gender | +-- + | 1 | tom | NULL | 1 | M | 2 | tao | NULL | 1 | M | 3 | xiu | | NULL | 2 | M | | 4 | wang | 25 | 5 | M | +-+ 4 rows in set (0.00 sec) MariaDB [mydb] > select * from tbl1 order by ClassID | # sort by ClassID ascending order +-- + | id | name | age | ClassID | gender | +-- + | 1 | tom | NULL | 1 | M | 2 | tao | NULL | 1 | M | | 3 | xiu | NULL | 2 | M | 4 | wang | 25 | 5 | M | +-+ 4 rows in set MariaDB [mydb] > select * from tbl1 order by ClassID Name # if the ClassID are the same, sort them in name ascending order: +-- + | id | name | age | ClassID | gender | +-+ | 2 | tao | NULL | 1 | M | 1 | tom | | NULL | 1 | M | 3 | xiu | NULL | 2 | M | 4 | wang | 25 | 5 | M | +-+ 4 rows in set (0.03 sec) MariaDB [mydb] > select * from tbl1 order by ClassID | Name DESC # if the ClassID are the same, sort them in name descending order: +-- + | id | name | age | ClassID | gender | +-+ | 1 | tom | NULL | 1 | M | 2 | tao | NULL | 1 | M | 3 | xiu | NULL | 2 | M | 4 | wang | 25 | 5 | M | +-+ 4 rows in set (0.02 sec) MariaDB [mydb] > select * from tbl1 order by ClassID DESC # sort +-+ | id | name | age | ClassID | gender | +-+ | 4 | wang | 25 | 5 | M | 3 | xiu | NULL | in descending order of ClassID 2 | M | | 1 | tom | NULL | 1 | M | | 2 | tao | NULL | 1 | M | +-+ 4 rows in set (0.00 sec) # in descending order of ClassID If it is the same, sort MariaDB [mydb] > select * from tbl1 order by ClassID DESC,name DESC in name descending order. +-+ | id | name | age | ClassID | gender | +-- + | 4 | wang | 25 | 5 | M | | 3 | xiu | NULL | 2 | M | | 2 | | | tao | NULL | 1 | M | | 1 | tom | NULL | 1 | M | +-+ 4 rows in set (0.00 sec) |

1.select pick field and where pick row

MariaDB [testdb] > show tables;+-+ | Tables_in_testdb | +-+ | tbl1 | | tbl2 | +-+ 2 rows in set (0.00 sec) MariaDB [testdb] > select id from tbl2 # select only pick fields +-+ | id | +-+ | 1 | | 2 | | 3 | +-+ 3 rows in set (0.00 sec) MariaDB [testdb] > select * from tbl2 # display all fields +-+ | id | name | age | +-+ | 1 | tom | 21 | 2 | tao | 15 | 3 | jing | 22 | +-+ 3 rows in set (0.00 sec) MariaDB [testdb] > select * from tbl2 where age > = 20 # where pick line +-+ | id | name | age | +-+ | 1 | tom | 21 | 3 | jing | 22 | +-+ 2 rows in set (0.00 sec) MariaDB [testdb] > select name,age from tbl2 where age > = 20 # pick fields and lines +-+-+ | name | age | +-+-+ | tom | 21 | jing | 22 | +-+-+

two。 For the selected data packet Group By, the purpose of the packet is to aggregate

MariaDB [testdb] > alter table tbl2 add gender enum ('Flying Magazine M'); # add a gender field Query OK, 3 rows affected (0.03 sec) Records: 3 Duplicates: 0 Warnings: 0MariaDB [testdb] > select * from tbl2 +-+ | id | name | age | gender | +-+ | 1 | tom | 21 | NULL | | 2 | tao | 15 | NULL | | 3 | jing | 22 | NULL | +-- -+-+ 3 rows in set (0.00 sec) MariaDB [testdb] > update tbl2 set gender='M' where id=1 # set its value Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0MariaDB [testdb] > update tbl2 set gender='M' where id=2;Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0MariaDB [testdb] > update tbl2 set gender='F' where id=3;Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0MariaDB [testdb] > select * from tbl2 +-+ | id | name | age | gender | +-+ | 1 | tom | 21 | M | 2 | tao | 15 | M | 3 | jing | 22 | F | +-+-+ -+-+ 3 rows in set (0.00 sec) MariaDB [testdb] > select * from tbl2 group by gender # grouping tbl2 by gender (divided into as many groups as there are several values) +-+ | id | name | age | gender | +-+ | 3 | jing | 22 | F | 1 | tom | 21 | M | +-- -+ 2 rows in set (0.01sec) MariaDB [testdb] > select avg (age) Gender from tbl2 group by gender # calculate the average value of each packet +-+-+ | avg (age) | gender | +-+-+ | 22.0000 | F | 18.0000 | M | +-+-+ 2 rows in set (0.00 sec) MariaDB [testdb] > select sum (age), gender from tbl2 group by gender # Sum +-+-+ for each packet | sum (age) | gender | +-+-+ | 22 | F | 36 | M | +-+-+ 2 rows in set (0.00 sec) MariaDB [testdb] > select avg (age) as avg_age,gender from tbl2 group by gender # define aliases +-+-+ for avg_age | avg_age | gender | +-+-+ | 22.0000 | F | | 18.0000 | M | +-+-+ 2 rows in set (sec)

3. Specify the filter condition HAVING for the aggregated result

MariaDB [testdb] > select avg (age) as avg_age,gender from tbl2 group by gender having avg_age > = 20 sec + | avg_age | gender | +-+-+ | 22.0000 | F | +-+-+ 1 sec)

4. Data deduplication

MariaDB [testdb] > select gender from tbl2;+-+ | gender | +-+ | M | | M | F | +-+ 3 rows in set (0.00 sec) MariaDB [testdb] > select distinct gender from tbl2 # remove weight +-+ | gender | +-+ | M | | F | +-+ 2 rows in set (0.00 sec) MariaDB [testdb] > SHOW VARIABLES LIKE'% cache%' # parameters related to cache cache +-- +-- + | Variable_name | Value | +-- +-- + | aria_pagecache_age_threshold | 134217728 | aria_pagecache_buffer_size | 134217728 | aria_pagecache_division_limit | 32768 | binlog_cache_size | 32768 | binlog_stmt_cache_size | 32768 | | have_query _ cache | YES | | join_cache_level | 2 | key_cache_age_threshold | 300 | | key_cache_block_size | 1024 | key_cache_division_limit | 100 | key_cache_segments | 0 | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_stmt_cache_size | 1844674407709547520 | metadata_locks_cache_size | 1024 | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | query_cache_size | 0 | # is 0 | No cache space | query_cache_strip_comments | OFF | | query_cache_type | ON | # cache is on | query_cache_wlock_invalidate | OFF | | stored_program_cache | 256 | table_definition_cache | 400 | | table_open _ cache | 400 | | thread_cache_size | 0 | +-- +-- + 24 rows in set (0.00 sec)

=

DCL statement of SQL statement

GRANT (authorization), REVOKE (recall rights)

Command demonstration:

1. Authorize a user to only allow local login

[root@centos7 bbs] # mysql-p134296 # does not specify a user, and the default is to log in to mysqlWelcome to the MariaDB monitor using the root user. Commands end with; or\ g.Your MariaDB connection id is 6Server version: 5.5.44-MariaDB MariaDB ServerCopyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.MariaDB [(none)] > CREATE DATABASE ultrax; # create database Query OK, 1 row affected (0.00 sec) # authorized user name is ultraxuser, only local login is allowed to access ultrax database, password is 134296MariaDB [(none)] > GRANT ALL ON ultrax.* TO 'ultraxuser'@'localhost' IDENTIFIED BY' 134296 database query OK, 0 rows affected (0.00 sec) MariaDB [(none)] > FLUSH PRIVILEGES # overload permission table Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] >

two。 Authorize a user and host that can log in remotely

[root@centos7] # mysqlWelcome to the MariaDB monitor. Commands end with; or\ g.Your MariaDB connection id is 2Server version: 5.5.44-MariaDB MariaDB ServerCopyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.Type 'help;' or'\ h' for help. The authorized user of Type'\ c'to clear the current input statement.# is test, and the host is any host in the network segment 10.1. All databases and tables are allowed to be accessed with a password of testpassMariaDB [(none)] > GRANT all ON *. * TO 'test'@'10.1.%.%' IDENTIFIED BY' testpass'; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)] > FLUSH PRIVILEGES;Query OK, 0 rows affected (0 sec) mysql > create database mydb # create a database Query OK, 1 row affected (0.00 sec) mysql > show databases +-+ | Database | +-+ | information_schema | | mydb | | mysql | | performance_schema | | test | +-+ 5 rows in set (0.01 sec) MariaDB [(none)] > exitBye