search
HomeDatabaseMysql TutorialWhat are the conditions when MySQL creates a table?

Due to the addition, deletion and modification of records in the emp table, a script is re-created here and used

create database bjpowernnode;
use bjpowernode;
source C:\Users\Administrator\Desktop\bjpowernode.sql;

constraints

1. What are constraints?

  • Constraints are the restrictions in the table

  • The keyword of the constraint is: constraint

2. Classification of constraints

  • Non-null constraint not null

  • Unique Sexual constraints unique

  • Primary key constraints primary key

  • ##Foreign key constraints

    foreign key

  • Checking constraints MySQL database does not support it, Oracle database supports it

1. not null (non-null constraint)

The fields with not null constraints cannot be null values. Specific data must be created.

Create a table and add non-null constraints to the fields [The user’s email address cannot be null]

drop table if exists t_user;
create table t_user(
        id int(10),
        name varchar(32) not null,
        email varchar (32)
);

What are the conditions when MySQL creates a table?

What are the conditions when MySQL creates a table?

2. unique (unique constraint)

Create a table to ensure that the email address is unique

create table t_user(
id int(10),
name varchar(32) not null,
email varchar(128) unique
);

What are the conditions when MySQL creates a table?

The fields of unique constraints cannot be repeated, but can be null

What are the conditions when MySQL creates a table?

The above constraints are column-level constraints

Table-level constraints:

 create table t_user(
     id int(10),
     name varchar(32),
     email varchar(128),
     unique(email)
 );

1. Use table-level constraints to add constraints to multiple fields

 create table t_user(
     id int(10),
     name varchar(32),
     email varchar(128),
     unique(name,email)
);

2. Table-level constraints can give the constraint a name, and later delete the constraint through this name

   create table t_user(
       id int(10),
       name varchar(32),
       email varchar(128),
       constraint t_user_email_unique unique(email)
 );

What are the conditions when MySQL creates a table?

What are the conditions when MySQL creates a table?

What are the conditions when MySQL creates a table?##not null and unique can be used together

What are the conditions when MySQL creates a table?3. primary key (primary key constraint)

1. Terms related to primary key:

    Primary key constraint
  • Primary key field
  • Primary key value
  • ##2. The relationship between the above three:

After adding a primary key constraint to a field in the table, the field is called the primary key field
  • Each occurrence in the primary key field The data are all called primary key values
  • 3. After adding a primary key constraint to a field, the field cannot be repeated or empty

The primary key constraint has the same effect as ''not null unique'', but the essence is different.
  • Primary key constraint can also achieve ''not null unique'' In addition to
  • the primary key field will also be added with ''index-index'' by default
  • 4. A table should have Primary key field, if not, it means that this table is invalid

The primary key value is the unique identifier of the current row of data
  • The primary key value is the ID number of the current row of data
  • Even if the two rows of record data in the table are exactly the same,
  • But because If the primary key values ​​are different, they are considered to be two completely different fields in the two rows
  • 5. Whether it is a single primary key or a composite primary key, a table can only have one primary key constraint.

Adding a primary key constraint to a field is called a single primary key constraint
  • Adding a primary key constraint to multiple fields jointly is called a single primary key constraint It is called a composite primary key
  • 6. Primary keys are classified according to their properties:

Natural primary key: The primary key value is a natural number , this primary key has nothing to do with the current business
  • Business primary key: The primary key value is closely related to the current business
  • When the business changes , the primary key value will always be affected, so the business primary key is of little use.
  • Single primary key, column-level constraints
  • create table t_user(
        id int(10) primary key,
        name varchar(32)
    );

Single primary key, table-level constraintsWhat are the conditions when MySQL creates a table?

 create table t_user(
     id int(10),
     name varchar(32),
     primary key(id)
);

Composite primary key: only table-level constraints can be usedWhat are the conditions when MySQL creates a table?

mysql> create table t_user(
    -> id int(10),
    -> name varchar(32),
    -> primary key(id,name)
    -> );

What are the conditions when MySQL creates a table?

auto_increment:主键自增

MySQL数据管理系统中提供了一个自增的数字,专门用来自动生成主键值

主键值不需要用户维护,也不需要用户提供了,自动生成的,

这个自增的数字默认从1开始以1递增:1,2,3,4,....

mysql> create table t_user(
    -> id int(10) primary key auto_increment,
    -> name varchar(32)
    -> );

What are the conditions when MySQL creates a table?

4. foreign key(外键约束)

1.外键约束涉及到的术语:

  • 外键约束

  • 外键值

  • 外键字段

2.以上三者之间的关系:

  • 某个字段添加外键约束以后称为外键字段

  • 外键字段中的每一个数据称为外键值

