Summarize and explain the table structure of MySQL knowledge points in detail

This article brings you relevant knowledge about mysql, which mainly introduces the table structure including constraints and related issues of modifying the table structure. I hope it will be helpful to everyone.

Recommended study: mysql tutorial

Constraint conditions

• What is the role of restriction? Assign values ​​to fields

View constraints

• ##mysql> desc library name.table name;

mysql> desc db1.t9;

//As shown in the picture, the meaning of each column is: field name | type | empty | key value | default value | additional settings

mysql> insert into t9 values (null,null,null);

//As shown in the picture above As shown, the constraint condition is allowed to be empty (NULL), so it can be empty when assigning a value here. null is not case-sensitive

mysql> select * from  db1.t9;

mysql> insert into db1.t9(name,sex) values("lucy","girl");
mysql> select * from db1.t9;

//Constraints of the t9 table In the condition, the default value of the third field is NULL, which means that when the third field is not assigned a value, the default value will be filled in the table, that is, as shown in the figure below, NULL

## will be filled in by default.

#Set constraints

##null                                                                                                                   ’ ’ ’ ’ s being allowed to be empty-setted (default setting)

• not null              Is null (empty)

• key                                                                                                                                                                                                                                                                           ​ 
• ##extra                                                                                       // Additional settings

mysql> create database db2;
mysql> create table db2.t1(
    -> name char(10) not null default "",
    -> age tinyint unsigned default 19,
    -> sex enum("m","w") not null default "m"
    -> );

• //Create a table db2.t1. The type of the name field is fixed-length char. The constraint is that it is not allowed to be Empty, the default value is 0 characters, the display effect is as shown in the figure below; the type of the age field is a tiny integer, the constraint is that negative numbers (unsigned) are not allowed, the default value is set to 19, note the default value setting here It must not exceed the range of the tinyint type, that is, the default value setting cannot exceed 255; the sex field type is an enumeration, enum is a radio selection, the constraint is that it is not allowed to be empty, and the default value is set to m;
  mysql> ; desc db2.t1;

• mysql> insert into db2.t1(name) values("bob");

//Only assign values ​​to the name field, and the remaining fields are assigned default values

mysql> select * from db2.t1;

mysql> insert into db2.t1 values("lucy","21","w");     //给字段赋值，就不会以默认值赋值了
mysql> select * from db2.t1;

mysql> insert into db2.t1 values(null,null,null);    
  //错误提示，name字段不允许为空；name和sex字段都不能为空
ERROR 1048 (23000): Column 'name' cannot be null
mysql> insert into db2.t1 values("null",null,"w");     
//"null"的意思不再是空，仅仅是字符null，没有空的含义了，所以可以给字段赋值
mysql> insert into db2.t1 values("",null,"w");     
//""为0个字符，和空不一样，也可以给字段赋值
mysql> select * from db2.t1;

Summary

Constraints

Type##Unique index unique Default (default value: do not assign a value to the field and use the default value assignment) Extra (extra Settings: There are no additional settings by default)

Null (whether null is allowed)	Default allows null
	##not allowed to be empty not null
