Shulou(Shulou.com)06/01 Report--

1. Insert data INSERT

1. Insert complete data (sequential insert)

Grammar one:

INSERT INTO table name (field 1, field 2, field 3... Field n) VALUES (value 1, value 2, value 3... Value n)

Grammar 2:

INSERT INTO table name VALUES (value 1, value 2, value 3... Value n)

two。 Specify fields to insert data

Syntax:

INSERT INTO table name (field 1, field 2, field 3...) VALUES (value 1, value 2, value 3...)

3. Insert multiple records

Syntax:

INSERT INTO table name VALUES

Value 1, value 2, value 3... Value n)

Value 1, value 2, value 3... Value n)

Value 1, value 2, value 3... Value n)

4. Insert query results

Syntax:

INSERT INTO table name (field 1, field 2, field 3... Field n)

SELECT (Field 1, Field 2, Field 3... Field n) FROM Table 2

WHERE...

2. Update data UPDATE

Syntax:

UPDATE table name SET

Field 1 = value 1

Field 2 = value 2

WHERE CONDITION

Example:

UPDATE mysql.user SET password=password ('123') where user='root' and host='localhost'

3. Delete data DELETE

Syntax:

DELETE FROM table name

WHERE CONITION

Example:

DELETE FROM mysql.user

WHERE password=''

IV. Authority management

# create a user

Create user 'egon'@'1.1.1.1' identified by' 123'

Create user 'egon'@'192.168.1.%' identified by' 123'

Create user 'egon'@'%' identified by' 123'

# Authorization: permissions on folders, files, and certain fields of files

View help: help grant

Common permissions are: select,update,alter,delete

All can represent all permissions except grant

# Authorization for all libraries: *. * grant select on *. * to 'li'@'localhost' identified by' 123permissions; # it can only be found in the user table that the select permission of the li user is set to Y# for a database: db1.*grant select on db1.* to 'wang'@'%' identified by' 123' # the select permission of the wang user can only be found in the db table is set to Y # for a certain table: db1.t1grant select on db1.t1 to 'tom'@'%' identified by' 123permissions; # the select permission of the tom user can only be found in the tables_ private table # for a certain field: mysql > select * from T3 +-+ | id | name | age | +-+ | 1 | egon1 | 18 | 2 | egon2 | 19 | 3 | egon3 | 29 | +-+ grant select (id,name), update (age) on db1.t3 to 'egon4'@'localhost' identified by' 123' # you can see the corresponding permissions mysql > select * from tables_priv where user='egon4'\ Gmysql > select * from columns_priv where user='egon4'\ G# Delete permissions revoke select on db1.* to 'alex'@'%' in tables_priv and columns_priv

5. Mysql single table query

1. Syntax of single table query

SELECT field 1, field 2... FROM table name

WHERE condition

GROUP BY field

HAVING screening

ORDER BY field

Limit the number of LIMIT entries

2. Execution priority of keywords

From # find the table

Where # take the constraints specified by where and go to the file / table to retrieve records one by one

Group by # groups the retrieved records into group by. If there is no group by, then the group is taken as a whole.

Having # filter the result of the grouping by having

Select # execute select

Distinct # weight removal

Order by # sorts the results by condition

Limit # limits the number of results displayed

3. Simple query

