Database 03: Table Logging Operations and queries

Overview

In the MySQL management software, you can use the language in the SQL statement DML to implement the operation of the data, including:

1. INSERTimplementation of data insertion;
2. UPDATEimplementation of data updates;
3. DELETEimplementation of data deletion;
4. SELECTQuery data.

INSERT

# 插入完整数据（顺序插入）    语法一：    INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);        语法二：    INSERT INTO 表名 VALUES (值1,值2,值3…值n);# 指定字段插入数据    语法：    INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);# 插入多条记录    语法：    INSERT INTO 表名 VALUES        (值1,值2,值3…值n),        (值1,值2,值3…值n),        (值1,值2,值3…值n);        # 插入查询结果    语法：    INSERT INTO 表名(字段1,字段2,字段3…字段n)                     SELECT (字段1,字段2,字段3…字段n) FROM 表2                    WHERE …;

UPDATE

UPDATE 表名 SET    字段1=值1,    字段2=值2 WHERE 条件;# 示例：    UPDATE mysql.user SET password=password(‘123’)         where user=’root’ and host=’localhost’;

DELETE

语法：    DELETE FROM 表名         WHERE CONITION;示例：    DELETE FROM mysql.user         WHERE password=’’;练习：    更新MySQL root用户密码为mysql123    删除除从本地登录的root用户以外的所有用户    

Single-Table Query

SELECT 字段1,字段2... FROM 表名      WHERE 条件      GROUP BY field      HAVING 筛选      ORDER BY field      LIMIT 限制条数

Execution priority (emphasis)

From top to bottom, executed sequentially, without skipping:

1. where: Constraint conditions;
2. group byGroup
3. havingFilter
4. select: ...;
5. distinct: Go heavy;
6. order bySort
7. limit: Displays the number of bars;

Steps :

1. Find table: from table_name ;
2. Take the where specified constraints, go to the File/table to remove a record;
3. Grouping the strips of records taken out group by , if not group by , the whole as a group;
4. Filtering the results of the grouping having ;
5. Implementation select ;
6. distinctGo heavy;
7. To sort the results by criteria:order by
8. Limit the number of display bars for the result:limit

Prepare tables and records

# COMPANY.EMPLOYEE table structure employee ID ID int name emp_name varchar     Sex sex, enum age, ages int, hire_date date              Post post varchar job description post_comment varchar Salary Salary  Double Office Office INT Department number depart_id int# creating table Create tables Employee (ID  int NOT null unique auto_increment, name varchar (a) not NULL, sex enum (' Male ', ' female ') is not null default ' male ', # mostly male, age int (3) unsigned NOT NULL default, hire_date date not NULL, post varchar, post_comment Varc Har (+), salary double (15,2), Office int, # One department, one room depart_id int); Insert record inserts into employee (Name,sex,age,hir e_date,post,salary,office,depart_id) VALUES (' Egon ', ' Male ', 18, ' 20170301 ', ' The old boy's diplomatic ambassador to the Shahe office ', 7300.33,401,1), (' Alex ', ' Male ', ' 20150302 ', ' teacher ', 1000000.31,401,1), (' Wupeiqi ', ' Male ', Bayi, ' 20130305 ', ' teacher ', ' 8300,401,1 '), (' Yuanhao ', ' Male ', "the ' 20140701 ', ' the '". 3500,401,1), (' Liwenzhou ', ' Male ', ' I ', ' 20121101 ', ' teacher ', 2100,401,1), (' Jingliyang ', ' female ', 18, ' 20110211 ', ' Teacher ', 9000,401,1), (' jinxin ', ' Male ', ' 19000301 ', ' teacher ', 30000,401, 1), (' Dragon ', ' Male ', 48, ' 20101111 ', ' Teacher ', 10000,401, 1), (' Crooked ', ' female ', ' 20150311 ', ' sale ', 3000.13,402,2), (' Ya Ya ', ' female ', ' 20101101 ', ' sale ', 2000.35,402,2), (' Ding ', ' female ', +, ' 20110312 ', ' sale ', 1000.37,402,2), (' stars ', ' female ', ' 20160513 ', ' sale ', 3000.29,402,2), (' Princess ', ' female ', ' I ', ' 20170127 ', ' sale ', 4000.33,402,2), (' Zhang Ye ', ' Male ', ' 20160311 ', ' Operation ', 10000.13,403,3), (' Cheng Bite gold ', ' Male ', ', ' 19970312 ', ' Operation ', 20000,403, 3), (' Cheng Bite silver ', ' female ', ' 20130311 ', ' operation ') , 19000,403, 3), (' Process bite copper ', ' male ', ' 20150411 ', ' Operation ', 18000,403, 3), (' Cheng Bite iron ', ' female ', ', ' 20140512 ', ' Operation ', 17000,403,3);

