MySQL Data manipulation

Data add and delete change to insert data

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 …;

Update data

语法：    UPDATE 表名 SET        字段1=值1,        字段2=值2,        WHERE CONDITION;示例：    UPDATE student set sname=‘sb‘,sex=‘male‘ WHERE sid=2;

Delete data

语法：    DELETE FROM 表名         WHERE CONITION;示例：    DELETE FROM student WHERE sid=2;

Rights Management

#授权表user #该表放行的权限，针对：所有数据，所有库下所有表，以及表下的所有字段db #该表放行的权限，针对：某一数据库，该数据库下的所有表，以及表下的所有字段tables_priv #该表放行的权限。针对：某一张表，以及该表下的所有字段columns_priv #该表放行的权限，针对：某一个字段#按图解释：user：放行db1，db2及其包含的所有db：放行db1，及其db1包含的所有tables_priv:放行db1.table1，及其该表包含的所有columns_prive:放行db1.table1.column1，只放行该字段

code example

Create user ' Egon ' @ ' 1.1.1.1 ' identified by ' 123 '; Create user ' Egon ' @ ' 192.168.1.% ' identified by ' 123 '; Create user ' ego n ' @ '% ' identified by ' 123 '; #授权: to a folder, to a file, to the permissions of a field in a file to view Help: the common privileges of the helper grant are: Select,update,alter, DeleteAll can represent all permissions except grant # for authorization for all libraries: *.*grant Select On *. * to ' egon1 ' @ ' localhost ' identified by ' 123 '; #只在user表中可以查到egon1用户的select权限被设置为Y # for a database: Db1.*grant select on db1.* to ' egon2 ' @ '% ' identified by ' 123 ';  #只在db表中可以查到egon2用户的select权限被设置为Y # for a table: Db1.t1grant select on Db1.t1 to ' egon3 ' @ '% ' identified by ' 123 '; #只在tables_priv表中可以查到egon3用户的select权限 # for a field:mysql> select * from t3;+------+-------+------+| ID | name |    Age |+------+-------+------+| 1 |   Egon1 |    18 | | 2 |   Egon2 |    19 | | 3 |   Egon3 | |+------+-------+------+grant Select (id,name), update (age) on Db1.t3 to ' egon4 ' @ ' localhost ' identified by ' 123 '; #可以在tables_priv和columns_priv中看到相应的权限mysql > select * from Tables_priv where user= ' Egon4 ' \g************************ 1. Row *********************Host:localhost db:db1 user:egon4 table_name:t3 grantor: [email protected] Timestam p:0000-00-00 00:00:00 Table_priv:column_priv:select,updaterow in Set (0.00 sec) mysql> Select * from Columns_priv wher E user= ' Egon4 ' \g*************************** 1. Row *************************** host:localhost db:db1 user:egon4 Table_name:t3Column_name:id Tim estamp:0000-00-00 00:00:00column_priv:select*************************** 2. Row *************************** host:localhost db:db1 user:egon4 Table_name:t3Column_name:name T imestamp:0000-00-00 00:00:00column_priv:select*************************** 3. Row *************************** host:localhost db:db1 user:egon4 Table_name:t3Column_name:age Ti Mestamp:0000-00-00 00:00:00column_priv:updaterows in Set (0.00 sec) #删除权限revoke Select on db1.* from ' Egon ' @ '% ';

Single-Table Query

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

