MySQL detailed single table add, delete, modify and query CRUD statements

MySQL add, delete, modify and query statements

1. Create a practice sheet

• The exercise table here does not satisfy the three normal forms

First Normal Form (also known as 1NF): Ensure the atomicity of each column. Each column (field) in the data table must be the smallest indivisible unit, that is, ensure the atomicity of each column. Meeting the first normal form is the minimum requirement for normalization of a relational model. Otherwise, many basic operations cannot be implemented in such a relational model.
Second Normal Form (also known as 2NF): Ensure that a table describes only one thing. After meeting 1NF, all columns in the table must be related to only one column, that is, a row of data can only do one thing. Whenever there is duplicate data in a column, the table must be split.
The third normal form (also known as 3NF): ensure that each column is directly related to the primary key. After satisfying 2NF, the requirement is: each column in the table must be directly related to the primary key, rather than indirectly related (each column in the table can only depend on the primary key).
The data cannot have a transitive relationship, that is, every attribute has a direct relationship with the primary key rather than an indirect relationship. Relationships between attributes like a–>b–>c do not conform to the third normal form.

• Five constraints

The five major constraints in the database include:

1. Primary Key Constraint: uniqueness and non-nullability;

2. Unique constraint (Unique Counstraint) uniqueness, can be empty, but there can only be one;

3.Default Constraint: The default value of the data;

4. Foreign Key Constraint: A relationship between two tables needs to be established;

5. Not Null Constraint: Set a not null constraint, the field cannot be empty.

Syntax examples of the five constraints:

Adding a primary key constraint
Alter table table name add Constraint primary key name primary key (field)

Adding a unique constraint
Alter table table name add Constraint constraint name unique (field)

Adding a default constraint
Alter table table name add Constraint constraint name default (default content) for field name

Adding a Check Constraint
Alter table table name add Constraint constraint name check (field expression)

Adding a foreign key constraint
Alter table table name add Constraint constraint name foreign key (field) references table name (field name)

1.1 User Table (user)

CREATE TABLE `user`(
	`id` INT AUTO_INCREMENT PRIMARY KEY COMMENT 'User id (primary key)',
	`username` VARCHAR(50) COMMENT 'User name', 
	`age` CHAR(3) COMMENT 'User age'
);

Inserting Data

INSERT INTO USER VALUES(2,'小等',12);
INSERT INTO USER VALUES(3,'张三',33);
INSERT INTO USER VALUES(4,'李四',24);
INSERT INTO USER VALUES(5,'王五',17);
INSERT INTO USER VALUES(6,'赵六',36);
INSERT INTO USER VALUES(7,'七七',18);

After completion, the table name is user
Table data:

1.2 Student Table

CREATE TABLE `students`(
	`id` INT AUTO_INCREMENT PRIMARY KEY COMMENT 'student id (primary key)',
	`name` VARCHAR(10) COMMENT 'Student name', 
	`grade` VARCHAR(10) COMMENT 'Student grade',
	`chinese` INT COMMENT 'Chinese language score',
	`math` INT COMMENT 'Mathematics results',
	`english` INT COMMENT 'English scores'
);

Inserting Data

INSERT INTO students VALUES(1,'迪丽热巴','第二年级',100,100,100);
INSERT INTO students VALUES(2,'Gulinaza','First Grade',99,88,98);
INSERT INTO students VALUES(3,'Malzahar','Grade 3',46,20,99);
INSERT INTO students VALUES(4,'Alibaba','First Grade',78,81,100);
INSERT INTO students VALUES(5,'哈哈哈哈','六年级',20,10,5);
INSERT INTO students VALUES(6,'Author','Second Grade',100,100,100);
INSERT INTO students VALUES(7,'嘻嘻哈哈','五年级',70,99,60);
INSERT INTO students VALUES(8,'crying','fourth grade',33,15,54);

If the column name or table name conflicts with a MySQL keyword, you can use the ` symbol, below the Esc key, to the left of 1, or above the Tab key.
Note ==> If the grade type is varcher, then using sort will not display correctly.

After completion, the table name is students
Table data:

2. Query

2.1 Query all (here using the user table)

Select * from table name;

SELECT * FROM USER;

2.2 Query users who have reached adulthood (using where condition query)

• Comparison operators in conditions: (equal to: = greater than: > greater than or equal to: >= less than: < less than or equal to: <= not equal to: != or <> )
• Select * from table name where condition;

SELECT * FROM USER WHERE age >= 18;

2.3 Query the name of the underage user

SELECT field name FROM table name WHERE condition;

SELECT username FROM USER WHERE age < 18;

2.4 Use Alias ​​Query

Use the as keyword (as can be omitted)

SELECT u.* FROM USER AS u;

2.5 Query using logical operators

Logical operators (and: and or && or or not: not or !)

Search for names of Xiaoer and ages of 12 (display name and age)

SELECT u.`username`,u.`age` FROM USER u WHERE u.`username`='小等' && u.`age`=12;

Search for people whose name is Zhang San and who are underage (the query result is empty, there is no such person)

SELECT * FROM USER WHERE username='张三' && age < 18;

2.6 Use operators to query (start using the students table here)

Query the operator priority of hahahaha's total score in three subjects:
1. Multiplication and division take precedence over addition and subtraction
2. The order of operations of the same level is from left to right
3. Use "brackets" in expressions to forcibly change the order of priority operations
select column 1 [ ±*/ ] column 2 from table name where condition;

SELECT s.chinese + s.math + s.english FROM students s WHERE s.name = '哈哈哈哈';

2.7 Range query (between condition 1 and condition 2)

(Good 60-70, Fair 70-80, Excellent 80-90, Excellent 90-100)

Query the names of students whose Chinese scores are good to medium
where field (column name) between condition 1 and condition 2;

SELECT NAME FROM students WHERE chinese BETWEEN 60 AND 80;

Query the names of students who failed math

two

where field (column name) not between condition 1 and condition 2;

SELECT NAME FROM students WHERE math NOT BETWEEN 60 AND 100;

Query the ID and name of students with excellent English scores;

three

where !(field name between condition 1 and condition 2);

SELECT s.`id`,s.`name` FROM students s WHERE !(s.`english` BETWEEN 0 AND 90);

2.8 Collection query to query the information of first-year and second-year students

where column name in (value1, value2, value3);

SELECT * FROM students WHERE grade IN ('First grade','Second grade');

*Query information of students other than first, second and third graders
where column name not in (value1, value2, value3);

SELECT * FROM students WHERE grade NOT IN ('First grade','Second grade','Third grade');

2.9 NULL Value Query

Note: If the value in the column is null, you cannot use = to query

Query the student data whose name is null (no data is stored, so there is nothing)
where column name is null;

SELECT * FROM students WHERE `name` IS NULL;

2.10 Fuzzy query (like)% represents 0 to n characters

where column name like '%巴'; -- means ending with 巴;
where column name like '巴%'; -- means starting with 巴;
where column name like '%巴巴%' -- means the data contains 巴巴;

Find students whose names end with 巴;

SELECT * FROM students WHERE `name` LIKE '%巴';

Find students whose names begin with author;

SELECT * FROM students WHERE `name` LIKE '作者%';

Find students whose names contain hip-hop;

SELECT * FROM students WHERE `name` LIKE '%嘻哈%';

_ represents a character and can be used multiple times

Search for students whose second to last character in their name contains "ha"
where column name like '哈_';

SELECT * FROM students WHERE `name` LIKE '%哈_';

2.11 Result Sorting

Sort the results by row (sort the query results by one or more columns in ascending or descending order, ascending order is ASC, descending order is DESC, ascending order is the default).

Please note that you cannot use Chinese or Chinese alias sorting.
where condition order by column name [ASC / DESC];

Query the data of first-grade, second-grade, and third-grade students, and sort them by Chinese scores in descending order.

SELECT * FROM students WHERE grade IN ('First grade','Second grade','Third grade') ORDER BY chinese DESC;

2.12 Pagination Query

Pagination query (beginIndex indicates the number of data to start from (or how many pages to skip), the first page starts at 0. pageSize indicates how many data are displayed per page);
select * from table name where condition limit beginIndex , pageSize;

Paging algorithm formula (current page - 1) * pageSize;

For example, there are 10 pages per page.
First page limit 0, 10; // Display 0-10; Skip the first 0 data Second page limit 10, 10; // Display 11-20; Skip the first 10 data Third page limit 20, 10; // Display 21-30; Skip the first 20 data

Note that when we use select * from students; (the bottom-level execution statement paginates limit 0, 1000)

Query all students, three records per page, first page

SELECT * FROM students LIMIT 0,3;

Page 2

SELECT * FROM students LIMIT 3,3;

Page 3

SELECT * FROM students LIMIT 6,3;

2.13 Aggregate Functions

Acts on a set of data and returns a value for that set

• count: the number of records in the statistical result.
• max: maximum value of statistics
• min: minimum value of statistics sum: sum of calculations
• avg: Calculate the average value

Note the grouping function group by. If you want to filter the grouped data, you must use the having keyword, and the condition should be written after having;

• where: filter the existing data first, then group it, and then calculate the aggregate function;
• having: Filter the data after grouping.

Insert the city table we need to practice

CREATE TABLE `city` (
	`id` INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Primary key',
	`city_name` VARCHAR(100) COMMENT 'city name',
	`city_year` VARCHAR(4) COMMENT 'Year, yyyy',
	`city_gdp` DOUBLE COMMENT 'The GDP of the city for the current year, in 100 million yuan',
	`city_population` DOUBLE COMMENT 'The total population of the city in the current year of good harvest, in 10,000 people'
);

Add data

INSERT INTO city VALUES(1,'Shanghai',2018,32679,2418);
INSERT INTO city VALUES(2,'Beijing',2018,30320,2171);
INSERT INTO city VALUES(3,'深圳',2018,24691,1253);
INSERT INTO city VALUES(4,'Guangzhou',2018,23000,1450);
INSERT INTO city VALUES(5,'重庆',2018,20363,3372);
INSERT INTO city VALUES(6,'Shanghai',2019,38155,2424);
INSERT INTO city VALUES(7,'Beijing',2019,35371,2171);
INSERT INTO city VALUES(8,'深圳',2019,26927,1302);
INSERT INTO city VALUES(9,'Guangzhou',2019,23628,1491);
INSERT INTO city VALUES(10,'重庆',2019,23605,3372);

COUNT
Query and calculate how many cities have entered data in 2019 (answers 5)

SELECT COUNT(*) FROM city WHERE city_year = '2019';

MAX
Check what the highest GDP was in 2018;

SELECT MAX(city_gdp) FROM city WHERE city_year = '2018';

MIN
Find out what the lowest GDP was in 2018;

SELECT MIN(city_gdp) FROM city WHERE city_year = '2018';

SUM
Query the total GDP of all cities in 2019;

SELECT SUM(city_gdp) FROM city WHERE city_year = '2019';

AVG
Query the average GDP of all cities in 2019;

SELECT AVG(city_gdp) FROM city WHERE city_year = '2019';

3. Increase

insert into table name (field 1, field 2, field 3, ...) values ​​(value 1, value 2, value 3, ...);

Add student cock

INSERT INTO students(id,NAME,grade,chinese,math,english) 
VALUES(9,'鸡鸡鸡鸡','七年级',77,77,77);

Other insertion methods

INSERT INTO table name (field 1, field 2, field 3) VALUES (value 1, value 2, value 3), (value 1, value 2, value 3); -- Insert multiple data INSERT INTO table name VALUES (value 1, value 2); -- Insert all fields of the table INSERT INTO table name (field) SELECT field FROM table 2; -- Insert the query results INSERT INTO table name SELECT field FROM table 2; -- Query results, insert the entire table

4. Update

update table name set field = value where condition; -- Modify the specified data with condition, otherwise modify the entire table;

Modify the English score of student 'Hahahaha' to 10;

UPDATE students SET english = 10 WHERE `name` = '哈哈哈哈';

5. Delete

delete from table name where condition; -- delete data with condition, otherwise delete all data in the table

Delete student '鸡鸡鸡';

DELETE FROM students WHERE `name` = '鸡鸡鸡鸡';

6. Statement execution order

from -> on -> join -> where -> group by -> having -> select -> distinct -> order by -> limit

For other MySQL articles, please see the following links

MySQL DDL statements

MySQL CRUD statements

MySQL Aggregate Functions

MySQL multi-table query

END…

This is the end of this article about MySQL detailed single table CRUD statements. For more relevant MySQL CRUD statements, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!