Simple query

# 查询所有数据    SELECT * FROM employee;            # 查询特定字段类的数据    SELECT name,salary FROM employee;   # 避免重复DISTINCT    SELECT DISTINCT post FROM employee;    # 通过四则运算查询    SELECT name, salary*12 FROM employee;    SELECT name, salary*12 AS Annual_salary FROM employee;  # 重新命名    SELECT name, salary*12 Annual_salary FROM employee;     # 重新命名  # 定义显示格式   CONCAT() 函数用于连接字符串   SELECT CONCAT('姓名: ',name,'  年薪: ', salary*12)  AS Annual_salary    FROM employee;      CONCAT_WS() 第一个参数为分隔符   SELECT CONCAT_WS(':',name,salary*12)  AS Annual_salary    FROM employee;#  练习1. 查出所有员工的名字，薪资,格式为: <名字:egon>    <薪资:3000>    select concat('<名字', name,'>', '    <薪资', salary, '>') from employee;    2. 查出所有的岗位（去掉重复）    select distinct post from employee;3. 查出所有员工名字，以及他们的年薪,年薪的字段名为annual_year    select name, salary*12 as annual_salary from employee;

WHERE

1. comparison operator: ; , < , >= , <= , <> , ! = ;
2. between value is between + to + ;
3. in (80,90,100) value is - or - or -;
4. like ' egon% ' : pattern can be % or _ , % means any number of characters, _ represents a character;
5. logical operators: logical operators can be used directly in multiple conditions and , or , not ;

# single-condition query SELECT name from employee WHERE post= ' sale '; # Multi-Criteria Query SELECT name,salary from employee WHERE post= ' teacher ' and salary>10000;# between NUM1 and Num2 SEL            ECT name,salary from employee WHERE salary between 10000 and 20000;          SELECT Name,salary from the employee WHERE salary not between 10000 and 20000;         # judgment Field < 10000 or > 20000 # is null: To determine if a field is null cannot be used as an equal sign, need to be SELECT name,post_comment from employee    WHERE Post_comment is NULL;            SELECT name,post_comment from employee WHERE post_comment are not NULL;         SELECT name,post_comment from employee WHERE post_comment= ';        # note ' is an empty string, NOT NULL PS: Perform update employee set post_comment= ' where id=2; And then look at it, there's going to be a result. # or, in: Collection Query SELECT name,salary from employee WHERE salary=3000 or salary=3500 O        R salary=4000 OR salary=9000; SELECT name,salary from Employee WHERE SalarY in (3000,3500,4000,9000); Select Name,salary from employee WHERE salary isn't in (3000,3500,4000,9000); # like: Fuzzy query wildcard '% ' SELECT * FR    OM employee WHERE name like ' eg% '; Wildcard ' _ ' SELECT * from the employee WHERE name like ' al__ '; # exercise 1.    Check the position is teacher employee name, age;    Select name, age from employee where post= ' teacher ';    2. Check the name and age of employees who are teacher and older than 30 years old;    Select name, age from employee where post= ' teacher ' and age>30;    3. Check the employee's name, age and salary in the teacher and salary range of 9000-10000.    Select name, age, salary from employee where post= ' teacher ' and salary between 9000 and 10000;    4. View employee information that the job description is not null;    Select ID, post from employee where post_comment are not null;    5. Check the employee's name, age and salary for teacher and salary of 10000 or 9000 or 30000;    Select name, age, salary from employee where post= ' teacher ' and salary in (10000,9000,30000);   6. View the position is teacher and salary is not 10000 or 9000 or 30000 employee name, age, salary; Select name, age, salary from employee where post= ' teacher ' and salary notIn (10000,9000,30000);    7. Check the position is teacher and the name is Jin beginning employee name, annual salary; Select Name,salary*12 as Annual_salary from employee where post= ' teacher ' and name like ' jin% ';

GROUP by

1. The grouping occurs after the keyword where constraint, that is, the grouping is based on the where records obtained later;
2. Grouping refers to classifying all records in one of the same fields, such as positions on employee information sheets, or grouping by gender;

3. Why should we group them?

   1. The highest wage per department
   2. Number of employees in each department
   3. Number of males and women
   4. Tips: The 每 field behind this word is the basis of our group

