What are the mysql add statements?-Mysql Tutorial-php.cn

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.

What are the mysql add statements?

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 校对规则名];

Copy after login

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;

Copy after login

What are the mysql add statements?

View or display the database

SHOW DATABASES;

Copy after login

What are the mysql add statements?

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 <表名> ([表定义选项])[表选项][分区选项]);

Copy after login

Among them, the format of [Table Definition Options] is:

<列名1> <类型1> [,…] <列名n> <类型n>

Copy after login

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" to specify which database the operation is to be performed in. If no database is selected, a No database selected error will be thrown.

Select the database test_db to create the table and create the tb_emp1 data table:

What are the mysql add statements?

CREATE TABLE tb_emp1
(
id INT(11),
name VARCHAR(25),
deptId INT(11),
salary FLOAT
);

Copy after login

What are the mysql add statements?

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

What are the mysql add statements?

##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 <新字段名><数据类型>[约束条件];

Copy after login

The syntax format is explained as follows:

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 structure

DESC tb_emp1;

Copy after login

What are the mysql add statements?

使用 ALTER TABLE 语句添加一个 INT 类型的字段 age

ALTER TABLE tb_emp1 ADD age INT(4);

Copy after login

What are the mysql add statements?

4、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

What are the mysql add statements?

查询结果显示当前表内容为空，没有数据，接下来执行插入数据的操作

INSERT INTO tb_courses
(course_id,course_name,course_grade,course_info)
VALUES(1,&#39;Network&#39;,3,&#39;Computer Network&#39;);

Copy after login

What are the mysql add statements?

可以看到插入记录成功。在插入数据时，指定了 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!

Related labels：

mysql

source：php.cn

Previous article：Does mysql have triggers? Next article：How to implement group sum in mysql

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