3、 MySQL basic application

Time:2022-1-12

1、 MySQL built-in functions

Connect to database

-U specify login user 
-P enter password
-S specifies the sock connection
-H specify IP address for remote connection
-P the specified port is 3306 by default
-E. viewing the database without interaction
< recover data
example:
1. mysql -uroot -p -S /tmp/mysql.sock
2. mysql -uroot -p -h10.0.0.51 -P3306
3. -e no interactive execution of SQL statements
mysql -uroot -p -e "show databases;"
4. < recover data
mysql -uroot -p123   </root/world.sql

Common built-in commands

Help print MySQL help
\C Ctrl + C ends the last command
\q quit;  exit;  CTRL + D to exit MySQL
\G display data vertically
Source restore backup files

2、 Basic application of SQL

2.1 introduction to SQL

Structured query language
General commands for relational databases
Follow SQL92 standard (sql_mode)

2.2 common types of SQL

DDL data definition language
DCL data control language
DML data manipulation language
DQL data query language

2.3 logical structure of SQL database

Library:
    library name
    Library properties: character set, collation

Table:
    Table name
    Table attributes: storage engine type, character set, collation
    Listing
    Column attributes: data type, constraint, other attributes
    data row

2,4 character set

see:
mysql> show charset;
8.0 default: utf8mb4 
Default before 8.0: Latin1

Utf8: up to 3 bytes of characters can be stored, 3 bytes of Chinese characters and 1 byte of numbers \ letters \ special symbols
Uf8mb4: it can store up to 4 bytes of characters, 3 bytes of Chinese characters and 1 byte of numbers \ letters \ special symbols. It can store Emoji expressions

2,5 collation

see:
mysql> show collation;
influence:
It is case sensitive for English strings
utf8mb4_ general_ Ci case insensitive
utf8mb4_ Bin case sensitive (save Pinyin, Japanese)

2.6 introduction to data types

Ensure the accuracy and standardization of data.

Common numeric types

tinyint
int
3、 MySQL basic application

image.png

character string

char
varchar
emun

difference:
char(100)        
    For fixed length string type, storage space of 100 characters is allocated immediately regardless of the length of the string, and the unoccupied space is filled with "space"
varchar(100) 
    Variable length string type. Before storing data every time, judge the length and allocate the disk space as needed
    A space of one character length will be separately requested to store the character length (less than 255, if more than 255, two storage spaces will be occupied)
How do I select these two data types?
    1. For column values with less than 255 string lengths and fixed length, select char
    2. For strings longer than 255 characters, varchar can be selected
Enum enum data types
address enum('sz','sh','bj'.....)
              1    2    3
3、 MySQL basic application

image.png

Time type

datetime 
The range is from 1000-01-01 00:00:00.000000 to 9999-12-31 23:59:59.999999.
timestamp
The range is from 1970-01-01 00:00:00.000000 to 2038-01-19 03:14:07.999999.

3、 MySQL basic application

image.png

Binary

3、 MySQL basic application

image.png

constraint

---Build table
Table name, column name, column attribute, table attribute
---Column properties
Primary key: primary key constraint. There can only be one in a table, which is non empty and unique
Not null: non NULL constraint, null value is not allowed
Unique key: unique key constraint. Duplicate values are not allowed
Default: generally used with not null
Unsigned: unsigned, usually matched with a numeric column, and non negative
Comment: comment
AUTO_ Increment: self growing column

3、 Application of SQL statement

3.1 definition of DDL statement library and table (data definition language)

Library definition specification
1. The library name uses lowercase characters
2. The library name cannot start with a number
3. It cannot be a keyword inside the database
4. The character set must be set
Create Library:

mysql> create database boy charset utf8mb4;

View Library:

mysql> show databases;
mysql> show create database boy;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| boy      | CREATE DATABASE `boy` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+-----------------------------------------------------------------+

Delete Library:

mysql> drop database test;

Modify Library:

mysql> show create database wordpress;
+-----------+--------------------------------------------------------------------+
| Database  | Create Database                                                    |
+-----------+--------------------------------------------------------------------+
| wordpress | CREATE DATABASE `wordpress` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+-----------+--------------------------------------------------------------------+

mysql> alter database wordpress charset utf8mb4;

