MySQL Table operation data operation

First, Introduction

In MySQL management software, you can use the DML language in SQL statements to implement data manipulation, including

Inserting data by using insert
Update to implement data updates
Using Delete to implement data deletion
Use Select to query data and.

Inserting data Insert

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

Third, update data updates

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

Iv. Deleting data Delete

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

Query data 1. Syntax for single-table queries

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

2. Keyword execution priority (emphasis)

重点中的重点：关键字的执行优先级fromwhere 约束条件group by  分组having  过滤selectdistinctorder bylimit

1.找到表:from

2.拿着where指定的约束条件，去文件/表中取出一条条记录3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组4.将分组的结果进行having过滤5.执行select6.去重7.将结果按条件排序：order by8.限制结果的显示条数

Prepare tables and records

Company.employee employee ID ID int name emp_name varchar sex s                Ex enum age int entry Date 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 varcha  R () not null,sex enum (' Male ', ' female ') is not null default ' male ', #大部分是男的age int (3) unsigned NOT NULL default 28,hire_date Date not null,post varchar (+), post_comment varchar (+), salary double (15,2), office int, #一个部门一个屋子depart_id int); View table Structure mysql> desc employee;+--------------+-----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------------+-----------------------+------+-----+---------+----------------+|  Id         | Int (11) | NO | PRI | NULL | auto_increment | | name | varchar (20) |     NO | |                NULL | || sex | Enum (' Male ', ' female ') |     NO | |                Male | || Age | Int (3) unsigned |     NO | |                28 | || hire_date | Date |     NO | |                NULL | || Post | varchar (50) |     YES | |                NULL | || post_comment | varchar (100) |     YES | |                NULL | || Salary | Double (15,2) |     YES | |                NULL | || Office | Int (11) |     YES | |                NULL | || depart_id | Int (11) |     YES | |                NULL | |+--------------+-----------------------+------+-----+---------+----------------+ #插入记录 # Three departments: teaching, Sales, operations insert INTO Employee (NAME,SEX,AGE,HIRE_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 ', 81 , ' 20130305 ', ' teacher ', 8300,401,1), (' Yuanhao ', ' Male ', and ' 20140701 ', ' teacher ', ' 3500,401,1 '), (' Liwenzhou ', ' Male ', 28 , ' 20121101 ', ' teacher ', 2100,401,1), (' Jingliyang ', ' female ', ', ' 20110211 ', ' teacher ', 9000,401,1), (' jinxin ', ' Male ', (' 19000301 ', ' teacher ', 30000,401, 1), (' Dragon ', ' Male ', ' 20101111 ', ' teacher ', 10000,401, 1), (' Crooked ', ' female ', 48, ' 20150311 ', ' sale ', 3000.13,402,2), (' Ya Ya ', ' female ',, ' 20101101 ', ' sale ', 2000.35,402,2), (' Ding ', ' female ', 18, ' 20110312 ', ' sale ', 1000.37,402,2), (' Star ', ' female ', ' 20160513 ', ' Sale ', 3000.29,402,2 '), (' Princess ', ' female ', ' 20170127 ' , 4000.33,402,2), (' Chang ye ', ' Male ', ' 20160311 ', ' operation ', 10000.13,403,3), (' Cheng Jin ', ' Male ', ' 19970312 ' , 20000,403, 3), (' Cheng Bites silver ', ' female ', ' 20130311 ', ' Operation ', 19000,403, 3), (' Cheng Bite copper ', ' male ', ', ' 20150411 ', ' Operation ', 18000,403,3), (' Cheng Bite iron ', ' female ', ', ' 20140512 ', ' Operation ', 17000,403, 3);

#简单查询    SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id     FROM employee;    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;

• Little Practice

1 查出所有员工的名字，薪资,格式为    <名字:egon>    <薪资:3000>2 查出所有的岗位（去掉重复）3 查出所有员工名字，以及他们的年薪,年薪的字段名为annual_year

where constraint

where字句中可以使用：

  1. Comparison operators:> < >= <= <>!=2. Between and 100 values between 10 and 20 3. In (80,90,100) value is 10 or 20 or 304. Like ' eg on% ' pattern can be% or _, and% means any number of characters _ represents a character 5. Logical operators: logical operators and or not  can be used directly in multiple conditions 

