Add, delete, modify, query, and delete a single table in basic MYSQL operations

Add, delete, modify, query, and delete a single table in basic MYSQL operations

1. Add data.

-- 1. Create a table and insert some data.

1 create table student (2 id int, 3 username varchar (20), 4 servlet int, 5 jsp int, 6 address varchar (30) 7 ); 8 insert into student values (1, 'zhang san', 'jingkou district '); 9 insert into student values (2, 'Li si', 'runzhou district '); 10 insert into student values (3, 'wang wu', 'jingkou district '); 11 insert into student values (4, 'zhao liu', 'jingkou district '); 12 insert into student values (5, 'Lee 7', 63,98, 'dagang district '); 13 insert into student values (6, 'zheng 8', 99,89, null ); 14 insert into student values (7, 'Sun jiu', 60, 84 ,'');

-- 2. Insert Part of data

Insert into student (ID, USERNAME) VALUES (8, 'Lee 10 ');

Ii. modify data

-- 1. modify data based on conditions

 UPDATE STUDENT SET SERVLET = 86 WHERE ID =1;

-- 2. modify the content of Multiple Fields

UPDATE STUDENT SET SERVLET = 65,JSP = 73 WHERE ID = 2;

 

3. delete data (no demonstration is available)

-- 7. Delete all data

-- Conditional deletion is supported. Only data in the table can be deleted. The constraints of the table cannot be deleted. That is, the number of self-increasing values increases after the last Deleted Values. The deleted data can be rolled back.
Delete from student;

-- 8. Conditional Deletion
Delete from student where sid = 2;
-- 9. Another deletion method is to output all data in the table.

-- Conditional deletion is not allowed. You can delete table data or table constraints and delete them permanently.

Truncate table student;

4. Single Table query

-- 1. query all columns

SELECT * FROM STUDENT;

 

-- 2. query specified Columns

SELECT ID ,USERNAME FROM STUDENT;

-- 3. Specify the alias during Query
-- The table alias is often used for multi-table queries.

Select id as 'number', username as 'username' from student as s;

 

-- 4. Add A constant column during A query

Select id, USERNAME, 'This is a class' AS 'note' from student;

-- 5. Parallel Query
-- Query the total SERVLET and JSP scores of each student
-- Merge columns can only merge numeric fields.

Select id, USERNAME, (SERVLET + JSP) AS 'total score 'from student;

 

-- 6. query and remove duplicate records
-- Query all possible JSP scores

SELECT DISTINCT ADDRESS FROM STUDENT;

-- Another syntax

SELECT DISTINCT(ADDRESS) FROM STUDENT; 

-- 7. Conditional query (WHERE)
-- 7.1 logical condition AND OR
-- Query the student whose SID is 2 and whose name is Li Si

SELECT * from student where id = 2 and username = 'Li si ';

 

-- 7.1.1 query a student whose SID is 2 or whose name is James

SELECT * from student where id = 2 or username = 'zhang san ';

-- 7.2 comparison condition> <>===<> BETWEEN AND
-- 7.2.1 query for students whose SERVLET score is greater than 70

SELECT * FROM STUDENT WHERE SERVLET > 70;

 

-- 7.2.2 query for students whose SERVLET score is greater than 60 and less than 80

SELECT * FROM STUDENT WHERE SERVLET >60 AND SERVLET < 80;

-- 7.2.3 query SERVLET scores greater than or equal to 70 students less than or equal to 80

SELECT * FROM STUDENT WHERE SERVLET >=70 AND SERVLET <=80;

-- 7.2.4 another syntax

SELECT * FROM STUDENT WHERE SERVLET BETWEEN 70 AND 80;

-- 7.2.5 query records with names not equal to Michael Jacob

SELECT * from student where username <> 'zhang san ';

 

-- 7.3 null (NULL String) IS null is not null = ''<>''
-- 7.3.1 judge NULL

SELECT * FROM STUDENT WHERE ADDRESS IS NULL;

-- 7.3.2 judge an empty string

SELECT * FROM STUDENT WHERE ADDRESS ='';

-- 7.3.3 query records with an empty address

SELECT * FROM STUDENT WHERE ADDRESS IS NULL OR ADDRESS = '';

-- 7.3.4 query records whose addresses are not empty

SELECT * FROM STUDENT WHERE ADDRESS IS NOT NULL AND ADDRESS <>'';

-- 7.4 fuzzy condition LIKE
-- The following replacement mark is usually used: %: represents any character; _: represents a character
-- 7.4.1 query records of student Li

SELECT * from student where username like 'Lee % ';

 

-- 8. Aggregate Query
-- Common Aggregate functions: SUM () AVG () MAX () MIN () COUNT ()
-- 8.1 query the total SERVLET score of a student

Select sum (SERVLET) AS 'total SERVLET score 'from student;

-- 8.2 query the average score of the student SERVLET

Select avg (SERVLET) AS 'average SERVLET score 'from student;

-- 8.3 query the highest score of the current SERVLET

Select max (SERVLET) AS 'highest point' from student;

-- 8.4 query the SERVLET shard

Select min (SERVLET) AS 'minute sub' from student;

-- Query the current number of student COUNT (field)

SELECT COUNT(*) FROM STUDENT;

-- 9. query the start line of the LIMIT by page. It is mainly used for paging.
-- Query records 1 and 2

SELECT * FROM STUDENT LIMIT 0,2;

-- 10. sort by default, sorted by the order of inserted records. ORDER
-- 10.1ASC forward order DESC Reverse Order

SELECT * FROM STUDENT ORDER BY ID DESC;

-- 10.2 in the forward order of the SERVLET, and then in the reverse order of the JSP

SELECT * FROM STUDENT ORDER BY SERVLET ASC ,JSP DESC;

-- 11. GROUP query GROUP
-- Query the number of people in each region

SELECT ADDRESS,COUNT(*) FROM STUDENT GROUP BY ADDRESS;

-- 12. Filter After grouping Query
-- The region group by with more than one person in the region after the query GROUP cannot be followed BY the condition where

SELECT ADDRESS ,COUNT(*) FROM STUDENT GROUP BY ADDRESS HAVING COUNT(*)>1;