MySQL database basic operations (III)

MySQL Supplement:

MySQL is a relational database, a relational database, a database based on a relational model,
The various entities in the real world, as well as the various connections between entities, are represented by a relational model (table).
#关系模型就是指二维表格模型, so a relational database is a data organization composed of two-dimensional tables and their connections.

# entity: is the data object (can be type, or can be non-type)

E-r diagram (Entity Relationship model)

The E-r graph, also known as the entity- contact Graph (Entities Relationship Diagram), provides methods for representing entity types, attributes, and connections to describe the conceptual model of the real world. It is an effective way to describe the conceptual model of real-world relations. is a way to represent a conceptual relationship model. Use "Rectangle box" to represent the entity type, the name of the entity is indicated in the rectangle box, the attribute of the entity is represented by "ellipse", and "solid line" is used to connect it with the "entity type" of the corresponding relation; Type (1:1,1:n or m:n) connected to the entity type, respectively, and on the "solid segment" side label

foreign KEY constraints FOREING KEY

FOREIGN KEY constraints FOREIGN KEY , to maintain data consistency, integrity to achieve one-to-one or one-to-many relationships.

(Because a table only has one type of information.) Use foreign key to make reference, ensure data consistency, can reduce data redundancy.

requirements for FOREIGN KEY constraints:

The storage engine for a data table can only be InnoDB foreign key columns and reference column data types consistent foreign key must be associated to the key, the general situation is related to, the other table's primary key

#Example:##表aMysql>CREATE TABLE ' a ' (-a_id int primary KEY,A_name varchar (20) notNULL-) insert into a values (1,'A1'), (2,'A2'ALTER TABLE A modify A_ID int auto_increment;show CREATE TABLE A;alter table A auto_increment= 6;#can only be adjusted to large, not to be small, a table with only one auto_increment, and the general and primary key together. Insert into a (a_name) value ('A6');##表bCREATE table ' B ' (b_id int primary key,b_name varchar (20) notnull,fy_id int notnull,constraint ab_id foreign Key (fy_id) references ' a ' (a_id));#Delete foreign keyALTER TABLE ' B ' drop foreign key ab_id;#Add foreign KeyMysql>ALTER TABLE ' B '-add constraint ab_id foreign key (fy_id) references ' a ' (a_id);#only one type of information exists for a table. 

One -to-many relationships

For example, a college can have a large number of students, and a student belongs to only one college (usually), and the relationship between the college and the student is one-to-many relationships, which is achieved through a foreign key association.

#Example:##创建学院表Mysql>CREATE TABLE Department (-d_id int primary key auto_increment,D_name varchar (20) notNULL- ); ##创建学生表Mysql>CREATE TABLE Student (-s_id int primary key auto_increment,S_name varchar (20) notNULL,-dept_id int notNull#a different column, a primary key associated to another table-constraint sd_id foreign key (dept_id) References Department (D_ID)- );#Inserting DataMysql>INSERT into ' Department ' (' D_name ')VALUES ('School of Computer science'),    ('College of Foreign Languages')    - ; Query OK,2 Rows affected (0.10sec) Records:2duplicates:0 Warnings:0mysql>INSERT into ' student ' (' s_name ', ' dept_id ')VALUES ('S1', 1),    ('S2', 2),    ('S3', 2)    - ; Query OK,3 Rows affected (0.08sec) Records:3 duplicates:0 warnings:0

a pair of relations

For example, students have a school number, name, college, but students have some such as telephone, home address and other more private information, this information will not be placed in the student table, will create a new student's details table to store. The relationship between the student table and the student's detail table is a one-to-one relationship, because a student has only a single piece of detailed information. This relationship is achieved by means of a foreign key plus a primary key.