# create table create database company;use company;create table employee (id int not null unique auto_increment,name varchar (20) not null,sex enum ('male','female') not null default' male',age int (3) unsigned not null default 28 hiring date date not null,post varchar (50), post_comment varchar (100), salary double (15) 2), office int,depart_id int) # insert records into three departments: teaching, sales Operate insert into employee (name,sex,age,hire_date,post,salary,office,depart_id) values ('wang','male',18,'20170301','teacher',7300.33,401,1), (' li','male',78,'20150302','teacher',1000000.31,401,1), ('jim','male',81,'20130305','teacher',8300,401,1), (' zhao','male',73,'20140701','teacher',3500,401,1), ('liwenzhou','male',28) ('jingliyang','female',18,'20110211','teacher',9000,401,1), (' jinxin','male',18,'19000301','teacher',30000,401,1), ('Jackie Chan', 'male',48,'20101111','teacher',10000,401,1), (' crooked', 'female',48,'20150311','sale',3000.13,402,2), (' Yaya', 'female',38,'20101101','sale',2000.35,402,2) ('Dingding', 'female',18,'20110312','sale',1000.37,402,2), (' Xing', 'female',18,'20160513','sale',3000.29,402,2), (' GE', 'female',28,'20170127','sale',4000.33,402,2), (' Zhang Ye', 'male',28,'20160311','operation',10000.13,403,3), (' Cheng Yaojin', 'male',18,'19970312','operation',20000,403,3), (' Cheng Jiaoyin' '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) +-- + | id | name | sex | age | Hire_date | post | post_comment | salary | office | depart_id | +- -+ | 1 | wang | male | 18 | 2017-03-01 | teacher | NULL | 7300.33 | 401 | 1 | 2 | li | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 1 | 3 | jim | male | 81 | 2013-03-05 | teacher | NULL | 8300 .00 | 401 | 1 | | 4 | zhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | | 1 | 1 | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | 8 | Jackie Chan | male | 48 | 2010-11 | teacher | NULL | 10000.00 | 401 | 1 | | 9 | askew | female | 48 | 2015-03-11 | sale | NULL | 3000 .13 | 402 | 2 | | 10 | Yaya | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | 11 | Tintin | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | 12 | Star | female | 18 | 2016-05-13 | sale | NULL | | | 3000.29 | 402 | 2 | 13 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | 14 | Zhangye | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | 15 | Cheng Yaojin | male | 18 | 1997-03-12 | Operation | NULL | 20000.00 | 20000.00 | 3 | 16 | Cheng Jiaoyin | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | 17 | Chengyao Copper | male | 18 | 2015-04-11 | operation | 18000.00 | 403 | 3 | 18 | Cheng Yaotie | female | 18 | 2014-05-12 | | operation | NULL | 17000.00 | 403 | 3 | + -+ # simple query SELECT id Name, sex, age, hire_date, post, post_comment, salary, office, depart_id FROM employee SELECT * FROM employee;SELECT name, salary FROM employee;# avoid repeating DISTINCTSELECT DISTINCT post FROM employee;+-+ | post | +-+ | teacher | | sale | | operation | +-+ # query SELECT name through four operations: salary * 12 FROM employee;SELECT name, salary * 12 AS Annual_salary FROM employee;SELECT name, salary * 12 Annual_salary FROM employee +-+-+ | name | Annual_salary | +-+-+ | wang | 87603.96 | | li | 12000003.72 | jim | 99600.00 | | zhao | 42000.00 | liwenzhou | 25200.00 | Jingliyang | 108000.00 | jinxin | 360000.00 | | Jackie Chan | 120000.00 | | askew | 36001.56 | Yaya | 24004.20 | | Tintin | 12004.44 | | Star | 36003.48 | | Lattice | 48003.96 | Zhangye | 120001.56 | Cheng Yaojin | 240000.00 | | Cheng Jiaoyin | 228000.00 | | Cheng YaoTong | 216000.00 | | Cheng Yaotie | 204000.00 | +-+-+ # define the display format CONCAT () function is used to connect the string SELECT CONCAT ('name:' Name, 'Annual salary:', salary * 12) AS Annual_salary FROM employee The first parameter of CONCAT_WS () is the delimiter SELECT CONCAT_WS (':', name, salary * 12) AS Annual_salary FROM employee +-- + | Annual_salary | +-+ | wang:87603.96 | | li:12000003.72 | | jim:99600.00 | | zhao:42000.00 | | liwenzhou:25200.00 | | jingliyang:108000.00 | | jinxin:360000.00 | | Jackie Chan: 120000.00 | | crooked: 36001.56 | | Ya: 24004.20 | | Dingding: 12004.44 | | Star: 36003.48 | Lattice: 48003.96 | | Zhangye: 120001.56 | Cheng Yaojin: 240000.00 | | Cheng Yaoyin: 228000.00 | | Chengyao Copper: 216000.00 | | Cheng Yaotie: 204000.00 | +-+ exercise:

(1) find out the names and salaries of all employees in the format of

(2) check out all posts (remove repetition)

(3) find out the names of all employees, as well as their annual salary, the field of annual salary is named annual_year

Select concat (',') from employee;select distinct depart_id from employee;select name,salary*12 annual_salary from employee

4. WHERE constraint

