I DDL operation

1. Some operations related to tables

Change table name

alter table name rename new table name;

Change field name

alter table name change column name new column name data type;

Add field

alter table name add column name type;

Delete field

alter table name drop column name;

Change the field type (try not to change it)

alter table name modify column name new data type;

View table creation statement

show create table name;

II constraint

Constraint type	keyword
Primary key	primary key
Foreign key	foreign key
only	unique
Non empty	not null
Self increasing	auto_increment
default	default

III Conditional judgment

1.and

and can be used to connect multiple conditions

example:

select * from student where name='Zhang San' and score > 90;

2.or

or

example:

select * from student where name='Zhang San' or score > 90;

3. Relational expression

expression	meaning
>	greater than
<	less than
>=	Greater than or equal to
<=	Less than or equal to
=	be equal to
<>	Not equal to
is null	Empty
is not null	Not empty

4.between and

Between the two

select * from student where score >= 98 and score<=100;
Equivalent to
select * from student where score between 98 and 100;

5.in

Use in when the condition is a range

example:

select Column qualification from Table limit where Listing in(Value 1,Value 2....);
as : Give a data set(1,3,10,20),Get students id Student information in this data set
select * from student where id in (1,3,10,20);

6. Fuzzy query

Use the keyword like

%Meaning of: 0-n arbitrary characters

-Meaning: match any single character

example:

as : hold name in,Find out the surname Zhang
select * from student where  name like 'Zhang%';
as : hold name in,The name has two words
select * from student where  name like '__';

Note: if you want to query_ Or% need escape

7.order by

Sort the queried data according to one or more fields

example:

select Column qualification from Table limit order by Listing asc/desc;
Asc : Ascending order
Desc : Descending order
 as : Query all student information,In descending order of grades
select * from student order by score desc;
as : Query all student information,In descending order of grades,If the results are the same,according to id Ascending order
select * from  student order by score desc , id asc;

8.limit

Limit the number of queries

example:

grammar : 
select Column qualification from Table limit limit Number of articles;
select Column qualification from Table limit limit Start value(Subscript starts at 0) ,Number of articles;
as : Query student table,Information about the top three scores
select * from  student order by score desc limit 3;
as : Query student table,Second and third in scores
select * from  student order by score desc limit 1,2;

IV Single table query

1. Group function

Common group functions

Function name	meaning
count(*)	Total number
Max (field name)	Maximum
Min (field name)	minimum value
AVG (field name)	average value
Sum (field name)	the sum

2.group by

as : How many students did each teacher bring(Show teacher id that will do)
select teacher_id, count(*) as stu_count  from student group by teacher_id;
as : Query the highest score of the students brought by each teacher
select teacher_id, count(*) as stu_count,max(score) as stu_max_score from student group by teacher_id;
as : Query the total score and average score of each teacher's students
select teacher_id, sum(score) as sum,avg(score) as avg from student group by teacher_id;

3.Having

Function: filter the results after query. where is filtered during query

example:

Sometimes we need to make some judgments,For example, find the average,I just want an average score with an average of more than 60 points,At this time where Not anymore
select teacher_id, avg(score) as avg from student  where avg > 60  group by teacher_id;
It needs to be used at this time having Filter
select teacher_id, avg(score) as avg from student group by teacher_id having avg > 60;

V Subquery

Subquery is also called nested query. It can usually be located after SELECT, FROM and WHERE. There are three usage scenarios.

1. After select

select Field name,(Query statement) from Table name;
as  : Query the information of all students and display the teacher's name
select *,(
        		select name from teacher where id=teacher_id   
    		) as teacher_name from student ;
as : Query the maximum score of each teacher's students,Minimum score,Average score,Sum of scores,Number of students,Teacher name
select max(score),min(score),sum(score),avg(score),count(*),(
        select name from teacher where id=teacher_id 
        ) as teacher_name from student group by teacher_id ;

be careful:

When it is after SELECT, pay attention to

1. Be sure to find the corresponding relationship between the two tables (the teacher.id must be the primary key or the teacher.id must be unique in the teacher table)

2. There can only be one field in the sub query (the results of the sub query must be one row and one column)

