With that in mind, next part we’ll start our review of Android data
1. Sort query 2. Aggregate function 3. Group query 4. Paging query 2. Constraints 3. Paradigm 5. Database backup and restoreCopy the codeDQL: query statement
1. Sorting query * syntax: Order by clause * Order by sorting field 1 Sorting field 1 sorting field 2 Sorting field 2... * Sort: * ASC: ascending, default. * DESC: descending order. * Note: * If there are more than one sort condition, the second condition will only be judged if the value of the current condition is the same. 2. Aggregation function: Take a column of data as a whole and make longitudinal calculation. Count (*) 2. Max: calculates the maximum value 3. Min: calculates the minimum value 4. Solution: 1. Select no non-empty columns to calculate 2. 1. Syntax: group by Group field; 2. What's the difference between where and having? 1. Where is qualified before grouping. If the condition is not met, the group does not participate in the grouping. If the result is not satisfied, the group will not be queried. 2. If the result is not satisfied, the group will not be queried. -- Grouping by gender. SELECT sex, AVG(math) FROM student GROUP BY sex; -- Grouping by gender. SELECT sex, AVG(math),COUNT(id) FROM student GROUP BY sex; -- Grouping by gender. SELECT sex, AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex; -- Grouping by gender. The average scores of male and female students are respectively queried. The number of students is required: those whose scores are lower than 70 will not participate in the grouping. SELECT COUNT(id), AVG(math),COUNT(id) FROM student WHERE sex HAVING COUNT(id) > 70; SELECT COUNT(id) FROM student WHERE sex > 70 GROUP BY sex HAVING sex > 2;Copy the code
1. Syntax: limit starts the index, and the number of items to be queried per page. SELECT * FROM student LIMIT 0,3; SELECT * FROM student LIMIT 0,3; -- 第1页 SELECT * FROM student LIMIT 3; -- 第2页 SELECT * FROM student LIMIT 6; 3. Limit is a MySQL" dialect"Copy the codeThe constraint
* Concept: The data in the table is limited to ensure the correctness, validity and integrity of the data. * Category: 1. Primary key constraint: primary key constraint: not null 3. 4. Foreign key * non-null constraint: not NULL 1. CREATE TABLE stu(ID INT, NAME VARCHAR(20) NOT NULL -- NAME is NOT NULL); ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL; ALTER TABLE stu MODIFY name VARCHAR(20) ALTER TABLE stu MODIFY name VARCHAR(20);Copy the code
CREATE TABLE STu (id INT, phone_number VARCHAR(20) UNIQUE (id INT, phone_number VARCHAR(20) unique); * Note that in mysql, columns with unique constraints can have multiple NULL valuesCopy the code
ALTER TABLE STU DROP INDEX phone_number; 3. ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE; * Primary key constraint: primary key constraint 2. Only one field in a table can be the primary key. 3. Create table stu(id int primary key, id vARCHar (20)); create table stu(ID int primary key, id varchar(20)); Alter table stu modify id int; ALTER TABLE stu DROP PRIMARY KEY; 4. ALTER TABLE stu MODIFY ID INT PRIMARY KEY; 5. The automatic growth: 1. Concept: if a column is a value type, 2 USES auto_increment can be done worth automatic growth. Create table STU (ID int primary key AUTO_increment) create table STU (ID int primary key auto_increment) CREATE table STU (ID int primary key auto_increment)Copy the code
ALTER TABLE stu MODIFY ID INT; ALTER TABLE stu MODIFY ID INT AUTO_INCREMENT; * Foreign key constraint: The foreign key constraint enables the table to have a relationship with the table to ensure the correctness of data. 1. When creating a table, you can add foreign key * syntax: create table Table name (....) Foreign key columns * Foreign key columns * Foreign key columns * Foreign key columns * ALTER TABLE TABLE name DROP FOREIGN KEY FOREIGN KEY name; ALTER TABLE TABLE name ADD CONSTRAINT FOREIGN KEY name (FOREIGN KEY name) REFERENCES Primary TABLE name (primary TABLE name);Copy the code
4. Cascading Operations 1. Add the syntax for cascading operations: ALTER TABLE TABLE name ADD CONSTRAINT FOREIGN KEY name REFERENCES Primary TABLE name ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE name ADD CONSTRAINT FOREIGN KEY name REFERENCES Primary TABLE name ON UPDATE CASCADE ON DELETE CASCADE; ON UPDATE CASCADE 2. ON DELETE CASCADECopy the codeDesign of database
A person has only one ID card, and one ID card can only correspond to one person. One to many (many to one) : * Such as: departments and employees * Analysis: a department has multiple employees, one employee can only correspond to one department 3. A student may choose many courses, and a course may be chosen by many students. 1. One-to-many (many-to-one) : * such as: departments and employees * Implementation: in the party of the establishment of a foreign key, pointing to a party of the primary key. 2. Many-to-many: * for example: students and courses * Implementation: The many-to-many relationship implementation requires the use of a third intermediate table. The middle table contains at least two fields that serve as foreign keys to the third table and point to the primary key 3 of each table. One to one (understanding) : * such as: people and ID cards * implementation: one to one relationship, you can add a unique foreign key pointing to the other party's primary key. 3. Case -- create travel route classification table tab_category -- CID travel route classification primary key, automatic growth -- CNAME Travel route classification name is not empty, unique, Character string 100 CREATE TABLE tab_category (CID INT PRIMARY KEY AUTO_INCREMENT, CNAME VARCHAR(100) NOT NULL UNIQUE); -- create a tour_route table tab_route /* RID Tour_route primary key, automatic increment rName Tour_route name, non-null, unique, string 100 price Price rDate Date, date type 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) ); /* Create user table tab_user uid Primary key, increment from username username length 100, unique, non-null password Length 30, non-null name Real name length 100 birthday birthday sex gender, Fixed-length string 1 telephone phone number, string 11 Email email, 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 id, foreign key date favorite time uid user ID, foreign key rid and UID cannot be the same, set main key, */ CREATE TABLE tab_favorite (rid INT, -- line ID DATE DATETIME, uid INT, FOREIGN KEY(RID) REFERENCES TAB_route (RID) FOREIGN KEY(uid) REFERENCES tab_user(uid) );Copy the code
2. Database design Paradigm * Concept: the specifications that need to be followed when designing a database. In order to follow the requirements of the following paradigm, we must first follow all the requirements of the previous paradigm. When designing a relational database, we must comply with different specification requirements to design a reasonable relational database. These different specification requirements are called different paradigms. There are currently six paradigms for relational databases: first Normal Form (1NF), second normal form (2NF), Third Normal Form (3NF), Bas-Coder normal Form (BCNF), Fourth Normal Form (4NF), and fifth Normal Form (5NF, also known as perfect Normal form). * 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 properties must be completely dependent on the code (on the basis of 1NF eliminate the partial functional dependence of the primary properties on the primary code) * several concepts: 1. Function dependence: A- >B, if the value of A attribute (attribute group), you can determine the value of the unique B attribute. B is said to depend on A for example: student number --> name. 2. Complete functional dependence: A-->B, if A is an attribute group, then the value of attribute B depends on all attribute values in attribute group A. For example: (student number, course name) --> grade 3. Partial function dependence: A-->B. If A is an attribute group, then attribute B is worth determining only by relying on some value in attribute group A. 4. Transfer function dependencies: A-->B, B --> C. If the value of attribute A (attribute group) can determine the unique value of attribute B, and the value of attribute C can be determined by the value of attribute B (attribute group), then the transfer function of C depends on A. For example: student number --> department name, department name --> dean 5. Code: If an attribute or attribute group is completely dependent on all other attributes in a table, the attribute (attribute group) is called the code of the table. For example, the code in this table is: (student number, course name) * Primary attribute: all attributes in the code attribute group * Non-primary attribute: attributes except for the code attribute group 3. Third normal form (3NF) : based on 2NF, any non-primary attribute does not depend on any other non-primary attribute (eliminates transitional dependencies based on 2NF)Copy the codeBackup and restore the database
Mysqldump -u user name -p password database name > save path * restore: 1. Log in to the database 2. Create the database 3. Use the database 4. 2. Graphics tool: SQLyog We can use this toolCopy the code