3.外键分为单一外键和复合外键

  • 单一外键:给一个字段添加外键约束

  • 复合外键:给多个字段添加外键约束

4.一张表中可以有多个外键字段

设计一个数据库表,用来存储学生和班级信息,给出两种解决方案:

学生信息和班级信息之间的关系:一个班级对应多个学生,这是典型的一对多的关系

在多的一方加外键

第一种设计方案:将学生信息和班级信息存储到一张表中

第二种设计方案:将学生信息和班级信息分开两张表存储,学生表+班级表

  • 学生表 t_student

sno(主键约束) sname classno(外键约束)
1 jack 100
2 lucy 100
3 kk 100
4 smith 200
5 frank 300
6 jhh 300
  • 班级表t_calss

cno(主键约束) cname
100 高三1班
200 高三2班
300 高三3班

为了保证t_student 表中的classno字段中的数据必须来自于t_class表中的cno字段中的数据,有必要给t_student表中的classno字段添加外键约束,classno称为外键字段,该字段中的值称为外键值。

注意:

1.外键值可以为空

2.外键字段必须得引用这张表中的主键吗?

  • 外键字段引用一张表的字段的时候,被引用的字段必须具备唯一性

  • 即具有unique约束,不一定非是主键

3.班级表为父表,学生表为子表

  • 应该先创建父表,再创建子表

  • 删除数据时,应该先删除子表中的数据,再删除父表中的数据

  • 插入数据时,应该先插入父表中的数据,再删除子表中的数据

What are the conditions when MySQL creates a table?

DROP TABLE IF EXISTS t_student;
DROP TABLE IF EXISTS t_class;
 
 CREATE TABLE t_class(
 cno INT(3) PRIMARY KEY,
 cname VARCHAR(128) NOT NULL UNIQUE
 );
 
 CREATE TABLE t_student(
 sno INT(3) PRIMARY KEY,
 sname VARCHAR(32) NOT NULL,
 classno INT(3),-- 外键
 CONSTRAINT t_student_class_fk FOREIGN KEY(classno) REFERENCES t_class(cno)
 );
 
 INSERT INTO t_class(cno,cname) VALUES(100,'高三1班');
 INSERT INTO t_class(cno,cname) VALUES(200,'高三2班');
 INSERT INTO t_class(cno,cname) VALUES(300,'高三3班');
 
 INSERT INTO t_student(sno,sname,classno) VALUES(1,'jack',100);
 INSERT INTO t_student(sno,sname,classno) VALUES(2,'lucy',100);
 INSERT INTO t_student(sno,sname,classno) VALUES(3,'hh',100);
 INSERT INTO t_student(sno,sname,classno) VALUES(4,'frank',200); 
 INSERT INTO t_student(sno,sname,classno) VALUES(5,'smith',300);
 INSERT INTO t_student(sno,sname,classno) VALUES(6,'jhh',300);
 
 SELECT * FROM t_student;
 SELECT * FROM t_class;
 
-- 添加失败,因为有外键约束 
 INSERT INTO t_student(sno,sname,classno) VALUES(8,'kk',500);

重点:典型的一对多关系,设计时在多的一方加外键

5. 级联更新与级联删除

在删除父表中的数据的时候,级联删除子表中的数据

在更新父表中的数据的时候,级联更新子表中的数据

以上的级联更新和级联删除谨慎使用,

因为级联操作会使数据数据改变或删除,数据是无价的。

语法:

  • 级联更新:on update cascase

  • 级联删除:on delete cascase

What are the conditions when MySQL creates a table?

What are the conditions when MySQL creates a table?

MySQL中对于有些约束的修改比较麻烦,所以应该先删除约束,再添加约束

删除外键约束:

alter table t_student drop foreign key t_student_class_fk

添加外键约束并级联更新:

alter table t_student add constraint t_student_class_fk foreign key(classno)
references t_class(no) on delete cascade;

添加外键约束并级联删除:

alter table t_student add constraint t_student_class_fk foreign key(classno)
references t_class(no) on update cascade;

级联删除

What are the conditions when MySQL creates a table?

What are the conditions when MySQL creates a table?

级联更新

What are the conditions when MySQL creates a table?

What are the conditions when MySQL creates a table?

The above is the detailed content of What are the conditions when MySQL creates a table?. For more information, please follow other related articles on the PHP Chinese website!

Statement
This article is reproduced at:亿速云. If there is any infringement, please contact admin@php.cn delete
Explain the role of InnoDB redo logs and undo logs.Explain the role of InnoDB redo logs and undo logs.Apr 15, 2025 am 12:16 AM

InnoDB uses redologs and undologs to ensure data consistency and reliability. 1.redologs record data page modification to ensure crash recovery and transaction persistence. 2.undologs records the original data value and supports transaction rollback and MVCC.

What are the key metrics to look for in an EXPLAIN output (type, key, rows, Extra)?What are the key metrics to look for in an EXPLAIN output (type, key, rows, Extra)?Apr 15, 2025 am 12:15 AM

Key metrics for EXPLAIN commands include type, key, rows, and Extra. 1) The type reflects the access type of the query. The higher the value, the higher the efficiency, such as const is better than ALL. 2) The key displays the index used, and NULL indicates no index. 3) rows estimates the number of scanned rows, affecting query performance. 4) Extra provides additional information, such as Usingfilesort prompts that it needs to be optimized.

What is the Using temporary status in EXPLAIN and how to avoid it?What is the Using temporary status in EXPLAIN and how to avoid it?Apr 15, 2025 am 12:14 AM

Usingtemporary indicates that the need to create temporary tables in MySQL queries, which are commonly found in ORDERBY using DISTINCT, GROUPBY, or non-indexed columns. You can avoid the occurrence of indexes and rewrite queries and improve query performance. Specifically, when Usingtemporary appears in EXPLAIN output, it means that MySQL needs to create temporary tables to handle queries. This usually occurs when: 1) deduplication or grouping when using DISTINCT or GROUPBY; 2) sort when ORDERBY contains non-index columns; 3) use complex subquery or join operations. Optimization methods include: 1) ORDERBY and GROUPB

Describe the different SQL transaction isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) and their implications in MySQL/InnoDB.Describe the different SQL transaction isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) and their implications in MySQL/InnoDB.Apr 15, 2025 am 12:11 AM

MySQL/InnoDB supports four transaction isolation levels: ReadUncommitted, ReadCommitted, RepeatableRead and Serializable. 1.ReadUncommitted allows reading of uncommitted data, which may cause dirty reading. 2. ReadCommitted avoids dirty reading, but non-repeatable reading may occur. 3.RepeatableRead is the default level, avoiding dirty reading and non-repeatable reading, but phantom reading may occur. 4. Serializable avoids all concurrency problems but reduces concurrency. Choosing the appropriate isolation level requires balancing data consistency and performance requirements.

MySQL vs. Other Databases: Comparing the OptionsMySQL vs. Other Databases: Comparing the OptionsApr 15, 2025 am 12:08 AM

MySQL is suitable for web applications and content management systems and is popular for its open source, high performance and ease of use. 1) Compared with PostgreSQL, MySQL performs better in simple queries and high concurrent read operations. 2) Compared with Oracle, MySQL is more popular among small and medium-sized enterprises because of its open source and low cost. 3) Compared with Microsoft SQL Server, MySQL is more suitable for cross-platform applications. 4) Unlike MongoDB, MySQL is more suitable for structured data and transaction processing.

How does MySQL index cardinality affect query performance?How does MySQL index cardinality affect query performance?Apr 14, 2025 am 12:18 AM

MySQL index cardinality has a significant impact on query performance: 1. High cardinality index can more effectively narrow the data range and improve query efficiency; 2. Low cardinality index may lead to full table scanning and reduce query performance; 3. In joint index, high cardinality sequences should be placed in front to optimize query.

MySQL: Resources and Tutorials for New UsersMySQL: Resources and Tutorials for New UsersApr 14, 2025 am 12:16 AM

The MySQL learning path includes basic knowledge, core concepts, usage examples, and optimization techniques. 1) Understand basic concepts such as tables, rows, columns, and SQL queries. 2) Learn the definition, working principles and advantages of MySQL. 3) Master basic CRUD operations and advanced usage, such as indexes and stored procedures. 4) Familiar with common error debugging and performance optimization suggestions, such as rational use of indexes and optimization queries. Through these steps, you will have a full grasp of the use and optimization of MySQL.

Real-World MySQL: Examples and Use CasesReal-World MySQL: Examples and Use CasesApr 14, 2025 am 12:15 AM

MySQL's real-world applications include basic database design and complex query optimization. 1) Basic usage: used to store and manage user data, such as inserting, querying, updating and deleting user information. 2) Advanced usage: Handle complex business logic, such as order and inventory management of e-commerce platforms. 3) Performance optimization: Improve performance by rationally using indexes, partition tables and query caches.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Best Graphic Settings
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. How to Fix Audio if You Can't Hear Anyone
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
WWE 2K25: How To Unlock Everything In MyRise
1 months agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 English version

SublimeText3 English version

Recommended: Win version, supports code prompts!

SublimeText3 Linux new version

SublimeText3 Linux new version

SublimeText3 Linux latest version