The priority of a single-table query 1. Find table: From2. Take the constraints specified in the Where, go to the File/table to take out a strip of records 3. Group BY, if there is no group BY, The whole as a group of 4. The results of the grouping are filtered 5. Perform select6. Go to weight 7. Sort the results by condition: order By8. Limit the number of display bars for results simple query combat

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 ', Bayi, ' 20130305 ', ' teacher ', 8300,401,1 ', (' Yuanhao ', ' Male ', ' the ' 20140701 ', ' the ', ' the ', ' the ', "Teacher ', 3500,401,1 '), (' Liwenzhou ', ' Male ', ' 20121101 ', ' teacher ', ' 2100,401,1 ', ' Jingliyang ', ' female ', ' 20110211 ', ' teacher ', 9000,401,1 ', (' jinxin ') , ' Male ', ' 19000301 ', ' teacher ', 30000,401, 1), (' Into the Dragon ', ' Male ', ', ' 20101111 ', ' teacher ', 10000,401, 1), (' Crooked ', ' female ', , ' 20150311 ', ' sale ', 3000.13,402,2), #以下是销售部门 (' Ya Ya ', ' female ', ' a ', ' 20101101 ', ' Sale ', 2000.35,402,2 '), (' Ding ', ' female ', (' 20110312 ', ' sale ', 1000.37,402,2), (' stars ', ' female ', ', ' 20160513 ', ' sale ', ' 3000.29,402,2 '), (' Princess ', ' female ', 28, ' 20170127 ', ' sale ', 4000.33,402,2), (' Ono ', ' Male ', ', ' 20160311 ', ' Operation ', 10000.13,403,3 '), #以下是运营部门 (' Cheng Bite gold ', ' male ', (' 19970312 ', ' Operation ', 20000,403, 3), (' Cheng Bites silver ', ' female ', ' 20130311 ', ' Operation ', 19000,403, 3), (' Cheng Bite copper ', ' male ', 18 , ' 20150411 ', ' Operation ', 18000,403, 3), (' Cheng Bite iron ', ' female ', ' 20140512 ', ' operation ', 17000,403,3); #ps: If you are in a Windows system, Insert Chinese characters, select'sThe result is blank, and all character encodings can be set uniformly to GBK 

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;#避免重复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;

Distinct, concat usage

select concat(‘<名字:‘,name,‘>    ‘,‘<薪资:‘,salary,‘>‘) from employee;select distinct depart_id from employee;select name,salary*12 annual_salary from employee;

WHERE usage

The WHERE clause can be used:

1. comparison operator:> < >= <= <>! =
2. between and 100 values between 10 and 20
3. in (80,90,100) value is 10 or 20 or 3 0
4. like ' egon% '
   pattern can be% or _,
   % means any number of characters
   _ represents a character
5. logical operator: 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-1000范围内的员工姓名、年龄、薪资4. 查看岗位描述不为NULL的员工信息5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪

Answer

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,9000,30000);select name,age,salary from employee where post=‘teacher‘ and salary not in (10000,9000,30000);select name,salary*12 from employee where post=‘teacher‘ and name like ‘jin%‘;

Group query: GROUP BY

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

Only_full_group_by

#查看MySQL 5.7默认的sql_mode如下：mysql> select @@global.sql_mode;ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION#！！！注意ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，在ONLY_FULL_GROUP_BY模式下，target list中的值要么是来自于聚集函数的结果，要么是来自于group by list中的表达式的值。#设置sql_mole如下操作(我们可以去掉ONLY_FULL_GROUP_BY模式)：mysql> set global sql_mode=‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION‘;

Mysql> SELECT @ @global. sql_mode;+-------------------+|                   @ @global. Sql_mode |+-------------------+| |+-------------------+1 Row in Set (0.00 sec) mysql> SELECT * from EMP GROUP by post; +----+------+--------+-----+------------+----------------------------+--------------+------------+--------+---- -------+| ID | name | sex | Age | hire_date | Post | post_comment | Salary | Office | depart_id |+----+------+--------+-----+------------+----------------------------+--------------+------------+-- ------+-----------+| 14 | Koushik |  Male | 28 | 2016-03-11 | Operation |   NULL |    10000.13 |         403 |  3 | | 9 | Crooked |  Female | 48 | 2015-03-11 | Sale |    NULL |    3000.13 |         402 |  2 | | 2 | Alex |  Male | 78 | 2015-03-02 | Teacher | NULL |    1000000.31 |         401 |  1 | | 1 | Egon |  Male | 18 | 2017-03-01 | Old boy in Shahe office Diplomatic Ambassador |    NULL | 7300.33|         401 | 1 |+----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-- ---------+4 rows in Set (0.00 sec) #由于没有设置ONLY_FULL_GROUP_BY, so you can have the result, the default is the first record in the group, but this is meaningless mysql> set global sql_ Mode= ' only_full_group_by '; Query OK, 0 rows Affected (0.00 sec) mysql> quit #设置成功后, be sure to exit and then re-login to take effect byemysql> use db1;database changedmysql> s Elect * from the EMP group by post; #报错ERROR 1055 (42000): ' db1.emp.id ' isn ' t in GROUP bymysql> select Post,count (ID) from the EMP GROUP by post; #只能查看分组依据和使用聚合函数 +----------------------------+-----------+| Post | Count (ID) |+----------------------------+-----------+|         Operation | 5 | |         Sale | 5 | |         Teacher | 7 | |         Old boy in Shahe office Diplomatic Ambassador | 1 |+----------------------------+-----------+4 rows in Set (0.00 sec)

GROUP by

单独使用GROUP BY关键字分组    SELECT post FROM employee GROUP BY post;    注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数GROUP BY关键字和GROUP_CONCAT()函数一起使用    SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照岗位分组，并查看组内成员名    SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;GROUP BY与聚合函数一起使用    select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人

Emphasize

如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义多条记录之间的某个字段值相同，该字段通常用来作为分组的依据

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;

Having filter

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

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;

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条

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} ';

Summary: How strings are matched
WHERE name = ' Egon ';
WHERE name like ' yua% ';
WHERE name REGEXP ' on$ ';
``
Transferred from: http://www.cnblogs.com/linhaifeng/articles/7267592.html

Multi-Table Query

Https://www.luffycity.com/python-book/di-8-zhang-mysql-shu-ju-ku/84-shu-ju-cao-zuo/843-duo-biao-cha-xun.html

MySQL Data manipulation