Note: this blog is MySql table operation instruction 1. If you want to learn the basic MySql instructions, you can use the author Blog Learning; If you want to practice further, you can do it after learning Operation 2 After that, according to Blog MySql instance Practice!

Add, delete, query and modify ~ simple instructions

Create table

The key to create a database is mainly create database, as follows:

create database dbname; -- dbname Refers to the database name
create database if not exists dbname;  --If not, create

The key words for creating the database are mainly create table. For example, create a student table with student number, name, age, gender and height, as follows

create table if not exists student(
	snum int comment 'Student number',
	name varchar(32) comment 'full name',
	age int comment 'Age',
	sex varchar(1) comment 'Gender',
	heig int comment 'height'
);

give the result as follows

insert data

There are two methods to insert data into a table, that is, single row insertion and multi row insertion. The keyword used is insert, as shown below

Single line insertion:

	insert into student values(1101, 'Long dada', 18, 'male', 175);
	insert into student(name, snum, sex)value('Beibei',1201,'female'); -- Specify insert field

Note: the statement select * from tbname; to view the table content, tbname refers to the name of the table!

Multiline insert:

	insert into student values(1102, 'Dragon 2', 28, 'male', 175),
		(1103, 'Dragon 3', 38, 'male', 185);

Modify data

The keyword for modifying the table content is update, as follows:

update tbname set name=xxxxxx,sex=xxxx； -- Modify the corresponding data of everyone in the table, where tbname Refers to the table name

The above statement will modify the corresponding content of everyone in the table. Therefore, in order to achieve the goal of modifying the specified personal data, it is often processed in conjunction with the conditional instruction where (the operations about where will be described later). Therefore, the above statement can be changed to

update tbname set name=xxxxxx where name2=xxx; -- among name For the data name to be changed, name2 Is the index of the target to be changed

For example, modify Beibei's age and height in the student table

update student set age = 17, heig = 169 where snum = 1201; -- where snum = 1201 The function of is to find student data with student number 1201

give the result as follows

Delete data

The keyword used to delete data is delete, as follows:

Delete all data in the table:
	delete from tbname; 
Delete table name by xxxx Data:
	delete from tbname where name=xxxx; 

for example
Delete the data named Dragon 3

delete from student where name='Dragon 3';

The result is

Delete all data in the table

delete from student;

The result is

query

Default full columns:  select * from tbname; 

Specify column query: select name1,name2... from tbname;  display name1 And name2 Contents of columns
		   select name1+name2... from tbname;   take name1 And name2 The contents of the columns are added and displayed
		   
Alias: as
	select name1+name2...  as xx from tbname;  take name1+name2...The and aliases are xx View, but xx It doesn't exist in the original table, where as Negligible
	as select name1+name2...  as xx，name3 from tbname;  It not only gives an alias, but also shows name3
		
duplicate removal: distinct
	select distinct name1,name2 from tbname;  In this way, multiple columns are de duplicated together
	
Sort: order by
	select * from tbname order by name desc;  according to name In descending order. desc Expressed in descending order, none desc The default is ascending( asc)
	select * from tbname order by name1 desc,name2 asc; with name1 In descending order, name2 Ascending arrangement
		
Paging query: limit
	select * from tbname limit 3;  View the first 3 rows of data, random
	select * from tbname order by name desc limit 3;  with name View the first 3 rows of data in descending order
	select * from tbname order by name desc limit 3 offset 0;  with name View the first 3 rows of data starting from row 0 in descending order   offset Is the offset

Condition query: where    There are many other operations here
		select * from tbname where name1 = xxx;  query name1 by xxx Information about
	Relational operators:
		Comparison:  > , >= , < , <= , = NULL Unsafe will report an error <=>be equal to NULL Safe and error free!= , <>Not equal to
			(Note: see the following table here)
			
		Null value: is null，is not null
		  as select * from tbname where name is null;
		  
		Range: between ... and ...
			  select * from tbname where name1 between xx and xxx;
				
		Subset matching: in((subset)
			  select * from tbname where name in(x1,x2,x3);
				
		Fuzzy matching: like
			  select * from tbname where name like '%xxx';  % Match multiple arbitrary characters (including 0)
			  select * from tbname where name like '_xxx';  _ Match a strict arbitrary character
			
	Logical operators:
		And: binocular, and Connect two comparison conditions  --- If both are true, the result is true
			select * from tbname where name1 >= x1 and name2 < x2;
		Or: binocular, or Connect two comparison conditions  --- If both are false, the result is false
			select * from tbname where name1 >= x1 or name2 < x2;
		Non: monocular, not For a single comparison condition  --- If the condition is false, the result is true
			select * from tbname where not name1 >= x1;
			

Comparison operator

operator	explain
>,>=,<,<=	Greater than, greater than or equal to, less than, less than or equal to
=	It is not safe to be equal to NULL. For example, the result of NULL = NULL is NULL
<=>	Equal to, NULL is safe. For example, NULL < = > the result of NULL is true, that is, there will be no error with NULL
!=,<>	Not equal to
between a and b	Range matching, [a,b]. If a < = value < = B, return true
in(op1,op2...)	If it is any one of the op, return true
is NULL	Is NULL
is not NULL	Not NULL
like	Fuzzy matching,% represents any number (including 0) of any characters_ Represents any character

Logical operator

operator	explain
and	Multiple conditions must be true for the result to be true
or	Any condition is true and the result is true
not	The condition is true and the result is false

Note:

1. where conditions can use expressions, but not aliases.
2. and takes precedence over or. When used at the same time, you need to use parentheses () to wrap the priority part

The above is the main content of this blog!

Infringement deletion~