#1: Single-condition query SELECT name from employee WHERE post= ' sale '; #2: Multi-criteria Query SELECT name,salary from employee WHERE post= ' teacher ' and salary>10000; #3: keyword between and select n    Ame,salary from employee WHERE salary between 10000 and 20000;    SELECT Name,salary from the employee WHERE salary not between 10000 and 20000;    #4: The keyword is null (determines whether a field is null, cannot be used as an equals 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 a null PS: Perform update employee set post_comment= ' where id=2; And then check it out, there's going to be a result. # #: keyword in collection query SELECT name,salary from employee WHERE salary=3000 or salary=3500 or salary=4000 or S        alary=9000;    SELECT Name,salary from the 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 the employee WHERE name like ' al__ ';

• Little Practice

1. 查看岗位是teacher的员工姓名、年龄2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄3. 查看岗位是teacher且薪资在9000-10000范围内的员工姓名、年龄、薪资4. 查看岗位描述不为NULL的员工信息5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪

Group query: GROUP BY

1. What is a group? Why are they grouped?

#1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的#2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等#3、为何要分组呢？    取每个部门的最高工资    取每个部门的员工数    取男人数和女人数小窍门：‘每’这个字后面的字段，就是我们分组的依据#4、大前提：    可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数

1. Group_by

• First determine the conditions of the grouping, and then use the function to consolidate the records of other fields into one line;

Use the group BY keyword to group SELECT post from the employee group by post alone;Note: We group by post field, then the field of the select query can only be post, want to get other related information within the group, need to use the function GROUP by keyword and group_concat () function with SELECT Post,group_ CONCAT (name) from the employee GROUP by post;#按照岗位分组 and view the member names in the groupSELECT Post,group_concat (name) asEmp_members from employee GROUP by post;GROUP by is used with the aggregation function select Post,count (ID) asCount fromEmployee GROUP BY Post;#按照岗位分组 and see how many people are in each group# Find the number of records for each categoryMysql>Select Depart_id,count (name) fromEmployee GROUP BY depart_id;+-----------+-------------+|depart_id|Count (name)|+-----------+-------------+|         1 |           8 ||         2 |           5 ||         3 |           5 |+-----------+-------------+3Rowsinch Set(0.00Sec# Combine names of all records that match a category into one itemMysql>Select depart_id, Group_concat (name) fromEmployee GROUP BY depart_id;+-----------+------------------------------------------------------------+|depart_id|Group_concat (name)|+-----------+------------------------------------------------------------+|         1 |Egon,alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin, Cheng Long||         2 |Crooked, Ya Ya, Tintin, stars, stars||         3 |Koushik, Cheng Bite Gold, Cheng Bite silver, Cheng Bite copper, Cheng bite iron|+-----------+------------------------------------------------------------+3Rowsinch Set(0.00Sec# Ask for the maximum value in each categoryMysql>Select depart_id,Max(Salary) fromEmployee GROUP BY depart_id;+-----------+-------------+|depart_id| Max(Salary)|+-----------+-------------+|         1 |  1000000.31 ||         2 |     3000.29 ||         3 |    20000.00 |+-----------+-------------+3Rowsinch Set(0.00Sec

1. Aggregation functions

#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组示例：    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;

• Little Practice

1. 查询岗位名以及岗位包含的所有员工名字mysql> select post, group_concat(name) from emp group by post;2. 查询岗位名以及各岗位内包含的员工个数mysql> select post, count(id) from emp group by post;3. 查询公司内男员工和女员工的个数mysql> select sex, count(id) from emp group by sex;4. 查询岗位名以及各岗位的平均薪资mysql> 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;

1. Having filter

#！！！执行优先级从高到低：where > group by > having #1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

• Little Practice

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

1. Query sort: 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;

• Little Practice

1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序select * from emp order by age desc, hire_date desc;2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列# 1.对post进行分组;2.having筛选工资;3.工资排序;select post, avg(salary) as ‘平均工资‘ from emp group by post having avg(salary)>10000 order by avg(salary);3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列

1. Limit the number of records for a query: limit

示例：    SELECT * FROM employee ORDER BY salary DESC         LIMIT 3;                    #默认初始位置为0         SELECT * FROM employee ORDER BY salary DESC        LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条    SELECT * FROM employee ORDER BY salary DESC        LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条

• Little Practice

1. 分页显示，每页5条select * from emp limit 5;select * from emp limit 5, 5;select * from emp limit 10, 5;

1. Querying using regular expressions

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$‘;  # 模糊查询

• Little Practice

查看所有员工中名字是jin开头，n或者g结果的员工信息select * from emp where name regexp "^jin.*[ng]$";

Data manipulation of MySQL table operations