4. Can be grouped by any field, but after grouping, for example group by post , can only view the post field, if you want to view the information in the group, need to use the aggregation function;

# GROUP BY: Use the SELECT post separately from the employee group by post;# Group BY & Group_concat () combination # to Group by post, and connect group members names SELECT Post,group_concat (name) as Emp_members from the employee group by the post;# Group BY & aggregate functions use # to GROUP by post and see how many per group    Person Select Post,count (ID) as count from employee group by post;    # Find the number of records for each category select Depart_id,count (name) from the employee group by DEPART_ID; +-----------+-------------+    | depart_id |    COUNT (name) |         +-----------+-------------+    |           1 |    8 |         |           2 |    5 |         |           3 |    5 | +-----------+-------------+ 3 rows in Set (0.00 sec) # Combines the names of all records that meet the category into a single select depart_id, Group_concat (name) fr    Om employee GROUP by depart_id;    # Ask for the maximum wage in each department select depart_id, Max (salary) from the employee group by DEPART_ID; +-----------+-------------+    | depart_id |    Max (Salary) |         +-----------+-------------+    |  1 |    1000000.31 |         |     2 |    3000.29 |         | 3 |   20000.00 | +-----------+-------------+ 3 rows in Set (0.00 sec)

Aggregation functions

Emphasis: Aggregation functions aggregate the contents of a group, and if there is no grouping, the default set of

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;# 练习1. 查询 岗位名 以及 岗位包含的所有员工名字    select post, group_concat(name) from emp group by post;2. 查询岗位名以及各岗位内包含的员工个数    select post, count(id) from emp group by post;3. 查询公司内男员工和女员工的个数    select sex, count(id) from emp group by sex;4. 查询岗位名以及各岗位的平均薪资    select post, avg(salary) as '平均薪资' from emp group by post;5. 查询岗位名以及各岗位的最高薪资    select post, max(salary) as '最高薪资' from emp group by post;6. 查询岗位名以及各岗位的最低薪资    select post, max(salary) as '最低薪资' from emp group by post;7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资    select sex, avg(salary) as '平均薪资' from emp group by sex;

Having

HavingAfter the condition is group by executed, the Having grouped fields can be used, and the aggregate function can be used, which cannot be taken directly to other fields.

# 练习1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数    select post, group_concat(name) as emp_names, count(id) as emp_num from employee group by post having emp_num <2;3. 查询每个岗位 平均薪资大于10000 的岗位名、平均工资    select post, avg(salary) as avg_salary from employee         group by post having avg_salary>10000;4. 查询每个岗位 平均薪资大于10000 且 小于20000的岗位名、平均工资    select post, avg(salary) from emp group by post         having avg(salary) in (10000, 20000) ;

ORDER by

# 按单列排序, 默认升序    SELECT * FROM employee ORDER BY salary;    SELECT * FROM employee ORDER BY salary ASC;     # 升序    SELECT * FROM employee ORDER BY salary DESC;    # 降序# 按多列排序    # 先按照age排序，如果年纪相同，则按照薪资排序    SELECT * from employee        ORDER BY age,        salary DESC;# 练习1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序:    select * from employee order by age, hire_date desc;2. 查询每个岗位 平均薪资 大于10000 的 岗位名、平均工资, 结果按平均薪资升序排列:    select post, avg(salary) as post_avg from employee         group by post having post_avg>10000 order by post_avg;

Limit

Limit the number of records for query results;
The index defaults from the 0 beginning;
limit 0, 2: Starting from index 0 , then length is 2 ;

SELECT * FROM employee ORDER BY salary DESC     LIMIT 3;SELECT * FROM employee ORDER BY salary DESC    LIMIT 0,5;SELECT * FROM employee ORDER BY salary DESC    LIMIT 5,5;# 练习1. 分页显示，每页5条select * from emp limit 5;select * from emp limit 5, 5;select * from emp limit 10, 5;

Regular expression Query

SELECT * FROM employee WHERE name REGEXP '^ale';SELECT * FROM employee WHERE name REGEXP 'on$';SELECT * FROM employee WHERE name REGEXP 'm{2}';小结：对字符串匹配的方式WHERE name = 'egon';        # 准确查询WHERE name LIKE 'yua%';     # 模糊查询WHERE name REGEXP 'on$';  # 模糊查询# 练习查看所有员工中名字是jin开头，n或者g结果的员工信息    select * from emp where name regexp "^jin.*[ng]$";

Database 03: Table Logging Operations and queries