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
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) );
create table t_user( id int(10), name varchar(32) not null, email varchar(128) unique );
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) );
##not null and unique can be used together
3. primary key (primary key constraint)
- 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 constraints
create table t_user( id int(10), name varchar(32), primary key(id) );
Composite primary key: only table-level constraints can be used
mysql> create table t_user( -> id int(10), -> name varchar(32), -> primary key(id,name) -> );
auto_increment:主键自增
MySQL数据管理系统中提供了一个自增的数字,专门用来自动生成主键值
主键值不需要用户维护,也不需要用户提供了,自动生成的,
这个自增的数字默认从1开始以1递增:1,2,3,4,....
mysql> create table t_user( -> id int(10) primary key auto_increment, -> name varchar(32) -> );
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.班级表为父表,学生表为子表
应该先创建父表,再创建子表
删除数据时,应该先删除子表中的数据,再删除父表中的数据
插入数据时,应该先插入父表中的数据,再删除子表中的数据
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
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;
级联删除
级联更新
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!

How to effectively monitor MySQL performance? Use tools such as mysqladmin, SHOWGLOBALSTATUS, PerconaMonitoring and Management (PMM), and MySQL EnterpriseMonitor. 1. Use mysqladmin to view the number of connections. 2. Use SHOWGLOBALSTATUS to view the query number. 3.PMM provides detailed performance data and graphical interface. 4.MySQLEnterpriseMonitor provides rich monitoring functions and alarm mechanisms.

The difference between MySQL and SQLServer is: 1) MySQL is open source and suitable for web and embedded systems, 2) SQLServer is a commercial product of Microsoft and is suitable for enterprise-level applications. There are significant differences between the two in storage engine, performance optimization and application scenarios. When choosing, you need to consider project size and future scalability.

In enterprise-level application scenarios that require high availability, advanced security and good integration, SQLServer should be chosen instead of MySQL. 1) SQLServer provides enterprise-level features such as high availability and advanced security. 2) It is closely integrated with Microsoft ecosystems such as VisualStudio and PowerBI. 3) SQLServer performs excellent in performance optimization and supports memory-optimized tables and column storage indexes.

MySQLmanagescharactersetsandcollationsbyusingUTF-8asthedefault,allowingconfigurationatdatabase,table,andcolumnlevels,andrequiringcarefulalignmenttoavoidmismatches.1)Setdefaultcharactersetandcollationforadatabase.2)Configurecharactersetandcollationfor

A MySQL trigger is an automatically executed stored procedure associated with a table that is used to perform a series of operations when a specific data operation is performed. 1) Trigger definition and function: used for data verification, logging, etc. 2) Working principle: It is divided into BEFORE and AFTER, and supports row-level triggering. 3) Example of use: Can be used to record salary changes or update inventory. 4) Debugging skills: Use SHOWTRIGGERS and SHOWCREATETRIGGER commands. 5) Performance optimization: Avoid complex operations, use indexes, and manage transactions.

The steps to create and manage user accounts in MySQL are as follows: 1. Create a user: Use CREATEUSER'newuser'@'localhost'IDENTIFIEDBY'password'; 2. Assign permissions: Use GRANTSELECT, INSERT, UPDATEONmydatabase.TO'newuser'@'localhost'; 3. Fix permission error: Use REVOKEALLPRIVILEGESONmydatabase.FROM'newuser'@'localhost'; then reassign permissions; 4. Optimization permissions: Use SHOWGRA

MySQL is suitable for rapid development and small and medium-sized applications, while Oracle is suitable for large enterprises and high availability needs. 1) MySQL is open source and easy to use, suitable for web applications and small and medium-sized enterprises. 2) Oracle is powerful and suitable for large enterprises and government agencies. 3) MySQL supports a variety of storage engines, and Oracle provides rich enterprise-level functions.

The disadvantages of MySQL compared to other relational databases include: 1. Performance issues: You may encounter bottlenecks when processing large-scale data, and PostgreSQL performs better in complex queries and big data processing. 2. Scalability: The horizontal scaling ability is not as good as Google Spanner and Amazon Aurora. 3. Functional limitations: Not as good as PostgreSQL and Oracle in advanced functions, some functions require more custom code and maintenance.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

SublimeText3 Linux new version
SublimeText3 Linux latest version

VSCode Windows 64-bit Download
A free and powerful IDE editor launched by Microsoft

MinGW - Minimalist GNU for Windows
This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

Dreamweaver Mac version
Visual web development tools

DVWA
Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software