MySQL Foundation II (Advanced)

MySQL Basics twoPhase one table constraint1, NOT NULL non-null constraint

Example:

CREATE TABLE TB1 (    ID int,    name varchar (not  null);

Note null characters are not equal to null

#手动, add a non-null constraint (This field must not have a null value)

mysql> ALTER TABLE TB1    not null;

# cancel non-null constraint

mysql> ALTER TABLE TB1    , modify ID int;

2. Unique key only constraint

Example:

CREATE TABLE TB2 (    ID int unique key,    name varchar); #

# Add a UNIQUE constraint ? mysql> alter TABLE TB2     , add unique key (name)    ->;

# Remove UNIQUE constraint ? mysql> alter TABLE TB2    , drop key name;

3. Primary KEY constraint primary key

The role of the primary key: You can uniquely identify a single piece of data, each table can have only one primary key. The primary key is to help MySQL find a piece of information in the table as quickly as it could.

Primary KEY attribute: non-null and unique. When the table does not have a primary key, the first occurrence of non-empty and unique columns is treated as a primary key.

Example:

CREATE TABLE TB3 (    ID int primary KEY,   name varchar (not

# Delete a primary KEY constraint  , ALTER TABLE TB3      , drop primary key;

4. Self-growth auto_increment

Auto_increment: Auto-numbering, commonly used in combination with primary key. There's only one self-increment in a table. By default, the starting value is 1, and the increment is 1 each time.

Example:

CREATE TABLE TB5 (   ID intprimary key auto_increment,   name varchar)auto_increment= 100;

# Delete auto-Grow ? mysql> alter TABLE TB5    -Modify ID int;

# Increase auto-growth auto_increment ? mysql> alter TABLE TB5    -Modify ID int auto_increment;

5. Default constraint Defaults

Default: The initial value setting, when inserting a record, is automatically assigned a value if it is not explicitly assigned to a field.

Example:

CREATE TABLE TB6 (  ID int primary key auto_increment,  name varchar (not  null,    NOT null default);

# Delete Default ? mysql> alter TABLE TB6    -Modify age int;

# Add default Manually ? mysql> alter TABLE TB6     -Modify age int default 20;

6. Foreign KEY constraint foreign key

FOREIGN KEY constraints: maintain data consistency and integrity to achieve a one-to-many relationship.

The foreign key must be associated to the top of the key, and the general case is that the primary key associated to the other table

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

##表a? CREATE Table A (a_id int primary key auto_increment, A_name varchar (20) notnull);? Insert into a values (1,'A1'), (2,'A2');?##表b? CREATE table B (b_id int primary KEY, B_name varchar (20) notnull, fy_id int notNULL, Constraint Ab_idforeign key (fy_id) references a (a_id));? Insert into B value (1,'AA', 2);

# Delete foreign key ALTER TABLE  b drop foreign key ab_id;

# Add foreign Key ? mysql> alter TABLE B    -add Constraint ab_id foreign key (fy_id) references  a (a_ ID);

# fy_id field in table B, you can only add data already in a_id.
# A_ID referenced data in table A, cannot be modified and deleted

Phase Two table relationship1. One-to-one relationship (student details)

Single-to-one: the primary key of two tables is associated with a foreign key

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 using the primary key plus the primary key.

#Student TableMysql>CREATE TABLE Student (-s_id int primary KEY,Sex varchar (20),    -Age int);#Inserting Datamysql> INSERT into Student value (1,'male', 22);?#Student Detail TableMysql>CREATE TABLE student_x (-ID int primary KEY,Name varchar (20),    -foreign key (ID) references student (s_id)- );#Inserting Datamysql> INSERT into student_x value (1,'zcm'); #ViewMysql> SELECT * fromstudent_x;+----+------+| ID |  Name |+----+------+| 1 | ZCM |+----+------+? MySQL> select * fromstudent;+------+------+------+| s_id | sex |    Age |+------+------+------+| 1 |   Nan | 22 |

2. One-to-many relationship (student-affiliated college)

? For example, usually, a college can have a lot of students, and a student belongs to only one college. The relationship between college and students is a one-to-many relationship, which is achieved through a foreign key association.

Note: The student table can only be added, the existing college ID

##创建学院表CREATE TABLE Department (d_id Int primary key auto_increment,#Academy IDD_name varchar (20) notNull#College Name);?##创建学生表CREATE TABLE student (s_id int primary key auto_increment,#Student IDS_name varchar (20) notNull#Student Namedept_id int notNull#Affiliated Academy IDConstraint sd_id foreign KEY (dept_id) References Department (D_ID)#FOREIGN Key);?#Inserting DataINSERT INTO department values (1,'College of Foreign Languages'), (2,'School of Computer science') insert into student values (1, ' Zhang San', 2), (2, ' John Doe', 1);?#ViewMysql> SELECT * fromDepartment;+------+-----------------+| d_id |    D_name |+------+-----------------+| 1 |    College of Foreign Languages | | 2 | Computer Academy |+------+-----------------+? MySQL> select * fromstudent_1;+------+--------+---------+| s_id | S_name |    dept_id |+------+--------+---------+| 1 |       Zhang San |    2 | | 2 |       John Doe | 1 |+------+--------+---------+

3, many-to-many relationship (students selected courses)

? 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 many-to-many relationships, you need to create an intermediate table implementation.

#Create a student tableMysql>CREATE TABLE Student_d (-s_id int primary key auto_increment,S_name varchar (20) notNULL- );?#set up a curriculumMysql>CREATE table Cours (-cours_id int primary key auto_increment,Cours_name varchar (20) notNULL- );?#Selected Timetable (intermediate table)Mysql>CREATE TABLE Ele (-s_id int,#used to record student IDs-cours_id int,#used to record course IDPrimary KEY (S_ID,COURS_ID),#Federated Primary KeyForeign KEY (s_id) references student (s_id),#Associate Student IDForeign KEY (cours_id) references cours (cours_id)#Associated Course ID- );?#Inserting DataINSERT into student_d values (1,'Zhang San'), (2,'John Doe'), (3,'Wangliuqi') insert into cours values (1,'python Programming'), (2,'College English'), (3,'Music Appreciation') insert into ele values (1, 3), (2,1), (3,2);?#ViewMysql> SELECT * fromStudent_d;+------+--------+| s_id |    S_name |+------+--------+| 1 |    Zhang San | | 2 |    John Doe | | 3 | Wangliuqi |+------+--------+3 rowsinchSet (0.00sec)? MySQL> select * fromCours;+----------+--------------+| cours_id |        Cours_name |+----------+--------------+| 1 |        Python Programming | | 2 |        College English | | 3 | Music Appreciation |+----------+--------------+3 rowsinchSet (0.00sec)? MySQL> select * fromele;+------+----------+| s_id |    cours_id |+------+----------+|        2 |    1 | |        3 |    2 | |        1 | 3 |+------+----------+3 rowsinchSet (0.00 sec)

MySQL Foundation II (Advanced)