mysql> show create database wordpress;
+-----------+-----------------------------------------------------------------------+
| Database  | Create Database                                                       |
+-----------+-----------------------------------------------------------------------+
| wordpress | CREATE DATABASE `wordpress` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+-----------+-----------------------------------------------------------------------+

Table definition
Table definition specification

  1. The table name is a lowercase letter and cannot start with a number,
  2. Cannot be reserved characters. Use table names related to business
  3. Select the appropriate data type and length
  4. Set not null + default for each column For data 0 padding, use valid string padding for characters
  5. No column set comment
  6. The table must have a storage engine and character set set set
  7. The primary key column should be an irrelevant column or a numeric column, preferably self growing
  8. Enum type can only be string type instead of number

Table creation:

CREATE TABLE stu (
id INT PRIMARY KEY NOT NULL AUTO_ Increment comment 'student number',
Sname varchar (255) not null comment 'name',
Age tinyint unsigned not null default 0 comment 'age',
Gender enum ('m ',' f ',' n ') not null default' n 'comment' gender ',
Intime datetime not null default now() comment 'time'
)ENGINE INNODB CHARSET utf8mb4;

Query table:

mysql> show tables;
+---------------+
| Tables_in_boy |
+---------------+
| stu           |
| student       |
+---------------+
2 rows in set (0.00 sec)

