mysql database notes

1. DQL: query statement
   1. Sort query
   2. Aggregate function
   3. Group query
   4. Paging query

   1. constraint

   2. Relationship between multiple tables

   3. normal form

   4. Backup and restore of database
      DQL: query statement

   5. Sort query

      • Syntax: order by clause

        • order by sort field 1, sort by 1, sort field 2, sort by 2

      • Sort by:

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

      • be careful:

        • If there are multiple sorting conditions, the second condition will be judged only when the condition values of the current edge are the same.

   6. Aggregate function: perform vertical calculation by taking a column of data as a whole.

      1. Count: count
         1. Generally, non empty columns are selected: primary key
         2. count(*)
      2. max: calculate maximum
      3. min: calculate the minimum value
      4. sum: Calculation and
      5. avg: calculate average
      • Note: the calculation of aggregate function excludes null values.
        Solution:
        1. Select non empty columns for calculation
        2. IFNULL function

   7. Group query:

      1. Syntax: group by group field;

      2. be careful:

         1. Fields queried after grouping: grouping fields and aggregate functions
         2. What's the difference between where and having?
            1. where is limited before grouping. If the conditions are not met, it will not participate in grouping. having is qualified after grouping. If the result is not satisfied, it will not be queried
            2. where cannot be followed by the aggregate function. having can judge the aggregate function.

         – grouped by gender. Query the average scores of male and female students respectively

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

– grouped 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;

– grouped by gender. Query the average scores of male and female students respectively. Number requirements: those with scores lower than 70 will not participate in the grouping

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

– grouped by gender. Query the average scores of male and female students respectively. The number requirements: those with a score less than 70 will not participate in the grouping, and will not participate in the grouping. The number of people should be more than 2

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

1. Paging query
   1. Syntax: the index starting from limit and the number of queries per page;
   2. Formula: starting index = (current page number - 1) * number of entries displayed per page
   – 3 records 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 It's a MySQL"dialect"
4. ## constraint
* Concept: limit the data in the table to ensure the correctness, effectiveness and integrity of the data.	
* Classification:
	1. Primary key constraint: primary key
	2. Non empty constraint: not null
	3. Unique constraint: unique
	4. Foreign key constraints: foreign key

* Non empty constraint: not null，The value of a column cannot be null null
	1. Add constraints when creating tables

CREATE TABLE stu(
				id INT,
				NAME VARCHAR(20) NOT NULL -- name Is not empty
			);

1. After creating the table, add a non empty constraint

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

1. Delete non empty constraint for name
2. ALTER TABLE stu MODIFY NAME VARCHAR(20);
3. • Unique constraint: unique, the value of a column cannot be repeated
     1. be careful:
        • Unique constraints can have null values, but only one record can be null
     2. When creating a table, add unique constraints

CREATE TABLE stu(
				id INT,
				phone_number VARCHAR(20) UNIQUE -- cell-phone number
			);

1. Delete primary key
2. --Error alter table stu modify id int; ALTER TABLE stu DROP PRIMARY KEY;
   4. After creating the table, add the primary key

ALTER TABLE stu MODIFY id INT PRIMARY KEY;

1. Automatic growth:
   1. Concept: if a column is numeric, use auto_increment can be used to complete automatic growth

   	2. When creating a table, add a primary key constraint and complete the primary key self growth
   

create table stu(
				id int primary key auto_increment,-- to id Add primary key constraint
				name varchar(20)
			);

		3. Delete auto growth

ALTER TABLE stu MODIFY id INT;

