Operation syntax of MySQL basic syntax on tables

This paper summarizes the syntax of adding, deleting and modifying data in tables in MySQL and the syntax of creating tables

Add data to the table (insert)

Adding data to a table can be done individually or in batches

Basic grammar

1. Add single

insert into Table name(field,field,field...) values(field value,field value,field value...)
//Note that the fields and field values should correspond to each other one by one
insert into Table name values(field value,field value,field value...) //Abbreviation
// This method is not recommended because the insert statement will be affected when the position of the field in the database table changes

test

insert into student(id,name,age,height) values(1,'Zhang San','20','172');

1. Batch add

insert into Table name(field,field,field...) values(field value,field value,field value...),
(field value,field value,field value...),(field value,field value,field value...),...
//Note that the fields and field values should correspond to each other one by one

test

insert into student(id,name,age,height) values(1,'Zhang San','20','172'),(2,'Li Si','19','173'),
(3,'Wang Wu','20','171');

How to insert time?

1. The inserted date format is consistent with the displayed date format

insert into student(id,name,start_time) values(1,'Zhang San','2020-09-01')

1. Using str_to_date

Date format of mysql

format	meaning
%Y	year
%m	month
%d	day
%h	Time
%i	branch
%s	second

insert into student(id,name,start_time) values(1,'Zhang San',str_to_date('2020-09-01','%Y-%m-%d'))

1. Add system date (now)

insert into student(id,name,start_time) values(1,'Zhang San',now()) //The now() function displays the time of the current system

Modify the data in the table (update)

Modify the data in some tables you want to modify

Basic grammar

update Table name set field = field value,field = field value,field = field value... where condition;

test

Change Zhang San's age to 21 and his height to 175

update student set age = 21,height = 175 where name = 'Zhang San';

Delete data in table (delete)

You can delete one or some data of a table, or delete all the data of a table

delete from Table name where condition   //Delete one or some data
delete from Table name  //Clear all data in the table

If you want to empty a large number of data, you need to use another method, truncate, to empty all the data in a table

truncate table Table name;

delete	truncate
Principle: the data in the table is deleted, but the real storage space of this data on the hard disk will not be released	Principle: the deletion efficiency is relatively high. The table is truncated at one time and physically deleted
Disadvantages: deletion efficiency is relatively low	Disadvantages: rollback is not supported
Advantages: rollback is supported, and data can be recovered after regret	Advantages: fast

test

delete from student where name = 'Zhang San'   //Delete all information of Zhang San
delete from student  //Clear all data in student table
truncate table student;  //Clear all data in the student table prompt: physical deletion

Create (create table)

When creating fields in the table, each field has a field strikethrough format name, field data type, field length limit and field constraint

Basic grammar

create table Table name(
	Field name data type field length limit field constraint,    //The data type is determined by the demand, and the field constraint is added by the demand
	Field name data type field length limit field constraint,
	Field name data type field length limit field constraint,
	...
)

data type

type	describe
Char (length)	Fixed length string with fixed storage space. It is suitable for primary key or foreign key
Varchar (length)	Variable length string. The storage space is equal to the actual data space
Double (significant digits, decimal places)	Numerical type
Float (significant digits, decimal places)	Numerical type
Int (length)	integer
Bigint (length)	Long integer
Date	Date type
blob	Binary Large OBject
Other types	...

Common constraints

Non NULL constraint not null the value of a constraint field cannot be empty
Unique constraint the value of the unique constraint field cannot be repeated. It can only be unique and can be empty
Primary key constraints primary key identifies the uniqueness of records
The foreign key constraint is mainly used to maintain the relationship between tables

Two additional points

1. unique constraints can be combined with multiple fields as constraints
2. Primary key constraints are divided into single primary key and composite (Federated) primary key. A single primary key is composed of one field and a composite (Federated) primary key is composed of multiple fields

test

• student table

create table student(
	id int primary key,   //The length limit of int type can be omitted, and the id is not written as the primary key
	student_id  char(11) not null unique,  //Student ID, unique and not empty
	name varchar(50)  not null,   //Student name; cannot be blank
	cno int //Foreign key class id
	foreign key(cno) references class(id) // Use the primary key id of the class table as the foreign key of the table
)

• class table

create table class(
	id int primary key,  //As the primary key, the class id is also the foreign key of the student table
	class_name varchar(255) not null unique  //Class name, unique and not empty	
)

The foreign key is not necessarily the primary key of another table, because the foreign key can be empty. Generally, you only need to ensure that the field as the foreign key is unique

Test the joint constraints of unique and primary key constraints

• student table

create table student(
	id int primary key,   //The length limit of int type can be omitted, and the id is not written as the primary key
	student_id  char(11) not null,  //Student ID, unique and not empty
	name varchar(50)  not null,   //Student name; cannot be blank
	cno int //Foreign key class id
	foreign key(cno) references class(id) // Use the primary key id of the class table as the foreign key of the table
	unique(student_id,name) // The combination of student ID and name is not unique, and the constraint is not added after the column. This constraint is called table level constraint.
)

• class table

create table class(
	id int,  //As the primary key, the class id is also the foreign key of the student table
	class_name varchar(255) not null //Class name, unique and not empty	
	primary key(id,class_name) // Class id and class name are combined as primary keys
)

In actual development, it is not recommended to use a composite primary key, but a single primary key

Tips

When creating a table with a primary foreign key, first create a table with a foreign key field, and then create a table with a field referencing it as a foreign key. If you delete it, it is the opposite. First delete the table referencing the foreign key, and then delete the table as a foreign key