mysql> show create table stu;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| stu   | CREATE TABLE `stu` (
  `id` int(11) NOT NULL AUTO_ Increment comment 'student number',
  `Sname ` varchar (255) not null comment 'name',
  `Age ` tinyint (3) unsigned not null default '0' comment 'age',
  `Gender ` enum ('m ',' f ',' n ') not null default' n 'comment' gender ',
  `intime` datetime NOT NULL DEFAULT CURRENT_ Timestamp comment 'time',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4           |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> desc stu;
+--------+---------------------+------+-----+-------------------+----------------+
| Field  | Type                | Null | Key | Default           | Extra          |
+--------+---------------------+------+-----+-------------------+----------------+
| id     | int(11)             | NO   | PRI | NULL              | auto_increment |
| sname  | varchar(255)        | NO   |     | NULL              |                |
| age    | tinyint(3) unsigned | NO   |     | 0                 |                |
| gender | enum('m','f','n')   | NO   |     | n                 |                |
| intime | datetime            | NO   |     | CURRENT_TIMESTAMP |                |
+--------+---------------------+------+-----+-------------------+----------------+
5 rows in set (0.00 sec)

Create a table with the same table structure

mysql> create table test like stu;

Delete table:

mysql> drop table test;

Modify table:

desc stu;
To add a QQ column to the stu table:
Alter table stu add QQ varchar (20) not null comment 'QQ number';
Add wechat column after sname:
Alter table stu add wechat varchar (64) not null unique comment 'micro signal' after sname;
Add a column num before the ID column:
Alter table stu add num int not null unique comment 'ID card' first
mysql> desc stu;
+--------+---------------------+------+-----+-------------------+----------------+
| Field  | Type                | Null | Key | Default           | Extra          |
+--------+---------------------+------+-----+-------------------+----------------+
| num    | int(11)             | NO   | UNI | NULL              |                |
| id     | int(11)             | NO   | PRI | NULL              | auto_increment |
| sname  | varchar(255)        | NO   |     | NULL              |                |
| wechat | varchar(64)         | NO   | UNI | NULL              |                |
| age    | tinyint(3) unsigned | NO   |     | 0                 |                |
| gender | enum('m','f','n')   | NO   |     | n                 |                |
| intime | datetime            | NO   |     | CURRENT_TIMESTAMP |                |
| qq     | varchar(20)         | NO   |     | NULL              |                |
+--------+---------------------+------+-----+-------------------+----------------+

Delete column:

ALTER TABLE stu DROP num;

Modify column:
Modify varchar (255) to varchar (64)

Alter table stu modify sname varchar (64) not null comment 'name';

Modify both column name and data type:

Alter table stu change gender sex char (4) not null comment 'gender';

3.2 DCL application

grant all on *.* to [email protected]'10.0.0.%' identified by '123' with grant option;
revoke delete on wordpress.*  from 'wordpress'@'10.0.0.%';
3.3 application of DML

Add, delete, modify and query data lines

Insert statement

INSERT INTO stu VALUES(1,'zs',18,'m',NOW());
mysql> SELECT * FROM stu;
+----+-------+-----+-----+---------------------+
| id | sname | age | sex | intime              |
+----+-------+-----+-----+---------------------+
|  1 | zs    |  18 | m   | 2021-05-12 17:16:34 |
+----+-------+-----+-----+---------------------+
The most standard writing method:
INSERT INTO stu(id,sname,age,sex,intime)
VALUES(2,'ls',19,'f',NOW())
mysql> SELECT * FROM stu;
+----+-------+-----+-----+---------------------+
| id | sname | age | sex | intime              |
+----+-------+-----+-----+---------------------+
|  1 | zs    |  18 | m   | 2021-05-12 17:16:34 |
|  2 | ls    |  19 | f   | 2021-05-12 17:19:34 |
+----+-------+-----+-----+---------------------+
Targeted entry:
INSERT INTO stu(sname,age,sex)
VALUES('w5',20,'m');
mysql> SELECT * FROM stu;
+----+-------+-----+-----+---------------------+
| id | sname | age | sex | intime              |
+----+-------+-----+-----+---------------------+
|  1 | zs    |  18 | m   | 2021-05-12 17:16:34 |
|  2 | ls    |  19 | f   | 2021-05-12 17:19:34 |
|  3 | w5    |  20 | m   | 2021-05-12 17:22:18 |
+----+-------+-----+-----+---------------------+
Enter multiple lines at one time:
INSERT INTO stu(sname,age,sex)
VALUES
('aa',12,'m'),
('bb',13,'n'),
('cc',14,'f');
mysql> SELECT * FROM stu;
+----+-------+-----+-----+---------------------+
| id | sname | age | sex | intime              |
+----+-------+-----+-----+---------------------+
|  1 | zs    |  18 | m   | 2021-05-12 17:16:34 |
|  2 | ls    |  19 | f   | 2021-05-12 17:19:34 |
|  3 | w5    |  20 | m   | 2021-05-12 17:22:18 |
|  4 | aa    |  12 | m   | 2021-05-12 17:26:01 |
|  5 | bb    |  13 | n   | 2021-05-12 17:26:01 |
|  6 | cc    |  14 | f   | 2021-05-12 17:26:01 |
+----+-------+-----+-----+---------------------+

Update (where condition must be added)

UPDATE stu SET sname='aaa';
SELECT * FROM stu;
UPDATE stu SET sname='bb' WHERE id=6;

Delete statement (where condition must be added)

DELETE FROM stu;
DELETE FROM stu WHERE id=9;

Shield delete function in production
Replace delete with update 
ALTER TABLE stu ADD is_del TINYINT DEFAULT 0 ;
UPDATE stu SET is_del=1 WHERE id=7;
SELECT * FROM stu WHERE is_del=0;
3.4dql query statement
Select application

Select is used alone

mysql> select @@port;
+--------+
| @@port |
+--------+
|   3306 |
+--------+
1 row in set (0.00 sec)

mysql> select @@basedir;
+--------------+
| @@basedir    |
+--------------+
| /data/mysql/ |
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-05-13 09:50:37 |
+---------------------+
mysql> select database();
+------------+
| database() |
+------------+
| boy        |
+------------+
Select general syntax (single table)
Select column   
From table   
Where condition  
Group by condition 
Having condition 
Order by condition
limit

Environment description (World Database)

Link: https://pan.baidu.com/s/1GJFvzDaPvOyr-HHyZScLPQ 
Extraction code: 2qh4 

View table structure
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+

Select is used with the from clause

Query all the information in the table (there is almost no such requirement in production)

use world;
SELECT * FROM city;
or
SELECT id,NAME,countrycode,district,population FROM city;

Query the values of name and population in the table

SELECT  NAME ,population   FROM  city;

Select is used with the where clause
Where equivalent query
example:
1. Query the names and population of all cities in China

select name,population from city
where countrycode='CHN';

Where matching comparison judgment query (> < > = < =)
example:
1. Name and population of cities with less than 100 people in the world

select name,population from city
where population<100;

Where match logical connector (and or)
example:

  1. Query the name and population of cities with a population greater than 800W in China
select name,population from city
where countrycode='CHN' and population>8000000;
  1. Query the city name and population of China or the United States
select name,population from city
where countrycode='CHN' OR countrycode='USA';
  1. Query the city name and population with a population between 500W and 600W
select name,population from city
where population>5000000 and population<6000000;
perhaps
SELECT NAME,population FROM city
where population between 5000000 and 6000000;

Where with like clause fuzzy query
example:

  1. Check the city information that begins with CH in the contriycode
select * from city
where  countrycode like 'CH%';

Note: statements like% ch% with a percent sign before and after it should not appear, because the performance is extremely poor without index

Where with in statement
example:

  1. Check the city information of China or the United States
seleCt name,population from city
where countrycode='CHN' or countrycode='USA'
perhaps
select name,population from city
where countrycode in ('CHN','USA');

Select is used in conjunction with the group by + aggregate function
Divide the data rows with common conditions in a column into a group, and then operate the aggregate function
example:

1. Common aggregate functions

Max (max)
Min (min)
AVG () average
Count() count
Sum()
group_concat()

example:

  1. Count the number of cities in each country
select countrycode,count(id) from city
group by countrycode;
  1. Count the total population of each country
select countrycode,sum(population) from city
group by countrycode;
  1. Count the number of provinces in each country
select countrycode,count(distinct district) from city
group by countrycode;
  1. Count the total population of each province in China
SELECT district,SUM(population) FROM city
WHERE countrycode='CHN'
GROUP BY district;
  1. Count the number of cities in each province of China
select district,count(name) from city
where countrycode='CHN'
group by district;
  1. Count the name list of cities in each province of China_ CONCAT()
select district,group_concat(name) from city
where countrycode='CHN'
group by district;

7. Small expansion

SELECT CONCAT(district,":",GROUP_CONCAT(NAME)) FROM city
WHERE countrycode='CHN'
GROUP BY district;

Select with having clause
The having clause is a function of judging after grouping and aggregation. Besides the position, the functions of having and where are the same
example:

  1. Count the total population of all countries and filter out those with a total population of more than 100 million
select countrycode,sum(population) from city
group by countrycode
having sum(population)>100000000;

Select is applied in conjunction with the order by clause
Sort the final result set, which can be used separately,
example:

  1. Count the total population of all countries, filter out those with a total population greater than 5000W, and arrange them in order from large to small
select countrycode,sum(population) from city
group by countrycode
having sum(population)>50000000
order by sum(population) desc;

Select with limit application
Limit can be understood as pagination display
example:

  1. Count the total population of all countries, filter out those with a total population greater than 5000W, and arrange them in order from large to small, showing only the top three
Usage 1: display the first three lines
select countrycode,sum(population) from city
group by countrycode
having sum(population)>50000000
order by sum(population) desc
limit 3;
Usage 2: display 4-6 lines
SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode
HAVING SUM(population)>50000000
ORDER BY SUM(population) DESC
LIMIT 3,3;

Limit m, n skip m lines and display a total of N lines
Limit y offset x skip x lines display a total of Y lines
Little practice
  1. The total population of each province in China is counted, and only those with a total population of less than 100W are printed
SELECT district,SUM(population) FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)<1000000;
  1. View all cities in China and sort by population (from large to small)