1. Add auto growth
2. `ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
3. Foreign key constraint: foreign key, which enables the table to generate a relationship with the table, so as to ensure the correctness of the data.
   1. When creating a table, you can add foreign keys
   *Syntax:

create table Table name(
					....
					Foreign key column
					constraint Foreign key name foreign key (Foreign key column name) references Main table name(Main table column name)
				);

1. Delete foreign key

ALTER TABLE Table name DROP FOREIGN KEY Foreign key name;

3. After creating the table, add the foreign key

ALTER TABLE Table name ADD CONSTRAINT Foreign key name FOREIGN KEY (Foreign key field name) REFERENCES Main table name(Main table column name);

1. Cascade operation
   1. Add cascade operation
   Syntax: ALTER TABLE table name ADD CONSTRAINT foreign key name
   FOREIGN KEY field name REFERENCES main table name (main table column name) ON UPDATE CASCADE ON DELETE CASCADE;
   2. Classification:
   1. Cascade update: ON UPDATE CASCADE
   2. Cascade delete: ON DELETE CASCADE

Database design

1. Relationship between multiple tables
	1. Classification:
		1. one-on-one(understand): 
			* Such as: person and ID card
			* Analysis: a person has only one ID card, and one ID card can only correspond to one person
		2. One to many(Many to one): 
			* E.g. departments and employees
			* Analysis: a department has multiple employees, and one employee can only correspond to one department
		3. Many to many:
			* E.g. students and courses
			* Analysis: a student can choose many courses, and a course can also be selected by many students
	2. Implementation relationship:
		1. One to many(Many to one): 
			* E.g. departments and employees
			* Implementation method: establish a foreign key on one of the multiple parties and point to the primary key of one party.
		2. Many to many:
			* E.g. students and courses
			* Implementation method: the third intermediate table is needed to implement the many to many relationship. The middle table contains at least two fields, which are used as the foreign keys of the third table and point to the primary keys of the two tables respectively
		3. one-on-one(understand): 
			* Such as: person and ID card
			* Implementation method: one-to-one relationship. You can add a unique foreign key on either side to point to the primary key of the other party.

	3. case
		-- Create tourism route classification table tab_category
		-- cid Tourism route classification, primary key, automatic growth
		-- cname Tourism route classification name is not empty, unique, string 100
		CREATE TABLE tab_category (
			cid INT PRIMARY KEY AUTO_INCREMENT,
			cname VARCHAR(100) NOT NULL UNIQUE
		);
		
		-- Create tour route table tab_route
		/*
		rid Tourist routes, primary key, automatic growth
		rname Travel route name is not empty and unique, string 100
		price Price
		rdate Launch time, date type
		cid Foreign key, category
		*/
		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)
		);
		
		/*Create user table tab_user
		uid User primary key, self growing
		username The user name is 100 long, unique and non empty
		password Password length: 30, non empty
		name Real name length 100
		birthday birthday
		sex Gender, fixed length string 1
		telephone Phone number, string 11
		email Mailbox, 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 tabletab_ favorite
		rid Travel route id, foreign key
		date Collection time
		uid User id, foreign key
		rid And uid cannot be duplicate. Set the composite primary key. The same user cannot collect the same circuit twice
		*/
		CREATE TABLE tab_favorite (
			rid INT, -- line id
			DATE DATETIME,
			uid INT, -- user id
			-- Create composite primary key
			PRIMARY KEY(rid,uid), -- composite keys 
			FOREIGN KEY (rid) REFERENCES tab_route(rid),
			FOREIGN KEY(uid) REFERENCES tab_user(uid)
		);

	
2. Paradigm of database design
	* Concept: some specifications to follow when designing a database. To follow the latter paradigm requirements, you must first follow all the previous paradigm requirements

		When designing relational database, we should comply with different specification requirements and design a reasonable relational database. These different specification requirements are called different paradigms. Various paradigms present sub specifications. The higher the paradigm, the smaller the database redundancy.
		At present, there are six paradigms of relational database: the first paradigm (1) NF),Second paradigm (2) NF),The third paradigm (3) NF),Bath-Codd paradigm( BCNF),The fourth paradigm(4NF)And the fifth paradigm (5) NF，Also known as perfect paradigm).

	* Classification:
		1. First paradigm (1) NF): Each column is an indivisible atomic data item
		2. Second paradigm (2) NF): At 1 NF On the basis of, the non code attribute must be completely dependent on the code (in 1 NF Eliminate partial functional dependence of non main attributes on main codes)
			* Several concepts:
				1. Functional dependencies: A-->B,If passed A attribute(Attribute group)Unique values can be determined B The value of the property B Depend on A
					For example: student number-->Name. (student number, course name) --> fraction
				2. Full functional dependency: A-->B， If A Is an attribute group, then B Attribute value determination needs to depend on A All attribute values in the attribute group.
					For example: (student number, course name) --> fraction
				3. Partial functional dependencies: A-->B， If A Is an attribute group, then B Attribute value determination only depends on A Some values in the attribute group are sufficient.
					For example: (student number, course name) -- > full name
				4. Transfer function dependency: A-->B, B -- >C . If passed A attribute(Attribute group)Unique values can be determined B Property through B The value of the attribute (attribute group) determines the unique value C Property, it is called C The transfer function depends on A
					For example: student number-->Department name-->Dean of Department
				5. Code: if an attribute or attribute group in a table is completely dependent on all other attributes, it is called this attribute(Attribute group)Code for 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: the attribute of the over code attribute group
					
		3. The third paradigm (3) NF): In 2 NF On this basis, any non primary attribute does not depend on other non primary attributes (in 2 NF (eliminate delivery dependency)

Backup and restore of database

1. Command line:
	* Syntax:
		* Backup: mysqldump -u user name -p Password database name > Saved path
		* Restore:
			1. Login database
			2. Create database
			3. Use database
			4. Execute the file. source File path
2. Graphical tools: