web123456

MySQL—Basic knowledge of introductory knowledge (you can understand it at first glance, and you can learn it at once)

Preface

Knowledge is unfinished and the sea of ​​knowledge is endless. Today, I have been studying MySQL for 5 days. Although the knowledge points are simple, there are many, so I wrote a blog to write down the basics of MySQL for the convenience of searching in the future, and I also share them with you.

1. Initial MySQL

1. What is a database

database (DataBase, abbreviationDB )

concept: A collection of large amounts of data stored in computers for a long time, organized and shared, is a data "warehouse"

effect: Save and manage data safely (such as: add, delete, modify, and check), reducing redundancy...

Database Overview:

  • Relational Database (SQL)

    • MySQL , Oracle , SQL Server , SQLite , DB2 , …
    • Relational databases establish relationships between tables through foreign key association

  • Non-relational database (NOSQL)

    • Redis , MongoDB , …
    • Non-relational databases usually refer to data stored in the database as objects, and the relationship between objects is determined by the properties of each object itself.

2. What is DBMS

Database Management System (DataBase Management System )

Database management software, scientifically organizes and stores data, and efficiently acquires and maintains data

Connect to database statement:mysql -h server host address -u user name -p user password

3. Basic database operation commands

update user set password=password('123456')where user='root'; Change password
 flush privileges; refresh the database
 show databases; display all databases
 use dbname; open a database
 show tables; display all tables in the database mysql
 describe user; display column information of user table in table mysql database
 create database name; create database
 use databasename; select database

2. Database operation

1. Structured query statement classification

name explain Order
DDL (Data Definition Language) Define and manage data objects, such as databases, data tables, etc. CREATE、DROP、ALTER
DML (data operation language) Used to manipulate data contained in database objects INSERT、UPDATE、DELETE
DQL (data query language) Used to query database data SELECT
DCL (Data Control Language) Language used to manage databases, including management permissions and data changes GRANT、COMMIT、ROLLBACK

2. Database operations

Create database: create database [if not exists] database name;
 Delete database: drop database [if exists] database name;
 View the database: show databases;
 Use database: use database name;

3. Create a data table

A type of DDL, syntax:

create table [if not exists] `Table name`(
     'Field Name 1' Column Type [Property][Index][Comment],
     'Field Name 2' Column Type [Property][Index][Comment],
     #...
     'Field Name n' Column Type [Property][Index][Comment]
 )[Table type][Table character set][Comment];

Note: Backticks are used to distinguish between MySQL reserved words and ordinary characters (keys under keyboard esc).

4. Data value and column type

Column type: Specifies the data type stored in the column in the database

4.1 Numerical Type

type illustrate Storage requirements
tinyint Very small data 1 byte
smallint Smaller data 2 bytes
mediumint Medium size data 3 bytes
int Standard integer 4 bytes
bigint Larger integers 8 bytes
float Single precision floating point number 4 bytes
double Double precision floating point number 8 bytes
decimal Floating point numbers in string form

4.2 String type

type illustrate Maximum length
char[(M)] Fixed long string, fast search but space-consuming, 0<=M<=255 M characters
varchar[(M)] Variable string, 0<=M<=65535 Variable length
tinytext Miniature text string 2^8-1 bytes
text Text string 2^16-1 bytes

4.3 Date and time type numerical types

type illustrate Value range
DATE YYYY-MM-DD, date format 1000-01-01~9999-12-31
TIME Hh:mm:ss, time format -838:59:59~838:59:59
DATETIME YY-MM-DD hh:mm:ss 1000-01-01 00:00:00 to

9999-12-31 23:59:59
TIMESTAMP Time stamp represented in YYYYMMDDhhmmss format 197010101000000~At some time in 2037
YEAR Year value in YYYY format 1901~2155

4.4NULL value

  • Understand as "no value" or "unknown value"
  • Do not use NULL for arithmetic operations, the result is still NULL

5. Data field properties

UnSigned

  • Unsigned
  • Declares that this data column does not allow negative numbers.

ZEROFILL

  • 0 filled
  • If there are less than digits, fill it with 0, if int(3), 5 will be 005.

Auto_InCrement

  • Automatically grow, every time a piece of data is added, it will automatically add 1 to the previous record number (default)

  • Usually used for setting upPrimary key, and is an integer type

  • Define the start value and step size

    • Set step size of the current table (AUTO_INCREMENT=100): only affects the current table
    • SET @@auto_increment_increment=5; affects all tables that use self-increment (global)