SELECT * FROM city
WHERE countrycode='CHN'
ORDER BY population DESC
  1. Make statistics of the total population of each province in China, in order of the total population from large to small
SELECT district,SUM(population) FROM city
WHERE countrycode='CHN'
GROUP BY district
ORDER BY SUM(population) DESC;
  1. Count the total population of each province in China and find out those with a total population greater than 500W,
    It is sorted by the total population from large to small, and only the top three are displayed
SELECT district,SUM(population) FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 3;

Union and union all
Function: the function of merging query of multiple result sets will generally rewrite the statements that appear in or or in, which will improve a lot of performance,

example:
1. Query the city information of China or the United States

SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';

Rewrite to:

SELECT * FROM city WHERE countrycode='CHN'
UNION ALL 
SELECT * FROM city WHERE countrycode='USA';

The difference between union and union all

Union all does not repeat
Union do to repeat
Select multi table join query (inner join)

Function: when single table data cannot meet query requirements
Environmental preparation:

Student: student form
SnO: student number
Sname: student name
Sage: student age
Ssex: student gender

Teacher: teacher table
TNO: teacher number
Tname: Teacher's name

Course: course schedule
CNO: course number
CNAME: course name
TNO: teacher number

Score: score sheet
SnO: student number
CNO: course number
Score: Score

DROP DATABASE school;
CREATE DATABASE school CHARSET utf8;
USE school