Key (key value)	Normal index index
## primary key primary key
foreign key foreign key
#Full text index fulltext
The default is null when it is not defined
It is default when defined (to match the field type)
	修改表结构 语法结构 用法 mysql> alter table 库名.表名 执行动作; 执行动作 add 添加字段 modify 修改字段类型 change 修改字段名 drop 删除字段 rename 修改表名 添加新字段 用法 —— 新字段默认添加在字段末尾 mysql> alter table 库名.表名 add 字段名 类型(宽度) 约束条件; add 字段名 类型 [约束条件] after 字段名; add 字段名 类型 [约束条件] first; 不指定位置添加字段 mysql> alter table db2.t1 add email varchar(50); //不指定添加字段的位置，默认就在末尾 mysql> desc db2.t1; 在某一字段后面添加字段 mysql> alter table db2.t1 add hobby set("eat","drink","play","happy") not null default "eat,drink" after age; //指定添加位置在字段age之后，条件设置不允许为空，并设置默认值为eat，drink mysql> desc db2.t1; mysql> select * from db2.t1; //查看表内容，发现hobby字段自动添加了默认值 将字段添加到最前面 mysql> alter table db2.t1 add class char(7) default "B180601" first; //将字段class添加到最前面 mysql> desc db2.t1; mysql> select * from db2.t1; //class字段自动添加默认值 修改字段类型 基本用法 —— 修改的字段类型不能与已存储的数据冲突 mysql> alter table 库名.表名 modify 字段名 类型(宽度) 约束条件; modify 字段名 类型 [约束条件] after 字段名; modify 字段名 类型 [约束条件] first; 注： 如果表中这个字段类型下面的字段已经有值了，那么修改的类型与约束不能与字段里面已经存储的数据发生冲突，如果发生冲突则不允许修改。比如，有一个字段name，里面存储了一个数据为bob，那么把字段的类型(宽度)改成char(1)，则会修改失败，因为bob的宽度为3，如果将字段的类型(宽度)改为char(1)，则源数据bob就存不下了，由于已存储的数据优先，所以就不能进行修改； 修改字段的类型时要注意，不修改的部分要原样进行抄写，如果不修改的部分不原样抄写，那就相当于对其进行还原即使用默认配置。比如有一个字段name，他的类型是char(10)，约束条件是不允许为空，默认值是""，现在要将其类型宽度改为char(20)，其余没修改的部分没有在命令中进行原样抄写，那么输出结果该字段的约束条件会变为默认情况，即允许为空，默认值为NULL。改变字段位置的时候也一样。 mysql> desc db2.t1; 将字段age的位置修改到字段class的后面 mysql> alter table db2.t1 modify age tinyint unsigned default 19 after class; //除了对字段age的位置进行了修改，其余不修改的地方如：类型(宽度)、约束条件进行原样抄写 mysql> desc db2.t1; 将字段name的类型变为varchar(15) mysql> alter table db2.t1 modify name varchar(15) not null default ""; mysql> desc db2.t1; 修改字段名 基本用法 —— 也可以用来修改字段类型 mysql> alter table 库名.表名 change 源字段名 新字段名 类型(宽度) 约束条件; 注：也可以用来修改类型和约束条件，只需要写上新的类型和新的约束条件即可 mysql> desc db2.t1; 将字段名email修改为mail mysql> alter table db2.t1 change email mail varchar(50); mysql> desc db2.t1; 将字段名mail修改为email，并修改约束条件 mysql> alter table db2.t1 change mail email varchar(50) not null default "njw@163.com"; //出现错误，因为源数据中，mail字段中的值为NULL，如果将约束条件修改为not null，那么就与源数据发生冲突，所以修改失败 ERROR 1138 (22004): Invalid use of NULL value mysql> alter table db2.t1 change mail email varchar(50) default "njw@163.com"; mysql> desc db2.t1; 删除字段 基本用法 mysql> alter table 库名.表名 drop 字段名; mysql> alter table db2.t1 drop email; //删除库db2中表t1的字段email mysql> desc db2.t1; //没有字段email，已被删除 mysql> select * from db2.t1;    //字段email及其数据已被删除 修改表名 基本用法 mysql> alter table 库名.表名 rename 新表名;                                                           库名.新表名; mysql> use db2; mysql> show tables; mysql> alter table db2.t1 rename stuinfo;     //将表名修改为stuinfo mysql> show tables; mysql> select * from stuinfo; 推荐学习：mysql视频教程 The above is the detailed content of Summarize and explain the table structure of MySQL knowledge points in detail. For more information, please follow other related articles on the PHP Chinese website! Statement of this Website The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn 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! Show More Hot Article Assassin's Creed Shadows: Seashell Riddle Solution 3 weeks ago By DDD What's New in Windows 11 KB5054979 & How to Fix Update Issues 2 weeks ago By DDD Where to find the Crane Control Keycard in Atomfall 3 weeks ago By DDD Saving in R.E.P.O. Explained (And Save Files) 1 months ago By 尊渡假赌尊渡假赌尊渡假赌 Assassin's Creed Shadows - How To Find The Blacksmith And Unlock Weapon And Armour Customisation 4 weeks ago By DDD Show More Hot Tools Notepad++7.3.1 Easy-to-use and free code editor SublimeText3 Chinese version Chinese version, very easy to use Zend Studio 13.0.1 Powerful PHP integrated development environment Dreamweaver CS6 Visual web development tools SublimeText3 Mac version God-level code editing software (SublimeText3) Show More Hot Topics Where is the login entrance for gmail email? 7572 15 CakePHP Tutorial 1386 52 What is the format of the account name of steam 87 11 win11 activation key permanent 62 19 nyt connections hints and answers 28 110 Show More Related knowledge MySQL: Simple Concepts for Easy Learning Apr 10, 2025 am 09:29 AM MySQL is an open source relational database management system. 1) Create database and tables: Use the CREATEDATABASE and CREATETABLE commands. 2) Basic operations: INSERT, UPDATE, DELETE and SELECT. 3) Advanced operations: JOIN, subquery and transaction processing. 4) Debugging skills: Check syntax, data type and permissions. 5) Optimization suggestions: Use indexes, avoid SELECT* and use transactions. How to open phpmyadmin Apr 10, 2025 pm 10:51 PM You can open phpMyAdmin through the following steps: 1. Log in to the website control panel; 2. Find and click the phpMyAdmin icon; 3. Enter MySQL credentials; 4. Click "Login". MySQL: An Introduction to the World's Most Popular Database Apr 12, 2025 am 12:18 AM MySQL is an open source relational database management system, mainly used to store and retrieve data quickly and reliably. Its working principle includes client requests, query resolution, execution of queries and return results. Examples of usage include creating tables, inserting and querying data, and advanced features such as JOIN operations. Common errors involve SQL syntax, data types, and permissions, and optimization suggestions include the use of indexes, optimized queries, and partitioning of tables. Why Use MySQL? Benefits and Advantages Apr 12, 2025 am 12:17 AM MySQL is chosen for its performance, reliability, ease of use, and community support. 1.MySQL provides efficient data storage and retrieval functions, supporting multiple data types and advanced query operations. 2. Adopt client-server architecture and multiple storage engines to support transaction and query optimization. 3. Easy to use, supports a variety of operating systems and programming languages. 4. Have strong community support and provide rich resources and solutions. How to use single threaded redis Apr 10, 2025 pm 07:12 PM Redis uses a single threaded architecture to provide high performance, simplicity, and consistency. It utilizes I/O multiplexing, event loops, non-blocking I/O, and shared memory to improve concurrency, but with limitations of concurrency limitations, single point of failure, and unsuitable for write-intensive workloads. MySQL and SQL: Essential Skills for Developers Apr 10, 2025 am 09:30 AM MySQL and SQL are essential skills for developers. 1.MySQL is an open source relational database management system, and SQL is the standard language used to manage and operate databases. 2.MySQL supports multiple storage engines through efficient data storage and retrieval functions, and SQL completes complex data operations through simple statements. 3. Examples of usage include basic queries and advanced queries, such as filtering and sorting by condition. 4. Common errors include syntax errors and performance issues, which can be optimized by checking SQL statements and using EXPLAIN commands. 5. Performance optimization techniques include using indexes, avoiding full table scanning, optimizing JOIN operations and improving code readability. MySQL's Place: Databases and Programming Apr 13, 2025 am 12:18 AM MySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen Monitor Redis Droplet with Redis Exporter Service Apr 10, 2025 pm 01:36 PM Effective monitoring of Redis databases is critical to maintaining optimal performance, identifying potential bottlenecks, and ensuring overall system reliability. Redis Exporter Service is a powerful utility designed to monitor Redis databases using Prometheus. This tutorial will guide you through the complete setup and configuration of Redis Exporter Service, ensuring you seamlessly build monitoring solutions. By studying this tutorial, you will achieve fully operational monitoring settings See all articles Public welfare online PHP training，Help PHP learners grow quickly！ About us Disclaimer Sitemap © php.cn All rights reserved