NULL and NOT NULL

  • The default is NULL, that is, no value of the column is inserted
  • If set to NOT NULL , the column must have a value

DEFAULT

  • Default
  • Used to set default values
  • For example, the gender field defaults to "Male" , otherwise it is "Female" ; if no value of this column is specified, the default value is "Male" value
-- Target: Create a school database
 -- Create student table (columns, fields)
 -- Student ID int Login password varchar(20) Name, gender varchar(2), date of birth (datatime), home address, email
 -- Before creating a table, be sure to select the database first

 CREATE TABLE IF NOT EXISTS `student` (
   `id` int(4) NOT NULL AUTO_INCREMENT COMMENT 'Student number',
   `name` varchar(30) NOT NULL DEFAULT 'Anonymous' COMMENT 'Name',
   `pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT 'password',
   `sex` varchar(2) NOT NULL DEFAULT 'male' COMMENT 'gender',
   `birthday` datetime DEFAULT NULL COMMENT 'birthday',
   `address` varchar(100) DEFAULT NULL COMMENT 'Address',
   `email` varchar(50) DEFAULT NULL COMMENT 'email',
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

 -- View the definition of the database
 SHOW CREATE DATABASE school;
 -- View the definition of the data table
 SHOW CREATE TABLE student;
 -- Display table structure
 DESC student; -- Set strict check mode (not tolerate errors) SET sql_mode='STRICT_TRANS_TABLES';

6. Types of data tables

6.1 Set the type of data table

CREATE TABLE table name(
     -- Omit some code
     -- Mysql comments
     -- 1. # Single line comment
     -- 2. /*...*/ Multi-line comments
 )ENGINE = MyISAM (or InnoDB)

 -- Check the engine types (table types) supported by mysql
 SHOW ENGINES;

MySQL data table type:MyISAM , InnoDB, HEAP , BOB , CSV , etc.

Common MyISAM and InnoDB types:

name MyISAM InnoDB
Transaction processing Not supported support
Data row lock Not supported support
Foreign key constraints Not supported support
Full text index support Not supported
Tablespace size Smaller Larger, about 2 times!

Experience (applicable occasions):

  • Applicable to MyISAM: Save space and speed
  • Applicable to InnoDB: Security, Transaction Processing and Multi-User Operation Data Sheets

6.2 Data table storage location

MySQL data tables are stored on disk in file form

  • Including table files, data files, and database option files
  • Location: The Mysql installation directory\data\ stores the data table. The directory name corresponds to the database name, and the file name under this directory corresponds to the data table.

Notice :

  • *. frm – Table structure definition file
  • *. MYD – Data File ( data )
  • *. MYI – Index File ( index )
  • InnoDB type data table has only one *.frm file, and ibdata1 file in the previous directory
  • MyISAM type data table corresponds to three files

6.3 Setting the data table character set

We can set different character sets for databases, data tables, and data columns. The setting method is:

  • Set it through commands when creating, such as: CREATE TABLE table name ()CHARSET = utf8;
  • If there is no setting, it is set according to the parameters in the MySQL database configuration file

7. Modify the database

7.1 Modify the table (ALTER TABLE)

Modify table name:ALTER TABLE Old table name RENAME AS New table name
 Add field: ALTER TABLE Table name ADD field name Column attribute [property]
 Modify fields:
 - ALTER TABLE Table name MODIFY Field name Column type [Properties]
 - ALTER TABLE Table name CHANGE Old field name New field name Column attribute [property]
 Delete field: ALTER TABLE table name DROP field name

7.2 Delete the data table

Syntax: DROP TABLE [IF EXISTS] Table name

  • IF EXISTS is optional, to determine whether the data table exists
  • If deleting a non-existent data table, an error will be thrown

7.3 Others

1. You can wrap the backticks (`) as identifiers (library name, table name, field name, index, alias) to avoid duplicate names with keywords!  Chinese can also be used as an identifier!
 2. Each library directory has an option file that holds the current database.
 3. Comments:
     Single line comment # Comment content
     Multi-line comment /* Comment content */
     Single-line comments -- Comment content (standard SQL comment style, requiring a space character (space, TAB, line break, etc.) after double dash)
 4. Pattern wildcards:
     _ Any single character
     %Arbitrary multiple characters, even zero characters
     Single quotes need to be escaped \'
 5. The statement ending characters in the CMD command line can be ";", "\G", "\g", which only affects the display results.  Other places are still ended with semicolons.  delimiter can modify the statement ending character of the current conversation.
 6. SQL is case-insensitive (keywords)
 7. Clear existing statements:\c

