Today’s content

DQL: query statement 1. Sort query 2. Aggregate function 3. Group query 4. Paging query 2. Constraint 3. Paradigm 5. Database backup and restoreCopy the code

The constraint

* Concept: The data in the table is qualified to ensure the correctness, validity and completeness of the data. * category: 1. Primary key constraint: primary key; 2. Foreign key constraint: foreign key * Non-null constraint: not null. The value cannot be null. CREATE TABLE stu(id INT, NAME VARCHAR(20) NOT NULL -- NAME is NOT NULL); 2. After the TABLE is created, add a non-null constraint ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL. ALTER TABLE stu MODIFY name VARCHAR(20); CREATE TABLE stu(id INT, phone_number VARCHAR(20) unique -- add unique constraint); * Note that in mysql, columns with unique constraints can have multiple NULL values 2. ALTER TABLE STu DROP INDEX phone_number; ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE; * Primary key constraint: primary key. Note: 1. Meaning: Non-empty and unique 2. A table can have only one primary key. Create table stu(id int primary key,-- add primary key name varchar(20)); Alter table stu modify id int; ALTER TABLE stu DROP PRIMARY KEY; 4. After the TABLE is created, add PRIMARY KEY ALTER TABLE stu MODIFY ID INT PRIMARY KEY. Concept: If a column is of numeric type, use auto_increment to increment a column. Create table STu (id int primary key auto_increment,-- add primary key name varchar(20)); ALTER TABLE stu MODIFY id INT; ALTER TABLE stu MODIFY ID INT AUTO_INCREMENT; * Foreign key constraint: foreign key, let table to table relationship, so as to ensure the correctness of data. 1. When creating a table, add a foreign key * syntax: create table name (....) Constraint foreign key name Foreign key name (references); ALTER TABLE TABLE name DROP FOREIGN KEY name. ALTER TABLE name ADD CONSTRAINT FOREIGN KEY name FOREIGN KEY name REFERENCES primary TABLE name ALTER TABLE name ADD CONSTRAINT FOREIGN KEY name 1. Add the syntax of the cascading operation: ALTER TABLE name ADD CONSTRAINT FOREIGN KEY REFERENCES ON UPDATE CASCADE ON DELETE CASCADE; 1. CASCADE UPDATE: ON UPDATE CASCADE 2. CASCADE deletion: ON DELETE CASCADECopy the code

Database design

One person has only one ID card, and one ID card can only correspond to one person. One-to-many (many-to-one) : * For example, departments and employees * Analysis: There are multiple employees in a department. One employee corresponds to only one department. 3. One student can choose many courses, and one course can be chosen by many students. 1. One-to-many (many-to-one) : * For example, department and employee * Implementation: Many-to-many relationships are implemented with the help of a third intermediate table. The intermediate table contains at least two fields that are the foreign keys of the third table and point to the primary keys of the two tables. 3. One-to-one (understand) : * such as: and ID card * implementation: one-to-one relationship implementation, you can add a unique foreign key in either party to point to the other party's primary key. Tab_category -- CID primary key for cid -- cname name for cid not empty, unique, String 100 CREATE TABLE tab_category (CID INT PRIMARY KEY AUTO_INCREMENT, Cname VARCHAR(100) NOT NULL UNIQUE); Tab_route /* RID tab_route /* rid tab_route /* tab_route /* RID tab_route /* TAB_route /* RID tab_route /* * RID tab_route /* * RID tab_route /* * RID tab_route /* * */ 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 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 id, foreign key date favorite time uid user id, foreign key rid and uid cannot be the same. */ CREATE TABLE tab_favorite (rid INT, -- line id DATE DATETIME, uid INT, -- user id -- PRIMARY KEY(RID,uid) -- FOREIGN KEY(RID) REFERENCES tab_route(RID), FOREIGN KEY(uid) REFERENCES tab_user(uid) ); 2. Paradigms for database design * Concepts: Some specifications to follow when designing a database. To follow the following paradigm requirements, must first follow all the previous paradigm requirements when designing a relational database, comply with different specification requirements, design a reasonable relational database, these different specification requirements are called different paradigms, various paradigms present sub-specifications, the higher the database redundancy is smaller. 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, the partial function dependence of the main attribute on the main code is eliminated) * Several 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. 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) --> score 3. Partial function dependence: A-->B, if A is an attribute group, then B attribute worth determining only needs to depend on some values in A attribute group. For example: (student id, course name) --> name 4. Transfer function dependencies: A-->B, B --> C. If the value of A attribute (attribute group) can determine the value of unique B attribute, and the value of C attribute (attribute group) can determine the value of unique C attribute, then the C transfer function is said to depend on A for example: Student id --> 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 codes in the table are: (Student number, course name) * Primary attribute: All attributes in the attribute group * Non-primary attribute: except the attributes in the 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)Copy the code

Database backup and restoration

-u username -p password database name > Save path * Restore: 1. Log in to the database 2. Create the database 3. Use the database 4. 2. Graphic tools:Copy the code