CREATE TABLE student(
sno INT NOT NULL PRIMARY KEY AUTO_ Increment comment 'student number',
Sname varchar (20) not null comment 'name',
Sage tinyint unsigned not null comment 'age',
Ssex enum ('f ','m') not null default'm 'comment' gender '
)ENGINE=INNODB CHARSET=utf8;

CREATE TABLE course(
CNO int not null primary key comment 'course number',
CNAME varchar (20) not null comment 'course name',
TNO int not null comment 'teacher number'
)ENGINE=INNODB CHARSET utf8;

CREATE TABLE sc (
SnO int not null comment 'student number',
CNO int not null comment 'course number',
Score int not null default 0 comment 'score'
)ENGINE=INNODB CHARSET=utf8;

CREATE TABLE teacher(
TNO int not null primary key comment 'teacher number',
Tname varchar (20) not null comment 'teacher name'
)ENGINE=INNODB CHARSET utf8;

INSERT INTO student(sno,sname,sage,ssex)
VALUES 
(1,'zhang3',18,'m');
(2,'zhang4',18,'m'),
(3,'li4',18,'m'),
(4,'wang5',19,'f');
(5,'zh4',18,'m'),
(6,'zhao4',18,'m'),
(7,'ma6',19,'f');
(8,'oldboy',20,'m'),
(9,'oldgirl',20,'f'),
(10,'oldp',25,'m');


INSERT INTO teacher(tno,tname) 
VALUES
(101,'oldboy'),
(102,'hesw'),
(103,'oldguo');

DESC course;
INSERT INTO course(cno,cname,tno)
VALUES
(1001,'linux',101),
(1002,'python',102),
(1003,'mysql',103);

DESC sc;
INSERT INTO sc(sno,cno,score)
VALUES
(1,1001,80),
(1,1002,59),
(2,1002,90),
(2,1003,100),
(3,1001,99),
(3,1003,40),
(4,1001,79),
(4,1002,61),
(4,1003,99),
(5,1003,40),
(6,1001,89),
(6,1003,77),
(7,1001,67),
(7,1003,82),
(8,1001,70),
(9,1003,80),
(10,1003,96);

SELECT * FROM student;
SELECT * FROM teacher;
SELECT * FROM course;
SELECT * FROM sc;

example:

  1. Statistics zhang3 studied several courses,
SELECT student.sname,COUNT(sc.cno) 
FROM student JOIN sc
ON student.sno=sc.sno
WHERE student.sname='zhang3';

2. What are the course names of statistics zhang3?

SELECT student.sname,GROUP_CONCAT(course.cname)
FROM student 
JOIN sc
ON student.sno=sc.sno
JOIN course
ON sc.cno=course.cno 
WHERE student.sname='zhang3'
GROUP BY student.sname;
  1. Query the student names taught by oldguo teacher
SELECT teacher.tname,GROUP_CONCAT(student.sname),COUNT(student.sname)
FROM teacher
JOIN course
ON teacher.tno=course.tno
JOIN sc 
ON course.cno=sc.cno
JOIN student
ON sc.sno=student.sno
WHERE teacher.tname='oldguo'
GROUP BY teacher.tname;
  1. Query the average score of the courses taught by oldguo
SELECT teacher.tname,AVG(sc.score)
FROM teacher
JOIN course 
ON teacher.tno=course.tno
JOIN sc 
ON course.cno=sc.cno
WHERE teacher.tname='oldguo';
  1. The average score of the courses taught by each teacher and sorted according to the average score
SELECT teacher.tname,course.cname,AVG(sc.score)
FROM teacher
JOIN course
ON teacher.tno=course.tno
JOIN sc
ON course.cno=sc.cno
GROUP BY course.cname
ORDER BY AVG(sc.score);

6. Query the names of failed students handed in by oldguo

SELECT teacher.tname,student.sname,sc.score
FROM teacher
JOIN course
ON teacher.tno=course.tno
JOIN sc
ON course.cno=sc.cno
JOIN student 
ON sc.sno=student.sno
WHERE teacher.tname='oldguo' AND sc.score<60;
  1. Query the information of all the students who have failed the teacher’s teaching
SELECT teacher.tname,GROUP_CONCAT(student.sname,":",sc.score) 
FROM teacher
JOIN course
ON teacher.tno=course.tno
JOIN sc
ON course.cno=sc.cno
JOIN student 
ON sc.sno=student.sno
WHERE sc.score<60
GROUP BY teacher.tname;
  1. Alias application