#Example:#Student Table:Mysql>DESC ' student ';+---------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+---------+-------------+------+-----+---------+----------------+| ID | Int (11) | NO | PRI | NULL | auto_increment | | name | varchar (20) |     NO | |                NULL | || dept_id | Int (11) | YES | MUL |                NULL | |+---------+-------------+------+-----+---------+----------------+3 rowsinchSet (0.06sec)#Create a detailed student table:Mysql>CREATE TABLE ' student_details ' (-' id ' INT PRIMARY KEY,' Sex ' VARCHAR (10) notNULL,-' age ' INT,"Address" VARCHAR (comment)'Home Address',      ' Parents ' VARCHAR (20),      ' Home_num ' VARCHAR (20),    -FOREIGN KEY (' id ') REFERENCES ' student ' (' s_id '));#Primary key mapping primary keyQuery OK, 0 rows affected (0.67sec) insert into Student_details value (2,'male', 18,'Changsha, Hunan','good dad .','123456789'); INSERT into Student_details value (4,'male', 20,'Changsha, Hunan','dad Li','12541656469'); INSERT into Student_details value (5,'male', 20,'Changsha, Hunan','dad Li','12541656469');

Many-to-many relationships

For example, students have to enroll in elective courses, one student can enroll in more than one course, and a lot of students enroll in a course, so the student table and curriculum form a many-to-many relationship. For a many-to-many relationship, you need to create a third relational table that implements this relationship in the form of a foreign key plus a primary key in the relational table.

#Example:#To Create a curriculum:Mysql>CREATE TABLE ' Course ' (-' id ' INT PRIMARY KEY auto_increment,' Name ' VARCHAR (20) Not NULL- ); Query OK, 0 rows affected (1.18sec) insert into course (name) VALUES ('python'),('Java'),('English'),('Japanese Language');#Many-to-many relationship tables for students and courses (intermediate table)Mysql>CREATE TABLE ' select ' (-' s_id ' INT,-' crs_id ' INT,#prevent the same student from choosing the same course multiple times-PRIMARY KEY (' s_id ', ' crs_id '),#Students must be students in order to choose a class-FOREIGN KEY (' s_id ') REFERENCES ' student ' (' s_id '),#only courses exist to be selected-FOREIGN KEY (' crs_id ') REFERENCES ' course ' (' id ')- ); Query OK, 0 rows affected (0.50sec) insert INTO ' SELECT ' Values ((1,3), (bis), (insert INTO ' SELECT ' Values (2, 3), (2,1), (2,4);

referential actions for foreign KEY constraints:

Mysql> SELECT *From ' Department ';+----+----------------------+------+| d_id | name |  Code |+-----+----------------------+------+| 1 |    Computer Academy |  1 | | 2 |    Foreign Language Institute | 2 |+-----+----------------------+------+MySQL> SELECT *From ' student ';+----+--------+--------+| ID | name |  dep_id |+----+--------+--------+| 1 |      Budong |  1 | | 2 |      Tuple |  1 | | 3 |      which | 2 |+----+--------+--------+MySQL> DELETE from ' Department ' WHERE ' d_id ' =1; ERROR1451 (23000): Cannot deleteorUpdate a parent row:a FOREIGN KEY constraint fails (' MyDB '. ' Student ', constraint ' stu_dep_for_key ' foreign key (' DEP _id ') REFERENCES ' Department ' (' ID '))#You cannot delete a record in a parent table because of a foreign key association reasonMySQL> UPDATE ' student ' SET ' dept_id ' =1 WHERE ' dept_id ' =2; MySQL> DELETE from ' Department ' WHERE ' d_id ' =2;#because dep_id in the student table has no value associated with id=2 data in department, department data in id=2 tables can be deleted

Some of the more common functions in queries

#ask for the maximum ageMysql>SELECT MAX (' age ') from ' student_details ';#ask for the minimum ageMysql>SELECT MIN (' age ') from ' student_details ';#sumMysql>SELECT SUM (' age ') from ' student_details ';#Find AverageMysql>SELECT AVG (' age ') from ' student_details ';#RoundingMysql>SELECT ROUND (AVG (' age ')) from ' student_details ';#StatisticsMysql> Select COUNT (s_id) fromstudent;#View current TimeMysql>SELECT now (); MySQL>CREATE TABLE TB1 (-ID INT,Name VARCHAR (20),    -dates DATETIME-); MySQL> INSERT into TB1 value (1,'Canon', now ());

Job

1. Set up the 5-sheet list of colleges, students, students, details, courses and course selection. It is simple to describe its table relation and how to realize its table relation.

MySQL database basic operations (III)