# 1: single-conditional query SELECT name FROM employee WHERE post = 'sale';# 2: multi-conditional query SELECT name, salary FROM employee WHERE post =' teacher' AND salary > 10000 AND # 3: keyword BETWEEN ANDSELECT name, salary FROM employee WHERE salary BETWEEN 10000 AND 200000 AND select name, salary FROM employee WHERE salary NOT BETWEEN 10000 AND 20000 political # 4: keyword IS NULL (to determine whether a field is NULL and cannot use the equal sign, you need to use IS) SELECT name, post_comment FROM employee WHERE post_comment IS NULL SELECT name, post_comment FROM employee WHERE post_comment IS NOT NULL;SELECT name, post_comment FROM employee WHERE post_comment =''; # Note''is an empty string, not nullps: execute update employee set post_comment =''where id = 2; then check with the previous entry, you will get the result # 5: keyword IN collection query SELECT name, salary FROM employee WHERE salary = 3000 OR salary = 3500 OR salary = 4000 OR salary = 9000 SELECT name, salary FROM employee WHERE salary IN (3000, 3500, 4000, 9000); SELECT name, salary FROM employee WHERE salary NOT IN (3000, 3500, 4000, 9000); # 6: keyword LIKE fuzzy query wildcard'% 'SELECT * FROM employee WHERE name LIKE' eg%'; wildcard'_ 'SELECT * FROM employee WHERE name LIKE' al__'

Exercise:

(1) check the name and age of the employee whose position is teacher

(2) check the name and age of employees whose position is teacher and are older than 30 years old.

(3) check the name, age and salary of employees whose position is teacher and whose salary is in the range of 9000-1000

(4) check the information of employees whose job description is not NULL.

(5) check the name, age and salary of employees whose position is teacher and whose salary is 10000 or 9000 or 30000.

(6) check the name, age and salary of employees whose position is teacher and whose salary is not 10000 or 9000 or 30000.

(7) check the name and annual salary of employees whose position is teacher and whose name begins with jin.

Select name,age from employee where post='teacher'; select name,age from employee where post='teacher' and age > 30: select name,age,salary from employee where post='teacher' and salary between 9000 and 10000: select * from employee where post_comment is not null;select name,age,salary from employee where post='teacher' and salary in (10000 and 9000herald 30000); select name,age,salary from employee where post='teacher' and salary not in (10000meme 9000meme 30000); select name,salary*12 from employee where post='teacher' and name like 'jin%' Mysql > select name,salary*12 as year_salary from employee where post='teacher' and name like 'jin%'

5. Group query: GROUP BY

(1) GROUP BY

Grouping with GROUP BY keyword separately

SELECT post FROM employee GROUP BY post

Note: if we group according to the post field, then the field queried by select can only be post. To obtain other relevant information in the group, you need the help of a function.

The GROUP BY keyword is used with the GROUP_CONCAT () function

SELECT post,GROUP_CONCAT (name) FROM employee GROUP BY post; # group by post and view the names of members in the group

SELECT post,GROUP_CONCAT (name) as emp_members FROM employee GROUP BY post

GROUP BY is used with aggregate functions

Select post,count (id) as count from employee group by post; # groups by post and check how many people there are in each group

If we use the unique field as the basis for grouping, then each record is grouped into its own group, and this grouping is meaningless.

A field between multiple records has the same value, which is usually used as a basis for grouping

(2) aggregate function

# emphasize: the aggregate function aggregates the contents of the group. If there is no grouping, it defaults to a group.

Example:

SELECT COUNT (*) FROM employee

SELECT COUNT (*) FROM employee WHERE depart_id=1

SELECT MAX (salary) FROM employee

SELECT MIN (salary) FROM employee

SELECT AVG (salary) FROM employee

SELECT SUM (salary) FROM employee

SELECT SUM (salary) FROM employee WHERE depart_id=3

Exercise:

(1) query the job name and the names of all employees included in the post.

(2) inquire about the job name and the number of employees included in each post.

(3) inquire about the number of male and female employees in the company

(4) inquire about the job name and the average salary of each post

(5) inquire about the job name and the maximum salary of each post.

(6) inquire about the job name and the minimum salary of each post.

(7) inquire about the average salary of male employees and male employees, and the average salaries of female employees and female employees.

