MySQL common sentences and practical training

create database database-name

• 1

Delete database:

drop database database-name

• 1

Modify data name:

RENAME DATABASE db_name TO new_db_name

• 1

Create new table:

create table table-name(
col1-name type1 [not null] [primary key] [auto_increment],
col2-name type2 ,
……
)

• 1
• 2
• 3
• 4
• 5

auto_increment ->Automatic growth, only for numerical type.

Create new table based on old table:

create table table_name like table_old_name

• 1

create table table_name as select col1,col2... from table_old_name;

• 1

Delete the table:

drop table table_name;

• 1

Amend table name:

alter table table_name rename table_new_name;

• 1

Add one column:

alter table table_name add column col type [not null] ...;

• 1

Delete a column:

alter table table_name drop column col_name;

• 1

Modify column name:

alter table table_name change column col_old_name col_new_name type [not null]...; 

• 1

Modify attributes of columns:

alter table table_name change column col_old_name col_old_name type [not null]...; 

• 1

Choice:

select * from table_name where ...

• 1

Insert:

insert into table_name(col1_name,col2_name...) values(...);

• 1

Copy all data:

insert into table1_name(table1_name.col1_name,table1_name.col2_name...) select table2_name.col1_name,
table2_name.col_name...
from table2_name

• 1
• 2
• 3

Copy of tables across databases (using absolute paths for specific data) (Access available)

insert into b(a, b, c) select d,e,f from b in ‘Concrete databasewhere condition

• 1

Delete:

delect from table_name where

• 1

To update:

update table_name set col1_nameee=value ...where ...

• 1

Find:

select * from table_name where ...

• 1

Sort:

select * from table_name order by col1_name asc/desc;

• 1

asc Ascending order
desc Descending order

Total:

select count(*|col_name) as count_name from table_name where ...

• 1

Summing up:

select sum(col_name) as sum_name from table_name where ...

• 1

Seek the mean value:

select avg(col_name) as avg_name from table_name where ...

• 1

Maximum:

select max(col_name) as max_name from table_name where ...

• 1

Minimum:

select min(col_name) as min_name from table_name where ...

• 1

unionAnd union all
UNION A result set that combines two or more SELECT statements and eliminates any duplicate rows in the table.  
UNION The internal SELECT statement must have the same number of columns, and the column must have similar data types.  
At the same time, the order of columns in each SELECT statement must be the same.

**Note: In addition, the column name in the UNION result set is always equal to the column name in the first SELECT statement in UNION.  
Note: 1. The column names in the UNION result set are always equal to the column names in the first SELECT statement.
2、UNION The internal SELECT statement must have the same number of columns. Columns must also have similar data types. At the same time, the order of columns in each SELECT statement must be the same.
** 
unionUsage and points for attention
union:Union means to combine the results of two or multiple queries.  
Requirement: the number of columns for two queries must be consistent.
Recommendation: the type of column can be different, but each column of the query is recommended, and the corresponding type is the same.
Data that can come from multiple tables: The column names taken out by multiple SQL statements can be inconsistent, whichever is the first SQL statement.  
If the rows taken out of different statements are exactly the same (in this case, the values for each column are the same), then union merges the same rows, leaving only one row. It can also be understood that union will remove duplicate rows.  
If you do not want to remove duplicate rows, you can use union all.  
If there are order by and limit in clauses, we need to wrap them in brackets. Recommendation is placed after all clauses, that is, to sort or filter the final merged results.

SELECT column_name FROM table1
UNION
SELECT column_name FROM table2

• 1
• 2
• 3

JoinSyntax overview
JOIN According to their functions, they can be roughly divided into three categories:

INNER JOIN（Internal join or equivalents): records that have connection matching relations in the two tables are obtained.

select * from table1_name inner join table2_name on teable1_name.col = teable2_name.col where ...

• 1

select * from table1_name inner join table2_name on teable1_name.col = teable2_name.col where table1_name.col is null or table2_name.col is null and...

• 1

LEFT JOIN（Left join: Gets the full record of the left table (table 1), that is, the right table (table 2) has no corresponding matching record.

select * from table1_name left join table2_name on teable1_name.col = teable2_name.col where ...

• 1

select * from table1_name inner join table2_name on teable1_name.col = teable2_name.col where teable2_name.col is null and ...

• 1

RIGHT JOIN（Right join: Contrary to LEFT JOIN, complete records of the right table (table 2) are obtained, i.e. there is no matching record of the left table (table 1).

Full join:

select * from A left join B on B.name = A.name 
union 
select * from A right join B on B.name = A.name;

• 1
• 2
• 3

Grouping:

select *|count,avg,sum,max,min from table_name group by table_name.col_name

• 1

Subquery:

#in
select * from table1_name where tabel1_name.col1 in (
select table2_name.col1 from teable2_name where... 
)
#not in
select * from table1_name where tabel1_name.col1 not in (
select table2_name.col1 from teable2_name where... 
)

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8

between Numerical value 1 and 2

select * from table_name where table_name.col between numerical value1 and numerical value2

• 1

not between Numerical value 1 and 2

select * from table_name where table_name.col between numerical value1 and numerical value2

• 1

The two connection table deletes the information that is not found in the secondary table in the main table:

delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )

• 1

limit Return to the first or middle row of data.

SELECT * FROM table  LIMIT [offset,] rows | rows OFFSET offset

• 1

havingWords and sentences allow us to filter the data after a group, where sentences filter records before aggregation, that is, before groups by and having sentences. The having clause is filtered after group aggregation.  
Example:

SELECT region, SUM(population), SUM(area)
FROM bbc
GROUP BY region
HAVING SUM(area)>1000000

• 1
• 2
• 3
• 4

Go back to weight:
Sometimes you need to query out records that are not duplicated in a field, and you can use the distinct keyword provided by Mysql to filter duplicate records.

select distinct col_name from table_name;

• 1

Title online test address: https://www.nowcoder.com/ta/sql

 

Find all the information of the latest entry staff.
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

nothing

output Description: