MySql learning day03: connection and query details between data tables

Primary Key:

Keyword: primary key

Features: cannot be null and must be unique.

Primary key classification:

1. Logical primary key: such as ID, does not represent actual business significance, but is only used to uniquely identify a record (recommended)
2. Business primary key: such as username, involved in actual business logic.

Primary key usage:

Method 1:

    Create table t1(

       Id int primary key,

       Name varchar(100)

);

Insert into t1 values(1,'zs');

Insert into t1 values(2,'ls');

Primary key auto-increment:

Keyword: auto_increment

   create table t4(

                     id int primary key auto_increment,

                     name varchar(100)

);

Insert into t4(name) values('zs');

Insert into t4 values(null,'ls');

Domain Integrity:

Refers to the columns (i.e. fields) of a database table that must conform to a certain data type or constraint

Data Types

length

Not null constraint: NOT NULL

Unique constraint: UNIQUE

    CREATE TABLE t5(

                     username varchar(100) NOT NULL UNIQUE,

                     gender varchar(100) NOT NULL,

                     phonenum varchar(100) UNIQUE

       );

Connections between data tables:

1. One-to-many (1*N): customers and orders. One customer can have multiple orders, and each order belongs to only one customer.

Create the customer table:

CREATE TABLE customers(

  id int,

  name varchar(100),

  address varchar(255),

  PRIMARY KEY(id)

);

Create the orders table:

CREATE TABLE orders(

       order_num int primary key,

       price float(8,2),

       status int,

       customer_id int,

CONSTRAINT customer_id_fk FOREIGN KEY(customer_id) REFERENCES customers(id)

);

Insert data:

2. Many-to-many: Use the foreign key of the third table to connect the primary keys of the two tables.

There is a many-to-many relationship between teachers and students. One teacher corresponds to multiple students, and one student is taught by multiple teachers.

Create the teacher table:

   Create table teachers(

              id int,

              name varchar(100)

              salary float(8,2),

              primary key(id)

);

Create the student table:

 Create table students(

  id int,

  name varchar(100),

  grade varchar(100),

  primary key(id)

);

The third table:

Create table teacher_student(

       t_id int,

       s_id int,

       primary key(t_id,s_id)

CONSTRAINT teacher_id_fk FOREIGN KEY(t_id) REFERENCES teachers(id),

CONSTRAINT student_id_fk FOREIGN KEY(s_id) REFERENCES students(id)

);

Insert data:

3. One-to-one: The foreign key of one table connects to the primary key of the second table. This is not necessary in actual development. You can just design a table.

Multi-table query:

Cross join (Cartesian product): all rows in the first table are multiplied by all rows in the second table. The final result is incorrect, so it is generally not used.

Inner join:

Because the result set obtained by the cross join is wrong. Therefore, the inner connection is based on the cross connection

Only the data rows in the connection table that match the connection conditions are listed, and the records that do not match will not be listed.

grammar:

Implicit syntax:

select * from customers,orders where customers.id=orders.customer_id;

Explicit syntax:

select * from customers c INNER JOIN orders o ON c.id=o.customer_id;

Outer Join:

An outer join uses one table as the base table and concatenates information from other tables. If the information exists, it is concatenated. If not, null is displayed. Outer joins are divided into left outer joins and right outer joins.

Left outer join: join with the table on the left of the keyword as the base table

Syntax: select * from customers c LEFT JOIN orders o ON c.id=o.customer_id;

Right outer join: The table to the right of the keyword is the base table

Syntax: select * from orders o RIGHT JOIN customers c ON c.id=o.customer_id;

Note: The result of table a left outer join table b and table b right outer join table a is the same.

Subquery:

In some cases, when performing a query, the condition required is the result of another select statement. At this time, a subquery will be used. The query (inner query) that is executed first to provide data for the main query (outer query) is called a subquery; subqueries are divided into nested subqueries and correlated subqueries.

Nested subqueries:

The execution of the inner query is independent of the outer query. The inner query is executed only once, and the result is used as the condition of the outer query after the execution is completed (the subquery statement in the nested subquery can be taken out and run separately.)

Grammar and exercises: Query all students taught by the teacher with id 1.

select * from students where id in(select s_id from teacher_student where t_id=1);

Correlated subqueries:

The execution of the internal query depends on the data of the external query. Each time the external query is executed, the internal query will also be executed. Each time, the outer query is executed first, a tuple in the outer query table is taken out, the data in the current tuple is passed to the inner query, and then the inner query is executed. Based on the result of the internal query execution, determine whether the current tuple meets the where condition in the external query. If so, the current tuple is a record that meets the requirements, otherwise it does not meet the requirements. Then, the external query continues to fetch the next tuple data and executes the above operations until all tuples are processed.

Grammar and Exercises: Find the number of students whose test scores are greater than the average score in each subject.

select * from score as a where a.score>(select avg(b.score) from score as b where a.cou_id=b.cou_id);

The above is the connection and query details integration between MySql data tables introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!