catalogue

1. Primary key constraint

Format 1: set when writing fields when creating a table

Format 2: create a table in. Declare the primary key constraint after the column

Format 3: after creating a table, modify the table structure and declare the columns as primary keys

2 . Unique constraint

Format 1: set when writing fields when creating a table

Format 2: create a table in. Declare the primary key constraint after the column

Format 3: after creating a table, modify the table structure and declare the columns as primary keys

3 non NULL constraint

4. MySQL specific auto growth column

5 supplement ---- truncate the decimal point

6. Access and query multiple tables

7 where field in (multiple conditions)

8 like fuzzy query

9 paging

10 difficulty 1 [aggregate avg+count + grouping + intercept decimal point]

1. Primary key constraint

• Keywords: primary key

Format 1: set when writing fields when creating a table

• Only one primary key can be set

• Cannot insert primary key repeatedly

• The inserted primary key cannot be null

  CREATE TABLE pk01(
      id INT PRIMARY KEY ,
      `name` VARCHAR(32)
  );

Format 2: create a table in. Declare the primary key constraint after the column

• Features: multiple columns can be declared as one primary key (joint primary key)

  CREATE TABLE pk02(
      id INT,
      `name` VARCHAR(20),
      CONSTRAINT PRIMARY KEY(id)
  );

Format 3: after creating a table, modify the table structure and declare the columns as primary keys

CREATE TABLE pk01(
    id INT ,
    `name` VARCHAR(32)
);
ALTER TABLE pk03 ADD CONSTRAINT PRIMARY KEY(id);

2 . Unique constraint

• Keywords: unique

Format 1: set when writing fields when creating a table

*Columns with unique constraints cannot be inserted repeatedly
 *null values are allowed for columns that set unique constraints

CREATE TABLE pk01(
    id INT UNIQUE ,
    `name` VARCHAR(32)
);

Format 2: create a table in. Declare the primary key constraint after the column

CREATE TABLE pk02(
    id INT,
    `name` VARCHAR(20),
    CONSTRAINT UNIQUE(id)
);

Format 3: after creating a table, modify the table structure and declare the columns as primary keys

CREATE TABLE pk01(
    id INT ,
    `name` VARCHAR(32)
);
ALTER TABLE pk03 ADD CONSTRAINT UNIQUE(id);

3 non NULL constraint

• Keyword: not null

• characteristic:

  • The set column can insert data repeatedly

  • null is not allowed

CREATE TABLE pk01(
    id VARCHAR(20) NOT NULL ,
    `name` VARCHAR(32)
);

4. MySQL specific auto growth column

• Keyword: auto_increment

• characteristic:

  • Columns modified by automatic growth columns will be automatically accumulated and maintain the data in the columns themselves

• Limiting conditions:

  • The type of the modified column must be numeric (int, float, double)

  • Must be modified by key (primary key, unique constraint)

CREATE TABLE ai01(){
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(20)
}

5 supplement ---- truncate the decimal point

• Encountered in operation -- intercept decimal point

• Keyword: truncate (the number to be intercepted and the number of digits to be intercepted);

SELECT cno `curriculum`, TRUNCATE(AVG(degree),2) `Average score` FROM score GROUP BY cno ;

6. Access and query multiple tables

• Syntax: select * | name from b table 1, table 1 alias, table 2, table 1 alias where condition;

• The two tables can be connected by conditions

-- 20,Query all students Sname,Cno and Degree column(Two meter connection). 
SELECT sname,cno,degree FROM student s , score c WHERE s.sno = c.sno ;

7 where field in (multiple conditions)

• Multiple query criteria can be written in parentheses after the where field

-- 5, query Score A record of 85, 86 or 88 in the table.
SELECT * FROM score WHERE degree IN(85,86) OR degree = 88 ; 

8 like fuzzy query

• Field 1 like '% field 2%': field 2 contained in field 1

• Field 1 like 'field 2%': field 1 starts with field 2

• Field 1 like '% field 2': field 1 ends with field 2

-- 16,query Student Records of students whose surname is not "Wang" in the table.
SELECT * FROM student WHERE sname NOT LIKE 'king%'; 
​
- Pass title(Fuzzy query)And price inquiry books
SELECT * FROM book WHERE title LIKE '%two%' AND price = 100 ;
​

9 paging

• Keywords: limit starting index, pages per page

• Formula: starting index = (Pages - 1) * pages per page

  # Syntax: select * from table name limit startIndex, pageSize;
  # Parameter 1: start index, starting from 0
  # Parameter 2: pageSize number of pages per page
  # Page 1, 2 entries per page
  SELECT * FROM person LIMIT 0,2 ;
  # Page 2
  SELECT * FROM person LIMIT 2,2 ;
  # Page 3
  SELECT * FROM person LIMIT 4,2 ;
  # Page pageNum, startIndex = (pageNum-1) * pageSize
  # Page 2, 3 articles per page
  SELECT * FROM person LIMIT 3,3 ;
  # Page 6, 5 articles per page
  SELECT * FROM person LIMIT 25,5 ;

  10 difficulty 1 [aggregate avg+count + grouping + intercept decimal point]

• analysis:

  • select cname the course to query

  • TRUNCATE(AVG(degree),2) calculate the average score of the column and intercept the decimal point

  • From score s, course C the table to query, and specify the alias of the table

  • WHERE s.cno = c.cno condition query, connect the two tables according to the corresponding foreign key

  • GROUP BY cname group the course names, and the average score can be used to calculate the average score of the grouped courses in a single column

  • Having count (degree) > = 5 condition constraints are performed after grouping. Only the groups that meet the conditions are counted and the single column average score is obtained

-- 19,query Score The average score of the course with at least 5 grade records in the table.
SELECT cname , TRUNCATE(AVG(degree),2) FROM score s , course c WHERE s.cno = c.cno GROUP BY cname HAVING COUNT(degree) >= 5 ;