3. DML language

1. Foreign keys

1.1 Foreign Key Concept

If the public keyword is the primary keyword in one relationship, then this public keyword is called the foreign key of another relationship. It can be seen from this that foreign keys represent the correlation between the two relationships. A table with a foreign key in another relationship as the primary keyword is calledMain table, a table with this foreign key is called the main tableFrom the table。

In practice, put the value of a table into the second table to represent the association, and the value used is the primary key value of the first table (compound primary key value may be included if necessary). At this time, the attributes that hold these values ​​in the second table are called foreign keys (foreign key)。

Foreign key function

Keep dataconsistency，Integrity, the main purpose is to control the data stored in the foreign key table,constraint. To make two tables related, foreign keys can only reference the values ​​of columns in the table or use null values.

1.2 Create a foreign key

Specify foreign key constraints when creating tables

-- Method 1 of creating foreign keys: Create subtables and create foreign keys
 -- Grade table (id\grade name)
 CREATE TABLE `grade` (
   `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Grade ID',
   `gradename` VARCHAR(50) NOT NULL COMMENT 'Grade Name',
   PRIMARY KEY (`gradeid`)
 ) ENGINE=INNODB DEFAULT CHARSET=utf8
 -- Student information form (student number, name, gender, grade, mobile phone, address, date of birth, email, ID number)
 CREATE TABLE `student` (
   `studentno` INT(4) NOT NULL COMMENT 'Student number',
   `studentname` VARCHAR(20) NOT NULL DEFAULT 'Anonymous' COMMENT 'Name',
   `sex` TINYINT(1) DEFAULT '1' COMMENT 'Gender',
   `gradeid` INT(10) DEFAULT NULL COMMENT 'Grade',
   `phoneNum` VARCHAR(50) NOT NULL COMMENT 'Mobile',
   `address` VARCHAR(255) DEFAULT NULL COMMENT 'Address',
   `borndate` DATETIME DEFAULT NULL COMMENT 'Birthday',
   `email` VARCHAR(50) DEFAULT NULL COMMENT 'Email',
   `idCard` VARCHAR(18) DEFAULT NULL COMMENT 'ID CARN 'ID number',
   PRIMARY KEY (`studentno`),
   KEY `FK_gradeid` (`gradeid`),
   CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
 ) ENGINE=INNODB DEFAULT CHARSET=utf8

Modify after building the table

-- Create foreign key method 2: After creating a subtable, modify the subtable to add foreign keys
 ALTER TABLE `student`
 ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);

1.3 Delete foreign keys

Notice: When deleting a table with a primary and foreign key relationship, you must first delete the sub-table and then delete the main table

-- Delete foreign keys
 ALTER TABLE student DROP FOREIGN KEY FK_gradeid;
 -- I found that the above index is still there after executing, so I have to delete the index
 -- Note: This index is generated by default when creating a foreign key.
 ALTER TABLE student DROP INDEX FK_gradeid;

language

Database meaning: Data storage, data management

How to manage database data:

  • Manage database data through management tools such as SQLyog
  • passDML statementsManage database data

DML language: Data operation language

  • Used to manipulate data contained in database objects

  • include :

    • INSERT (Add data statement)
    • UPDATE (Update data statement)
    • DELETE (Delete data statement)

3. Add data

3.1INSERT command

grammar:

INSERT INTO Table name [(field 1, field 2, field 3,...)] VALUES('value 1','value 2','value 3')

Notice :

  • Fields or values ​​are separated by English commas.
  • ’ Field 1, Field 2…’ This part can be omitted, but the added values ​​must correspond to the table structure, data columns, and the order is consistent with the number.
  • Multiple pieces of data can be inserted at the same time, and values ​​are separated by English commas.
-- How to add a statement when using a statement?
 -- Syntax: INSERT INTO Table name [(field 1, field 2, field 3,...)] VALUES('value 1','value 2','value 3')
 INSERT INTO grade(gradename) VALUES ('Freshman');
 -- The primary key increases by itself, can it be omitted?
 INSERT INTO grade VALUES ('Soul');
 -- Query: INSERT INTO grade VALUE ('Sothly') Error code: 1136
 Column count doesn`t match value count at row 1
 -- Conclusion: 'Field 1, Field 2...'This part can be omitted, but the added values ​​must correspond to the table structure, data columns, and the order is consistent with the quantity.
 -- Insert multiple data at once
 INSERT INTO grade(gradename) VALUES ('Junior'), ('Senior');

4. Modify the data

4.1 update command

grammar:

UPDATE table name SET column_name=value [,column_name2=value2,...] [WHERE condition];

Notice :

  • column_name is the column of data to be changed
  • value is the modified data, which can be a variable, specifically refers to, expressions or nested SELECT results.
  • condition is a filter condition, if not specified, modify all column data of the table.

4.2 where condition clause

It can be simply understood as: filtering data from tables conditionally

-- Modify grade information
 UPDATE grade SET gradename = 'High School' WHERE gradeid = 1;

5. Delete data

5.1DELETE command

grammar:

DELETE FROM table name [WHERE condition];

Note: condition is a filter condition. If not specified, delete all column data of the table.

-- Delete the last data
 DELETE FROM grade WHERE gradeid = 5

5.2 TRUNCATE command

Function: used to completely clear table data, but the table structure, index, constraints, etc. remain unchanged;

grammar:

TRUNCATE [TABLE] table_name;
 -- Clear the grade table
 TRUNCATE grade

Note: Different from the DELETE command

  • Same: Both can delete data, and do not delete table structure, but TRUNCATE is faster

  • different :

    • Reset the AUTO_INCREMENT counter using TRUNCATE TABLE
    • Using TRUNCATE TABLE will not have any impact on transactions (the transaction will be said later)

test:

-- Create a test table
 CREATE TABLE `test` (
   `id` INT(4) NOT NULL AUTO_INCREMENT,
   `coll` VARCHAR(20) NOT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=INNODB DEFAULT CHARSET=utf8

 -- Insert several test data
 INSERT INTO test(coll) VALUES('row1'),('row2'),('row3');
 -- Delete table data (delete without where conditions)
 DELETE FROM test;
 -- Conclusion: If you do not specify Where, all column data of the table are deleted, and the current value will still be increased from the original basis, and the log will be recorded.
 -- Delete table data (truncate)
 TRUNCATE TABLE test;
 -- Conclusion: truncate deletes data, and the current value will be restored to the initial value and restarted; no log will be recorded.
 -- Also use DELETE to clear the database table data of different engines. After restarting the database service
 -- InnoDB: The self-increment column starts again from the initial value (because it is stored in memory, power is lost if it is powered off)
 -- MyISAM: The self-increase column still starts from the previous self-increase data (exist in the file and will not be lost)

4. Use DQL to query data

language

DQL (Data Query Language Data Query Language)

  • Query database data, such asSELECTStatement
  • Simple single-table query or complex and nested query for multiple tables
  • It is the most core and most important statement in the database language
  • The most frequently used statement

1.1SELECT syntax

SELECT [ALL | DISTINCT]
 {* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
 FROM table_name [as table_alias]
     [left | right | inner join table_name2] -- Joint query
     [WHERE ...] -- Specify the conditions to which the result needs to be met
     [GROUP BY ...] -- Specify the fields to group the results according to
     [HAVING] -- The secondary conditions that must be met for records in filtered groups
     [ORDER BY ...] -- Specify that query records are sorted by one or more criteria
     [LIMIT {[offset,]row_count | row_countOFFSET offset}];
     -- Specify which records to query are from which

Note: [ ] brackets represent optional, { } brackets represent required

2. Specify the query field

-- Query all data column results in the table, using the **" \* "** symbol; but it is inefficient and is not recommended.
 -- Query all student information
 SELECT * FROM student;
 -- Query the specified column (student number, name)
 SELECT studentno,studentname FROM student;

2.1AS clause as an alias

effect:

  • A new alias can be given to the data column
  • A new alias can be given to the table
  • The calculated or summarized result can be replaced by another new name
-- Here is an alias for the column (of course, the keyword as can be omitted)
 SELECT studentno AS student number, studentname AS name FROM student;

 -- Use as can also alias the table
 SELECT studentno AS student number, studentname AS name FROM student AS s;

 -- Use as to give a new name to the query result
 -- CONCAT() function splicing string
 SELECT CONCAT('Name:',studentname) AS New name FROM student;

2.2 Use of DISTINCT keywords

Function: Remove duplicate records in the record result returned by the SELECT query (returns the values ​​of all columns are the same), and only one is returned

-- # Check which students took the exam (student number) Remove duplicates
 SELECT * FROM result; -- Check the test results
 SELECT studentno FROM result; -- Check which students took the exam
 SELECT DISTINCT studentno FROM result; -- Understand: DISTINCT remove duplicates, (default is ALL)

2.3 Using columns of expressions

Expressions in database: generally consist of text values, column values, NULLs, functions and operators, etc.

Application scenarios:

  • SELECT statement returns result column use

  • Used in ORDER BY, HAVING and other clauses in SELECT statements

  • Use expressions in where conditional statements in DML statements

    -- Expressions can be used in selcet query
 SELECT @@auto_increment_increment; -- Query the self-increase step length
 SELECT VERSION(); -- Query version number
 SELECT 100*3-1 AS calculation result; -- Expression

 -- Students' test scores are collectively improved by one point to view
 SELECT studentno,StudentResult+1 AS 'After increasing the score' FROM result;

  • Avoid interfering with development language programs such as ' . ' , ' * ' and brackets in SQL returns.

Conditional statements

Function: Used to retrieve records that meet the criteria in the data table

Search criteria can be composed of one or more logical expressions, and the results are generally true or false.

3.1 Logical Operators

Operator name grammar describe
AND or && a AND b or a && b Logical and the result is true
OR or || a OR b or a || b Logical or, as long as one is true, the result is true
NOT or ! NOT a or ! a The logic is not, if the operand is false, the result is true!

test

-- Query that meets the conditions (where)
 SELECT Studentno,StudentResult FROM result;

 -- Check the test scores between 95-100
 SELECT Studentno,StudentResult
 FROM result
 WHERE StudentResult>=95 AND StudentResult<=100;

 -- AND can also be written as &&
 SELECT Studentno,StudentResult
 FROM result
 WHERE StudentResult>=95 && StudentResult<=100;

 -- Fuzzy query (corresponding word: exact query)
 SELECT Studentno,StudentResult
 FROM result
 WHERE StudentResult BETWEEN 95 AND 100;

 -- Except for students No. 1000, you need the results of other students
 SELECT studentno, studentresult
 FROM result
 WHERE studentno!=1000;

 -- Use NOT
 SELECT studentno, studentresult
 FROM result
 WHERE NOT studentno=1000;

3.2 Fuzzy Query: Comparison Operator

Operator name grammar describe
IS NULL a IS NULL If the operator is NULL, the result is true
IS NOT NULL a IS NOT NULL If the operator is not NULL, the result is true
BETWEEN a BETWEEN b AND c If the range a is between b and c, the result is true
LIKE a LIKE b SQL pattern matching, if a matches b, the result is true
IN a IN（a1,a2,a3,…） If a is equal to one of a1, a2…, the result is true

Notice:

  • Arithmetic operations can only be performed between records of numerical data types;
  • Comparison can only be made between data of the same data type;

test:

-- Fuzzy query between and \ like \ in \ null

 -- =============================================================
 -- LIKE
 -- =============================================================
 -- Check the student number and name of the student name of Liu
 -- Wildcards used in combination with like: % (represents 0 to any character) _ (one character)
 SELECT studentno,studentname FROM student
 WHERE studentname LIKE 'Liu %';

 -- Inquiry for a classmate named Liu, there is only one word behind it
 SELECT studentno,studentname FROM student
 WHERE studentname LIKE 'Liu_';

 -- When searching for a classmate named Liu, there are only two characters behind it
 SELECT studentno,studentname FROM student
 WHERE studentname LIKE 'Liu__';

 -- Query the name containing the word Jia
 SELECT studentno,studentname FROM student
 WHERE studentname LIKE '%Jia%';

 -- To query the name that contains special characters, use escape symbols '\'
 -- Custom escape keywords: ESCAPE ':'

 -- =============================================================
 -- IN
 -- =============================================================
 -- Query the student name with student number 1000,1001,1002
 SELECT studentno,studentname FROM student
 WHERE studentno IN (1000,1001,1002);

 -- Search students with address in Beijing, Nanjing, Luoyang, Henan
 SELECT studentno, studentname, address FROM student
 WHERE address IN ('Beijing','Nanjing','Luoyang, Henan');

 -- =============================================================
 -- NULL
 -- =============================================================
 -- Inquiry for students who did not fill in their date of birth
 -- Cannot write =NULL directly, this means error, use is null
 SELECT studentname FROM student
 WHERE BornDate IS NULL;

 -- Inquiry of students who fill out their date of birth
 SELECT studentname FROM student
 WHERE BornDate IS NOT NULL;

 -- Query students who do not write their home address (empty string does not equal null)
 SELECT studentname FROM student
 WHERE Address='' OR Address IS NULL;

4. Connection query

4.1JOIN comparison

Operator name describe
INNER JOIN If there is at least one match in the table, return the row
LEFT JOIN Return all rows from the left table even if there is no match in the right table.
RIGHT JOIN Return all rows from the right table even if there is no match in the left table.

test

/*
 Connection query
     If you need to query data from multiple data tables, multiple queries can be implemented through the connection operator
 Inner join
     Query the intersection of the result set in two tables
 External connection outer join
     left join
         (Use the left table as the benchmark, and match the right table one by one. If the match cannot be matched, the record of the left table will be returned, and the right table will be filled with NULL)
     Right-right join
         (Take the right table as the benchmark, and match the left table one by one. If the match cannot be matched, the record in the right table will be returned, and the left table will be filled with NULL)
        
 Equivalent and non-equivalent connections

 Self-connection
 */

 -- Query the information of students who took the exam (student number, student name, subject number, score)
 SELECT * FROM student;
 SELECT * FROM result;

 /*Thoughts:
 (1): Analyze the requirements and determine that the columns of the query come from two classes, student result, and join query
 (2): Determine which connection query to use? (Inner connection)
 */
 SELECT ,studentname,subjectno,StudentResult
 FROM student s
 INNER JOIN result r
 ON =

 -- Right connection (can also be implemented)
 SELECT ,studentname,subjectno,StudentResult
 FROM student s
 RIGHT JOIN result r
 ON =

 -- Equivalent connection
 SELECT ,studentname,subjectno,StudentResult
 FROM student s , result r
 WHERE =

 -- Left connection (All students are checked, those who do not take the exam will also be found)
 SELECT ,studentname,subjectno,StudentResult
 FROM student s
 LEFT JOIN result r
 ON =

 -- Check out the students who are missing the exam (left connection application scenario)
 SELECT ,studentname,subjectno,StudentResult
 FROM student s
 LEFT JOIN result r
 ON =
 WHERE StudentResult IS NULL

 -- Thinking questions: Check the information of students who took the exam (student number, student name, subject name, score)
 SELECT ,studentname,subjectname,StudentResult
 FROM student s
 INNER JOIN result r
 ON =
 INNER JOIN `subject` sub
 ON =

4.2 Self-connection

/*
 Self-connection
     Connect the data table to itself

 Requirements: From a table containing column ID, column name and parent column ID
      Query the parent column name and other child column names
 */

 -- Create a table
 CREATE TABLE `category` (
   `categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Theme id',
   `pid` INT(10) NOT NULL COMMENT 'parent id',
   `categoryName` VARCHAR(50) NOT NULL COMMENT 'Theme name',
   PRIMARY KEY (`categoryid`)
 ) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

 -- Insert data
 INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
 VALUES('2','1','Information Technology'),
 ('3','1','Software Development'),
 ('4','3','Database'),
 ('5','1','Art Design'),
 ('6','3','web development'),
 ('7','5','ps technology'),
 ('8','2','Office Information');

 -- Write SQL statements to present the parent-child relationship of the column (the name of the parent column, the name of the child column)
 -- Core idea: treat a table as two identical tables, and then join these two tables to query (self-join)
 SELECT AS 'Parent Column', AS 'Sub-Column'
 FROM category AS a,category AS b
 WHERE a.`categoryid`=b.`pid`

 -- Thinking questions: Check the information of students who took the exam (student number, student name, subject name, score)
 SELECT ,studentname,subjectname,StudentResult
 FROM student s
 INNER JOIN result r
 ON =
 INNER JOIN `subject` sub
 ON =

 -- Check the student and his or her grade (student number, student name, grade name)
 SELECT studentno AS student number, studentname AS student name, gradename AS grade name
 FROM student s
 INNER JOIN grade g
 ON s.`GradeId` = g.`GradeID`

 -- Query the subject and the grade to which it belongs (subject name, grade name)
 SELECT subjectname AS Subject name,gradename AS Gradename
 FROM SUBJECT sub
 INNER JOIN grade g
 ON =

 -- Query all test results of database structure-1 (student number, student name, subject name, score)
 SELECT ,studentname,subjectname,StudentResult
 FROM student s
 INNER JOIN result r
 ON =
 INNER JOIN `subject` sub
 ON =
 WHERE subjectname='Database Structure-1'

5. Sort and pagination

test

/*===============================================
 Syntax: ORDER BY
     The ORDER BY statement is used to sort the result set according to the specified column.
     The ORDER BY statement sorts records in ascending order by default.
     If you want to sort records in descending order, you can use the DESC keyword.
    
 */

 -- Query all test results of database structure-1 (student number, student name, subject name, score)
 -- Sort by descending order of grades
 SELECT ,studentname,subjectname,StudentResult
 FROM student s
 INNER JOIN result r
 ON =
 INNER JOIN `subject` sub
 ON =
 WHERE subjectname='Database Structure-1'
 ORDER BY StudentResult DESC

 /*===================== Paging ===========================
 Syntax: SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
 Benefits: (User experience, network transmission, query pressure)

 Derivation:
     Page 1: limit 0,5
     Page 2: limit 5,5
     Page 3: limit 10,5
     ......
     Page N: limit (pageNo-1)*pageSzie,pageSzie
     [pageNo: page number, pageSize: number of pieces displayed on a single page]
    
 */

 -- 5 pieces of data are displayed on each page
 SELECT ,studentname,subjectname,StudentResult
 FROM student s
 INNER JOIN result r
 ON =
 INNER JOIN `subject` sub
 ON =
 WHERE subjectname='Database Structure-1'
 ORDER BY StudentResult DESC , studentno
 LIMIT 0,5

 -- Query the information of students with top 10 course scores and scores greater than 80 (student number, name, course name, score) in the first academic year of JAVA
 SELECT ,studentname,subjectname,StudentResult
 FROM student s
 INNER JOIN result r
 ON =
 INNER JOIN `subject` sub
 ON =
 WHERE subjectname='JAVA First School Year'
 ORDER BY StudentResult DESC
 LIMIT 0,10

6. Subquery

*=================== Subquery ====================
 What is a subquery?
     In the WHERE condition clause in the query statement, another query statement is nested
     Nested queries can be composed of multiple subqueries, and the solution is from the inside to the outside;
     The results returned by subqueries are generally sets, so it is recommended to use the IN keyword;
 */

 -- Query all test results of database structure-1 (student number, subject number, score), and sort the scores in descending order
 -- Method 1: Use connection query
 SELECT studentno,StudentResult
 FROM result r
 INNER JOIN `subject` sub
 ON r.`SubjectNo`=sub.`SubjectNo`
 WHERE subjectname = 'Database Structure-1'
 ORDER BY studentresult DESC;

 -- Method 2: Use subquery (execution order: from inside to outside)
 SELECT studentno,subjectno,StudentResult
 FROM result
 WHERE subjectno=(
     SELECT subjectno FROM `subject`
     WHERE subjectname = 'Database Structure-1'
 )
 ORDER BY studentresult DESC;

 -- Query the student number and name of students whose course is Advanced Mathematics-2 and whose score is not less than 80 points
 -- Method 1: Use connection query
 SELECT ,studentname
 FROM student s
 INNER JOIN result r
 ON s.`StudentNo` = r.`StudentNo`
 INNER JOIN `subject` sub
 ON sub.`SubjectNo` = r.`SubjectNo`
 WHERE subjectname = 'Advanced Mathematics-2' AND StudentResult>=80

 -- Method 2: Use connection query + subquery
 -- The student number and name of a student with a score of not less than 80
 SELECT ,studentname FROM student s
 INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
 WHERE StudentResult>=80

 -- Based on the above SQL, add requirements: courses are Advanced Mathematics-2
 SELECT ,studentname FROM student s
 INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
 WHERE StudentResult>=80 AND subjectno=(
     SELECT subjectno FROM `subject`
     WHERE subjectname = 'Advanced Mathematics-2'
 )

 -- Method 3: Use subquery
 -- Write simple sql statements in steps and then nest them
 SELECT studentno,studentname FROM student WHERE studentno IN(
     SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=(
         SELECT subjectno FROM `subject` WHERE subjectname = 'Advanced Mathematics-2'
     )
 )

5. MySQL functions

1. Common functions

1.1 Data function

SELECT ABS(-8); /*Absolute value*/
  SELECT CEILING(9.4); /* round up*/
  SELECT FLOOR(9.4); /* round down*/
  SELECT RAND(); /*Random number, return a random number between 0-1*/
  SELECT SIGN(0); /* symbol function: negative number returns -1, positive number returns 1,0 returns 0*/

1.2 String function

SELECT CHAR_LENGTH('The crazy god says you can succeed if you persist'); /*Return the number of characters contained in the string*/
  SELECT CONCAT('I','love','program'); /*Merge strings, parameters can have multiple */
  SELECT INSERT('I love programming helloworld',1,2,'super love'); /*Replace string, start at a certain position to replace a certain length*/
  SELECT LOWER('KuangShen'); /*Lowercase*/
  SELECT UPPER('KuangShen'); /*Uppercase*/
  SELECT LEFT('hello,world',5); /*Seave from the left*/
  SELECT RIGHT('hello,world',5); /*Seave from the right*/
  SELECT REPLACE('The crazy god says persistence can succeed','persistence','work hard'); /*Replace string*/
  SELECT SUBSTR('The crazy god says you can succeed if you persist', 4,6); /*Intercept the string, start and length*/
  SELECT REVERSE('The crazy god says you can succeed if you persist'); /*Reversal
 
  -- Check the classmate named Zhou, change it to Zou
  SELECT REPLACE(studentname,'Zhou','Zou') AS New Name
  FROM student WHERE studentname LIKE 'week %';

1.3 Date and time functions

SELECT CURRENT_DATE(); /*get the current date*/
  SELECT CURDATE(); /*get the current date*/
  SELECT NOW(); /*get the current date and time*/
  SELECT LOCALTIME(); /*get the current date and time*/
  SELECT SYSDATE(); /*get the current date and time*/
 
  -- Get the year, month, day, hour, minute, and second
  SELECT YEAR(NOW());
  SELECT MONTH(NOW());
  SELECT DAY(NOW());
  SELECT HOUR(NOW());
  SELECT MINUTE(NOW());
  SELECT SECOND(NOW());

1.4 System information function

SELECT VERSION(); /*version*/
  SELECT USER(); /*User*/

2. Aggregation function

Function name describe
COUNT() Returns the sum of records that meet the Select condition, such as select count()【Not recommended to use, low efficiency]
SUM（） Returns a number field or expression column for statistics, and returns the sum of a column.
AVG（） Usually used as statistics for numeric fields or expression columns, returning the average value of a column
MAX（） Can be used as statistics for numeric fields, character fields or expression columns to return the maximum value
MIN（） Can be used as statistics for numeric fields, character fields or expression columns to return the smallest value 
-- Aggregation function
  /*COUNT:Non-empty*/
  SELECT COUNT(studentname) FROM student;
  SELECT COUNT(*) FROM student;
  SELECT COUNT(1) FROM student; /*Recommended*/
 
  -- In terms of meaning, count(1) and count(*) both represent queries to all data rows.
  -- count(field) counts the number of times this field appears in the table, and ignores the situation where the field is null.  That is, records whose field is null are not counted.
  -- count(*) includes all columns, equivalent to the number of rows, and when stating the results, it contains records with field null;
  -- count(1) Use 1 to represent the code line, and when stating the result, it contains records with the field null.
  /*
  Many people think that count(1) execution efficiency will be higher than count(*), because count(*) will have a full table scan, while count(1) can query a field.  In fact, count(1) and count(*) will scan the entire table and count the number of records, including those that are null. Therefore, their efficiency can be said to be almost the same.  count (field) is different from the first two, and it will count the number of records in which the field is not null.
 
  Here are some comparisons between them:
 
  1) When the table has no primary key, count(1) is faster than count(*)
  2) When there is a primary key, the primary key is used as a calculation condition, and count (primary key) is the most efficient;
  3) If the table has only one field, count(*) is more efficient.
  */
 
  SELECT SUM(StudentResult) AS Sum FROM result;
  SELECT AVG(StudentResult) AS Average Score FROM result;
  SELECT MAX(StudentResult) AS Highest Score FROM result;
  SELECT MIN(StudentResult) AS minimum score FROM result;

Summarize

important:
The order of writing and execution of query statements
select ===> from ===> where ===> group by ===> having ===> order by ===> limit
The order of execution of query statements
from ===> where ===> group by ===> having ===> select ===> order by ===> limi