Shulou(Shulou.com)06/01 Report--

Primary key:

Keyword: primary key

Features: can not be null, and unique.

Primary key classification:

Logical primary key: for example, ID, which does not represent the actual business meaning, is only used to uniquely identify a record (recommended) business primary key: for example, username, which participates in the actual business logic.

Primary key usage:

Method 1:

Create table T1 (Id int primary key, Name varchar (100)); Insert into T1 values (1djinzs'); Insert into T1 values (2jingls')

The primary key automatically grows:

Keyword: auto_increment

Create table T4 (id int primary key auto_increment, name varchar); Insert into T4 (name) values ('zs'); Insert into T4 values (null,'ls')

Domain integrity:

The columns (that is, fields) of a database table must conform to a specific data type or constraint

Data type

Length

Non-empty constraints: 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: customers and orders. A customer can have multiple orders, and each order belongs to only one customer.

Create a customer table:

CREATE TABLE customers (id int, name varchar, address varchar, PRIMARY KEY (id))

Create an order table:

CREATE TABLE orders (order_num int primary key, price float (8 customer_id 2), status int, customer_id int,CONSTRAINT customer_id_fk FOREIGN KEY (customer_id) REFERENCES customers (id))

Insert data:

two。 Many-to-many: connect the primary keys of two tables with the foreign keys of the third table.

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 a teacher table:

Create table teachers (id int, name varchar (100) salary float (8), primary key (id))

Create a student table:

Create table students (id int, name varchar, grade varchar, primary key (id))

The third form:

Create table teacher_student (t_id int, s_id int, primary key) 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 a table is connected to the primary key of the second table. You don't need to do this in actual development, 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, and the final result is incorrect, so it is generally not used.

Internal connection:

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

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

Syntax:

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

External connection:

External join is based on a table, other table information is spliced, if there is a splicing, if there is no display of null; external connection is divided into left outer connection and right outer connection.

Left outer join: stitching based on the table to the left of the keyword

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

Right outer join: based on the table to the right of the keyword

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

Note: table a left outer join table b and table b right outer join table a have the same result.

Subquery:

In some cases, when a query is carried out, the necessary condition is the result of another select statement, and a subquery is used, and the query (internal query) that is first executed in order to provide data to the main query (external query) is called subquery; the subquery is divided into nested subquery and related subquery.

Nested subqueries:

The execution of the internal query is independent of the external query, and the internal query is executed only once. After the execution, the results are used as the conditions of the external query (the subquery statements in the nested subquery can be taken out and run separately. )

Grammar and exercises: find out all the students who have been taught by teachers with id 1.

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

Related subqueries:

The execution of the internal query depends on the data of the external query, and each time the external query is executed, the internal query is also executed. Each time, the external query is executed first, taking a tuple from the external query table, passing the data in the current tuple to the internal query, and then executing the internal query. According to the results of the execution of the internal query, it is judged whether the current tuple meets the where condition in the external query, and if so, the current tuple is a record that meets the requirements, otherwise it does not meet the requirements. The external query then continues to fetch the next tuple data and perform the above operation until all tuples are processed.

Grammar and exercises: ask for the scores of students whose scores are higher than the average 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 between the MySql data sheets introduced by the editor, query detailed interpretation and integration, I hope to be helpful to you, if you have any questions, please leave me a message, the editor will reply to you in time. Thank you very much for your support to the website!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.