MySQL 4;

1 The relationship between tables:

2select Query statement:

1 The relationship between tables

(1) Many-to-one: (Multiple records in a table correspond to a record in another table)

The establishment of a multi-to-one relationship requires attention
1 first establish the associated table, the associated field must be guaranteed to be unique
2 again create the associated table, the associated fields must be guaranteed to be repeatable
PS: The associated field must be a value from the corresponding field of the associated table
Code:

CREATE TABLE DEP (# Department table # The fields that are associated must be guaranteed to be unique #id fields are primary keys and are self-increasing name varchar,# Comment varchar (());

CREATE TABLE emp (#员工表 ID int primary KEY auto_increment,name varchar (# # associated fields, Be sure to be repeatable constraint fk_depid_id foreign key (dep_id)  references dep (ID)# define foreign key on update Cascade# This indicates that the two are on DELETE cascade that are changed or deleted along with the associated table );

(2) One-to-one

 CREATE TABLE User (#   Establish user table  UID int Primary key Auto_increment,#   primary key and self-increment  name varchar (
   
    #  
     user's name  
     #   insert data  ( " frank1   "   '  ), 
   

Create TABLE admin (# build admin table ID int primary key auto_increment,# primary KEY and user_id int Unique,# Make sure it is unique password varchar (#),# number of password foreign key (user_id) references User (UID)# is associated to the UID of user to ensure that foreign is unique on update cascade# After this ensures that the associated table can be changed along with the associated or deleted on Delete cascade);

Insert into admin (user_id,password) values
(1, ' frank3714 '),
(2, ' alfrank371asdf4 ')

(3) Many-to-many relationships

Many-to-many code:
Create TABLE author (#Create author InformationID int primary key auto_increment, name char (10)); CREATE TABLE Books (#Create book InformationID int primary key auto_increment,name char (10)); Create Table Books_authors (#Create a relational tableID int primary KEY auto_increment,#primary KEY and self-incrementbook_id int notNull#is not emptyauthor_id int notNull#is not emptyUnique (book_id,author_id),#Union UniqueForeign KEY (book_id) references books (ID)#set foreign keysON DELETE Cascade#These two are notes that the association table is followed by the associated table changes togetherOn update cascade, FOREIGN KEY (author_id) references author (id)#set foreign keysOn Delete cascadeon update cascade);

2select Query statement:

MySQL: Increase, delete, change, check; we're using the database. The most is the query data

We've learned basic query statements before: SELECT * from T1;

Let's take a look at the next where statement and (And,between and, not, or, in, are, like, etc.)

SELECT * from t1 where id=5;

Select Name,age where salary>1000 and salary <2000; can be written as select Name,age where salary between and 2000;

Select Name,age where salary not between 1000and 2000;

Select Name,salary from employee where salary = 10000 or salary = 20000 or salary = 30000;
Select Name,salary from the employee where salary in (10000,20000,30000);
Select salary from the employee where name like '%ank% ';  #like is a fuzzy match% can represent any character (can represent more than one)

Select salary from the employee where name like ' Frank_ ';  #like is a fuzzy match _ can represent any one character

SELECT * from the employee where dep_comment is null, #is NULL to determine whether NULL;
SELECT * FROM employee where dep_comment = Null; #这个是错误的
SELECT * FROM employee where dep_comment are not Null;
Group BY is a group (in what group)

Select Depart_id,count (ID) from the  employee group by DEPART_ID;

MySQL 4;