What are the mysql add statements?
Add statements include: 1. CREATE DATABASE statement, used to add a database, the syntax "CREATE DATABASE database name;"; 2. CREATE TABLE statement, used to add a data table, the syntax "CREATE TABLE table name (column name type);"; 3. ALTER TABLE statement, which can add fields to the data table, the syntax is "ALTER TABLE table name ADD field name type;"; 4. INSERT statement, which can add data to fields.
The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.
1. MySQL adds a database (CREATE DATABASE statement)
In MySQL, You can use the CREATE DATABASE statement to create a database. The basic syntax format is as follows:
CREATE DATABASE [IF NOT EXISTS] 数据库名 [CHARACTER SET 字符集名] [COLLATE 校对规则名];
IF NOT EXISTS: Make a judgment before creating the database. The operation can only be performed if the database does not currently exist. This option can be used to avoid duplicate creation errors when the database already exists.
CHARACTER SET: Specifies the character set of the database. The purpose of specifying the character set is to avoid garbled data stored in the database. If you do not specify a character set when creating the database, the system's default character set is used.
COLLATE: Specifies the default collation rule for the character set.
MySQL's character set (CHARACTER) and collation rules (COLLATION) are two different concepts. Character sets are used to define how MySQL stores strings, and collation rules define how to compare strings. We will explain MySQL's character set and collation rules separately later.
Example: Create a database named test_db
CREATE DATABASE test_db;
View or display the database
SHOW DATABASES;
2. MySQL adds a data table (CREATE TABLE statement)
In MySQL, you can use the CREATE TABLE statement to create a table. The syntax format is:
CREATE TABLE <表名> ([表定义选项])[表选项][分区选项]);
Among them, the format of [Table Definition Options] is:
<列名1> <类型1> [,…] <列名n> <类型n>
The CREATE TABLE command syntax is more, which mainly consists of table creation definition (create-definition), Composed of table options (table-options) and partition options (partition-options).
Tip: When using CREATE TABLE to create a table, you must specify the following information:
The name of the table to be created is not case-sensitive and cannot use SQL language Keywords in , such as DROP, ALTER, INSERT, etc.
The name and data type of each column (field) in the data table. If you create multiple columns, separate them with commas.
Example: Create a table in the specified database
Note: The data table belongs to the database. Before creating the data table, you should Use the statement "USE
Select the database test_db to create the table and create the tb_emp1 data table:
CREATE TABLE tb_emp1 ( id INT(11), name VARCHAR(25), deptId INT(11), salary FLOAT );
After the statement is executed, it is created For a data table named tb_emp1, use the SHOW TABLES; statement to check whether the data table is created successfully
##3. MySQL data Add fields to the table (ALTER TABLE statement)
You can use the ALTER TABLE statement in MySQL to change the structure of the original table, such as adding or deleting columns, changing the original column type, and re- Name columns or tables etc. A complete field includes field name, data type and constraints. The syntax format for adding fields in MySQL is as follows:ALTER TABLE <表名> ADD <新字段名><数据类型>[约束条件];
is the name of the data table;
is the name of the field to be added; - is the field that can store data Data type;
- [Constraints] is optional and is used to constrain the added fields.
This syntax format adds a new field at the last position of the table (after the last column) by default.Example: Add fields in the tb_emp1 data table
Use DESC to view the tb_emp1 table structureDESC tb_emp1;
Copy after login
使用 ALTER TABLE 语句添加一个 INT 类型的字段 age
ALTER TABLE tb_emp1 ADD age INT(4);
Copy after login4、MySQL添加数据（INSERT语句）
数据库与表创建成功以后，需要向数据库的表中插入数据。在 MySQL 中可以使用 INSERT 语句向数据库已有的表中插入一行或者多行元组数据。
INSERT 语句有两种语法形式，分别是 INSERT…VALUES 语句和 INSERT…SET 语句。
1)、 INSERT…VALUES语句
INSERT VALUES 的语法格式为：
INSERT INTO <表名> [ <列名1> [ , … <列名n>] ] VALUES (值1) [… , (值n) ];
Copy after login语法说明如下。
<表名>：指定被操作的表名。
<列名>：指定需要插入数据的列名。若向表中的所有列插入数据，则全部的列名均可以省略，直接采用 INSERT<表名>VALUES(…) 即可。
VALUES 或 VALUE 子句：该子句包含要插入的数据清单。数据清单中数据的顺序要和列的顺序相对应。
2)、INSERT…SET语句
语法格式为：
INSERT INTO <表名> SET <列名1> = <值1>, <列名2> = <值2>, …Copy after login此语句用于直接给表中的某些列指定对应的列值，即要插入的数据的列名在 SET 子句中指定，col_name 为指定的列名，等号后面为指定的数据，而对于未指定的列，列值会指定为该列的默认值。
注：当使用单条 INSERT 语句插入多行数据的时候，只需要将每行数据用圆括号括起来即可。
示例：向表中的全部字段添加值
在 test_db 数据库中创建一个课程信息表 tb_courses，包含课程编号 course_id、课程名称 course_name、课程学分 course_grade 和课程备注 course_info
CREATE TABLE tb_courses ( course_id INT NOT NULL AUTO_INCREMENT, course_name CHAR(40) NOT NULL, course_grade FLOAT NOT NULL, course_info CHAR(100) NULL, PRIMARY KEY(course_id) );
Copy after login在 tb_courses 表中插入一条新记录，course_id 值为 1，course_name 值为“Network”，course_grade 值为 3，info 值为“Computer Network”
在执行插入操作之前，查看 tb_courses 表
SELECT * FROM tb_courses;
Copy after login查询结果显示当前表内容为空，没有数据，接下来执行插入数据的操作
INSERT INTO tb_courses (course_id,course_name,course_grade,course_info) VALUES(1,'Network',3,'Computer Network');
Copy after login可以看到插入记录成功。在插入数据时，指定了 tb_courses 表的所有字段，因此将为每一个字段插入新的值。
【相关推荐：mysql视频教程】
The above is the detailed content of What are the mysql add statements?. For more information, please follow other related articles on the PHP Chinese website!
Statement of this WebsiteThe 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
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 ago By 尊渡假赌尊渡假赌尊渡假赌R.E.P.O. Best Graphic Settings4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌Assassin's Creed Shadows: Seashell Riddle Solution2 weeks ago By DDDR.E.P.O. How to Fix Audio if You Can't Hear Anyone4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌WWE 2K25: How To Unlock Everything In MyRise1 months ago By 尊渡假赌尊渡假赌尊渡假赌
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)
Hot Topics
CakePHP Tutorial
1378
52
See all articles
MySQL: The Ease of Data Management for Beginners
Apr 09, 2025 am 12:07 AM
MySQL is suitable for beginners because it is simple to install, powerful and easy to manage data. 1. Simple installation and configuration, suitable for a variety of operating systems. 2. Support basic operations such as creating databases and tables, inserting, querying, updating and deleting data. 3. Provide advanced functions such as JOIN operations and subqueries. 4. Performance can be improved through indexing, query optimization and table partitioning. 5. Support backup, recovery and security measures to ensure data security and consistency.
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: 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.
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.
How to create navicat premium
Apr 09, 2025 am 07:09 AM
Create a database using Navicat Premium: Connect to the database server and enter the connection parameters. Right-click on the server and select Create Database. Enter the name of the new database and the specified character set and collation. Connect to the new database and create the table in the Object Browser. Right-click on the table and select Insert Data to insert the data.
How to create a new connection to mysql in navicat
Apr 09, 2025 am 07:21 AM
You can create a new MySQL connection in Navicat by following the steps: Open the application and select New Connection (Ctrl N). Select "MySQL" as the connection type. Enter the hostname/IP address, port, username, and password. (Optional) Configure advanced options. Save the connection and enter the connection name.
How to execute sql in navicat
Apr 08, 2025 pm 11:42 PM
Steps to perform SQL in Navicat: Connect to the database. Create a SQL Editor window. Write SQL queries or scripts. Click the Run button to execute a query or script. View the results (if the query is executed).
Navicat connects to database error code and solution
Apr 08, 2025 pm 11:06 PM
Common errors and solutions when connecting to databases: Username or password (Error 1045) Firewall blocks connection (Error 2003) Connection timeout (Error 10060) Unable to use socket connection (Error 1042) SSL connection error (Error 10055) Too many connection attempts result in the host being blocked (Error 1129) Database does not exist (Error 1049) No permission to connect to database (Error 1000)
