Updates to MySQL database data

There are tables, a, B, Name,sal,deptno
1, Data Update
Update
Update A set sal=500 where name= ' Zhangsan ';(can change multiple values at once, separated by commas)
2, deletion of data
Delete
Delete from A where name= ' Zhangsan ';(delete a row of data)
Delete from A where a_age between C and D; (delete all data between C and D)
3, increase

Insert

INSERT into A values (ID int, name varchar (), Sal Deciml,deptno int);

Insert into B (name,sal) VALUES (name varchar (), Sal Deciml);

4, query (DQL)
A,select {*| column name, ...} from A; (* Search all data in a table, column name query a column in table a)
b, set column name alias: Select A.name as name, a.sal as salary from a as A; (as can not be added)
c, do the arithmetic: select a.sal+5000 as wages from a A;
D, splicing: Select Concat (a.name, ' & ', a.sal) as salary table from a A;
E, limit the number of rows displayed: SELECT * from A limit 0,3; (the two digits followed by the limit, which indicates which line to start from, which indicates how many rows are returned)
F, de-weight: SELECT distinct Sal from A; (excluding duplicate data)
Use of the G,where clause in the query:
SELECT * from A where Sal in (5100,3000);(query sal for 5100 and 30,002 person information)
SELECT * from A where Sal isn't in (4000);(query except for everyone except Sal 4000)
SELECT * from a WHERE (deptno,sal) in (select Deptno,max (SAL) from a GROUP by DeptNo);(query the highest paid person per department)
H, Fuzzy query: SELECT * from A where name like ' ma% ';
SELECT * from A where name like ' ma% ';(query for information of people with names of two characters)
Wildcard: '% ' stands for 0 or more arbitrary characters
' _ ' represents a character
I, sort query: SELECT * from A order by name; (Ascending)
SELECT * from A order BY name Desc; (Descending)
5, aggregate function
A,count function Select COUNT (*) from A; (Number of all columns in table a)
Select count (column name) from A; (number of rows in this column)
Select COUNT (distinct column name) from A; (number of columns to be re-weighed)
b,sum function Select SUM (< count specification >) from A;
Max,avg,min is the same as the sum usage
6, Data grouping
Select column A, aggregate function from table name where condition group By column A having filter condition;
7, sub-query---even table query
Classification-dependent subqueries: Executing data that relies on external queries; Non-correlated subqueries
Select is nested in select A.name,a.sal, (select b.name from b b where a.deptno=b.deptno) from a A;
Where in the nested select * from a WHERE sal> (select Sal from a where name= ' Zhangsan ');
8, combination query--Union is a combination of two or more than two query statements, similar to the effect of multiple query results
Structure: Select Column Name 1 from A
UNION
Select Column Name 2 from B
Table A, table B must be the same number of columns, and after the query is complete, table B column Name 2 is ignored
9, mating
A, table join INNER JOIN (inner join) inner can be omitted, the efficiency is very low
Outer join (left out join,right join) where out can be omitted
Select A.name,b.name from A-a LEFT join B-B on Join condition (foreign key condition);
B, a table joined by a self-join from the same table
Select A.name,i.name from A-a left join a I-on condition;
order of execution of 10,sql statements
SELECT clause
FROM clause
WHERE clause
GROUP BY clause
ORDER BY clause
HAVING clause
11, a numeric value that is added with NULL to the resulting value is also null

Updates to MySQL database data