When using sub queries, we recommend that you develop the good habit of using aliases, which can make our query statements clearer. Aliases can be used to command new fields or to name new tables

2.from back

Or student form student，We should grade the grades and display the grades of Chinese characters and letters. Subqueries can be used here. Equivalent to giving student"Two new fields have been added
 as : Grade grades using subqueries, score<60 ,grade C And it's bad,score>=60 And score<80 grade B And good,score>=80 The rating is A And it's excellent
select *,
case rank
when 'A' then 'excellent'
when 'B' then 'good'
when 'C' then 'difference'
end rank_ch
from (
select *,
case 
when score < 60 then 'C' 
when score >=60 and score <80 then 'B' 
when score >=80 then 'A' 
end as rank 
from student
) a;

be careful:

When it is behind FROM, pay attention to

1. We can treat the sub query as a table

2. There must be an alias, because the sub query is executed first. The alias of the sub query can allow other queries to operate as tables or columns

3. Behind where

as : I don't know teacher_id And the teacher's name,I want to find out all the student information under Mr. Zhang
select * from student where teacher_id in (
select id from teacher where name='Miss Zhang'
);

be careful:

1. For multiple pieces of data, use in instead of =. If you are sure that the result of the sub query is one row and one column, you can use = equal to

2. If the returned result is multiple rows and one column, in must be used. One column must be one column

3. There can only be one field after the SELECT in the subquery (an error will be reported if there are multiple fields)

Vi Union and union all

Union query, merge query results

union: it will de duplicate and remove duplicate data

union all: instead of de duplication, it combines the data found in the two tables

VII Common functions

Function name	effect
select version() ;	Displays the current MySQL software version
select database();	Displays which database you are currently in
select char_length('China ');	Returns the number of characters.
select length('China ');	Returns the number of bytes occupied by characters. In MySQL, a UTF8 encoded Chinese character occupies 3 bytes
select concat( 'a', 'b', 'c', 'd');	Return to 'abcd'. String splicing function
select concat_ws( '=', 'a', 'b', 'c');	Return 'a=b=c'. String splicing function, the first is the splicing spacer
select upper('abcd');	Return to ABCD. Converts all lowercase letters in the parameter to uppercase
select lower('ABCD');	Returns abcd. Converts all uppercase letters in the parameter to lowercase
select substring('system information class', 1, 3);	Return the system message. The second parameter represents the number of characters starting from 1, and the third parameter represents the number of intercepted characters
select trim(' abc ');	Return abc. Used to delete all spaces around the parameter
select curdate();	Returns the current date
select curtime();	Returns the current time
select now();	Returns the current date and time
select unix_timestamp();	Returns the timestamp (in seconds) corresponding to the current date and time
select unix_timestamp('2018-05-24 20:00:00');	Returns the timestamp (in seconds) corresponding to the date and time specified by the parameter
select from_unixtime(1527163397);	Returns the date and time corresponding to the time stamp (in seconds) specified by the parameter
select datediff( '2018-05-23', now() );	Returns the number of days between the corresponding dates of two parameters (the first parameter minus the second parameter)
select adddate( now(), -2 );	Returns the date and time before / after the specified number of days (the first parameter is the date and time, the second parameter is the number of days, plus backward is a positive number, and minus forward is a negative number)
select year('2019-02-24');	Return to 2019 to obtain the year
select month('2019-02-24')	Return to February for month
select day('2019-02-24')	Return to 24 days
Select if (< judgment condition >, < return value when condition is true >, < return value when condition is false >);	Equivalent to the ternary operator in Java < judgment condition >< Return value with true condition: < return value with false condition >.
For example, select if(1=1, 2, 3);	Return to 2.
Select ifnull (< expression or field >, < return value when expression or field is NULL >);	It is usually used to provide a default value for when there may be NULL.
select ifnull(null, 'anonymous');	Null column name can be written here, and the column with null value will be displayed as anonymous
f(1=1, 2, 3);	Return to 2.
Select ifnull (< expression or field >, < return value when expression or field is NULL >);	It is usually used to provide a default value for when there may be NULL.
select ifnull(null, 'anonymous');	Null column name can be written here, and the column with null value will be displayed as anonymous