Table alias: it is called globally

SELECT t.tname,GROUP_CONCAT(st.sname,":",sc.score) 
FROM teacher AS t
JOIN course AS c
ON t.tno=c.tno
JOIN sc
ON c.cno=sc.cno
JOIN student  AS st 
ON sc.sno=st.sno
WHERE sc.score<60
GROUP BY t.tname;

Column alias: can be called by having and order by

Select t.tname as lecturer name, group_ Concat (st.sname, ":", sc.score) failed as
FROM teacher AS t
JOIN course AS c
ON t.tno=c.tno
JOIN sc
ON c.cno=sc.cno
JOIN student  AS st 
ON sc.sno=st.sno
WHERE sc.score<60
GROUP BY t.tname;

3.5 metadata acquisition

Introduction and acquisition of metadata

Metadata is stored in the base table.
Modify through special DDL statements and DCL statements
Query metadata through dedicated views and commands
information_ Schema contains a large number of metadata queries
The show command encapsulates the function and provides the basic function of metadata query

information_ Basic application of schema

Create a view
USE school

Create view AA as select t.tname as lecturer name, group_ Concat (st.sname, ":", sc.score) failed as
FROM teacher AS t
JOIN course AS c
ON t.tno=c.tno
JOIN sc
ON c.cno=sc.cno
JOIN student  AS st 
ON sc.sno=st.sno
WHERE sc.score<60
GROUP BY t.tname;

SELECT * FROM aa;
It is applicable to common SQL statements,

Create a view

USE school

Create view AA as select t.tname as lecturer name, group_ Concat (st.sname, ":", sc.score) failed as
FROM teacher AS t
JOIN course AS c
ON t.tno=c.tno
JOIN sc
ON c.cno=sc.cno
JOIN student  AS st 
ON sc.sno=st.sno
WHERE sc.score<60
GROUP BY t.tname;

SELECT * FROM aa;

USE informetion_schema
SHOW TABLES;
DESC TABLES;

Application in tables view

TABLE_ The name of the library where the schema table is located
TABLE_ Name table name
Engine storage engine
TABLE_ Rows data row
AVG_ ROW_ Length average row length
INDEX_ Length index length

example:
1. Display the information of all databases and tables under the database,

SELECT table_schema,table_name FROM information_schema.tables;

2. Query the InnoDB engine table,

SELECT table_schema,table_name FROM information_schema.tables
WHERE ENGINE='innodb';

3. Count the space occupied by the city table under the world database

Table data volume = average length * rows + index length
AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH

SELECT table_name,AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH
FROM information_schema.tables
WHERE table_schema='world' AND table_name='city';
  1. Count the data volume of the world database
SELECT table_schema,SUM((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024
FROM information_schema.TABLES
WHERE table_schema='world';

5. Count the data volume of each database and sort it from large to small

SELECT table_schema,SUM((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024 AS aa
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY aa DESC;

Splicing statements or commands with concat() function

example:

1. Backup the database by database and table

mysqldump -uroot -p123 world city >/bak/world_city.sql

SELECT CONCAT("mysqldump -uroot -p123 "
,table_schema," ",table_name," >/bak/",table_schema,"_",table_name,".sql")
FROM information_schema.tables;

2. Imitate the following statements to generate in batches and operate all tables under the world library

ALTER TABLE world.city DISCARD TABLESPACE;

SELECT
CONCAT("ALTER TABLE ",table_schema,".",table_name," DISCARD TABLESPACE;")
FROM information_schema.TABLES
WHERE table_schema='world';

Introduction to show statement

SHOW DATABASES;                    View all database names
SHOW TABLES;                       View table name
Show create database 'database name' view database creation statement
Show create table 'table name' view table creation statement
Show processlist view all user connections
Show charset view supported character sets
Show collation view all supported collation rules
Show grants for like "user" to view the user's permission information
Show variables like "XXX" view parameter information
Show engines view all supported storage engine types
Show index from 'table name' view the index information of the table
Show engine InnoDB status \ g view InnoDB engine status details
Show binary logs view the list information of binary logs
Show binlog events in '' View binary log event information
SHOW MASTER STATUS;                View the binary log information currently used by mysql
Show slave status \ g view library status information
Show relaylog events in '' view event information in relay log
Show status like ''view the overall database status information