SQL statement supplement for operation table
1.Modify Table Name alter table Table Name remane New table name; 2.New Field alter table Table Name add Field name field type(number) constraint condition; alter table Table Name add Field name field type(number) constraint condition after Fields that already exist; alter table Table Name add Field name field type(number) constraint condition first; 3.Modify Field alter table Table Name change Old Field New Field Field Type(number) constraint condition; alter table Table Name modify Field Name New Field Type(number) constraint condition; 4.Delete Field alter table Table Name drop Field name;
Query table keywords
1.Data preparation(direct copy)
create table emp(
id int not null unique auto_increment,
name varchar(20) not null,
gender enum('male','female') not null default 'male', #Mostly male
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #One department, one house
depart_id int
);
#Three departments: teaching, sales, operation
insert into emp(name,gender,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','The Image of Pudong First Leader',7300.33,401,1), #Below is the Department of Education
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('Ha-ha','female',48,'20150311','sale',3000.13,402,2),#The following are the sales departments
('Ha-ha','female',38,'20101101','sale',2000.35,402,2),
('West','female',18,'20110312','sale',1000.37,402,2),
('Lele','female',18,'20160513','sale',3000.29,402,2),
('Lala','female',28,'20170127','sale',4000.33,402,2),
('Sanglong','male',28,'20160311','operation',10000.13,403,3), #Here is the Operations Department
('Cheng Bite Gold','male',18,'19970312','operation',20000,403,3),
('Cheng Bite Silver','female',18,'20130311','operation',19000,403,3),
('Cheng Bite Copper','male',18,'20150411','operation',18000,403,3),
('Cheng Bite Iron','female',18,'20140512','operation',17000,403,3);
table data
select and from of query keywords
''' SQL The order in which keywords are written and executed for a statement is inconsistent!!! eg: select name from emp; Definitely support first from Execute after table is determined select Determine Fields To write SQL Statement for select and from You can write a fixed template first select * from Table name other operations select The latter field may be actual or may be passed through SQL Dynamically generated so can be used first*Place last to modify ''' 1.select Speech corresponding to field in custom query table 2.from Object for specified operation(Which table is it from, maybe more than one)
where filtering for query keywords
When you are new to mysql queries, it is recommended that you spell out your sql statements in the order of query priority
1. First check which table is from emp
2. Then, under what conditions do we look up where id = 6
3. Then select name,salary for the data filtering display part of the query
1. Query data with id greater than or equal to 3 and less than or equal to 6 (two ways)
select id,name from emp where id >= 3 and id <= 6; select id,name from emp where id between 3 and 6;
2. Query for data from 2000 or 18,000 or 17,000 (both)
select * from emp where salary = 20000 or salary = 18000 or salary = 17000; select * from emp where salary in (20000,18000,17000);
3. Query the employee's name and salary with the o letter in the employee's name
select name,salary from emp where name like '%o%';
4. Query an employee's name is a four-character employee's name and salary (in two ways)
select name,salary from emp where name like '____'; select name,salary from emp where char_length(name) = 4;
5. Query data with id less than 3 or greater than 6
select * from emp where id not between 3 and 6;
6. Query data for salaries not in the 20000, 18000, 17000 range
select * from emp where salary not in (20000,18000,17000);
7. Employee name and job name with empty job description can not be equal sign for null, only is can be used.
select name,post from emp where post_comment = NULL; # Query is empty select name,post from emp where post_comment is NULL; select name,post from emp where post_comment is not NULL;
group by grouping of query keywords
Grouping: dividing a single data into a whole according to some specified criteria
After grouping, we should study the object that should be grouped as a unit and should no longer get individual data items directly. If we get field names that can be filled out directly after a select ion that should be directly mistaken, they can only be grouped on (other fields need some methods to get them)
set global sql_mode='strict_trans_tables,only_full_group_by';
Whether we need to use grouping for writing SQL can be answered in the title
_Each, Average, Maximum, Minimum
Configure aggregate functions commonly used by grouping
| Sequence Number | Keyword | Effect |
|---|---|---|
| 1. | max | Maximum |
| 2. | min | minimum value |
| 3. | sum | The sum |
| 4. | counnt | count |
| 5. | avg | average value |
1. Maximum salary per department
select post,max(salary) from emp group by post;
2. Minimum salary per department
select post,min(salary) from emp group by post;
3. Average salary per department
select post,avg(salary) from emp group by post;
4. Total wages per department
select post,sum(salary) from emp group by post;
5. Number of people in each department
select post,count(salary) from emp group by post;
6. You can also alias fields when displaying them
as can also be omitted but not recommended because the meaning is ambiguous
mysql> select post as 'department',max(salary) as 'Maximum Salary' from emp group by post; mysql> select post 'department',max(salary) 'Maximum Salary' from emp group by post;
Group_ Conca (used after grouping) can be used not only to show fields other than grouping but also to stitch strings
1. Everyone's name
mysql> select post,group_concat(name) from emp group by post;
2. Add suffixes
mysql> select post,group_concat(name,'_NB') from emp group by post;
3. Stitching Strings
select post,group_concat(name,'Pay is: ',salary) from emp group by post;
4. Pay per person
mysql> select post,group_concat(salary) from emp group by post;
having filtering for query keywords
where and having actually do the same thing to filter data
Only where is used for pre-grouping filtering and where is used for post-grouping filtering
where is Filtering Hasing is Filtering for Artificial Distinguishing
1. Statistics the average wages of employees over 30 years of age in various departments, and keep departments with average wages over 10,000
select post,avg(salary) from emp where age > 30 group by post having avg(salary) > 10000;
distinct de-duplication of query keywords
The premise of de-duplication is that the data must be identical
select distinct age from emp;
order by sorting of query keywords
1.Ascending order(Default) select * from emp order by salary asc;
2.Descending order select * from emp order by salary desc;
Case: Statistics the average wages of employees over 10 years of age in various departments, and retain departments with average wages greater than 1000, then rank the average wages
select post,avg(salary) from emp where age > 10 group by post having avg(salary) > 1000 order by avg(salary);
limit Paging of Query Keyword
1. Limit the number of items displayed
select * from emp limit 3;
2. Paging Display
The first parameter represents the starting position, and the second parameter represents the number of bars, not the index position
select * from emp limit 5,5;
regexp rule for query keywords
select * from emp where name regexp'^j.*(ny)$';
Multi-table query ideas
1.Subquery
Bracket one table query result as another SQL Conditions for statements
eg:
What's the first step
The second step is based on the results of the first step
2.Joint Table Query
Now stitch together all the tables that involve the results to form a large table and query the data from the large table
# Building tables
create table dep1(
id int primary key auto_increment,
name varchar(20)
);
create table emp1(
id int primary key auto_increment,
name varchar(20),
gender enum('male','female') not null default 'male',
age int,
dep1_id int
);
# insert data
insert into dep1 values
(200,'technology'),
(201,'Human resources'),
(202,'Sale'),
(203,'Operate'),
(205,'Security')
;
insert into emp1(name,gender,age,dep1_id) values
('jason','male',18,200),
('lisa','female',48,201),
('kevin','male',18,201),
('oscar','male',28,202),
('tony','male',18,203),
('jerry','female',18,204);
Subquery
Query jason's department name
1.Get First jason Department number select dep1_id from emp1 where name = 'jason'; # 200 2.Get department name from department number select name from dep1 where id = 200; 3.Subquery select name from dep1 where id = (select dep1_id from emp1 where name = 'jason');
Join Table Operation
1.Cartesian product select * from emp1,dep1; ' We don't use Cartesian products for inefficient data. Joining tables has a special syntax '
1.inner join inner connection
Effect:Only fields that have both sides can be stitched together select * from emp1 inner join dep1 on emp1.dep1_id = dep1.id;
2.left join left join
Effect:Show all data on the left table without corresponding data NULL Fill select * from emp1 left join dep1 on emp1.dep1_id = dep1.id;
3.right join right connection
Effect:Show all data against right benchmark without corresponding data then apply NULL Fill select * from emp1 right join dep1 on emp1.dep1_id = dep1.id;
4.union Full Connection
Effect:Left join plus right join Both sides of the data have no data to use NULL Fill select * from emp1 left join dep1 on emp1.dep1_id = dep1.id union select *from emp1 right join dep1 on emp1.dep1_id = dep1.id;
This is all the content of this article. Thank you for reading, and I hope you can help. Visit more tutorials Yanong Home