Mysql > select post,group_concat (name) from employee group by post;mysql > select post,count (id) from employee group by post;mysql > select sex,count (id) from employee group by sex;mysql > select post,avg (salary) from employee group by post;mysql > select post,max (salary) from employee group by post;mysql > select post,min (salary) from employee group by post;mysql > select sex,avg (salary) from employee group by sex

6. HAVING filtering

What makes HAVING different from WHERE is!

#! Execution priority from high to low: where > group by > having

# 1. Where occurs before grouping group by, so there can be any fields in Where, but aggregate functions must not be used.

# 2. Having occurs after grouping group by, so grouped fields can be used in Having, and other fields cannot be fetched directly. Aggregate functions can be used.

Exercise:

1. Inquire the name of the post with less than 2 employees in each post, and the name and number of employees in the post.

two。 Inquire about the position name and average salary of each post whose average salary is more than 10000.

3. Inquire about the job name and average salary of each post whose average salary is more than 10000 and less than 20000.

Mysql > select post,group_concat (name), count (id) from employee group by post having count (id)

Select post,avg (salary) from employee group by post having avg (salary) > 10000 salary MySQL > select post,avg (salary) from employee group by post having avg (salary) > 10000 and avg (salary) select * from employee ORDER BY age asc,hire_date desc;mysql > select post,avg (salary) from employee group by post having avg (salary) > 10000 order by avg (salary) asc;mysql > select post,avg (salary) from employee group by post having avg (salary) > 10000 order by avg (salary) desc

8. Limit the number of records queried: LIMIT

SELECT * FROM employee ORDER BY salary DESC LIMIT 3; # default initial location is 0SELECT * FROM employee ORDER BY salary DESC LIMIT 0,5; # starting from 0, that is, first query the first item, then including this one, check back 5 SELECT * FROM employee ORDER BY salary DESC LIMIT 5,5; # starting from 5, check out section 6 first, and then include this section to check back 5 articles

Exercise: display in pages with 5 entries per page

Mysql > select * from employee limit 0re5; # shows 1st to 5th records mysql > select * from employee limit 5 5th; # shows 5th to 10th records mysql > select * from employee limit 10L5; # shows 10th to 15th records

9. Use regular expressions to query

SELECT * FROM employee WHERE name REGEXP'^ ale';SELECT * FROM employee WHERE name REGEXP 'on$';SELECT * FROM employee WHERE name REGEXP'm {2}'

Summary: ways to match strings

WHERE name = 'li'

WHERE name LIKE 'yua%'

WHERE name REGEXP 'on$'

Exercise:

View all employee information whose names begin with jin, n or g results

Select * from employee where name regexp'^ jin.* [gn] $'

6. Mysql multi-table query

1. Create a table

Create table department (id int,name varchar (20)); create table employee (id int primary key auto_increment,name varchar (20), sex enum ('male','female') not null default' male',age int,dep_id int); # insert data insert into department values (200Jing 'Technology'), (201Jing 'Human Resources'), (202Jing 'Sale'), (203Jing 'Operation') Insert into employee (name,sex,age,dep_id) values ('egon','male',18200), (' alex','female',48201), ('wupeiqi','male',38201), (' yuanhao','female',28202), ('liwenzhou','male',18200), (' jingliyang','female',18204)

2. Multi-table join query

(1) Cross-connect: no matching conditions apply. Generate Cartesian product

Mysql > select * from department;+-+-+ | id | name | +-+-+ | 200 | Technology | | 201 | Human Resources | | 202 | sales | | 203 | Operation | +-+-+ mysql > select * from employee +-+ | id | name | sex | age | dep_id | +-+ | 1 | egon | male | 18 | 200 | | 2 | | alex | female | 48 | 201 | 3 | wupeiqi | male | 38 | 201 | 4 | yuanhao | female | 28 | 202 | 5 | liwenzhou | male | 18 | 200 | 6 | jingliyang | female | 18 | 204 | +-+ mysql > select * from employee | Department

(2) Internal join: only match lines are connected

# finding the common parts of the two tables is equivalent to using the conditions to filter out the correct results from the Cartesian product results

# department does not have a 204department, so the employee information about 204is not matched in the employee table

