Today’s content

  1. DQL: query statement

    1. Sorting query
    2. Aggregation function
    3. Grouping query
    4. Paging query

  2. The constraint

  3. Relationships between multiple tables
  4. paradigm
  5. Database backup and restoration

DQL: query statement

  1. Sorting query

    • Syntax: Order by clause

      • Order by… order by… order by…

    • Sorting methods:

      • ASC: Ascending, default.
      • DESC: descending order.

    • Note:

      • If there are more than one sorting condition, the second condition will be judged only when the condition value of the current edge is the same.

  2. Aggregate function: vertical calculation of a column of data as a whole.

    1. Count: Indicates the number to be calculated
      1. You typically choose a non-empty column: the primary key
      2. count(*)
    2. Max: Calculates the maximum value
    3. Min: Calculates the minimum value
    4. Sum: calculates the sum

    5. Avg: Calculate the average value

    6. Note: Aggregate function calculations exclude null values. Solution: 1. Select columns that do not contain non-empty columns for calculation. 2

  3. Group query:

    1. Syntax: group by;

    2. Note:

      1. Query fields after grouping: grouping fields, aggregation functions
      2. What’s the difference between where and having?
        1. Where is qualified before grouping and does not participate in grouping if the conditions are not met. Having is qualified after grouping and will not be queried if the result is not satisfied
        2. Where can not be followed by aggregator functions. Having can be used to judge aggregator functions.

      — By gender. Query the average scores of male and female students respectively

      SELECT sex , AVG(math) FROM student GROUP BY sex;

      — By gender. Query the average score and number of male and female students respectively

      SELECT sex , AVG(math),COUNT(id) FROM student GROUP BY sex;

      — By gender. SELECT sex, AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex; SELECT * FROM student WHERE math > 70 GROUP BY sex;

      — By gender. Query the average score of male and female students respectively, the number of requirements: scores below 70, not to participate in the group, after the group. SELECT sex, AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;

      SELECT math, AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING math > 2;

  4. Paging query

    1. Syntax: limit start index, number of queries per page;

    2. Formula: Start index = (current page number -1) * Number of entries per page – 3 entries per page

      SELECT * FROM student LIMIT 0,3; – page 1

      SELECT * FROM student LIMIT 3,3; – page 2

      SELECT * FROM student LIMIT 6,3; – page 3

    3. Limit is a MySQL” dialect”

  5. Concept: To qualify data in a table to ensure correctness, validity, and integrity of the data.

  6. Classification:

    1. Primary key constraint: Primary key
    2. Non-null constraint: not NULL
    3. The only constraint is unique
    4. Foreign key constraints: Foreign key

  7. Non-null constraint: not NULL, a column value cannot be null

    1. CREATE TABLE stu(id INT, NAME VARCHAR(20) NOT NULL — NAME is NOT NULL);

    2. ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

    3. ALTER TABLE stu MODIFY name VARCHAR(20);

  8. Only constraint: unique, the value of a column cannot be repeated

    1. Note:
      • A unique constraint can have a NULL value, but only one record can be NULL
    2. CREATE TABLE stu(id INT, phone_number VARCHAR(20) UNIQUE — phone number);
    3. ALTER TABLE STu DROP INDEX phone_number;
    4. ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

  9. Primary key constraint: Primary key.

    1. Note:

      1. Meaning: Not empty and unique
      2. A table can have only one field primary key
      3. The primary key is the unique identification of the records in the table

    2. Create table stu(id int primary key,– add primary key name varchar(20));

    3. Alter table stu modify id int; ALTER TABLE stu DROP PRIMARY KEY;

    4. ALTER TABLE stu MODIFY ID INT PRIMARY KEY;

    5. Automatic growth:

      1. Concept: If a column is of numeric type, use auto_increment to perform automatic increment

      2. Create table STu (id int primary key auto_increment,– add primary key name varchar(20));

      3. ALTER TABLE stu MODIFY id INT;

      4. ALTER TABLE stu MODIFY ID INT AUTO_INCREMENT;

  10. Foreign key constraint: a foreign key that allows tables to be related to tables to ensure data correctness.

    1. When you create a table, you can add foreign keys

      • Syntax: create table Table name (…. Constraint foreign key name Foreign key name (references);

    2. ALTER TABLE TABLE name DROP FOREIGN KEY name;

    3. ALTER TABLE name ADD CONSTRAINT FOREIGN KEY name FOREIGN KEY name REFERENCES primary TABLE name;

    4. Cascade operation

      1. Add cascading operation syntax: ALTER TABLE name ADD CONSTRAINT FOREIGN KEY REFERENCES ON UPDATE CASCADE ON DELETE CASCADE;
      2. Classification:
        1. Cascading UPDATE: ON UPDATE CASCADE
        2. CASCADE deletion: ON DELETE CASCADE

Database design

  1. Relationships between multiple tables

    1. Classification:
      1. One to one (understanding) :
        • Such as: and id card
        • Analysis: a person has only one ID card, one ID card can only correspond to one person
      2. One to many (many to one) :
        • Such as: department and staff
        • Analysis: A department has multiple employees. One employee corresponds to only one department
      3. Many-to-many:
        • Such as students and courses
        • A student can choose many courses, and a course can be chosen by many students

    2. Realizing relationships:

      1. One to many (many to one) :
        • Such as: department and staff
        • Implementation: set up a foreign key in many parties, pointing to the primary key of one party.
      2. Many-to-many:
        • Such as students and courses
        • Implementation: Many-to-many relationships are implemented with the help of a third intermediate table. The intermediate table contains at least two fields that serve as the foreign key of the third table and point to the primary key of each table
      3. One to one (understanding) :
        • Such as: and id card
        • Implementation: one-to-one relationship, you can add a unique foreign key in either party to point to the other party’s primary key.

    3. Tab_category — CID Primary key of travel route category, automatic growth — cname Travel route category name is not empty, unique, string 100

      CREATE TABLE tab_category (
    cid INT PRIMARY KEY AUTO_INCREMENT,
    cname VARCHAR(100) NOT NULL UNIQUE
);
Copy the code

      Tab_route /* RID Rname rname 100 price rdate date cid foreign key */

      CREATE TABLE tab_route(
    rid INT PRIMARY KEY AUTO_INCREMENT,
    rname VARCHAR(100) NOT NULL UNIQUE,
    price DOUBLE,
    rdate DATE,
    cid INT,
    FOREIGN KEY (cid) REFERENCES tab_category(cid)
);
Copy the code

      /* create a user table tab_user uid primary key (uid) username length 100 (unique) password length 30 (non-empty) name Name length 100 birthday birthday sex Sex The value is a fixed-length character string. 1 telephone Mobile phone number, the value is 11 Email email address, String Length 100 */ CREATE TABLE tab_user (uid INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(100) UNIQUE NOT NULL, PASSWORD VARCHAR(30) NOT NULL, NAME VARCHAR(100), birthday DATE, sex CHAR(1) DEFAULT ‘male ‘, telephone VARCHAR(11), email VARCHAR(100) );

      /* Create favorite table tab_favorite RID travel route ID, foreign key date Favorite time uid User ID, foreign key RID and uid cannot be the same, set compound primary key, same user cannot favorites the same route two times */

      CREATE TABLE tab_favorite (rid INT, -- line id DATE DATETIME, uid INT, -- user id -- PRIMARY KEY(rid,uid), REFERENCES tab_route(RID), FOREIGN KEY(UID) REFERENCES tab_user(uid));Copy the code

  2. The paradigm of database design

    • Concept: Some specifications to follow when designing a database. To comply with the following paradigm requirements, you must first comply with all the previous paradigm requirements

      When designing relational database, follow different standard requirements and design reasonable relational database. These different standard requirements are called different paradigms. Various paradigms present sub-norms, and the higher the paradigms, the less redundancy of database. At present, there are six paradigms for relational databases: first paradigms (1NF), second paradigms (2NF), third paradigms (3NF), Bas-Coad paradigms (BCNF), fourth paradigms (4NF) and fifth paradigms (5NF, also known as perfect paradigms).

    • Classification:

      1. First Normal Form (1NF) : Each column is an indivisible atomic data item

      2. Second normal Form (2NF) : On the basis of 1NF, non-code attributes must be completely dependent on the code (on the basis of 1NF eliminate the partial function dependence of the main attribute on the main code)

        • A few concepts:
          1. Function dependence: A–>B, if the value of the A attribute (attribute group) can determine the value of the unique B attribute. B depends on A for example: student id –> name. (Student ID, course name) –> Mark
          2. Complete function dependence: A–>B, if A is an attribute group, then B attribute worth determining needs to depend on all attribute values in A attribute group. For example :(student number, course name) –> mark
          3. Partial function dependence: A- >B, if A is an attribute group, then B attribute is worth determining only to depend on some values in A attribute group. For example :(student number, course name) — > name
          4. Transfer function dependence: A–>B, B –> C. If the value of A attribute (attribute group) can determine the value of the unique B attribute, and if the value of B attribute (attribute group) can determine the value of the unique C attribute, then the C transfer function depends on A e.g. student number –> department name, department name –> dean
          5. Code: If an attribute or attribute group in a table is completely dependent on all other attributes, the attribute (attribute group) is called the code of the table. For example, the code in the table is: (Student number, course name)
            • Primary attribute: All attributes in the code attribute group
            • Non-primary attribute: attributes that are not included in the code attribute group

      3. Third normal Form (3NF) : On 2NF basis, any non-primary attribute is not dependent on other non-primary attributes (transitive dependency is eliminated on 2NF basis)

Database backup and restoration

  1. The command line:
    • Grammar:
      • Backup: mysqldump -u username -p password Database name > save path
      • Restore:
        1. Logging In to the Database
        2. Creating a database
        3. Using a database
        4. Execute file. Source File path
  2. Graphic chemical tools: