MySQL (2)

Time:2020-9-23

Prepare data sheet:

CREATE TABLE student3 (
			id int,
			name varchar(20),
			age int,
			sex varchar(5),
			address varchar(100),
			math int,
			english int
			);

	"Insert into student3 (ID, name, age, sex, address, math, English) values (1,'tom', 55,'55,'mal' 'Hangzhou', 66,78), (2,'jim', 45,'female' '' Shenzhen ', 98,87), (3,'tim', 55, 'mall', 55, 'mall' ', Hong Kong', 56,77), (4, 'Liuyan', 20, 'female', 20, 'female' ', Hunan', Hunan ', 76,65), (5,' Liuqing ', 20,' Malo, '20,' Malo, 'Hunan', Hunan ', 86, null), (6,' Liu deliu DE6, 'Liu Dede, (6,' Liu Dede, (6, 'Liu Dede, (6,' Liu Dede, (6, 'liudede, (6,' liudedede, (6, 'liudedede, (6,' liudedede, (6, 'liudedede, (6,' liudechina ', 57,' mal', Hong Kong', 99,99), (7, 'made', 22, 'female', Hong Kong ', 99,99), (8, 'demacia ', 18,' mal', Nanjing', 56,65);

SQL comment:

  • Single line comment
    #This is also a single line comment, which is usually written on CMD
  • multiline comment

Query statement:

1. simple query: select * from table name

select * from student3   
			select id from student3  
	select id,name,sex from student3 

2. Where querywherekeyword

The where keyword represents a condition. What criteria is used to query
			*/
			select * from student3 where id = 1;  
	select * from student3 where name = "Tom"

3. Comparison operator query

>Greater than<less than   <=Less than or equal to    >=Greater than or equal to    be equal to    <>!=Not equal to

select * from student3 where math > 66   
	select name from student3 where  english = 0 

4. Logical operator query

and(&&)Multiple conditions are satisfied at the same time     or(||)Multiple conditions, one of which is satisfied      not(!)dissatisfaction

select name from student3 where math = 56 && english = 77 
			select * from student3 where sex in('female'); 
	select * from student3 where sex not in('female') 

5. Between values 1 and 2 query the data between values 1 and 2. Indicates the range from value 1 to value 2, with packet header and tail

select * from student3 where math between 56 and 99  
	select * from student3 where math>=56 and math<=99   

Fuzzy query, like

Format: select * from table name where field name like ‘wildcard string’;

Wildcard string:

  • %: represents 0 or more characters (any characters)
  • _: represents a character
select * from student3 where name like "T%";  
	select * from student3 where name like "T_";  

7. sorting

Order by clause: you can sort the query results (sorting is only the display mode and will not affect the order of data in the database)

Asc: ascending, ascending by default ; Desc: descending

select * from student3  order by id;  
			select * from student3 order by id desc 

	select * from student3 order by name desc 

Combination sort:

select * from student3 order by math asc,english asc  

8. Aggregate function query

Five aggregate functions:

  • count: statistics the number of specified column records, non statistics with null record
  • sum: evaluates the numeric sum of the specified column. If it is not a numeric type, the result is 0
  • max: calculates the maximum value of the specified column
  • min: calculates the minimum value of the specified column
  • avg: calculates the average value of the specified column. If it is not a numeric type, the result is 0
select count(*) from student3 where  name like "T%"  

			select sum(math) from student3    

			select max(math) from student3  

			select min(english)  from student3 

	select avg(math)  from student3  

9. Limit keyword

limit:yeslimitSoLIMITIs to limit the number of query records.  For paging query

Grammar:Limit offset, length; or limit length; offsetRefers to the offset, which can be considered as the number of records to be skipped. The default value is 0 lengthIs the total number of records that need to be displayed

select * from student3 limit 0,3   

	select * from student3 limit 3,3   

Limit application scenario:


			SELECT * FROM student3 LIMIT 0,5;
			SELECT * FROM student3 LIMIT 5,5;
	SELECT * FROM student3 LIMIT 10,5;

10. alias queryasKeywordsAs can be omitted

select name as StudentName,age as StudentAge from student3 

When querying, the database query will display the field name. We can use alias to change its presentation. When querying, we need to use as keyword to specify alias for columns and tables

select name StudentName,age StudentAge from student3 

 

The comparison is as follows:

select name,age from student3 

Add statement:

General statement:INSERT INTO TableName (FieldName1, FieldName2, FieldName3…) VALUES (Value1, Value2, Value3);

insert into student3(`name`,age,sex,address,math,english) values ('Lee',21,'male','Shenzhen',98,68)  

Omitted statements:INSERT INTO TableName VALUES (Value1, Value2, Value3…);

insert into student3 values (18,'Lee',21,'male','Shenzhen',98,68)  

Insert multiple statements:INSERT INTO TableName VALUES (Value1, Value2, Value3…),(Value1, Value2, Value3…),(Value1, Value2, Value3…),(Value1, Value2, Value3…)..... ;

insert into student3 (`name`,age,sex,address,math,english) 
			values 
			('Lee1',21,'male','Shenzhen',98,68),
			('Lee2',21,'male','Shenzhen',98,68),
			('Lee3',21,'male','Shenzhen',98,68),
			('Lee4',21,'male','Shenzhen',98,68),
	('Lee5',21,'male','Shenzhen',98,68);

be careful:

1. insert into student3 values (18,'Lee',21,'male','Shenzhen',98,68)  
--Insert a piece of data
2、 insert into student3(`name`,age,sex,address,math,english) values ('Lee',21,'male','Shenzhen',98,68)  
--The difference between inserting a piece of data: first, there is no restriction on the fields to be inserted. All the fields inserted into the database by default should be added with the primary key ID. the second one specifies the number of fields. Since the ID is an auto increment of the primary key, I will not write it. Of course, it is OK to add it.

Worm replication :INSERT INTO TableName1 SELECT * FROM TableName2;

CREATE TABLE student2 LIKE student3;     
	insert into student2  select * from student3  

Note: the table structure of Table 1 and table 2 should be consistent

Modify statement:

Syntax:update TableName set ColumnName = Value where ColumnName =Value

update student3 set `name` = 'Mary' where id = 4 
	update student3 set age = 22 where `name` = 'Tom' 

Note: modify the statement with a where condition

Delete statement:

Delete all table data: delete from tablename;

delete from student2  

Destroy table:TRUNCATE TABLE TableName;

truncate table student2   

Delete according to conditions:DELETE FROM TableName WHERE Field=Value;

delete from student3 where id = 18 
delete from student3 where id = 19 || id = 20  

 

Personal learning, the content is brief