Mysql barrier free and easy learning (IV) addition, deletion, modification and query of Mysql, CRUD quick entry

Mysql barrier free learning series

Mysql barrier free and easy learning (I) installation and configuration under Linux
Mysql barrier free and easy learning (II) database tool DataGrid download and indefinite trial
Mysql barrier free and easy learning (III) DataGrid connects Mysql and creates a database

1, What is CRUD?

crud refers to the acronyms of create, retrieve, update and delete words when doing calculation processing. crud is mainly used to describe the basic operation functions of database or persistence layer in software system.

2, Create data table

1.Sql

General SQL syntax for creating MySQL data table:

CREATE TABLE table_name (column_name column_type);

In the following example, we will create the data table RUNOOB in the RUNOOB database_ tbl:

CREATE TABLE `insert_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(300) DEFAULT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Instance resolution:
AUTO_INCREMENT defines that the column is a self incrementing attribute, which is generally used for the primary key, and the value will be automatically incremented by 1.
PRIMARY KEY keyword is used to define a column as a PRIMARY KEY.
ENGINE sets the storage ENGINE and CHARSET sets the encoding.

2.DataGrip

1. Right click our study library and select new - > table

2. In the pop-up window, click the plus sign to fill in the field name

Click execute

2, Add (Create)

1.INSERT

INSERT INTO SQL statements are used in MySQL tables to insert data.

INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );

If the data is character type, you must use single quotation marks or double quotation marks, such as "value".

Three pieces of data are inserted

It can be seen that three pieces of data are inserted successfully

2, Retrieve

1.SELECT

MySQL database uses SQL SELECT statement to query data.
You can use one or more fields in a query statement, separated by commas (,).

SELECT column_name,column_name
FROM table_name

For example:

SELECT id,age
FROM insert_test

Query only id and age

2.WHERE

Use the WHERE statement to include any conditions

SELECT column_name,column_name
FROM table_name
WHERE Clause

For example:

SELECT id,age
FROM insert_test
WHERE age = 20

Only data with id and age and only data with age equal to 20 are queried

3. Asterisk (*)

Use the WHERE statement to include any conditions

SELECT *
FROM table_name

For example:

SELECT *
FROM insert_test

All data of all fields will be queried

4.LIMIT

Use the LIMIT property to set the number of records returned.

SELECT column_name,column_name
FROM table_name
LIMIT N

For example:

SELECT *
FROM insert_test
LIMIT 2

At this time, the first two pieces of all data of all fields will be queried, and limit 2 is the two pieces of restricted output

5.OFFSET

Use OFFSET to specify the data OFFSET of the query.

SELECT column_name,column_name
FROM table_name
LIMIT N
OFFSET M

For example:

SELECT *
FROM insert_test
LIMIT 2
OFFSET 1

At this time, all data of all fields will be queried. Skip the first two after one, limit 2 is to limit the output of two, and OFFSET 1 is to skip one

3, Modify (Update)

1.Update

Use the SQL UPDATE command to manipulate data in MySQL tables. If the WHERE clause is not specified, all records in the MySQL table will be modified.

UPDATE table_name 
SET field1=new-value1, field2=new-value2

For example:

Update insert_test
set user_name = 'Zhang San'

The insert is updated_ All users in the test table_ name

2.where

Use the SQL UPDATE WHERE command in MySQL tables to manipulate the specified data.

UPDATE table_name 
SET field1=new-value1, field2=new-value2
WHERE Clause

For example:

Update insert_test
set user_name = 'wh'
where age = 20

The insert is updated_ All users with age equal to 20 in the test table_ name

3, Delete

1.Delete

In MySQL tables, use the DELETE FROM command of SQL to delete records in MySQL data tables. If the WHERE clause is not specified, all records in the MySQL table will be deleted.

DELETE 
FROM table_name 

For example:

DELETE 
FROM table_name 

The insert is deleted_ All data in test table

2.where

Use the SQL DELETE WHERE command to manipulate data in MySQL tables.

DELETE 
FROM table_name 
WHERE Clause

For example:

DELETE
FROM insert_test
WHERE age = 20

The insert is deleted_ All data of test table when age is equal to 20

summary

Mysql barrier free and easy learning series set sail, providing a simple and practical learning series with less concepts and more operations. In the first step of zero foundation, we use tools to familiarize ourselves with and use the database to achieve our goal. The purpose of barrier free learning is here to make all programs simple and transparent.

I hope this blog will be beneficial to you. I'm the light king. I speak for myself.