Mysql > select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id +-+ | id | name | age | sex | name | +-+ | 1 | egon | 18 | male | Technology | | 2 | alex | 48 | female | Human Resources | | 3 | wupeiqi | 38 | male | Human Resources | | 4 | yuanhao | 28 | female | sales | | 5 | liwenzhou | 18 | male | Technology | + -+ mysql > select employee.id Employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id

(3) the left link of the external link: give priority to display all records in the left table.

# to the left table, that is, to find out all employee information, including, of course, employees without departments

# the essence is: on the basis of internal connection, add the result that there is no right side on the left side.

Mysql > select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id +-+ | id | name | depart_name | +-- + | 1 | egon | Technology | | 5 | liwenzhou | Technology | | 2 | alex | Human Resources | | 3 | wupeiqi | | Human Resources | | 4 | yuanhao | sales | | 6 | jingliyang | NULL | +-- + |

(4) the right link of the external link: give priority to displaying all the records in the right table.

# based on the table on the right, that is, to find out all department information, including departments without employees

# the essence is: on the basis of the inner connection, adding the right side has the result that the left side does not.

Mysql > select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id +-+ | id | name | depart_name | +-+ | 1 | egon | Technology | | 2 | alex | Human Resources | | 3 | wupeiqi | Human Resources | | 4 | yuanhao | sales | | 5 | liwenzhou | Technology | | NULL | NULL | Operation | +-+ |

(5) full external connection: display all records of the left and right tables

Full external connection: on the basis of the inner connection, add the result that the left has no right and the right has no left.

# Note: mysql does not support full external connection to full JOIN

# emphasize: mysql can use this way to indirectly achieve full external connection

Select * from employee left join department on employee.dep_id = department.idunionselect * from employee right join department on employee.dep_id = department.id +-+ | id | name | sex | age | dep_id | id | name | +- -- + | 1 | egon | male | 18 | 200 | 200 | Technology | 5 | liwenzhou | male | 18 | 200 | Technology | 2 | alex | female | 48 | 201 | Human Resources | | 3 | wupeiqi | | male | 38 | 201,201 | Human Resources | | 4 | yuanhao | female | 28 | 202 | sales | | 6 | jingliyang | female | 18 | 204 | NULL | NULL | | NULL | 203 | Operation | +-- | -- + # Note the difference between union and union all: union removes the same record

3. Join query in accordance with the conditions

# example 1: query the employee and department tables within the join mode, and the age field value in the employee table must be greater than 25, that is, to find out the employees older than 25 years old and the department where the employee belongs, select employee.name,department.name from employee inner join department on employee.dep_id = department.id where age > 25. +-+-+ | name | name | +-+-+ | alex | Human Resources | | wupeiqi | Human Resources | | yuanhao | sales | +-+-# example 2: query employee and department tables within connections And display select employee.id,employee.name,employee.age,department.name from employee,department where employee.dep_id = department.id and age > 25 order by age asc in ascending order of the age field +-+ | id | name | age | name | +-- + | 4 | yuanhao | 28 | sales | | 3 | wupeiqi | 38 | Human Resources | | 2 | alex | | 48 | Human Resources | +-+ |

4. Subquery

# 1: a subquery is to nest one query statement in another query statement.

# 2: the query result of the inner query statement can provide query conditions for the outer query statement.

# 3: subqueries can contain keywords such as IN, NOT IN, ANY, ALL, EXISTS and NOT EXISTS

# 4: you can also include comparison operators: =,! =, >, 25); # View the name of the technical staff select name from employee where dep_id in (select id from department where name=' Technology'); # View the department name of less than 1 person (the subquery gets someone's department id) select name from department where id not in (select distinct dep_id from employee)

(2) subquery with comparison operator

# comparison operators: =,! =, >, > =, (select avg (age) from emp); # query the name and age of employees who are older than the average age in the department, select t1.namememery (select dep_id,avg (age) avg_age from emp group by dep_id) T2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age

(3) subquery with EXISTS keyword

The EXISTS key word indicates existence. When the EXISTS keyword is used, the inner query statement does not return the record of the query.

Instead, it returns a true or false value. True or False

When True is returned, the outer query statement will query; when the return value is False, the outer query statement will not query

# dept_id=203,Turemysql > select * from employee where exists (select id from department where id=200) exists in # department table; dept_id=205,Falsemysql > select * from employee where exists (select id from department where id=204) exists in # department table

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.